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

const router = express.Router();

// Get all petty cash entries
router.get('/', async (req, res) => {
    try {
        const [rows] = await pool.execute('SELECT * FROM petty_cash ORDER BY date DESC, created_at DESC');
        res.json(rows);
    } catch (error: any) {
        res.status(500).json({ error: error.message });
    }
});

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

        await connection.query(
            'INSERT INTO petty_cash (id, date, description, amount, type, category, reference, project_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            [id, date, description, amount, type, category, reference, project_id || null]
        );

        // If Petty Cash is 'In' (Topping up), deduct from Bank
        if (type === 'In') {
            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, `Petty Cash Top-up - ${description}`, amount, 'Debit', 'Transfer', id, 'Petty Cash', project_id || null]
            );

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

        await connection.commit();
        res.status(201).json({ id, ...req.body });
    } catch (error: any) {
        await connection.rollback();
        res.status(500).json({ error: error.message });
    } finally {
        connection.release();
    }
});

// Delete entry
router.delete('/:id', async (req, res) => {
    try {
        await pool.execute('DELETE FROM petty_cash WHERE id = ?', [req.params.id]);
        res.status(204).send();
    } catch (error: any) {
        res.status(500).json({ error: error.message });
    }
});

export default router;
