const { Expense, ExpenseCategory, Vendor } = require('../models/expense');
const { User } = require('../models/foundation');
const { logAction } = require('../utils/logger');
const { Op } = require('sequelize');
const { sequelize } = require('../config/db');
const { recordToDefaultBank } = require('./bankService');

exports.createExpense = async (data, userId) => {
    const transaction = await sequelize.transaction();
    try {
        // Sanitize optional fields
        const sanitizedData = { ...data };
        if (!sanitizedData.vendorId || sanitizedData.vendorId === '') {
            sanitizedData.vendorId = null;
        }

        const expense = await Expense.create({
            ...sanitizedData,
            createdBy: userId,
            status: 'PAID', // Default
            balanceDue: 0.00
        }, { transaction });

        const totalAmount = parseFloat(sanitizedData.amount);
        let paymentAmount = sanitizedData.paymentAmount !== undefined ? parseFloat(sanitizedData.paymentAmount) : totalAmount;

        // Handle Vendor Credit Logic
        if (sanitizedData.vendorId) {
            const vendor = await Vendor.findByPk(sanitizedData.vendorId, { transaction });
            if (vendor) {
                // 1. Check for Overpayment (Create Credit)
                if (paymentAmount > totalAmount) {
                    const surplus = paymentAmount - totalAmount;
                    await vendor.increment('creditBalance', { by: surplus, transaction });
                    await logAction(userId, 'UPDATE', 'VENDOR', { id: vendor.id, action: 'Added Credit', amount: surplus }, null);
                }

                // 2. Check for Underpayment (Pending Balance)
                if (paymentAmount < totalAmount) {
                    let balanceDue = totalAmount - paymentAmount;

                    // 3. Auto-Apply Existing Credit
                    if (vendor.creditBalance > 0) {
                        const creditToApply = Math.min(balanceDue, parseFloat(vendor.creditBalance));
                        if (creditToApply > 0) {
                            balanceDue -= creditToApply;
                            paymentAmount += creditToApply; // Treat credit as payment
                            await vendor.decrement('creditBalance', { by: creditToApply, transaction });
                            await logAction(userId, 'UPDATE', 'VENDOR', { id: vendor.id, action: 'Applied Credit', amount: creditToApply }, null);
                        }
                    }

                    // Update Expense Status
                    if (balanceDue > 0) {
                        await expense.update({
                            status: paymentAmount > 0 ? 'PARTIAL' : 'PENDING',
                            balanceDue: balanceDue
                        }, { transaction });
                    } else {
                        // Fully covered by credit
                        await expense.update({ status: 'PAID', balanceDue: 0 }, { transaction });
                    }
                }
            }
        }

        // Record to Banking (Debit) - Record ACTUAL Cash Outflow
        if (paymentAmount > 0) {
            await recordToDefaultBank({
                type: 'Debit',
                amount: paymentAmount, // Cash out
                description: `Expense: ${expense.description || 'No description'}`,
                reference: expense.reference,
                module: 'Expense',
                referenceId: expense.id
            }, transaction);
        }

        await logAction(userId, 'CREATE', 'EXPENSE', { id: expense.id, amount: expense.amount, payment: paymentAmount }, null);
        await transaction.commit();
        return expense;
    } catch (err) {
        await transaction.rollback();
        throw err;
    }
};

exports.getAllExpenses = async (filters = {}) => {
    const where = {};
    if (filters.category) where.expenseCategoryId = filters.category;
    if (filters.vendor) where.vendorId = filters.vendor;
    if (filters.startDate && filters.endDate) {
        where.expenseDate = { [Op.between]: [filters.startDate, filters.endDate] };
    }

    return await Expense.findAll({
        where,
        include: [
            { model: ExpenseCategory, attributes: ['name'] },
            { model: Vendor, attributes: ['name'] },
            { model: User, as: 'creator', attributes: ['fullName'] }
        ],
        order: [['expenseDate', 'DESC'], ['createdAt', 'DESC']]
    });
};

exports.getExpenseById = async (id) => {
    return await Expense.findByPk(id, {
        include: [ExpenseCategory, Vendor, { model: User, as: 'creator', attributes: ['fullName'] }]
    });
};

