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'
    };
    return map[col] || 'a.nombre';
}

function buildWhere(req) {
    var idEmpresa = parseInt(req.query.id_empresa);
    var buscar = req.query.buscar || '';
    var idFamilia = parseInt(req.query.id_familia) || 0;
    var fechaDesde = req.query.fecha_desde || '';
    var fechaHasta = req.query.fecha_hasta || '';
    var soloSinRotacion = req.query.solo_sin_rotacion === 'true';

    var conditions = ['m.id_empresa = @id_empresa'];
    var params = { id_empresa: { type: db.sql.Int, value: idEmpresa } };
    var idSucursal = req.query.id_sucursal ? parseInt(req.query.id_sucursal) : null;

    if (idSucursal) {
        conditions.push('m.id_sucursal = @id_sucursal');
        params.id_sucursal = { type: db.sql.Int, value: idSucursal };
    }

    if (buscar) {
        conditions.push("(a.cod_articulo LIKE @buscar OR a.nombre LIKE @buscar)");
        params.buscar = { type: db.sql.NVarChar, value: '%' + buscar + '%' };
    }
    if (idFamilia > 0) {
        conditions.push("a.id_familia = @id_familia");
        params.id_familia = { type: db.sql.Int, value: idFamilia };
    }
    if (fechaDesde) {
        conditions.push("m.fecha_movimiento >= @fecha_desde");
        params.fecha_desde = { type: db.sql.Date, value: fechaDesde };
    }
    if (fechaHasta) {
        conditions.push("m.fecha_movimiento <= @fecha_hasta");
        params.fecha_hasta = { type: db.sql.Date, value: fechaHasta };
    }

    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) {
                        conditions.push(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(',');
                        conditions.push(getColSql(col) + ' IN (' + placeholders + ')');
                        vals.forEach(function(v, i) {
                            params['filtro_' + col + '_' + i] = { type: db.sql.NVarChar(200), value: v };
                        });
                    }
                }
            }
        } catch(e) {}
    }

    return { where: conditions.join(' AND '), params: params, soloSinRotacion: soloSinRotacion };
}

router.get('/rotacion-inventario', function(req, res) {
    var limit = parseInt(req.query.limit) || 50;
    var page = parseInt(req.query.page) || 1;
    var offset = (page - 1) * limit;
    var built = buildWhere(req);

    var sort = req.query.sort || 'rotacion';
    var order = (req.query.order || 'DESC').toUpperCase();
    var orderMap = {
        'cod_articulo': 'sub.cod_articulo',
        'nom_art': 'sub.nom_art',
        'nom_fam': 'sub.nom_fam',
        'stock_actual': 'sub.stock_actual',
        'salidas_cant': 'sub.salidas_cant',
        'salidas_valor': 'sub.salidas_valor',
        'rotacion': 'sub.rotacion',
        'dias_stock': 'sub.dias_stock'
    };
    var sortCol = orderMap[sort] || 'sub.rotacion';
    var orderSql = sortCol + ' ' + (order === 'DESC' ? 'DESC' : 'ASC');

    var havingClause = built.soloSinRotacion ? ' HAVING ISNULL(SUM(CASE WHEN m.tipo = \'S\' THEN m.costo_total ELSE 0 END), 0) = 0' : '';

    var dataSql = `
        SELECT sub.* FROM (
            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(COALESCE(a.stock, a.stock_actual, 0), 0) AS stock_actual,
                ISNULL(a.costo_promedio, 0) AS costo_promedio,
                ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * ISNULL(a.costo_promedio, 0) AS valor_stock,
                ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.cantidad ELSE 0 END), 0) AS salidas_cant,
                ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.costo_total ELSE 0 END), 0) AS salidas_valor,
                ISNULL(SUM(CASE WHEN m.tipo = 'E' THEN m.cantidad ELSE 0 END), 0) AS entradas_cant,
                ISNULL(SUM(CASE WHEN m.tipo = 'E' THEN m.costo_total ELSE 0 END), 0) AS entradas_valor,
                CASE WHEN ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) > 0
                     THEN ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.costo_total ELSE 0 END), 0) /
                          (ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * ISNULL(a.costo_promedio, 0))
                     ELSE 0 END AS rotacion,
                CASE WHEN ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.costo_total ELSE 0 END), 0) > 0
                     AND ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) > 0
                     THEN 365.0 /
                          (ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.costo_total ELSE 0 END), 0) /
                           (ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * ISNULL(a.costo_promedio, 0)))
                     ELSE 0 END AS dias_stock
            FROM movimiento_almacen m
            INNER JOIN articulos a ON m.id_articulo = a.id_articulo AND m.id_empresa = a.id_empresa
            LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia
            WHERE ${built.where} AND a.estado = 1
            GROUP BY a.id_articulo, a.cod_articulo, a.cod_vta, a.nombre, f.nombre, a.id_familia,
                     COALESCE(a.stock, a.stock_actual, 0), a.costo_promedio
            ${havingClause}
        ) sub
        ORDER BY ${orderSql}
        OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY
    `;

    var countSql = `
        SELECT COUNT(*) AS total FROM (
            SELECT a.cod_articulo
            FROM movimiento_almacen m
            INNER JOIN articulos a ON m.id_articulo = a.id_articulo AND m.id_empresa = a.id_empresa
            LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia
            WHERE ${built.where} AND a.estado = 1
            GROUP BY a.cod_articulo, a.nombre, a.id_familia,
                     COALESCE(a.stock, a.stock_actual, 0), a.costo_promedio
            ${havingClause}
        ) cnt
    `;

    var params = Object.assign({}, built.params);
    params.offset = { type: db.sql.Int, value: offset };
    params.limit = { type: db.sql.Int, value: limit };

    var conn = db.getPool();
    conn.then(function(pool) {
        var countReq = new db.sql.Request(pool);
        Object.keys(built.params).forEach(function(k) {
            countReq.input(k, built.params[k].type, built.params[k].value);
        });
        countReq.query(countSql, function(err, countResult) {
            if (err) {
                console.error('[ROTACION] Count error:', err.message);
                return res.json({ ok: false, error: err.message });
            }
            var total = countResult.recordset[0].total;
            var totalPaginas = limit > 0 ? Math.ceil(total / limit) : 1;

            var dataReq = new db.sql.Request(pool);
            Object.keys(params).forEach(function(k) {
                dataReq.input(k, params[k].type, params[k].value);
            });
            dataReq.query(dataSql, function(err2, result) {
                if (err2) {
                    console.error('[ROTACION] Query error:', err2.message);
                    return res.json({ ok: false, error: err2.message });
                }
                res.json({
                    ok: true,
                    data: result.recordset || [],
                    total: total,
                    pagina: page,
                    totalPaginas: totalPaginas,
                    limit: limit
                });
            });
        });
    }).catch(function(err) {
        res.json({ ok: false, error: err.message });
    });
});

