<?php
// =========================================================
// backend/bot/fill_durations.php
// One-off script to fill missing duration_min values
// in the scraped_competitor_prices table using Intaleq Maps.
// =========================================================

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

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

echo "Fetching rows where duration_min IS NULL...\n";

// Get rows needing update
$stmt = $con->prepare("
    SELECT id, start_lat, start_lng, end_lat, end_lng, country_code 
    FROM scraped_competitor_prices 
    WHERE duration_min IS NULL 
    AND start_lat IS NOT NULL 
    AND start_lng IS NOT NULL
");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!$rows || count($rows) === 0) {
    echo "No rows found needing duration updates.\n";
    exit(0);
}

echo "Found " . count($rows) . " rows to update. Processing...\n";

$updateStmt = $con->prepare("
    UPDATE scraped_competitor_prices 
    SET duration_min = :duration_min, distance_km = :distance_km
    WHERE id = :id
");

$successCount = 0;
$failCount = 0;

foreach ($rows as $index => $row) {
    $id = $row['id'];
    $countryCode = $row['country_code'];
    
    // Call GraphHopper / Intaleq Maps
    $routeInfo = getOsrmRouteDetails($row['start_lat'], $row['start_lng'], $row['end_lat'], $row['end_lng'], $countryCode);
    
    if ($routeInfo && isset($routeInfo['duration_min']) && isset($routeInfo['distance_km'])) {
        $updateStmt->execute([
            ':duration_min' => (int) round($routeInfo['duration_min']),
            ':distance_km'  => $routeInfo['distance_km'],
            ':id'           => $id
        ]);
        $successCount++;
        echo "Row $id: Distance {$routeInfo['distance_km']}km, Duration " . round($routeInfo['duration_min']) . "min [SUCCESS]\n";
    } else {
        $failCount++;
        echo "Row $id: Failed to fetch route details.\n";
    }
    
    // Small sleep to avoid hitting API rate limits too hard
    usleep(100000); // 100ms
}

echo "======================================\n";
echo "Completed processing " . count($rows) . " rows.\n";
echo "Success: $successCount\n";
echo "Failed:  $failCount\n";