import { pool } from "../config/db.js";
import { TICKET_STATUS } from "../constants/ticketStatus.js";

function buildLogDescription(updateData, previousRow) {
  const nextStatus = Number(updateData.status_id);
  const prevStatus = Number(previousRow.status_id);

  if (nextStatus === TICKET_STATUS.RESOLVED) {
    return "Ticket resolved";
  }
  if (
    nextStatus === TICKET_STATUS.IN_PROGRESS &&
    prevStatus === TICKET_STATUS.OPEN
  ) {
    return "Ticket set to in progress";
  }
  if (typeof updateData.description === "string" && updateData.description.trim()) {
    return updateData.description.trim();
  }
  return "Ticket updated";
}

async function nextTicketId(db) {
  const [rows] = await db.query(
    "SELECT COALESCE(MAX(id), 0) + 1 AS next_id FROM tickets"
  );
  return Number(rows[0]?.next_id) || 1;
}

async function nextTicketLogId(db) {
  const [rows] = await db.query(
    "SELECT COALESCE(MAX(id), 0) + 1 AS next_id FROM tickets_logs"
  );
  return Number(rows[0]?.next_id) || 1;
}

async function insertTicketLog(db, logData) {
  const logId = await nextTicketLogId(db);
  await db.query(
    `INSERT INTO tickets_logs
      (id, ticket_id, user_id, status_id, ticket_type_id, description, created_at, closed_at)
     VALUES (?, ?, ?, ?, ?, ?, NOW(), ?)`,
    [
      logId,
      logData.ticket_id,
      logData.user_id,
      logData.status_id,
      logData.ticket_type_id,
      logData.description,
      logData.closed_at ?? null,
    ]
  );
}

export async function listTickets() {
  const [rows] = await pool.query(`
    SELECT t.*,
           u.employee_name AS user_name,
           u.email_id AS user_email,
           ts.status_type,
           tt.type_name AS ticket_type_name
    FROM tickets t
    LEFT JOIN users u ON t.user_id = u.user_id
    LEFT JOIN tickets_status ts ON t.status_id = ts.status_id
    LEFT JOIN tickets_type tt ON t.ticket_type_id = tt.type_id
    ORDER BY t.created_at DESC, t.id DESC
  `);
  return rows;
}

export async function getTicketById(id) {
  const [ticketRows] = await pool.query(
    `
    SELECT t.*,
           u.employee_name AS user_name,
           u.email_id AS user_email,
           ts.status_type,
           tt.type_name AS ticket_type_name
    FROM tickets t
    LEFT JOIN users u ON t.user_id = u.user_id
    LEFT JOIN tickets_status ts ON t.status_id = ts.status_id
    LEFT JOIN tickets_type tt ON t.ticket_type_id = tt.type_id
    WHERE t.id = ?
    LIMIT 1
  `,
    [id]
  );

  if (!ticketRows.length) return null;

  const [logRows] = await pool.query(
    `
    SELECT tl.*,
           u.employee_name AS user_name,
           ts.status_type,
           tt.type_name AS ticket_type_name
    FROM tickets_logs tl
    LEFT JOIN users u ON tl.user_id = u.user_id
    LEFT JOIN tickets_status ts ON tl.status_id = ts.status_id
    LEFT JOIN tickets_type tt ON tl.ticket_type_id = tt.type_id
    WHERE tl.ticket_id = ?
    ORDER BY tl.created_at DESC, tl.id DESC
  `,
    [id]
  );

  return { ...ticketRows[0], logs: logRows };
}

export async function listTicketTypes() {
  const [rows] = await pool.query(
    "SELECT type_id, type_name FROM tickets_type ORDER BY type_name ASC"
  );
  return rows;
}

export async function listTicketStatuses() {
  const [rows] = await pool.query(
    "SELECT status_id, status_type FROM tickets_status ORDER BY status_id ASC"
  );
  return rows;
}

export async function createTicket(ticketData, connection = null) {
  const db = connection || pool;
  const { user_id, ticket_type_id, status_id, description } = ticketData;
  const ticketId = await nextTicketId(db);

  await db.query(
    `INSERT INTO tickets
      (id, user_id, ticket_type_id, status_id, description, created_at)
     VALUES (?, ?, ?, ?, ?, NOW())`,
    [ticketId, user_id, ticket_type_id, status_id, description || null]
  );

  await insertTicketLog(db, {
    ticket_id: ticketId,
    user_id,
    status_id,
    ticket_type_id,
    description: description || "Ticket created",
  });

  return ticketId;
}

export async function updateTicket(id, updateData, connection = null) {
  const db = connection || pool;
  const allowed = [
    "user_id",
    "ticket_type_id",
    "status_id",
    "description",
    "closed_at",
  ];

  const updateFields = [];
  const updateValues = [];

  for (const key of allowed) {
    if (updateData[key] !== undefined) {
      updateFields.push(`${key} = ?`);
      updateValues.push(updateData[key]);
    }
  }

  if (!updateFields.length) return true;

  const [beforeRows] = await db.query(
    "SELECT * FROM tickets WHERE id = ? LIMIT 1",
    [id]
  );
  if (!beforeRows.length) return false;
  const previousRow = beforeRows[0];

  updateValues.push(id);
  const [result] = await db.query(
    `UPDATE tickets SET ${updateFields.join(", ")} WHERE id = ?`,
    updateValues
  );

  if (result.affectedRows === 0) return false;

  const [ticket] = await db.query("SELECT * FROM tickets WHERE id = ? LIMIT 1", [
    id,
  ]);

  if (ticket.length) {
    const row = ticket[0];
    await insertTicketLog(db, {
      ticket_id: id,
      user_id: row.user_id,
      status_id: row.status_id,
      ticket_type_id: row.ticket_type_id,
      description: buildLogDescription(updateData, previousRow),
      closed_at: row.closed_at || null,
    });
  }

  return true;
}

export async function deleteTicket(id, connection = null) {
  const db = connection || pool;
  await db.query("DELETE FROM tickets_logs WHERE ticket_id = ?", [id]);
  const [result] = await db.query("DELETE FROM tickets WHERE id = ?", [id]);
  return result.affectedRows > 0;
}

export async function listTicketLogs({ limit = 100, ticketId, statusId } = {}) {
  const safeLimit = Math.min(Math.max(Number(limit) || 100, 1), 500);
  const whereParts = [];
  const values = [];

  if (ticketId != null && ticketId !== "") {
    whereParts.push("tl.ticket_id = ?");
    values.push(Number(ticketId));
  }
  if (statusId != null && statusId !== "") {
    whereParts.push("tl.status_id = ?");
    values.push(Number(statusId));
  }

  const whereClause = whereParts.length
    ? `WHERE ${whereParts.join(" AND ")}`
    : "";

  const [rows] = await pool.query(
    `
    SELECT tl.*,
           u.employee_name AS user_name,
           ts.status_type,
           tt.type_name AS ticket_type_name,
           t.description AS ticket_description
    FROM tickets_logs tl
    LEFT JOIN users u ON tl.user_id = u.user_id
    LEFT JOIN tickets_status ts ON tl.status_id = ts.status_id
    LEFT JOIN tickets_type tt ON tl.ticket_type_id = tt.type_id
    LEFT JOIN tickets t ON tl.ticket_id = t.id
    ${whereClause}
    ORDER BY tl.created_at DESC, tl.id DESC
    LIMIT ?
  `,
    [...values, safeLimit]
  );
  return rows;
}
