<?php
/**
 * Helpers fase v7.3: blindaje de prestaciones con historial salarial real, validación de ingreso, intereses BCV y limpieza técnica.
 * Compatible MariaDB/MySQL. Sin sintaxis UPSERT incompatible.
 */
if (!function_exists('nomina_pm_table_exists')) {
    function nomina_pm_table_exists($conexion, $table) {
        $table = preg_replace('/[^a-zA-Z0-9_]/', '', (string)$table);
        if ($table === '') { return false; }
        $res = @mysqli_query($conexion, "SHOW TABLES LIKE '" . mysqli_real_escape_string($conexion, $table) . "'");
        return $res && mysqli_num_rows($res) > 0;
    }
}

if (!function_exists('nomina_pm_ensure_schema')) {
    function nomina_pm_ensure_schema($conexion) {
        @mysqli_query($conexion, "CREATE TABLE IF NOT EXISTS nomina_tasas_interes_bcv (
            id INT AUTO_INCREMENT PRIMARY KEY,
            periodo_ym CHAR(7) NOT NULL,
            anio SMALLINT NOT NULL,
            mes TINYINT NOT NULL,
            tasa_activa_anual DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
            tasa_pasiva_anual DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
            tasa_promedio_anual DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
            fecha_publicacion DATE DEFAULT NULL,
            fuente_url VARCHAR(255) DEFAULT NULL,
            aplica_prestaciones TINYINT(1) NOT NULL DEFAULT 1,
            observacion VARCHAR(255) DEFAULT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_periodo_ym (periodo_ym),
            KEY idx_anio_mes (anio, mes),
            KEY idx_aplica (aplica_prestaciones)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");


        if (function_exists('nomina_table_has_column')) {
            if (!nomina_table_has_column($conexion, 'nomina_tasas_interes_bcv', 'gaceta_numero')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN gaceta_numero VARCHAR(30) DEFAULT NULL AFTER tasa_promedio_anual");
            }
            if (!nomina_table_has_column($conexion, 'nomina_tasas_interes_bcv', 'gaceta_fecha')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN gaceta_fecha DATE DEFAULT NULL AFTER gaceta_numero");
            }
            if (!nomina_table_has_column($conexion, 'nomina_tasas_interes_bcv', 'fuente_archivo')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN fuente_archivo VARCHAR(180) DEFAULT NULL AFTER fuente_url");
            }
            if (!nomina_table_has_column($conexion, 'nomina_tasas_interes_bcv', 'imported_at')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN imported_at DATETIME DEFAULT NULL AFTER updated_at");
            }
            if (!nomina_table_has_column($conexion, 'nomina_tasas_interes_bcv', 'imported_by')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN imported_by VARCHAR(80) DEFAULT NULL AFTER imported_at");
            }
        } else {
            @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN gaceta_numero VARCHAR(30) DEFAULT NULL AFTER tasa_promedio_anual");
            @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN gaceta_fecha DATE DEFAULT NULL AFTER gaceta_numero");
            @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN fuente_archivo VARCHAR(180) DEFAULT NULL AFTER fuente_url");
            @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN imported_at DATETIME DEFAULT NULL AFTER updated_at");
            @mysqli_query($conexion, "ALTER TABLE nomina_tasas_interes_bcv ADD COLUMN imported_by VARCHAR(80) DEFAULT NULL AFTER imported_at");
        }

        @mysqli_query($conexion, "CREATE TABLE IF NOT EXISTS nomina_salario_historial (
            id INT AUTO_INCREMENT PRIMARY KEY,
            persona_id INT NOT NULL,
            fecha_desde DATE NOT NULL,
            fecha_hasta DATE DEFAULT NULL,
            salario_origen VARCHAR(30) DEFAULT NULL,
            salario_moneda VARCHAR(10) DEFAULT NULL,
            salario_monto_original DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
            tasa_bcv_usada DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
            salario_mensual_bs DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
            salario_diario_bs DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
            salario_hora_bs DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
            bono_extra_usd_mensual DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
            salario_normal_mensual_bs DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
            salario_integral_mensual_bs DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
            origen VARCHAR(40) NOT NULL DEFAULT 'CORRIDA_CERRADA',
            referencia_corrida_id INT DEFAULT NULL,
            snapshot_json JSON DEFAULT NULL,
            observacion VARCHAR(255) DEFAULT NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_persona_corrida (persona_id, referencia_corrida_id),
            KEY idx_persona_fecha (persona_id, fecha_desde),
            KEY idx_origen (origen)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        @mysqli_query($conexion, "CREATE TABLE IF NOT EXISTS nomina_prestaciones_intereses (
            id INT AUTO_INCREMENT PRIMARY KEY,
            persona_id INT NOT NULL,
            periodo_ym CHAR(7) NOT NULL,
            fecha_desde DATE NOT NULL,
            fecha_hasta DATE NOT NULL,
            metodo VARCHAR(30) NOT NULL DEFAULT 'PROMEDIO_ACTIVA_PASIVA',
            tasa_id INT DEFAULT NULL,
            tasa_anual DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
            tasa_mensual DECIMAL(12,8) NOT NULL DEFAULT 0.00000000,
            capital_base_bs DECIMAL(18,2) NOT NULL DEFAULT 0.00,
            interes_bs DECIMAL(18,2) NOT NULL DEFAULT 0.00,
            fuente_url VARCHAR(255) DEFAULT NULL,
            observacion VARCHAR(255) DEFAULT NULL,
            estado VARCHAR(20) NOT NULL DEFAULT 'REGISTRADO',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_persona_periodo_metodo (persona_id, periodo_ym, metodo),
            KEY idx_persona_fecha (persona_id, fecha_hasta),
            KEY idx_estado (estado)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        if (function_exists('nomina_table_has_column')) {
            if (!nomina_table_has_column($conexion, 'nomina_prestaciones_eventos', 'tasa_bcv_usada')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN tasa_bcv_usada DECIMAL(18,6) NOT NULL DEFAULT 0.000000 AFTER salario_integral_diario_bs");
            }
            if (!nomina_table_has_column($conexion, 'nomina_prestaciones_eventos', 'salario_moneda')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN salario_moneda VARCHAR(10) DEFAULT NULL AFTER tasa_bcv_usada");
            }
            if (!nomina_table_has_column($conexion, 'nomina_prestaciones_eventos', 'salario_monto_original')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN salario_monto_original DECIMAL(18,4) NOT NULL DEFAULT 0.0000 AFTER salario_moneda");
            }
            if (!nomina_table_has_column($conexion, 'nomina_prestaciones_eventos', 'fuente_salario')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN fuente_salario VARCHAR(40) DEFAULT NULL AFTER salario_monto_original");
            }
            if (!nomina_table_has_column($conexion, 'nomina_prestaciones_eventos', 'snapshot_json')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN snapshot_json LONGTEXT DEFAULT NULL AFTER fuente_salario");
            }
            if (!nomina_table_has_column($conexion, 'nomina_prestaciones_eventos', 'origen_registro')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN origen_registro VARCHAR(40) NOT NULL DEFAULT 'MANUAL' AFTER snapshot_json");
            }
            if (!nomina_table_has_column($conexion, 'nomina_prestaciones_eventos', 'soporte_referencia')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN soporte_referencia VARCHAR(255) DEFAULT NULL AFTER origen_registro");
            }
            if (!nomina_table_has_column($conexion, 'nomina_prestaciones_eventos', 'created_by')) {
                @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN created_by VARCHAR(80) DEFAULT NULL AFTER soporte_referencia");
            }
        }
        return true;
    }
}

if (!function_exists('nomina_pm_parse_period_ym')) {
    function nomina_pm_parse_period_ym($periodoYm) {
        $periodoYm = trim((string)$periodoYm);
        if (!preg_match('/^(20\d{2})-(0[1-9]|1[0-2])$/', $periodoYm, $m)) {
            return null;
        }
        return ['anio' => (int)$m[1], 'mes' => (int)$m[2], 'ym' => $periodoYm];
    }
}

if (!function_exists('nomina_pm_save_interest_rate')) {
    function nomina_pm_save_interest_rate($conexion, array $data) {
        nomina_pm_ensure_schema($conexion);
        $period = nomina_pm_parse_period_ym($data['periodo_ym'] ?? '');
        if (!$period) { return ['ok' => false, 'message' => 'Período inválido. Usa formato AAAA-MM.']; }
        $activa = nomina_normalize_decimal($data['tasa_activa_anual'] ?? 0);
        $pasiva = nomina_normalize_decimal($data['tasa_pasiva_anual'] ?? 0);
        $promedio = nomina_normalize_decimal($data['tasa_promedio_anual'] ?? 0);
        if ($promedio <= 0 && ($activa > 0 || $pasiva > 0)) { $promedio = ($activa + $pasiva) / 2; }
        $fechaPub = nomina_date_to_iso($data['fecha_publicacion'] ?? '') ?: null;
        $fuente = trim((string)($data['fuente_url'] ?? 'https://www.bcv.org.ve/estadisticas/tasas-de-interes'));
        $obs = trim((string)($data['observacion'] ?? 'Tasa de interés BCV para cálculo de prestaciones'));
        $aplica = !empty($data['aplica_prestaciones']) ? 1 : 0;

        $id = 0;
        $stmt = mysqli_prepare($conexion, "SELECT id FROM nomina_tasas_interes_bcv WHERE periodo_ym = ? LIMIT 1");
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, 's', $period['ym']);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_bind_result($stmt, $foundId);
            if (mysqli_stmt_fetch($stmt)) { $id = (int)$foundId; }
            mysqli_stmt_close($stmt);
        }
        if ($id > 0) {
            $stmt = mysqli_prepare($conexion, "UPDATE nomina_tasas_interes_bcv SET anio=?, mes=?, tasa_activa_anual=?, tasa_pasiva_anual=?, tasa_promedio_anual=?, fecha_publicacion=?, fuente_url=?, aplica_prestaciones=?, observacion=? WHERE id=?");
            if (!$stmt) { return ['ok' => false, 'message' => mysqli_error($conexion)]; }
            mysqli_stmt_bind_param($stmt, 'iidddssisi', $period['anio'], $period['mes'], $activa, $pasiva, $promedio, $fechaPub, $fuente, $aplica, $obs, $id);
            $ok = mysqli_stmt_execute($stmt);
            $err = mysqli_stmt_error($stmt);
            mysqli_stmt_close($stmt);
            return ['ok' => $ok, 'message' => $ok ? 'Tasa actualizada.' : $err];
        }
        $stmt = mysqli_prepare($conexion, "INSERT INTO nomina_tasas_interes_bcv (periodo_ym, anio, mes, tasa_activa_anual, tasa_pasiva_anual, tasa_promedio_anual, fecha_publicacion, fuente_url, aplica_prestaciones, observacion) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        if (!$stmt) { return ['ok' => false, 'message' => mysqli_error($conexion)]; }
        mysqli_stmt_bind_param($stmt, 'siidddssis', $period['ym'], $period['anio'], $period['mes'], $activa, $pasiva, $promedio, $fechaPub, $fuente, $aplica, $obs);
        $ok = mysqli_stmt_execute($stmt);
        $err = mysqli_stmt_error($stmt);
        mysqli_stmt_close($stmt);
        return ['ok' => $ok, 'message' => $ok ? 'Tasa registrada.' : $err];
    }
}

if (!function_exists('nomina_pm_fetch_interest_rates')) {
    function nomina_pm_fetch_interest_rates($conexion, $limit = 60) {
        nomina_pm_ensure_schema($conexion);
        $limit = max(1, (int)$limit);
        $rows = [];
        $res = mysqli_query($conexion, "SELECT * FROM nomina_tasas_interes_bcv ORDER BY periodo_ym DESC LIMIT {$limit}");
        if ($res) { while ($r = mysqli_fetch_assoc($res)) { $rows[] = $r; } }
        return $rows;
    }
}

if (!function_exists('nomina_pm_get_rate_for_month')) {
    function nomina_pm_get_rate_for_month($conexion, $periodoYm, $metodo = 'PROMEDIO_ACTIVA_PASIVA') {
        nomina_pm_ensure_schema($conexion);
        $period = nomina_pm_parse_period_ym($periodoYm);
        if (!$period) { return null; }
        $stmt = mysqli_prepare($conexion, "SELECT * FROM nomina_tasas_interes_bcv WHERE periodo_ym = ? AND aplica_prestaciones = 1 LIMIT 1");
        if (!$stmt) { return null; }
        mysqli_stmt_bind_param($stmt, 's', $period['ym']);
        mysqli_stmt_execute($stmt);
        $res = mysqli_stmt_get_result($stmt);
        $row = $res ? mysqli_fetch_assoc($res) : null;
        mysqli_stmt_close($stmt);
        if (!$row) { return null; }
        $metodo = strtoupper((string)$metodo);
        if ($metodo === 'ACTIVA') { $tasa = (float)$row['tasa_activa_anual']; }
        elseif ($metodo === 'PASIVA') { $tasa = (float)$row['tasa_pasiva_anual']; }
        else { $tasa = (float)$row['tasa_promedio_anual']; }
        return ['row' => $row, 'tasa_anual' => $tasa, 'metodo' => $metodo === 'ACTIVA' || $metodo === 'PASIVA' ? $metodo : 'PROMEDIO_ACTIVA_PASIVA'];
    }
}

if (!function_exists('nomina_pm_capital_base_until')) {
    function nomina_pm_capital_base_until($conexion, $personaId, $fechaHasta) {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId;
        $fechaHasta = nomina_date_to_iso($fechaHasta);
        if ($personaId <= 0 || $fechaHasta === '') { return 0.0; }
        $sql = "SELECT tipo, monto_bs FROM nomina_prestaciones_eventos WHERE persona_id=? AND estado='REGISTRADO' AND fecha_evento <= ?";
        $stmt = mysqli_prepare($conexion, $sql);
        if (!$stmt) { return 0.0; }
        mysqli_stmt_bind_param($stmt, 'is', $personaId, $fechaHasta);
        mysqli_stmt_execute($stmt);
        $res = mysqli_stmt_get_result($stmt);
        $capital = 0.0;
        if ($res) {
            while ($row = mysqli_fetch_assoc($res)) {
                $tipo = strtoupper((string)$row['tipo']);
                $monto = nomina_normalize_decimal($row['monto_bs'] ?? 0);
                if ($tipo === 'ANTICIPO') { $capital -= abs($monto); }
                elseif (in_array($tipo, ['TRIMESTRAL','ADICIONAL_ANUAL','AJUSTE_HISTORICO'], true)) { $capital += $monto; }
            }
        }
        mysqli_stmt_close($stmt);
        return max(0.0, round($capital, 2));
    }
}

if (!function_exists('nomina_pm_register_advance')) {
    function nomina_pm_register_advance($conexion, $personaId, $fechaDocumento, $montoBs, $observacion) {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId;
        $fechaDocumento = nomina_date_to_iso($fechaDocumento) ?: date('Y-m-d');
        $monto = abs(nomina_normalize_decimal($montoBs));
        $obs = trim((string)$observacion);
        if ($personaId <= 0 || $monto <= 0 || strlen($obs) < 8) { return ['ok' => false, 'message' => 'Indica trabajador, monto y observación/soporte del anticipo.']; }
        $periodRef = 'ANT-' . date('YmdHis');
        $stmt = mysqli_prepare($conexion, "INSERT INTO nomina_prestaciones_eventos (persona_id, fecha_documento, fecha_evento, tipo, periodo_ref, anio_servicio, trimestre_nro, es_historico, dias, salario_normal_mensual_bs, salario_diario_bs, salario_integral_diario_bs, monto_bs, observacion, estado) VALUES (?, ?, ?, 'ANTICIPO', ?, 0, 0, 0, 0, 0, 0, 0, ?, ?, 'REGISTRADO')");
        if (!$stmt) { return ['ok' => false, 'message' => mysqli_error($conexion)]; }
        mysqli_stmt_bind_param($stmt, 'isssds', $personaId, $fechaDocumento, $fechaDocumento, $periodRef, $monto, $obs);
        $ok = mysqli_stmt_execute($stmt);
        $err = mysqli_stmt_error($stmt);
        mysqli_stmt_close($stmt);
        return ['ok' => $ok, 'message' => $ok ? 'Anticipo de prestaciones registrado.' : $err];
    }
}

if (!function_exists('nomina_pm_generate_interests')) {
    function nomina_pm_generate_interests($conexion, $personaId, $fechaCorte, $metodo = 'PROMEDIO_ACTIVA_PASIVA', $observacion = '') {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId;
        $fechaCorte = nomina_date_to_iso($fechaCorte) ?: date('Y-m-d');
        if ($personaId <= 0) { return ['ok' => false, 'message' => 'Trabajador inválido.', 'created' => 0, 'missing' => []]; }
        $minDate = null;
        $stmt = mysqli_prepare($conexion, "SELECT MIN(fecha_evento) FROM nomina_prestaciones_eventos WHERE persona_id=? AND estado='REGISTRADO' AND tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL','AJUSTE_HISTORICO')");
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, 'i', $personaId);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_bind_result($stmt, $minDate);
            mysqli_stmt_fetch($stmt);
            mysqli_stmt_close($stmt);
        }
        if (!$minDate) { return ['ok' => false, 'message' => 'No hay capital base de prestaciones para calcular intereses.', 'created' => 0, 'missing' => []]; }
        try {
            $start = new DateTime(substr($minDate, 0, 7) . '-01');
            $end = new DateTime(substr($fechaCorte, 0, 7) . '-01');
        } catch (Exception $e) {
            return ['ok' => false, 'message' => 'Fechas inválidas.', 'created' => 0, 'missing' => []];
        }
        $created = 0; $skipped = 0; $missing = []; $errors = [];
        $obs = trim((string)$observacion);
        if ($obs === '') { $obs = 'Interés mensual sobre prestaciones con tasa BCV'; }
        $current = clone $start;
        while ($current <= $end) {
            $ym = $current->format('Y-m');
            $monthEnd = clone $current;
            $monthEnd->modify('last day of this month');
            if ($monthEnd->format('Y-m-d') > $fechaCorte) { $monthEnd = new DateTime($fechaCorte); }
            $rateInfo = nomina_pm_get_rate_for_month($conexion, $ym, $metodo);
            if (!$rateInfo || (float)$rateInfo['tasa_anual'] <= 0) { $missing[] = $ym; $current->modify('+1 month'); continue; }
            $capital = nomina_pm_capital_base_until($conexion, $personaId, $monthEnd->format('Y-m-d'));
            if ($capital <= 0) { $skipped++; $current->modify('+1 month'); continue; }
            $exists = 0;
            $stmt = mysqli_prepare($conexion, "SELECT id FROM nomina_prestaciones_intereses WHERE persona_id=? AND periodo_ym=? AND metodo=? AND estado='REGISTRADO' LIMIT 1");
            if ($stmt) {
                mysqli_stmt_bind_param($stmt, 'iss', $personaId, $ym, $rateInfo['metodo']);
                mysqli_stmt_execute($stmt);
                mysqli_stmt_bind_result($stmt, $existsId);
                if (mysqli_stmt_fetch($stmt)) { $exists = (int)$existsId; }
                mysqli_stmt_close($stmt);
            }
            if ($exists > 0) { $skipped++; $current->modify('+1 month'); continue; }
            $tasaAnual = (float)$rateInfo['tasa_anual'];
            $tasaMensual = ($tasaAnual / 100.0) / 12.0;
            $interes = round($capital * $tasaMensual, 2);
            $rateRow = $rateInfo['row'];
            $tasaId = (int)$rateRow['id'];
            $fechaDesde = $current->format('Y-m-01');
            $fechaHasta = $monthEnd->format('Y-m-d');
            $fuente = (string)($rateRow['fuente_url'] ?? '');
            $stmt = mysqli_prepare($conexion, "INSERT INTO nomina_prestaciones_intereses (persona_id, periodo_ym, fecha_desde, fecha_hasta, metodo, tasa_id, tasa_anual, tasa_mensual, capital_base_bs, interes_bs, fuente_url, observacion, estado) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'REGISTRADO')");
            if (!$stmt) { $errors[] = mysqli_error($conexion); $current->modify('+1 month'); continue; }
            mysqli_stmt_bind_param($stmt, 'issssiddddss', $personaId, $ym, $fechaDesde, $fechaHasta, $rateInfo['metodo'], $tasaId, $tasaAnual, $tasaMensual, $capital, $interes, $fuente, $obs);
            $ok = mysqli_stmt_execute($stmt);
            $err = mysqli_stmt_error($stmt);
            mysqli_stmt_close($stmt);
            if ($ok) { $created++; } else { $errors[] = $err; }
            $current->modify('+1 month');
        }
        $msg = 'Intereses generados: ' . $created . '. Meses omitidos/existentes: ' . $skipped . '.';
        if ($missing) { $msg .= ' Faltan tasas BCV para: ' . implode(', ', array_unique($missing)) . '.'; }
        if ($errors) { $msg .= ' Errores: ' . implode(' | ', array_slice($errors, 0, 3)); }
        return ['ok' => empty($errors), 'message' => $msg, 'created' => $created, 'missing' => array_unique($missing)];
    }
}

