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

$countrySeeds = [
    ['India', 'IN', 'IND'],
    ['United Arab Emirates', 'AE', 'ARE'],
    ['United States', 'US', 'USA'],
    ['Saudi Arabia', 'SA', 'SAU'],
    ['Bahrain', 'BH', 'BHR'],
    ['Qatar', 'QA', 'QAT'],
    ['Oman', 'OM', 'OMN'],
    ['Kuwait', 'KW', 'KWT'],
    ['Canada', 'CA', 'CAN'],
];

$stateSeeds = [
    'India' => [
        ['Andaman and Nicobar Islands', 'AN'],
        ['Andhra Pradesh', 'AP'],
        ['Arunachal Pradesh', 'AR'],
        ['Assam', 'AS'],
        ['Bihar', 'BR'],
        ['Chandigarh', 'CH'],
        ['Chhattisgarh', 'CG'],
        ['Dadra and Nagar Haveli', 'DN'],
        ['Daman and Diu', 'DD'],
        ['Delhi', 'DL'],
        ['Goa', 'GA'],
        ['Gujarat', 'GJ'],
        ['Haryana', 'HR'],
        ['Himachal Pradesh', 'HP'],
        ['Jammu and Kashmir', 'JK'],
        ['Jharkhand', 'JH'],
        ['Karnataka', 'KA'],
        ['Kerala', 'KL'],
        ['Ladakh', 'LA'],
        ['Lakshadweep', 'LD'],
        ['Madhya Pradesh', 'MP'],
        ['Maharashtra', 'MH'],
        ['Manipur', 'MN'],
        ['Meghalaya', 'ML'],
        ['Mizoram', 'MZ'],
        ['Nagaland', 'NL'],
        ['Odisha', 'OD'],
        ['Puducherry', 'PY'],
        ['Punjab', 'PB'],
        ['Rajasthan', 'RJ'],
        ['Sikkim', 'SK'],
        ['Tamil Nadu', 'TN'],
        ['Telangana', 'TS'],
        ['Tripura', 'TR'],
        ['Uttar Pradesh', 'UP'],
        ['Uttarakhand', 'UK'],
        ['West Bengal', 'WB'],
    ],
    'United Arab Emirates' => [
        ['Abu Dhabi', 'AZ'],
        ['Ajman', 'AJ'],
        ['Dubai', 'DU'],
        ['Fujairah', 'FU'],
        ['Ras Al Khaimah', 'RK'],
        ['Sharjah', 'SH'],
        ['Umm Al Quwain', 'UQ'],
    ],
    'United States' => [
        ['California', 'CA'],
        ['Florida', 'FL'],
        ['Illinois', 'IL'],
        ['New York', 'NY'],
        ['Texas', 'TX'],
        ['Washington', 'WA'],
    ],
    'Saudi Arabia' => [
        ['Eastern Province', 'EP'],
        ['Madinah', 'MD'],
        ['Makkah', 'MK'],
        ['Riyadh', 'RD'],
    ],
    'Bahrain' => [
        ['Capital Governorate', 'CG'],
        ['Muharraq Governorate', 'MG'],
        ['Northern Governorate', 'NG'],
        ['Southern Governorate', 'SG'],
    ],
    'Qatar' => [
        ['Al Rayyan', 'RA'],
        ['Al Wakrah', 'WA'],
        ['Doha', 'DA'],
        ['Umm Salal', 'US'],
    ],
    'Oman' => [
        ['Ad Dakhiliyah', 'DA'],
        ['Al Batinah North', 'BN'],
        ['Dhofar', 'ZU'],
        ['Muscat', 'MA'],
    ],
    'Kuwait' => [
        ['Al Ahmadi', 'AH'],
        ['Al Asimah', 'KU'],
        ['Al Farwaniyah', 'FA'],
        ['Hawalli', 'HA'],
    ],
    'Canada' => [
        ['Alberta', 'AB'],
        ['British Columbia', 'BC'],
        ['Ontario', 'ON'],
        ['Quebec', 'QC'],
    ],
];

$conn->begin_transaction();

try {
    $countryIdMap = [];

    $findCountry = $conn->prepare('SELECT id FROM countries WHERE name = ? LIMIT 1');
    $insertCountry = $conn->prepare('INSERT INTO countries (name, iso2, iso3) VALUES (?, ?, ?)');

    foreach ($countrySeeds as [$name, $iso2, $iso3]) {
        $findCountry->bind_param('s', $name);
        $findCountry->execute();
        $result = $findCountry->get_result();
        $row = $result ? $result->fetch_assoc() : null;

        if ($row) {
            $countryIdMap[$name] = (int) $row['id'];
            continue;
        }

        $insertCountry->bind_param('sss', $name, $iso2, $iso3);
        $insertCountry->execute();
        $countryIdMap[$name] = (int) $conn->insert_id;
    }

    $findState = $conn->prepare('SELECT id FROM states WHERE country_id = ? AND name = ? LIMIT 1');
    $insertState = $conn->prepare('INSERT INTO states (country_id, name, state_code) VALUES (?, ?, ?)');

    foreach ($stateSeeds as $countryName => $states) {
        if (!isset($countryIdMap[$countryName])) {
            continue;
        }

        $countryId = $countryIdMap[$countryName];
        foreach ($states as [$stateName, $stateCode]) {
            $findState->bind_param('is', $countryId, $stateName);
            $findState->execute();
            $result = $findState->get_result();
            $row = $result ? $result->fetch_assoc() : null;

            if ($row) {
                continue;
            }

            $insertState->bind_param('iss', $countryId, $stateName, $stateCode);
            $insertState->execute();
        }
    }

    $conn->commit();
    echo "Signup location seed completed successfully.\n";
} catch (Throwable $e) {
    $conn->rollback();
    fwrite(STDERR, "Seed failed: " . $e->getMessage() . "\n");
    exit(1);
}
