const mysql = require("mysql2/promise");
const dotenv = require("dotenv");
const path = require("path");

// Load .env from project root (two levels up from bot.js, one level up from this file)
dotenv.config({ path: path.join(__dirname, "..", ".env") });

console.log({
  user: process.env.DB_USER,
  db: process.env.DB_NAME,
  envPath: path.join(__dirname, "..", ".env")
});

const dbConfig = {
  host: process.env.DB_HOST || "127.0.0.1",
  port: Number(process.env.DB_PORT || 3306),
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD || "",
};

const dbName = process.env.DB_NAME || "eaglertiers";

const pool = mysql.createPool({
  ...dbConfig,
  database: dbName,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

// Test the database connection immediately
(async () => {
  try {
    const conn = await pool.getConnection();
    console.log("✅ Database connected successfully");
    conn.release();
  } catch (err) {
    console.error("❌ Database connection failed:", err.message);
    // You may want to exit the process if the database is critical
    // process.exit(1);
  }
})();

async function ensureDatabase() {
  const adminPool = mysql.createPool({
    ...dbConfig,
    waitForConnections: true,
    connectionLimit: 2,
    queueLimit: 0,
  });

  const schemaPool = mysql.createPool({
    ...dbConfig,
    database: dbName,
    waitForConnections: true,
    connectionLimit: 2,
    queueLimit: 0,
  });

  try {
    await adminPool.query(`CREATE DATABASE IF NOT EXISTS \`${dbName}\``);

    await schemaPool.query(`
      CREATE TABLE IF NOT EXISTS players (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(32) NOT NULL UNIQUE,
        region ENUM('NA','EU','AS','AU') NOT NULL DEFAULT 'NA',
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
      )
    `);

    try {
      await schemaPool.query(
        "ALTER TABLE players ADD COLUMN IF NOT EXISTS region ENUM('NA','EU','AS','AU') NOT NULL DEFAULT 'NA' AFTER username"
      );
    } catch (error) {
      if (!String(error?.message || "").includes("Duplicate column name")) {
        throw error;
      }
    }

    await schemaPool.query(`
      CREATE TABLE IF NOT EXISTS player_ranks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        player_id INT NOT NULL,
        gamemode VARCHAR(32) NOT NULL,
        tier ENUM('HT1','LT1','HT2','LT2','HT3','LT3','HT4','LT4','HT5','LT5') NOT NULL,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_player_gamemode (player_id, gamemode),
        CONSTRAINT fk_player_ranks_player
          FOREIGN KEY (player_id)
          REFERENCES players(id)
          ON DELETE CASCADE
      )
    `);

    try {
      await schemaPool.query("CREATE INDEX idx_player_ranks_gamemode ON player_ranks (gamemode)");
    } catch (error) {
      if (!String(error?.message || "").includes("Duplicate key name")) {
        throw error;
      }
    }

    await schemaPool.query(`
      CREATE TABLE IF NOT EXISTS queue_test_tickets (
        id INT AUTO_INCREMENT PRIMARY KEY,
        discord_user_id VARCHAR(32) NOT NULL,
        tester_discord_id VARCHAR(32) NOT NULL,
        gamemode VARCHAR(32) NOT NULL,
        ticket_channel_id VARCHAR(32) NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_queue_tickets_user_mode_time (discord_user_id, gamemode, created_at)
      )
    `);

  } finally {
    await schemaPool.end();
    await adminPool.end();
  }
}

module.exports = {
  pool,
  ensureDatabase,
};