router.get('/rotacion-inventario/totales', function(req, res) {
    var built = buildWhere(req);

    var sql = `
        SELECT
            COUNT(*) AS total_articulos,
            SUM(ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * ISNULL(a.costo_promedio, 0)) AS valor_total_stock,
            SUM(ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.costo_total ELSE 0 END), 0)) AS total_salidas_valor,
            SUM(ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.cantidad ELSE 0 END), 0)) AS total_salidas_cant
        FROM movimiento_almacen m
        INNER JOIN articulos a ON m.id_articulo = a.id_articulo AND m.id_empresa = a.id_empresa
        LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia
        WHERE ${built.where} AND a.estado = 1
        GROUP BY a.cod_articulo, COALESCE(a.stock, a.stock_actual, 0), a.costo_promedio
    `;

    var summarySql = `
        SELECT
            COUNT(*) AS total_articulos,
            SUM(sub.valor_stock) AS valor_total_stock,
            SUM(sub.salidas_valor) AS total_salidas_valor,
            SUM(sub.salidas_cant) AS total_salidas_cant
        FROM (
            SELECT
                a.cod_articulo,
                ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * ISNULL(a.costo_promedio, 0) AS valor_stock,
                ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.costo_total ELSE 0 END), 0) AS salidas_valor,
                ISNULL(SUM(CASE WHEN m.tipo = 'S' THEN m.cantidad ELSE 0 END), 0) AS salidas_cant
            FROM movimiento_almacen m
            INNER JOIN articulos a ON m.id_articulo = a.id_articulo AND m.id_empresa = a.id_empresa
            WHERE ${built.where} AND a.estado = 1
            GROUP BY a.cod_articulo, COALESCE(a.stock, a.stock_actual, 0), a.costo_promedio
        ) sub
    `;

    var conn = db.getPool();
    conn.then(function(pool) {
        var req2 = new db.sql.Request(pool);
        Object.keys(built.params).forEach(function(k) {
            req2.input(k, built.params[k].type, built.params[k].value);
        });
        req2.query(summarySql, function(err, result) {
            if (err) return res.json({ ok: false, error: err.message });
            var r = result.recordset[0] || {};
            var rotacion = (r.valor_total_stock || 0) > 0 ? (r.total_salidas_valor || 0) / r.valor_total_stock : 0;
            res.json({
                ok: true,
                total_articulos: r.total_articulos || 0,
                valor_total_stock: r.valor_total_stock || 0,
                total_salidas_valor: r.total_salidas_valor || 0,
                total_salidas_cant: r.total_salidas_cant || 0,
                rotacion_global: rotacion,
                dias_stock_global: rotacion > 0 ? Math.round(365 / rotacion) : 0
            });
        });
    }).catch(function(err) {
        res.json({ ok: false, error: err.message });
    });
});

router.get('/rotacion-inventario/familias', function(req, res) {
    var idEmpresa = parseInt(req.query.id_empresa);
    if (!idEmpresa) return res.json({ ok: true, data: [] });
    var idSucursal = req.query.id_sucursal ? parseInt(req.query.id_sucursal) : null;

    var whereExtra = 'm.id_empresa = @id_empresa AND f.nombre IS NOT NULL';
    var params = { id_empresa: { type: db.sql.Int, value: idEmpresa } };
    if (idSucursal) {
        whereExtra += ' AND m.id_sucursal = @id_sucursal';
        params.id_sucursal = { type: db.sql.Int, value: idSucursal };
    }

    var sql = `
        SELECT DISTINCT f.id_familia, f.nombre AS nom_fam
        FROM movimiento_almacen m
        INNER JOIN articulos a ON m.id_articulo = a.id_articulo AND m.id_empresa = a.id_empresa
        LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia
        WHERE ${whereExtra}
        ORDER BY f.nombre
    `;

    var conn = db.getPool();
    conn.then(function(pool) {
        var req2 = new db.sql.Request(pool);
        Object.keys(params).forEach(function(k) {
            req2.input(k, params[k].type, params[k].value);
        });
        req2.query(sql, function(err, result) {
            if (err) return res.json({ ok: false, error: err.message });
            res.json({ ok: true, data: result.recordset || [] });
        });
    }).catch(function(err) {
        res.json({ ok: false, error: err.message });
    });
});

module.exports = router;
