const mysql = require('mysql2/promise');
const dotenv = require('dotenv');

dotenv.config();

// Helper to initialize database and tables
const initDB = async () => {
  try {
    // 1. Connect without database to ensure it exists
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
    });

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

    console.log(`Database "${dbName}" verified/created successfully.`);

    // 2. Connect to the database and initialize tables
    const dbConnection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: dbName
    });

    // Create admins table
    await dbConnection.query(`
      CREATE TABLE IF NOT EXISTS \`admins\` (
        \`id\` INT AUTO_INCREMENT PRIMARY KEY,
        \`username\` VARCHAR(255) NOT NULL UNIQUE,
        \`password\` VARCHAR(255) NOT NULL,
        \`created_at\` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // Create videos table
    await dbConnection.query(`
      CREATE TABLE IF NOT EXISTS \`videos\` (
        \`id\` INT AUTO_INCREMENT PRIMARY KEY,
        \`title\` VARCHAR(255) NOT NULL,
        \`speaker\` VARCHAR(255) DEFAULT 'Admin Added',
        \`category\` VARCHAR(100) NOT NULL,
        \`youtubeId\` VARCHAR(100) NOT NULL,
        \`duration\` VARCHAR(50),
        \`description\` TEXT,
        \`isShort\` BOOLEAN DEFAULT FALSE,
        \`created_at\` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // Check and add new columns to videos table if they are missing
    const [columns] = await dbConnection.query('SHOW COLUMNS FROM `videos`');
    const colNames = columns.map(c => c.Field);

    if (!colNames.includes('videoId')) {
      await dbConnection.query('ALTER TABLE `videos` ADD COLUMN `videoId` VARCHAR(255) UNIQUE AFTER `id`');
      console.log('Added column videoId to videos table.');
    }
    if (!colNames.includes('thumbnail')) {
      await dbConnection.query('ALTER TABLE `videos` ADD COLUMN `thumbnail` VARCHAR(500) AFTER `description`');
      console.log('Added column thumbnail to videos table.');
    }
    if (!colNames.includes('publishDate')) {
      await dbConnection.query('ALTER TABLE `videos` ADD COLUMN `publishDate` DATETIME AFTER `thumbnail`');
      console.log('Added column publishDate to videos table.');
    }
    if (!colNames.includes('videoUrl')) {
      await dbConnection.query('ALTER TABLE `videos` ADD COLUMN `videoUrl` VARCHAR(255) AFTER `publishDate`');
      console.log('Added column videoUrl to videos table.');
    }

    // Create bhajans table
    await dbConnection.query(`
      CREATE TABLE IF NOT EXISTS \`bhajans\` (
        \`id\` INT AUTO_INCREMENT PRIMARY KEY,
        \`title\` VARCHAR(255) NOT NULL,
        \`singer\` VARCHAR(255) DEFAULT 'Traditional',
        \`audio_url\` TEXT NOT NULL,
        \`category\` VARCHAR(100) DEFAULT 'Mantra',
        \`duration\` VARCHAR(50),
        \`description\` TEXT,
        \`lyrics\` TEXT,
        \`created_at\` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    `);

    // Check and add columns to bhajans table if missing
    const [bhajanColumns] = await dbConnection.query('SHOW COLUMNS FROM `bhajans`');
    const bhajanColNames = bhajanColumns.map(c => c.Field);

    if (!bhajanColNames.includes('singer')) {
      await dbConnection.query('ALTER TABLE `bhajans` ADD COLUMN `singer` VARCHAR(255) DEFAULT "Traditional" AFTER `title`');
      console.log('Added column singer to bhajans table.');
      if (bhajanColNames.includes('artist')) {
        await dbConnection.query('UPDATE `bhajans` SET `singer` = `artist` WHERE `singer` = "Traditional" OR `singer` IS NULL');
      }
    }
    if (!bhajanColNames.includes('audio_url')) {
      await dbConnection.query('ALTER TABLE `bhajans` ADD COLUMN `audio_url` TEXT AFTER `singer`');
      console.log('Added column audio_url to bhajans table.');
      if (bhajanColNames.includes('url')) {
        await dbConnection.query('UPDATE `bhajans` SET `audio_url` = \`url\` WHERE `audio_url` IS NULL');
      }
    }
    if (!bhajanColNames.includes('lyrics')) {
      await dbConnection.query('ALTER TABLE `bhajans` ADD COLUMN `lyrics` TEXT AFTER `audio_url`');
      console.log('Added column lyrics to bhajans table.');
    }

    // Ensure legacy columns are nullable so they don't block inserts
    if (bhajanColNames.includes('url')) {
      await dbConnection.query('ALTER TABLE `bhajans` MODIFY COLUMN `url` TEXT NULL');
      console.log('Ensured legacy url column is nullable.');
    }
    if (bhajanColNames.includes('artist')) {
      await dbConnection.query('ALTER TABLE `bhajans` MODIFY COLUMN `artist` VARCHAR(255) NULL');
      console.log('Ensured legacy artist column is nullable.');
    }

    console.log('Database tables verified/created successfully.');
    await dbConnection.end();

  } catch (error) {
    console.error('Error initializing database:', error);
  }
};

// Run DB verification
(async () => {
  await initDB();
})().catch(err => console.error(err));

// Create connection pool for application queries
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'vedant_devotions',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

module.exports = pool;
