<?php
require_once 'auth.php';
require_once __DIR__ . '/../includes/db.php';

// Set error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Function to fetch cities from the API with multiple fallback options
function fetchMaharashtraCities() {
    // Try multiple endpoints in case one fails
    $endpoints = [
        'https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/countries+states+cities.json',
        'https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/countries.json',
        'https://raw.githubusercontent.com/dr5hn/countries-states-cities-database/master/countries_states_cities_database.json'
    ];
    
    $lastError = null;
    
    foreach ($endpoints as $apiUrl) {
        try {
            // Try file_get_contents first (simpler, might work with allow_url_fopen)
            if (ini_get('allow_url_fopen')) {
                $context = stream_context_create([
                    'http' => ['ignore_errors' => true],
                    'ssl' => ['verify_peer' => false, 'verify_peer_name' => false]
                ]);
                
                $response = @file_get_contents($apiUrl, false, $context);
                
                if ($response !== false) {
                    // Verify if we got valid JSON
                    $data = json_decode($response, true);
                    if (json_last_error() === JSON_ERROR_NONE) {
                        return $response;
                    }
                }
            }
            
            // Fall back to cURL if file_get_contents fails or is disabled
            $ch = curl_init();
            curl_setopt_array($ch, [
                CURLOPT_URL => $apiUrl,
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_SSL_VERIFYPEER => false, // For testing only, use proper SSL in production
                CURLOPT_FOLLOWLOCATION => true,
                CURLOPT_MAXREDIRS => 5,
                CURLOPT_TIMEOUT => 30,
                CURLOPT_HTTPHEADER => [
                    'User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
                ]
            ]);
            
            $response = curl_exec($ch);
            $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
            
            if (curl_errno($ch)) {
                $lastError = 'Curl error: ' . curl_error($ch);
                curl_close($ch);
                continue; // Try next endpoint
            }
            
            curl_close($ch);
            
            if ($httpCode === 200) {
                // Verify if we got valid JSON
                $data = json_decode($response, true);
                if (json_last_error() === JSON_ERROR_NONE) {
                    return $response;
                } else {
                    $lastError = 'Invalid JSON response: ' . json_last_error_msg();
                }
            } else {
                $lastError = "HTTP Code: $httpCode for URL: $apiUrl";
            }
            
        } catch (Exception $e) {
            $lastError = $e->getMessage();
            continue; // Try next endpoint
        }
    }
    
    // If we get here, all endpoints failed, try local fallback
    $localFile = __DIR__ . '/data/maharashtra_cities.json';
    if (file_exists($localFile)) {
        $response = file_get_contents($localFile);
        if ($response !== false) {
            // Verify if we got valid JSON
            $data = json_decode($response, true);
            if (json_last_error() === JSON_ERROR_NONE) {
                return json_encode($data); // Return as string to match the expected format
            }
        }
    }
    
    throw new Exception("All endpoints failed and local fallback not available. Last error: $lastError");
}

