<?php
/**
 * ایجاد ایندکس‌های GIS برای سرعت لود محلات/مناطق
 * php run-gis-indexes.php
 */
declare(strict_types=1);

define('ACC_BASE_PATH', __DIR__);
require __DIR__ . '/standalone/bootstrap.php';

header('Content-Type: application/json; charset=utf-8');
global $wpdb;

$idx_list = [
    ['gis_neighborhoods', 'idx_district_name',       '(district_id, name)'],
    ['gis_neighborhoods', 'idx_name',                '(name)'],
    ['gis_parcels',       'idx_neighborhood_status', '(neighborhood_id, status)'],
    ['gis_parcels',       'idx_district_status',     '(district_id, status)'],
    ['gis_districts',     'idx_city_name',           '(city, name)'],
    ['gis_districts',     'idx_name',                '(name)'],
];

$created = [];
$skipped = [];

foreach ($idx_list as [$table, $idx_name, $cols]) {
    $tbl = $wpdb->prefix . $table;
    if (!$wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl))) {
        $skipped[] = "{$table}: table missing";
        continue;
    }
    $exists = (int) $wpdb->get_var($wpdb->prepare(
        "SELECT COUNT(*) FROM information_schema.STATISTICS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s",
        $tbl, $idx_name
    ));
    if ($exists) {
        $skipped[] = "{$table}.{$idx_name}";
        continue;
    }
    $wpdb->query("ALTER TABLE {$tbl} ADD INDEX {$idx_name} {$cols}");
    $created[] = "{$table}.{$idx_name}";
}

update_option('acc_engine_db_version', '17.5');

$indexes = [];
foreach (['gis_neighborhoods', 'gis_parcels', 'gis_districts'] as $t) {
    $tbl = $wpdb->prefix . $t;
    $rows = $wpdb->get_results("SHOW INDEX FROM {$tbl}");
    $indexes[$t] = array_values(array_unique(array_map(fn($r) => $r->Key_name, $rows ?: [])));
}

echo json_encode([
    'success'  => true,
    'created'  => $created,
    'skipped'  => $skipped,
    'indexes'  => $indexes,
], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
