<?php
if (!function_exists('nomina_ensure_prestaciones_schema')) {
    function nomina_ensure_prestaciones_schema($conexion) {
        $sql = "CREATE TABLE IF NOT EXISTS nomina_prestaciones_eventos (
            id INT AUTO_INCREMENT PRIMARY KEY,
            persona_id INT NOT NULL,
            fecha_documento DATE NOT NULL,
            fecha_evento DATE NOT NULL,
            tipo VARCHAR(30) NOT NULL DEFAULT 'TRIMESTRAL',
            periodo_ref VARCHAR(40) NOT NULL DEFAULT '',
            anio_servicio INT NOT NULL DEFAULT 0,
            trimestre_nro INT NOT NULL DEFAULT 0,
            es_historico TINYINT(1) NOT NULL DEFAULT 0,
            dias DECIMAL(10,2) NOT NULL DEFAULT 0,
            salario_normal_mensual_bs DECIMAL(18,4) NOT NULL DEFAULT 0,
            salario_diario_bs DECIMAL(18,4) NOT NULL DEFAULT 0,
            salario_integral_diario_bs DECIMAL(18,4) NOT NULL DEFAULT 0,
            monto_bs DECIMAL(18,2) NOT NULL DEFAULT 0,
            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_tipo_ref (persona_id, tipo, periodo_ref),
            KEY idx_persona_fecha (persona_id, fecha_evento),
            KEY idx_estado (estado)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $ok = @mysqli_query($conexion, $sql) !== false || mysqli_errno($conexion) === 0;
        @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN salario_normal_mensual_bs DECIMAL(18,4) NOT NULL DEFAULT 0 AFTER dias");
        @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN salario_diario_bs DECIMAL(18,4) NOT NULL DEFAULT 0 AFTER salario_normal_mensual_bs");
        @mysqli_query($conexion, "ALTER TABLE nomina_prestaciones_eventos ADD COLUMN salario_integral_diario_bs DECIMAL(18,4) NOT NULL DEFAULT 0 AFTER salario_diario_bs");
        return $ok;
    }
}

if (!function_exists('nomina_prestaciones_global_regular_bonus_bs')) {
    function nomina_prestaciones_global_regular_bonus_bs($conexion, $tasaBcv = 1.0) {
        $total = 0.0;
        $sql = "SELECT * FROM conceptos_nomina WHERE activo = 1 AND aplica_global = 1 AND tipo = 'ASIG' AND modo_calculo = 'FIJO' AND incide_salario_normal = 1";
        $res = mysqli_query($conexion, $sql);
        if ($res) {
            while ($row = mysqli_fetch_assoc($res)) {
                if (nomina_es_concepto_base($row['codigo'] ?? '')) {
                    continue;
                }
                $monto = nomina_normalize_decimal($row['monto_fijo'] ?? 0);
                $moneda = strtoupper((string)($row['moneda_monto_fijo'] ?? 'VES'));
                $total += ($moneda === 'USD') ? ($monto * (float)$tasaBcv) : $monto;
            }
        }
        return $total;
    }
}

if (!function_exists('nomina_prestaciones_service_parts')) {
    function nomina_prestaciones_service_parts($fechaIngreso, $fechaCorte) {
        $ingreso = nomina_date_to_iso($fechaIngreso);
        $corte = nomina_date_to_iso($fechaCorte);
        if ($ingreso === '' || $corte === '') {
            return ['years' => 0, 'months' => 0, 'days' => 0];
        }
        try {
            $d1 = new DateTime($ingreso);
            $d2 = new DateTime($corte);
        } catch (Exception $e) {
            return ['years' => 0, 'months' => 0, 'days' => 0];
        }
        if ($d2 < $d1) {
            return ['years' => 0, 'months' => 0, 'days' => 0];
        }
        $diff = $d1->diff($d2);
        return ['years' => (int)$diff->y, 'months' => (int)$diff->m, 'days' => (int)$diff->d];
    }
}