exports.updateExpense = async (id, data, userId) => {
    const transaction = await sequelize.transaction();
    try {
        const expense = await Expense.findByPk(id);
        if (!expense) throw new Error('Expense record not found');

        const oldAmount = expense.amount;
        // Sanitize optional fields
        const sanitizedData = { ...data };
        if (sanitizedData.vendorId === '') {
            sanitizedData.vendorId = null;
        }

        await expense.update(sanitizedData, { transaction });

        // If amount changed, adjust bank balance
        if (parseFloat(data.amount) !== parseFloat(oldAmount)) {
            // Reverse old amount (Credit)
            await recordToDefaultBank({
                type: 'Credit',
                amount: oldAmount,
                description: `Correction: Reverse old expense (ID: ${id})`,
                module: 'Expense',
                referenceId: id
            }, transaction);

            // Record new amount (Debit)
            await recordToDefaultBank({
                type: 'Debit',
                amount: data.amount,
                description: `Correction: Record updated expense (ID: ${id})`,
                module: 'Expense',
                referenceId: id
            }, transaction);
        }

        await logAction(userId, 'UPDATE', 'EXPENSE', { id, ...data }, null);
        await transaction.commit();
        return expense;
    } catch (err) {
        await transaction.rollback();
        throw err;
    }
};

exports.deleteExpense = async (id, userId) => {
    const transaction = await sequelize.transaction();
    try {
        const expense = await Expense.findByPk(id);
        if (!expense) throw new Error('Expense record not found');

        // Reverse the expense in banking (Credit)
        await recordToDefaultBank({
            type: 'Credit',
            amount: expense.amount,
            description: `Reversal: Deleted expense record (ID: ${id})`,
            module: 'Expense',
            referenceId: id
        }, transaction);

        await expense.destroy({ transaction });
        await logAction(userId, 'DELETE', 'EXPENSE', { id }, null);
        await transaction.commit();
    } catch (err) {
        await transaction.rollback();
        throw err;
    }
};

exports.getExpenseSummary = async () => {
    const total = await Expense.sum('amount') || 0;
    const recent = await Expense.findAll({
        limit: 5,
        order: [['createdAt', 'DESC']],
        include: [ExpenseCategory, Vendor]
    });
    return { total, recent };
};

// --- Category Logic ---
exports.getCategories = async () => {
    return await ExpenseCategory.findAll({ where: { isActive: true } });
};

exports.createCategory = async (data) => {
    return await ExpenseCategory.create(data);
};

exports.updateCategory = async (id, data) => {
    const cat = await ExpenseCategory.findByPk(id);
    if (!cat) throw new Error('Category not found');
    return await cat.update(data);
};

exports.deleteCategory = async (id) => {
    const cat = await ExpenseCategory.findByPk(id);
    if (!cat) throw new Error('Category not found');

    // Prevent deleting if linked to expense records
    const usageCount = await Expense.count({ where: { expenseCategoryId: id } });
    if (usageCount > 0) {
        throw new Error('Cannot delete category: It is already linked to expense records.');
    }

    return await cat.destroy();
};

// --- Vendor Logic ---
exports.getAllVendors = async () => {
    const vendors = await Vendor.findAll({
        order: [['name', 'ASC']],
        include: [{
            model: Expense,
            attributes: ['balanceDue'],
            where: { status: { [Op.in]: ['PENDING', 'PARTIAL'] } },
            required: false
        }]
    });

    // Calculate pending totals manually to avoid group by complexity
    return vendors.map(v => {
        const vendor = v.toJSON();
        vendor.pendingBalance = vendor.Expenses ? vendor.Expenses.reduce((sum, e) => sum + parseFloat(e.balanceDue || 0), 0) : 0;
        delete vendor.Expenses; // Clean up response
        return vendor;
    });
};

exports.createVendor = async (data, userId) => {
    const vendor = await Vendor.create(data);
    await logAction(userId, 'CREATE', 'VENDOR', { id: vendor.id, name: vendor.name }, null);
    return vendor;
};

exports.updateVendor = async (id, data, userId) => {
    const vendor = await Vendor.findByPk(id);
    if (!vendor) throw new Error('Vendor not found');
    await vendor.update(data);
    await logAction(userId, 'UPDATE', 'VENDOR', { id, ...data }, null);
    return vendor;
};

exports.deleteVendor = async (id, userId) => {
    console.log(`Attempting to delete vendor: ${id} by user: ${userId}`);
    const vendor = await Vendor.findByPk(id);
    if (!vendor) throw new Error('Vendor not found');

    // Prevent deleting if linked to expense records
    const usageCount = await Expense.count({ where: { vendorId: id } });
    console.log(`Vendor usage count: ${usageCount}`);
    if (usageCount > 0) {
        throw new Error('Cannot delete vendor: They are already linked to expense records.');
    }

    await vendor.destroy();
    await logAction(userId, 'DELETE', 'VENDOR', { id }, null);
    return true;
};
