/* =========================================================
   MySQL Database Layer — Connection Pool + Helper Functions
   ========================================================= */

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

const pool = mysql.createPool({
  host: process.env.DB_HOST || "localhost",
  port: parseInt(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER || "root",
  password: process.env.DB_PASSWORD || "",
  database: process.env.DB_NAME || "zatoctlb_za_farm_kh",
  waitForConnections: true,
  connectionLimit: 20,
  queueLimit: 0,
  charset: "utf8mb4",
  timezone: "+00:00",
  // Auto-reconnect on connection loss
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000,
});

// Create secondary connection pool for Licenses
const licensePool = mysql.createPool({
  host: process.env.LICENSE_DB_HOST || process.env.DB_HOST || "localhost",
  port: parseInt(process.env.LICENSE_DB_PORT || process.env.DB_PORT) || 3306,
  user: process.env.LICENSE_DB_USER || "zatoctlb_my_license_db",
  password: process.env.LICENSE_DB_PASSWORD || "C6RudNj35[Uv",
  database: process.env.LICENSE_DB_NAME || "zatoctlb_my_license_db",
  waitForConnections: true,
  connectionLimit: 20,
  queueLimit: 0,
  charset: "utf8mb4",
  timezone: "+00:00",
  enableKeepAlive: true,
  keepAliveInitialDelay: 10000,
});

// Test connection on startup
pool.getConnection()
  .then(async (conn) => {
    console.log("✅ MySQL connected successfully");
    try {
      await conn.execute("UPDATE services SET price = 0.055 WHERE id = 'gmail_otp'");
      console.log("✅ Gmail OTP price updated to $0.055 in database");
      await conn.execute("INSERT IGNORE INTO services (id, title, sub, stock, price) VALUES ('phone_sms_us', 'Phone SMS (US)', 'US Phone Number for FB Verification', 9999, 0.032)");
      await conn.execute("UPDATE services SET price = 0.032 WHERE id = 'phone_sms_us'");
      await conn.execute("INSERT IGNORE INTO services (id, title, sub, stock, price) VALUES ('phone_sms_kh', 'Phone SMS (KH)', 'KH Phone Number for FB Verification', 9999, 0.032)");
      await conn.execute("UPDATE services SET price = 0.032 WHERE id = 'phone_sms_kh'");
      console.log("✅ Phone SMS service verified in database");
      
      // Fix payments.username truncation bug (expand to 255 chars)
      await conn.execute("ALTER TABLE payments MODIFY username VARCHAR(255) NOT NULL");
      console.log("✅ Expanded payments.username to VARCHAR(255)");
    } catch (e) {
      console.warn("⚠️ Failed to update database:", e.message);
    }
    conn.release();
  })
  .catch(err => {
    console.error("❌ MySQL connection failed:", err.message);
    console.error("   សូមពិនិត្យ DB_HOST, DB_USER, DB_PASSWORD, DB_NAME ក្នុង .env");
  });

// ═══════════════════════════════════════════════════════════
// Helper functions
// ═══════════════════════════════════════════════════════════

/**
 * Execute a query with parameters
 * @param {string} sql - SQL query with ? placeholders
 * @param {Array} params - Parameters to bind
 * @returns {Promise<Array>} - Result rows
 */
async function query(sql, params = []) {
  const [rows] = await pool.execute(sql, params);
  return rows;
}

async function queryLicense(sql, params = []) {
  const [rows] = await licensePool.execute(sql, params);
  return rows;
}

/**
 * Execute an INSERT and return insertId
 */
async function insert(sql, params = []) {
  const [result] = await pool.execute(sql, params);
  return result;
}

async function insertLicense(sql, params = []) {
  const [result] = await licensePool.execute(sql, params);
  return result;
}

/**
 * Get a single row
 */
async function queryOne(sql, params = []) {
  const [rows] = await pool.execute(sql, params);
  return rows[0] || null;
}

async function queryOneLicense(sql, params = []) {
  const [rows] = await licensePool.execute(sql, params);
  return rows[0] || null;
}

/**
 * Transaction helper
 */
async function transaction(callback) {
  const conn = await pool.getConnection();
  await conn.beginTransaction();
  try {
    const result = await callback(conn);
    await conn.commit();
    return result;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

async function licenseTransaction(callback) {
  const conn = await licensePool.getConnection();
  await conn.beginTransaction();
  try {
    const result = await callback(conn);
    await conn.commit();
    return result;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

module.exports = { 
  pool, query, insert, queryOne, transaction,
  licensePool, queryLicense, insertLicense, queryOneLicense, licenseTransaction 
};