if (!function_exists('nomina_pm_sum_interests')) {
    function nomina_pm_sum_interests($conexion, $personaId) {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId;
        $sum = 0.0;
        $stmt = mysqli_prepare($conexion, "SELECT COALESCE(SUM(interes_bs),0) FROM nomina_prestaciones_intereses WHERE persona_id=? AND estado='REGISTRADO'");
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, 'i', $personaId);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_bind_result($stmt, $sum);
            mysqli_stmt_fetch($stmt);
            mysqli_stmt_close($stmt);
        }
        return round((float)$sum, 2);
    }
}

if (!function_exists('nomina_pm_sum_advances')) {
    function nomina_pm_sum_advances($conexion, $personaId) {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId;
        $sum = 0.0;
        $stmt = mysqli_prepare($conexion, "SELECT COALESCE(SUM(monto_bs),0) FROM nomina_prestaciones_eventos WHERE persona_id=? AND tipo='ANTICIPO' AND estado='REGISTRADO'");
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, 'i', $personaId);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_bind_result($stmt, $sum);
            mysqli_stmt_fetch($stmt);
            mysqli_stmt_close($stmt);
        }
        return round((float)$sum, 2);
    }
}

if (!function_exists('nomina_pm_fetch_interests')) {
    function nomina_pm_fetch_interests($conexion, $personaId, $limit = 24) {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId; $limit = max(1, (int)$limit);
        $rows = [];
        $stmt = mysqli_prepare($conexion, "SELECT * FROM nomina_prestaciones_intereses WHERE persona_id=? AND estado='REGISTRADO' ORDER BY periodo_ym DESC LIMIT {$limit}");
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, 'i', $personaId);
            mysqli_stmt_execute($stmt);
            $res = mysqli_stmt_get_result($stmt);
            if ($res) { while ($r = mysqli_fetch_assoc($res)) { $rows[] = $r; } }
            mysqli_stmt_close($stmt);
        }
        return $rows;
    }
}



