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

error_reporting(E_ALL);
ini_set('display_errors', 1);

// Function to create URL-friendly slug from city name
function createSlug($text) {
    // Replace non-letter or non-digit characters with -
    $text = preg_replace('~[^\pL\d]+~u', '-', $text);
    // Transliterate to ASCII
    $text = iconv('utf-8', 'us-ascii//TRANSLIT', $text);
    // Remove unwanted characters
    $text = preg_replace('~[^-\w]+~', '', $text);
    // Trim and convert to lowercase
    $text = trim($text, '-');
    $text = strtolower($text);
    // Remove duplicate dashes
    $text = preg_replace('~-+~', '-', $text);
    return $text;
}

// File path
$filePath = __DIR__ . '/../city.txt';

// Check if file exists
if (!file_exists($filePath)) {
    die("Error: File not found at $filePath");
}

// Read file into array
$cities = file($filePath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);

if (empty($cities)) {
    die("Error: No cities found in the file or file is empty");
}

// Prepare statements
$checkStmt = $conn->prepare("SELECT id FROM cities WHERE name = ? OR slug = ?");
$insertStmt = $conn->prepare("INSERT INTO cities (name, slug) VALUES (?, ?)");

if (!$checkStmt || !$insertStmt) {
    die("Error preparing statements: " . $conn->error);
}

$imported = 0;
$skipped = 0;
$errors = [];

// Start transaction
$conn->begin_transaction();

try {
    foreach ($cities as $city) {
        $city = trim($city);
        if (empty($city)) continue;
        
        $slug = createSlug($city);
        
        // Check if city already exists
        $checkStmt->bind_param("ss", $city, $slug);
        $checkStmt->execute();
        $result = $checkStmt->get_result();
        
        if ($result->num_rows > 0) {
            $skipped++;
            continue;
        }
        
        // Insert new city
        $insertStmt->bind_param("ss", $city, $slug);
        if ($insertStmt->execute()) {
            $imported++;
        } else {
            // If there's a duplicate key error (unique constraint on slug)
            if ($conn->errno == 1062) {
                $skipped++;
            } else {
                throw new Exception("Error inserting city '$city': " . $insertStmt->error);
            }
        }
    }
    
    // Commit the transaction
    $conn->commit();
    
    // Output results
    echo "<h2>Import Results</h2>";
    echo "<p>Total cities in file: " . count($cities) . "</p>";
    echo "<p>Successfully imported: $imported</p>";
    echo "<p>Skipped (already exists): $skipped</p>";
    
    if (!empty($errors)) {
        echo "<h3>Errors:</h3>";
        echo "<ul>";
        foreach ($errors as $error) {
            echo "<li>" . htmlspecialchars($error) . "</li>";
        }
        echo "</ul>";
    }
    
} catch (Exception $e) {
    // Rollback transaction on error
    $conn->rollback();
    echo "<div style='color:red;'>Error: " . htmlspecialchars($e->getMessage()) . "</div>";
    if (isset($conn->error)) {
        echo "<div style='color:red;'>MySQL Error: " . htmlspecialchars($conn->error) . "</div>";
    }
    echo "<p>No cities were imported due to an error.</p>";
}

// Close statements
$checkStmt->close();
$insertStmt->close();

// Close connection
$conn->close();

// Add a link back to admin
if (file_exists(__DIR__ . '/admin-dashboard.php')) {
    echo "<p><a href='admin-dashboard.php'>← Back to Admin Dashboard</a></p>";
}
?>

<style>
    body { font-family: Arial, sans-serif; line-height: 1.6; margin: 20px; }
    h2 { color: #2c3e50; }
    .success { color: #27ae60; font-weight: bold; }
</style>
