<?php
if (!function_exists('nomina_ensure_vacaciones_schema')) {
    function nomina_ensure_vacaciones_schema($conexion) {
        $sql = "CREATE TABLE IF NOT EXISTS nomina_vacaciones_eventos (
            id INT AUTO_INCREMENT PRIMARY KEY,
            persona_id INT NOT NULL,
            fecha_documento DATE NOT NULL,
            fecha_inicio DATE DEFAULT NULL,
            fecha_fin DATE DEFAULT NULL,
            tipo VARCHAR(30) NOT NULL DEFAULT 'DISFRUTE',
            ciclo_anual INT DEFAULT NULL,
            es_historico TINYINT(1) NOT NULL DEFAULT 0,
            dias_disfrute DECIMAL(10,2) NOT NULL DEFAULT 0,
            dias_bono DECIMAL(10,2) NOT NULL DEFAULT 0,
            monto_disfrute_bs DECIMAL(18,2) NOT NULL DEFAULT 0,
            monto_bono_bs DECIMAL(18,2) NOT NULL DEFAULT 0,
            periodo_id INT DEFAULT NULL,
            corrida_id INT 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,
            KEY idx_persona_fecha (persona_id, fecha_documento),
            KEY idx_persona_ciclo (persona_id, ciclo_anual),
            KEY idx_periodo (periodo_id),
            KEY idx_estado (estado)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        $ok = @mysqli_query($conexion, $sql) !== false || mysqli_errno($conexion) === 0;
        foreach ([
            "ALTER TABLE nomina_vacaciones_eventos ADD COLUMN ciclo_anual INT DEFAULT NULL AFTER tipo",
            "ALTER TABLE nomina_vacaciones_eventos ADD COLUMN es_historico TINYINT(1) NOT NULL DEFAULT 0 AFTER ciclo_anual",
            "ALTER TABLE nomina_vacaciones_eventos MODIFY tipo VARCHAR(30) NOT NULL DEFAULT 'DISFRUTE'"
        ] as $ddl) {
            @mysqli_query($conexion, $ddl);
        }
        return $ok;
    }
}

if (!function_exists('nomina_vacaciones_completed_years')) {
    function nomina_vacaciones_completed_years($fechaIngreso, $fechaCorte) {
        $ingreso = nomina_date_to_iso($fechaIngreso);
        $corte = nomina_date_to_iso($fechaCorte);
        if ($ingreso === '' || $corte === '') {
            return 0;
        }
        try {
            $d1 = new DateTime($ingreso);
            $d2 = new DateTime($corte);
        } catch (Exception $e) {
            return 0;
        }
        if ($d2 < $d1) {
            return 0;
        }
        return (int)$d1->diff($d2)->y;
    }
}

if (!function_exists('nomina_vacaciones_days_for_cycle')) {
    function nomina_vacaciones_days_for_cycle($completedYears) {
        $completedYears = (int)$completedYears;
        if ($completedYears < 1) {
            return 0;
        }
        return 15 + min(max($completedYears - 1, 0), 15);
    }
}

if (!function_exists('nomina_vacaciones_total_accrued_days')) {
    function nomina_vacaciones_total_accrued_days($completedYears) {
        $completedYears = (int)$completedYears;
        $total = 0;
        for ($i = 1; $i <= $completedYears; $i++) {
            $total += nomina_vacaciones_days_for_cycle($i);
        }
        return $total;
    }
}

if (!function_exists('nomina_vacaciones_total_bonus_days')) {
    function nomina_vacaciones_total_bonus_days($completedYears) {
        return nomina_vacaciones_total_accrued_days($completedYears);
    }
}


if (!function_exists('nomina_vacaciones_cycle_years_map')) {
    function nomina_vacaciones_cycle_years_map($completedYears) {
        $completedYears = (int)$completedYears;
        $map = [];
        for ($i = 1; $i <= $completedYears; $i++) {
            $map[$i] = nomina_vacaciones_days_for_cycle($i);
        }
        return $map;
    }
}