if (!function_exists('nomina_pm_valid_hire_date')) {
    function nomina_pm_valid_hire_date($fechaIngreso, $fechaCorte = null) {
        $ingreso = function_exists('nomina_date_to_iso') ? nomina_date_to_iso($fechaIngreso) : trim((string)$fechaIngreso);
        $corte = $fechaCorte ? (function_exists('nomina_date_to_iso') ? nomina_date_to_iso($fechaCorte) : trim((string)$fechaCorte)) : date('Y-m-d');
        if ($ingreso === '' || $ingreso === '0000-00-00') { return false; }
        try { $dIngreso = new DateTime($ingreso); $dCorte = new DateTime($corte ?: date('Y-m-d')); }
        catch (Exception $e) { return false; }
        return $dIngreso <= $dCorte;
    }
}

if (!function_exists('nomina_pm_invalid_hire_date_people')) {
    function nomina_pm_invalid_hire_date_people($conexion, $fechaCorte = null) {
        $fechaCorte = $fechaCorte ? nomina_date_to_iso($fechaCorte) : date('Y-m-d');
        if ($fechaCorte === '') { $fechaCorte = date('Y-m-d'); }
        $rows = [];
        // IMPORTANTE: no comparar contra DATE literal '0000-00-00'.
        // En MySQL/MariaDB con sql_mode estricto puede disparar: Incorrect DATE value '0000-00-00'.
        // Se compara como texto para detectar datos heredados inválidos sin romper la consulta.
        $fechaCorteSql = mysqli_real_escape_string($conexion, $fechaCorte);
        $sql = "SELECT p.id, p.nombre, p.apellido, p.cedula, p.fecha_ingreso, p.estatus_laboral, c.cargo
                FROM personas p
                LEFT JOIN cargos c ON c.id_cargo = p.id_cargo
                WHERE IFNULL(p.estatus_laboral,'ACTIVO') = 'ACTIVO'
                  AND (
                        p.fecha_ingreso IS NULL
                     OR CAST(p.fecha_ingreso AS CHAR) = ''
                     OR CAST(p.fecha_ingreso AS CHAR) = '0000-00-00'
                     OR CAST(p.fecha_ingreso AS CHAR) > '" . $fechaCorteSql . "'
                  )
                ORDER BY p.apellido, p.nombre";
        $res = mysqli_query($conexion, $sql);
        if ($res) { while ($r = mysqli_fetch_assoc($res)) { $rows[] = $r; } }
        return $rows;
    }
}

if (!function_exists('nomina_pm_balance_has_invalid_hire_date')) {
    function nomina_pm_balance_has_invalid_hire_date(array $balance, $fechaCorte = null) {
        $persona = $balance['persona'] ?? [];
        return !nomina_pm_valid_hire_date($persona['fecha_ingreso'] ?? '', $fechaCorte ?: date('Y-m-d'));
    }
}

if (!function_exists('nomina_pm_latest_salary_history_for_person')) {
    function nomina_pm_latest_salary_history_for_person($conexion, $personaId, $fechaRef = null) {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId;
        $fechaRef = $fechaRef ? nomina_date_to_iso($fechaRef) : date('Y-m-d');
        if ($personaId <= 0 || $fechaRef === '') { return null; }
        $stmt = mysqli_prepare($conexion, "SELECT * FROM nomina_salario_historial WHERE persona_id=? AND fecha_desde <= ? ORDER BY fecha_desde DESC, id DESC LIMIT 1");
        if (!$stmt) { return null; }
        mysqli_stmt_bind_param($stmt, 'is', $personaId, $fechaRef);
        mysqli_stmt_execute($stmt);
        $res = mysqli_stmt_get_result($stmt);
        $row = $res ? mysqli_fetch_assoc($res) : null;
        mysqli_stmt_close($stmt);
        return $row ?: null;
    }
}

if (!function_exists('nomina_pm_reference_salary_from_history')) {
    function nomina_pm_reference_salary_from_history(array $hist, $utilidadesDiasRef = 30) {
        $normalMonthly = nomina_normalize_decimal($hist['salario_normal_mensual_bs'] ?? 0);
        $integralMonthly = nomina_normalize_decimal($hist['salario_integral_mensual_bs'] ?? 0);
        $baseMonthly = nomina_normalize_decimal($hist['salario_mensual_bs'] ?? 0);
        $dailyNormal = $normalMonthly > 0 ? ($normalMonthly / 30.0) : nomina_normalize_decimal($hist['salario_diario_bs'] ?? 0);
        if ($integralMonthly <= 0 && $dailyNormal > 0) {
            $vacAlicuota = $dailyNormal * (15 / 360.0);
            $utilAlicuota = $dailyNormal * (max(0, (float)$utilidadesDiasRef) / 360.0);
            $integralDaily = $dailyNormal + $vacAlicuota + $utilAlicuota;
            $integralMonthly = $integralDaily * 30.0;
        }
        $integralDaily = $integralMonthly > 0 ? ($integralMonthly / 30.0) : $dailyNormal;
        $vacAlicuota = max(0, $integralDaily - $dailyNormal) / 2.0;
        $utilAlicuota = max(0, $integralDaily - $dailyNormal) / 2.0;
        return [
            'salary_source' => 'HISTORIAL_CORRIDA',
            'salary_currency' => (string)($hist['salario_moneda'] ?? 'VES'),
            'salary_amount' => (float)($hist['salario_monto_original'] ?? 0),
            'base_monthly_bs' => round($baseMonthly, 2),
            'cargo_bonus_bs' => round(nomina_normalize_decimal($hist['bono_extra_usd_mensual'] ?? 0) * nomina_normalize_decimal($hist['tasa_bcv_usada'] ?? 0), 2),
            'global_bonus_bs' => 0.0,
            'monthly_normal_bs' => round($normalMonthly, 2),
            'daily_normal_bs' => round($dailyNormal, 4),
            'vac_days_ref' => 15,
            'vac_alicuota_bs' => round($vacAlicuota, 4),
            'utilidades_dias_ref' => (float)$utilidadesDiasRef,
            'util_alicuota_bs' => round($utilAlicuota, 4),
            'integral_daily_bs' => round($integralDaily, 4),
            'tasa_bcv_ref' => round(nomina_normalize_decimal($hist['tasa_bcv_usada'] ?? 0), 6),
            'history_id' => (int)($hist['id'] ?? 0),
            'history_from' => (string)($hist['fecha_desde'] ?? ''),
            'history_corrida_id' => (int)($hist['referencia_corrida_id'] ?? 0),
        ];
    }
}

if (!function_exists('nomina_pm_count_orphan_snapshots')) {
    function nomina_pm_count_orphan_snapshots($conexion) {
        $tables = ['nomina_corrida_trabajador_snapshot','nomina_corrida_conceptos_snapshot','nomina_corrida_calendario_snapshot'];
        $out = [];
        foreach ($tables as $t) {
            $out[$t] = 0;
            if (!nomina_pm_table_exists($conexion, $t) || !nomina_pm_table_exists($conexion, 'nomina_corridas')) { continue; }
            $res = mysqli_query($conexion, "SELECT COUNT(*) AS c FROM {$t} s LEFT JOIN nomina_corridas c ON c.id = s.corrida_id WHERE c.id IS NULL");
            if ($res && ($r = mysqli_fetch_assoc($res))) { $out[$t] = (int)$r['c']; }
        }
        return $out;
    }
}

if (!function_exists('nomina_pm_clean_orphan_snapshots')) {
    function nomina_pm_clean_orphan_snapshots($conexion) {
        $counts = nomina_pm_count_orphan_snapshots($conexion);
        $deleted = 0; $errors = [];
        foreach ($counts as $t => $count) {
            if ($count <= 0) { continue; }
            $ok = mysqli_query($conexion, "DELETE s FROM {$t} s LEFT JOIN nomina_corridas c ON c.id = s.corrida_id WHERE c.id IS NULL");
            if ($ok) { $deleted += mysqli_affected_rows($conexion); }
            else { $errors[] = $t . ': ' . mysqli_error($conexion); }
        }
        return ['ok' => empty($errors), 'message' => 'Snapshots huérfanos eliminados: ' . $deleted . ($errors ? '. Errores: ' . implode(' | ', $errors) : ''), 'deleted' => $deleted];
    }
}

