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

function buildWhere(req) {
    var idEmpresa = parseInt(req.query.id_empresa);
    var buscar = req.query.buscar || '';
    var idFamilia = parseInt(req.query.id_familia) || 0;
    var conStock = req.query.con_stock === 'true';

    var conditions = ['a.id_empresa = @id_empresa', 'a.estado = 1'];
    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('a.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 (conStock) {
        conditions.push("ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) > 0");
    }

    var filtros = req.query.filtros ? JSON.parse(req.query.filtros) : null;
    if (filtros) {
        var fMap = {
            cod_articulo: 'a.cod_articulo',
            cod_vta: 'a.cod_vta',
            nom_art: 'a.nombre',
            nom_fam: 'f.nombre'
        };
        var fIdx = 0;
        for (var col in filtros) {
            var vals = Array.isArray(filtros[col]) ? filtros[col] : String(filtros[col]).split('\x01');
            var dbCol = fMap[col] || col;
            var conds = vals.map(function(v) {
                var pn = 'f' + fIdx;
                fIdx++;
                params[pn] = { type: db.sql.NVarChar(200), value: '%' + v.trim() + '%' };
                return dbCol + ' LIKE @' + pn;
            });
            conditions.push('(' + conds.join(' OR ') + ')');
        }
    }

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

router.get('/margen-articulo', function(req, res) {
    var limit = parseInt(req.query.limit);
    if (limit <= 0 || isNaN(limit)) limit = null;
    var page = parseInt(req.query.page) || 1;
    var built = buildWhere(req);

    var sort = req.query.sort || 'margen_porc';
    var order = (req.query.order || 'DESC').toUpperCase();
    var orderMap = {
        'cod_articulo': 'sub.cod_articulo',
        'nom_art': 'sub.nom_art',
        'nom_fam': 'sub.nom_fam',
        'precio_venta': 'sub.precio_venta',
        'costo_promedio': 'sub.costo_promedio',
        'margen_unit': 'sub.margen_unit',
        'margen_porc': 'sub.margen_porc',
        'stock': 'sub.stock',
        'valor_venta': 'sub.valor_venta',
        'valor_stock': 'sub.valor_stock',
        'margen_total': 'sub.margen_total'
    };
    var sortCol = orderMap[sort] || 'sub.margen_porc';
    var orderSql = sortCol + ' ' + (order === 'DESC' ? 'DESC' : 'ASC');

    var baseSql = `
        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(a.precio_venta, 0) AS precio_venta,
                ISNULL(a.costo_promedio, 0) AS costo_promedio,
                ISNULL(a.precio_venta, 0) - ISNULL(a.costo_promedio, 0) AS margen_unit,
                CASE WHEN ISNULL(a.precio_venta, 0) > 0
                     THEN (ISNULL(a.precio_venta, 0) - ISNULL(a.costo_promedio, 0)) / ISNULL(a.precio_venta, 0) * 100
                     ELSE 0 END AS margen_porc,
                ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) AS stock,
                ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * ISNULL(a.precio_venta, 0) AS valor_venta,
                ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * ISNULL(a.costo_promedio, 0) AS valor_stock,
                ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * (ISNULL(a.precio_venta, 0) - ISNULL(a.costo_promedio, 0)) AS margen_total
            FROM articulos a
            LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia
            WHERE ${built.where}
        ) sub
        ORDER BY ${orderSql}
    `;

    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 ${built.where}
    `;

    var params = Object.assign({}, built.params);

    var conn = db.getPool();
    conn.then(function(pool) {
        if (limit !== null) {
            var offset = (page - 1) * limit;
            var dataSql = baseSql + ' OFFSET @offset ROWS FETCH NEXT @limit ROWS ONLY';
            var allParams = Object.assign({}, params);
            allParams.offset = { type: db.sql.Int, value: offset };
            allParams.limit = { type: db.sql.Int, value: limit };

            var countReq = new db.sql.Request(pool);
            Object.keys(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('[MARGEN] Count error:', err.message); return res.json({ ok: false, error: err.message }); }
                var total = countResult.recordset[0].total;
                var totalPaginas = Math.ceil(total / limit);

                var dataReq = new db.sql.Request(pool);
                Object.keys(allParams).forEach(function(k) {
                    dataReq.input(k, allParams[k].type, allParams[k].value);
                });
                dataReq.query(dataSql, function(err2, result) {
                    if (err2) { console.error('[MARGEN] 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 });
                });
            });
        } else {
            var dataReq = new db.sql.Request(pool);
            Object.keys(params).forEach(function(k) {
                dataReq.input(k, params[k].type, params[k].value);
            });
            dataReq.query(baseSql, function(err2, result) {
                if (err2) { console.error('[MARGEN] Query error:', err2.message); return res.json({ ok: false, error: err2.message }); }
                res.json({ ok: true, data: result.recordset || [], total: 0, pagina: 1, totalPaginas: 1, limit: 0 });
            });
        }
    }).catch(function(err) {
        res.json({ ok: false, error: err.message });
    });
});

router.get('/margen-articulo/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.precio_venta, 0)) AS valor_venta_total,
            SUM(ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * ISNULL(a.costo_promedio, 0)) AS valor_stock_total,
            SUM(ISNULL(COALESCE(a.stock, a.stock_actual, 0), 0) * (ISNULL(a.precio_venta, 0) - ISNULL(a.costo_promedio, 0))) AS margen_total,
            SUM(CASE WHEN ISNULL(a.precio_venta, 0) > 0 THEN 1 ELSE 0 END) AS con_precio,
            SUM(CASE WHEN ISNULL(a.precio_venta, 0) = 0 THEN 1 ELSE 0 END) AS sin_precio
        FROM articulos a
        LEFT JOIN familias f ON a.id_empresa = f.id_empresa AND a.id_familia = f.id_familia
        WHERE ${built.where}
    `;

    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(sql, function(err, result) {
            if (err) return res.json({ ok: false, error: err.message });
            var r = result.recordset[0] || {};
            var margenP = (r.valor_venta_total || 0) > 0 ? (r.margen_total || 0) / r.valor_venta_total * 100 : 0;
            res.json({
                ok: true,
                total_articulos: r.total_articulos || 0,
                valor_venta_total: r.valor_venta_total || 0,
                valor_stock_total: r.valor_stock_total || 0,
                margen_total: r.margen_total || 0,
                margen_porc: margenP,
                con_precio: r.con_precio || 0,
                sin_precio: r.sin_precio || 0
            });
        });
    }).catch(function(err) {
        res.json({ ok: false, error: err.message });
    });
});

router.get('/margen-articulo/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 = 'a.id_empresa = @id_empresa AND a.estado = 1 AND f.nombre IS NOT NULL';
    var params = { id_empresa: { type: db.sql.Int, value: idEmpresa } };
    if (idSucursal) {
        whereExtra += ' AND a.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 articulos a
        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;