// Function to create cities table if not exists
function createCitiesTable($pdo) {
    $sql = "CREATE TABLE IF NOT EXISTS maharashtra_cities (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        state_code VARCHAR(10) NOT NULL,
        state_name VARCHAR(100) NOT NULL,
        country_code VARCHAR(10) NOT NULL,
        country_name VARCHAR(100) NOT NULL,
        latitude DECIMAL(10, 8),
        longitude DECIMAL(11, 8),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY unique_city (name, state_code, country_code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $pdo->exec($sql);
}

// Function to insert cities into the database
function insertCities($pdo, $cities) {
    $sql = "INSERT IGNORE INTO maharashtra_cities 
            (name, state_code, state_name, country_code, country_name, latitude, longitude)
            VALUES (:name, :state_code, :state_name, :country_code, :country_name, :latitude, :longitude)";
    
    $stmt = $pdo->prepare($sql);
    $inserted = 0;
    
    foreach ($cities as $city) {
        try {
            $stmt->execute([
                ':name' => $city['name'],
                ':state_code' => $city['state_code'],
                ':state_name' => $city['state_name'],
                ':country_code' => $city['country_code'],
                ':country_name' => $city['country_name'],
                ':latitude' => $city['latitude'] ?? null,
                ':longitude' => $city['longitude'] ?? null
            ]);
            $inserted++;
        } catch (PDOException $e) {
            // Log error but continue with next city
            error_log("Error inserting city {$city['name']}: " . $e->getMessage());
        }
    }
    
    return $inserted;
}

try {
    echo "<h2>Importing Maharashtra Cities</h2>";
    
    // Create table if not exists
    createCitiesTable($pdo);
    echo "<p>✓ Table checked/created successfully</p>";
    
    // Fetch data from API
    echo "<p>Fetching data from API...</p>";
    $data = fetchMaharashtraCities();
    $data = json_decode($data, true);
    
    if (json_last_error() !== JSON_ERROR_NONE) {
        throw new Exception('Invalid JSON response: ' . json_last_error_msg());
    }
    
    // Check if we have the direct cities array (local fallback format)
    if (isset($data[0]['state_name']) && $data[0]['state_name'] === 'Maharashtra') {
        // This is our local fallback format
        $cities = array_map(function($city) {
            return [
                'name' => $city['name'],
                'state_code' => 'MH',
                'state_name' => 'Maharashtra',
                'country_code' => 'IN',
                'country_name' => 'India',
                'latitude' => $city['latitude'] ?? null,
                'longitude' => $city['longitude'] ?? null
            ];
        }, $data);
    } else {
        // Original format from the API
        // Find India in the data
        $india = null;
        foreach ($data as $country) {
            if (strtolower($country['name']) === 'india') {
                $india = $country;
                break;
            }
        }
        
        if (!$india) {
            throw new Exception('India not found in the data');
        }
        
        // Find Maharashtra state
        $maharashtra = null;
        foreach ($india['states'] as $state) {
            if (strtolower($state['name']) === 'maharashtra') {
                $maharashtra = $state;
                break;
            }
        }
        
        if (!$maharashtra) {
            throw new Exception('Maharashtra not found in the data');
        }
        
        // Prepare cities data from API
        $cities = [];
        if (!empty($maharashtra['cities'])) {
            foreach ($maharashtra['cities'] as $city) {
                $cities[] = [
                    'name' => $city['name'],
                    'state_code' => $maharashtra['state_code'],
                    'state_name' => $maharashtra['name'],
                    'country_code' => $india['iso2'],
                    'country_name' => $india['name'],
                    'latitude' => $city['latitude'] ?? null,
                    'longitude' => $city['longitude'] ?? null
                ];
            }
        }
    }
    
    if (empty($cities)) {
        throw new Exception('No cities found for Maharashtra');
    }
    
    echo "<p>Found " . count($cities) . " cities in Maharashtra</p>";
    
    // Insert cities into database
    $inserted = insertCities($pdo, $cities);
    
    echo "<p>✓ Successfully inserted/updated $inserted cities in the database</p>";
    
    // Show sample of inserted data
    echo "<h3>Sample of inserted cities:</h3>";
    echo "<ul>";
    $sampleCities = array_slice($cities, 0, 10);
    foreach ($sampleCities as $city) {
        echo "<li>" . htmlspecialchars($city['name']) . "</li>";
    }
    echo "</ul>";
    
    if (count($cities) > 10) {
        echo "<p>... and " . (count($cities) - 10) . " more cities</p>";
    }
    
} catch (Exception $e) {
    echo "<div style='color: red; padding: 10px; border: 1px solid #f5c6cb; background-color: #f8d7da; margin: 10px 0;'>";
    echo "<strong>Error:</strong> " . htmlspecialchars($e->getMessage());
    echo "</div>";
    
    // Log the full error for debugging
    error_log($e->getMessage());
    if (isset($e->getTraceAsString)) {
        error_log($e->getTraceAsString());
    }
}

// Add a link back to admin
echo "<p><a href='admin-dashboard.php'>&larr; Back to Dashboard</a></p>";
?>

<style>
    body {
        font-family: Arial, sans-serif;
        line-height: 1.6;
        margin: 20px;
        color: #333;
    }
    h2, h3 {
        color: #2c3e50;
    }
    p {
        margin: 10px 0;
    }
    a {
        color: #3498db;
        text-decoration: none;
    }
    a:hover {
        text-decoration: underline;
    }
    ul {
        list-style-type: none;
        padding: 0;
    }
    li {
        padding: 5px 0;
        border-bottom: 1px solid #eee;
    }
</style>
