const mysql = require('mysql2');
const pool = require('../config/db');
const { buildAuditFilter } = require('../utils/auditFilterHelper');

/**
 * Column on sat_audit_logs that stores the action string (e.g. CREATED).
 * Historically some DBs had a typo column "starus". Default: status.
 * Set SAT_AUDIT_LOG_ACTION_COLUMN=starus only if your table still uses the typo name.
 */
const AUDIT_LOG_ACTION_COLUMN = process.env.SAT_AUDIT_LOG_ACTION_COLUMN || 'status';

const qAl = mysql.escapeId('al');
const qActionCol = mysql.escapeId(AUDIT_LOG_ACTION_COLUMN);

/**
 * Insert one row into sat_audit_logs.
 *
 * @param {number} auditId   - audit_id (required)
 * @param {number} userId    - user_id who performed the action (required)
 * @param {string} action    - e.g. 'CREATED', 'OBSERVATION_ADDED', 'CLOSED'
 * @param {object} _options  - kept for backward‑compatibility, currently ignored
 * @param {object} connection - optional pool connection for transactions
 */
const insertAuditLog = async (auditId, userId, action, _options = {}, connection = null) => {
  const db = connection || pool;
  const col = mysql.escapeId(AUDIT_LOG_ACTION_COLUMN);

  await db.query(
    `INSERT IGNORE INTO sat_audit_logs (audit_id, user_id, ${col}, status_changed_at) VALUES (?, ?, ?, NOW())`,
    [auditId, userId, action]
  );
};

/**
 * Get audit logs.
 * Optional filters: audit_id, action (matched against action column), limit, offset.
 *
 * @param {object} options - { audit_id, action, limit, offset }
 */
const getAuditLogs = async (options = {}) => {
  const { audit_id, action, limit = 100, offset = 0 } = options;
  const limitVal = Math.min(parseInt(limit, 10) || 100, 500);
  const offsetVal = Math.max(0, parseInt(offset, 10) || 0);
  const col = mysql.escapeId(AUDIT_LOG_ACTION_COLUMN);

  let sql = `
    SELECT audit_id,
           user_id,
           ${col} AS action,
           status_changed_at
    FROM sat_audit_logs
  `;
  const params = [];

  const conditions = [];
  if (audit_id != null && audit_id !== '') {
    conditions.push('audit_id = ?');
    params.push(audit_id);
  }
  if (action) {
    conditions.push(`${col} LIKE ?`);
    params.push(`${action}%`);
  }
  if (conditions.length > 0) {
    sql += ' WHERE ' + conditions.join(' AND ');
  }
  sql += ' ORDER BY status_changed_at DESC LIMIT ? OFFSET ?';
  params.push(limitVal, offsetVal);

  const [rows] = await pool.query(sql, params);
  return rows;
};

/**
 * Get audit log joined with sat_audits (and sat_audit_status name).
 *
 * @param {object} options - { audit_id, action, limit, offset }
 */
const getAuditLogsJoined = async (options = {}) => {
  const { audit_id, action, limit = 100, offset = 0, country_id, location_id, department_id } = options;
  const limitVal = Math.min(parseInt(limit, 10) || 100, 500);
  const offsetVal = Math.max(0, parseInt(offset, 10) || 0);
  const { clause: filterClause, params: filterParams } = buildAuditFilter({ country_id, location_id, department_id });

  let sql = `
    SELECT
      a.audit_id,
      a.auditor_id,
      auditor.employee_name AS auditor_name,
      a.audit_name,
      a.location,
      l.location_name,
      a.audit_status_id,
      ast.audit_status_name,
      COALESCE(a.created_at, a.observed_at, al.status_changed_at) AS audit_created_at,
      a.observed_at,
      a.country_id,
      a.department_id,
      a.start_time,
      a.end_time,
      al.user_id,
      ${qAl}.${qActionCol} AS action,
      al.status_changed_at,
      (SELECT GROUP_CONCAT(COALESCE(u.employee_name, ea.full_name, c.co_auditor_name) SEPARATOR ', ')
       FROM sat_coauditors c
       LEFT JOIN users u ON c.co_auditor_user_id = u.user_id
       LEFT JOIN sat_external_auditors ea ON c.external_auditor_id = ea.external_auditor_id
       WHERE c.audit_id = a.audit_id) AS co_auditors_names,
      (SELECT GROUP_CONCAT(au.employee_name SEPARATOR ', ')
       FROM sat_auditees ae
       INNER JOIN users au ON ae.auditee_id = au.user_id
       WHERE ae.audit_id = a.audit_id) AS auditees_names
    FROM sat_audit_logs al
    INNER JOIN sat_audits a ON a.audit_id = al.audit_id
    LEFT JOIN sat_audit_status ast ON a.audit_status_id = ast.audit_status_id
    LEFT JOIN users auditor ON a.auditor_id = auditor.user_id
    LEFT JOIN locations l ON a.location = l.location_id
  `;
  const params = [];

  const conditions = [];
  if (audit_id != null && audit_id !== '') {
    conditions.push('al.audit_id = ?');
    params.push(audit_id);
  }
  if (action) {
    conditions.push(`${qAl}.${qActionCol} LIKE ?`);
    params.push(`${action}%`);
  }
  if (filterClause) {
    conditions.push('1=1' + filterClause);
    params.push(...filterParams);
  }
  if (conditions.length > 0) {
    sql += ' WHERE ' + conditions.join(' AND ');
  }
  sql += ' ORDER BY al.status_changed_at DESC LIMIT ? OFFSET ?';
  params.push(limitVal, offsetVal);

  const [rows] = await pool.query(sql, params);
  return rows;
};

module.exports = {
  insertAuditLog,
  getAuditLogs,
  getAuditLogsJoined
};
