/**
 * Centralized audit-log snapshot helpers.
 *
 * Each helper reads the CURRENT (prior) state of a row and writes a denormalized
 * snapshot into the matching `*_logs` table. Always call BEFORE the domain
 * UPDATE/DELETE runs, and pass the same transaction connection so the log and
 * the mutation commit (or roll back) together.
 *
 * Pass `conn` as a pool connection inside a transaction, or the pool itself.
 */

/**
 * Snapshot a user's prior state into `users_logs`.
 * @param {import("mysql2/promise").PoolConnection} conn
 * @param {number} userId
 * @param {"UPDATE"|"DELETE"} action
 */
export async function snapshotUserLog(conn, userId, action) {
  const id = Number(userId);
  if (!Number.isInteger(id) || id <= 0) return;

  const [rows] = await conn.query(
    `SELECT u.user_id, u.employee_id, u.employee_name, u.phonenumber, u.photo, u.password,
        ast.status_name AS status,
        dep.department_name AS department,
        loc.location_name AS location,
        (SELECT GROUP_CONCAT(r.role_name SEPARATOR ', ')
           FROM user_role ur
           JOIN roles r ON r.role_id = ur.role_id
          WHERE ur.user_id = u.user_id) AS role
       FROM users u
       LEFT JOIN account_status ast ON ast.status_id = u.status_id
       LEFT JOIN departments dep ON dep.department_id = u.department_id
       LEFT JOIN locations loc ON loc.location_id = u.location_id
      WHERE u.user_id = ? LIMIT 1`,
    [id]
  );
  if (!rows.length) return;
  const r = rows[0];

  await conn.query(
    `INSERT INTO users_logs
       (user_id, employee_id, role, status, employee_name, phonenumber,
        department, location, photo, password, action, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())`,
    [
      r.user_id,
      r.employee_id ?? null,
      r.role ?? null,
      r.status ?? null,
      r.employee_name ?? null,
      r.phonenumber ?? null,
      r.department ?? null,
      r.location ?? null,
      r.photo ?? null,
      r.password ?? null,
      action
    ]
  );
}

/**
 * Snapshot a ticket's prior state into `tickets_logs`.
 * @param {import("mysql2/promise").PoolConnection} conn
 * @param {number} ticketId
 */
export async function snapshotTicketLog(conn, ticketId) {
  const id = Number(ticketId);
  if (!Number.isInteger(id) || id <= 0) return;

  const [rows] = await conn.query(
    `SELECT id, user_id, status_id, ticket_type_id, description, created_at, closed_at
       FROM tickets WHERE id = ? LIMIT 1`,
    [id]
  );
  if (!rows.length) return;
  const r = rows[0];

  await conn.query(
    `INSERT INTO tickets_logs
       (ticket_id, user_id, status_id, ticket_type_id, description, created_at, closed_at)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [
      r.id,
      r.user_id ?? null,
      r.status_id ?? null,
      r.ticket_type_id ?? null,
      r.description ?? null,
      r.created_at ?? null,
      r.closed_at ?? null
    ]
  );
}

/**
 * Snapshot a PTW form's prior state into `ptw_form_logs`.
 * @param {import("mysql2/promise").PoolConnection} conn
 * @param {number} formId
 * @param {number|null} actorUserId who performed the action (from JWT)
 * @param {"UPDATE"|"DELETE"} action
 */
export async function snapshotFormLog(conn, formId, actorUserId, action) {
  const id = Number(formId);
  if (!Number.isInteger(id) || id <= 0) return;

  const [rows] = await conn.query(
    `SELECT f.form_id, f.form_parent_id, f.title, f.description, f.created_by, f.created_date,
        ft.form_type_name, jt.job_type_name,
        (SELECT GROUP_CONCAT(DISTINCT loc.location_name SEPARATOR ', ')
           FROM ptw_location_forms_details lfd
           JOIN locations loc ON loc.location_id = lfd.location_id
          WHERE lfd.form_id = f.form_id) AS location_name
       FROM ptw_form_details f
       LEFT JOIN ptw_form_type ft ON ft.form_type_id = f.form_type_id
       LEFT JOIN ptw_job_type jt ON jt.job_type_id = f.job_type_id
      WHERE f.form_id = ? LIMIT 1`,
    [id]
  );
  if (!rows.length) return;
  const r = rows[0];

  await conn.query(
    `INSERT INTO ptw_form_logs
       (user_id, form_id, form_parent_id, title, description, form_type_name,
        job_type_name, created_by, created_date, location_name, action, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())`,
    [
      actorUserId ?? null,
      r.form_id,
      r.form_parent_id ?? null,
      r.title ?? null,
      r.description ?? null,
      r.form_type_name ?? null,
      r.job_type_name ?? null,
      r.created_by ?? null,
      r.created_date ?? null,
      r.location_name ?? null,
      action
    ]
  );
}

/**
 * Snapshot a PTW section's prior state into `ptw_section_logs`.
 * @param {import("mysql2/promise").PoolConnection} conn
 * @param {number} sectionId
 * @param {number|null} actorUserId who performed the action (from JWT)
 * @param {"UPDATE"|"DELETE"} action
 */
export async function snapshotSectionLog(conn, sectionId, actorUserId, action) {
  const id = Number(sectionId);
  if (!Number.isInteger(id) || id <= 0) return;

  const [rows] = await conn.query(
    `SELECT s.section_id, s.section_parent_id, s.title, s.created_by, s.created_date,
        (SELECT GROUP_CONCAT(DISTINCT loc.location_name SEPARATOR ', ')
           FROM ptw_form_section_map fsm
           JOIN ptw_location_forms_details lfd ON lfd.form_id = fsm.form_id
           JOIN locations loc ON loc.location_id = lfd.location_id
          WHERE fsm.section_id = s.section_id) AS location_name
       FROM ptw_section_details s
      WHERE s.section_id = ? LIMIT 1`,
    [id]
  );
  if (!rows.length) return;
  const r = rows[0];

  await conn.query(
    `INSERT INTO ptw_section_logs
       (user_id, section_id, section_parent_id, title, created_by, created_date,
        location_name, action, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW())`,
    [
      actorUserId ?? null,
      r.section_id,
      r.section_parent_id ?? null,
      r.title ?? null,
      r.created_by ?? null,
      r.created_date ?? null,
      r.location_name ?? null,
      action
    ]
  );
}
