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

const router = Router();

// Get bank status (details + recent transactions)
router.get('/', async (req, res) => {
    try {
        const [detailsRows]: any = await pool.query('SELECT * FROM bank_details WHERE id = 1');
        const [transactionsRows]: any = await pool.query(`
            SELECT bt.*, p.name as project_name 
            FROM bank_transactions bt 
            LEFT JOIN projects p ON bt.project_id = p.id 
            ORDER BY bt.date DESC, bt.created_at DESC 
            LIMIT 50
        `);

        res.json({
            details: detailsRows[0] || null,
            transactions: transactionsRows
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    }
});

// Get bank details only
router.get('/details', async (req, res) => {
    try {
        const [rows]: any = await pool.query('SELECT * FROM bank_details WHERE id = 1');
        res.json(rows[0] || null);
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    }
});

// Update or create bank details
router.put('/details', async (req, res) => {
    const { bank_name, account_name, account_number, iban, swift_code, branch, current_balance } = req.body;
    try {
        // We use INSERT ... ON DUPLICATE KEY UPDATE for the single row with id=1
        await pool.query(
            `INSERT INTO bank_details (id, bank_name, account_name, account_number, iban, swift_code, branch, current_balance)
             VALUES (1, ?, ?, ?, ?, ?, ?, ?)
             ON DUPLICATE KEY UPDATE
             bank_name = VALUES(bank_name),
             account_name = VALUES(account_name),
             account_number = VALUES(account_number),
             iban = VALUES(iban),
             swift_code = VALUES(swift_code),
             branch = VALUES(branch),
             current_balance = VALUES(current_balance)`,
            [bank_name, account_name, account_number, iban, swift_code, branch, current_balance || 0]
        );
        res.json({ message: 'Bank details updated successfully' });
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    }
});

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

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

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

        // Update balance
        const balanceChange = type === 'Credit' ? amount : -amount;
        await connection.query(
            'UPDATE bank_details SET current_balance = current_balance + ? WHERE id = 1',
            [balanceChange]
        );

        await connection.commit();
        res.status(201).json({ id, ...req.body });
    } catch (error) {
        await connection.rollback();
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    } finally {
        connection.release();
    }
});

// Delete transaction
router.delete('/transactions/:id', async (req, res) => {
    const { id } = req.params;
    const connection = await pool.getConnection();
    try {
        await connection.beginTransaction();

        // Get transaction details first to reverse balance
        const [rows]: any = await connection.query('SELECT * FROM bank_transactions WHERE id = ?', [id]);
        if (rows.length > 0) {
            const tx = rows[0];
            const reverseChange = tx.type === 'Credit' ? -tx.amount : tx.amount;

            await connection.query(
                'UPDATE bank_details SET current_balance = current_balance + ? WHERE id = 1',
                [reverseChange]
            );

            await connection.query('DELETE FROM bank_transactions WHERE id = ?', [id]);
        }

        await connection.commit();
        res.json({ message: 'Transaction deleted successfully' });
    } catch (error) {
        await connection.rollback();
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    } finally {
        connection.release();
    }
});

export default router;
