<?php
/**
 * Cron Job: Sync smspva.com 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/YOUR_USER/public_html/cron/sync_smspva_cron.php
 *
 * This script:
 *  1. Fetches the current USD→NGN exchange rate
 *  2. Loops through all active smspva countries (otp_server joined to smspva api_detail)
 *  3. Calls smspva /activation/serviceprice/{country} for each country
 *  4. Converts USD prices → NGN with smspva markup
 *  5. Upserts services into the shared `service` table + smspva_service_cache
 *  6. Logs every run in smspva_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.');
}

set_time_limit(0);
ini_set('memory_limit', '256M');

$start_time = time();

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

if (session_status() === PHP_SESSION_NONE) {
    @session_start();
}

// Do NOT define VALID_ACCESS here — this server's config.php skips $pdo when it is set.
// The working sync_services_cron.php never defines it and loads $pdo correctly.
include($config_path);

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

// Load smspva helper functions only — otp_functions.php is not needed
$smspva_fn = dirname(__DIR__) . '/include/smspva_functions.php';
if (!file_exists($smspva_fn)) {
    cron_log("ERROR: smspva_functions.php not found at {$smspva_fn}");
    exit(1);
}
require_once $smspva_fn;

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

// ══════════════════════════════════════════════════════════════════
// STEP 1: Load smspva API config
// ══════════════════════════════════════════════════════════════════
try {
    $api_stmt = $pdo->query("SELECT * FROM api_detail WHERE api_type='smspva' LIMIT 1");
    $api = $api_stmt->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    cron_log("FATAL: Could not load smspva API detail: " . $e->getMessage());
    record_smspva_cron_log($pdo, $start_time, 0, 0, 0, 'failed', 'DB error loading API');
    exit(1);
}

if (!$api) {
    cron_log("FATAL: No smspva API entry found in api_detail. Run the SQL schema first.");
    record_smspva_cron_log($pdo, $start_time, 0, 0, 0, 'failed', 'smspva API not configured');
    exit(1);
}

cron_log("smspva API: {$api['api_url']} (id={$api['id']})");

// ══════════════════════════════════════════════════════════════════
// STEP 2: Load price settings
// ══════════════════════════════════════════════════════════════════
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['smspva_markup_percent'])
    ? (float)$ps_rows['smspva_markup_percent']
    : (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 3: 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) {
    // No valid rate in DB — use a safe fallback and warn
    $usd_to_ngn = 1600.0;
    cron_log("WARNING: No manual rate found in price_settings. Using fallback ₦1600.00.");
    cron_log("Please set the rate in Admin → Sync Services → Exchange Rate.");
} else {
    cron_log("Using manual rate from admin: \$1 = ₦" . number_format($usd_to_ngn, 2));
}

cron_log("Markup: {$markup_pct}% | Rate: ₦" . number_format($usd_to_ngn, 2));

// ══════════════════════════════════════════════════════════════════
// STEP 4: Check if smspva is enabled
// ══════════════════════════════════════════════════════════════════
if (($ps_rows['smspva_api_enabled'] ?? '1') !== '1') {
    cron_log("smspva is disabled in settings. Exiting.");
    record_smspva_cron_log($pdo, $start_time, 0, 0, 0, 'success', 'smspva disabled');
    exit(0);
}

// ══════════════════════════════════════════════════════════════════
// STEP 5: Load smspva countries (otp_server rows linked to smspva api)
// ══════════════════════════════════════════════════════════════════
try {
    $servers = $pdo->prepare(
        "SELECT * FROM otp_server WHERE api_id = :api_id AND status = '1' ORDER BY server_name ASC"
    );
    $servers->execute([':api_id' => $api['id']]);
    $servers = $servers->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    cron_log("FATAL: Could not load smspva countries: " . $e->getMessage());
    record_smspva_cron_log($pdo, $start_time, 0, 0, 0, 'failed', 'DB error loading countries');
    exit(1);
}

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

if ($total_servers === 0) {
    cron_log("No smspva countries configured. Sync smspva countries first via admin panel.");
    record_smspva_cron_log($pdo, $start_time, 0, 0, 0, 'success', 'No smspva countries');
    exit(0);
}

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

// Service name mapping
$name_map = smspva_cron_service_name_map();

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

    cron_log("--- [" . ($idx + 1) . "/{$total_servers}] Syncing: {$country_name} ({$country_iso}) ---");

    // Fetch service prices for this country
    $prices = smspva_get_country_prices($api['api_url'], $api['api_key'], $country_iso);

    if (!$prices) {
        cron_log("  SKIP: No prices returned for {$country_iso}.");
        $countries_failed++;
        continue;
    }

    $countries_done++;
    $c_inserted = 0;
    $c_updated  = 0;
    $c_skipped  = 0;

    foreach ($prices as $svc_code => $svc_data) {
        $ops = $svc_data['operators'] ?? [];

        // Pick cheapest operator with a valid price
        // smspva always returns count=999 (synthetic), so filter only on price > 0
        $available = array_filter($ops, function($o) { return $o['price'] > 0; });
        if (empty($available)) { $c_skipped++; continue; }

        usort($available, function($a, $b) { return $a['price'] < $b['price'] ? -1 : ($a['price'] > $b['price'] ? 1 : 0); });
        $best      = reset($available);
        $usd_price = (float)$best['price'];

        $naira_price = round($usd_price * $usd_to_ngn * (1 + $markup_pct / 100), 2);
        // Prefer API-returned name, then static map, then raw code
        $api_name = trim($svc_data['service_name'] ?? '');
        $svc_name = ($api_name !== '' && $api_name !== $svc_code)
            ? $api_name
            : ($name_map[$svc_code] ?? strtoupper($svc_code));

        // ── Upsert into shared `service` table ───────────────────────────────
        try {
            $check = $pdo->prepare(
                "SELECT id FROM service WHERE service_id = :sid AND server_id = :svid LIMIT 1"
            );
            $check->execute([':sid' => $svc_code, ':svid' => $sid]);
            $existing = $check->fetch(PDO::FETCH_ASSOC);

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

        // ── Update smspva_service_cache for all operators ──────────────────
        foreach ($ops as $op) {
            if ($op['price'] <= 0) continue;
            $ngn_op = round($op['price'] * $usd_to_ngn * (1 + $markup_pct / 100), 2);
            try {
                $pdo->prepare(
                    "INSERT INTO smspva_service_cache
                        (service_code, service_name, country_code, operator, usd_price, ngn_price, count_available)
                     VALUES (:sc, :sn, :cc, :op, :usd, :ngn, :cnt)
                     ON DUPLICATE KEY UPDATE
                        service_name    = VALUES(service_name),
                        usd_price       = VALUES(usd_price),
                        ngn_price       = VALUES(ngn_price),
                        count_available = VALUES(count_available)"
                )->execute([
                    ':sc'  => $svc_code,
                    ':sn'  => $svc_name,
                    ':cc'  => $country_iso,
                    ':op'  => $op['operator'] ?: 'any',
                    ':usd' => $op['price'],
                    ':ngn' => $ngn_op,
                    ':cnt' => $op['count'],
                ]);
            } catch (PDOException $e) { /* non-fatal */ }
        }
    }

    $total_inserted += $c_inserted;
    $total_updated  += $c_updated;
    $total_skipped  += $c_skipped;

    cron_log("  Done: +{$c_inserted} new, {$c_updated} updated, {$c_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' => 'smspva_sync_country_' . $sid]);
    } catch (PDOException $e) { /* non-fatal */ }

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

