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

const router = Router();

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

// Get LPO by ID (including items)
router.get('/:id', async (req, res) => {
    try {
        const [lpoRows]: any = await pool.query(`
            SELECT l.*, v.name as vendor_name, v.email as vendor_email, v.phone as vendor_phone, v.address as vendor_address, v.trn_no as vendor_trn, p.name as project_name, p.project_number
            FROM lpos l
            LEFT JOIN vendors v ON l.vendor_id = v.id
            LEFT JOIN projects p ON l.project_id = p.id
            WHERE l.id = ?
        `, [req.params.id]);

        if (lpoRows.length === 0) {
            return res.status(404).json({ error: 'LPO not found' });
        }

        const [itemRows] = await pool.query(`
            SELECT i.*, m.name as material_name
            FROM lpo_items i
            LEFT JOIN materials m ON i.material_id = m.id
            WHERE i.lpo_id = ?
        `, [req.params.id]);

        res.json({ ...lpoRows[0], items: itemRows });
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    }
});

// Create LPO
router.post('/', async (req, res) => {
    const { vendor_id, project_id, lpo_number, date, items, terms, total_amount } = req.body;
    const lpoId = uuidv4();
    const connection = await pool.getConnection();

    try {
        await connection.beginTransaction();

        await connection.query(
            'INSERT INTO lpos (id, vendor_id, project_id, lpo_number, date, terms, total_amount, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            [lpoId, vendor_id, project_id, lpo_number, date, terms, total_amount, 'Draft']
        );

        for (const item of items) {
            const itemId = uuidv4();
            await connection.query(
                'INSERT INTO lpo_items (id, lpo_id, material_id, description, quantity, rate, total) VALUES (?, ?, ?, ?, ?, ?, ?)',
                [itemId, lpoId, item.material_id, item.description, item.quantity, item.rate, item.total]
            );
        }

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

// Accept LPO (Trigger Expense & Bank Transaction)
router.post('/:id/accept', async (req, res) => {
    const lpoId = req.params.id;
    const connection = await pool.getConnection();

    try {
        await connection.beginTransaction();

        // 1. Get LPO details
        const [lpoRows]: any = await connection.query('SELECT * FROM lpos WHERE id = ?', [lpoId]);
        if (lpoRows.length === 0) {
            await connection.rollback();
            return res.status(404).json({ error: 'LPO not found' });
        }
        const lpo = lpoRows[0];

        if (lpo.status === 'Accepted') {
            await connection.rollback();
            return res.status(400).json({ error: 'LPO already accepted' });
        }

        // 2. Update LPO status
        await connection.query('UPDATE lpos SET status = ? WHERE id = ?', ['Accepted', lpoId]);

        // 3. Create Expense
        const expenseId = uuidv4();
        const [vendorRows]: any = await connection.query('SELECT name FROM vendors WHERE id = ?', [lpo.vendor_id]);
        const vendorName = vendorRows.length > 0 ? vendorRows[0].name : 'Vendor';

        await connection.query(
            'INSERT INTO expenses (id, project_id, category, description, amount, total_amount, date, payment_method, reference) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
            [expenseId, lpo.project_id, 'Materials', `LPO ${lpo.lpo_number}: Purchase from ${vendorName}`, lpo.total_amount, lpo.total_amount, new Date(), 'Bank Transfer', lpo.lpo_number]
        );

        // 4. Create Bank Transaction (Debit)
        const bankTransactionId = uuidv4();
        await connection.query(
            'INSERT INTO bank_transactions (id, date, description, amount, type, category, reference_id, reference_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            [bankTransactionId, new Date(), `Payment for LPO ${lpo.lpo_number}`, lpo.total_amount, 'Debit', 'Materials', expenseId, 'Expense']
        );

        // 5. Update Bank Balance
        await connection.query('UPDATE bank_details SET current_balance = current_balance - ? WHERE id = 1', [lpo.total_amount]);

        await connection.commit();
        res.json({ message: 'LPO accepted, expense and bank transaction recorded.', status: 'Accepted' });
    } catch (error) {
        await connection.rollback();
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    } finally {
        connection.release();
    }
});

// Delete LPO
router.delete('/:id', async (req, res) => {
    try {
        await pool.query('DELETE FROM lpos WHERE id = ?', [req.params.id]);
        res.status(204).send();
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: 'Internal Server Error' });
    }
});

export default router;
