<?php
if (!function_exists('nomina_bcv_default_source_url')) {
    function nomina_bcv_default_source_url() {
        return 'https://www.bcv.org.ve/seccionportal/tipo-de-cambio-de-referencia-bsmoneda-extranjera';
    }
}

if (!function_exists('nomina_bcv_ensure_schema')) {
    function nomina_bcv_ensure_schema($conexion) {
        $sql = "CREATE TABLE IF NOT EXISTS nomina_bcv_rate (
            id INT AUTO_INCREMENT PRIMARY KEY,
            rate_date DATE NOT NULL,
            rate_value DECIMAL(18,8) NOT NULL,
            currency_from VARCHAR(8) NOT NULL DEFAULT 'USD',
            currency_to VARCHAR(8) NOT NULL DEFAULT 'VES',
            source_type VARCHAR(16) NOT NULL DEFAULT 'AUTO',
            source_url VARCHAR(255) DEFAULT NULL,
            source_note VARCHAR(255) DEFAULT NULL,
            status TINYINT(1) NOT NULL DEFAULT 1,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_nomina_bcv_rate (rate_date, currency_from, currency_to)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
        return (bool)mysqli_query($conexion, $sql);
    }
}

if (!function_exists('nomina_bcv_normalize_number')) {
    function nomina_bcv_normalize_number($value) {
        $value = trim((string)$value);
        if ($value === '') return 0.0;
        if (strpos($value, ',') !== false && strpos($value, '.') !== false) {
            if (strrpos($value, ',') > strrpos($value, '.')) {
                $value = str_replace('.', '', $value);
                $value = str_replace(',', '.', $value);
            } else {
                $value = str_replace(',', '', $value);
            }
        } elseif (strpos($value, ',') !== false) {
            $value = str_replace('.', '', $value);
            $value = str_replace(',', '.', $value);
        }
        return is_numeric($value) ? (float)$value : 0.0;
    }
}

if (!function_exists('nomina_bcv_download_url')) {
    function nomina_bcv_download_url($url) {
        if (function_exists('curl_init')) {
            $ch = curl_init($url);
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
            curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 10);
            curl_setopt($ch, CURLOPT_TIMEOUT, 20);
            curl_setopt($ch, CURLOPT_USERAGENT, 'NominaBCV/1.0');
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
            $data = curl_exec($ch);
            curl_close($ch);
            return $data;
        }

        $context = stream_context_create(array(
            'http' => array(
                'timeout' => 20,
                'header' => "User-Agent: NominaBCV/1.0\r\n"
            ),
            'ssl' => array(
                'verify_peer' => false,
                'verify_peer_name' => false,
            )
        ));

        return @file_get_contents($url, false, $context);
    }
}

if (!function_exists('nomina_bcv_extract_usd_rate_from_html')) {
    function nomina_bcv_extract_usd_rate_from_html($html) {
        $clean = preg_replace('/\s+/u', ' ', html_entity_decode((string)$html, ENT_QUOTES, 'UTF-8'));
        $patterns = array(
            '/USD.{0,300}?([0-9]{1,3}(?:[\.,][0-9]{3})*(?:[\.,][0-9]{2,8}))/iu',
            '/D[oó]lar(?:es)?(?:\s+estadounidense)?[^0-9]{0,200}([0-9]{1,3}(?:[\.,][0-9]{3})*(?:[\.,][0-9]{2,8}))/iu',
            '/"USD".{0,200}?"([0-9]{1,3}(?:[\.,][0-9]{3})*(?:[\.,][0-9]{2,8}))"/iu',
            '/usd.{0,200}?rate.{0,50}?([0-9]{1,3}(?:[\.,][0-9]{3})*(?:[\.,][0-9]{2,8}))/iu'
        );
        foreach ($patterns as $pattern) {
            if (preg_match($pattern, $clean, $m)) {
                $num = nomina_bcv_normalize_number($m[1]);
                if ($num > 0) return $num;
            }
        }
        return 0.0;
    }
}

if (!function_exists('nomina_bcv_fetch_official_rate')) {
    function nomina_bcv_fetch_official_rate($url = null) {
        $url = trim((string)($url ?: nomina_bcv_default_source_url()));
        $html = nomina_bcv_download_url($url);
        if ($html === false || $html === '') {
            return array('ok' => false, 'error' => 'No se pudo descargar la fuente oficial BCV.', 'source_url' => $url);
        }
        $rate = nomina_bcv_extract_usd_rate_from_html($html);
        if ($rate <= 0) {
            return array('ok' => false, 'error' => 'No se pudo interpretar la tasa USD desde la página oficial BCV.', 'source_url' => $url);
        }
        return array('ok' => true, 'rate_value' => $rate, 'source_url' => $url);
    }
}

