<?php
// === LOCATIONS FROM DB ===
function signup_country_fallbacks() {
    return [
        'India' => [
            'Andaman and Nicobar Islands',
            'Andhra Pradesh',
            'Arunachal Pradesh',
            'Assam',
            'Bihar',
            'Chandigarh',
            'Chhattisgarh',
            'Dadra and Nagar Haveli',
            'Daman and Diu',
            'Delhi',
            'Goa',
            'Gujarat',
            'Haryana',
            'Himachal Pradesh',
            'Jammu and Kashmir',
            'Jharkhand',
            'Karnataka',
            'Kerala',
            'Ladakh',
            'Lakshadweep',
            'Madhya Pradesh',
            'Maharashtra',
            'Manipur',
            'Meghalaya',
            'Mizoram',
            'Nagaland',
            'Odisha',
            'Puducherry',
            'Punjab',
            'Rajasthan',
            'Sikkim',
            'Tamil Nadu',
            'Telangana',
            'Tripura',
            'Uttar Pradesh',
            'Uttarakhand',
            'West Bengal',
        ],
        'United Arab Emirates' => ['Abu Dhabi', 'Ajman', 'Dubai', 'Fujairah', 'Ras Al Khaimah', 'Sharjah', 'Umm Al Quwain'],
        'United States' => ['California', 'Florida', 'Illinois', 'New York', 'Texas', 'Washington'],
        'Saudi Arabia' => ['Eastern Province', 'Makkah', 'Madinah', 'Riyadh'],
        'Bahrain' => ['Capital Governorate', 'Muharraq Governorate', 'Northern Governorate', 'Southern Governorate'],
        'Qatar' => ['Doha', 'Al Rayyan', 'Al Wakrah', 'Umm Salal'],
        'Oman' => ['Ad Dakhiliyah', 'Al Batinah North', 'Dhofar', 'Muscat'],
        'Kuwait' => ['Al Ahmadi', 'Al Asimah', 'Al Farwaniyah', 'Hawalli'],
        'Canada' => ['Alberta', 'British Columbia', 'Ontario', 'Quebec'],
    ];
}

function merge_unique_sorted($items) {
    $items = array_values(array_filter(array_map('trim', $items), static function ($item) {
        return $item !== '';
    }));
    $items = array_values(array_unique($items));
    natcasesort($items);
    return array_values($items);
}

function table_exists($conn, $table) {
    $stmt = $conn->prepare("SHOW TABLES LIKE ?");
    $stmt->bind_param("s", $table);
    $stmt->execute();
    $result = $stmt->get_result();
    $exists = $result && $result->num_rows > 0;
    $stmt->close();
    return $exists;
}

function pick_column($columns, $candidates) {
    foreach ($candidates as $candidate) {
        if (in_array($candidate, $columns, true)) {
            return $candidate;
        }
    }
    return null;
}

function pick_any_column($columns, $candidates) {
    foreach ($candidates as $candidate) {
        if (in_array($candidate, $columns, true)) {
            return $candidate;
        }
    }
    return null;
}

function describe_columns($conn, $table) {
    $cols = [];
    $result = $conn->query("DESCRIBE {$table}");
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $cols[] = $row['Field'];
        }
    }
    return $cols;
}

