/**
 * mysqldb.js — MySQL connection for admin authentication
 * Connecticut Elderly Services Training Platform
 *
 * Handles admin_users table: login, logout, password change.
 * All learner/module/exam data stays in SQLite (lib/db.js).
 */

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

let pool = null;

// ─── Connect ──────────────────────────────────────────────────────────────────

async function connect() {
  if (pool) return pool;

  pool = mysql.createPool({
    host:     process.env.DB_HOST     || 'localhost',
    port:     parseInt(process.env.DB_PORT || '3306'),
    user:     process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 5,
    queueLimit: 0
  });

  // Test connection on startup
  try {
    const conn = await pool.getConnection();
    conn.release();
    console.log('[MySQL] Connected to', process.env.DB_NAME);
  } catch (err) {
    console.error('[MySQL] Connection failed:', err.message);
    pool = null;
    throw err;
  }

  return pool;
}

// ─── Password helpers ─────────────────────────────────────────────────────────

function hashPassword(password) {
  const salt = crypto.randomBytes(32).toString('hex');
  const hash = crypto.scryptSync(password, salt, 64).toString('hex');
  return salt + ':' + hash;
}

function verifyPassword(password, stored) {
  try {
    const [salt, hash] = stored.split(':');
    const attempt = crypto.scryptSync(password, salt, 64).toString('hex');
    return crypto.timingSafeEqual(Buffer.from(attempt, 'hex'), Buffer.from(hash, 'hex'));
  } catch {
    return false;
  }
}

// ─── Admin user methods ───────────────────────────────────────────────────────

/**
 * Find an admin user by username.
 */
async function getAdminUser(username) {
  const db = await connect();
  const [rows] = await db.execute(
    'SELECT * FROM admin_users WHERE username = ? AND active = 1 LIMIT 1',
    [username]
  );
  return rows[0] || null;
}

/**
 * Verify username + password. Returns the user object on success, null on failure.
 */
async function verifyAdminLogin(username, password) {
  const user = await getAdminUser(username);
  if (!user) return null;
  if (!verifyPassword(password, user.password_hash)) return null;

  // Update last_login timestamp
  const db = await connect();
  await db.execute(
    'UPDATE admin_users SET last_login = NOW() WHERE id = ?',
    [user.id]
  );

  return user;
}

/**
 * Change password for an admin user.
 */
async function changeAdminPassword(userId, newPassword) {
  const db = await connect();
  const hash = hashPassword(newPassword);
  await db.execute(
    'UPDATE admin_users SET password_hash = ? WHERE id = ?',
    [hash, userId]
  );
  return true;
}

/**
 * Get all admin users (for future multi-user display).
 */
async function getAllAdminUsers() {
  const db = await connect();
  const [rows] = await db.execute(
    'SELECT id, username, display_name, role, last_login, active FROM admin_users ORDER BY username'
  );
  return rows;
}

/**
 * Add a new admin user.
 */
async function addAdminUser(username, displayName, password, role = 'agency') {
  const db = await connect();
  const hash = hashPassword(password);
  await db.execute(
    'INSERT INTO admin_users (username, display_name, password_hash, role) VALUES (?, ?, ?, ?)',
    [username, displayName, hash, role]
  );
  return true;
}

/**
 * Deactivate an admin user.
 */
async function deactivateAdminUser(userId) {
  const db = await connect();
  await db.execute('UPDATE admin_users SET active = 0 WHERE id = ?', [userId]);
  return true;
}

/**
 * Returns the first admin row (id only), or null if no admins exist.
 * Used by the bootstrap route to close itself once setup is done.
 */
async function getAnyAdminUser() {
  const db = await connect();
  const [rows] = await db.execute('SELECT id FROM admin_users LIMIT 1');
  return rows[0] || null;
}

/**
 * Find any admin row by username regardless of active status.
 * Returns only safe, non-sensitive fields (no password_hash).
 * Used by /admin/setup-status and /admin/cleanup to inspect legacy accounts.
 */
async function findAdminByUsername(username) {
  const db = await connect();
  const [rows] = await db.execute(
    'SELECT id, username, active, created_at FROM admin_users WHERE username = ? LIMIT 1',
    [username]
  );
  return rows[0] || null;
}

/**
 * Delete an admin user by username. Returns the number of rows deleted (0 or 1).
 * Used by /admin/cleanup to remove the legacy "admin" account.
 */
async function deleteAdminByUsername(username) {
  const db = await connect();
  const [result] = await db.execute(
    'DELETE FROM admin_users WHERE username = ?',
    [username]
  );
  return result.affectedRows;
}

/**
 * Check if MySQL is reachable (used for graceful fallback).
 */
async function isAvailable() {
  try {
    await connect();
    return true;
  } catch {
    return false;
  }
}

module.exports = {
  connect,
  hashPassword,
  verifyPassword,
  verifyAdminLogin,
  changeAdminPassword,
  getAllAdminUsers,
  addAdminUser,
  deactivateAdminUser,
  getAnyAdminUser,
  findAdminByUsername,
  deleteAdminByUsername,
  isAvailable
};
