import mysql from 'mysql2/promise';
import dotenv from 'dotenv';
dotenv.config();

async function createSchema() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
  });

  try {
    const dbName = process.env.DB_NAME || 'youtube_niche_finder';
    await connection.query(`CREATE DATABASE IF NOT EXISTS \`${dbName}\`;`);
    await connection.query(`USE \`${dbName}\`;`);

    // Channels Table
    await connection.query(`
      CREATE TABLE IF NOT EXISTS channels (
        id VARCHAR(255) PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        customUrl VARCHAR(255),
        publishedAt DATETIME,
        thumbnailUrl VARCHAR(500),
        country VARCHAR(10),
        isShortsFocus BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);

    // Daily Stats Table for 48h and 7d calculations
    await connection.query(`
      CREATE TABLE IF NOT EXISTS channel_stats (
        id INT AUTO_INCREMENT PRIMARY KEY,
        channel_id VARCHAR(255) NOT NULL,
        date DATE NOT NULL,
        viewCount BIGINT NOT NULL,
        subscriberCount BIGINT NOT NULL,
        videoCount INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY unique_channel_date (channel_id, date),
        FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
      );
    `);

    // Videos table for outlier detection
    await connection.query(`
      CREATE TABLE IF NOT EXISTS videos (
        id VARCHAR(255) PRIMARY KEY,
        channel_id VARCHAR(255) NOT NULL,
        title VARCHAR(255) NOT NULL,
        publishedAt DATETIME NOT NULL,
        viewCount BIGINT NOT NULL,
        likeCount INT,
        commentCount INT,
        duration VARCHAR(50),
        isShort BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
      );
    `);

    // Watchlist table — only these channels are tracked/stored
    await connection.query(`
      CREATE TABLE IF NOT EXISTS watchlist (
        id INT AUTO_INCREMENT PRIMARY KEY,
        channel_id VARCHAR(255) NOT NULL UNIQUE,
        channel_title VARCHAR(255) NOT NULL,
        channel_thumbnail VARCHAR(500),
        channel_description TEXT,
        publishedAt DATETIME,
        added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
      );
    `);

    console.log('Database schema created successfully.');
  } catch (error) {
    console.error('Error creating database schema:', error);
  } finally {
    await connection.end();
  }
}

createSchema();