// ══════════════════════════════════════════════════════════════════
// STEP 7: Record cron run
// ══════════════════════════════════════════════════════════════════
$duration = time() - $start_time;
$status   = ($countries_failed > 0 && $countries_done === 0) ? '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_smspva_cron_log($pdo, $start_time, $countries_done, $total_inserted + $total_updated, $total_skipped, $status, $details);

// Update last run times in price_settings
try {
    $pdo->prepare(
        "INSERT INTO price_settings (setting_key, setting_value)
           VALUES ('smspva_cron_last_run', :t), ('smspva_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) {
    cron_log("WARNING: Could not update smspva_cron_last_run: " . $e->getMessage());
}

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


// ══════════════════════════════════════════════════════════════════
// HELPERS
// ══════════════════════════════════════════════════════════════════

function cron_log(string $message): void
{
    $line = '[' . date('Y-m-d H:i:s') . '] ' . $message . "\n";
    echo $line;
    $log_dir = dirname(__DIR__) . '/cron/logs';
    if (!is_dir($log_dir)) {
        @mkdir($log_dir, 0755, true);
    }
    @file_put_contents($log_dir . '/smspva_sync_' . date('Y-m-d') . '.log', $line, FILE_APPEND | LOCK_EX);
}

function record_smspva_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 smspva_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) {
        cron_log("WARNING: Could not write smspva_cron_log: " . $e->getMessage());
    }
}

