const { Op } = require('sequelize');
const moment = require('moment-timezone');
const PlanillaAporte = require('../models/planilla_aporte');

let tablaInicializada = false;

async function asegurarTabla() {
    if (tablaInicializada) return;
    await PlanillaAporte.sync();
    tablaInicializada = true;
}

function texto(value) {
    return String(value === undefined || value === null ? '' : value).trim();
}

function numero(value) {
    if (value === undefined || value === null || value === '') return null;
    if (typeof value === 'number') return Number.isFinite(value) ? value : null;
    const limpio = String(value).replace(/\s/g, '').replace(/,/g, '');
    const convertido = Number(limpio);
    return Number.isFinite(convertido) ? convertido : null;
}

function normalizarCedula(value) {
    return texto(value).replace(/\D/g, '');
}

function normalizarEstado(value) {
    return texto(value).replace(/\s+/g, ' ');
}

function valorClaveNumero(value) {
    const parsed = numero(value);
    return parsed === null ? '' : parsed.toFixed(2);
}

function claveRegistro(row) {
    return [
        texto(row.PERIODO).toUpperCase(),
        normalizarEstado(row.ESTADO_PLANILLA).toUpperCase(),
        texto(row.SUB_TIPO).toUpperCase(),
        normalizarCedula(row.CEDULA),
        texto(row.NOMBRE).toUpperCase(),
        texto(row.REGIMEN_TRABAJO).toUpperCase(),
        texto(row.NOVEDAD).toUpperCase(),
        valorClaveNumero(row.SUELDO),
        valorClaveNumero(row.DIAS),
        valorClaveNumero(row.PATRONAL),
        valorClaveNumero(row.INDIVIDUAL),
        valorClaveNumero(row.APORTE_ADICIONAL),
        valorClaveNumero(row.CESANTIA),
        valorClaveNumero(row.PORCENTAJE_CCC),
        valorClaveNumero(row.VALOR_CCC),
        valorClaveNumero(row.TOTAL_APORTE),
        valorClaveNumero(row.ADICIONAL_TIEMPO_PARCIAL),
        texto(row.JUSTIFICACION_PAGO).toUpperCase(),
        texto(row.MARCADO_PLANILLA).toUpperCase(),
        texto(row.HOJA_ORIGEN).toUpperCase()
    ].join('|');
}

function ordenarPeriodos(a, b) {
    const valorA = periodoParaFecha(a);
    const valorB = periodoParaFecha(b);

    if (valorA !== null && valorB !== null) {
        return valorB - valorA;
    }

    return String(b || '').localeCompare(String(a || ''));
}

function periodoParaFecha(periodo) {
    const textoPeriodo = texto(periodo).toLowerCase();
    const match = textoPeriodo.match(/^([a-z]{3})[-/ ]?(\d{2,4})$/);
    if (!match) return null;

    const meses = {
        ene: 0, jan: 0,
        feb: 1,
        mar: 2,
        abr: 3, apr: 3,
        may: 4,
        jun: 5,
        jul: 6,
        ago: 7, aug: 7,
        sep: 8,
        oct: 9,
        nov: 10,
        dic: 11, dec: 11
    };

    const mes = meses[match[1]];
    if (mes === undefined) return null;

    let anio = Number(match[2]);
    if (!Number.isFinite(anio)) return null;
    if (anio < 100) anio += 2000;

    return new Date(anio, mes, 1).getTime();
}

function construirWhere(body = {}) {
    const where = { ESTADO: true };
    if (body.periodo) where.PERIODO = texto(body.periodo);
    if (body.estadoPlanilla) where.ESTADO_PLANILLA = texto(body.estadoPlanilla);
    if (body.hoja) where.HOJA_ORIGEN = texto(body.hoja);

    const buscar = texto(body.palabra);
    if (buscar) {
        where[Op.or] = [
            { PERIODO: { [Op.substring]: buscar } },
            { CEDULA: { [Op.substring]: buscar } },
            { NOMBRE: { [Op.substring]: buscar } },
            { NOVEDAD: { [Op.substring]: buscar } },
            { REGIMEN_TRABAJO: { [Op.substring]: buscar } }
        ];
    }

    return where;
}

function normalizarFila(row, loteCarga, usuarioCarga) {
    return {
        PERIODO: texto(row.periodo),
        ESTADO_PLANILLA: normalizarEstado(row.estadoPlanilla),
        SUB_TIPO: texto(row.subTipo),
        CEDULA: normalizarCedula(row.cedula),
        NOMBRE: texto(row.nombre),
        REGIMEN_TRABAJO: texto(row.regimenTrabajo),
        NOVEDAD: texto(row.novedad),
        SUELDO: numero(row.sueldo),
        DIAS: numero(row.dias),
        PATRONAL: numero(row.patronal),
        INDIVIDUAL: numero(row.individual),
        APORTE_ADICIONAL: numero(row.aporteAdicional),
        CESANTIA: numero(row.cesantia),
        PORCENTAJE_CCC: numero(row.porcentajeCcc),
        VALOR_CCC: numero(row.valorCcc),
        TOTAL_APORTE: numero(row.totalAporte),
        ADICIONAL_TIEMPO_PARCIAL: numero(row.adicionalTiempoParcial),
        JUSTIFICACION_PAGO: texto(row.justificacionPago),
        MARCADO_PLANILLA: texto(row.marcadoPlanilla),
        HOJA_ORIGEN: texto(row.hojaOrigen) || 'HOJA_1',
        LOTE_CARGA: loteCarga,
        FECHA_CARGA: moment().tz('America/Guayaquil').format('YYYY-MM-DD HH:mm:ss'),
        ID_USUARIO_CARGA: usuarioCarga,
        ESTADO: true
    };
}

