import { pool } from "../config/db.js";
import { ApiError } from "../utils/ApiError.js";
import { snapshotTicketLog } from "../utils/auditLogs.js";

const TICKET_COLUMNS = [
  "user_id",
  "ticket_type_id",
  "status_id",
  "description",
  "created_at",
  "closed_at"
];

function toPositiveInt(value, fieldName) {
  const n = Number(value);
  if (!Number.isInteger(n) || n <= 0) {
    throw new ApiError(400, `${fieldName} must be a positive integer`);
  }
  return n;
}

function pickColumns(body) {
  const out = {};
  for (const col of TICKET_COLUMNS) {
    if (Object.prototype.hasOwnProperty.call(body, col) && body[col] !== undefined) {
      out[col] = body[col];
    }
  }
  return out;
}

export async function listTickets(req, res, next) {
  try {
    const filters = req.query || {};
    const whereParts = [];
    const values = [];
    Object.entries(filters).forEach(([key, value]) => {
      if (value === undefined || value === null || value === "") return;
      if (key.endsWith("_like")) {
        whereParts.push(`\`${key.slice(0, -5)}\` LIKE ?`);
        values.push(`%${value}%`);
      } else {
        whereParts.push(`\`${key}\` = ?`);
        values.push(value);
      }
    });
    let sql = "SELECT * FROM tickets";
    if (whereParts.length) sql += ` WHERE ${whereParts.join(" AND ")}`;
    sql += " ORDER BY id DESC";
    const [rows] = await pool.query(sql, values);
    res.json(rows);
  } catch (err) {
    next(err);
  }
}

export async function getTicket(req, res, next) {
  try {
    const id = toPositiveInt(req.params.id, "id");
    const [rows] = await pool.query("SELECT * FROM tickets WHERE id = ? LIMIT 1", [id]);
    if (!rows.length) throw new ApiError(404, "tickets record not found");
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
}

export async function createTicket(req, res, next) {
  try {
    const body = pickColumns(req.body || {});
    const columns = Object.keys(body);
    if (!columns.length) throw new ApiError(400, "Body cannot be empty");
    const placeholders = columns.map(() => "?").join(",");
    const [result] = await pool.query(
      `INSERT INTO tickets (${columns.map((c) => `\`${c}\``).join(",")}) VALUES (${placeholders})`,
      columns.map((c) => body[c])
    );
    res.status(201).json({
      insertedId: result.insertId != null ? Number(result.insertId) : null,
      affectedRows: result.affectedRows
    });
  } catch (err) {
    next(err);
  }
}

export async function updateTicket(req, res, next) {
  const conn = await pool.getConnection();
  try {
    const id = toPositiveInt(req.params.id, "id");
    const body = pickColumns(req.body || {});
    const columns = Object.keys(body);
    if (!columns.length) throw new ApiError(400, "Body cannot be empty");

    await conn.beginTransaction();
    // Capture prior state (e.g. before a status transition) before mutating.
    await snapshotTicketLog(conn, id);

    const setClause = columns.map((c) => `\`${c}\` = ?`).join(", ");
    const [result] = await conn.query(
      `UPDATE tickets SET ${setClause} WHERE id = ?`,
      [...columns.map((c) => body[c]), id]
    );

    await conn.commit();
    res.json({ affectedRows: result.affectedRows });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}

export async function deleteTicket(req, res, next) {
  const conn = await pool.getConnection();
  try {
    const id = toPositiveInt(req.params.id, "id");
    await conn.beginTransaction();
    await snapshotTicketLog(conn, id);
    const [result] = await conn.query("DELETE FROM tickets WHERE id = ?", [id]);
    await conn.commit();
    res.json({ affectedRows: result.affectedRows });
  } catch (err) {
    await conn.rollback().catch(() => {});
    next(err);
  } finally {
    conn.release();
  }
}
