<?php
require_once 'auth.php';
require_once '../fingguwebsite/db.php';

echo "<h1>Sync City-Service Combinations</h1>";

try {
    // Find cities that don't have all service combinations
    $stmt = $pdo->query("
        SELECT c.id as city_id, c.name as city_name, s.id as service_id, s.name as service_name
        FROM cities c
        CROSS JOIN services s
        LEFT JOIN city_service cs ON c.id = cs.city_id AND s.id = cs.service_id
        WHERE cs.id IS NULL
        ORDER BY c.name, s.name
    ");
    
    $missing = $stmt->fetchAll();
    
    if (empty($missing)) {
        echo "<p>✅ All city-service combinations already exist!</p>";
    } else {
        echo "<p>Found " . count($missing) . " missing combinations:</p>";
        echo "<ul>";
        
        $insertStmt = $pdo->prepare("
            INSERT INTO city_service (city_id, service_id, seo_title, seo_description, content)
            VALUES (?, ?, ?, ?, ?)
        ");
        
        $created = 0;
        foreach ($missing as $combo) {
            $cityName = $combo['city_name'];
            $serviceName = $combo['service_name'];
            
            $seoTitle = "$serviceName in $cityName | Finggu Infotech";
            $seoDescription = "Professional $serviceName services in $cityName. Get expert digital solutions from Finggu Infotech. Contact us for a free consultation.";
            $content = "Looking for professional $serviceName services in $cityName? Finggu Infotech offers comprehensive digital solutions tailored to your business needs. Our expert team delivers high-quality results that drive growth and success.\n\nWhy choose us for $serviceName in $cityName?\n- Experienced professionals\n- Proven track record\n- Competitive pricing\n- 24/7 support\n- Custom solutions\n\nContact us today for a free consultation and see how we can help your business grow with our $serviceName services in $cityName.";
            
            if ($insertStmt->execute([$combo['city_id'], $combo['service_id'], $seoTitle, $seoDescription, $content])) {
                echo "<li>✅ Created: $cityName - $serviceName</li>";
                $created++;
            } else {
                echo "<li>❌ Failed: $cityName - $serviceName</li>";
            }
        }
        
        echo "</ul>";
        echo "<p><strong>Successfully created $created new combinations!</strong></p>";
    }
    
    // Show current totals
    $totalCities = $pdo->query("SELECT COUNT(*) as count FROM cities")->fetch()['count'];
    $totalServices = $pdo->query("SELECT COUNT(*) as count FROM services")->fetch()['count'];
    $totalCombinations = $pdo->query("SELECT COUNT(*) as count FROM city_service")->fetch()['count'];
    $expectedCombinations = $totalCities * $totalServices;
    
    echo "<h2>Summary:</h2>";
    echo "<ul>";
    echo "<li>Cities: $totalCities</li>";
    echo "<li>Services: $totalServices</li>";
    echo "<li>Current combinations: $totalCombinations</li>";
    echo "<li>Expected combinations: $expectedCombinations</li>";
    echo "</ul>";
    
    if ($totalCombinations == $expectedCombinations) {
        echo "<p>✅ All combinations are now in sync!</p>";
    } else {
        echo "<p>⚠️ There might still be missing combinations.</p>";
    }
    
    echo "<p><a href='city-service-admin.php'>← Back to Admin Panel</a></p>";
    
} catch (Exception $e) {
    echo "<p>❌ Error: " . $e->getMessage() . "</p>";
}
?>

<style>
body { font-family: Arial, sans-serif; margin: 20px; }
ul { margin: 10px 0; }
li { margin: 5px 0; }
a { color: #dc2626; text-decoration: none; }
a:hover { text-decoration: underline; }
</style>