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

const getAllTickets = async () => {
  const [rows] = await pool.query(`
    SELECT t.*, 
           u.employee_name as user_name,
           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
  `);
  return rows;
};

const getTicketById = async (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 = ?
  `, [id]);
  
  if (ticketRows.length === 0) return null;
  
  const [logRows] = await pool.query(`
    SELECT tl.*, u.employee_name as user_name
    FROM tickets_logs tl
    LEFT JOIN users u ON tl.user_id = u.user_id
    WHERE tl.ticket_id = ?
    ORDER BY tl.created_at DESC
  `, [id]);
  
  return {
    ...ticketRows[0],
    logs: logRows
  };
};

const createTicket = async (ticketData, connection = null) => {
  const db = connection || pool;
  const { user_id, ticket_type_id, status_id, description, subject, recipient_email } = ticketData;
  const hasSubject = typeof subject !== 'undefined';
  const hasRecipient = typeof recipient_email !== 'undefined';
  const columns = ['user_id', 'ticket_type_id', 'status_id', 'description', 'created_at'];
  const placeholders = ['?', '?', '?', '?', 'NOW()'];
  const values = [user_id, ticket_type_id, status_id, description || null];
  if (hasSubject) { columns.push('subject'); placeholders.push('?'); values.push(subject || null); }
  if (hasRecipient) { columns.push('recipient_email'); placeholders.push('?'); values.push(recipient_email || null); }
  const [result] = await db.query(
    `INSERT INTO tickets (${columns.join(', ')}) VALUES (${placeholders.join(', ')})`,
    values
  );
  const ticketId = result.insertId;
  await db.query(
    'INSERT INTO tickets_logs (ticket_id, user_id, status_id, ticket_type_id, description, created_at) VALUES (?, ?, ?, ?, ?, NOW())',
    [ticketId, user_id, status_id, ticket_type_id, description || 'Ticket created']
  );
  return ticketId;
};

const updateTicket = async (id, updateData, connection = null) => {
  const db = connection || pool;
  const { user_id, ticket_type_id, status_id, description, closed_at, subject, recipient_email } = updateData;
  const updateFields = [];
  const updateValues = [];
  if (user_id !== undefined) { updateFields.push('user_id = ?'); updateValues.push(user_id); }
  if (ticket_type_id !== undefined) { updateFields.push('ticket_type_id = ?'); updateValues.push(ticket_type_id); }
  if (status_id !== undefined) { updateFields.push('status_id = ?'); updateValues.push(status_id); }
  if (description !== undefined) { updateFields.push('description = ?'); updateValues.push(description); }
  if (closed_at !== undefined) { updateFields.push('closed_at = ?'); updateValues.push(closed_at); }
  if (subject !== undefined) { updateFields.push('subject = ?'); updateValues.push(subject); }
  if (recipient_email !== undefined) { updateFields.push('recipient_email = ?'); updateValues.push(recipient_email); }
  
  if (updateFields.length > 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 = ?', [id]);
    if (ticket.length > 0) {
      await db.query(
        'INSERT INTO tickets_logs (ticket_id, user_id, status_id, ticket_type_id, description, created_at, closed_at) VALUES (?, ?, ?, ?, ?, NOW(), ?)',
        [id, ticket[0].user_id, ticket[0].status_id, ticket[0].ticket_type_id, description || 'Ticket updated', closed_at || null]
      );
    }
  }
  
  return true;
};

const deleteTicket = async (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;
};

module.exports = {
  getAllTickets,
  getTicketById,
  createTicket,
  updateTicket,
  deleteTicket
};