if (!function_exists('nomina_pm_count_mojibake_rows')) {
    function nomina_pm_count_mojibake_rows($conexion) {
        $checks = [
            'personas' => ['nombre','apellido','direccion','departamento','area'],
            'cargos' => ['cargo'],
            'nomina_corrida_trabajador_snapshot' => ['nombre','apellido','cargo'],
        ];
        $out = [];
        foreach ($checks as $table => $cols) {
            $out[$table] = 0;
            if (!nomina_pm_table_exists($conexion, $table)) { continue; }
            $parts = [];
            foreach ($cols as $col) {
                if (function_exists('nomina_table_has_column') && !nomina_table_has_column($conexion, $table, $col)) { continue; }
                $parts[] = "{$col} LIKE '%Ã%' OR {$col} LIKE '%Â%'";
            }
            if (!$parts) { continue; }
            $res = mysqli_query($conexion, "SELECT COUNT(*) AS c FROM {$table} WHERE " . implode(' OR ', $parts));
            if ($res && ($r = mysqli_fetch_assoc($res))) { $out[$table] = (int)$r['c']; }
        }
        return $out;
    }
}

if (!function_exists('nomina_pm_repair_common_mojibake_sql')) {
    function nomina_pm_repair_common_mojibake_sql($conexion, $table, array $cols) {
        if (!nomina_pm_table_exists($conexion, $table)) { return 0; }
        $affected = 0;
        foreach ($cols as $col) {
            if (function_exists('nomina_table_has_column') && !nomina_table_has_column($conexion, $table, $col)) { continue; }
            $sql = "UPDATE {$table} SET {$col} = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({$col}, 'Ã¡','á'), 'Ã©','é'), 'Ã­','í'), 'Ã³','ó'), 'Ãº','ú'), 'Ã±','ñ'), 'Ã‘','Ñ'), 'Â','') WHERE {$col} LIKE '%Ã%' OR {$col} LIKE '%Â%'";
            if (mysqli_query($conexion, $sql)) { $affected += mysqli_affected_rows($conexion); }
        }
        return $affected;
    }
}

if (!function_exists('nomina_pm_repair_mojibake_data')) {
    function nomina_pm_repair_mojibake_data($conexion) {
        $affected = 0;
        $affected += nomina_pm_repair_common_mojibake_sql($conexion, 'personas', ['nombre','apellido','direccion','departamento','area']);
        $affected += nomina_pm_repair_common_mojibake_sql($conexion, 'cargos', ['cargo']);
        $affected += nomina_pm_repair_common_mojibake_sql($conexion, 'nomina_corrida_trabajador_snapshot', ['nombre','apellido','cargo']);
        return ['ok' => true, 'message' => 'Reparación UTF/mojibake aplicada. Campos afectados aproximados: ' . $affected . '.', 'affected' => $affected];
    }
}

if (!function_exists('nomina_pm_sync_salary_history_from_closed_runs')) {
    function nomina_pm_sync_salary_history_from_closed_runs($conexion) {
        nomina_pm_ensure_schema($conexion);
        if (!nomina_pm_table_exists($conexion, 'nomina_corrida_trabajador_snapshot')) {
            return ['ok' => false, 'message' => 'No existe nomina_corrida_trabajador_snapshot.', 'created' => 0];
        }
        $sql = "SELECT c.id AS corrida_id, c.tasa_bcv_usd, p.fecha_desde AS fecha_inicio, p.fecha_hasta AS fecha_fin,
                       s.*, d.base_salario_normal, d.base_salario_integral
                FROM nomina_corrida_trabajador_snapshot s
                INNER JOIN nomina_corridas c ON c.id = s.corrida_id
                INNER JOIN periodos_nomina p ON p.id = c.periodo_id
                LEFT JOIN nomina_detalle d ON d.corrida_id = c.id AND d.persona_id = s.persona_id
                WHERE c.estado IN ('PAGADA','CERRADA')
                ORDER BY c.id ASC, s.persona_id ASC";
        $res = mysqli_query($conexion, $sql);
        if (!$res) { return ['ok' => false, 'message' => mysqli_error($conexion), 'created' => 0]; }
        $created = 0; $updated = 0; $skipped = 0; $errors = [];
        while ($row = mysqli_fetch_assoc($res)) {
            $personaId = (int)$row['persona_id'];
            $corridaId = (int)$row['corrida_id'];
            if ($personaId <= 0 || $corridaId <= 0) { $skipped++; continue; }
            $desde = $row['fecha_inicio'] ?: date('Y-m-d');
            $hasta = $row['fecha_fin'] ?: null;
            $salarioOrigen = (string)($row['salario_origen'] ?? 'CORRIDA');
            $salarioMoneda = (string)($row['salario_moneda'] ?? 'VES');
            $salarioMontoOriginal = nomina_normalize_decimal($row['salario_monto_original'] ?? 0);
            $tasa = nomina_normalize_decimal($row['tasa_bcv_usada'] ?? 0);
            if ($tasa <= 0) { $tasa = nomina_normalize_decimal($row['tasa_bcv_usd'] ?? 0); }
            $mensualBase = nomina_normalize_decimal($row['salario_mensual_bs'] ?? 0);
            $diario = nomina_normalize_decimal($row['salario_diario_bs'] ?? 0);
            $hora = nomina_normalize_decimal($row['salario_hora_bs'] ?? 0);
            $bonoUsd = nomina_normalize_decimal($row['bono_extra_usd_mensual'] ?? 0);
            $normalFromSnapshot = $mensualBase + ($bonoUsd > 0 && $tasa > 0 ? ($bonoUsd * $tasa) : 0);
            $normalFromDetalle = nomina_normalize_decimal($row['base_salario_normal'] ?? 0) * 2.0;
            $normal = max($normalFromSnapshot, $normalFromDetalle);
            if ($normal <= 0 && $diario > 0) { $normal = $diario * 30.0; }
            $integralFromDetalle = nomina_normalize_decimal($row['base_salario_integral'] ?? 0) * 2.0;
            $integral = max($normal, $integralFromDetalle);
            if ($integral <= 0) { $integral = $normal; }
            $snapshot = (string)($row['snapshot_json'] ?? '');
            if ($snapshot === '') { $snapshot = null; }
            $obs = 'Sincronizado desde corrida cerrada/pagada #' . $corridaId . ' (v7.3 blindaje prestaciones)';
            $exists = 0;
            $stmt = mysqli_prepare($conexion, "SELECT id FROM nomina_salario_historial WHERE persona_id=? AND referencia_corrida_id=? LIMIT 1");
            if ($stmt) {
                mysqli_stmt_bind_param($stmt, 'ii', $personaId, $corridaId);
                mysqli_stmt_execute($stmt);
                mysqli_stmt_bind_result($stmt, $foundId);
                if (mysqli_stmt_fetch($stmt)) { $exists = (int)$foundId; }
                mysqli_stmt_close($stmt);
            }
            if ($exists > 0) {
                $stmt = mysqli_prepare($conexion, "UPDATE nomina_salario_historial SET fecha_desde=?, fecha_hasta=?, salario_origen=?, salario_moneda=?, salario_monto_original=?, tasa_bcv_usada=?, salario_mensual_bs=?, salario_diario_bs=?, salario_hora_bs=?, bono_extra_usd_mensual=?, salario_normal_mensual_bs=?, salario_integral_mensual_bs=?, origen='CORRIDA_CERRADA', snapshot_json=?, observacion=? WHERE id=?");
                if (!$stmt) { $errors[] = mysqli_error($conexion); continue; }
                mysqli_stmt_bind_param($stmt, 'ssssddddddddssi', $desde, $hasta, $salarioOrigen, $salarioMoneda, $salarioMontoOriginal, $tasa, $mensualBase, $diario, $hora, $bonoUsd, $normal, $integral, $snapshot, $obs, $exists);
                $ok = mysqli_stmt_execute($stmt); $err = mysqli_stmt_error($stmt); mysqli_stmt_close($stmt);
                if ($ok) { $updated++; } else { $errors[] = $err; }
            } else {
                $stmt = mysqli_prepare($conexion, "INSERT INTO nomina_salario_historial (persona_id, fecha_desde, fecha_hasta, salario_origen, salario_moneda, salario_monto_original, tasa_bcv_usada, salario_mensual_bs, salario_diario_bs, salario_hora_bs, bono_extra_usd_mensual, salario_normal_mensual_bs, salario_integral_mensual_bs, origen, referencia_corrida_id, snapshot_json, observacion) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'CORRIDA_CERRADA', ?, ?, ?)");
                if (!$stmt) { $errors[] = mysqli_error($conexion); continue; }
                mysqli_stmt_bind_param($stmt, 'issssddddddddiss', $personaId, $desde, $hasta, $salarioOrigen, $salarioMoneda, $salarioMontoOriginal, $tasa, $mensualBase, $diario, $hora, $bonoUsd, $normal, $integral, $corridaId, $snapshot, $obs);
                $ok = mysqli_stmt_execute($stmt); $err = mysqli_stmt_error($stmt); mysqli_stmt_close($stmt);
                if ($ok) { $created++; } else { $errors[] = $err; }
            }
        }
        $msg = 'Historial salarial sincronizado. Nuevos: ' . $created . '. Actualizados: ' . $updated . '. Omitidos: ' . $skipped . '.';
        if ($errors) { $msg .= ' Errores: ' . implode(' | ', array_slice($errors, 0, 3)); }
        return ['ok' => empty($errors), 'message' => $msg, 'created' => $created, 'updated' => $updated];
    }
}

if (!function_exists('nomina_pm_fetch_salary_history')) {
    function nomina_pm_fetch_salary_history($conexion, $personaId, $limit = 24) {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId; $limit = max(1, (int)$limit);
        $rows = [];
        $stmt = mysqli_prepare($conexion, "SELECT * FROM nomina_salario_historial WHERE persona_id=? ORDER BY fecha_desde DESC, id DESC LIMIT {$limit}");
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, 'i', $personaId);
            mysqli_stmt_execute($stmt);
            $res = mysqli_stmt_get_result($stmt);
            if ($res) { while ($r = mysqli_fetch_assoc($res)) { $rows[] = $r; } }
            mysqli_stmt_close($stmt);
        }
        return $rows;
    }
}

if (!function_exists('nomina_pm_recent_salary_history_count')) {
    function nomina_pm_recent_salary_history_count($conexion) {
        nomina_pm_ensure_schema($conexion);
        $count = 0;
        $res = mysqli_query($conexion, "SELECT COUNT(*) AS c FROM nomina_salario_historial");
        if ($res && ($row = mysqli_fetch_assoc($res))) { $count = (int)$row['c']; }
        return $count;
    }
}

if (!function_exists('nomina_pm_current_user_label')) {
    function nomina_pm_current_user_label() {
        if (session_status() !== PHP_SESSION_ACTIVE) { @session_start(); }
        return trim((string)($_SESSION['usuario'] ?? $_SESSION['username'] ?? 'sistema'));
    }
}

