const pool = require("../config/db");

/**
 * Get all external auditors, optionally filtered by search query (hot search).
 * @param {string} [q] - Search query (matches full_name, email, organization)
 */
const getExternalAuditors = async (q = '') => {
  const search = typeof q === 'string' ? q.trim() : '';
  if (!search) {
    const [rows] = await pool.query(
      'SELECT external_auditor_id, full_name, email, organization, created_at FROM sat_external_auditors ORDER BY full_name'
    );
    return rows;
  }
  const pattern = `%${search}%`;
  const [rows] = await pool.query(
    `SELECT external_auditor_id, full_name, email, organization, created_at 
     FROM sat_external_auditors 
     WHERE full_name LIKE ? OR email LIKE ? OR organization LIKE ?
     ORDER BY full_name`,
    [pattern, pattern, pattern]
  );
  return rows;
};

const getExternalAuditorById = async (id) => {
  const [rows] = await pool.query(
    'SELECT external_auditor_id, full_name, email, organization, created_at FROM sat_external_auditors WHERE external_auditor_id = ?',
    [id]
  );
  return rows[0] || null;
};

const createExternalAuditor = async (data) => {
  const { full_name, email, organization } = data;
  const [maxRow] = await pool.query(
    'SELECT COALESCE(MAX(external_auditor_id), 0) + 1 AS next_id FROM sat_external_auditors'
  );
  const nextId = maxRow[0].next_id;
  await pool.query(
    'INSERT INTO sat_external_auditors (external_auditor_id, full_name, email, organization) VALUES (?, ?, ?, ?)',
    [
      nextId,
      String(full_name || '').trim(),
      email ? String(email).trim() || null : null,
      organization ? String(organization).trim() || null : null
    ]
  );
  return nextId;
};

module.exports = {
  getExternalAuditors,
  getExternalAuditorById,
  createExternalAuditor
};
