// src/lib/discoverDb.ts
// Auto-creates the 5 discovery tables on first import

import pool from './db';

let initialized = false;

export async function ensureDiscoveryTables() {
    if (initialized) return;
    initialized = true;

    await pool.execute(`
        CREATE TABLE IF NOT EXISTS algo_feed_snapshots (
            id          INT AUTO_INCREMENT PRIMARY KEY,
            session_id  VARCHAR(36) NOT NULL,
            title       TEXT NOT NULL,
            channel     VARCHAR(255),
            views       VARCHAR(50),
            video_id    VARCHAR(50),
            thumbnail   TEXT,
            source      VARCHAR(100) DEFAULT 'trending',
            crawled_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_session (session_id),
            INDEX idx_crawled (crawled_at)
        )
    `);

    await pool.execute(`
        CREATE TABLE IF NOT EXISTS discovered_niches (
            id              INT AUTO_INCREMENT PRIMARY KEY,
            name            VARCHAR(255) NOT NULL,
            keywords        TEXT,
            source          VARCHAR(50) DEFAULT 'algo_feed',
            session_count   INT DEFAULT 1,
            item_count      INT DEFAULT 1,
            score           FLOAT DEFAULT 0,
            sample_titles   TEXT,
            sample_channels TEXT,
            last_seen       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_source (source),
            INDEX idx_score (score DESC),
            INDEX idx_last_seen (last_seen)
        )
    `);

    await pool.execute(`
        CREATE TABLE IF NOT EXISTS channel_snapshots (
            id              INT AUTO_INCREMENT PRIMARY KEY,
            channel_id      VARCHAR(100) NOT NULL,
            channel_title   VARCHAR(255),
            subscribers     BIGINT DEFAULT 0,
            total_views     BIGINT DEFAULT 0,
            video_count     INT DEFAULT 0,
            snapped_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_channel (channel_id),
            INDEX idx_snapped (snapped_at)
        )
    `);

    // Feature 1: Niche score trend history — one row per crawl per niche, powers sparklines
    await pool.execute(`
        CREATE TABLE IF NOT EXISTS niche_score_history (
            id            INT AUTO_INCREMENT PRIMARY KEY,
            niche_name    VARCHAR(255) NOT NULL,
            score         FLOAT NOT NULL,
            session_count INT DEFAULT 1,
            item_count    INT DEFAULT 1,
            crawled_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_name (niche_name),
            INDEX idx_crawled (crawled_at)
        )
    `);

    // Feature 12: Saved niches — bookmarks created from the main search page
    await pool.execute(`
        CREATE TABLE IF NOT EXISTS saved_niches (
            id                INT AUTO_INCREMENT PRIMARY KEY,
            keyword           VARCHAR(255) NOT NULL UNIQUE,
            white_space_score INT DEFAULT 0,
            label             VARCHAR(100) DEFAULT '',
            notes             TEXT,
            saved_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            last_checked      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_keyword (keyword),
            INDEX idx_saved (saved_at DESC)
        )
    `);
}

ensureDiscoveryTables().catch(e => console.error('[DiscoverDB] init error:', e));
