-- Safe upgrade migration for Advanced Feed Hub. Existing tables/data are not deleted.
CREATE TABLE IF NOT EXISTS feed_sources (
    id VARCHAR(64) NOT NULL, name VARCHAR(255) NOT NULL, source_kind VARCHAR(30) NOT NULL DEFAULT 'rss',
    provider VARCHAR(30) NOT NULL DEFAULT 'generic', input_url VARCHAR(1000) NULL, feed_url VARCHAR(1000) NOT NULL,
    site_url VARCHAR(1000) NULL, channel_id VARCHAR(100) NULL, category VARCHAR(100) NOT NULL DEFAULT 'general',
    tags VARCHAR(500) NULL, active TINYINT(1) NOT NULL DEFAULT 1, priority INT NOT NULL DEFAULT 100,
    include_keywords TEXT NULL, exclude_keywords TEXT NULL, last_fetch_at DATETIME NULL, last_success_at DATETIME NULL,
    last_error TEXT NULL, consecutive_fails INT NOT NULL DEFAULT 0, item_count INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_feed_url (feed_url(255)),
    INDEX idx_feed_active_priority (active, priority), INDEX idx_feed_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS feed_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, source_id VARCHAR(64) NOT NULL, external_id VARCHAR(500) NOT NULL,
    item_kind VARCHAR(30) NOT NULL DEFAULT 'feed_item', title VARCHAR(700) NOT NULL, description TEXT NULL,
    thumbnail_url VARCHAR(1200) NULL, watch_url VARCHAR(1200) NULL, author VARCHAR(255) NULL,
    category VARCHAR(100) NOT NULL DEFAULT 'general', tags VARCHAR(500) NULL, published_at DATETIME NULL,
    published_ts BIGINT NOT NULL DEFAULT 0, highlight TINYINT(1) NOT NULL DEFAULT 0, featured TINYINT(1) NOT NULL DEFAULT 0,
    hidden TINYINT(1) NOT NULL DEFAULT 0, highlight_order INT NOT NULL DEFAULT 100, fetched_at DATETIME NOT NULL,
    PRIMARY KEY (id), UNIQUE KEY uq_source_external (source_id, external_id(190)), INDEX idx_feed_items_pub (published_ts),
    INDEX idx_feed_items_category_pub (category, published_ts), INDEX idx_feed_items_highlight (highlight, highlight_order, published_ts),
    INDEX idx_feed_items_featured (featured, published_ts), CONSTRAINT fk_feed_items_source FOREIGN KEY (source_id) REFERENCES feed_sources(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS feed_refresh_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, source_id VARCHAR(64) NULL, status VARCHAR(20) NOT NULL,
    message VARCHAR(1000) NULL, items_seen INT NOT NULL DEFAULT 0, items_saved INT NOT NULL DEFAULT 0,
    duration_ms INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id),
    INDEX idx_feed_logs_created (created_at), INDEX idx_feed_logs_source (source_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;