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

exports.createMultiItemPurchase = async (req, res) => {
    const {
        supplier_id,
        bank_id,
        items,
        purchase_date,
        status = 'Received',
        reference = '',
        paid_amount = 0,
        payment_status,
        discount = 0,
        other_charges = 0,
        note = ''
    } = req.body;

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

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

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

        const [purchaseResult] = await conn.query(
            `INSERT INTO purchases 
             (supplier_id, purchase_date, total, bank_id, status, payment_status, paid_amount, reference, discount, other_charges, note) 
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [supplier_id, purchaseDate, total, bank_id, status, paymentStatusFinal, paid_amount, reference, discount, other_charges, note]
        );

        const purchase_id = purchaseResult.insertId;

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

            for (const s of sizes) {
                const qty = Number(s.quantity || 0);
                const total = qty * item.price;
                await conn.query(
                    `INSERT INTO purchase_items (purchase_id, item_id, size, quantity, price, discount, total)
                     VALUES (?, ?, ?, ?, ?, ?, ?)`,
                    [purchase_id, itemId, s.size, qty, item.price, item.discount || 0, total]
                );

                // Stock update
                if (status === 'Received') {
                    const [existing] = await conn.query(
                        `SELECT quantity FROM item_sizes WHERE item_id = ? AND size = ?`,
                        [itemId, s.size]
                    );

                    if (existing.length > 0) {
                        await conn.query(
                            `UPDATE item_sizes SET quantity = quantity + ? WHERE item_id = ? AND size = ?`,
                            [qty, itemId, s.size]
                        );
                    } else {
                        await conn.query(
                            `INSERT INTO item_sizes (item_id, size, quantity) VALUES (?, ?, ?)`,
                            [itemId, s.size, qty]
                        );
                    }
                }
            }

            // Update item price data
            await conn.query(`UPDATE items SET purchase_price_last = ? WHERE id = ?`, [item.price, itemId]);
            await conn.query(`
                UPDATE items i
                JOIN (
                    SELECT pi2.item_id, pi2.price AS previous_price
                    FROM purchase_items pi2
                    JOIN purchases p2 ON pi2.purchase_id = p2.id
                    WHERE pi2.item_id = ?
                    ORDER BY p2.purchase_date DESC
                    LIMIT 1 OFFSET 1
                ) prev ON i.id = prev.item_id
                SET i.purchase_price_prev = prev.previous_price`, [itemId]);

            await conn.query(`
                UPDATE items i
                JOIN (
                    SELECT pi.item_id, SUM(pi.price * pi.quantity) / SUM(pi.quantity) AS avg_price
                    FROM purchase_items pi
                    JOIN purchases p ON pi.purchase_id = p.id
                    WHERE pi.item_id = ?
                    GROUP BY pi.item_id
                ) avg_data ON i.id = avg_data.item_id
                SET i.purchase_price_avg = avg_data.avg_price`, [itemId]);
        }

        const refCode = `PURCHASE-${purchase_id}`;

        if (status === 'Received') {
            await conn.query(
                `INSERT INTO accounts_ledger (type, reference, debit, description, bank_id)
                 VALUES (?, ?, ?, ?, ?)`,
                ['purchase', refCode, paid_amount, 'Auto debit from purchase', bank_id]
            );

            await conn.query(
                `INSERT INTO bank_transactions (bank_id, type, amount, description, reference_type, reference_id)
                 VALUES (?, 'withdraw', ?, ?, 'purchase', ?)`,
                [bank_id, paid_amount, `Purchase paid: ${refCode}`, purchase_id]
            );
        }

        await conn.commit();
        await autoUpdateSupplierTotals(purchase_id);
        res.json({ success: true, message: 'Purchase saved with size-wise entries and stock updates.' });

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


exports.getAllPurchases = async (req, res) => {
    try {
        const [rows] = await purchaseModel.getAll();
        const [items] = await purchaseModel.getAllPurchaseItemsWithSizes();

        const purchaseMap = {};
        for (const row of rows) {
            purchaseMap[row.id] = { ...row, items: [] };
        }

        for (const item of items) {
            if (purchaseMap[item.purchase_id]) {
                purchaseMap[item.purchase_id].items.push({
                    item_id: item.item_id,
                    item_name: item.item_name,
                    size: item.size,
                    quantity: item.quantity,
                    price: item.price,
                    discount: item.discount,
                    total: item.total
                });
            }
        }

        res.json(Object.values(purchaseMap));
    } catch (err) {
        console.error('Fetch purchase list error:', err);
        res.status(500).json({ error: 'Failed to load purchases' });
    }
};

exports.getFilteredPurchases = async (req, res) => {
    try {
        const {
            start_date,
            end_date,
            payment_status,
            status,
            supplier_id
        } = req.query;

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

        const [rows] = await purchaseModel.getFilteredPurchases(filters);
        res.json(rows);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
};
exports.getPurchaseById = async (req, res) => {
    try {
        const [purchase] = await purchaseModel.getById(req.params.id);
        const [items] = await purchaseModel.getItemsWithSizes(req.params.id);

        // Group items by item_id and map sizes
        const itemMap = {};
        for (const row of items) {
            if (!itemMap[row.item_id]) {
                itemMap[row.item_id] = {
                    item_id: row.item_id,
                    name: row.item_name,
                    price: row.price,
                    discount: row.discount,
                    sizes: [],
                    total: 0
                };
            }
            itemMap[row.item_id].sizes.push({ size: row.size, quantity: row.quantity });
            itemMap[row.item_id].total += row.total;
        }

        res.json({ ...purchase[0], items: Object.values(itemMap) });
    } catch (err) {
        console.error('Error fetching purchase:', err);
        res.status(500).json({ error: 'Failed to fetch purchase' });
    }
};

exports.updatePurchase = async (req, res) => {
    const {
        supplier_id,
        bank_id,
        items,
        purchase_date,
        status = 'Received',
        reference = '',
        paid_amount = 0,   // This is NEW payment only
        discount = 0,
        other_charges = 0,
        note = ''
    } = req.body;

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

    try {
        await conn.query('SET innodb_lock_wait_timeout = 5');
        const purchase_id = req.params.id;
        const refCode = `PURCHASE-${purchase_id}`;

        // Fetch old purchase & items
        const [[oldPurchase]] = await conn.query('SELECT * FROM purchases WHERE id = ?', [purchase_id]);
        const [oldItems] = await conn.query('SELECT * FROM purchase_items WHERE purchase_id = ?', [purchase_id]);

        // Reverse stock & ledger if previously 'Received'
        if (oldPurchase.status === 'Received') {
            for (const item of oldItems) {
                await conn.query('UPDATE items SET quantity = quantity - ? WHERE id = ?', [item.quantity, item.item_id]);
            }
            await conn.query('DELETE FROM accounts_ledger WHERE reference = ?', [refCode]);
            // await conn.query('UPDATE banks SET balance = balance + ? WHERE id = ?', [oldPurchase.paid_amount, oldPurchase.bank_id]);
        }

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

        // Update purchase basic info (exclude paid_amount here)
        await conn.query(`
            UPDATE purchases SET 
              supplier_id = ?, purchase_date = ?, total = ?, bank_id = ?, status = ?, 
              reference = ?, discount = ?, other_charges = ?, note = ?
            WHERE id = ?
        `, [
            supplier_id, purchase_date, total, bank_id, status,
            reference, discount, other_charges, note, purchase_id
        ]);

        // Refresh purchase items
        await conn.query('DELETE FROM purchase_items WHERE purchase_id = ?', [purchase_id]);

        const itemValues = items.map(item => [
            purchase_id,
            item.item_id,
            item.quantity,
            item.price,
            item.discount || 0,
            item.total
        ]);

        await conn.query(
            `INSERT INTO purchase_items (purchase_id, item_id, quantity, price, discount, total) VALUES ?`,
            [itemValues]
        );

        // If status is 'Received', handle stock, ledger, and payment
        if (status === 'Received') {
            for (const item of items) {
                await conn.query('UPDATE items SET quantity = quantity + ?, purchase_price_last = ? WHERE id = ?', [
                    item.quantity, item.price, item.item_id
                ]);

                // Update previous and average prices
                await conn.query(
                    `UPDATE items i
                     JOIN (
                       SELECT pi2.item_id, pi2.price AS previous_price
                       FROM purchase_items pi2
                       JOIN purchases p2 ON pi2.purchase_id = p2.id
                       WHERE pi2.item_id = ?
                       ORDER BY p2.purchase_date DESC
                       LIMIT 1 OFFSET 1
                     ) prev ON i.id = prev.item_id
                     SET i.purchase_price_prev = prev.previous_price`,
                    [item.item_id]
                );

                await conn.query(
                    `UPDATE items i
                     JOIN (
                       SELECT pi.item_id, SUM(pi.price * pi.quantity) / SUM(pi.quantity) AS avg_price
                       FROM purchase_items pi
                       JOIN purchases p ON pi.purchase_id = p.id
                       WHERE pi.item_id = ?
                       GROUP BY pi.item_id
                     ) avg_data ON i.id = avg_data.item_id
                     SET i.purchase_price_avg = avg_data.avg_price`,
                    [item.item_id]
                );
            }

            // Insert new payment if applicable
            if (paid_amount > 0) {
                await conn.query(`
                    INSERT INTO bank_transactions (bank_id, type, amount, description, reference_type, reference_id)
                    VALUES (?, 'withdraw', ?, ?, 'purchase', ?)
                `, [bank_id, paid_amount, `Payment update for ${refCode}`, purchase_id]);

                // await conn.query('UPDATE banks SET balance = balance - ? WHERE id = ?', [paid_amount, bank_id]);
            }

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

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

            // Update purchase with total paid and payment status
            await conn.query(`
                UPDATE purchases SET paid_amount = ?, payment_status = ? WHERE id = ?
            `, [total_paid, paymentStatusFinal, purchase_id]);

            // Insert ledger entry
            await conn.query(`
                INSERT INTO accounts_ledger (type, reference, debit, description, bank_id)
                VALUES ('purchase', ?, ?, 'Updated purchase ledger entry', ?)
            `, [refCode, total_paid, bank_id]);
        }

        await conn.commit();
        // Auto-update supplier totals
        await autoUpdateSupplierTotals(purchase_id);
        res.json({ success: true, message: 'Purchase updated successfully.' });

    } catch (err) {
        await conn.rollback();
        console.error('Update Purchase Error:', err);
        res.status(500).json({ error: err.message });
    } finally {
        conn.release();
    }
};


exports.softDeletePurchase = async (req, res) => {
    await purchaseModel.softDelete(req.params.id);
    res.json({ success: true });
};

exports.restorePurchase = async (req, res) => {
    await purchaseModel.restore(req.params.id);
    res.json({ success: true });
};

exports.getArchivedPurchases = async (req, res) => {
    const [rows] = await purchaseModel.getArchived();
    res.json(rows);
};
exports.getPurchasePaymentHistory = async (req, res) => {
    const purchase_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 = 'purchase' AND bt.reference_id = ?
        ORDER BY bt.created_at DESC;
      `, [purchase_id]);

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

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

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

    try {
        const referenceCode = `PURCHASE-${purchase_id}`;

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

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

        // Fetch purchase total
        const [[purchase]] = await conn.query('SELECT total FROM purchases WHERE id = ?', [purchase_id]);

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

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

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

        await conn.commit();
        res.json({ success: true, message: 'Payment added successfully', total_paid, payment_status: paymentStatus });

    } catch (err) {
        await conn.rollback();
        console.error('Add Purchase Payment Error:', err);
        res.status(500).json({ error: 'Failed to add payment' });
    } finally {
        conn.release();
    }
};
exports.exportPurchasesPrint = 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 supplier_name, b.name AS bank_name
      FROM purchases s
      LEFT JOIN users a ON s.created_by = a.id
      LEFT JOIN suppliers c ON s.supplier_id = c.id
      LEFT JOIN banks b ON s.bank_id = b.id
      WHERE 1=1
      `);

        const html = `
        <html>
          <head>
            <title>Purchases 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>Purchases List</h2>
            <table>
              <thead>
                <tr>
                  <th>Date</th>
                  <th>Supplier</th>
                  <th>Paid Amount</th>
                  <th>Due Amount</th>
                  <th>Total</th>
                  <th>Purchases Status</th>
                  <th>Payment Status</th>
                  <th>Created by</th>
                </tr>
              </thead>
              <tbody>
                ${items.map(item => `
                  <tr>
                    <td>${formatDate(item.purchase_date)}</td>
                    <td>${item.supplier_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' });
    }
};