const db = require('../db/config');

exports.autoUpdateSupplierTotals = async (purchase_id) => {
    try {
        // Fetch supplier_id from purchase
        const [[purchase]] = await db.query('SELECT supplier_id FROM purchases WHERE id = ?', [purchase_id]);

        if (!purchase) {
            console.warn(`No purchase found with ID: ${purchase_id}`);
            return;
        }

        const supplier_id = purchase.supplier_id;

        // Calculate total purchases
        const [[purchaseData]] = await db.query(`
            SELECT IFNULL(SUM(total), 0) AS total_purchases
            FROM purchases
            WHERE supplier_id = ? AND is_deleted = 0
        `, [supplier_id]);

        // Calculate total paid
        const [[paidData]] = await db.query(`
            SELECT IFNULL(SUM(amount), 0) AS total_paid
            FROM bank_transactions
            WHERE reference_type = 'purchase' AND reference_id IN (
                SELECT id FROM purchases WHERE supplier_id = ? AND is_deleted = 0
            )
        `, [supplier_id]);

        const purchase_due = purchaseData.total_purchases - paidData.total_paid;

        // Update suppliers table
        await db.query(`
            UPDATE suppliers SET total_paid = ?, purchase_due = ?
            WHERE id = ?
        `, [paidData.total_paid, purchase_due, supplier_id]);

        console.log(`Supplier totals updated for Supplier ID: ${supplier_id}`);

    } catch (err) {
        console.error('Auto-update supplier totals failed:', err.message);
    }
};
