tripz-llc/backend/bot/cron_surge_opportunity.php

<?php
/**
 * cron_surge_opportunity.php
 * مؤشر فرصة الذروة للعمل في الخلفية كـ Cron Job
 * 
 * المنطق:
 * 1. يحسب baseline و current لكل منافس في كل grid.
 * 2. يحدد المناطق التي تشهد surge شامل من المنافسين.
 * 3. يحفظ المناطق والمضاعف المقترح (multiplier) في Redis.
 */

// Allow script to run indefinitely
set_time_limit(0);
ini_set('memory_limit', '256M');

// Mock request to satisfy connect.php dependencies if any
$_SERVER['REQUEST_METHOD'] = 'POST';

require_once __DIR__ . '/../core/bootstrap.php';
require_once __DIR__ . '/../functions.php';

try {
    $con = Database::get('main');
} catch (Exception $e) {
    die("Database connection failed: " . $e->getMessage() . "\n");
}

echo "[".date('Y-m-d H:i:s')."] Starting cron_surge_opportunity...\n";

try {
    // 1. حساب الـ baseline و current
    $sql = "SELECT 
                ROUND(cp.start_lat * 74, 0) / 74 AS lat_group,
                ROUND(cp.start_lng * 74, 0) / 74 AS lng_group,
                cp.competitor_name,
                cp.country_code,
                AVG(CASE WHEN cp.created_at < DATE_SUB(NOW(), INTERVAL 6 HOUR)
                         THEN cp.price_per_km END) AS baseline_avg,
                AVG(CASE WHEN cp.created_at >= DATE_SUB(NOW(), INTERVAL 2 HOUR)
                         THEN cp.price_per_km END) AS current_avg,
                COUNT(*) AS total_samples,
                SUM(CASE WHEN cp.created_at >= DATE_SUB(NOW(), INTERVAL 2 HOUR) THEN 1 ELSE 0 END) AS recent_samples
            FROM scraped_competitor_prices cp
            WHERE cp.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
              AND cp.price_per_km > 0
            GROUP BY lat_group, lng_group, cp.competitor_name, cp.country_code
            HAVING recent_samples >= 2
            ORDER BY lat_group, lng_group, cp.competitor_name";

    $stmt = $con->prepare($sql);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // 2. تجميع البيانات لكل zone
    $zones = [];
    foreach ($rows as $row) {
        $zoneKey = $row['lat_group'] . '_' . $row['lng_group'];
        
        $baseline = (float)$row['baseline_avg'];
        $current  = (float)$row['current_avg'];
        
        $surgeRatio = ($baseline > 0) ? round($current / $baseline, 2) : 1.0;
        $isSurging = $baseline > 0 && $surgeRatio >= 1.2;

        if (!isset($zones[$zoneKey])) {
            $zones[$zoneKey] = [
                'lat'            => (float)$row['lat_group'],
                'lng'            => (float)$row['lng_group'],
                'country_code'   => $row['country_code'],
                'competitors'    => [],
                'total_active'   => 0,
                'total_surging'  => 0,
            ];
        }

        $zones[$zoneKey]['competitors'][] = [
            'name'        => $row['competitor_name'],
            'baseline'    => round($baseline, 2),
            'current'     => round($current, 2),
            'surge_ratio' => $surgeRatio,
            'is_surging'  => $isSurging,
        ];
        $zones[$zoneKey]['total_active']++;
        if ($isSurging) {
            $zones[$zoneKey]['total_surging']++;
        }
    }

    // 3. تحديد فرص الذروة
    $opportunities = [];
    $gridSurgeZones = [];
    $gridSurgeZonesByCountry = [];

    foreach ($zones as $key => &$zone) {
        $zone['opportunity'] = (
            $zone['total_active'] >= 1 &&
            $zone['total_surging'] === $zone['total_active']
        );

        if ($zone['opportunity']) {
            $avgRatio = 0;
            foreach ($zone['competitors'] as $c) {
                $avgRatio += $c['surge_ratio'];
            }
            $avgRatio /= count($zone['competitors']);

            $suggestedMultiplier = round(1.0 + ($avgRatio - 1.0) * 0.6, 2);
            if ($suggestedMultiplier < 1.0) $suggestedMultiplier = 1.0;

            $zone['suggested_multiplier'] = $suggestedMultiplier;

            $opportunities[] = [
                'lat'                 => $zone['lat'],
                'lng'                 => $zone['lng'],
                'country_code'        => $zone['country_code'],
                'avg_competitor_surge_ratio' => round($avgRatio, 2),
                'suggested_siro_multiplier'  => $suggestedMultiplier,
            ];

            // حفظ المنطقة مع المضاعف المقترح في Redis (للقراءة من get.php بعدين)
            $gridSurgeZones[$key] = $suggestedMultiplier;

            // نفس البيانات مجمّعة حسب الدولة — يقرأها get_surge_heatmap.php،
            // winback_hotspot_targets.php، و cron_kazan_adjuster.php كخريطة
            // grid_id → multiplier لكل دولة على حدة
            $countryCode = $zone['country_code'];
            if (!isset($gridSurgeZonesByCountry[$countryCode])) {
                $gridSurgeZonesByCountry[$countryCode] = [];
            }
            $gridSurgeZonesByCountry[$countryCode][$key] = $suggestedMultiplier;
        }
    }
    unset($zone);

    // 4. تخزين فرص الذروة في Redis بصلاحية 10 دقائق (600 ثانية)
    if (!empty($gridSurgeZones) && isset($redis) && $redis !== null) {
        $redisKey = 'surge:opportunities';
        $redis->setex($redisKey, 600, json_encode($gridSurgeZones));
        echo "[".date('Y-m-d H:i:s')."] Successfully stored ".count($gridSurgeZones)." surge zones in Redis.\n";
    } else {
        if (!isset($redis) || $redis === null) {
            echo "[".date('Y-m-d H:i:s')."] Error: Redis connection not available.\n";
        } else {
            // مسح الكي في حال لم يعد هناك أي ذروة
            $redis->del('surge:opportunities');
            echo "[".date('Y-m-d H:i:s')."] No active surge opportunities found. Cleared Redis key.\n";
        }
    }

    // 5. تخزين/مسح النسخة المقسّمة حسب الدولة — نفس مفتاح surge:opportunities:{CC}
    // اللي يقرأه get_surge_heatmap.php و winback_hotspot_targets.php و cron_kazan_adjuster.php
    if (isset($redis) && $redis !== null) {
        $knownCountries = ['SY', 'JO', 'EG', 'IQ'];
        foreach ($knownCountries as $cc) {
            $countryKey = "surge:opportunities:{$cc}";
            if (!empty($gridSurgeZonesByCountry[$cc])) {
                $redis->setex($countryKey, 600, json_encode($gridSurgeZonesByCountry[$cc]));
                echo "[".date('Y-m-d H:i:s')."] [$cc] Stored ".count($gridSurgeZonesByCountry[$cc])." surge zones.\n";
            } else {
                $redis->del($countryKey);
            }
        }
    }

    echo "[".date('Y-m-d H:i:s')."] cron_surge_opportunity completed successfully.\n";

} catch (Exception $e) {
    echo "[".date('Y-m-d H:i:s')."] Error: " . $e->getMessage() . "\n";
}