function smspva_cron_service_name_map(): array
{
    // Complete 260-service list from smspva.com official documentation
    return [
        'opt0'=>'myopinions & erewards','opt1'=>'Google (YouTube, Gmail)',
        'opt2'=>'Facebook','opt3'=>'Parimatch','opt4'=>'Mail.ru Group',
        'opt5'=>'OD','opt6'=>'Fiverr','opt7'=>'MS Office 365','opt8'=>'LinkedIn',
        'opt9'=>'Tinder','opt10'=>'AOL','opt11'=>'Viber','opt13'=>'Fotostrana',
        'opt15'=>'Microsoft (Azure, Bing, Skype, etc)','opt16'=>'Instagram (+Threads)',
        'opt17'=>'bet365','opt18'=>'Qiwi','opt19'=>'OTHER (no guarantee)',
        'opt20'=>'WhatsAPP','opt21'=>'EasyPay','opt22'=>'888casino',
        'opt23'=>'Yandex & YooMoney','opt24'=>'WebMoney & ENUM','opt25'=>'BetFair',
        'opt26'=>'CrigslistApp','opt27'=>'PapaJohns',
        'opt28'=>'Gamers set (offgamers.com, G2A.com, seagm.com)',
        'opt29'=>'Telegram','opt30'=>'GrabTaxi','opt31'=>'Drug Vokrug',
        'opt32'=>'Drom.RU','opt33'=>'Mail.RU','opt34'=>'Tencent QQ',
        'opt35'=>'GetTaxi','opt37'=>'Line Messenger','opt38'=>'Sisal',
        'opt39'=>'Verse','opt40'=>'Doordash','opt41'=>'X (Twitter)',
        'opt42'=>'LiveScore','opt43'=>'FastMail','opt44'=>'Amazon',
        'opt45'=>'Discord','opt46'=>'Airbnb','opt47'=>'MoneyLion',
        'opt48'=>'Shopee','opt49'=>'Skout','opt51'=>'CONTACT',
        'opt52'=>'Ticketmaster','opt53'=>'Deliveroo','opt54'=>'Weebly',
        'opt55'=>'ifood','opt56'=>'Badoo','opt57'=>'Proton Mail',
        'opt58'=>'Steam','opt59'=>'Avito','opt60'=>'Lazada',
        'opt61'=>'Alibaba (Taobao, 1688.com)','opt65'=>'Yahoo',
        'opt66'=>'Twilio','opt67'=>'WeChat','opt68'=>'G2A.COM',
        'opt69'=>'VK (no guarantee)','opt70'=>'OLX','opt71'=>'KakaoTalk',
        'opt72'=>'Uber','opt73'=>'Naver','opt74'=>'Taxi Maksim',
        'opt75'=>'Lyft','opt76'=>'CityMobil','opt77'=>'Gameflip',
        'opt78'=>'Blizzard','opt80'=>'WESTSTEIN','opt81'=>'Bolt',
        'opt82'=>'Tango','opt83'=>'PayPal + Ebay','opt84'=>'POF',
        'opt85'=>'giocodigitale.it','opt86'=>'Adidas & Nike','opt88'=>'Yalla.live',
        'opt89'=>'Careem','opt90'=>'Snapchat','opt91'=>'Wise','opt92'=>'DiDi',
        'opt93'=>'Zoho','opt94'=>'JD.com','opt95'=>'NetBet','opt96'=>'MiChat',
        'opt97'=>'Kuper (SberMarket)','opt98'=>'Clubhouse','opt99'=>'KoronaPay',
        'opt100'=>'Mamba','opt101'=>'Netflix','opt103'=>'ICard','opt104'=>'TikTok',
        'opt105'=>'LocalBitcoins','opt106'=>'Wing Money','opt107'=>'Prom.UA',
        'opt108'=>'Glovo & Raketa','opt109'=>'Paddy Power','opt110'=>'Grindr',
        'opt111'=>'IMO','opt112'=>'CoinBase','opt113'=>'OfferUp',
        'opt114'=>'Locanto.com','opt115'=>'foodpanda','opt116'=>'Neteller',
        'opt117'=>'Skrill','opt118'=>'Inboxdollars','opt119'=>'Nico',
        'opt120'=>'Hinge','opt121'=>'Monese','opt122'=>'Paysafecard',
        'opt123'=>'Whoosh','opt124'=>'Credit Karma','opt125'=>'Swagbucks',
        'opt126'=>'Magnit','opt127'=>'Signal','opt129'=>'kwiff.com',
        'opt130'=>'Vinted','opt131'=>'Apple',
        'opt132'=>'OpenAI API (chatGPT, DALL-e 2)','opt133'=>'Revolut',
        'opt134'=>'Schibsted-konto','opt135'=>'blsspain-russia.com',
        'opt136'=>'dundle','opt137'=>'bwin','opt138'=>'Bazos.sk',
        'opt139'=>'fontbet','opt140'=>'Google Voice','opt141'=>'EUROBET',
        'opt142'=>'funpay','opt143'=>'autocosmos.com','opt144'=>'hopper',
        'opt145'=>'bumble','opt146'=>'subito.it','opt147'=>'VooV Meeting',
        'opt148'=>'casa.it','opt149'=>'pm.by','opt150'=>'Czech email services',
        'opt151'=>'novibet.com','opt152'=>'kleinanzeigen.de','opt153'=>'ROOMSTER',
        'opt154'=>'Rambler.ru','opt155'=>'Happn','opt156'=>'mobileDE',
        'opt157'=>'CupidMedia','opt158'=>'Zalo','opt159'=>'Feeld',
        'opt160'=>'U By Prodia','opt161'=>'TANK.RU','opt162'=>'Payoneer',
        'opt163'=>'Wolt','opt164'=>'Leboncoin','opt165'=>'idealista.com',
        'opt166'=>'HUAWEI','opt167'=>'inbox.lv','opt169'=>'Pari.ru',
        'opt170'=>'Royal Canin','opt171'=>'Marktplaats','opt172'=>'WEB.DE',
        'opt173'=>'X World Wallet','opt174'=>'Samsung','opt175'=>'Klarna',
        'opt176'=>'ChoTot','opt177'=>'NHNcorp','opt178'=>'VonageVF',
        'opt179'=>'GetsBet.ro','opt180'=>'Lalamove','opt181'=>'OZON.ru',
        'opt182'=>'LAPOSTE','opt183'=>'PAYSEND','opt184'=>'MOMO',
        'opt185'=>'Samokat','opt186'=>'RusDate','opt187'=>'Beget.com',
        'opt188'=>'GGbet','opt189'=>'foodora','opt190'=>'SNKRDUNK',
        'opt191'=>'TopCashback','opt192'=>'Betano (all countries)',
        'opt193'=>'Ipsos','opt194'=>'ZoomInfo','opt195'=>'Lajumate.ro',
        'opt196'=>'Claude (Anthropic)','opt197'=>'MPSellers','opt198'=>'Nectar',
        'opt199'=>'bunq','opt200'=>'ENEBA','opt201'=>'Casino Plus',
        'opt202'=>'NHNCloud','opt203'=>'HelloTalk','opt204'=>'DenimApp',
        'opt205'=>'Twitch','opt206'=>'WalletHub','opt207'=>'Publi24',
        'opt208'=>'WooPlus','opt209'=>'BANDUS','opt211'=>'MrGreen',
        'opt212'=>'OurTime','opt213'=>'Waitomo','opt214'=>'Cashrewards',
        'opt215'=>'Fbet','opt216'=>'Hey','opt217'=>'Brevo',
        'opt218'=>'TransferGo','opt219'=>'maxline.by','opt220'=>'TOTOGAMING',
        'opt221'=>'Fortuna','opt222'=>'LASVEGAS.RO','opt223'=>'Betmgm',
        'opt224'=>'22bet','opt225'=>'Zasilkovna','opt226'=>'Cash App',
        'opt227'=>'Walmart','opt228'=>'OKX','opt229'=>'GGPokerUK',
        'opt230'=>'OkCupid','opt231'=>'Whatnot','opt232'=>'DistroKid',
        'opt233'=>'TrueCaller','opt234'=>'Solitaire','opt235'=>'TLScontact',
        'opt236'=>'Year13','opt237'=>'Bitpanda','opt238'=>'hepsiburada',
        'opt239'=>'Taptap','opt240'=>'goldbet.it','opt241'=>'onet.pl (Onet Konto)',
        'opt242'=>'Abbott','opt243'=>'IQOS','opt244'=>'Truth Social',
        'opt245'=>'Linode','opt246'=>'Outlier','opt247'=>'RadQuest',
        'opt248'=>'ESX (abonamentesali.ro)','opt249'=>'Superbet','opt250'=>'Match',
        'opt251'=>'1cupis.ru','opt252'=>'Best Buy','opt253'=>'Zoosk',
        'opt254'=>'Monster Energy','opt255'=>'Casa Pariurilor','opt256'=>'Astropay',
        'opt257'=>'Remitly','opt258'=>'SumUp','opt259'=>'Google Messenger',
        'opt260'=>'Parions Sport','opt261'=>'Welo Data','opt262'=>'BC GAME',
        'opt263'=>'Profee.com','opt264'=>'Toloka.ai','opt265'=>'Reddit',
        'opt266'=>'Capital One Shopping','opt267'=>'Caliente',
        'opt268'=>'Pokemon Center Online','opt269'=>'Neosurf',
        'opt270'=>'Skelbiu','opt271'=>'Google Chat','opt420'=>'Grailed',
    ];
}