function construirPayloadManual(row, usuarioCarga) {
    return normalizarFila(row, `MANUAL-${moment().tz('America/Guayaquil').format('YYYYMMDDHHmmss')}`, usuarioCarga);
}

async function cargarPlanillaAportes(req, res) {
    try {
        await asegurarTabla();
        const rows = Array.isArray(req.body.rows) ? req.body.rows : [];
        if (!rows.length) return res.status(400).send({ message: 'No se recibieron filas para procesar.' });

        const loteCarga = moment().tz('America/Guayaquil').format('YYYYMMDDHHmmss');
        const usuarioCarga = req.user && req.user.sub ? req.user.sub : null;
        const normalizadas = rows
            .map((row) => normalizarFila(row, loteCarga, usuarioCarga))
            .filter((row) => row.PERIODO || row.CEDULA || row.NOMBRE);

        if (!normalizadas.length) return res.status(400).send({ message: 'El archivo no contiene filas válidas.' });

        const unicasArchivo = [];
        const clavesArchivo = new Set();
        for (const row of normalizadas) {
            const clave = claveRegistro(row);
            if (clavesArchivo.has(clave)) continue;
            clavesArchivo.add(clave);
            unicasArchivo.push(row);
        }

        const periodos = [...new Set(unicasArchivo.map((row) => row.PERIODO).filter(Boolean))];
        const hojas = [...new Set(unicasArchivo.map((row) => row.HOJA_ORIGEN).filter(Boolean))];

        let existentes = [];
        if (periodos.length || hojas.length) {
            const whereExistentes = { ESTADO: true };
            if (periodos.length) whereExistentes.PERIODO = { [Op.in]: periodos };
            if (hojas.length) whereExistentes.HOJA_ORIGEN = { [Op.in]: hojas };

            existentes = await PlanillaAporte.findAll({
                where: whereExistentes,
                raw: true,
                attributes: [
                    'PERIODO', 'ESTADO_PLANILLA', 'SUB_TIPO', 'CEDULA', 'NOMBRE', 'REGIMEN_TRABAJO', 'NOVEDAD',
                    'SUELDO', 'DIAS', 'PATRONAL', 'INDIVIDUAL', 'APORTE_ADICIONAL', 'CESANTIA', 'PORCENTAJE_CCC',
                    'VALOR_CCC', 'TOTAL_APORTE', 'ADICIONAL_TIEMPO_PARCIAL', 'JUSTIFICACION_PAGO',
                    'MARCADO_PLANILLA', 'HOJA_ORIGEN'
                ]
            });
        }

        const clavesExistentes = new Set(existentes.map((row) => claveRegistro(row)));
        const nuevos = unicasArchivo.filter((row) => !clavesExistentes.has(claveRegistro(row)));

        if (nuevos.length) {
            await PlanillaAporte.bulkCreate(nuevos);
        }

        return res.status(200).send({
            message: `Planilla procesada correctamente. ${nuevos.length} registros nuevos insertados, ${unicasArchivo.length - nuevos.length} ya existían.`,
            data: { totalRecibido: normalizadas.length, totalUnicosArchivo: unicasArchivo.length, totalInsertados: nuevos.length, totalExistentes: unicasArchivo.length - nuevos.length, hojas, periodos, loteCarga }
        });
    } catch (e) {
        console.log('Error cargarPlanillaAportes:', e);
        return res.status(500).send({ message: e.message });
    }
}

async function crearPlanillaAporte(req, res) {
    try {
        await asegurarTabla();
        const usuarioCarga = req.user && req.user.sub ? req.user.sub : null;
        const payload = construirPayloadManual(req.body || {}, usuarioCarga);
        if (!payload.PERIODO || !payload.NOMBRE) {
            return res.status(400).send({ message: 'Periodo y nombre son obligatorios.' });
        }
        const creado = await PlanillaAporte.create(payload);
        return res.status(200).send({ message: 'Registro creado correctamente.', data: creado });
    } catch (e) {
        console.log('Error crearPlanillaAporte:', e);
        return res.status(500).send({ message: e.message });
    }
}

