Jacek Pyziak
b653cea252
- Create SQL migration for prompt templates used in article and image generation. - Add migration to change publish interval from days to hours in the sites table. - Implement InstallerController to handle installation requests and validation. - Develop FtpService for FTP connections and file uploads. - Create InstallerService to manage the WordPress installation process, including downloading, extracting, and configuring WordPress. - Add index view for the installer with form inputs for FTP, database, and WordPress admin settings. - Implement progress tracking for the installation process with AJAX polling.
84 lines
3.1 KiB
SQL
84 lines
3.1 KiB
SQL
-- BackPRO - Initial database migration
|
|
-- Baza danych musi być już utworzona w panelu hostingu
|
|
|
|
-- Users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Sites (WordPress instances)
|
|
CREATE TABLE IF NOT EXISTS sites (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
url VARCHAR(255) NOT NULL,
|
|
api_user VARCHAR(100) NOT NULL,
|
|
api_token VARCHAR(255) NOT NULL,
|
|
publish_interval_hours INT NOT NULL DEFAULT 24,
|
|
last_published_at DATETIME NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
is_multisite TINYINT(1) NOT NULL DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Topics (assigned to sites)
|
|
CREATE TABLE IF NOT EXISTS topics (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
site_id INT NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT NULL,
|
|
wp_category_id INT NULL,
|
|
article_count INT NOT NULL DEFAULT 0,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Articles (generated and published)
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
site_id INT NOT NULL,
|
|
topic_id INT NOT NULL,
|
|
title VARCHAR(500) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
wp_post_id INT NULL,
|
|
image_url VARCHAR(500) NULL,
|
|
status ENUM('generated', 'published', 'failed') NOT NULL DEFAULT 'generated',
|
|
ai_model VARCHAR(50) NULL,
|
|
prompt_used TEXT NULL,
|
|
error_message TEXT NULL,
|
|
published_at DATETIME NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (topic_id) REFERENCES topics(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Settings (key-value store)
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
`key` VARCHAR(100) NOT NULL UNIQUE,
|
|
value TEXT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_topics_site_id ON topics(site_id);
|
|
CREATE INDEX idx_articles_site_id ON articles(site_id);
|
|
CREATE INDEX idx_articles_topic_id ON articles(topic_id);
|
|
CREATE INDEX idx_articles_status ON articles(status);
|
|
CREATE INDEX idx_sites_is_active ON sites(is_active);
|
|
CREATE INDEX idx_sites_last_published ON sites(last_published_at);
|
|
|
|
-- Default admin user - hasło ustawiasz przez skrypt install.php
|
|
-- LUB ręcznie wstaw hash bcrypt:
|
|
-- INSERT INTO users (username, password_hash) VALUES ('admin', '$2y$10$...');
|
|
|
|
-- Default settings
|
|
INSERT INTO settings (`key`, value) VALUES
|
|
('openai_model', 'gpt-4o'),
|
|
('image_provider', 'freepik'),
|
|
('article_min_words', '800'),
|
|
('article_max_words', '1200')
|
|
ON DUPLICATE KEY UPDATE value = value;
|