if (!function_exists('nomina_prestaciones_retroactive_years')) {
    function nomina_prestaciones_retroactive_years($fechaIngreso, $fechaCorte) {
        $parts = nomina_prestaciones_service_parts($fechaIngreso, $fechaCorte);
        $years = (int)$parts['years'];
        if ($parts['months'] > 6 || ($parts['months'] == 6 && $parts['days'] > 0)) {
            $years++;
        }
        return $years;
    }
}

if (!function_exists('nomina_prestaciones_quarter_periods')) {
    function nomina_prestaciones_quarter_periods($fechaIngreso, $fechaCorte) {
        $ingreso = nomina_date_to_iso($fechaIngreso);
        $corte = nomina_date_to_iso($fechaCorte);
        if ($ingreso === '' || $corte === '') { return []; }
        try {
            $start = new DateTime($ingreso);
            $cut = new DateTime($corte);
        } catch (Exception $e) {
            return [];
        }
        $rows = [];
        $quarterNo = 1;
        $currentStart = clone $start;
        while (true) {
            $currentEnd = clone $currentStart;
            $currentEnd->modify('+3 months');
            $currentEnd->modify('-1 day');
            if ($currentEnd > $cut) {
                break;
            }
            $rows[] = [
                'quarter_no' => $quarterNo,
                'start' => $currentStart->format('Y-m-d'),
                'end' => $currentEnd->format('Y-m-d'),
                'period_ref' => 'TRI-' . $quarterNo . '-' . $currentEnd->format('Ymd'),
            ];
            $currentStart = clone $currentEnd;
            $currentStart->modify('+1 day');
            $quarterNo++;
        }
        return $rows;
    }
}

if (!function_exists('nomina_prestaciones_annual_extra_periods')) {
    function nomina_prestaciones_annual_extra_periods($fechaIngreso, $fechaCorte) {
        $ingreso = nomina_date_to_iso($fechaIngreso);
        $corte = nomina_date_to_iso($fechaCorte);
        if ($ingreso === '' || $corte === '') { return []; }
        $completedYears = nomina_prestaciones_service_parts($ingreso, $corte)['years'];
        try {
            $base = new DateTime($ingreso);
            $cut = new DateTime($corte);
        } catch (Exception $e) {
            return [];
        }
        $rows = [];
        for ($year = 2; $year <= $completedYears; $year++) {
            $ann = clone $base;
            $ann->modify('+' . $year . ' years');
            if ($ann > $cut) { break; }
            $days = min(($year - 1) * 2, 30);
            $rows[] = [
                'year_no' => $year,
                'days' => $days,
                'date' => $ann->format('Y-m-d'),
                'period_ref' => 'ANU-' . $year,
            ];
        }
        return $rows;
    }
}

if (!function_exists('nomina_prestaciones_reference_salary')) {
    function nomina_prestaciones_reference_salary($conexion, array $persona, $tasaBcv, $utilidadesDiasRef = 30) {
        $baseMonthlyBs = nomina_get_effective_salary_bs($persona, $tasaBcv);
        $salaryInfo = nomina_get_effective_salary_info($persona);
        $cargoBonusUsd = nomina_normalize_decimal($persona['bono_extra_usd_mensual'] ?? 0);
        $cargoBonusBs = $cargoBonusUsd > 0 ? $cargoBonusUsd * (float)$tasaBcv : 0.0;
        $globalBonusBs = nomina_prestaciones_global_regular_bonus_bs($conexion, $tasaBcv);
        $monthlyNormalBs = $baseMonthlyBs + $cargoBonusBs + $globalBonusBs;
        $dailyNormalBs = $monthlyNormalBs / 30.0;

        $completedYears = nomina_prestaciones_service_parts($persona['fecha_ingreso'] ?? '', date('Y-m-d'))['years'];
        $vacDaysRef = $completedYears >= 1 ? nomina_vacaciones_days_for_cycle($completedYears) : 15;
        $vacAlicuota = $dailyNormalBs * ($vacDaysRef / 360.0);
        $utilAlicuota = $dailyNormalBs * (max(0, (float)$utilidadesDiasRef) / 360.0);
        $integralDailyBs = $dailyNormalBs + $vacAlicuota + $utilAlicuota;

        return [
            'salary_source' => $salaryInfo['source'],
            'salary_currency' => $salaryInfo['currency'],
            'salary_amount' => $salaryInfo['amount'],
            'base_monthly_bs' => round($baseMonthlyBs, 2),
            'cargo_bonus_bs' => round($cargoBonusBs, 2),
            'global_bonus_bs' => round($globalBonusBs, 2),
            'monthly_normal_bs' => round($monthlyNormalBs, 2),
            'daily_normal_bs' => round($dailyNormalBs, 4),
            'vac_days_ref' => $vacDaysRef,
            'vac_alicuota_bs' => round($vacAlicuota, 4),
            'utilidades_dias_ref' => (float)$utilidadesDiasRef,
            'util_alicuota_bs' => round($utilAlicuota, 4),
            'integral_daily_bs' => round($integralDailyBs, 4),
        ];
    }
}

