<?php
/**
 * Geographic data repository — single source of truth backed by a custom DB table.
 *
 * Replaces the legacy static-file + JSON-option approach with a single
 * `{prefix}scpwoo_geodata` table managed via dbDelta().
 *
 * When the migration has not yet run, every read method transparently
 * delegates to SCPWOO_Geo_Helper so the plugin keeps working.
 *
 * @package AfriRegions
 */

defined( 'ABSPATH' ) or die( 'Forbidden' );

class SCPWOO_Geo_Repository {

    /** @var string DB version written to options after successful migration */
    const DB_VERSION = '2.0.0';

    const CACHE_KEY_STATES        = 'scpwoo_geo_states';
    const CACHE_KEY_PLACES        = 'scpwoo_geo_places';
    const CACHE_KEY_MERGED_PLACES = 'scpwoo_geo_mplaces';
    const CACHE_KEY_HOODS         = 'scpwoo_geo_hoods';
    const CACHE_KEY_MERGED_HOODS  = 'scpwoo_geo_mhoods';
    const CACHE_TTL               = 3600;

    /** @var string|null Cached table name */
    private static $table_name = null;

    /** @var array|null Per-request cache: states by country */
    private static $states_cache = null;

    /** @var array|null Per-request cache: default places by country */
    private static $places_cache = null;

    /** @var array|null Per-request cache: merged places by country */
    private static $merged_places_cache = null;

    /** @var array|null Per-request cache: default neighborhoods by country */
    private static $neighborhoods_cache = null;

    /** @var array|null Per-request cache: merged neighborhoods by country */
    private static $merged_neighborhoods_cache = null;

    // =========================================================================
    // Table management
    // =========================================================================

    /**
     * Get the fully-qualified table name.
     *
     * @return string
     */
    public static function get_table_name(): string {
        if ( self::$table_name === null ) {
            global $wpdb;
            self::$table_name = $wpdb->prefix . 'scpwoo_geodata';
        }
        return self::$table_name;
    }

