import { Router } from 'express';
import pool from '../db.js';
import { v4 as uuidv4 } from 'uuid';

const router = Router();

// Get all expenses
router.get('/', async (req, res) => {
    try {
        const [rows] = await pool.query(`
      SELECT e.*, p.name as project_name 
      FROM expenses e 
      LEFT JOIN projects p ON e.project_id = p.id 
      ORDER BY e.date DESC
    `);
        res.json(rows);
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    }
});

// Create expense
router.post('/', async (req, res) => {
    const { project_id, category, description, amount, vat_amount, total_amount, date, payment_method, reference } = req.body;
    const id = uuidv4();
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();

        await connection.query(
            'INSERT INTO expenses (id, project_id, category, description, amount, vat_amount, total_amount, date, payment_method, reference) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            [id, project_id, category, description, amount, vat_amount, total_amount, date, payment_method, reference]
        );

        // Log bank transaction if payment method is bank transfer
        if (payment_method === 'Bank Transfer' || payment_method === 'Bank') {
            const txId = uuidv4();
            await connection.query(
                'INSERT INTO bank_transactions (id, date, description, amount, type, category, reference_id, reference_type, project_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
                [txId, date, `Expense - ${description || category}`, total_amount, 'Debit', category, id, 'Expense', project_id || null]
            );

            // Update bank balance
            await connection.query(
                'UPDATE bank_details SET current_balance = current_balance - ? WHERE id = 1',
                [total_amount]
            );
        }

        await connection.commit();
        res.status(201).json({ id, project_id, category, description, amount, vat_amount, total_amount, date, payment_method, reference });
    } catch (error) {
        await connection.rollback();
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    } finally {
        connection.release();
    }
});

// Delete expense
router.delete('/:id', async (req, res) => {
    const { id } = req.params;
    try {
        await pool.query('DELETE FROM expenses WHERE id = ?', [id]);
        res.json({ message: 'Expense deleted successfully' });
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    }
});

export default router;