function get_locations($conn, $country = 'India') {
    $fallbacks = signup_country_fallbacks();
    $fallbackStates = $fallbacks[$country] ?? [];

    try {
        if (table_exists($conn, 'india_locations')) {
            $stmt = $conn->prepare("SELECT DISTINCT state FROM india_locations WHERE country = ? ORDER BY state");
            $stmt->bind_param("s", $country);
            $stmt->execute();
            $result = $stmt->get_result();
            $states = [];
            while ($row = $result->fetch_assoc()) {
                $states[] = $row['state'];
            }
            $stmt->close();
            return merge_unique_sorted(array_merge($states, $fallbackStates));
        }

        if (table_exists($conn, 'states')) {
            $stateCols = describe_columns($conn, 'states');
            $stateNameCol = pick_column($stateCols, ['name', 'state_name', 'state']);
            $stateCountryCol = pick_any_column($stateCols, ['country', 'country_name', 'country_id', 'country_code', 'country_iso', 'country_iso2', 'country_iso3', 'iso2', 'iso3']);

            if ($stateNameCol && $stateCountryCol) {
                if (in_array($stateCountryCol, ['country_id'], true) && table_exists($conn, 'countries')) {
                    $countryCols = describe_columns($conn, 'countries');
                    $countryIdCol = pick_column($countryCols, ['id', 'country_id']);
                    $countryNameCol = pick_column($countryCols, ['name', 'country_name', 'country']);
                    if ($countryIdCol && $countryNameCol) {
                        $stmt = $conn->prepare("SELECT DISTINCT s.`{$stateNameCol}` AS state
                            FROM states s JOIN countries c ON s.`{$stateCountryCol}` = c.`{$countryIdCol}`
                            WHERE c.`{$countryNameCol}` = ? ORDER BY s.`{$stateNameCol}`");
                        $stmt->bind_param("s", $country);
                        $stmt->execute();
                        $result = $stmt->get_result();
                        $states = [];
                        while ($row = $result->fetch_assoc()) {
                            $states[] = $row['state'];
                        }
                        $stmt->close();
                        return merge_unique_sorted(array_merge($states, $fallbackStates));
                    }
                } elseif (in_array($stateCountryCol, ['country_code', 'country_iso', 'country_iso2', 'country_iso3', 'iso2', 'iso3'], true) && table_exists($conn, 'countries')) {
                    $countryCols = describe_columns($conn, 'countries');
                    $countryNameCol = pick_column($countryCols, ['name', 'country_name', 'country']);
                    $countryCodeCol = pick_any_column($countryCols, ['code', 'country_code', 'iso2', 'iso3', 'country_iso', 'country_iso2', 'country_iso3']);
                    if ($countryNameCol && $countryCodeCol) {
                        $stmt = $conn->prepare("SELECT DISTINCT s.`{$stateNameCol}` AS state
                            FROM states s JOIN countries c ON s.`{$stateCountryCol}` = c.`{$countryCodeCol}`
                            WHERE c.`{$countryNameCol}` = ? ORDER BY s.`{$stateNameCol}`");
                        $stmt->bind_param("s", $country);
                        $stmt->execute();
                        $result = $stmt->get_result();
                        $states = [];
                        while ($row = $result->fetch_assoc()) {
                            $states[] = $row['state'];
                        }
                        $stmt->close();
                        return merge_unique_sorted(array_merge($states, $fallbackStates));
                    }
                } else {
                    $stmt = $conn->prepare("SELECT DISTINCT `{$stateNameCol}` AS state FROM states WHERE `{$stateCountryCol}` = ? ORDER BY `{$stateNameCol}`");
                    $stmt->bind_param("s", $country);
                    $stmt->execute();
                    $result = $stmt->get_result();
                    $states = [];
                    while ($row = $result->fetch_assoc()) {
                        $states[] = $row['state'];
                    }
                    $stmt->close();
                    return merge_unique_sorted(array_merge($states, $fallbackStates));
                }
            }
        }
    } catch (Throwable $e) {
        return merge_unique_sorted($fallbackStates);
    }

    return merge_unique_sorted($fallbackStates);
}

function get_countries($conn) {
    $fallbacks = signup_country_fallbacks();

    try {
        if (table_exists($conn, 'india_locations')) {
            $result = $conn->query("SELECT DISTINCT country FROM india_locations ORDER BY country");
            $countries = [];
            while ($row = $result->fetch_assoc()) {
                $name = $row['country'];
                $countries[$name] = get_locations($conn, $name);
            }
            foreach ($fallbacks as $country => $states) {
                $countries[$country] = merge_unique_sorted(array_merge($countries[$country] ?? [], $states));
            }
            return $countries;
        }

        if (table_exists($conn, 'countries')) {
            $countryCols = describe_columns($conn, 'countries');
            $countryNameCol = pick_column($countryCols, ['name', 'country_name', 'country']);
            if ($countryNameCol) {
                $result = $conn->query("SELECT DISTINCT `{$countryNameCol}` AS country FROM countries ORDER BY `{$countryNameCol}`");
                $countries = [];
                while ($row = $result->fetch_assoc()) {
                    $name = $row['country'];
                    $countries[$name] = get_locations($conn, $name);
                }
                foreach ($fallbacks as $country => $states) {
                    $countries[$country] = merge_unique_sorted(array_merge($countries[$country] ?? [], $states));
                }
                return $countries;
            }
        }
    } catch (Throwable $e) {
        return $fallbacks;
    }

    return $fallbacks;
}

function get_country_names($conn) {
    $countries = array_keys(signup_country_fallbacks());

    try {
        if (table_exists($conn, 'india_locations')) {
            $result = $conn->query("SELECT DISTINCT country FROM india_locations ORDER BY country");
            while ($row = $result->fetch_assoc()) {
                $countries[] = $row['country'];
            }
            return merge_unique_sorted($countries);
        }

        if (table_exists($conn, 'countries')) {
            $countryCols = describe_columns($conn, 'countries');
            $countryNameCol = pick_column($countryCols, ['name', 'country_name', 'country']);
            if ($countryNameCol) {
                $result = $conn->query("SELECT DISTINCT `{$countryNameCol}` AS country FROM countries ORDER BY `{$countryNameCol}`");
                while ($row = $result->fetch_assoc()) {
                    $countries[] = $row['country'];
                }
                return merge_unique_sorted($countries);
            }
        }
    } catch (Throwable $e) {
        return merge_unique_sorted($countries);
    }

    return merge_unique_sorted($countries);
}
?>

