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

const crypto = require('crypto');

/**
 * Helper: generate unique transaction number (race-safe).
 * Uses timestamp + 4 random hex chars to avoid collision.
 */
function generateTxnNumber() {
  const now = new Date();
  const datePart = now.toISOString().slice(2, 10).replace(/-/g, ''); // YYMMDD
  const rand = crypto.randomBytes(2).toString('hex').toUpperCase(); // 4 hex chars
  return `TXN-${datePart}-${rand}`;
}

exports.getAll = async (req, res, next) => {
  try {
    const { type, payment_method, customer_id, from_date, to_date } = req.query;
      let sql = `
      SELECT t.*, u.full_name AS user_name, CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
             COALESCE(cost.total_cost, 0) AS total_cost,
             COALESCE(t.is_deleted, 0) AS is_deleted, t.deleted_at,
             del_u.full_name AS deleted_by
      FROM transactions t
      LEFT JOIN users u ON u.id = t.user_id
      LEFT JOIN customers c ON c.id = t.customer_id
      LEFT JOIN users del_u ON del_u.id = t.deleted_by
      LEFT JOIN (
        SELECT ti.transaction_id, SUM(ti.line_cost) AS total_cost
        FROM transaction_items ti
        GROUP BY ti.transaction_id
      ) cost ON cost.transaction_id = t.id
      WHERE 1=1
    `;
    const params = [];

    if (type) { params.push(type); sql += ' AND t.type = ?'; }
    if (payment_method) { params.push(payment_method); sql += ' AND t.payment_method = ?'; }
    if (customer_id) { params.push(customer_id); sql += ' AND t.customer_id = ?'; }
    if (from_date) { params.push(from_date); sql += ' AND t.created_at >= ?'; }
    if (to_date) { params.push(to_date); sql += ' AND t.created_at <= DATE_ADD(?, INTERVAL 1 DAY)'; }

    // By default, exclude soft-deleted transactions.
    // When show_deleted=1, include them so the ledger can show them greyed-out.
    const showDeleted = req.query.show_deleted === '1';
    if (!showDeleted) {
      sql += ' AND COALESCE(t.is_deleted, 0) = 0';
    }

    sql += ' ORDER BY t.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 [txnRows] = await db.query(
      `SELECT t.*, u.full_name AS user_name, CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
               COALESCE(t.is_deleted, 0) AS is_deleted, t.deleted_at, t.deleted_by
       FROM transactions t
       LEFT JOIN users u ON u.id = t.user_id
       LEFT JOIN customers c ON c.id = t.customer_id
       WHERE t.id = ?`,
      [req.params.id]
    );
    if (txnRows.length === 0) return res.status(404).json({ error: 'Transaction not found' });

    const [items] = await db.query(
      `SELECT ti.*, COALESCE(p.name, ti.item_name) AS item_name, p.sku
       FROM transaction_items ti
       LEFT JOIN products p ON p.id = ti.product_id
       WHERE ti.transaction_id = ?`,
      [req.params.id]
    );

    const [tickets] = await db.query(
      `SELECT tk.*, a.name AS airline_name, a.code AS airline_code
       FROM tickets tk
       LEFT JOIN airlines a ON a.id = tk.airline_id
       WHERE tk.transaction_id = ?`,
      [req.params.id]
    );

    res.json({ ...txnRows[0], items, tickets });
  } catch (err) {
    next(err);
  }
};

/**
 * Create a full transaction (sale) with line items.
 * Body: { payment_method, customer_id?, vat_percent?, items: [{ product_id, quantity }], notes? }
 */
