CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(120) NOT NULL UNIQUE,
    value LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(120) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS feeds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    rss_url TEXT NOT NULL,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    feed_id INT NOT NULL,
    original_title TEXT NULL,
    original_description LONGTEXT NULL,
    extracted_title TEXT NULL,
    extracted_text LONGTEXT NULL,
    source_url TEXT NOT NULL,
    source_hash CHAR(40) NOT NULL,
    source_published_at VARCHAR(120) NULL,
    rewrite_status VARCHAR(40) NOT NULL DEFAULT 'pending',
    telegram_status VARCHAR(40) NOT NULL DEFAULT 'pending',
    queue_status VARCHAR(40) NOT NULL DEFAULT 'queued',
    rewritten_text LONGTEXT NULL,
    error_message LONGTEXT NULL,
    telegram_response LONGTEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    sent_at DATETIME NULL,
    UNIQUE KEY uniq_source_hash (source_hash),
    KEY idx_feed_id (feed_id),
    KEY idx_queue_status (queue_status),
    KEY idx_rewrite_status (rewrite_status),
    KEY idx_telegram_status (telegram_status),
    CONSTRAINT fk_posts_feed FOREIGN KEY (feed_id) REFERENCES feeds(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;