CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sources (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    source_type ENUM('rss','webpage') NOT NULL DEFAULT 'rss',
    url TEXT NOT NULL,
    category VARCHAR(100) NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    scan_interval_minutes INT NOT NULL DEFAULT 60,
    last_scan_at DATETIME NULL,
    last_status VARCHAR(50) NULL,
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE raw_contents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_id BIGINT UNSIGNED NOT NULL,
    original_title TEXT NOT NULL,
    original_url TEXT NOT NULL,
    original_content LONGTEXT NULL,
    original_image TEXT NULL,
    published_at DATETIME NULL,
    content_hash CHAR(64) NOT NULL,
    status ENUM('new','processed','rejected') NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_content_hash (content_hash),
    INDEX idx_source_id (source_id),
    INDEX idx_status (status),
    CONSTRAINT fk_raw_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE articles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    raw_content_id BIGINT UNSIGNED NOT NULL UNIQUE,
    seo_title TEXT NULL,
    short_title TEXT NULL,
    summary LONGTEXT NULL,
    article_bm LONGTEXT NULL,
    meta_description TEXT NULL,
    seo_keywords TEXT NULL,
    facebook_caption LONGTEXT NULL,
    instagram_caption LONGTEXT NULL,
    tiktok_caption LONGTEXT NULL,
    hashtags TEXT NULL,
    featured_image_path TEXT NULL,
    status ENUM('draft','pending_review','approved','published','rejected') NOT NULL DEFAULT 'draft',
    wordpress_post_id BIGINT NULL,
    published_url TEXT NULL,
    generated_at DATETIME NULL,
    published_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_article_raw FOREIGN KEY (raw_content_id) REFERENCES raw_contents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE publish_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    article_id BIGINT UNSIGNED NOT NULL,
    platform VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL,
    response_log LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_article_id (article_id),
    CONSTRAINT fk_log_article FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;