if (!function_exists('nomina_vacaciones_fetch_cycle_usage')) {
    function nomina_vacaciones_fetch_cycle_usage($conexion, $personaId) {
        nomina_ensure_vacaciones_schema($conexion);
        $personaId = (int)$personaId;
        $rows = [];
        $sql = "SELECT ciclo_anual,
                       SUM(CASE WHEN estado='REGISTRADO' THEN dias_disfrute ELSE 0 END) AS dias_disfrute,
                       SUM(CASE WHEN estado='REGISTRADO' THEN dias_bono ELSE 0 END) AS dias_bono,
                       SUM(CASE WHEN estado='REGISTRADO' THEN monto_disfrute_bs ELSE 0 END) AS monto_disfrute_bs,
                       SUM(CASE WHEN estado='REGISTRADO' THEN monto_bono_bs ELSE 0 END) AS monto_bono_bs
                FROM nomina_vacaciones_eventos
                WHERE persona_id = {$personaId} AND IFNULL(ciclo_anual,0) > 0
                GROUP BY ciclo_anual";
        $res = mysqli_query($conexion, $sql);
        if ($res) {
            while ($row = mysqli_fetch_assoc($res)) {
                $rows[(int)$row['ciclo_anual']] = $row;
            }
        }
        return $rows;
    }
}

if (!function_exists('nomina_vacaciones_fetch_historical_usage')) {
    function nomina_vacaciones_fetch_historical_usage($conexion, $personaId) {
        nomina_ensure_vacaciones_schema($conexion);
        $personaId = (int)$personaId;
        $sql = "SELECT SUM(CASE WHEN estado='REGISTRADO' THEN dias_disfrute ELSE 0 END) AS dias_disfrute,
                       SUM(CASE WHEN estado='REGISTRADO' THEN dias_bono ELSE 0 END) AS dias_bono
                FROM nomina_vacaciones_eventos
                WHERE persona_id = {$personaId} AND es_historico = 1";
        $res = mysqli_query($conexion, $sql);
        return ($res && mysqli_num_rows($res)) ? mysqli_fetch_assoc($res) : ['dias_disfrute'=>0,'dias_bono'=>0];
    }
}

if (!function_exists('nomina_vacaciones_cycle_exists')) {
    function nomina_vacaciones_cycle_exists($conexion, $personaId, $cycleNo) {
        $personaId = (int)$personaId;
        $cycleNo = (int)$cycleNo;
        if ($personaId <= 0 || $cycleNo <= 0) { return false; }
        $sql = "SELECT id FROM nomina_vacaciones_eventos WHERE persona_id = {$personaId} AND ciclo_anual = {$cycleNo} AND es_historico = 0 AND estado='REGISTRADO' AND tipo='DISFRUTE' LIMIT 1";
        $res = mysqli_query($conexion, $sql);
        return $res && mysqli_num_rows($res) > 0;
    }
}

if (!function_exists('nomina_vacaciones_fetch_events_summary')) {
    function nomina_vacaciones_fetch_events_summary($conexion) {
        nomina_ensure_vacaciones_schema($conexion);
        $rows = [];
        $sql = "SELECT persona_id,
                       SUM(CASE WHEN estado = 'REGISTRADO' THEN dias_disfrute ELSE 0 END) AS dias_disfrute,
                       SUM(CASE WHEN estado = 'REGISTRADO' THEN dias_bono ELSE 0 END) AS dias_bono,
                       SUM(CASE WHEN estado = 'REGISTRADO' THEN monto_disfrute_bs ELSE 0 END) AS monto_disfrute_bs,
                       SUM(CASE WHEN estado = 'REGISTRADO' THEN monto_bono_bs ELSE 0 END) AS monto_bono_bs
                FROM nomina_vacaciones_eventos
                GROUP BY persona_id";
        $res = mysqli_query($conexion, $sql);
        if ($res) {
            while ($row = mysqli_fetch_assoc($res)) {
                $rows[(int)$row['persona_id']] = $row;
            }
        }
        return $rows;
    }
}

