const express = require('express');
const router = express.Router();
const db = require('../config/db');

function getColSql(col) {
    var map = {
        'cod_articulo': 'a.cod_articulo',
        'cod_vta': 'a.cod_vta',
        'nom_art': 'a.nombre',
        'nom_fam': 'f.nombre',
        'estado_alerta': "CASE WHEN ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) < ISNULL(a.stock_minimo, 0) THEN 'CRITICO' WHEN ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) = ISNULL(a.stock_minimo, 0) THEN 'REGULAR' ELSE 'SUFICIENTE' END"
    };
    return map[col] || 'a.nombre';
}

function buildWhere(req) {
    var idEmpresa = parseInt(req.query.id_empresa);
    var buscar = req.query.buscar || '';
    var idFamilia = req.query.id_familia || '';
    var soloAlertas = req.query.solo_alertas === 'true';
    var idSucursal = req.query.id_sucursal ? parseInt(req.query.id_sucursal) : null;

    var where = 'WHERE a.id_empresa = @id_empresa AND a.estado = 1';
    var params = {
        id_empresa: { type: db.sql.Int, value: idEmpresa }
    };

    if (idSucursal) {
        where += ' AND a.id_sucursal = @id_sucursal';
        params.id_sucursal = { type: db.sql.Int, value: idSucursal };
    }

    if (idFamilia) {
        where += ' AND a.id_familia = @id_familia';
        params.id_familia = { type: db.sql.Int, value: parseInt(idFamilia) };
    }

    if (buscar) {
        where += ' AND (a.cod_articulo LIKE @buscar OR a.cod_vta LIKE @buscar OR a.nombre LIKE @buscar)';
        params.buscar = { type: db.sql.NVarChar(100), value: '%' + buscar + '%' };
    }

    if (soloAlertas) {
        where += ' AND ISNULL(a.stock_minimo, 0) > 0 AND ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) <= ISNULL(a.stock_minimo, 0)';
    }

    if (req.query.filtros) {
        try {
            var filtros = JSON.parse(req.query.filtros);
            for (var col in filtros) {
                if (filtros.hasOwnProperty(col) && filtros[col]) {
                    var vals = (Array.isArray(filtros[col]) ? filtros[col] : String(filtros[col]).split('\x01')).map(function(v) { return v.trim(); });
                    if (vals.length === 1) {
                        where += ' AND ' + getColSql(col) + ' = @filtro_' + col;
                        params['filtro_' + col] = { type: db.sql.NVarChar(200), value: vals[0] };
                    } else {
                        var placeholders = vals.map(function(_, i) { return '@filtro_' + col + '_' + i; }).join(',');
                        where += ' AND ' + getColSql(col) + ' IN (' + placeholders + ')';
                        vals.forEach(function(v, i) {
                            params['filtro_' + col + '_' + i] = { type: db.sql.NVarChar(200), value: v };
                        });
                    }
                }
            }
        } catch(e) {}
    }

    return { where: where, params: params };
}

