const express = require('express');
const router = express.Router();
const db = require('../config/database');
const CalculationService = require('../services/CalculationService');
const auth = require('../middleware/auth');
const checkRole = require('../middleware/checkRole');

router.use(auth);

// GET Orders by ClienteID
// GET Orders (Global or by Client)
router.get('/', (req, res) => {
    try {
        const { clienteId } = req.query;

        let query, params;

        if (clienteId) {
            query = `
                SELECT o.*, c.Nombre as ClienteNombre 
                FROM OrdenesRP o
                JOIN Clientes c ON o.ClienteID = c.ClienteID
                WHERE o.ClienteID = ? 
                ORDER BY o.Fecha DESC
            `;
            params = [clienteId];
        } else {
            // Global orders view
            query = `
                SELECT o.*, c.Nombre as ClienteNombre 
                FROM OrdenesRP o
                JOIN Clientes c ON o.ClienteID = c.ClienteID
                ORDER BY o.Fecha DESC
                LIMIT 50
            `;
            params = [];
        }

        const ordenes = db.prepare(query).all(...params);
        res.json(ordenes);
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// GET Single Order
router.get('/:id', (req, res) => {
    try {
        const orden = db.prepare('SELECT * FROM OrdenesRP WHERE OrdenID = ?').get(req.params.id);
        if (!orden) return res.status(404).json({ error: 'Order not found' });

        const items = db.prepare('SELECT * FROM OrdenItemsRP WHERE OrdenID = ?').all(req.params.id);
        res.json({ ...orden, items });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// POST Create Order with Items
router.post('/', checkRole('DISTRIBUIDOR', 'VENDEDOR'), (req, res) => {
    try {
        const { ClienteID, CuentaID, NumeroOrden, Fecha, TipoOrden, Impuestos, Notas, items } = req.body;

        const createOrder = db.transaction(() => {
            // 1. Create Order
            const info = db.prepare(`
                INSERT INTO OrdenesRP (ClienteID, CuentaID, NumeroOrden, Fecha, TipoOrden, Impuestos, Notas, Total, Balance)
                VALUES (?, ?, ?, ?, ?, ?, ?, 0, 0)
            `).run(ClienteID, CuentaID, NumeroOrden, Fecha, TipoOrden, Impuestos || 0, Notas);

            const ordenId = info.lastInsertRowid;

            // 2. Add Items
            if (items && Array.isArray(items)) {
                const insertItem = db.prepare(`
                    INSERT INTO OrdenItemsRP (OrdenID, Descripcion, Cantidad, PrecioUnitario, Subtotal)
                    VALUES (?, ?, ?, ?, ?)
                `);

                for (const item of items) {
                    const subtotal = item.Cantidad * item.PrecioUnitario;
                    insertItem.run(ordenId, item.Descripcion, item.Cantidad, item.PrecioUnitario, subtotal);
                }
            }

            // 3. Recalculate Totals
            CalculationService.recalculateOrder(ordenId);

            return ordenId;
        });

        const newOrdenId = createOrder();

        res.json({ id: newOrdenId, message: 'Orden creada exitosamente' });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

// POST Add Item
router.post('/:id/items', checkRole('DISTRIBUIDOR', 'VENDEDOR'), (req, res) => {
    try {
        const { Descripcion, Cantidad, PrecioUnitario } = req.body;
        const Subtotal = Cantidad * PrecioUnitario;

        db.prepare(`
            INSERT INTO OrdenItemsRP (OrdenID, Descripcion, Cantidad, PrecioUnitario, Subtotal)
            VALUES (?, ?, ?, ?, ?)
        `).run(req.params.id, Descripcion, Cantidad, PrecioUnitario, Subtotal);

        CalculationService.recalculateOrder(req.params.id);

        res.json({ message: 'Item agregado' });
    } catch (err) {
        res.status(500).json({ error: err.message });
    }
});

module.exports = router;