if (!function_exists('nomina_pm_count_interest_rates')) {
    function nomina_pm_count_interest_rates($conexion) {
        nomina_pm_ensure_schema($conexion);
        $count = 0;
        $res = mysqli_query($conexion, "SELECT COUNT(*) AS c FROM nomina_tasas_interes_bcv WHERE aplica_prestaciones=1");
        if ($res && ($row = mysqli_fetch_assoc($res))) { $count = (int)$row['c']; }
        return $count;
    }
}

if (!function_exists('nomina_pm_count_registered_events')) {
    function nomina_pm_count_registered_events($conexion, $tipo = null) {
        nomina_pm_ensure_schema($conexion);
        $sql = "SELECT COUNT(*) AS c FROM nomina_prestaciones_eventos WHERE estado='REGISTRADO'";
        if ($tipo !== null && $tipo !== '') {
            $safe = mysqli_real_escape_string($conexion, strtoupper((string)$tipo));
            $sql .= " AND tipo='{$safe}'";
        }
        $count = 0;
        $res = mysqli_query($conexion, $sql);
        if ($res && ($row = mysqli_fetch_assoc($res))) { $count = (int)$row['c']; }
        return $count;
    }
}

if (!function_exists('nomina_pm_salary_snapshot_from_balance')) {
    function nomina_pm_salary_snapshot_from_balance(array $balance, $fechaDocumento, $tasaRef, $tipo, $periodRef, $dias, $monto) {
        $ri = $balance['ref'] ?? [];
        $persona = $balance['persona'] ?? [];
        return [
            'version' => 'v7.3',
            'fecha_documento' => $fechaDocumento,
            'tipo' => $tipo,
            'periodo_ref' => $periodRef,
            'dias' => (float)$dias,
            'monto_bs' => (float)$monto,
            'persona_id' => (int)($persona['id'] ?? 0),
            'cedula' => (string)($persona['cedula'] ?? ''),
            'fecha_ingreso' => (string)($persona['fecha_ingreso'] ?? ''),
            'salario_source' => (string)($ri['salary_source'] ?? ''),
            'salario_currency' => (string)($ri['salary_currency'] ?? 'VES'),
            'salario_amount' => (float)($ri['salary_amount'] ?? 0),
            'tasa_bcv_ref' => (float)$tasaRef,
            'base_monthly_bs' => (float)($ri['base_monthly_bs'] ?? 0),
            'cargo_bonus_bs' => (float)($ri['cargo_bonus_bs'] ?? 0),
            'global_bonus_bs' => (float)($ri['global_bonus_bs'] ?? 0),
            'monthly_normal_bs' => (float)($ri['monthly_normal_bs'] ?? 0),
            'daily_normal_bs' => (float)($ri['daily_normal_bs'] ?? 0),
            'vac_alicuota_bs' => (float)($ri['vac_alicuota_bs'] ?? 0),
            'util_alicuota_bs' => (float)($ri['util_alicuota_bs'] ?? 0),
            'integral_daily_bs' => (float)($ri['integral_daily_bs'] ?? 0),
        ];
    }
}