async function actualizarPlanillaAporte(req, res) {
    try {
        await asegurarTabla();
        const existente = await PlanillaAporte.findOne({ where: { ID_PLANILLA_APORTE: req.params.id, ESTADO: true } });
        if (!existente) return res.status(404).send({ message: 'Registro no encontrado.' });

        const usuarioCarga = req.user && req.user.sub ? req.user.sub : null;
        const payload = construirPayloadManual(req.body || {}, usuarioCarga);
        payload.LOTE_CARGA = existente.LOTE_CARGA || payload.LOTE_CARGA;

        await PlanillaAporte.update(payload, { where: { ID_PLANILLA_APORTE: req.params.id } });
        return res.status(200).send({ message: 'Registro actualizado correctamente.' });
    } catch (e) {
        console.log('Error actualizarPlanillaAporte:', e);
        return res.status(500).send({ message: e.message });
    }
}

async function eliminarPlanillaAporte(req, res) {
    try {
        await asegurarTabla();
        const eliminado = await PlanillaAporte.destroy({ where: { ID_PLANILLA_APORTE: req.params.id } });
        if (!eliminado) return res.status(404).send({ message: 'Registro no encontrado.' });
        return res.status(200).send({ message: 'Registro eliminado correctamente.' });
    } catch (e) {
        console.log('Error eliminarPlanillaAporte:', e);
        return res.status(500).send({ message: e.message });
    }
}

async function listarPlanillaAportes(req, res) {
    try {
        await asegurarTabla();
        const limitSolicitado = Number(req.body.tamanioLote || 100);
        const limit = !Number.isFinite(limitSolicitado) || limitSolicitado <= 0 ? 100 : Math.min(limitSolicitado, 1000);
        const pagina = Number(req.body.numeroPagina || 0);
        const offset = pagina * limit;

        const data = await PlanillaAporte.findAll({
            where: construirWhere(req.body),
            offset,
            limit,
            order: [['PERIODO', 'DESC'], ['HOJA_ORIGEN', 'ASC'], ['NOMBRE', 'ASC']]
        });

        return res.status(200).send({ data });
    } catch (e) {
        console.log('Error listarPlanillaAportes:', e);
        return res.status(500).send({ message: e.message });
    }
}

async function contarPlanillaAportes(req, res) {
    try {
        await asegurarTabla();
        const total = await PlanillaAporte.count({ where: construirWhere(req.body) });
        return res.status(200).send({ data: total });
    } catch (e) {
        console.log('Error contarPlanillaAportes:', e);
        return res.status(500).send({ message: e.message });
    }
}

async function resumenPlanillaAportes(req, res) {
    try {
        await asegurarTabla();
        const rows = await PlanillaAporte.findAll({
            where: construirWhere(req.body),
            raw: true,
            order: [['PERIODO', 'DESC']]
        });

        const periodos = [...new Set(rows.map((row) => row.PERIODO).filter(Boolean))].sort(ordenarPeriodos);
        const estadosPlanilla = [...new Set(rows.map((row) => normalizarEstado(row.ESTADO_PLANILLA)).filter(Boolean))].sort();
        const hojas = [...new Set(rows.map((row) => row.HOJA_ORIGEN).filter(Boolean))].sort();

        const totalPatronal = rows.reduce((acc, row) => acc + Number(row.PATRONAL || 0), 0);
        const totalIndividual = rows.reduce((acc, row) => acc + Number(row.INDIVIDUAL || 0), 0);
        const totalAporte = rows.reduce((acc, row) => acc + Number(row.TOTAL_APORTE || 0), 0);
        const ultimaCarga = rows[0] ? rows[0].FECHA_CARGA : null;
        const ultimoLote = rows[0] ? rows[0].LOTE_CARGA : null;
        const rowsUltimoLote = ultimoLote ? rows.filter((row) => row.LOTE_CARGA === ultimoLote) : rows;
        const periodosUltimoLote = [...new Set(rowsUltimoLote.map((row) => row.PERIODO).filter(Boolean))].sort(ordenarPeriodos);
        const periodoMasReciente = periodosUltimoLote.length ? periodosUltimoLote[0] : null;
        const rowsPeriodoActual = periodoMasReciente ? rowsUltimoLote.filter((row) => row.PERIODO === periodoMasReciente) : rowsUltimoLote;
        const empleados = new Set(rowsPeriodoActual.map((row) => normalizarCedula(row.CEDULA)).filter(Boolean));

        return res.status(200).send({
            data: {
                periodos,
                estadosPlanilla,
                hojas,
                totalRegistros: rows.length,
                totalPatronal: Number(totalPatronal.toFixed(2)),
                totalIndividual: Number(totalIndividual.toFixed(2)),
                totalAporte: Number(totalAporte.toFixed(2)),
                totalEmpleados: empleados.size,
                ultimaCarga
            }
        });
    } catch (e) {
        console.log('Error resumenPlanillaAportes:', e);
        return res.status(500).send({ message: e.message });
    }
}

module.exports = {
    cargarPlanillaAportes,
    crearPlanillaAporte,
    actualizarPlanillaAporte,
    eliminarPlanillaAporte,
    listarPlanillaAportes,
    contarPlanillaAportes,
    resumenPlanillaAportes
};


