-- 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;