exports.create = async (req, res, next) => {
  try {
    const { payment_method, customer_id, vat_percent, items, notes, paid_amount, discount_type, discount_value } = req.body;
    if (!items || items.length === 0) {
      return res.status(400).json({ error: 'At least one item is required' });
    }

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

      const txnNumber = generateTxnNumber();

      // Fetch VAT rate from settings if not provided
      let vat = vat_percent;
      if (vat == null) {
        const [settingsRows] = await conn.query(
          "SELECT `value` FROM settings WHERE `key` = 'vat_rate'"
        );
        vat = settingsRows.length > 0 ? parseFloat(settingsRows[0].value) : 5;
      }

      // Fetch product prices and validate stock
      let subtotal = 0;
      let totalCost = 0;
      const resolvedItems = [];
      for (const item of items) {
        const isManualItem = item.product_id == null;
        if (isManualItem) {
          const itemName = `${item.item_name ?? item.name ?? ''}`.trim();
          const unitPrice = Number(item.unit_price ?? item.selling_price ?? 0);
          const costPrice = Number(item.cost_price ?? 0);
          const quantity = Number(item.quantity ?? 1);
          if (!itemName) throw Object.assign(new Error('Manual sale item name is required'), { status: 400 });
          if (quantity <= 0) throw Object.assign(new Error(`Invalid quantity for ${itemName}`), { status: 400 });
          if (unitPrice <= 0) throw Object.assign(new Error(`Selling price is required for ${itemName}`), { status: 400 });
          const lineTotal = unitPrice * quantity;
          const lineCost = costPrice * quantity;
          subtotal += parseFloat(lineTotal);
          totalCost += parseFloat(lineCost);
          resolvedItems.push({
            ...item,
            item_name: itemName,
            unit_price: unitPrice,
            cost_price: costPrice,
            line_total: lineTotal,
            line_cost: lineCost,
          });
          continue;
        }

        const [rows] = await conn.query(
          'SELECT id, selling_price, cost_price, stock_level, name FROM products WHERE id = ?',
          [item.product_id]
        );
        if (rows.length === 0) throw Object.assign(new Error(`Product ${item.product_id} not found`), { status: 400 });
        const product = rows[0];
        if (product.stock_level < item.quantity) {
          throw Object.assign(new Error(`Insufficient stock for ${product.name}`), { status: 400 });
        }
        const lineTotal = product.selling_price * item.quantity;
        const lineCost = (product.cost_price || 0) * item.quantity;
        subtotal += parseFloat(lineTotal);
        totalCost += parseFloat(lineCost);
        resolvedItems.push({ ...item, item_name: product.name, unit_price: product.selling_price, cost_price: product.cost_price || 0, line_total: lineTotal, line_cost: lineCost });
      }

      // Calculate discount amount
      let discountAmount = 0;
      if (discount_type && discount_value && discount_value > 0) {
        if (discount_type === 'percentage') {
          discountAmount = subtotal * (discount_value / 100);
        } else if (discount_type === 'fixed_amount') {
          discountAmount = parseFloat(discount_value);
          // Ensure discount doesn't exceed subtotal
          if (discountAmount > subtotal) {
            discountAmount = subtotal;
          }
        }
      }

      // Calculate final amounts: (subtotal - discount) + vat
      const discountedSubtotal = subtotal - discountAmount;
      const vatAmount = discountedSubtotal * (vat / 100);
      const totalAmount = discountedSubtotal + 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, discount_type, discount_value, discount_amount, vat_percent, vat_amount, total_amount, paid_amount, customer_id, user_id, notes)
         VALUES (?, 'sale', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [txnNumber, payment_method || 'cash', subtotal, discount_type || null, discount_value || 0, discountAmount, vat, vatAmount, totalAmount, effectivePaid, customer_id || null, req.user.id, notes || null]
      );
      const txnId = txnResult.insertId;

      // Insert line items, update stock
      for (const item of resolvedItems) {
        await conn.query(
          `INSERT INTO transaction_items (transaction_id, product_id, item_name, quantity, unit_price, cost_price, line_cost, line_total)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
            [txnId, item.product_id ?? null, item.item_name ?? item.name ?? null, item.quantity, item.unit_price, item.cost_price || 0, item.line_cost || 0, item.line_total]
        );
          if (item.product_id != null) {
            await conn.query(
              'UPDATE products SET stock_level = stock_level - ?, updated_at = NOW() WHERE id = ?',
              [item.quantity, item.product_id]
            );
            await conn.query(
              `INSERT INTO stock_movements (product_id, quantity_change, reason, reference_id, user_id)
               VALUES (?, ?, 'sale', ?, ?)`,
              [item.product_id, -item.quantity, txnId, req.user.id]
            );
          }
      }

      // Auto-update low stock statuses
      for (const item of resolvedItems) {
        if (item.product_id != null) {
          await conn.query(
            `UPDATE products SET status = CASE
               WHEN stock_level <= low_stock_threshold THEN 'low_stock' ELSE 'active'
             END WHERE id = ?`,
            [item.product_id]
          );
        }
      }

      // 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]
          );
        }
      }

      const [txnRows] = await conn.query('SELECT * FROM transactions WHERE id = ?', [txnId]);
      await conn.commit();

      // Audit log the sale
      logAuditFromReq(req, 'create', 'transaction', txnId, {
        transaction_number: txnNumber,
        type: 'sale',
        payment_method: payment_method || 'cash',
        total_amount: totalAmount,
        discount_amount: discountAmount,
        item_count: resolvedItems.length,
        customer_id: customer_id || null,
      });

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

exports.remove = async (req, res, next) => {
  try {
    const txnId = parseInt(req.params.id, 10);

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

      // Fetch the transaction
      const [txnRows] = await conn.query('SELECT * FROM transactions WHERE id = ?', [txnId]);
      if (txnRows.length === 0) {
        conn.release();
        return res.status(404).json({ error: 'Transaction not found' });
      }
      const txn = txnRows[0];

      // Fetch line items to reverse stock
      const [items] = await conn.query(
        'SELECT product_id, quantity FROM transaction_items WHERE transaction_id = ?',
        [txnId]
      );

      // Reverse stock for each item
      for (const item of items) {
        if (item.product_id != null) {
          await conn.query(
            'UPDATE products SET stock_level = stock_level + ?, updated_at = NOW() WHERE id = ?',
            [item.quantity, item.product_id]
          );
          await conn.query(
            `INSERT INTO stock_movements (product_id, quantity_change, reason, reference_id, user_id)
             VALUES (?, ?, 'return', ?, ?)`,
            [item.product_id, item.quantity, txnId, req.user.id]
          );
          // Re-evaluate stock status
          await conn.query(
            `UPDATE products SET status = CASE
               WHEN stock_level <= low_stock_threshold THEN 'low_stock' ELSE 'active'
             END WHERE id = ?`,
            [item.product_id]
          );
        }
      }

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

      // Soft-delete: mark transaction as deleted and record who deleted it.
      await conn.query(
        'UPDATE transactions SET is_deleted = 1, deleted_at = NOW(), deleted_by = ? WHERE id = ?',
        [req.user.id, txnId]
      );

      await conn.commit();

      // Audit log the soft-delete and reversal
      logAuditFromReq(req, 'delete', 'transaction', txnId, {
        transaction_number: txn.transaction_number,
        reversed_items: items.length,
        reversed_amount: parseFloat(txn.total_amount),
        soft_delete: true,
      });

      res.json({ message: 'Transaction marked deleted (soft-delete). Stock and balances reversed' });
    } catch (e) {
      await conn.rollback();
      throw e;
    } finally {
      conn.release();
    }
  } catch (err) {
    next(err);
  }
};
