<?php
/**
 * Cron Job: Sync Services for All Countries
 ** Developer: core tech web design
 * Whatsapp: +234 9162671360
 * Telegram: @jeditech
 ** 
 * Runs every 30 minutes via cPanel cron.
 * cPanel command:
 *   /usr/local/bin/php /home/softombe/public_html/cron/sync_services_cron.php
 *
 * This script:
 *  1. Fetches the current USD→NGN exchange rate
 *  2. Loops through all active countries (otp_server)
 *  3. Calls smscodes.io /GetCountryPrices for each country
 *  4. Converts prices USD → NGN with admin markup
 *  5. Upserts services into the service table
 *  6. Fetches service logos from /GetServiceCodeLogos
 *  7. Logs every run in cron_log table and price_settings
 */

// ── Prevent browser access ────────────────────────────────────────────────────
if (php_sapi_name() !== 'cli') {
    http_response_code(403);
    exit('This script can only be run from the command line.');
}

// ── Increase limits for long-running sync ─────────────────────────────────────
set_time_limit(0);
ini_set('memory_limit', '256M');

$start_time = time();

// ── Load config (provides $pdo) ───────────────────────────────────────────────
$config_path = dirname(__DIR__) . '/include/config.php';
if (!file_exists($config_path)) {
    log_cron("ERROR: config.php not found at {$config_path}");
    exit(1);
}

// Some config files use session; prevent errors in CLI
if (session_status() === PHP_SESSION_NONE) {
    // Suppress session start warnings in CLI
    @session_start();
}

include($config_path);

if (!isset($pdo)) {
    log_cron("ERROR: \$pdo not available after loading config.");
    exit(1);
}

log_cron("=== Cron sync started at " . date('Y-m-d H:i:s') . " ===");

// ── Helper: cURL GET ──────────────────────────────────────────────────────────
function cron_curl_get(string $url, int $timeout = 90): string
{
    $ch = curl_init($url);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_TIMEOUT        => $timeout,
        CURLOPT_CONNECTTIMEOUT => 20,
        CURLOPT_SSL_VERIFYPEER => false,
        CURLOPT_SSL_VERIFYHOST => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_USERAGENT      => 'Mozilla/5.0 (compatible; Bot/1.0)',
        CURLOPT_ENCODING       => '',
    ]);
    $res = curl_exec($ch);
    $err = curl_error($ch);
    curl_close($ch);
    if ($res === false || $res === '') {
        return 'CURL_ERROR: ' . ($err ?: 'Empty response');
    }
    return (string)$res;
}

// ── Helper: Build smscodes.io API URL ─────────────────────────────────────────
function cron_build_url(string $api_url, string $method, string $api_key, array $params = []): string
{
    $url = rtrim($api_url, '/') . '/' . ltrim($method, '/') . '?key=' . urlencode($api_key);
    foreach ($params as $k => $v) {
        $url .= '&' . urlencode($k) . '=' . urlencode($v);
    }
    return $url;
}

// ── Helper: Console + file log ────────────────────────────────────────────────
function log_cron(string $message): void
{
    $line = '[' . date('Y-m-d H:i:s') . '] ' . $message . "\n";
    echo $line;

    // Also write to a log file
    $log_dir = dirname(__DIR__) . '/cron/logs';
    if (!is_dir($log_dir)) {
        @mkdir($log_dir, 0755, true);
    }
    @file_put_contents($log_dir . '/sync_' . date('Y-m-d') . '.log', $line, FILE_APPEND | LOCK_EX);
}


// ══════════════════════════════════════════════════════════════════
// STEP 1: Load price settings (markup, exchange rate)
// ══════════════════════════════════════════════════════════════════
try {
    $ps_rows = $pdo->query("SELECT setting_key, setting_value FROM price_settings")->fetchAll(PDO::FETCH_KEY_PAIR);
} catch (PDOException $e) {
    $ps_rows = [];
}

$markup_pct = isset($ps_rows['markup_percent']) ? (float)$ps_rows['markup_percent'] : 35.0;
$usd_to_ngn = isset($ps_rows['usd_to_ngn']) && (float)$ps_rows['usd_to_ngn'] > 0
    ? (float)$ps_rows['usd_to_ngn'] : 0.0;


// ══════════════════════════════════════════════════════════════════
// STEP 2: Load manually-set USD→NGN rate from price_settings
// (Rate is set by admin in Sync Services page — cron never overwrites it)
// ══════════════════════════════════════════════════════════════════
if ($usd_to_ngn <= 0) {
    $usd_to_ngn = 1600.0;
    log_cron("WARNING: No manual rate found in price_settings. Using fallback ₦1600.00.");
    log_cron("Please set the rate in Admin → Sync Services → Exchange Rate.");
} else {
    log_cron("Using manual rate from admin: \$1 = ₦" . number_format($usd_to_ngn, 2));
}

