intaleq/backend/bot/cron_generate_pricing_cache.php

<?php
/**
 * cron_generate_pricing_cache.php
 * يجمع أسعار المنافسين لكل مربع جغرافي (2.5km) ويحفظ النتيجة في Redis
 * يتم تشغيله كـ Cron Job (CLI) لتخفيف الضغط تماماً عن الاستعلام المباشر للركاب.
 */

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

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

echo "Starting Pricing Cache Generation (Redis)...\n";

try {
    // 1. جلب متوسط الأسعار من المنافسين في آخر ساعة للشبكة
    $sql = "SELECT country_code,
                   ROUND(latitude / 0.025) * 0.025 AS grid_lat,
                   ROUND(longitude / 0.025) * 0.025 AS grid_lng,
                   competitor_name,
                   AVG(price) as avg_price,
                   COUNT(*) as requests_count
            FROM competitor_prices
            WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
            GROUP BY country_code, grid_lat, grid_lng, competitor_name";

    $stmt = $con->query($sql);
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // بناء مصفوفة الشبكات
    $grids = [];
    foreach ($results as $row) {
        $cc = strtoupper($row['country_code']);
        $gLat = number_format((float)$row['grid_lat'], 3);
        $gLng = number_format((float)$row['grid_lng'], 3);
        
        $key = "{$cc}_{$gLat}_{$gLng}";
        
        if (!isset($grids[$key])) {
            $grids[$key] = [
                'competitors' => [],
                'total_price' => 0,
                'total_competitors' => 0
            ];
        }
        
        $grids[$key]['competitors'][$row['competitor_name']] = (float)$row['avg_price'];
        $grids[$key]['total_price'] += (float)$row['avg_price'];
        $grids[$key]['total_competitors']++;
    }

    $processedGrids = [];
    $fallbackData = []; // لمتوسط البلد بالكامل كبديل

    foreach ($grids as $key => $data) {
        if ($data['total_competitors'] == 0) continue;

        $overallAvg = $data['total_price'] / $data['total_competitors'];
        
        // إيجاد المنافس الأرخص في هذا المربع
        $cheapestComp = '';
        $cheapestPrice = 999999;
        foreach ($data['competitors'] as $name => $price) {
            if ($price < $cheapestPrice) {
                $cheapestPrice = $price;
                $cheapestComp = $name;
            }
        }

        $gridInfo = [
            'avg_price' => round($overallAvg, 2),
            'top_competitor' => $cheapestComp,
            'cheapest_price' => round($cheapestPrice, 2)
        ];
        
        $processedGrids[$key] = $gridInfo;

        // حفظ المتوسط للـ Fallback
        $cc = explode('_', $key)[0];
        if (!isset($fallbackData[$cc])) {
            $fallbackData[$cc] = ['sum' => 0, 'count' => 0, 'cheapest_comp' => $cheapestComp];
        }
        $fallbackData[$cc]['sum'] += $overallAvg;
        $fallbackData[$cc]['count']++;
    }

    // إضافة Fallback لكل دولة (في حال الراكب كان في مربع فارغ)
    foreach ($fallbackData as $cc => $d) {
        if ($d['count'] > 0) {
            $processedGrids["{$cc}_FALLBACK"] = [
                'avg_price' => round($d['sum'] / $d['count'], 2),
                'top_competitor' => $d['cheapest_comp'],
                'cheapest_price' => round($d['sum'] / $d['count'], 2)
            ];
        }
    }

    // 2. الحفظ في Redis
    // نحفظ المصفوفة بالكامل كـ JSON String داخل مفتاح رئيسي واحد للسرعة العالية في القراءة 
    // مفتاح: siro:cache:pricing:grids
    $redisData = [
        'last_updated' => date('Y-m-d H:i:s'),
        'grids' => $processedGrids
    ];
    
    $redis->set('siro:cache:pricing:grids', json_encode($redisData, JSON_UNESCAPED_UNICODE));
    
    echo "Pricing Cache Generated Successfully in Redis. Grids: " . count($processedGrids) . "\n";

} catch (Exception $e) {
    error_log("Error generating pricing cache (Redis): " . $e->getMessage());
    echo "Error: " . $e->getMessage();
}
?>