const saleModel = require('../models/saleModel');
const { autoUpdateCustomerTotals } = require('../helpers/customerHelper');
const db = require('../db/config');
const ExcelJS = require('exceljs');
const fs = require('fs');
const path = require('path');
const PDFDocument = require("pdfkit-table");

exports.createMultiItemSale = async (req, res) => {
  const {
    customer_id,
    bank_id,
    items,
    sale_date,
    status,
    reference,
    note,
    discount = 0,
    other_charges = 0,
    paid_amount = 0,
    payment_status
  } = req.body;

  const conn = await db.getConnection();

  try {
    await conn.query('SET innodb_lock_wait_timeout = 5');
    await conn.beginTransaction();

    const itemTotal = items.reduce((sum, item) => sum + Number(item.total), 0);
    const total = itemTotal + Number(other_charges) - Number(discount);
    const saleStatus = status || 'final';
    const paymentStatusFinal = payment_status || (paid_amount >= total ? 'paid' : paid_amount > 0 ? 'partial' : 'unpaid');
    const saleDate = sale_date || new Date();

    const [result] = await saleModel.insertSale(
      conn,
      Number(customer_id),
      saleDate,
      Number(bank_id),
      Number(total),
      saleStatus,
      Number(paid_amount),
      paymentStatusFinal,
      reference,
      note,
      Number(discount),
      Number(other_charges),
      req.user?.id || 0
    );

    const sale_id = result.insertId;
    await saleModel.insertSaleItems(conn, sale_id, items, saleStatus);

    if (saleStatus === 'final') {
      await saleModel.insertLedgerAndTransaction(conn, sale_id, bank_id, paid_amount);
    }

    await conn.commit();
    await autoUpdateCustomerTotals(sale_id);
    res.json({ success: true, sale_id });

  } catch (err) {
    await conn.rollback();
    console.error("Sale Create Error:", err);
    res.status(500).json({ error: err.message });
  } finally {
    conn.release();
  }
};