router.get('/stock-minimo-actual', async function(req, res) {
    try {
        var idEmpresa = parseInt(req.query.id_empresa);
        if (!idEmpresa) return res.status(400).json({ ok: false, mensaje: 'id_empresa es requerido' });

        var page = parseInt(req.query.page) || 1;
        var limit = req.query.limit !== undefined && req.query.limit !== '' ? parseInt(req.query.limit) : 50;
        if (isNaN(limit)) limit = 50;
        var offset = (page - 1) * limit;
        var sort = req.query.sort || 'estado_alerta';
        var order = req.query.order === 'DESC' ? 'DESC' : 'ASC';

        var built = buildWhere(req);
        var where = built.where;
        var params = built.params;

        var sortMap = {
            'cod_articulo': 'a.cod_articulo',
            'cod_vta': 'a.cod_vta',
            'nom_art': 'a.nombre',
            'nom_fam': 'f.nombre',
            'stock_minimo': 'ISNULL(a.stock_minimo, 0)',
            'stock': 'ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0)',
            'diferencia': 'ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) - ISNULL(a.stock_minimo, 0)',
        'estado_alerta': "CASE WHEN ISNULL(a.stock_minimo, 0) = 0 THEN 'SIN_MINIMO' WHEN ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) < ISNULL(a.stock_minimo, 0) THEN 'CRITICO' WHEN ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) = ISNULL(a.stock_minimo, 0) THEN 'REGULAR' ELSE 'SUFICIENTE' END"
        };
        var orderSql = sortMap[sort] || 'estado_alerta';
        if (order === 'DESC') orderSql += ' DESC'; else orderSql += ' ASC';

        var countSql = `SELECT COUNT(*) AS total FROM articulos a LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia ${where}`;
        var countResult = await db.query(countSql, params);
        var total = countResult[0] ? countResult[0].total : 0;

        var pagination = limit > 0 ? 'OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY' : '';
        if (limit > 0) {
            params.offset = { type: db.sql.Int, value: offset };
            params.limit = { type: db.sql.Int, value: limit };
        }

        var sql = `
            SELECT
                a.id_articulo,
                a.cod_articulo,
                a.cod_vta,
                a.nombre AS nom_art,
                ISNULL(f.nombre, '') AS nom_fam,
                a.id_familia,
                ISNULL(a.stock_minimo, 0) AS stock_minimo,
                ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) AS stock,
                (ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) - ISNULL(a.stock_minimo, 0)) AS diferencia,
                CASE
                    WHEN ISNULL(a.stock_minimo, 0) = 0 THEN 'SIN_MINIMO'
                    WHEN ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) < ISNULL(a.stock_minimo, 0) THEN 'CRITICO'
                    WHEN ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) = ISNULL(a.stock_minimo, 0) THEN 'REGULAR'
                    ELSE 'SUFICIENTE'
                END AS estado_alerta
            FROM articulos a
            LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia
            ${where}
            ORDER BY ${orderSql}
            ${pagination}
        `;

        var result = await db.query(sql, params);

        var totalPaginas = limit > 0 ? Math.ceil(total / limit) : 1;
        res.json({ ok: true, data: result, total: total, totalPaginas: totalPaginas });

    } catch (err) {
        console.error('[STOCK-MINIMO] Error:', err.message);
        res.status(500).json({ ok: false, mensaje: 'Error al obtener stock mínimo: ' + err.message });
    }
});

router.get('/stock-minimo-actual/totales', async function(req, res) {
    try {
        var idEmpresa = parseInt(req.query.id_empresa);
        if (!idEmpresa) return res.status(400).json({ ok: false, mensaje: 'id_empresa es requerido' });

        var built = buildWhere(req);
        var where = built.where;
        var params = built.params;

        var sql = `
            SELECT
                COUNT(*) AS total_articulos,
                SUM(CASE WHEN ISNULL(a.stock_minimo, 0) = 0 THEN 1 ELSE 0 END) AS sin_minimo,
                SUM(CASE WHEN ISNULL(a.stock_minimo, 0) > 0 AND ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) < ISNULL(a.stock_minimo, 0) THEN 1 ELSE 0 END) AS criticos,
                SUM(CASE WHEN ISNULL(a.stock_minimo, 0) > 0 AND ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) = ISNULL(a.stock_minimo, 0) THEN 1 ELSE 0 END) AS regular,
                SUM(CASE WHEN ISNULL(a.stock_minimo, 0) > 0 AND ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) > ISNULL(a.stock_minimo, 0) THEN 1 ELSE 0 END) AS suficiente,
                SUM(ISNULL(a.stock_minimo, 0)) AS total_minimo,
                SUM(ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0)) AS total_stock
            FROM articulos a
            LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia
            ${where}
        `;
        var result = await db.query(sql, params);
        var row = result[0] || {};
        res.json({
            ok: true,
            total_articulos: row.total_articulos || 0,
            sin_minimo: row.sin_minimo || 0,
            criticos: row.criticos || 0,
            regular: row.regular || 0,
            suficiente: row.suficiente || 0,
            total_minimo: row.total_minimo || 0,
            total_stock: row.total_stock || 0
        });
    } catch (err) {
        console.error('[STOCK-MINIMO] Totales error:', err.message);
        res.status(500).json({ ok: false, mensaje: err.message });
    }
});

router.get('/stock-minimo-actual/familias', async function(req, res) {
    try {
        var idEmpresa = parseInt(req.query.id_empresa);
        if (!idEmpresa) return res.status(400).json({ ok: false, mensaje: 'id_empresa es requerido' });
        var result = await db.query(
            `SELECT id_familia, nombre FROM familias WHERE id_empresa = @id_empresa AND estado = 1 ORDER BY nombre`,
            { id_empresa: { type: db.sql.Int, value: idEmpresa } }
        );
        res.json({ ok: true, data: result });
    } catch (err) {
        res.status(500).json({ ok: false, mensaje: err.message });
    }
});

module.exports = router;