if (!function_exists('nomina_bcv_get_latest_rate')) {
    function nomina_bcv_get_latest_rate($conexion, $onlyActive = true) {
        nomina_bcv_ensure_schema($conexion);
        $sql = "SELECT * FROM nomina_bcv_rate WHERE currency_from='USD' AND currency_to='VES'" . ($onlyActive ? ' AND status = 1' : '') . " ORDER BY rate_date DESC, id DESC LIMIT 1";
        $res = mysqli_query($conexion, $sql);
        return ($res && mysqli_num_rows($res)) ? mysqli_fetch_assoc($res) : null;
    }
}

if (!function_exists('nomina_bcv_save_rate')) {
    function nomina_bcv_save_rate($conexion, $rateDate, $rateValue, $sourceType = 'AUTO', $sourceUrl = '', $sourceNote = '') {
        nomina_bcv_ensure_schema($conexion);
        $rateDate = preg_replace('/[^0-9\-]/', '', (string)$rateDate);
        $rateValue = (float)$rateValue;
        $sourceType = mysqli_real_escape_string($conexion, substr((string)$sourceType, 0, 16));
        $sourceUrl = mysqli_real_escape_string($conexion, substr((string)$sourceUrl, 0, 255));
        $sourceNote = mysqli_real_escape_string($conexion, substr((string)$sourceNote, 0, 255));
        $check = mysqli_query($conexion, "SELECT id FROM nomina_bcv_rate WHERE rate_date='" . mysqli_real_escape_string($conexion, $rateDate) . "' AND currency_from='USD' AND currency_to='VES' LIMIT 1");
        if ($check && mysqli_num_rows($check)) {
            $row = mysqli_fetch_assoc($check);
            $sql = "UPDATE nomina_bcv_rate SET rate_value={$rateValue}, source_type='{$sourceType}', source_url='{$sourceUrl}', source_note='{$sourceNote}', status=1 WHERE id=" . (int)$row['id'];
            $ok = mysqli_query($conexion, $sql);
            return array('ok' => (bool)$ok, 'mode' => 'update', 'id' => (int)$row['id'], 'error' => $ok ? null : mysqli_error($conexion));
        }
        $sql = "INSERT INTO nomina_bcv_rate (rate_date, rate_value, currency_from, currency_to, source_type, source_url, source_note, status) VALUES ('" . mysqli_real_escape_string($conexion, $rateDate) . "', {$rateValue}, 'USD', 'VES', '{$sourceType}', '{$sourceUrl}', '{$sourceNote}', 1)";
        $ok = mysqli_query($conexion, $sql);
        return array('ok' => (bool)$ok, 'mode' => 'insert', 'id' => $ok ? (int)mysqli_insert_id($conexion) : 0, 'error' => $ok ? null : mysqli_error($conexion));
    }
}

if (!function_exists('nomina_bcv_auto_refresh')) {
    function nomina_bcv_auto_refresh($conexion, $force = false) {
        nomina_bcv_ensure_schema($conexion);
        $latest = nomina_bcv_get_latest_rate($conexion, true);
        $today = date('Y-m-d');
        $shouldFetch = $force;
        if (!$latest) {
            $shouldFetch = true;
        } else {
            $lastUpdateTs = !empty($latest['updated_at']) ? strtotime($latest['updated_at']) : 0;
            $isTodayRate = ($latest['rate_date'] ?? '') === $today;
            $recentEnough = $lastUpdateTs > 0 && $lastUpdateTs >= (time() - 6 * 3600);
            if (!$isTodayRate || !$recentEnough) {
                $shouldFetch = true;
            }
        }

        $message = '';
        $status = 'cache';
        if ($shouldFetch) {
            $fetch = nomina_bcv_fetch_official_rate();
            if (!empty($fetch['ok'])) {
                $save = nomina_bcv_save_rate($conexion, $today, $fetch['rate_value'], 'AUTO', $fetch['source_url'], 'Actualización automática independiente de nómina');
                if (!empty($save['ok'])) {
                    $latest = nomina_bcv_get_latest_rate($conexion, true);
                    $status = 'updated';
                    $message = 'Tasa BCV actualizada automáticamente desde la fuente oficial.';
                } else {
                    $status = 'warning';
                    $message = 'No se pudo guardar la tasa BCV automática: ' . ($save['error'] ?? 'error desconocido');
                }
            } else {
                $status = 'warning';
                $message = $fetch['error'] ?? 'No se pudo actualizar la tasa BCV en este momento.';
            }
        }

        return array(
            'latest' => $latest,
            'status' => $status,
            'message' => $message,
            'updated_at' => $latest['updated_at'] ?? null,
            'rate_date' => $latest['rate_date'] ?? null,
            'rate_value' => isset($latest['rate_value']) ? (float)$latest['rate_value'] : null,
        );
    }
}