if (!function_exists('nomina_prestaciones_fetch_events_map')) {
    function nomina_prestaciones_fetch_events_map($conexion, $personaId) {
        nomina_ensure_prestaciones_schema($conexion);
        $personaId = (int)$personaId;
        $rows = [];
        $res = mysqli_query($conexion, "SELECT * FROM nomina_prestaciones_eventos WHERE persona_id = {$personaId} AND estado = 'REGISTRADO' ORDER BY fecha_evento ASC, id ASC");
        if ($res) {
            while ($row = mysqli_fetch_assoc($res)) {
                $rows[$row['tipo'] . '|' . $row['periodo_ref']] = $row;
            }
        }
        return $rows;
    }
}

if (!function_exists('nomina_prestaciones_fetch_recent_events')) {
    function nomina_prestaciones_fetch_recent_events($conexion, $limit = 25) {
        nomina_ensure_prestaciones_schema($conexion);
        $limit = max(1, (int)$limit);
        $sql = "SELECT e.*, p.nombre, p.apellido, p.cedula
                FROM nomina_prestaciones_eventos e
                LEFT JOIN personas p ON p.id = e.persona_id
                WHERE e.estado = 'REGISTRADO'
                ORDER BY e.fecha_documento DESC, e.id DESC
                LIMIT {$limit}";
        $rows = [];
        $res = mysqli_query($conexion, $sql);
        if ($res) {
            while ($row = mysqli_fetch_assoc($res)) {
                $rows[] = $row;
            }
        }
        return $rows;
    }
}



if (!function_exists('nomina_prestaciones_fetch_person_events')) {
    function nomina_prestaciones_fetch_person_events($conexion, $personaId, $limit = 50) {
        nomina_ensure_prestaciones_schema($conexion);
        $personaId = (int)$personaId;
        $limit = max(1, (int)$limit);
        $rows = [];
        $sql = "SELECT * FROM nomina_prestaciones_eventos WHERE persona_id = {$personaId} AND estado = 'REGISTRADO' ORDER BY fecha_documento DESC, id DESC LIMIT {$limit}";
        $res = mysqli_query($conexion, $sql);
        if ($res) {
            while ($row = mysqli_fetch_assoc($res)) {
                $rows[] = $row;
            }
        }
        return $rows;
    }
}

if (!function_exists('nomina_prestaciones_event_type_label')) {
    function nomina_prestaciones_event_type_label($tipo) {
        $tipo = strtoupper(trim((string)$tipo));
        $map = [
            'TRIMESTRAL' => 'Trimestral',
            'ADICIONAL_ANUAL' => 'Adicional anual',
            'AJUSTE_HISTORICO' => 'Saldo inicial histórico',
            'ANTICIPO' => 'Anticipo',
            'EGRESO' => 'Liquidación egreso',
        ];
        return isset($map[$tipo]) ? $map[$tipo] : $tipo;
    }
}

if (!function_exists('nomina_prestaciones_has_pending_base')) {
    function nomina_prestaciones_has_pending_base(array $balance) {
        return ((int)($balance['missing_quarters'] ?? 0) > 0) || ((int)($balance['missing_annuals'] ?? 0) > 0);
    }
}