exports.getSales = async (req, res) => {
  const sales = await saleModel.getAllSales();
  res.json(sales);
};
exports.getFilteredSales = async (req, res) => {
  try {
    const {
      start_date,
      end_date,
      payment_status,
      status,
      customer_id
    } = req.query;

    const filters = {
      start_date,
      end_date,
      payment_status,
      status,
      customer_id
    };

    const [rows] = await saleModel.getFilteredSales(filters);
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

exports.getSaleById = async (req, res) => {
  try {
    const [sale] = await saleModel.getById(req.params.id);
    const [items] = await saleModel.getItemsBySaleId(req.params.id);
    res.json({ ...sale[0], items });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

exports.updateSale = async (req, res) => {
  const {
    customer_id,
    bank_id,
    items,
    sale_date,
    status,
    reference,
    note,
    discount = 0,
    discount_type = 'percent',
    other_charges = 0,
    paid_amount = 0
  } = req.body;

  const conn = await db.getConnection();

  try {
    await conn.query('SET innodb_lock_wait_timeout = 5');
    await conn.beginTransaction();

    const sale_id = Number(req.params.id);
    if (isNaN(sale_id)) {
      return res.status(400).json({ error: 'Invalid sale ID' });
    }
    const referenceCode = `SALE-${sale_id}`;

    const [[oldSale]] = await saleModel.getSaleById(conn, sale_id);
    const [oldItems] = await saleModel.getOldSaleItems(conn, sale_id);

    const saleStatus = status || 'final';

    if (oldSale.status === 'final') {
      
      for (const item of oldItems) {
        console.log(item)
        await saleModel.restoreItemSize(conn, item.quantity, item.item_id, item.size);
      }
      await saleModel.clearLedger(conn, referenceCode);
    }

    const itemTotal = items.reduce((sum, item) => sum + Number(item.total), 0);
    const total = itemTotal + Number(other_charges) - Number(discount);

    await saleModel.updateSaleInfo(conn, {
      sale_id,
      customer_id,
      sale_date,
      bank_id,
      total,
      status: saleStatus,
      reference,
      note,
      discount,
      discount_type,
      other_charges,
      modified_by: req.user?.id || 0
    });

    await saleModel.clearSaleItems(conn, sale_id);

    for (const item of items) {
      const sizes = item.sizes || [{ size: '', quantity: item.quantity }];

      for (const s of sizes) {
        const qty = Number(s.quantity);
        const totalLine = qty * item.price;

        await saleModel.insertSaleItem(conn, {
          sale_id,
          item_id: item.item_id,
          size: s.size,
          quantity: qty,
          price: item.price,
          discount_type: item.discount_type || discount_type,
          discount: item.discount || 0,
          total: totalLine
        });

        if (saleStatus === 'final') {
          await saleModel.reduceItemSize(conn, qty, item.item_id, s.size);
        }
      }
    }

    if (saleStatus === 'final') {
      if (paid_amount > 0) {
        await saleModel.insertBankTransaction(conn, {
          bank_id,
          amount: paid_amount,
          description: `Payment update for ${referenceCode}`,
          reference_type: 'sale',
          reference_id: sale_id
        });
      }

      const total_paid = await saleModel.getTotalPaid(conn, sale_id);
      const paymentStatusFinal = total_paid >= total ? 'paid' : total_paid > 0 ? 'partial' : 'unpaid';

      await saleModel.updatePaymentStatus(conn, sale_id, total_paid, paymentStatusFinal);
      await saleModel.insertLedgerEntry(conn, {
        reference: referenceCode,
        amount: total,
        bank_id
      });
    }

    await saleModel.insertAuditLog(conn, {
      sale_id,
      changed_by: req.user?.id || 0,
      old_data: { sale: oldSale, items: oldItems },
      new_data: { sale: { customer_id, bank_id, total, discount_type }, items }
    });

    await conn.commit();
    await saleModel.autoUpdateCustomerTotals(sale_id);

    res.json({ success: true, message: 'Sale updated successfully!' });
  } catch (err) {
    await conn.rollback();
    console.error('Update Sale Error:', err);
    res.status(500).json({ error: err.message });
  } finally {
    conn.release();
  }
};



exports.softDeleteSale = async (req, res) => {
  try {
    await saleModel.softDelete(req.params.id);
    res.json({ success: true, message: 'Sale soft-deleted' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

exports.getArchivedSales = async (req, res) => {
  try {
    const [rows] = await saleModel.getArchived();
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};

exports.restoreSale = async (req, res) => {
  try {
    await saleModel.restore(req.params.id);
    res.json({ success: true, message: 'Sale restored successfully' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
};
exports.exportSalesExcel = async (req, res) => {
  try {
    const [items] = await db.query(`
    SELECT s.*, a.name AS user_name, c.name AS customer_name, b.name AS bank_name
    FROM sales s
    LEFT JOIN users a ON s.created_by = a.id
    LEFT JOIN customers c ON s.customer_id = c.id
    LEFT JOIN banks b ON s.bank_id = b.id
    WHERE 1=1
    `);

    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet('Sales List');

    sheet.columns = [
      { header: 'Date', key: 'sale_date' },
      { header: 'Customer', key: 'customer_name' },
      { header: 'Ref', key: 'reference' },
      { header: 'Paid Amount', key: 'paid_amount' },
      { header: 'Due Amount', key: 'due_amount' },
      { header: 'Total', key: 'total' },
      { header: 'Sales Status', key: 'status' },
      { header: 'Payment Status', key: 'payment_status' },
      { header: 'Created', key: 'user_name' },
    ];

    const formatDate = (date) =>
      date ? new Date(date).toISOString().split("T")[0] : "";

    items.forEach(item => {
      sheet.addRow({
        sale_date: formatDate(item.sale_date),
        customer_name: item.customer_name,
        reference: item.reference,
        paid_amount: item.paid_amount,
        due_amount: item.due_amount,
        total: item.total,
        status: item.status,
        payment_status: item.payment_status,
        user_name: item.user_name
      });
    });

    const filePath = path.join(__dirname, '../exports/sales_list.xlsx');
    await workbook.xlsx.writeFile(filePath);
    res.download(filePath, 'sales_list.xlsx', () => fs.unlinkSync(filePath));

  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to export Excel' });
  }
};

exports.exportSalesPrint = async (req, res) => {
  const formatDate = (date) =>
    date ? new Date(date).toISOString().split("T")[0] : "";
  try {
    const [items] = await db.query(`
    SELECT s.*, a.name AS user_name, c.name AS customer_name, b.name AS bank_name
    FROM sales s
    LEFT JOIN users a ON s.created_by = a.id
    LEFT JOIN customers c ON s.customer_id = c.id
    LEFT JOIN banks b ON s.bank_id = b.id
    WHERE 1=1
    `);

    const html = `
      <html>
        <head>
          <title>Sales List</title>
          <style>
            table { border-collapse: collapse; width: 100%; font-family: sans-serif; }
            th, td { border: 1px solid #ccc; padding: 8px; text-align: left; }
            th { background: #f0f0f0; }
          </style>
        </head>
        <body>
          <h2>Sales List</h2>
          <table>
            <thead>
              <tr>
                <th>Date</th>
                <th>Customer</th>
                <th>Paid Amount</th>
                <th>Due Amount</th>
                <th>Total</th>
                <th>Sales Status</th>
                <th>Payment Status</th>
                <th>Created by</th>
              </tr>
            </thead>
            <tbody>
              ${items.map(item => `
                <tr>
                  <td>${formatDate(item.sale_date)}</td>
                  <td>${item.customer_name}</td>
                  <td>${item.paid_amount || ''}</td>
                  <td>${item.due_amount}</td>
                  <td>${item.total}</td>
                  <td>${item.status}</td>
                  <td>${item.payment_status}</td>
                  <td>${item.user_name}</td>
                </tr>`).join('')}
            </tbody>
          </table>
          <script>window.print()</script>
        </body>
      </html>
    `;

    res.setHeader('Content-Type', 'text/html');
    res.send(html);

  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to generate printable view' });
  }
};
exports.exportSalesPdf = async (req, res) => {
  const formatDate = (date) =>
    date ? new Date(date).toISOString().split("T")[0] : "";
  const capitalizeFirstLetter = (word) => {
    if (!word || typeof word !== "string") return word;
    return word.charAt(0).toUpperCase() + word.slice(1);
  };
  try {
    const [items] = await db.query(`
    SELECT s.*, a.name AS user_name, c.name AS customer_name, b.name AS bank_name
    FROM sales s
    LEFT JOIN users a ON s.created_by = a.id
    LEFT JOIN customers c ON s.customer_id = c.id
    LEFT JOIN banks b ON s.bank_id = b.id
    WHERE 1=1
    `);

    const doc = new PDFDocument({ margin: 20, size: 'A4', layout: 'portrait' });

    res.setHeader("Content-Disposition", "attachment; filename=item_list.pdf");
    res.setHeader("Content-Type", "application/pdf");

    doc.pipe(res); // stream to browser

    // Title
    doc.fontSize(16).text("Sales List", { align: "center" });
    doc.moveDown(1);

    // Table content
    const table = {
      headers: [
        "Date", "Customer", "Paid Amt", "Due Amt", "Total", "Sales",
        "Payment", "Prepared"
      ],
      rows: items.map((item) => [
        formatDate(item.sale_date),
        item.customer_name,
        item.paid_amount != null ? Number(item.paid_amount).toFixed(2) : "0.00",
        item.due_amount != null ? Number(item.due_amount).toFixed(2) : "0.00",
        item.total != null ? Number(item.total).toFixed(2) : "0.00",
        capitalizeFirstLetter(item.status) || '-',
        capitalizeFirstLetter(item.payment_status) || '-',
        item.user_name || '-',
      ])
    };

    await doc.table(table, {
      border: true, // enable column borders
      columnSpacing: 5,
      padding: 4,
      prepareHeader: () => doc.font("Helvetica-Bold").fontSize(10),
      prepareRow: (row, i) =>
        doc.font("Helvetica").fontSize(9).fillColor(i % 2 === 0 ? "#000" : "#444")
    });

    doc.end();
  } catch (err) {
    console.error("PDF export error:", err);
    res.status(500).json({ error: "PDF generation failed" });
  }
};
exports.getSalePaymentHistory = async (req, res) => {
  const sale_id = req.params.id;

  try {
    const [payments] = await db.query(`
      SELECT 
        bt.id,
        bt.bank_id,
        b.name AS bank_name,
        bt.type,
        bt.amount,
        bt.description,
        bt.created_at
      FROM bank_transactions bt
      JOIN banks b ON bt.bank_id = b.id
      WHERE bt.reference_type = 'sale' AND bt.reference_id = ?
      ORDER BY bt.created_at DESC
    `, [sale_id]);

    res.json(payments);
  } catch (err) {
    console.error('Sale Payment History Fetch Error:', err);
    res.status(500).json({ error: 'Failed to fetch sale payment history' });
  }
};
exports.addSalePayment = async (req, res) => {
  const sale_id = req.params.id;
  const { bank_id, amount } = req.body;

  if (!bank_id || !amount || amount <= 0) {
    return res.status(400).json({ error: 'Invalid bank or amount' });
  }

  const conn = await db.getConnection();
  await conn.beginTransaction();

  try {
    const referenceCode = `SALE-${sale_id}`;

    // Insert new payment
    await conn.query(`
          INSERT INTO bank_transactions (bank_id, type, amount, description, reference_type, reference_id)
          VALUES (?, 'deposit', ?, ?, 'sale', ?)
      `, [bank_id, amount, `Manual payment for ${referenceCode}`, sale_id]);

    // Update bank balance
    // await conn.query('UPDATE banks SET balance = balance + ? WHERE id = ?', [amount, bank_id]);

    // Fetch sale total
    const [[sale]] = await conn.query('SELECT total FROM sales WHERE id = ?', [sale_id]);

    // Calculate total paid
    const [[{ total_paid }]] = await conn.query(`
          SELECT IFNULL(SUM(amount), 0) AS total_paid 
          FROM bank_transactions 
          WHERE reference_type = 'sale' AND reference_id = ?
      `, [sale_id]);

    // Determine payment status
    const paymentStatus = total_paid >= sale.total ? 'paid' : total_paid > 0 ? 'partial' : 'unpaid';

    // Update sales table
    await conn.query(`
          UPDATE sales SET paid_amount = ?, payment_status = ? WHERE id = ?
      `, [total_paid, paymentStatus, sale_id]);

    await conn.commit();
    // Trigger customer totals update
    await autoUpdateCustomerTotals(sale_id);
    res.json({ success: true, message: 'Payment added successfully', total_paid, payment_status: paymentStatus });

  } catch (err) {
    await conn.rollback();
    console.error('Add Sale Payment Error:', err);
    res.status(500).json({ error: 'Failed to add payment' });
  } finally {
    conn.release();
  }
};