log_cron("Markup: {$markup_pct}%");


// ══════════════════════════════════════════════════════════════════
// STEP 3: Load all active servers (countries)
// ══════════════════════════════════════════════════════════════════
try {
    $servers = $pdo->query("SELECT * FROM otp_server WHERE status = '1' ORDER BY server_name ASC")->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    log_cron("FATAL: Could not load servers: " . $e->getMessage());
    record_cron_log($pdo, $start_time, 0, 0, 0, 'failed', 'DB error loading servers');
    exit(1);
}

$total_servers = count($servers);
log_cron("Found {$total_servers} active countries to sync.");

if ($total_servers === 0) {
    log_cron("No active countries. Exiting.");
    record_cron_log($pdo, $start_time, 0, 0, 0, 'success', 'No active countries');
    exit(0);
}


// ══════════════════════════════════════════════════════════════════
// STEP 4: Cache API details (to avoid repeated queries)
// ══════════════════════════════════════════════════════════════════
$api_cache = [];
try {
    $api_all = $pdo->query("SELECT * FROM api_detail")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($api_all as $a) {
        $api_cache[(int)$a['id']] = $a;
    }
} catch (PDOException $e) {
    log_cron("FATAL: Could not load API details: " . $e->getMessage());
    record_cron_log($pdo, $start_time, 0, 0, 0, 'failed', 'DB error loading APIs');
    exit(1);
}


// ══════════════════════════════════════════════════════════════════
// STEP 5: Fetch service logos once (shared across all countries)
// ══════════════════════════════════════════════════════════════════
$logo_map = [];
// Use the first available API to fetch logos
$first_api = reset($api_cache);
if ($first_api) {
    log_cron("Fetching service logos...");
    $logo_url = cron_build_url($first_api['api_url'], 'GetServiceCodeLogos', $first_api['api_key']);
    $logo_raw = cron_curl_get($logo_url, 30);
    $logo_resp = json_decode($logo_raw, true);
    if (is_array($logo_resp) && isset($logo_resp['Services'])) {
        foreach ($logo_resp['Services'] as $svc) {
            if (isset($svc['ServiceID'], $svc['ServiceLogo'])) {
                $logo_map[trim($svc['ServiceID'])] = trim($svc['ServiceLogo']);
            }
        }
        log_cron("Loaded " . count($logo_map) . " service logos.");
    } else {
        log_cron("WARNING: Could not fetch service logos.");
    }
}


// ══════════════════════════════════════════════════════════════════
// STEP 6: Sync each country
// ══════════════════════════════════════════════════════════════════
$total_updated  = 0;
$total_inserted = 0;
$total_skipped  = 0;
$countries_done = 0;
$countries_failed = 0;