if (!function_exists('nomina_pm_insert_base_event')) {
    function nomina_pm_insert_base_event($conexion, $personaId, $fechaDocumento, $fechaEvento, $tipo, $periodRef, $anioServicio, $trimestreNro, $dias, array $balance, $monto, $observacion, $origenRegistro = 'V7_1_GENERACION') {
        nomina_pm_ensure_schema($conexion);
        $personaId = (int)$personaId;
        $fechaDocumento = nomina_date_to_iso($fechaDocumento) ?: date('Y-m-d');
        $fechaEvento = nomina_date_to_iso($fechaEvento) ?: $fechaDocumento;
        $tipo = strtoupper(trim((string)$tipo));
        $periodRef = trim((string)$periodRef);
        $obs = trim((string)$observacion);
        if ($personaId <= 0 || $tipo === '' || $periodRef === '') {
            return ['ok' => false, 'message' => 'Datos insuficientes para registrar evento de prestaciones.'];
        }
        if ($obs === '') { $obs = 'Generación base de prestaciones sociales'; }
        $exists = 0;
        $stmt = mysqli_prepare($conexion, "SELECT id FROM nomina_prestaciones_eventos WHERE persona_id=? AND tipo=? AND periodo_ref=? AND estado='REGISTRADO' LIMIT 1");
        if ($stmt) {
            mysqli_stmt_bind_param($stmt, 'iss', $personaId, $tipo, $periodRef);
            mysqli_stmt_execute($stmt);
            mysqli_stmt_bind_result($stmt, $foundId);
            if (mysqli_stmt_fetch($stmt)) { $exists = (int)$foundId; }
            mysqli_stmt_close($stmt);
        }
        if ($exists > 0) {
            return ['ok' => true, 'message' => 'Evento ya existente omitido.', 'created' => 0, 'skipped' => 1];
        }
        $ri = $balance['ref'] ?? [];
        $mensual = (float)($ri['monthly_normal_bs'] ?? 0);
        $diario = (float)($ri['daily_normal_bs'] ?? 0);
        $sid = (float)($ri['integral_daily_bs'] ?? 0);
        $tasa = (float)($ri['tasa_bcv_ref'] ?? 0);
        if ($tasa <= 0 && isset($_GET['tasa_bcv_ref'])) { $tasa = nomina_normalize_decimal($_GET['tasa_bcv_ref'] ?? 0); }
        $moneda = (string)($ri['salary_currency'] ?? 'VES');
        $montoOriginal = (float)($ri['salary_amount'] ?? 0);
        $fuenteSalario = (string)($ri['salary_source'] ?? 'REFERENCIAL');
        $snapshot = json_encode(nomina_pm_salary_snapshot_from_balance($balance, $fechaDocumento, $tasa, $tipo, $periodRef, $dias, $monto), JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        $origenRegistro = strtoupper(trim((string)$origenRegistro));
        $soporte = 'Generado desde módulo de prestaciones v7.3';
        $createdBy = nomina_pm_current_user_label();
        $stmt = mysqli_prepare($conexion, "INSERT INTO nomina_prestaciones_eventos (persona_id, fecha_documento, fecha_evento, tipo, periodo_ref, anio_servicio, trimestre_nro, es_historico, dias, salario_normal_mensual_bs, salario_diario_bs, salario_integral_diario_bs, tasa_bcv_usada, salario_moneda, salario_monto_original, fuente_salario, snapshot_json, origen_registro, soporte_referencia, created_by, monto_bs, observacion, estado) VALUES (?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'REGISTRADO')");
        if (!$stmt) { return ['ok' => false, 'message' => mysqli_error($conexion), 'created' => 0]; }
        mysqli_stmt_bind_param($stmt, 'issssiidddddsdsssssds', $personaId, $fechaDocumento, $fechaEvento, $tipo, $periodRef, $anioServicio, $trimestreNro, $dias, $mensual, $diario, $sid, $tasa, $moneda, $montoOriginal, $fuenteSalario, $snapshot, $origenRegistro, $soporte, $createdBy, $monto, $obs);
        $ok = mysqli_stmt_execute($stmt);
        $err = mysqli_stmt_error($stmt);
        mysqli_stmt_close($stmt);
        return ['ok' => $ok, 'message' => $ok ? 'Evento registrado.' : $err, 'created' => $ok ? 1 : 0, 'skipped' => 0];
    }
}

if (!function_exists('nomina_pm_generate_base_for_balance')) {
    function nomina_pm_generate_base_for_balance($conexion, array $balance, $fechaDocumento, $observacion = '') {
        nomina_pm_ensure_schema($conexion);
        $persona = $balance['persona'] ?? [];
        $personaId = (int)($persona['id'] ?? 0);
        if ($personaId <= 0) { return ['ok' => false, 'message' => 'Trabajador inválido.', 'created' => 0, 'skipped' => 0]; }
        if (!nomina_pm_valid_hire_date($persona['fecha_ingreso'] ?? '', $fechaDocumento)) {
            return ['ok' => false, 'message' => 'No se puede generar prestaciones: trabajador sin fecha de ingreso válida.', 'created' => 0, 'skipped' => 0];
        }
        $fechaDocumento = nomina_date_to_iso($fechaDocumento) ?: date('Y-m-d');
        $obs = trim((string)$observacion);
        if ($obs === '') { $obs = 'Generación base referencial de prestaciones sociales v7.3'; }
        $sid = (float)($balance['ref']['integral_daily_bs'] ?? 0);
        if ($sid <= 0) { return ['ok' => false, 'message' => 'Salario integral diario inválido.', 'created' => 0, 'skipped' => 0]; }
        $created = 0; $skipped = 0; $errors = [];
        foreach (($balance['quarter_periods'] ?? []) as $p) {
            $key = 'TRIMESTRAL|' . $p['period_ref'];
            if (isset($balance['events_map'][$key])) { $skipped++; continue; }
            $dias = 15.0;
            $monto = round($sid * $dias, 2);
            $res = nomina_pm_insert_base_event($conexion, $personaId, $fechaDocumento, $p['end'], 'TRIMESTRAL', $p['period_ref'], 0, (int)$p['quarter_no'], $dias, $balance, $monto, $obs);
            if ($res['ok']) { $created += (int)($res['created'] ?? 0); $skipped += (int)($res['skipped'] ?? 0); }
            else { $errors[] = $res['message']; }
        }
        foreach (($balance['annual_periods'] ?? []) as $p) {
            $key = 'ADICIONAL_ANUAL|' . $p['period_ref'];
            if (isset($balance['events_map'][$key])) { $skipped++; continue; }
            $dias = (float)$p['days'];
            $monto = round($sid * $dias, 2);
            $res = nomina_pm_insert_base_event($conexion, $personaId, $fechaDocumento, $p['date'], 'ADICIONAL_ANUAL', $p['period_ref'], (int)$p['year_no'], 0, $dias, $balance, $monto, $obs);
            if ($res['ok']) { $created += (int)($res['created'] ?? 0); $skipped += (int)($res['skipped'] ?? 0); }
            else { $errors[] = $res['message']; }
        }
        $ok = empty($errors);
        $msg = 'Movimientos base creados: ' . $created . '. Omitidos/existentes: ' . $skipped . '.';
        if ($errors) { $msg .= ' Errores: ' . implode(' | ', array_slice($errors, 0, 3)); }
        return ['ok' => $ok, 'message' => $msg, 'created' => $created, 'skipped' => $skipped, 'errors' => $errors];
    }
}

if (!function_exists('nomina_pm_generate_base_for_many')) {
    function nomina_pm_generate_base_for_many($conexion, array $balances, $fechaDocumento, $observacion = '') {
        $totalCreated = 0; $totalSkipped = 0; $workers = 0; $errors = [];
        mysqli_begin_transaction($conexion);
        foreach ($balances as $bal) {
            if (!nomina_prestaciones_has_pending_base($bal)) { continue; }
            $workers++;
            $res = nomina_pm_generate_base_for_balance($conexion, $bal, $fechaDocumento, $observacion);
            $totalCreated += (int)($res['created'] ?? 0);
            $totalSkipped += (int)($res['skipped'] ?? 0);
            if (!$res['ok']) { $errors[] = $res['message']; }
        }
        if ($errors) { mysqli_rollback($conexion); }
        else { mysqli_commit($conexion); }
        $msg = 'Trabajadores procesados: ' . $workers . '. Eventos creados: ' . $totalCreated . '. Omitidos/existentes: ' . $totalSkipped . '.';
        if ($errors) { $msg .= ' Errores: ' . implode(' | ', array_slice($errors, 0, 4)); }
        return ['ok' => empty($errors), 'message' => $msg, 'created' => $totalCreated, 'skipped' => $totalSkipped];
    }
}

if (!function_exists('nomina_pm_generate_interests_for_many')) {
    function nomina_pm_generate_interests_for_many($conexion, array $personas, $fechaCorte, $metodo = 'PROMEDIO_ACTIVA_PASIVA', $observacion = '') {
        $workers = 0; $created = 0; $missing = []; $messages = [];
        foreach ($personas as $persona) {
            $pid = (int)($persona['id'] ?? 0);
            if ($pid <= 0) { continue; }
            $res = nomina_pm_generate_interests($conexion, $pid, $fechaCorte, $metodo, $observacion);
            if ($res['ok'] || (int)($res['created'] ?? 0) > 0) { $workers++; }
            $created += (int)($res['created'] ?? 0);
            if (!empty($res['missing'])) { $missing = array_merge($missing, (array)$res['missing']); }
            if (!$res['ok'] && stripos($res['message'], 'No hay capital base') === false) { $messages[] = $res['message']; }
        }
        $missing = array_values(array_unique($missing));
        $msg = 'Intereses generados para lote. Trabajadores con operación: ' . $workers . '. Registros creados: ' . $created . '.';
        if ($missing) { $msg .= ' Faltan tasas: ' . implode(', ', array_slice($missing, 0, 18)) . (count($missing) > 18 ? '...' : '') . '.'; }
        if ($messages) { $msg .= ' Advertencias: ' . implode(' | ', array_slice($messages, 0, 3)); }
        return ['ok' => empty($messages), 'message' => $msg, 'created' => $created, 'missing' => $missing];
    }
}

if (!function_exists('nomina_pm_missing_rate_months_from_events')) {
    function nomina_pm_missing_rate_months_from_events($conexion, $fechaCorte) {
        nomina_pm_ensure_schema($conexion);
        $fechaCorte = nomina_date_to_iso($fechaCorte) ?: date('Y-m-d');
        $minDate = null;
        $res = mysqli_query($conexion, "SELECT MIN(fecha_evento) AS min_fecha FROM nomina_prestaciones_eventos WHERE estado='REGISTRADO' AND tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL','AJUSTE_HISTORICO')");
        if ($res && ($row = mysqli_fetch_assoc($res))) { $minDate = $row['min_fecha']; }
        if (!$minDate) { return []; }
        try { $start = new DateTime(substr($minDate,0,7).'-01'); $end = new DateTime(substr($fechaCorte,0,7).'-01'); }
        catch(Exception $e) { return []; }
        $missing = [];
        while ($start <= $end) {
            $ym = $start->format('Y-m');
            $rate = nomina_pm_get_rate_for_month($conexion, $ym, 'PROMEDIO_ACTIVA_PASIVA');
            if (!$rate || (float)$rate['tasa_anual'] <= 0) { $missing[] = $ym; }
            $start->modify('+1 month');
        }
        return $missing;
    }
}


if (!function_exists('nomina_pm_xlsx_col_index')) {
    function nomina_pm_xlsx_col_index($cellRef) {
        if (!preg_match('/^([A-Z]+)/', strtoupper((string)$cellRef), $m)) { return 0; }
        $letters = $m[1];
        $n = 0;
        for ($i = 0; $i < strlen($letters); $i++) { $n = ($n * 26) + (ord($letters[$i]) - 64); }
        return max(0, $n - 1);
    }
}

if (!function_exists('nomina_pm_xlsx_text_clean')) {
    function nomina_pm_xlsx_text_clean($value) {
        $value = trim((string)$value);
        $value = str_replace(["\xC2\xA0", "\r", "\n", "\t"], ' ', $value);
        return trim(preg_replace('/\s+/', ' ', $value));
    }
}

if (!function_exists('nomina_pm_xlsx_number')) {
    function nomina_pm_xlsx_number($value) {
        $value = nomina_pm_xlsx_text_clean($value);
        if ($value === '') { return 0.0; }
        $value = str_replace('%', '', $value);
        if (strpos($value, ',') !== false && strpos($value, '.') !== false) {
            $value = str_replace('.', '', $value);
            $value = str_replace(',', '.', $value);
        } else {
            $value = str_replace(',', '.', $value);
        }
        $value = preg_replace('/[^0-9\.\-]/', '', $value);
        if ($value === '' || $value === '-' || $value === '.') { return 0.0; }
        return (float)$value;
    }
}

if (!function_exists('nomina_pm_excel_date_to_iso')) {
    function nomina_pm_excel_date_to_iso($value) {
        $value = nomina_pm_xlsx_text_clean($value);
        if ($value === '') { return null; }
        if (is_numeric($value) && (float)$value > 20000) {
            $days = (int)round((float)$value);
            $ts = ($days - 25569) * 86400;
            if ($ts > 0) { return gmdate('Y-m-d', $ts); }
        }
        if (preg_match('/^(\d{1,2})\/(\d{1,2})\/(\d{4})$/', $value, $m)) {
            return sprintf('%04d-%02d-%02d', (int)$m[3], (int)$m[2], (int)$m[1]);
        }
        if (preg_match('/^(\d{4})-(\d{1,2})-(\d{1,2})$/', $value, $m)) {
            return sprintf('%04d-%02d-%02d', (int)$m[1], (int)$m[2], (int)$m[3]);
        }
        return nomina_date_to_iso($value) ?: null;
    }
}

if (!function_exists('nomina_pm_month_number_es')) {
    function nomina_pm_month_number_es($monthName) {
        $s = strtolower(nomina_pm_xlsx_text_clean($monthName));
        $s = strtr($s, ['á'=>'a','é'=>'e','í'=>'i','ó'=>'o','ú'=>'u','ñ'=>'n','Á'=>'a','É'=>'e','Í'=>'i','Ó'=>'o','Ú'=>'u','Ñ'=>'n']);
        $s = preg_replace('/[^a-z]/', '', $s);
        $map = [
            'enero'=>1, 'febrero'=>2, 'marzo'=>3, 'abril'=>4, 'mayo'=>5, 'junio'=>6,
            'julio'=>7, 'agosto'=>8, 'septiembre'=>9, 'setiembre'=>9, 'octubre'=>10, 'noviembre'=>11, 'diciembre'=>12
        ];
        return $map[$s] ?? 0;
    }
}


if (!function_exists('nomina_pm_xlsx_open_archive')) {
    function nomina_pm_xlsx_open_archive($xlsxPath) {
        if (class_exists('ZipArchive')) {
            $zip = new ZipArchive();
            if ($zip->open($xlsxPath) === true) { return ['type' => 'zip', 'obj' => $zip]; }
        }
        if (class_exists('PharData')) {
            try { return ['type' => 'phar', 'obj' => new PharData($xlsxPath)]; }
            catch (Throwable $e) { return null; }
        }
        return null;
    }
}

if (!function_exists('nomina_pm_xlsx_zip_get')) {
    function nomina_pm_xlsx_zip_get($archive, $name) {
        if (!$archive || empty($archive['obj'])) { return false; }
        if (($archive['type'] ?? '') === 'zip') { return $archive['obj']->getFromName($name); }
        if (($archive['type'] ?? '') === 'phar') {
            try { return isset($archive['obj'][$name]) ? $archive['obj'][$name]->getContent() : false; }
            catch (Throwable $e) { return false; }
        }
        return false;
    }
}

if (!function_exists('nomina_pm_xlsx_close_archive')) {
    function nomina_pm_xlsx_close_archive($archive) {
        if ($archive && ($archive['type'] ?? '') === 'zip' && !empty($archive['obj'])) { @$archive['obj']->close(); }
    }
}


if (!function_exists('nomina_pm_xml_attr')) {
    function nomina_pm_xml_attr($tag, $name) {
        if (preg_match('/\s' . preg_quote($name, '/') . '="([^"]*)"/i', $tag, $m)) { return html_entity_decode($m[1], ENT_QUOTES | ENT_XML1, 'UTF-8'); }
        return '';
    }
}

if (!function_exists('nomina_pm_xml_texts')) {
    function nomina_pm_xml_texts($xml, $tagName = 't') {
        $out = [];
        if (preg_match_all('/<' . preg_quote($tagName, '/') . '(?:\s[^>]*)?>(.*?)<\/' . preg_quote($tagName, '/') . '>/is', $xml, $m)) {
            foreach ($m[1] as $txt) { $out[] = html_entity_decode($txt, ENT_QUOTES | ENT_XML1, 'UTF-8'); }
        }
        return $out;
    }
}

if (!function_exists('nomina_pm_xlsx_shared_strings')) {
    function nomina_pm_xlsx_shared_strings($zip) {
        $strings = [];
        $raw = nomina_pm_xlsx_zip_get($zip, 'xl/sharedStrings.xml');
        if ($raw === false) { return $strings; }
        if (function_exists('simplexml_load_string')) {
            $xml = @simplexml_load_string($raw);
            if ($xml) {
                foreach ($xml->si as $si) {
                    $txt = '';
                    if (isset($si->t)) { $txt .= (string)$si->t; }
                    foreach ($si->r as $run) { if (isset($run->t)) { $txt .= (string)$run->t; } }
                    $strings[] = $txt;
                }
                return $strings;
            }
        }
        if (preg_match_all('/<si[^>]*>(.*?)<\/si>/is', $raw, $items)) {
            foreach ($items[1] as $siXml) { $strings[] = implode('', nomina_pm_xml_texts($siXml, 't')); }
        }
        return $strings;
    }
}

if (!function_exists('nomina_pm_xlsx_sheet_paths')) {
    function nomina_pm_xlsx_sheet_paths($zip) {
        $workbookRaw = nomina_pm_xlsx_zip_get($zip, 'xl/workbook.xml');
        $relsRaw = nomina_pm_xlsx_zip_get($zip, 'xl/_rels/workbook.xml.rels');
        if ($workbookRaw === false || $relsRaw === false) { return []; }
        $relMap = [];
        if (preg_match_all('/<Relationship\b[^>]*>/i', $relsRaw, $relTags)) {
            foreach ($relTags[0] as $tag) {
                $id = nomina_pm_xml_attr($tag, 'Id');
                $target = nomina_pm_xml_attr($tag, 'Target');
                if ($id !== '' && $target !== '') { $relMap[$id] = $target; }
            }
        }
        $sheets = [];
        if (preg_match_all('/<sheet\b[^>]*>/i', $workbookRaw, $sheetTags)) {
            foreach ($sheetTags[0] as $tag) {
                $name = nomina_pm_xml_attr($tag, 'name');
                $rid = nomina_pm_xml_attr($tag, 'r:id');
                $target = $relMap[$rid] ?? '';
                if ($target === '') { continue; }
                $path = ltrim($target, '/');
                if (strpos($path, 'xl/') !== 0) { $path = 'xl/' . $path; }
                $sheets[] = ['name' => $name, 'path' => $path];
            }
        }
        return $sheets;
    }
}

if (!function_exists('nomina_pm_xlsx_sheet_rows')) {
    function nomina_pm_xlsx_sheet_rows($zip, $path, array $sharedStrings) {
        $raw = nomina_pm_xlsx_zip_get($zip, $path);
        if ($raw === false) { return []; }
        $rows = [];
        if (preg_match_all('/<row\b[^>]*>(.*?)<\/row>/is', $raw, $rowMatches)) {
            foreach ($rowMatches[1] as $rowXml) {
                $out = [];
                if (preg_match_all('/<c\b((?:(?!\/>)[^>])*)>(.*?)<\/c>/is', $rowXml, $cellMatches, PREG_SET_ORDER)) {
                    foreach ($cellMatches as $cm) {
                        $tag = '<c' . $cm[1] . '>';
                        $inner = $cm[2];
                        $ref = nomina_pm_xml_attr($tag, 'r');
                        $type = nomina_pm_xml_attr($tag, 't');
                        $idx = nomina_pm_xlsx_col_index($ref);
                        $value = '';
                        if ($type === 's') {
                            if (preg_match('/<v[^>]*>(.*?)<\/v>/is', $inner, $vm)) {
                                $n = (int)trim($vm[1]);
                                $value = $sharedStrings[$n] ?? '';
                            }
                        } elseif ($type === 'inlineStr') {
                            $value = implode('', nomina_pm_xml_texts($inner, 't'));
                        } else {
                            if (preg_match('/<v[^>]*>(.*?)<\/v>/is', $inner, $vm)) {
                                $value = html_entity_decode(trim($vm[1]), ENT_QUOTES | ENT_XML1, 'UTF-8');
                            }
                        }
                        $out[$idx] = nomina_pm_xlsx_text_clean($value);
                    }
                }
                if ($out) { ksort($out); $rows[] = $out; }
            }
        }
        return $rows;
    }
}

if (!function_exists('nomina_pm_parse_bcv_interest_xlsx')) {
    function nomina_pm_parse_bcv_interest_xlsx($xlsxPath, $minYear = 1997) {
        if (!is_file($xlsxPath)) { return ['ok' => false, 'message' => 'Archivo XLSX no encontrado.', 'rows' => []]; }
        $zip = nomina_pm_xlsx_open_archive($xlsxPath);
        if (!$zip) { return ['ok' => false, 'message' => 'No se pudo abrir el XLSX. Activa ZipArchive/ext-zip o PharData en PHP y verifica que sea un archivo Excel válido.', 'rows' => []]; }
        $shared = nomina_pm_xlsx_shared_strings($zip);
        $sheets = nomina_pm_xlsx_sheet_paths($zip);
        $parsed = [];
        foreach ($sheets as $sheet) {
            $sheetName = (string)$sheet['name'];
            $sheetNameLower = strtolower($sheetName);
            if (strpos($sheetNameLower, 'netp') === 0 || strpos($sheetNameLower, 'module') === 0) { continue; }
            $currentYear = 0;
            $rows = nomina_pm_xlsx_sheet_rows($zip, $sheet['path'], $shared);
            foreach ($rows as $r) {
                $c0 = nomina_pm_xlsx_text_clean($r[0] ?? '');
                if (preg_match('/^(19|20)\d{2}$/', $c0)) { $currentYear = (int)$c0; continue; }
                $month = nomina_pm_month_number_es($c0);
                if ($month <= 0 || $currentYear <= 0 || $currentYear < (int)$minYear) { continue; }
                $tasaPromedio = nomina_pm_xlsx_number($r[4] ?? 0);
                $tasaActiva = nomina_pm_xlsx_number($r[5] ?? 0);
                if ($tasaPromedio <= 0) { continue; }
                if ($tasaActiva <= 0) { $tasaActiva = $tasaPromedio; }
                $tasaPasiva = round(($tasaPromedio * 2) - $tasaActiva, 4);
                if ($tasaPasiva < 0) { $tasaPasiva = 0.0; }
                $periodoYm = sprintf('%04d-%02d', $currentYear, $month);
                $parsed[$periodoYm] = [
                    'periodo_ym' => $periodoYm,
                    'anio' => $currentYear,
                    'mes' => $month,
                    'mes_nombre' => $c0,
                    'gaceta_numero' => nomina_pm_xlsx_text_clean($r[2] ?? ''),
                    'gaceta_fecha' => nomina_pm_excel_date_to_iso($r[3] ?? ''),
                    'tasa_activa_anual' => round($tasaActiva, 4),
                    'tasa_pasiva_anual' => round($tasaPasiva, 4),
                    'tasa_promedio_anual' => round($tasaPromedio, 4),
                    'hoja' => $sheetName,
                ];
            }
        }
        nomina_pm_xlsx_close_archive($zip);
        krsort($parsed);
        $rows = array_values($parsed);
        return ['ok' => true, 'message' => 'Filas detectadas en XLSX: ' . count($rows), 'rows' => $rows];
    }
}

if (!function_exists('nomina_pm_import_interest_rates_xlsx')) {
    function nomina_pm_import_interest_rates_xlsx($conexion, $tmpPath, $originalName, array $options = []) {
        nomina_pm_ensure_schema($conexion);
        $minYear = isset($options['min_year']) ? (int)$options['min_year'] : 1997;
        if ($minYear < 1975 || $minYear > (int)date('Y') + 1) { $minYear = 1997; }
        $updateExisting = !empty($options['update_existing']);
        $aplica = isset($options['aplica_prestaciones']) ? (int)$options['aplica_prestaciones'] : 1;
        $sourceUrl = trim((string)($options['fuente_url'] ?? 'https://www.bcv.org.ve/estadisticas/tasas-de-interes'));
        $user = nomina_pm_current_user_label();
        $parsed = nomina_pm_parse_bcv_interest_xlsx($tmpPath, $minYear);
        if (!$parsed['ok']) { return $parsed + ['inserted' => 0, 'updated' => 0, 'skipped' => 0]; }
        if (!$parsed['rows']) { return ['ok' => false, 'message' => 'No se detectaron tasas mensuales en el XLSX. Verifica que sea el archivo de tasas de interés para prestaciones sociales del BCV.', 'inserted' => 0, 'updated' => 0, 'skipped' => 0, 'sample' => []]; }
        $inserted = 0; $updated = 0; $skipped = 0; $errors = [];
        mysqli_begin_transaction($conexion);
        foreach ($parsed['rows'] as $row) {
            $periodo = $row['periodo_ym'];
            $id = 0;
            $stmt = mysqli_prepare($conexion, "SELECT id FROM nomina_tasas_interes_bcv WHERE periodo_ym=? LIMIT 1");
            if ($stmt) {
                mysqli_stmt_bind_param($stmt, 's', $periodo);
                mysqli_stmt_execute($stmt);
                mysqli_stmt_bind_result($stmt, $foundId);
                if (mysqli_stmt_fetch($stmt)) { $id = (int)$foundId; }
                mysqli_stmt_close($stmt);
            }
            $obs = 'Importado desde XLS BCV ' . basename((string)$originalName) . ' / hoja ' . $row['hoja'] . '. Promedio activa-pasiva y activa publicados por BCV; pasiva calculada como (2 x promedio) - activa.';
            $fileLabel = mb_substr(basename((string)$originalName), 0, 180, 'UTF-8');
            if ($id > 0 && !$updateExisting) { $skipped++; continue; }
            if ($id > 0) {
                $stmt = mysqli_prepare($conexion, "UPDATE nomina_tasas_interes_bcv SET anio=?, mes=?, tasa_activa_anual=?, tasa_pasiva_anual=?, tasa_promedio_anual=?, gaceta_numero=?, gaceta_fecha=?, fecha_publicacion=?, fuente_url=?, fuente_archivo=?, aplica_prestaciones=?, observacion=?, imported_at=NOW(), imported_by=? WHERE id=?");
                if (!$stmt) { $errors[] = mysqli_error($conexion); break; }
                mysqli_stmt_bind_param($stmt, 'iidddsssssissi', $row['anio'], $row['mes'], $row['tasa_activa_anual'], $row['tasa_pasiva_anual'], $row['tasa_promedio_anual'], $row['gaceta_numero'], $row['gaceta_fecha'], $row['gaceta_fecha'], $sourceUrl, $fileLabel, $aplica, $obs, $user, $id);
                $ok = mysqli_stmt_execute($stmt);
                if (!$ok) { $errors[] = mysqli_stmt_error($stmt); }
                else { $updated++; }
                mysqli_stmt_close($stmt);
            } else {
                $stmt = mysqli_prepare($conexion, "INSERT INTO nomina_tasas_interes_bcv (periodo_ym, anio, mes, tasa_activa_anual, tasa_pasiva_anual, tasa_promedio_anual, gaceta_numero, gaceta_fecha, fecha_publicacion, fuente_url, fuente_archivo, aplica_prestaciones, observacion, imported_at, imported_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?)");
                if (!$stmt) { $errors[] = mysqli_error($conexion); break; }
                mysqli_stmt_bind_param($stmt, 'siidddsssssiss', $periodo, $row['anio'], $row['mes'], $row['tasa_activa_anual'], $row['tasa_pasiva_anual'], $row['tasa_promedio_anual'], $row['gaceta_numero'], $row['gaceta_fecha'], $row['gaceta_fecha'], $sourceUrl, $fileLabel, $aplica, $obs, $user);
                $ok = mysqli_stmt_execute($stmt);
                if (!$ok) { $errors[] = mysqli_stmt_error($stmt); }
                else { $inserted++; }
                mysqli_stmt_close($stmt);
            }
        }
        if ($errors) { mysqli_rollback($conexion); }
        else { mysqli_commit($conexion); }
        $sample = array_slice($parsed['rows'], 0, 8);
        $msg = 'XLS procesado. Detectadas: ' . count($parsed['rows']) . '. Insertadas: ' . $inserted . '. Actualizadas: ' . $updated . '. Omitidas existentes: ' . $skipped . '.';
        if ($errors) { $msg .= ' Error: ' . implode(' | ', array_slice($errors, 0, 3)); }
        return ['ok' => empty($errors), 'message' => $msg, 'inserted' => $inserted, 'updated' => $updated, 'skipped' => $skipped, 'sample' => $sample];
    }
}


/* ============================================================================
 * v7.3.2 - Reconstrucción segura de prestaciones
 * Respaldar, limpiar eventos automáticos/intereses, sincronizar historial real,
 * regenerar base e intereses sin tocar corridas cerradas ni datos maestros.
 * ========================================================================== */
if (!function_exists('nomina_pm_v732_count_generated_events')) {
    function nomina_pm_v732_count_generated_events($conexion) {
        nomina_pm_ensure_schema($conexion);
        $count = 0;
        $sql = "SELECT COUNT(*) AS c
                FROM nomina_prestaciones_eventos
                WHERE tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL')
                  AND estado='REGISTRADO'";
        $res = mysqli_query($conexion, $sql);
        if ($res && ($r = mysqli_fetch_assoc($res))) { $count = (int)$r['c']; }
        return $count;
    }
}

if (!function_exists('nomina_pm_v732_count_interests')) {
    function nomina_pm_v732_count_interests($conexion) {
        nomina_pm_ensure_schema($conexion);
        $count = 0;
        $res = mysqli_query($conexion, "SELECT COUNT(*) AS c FROM nomina_prestaciones_intereses WHERE estado='REGISTRADO'");
        if ($res && ($r = mysqli_fetch_assoc($res))) { $count = (int)$r['c']; }
        return $count;
    }
}

if (!function_exists('nomina_pm_v732_count_events_without_bcv')) {
    function nomina_pm_v732_count_events_without_bcv($conexion) {
        nomina_pm_ensure_schema($conexion);
        $count = 0;
        $sql = "SELECT COUNT(*) AS c
                FROM nomina_prestaciones_eventos
                WHERE estado='REGISTRADO'
                  AND tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL')
                  AND (tasa_bcv_usada IS NULL OR tasa_bcv_usada <= 0)";
        $res = mysqli_query($conexion, $sql);
        if ($res && ($r = mysqli_fetch_assoc($res))) { $count = (int)$r['c']; }
        return $count;
    }
}

if (!function_exists('nomina_pm_v732_create_rebuild_backups')) {
    function nomina_pm_v732_create_rebuild_backups($conexion) {
        nomina_pm_ensure_schema($conexion);
        $ok1 = mysqli_query($conexion, "CREATE TABLE IF NOT EXISTS backup_v732_nomina_prestaciones_eventos LIKE nomina_prestaciones_eventos");
        $ok2 = mysqli_query($conexion, "CREATE TABLE IF NOT EXISTS backup_v732_nomina_prestaciones_intereses LIKE nomina_prestaciones_intereses");
        if (!$ok1 || !$ok2) {
            return ['ok' => false, 'message' => 'No se pudieron crear tablas backup v7.3.2: ' . mysqli_error($conexion)];
        }
        // INSERT IGNORE evita duplicar por PRIMARY KEY si el operador repite la reconstrucción.
        $b1 = mysqli_query($conexion, "INSERT IGNORE INTO backup_v732_nomina_prestaciones_eventos SELECT * FROM nomina_prestaciones_eventos");
        $e1 = mysqli_affected_rows($conexion);
        $b2 = mysqli_query($conexion, "INSERT IGNORE INTO backup_v732_nomina_prestaciones_intereses SELECT * FROM nomina_prestaciones_intereses");
        $e2 = mysqli_affected_rows($conexion);
        if (!$b1 || !$b2) {
            return ['ok' => false, 'message' => 'No se pudo copiar backup v7.3.2: ' . mysqli_error($conexion)];
        }
        return ['ok' => true, 'message' => 'Backups v7.3.2 actualizados. Eventos respaldados nuevos: ' . max(0,$e1) . '. Intereses respaldados nuevos: ' . max(0,$e2) . '.', 'eventos' => max(0,$e1), 'intereses' => max(0,$e2)];
    }
}

if (!function_exists('nomina_pm_v732_clear_generated_prestaciones')) {
    function nomina_pm_v732_clear_generated_prestaciones($conexion) {
        nomina_pm_ensure_schema($conexion);
        $errors = [];
        // Intereses no tienen FK al evento; se respaldan y se regeneran completos para evitar tasas huérfanas.
        $okInt = mysqli_query($conexion, "DELETE FROM nomina_prestaciones_intereses WHERE estado='REGISTRADO'");
        $deletedInterests = mysqli_affected_rows($conexion);
        if (!$okInt) { $errors[] = 'Intereses: ' . mysqli_error($conexion); }

        // Solo se reconstruyen bases automáticas. Se conservan saldos iniciales, anticipos, egresos o ajustes manuales.
        $okEvt = mysqli_query($conexion, "DELETE FROM nomina_prestaciones_eventos
            WHERE estado='REGISTRADO'
              AND tipo IN ('TRIMESTRAL','ADICIONAL_ANUAL')");
        $deletedEvents = mysqli_affected_rows($conexion);
        if (!$okEvt) { $errors[] = 'Eventos: ' . mysqli_error($conexion); }

        return [
            'ok' => empty($errors),
            'message' => 'Limpieza de prestaciones automáticas. Eventos eliminados: ' . max(0,$deletedEvents) . '. Intereses eliminados: ' . max(0,$deletedInterests) . ($errors ? '. Errores: ' . implode(' | ', $errors) : ''),
            'eventos' => max(0,$deletedEvents),
            'intereses' => max(0,$deletedInterests),
        ];
    }
}

if (!function_exists('nomina_pm_v732_rebuild_all')) {
    function nomina_pm_v732_rebuild_all($conexion, $fechaCorte, $tasaRef, $utilidadesDiasRef = 30, $metodoInteres = 'PROMEDIO_ACTIVA_PASIVA', $permitirOmitirIngreso = true) {
        nomina_pm_ensure_schema($conexion);
        $fechaCorte = nomina_date_to_iso($fechaCorte) ?: date('Y-m-d');
        $tasaRef = nomina_normalize_decimal($tasaRef ?? 0);
        $utilidadesDiasRef = nomina_normalize_decimal($utilidadesDiasRef ?? 30, 30);
        if ($tasaRef <= 0) { return ['ok' => false, 'message' => 'Indica una tasa BCV de referencia mayor a cero.']; }

        $tasas = nomina_pm_count_interest_rates($conexion);
        if ($tasas <= 0) {
            return ['ok' => false, 'message' => 'No hay tasas de interés BCV cargadas. Importa el XLS oficial antes de reconstruir prestaciones.'];
        }

        $invalidos = nomina_pm_invalid_hire_date_people($conexion, $fechaCorte);
        if (!$permitirOmitirIngreso && count($invalidos) > 0) {
            return ['ok' => false, 'message' => 'Hay trabajadores activos sin fecha de ingreso válida. Corrige las fichas antes de reconstruir. Trabajadores bloqueados: ' . count($invalidos) . '.'];
        }
        $invalidIds = [];
        foreach ($invalidos as $iw) { $invalidIds[(int)$iw['id']] = true; }

        mysqli_begin_transaction($conexion);
        $messages = [];
        $backup = nomina_pm_v732_create_rebuild_backups($conexion);
        if (!$backup['ok']) { mysqli_rollback($conexion); return $backup; }
        $messages[] = $backup['message'];

        if (function_exists('nomina_pm_clean_orphan_snapshots')) {
            $clean = nomina_pm_clean_orphan_snapshots($conexion);
            $messages[] = $clean['message'];
        }
        if (function_exists('nomina_pm_repair_mojibake_data')) {
            $repair = nomina_pm_repair_mojibake_data($conexion);
            $messages[] = $repair['message'];
        }

        $sync = nomina_pm_sync_salary_history_from_closed_runs($conexion);
        if (!$sync['ok']) { mysqli_rollback($conexion); return ['ok' => false, 'message' => 'No se pudo sincronizar historial salarial: ' . $sync['message']]; }
        $messages[] = $sync['message'];

        $clear = nomina_pm_v732_clear_generated_prestaciones($conexion);
        if (!$clear['ok']) { mysqli_rollback($conexion); return ['ok' => false, 'message' => $clear['message']]; }
        $messages[] = $clear['message'];

        $personas = nomina_fetch_active_people($conexion);
        $balances = [];
        $omitidosIngreso = 0;
        foreach ($personas as $persona) {
            $pid = (int)($persona['id'] ?? 0);
            if (isset($invalidIds[$pid])) { $omitidosIngreso++; continue; }
            $balances[$pid] = nomina_prestaciones_calculate_balance($conexion, $persona, $fechaCorte, $tasaRef, $utilidadesDiasRef);
        }

        $base = nomina_pm_generate_base_for_many($conexion, $balances, $fechaCorte, 'Reconstrucción segura v7.3.2 con historial salarial y tasa BCV congelada');
        if (!$base['ok']) { mysqli_rollback($conexion); return ['ok' => false, 'message' => 'No se pudo regenerar base: ' . $base['message']]; }
        $messages[] = $base['message'];

        $personasValidas = [];
        foreach ($personas as $persona) {
            $pid = (int)($persona['id'] ?? 0);
            if (!isset($invalidIds[$pid])) { $personasValidas[] = $persona; }
        }
        $ints = nomina_pm_generate_interests_for_many($conexion, $personasValidas, $fechaCorte, $metodoInteres, 'Reconstrucción segura v7.3.2 de intereses con tasas BCV importadas');
        // Si faltan meses de tasas se permite commit, porque se registran los intereses posibles y se informa al operador.
        $messages[] = $ints['message'];

        mysqli_commit($conexion);
        $sinBcv = nomina_pm_v732_count_events_without_bcv($conexion);
        $hist = nomina_pm_recent_salary_history_count($conexion);
        $final = 'Reconstrucción v7.3.2 finalizada. Trabajadores válidos procesados: ' . count($personasValidas) . '. Omitidos por fecha de ingreso: ' . $omitidosIngreso . '. Historial salarial: ' . $hist . '. Eventos automáticos sin tasa BCV: ' . $sinBcv . '. ' . implode(' ', $messages);
        return ['ok' => true, 'message' => $final, 'created_base' => (int)($base['created'] ?? 0), 'created_interests' => (int)($ints['created'] ?? 0), 'missing_rates' => (array)($ints['missing'] ?? [])];
    }
}

?>