if (!function_exists('nomina_vacaciones_fetch_recent_events')) {
    function nomina_vacaciones_fetch_recent_events($conexion, $limit = 20) {
        nomina_ensure_vacaciones_schema($conexion);
        $limit = max(1, (int)$limit);
        $sql = "SELECT e.*, p.nombre, p.apellido, p.cedula
                FROM nomina_vacaciones_eventos e
                LEFT JOIN personas p ON p.id = e.persona_id
                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_vacaciones_find_persona')) {
    function nomina_vacaciones_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_vacaciones_calculate_balance')) {
    function nomina_vacaciones_calculate_balance(array $persona, array $summary, $fechaCorte, $tasaBcv, array $cycleUsage = [], array $historicalUsage = []) {
        $completedYears = nomina_vacaciones_completed_years($persona['fecha_ingreso'] ?? '', $fechaCorte);
        $currentCycle = $completedYears;
        $cycleDays = nomina_vacaciones_days_for_cycle($completedYears);
        $cycleBonusDays = $cycleDays;
        $totalAccruedDays = nomina_vacaciones_total_accrued_days($completedYears);
        $totalBonusAccrued = nomina_vacaciones_total_bonus_days($completedYears);

        $usedDays = nomina_normalize_decimal($summary['dias_disfrute'] ?? 0);
        $usedBonusDays = nomina_normalize_decimal($summary['dias_bono'] ?? 0);
        $pendingDays = max(0, $totalAccruedDays - $usedDays);
        $pendingBonusDays = max(0, $totalBonusAccrued - $usedBonusDays);

        $histUsedDays = nomina_normalize_decimal($historicalUsage['dias_disfrute'] ?? 0);
        $histUsedBonusDays = nomina_normalize_decimal($historicalUsage['dias_bono'] ?? 0);
        $cycleUsedDays = nomina_normalize_decimal($cycleUsage[$currentCycle]['dias_disfrute'] ?? 0);
        $cycleUsedBonusDays = nomina_normalize_decimal($cycleUsage[$currentCycle]['dias_bono'] ?? 0);
        $pendingCycleDays = max(0, $cycleDays - $cycleUsedDays);
        $pendingCycleBonusDays = max(0, $cycleBonusDays - $cycleUsedBonusDays);

        $salaryInfo = nomina_get_effective_salary_info($persona);
        $salaryBs = nomina_get_effective_salary_bs($persona, $tasaBcv);
        $dailyBs = $salaryBs / 30.0;
        $cycleEnjoymentBs = $pendingCycleDays * $dailyBs;
        $cycleBonusBs = $pendingCycleBonusDays * $dailyBs;
        $pendingEnjoymentBs = $pendingDays * $dailyBs;
        $pendingBonusBs = $pendingBonusDays * $dailyBs;

        return [
            'completed_years' => $completedYears,
            'current_cycle' => $currentCycle,
            'cycle_days' => $cycleDays,
            'cycle_bonus_days' => $cycleBonusDays,
            'total_accrued_days' => $totalAccruedDays,
            'total_bonus_accrued_days' => $totalBonusAccrued,
            'used_days' => $usedDays,
            'used_bonus_days' => $usedBonusDays,
            'historical_used_days' => $histUsedDays,
            'historical_used_bonus_days' => $histUsedBonusDays,
            'cycle_used_days' => $cycleUsedDays,
            'cycle_used_bonus_days' => $cycleUsedBonusDays,
            'pending_days' => $pendingDays,
            'pending_bonus_days' => $pendingBonusDays,
            'pending_cycle_days' => $pendingCycleDays,
            'pending_cycle_bonus_days' => $pendingCycleBonusDays,
            'salary_source' => $salaryInfo['source'],
            'salary_amount' => $salaryInfo['amount'],
            'salary_currency' => $salaryInfo['currency'],
            'salary_bs' => $salaryBs,
            'daily_bs' => $dailyBs,
            'cycle_enjoyment_bs' => $cycleEnjoymentBs,
            'cycle_bonus_bs' => $cycleBonusBs,
            'pending_enjoyment_bs' => $pendingEnjoymentBs,
            'pending_bonus_bs' => $pendingBonusBs,
            'pending_total_bs' => $pendingEnjoymentBs + $pendingBonusBs,
        ];
    }
}

if (!function_exists('nomina_vacaciones_ensure_novedad_row')) {

    function nomina_vacaciones_ensure_novedad_row($conexion, $schema, $periodoId, $personaId) {
        $periodoId = (int)$periodoId;
        $personaId = (int)$personaId;
        $res = mysqli_query($conexion, "SELECT id FROM novedades_periodo WHERE periodo_id = {$periodoId} AND persona_id = {$personaId} LIMIT 1");
        if ($res && mysqli_num_rows($res)) {
            $row = mysqli_fetch_assoc($res);
            return (int)$row['id'];
        }

        $columns = ['periodo_id', 'persona_id'];
        $values = [$periodoId, $personaId];
        foreach (['dias_ausencia_periodo','horas_retardo_periodo','dias_ausencia_mes','bono_manual_bs','otros_descuentos_bs','prestamo_bs','pago_vacaciones_bs','observaciones'] as $key) {
            if (!empty($schema[$key])) {
                $columns[] = $schema[$key];
                $values[] = $key === 'observaciones' ? "''" : '0';
            }
        }
        $colSql = '`' . implode('`,`', $columns) . '`';
        $valSql = implode(',', array_map(function($v){ return is_string($v) && $v !== "''" ? (int)$v : $v; }, $values));
        $sql = "INSERT INTO novedades_periodo ({$colSql}) VALUES ({$valSql})";
        if (mysqli_query($conexion, $sql)) {
            return (int)mysqli_insert_id($conexion);
        }
        return 0;
    }
}


if (!function_exists('nomina_vacaciones_period_label')) {
    function nomina_vacaciones_period_label($conexion, $periodoId) {
        $periodoId = (int)$periodoId;
        if ($periodoId <= 0) { return ''; }
        $res = mysqli_query($conexion, "SELECT codigo FROM periodos_nomina WHERE id = {$periodoId} LIMIT 1");
        if ($res && mysqli_num_rows($res)) {
            $row = mysqli_fetch_assoc($res);
            return $row['codigo'];
        }
        return (string)$periodoId;
    }
}

if (!function_exists('nomina_vacaciones_sync_to_period')) {
    function nomina_vacaciones_sync_to_period($conexion, $schema, $periodoId, $personaId, $montoBs, $observacion) {
        $periodoId = (int)$periodoId;
        $personaId = (int)$personaId;
        $montoBs = nomina_normalize_decimal($montoBs);
        if ($periodoId <= 0 || $personaId <= 0 || empty($schema['pago_vacaciones_bs'])) {
            return ['ok' => false, 'message' => 'La estructura de novedades aún no tiene el campo de pago manual de vacaciones.'];
        }
        nomina_vacaciones_ensure_novedad_row($conexion, $schema, $periodoId, $personaId);
        $obsSql = '';
        if (!empty($schema['observaciones']) && trim((string)$observacion) !== '') {
            $obsEsc = mysqli_real_escape_string($conexion, trim((string)$observacion));
            $obsSql = ", `{$schema['observaciones']}` = TRIM(CONCAT(IFNULL(`{$schema['observaciones']}`, ''), CASE WHEN IFNULL(`{$schema['observaciones']}`, '') = '' THEN '' ELSE ' | ' END, '{$obsEsc}'))";
        }
        $sql = "UPDATE novedades_periodo SET `{$schema['pago_vacaciones_bs']}` = {$montoBs}{$obsSql} WHERE periodo_id = {$periodoId} AND persona_id = {$personaId}";
        $ok = mysqli_query($conexion, $sql);
        return ['ok' => (bool)$ok, 'message' => $ok ? 'Pago legal sugerido copiado a novedades del período.' : mysqli_error($conexion)];
    }
}