foreach ($servers as $idx => $server) {
    $sid         = (int)$server['id'];
    $api_id      = (int)$server['api_id'];
    $country_iso = $server['server_code'];
    $country_name = $server['server_name'];

    log_cron("--- [{$idx}/{$total_servers}] Syncing: {$country_name} ({$country_iso}) ---");

    // Get API details
    if (!isset($api_cache[$api_id])) {
        log_cron("  SKIP: No API found for api_id={$api_id}");
        $countries_failed++;
        continue;
    }
    $api = $api_cache[$api_id];

    // Fetch prices from smscodes.io
    $price_url = cron_build_url($api['api_url'], 'GetCountryPrices', $api['api_key'], ['iso' => $country_iso]);
    $raw       = cron_curl_get($price_url, 90);
    $pdata     = json_decode($raw, true);

    if (!is_array($pdata) || !isset($pdata['Prices']) || empty($pdata['Prices'])) {
        $err = '';
        if (is_array($pdata) && isset($pdata['Error']) && $pdata['Error'] !== 'Non') {
            $err = $pdata['Error'];
        } elseif (strpos($raw, 'CURL_ERROR') === 0) {
            $err = $raw;
        }
        log_cron("  SKIP: No prices returned. {$err}");
        $countries_failed++;
        continue;
    }

    $countries_done++;
    $country_inserted = 0;
    $country_updated  = 0;
    $country_skipped  = 0;

    foreach ($pdata['Prices'] as $entry) {
        $svc_name  = trim($entry['ServiceName'] ?? '');
        $svc_id    = trim($entry['ServiceID']   ?? '');
        $usd_price = (float)($entry['Price']    ?? 0);

        if (empty($svc_name) || empty($svc_id)) continue;

        if ($usd_price <= 0) {
            $country_skipped++;
            continue;
        }

        // Convert price
        $naira_base  = $usd_price * $usd_to_ngn;
        $markup_val  = $naira_base * ($markup_pct / 100);
        $naira_price = round($naira_base + $markup_val, 2);

        $logo_file = $logo_map[$svc_id] ?? '';

        try {
            $check = $pdo->prepare("SELECT id FROM service WHERE service_id = :sid AND server_id = :svid LIMIT 1");
            $check->execute([':sid' => $svc_id, ':svid' => $sid]);
            $existing = $check->fetch(PDO::FETCH_ASSOC);

            if ($existing) {
                $pdo->prepare(
                    "UPDATE service SET service_name = :name, service_price = :price, service_logo = :logo WHERE id = :id"
                )->execute([
                    ':name'  => $svc_name,
                    ':price' => (string)$naira_price,
                    ':logo'  => $logo_file,
                    ':id'    => $existing['id'],
                ]);
                $country_updated++;
            } else {
                $pdo->prepare(
                    "INSERT INTO service (service_name, service_id, server_id, service_price, service_logo, status)
                     VALUES (:name, :sid, :svid, :price, :logo, '1')"
                )->execute([
                    ':name'  => $svc_name,
                    ':sid'   => $svc_id,
                    ':svid'  => $sid,
                    ':price' => (string)$naira_price,
                    ':logo'  => $logo_file,
                ]);
                $country_inserted++;
            }
        } catch (PDOException $e) {
            $country_skipped++;
        }
    }

    $total_inserted += $country_inserted;
    $total_updated  += $country_updated;
    $total_skipped  += $country_skipped;

    log_cron("  Done: +{$country_inserted} new, {$country_updated} updated, {$country_skipped} skipped");

    // Record per-country sync time
    try {
        $pdo->prepare(
            "INSERT INTO price_settings (setting_key, setting_value)
               VALUES (:key, NOW())
             ON DUPLICATE KEY UPDATE setting_value = NOW(), updated_at = NOW()"
        )->execute([':key' => 'sync_country_' . $sid]);
    } catch (PDOException $e) { /* non-fatal */ }

    // Small delay between countries to be kind to the API
    if ($idx < $total_servers - 1) {
        sleep(2);
    }
}


// ══════════════════════════════════════════════════════════════════
// STEP 7: Record cron run
// ══════════════════════════════════════════════════════════════════
$duration = time() - $start_time;
$status   = $countries_failed === $total_servers ? 'failed' : 'success';
$details  = "Rate: \$1=₦" . number_format($usd_to_ngn, 2) . " | Markup: {$markup_pct}% | "
          . "Countries: {$countries_done}/{$total_servers} | "
          . "Inserted: {$total_inserted} | Updated: {$total_updated} | Skipped: {$total_skipped}";

if ($countries_failed > 0) {
    $details .= " | Failed: {$countries_failed}";
}

record_cron_log($pdo, $start_time, $countries_done, $total_inserted + $total_updated, $total_skipped, $status, $details);

// Update last run time in price_settings
try {
    $pdo->prepare(
        "INSERT INTO price_settings (setting_key, setting_value)
           VALUES ('cron_last_run', :t), ('cron_last_status', :s)
         ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), updated_at = NOW()"
    )->execute([':t' => date('Y-m-d H:i:s', $start_time), ':s' => $status . ' — ' . $details]);
} catch (PDOException $e) {
    log_cron("WARNING: Could not update cron_last_run: " . $e->getMessage());
}

log_cron("=== Cron sync finished in {$duration}s ===");
log_cron("Total: {$countries_done} countries, +{$total_inserted} new, {$total_updated} updated, {$total_skipped} skipped");


// ══════════════════════════════════════════════════════════════════
// HELPER: Record a cron log entry
// ══════════════════════════════════════════════════════════════════
function record_cron_log(PDO $pdo, int $start_time, int $countries, int $updated, int $skipped, string $status, string $details): void
{
    $duration = time() - $start_time;
    try {
        $pdo->prepare(
            "INSERT INTO cron_log (run_time, countries_processed, services_updated, services_skipped, duration_seconds, status, details)
             VALUES (:rt, :cp, :su, :ss, :ds, :st, :dt)"
        )->execute([
            ':rt' => date('Y-m-d H:i:s', $start_time),
            ':cp' => $countries,
            ':su' => $updated,
            ':ss' => $skipped,
            ':ds' => $duration,
            ':st' => $status,
            ':dt' => $details,
        ]);
    } catch (PDOException $e) {
        log_cron("WARNING: Could not write cron_log: " . $e->getMessage());
    }
}