if (!function_exists('nomina_prestaciones_find_persona')) {
    function nomina_prestaciones_find_persona(array $personas, $personaId) {
        $personaId = (int)$personaId;
        foreach ($personas as $persona) {
            if ((int)$persona['id'] === $personaId) {
                return $persona;
            }
        }
        return null;
    }
}

if (!function_exists('nomina_prestaciones_calculate_balance')) {
    function nomina_prestaciones_calculate_balance($conexion, array $persona, $fechaCorte, $tasaBcv, $utilidadesDiasRef = 30) {
        nomina_ensure_prestaciones_schema($conexion);
        $service = nomina_prestaciones_service_parts($persona['fecha_ingreso'] ?? '', $fechaCorte);
        $ref = nomina_prestaciones_reference_salary($conexion, $persona, $tasaBcv, $utilidadesDiasRef);
        $quarterPeriods = nomina_prestaciones_quarter_periods($persona['fecha_ingreso'] ?? '', $fechaCorte);
        $annualPeriods = nomina_prestaciones_annual_extra_periods($persona['fecha_ingreso'] ?? '', $fechaCorte);
        $eventsMap = nomina_prestaciones_fetch_events_map($conexion, $persona['id']);

        $theoreticalGuarantee = 0.0;
        $registeredGuarantee = 0.0;
        $historicalOpening = 0.0;
        $missingQuarters = 0;
        $missingAnnuals = 0;

        foreach ($quarterPeriods as $p) {
            $amount = $ref['integral_daily_bs'] * 15.0;
            $theoreticalGuarantee += $amount;
            $key = 'TRIMESTRAL|' . $p['period_ref'];
            if (isset($eventsMap[$key])) {
                $registeredGuarantee += nomina_normalize_decimal($eventsMap[$key]['monto_bs'] ?? 0);
            } else {
                $missingQuarters++;
            }
        }
        foreach ($annualPeriods as $p) {
            $amount = $ref['integral_daily_bs'] * (float)$p['days'];
            $theoreticalGuarantee += $amount;
            $key = 'ADICIONAL_ANUAL|' . $p['period_ref'];
            if (isset($eventsMap[$key])) {
                $registeredGuarantee += nomina_normalize_decimal($eventsMap[$key]['monto_bs'] ?? 0);
            } else {
                $missingAnnuals++;
            }
        }
        foreach ($eventsMap as $row) {
            if (($row['tipo'] ?? '') === 'AJUSTE_HISTORICO') {
                $historicalOpening += nomina_normalize_decimal($row['monto_bs'] ?? 0);
                $registeredGuarantee += nomina_normalize_decimal($row['monto_bs'] ?? 0);
                $theoreticalGuarantee += nomina_normalize_decimal($row['monto_bs'] ?? 0);
            }
        }

        $retroYears = nomina_prestaciones_retroactive_years($persona['fecha_ingreso'] ?? '', $fechaCorte);
        $retroactiveAmount = $ref['integral_daily_bs'] * 30.0 * $retroYears;
        $favorableBase = max($theoreticalGuarantee, $retroactiveAmount);

        $recentEvents = nomina_prestaciones_fetch_person_events($conexion, $persona['id'], 12);
        $pendingControlBs = max($favorableBase - $registeredGuarantee, 0);
        return [
            'persona' => $persona,
            'service' => $service,
            'ref' => $ref,
            'quarter_periods' => $quarterPeriods,
            'annual_periods' => $annualPeriods,
            'events_map' => $eventsMap,
            'quarter_count' => count($quarterPeriods),
            'annual_count' => count($annualPeriods),
            'missing_quarters' => $missingQuarters,
            'missing_annuals' => $missingAnnuals,
            'guarantee_estimated_bs' => round($theoreticalGuarantee, 2),
            'guarantee_registered_bs' => round($registeredGuarantee, 2),
            'historical_opening_bs' => round($historicalOpening, 2),
            'retroactive_years' => $retroYears,
            'retroactive_bs' => round($retroactiveAmount, 2),
            'favorable_base_bs' => round($favorableBase, 2),
            'pending_control_bs' => round($pendingControlBs, 2),
            'recent_events' => $recentEvents,
        ];
    }
}
