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

exports.createExpense = async (req, res) => {
    const { category_id, amount, note, bank_id, reference_type = null, reference_id = null } = req.body;
    const user_id = req.user?.id || 0;  // Assuming auth middleware sets req.user

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

    try {
        // Create Expense
        const expenseId = await expenseModel.createExpense({
            category_id,
            amount,
            note,
            bank_id,
            created_by: user_id,
            reference_type,
            reference_id
        });

        const reference = `EXP-${expenseId}`;

        // Add Ledger Entry
        await expenseModel.addLedgerEntry({
            reference,
            amount,
            bank_id,
            description: 'Auto debit for expense'
        });

        // Add Bank Transaction
        await expenseModel.addBankTransaction({
            bank_id,
            amount,
            description: `Expense paid: ${reference}`,
            reference_id: expenseId,
            reference_type: 'expense'
        });

        // Update Bank Balance
        await expenseModel.updateBankBalance(amount, bank_id);

        await conn.commit();
        res.json({ success: true, message: 'Expense recorded successfully.' });
    } catch (err) {
        await conn.rollback();
        console.error('Create Expense Error:', err);
        res.status(500).json({ error: err.message });
    } finally {
        conn.release();
    }
};
exports.updateExpense = async (req, res) => {
    const expense_id = req.params.id;
    const { category_id, amount, note, bank_id } = req.body;

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

    try {
        // Fetch existing expense
        const expense = await expenseModel.getExpenseById(expense_id);
        if (!expense || expense.is_deleted) {
            throw new Error('Expense not found or already deleted.');
        }

        const reference = `EXP-${expense_id}`;

        // Reverse previous financial transactions
        await expenseModel.reverseFinancials({
            reference,
            amount: expense.amount,
            bank_id: expense.bank_id
        });

        // Update expense record
        await expenseModel.updateExpense({
            expense_id,
            category_id,
            amount,
            note,
            bank_id
        });

        // Re-apply ledger entry
        await expenseModel.addLedgerEntry({
            reference,
            amount,
            bank_id,
            description: 'Updated expense auto debit'
        });

        // Re-apply bank transaction
        await expenseModel.addBankTransaction({
            bank_id,
            amount,
            description: `Expense paid: ${reference}`,
            reference_id: expense_id,
            reference_type: 'expense'
        });

        // Deduct updated amount from bank balance
        await expenseModel.updateBankBalance(amount, bank_id);

        await conn.commit();
        res.json({ success: true, message: 'Expense updated successfully.' });

    } catch (err) {
        await conn.rollback();
        console.error('Update Expense Error:', err);
        res.status(500).json({ error: err.message });
    } finally {
        conn.release();
    }
};
exports.getExpenses = async (req, res) => {
    const { start_date, end_date, category_id } = req.query;

    try {
        const expenses = await expenseModel.getExpenses({
            startDate: start_date,
            endDate: end_date,
            category_id,
        });
        res.json(expenses);
    } catch (err) {
        console.error('Fetch Expenses Error:', err);
        res.status(500).json({ error: err.message });
    }
};


// exports.getExpenses = async (req, res) => {
//     try {
//         const expenses = await expenseModel.getExpenses();
//         res.json(expenses);
//     } catch (err) {
//         console.error('Fetch Expenses Error:', err);
//         res.status(500).json({ error: err.message });
//     }
// };

exports.deleteExpense = async (req, res) => {
    const expense_id = req.params.id;

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

    try {
        const expense = await expenseModel.getExpenseById(expense_id);
        if (!expense || expense.is_deleted) {
            throw new Error('Expense not found or already deleted');
        }

        // Reverse Financials
        await expenseModel.reverseExpenseFinancials(expense);

        // Soft Delete
        await expenseModel.deleteExpense(expense_id);

        await conn.commit();
        res.json({ success: true, message: 'Expense deleted successfully.' });
    } catch (err) {
        await conn.rollback();
        console.error('Delete Expense Error:', err);
        res.status(500).json({ error: err.message });
    } finally {
        conn.release();
    }
};
exports.getSingleExpenseById = async (req, res) => {
    const expense_id = req.params.id;

    try {
        const expense = await expenseModel.getSingleExpenseById(expense_id);

        if (!expense) {
            return res.status(404).json({ error: 'Expense not found.' });
        }

        res.json(expense);
    } catch (err) {
        console.error('Get Expense Error:', err);
        res.status(500).json({ error: err.message });
    }
};

exports.createExpenseCategory = async (req, res) => {
    const { name, description = '' } = req.body;
    try {
        await db.query('INSERT INTO expense_categories (name, description) VALUES (?, ?)', [name, description]);
        res.json({ success: true, message: 'Category created successfully.' });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
};
exports.updateCategory = async (req, res) => {
    const category_id = req.params.id;
    const { name, description } = req.body;

    try {
        const category = await expenseModel.getCategoryById(category_id);
        if (!category) {
            return res.status(404).json({ error: 'Category not found.' });
        }

        await expenseModel.updateCategory({ category_id, name, description });

        res.json({ success: true, message: 'Category updated successfully.' });
    } catch (err) {
        console.error('Update Category Error:', err);
        res.status(500).json({ error: err.message });
    }
};
exports.listExpenseCategories = async (req, res) => {
    try {
        const [categories] = await db.query(`
        SELECT * FROM expense_categories
        WHERE is_deleted = 0
        ORDER BY created_at DESC`);
        res.json(categories);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
};
exports.getCategoryById = async (req, res) => {
    const category_id = req.params.id;

    try {
        const category = await expenseModel.getCategoryById(category_id);
        if (!category) {
            return res.status(404).json({ error: 'Category not found.' });
        }

        res.json(category);
    } catch (err) {
        console.error('Get Category Error:', err);
        res.status(500).json({ error: err.message });
    }
};
exports.deleteCategory = async (req, res) => {
    const category_id = req.params.id;

    try {
        const category = await expenseModel.getCategoryById(category_id);
        if (!category || category.is_deleted) {
            return res.status(404).json({ error: 'Category not found or already deleted.' });
        }

        // Optional: Check if category is linked to expenses
        const [linkedExpenses] = await db.query('SELECT COUNT(*) AS count FROM expenses WHERE category = ? AND is_deleted = 0', [category_id]);
        if (linkedExpenses[0].count > 0) {
            return res.status(400).json({ error: 'Cannot delete category linked to existing expenses.' });
        }

        await expenseModel.deleteCategory(category_id);

        res.json({ success: true, message: 'Category deleted successfully.' });
    } catch (err) {
        console.error('Delete Category Error:', err);
        res.status(500).json({ error: err.message });
    }
};