    /**
     * Create (or update) the geodata table using dbDelta().
     */
    public static function create_table(): void {
        global $wpdb;

        $table          = self::get_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$table} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            country_code CHAR(2) NOT NULL,
            region_code VARCHAR(10) NOT NULL DEFAULT '',
            type ENUM('region','city','neighborhood') NOT NULL,
            name VARCHAR(200) NOT NULL,
            parent_name VARCHAR(200) NOT NULL DEFAULT '',
            source ENUM('default','custom') NOT NULL DEFAULT 'default',
            is_deleted TINYINT(1) NOT NULL DEFAULT 0,
            PRIMARY KEY  (id),
            UNIQUE KEY idx_unique_entry (country_code, region_code, type, name, parent_name),
            KEY idx_regions (country_code, type),
            KEY idx_cities (country_code, region_code, type, is_deleted),
            KEY idx_neighborhoods (country_code, parent_name, type, is_deleted),
            KEY idx_search (name)
        ) {$charset_collate};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );
    }

    /**
     * Whether the DB migration still needs to run.
     *
     * @return bool
     */
    public static function needs_migration(): bool {
        return get_option( 'scpwoo_db_version', '' ) !== self::DB_VERSION;
    }

    /**
     * Run the full migration: create table, seed from files, import JSON customizations.
     *
     * A transient lock prevents concurrent executions (e.g. two simultaneous page loads).
     */
    public static function run_migration(): void {
        if ( get_transient( 'scpwoo_migration_lock' ) ) {
            return;
        }
        set_transient( 'scpwoo_migration_lock', 1, 300 );

        try {
            self::create_table();
            self::seed_from_files();
            self::migrate_json_customizations();
            update_option( 'scpwoo_db_version', self::DB_VERSION );
        } catch ( \Exception $e ) {
            if ( class_exists( 'SCPWOO_Audit' ) ) {
                SCPWOO_Audit::log( 'error', 'Migration failed: ' . $e->getMessage() );
            }
        } finally {
            delete_transient( 'scpwoo_migration_lock' );
        }
    }

    // =========================================================================
    // Seeding
    // =========================================================================

    /**
     * Load static data files the same way SCPWOO_States_Places does, but for ALL countries
     * (not filtered by WC allowed countries).
     *
     * @param string $directory  Subdirectory name: 'states', 'places', or 'neighborhoods'.
     * @param string $global_var Name of the global variable the files populate.
     * @return array
     */
    private static function load_data_files( string $directory, string $global_var ): array {
        global $$global_var;

        $backup      = isset( $$global_var ) ? $$global_var : null;
        $$global_var = array();

        $plugin_path = plugin_dir_path( SCPWOO_PLUGIN_FILE );
        $dir         = $plugin_path . $directory . '/';

        if ( is_dir( $dir ) ) {
            foreach ( glob( $dir . '*.php' ) as $file ) {
                $code = strtoupper( basename( $file, '.php' ) );
                if ( preg_match( '/^[A-Z]{2}$/', $code ) && ! isset( ${$global_var}[ $code ] ) ) {
                    include $file;
                }
            }
        }

        $result      = is_array( $$global_var ) ? $$global_var : array();
        $$global_var = $backup;

        return $result;
    }

    // =========================================================================
    // Legacy fallback (file-based, used when migration has not run)
    // =========================================================================

    /** @var array|null Cached legacy states. */
    private static $legacy_states = null;
    /** @var array|null Cached legacy places. */
    private static $legacy_places = null;
    /** @var array|null Cached legacy neighborhoods. */
    private static $legacy_neighborhoods = null;

    /**
     * Get states from static files (legacy fallback).
     * Used internally to avoid infinite loop with SCPWOO_Geo_Helper.
     */
    private static function legacy_get_states_by_country(): array {
        if ( self::$legacy_states !== null ) {
            return self::$legacy_states;
        }
        $raw = self::load_data_files( 'states', 'states' );
        self::$legacy_states = is_array( $raw ) ? $raw : array();
        return self::$legacy_states;
    }

    /**
     * Get merged places from static files + JSON customizations (legacy fallback).
     */
    private static function legacy_get_merged_places_by_country(): array {
        if ( self::$legacy_places !== null ) {
            return self::$legacy_places;
        }
        $places = self::load_data_files( 'places', 'places' );
        $custom = SCPWOO_Options::get_json_option( 'scpwoo_custom_places' );

        foreach ( $custom as $cc => $data ) {
            if ( ! empty( $data['added'] ) ) {
                foreach ( $data['added'] as $rc => $cities ) {
                    if ( ! isset( $places[ $cc ][ $rc ] ) ) {
                        $places[ $cc ][ $rc ] = array();
                    }
                    $places[ $cc ][ $rc ] = array_unique( array_merge( $places[ $cc ][ $rc ], $cities ) );
                    sort( $places[ $cc ][ $rc ] );
                }
            }
            if ( ! empty( $data['deleted'] ) ) {
                foreach ( $data['deleted'] as $rc => $cities ) {
                    if ( isset( $places[ $cc ][ $rc ] ) ) {
                        $places[ $cc ][ $rc ] = array_values( array_diff( $places[ $cc ][ $rc ], $cities ) );
                    }
                }
            }
        }

        self::$legacy_places = $places;
        return self::$legacy_places;
    }

    /**
     * Get merged neighborhoods from static files + JSON customizations (legacy fallback).
     */
    private static function legacy_get_merged_neighborhoods_by_country(): array {
        if ( self::$legacy_neighborhoods !== null ) {
            return self::$legacy_neighborhoods;
        }
        $neighborhoods = self::load_data_files( 'neighborhoods', 'neighborhoods' );
        $custom = SCPWOO_Options::get_json_option( 'scpwoo_custom_neighborhoods' );

        foreach ( $custom as $cc => $data ) {
            if ( ! empty( $data['added'] ) ) {
                foreach ( $data['added'] as $city => $hoods ) {
                    if ( ! isset( $neighborhoods[ $cc ][ $city ] ) ) {
                        $neighborhoods[ $cc ][ $city ] = array();
                    }
                    $neighborhoods[ $cc ][ $city ] = array_unique( array_merge( $neighborhoods[ $cc ][ $city ], $hoods ) );
                    sort( $neighborhoods[ $cc ][ $city ] );
                }
            }
            if ( ! empty( $data['deleted'] ) ) {
                foreach ( $data['deleted'] as $city => $hoods ) {
                    if ( isset( $neighborhoods[ $cc ][ $city ] ) ) {
                        $neighborhoods[ $cc ][ $city ] = array_values( array_diff( $neighborhoods[ $cc ][ $city ], $hoods ) );
                    }
                }
            }
        }

        self::$legacy_neighborhoods = $neighborhoods;
        return self::$legacy_neighborhoods;
    }

    /**
     * Get default places from static files only (no customizations).
     */
    private static function legacy_get_default_places_by_country(): array {
        return self::load_data_files( 'places', 'places' );
    }

    /**
     * Get default neighborhoods from static files only (no customizations).
     */
    private static function legacy_get_default_neighborhoods_by_country(): array {
        return self::load_data_files( 'neighborhoods', 'neighborhoods' );
    }

    /**
     * Get country codes from static files (legacy fallback).
     */
    private static function legacy_get_country_codes(): array {
        $plugin = class_exists( 'SCPWOO_States_Places' ) ? SCPWOO_States_Places::instance() : null;
        if ( $plugin ) {
            $supported = $plugin->get_supported_countries();
            if ( function_exists( 'WC' ) && WC()->countries ) {
                $wc_allowed  = WC()->countries->get_allowed_countries();
                $wc_shipping = WC()->countries->get_shipping_countries();
                $all_allowed = array_merge( $wc_allowed, $wc_shipping );
                $allowed_codes = array_map( 'strtoupper', array_keys( $all_allowed ) );
                $filtered = array_values( array_intersect( $supported, $allowed_codes ) );
                if ( ! empty( $filtered ) ) {
                    return $filtered;
                }
            }
            return $supported;
        }
        return array();
    }

    // =========================================================================
    // Seeding
    // =========================================================================

    /**
     * Seed the geodata table from the static PHP data files.
     *
     * Processes in batches of 500 rows using INSERT IGNORE so re-runs are safe.
     */
    private static function seed_from_files(): void {
        global $wpdb;

        $table = self::get_table_name();

        // ----- 1. Regions (states) -----
        $all_states = self::load_data_files( 'states', 'states' );

        foreach ( $all_states as $cc => $regions ) {
            if ( ! is_array( $regions ) ) {
                continue;
            }
            $rows = array();
            foreach ( $regions as $rc => $name ) {
                $rows[] = $wpdb->prepare(
                    '(%s, %s, %s, %s, %s, %s, 0)',
                    $cc, $rc, 'region', $name, '', 'default'
                );
                if ( count( $rows ) >= 500 ) {
                    self::bulk_insert_ignore( $table, $rows );
                    $rows = array();
                }
            }
            if ( ! empty( $rows ) ) {
                self::bulk_insert_ignore( $table, $rows );
            }
        }

        // ----- 2. Cities (places) -----
        $all_places = self::load_data_files( 'places', 'places' );

        foreach ( $all_places as $cc => $regions ) {
            if ( ! is_array( $regions ) ) {
                continue;
            }
            $rows = array();
            foreach ( $regions as $rc => $cities ) {
                if ( ! is_array( $cities ) ) {
                    continue;
                }
                foreach ( $cities as $city ) {
                    $rows[] = $wpdb->prepare(
                        '(%s, %s, %s, %s, %s, %s, 0)',
                        $cc, $rc, 'city', $city, '', 'default'
                    );
                    if ( count( $rows ) >= 500 ) {
                        self::bulk_insert_ignore( $table, $rows );
                        $rows = array();
                    }
                }
            }
            if ( ! empty( $rows ) ) {
                self::bulk_insert_ignore( $table, $rows );
            }
        }

        // ----- 3. Neighborhoods -----
        $all_neighborhoods = self::load_data_files( 'neighborhoods', 'neighborhoods' );

        // Build city -> region_code lookup from places data
        $city_to_region = array();
        foreach ( $all_places as $cc => $regions ) {
            if ( ! is_array( $regions ) ) {
                continue;
            }
            foreach ( $regions as $rc => $cities ) {
                if ( ! is_array( $cities ) ) {
                    continue;
                }
                foreach ( $cities as $city ) {
                    $city_to_region[ $cc . ':' . $city ] = $rc;
                }
            }
        }

        foreach ( $all_neighborhoods as $cc => $cities ) {
            if ( ! is_array( $cities ) ) {
                continue;
            }
            $rows = array();
            foreach ( $cities as $city => $hoods ) {
                if ( ! is_array( $hoods ) ) {
                    continue;
                }
                $rc = isset( $city_to_region[ $cc . ':' . $city ] ) ? $city_to_region[ $cc . ':' . $city ] : '';
                foreach ( $hoods as $hood ) {
                    $rows[] = $wpdb->prepare(
                        '(%s, %s, %s, %s, %s, %s, 0)',
                        $cc, $rc, 'neighborhood', $hood, $city, 'default'
                    );
                    if ( count( $rows ) >= 500 ) {
                        self::bulk_insert_ignore( $table, $rows );
                        $rows = array();
                    }
                }
            }
            if ( ! empty( $rows ) ) {
                self::bulk_insert_ignore( $table, $rows );
            }
        }
    }

    /**
     * Execute a batched INSERT IGNORE.
     *
     * @param string $table Table name.
     * @param array  $rows  Array of prepared value tuples.
     */
    private static function bulk_insert_ignore( string $table, array $rows ): void {
        global $wpdb;

        if ( empty( $rows ) ) {
            return;
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- values are individually prepared above
        $wpdb->query(
            "INSERT IGNORE INTO {$table} (country_code, region_code, type, name, parent_name, source, is_deleted) VALUES "
            . implode( ', ', $rows )
        );
    }

    /**
     * Migrate JSON-based admin customizations (added / deleted cities and neighborhoods)
     * from wp_options into the geodata table.
     */
    private static function migrate_json_customizations(): void {
        global $wpdb;

        $table = self::get_table_name();

        // --- Custom places ---
        $custom_places = self::read_json_option( SCPWOO_States_Places::OPTION_CUSTOM_PLACES );

        foreach ( $custom_places as $cc => $actions ) {
            if ( ! is_array( $actions ) ) {
                continue;
            }

            // Additions
            if ( ! empty( $actions['added'] ) && is_array( $actions['added'] ) ) {
                $rows = array();
                foreach ( $actions['added'] as $rc => $cities ) {
                    if ( ! is_array( $cities ) ) {
                        continue;
                    }
                    foreach ( $cities as $city ) {
                        $rows[] = $wpdb->prepare(
                            '(%s, %s, %s, %s, %s, %s, 0)',
                            $cc, $rc, 'city', $city, '', 'custom'
                        );
                        if ( count( $rows ) >= 500 ) {
                            self::bulk_insert_ignore( $table, $rows );
                            $rows = array();
                        }
                    }
                }
                if ( ! empty( $rows ) ) {
                    self::bulk_insert_ignore( $table, $rows );
                }
            }

            // Deletions — soft-delete matching default rows
            if ( ! empty( $actions['deleted'] ) && is_array( $actions['deleted'] ) ) {
                foreach ( $actions['deleted'] as $rc => $cities ) {
                    if ( ! is_array( $cities ) ) {
                        continue;
                    }
                    foreach ( $cities as $city ) {
                        $wpdb->query( $wpdb->prepare(
                            "UPDATE {$table} SET is_deleted = 1 WHERE country_code = %s AND region_code = %s AND type = 'city' AND name = %s AND source = 'default'",
                            $cc, $rc, $city
                        ) );
                    }
                }
            }
        }

        // --- Custom neighborhoods ---
        $custom_hoods = self::read_json_option( SCPWOO_States_Places::OPTION_CUSTOM_HOODS );

        // Build city -> region_code lookup from existing table data
        $city_region_map = array();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery
        $city_rows = $wpdb->get_results(
            "SELECT DISTINCT country_code, region_code, name FROM {$table} WHERE type = 'city'",
            ARRAY_A
        );
        if ( is_array( $city_rows ) ) {
            foreach ( $city_rows as $row ) {
                $city_region_map[ $row['country_code'] . ':' . $row['name'] ] = $row['region_code'];
            }
        }

        foreach ( $custom_hoods as $cc => $actions ) {
            if ( ! is_array( $actions ) ) {
                continue;
            }

            // Additions
            if ( ! empty( $actions['added'] ) && is_array( $actions['added'] ) ) {
                $rows = array();
                foreach ( $actions['added'] as $city => $hoods ) {
                    if ( ! is_array( $hoods ) ) {
                        continue;
                    }
                    $rc = isset( $city_region_map[ $cc . ':' . $city ] ) ? $city_region_map[ $cc . ':' . $city ] : '';
                    foreach ( $hoods as $hood ) {
                        $rows[] = $wpdb->prepare(
                            '(%s, %s, %s, %s, %s, %s, 0)',
                            $cc, $rc, 'neighborhood', $hood, $city, 'custom'
                        );
                        if ( count( $rows ) >= 500 ) {
                            self::bulk_insert_ignore( $table, $rows );
                            $rows = array();
                        }
                    }
                }
                if ( ! empty( $rows ) ) {
                    self::bulk_insert_ignore( $table, $rows );
                }
            }

            // Deletions
            if ( ! empty( $actions['deleted'] ) && is_array( $actions['deleted'] ) ) {
                foreach ( $actions['deleted'] as $city => $hoods ) {
                    if ( ! is_array( $hoods ) ) {
                        continue;
                    }
                    foreach ( $hoods as $hood ) {
                        $wpdb->query( $wpdb->prepare(
                            "UPDATE {$table} SET is_deleted = 1 WHERE country_code = %s AND type = 'neighborhood' AND name = %s AND parent_name = %s AND source = 'default'",
                            $cc, $hood, $city
                        ) );
                    }
                }
            }
        }
    }

    /**
     * Read a JSON option, using SCPWOO_Options if available, else get_option().
     *
     * @param string $key Option name.
     * @return array
     */
    private static function read_json_option( string $key ): array {
        if ( class_exists( 'SCPWOO_Options' ) ) {
            return SCPWOO_Options::get_json_option( $key, array() );
        }

        $raw = get_option( $key, array() );
        if ( is_array( $raw ) ) {
            return $raw;
        }
        if ( is_string( $raw ) ) {
            $decoded = json_decode( $raw, true );
            return is_array( $decoded ) ? $decoded : array();
        }
        return array();
    }

    // =========================================================================
    // Read methods
    // =========================================================================

    /**
     * Get regions (states) indexed by country code.
     *
     * @return array [ 'CM' => [ 'CE' => 'Centre', ... ], ... ]
     */
    public static function get_states_by_country(): array {
        if ( self::needs_migration() ) {
            return self::legacy_get_states_by_country();
        }

        if ( self::$states_cache !== null ) {
            return self::$states_cache;
        }

        $cached = get_transient( self::CACHE_KEY_STATES );
        if ( $cached !== false ) {
            self::$states_cache = $cached;
            return $cached;
        }

        global $wpdb;
        $table = self::get_table_name();

        $allowed = self::get_allowed_country_codes();

        if ( empty( $allowed ) ) {
            self::$states_cache = array();
            return self::$states_cache;
        }

        $placeholders = implode( ', ', array_fill( 0, count( $allowed ), '%s' ) );

        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT country_code, region_code, name FROM {$table} WHERE type = 'region' AND is_deleted = 0 AND country_code IN ({$placeholders}) ORDER BY country_code, name",
            ...$allowed
        ), ARRAY_A );

        $result = array();
        if ( is_array( $rows ) ) {
            foreach ( $rows as $row ) {
                $result[ $row['country_code'] ][ $row['region_code'] ] = $row['name'];
            }
        }

        set_transient( self::CACHE_KEY_STATES, $result, self::CACHE_TTL );
        self::$states_cache = $result;
        return $result;
    }

    /**
     * Get default places (cities) indexed by country code.
     *
     * @return array [ 'CM' => [ 'CE' => ['Yaoundé', ...], ... ], ... ]
     */
    public static function get_places_by_country(): array {
        if ( self::needs_migration() ) {
            return self::legacy_get_default_places_by_country();
        }

        if ( self::$places_cache !== null ) {
            return self::$places_cache;
        }

        $cached = get_transient( self::CACHE_KEY_PLACES );
        if ( $cached !== false ) {
            self::$places_cache = $cached;
            return $cached;
        }

        global $wpdb;
        $table = self::get_table_name();

        $allowed = self::get_allowed_country_codes();

        if ( empty( $allowed ) ) {
            self::$places_cache = array();
            return self::$places_cache;
        }

        $placeholders = implode( ', ', array_fill( 0, count( $allowed ), '%s' ) );

        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT country_code, region_code, name FROM {$table} WHERE type = 'city' AND is_deleted = 0 AND source = 'default' AND country_code IN ({$placeholders}) ORDER BY country_code, region_code, name",
            ...$allowed
        ), ARRAY_A );

        $result = array();
        if ( is_array( $rows ) ) {
            foreach ( $rows as $row ) {
                $result[ $row['country_code'] ][ $row['region_code'] ][] = $row['name'];
            }
        }

        set_transient( self::CACHE_KEY_PLACES, $result, self::CACHE_TTL );
        self::$places_cache = $result;
        return $result;
    }

    /**
     * Get all active places (default + custom, excluding soft-deleted) indexed by country code.
     *
     * Return format matches SCPWOO_Geo_Helper::get_merged_places_by_country() exactly:
     *   [ 'CM' => [ 'CE' => ['Yaoundé', ...], ... ], ... ]
     * Cities are sorted alphabetically within each region.
     *
     * @return array
     */
    public static function get_merged_places_by_country(): array {
        if ( self::needs_migration() ) {
            return self::legacy_get_merged_places_by_country();
        }

        if ( self::$merged_places_cache !== null ) {
            return self::$merged_places_cache;
        }

        $cached = get_transient( self::CACHE_KEY_MERGED_PLACES );
        if ( $cached !== false ) {
            self::$merged_places_cache = $cached;
            return $cached;
        }

        global $wpdb;
        $table = self::get_table_name();

        $allowed = self::get_allowed_country_codes();

        if ( empty( $allowed ) ) {
            self::$merged_places_cache = array();
            return self::$merged_places_cache;
        }

        $placeholders = implode( ', ', array_fill( 0, count( $allowed ), '%s' ) );

        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT country_code, region_code, name FROM {$table} WHERE type = 'city' AND is_deleted = 0 AND country_code IN ({$placeholders}) ORDER BY country_code, region_code, name",
            ...$allowed
        ), ARRAY_A );

        $result = array();
        if ( is_array( $rows ) ) {
            foreach ( $rows as $row ) {
                $result[ $row['country_code'] ][ $row['region_code'] ][] = $row['name'];
            }
        }

        // Ensure alphabetical sort per region (ORDER BY should handle it, but be explicit)
        foreach ( $result as $cc => $regions ) {
            foreach ( $regions as $rc => $cities ) {
                sort( $result[ $cc ][ $rc ] );
            }
        }

        set_transient( self::CACHE_KEY_MERGED_PLACES, $result, self::CACHE_TTL );
        self::$merged_places_cache = $result;
        return $result;
    }

    /**
     * Get default neighborhoods indexed by country code.
     *
     * @return array [ 'CM' => [ 'Yaoundé' => ['Bastos', ...], ... ], ... ]
     */
    public static function get_neighborhoods_by_country(): array {
        if ( self::needs_migration() ) {
            return self::legacy_get_default_neighborhoods_by_country();
        }

        if ( self::$neighborhoods_cache !== null ) {
            return self::$neighborhoods_cache;
        }

        $cached = get_transient( self::CACHE_KEY_HOODS );
        if ( $cached !== false ) {
            self::$neighborhoods_cache = $cached;
            return $cached;
        }

        global $wpdb;
        $table = self::get_table_name();

        $allowed = self::get_allowed_country_codes();

        if ( empty( $allowed ) ) {
            self::$neighborhoods_cache = array();
            return self::$neighborhoods_cache;
        }

        $placeholders = implode( ', ', array_fill( 0, count( $allowed ), '%s' ) );

        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT country_code, parent_name, name FROM {$table} WHERE type = 'neighborhood' AND is_deleted = 0 AND source = 'default' AND country_code IN ({$placeholders}) ORDER BY country_code, parent_name, name",
            ...$allowed
        ), ARRAY_A );

        $result = array();
        if ( is_array( $rows ) ) {
            foreach ( $rows as $row ) {
                $result[ $row['country_code'] ][ $row['parent_name'] ][] = $row['name'];
            }
        }

        set_transient( self::CACHE_KEY_HOODS, $result, self::CACHE_TTL );
        self::$neighborhoods_cache = $result;
        return $result;
    }

    /**
     * Get all active neighborhoods (default + custom, excluding soft-deleted) indexed by country code.
     *
     * @return array [ 'CM' => [ 'Yaoundé' => ['Bastos', ...], ... ], ... ]
     */
    public static function get_merged_neighborhoods_by_country(): array {
        if ( self::needs_migration() ) {
            return self::legacy_get_merged_neighborhoods_by_country();
        }

        if ( self::$merged_neighborhoods_cache !== null ) {
            return self::$merged_neighborhoods_cache;
        }

        $cached = get_transient( self::CACHE_KEY_MERGED_HOODS );
        if ( $cached !== false ) {
            self::$merged_neighborhoods_cache = $cached;
            return $cached;
        }

        global $wpdb;
        $table = self::get_table_name();

        $allowed = self::get_allowed_country_codes();

        if ( empty( $allowed ) ) {
            self::$merged_neighborhoods_cache = array();
            return self::$merged_neighborhoods_cache;
        }

        $placeholders = implode( ', ', array_fill( 0, count( $allowed ), '%s' ) );

        // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT country_code, parent_name, name FROM {$table} WHERE type = 'neighborhood' AND is_deleted = 0 AND country_code IN ({$placeholders}) ORDER BY country_code, parent_name, name",
            ...$allowed
        ), ARRAY_A );

        $result = array();
        if ( is_array( $rows ) ) {
            foreach ( $rows as $row ) {
                $result[ $row['country_code'] ][ $row['parent_name'] ][] = $row['name'];
            }
        }

        // Ensure alphabetical sort per city
        foreach ( $result as $cc => $cities ) {
            foreach ( $cities as $city => $hoods ) {
                sort( $result[ $cc ][ $city ] );
            }
        }

        set_transient( self::CACHE_KEY_MERGED_HOODS, $result, self::CACHE_TTL );
        self::$merged_neighborhoods_cache = $result;
        return $result;
    }

    /**
     * Get distinct country codes that have region data in the table,
     * intersected with WooCommerce allowed countries.
     *
     * @return array e.g. ['CM', 'SN']
     */
    public static function get_country_codes(): array {
        if ( self::needs_migration() ) {
            return self::legacy_get_country_codes();
        }

        return self::get_allowed_country_codes();
    }

    // =========================================================================
    // Validation helpers
    // =========================================================================

    /**
     * Check if a country code is in the allowed list.
     *
     * @param string $cc Country code.
     * @return bool
     */
    public static function is_valid_country( string $cc ): bool {
        return in_array( strtoupper( $cc ), self::get_country_codes(), true );
    }

    /**
     * Check if a region code belongs to a given country.
     *
     * @param string $cc Country code.
     * @param string $rc Region code.
     * @return bool
     */
    public static function is_valid_region( string $cc, string $rc ): bool {
        $states = self::get_states_by_country();
        return isset( $states[ $cc ] ) && array_key_exists( $rc, $states[ $cc ] );
    }

    /**
     * Check if a city exists anywhere in a country (any region).
     *
     * @param string $cc   Country code.
     * @param string $city City name.
     * @return bool
     */
    public static function is_valid_city_in_country( string $cc, string $city ): bool {
        $places = self::get_merged_places_by_country();
        if ( ! isset( $places[ $cc ] ) ) {
            return false;
        }
        foreach ( $places[ $cc ] as $cities ) {
            if ( in_array( $city, $cities, true ) ) {
                return true;
            }
        }
        return false;
    }

    /**
     * Check if a city belongs to a specific region in a country.
     *
     * @param string $cc   Country code.
     * @param string $rc   Region code.
     * @param string $city City name.
     * @return bool
     */
    public static function is_valid_city_in_region( string $cc, string $rc, string $city ): bool {
        $places = self::get_merged_places_by_country();
        return isset( $places[ $cc ][ $rc ] ) && in_array( $city, $places[ $cc ][ $rc ], true );
    }

    // =========================================================================
    // Lookup helpers
    // =========================================================================

    /**
     * Find the country code that owns a region code.
     * Returns the first match if ambiguous.
     *
     * @param string $rc Region code.
     * @return string Country code or empty string.
     */
    public static function find_country_for_region( string $rc ): string {
        $states = self::get_states_by_country();
        foreach ( $states as $cc => $regions ) {
            if ( array_key_exists( $rc, $regions ) ) {
                return $cc;
            }
        }
        return '';
    }

    /**
     * Find the country code that owns a city name.
     *
     * @param string $city City name.
     * @return string Country code or empty string.
     */
    public static function find_country_for_city( string $city ): string {
        $places = self::get_merged_places_by_country();
        foreach ( $places as $cc => $regions ) {
            foreach ( $regions as $cities ) {
                if ( in_array( $city, $cities, true ) ) {
                    return $cc;
                }
            }
        }
        return '';
    }

    /**
     * Determine if an item is from the default dataset or a custom addition.
     *
     * @param string $item     Item name.
     * @param array  $defaults List of default item names.
     * @return string 'default' or 'custom'.
     */
    public static function get_item_source( string $item, array $defaults ): string {
        return in_array( $item, $defaults, true ) ? 'default' : 'custom';
    }

    // =========================================================================
    // Composite key helpers
    // =========================================================================

    /**
     * Build a composite key: "CC:RC".
     *
     * @param string $cc Country code.
     * @param string $rc Region code (or city name for neighborhood keys).
     * @return string
     */
    public static function composite_key( string $cc, string $rc ): string {
        return $cc . ':' . $rc;
    }

    /**
     * Parse a composite key back to country + region.
     *
     * @param string $key e.g. 'CM:CE'.
     * @return array [ 'country' => 'CM', 'region' => 'CE' ]
     */
    public static function parse_key( string $key ): array {
        $parts = explode( ':', $key, 2 );
        return array(
            'country' => $parts[0],
            'region'  => isset( $parts[1] ) ? $parts[1] : '',
        );
    }

    // =========================================================================
    // Write methods
    // =========================================================================

    /**
     * Add a city to the geodata table.
     *
     * If the city was previously soft-deleted, it is restored.
     *
     * @param string $cc        Country code.
     * @param string $rc        Region code.
     * @param string $city_name City name.
     * @return bool True on success, false if already exists or on failure.
     */
    public static function add_city( string $cc, string $rc, string $city_name ): bool {
        global $wpdb;

        $table = self::get_table_name();

        // Check for existing (including soft-deleted)
        $existing = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, source, is_deleted FROM {$table} WHERE country_code = %s AND region_code = %s AND type = 'city' AND name = %s",
            $cc, $rc, $city_name
        ) );

        if ( $existing && (int) $existing->is_deleted === 1 ) {
            // Restore soft-deleted item
            $wpdb->update( $table, array( 'is_deleted' => 0 ), array( 'id' => $existing->id ) );
            self::invalidate_cache();
            return true;
        }

        if ( $existing ) {
            return false; // Already exists and is active
        }

        $result = $wpdb->insert( $table, array(
            'country_code' => $cc,
            'region_code'  => $rc,
            'type'         => 'city',
            'name'         => $city_name,
            'parent_name'  => '',
            'source'       => 'custom',
            'is_deleted'   => 0,
        ) );

        self::invalidate_cache();
        return $result !== false;
    }

    /**
     * Delete a city from the geodata table.
     *
     * Custom entries are hard-deleted; default entries are soft-deleted.
     * Cascades to neighborhoods of the city.
     *
     * @param string $cc        Country code.
     * @param string $rc        Region code.
     * @param string $city_name City name.
     * @return bool True on success.
     */
    public static function delete_city( string $cc, string $rc, string $city_name ): bool {
        global $wpdb;

        $table = self::get_table_name();

        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, source FROM {$table} WHERE country_code = %s AND region_code = %s AND type = 'city' AND name = %s AND is_deleted = 0",
            $cc, $rc, $city_name
        ) );

        if ( ! $row ) {
            return false;
        }

        if ( $row->source === 'custom' ) {
            $wpdb->delete( $table, array( 'id' => $row->id ) );
        } else {
            $wpdb->update( $table, array( 'is_deleted' => 1 ), array( 'id' => $row->id ) );
        }

        // Cascade: delete neighborhoods of this city
        // Hard delete custom neighborhoods
        $wpdb->query( $wpdb->prepare(
            "DELETE FROM {$table} WHERE country_code = %s AND type = 'neighborhood' AND parent_name = %s AND source = 'custom'",
            $cc, $city_name
        ) );
        // Soft delete default neighborhoods
        $wpdb->query( $wpdb->prepare(
            "UPDATE {$table} SET is_deleted = 1 WHERE country_code = %s AND type = 'neighborhood' AND parent_name = %s AND source = 'default' AND is_deleted = 0",
            $cc, $city_name
        ) );

        self::invalidate_cache();
        return true;
    }

    /**
     * Add a neighborhood to the geodata table.
     *
     * The region_code is looked up from the parent city. If the city
     * does not exist in the table, the operation fails.
     *
     * @param string $cc        Country code.
     * @param string $city_name Parent city name.
     * @param string $hood_name Neighborhood name.
     * @return bool True on success.
     */
    public static function add_neighborhood( string $cc, string $city_name, string $hood_name ): bool {
        global $wpdb;

        $table = self::get_table_name();

        // Find region_code for this city
        $region_code = $wpdb->get_var( $wpdb->prepare(
            "SELECT region_code FROM {$table} WHERE country_code = %s AND type = 'city' AND name = %s AND is_deleted = 0 LIMIT 1",
            $cc, $city_name
        ) );

        if ( $region_code === null ) {
            return false;
        }

        // Check for existing (including soft-deleted)
        $existing = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, is_deleted FROM {$table} WHERE country_code = %s AND type = 'neighborhood' AND name = %s AND parent_name = %s",
            $cc, $hood_name, $city_name
        ) );

        if ( $existing && (int) $existing->is_deleted === 1 ) {
            $wpdb->update( $table, array( 'is_deleted' => 0 ), array( 'id' => $existing->id ) );
            self::invalidate_cache();
            return true;
        }

        if ( $existing ) {
            return false; // Already exists and is active
        }

        $result = $wpdb->insert( $table, array(
            'country_code' => $cc,
            'region_code'  => $region_code,
            'type'         => 'neighborhood',
            'name'         => $hood_name,
            'parent_name'  => $city_name,
            'source'       => 'custom',
            'is_deleted'   => 0,
        ) );

        self::invalidate_cache();
        return $result !== false;
    }

    /**
     * Delete a neighborhood from the geodata table.
     *
     * Custom entries are hard-deleted; default entries are soft-deleted.
     *
     * @param string $cc        Country code.
     * @param string $city_name Parent city name.
     * @param string $hood_name Neighborhood name.
     * @return bool True on success.
     */
    public static function delete_neighborhood( string $cc, string $city_name, string $hood_name ): bool {
        global $wpdb;

        $table = self::get_table_name();

        $row = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, source FROM {$table} WHERE country_code = %s AND type = 'neighborhood' AND name = %s AND parent_name = %s AND is_deleted = 0",
            $cc, $hood_name, $city_name
        ) );

        if ( ! $row ) {
            return false;
        }

        if ( $row->source === 'custom' ) {
            $wpdb->delete( $table, array( 'id' => $row->id ) );
        } else {
            $wpdb->update( $table, array( 'is_deleted' => 1 ), array( 'id' => $row->id ) );
        }

        self::invalidate_cache();
        return true;
    }

    // =========================================================================
    // Cache
    // =========================================================================

    /**
     * Invalidate all per-request static caches.
     *
     * Must be called after any write operation.
     */
    public static function invalidate_cache(): void {
        self::$states_cache               = null;
        self::$places_cache               = null;
        self::$merged_places_cache        = null;
        self::$neighborhoods_cache        = null;
        self::$merged_neighborhoods_cache = null;

        delete_transient( self::CACHE_KEY_STATES );
        delete_transient( self::CACHE_KEY_PLACES );
        delete_transient( self::CACHE_KEY_MERGED_PLACES );
        delete_transient( self::CACHE_KEY_HOODS );
        delete_transient( self::CACHE_KEY_MERGED_HOODS );
    }

    // =========================================================================
    // Internal helpers
    // =========================================================================

    /**
     * Get the list of country codes that have region data in the geodata table
     * and are allowed by WooCommerce settings.
     *
     * Uses the same intersection logic as SCPWOO_Geo_Helper::get_country_codes().
     *
     * @return array
     */
    private static function get_allowed_country_codes(): array {
        global $wpdb;

        $table = self::get_table_name();

        // Get all country codes that have region data
        $supported = $wpdb->get_col(
            "SELECT DISTINCT country_code FROM {$table} WHERE type = 'region' AND is_deleted = 0 ORDER BY country_code"
        );

        if ( empty( $supported ) ) {
            return array();
        }

        // Intersect with WooCommerce allowed countries
        if ( ! function_exists( 'WC' ) || ! WC()->countries ) {
            return $supported;
        }

        $wc_allowed  = WC()->countries->get_allowed_countries();
        $wc_shipping = WC()->countries->get_shipping_countries();
        $all_allowed = array_merge( $wc_allowed, $wc_shipping );
        $allowed_codes = array_map( 'strtoupper', array_keys( $all_allowed ) );

        $filtered = array_values( array_intersect( $supported, $allowed_codes ) );

        // Fallback: if no intersection, use store base country
        if ( empty( $filtered ) ) {
            $plugin = class_exists( 'SCPWOO_States_Places' ) ? SCPWOO_States_Places::instance() : null;
            if ( $plugin ) {
                $base = $plugin->get_store_country();
                if ( in_array( $base, $supported, true ) ) {
                    return array( $base );
                }
            }
        }

        return $filtered;
    }
}
