const db = require('../config/db');
const crypto = require('crypto');
const { logAuditFromReq } = require('../middlewares/audit');

exports.getAll = async (req, res, next) => {
  try {
    const { airline_id, status, search } = req.query;
    let sql = `
      SELECT tk.*, a.name AS airline_name, a.code AS airline_code,
             t.transaction_number, t.payment_method
      FROM tickets tk
      JOIN airlines a ON a.id = tk.airline_id
      JOIN transactions t ON t.id = tk.transaction_id
      WHERE 1=1
    `;
    const params = [];

    if (airline_id) { params.push(airline_id); sql += ' AND tk.airline_id = ?'; }
    if (status) { params.push(status); sql += ' AND tk.status = ?'; }
    if (search) {
      const like = `%${search}%`;
      params.push(like, like);
      sql += ' AND (tk.pnr_code LIKE ? OR tk.passenger_name LIKE ?)';
    }

    sql += ' ORDER BY tk.created_at DESC';
    const [rows] = await db.query(sql, params);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.getById = async (req, res, next) => {
  try {
    const [rows] = await db.query(
      `SELECT tk.*, a.name AS airline_name, a.code AS airline_code,
              t.transaction_number, t.payment_method
       FROM tickets tk
       JOIN airlines a ON a.id = tk.airline_id
       JOIN transactions t ON t.id = tk.transaction_id
       WHERE tk.id = ?`,
      [req.params.id]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'Ticket not found' });
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

/**
 * Create a ticket booking — creates both a transaction and a ticket row.
 */
exports.create = async (req, res, next) => {
  try {
    const {
      pnr_code, passenger_name, passenger_phone, airline_id, route, travel_date,
      destination, return_date,
      net_cost, selling_price, seat_fee,
      extra_luggage_qty, extra_luggage_cost, visa_required,
      payment_method, customer_id, notes, paid_amount,
    } = req.body;

    if (!pnr_code || !passenger_name || !airline_id || net_cost == null || selling_price == null) {
      return res.status(400).json({ error: 'pnr_code, passenger_name, airline_id, net_cost and selling_price are required' });
    }

    const conn = await db.pool.getConnection();
    try {
      await conn.beginTransaction();

      // Fetch VAT rate and default seat fee from settings
      const [settingsRows] = await conn.query(
        "SELECT `key`, `value` FROM settings WHERE `key` IN ('vat_rate', 'default_seat_fee')"
      );
      const settingsMap = {};
      for (const row of settingsRows) settingsMap[row.key] = row.value;
      const vatPercent = parseFloat(settingsMap['vat_rate'] || 5);
      const defaultSeatFee = parseFloat(settingsMap['default_seat_fee'] || 25);

      // Generate transaction number (race-safe)
      const datePart = new Date().toISOString().slice(2, 10).replace(/-/g, '');
      const rand = crypto.randomBytes(2).toString('hex').toUpperCase();
      const txnNumber = `TXN-${datePart}-${rand}`;

      const actualSeatFee = parseFloat(seat_fee != null ? seat_fee : defaultSeatFee);
      const subtotal = parseFloat(selling_price) + actualSeatFee + parseFloat(extra_luggage_cost || 0);
      const vatAmount = subtotal * (vatPercent / 100);
      const totalAmount = subtotal + vatAmount;

      // Insert transaction
      const effectivePaid = (payment_method === 'pay_later' && paid_amount != null)
        ? parseFloat(paid_amount)
        : (payment_method === 'pay_later' ? 0 : totalAmount);
      const [txnResult] = await conn.query(
        `INSERT INTO transactions (transaction_number, type, payment_method, subtotal, vat_percent, vat_amount, total_amount, paid_amount, customer_id, user_id, notes)
         VALUES (?, 'ticket', ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [txnNumber, payment_method || 'cash', subtotal, vatPercent, vatAmount, totalAmount, effectivePaid, customer_id || null, req.user.id, notes || null]
      );
      const txnId = txnResult.insertId;
      const [txnRows] = await conn.query('SELECT * FROM transactions WHERE id = ?', [txnId]);

      // Insert ticket
      const [ticketResult] = await conn.query(
        `INSERT INTO tickets (transaction_id, pnr_code, passenger_name, passenger_phone, airline_id, route, travel_date, destination, return_date,
           net_cost, selling_price, seat_fee, extra_luggage_qty, extra_luggage_cost, visa_required)
         VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
        [
          txnId, pnr_code, passenger_name, passenger_phone || null, airline_id, route || null, travel_date || null,
          destination || null, return_date || null,
          net_cost, selling_price, actualSeatFee, extra_luggage_qty || 0,
          extra_luggage_cost || 0, visa_required ? 1 : 0,
        ]
      );
      const [ticketRows] = await conn.query('SELECT * FROM tickets WHERE id = ?', [ticketResult.insertId]);

      // Update customer balance: only the unpaid portion goes to ledger
      if (customer_id && (payment_method === 'credit' || payment_method === 'pay_later')) {
        const dueAmount = totalAmount - effectivePaid;
        if (dueAmount > 0) {
          await conn.query(
            'UPDATE customers SET ledger_balance = ledger_balance + ?, updated_at = NOW() WHERE id = ?',
            [dueAmount, customer_id]
          );
        }
      }

      await conn.commit();

      logAuditFromReq(req, 'create', 'ticket', ticketResult.insertId, {
        pnr_code, passenger_name, airline_id,
        total_amount: totalAmount, transaction_number: txnNumber,
      });

      res.status(201).json({ transaction: txnRows[0], ticket: ticketRows[0] });
    } catch (e) {
      await conn.rollback();
      throw e;
    } finally {
      conn.release();
    }
  } catch (err) {
    next(err);
  }
};

exports.update = async (req, res, next) => {
  try {
    const { status, route, destination, return_date, extra_luggage_qty, extra_luggage_cost } = req.body;
    const [result] = await db.query(
      `UPDATE tickets SET
         status             = COALESCE(?, status),
         route              = COALESCE(?, route),
         destination        = COALESCE(?, destination),
         return_date        = COALESCE(?, return_date),
         extra_luggage_qty  = COALESCE(?, extra_luggage_qty),
         extra_luggage_cost = COALESCE(?, extra_luggage_cost),
         updated_at         = NOW()
       WHERE id = ?`,
      [status, route, destination, return_date, extra_luggage_qty, extra_luggage_cost, req.params.id]
    );
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Ticket not found' });
    const [rows] = await db.query('SELECT * FROM tickets WHERE id = ?', [req.params.id]);

    logAuditFromReq(req, 'update', 'ticket', parseInt(req.params.id, 10), {
      fields_updated: Object.keys(req.body),
    });

    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.remove = async (req, res, next) => {
  const conn = await db.pool.getConnection();
  try {
    await conn.beginTransaction();

    // Fetch ticket + transaction to check customer balance reversal
    const [ticketRows] = await conn.query(
      `SELECT tk.*, t.customer_id, t.payment_method, t.total_amount
       FROM tickets tk
       JOIN transactions t ON t.id = tk.transaction_id
       WHERE tk.id = ?`,
      [req.params.id]
    );
    if (ticketRows.length === 0) {
      await conn.rollback();
      conn.release();
      return res.status(404).json({ error: 'Ticket not found' });
    }

    const ticket = ticketRows[0];
    if (ticket.status === 'cancelled') {
      await conn.rollback();
      conn.release();
      return res.status(400).json({ error: 'Ticket is already cancelled' });
    }

    // Cancel the ticket
    await conn.query(
      "UPDATE tickets SET status = 'cancelled', updated_at = NOW() WHERE id = ?",
      [req.params.id]
    );

    // Reverse customer balance if payment was on credit
    if (ticket.customer_id && (ticket.payment_method === 'credit' || ticket.payment_method === 'pay_later')) {
      await conn.query(
        'UPDATE customers SET ledger_balance = ledger_balance - ?, updated_at = NOW() WHERE id = ?',
        [ticket.total_amount, ticket.customer_id]
      );
    }

    await conn.commit();

    logAuditFromReq(req, 'cancel', 'ticket', parseInt(req.params.id, 10), {
      reversed_amount: ticket.total_amount,
      customer_id: ticket.customer_id,
    });

    res.json({ message: 'Ticket cancelled' });
  } catch (err) {
    await conn.rollback();
    next(err);
  } finally {
    conn.release();
  }
};
