<?php
/**
 * GIS_Controller.php — کنترلر ماژول GIS (ثمن)
 * سه لایه: مناطق → محلات → پارسل‌ها
 * به‌علاوه: پرونده کامل ملک، قفل تیمی
 */

if ( ! defined( 'ABSPATH' ) ) exit;

class GIS_Controller {

    /** حذف ستون باینری geom که json_encode را خراب می‌کند */
    private static function _gis_sanitize_rows( $rows ): array {
        if ( ! is_array( $rows ) ) return [];
        foreach ( $rows as &$row ) {
            if ( is_array( $row ) ) {
                unset( $row['geom'] );
            } elseif ( is_object( $row ) ) {
                unset( $row->geom );
            }
        }
        unset( $row );
        return $rows;
    }

    /** ستون‌های امن برای SELECT محلات (بدون geom باینری) */
    private static function _neighborhood_select_cols( string $alias = 'n', bool $with_geometry = true ): string {
        $geo = $with_geometry ? ", {$alias}.geometry" : '';
        return "{$alias}.id, {$alias}.district_id, {$alias}.name, {$alias}.area_km2,
                {$alias}.assigned_user_id, {$alias}.assigned_user_name, {$alias}.dominant_use,
                {$alias}.development_trend, {$alias}.has_metro, {$alias}.has_brt,
                {$alias}.infrastructure{$geo}, {$alias}.notes,
                {$alias}.created_at, {$alias}.updated_at";
    }

    /** ستون‌های سبک منطقه برای لیست (بدون geometry) */
    private static function _district_select_cols( string $alias = 'd', bool $with_geometry = false ): string {
        $geo = $with_geometry ? ", {$alias}.geometry" : '';
        return "{$alias}.id, {$alias}.city, {$alias}.name, {$alias}.code, {$alias}.area_km2,
                {$alias}.assigned_user_id, {$alias}.assigned_user_name, {$alias}.color,
                {$alias}.notes, {$alias}.created_at{$geo}";
    }

    /* ── ثبت مسیرها ───────────────────────────────────────────── */
    public static function register_routes(): void {
        $ns = 'acc/v1';
        $p  = 'acc_jwt_check_permission';

        // ── ACL-aware permission callbacks ────────────────────
        $perm_view   = function_exists('acl_permission_callback') ? acl_permission_callback('gis.view')           : $p;
        $perm_pview  = function_exists('acl_permission_callback') ? acl_permission_callback('gis.parcels.view')   : $p;
        $perm_pedit  = function_exists('acl_permission_callback') ? acl_permission_callback('gis.parcels.edit')   : $p;
        $perm_pnew   = function_exists('acl_permission_callback') ? acl_permission_callback('gis.parcels.create') : $p;
        $perm_pdel   = function_exists('acl_permission_callback') ? acl_permission_callback('gis.parcels.delete') : $p;
        $perm_plock  = function_exists('acl_permission_callback') ? acl_permission_callback('gis.parcels.lock')   : $p;

        // داده نقشه (یک درخواست برای رندر کامل نقشه)
        register_rest_route($ns, '/gis/map-data', [
            'methods' => 'GET', 'callback' => [self::class,'get_map_data'], 'permission_callback' => $perm_view,
        ]);

        // مناطق
        register_rest_route($ns, '/gis/districts', [
            ['methods'=>'GET',  'callback'=>[self::class,'get_districts'],   'permission_callback'=>$p],
            ['methods'=>'POST', 'callback'=>[self::class,'create_district'], 'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/districts/(?P<id>\d+)', [
            ['methods'=>'PUT',    'callback'=>[self::class,'update_district'], 'permission_callback'=>$p],
            ['methods'=>'DELETE', 'callback'=>[self::class,'delete_district'], 'permission_callback'=>$p],
        ]);

        // محلات
        register_rest_route($ns, '/gis/neighborhoods', [
            ['methods'=>'GET',  'callback'=>[self::class,'get_neighborhoods'],   'permission_callback'=>$p],
            ['methods'=>'POST', 'callback'=>[self::class,'create_neighborhood'], 'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/neighborhoods/(?P<id>\d+)', [
            ['methods'=>'PUT',    'callback'=>[self::class,'update_neighborhood'], 'permission_callback'=>$p],
            ['methods'=>'DELETE', 'callback'=>[self::class,'delete_neighborhood'], 'permission_callback'=>$p],
        ]);

        // پارسل‌ها
        register_rest_route($ns, '/gis/parcels', [
            ['methods'=>'GET',  'callback'=>[self::class,'get_parcels'],   'permission_callback'=>$perm_pview],
            ['methods'=>'POST', 'callback'=>[self::class,'create_parcel'], 'permission_callback'=>$perm_pnew],
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)', [
            ['methods'=>'GET',    'callback'=>[self::class,'get_parcel'],   'permission_callback'=>$perm_pview],
            ['methods'=>'PUT',    'callback'=>[self::class,'update_parcel'], 'permission_callback'=>$perm_pedit],
            ['methods'=>'DELETE', 'callback'=>[self::class,'delete_parcel'], 'permission_callback'=>$perm_pdel],
        ]);

        // زیر-مسیرهای دفترهای پارسل
        foreach (['land','building','use'] as $sub) {
            register_rest_route($ns, "/gis/parcels/(?P<id>\\d+)/{$sub}", [
                'methods'=>'PUT', 'callback'=>[self::class,"save_parcel_{$sub}"], 'permission_callback'=>$p,
            ]);
        }
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/owners', [
            ['methods'=>'GET',  'callback'=>[self::class,'get_parcel_owners'], 'permission_callback'=>$p],
            ['methods'=>'POST', 'callback'=>[self::class,'save_parcel_owner'], 'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/owners/(?P<owner_id>\d+)', [
            'methods'=>'DELETE', 'callback'=>[self::class,'delete_parcel_owner'], 'permission_callback'=>$p,
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/history', [
            ['methods'=>'GET',  'callback'=>[self::class,'get_ownership_history'], 'permission_callback'=>$p],
            ['methods'=>'POST', 'callback'=>[self::class,'add_ownership_history'], 'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/issues', [
            ['methods'=>'GET',  'callback'=>[self::class,'get_parcel_issues'], 'permission_callback'=>$p],
            ['methods'=>'POST', 'callback'=>[self::class,'save_parcel_issue'], 'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/issues/(?P<issue_id>\d+)', [
            ['methods'=>'PUT',    'callback'=>[self::class,'save_parcel_issue'],   'permission_callback'=>$p],
            ['methods'=>'DELETE', 'callback'=>[self::class,'delete_parcel_issue'], 'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/commissions', [
            ['methods'=>'GET',  'callback'=>[self::class,'get_commissions'], 'permission_callback'=>$p],
            ['methods'=>'POST', 'callback'=>[self::class,'save_commission'],  'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/commissions/(?P<c_id>\d+)', [
            ['methods'=>'PUT',    'callback'=>[self::class,'save_commission'],   'permission_callback'=>$p],
            ['methods'=>'DELETE', 'callback'=>[self::class,'delete_commission'], 'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/history/(?P<hid>\d+)', [
            'methods'=>'DELETE', 'callback'=>[self::class,'delete_ownership_history'], 'permission_callback'=>$p,
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/lock', [
            'methods'=>'POST', 'callback'=>[self::class,'lock_parcel'], 'permission_callback'=>$perm_plock,
        ]);
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/unlock', [
            'methods'=>'POST', 'callback'=>[self::class,'unlock_parcel'], 'permission_callback'=>$perm_plock,
        ]);

        // اتصال به CPM
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/create-project', [
            'methods'=>'POST', 'callback'=>[self::class,'create_project_for_parcel'], 'permission_callback'=>$p,
        ]);

        // کد نوسازی واحدها
        register_rest_route($ns, '/gis/parcels/(?P<id>\d+)/units', [
            ['methods'=>'GET',  'callback'=>[self::class,'get_parcel_units'],   'permission_callback'=>$p],
            ['methods'=>'POST', 'callback'=>[self::class,'add_parcel_unit'],    'permission_callback'=>$p],
        ]);
        register_rest_route($ns, '/gis/parcel-units/(?P<uid>\d+)', [
            'methods'=>'DELETE', 'callback'=>[self::class,'delete_parcel_unit'], 'permission_callback'=>$p,
        ]);

        // داشبورد املاک
        register_rest_route($ns, '/gis/dashboard', [
            'methods' => 'GET', 'callback' => [self::class,'get_dashboard'], 'permission_callback' => $p,
        ]);
        register_rest_route($ns, '/gis/dashboard/land-uses', [
            'methods' => 'GET', 'callback' => [self::class,'get_land_uses'], 'permission_callback' => $p,
        ]);
        // داشبورد drill-down: منطقه → محله → بلوک (شمارش بر حسب بلوک)
        register_rest_route($ns, '/gis/drilldown', [
            'methods' => 'GET', 'callback' => [self::class,'get_drilldown'], 'permission_callback' => $p,
        ]);
        // پارسل‌های یک بلوک + پرچم is_match نسبت به فیلترها (برای رنگ‌آمیزی نقشه)
        register_rest_route($ns, '/gis/block-parcels', [
            'methods' => 'GET', 'callback' => [self::class,'get_block_parcels'], 'permission_callback' => $perm_pview,
        ]);
        // بازسازی دستی انبار آمار بلوک‌ها (data warehouse) — بعد از import یا تغییرات بزرگ
        register_rest_route($ns, '/gis/rebuild-stats', [
            'methods' => 'POST', 'callback' => [self::class,'rebuild_stats_endpoint'], 'permission_callback' => $p,
        ]);

        // کلیک روی نقشه → نزدیک‌ترین بلوک
        register_rest_route($ns, '/gis/parcels-by-click', [
            'methods' => 'GET', 'callback' => [self::class,'get_parcels_by_click'], 'permission_callback' => $p,
        ]);

        // جستجو بر اساس کد نوسازی
        register_rest_route($ns, '/gis/search-by-code', [
            'methods' => 'GET', 'callback' => [self::class,'search_by_code'], 'permission_callback' => $p,
        ]);

        // بازسازی جدول blocks (یک‌بار بعد از import یا تغییرات بزرگ)
        register_rest_route($ns, '/gis/rebuild-blocks', [
            'methods' => 'POST', 'callback' => [self::class,'rebuild_blocks'], 'permission_callback' => $p,
        ]);

        // لیست لایه‌های موجود (LayerIDهای distinct)
        register_rest_route($ns, '/gis/layers-list', [
            'methods' => 'GET', 'callback' => [self::class,'get_layers_list'], 'permission_callback' => $p,
        ]);

        // ایمپورت JSON
        register_rest_route($ns, '/gis/import', [
            'methods'=>'POST', 'callback'=>[self::class,'import_parcels'], 'permission_callback'=>$p,
        ]);
        register_rest_route($ns, '/gis/import/install', [
            'methods'=>'POST', 'callback'=>[self::class,'install_parcel_units_table'], 'permission_callback'=>$p,
        ]);
    }

    /* ══════════════════════════════════════════════════════════
       MAP DATA — یک endpoint برای رندر کامل نقشه
    ══════════════════════════════════════════════════════════ */
    public static function get_map_data( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;

        $uid = acl_current_user_id();

        // مناطق
        $d_wheres = [];
        $d_args   = [];
        if ( ! acl_append_scope_where( $uid, 'district', 'd.id', $d_wheres, $d_args ) ) {
            return new WP_REST_Response( [
                'districts' => [], 'neighborhoods' => [], 'parcels' => [],
            ] );
        }
        $d_where = $d_wheres ? 'WHERE ' . implode( ' AND ', $d_wheres ) : '';
        $d_sql   = "SELECT d.id, d.city, d.name, d.code, d.color, d.geometry, d.assigned_user_name
                    FROM {$pre}gis_districts d {$d_where} ORDER BY d.name";
        $districts = $d_args
            ? $wpdb->get_results( $wpdb->prepare( $d_sql, ...$d_args ), ARRAY_A )
            : $wpdb->get_results( $d_sql, ARRAY_A );

        // محلات (با geometry برای رسم روی نقشه)
        $n_wheres = [];
        $n_args   = [];
        if ( ! acl_append_scope_where( $uid, 'district', 'n.district_id', $n_wheres, $n_args ) ) {
            return new WP_REST_Response( [
                'districts' => self::_gis_sanitize_rows( $districts ),
                'neighborhoods' => [], 'parcels' => [],
            ] );
        }
        $n_where = $n_wheres ? 'WHERE ' . implode( ' AND ', $n_wheres ) : '';
        $n_cols  = self::_neighborhood_select_cols( 'n', true );
        $n_sql   = "SELECT {$n_cols}, d.name AS district_name, d.color AS district_color
                    FROM {$pre}gis_neighborhoods n
                    INNER JOIN {$pre}gis_districts d ON d.id = n.district_id
                    {$n_where} ORDER BY n.district_id, n.name";
        $neighborhoods = $n_args
            ? $wpdb->get_results( $wpdb->prepare( $n_sql, ...$n_args ), ARRAY_A )
            : $wpdb->get_results( $n_sql, ARRAY_A );

        return new WP_REST_Response( [
            'districts'     => self::_gis_sanitize_rows( $districts ?: [] ),
            'neighborhoods' => self::_gis_sanitize_rows( $neighborhoods ?: [] ),
            'parcels'       => [],
        ] );
    }

    /* ══════════════════════════════════════════════════════════
       مناطق
    ══════════════════════════════════════════════════════════ */
    public static function get_districts( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;
        $city = sanitize_text_field( $req->get_param('city') ?? '' );
        $wheres = [];
        $args   = [];
        if ( $city ) { $wheres[] = 'd.city = %s'; $args[] = $city; }
        $uid = acl_current_user_id();
        if ( ! acl_append_scope_where( $uid, 'district', 'd.id', $wheres, $args ) ) {
            return new WP_REST_Response( ['data' => []] );
        }
        $where = $wheres ? 'WHERE ' . implode( ' AND ', $wheres ) : '';

        $d_cols = self::_district_select_cols( 'd', false );
        $sql = "SELECT {$d_cols},
                    COALESCE(nc.cnt, 0) AS neighborhood_count,
                    COALESCE(pc.cnt, 0) AS parcel_count
             FROM {$pre}gis_districts d
             LEFT JOIN (
                 SELECT district_id, COUNT(*) AS cnt
                 FROM {$pre}gis_neighborhoods GROUP BY district_id
             ) nc ON nc.district_id = d.id
             LEFT JOIN (
                 SELECT district_id, COUNT(*) AS cnt
                 FROM {$pre}gis_parcels
                 WHERE status != 'archived' AND district_id IS NOT NULL
                 GROUP BY district_id
             ) pc ON pc.district_id = d.id
             {$where} ORDER BY d.city, d.name";
        $rows = $args ? $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A )
                      : $wpdb->get_results( $sql, ARRAY_A );
        return new WP_REST_Response(['data' => self::_gis_sanitize_rows( $rows ?: [] )]);
    }

    public static function create_district( WP_REST_Request $req ): WP_REST_Response {
        $deny = acl_deny_if_scope_restricted( acl_current_user_id(), 'district', 'ایجاد منطقه فقط برای مدیران سراسری مجاز است' );
        if ( $deny ) return $deny;
        global $wpdb;
        $data = [
            'city'               => sanitize_text_field($req->get_param('city') ?: 'تهران'),
            'name'               => sanitize_text_field($req->get_param('name') ?: ''),
            'code'               => sanitize_text_field($req->get_param('code') ?: ''),
            'area_km2'           => $req->get_param('area_km2') ? (float)$req->get_param('area_km2') : null,
            'assigned_user_id'   => $req->get_param('assigned_user_id') ? (int)$req->get_param('assigned_user_id') : null,
            'assigned_user_name' => sanitize_text_field($req->get_param('assigned_user_name') ?: ''),
            'color'              => sanitize_hex_color($req->get_param('color') ?: '#3b82f6') ?: '#3b82f6',
            'geometry'           => $req->get_param('geometry') ?: null,
            'notes'              => sanitize_textarea_field($req->get_param('notes') ?: ''),
        ];
        if ( ! $data['name'] ) return new WP_REST_Response(['message'=>'نام منطقه الزامی است'], 400);
        $wpdb->insert($wpdb->prefix.'gis_districts', $data);
        return new WP_REST_Response(['id' => $wpdb->insert_id, 'success' => true], 201);
    }

    public static function update_district( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $deny = acl_deny_scope( acl_current_user_id(), 'district', $id, 'gis.view' );
        if ( $deny ) return $deny;
        $data = array_filter([
            'city'               => sanitize_text_field($req->get_param('city') ?: ''),
            'name'               => sanitize_text_field($req->get_param('name') ?: ''),
            'code'               => sanitize_text_field($req->get_param('code') ?: ''),
            'area_km2'           => $req->get_param('area_km2') !== null ? (float)$req->get_param('area_km2') : null,
            'assigned_user_id'   => $req->get_param('assigned_user_id') ? (int)$req->get_param('assigned_user_id') : null,
            'assigned_user_name' => sanitize_text_field($req->get_param('assigned_user_name') ?: ''),
            'color'              => sanitize_hex_color($req->get_param('color') ?: '') ?: null,
            'geometry'           => $req->get_param('geometry'),
            'notes'              => sanitize_textarea_field($req->get_param('notes') ?: ''),
        ], fn($v) => $v !== null && $v !== '');
        $wpdb->update($wpdb->prefix.'gis_districts', $data, ['id'=>$id]);
        return new WP_REST_Response(['success'=>true]);
    }

    public static function delete_district( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id = (int)$req->get_param('id');
        $deny = acl_deny_scope( acl_current_user_id(), 'district', $id, 'gis.view' );
        if ( $deny ) return $deny;
        $wpdb->delete($wpdb->prefix.'gis_districts', ['id'=>$id]);
        return new WP_REST_Response(['success'=>true]);
    }

    /* ══════════════════════════════════════════════════════════
       محلات
    ══════════════════════════════════════════════════════════ */
    public static function get_neighborhoods( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;
        $did  = $req->get_param('district_id') ? (int)$req->get_param('district_id') : 0;
        $wheres = [];
        $args   = [];
        if ( $did ) {
            $deny = acl_deny_scope( acl_current_user_id(), 'district', $did, 'gis.view' );
            if ( $deny ) return $deny;
            $wheres[] = 'n.district_id = %d';
            $args[]   = $did;
        }
        $uid = acl_current_user_id();
        if ( ! acl_append_scope_where( $uid, 'district', 'n.district_id', $wheres, $args ) ) {
            return new WP_REST_Response( ['data' => []] );
        }
        $where = $wheres ? 'WHERE ' . implode( ' AND ', $wheres ) : '';

        $n_cols = self::_neighborhood_select_cols( 'n', false );
        $sql = "SELECT {$n_cols}, d.name AS district_name,
                    COALESCE(pc.cnt, 0) AS parcel_count
             FROM {$pre}gis_neighborhoods n
             LEFT JOIN {$pre}gis_districts d ON d.id = n.district_id
             LEFT JOIN (
                 SELECT neighborhood_id, COUNT(*) AS cnt
                 FROM {$pre}gis_parcels
                 WHERE status != 'archived' AND neighborhood_id IS NOT NULL
                 GROUP BY neighborhood_id
             ) pc ON pc.neighborhood_id = n.id
             {$where} ORDER BY n.district_id, n.name";
        $rows = $args ? $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A )
                      : $wpdb->get_results( $sql, ARRAY_A );
        return new WP_REST_Response(['data' => self::_gis_sanitize_rows( $rows ?: [] )]);
    }

    public static function create_neighborhood( WP_REST_Request $req ): WP_REST_Response {
        $did = (int) ( $req->get_param('district_id') ?: 0 );
        $deny = acl_deny_scope( acl_current_user_id(), 'district', $did ?: null, 'gis.view' );
        if ( $deny ) return $deny;
        global $wpdb;
        $data = [
            'district_id'        => (int)($req->get_param('district_id') ?: 0),
            'name'               => sanitize_text_field($req->get_param('name') ?: ''),
            'area_km2'           => $req->get_param('area_km2') ? (float)$req->get_param('area_km2') : null,
            'assigned_user_id'   => $req->get_param('assigned_user_id') ? (int)$req->get_param('assigned_user_id') : null,
            'assigned_user_name' => sanitize_text_field($req->get_param('assigned_user_name') ?: ''),
            'dominant_use'       => sanitize_text_field($req->get_param('dominant_use') ?: ''),
            'development_trend'  => sanitize_text_field($req->get_param('development_trend') ?: 'stable'),
            'has_metro'          => $req->get_param('has_metro') ? 1 : 0,
            'has_brt'            => $req->get_param('has_brt') ? 1 : 0,
            'infrastructure'     => sanitize_text_field($req->get_param('infrastructure') ?: 'complete'),
            'geometry'           => $req->get_param('geometry') ?: null,
            'notes'              => sanitize_textarea_field($req->get_param('notes') ?: ''),
        ];
        if ( ! $data['name'] || ! $data['district_id'] ) {
            return new WP_REST_Response(['message'=>'نام و منطقه الزامی است'], 400);
        }
        $wpdb->insert($wpdb->prefix.'gis_neighborhoods', $data);
        return new WP_REST_Response(['id' => $wpdb->insert_id, 'success' => true], 201);
    }

    public static function update_neighborhood( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id  = (int)$req->get_param('id');
        $row = $wpdb->get_row( $wpdb->prepare( "SELECT district_id FROM {$wpdb->prefix}gis_neighborhoods WHERE id=%d", $id ), ARRAY_A );
        if ( $row ) {
            $deny = acl_deny_scope( acl_current_user_id(), 'district', (int) $row['district_id'], 'gis.view' );
            if ( $deny ) return $deny;
        }
        if ( $req->get_param('district_id') ) {
            $deny = acl_deny_scope( acl_current_user_id(), 'district', (int) $req->get_param('district_id'), 'gis.view' );
            if ( $deny ) return $deny;
        }
        $data = [];
        $map  = [
            'district_id','name','area_km2','assigned_user_id','assigned_user_name',
            'dominant_use','development_trend','has_metro','has_brt','infrastructure','geometry','notes',
        ];
        foreach ($map as $k) {
            $v = $req->get_param($k);
            if ($v !== null) $data[$k] = in_array($k,['has_metro','has_brt']) ? (int)(bool)$v : sanitize_text_field((string)$v);
        }
        $wpdb->update($wpdb->prefix.'gis_neighborhoods', $data, ['id'=>$id]);
        return new WP_REST_Response(['success'=>true]);
    }

    public static function delete_neighborhood( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $wpdb->delete($wpdb->prefix.'gis_neighborhoods', ['id'=>(int)$req->get_param('id')]);
        return new WP_REST_Response(['success'=>true]);
    }

    /* ══════════════════════════════════════════════════════════
       پارسل‌ها — CRUD پایه
    ══════════════════════════════════════════════════════════ */
    public static function get_parcels( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre    = $wpdb->prefix;
        $wheres = ['p.status != \'archived\''];
        $args   = [];

        if ($v = $req->get_param('district_id'))     { $wheres[] = 'p.district_id = %d';     $args[] = (int)$v; }
        if ($v = $req->get_param('neighborhood_id')) { $wheres[] = 'p.neighborhood_id = %d'; $args[] = (int)$v; }
        $uid = acl_current_user_id();
        if ( ! acl_append_scope_where( $uid, 'district', 'p.district_id', $wheres, $args ) ) {
            return new WP_REST_Response( ['data' => [], 'total' => 0] );
        }
        if ($v = $req->get_param('status'))          { $wheres[] = 'p.status = %s';           $args[] = sanitize_text_field($v); }
        if ($v = $req->get_param('assigned_user_id')){ $wheres[] = 'p.assigned_user_id = %d'; $args[] = (int)$v; }
        if ($v = $req->get_param('search'))          { $wheres[] = '(p.code LIKE %s OR p.address LIKE %s)'; $s="%{$v}%"; $args[]=$s; $args[]=$s; }
        if ($v = $req->get_param('layer_id'))        { $wheres[] = 'p.LayerID = %s';          $args[] = sanitize_text_field($v); }
        // ── فیلتر کاربری (current_use) — برای داشبورد کاربری در سطح پارسل ──
        if ($v = $req->get_param('current_use') ?: $req->get_param('land_use')) {
            $wheres[] = 'p.current_use = %s'; $args[] = sanitize_text_field($v);
        }
        if (($v = $req->get_param('area_min')) !== null && $v !== '') { $wheres[] = 'p.area_sqm >= %f'; $args[] = (float)$v; }
        if (($v = $req->get_param('area_max')) !== null && $v !== '') { $wheres[] = 'p.area_sqm <= %f'; $args[] = (float)$v; }

        // ── فیلتر بلوک (block_code: منطقه-حوزه-بلوک) — index-friendly ──
        if ($v = $req->get_param('block')) {
            $wheres[] = 'p.block_code = %s';
            $args[]   = sanitize_text_field($v);
        }
        // ── چند بلوک همزمان (block=10-2-162,10-2-165) ──────────
        if ($v = $req->get_param('blocks')) {
            $block_list = array_filter(array_map('trim', explode(',', sanitize_text_field($v))));
            if ($block_list) {
                $placeholders = implode(',', array_fill(0, count($block_list), '%s'));
                $wheres[]     = "p.block_code IN ({$placeholders})";
                foreach ($block_list as $b) $args[] = $b;
            }
        }

        // ── انتخاب ستون‌ها: light=1 → فقط فیلدهای لازم نقشه ──
        $is_light = (string) $req->get_param('light') === '1';
        $cols = $is_light
            ? "p.id, p.lat, p.lng, p.code, p.block_code, p.status, p.pipeline_stage,
               p.assigned_user_name, p.locked_by_name"
            : "p.*, d.name AS district_name, n.name AS neighborhood_name";

        $joins = $is_light
            ? ""
            : "LEFT JOIN {$pre}gis_districts d ON d.id = p.district_id
               LEFT JOIN {$pre}gis_neighborhoods n ON n.id = p.neighborhood_id";

        // include geometry فقط در صورت درخواست صریح
        $with_geo = (string) $req->get_param('with_geometry') === '1';
        if ( $is_light && $with_geo ) $cols .= ", p.geometry";

        // limit (پیش‌فرض 500 برای جلوگیری از حجم بالا)
        $limit = min( (int) ( $req->get_param('per_page') ?? 500 ), 5000 );

        $where = 'WHERE '.implode(' AND ',$wheres);
        $sql   = "SELECT {$cols}
                  FROM {$pre}gis_parcels p
                  {$joins}
                  {$where}
                  ORDER BY p.id ASC
                  LIMIT {$limit}";

        $rows = $args ? $wpdb->get_results($wpdb->prepare($sql,...$args), ARRAY_A)
                      : $wpdb->get_results($sql, ARRAY_A);

        return new WP_REST_Response(['data'=>$rows ?? [], 'total'=>count($rows ?? [])]);
    }

    public static function get_parcel( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $id  = (int)$req->get_param('id');

        // ── (1) Transient cache: 2 دقیقه ──
        $with_geo  = (string) $req->get_param('with_geometry') === '1';
        $cache_key = "acc_gis_parcel_{$id}" . ($with_geo ? '_geo' : '');
        $cached    = get_transient( $cache_key );
        if ( $cached !== false ) {
            $deny = acl_deny_scope( acl_current_user_id(), 'district', (int) ( $cached['district_id'] ?? 0 ) ?: null, 'gis.parcels.view' );
            if ( $deny ) return $deny;
            return new WP_REST_Response(['data' => $cached, 'cached' => true]);
        }

        // ── (2) ستون geometry فقط در صورت درخواست ──
        $geo_col = $with_geo ? ', p.geometry' : '';

        $parcel = $wpdb->get_row($wpdb->prepare(
            "SELECT p.id, p.district_id, p.neighborhood_id, p.assigned_user_id, p.assigned_user_name,
                    p.code, p.block_code, p.sub_code, p.registry_section, p.address,
                    p.lat, p.lng, p.status, p.pipeline_stage, p.cpm_project_id,
                    p.locked_by_user_id, p.locked_by_name, p.locked_at,
                    p.notes, p.created_at, p.updated_at
                    {$geo_col},
                    d.name AS district_name, n.name AS neighborhood_name
             FROM {$pre}gis_parcels p
             LEFT JOIN {$pre}gis_districts d ON d.id = p.district_id
             LEFT JOIN {$pre}gis_neighborhoods n ON n.id = p.neighborhood_id
             WHERE p.id = %d", $id
        ), ARRAY_A);

        if ( ! $parcel ) return new WP_REST_Response(['message'=>'پارسل یافت نشد'], 404);

        $deny = acl_deny_scope( acl_current_user_id(), 'district', (int) ( $parcel['district_id'] ?? 0 ) ?: null, 'gis.parcels.view' );
        if ( $deny ) return $deny;

        // ── (3) تک کوئری ترکیبی با UNION ALL ──
        // به جای ۷ کوئری مجزا، یک کوئری بزرگ
        $combined = $wpdb->get_results( $wpdb->prepare(
            "
            SELECT 'land' AS rtype, JSON_OBJECT(
                'id',id,'area_sqm',area_sqm,'shape',shape,'position',position,
                'ownership_type',ownership_type,'is_national_land',is_national_land,
                'north_dim',north_dim,'south_dim',south_dim,'east_dim',east_dim,'west_dim',west_dim,
                'main_street_width',main_street_width,'main_street_dir',main_street_dir,
                'second_street_width',second_street_width,'second_street_dir',second_street_dir
            ) AS data FROM {$pre}gis_parcel_land WHERE parcel_id=%d
            UNION ALL
            SELECT 'building', JSON_OBJECT(
                'id',id,'has_building',has_building,'floors',floors,'units',units,
                'total_area_sqm',total_area_sqm,'built_year',built_year,
                'structure_type',structure_type,'facade',facade,'has_basement',has_basement,
                'has_permit',has_permit,'permit_number',permit_number,'has_completion',has_completion,
                'has_water',has_water,'has_electricity',has_electricity,'has_gas',has_gas,
                'has_elevator',has_elevator,'has_parking',has_parking,'has_storage',has_storage
            ) FROM {$pre}gis_parcel_building WHERE parcel_id=%d
            UNION ALL
            SELECT 'use', JSON_OBJECT(
                'id',id,'current_use',current_use,'approved_use',approved_use,
                'density_pct',density_pct,'coverage_pct',coverage_pct,
                'max_floors',max_floors,'max_build_area',max_build_area
            ) FROM {$pre}gis_parcel_use WHERE parcel_id=%d
            ",
            $id, $id, $id
        ), ARRAY_A );

        // ساختار خالی پیش‌فرض
        $parcel['land']     = null;
        $parcel['building'] = null;
        $parcel['use']      = null;

        foreach ( $combined as $row ) {
            $decoded = json_decode( $row['data'], true );
            if ( $decoded ) $parcel[ $row['rtype'] ] = $decoded;
        }

        // ── (4) لیست‌ها — هر کدام limit دارند ──
        $parcel['owners']      = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$pre}gis_parcel_owners
             WHERE parcel_id=%d ORDER BY is_primary DESC LIMIT 50", $id
        ), ARRAY_A) ?: [];

        $parcel['history']     = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$pre}gis_ownership_history
             WHERE parcel_id=%d ORDER BY transfer_date DESC LIMIT 100", $id
        ), ARRAY_A) ?: [];

        $parcel['issues']      = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$pre}gis_parcel_issues
             WHERE parcel_id=%d ORDER BY severity DESC, created_at DESC LIMIT 100", $id
        ), ARRAY_A) ?: [];

        $parcel['commissions'] = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$pre}gis_commissions
             WHERE parcel_id=%d ORDER BY year DESC LIMIT 50", $id
        ), ARRAY_A) ?: [];

        // ── (5) محاسبه امتیاز ──
        $parcel['score'] = self::_calc_score($parcel);

        // ── (6) cache کن ──
        set_transient( $cache_key, $parcel, 2 * MINUTE_IN_SECONDS );

        return new WP_REST_Response(['data'=>$parcel, 'cached'=>false]);
    }

    /** پاک کردن cache یک پارسل (هر دو نسخه با/بدون geometry) */
    private static function _invalidate_parcel_cache( int $id ): void {
        delete_transient( "acc_gis_parcel_{$id}" );
        delete_transient( "acc_gis_parcel_{$id}_geo" );
        self::_bump_dashboard_version(); // آمار داشبورد ممکن است تغییر کرده باشد
    }

    public static function create_parcel( WP_REST_Request $req ): WP_REST_Response {
        $did  = $req->get_param('district_id') ? (int) $req->get_param('district_id') : null;
        $deny = acl_deny_scope( acl_current_user_id(), 'district', $did, 'gis.parcels.create' );
        if ( $deny ) return $deny;
        global $wpdb;
        $code = sanitize_text_field($req->get_param('code') ?: '');
        $data = [
            'district_id'        => $req->get_param('district_id') ? (int)$req->get_param('district_id') : null,
            'neighborhood_id'    => $req->get_param('neighborhood_id') ? (int)$req->get_param('neighborhood_id') : null,
            'assigned_user_id'   => $req->get_param('assigned_user_id') ? (int)$req->get_param('assigned_user_id') : null,
            'assigned_user_name' => sanitize_text_field($req->get_param('assigned_user_name') ?: ''),
            'code'               => $code,
            'block_code'         => self::_extract_block_code($code),
            'sub_code'           => sanitize_text_field($req->get_param('sub_code') ?: ''),
            'registry_section'   => sanitize_text_field($req->get_param('registry_section') ?: ''),
            'address'            => sanitize_textarea_field($req->get_param('address') ?: ''),
            'lat'                => $req->get_param('lat') ? (float)$req->get_param('lat') : null,
            'lng'                => $req->get_param('lng') ? (float)$req->get_param('lng') : null,
            'geometry'           => $req->get_param('geometry') ?: null,
            'status'             => sanitize_text_field($req->get_param('status') ?: 'prospect'),
            'pipeline_stage'     => sanitize_text_field($req->get_param('pipeline_stage') ?: 'discovery'),
            'notes'              => sanitize_textarea_field($req->get_param('notes') ?: ''),
        ];
        $wpdb->insert($wpdb->prefix.'gis_parcels', $data);
        self::_invalidate_block_cache($data['block_code']);
        return new WP_REST_Response(['id'=>$wpdb->insert_id,'success'=>true], 201);
    }

    /** استخراج block_code از code: "6-91-24-1-0-0-0" → "6-91-24" */
    private static function _extract_block_code( ?string $code ): ?string {
        if ( ! $code ) return null;
        $parts = explode( '-', $code );
        return count( $parts ) >= 3 ? implode( '-', array_slice( $parts, 0, 3 ) ) : null;
    }

    /** پاک کردن transient cache یک بلوک */
    private static function _invalidate_block_cache( ?string $block_code ): void {
        self::_bump_dashboard_version(); // ایجاد/جابه‌جایی پارسل آمار داشبورد را تغییر می‌دهد (حتی اگر block_code خالی باشد)
        if ( ! $block_code ) return;
        global $wpdb;
        // پاک کردن همه نسخه‌های (با و بدون layer_id)
        $wpdb->query( $wpdb->prepare(
            "DELETE FROM {$wpdb->options}
             WHERE option_name LIKE %s OR option_name LIKE %s",
            '_transient_acc_gis_block_parcels_' . md5( $block_code . '|' ),
            '_transient_acc_gis_block_parcels_%'
        ) );
        delete_transient( 'acc_gis_layers_list' );
    }

    /**
     * جستجو بر اساس کد نوسازی
     *  - تطابق دقیق اولویت دارد
     *  - اگر دقیق نبود، تطابق با پیشوند برمی‌گرداند (تا 20 ردیف)
     *  - یافت شده + بلوک مربوطه برگشت داده می‌شود
     */
    public static function search_by_code( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;
        $code = trim( sanitize_text_field( $req->get_param('code') ?? '' ) );

        if ( ! $code ) return new WP_REST_Response( ['message' => 'code الزامی است'], 400 );

        // 1) تطابق دقیق
        $exact = $wpdb->get_row( $wpdb->prepare(
            "SELECT id, code, block_code, lat, lng, geometry, status, address,
                    district_id, neighborhood_id
             FROM {$pre}gis_parcels
             WHERE code = %s AND status != 'archived'
             LIMIT 1",
            $code
        ), ARRAY_A );

        if ( $exact ) {
            // پارسل پیدا شد → کل بلوک را هم بازگردان
            $block_parcels = $wpdb->get_results( $wpdb->prepare(
                "SELECT id, code, block_code, lat, lng, geometry, status,
                        address, district_id, neighborhood_id,
                        assigned_user_name, locked_by_name
                 FROM {$pre}gis_parcels
                 WHERE block_code = %s AND status != 'archived'
                 ORDER BY code ASC",
                $exact['block_code']
            ), ARRAY_A );

            return new WP_REST_Response([
                'match_type' => 'exact',
                'parcel'     => $exact,
                'block'      => $exact['block_code'],
                'parcels'    => $block_parcels ?? [],
                'total'      => count( $block_parcels ?? [] ),
            ]);
        }

        // 2) تطابق پیشوندی (نتیجه چندتایی)
        $like = $wpdb->esc_like( $code ) . '%';
        $matches = $wpdb->get_results( $wpdb->prepare(
            "SELECT id, code, block_code, lat, lng, status, address
             FROM {$pre}gis_parcels
             WHERE code LIKE %s AND status != 'archived'
             ORDER BY code ASC
             LIMIT 20",
            $like
        ), ARRAY_A );

        return new WP_REST_Response([
            'match_type' => $matches ? 'partial' : 'none',
            'parcel'     => null,
            'matches'    => $matches ?? [],
            'total'      => count( $matches ?? [] ),
        ]);
    }

    /**
     * لیست LayerIDهای موجود + تعداد پارسل هر کدام
     */
    public static function get_layers_list( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $cache_key = 'acc_gis_layers_list';
        $cached = get_transient( $cache_key );
        if ( $cached !== false ) return new WP_REST_Response(['data' => $cached]);

        $rows = $wpdb->get_results(
            "SELECT LayerID AS layer_id, COUNT(*) AS parcel_count
             FROM {$wpdb->prefix}gis_parcels
             WHERE LayerID IS NOT NULL AND LayerID != ''
               AND status != 'archived'
             GROUP BY LayerID
             ORDER BY LayerID ASC",
            ARRAY_A
        );

        set_transient( $cache_key, $rows ?? [], 30 * MINUTE_IN_SECONDS );
        return new WP_REST_Response(['data' => $rows ?? []]);
    }

    /**
     * بازسازی جدول wp_gis_blocks از روی wp_gis_parcels
     * (یک‌بار بعد از import یا تغییرات بزرگ)
     */
    public static function rebuild_blocks( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;

        // populate block_code برای رکوردهایی که null دارند
        $wpdb->query( "UPDATE {$pre}gis_parcels
                       SET block_code = SUBSTRING_INDEX(code, '-', 3)
                       WHERE block_code IS NULL AND code IS NOT NULL AND code LIKE '%-%-%-%'" );

        // پاک کردن جدول blocks
        $wpdb->query( "TRUNCATE TABLE {$pre}gis_blocks" );

        // بازسازی از parcels
        $inserted = $wpdb->query( "
            INSERT INTO {$pre}gis_blocks (block_code, center_lat, center_lng, parcel_count)
            SELECT
                block_code,
                AVG(lat),
                AVG(lng),
                COUNT(*)
            FROM {$pre}gis_parcels
            WHERE block_code IS NOT NULL
              AND lat IS NOT NULL
              AND lng IS NOT NULL
              AND status != 'archived'
            GROUP BY block_code
        " );

        // اگر spatial column موجود است، آن را هم پر کن
        $has_location = $wpdb->get_var( "SHOW COLUMNS FROM {$pre}gis_blocks LIKE 'location'" );
        if ( $has_location ) {
            $wpdb->query( "UPDATE {$pre}gis_blocks
                           SET location = ST_GeomFromText(CONCAT('POINT(', center_lng, ' ', center_lat, ')'))
                           WHERE center_lat IS NOT NULL AND center_lng IS NOT NULL" );
        }

        // پاک کردن همه cache های پارسل
        $wpdb->query( "DELETE FROM {$wpdb->options} WHERE option_name LIKE '_transient_acc_gis_block_parcels_%' OR option_name LIKE '_transient_timeout_acc_gis_block_parcels_%'" );
        self::_bump_dashboard_version(); // بازسازی بلوک‌ها → آمار داشبورد بی‌اعتبار

        return new WP_REST_Response([
            'success'      => true,
            'blocks_count' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$pre}gis_blocks" ),
            'inserted'     => (int) $inserted,
            'message'      => 'جدول بلوک‌ها بازسازی شد',
        ]);
    }

    public static function update_parcel( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $existing = $wpdb->get_row( $wpdb->prepare( "SELECT district_id FROM {$wpdb->prefix}gis_parcels WHERE id=%d", $id ), ARRAY_A );
        if ( $existing ) {
            $deny = acl_deny_scope( acl_current_user_id(), 'district', (int) ( $existing['district_id'] ?? 0 ) ?: null, 'gis.parcels.edit' );
            if ( $deny ) return $deny;
        }
        if ( $req->get_param('district_id') !== null ) {
            $deny = acl_deny_scope( acl_current_user_id(), 'district', (int) $req->get_param('district_id') ?: null, 'gis.parcels.edit' );
            if ( $deny ) return $deny;
        }
        $keys = ['district_id','neighborhood_id','assigned_user_id','assigned_user_name',
                 'code','sub_code','registry_section','address','lat','lng',
                 'geometry','status','pipeline_stage','notes','cpm_project_id'];
        $data = [];
        foreach ($keys as $k) {
            $v = $req->get_param($k);
            if ($v !== null) $data[$k] = in_array($k,['district_id','neighborhood_id','assigned_user_id','cpm_project_id']) ? (int)$v
                                        : (in_array($k,['lat','lng']) ? (float)$v : sanitize_textarea_field((string)$v));
        }

        // اگر code تغییر کرد، block_code را به‌روز کن
        if ( isset( $data['code'] ) ) {
            $data['block_code'] = self::_extract_block_code( $data['code'] );
        }

        // قبل از update، block_code قدیمی را برای invalidate نگه دار
        $old_block = $wpdb->get_var( $wpdb->prepare(
            "SELECT block_code FROM {$wpdb->prefix}gis_parcels WHERE id=%d", $id
        ) );

        $wpdb->update($wpdb->prefix.'gis_parcels', $data, ['id'=>$id]);

        self::_invalidate_block_cache( $old_block );
        if ( isset( $data['block_code'] ) && $data['block_code'] !== $old_block ) {
            self::_invalidate_block_cache( $data['block_code'] );
        }
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['success'=>true]);
    }

    public static function delete_parcel( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id  = (int)$req->get_param('id');
        $pre = $wpdb->prefix;

        $existing = $wpdb->get_row( $wpdb->prepare( "SELECT district_id FROM {$pre}gis_parcels WHERE id=%d", $id ), ARRAY_A );
        if ( $existing ) {
            $deny = acl_deny_scope( acl_current_user_id(), 'district', (int) ( $existing['district_id'] ?? 0 ) ?: null, 'gis.parcels.delete' );
            if ( $deny ) return $deny;
        }

        $old_block = $wpdb->get_var( $wpdb->prepare(
            "SELECT block_code FROM {$pre}gis_parcels WHERE id=%d", $id
        ) );

        // نرم حذف — فقط archived
        $wpdb->update("{$pre}gis_parcels", ['status'=>'archived'], ['id'=>$id]);
        self::_invalidate_block_cache( $old_block );
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['success'=>true]);
    }

    /* ══════════════════════════════════════════════════════════
       دفترهای پارسل
    ══════════════════════════════════════════════════════════ */
    public static function save_parcel_land( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $pre  = $wpdb->prefix;
        $data = [
            'parcel_id'           => $id,
            'area_sqm'            => (float)($req->get_param('area_sqm') ?: 0),
            'north_dim'           => $req->get_param('north_dim') ? (float)$req->get_param('north_dim') : null,
            'south_dim'           => $req->get_param('south_dim') ? (float)$req->get_param('south_dim') : null,
            'east_dim'            => $req->get_param('east_dim')  ? (float)$req->get_param('east_dim')  : null,
            'west_dim'            => $req->get_param('west_dim')  ? (float)$req->get_param('west_dim')  : null,
            'shape'               => sanitize_text_field($req->get_param('shape') ?: 'rectangular'),
            'position'            => sanitize_text_field($req->get_param('position') ?: 'middle'),
            'ownership_type'      => sanitize_text_field($req->get_param('ownership_type') ?: 'private'),
            'is_national_land'    => $req->get_param('is_national_land') ? 1 : 0,
            'main_street_width'   => $req->get_param('main_street_width')   ? (float)$req->get_param('main_street_width')   : null,
            'main_street_dir'     => sanitize_text_field($req->get_param('main_street_dir') ?: ''),
            'second_street_width' => $req->get_param('second_street_width') ? (float)$req->get_param('second_street_width') : null,
            'second_street_dir'   => sanitize_text_field($req->get_param('second_street_dir') ?: ''),
        ];
        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$pre}gis_parcel_land WHERE parcel_id=%d",$id));
        $exists ? $wpdb->update("{$pre}gis_parcel_land",$data,['parcel_id'=>$id])
                : $wpdb->insert("{$pre}gis_parcel_land",$data);
        // denormalize → gis_parcels.area_sqm (انبار دیتاوِرهاوس)
        $wpdb->update("{$pre}gis_parcels", ['area_sqm' => $data['area_sqm']], ['id'=>$id]);
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['success'=>true]);
    }

    public static function save_parcel_building( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $pre  = $wpdb->prefix;
        $bool = fn($k) => $req->get_param($k) ? 1 : 0;
        $data = [
            'parcel_id'      => $id,
            'has_building'   => $bool('has_building'),
            'floors'         => $req->get_param('floors') ? (int)$req->get_param('floors') : null,
            'units'          => $req->get_param('units')  ? (int)$req->get_param('units')  : null,
            'total_area_sqm' => $req->get_param('total_area_sqm') ? (float)$req->get_param('total_area_sqm') : null,
            'built_year'     => $req->get_param('built_year') ? (int)$req->get_param('built_year') : null,
            'structure_type' => sanitize_text_field($req->get_param('structure_type') ?: 'concrete'),
            'facade'         => sanitize_text_field($req->get_param('facade') ?: ''),
            'has_basement'   => $bool('has_basement'),
            'has_permit'     => sanitize_text_field($req->get_param('has_permit') ?: 'unknown'),
            'permit_number'  => sanitize_text_field($req->get_param('permit_number') ?: ''),
            'has_completion' => sanitize_text_field($req->get_param('has_completion') ?: 'no'),
            'has_water'      => $bool('has_water'),
            'has_electricity'=> $bool('has_electricity'),
            'has_gas'        => $bool('has_gas'),
            'has_elevator'   => $bool('has_elevator'),
            'has_parking'    => $bool('has_parking'),
            'has_storage'    => $bool('has_storage'),
        ];
        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$pre}gis_parcel_building WHERE parcel_id=%d",$id));
        $exists ? $wpdb->update("{$pre}gis_parcel_building",$data,['parcel_id'=>$id])
                : $wpdb->insert("{$pre}gis_parcel_building",$data);
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['success'=>true]);
    }

    public static function save_parcel_use( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $pre  = $wpdb->prefix;
        $area = (float)($req->get_param('area_sqm') ?: 0);
        $dens = (float)($req->get_param('density_pct') ?: 0);
        $data = [
            'parcel_id'       => $id,
            'current_use'     => sanitize_text_field($req->get_param('current_use')  ?: ''),
            'approved_use'    => sanitize_text_field($req->get_param('approved_use') ?: ''),
            'density_pct'     => $dens ?: null,
            'coverage_pct'    => $req->get_param('coverage_pct') ? (float)$req->get_param('coverage_pct') : null,
            'max_floors'      => $req->get_param('max_floors') ? (int)$req->get_param('max_floors') : null,
            'max_build_area'  => ($area > 0 && $dens > 0) ? round($area * $dens / 100, 2) : null,
        ];
        $exists = $wpdb->get_var($wpdb->prepare("SELECT id FROM {$pre}gis_parcel_use WHERE parcel_id=%d",$id));
        $exists ? $wpdb->update("{$pre}gis_parcel_use",$data,['parcel_id'=>$id])
                : $wpdb->insert("{$pre}gis_parcel_use",$data);
        // denormalize → gis_parcels.current_use (انبار دیتاوِرهاوس)
        $wpdb->update("{$pre}gis_parcels", ['current_use' => $data['current_use']], ['id'=>$id]);
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['success'=>true]);
    }

    /* مالکین */
    public static function get_parcel_owners( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}gis_parcel_owners WHERE parcel_id=%d ORDER BY is_primary DESC",$id
        ), ARRAY_A);
        return new WP_REST_Response(['data'=>$rows]);
    }

    public static function save_parcel_owner( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id  = (int)$req->get_param('id');
        $pre = $wpdb->prefix;
        $oid = $req->get_param('owner_id') ? (int)$req->get_param('owner_id') : 0;
        $data = [
            'parcel_id'       => $id,
            'name'            => sanitize_text_field($req->get_param('name') ?: ''),
            'national_id'     => sanitize_text_field($req->get_param('national_id') ?: ''),
            'ownership_share' => sanitize_text_field($req->get_param('ownership_share') ?: '6/6'),
            'ownership_type'  => sanitize_text_field($req->get_param('ownership_type') ?: 'full'),
            'deed_type'       => sanitize_text_field($req->get_param('deed_type') ?: 'single_page'),
            'deed_number'     => sanitize_text_field($req->get_param('deed_number') ?: ''),
            'registry_office' => sanitize_text_field($req->get_param('registry_office') ?: ''),
            'deed_date'       => sanitize_text_field($req->get_param('deed_date') ?: ''),
            'phone'           => sanitize_text_field($req->get_param('phone') ?: ''),
            'is_primary'      => $req->get_param('is_primary') ? 1 : 0,
        ];
        if (!$data['name']) return new WP_REST_Response(['message'=>'نام مالک الزامی است'],400);
        if ($oid) { $wpdb->update("{$pre}gis_parcel_owners",$data,['id'=>$oid,'parcel_id'=>$id]); }
        else      { $wpdb->insert("{$pre}gis_parcel_owners",$data); $oid = $wpdb->insert_id; }
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['id'=>$oid,'success'=>true]);
    }

    public static function delete_parcel_owner( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pid = (int)$req->get_param('id');
        $wpdb->delete($wpdb->prefix.'gis_parcel_owners',[
            'id'=>(int)$req->get_param('owner_id'),
            'parcel_id'=>$pid,
        ]);
        self::_invalidate_parcel_cache( $pid );
        return new WP_REST_Response(['success'=>true]);
    }

    /* تاریخچه مالکیت */
    public static function get_ownership_history( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}gis_ownership_history WHERE parcel_id=%d ORDER BY transfer_date DESC",$id
        ), ARRAY_A);
        return new WP_REST_Response(['data'=>$rows]);
    }

    public static function add_ownership_history( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $data = [
            'parcel_id'      => $id,
            'owner_name'     => sanitize_text_field($req->get_param('owner_name') ?: ''),
            'transfer_type'  => sanitize_text_field($req->get_param('transfer_type') ?: 'sale'),
            'transfer_date'  => sanitize_text_field($req->get_param('transfer_date') ?: ''),
            'transfer_price' => sanitize_text_field($req->get_param('transfer_price') ?: '') ?: null,
            'notes'          => sanitize_textarea_field($req->get_param('notes') ?: ''),
        ];
        if (!$data['owner_name']) return new WP_REST_Response(['message'=>'نام مالک الزامی است'],400);
        $wpdb->insert($wpdb->prefix.'gis_ownership_history',$data);
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['id'=>$wpdb->insert_id,'success'=>true],201);
    }

    /* مشکلات */
    public static function get_parcel_issues( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}gis_parcel_issues WHERE parcel_id=%d ORDER BY severity DESC,created_at DESC",$id
        ),ARRAY_A);
        return new WP_REST_Response(['data'=>$rows]);
    }

    public static function save_parcel_issue( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $iid  = $req->get_param('issue_id') ? (int)$req->get_param('issue_id') : 0;
        $pre  = $wpdb->prefix;
        $data = [
            'parcel_id'   => $id,
            'type'        => sanitize_text_field($req->get_param('type') ?: 'other'),
            'severity'    => sanitize_text_field($req->get_param('severity') ?: 'warning'),
            'title'       => sanitize_text_field($req->get_param('title') ?: ''),
            'description' => sanitize_textarea_field($req->get_param('description') ?: ''),
            'amount'      => $req->get_param('amount') ? (float)$req->get_param('amount') : null,
            'status'      => sanitize_text_field($req->get_param('status') ?: 'active'),
            'resolved_at' => sanitize_text_field($req->get_param('resolved_at') ?: ''),
        ];
        if ($iid) { $wpdb->update("{$pre}gis_parcel_issues",$data,['id'=>$iid,'parcel_id'=>$id]); }
        else      { $wpdb->insert("{$pre}gis_parcel_issues",$data); $iid=$wpdb->insert_id; }
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['id'=>$iid,'success'=>true]);
    }

    public static function delete_parcel_issue( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pid = (int)$req->get_param('id');
        $wpdb->delete($wpdb->prefix.'gis_parcel_issues',[
            'id'=>(int)$req->get_param('issue_id'), 'parcel_id'=>$pid,
        ]);
        self::_invalidate_parcel_cache( $pid );
        return new WP_REST_Response(['success'=>true]);
    }

    /* کمیسیون ماده ۱۰۰ */
    public static function get_commissions( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}gis_commissions WHERE parcel_id=%d ORDER BY year DESC",$id
        ),ARRAY_A);
        return new WP_REST_Response(['data'=>$rows]);
    }

    public static function save_commission( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $cid  = $req->get_param('c_id') ? (int)$req->get_param('c_id') : 0;
        $pre  = $wpdb->prefix;
        $data = [
            'parcel_id'      => $id,
            'year'           => (int)($req->get_param('year') ?: date('Y')),
            'violation_type' => sanitize_text_field($req->get_param('violation_type') ?: 'other'),
            'description'    => sanitize_textarea_field($req->get_param('description') ?: ''),
            'verdict'        => sanitize_text_field($req->get_param('verdict') ?: 'fine'),
            'fine_amount'    => $req->get_param('fine_amount') ? (float)$req->get_param('fine_amount') : null,
            'verdict_status' => sanitize_text_field($req->get_param('verdict_status') ?: 'pending'),
            'case_number'    => sanitize_text_field($req->get_param('case_number') ?: ''),
        ];
        if ($cid) { $wpdb->update("{$pre}gis_commissions",$data,['id'=>$cid,'parcel_id'=>$id]); }
        else      { $wpdb->insert("{$pre}gis_commissions",$data); $cid=$wpdb->insert_id; }
        self::_invalidate_parcel_cache( $id );
        return new WP_REST_Response(['id'=>$cid,'success'=>true]);
    }

    public static function delete_ownership_history( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pid = (int) $req->get_param('id');
        $wpdb->delete( $wpdb->prefix . 'gis_ownership_history', [
            'id'        => (int) $req->get_param('hid'),
            'parcel_id' => $pid,
        ] );
        self::_invalidate_parcel_cache( $pid );
        return new WP_REST_Response( ['success' => true] );
    }

    public static function delete_commission( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pid = (int)$req->get_param('id');
        $wpdb->delete($wpdb->prefix.'gis_commissions',[
            'id'=>(int)$req->get_param('c_id'), 'parcel_id'=>$pid,
        ]);
        self::_invalidate_parcel_cache( $pid );
        return new WP_REST_Response(['success'=>true]);
    }

    /* ══════════════════════════════════════════════════════════
       قفل تیمی — جلوگیری از تداخل
    ══════════════════════════════════════════════════════════ */
    public static function lock_parcel( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id   = (int)$req->get_param('id');
        $pre  = $wpdb->prefix;
        $row  = $wpdb->get_row($wpdb->prepare("SELECT locked_by_user_id, locked_at FROM {$pre}gis_parcels WHERE id=%d",$id), ARRAY_A);
        if (!$row) return new WP_REST_Response(['message'=>'پارسل یافت نشد'],404);

        // اگر قبلاً قفل شده (۲۴ ساعت اعتبار)
        if ($row['locked_by_user_id'] && $row['locked_at']) {
            $age = time() - strtotime($row['locked_at']);
            if ($age < 86400) {
                $uid = (int)$req->get_param('user_id');
                if ($row['locked_by_user_id'] !== $uid) {
                    return new WP_REST_Response(['message'=>'این پارسل توسط '.$row['locked_by_user_id'].' قفل شده است','locked'=>true],409);
                }
            }
        }

        $wpdb->update("{$pre}gis_parcels",[
            'locked_by_user_id' => (int)$req->get_param('user_id'),
            'locked_by_name'    => sanitize_text_field($req->get_param('user_name') ?: ''),
            'locked_at'         => current_time('mysql'),
        ],['id'=>$id]);
        return new WP_REST_Response(['success'=>true]);
    }

    public static function unlock_parcel( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id = (int)$req->get_param('id');
        $wpdb->update($wpdb->prefix.'gis_parcels',[
            'locked_by_user_id'=>null,'locked_by_name'=>null,'locked_at'=>null,
        ],['id'=>$id]);
        return new WP_REST_Response(['success'=>true]);
    }

    /* ══════════════════════════════════════════════════════════
       اتصال به CPM — ایجاد پروژه از روی پارسل
    ══════════════════════════════════════════════════════════ */
    public static function create_project_for_parcel( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $id  = (int)$req->get_param('id');
        $pre = $wpdb->prefix;

        $parcel = $wpdb->get_row($wpdb->prepare(
            "SELECT id, code, sub_code, address, cpm_project_id FROM {$pre}gis_parcels WHERE id=%d", $id
        ), ARRAY_A);

        if ( ! $parcel ) {
            return new WP_REST_Response(['message'=>'پارسل یافت نشد'], 404);
        }

        // اگر قبلاً پروژه دارد
        if ( ! empty($parcel['cpm_project_id']) ) {
            return new WP_REST_Response([
                'message'    => 'این پارسل قبلاً دارای پروژه است',
                'project_id' => (int)$parcel['cpm_project_id'],
            ], 409);
        }

        $label = ($parcel['code'] ? 'پلاک '.$parcel['code'] : 'پارسل #'.$id);
        $name  = sanitize_text_field($req->get_param('name') ?: 'پروژه '.$label);
        $data  = [
            'code'         => sanitize_text_field($req->get_param('code') ?: ''),
            'name'         => $name,
            'description'  => sanitize_textarea_field($req->get_param('description') ?: ($parcel['address'] ?: '')),
            'type'         => 'construction',
            'status'       => 'planned',
            'start_date'   => sanitize_text_field($req->get_param('start_date') ?: date('Y-m-d')),
            'end_date'     => sanitize_text_field($req->get_param('end_date') ?: ''),
            'budget'       => $req->get_param('budget') ? (float)$req->get_param('budget') : 0,
            'manager_name' => sanitize_text_field($req->get_param('manager_name') ?: ''),
            'currency'     => 'IRR',
            'progress'     => 0.0,
        ];
        // حذف end_date خالی
        if ( ! $data['end_date'] ) unset($data['end_date']);

        $wpdb->insert("{$pre}pc_projects", $data);
        $project_id = (int)$wpdb->insert_id;

        if ( ! $project_id ) {
            return new WP_REST_Response(['message'=>'خطا در ایجاد پروژه'], 500);
        }

        // ثبت لینک در پارسل
        $wpdb->update("{$pre}gis_parcels", ['cpm_project_id'=>$project_id], ['id'=>$id]);

        return new WP_REST_Response(['id'=>$project_id, 'success'=>true], 201);
    }

    /* ══════════════════════════════════════════════════════════
       کارنامه — محاسبه امتیاز خودکار
    ══════════════════════════════════════════════════════════ */
    private static function _calc_score( array $p ): array {
        $score    = 100;
        $warnings = [];

        // مشکلات بحرانی: هر کدام -25
        $critical = array_filter($p['issues'] ?? [], fn($i) => $i['severity']==='critical' && $i['status']==='active');
        foreach ($critical as $c) {
            $score -= 25;
            $warnings[] = ['level'=>'critical','text'=>$c['title']];
        }

        // مشکلات هشداری: هر کدام -10
        $warn = array_filter($p['issues'] ?? [], fn($i) => $i['severity']==='warning' && $i['status']==='active');
        foreach ($warn as $w) {
            $score -= 10;
            $warnings[] = ['level'=>'warning','text'=>$w['title']];
        }

        // کمیسیون ماده ۱۰۰ اجرا نشده: -15
        $nc = array_filter($p['commissions'] ?? [], fn($c) => $c['verdict_status']==='not_executed');
        foreach ($nc as $c) {
            $score -= 15;
            $warnings[] = ['level'=>'critical','text'=>'رأی کمیسیون ماده ۱۰۰ اجرا نشده'];
        }

        // اراضی ملی: -50
        if (!empty($p['land']['is_national_land'])) {
            $score -= 50;
            $warnings[] = ['level'=>'critical','text'=>'اراضی ملی'];
        }

        // سند قولنامه‌ای: -15
        $qol = array_filter($p['owners'] ?? [], fn($o) => $o['deed_type']==='qolnameh');
        if ($qol) {
            $score -= 15;
            $warnings[] = ['level'=>'warning','text'=>'سند قولنامه‌ای'];
        }

        $score = max(0, $score);
        $level = $score >= 80 ? 'good' : ($score >= 50 ? 'moderate' : 'risky');
        return ['score'=>$score,'level'=>$level,'warnings'=>array_values($warnings)];
    }

    /* ══════════════════════════════════════════════════════════
       کلیک روی نقشه — نزدیک‌ترین بلوک (بهینه‌سازی شده)
       — استفاده از spatial index اگر موجود باشد
       — استفاده از block_code (بدون LIKE)
       — transient cache برای کاهش query تکراری
    ══════════════════════════════════════════════════════════ */
    public static function get_parcels_by_click( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;

        $lat = (float) $req->get_param('lat');
        $lng = (float) $req->get_param('lng');

        if ( ! $lat || ! $lng ) {
            return new WP_REST_Response(['message' => 'lat و lng الزامی است'], 400);
        }

        $layer_id = sanitize_text_field( $req->get_param('layer_id') ?? '' );

        $block_row = self::_find_nearest_block( $lat, $lng );

        if ( ! $block_row ) {
            return new WP_REST_Response(['parcels' => [], 'block' => null, 'total' => 0]);
        }

        $block = $block_row['block_code'];
        $deny  = acl_deny_block_code( acl_current_user_id(), $block, 'gis.parcels.view' );
        if ( $deny ) return new WP_REST_Response( [ 'parcels' => [], 'block' => null, 'total' => 0, 'denied' => true ] );

        // ── cache پارسل‌های بلوک برای ۵ دقیقه ──
        // در cache_key لایه را هم بگنجان تا cache هر لایه جدا باشد
        $cache_key = 'acc_gis_block_parcels_' . md5( $block . '|' . $layer_id );
        $parcels   = get_transient( $cache_key );

        if ( $parcels === false ) {
            $extra_where = '';
            $extra_args  = [ $block ];
            if ( $layer_id ) {
                $extra_where = ' AND p.LayerID = %s';
                $extra_args[] = $layer_id;
            }

            // استفاده از block_code (بدون LIKE) — index-friendly
            $parcels = $wpdb->get_results( $wpdb->prepare(
                "SELECT p.id, p.lat, p.lng, p.geometry, p.status, p.pipeline_stage,
                        p.code, p.LayerID, p.address, p.district_id, p.neighborhood_id,
                        p.assigned_user_name, p.locked_by_name, p.locked_at,
                        d.name AS district_name, n.name AS neighborhood_name
                 FROM {$pre}gis_parcels p
                 LEFT JOIN {$pre}gis_districts d ON d.id = p.district_id
                 LEFT JOIN {$pre}gis_neighborhoods n ON n.id = p.neighborhood_id
                 WHERE p.block_code = %s
                   AND p.status != 'archived'
                   {$extra_where}
                 ORDER BY p.code ASC",
                ...$extra_args
            ), ARRAY_A );

            set_transient( $cache_key, $parcels ?? [], 5 * MINUTE_IN_SECONDS );
        }

        return new WP_REST_Response([
            'block'        => $block,
            'center_lat'   => (float) $block_row['center_lat'],
            'center_lng'   => (float) $block_row['center_lng'],
            'parcel_count' => (int)   $block_row['parcel_count'],
            'parcels'      => $parcels ?? [],
            'total'        => count($parcels ?? []),
            'cached'       => $parcels !== false,
        ]);
    }

    /**
     * پیدا کردن نزدیک‌ترین بلوک — اول spatial، سپس bounding box
     */
    private static function _find_nearest_block( float $lat, float $lng ): ?array {
        global $wpdb;
        $pre = $wpdb->prefix;

        // (الف) اگر spatial index داریم → ST_Distance_Sphere
        $has_spatial = $wpdb->get_var(
            "SHOW INDEX FROM {$pre}gis_blocks WHERE Key_name = 'idx_blocks_spatial'"
        );

        if ( $has_spatial ) {
            $row = $wpdb->get_row( $wpdb->prepare(
                "SELECT block_code, center_lat, center_lng, parcel_count,
                        ST_Distance_Sphere(location, ST_GeomFromText(%s)) AS dist
                 FROM {$pre}gis_blocks
                 ORDER BY dist ASC
                 LIMIT 1",
                "POINT({$lng} {$lat})"
            ), ARRAY_A );
            if ( $row ) return $row;
        }

        // (ب) Fallback: bounding box با index lat/lng
        $box = 0.05; // ≈ ۵ کیلومتر
        return $wpdb->get_row( $wpdb->prepare(
            "SELECT block_code, center_lat, center_lng, parcel_count,
                    ( (center_lat - %f)*(center_lat - %f) + (center_lng - %f)*(center_lng - %f) ) AS dist
             FROM {$pre}gis_blocks
             WHERE center_lat BETWEEN %f AND %f
               AND center_lng BETWEEN %f AND %f
             ORDER BY dist ASC
             LIMIT 1",
            $lat, $lat, $lng, $lng,
            $lat - $box, $lat + $box,
            $lng - $box, $lng + $box
        ), ARRAY_A );
    }

    /* ═══════════════════════════════════════════════════════════
       کد نوسازی واحدها (gis_parcel_units)
    ═══════════════════════════════════════════════════════════ */

    public static function get_parcel_units( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $rows = $wpdb->get_results( $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}gis_parcel_units WHERE parcel_id=%d ORDER BY id ASC",
            (int) $req['id']
        ), ARRAY_A );
        return new WP_REST_Response(['data' => $rows ?: []]);
    }

    public static function add_parcel_unit( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $code = sanitize_text_field( $req->get_param('renovation_code') ?? '' );
        if ( ! $code ) return new WP_REST_Response(['message'=>'کد نوسازی الزامی است'], 400);
        $wpdb->insert( $wpdb->prefix.'gis_parcel_units', [
            'parcel_id'       => (int) $req['id'],
            'renovation_code' => $code,
        ]);
        return new WP_REST_Response(['success'=>true,'id'=>$wpdb->insert_id], 201);
    }

    public static function delete_parcel_unit( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $wpdb->delete( $wpdb->prefix.'gis_parcel_units', ['id'=>(int)$req['uid']] );
        return new WP_REST_Response(['success'=>true]);
    }

    /* ═══════════════════════════════════════════════════════════
       نصب جدول gis_parcel_units
    ═══════════════════════════════════════════════════════════ */

    public static function install_parcel_units_table(): WP_REST_Response {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();
        dbDelta("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}gis_parcel_units (
            id               INT NOT NULL AUTO_INCREMENT,
            parcel_id        INT NOT NULL,
            renovation_code  VARCHAR(100) NOT NULL,
            created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_parcel (parcel_id),
            KEY idx_code   (renovation_code)
        ) {$charset};");
        return new WP_REST_Response(['success'=>true,'message'=>'جدول ساخته شد']);
    }

    /* ═══════════════════════════════════════════════════════════
       ایمپورت JSON پارسل‌ها
    ═══════════════════════════════════════════════════════════ */

    public static function import_parcels( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;

        $file_path   = sanitize_text_field( $req->get_param('file_path') ?? '' );
        $district_id = $req->get_param('district_id') ? (int)$req->get_param('district_id') : null;
        $skip_dup    = (bool)($req->get_param('skip_duplicates') ?? true);

        // ── اعتبارسنجی فایل ──────────────────────────────────
        if ( ! $file_path ) return new WP_REST_Response(['message'=>'مسیر فایل الزامی است'], 400);
        if ( ! file_exists($file_path) ) return new WP_REST_Response(['message'=>'فایل یافت نشد: '.$file_path], 404);

        $json = file_get_contents($file_path);
        $data = json_decode($json, true);
        if ( ! is_array($data) ) return new WP_REST_Response(['message'=>'فرمت JSON نامعتبر است'], 400);

        // ── اطمینان از وجود جدول ──────────────────────────────
        self::install_parcel_units_table();

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

        foreach ( $data as $i => $row ) {
            $geom_wkt = trim($row['Geom'] ?? '');
            $code     = trim($row['Code'] ?? $row['Name'] ?? '');
            $parsel   = trim($row['parsel'] ?? '');

            if ( ! $geom_wkt ) { $errors[] = "ردیف {$i}: geometry خالی"; continue; }

            // تبدیل WKT → GeoJSON + محاسبه مرکز ثقل
            $geo_result = self::wkt_to_geojson_centroid($geom_wkt);
            if ( ! $geo_result ) { $errors[] = "ردیف {$i}: WKT نامعتبر"; continue; }
            [$geojson, $lat, $lng] = $geo_result;

            // بررسی تکراری
            if ( $code && $skip_dup ) {
                $exists = $wpdb->get_var($wpdb->prepare(
                    "SELECT id FROM {$wpdb->prefix}gis_parcels WHERE code=%s LIMIT 1", $code
                ));
                if ( $exists ) { $skipped++; continue; }
            }

            // درج پارسل
            $wpdb->insert($wpdb->prefix.'gis_parcels', [
                'district_id'  => $district_id,
                'code'         => $code ?: null,
                'block_code'   => self::_extract_block_code($code),
                'lat'          => round($lat, 8),
                'lng'          => round($lng, 8),
                'geometry'     => $geojson,
                'status'       => 'prospect',
                'pipeline_stage' => 'discovery',
            ]);
            $parcel_id = $wpdb->insert_id;

            if ( ! $parcel_id ) { $errors[] = "ردیف {$i}: خطا در درج"; continue; }
            $inserted++;

            // درج کدهای نوسازی
            if ( $parsel ) {
                $codes = array_filter(array_map('trim', explode(',', $parsel)));
                foreach ( $codes as $rcode ) {
                    $wpdb->insert($wpdb->prefix.'gis_parcel_units', [
                        'parcel_id'      => $parcel_id,
                        'renovation_code'=> $rcode,
                    ]);
                }
            }
        }

        if ( $inserted > 0 ) {
            self::rebuild_block_stats(); // انبار دیتاوِرهاوس را بازساز (شامل bump نسخه‌ی کش)
        }

        return new WP_REST_Response([
            'success'  => true,
            'inserted' => $inserted,
            'skipped'  => $skipped,
            'errors'   => $errors,
            'total'    => count($data),
        ], 200);
    }

    /* ── تبدیل WKT POLYGON به GeoJSON + مرکز ثقل ────────────── */
    private static function wkt_to_geojson_centroid( string $wkt ): ?array {
        // POLYGON ((lng lat, lng lat, ...))
        if ( ! preg_match('/POLYGON\s*\(\((.+)\)\)/i', $wkt, $m) ) return null;

        $pairs = array_map('trim', explode(',', $m[1]));
        $coords = [];
        foreach ( $pairs as $pair ) {
            $parts = preg_split('/\s+/', trim($pair));
            if ( count($parts) < 2 ) return null;
            $coords[] = [(float)$parts[0], (float)$parts[1]]; // [lng, lat]
        }
        if ( count($coords) < 3 ) return null;

        // مرکز ثقل ساده
        $lng_sum = array_sum(array_column($coords, 0));
        $lat_sum = array_sum(array_column($coords, 1));
        $n       = count($coords);
        $lat     = $lat_sum / $n;
        $lng     = $lng_sum / $n;

        $geojson = wp_json_encode(['type'=>'Polygon','coordinates'=>[$coords]]);
        return [$geojson, $lat, $lng];
    }

    /* ══════════════════════════════════════════════════════════
       داشبورد املاک — آمار به تفکیک محله
    ══════════════════════════════════════════════════════════ */
    public static function get_dashboard( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;

        /* ── فیلترها ── */
        $district_id     = $req->get_param('district_id')     ? (int)$req->get_param('district_id')     : null;
        $neighborhood_id = $req->get_param('neighborhood_id') ? (int)$req->get_param('neighborhood_id') : null;
        $statuses        = $req->get_param('statuses')        ? array_map('sanitize_text_field', explode(',', $req->get_param('statuses'))) : [];
        $land_use        = $req->get_param('land_use')        ? sanitize_text_field($req->get_param('land_use')) : '';
        $area_min        = $req->get_param('area_min')        ? (float)$req->get_param('area_min')        : null;
        $area_max        = $req->get_param('area_max')        ? (float)$req->get_param('area_max')        : null;

        /* ── تعریف «نو ملک»: ساختمان | آپارتمان | مغازه ── */
        $def_building = $req->get_param('def_building') !== null ? (int)$req->get_param('def_building') : -1; // -1 = بدون فیلتر
        $def_units    = $req->get_param('def_units')    !== null ? (int)$req->get_param('def_units')    : -1;
        $def_shops    = $req->get_param('def_shops')    !== null ? (int)$req->get_param('def_shops')    : -1;

        /* ── ساخت WHERE ── */
        $wheres = ["p.status != 'archived'"];
        $args   = [];
        $uid    = acl_current_user_id();

        if ($district_id) {
            $deny = acl_deny_scope( $uid, 'district', $district_id, 'gis.view' );
            if ( $deny ) return $deny;
            $wheres[] = 'p.district_id = %d'; $args[] = $district_id;
        }
        if ($neighborhood_id) { $wheres[] = 'p.neighborhood_id = %d'; $args[] = $neighborhood_id; }
        if ( ! acl_append_scope_where( $uid, 'district', 'p.district_id', $wheres, $args ) ) {
            return new WP_REST_Response( [ 'data' => [], 'summary' => [], 'cached' => false ] );
        }
        if ($statuses)        { $placeholders = implode(',', array_fill(0, count($statuses), '%s')); $wheres[] = "p.status IN ({$placeholders})"; foreach ($statuses as $s) $args[] = $s; }
        if ($land_use)        { $wheres[] = 'pu.current_use = %s';    $args[] = $land_use; }
        if ($area_min)        { $wheres[] = 'pl.area_sqm >= %f';      $args[] = $area_min; }
        if ($area_max)        { $wheres[] = 'pl.area_sqm <= %f';      $args[] = $area_max; }

        /* فیلتر نو ملک — از ستون‌های سریع gis_parcels */
        if ( $def_building === 0 && $def_units === 0 && $def_shops === 0 ) {
            /* زمین تنها */
            $wheres[] = 'p.bld_count = 0 AND p.apt_count = 0 AND p.shop_count = 0';
        } else {
            if ( $def_building > 0 ) { $wheres[] = 'p.bld_count  >= %d'; $args[] = $def_building; }
            if ( $def_units    > 0 ) { $wheres[] = 'p.apt_count  >= %d'; $args[] = $def_units; }
            if ( $def_shops    > 0 ) { $wheres[] = 'p.shop_count >= %d'; $args[] = $def_shops; }
        }

        $where_sql = 'WHERE ' . implode(' AND ', $wheres);

        /* ── (الف) Cache: کلید = هش پارامترها + نسخه‌ی سراسری ──
           هر تغییر پارسل نسخه را +۱ می‌کند (نسخه‌بندی سراسری) و همه‌ی
           نسخه‌های کش‌شده‌ی داشبورد به‌صورت خودکار بی‌اعتبار می‌شوند. */
        $cache_ver = (int) get_option( 'acc_gis_dashboard_ver', 1 );
        $cache_key = 'acc_gis_dashboard_' . $cache_ver . '_' . md5( $where_sql . '|' . wp_json_encode( $args ) );
        $cached    = acl_is_scope_unrestricted( $uid, 'district' ) ? get_transient( $cache_key ) : false;
        if ( $cached !== false ) {
            return new WP_REST_Response( $cached + ['cached' => true] );
        }

        /* ── (ب) JOINهای شرطی ──
           pl (parcel_land) فقط برای avg/total_area یا فیلتر مساحت لازم است.
           pu (parcel_use)  فقط وقتی فیلتر land_use فعال است لازم است. */
        $need_land = true; // همیشه برای avg_area/total_area استفاده می‌شود
        $need_use  = ($land_use !== '');

        $join_land = $need_land ? "LEFT JOIN {$pre}gis_parcel_land pl ON pl.parcel_id = p.id" : '';
        $join_use  = $need_use  ? "LEFT JOIN {$pre}gis_parcel_use  pu ON pu.parcel_id = p.id" : '';
        $area_avg  = $need_land ? 'AVG(pl.area_sqm)' : 'NULL';
        $area_sum  = $need_land ? 'SUM(pl.area_sqm)' : 'NULL';

        /* ── آمار به تفکیک محله — GROUP BY فقط روی ID‌ها (نه رشته‌ها) ──
           نام محله/منطقه با MAX() برگردانده می‌شود تا از filesort روی VARCHAR
           پرهیز شود؛ مقدار نام در هر گروه یکتاست. */
        $sql = "
            SELECT
                p.neighborhood_id,
                MAX(n.name)                      AS neighborhood_name,
                p.district_id,
                MAX(d.name)                      AS district_name,
                COUNT(*)                         AS total_parcels,
                SUM(p.bld_count  > 0)            AS cnt_building,
                SUM(p.apt_count  > 0)            AS cnt_apartment,
                SUM(p.shop_count > 0)            AS cnt_shop,
                SUM(p.bld_count = 0 AND p.apt_count = 0 AND p.shop_count = 0) AS cnt_land_only,
                {$area_avg}                      AS avg_area,
                {$area_sum}                      AS total_area,
                SUM(p.status = 'active')         AS status_active,
                SUM(p.status = 'prospect')       AS status_prospect,
                SUM(p.status = 'completed')      AS status_completed,
                SUM(p.status = 'suspended')      AS status_suspended
            FROM {$pre}gis_parcels p
            LEFT JOIN {$pre}gis_neighborhoods n ON n.id = p.neighborhood_id
            LEFT JOIN {$pre}gis_districts d     ON d.id = p.district_id
            {$join_land}
            {$join_use}
            {$where_sql}
            GROUP BY p.neighborhood_id, p.district_id
            ORDER BY p.district_id, total_parcels DESC";

        $rows = $args
            ? $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A)
            : $wpdb->get_results($sql, ARRAY_A);

        /* ── خلاصه کلی ── */
        $total_sql = "
            SELECT
                COUNT(*)              AS total_parcels,
                SUM(p.bld_count  > 0) AS cnt_building,
                SUM(p.apt_count  > 0) AS cnt_apartment,
                SUM(p.shop_count > 0) AS cnt_shop,
                {$area_avg}           AS avg_area,
                {$area_sum}           AS total_area
            FROM {$pre}gis_parcels p
            {$join_land}
            {$join_use}
            {$where_sql}";

        $summary = $args
            ? $wpdb->get_row($wpdb->prepare($total_sql, ...$args), ARRAY_A)
            : $wpdb->get_row($total_sql, ARRAY_A);

        $payload = [
            'data'    => $rows    ?? [],
            'summary' => $summary ?? [],
        ];

        /* ── (ج) ذخیره در کش — TTL طولانی چون نسخه‌بندی باطل‌سازی را تضمین می‌کند ── */
        set_transient( $cache_key, $payload, 30 * MINUTE_IN_SECONDS );

        return new WP_REST_Response( $payload + ['cached' => false] );
    }

    /**
     * بالا بردن نسخه‌ی کش داشبورد (نسخه‌بندی سراسری).
     * با هر تغییر پارسل صدا زده می‌شود تا همه‌ی نسخه‌های کش‌شده‌ی
     * داشبورد یک‌جا بی‌اعتبار شوند — بدون نیاز به DELETE روی wp_options.
     */
    private static function _bump_dashboard_version(): void {
        $ver = (int) get_option( 'acc_gis_dashboard_ver', 1 );
        update_option( 'acc_gis_dashboard_ver', $ver + 1, false );
    }

    /* ══════════════════════════════════════════════════════════
       DATA WAREHOUSE — بازسازی انبار آمار بلوک‌ها
       (denormalize ستون‌ها + پر کردن gis_block_stats)
       با cron شبانه یا دستی فراخوانی می‌شود.
    ══════════════════════════════════════════════════════════ */
    public static function rebuild_block_stats(): array {
        global $wpdb;
        $pre = $wpdb->prefix;
        @set_time_limit( 600 );

        // (0) اطمینان از وجود ستون‌های denormalize (در نصبِ قدیمی)
        $cols = array_column( $wpdb->get_results( "SHOW COLUMNS FROM {$pre}gis_parcels" ), 'Field' );
        if ( ! in_array('area_sqm', $cols, true) ) {
            $wpdb->query( "ALTER TABLE {$pre}gis_parcels ADD COLUMN area_sqm DECIMAL(12,2) NULL DEFAULT NULL" );
        }
        if ( ! in_array('current_use', $cols, true) ) {
            $wpdb->query( "ALTER TABLE {$pre}gis_parcels ADD COLUMN current_use VARCHAR(100) NULL DEFAULT NULL" );
        }

        // (1) denormalize: area_sqm و current_use را داخل gis_parcels بریز
        $wpdb->query(
            "UPDATE {$pre}gis_parcels p
             LEFT JOIN {$pre}gis_parcel_land pl ON pl.parcel_id = p.id
             SET p.area_sqm = pl.area_sqm"
        );
        $wpdb->query(
            "UPDATE {$pre}gis_parcels p
             LEFT JOIN {$pre}gis_parcel_use pu ON pu.parcel_id = p.id
             SET p.current_use = pu.current_use"
        );

        // (2) populate block_code برای ردیف‌های ناقص (احتیاطی)
        $wpdb->query(
            "UPDATE {$pre}gis_parcels
             SET block_code = SUBSTRING_INDEX(code, '-', 3)
             WHERE block_code IS NULL AND code LIKE '%-%-%-%'"
        );

        // (3) بازسازی جدول خلاصه از صفر
        $wpdb->query( "TRUNCATE TABLE {$pre}gis_block_stats" );
        $inserted = $wpdb->query(
            "INSERT INTO {$pre}gis_block_stats
                (block_code, district_id, neighborhood_id, layer_id,
                 total_parcels, cnt_building, cnt_apartment, cnt_shop, cnt_land_only,
                 status_active, status_prospect, status_completed, status_suspended,
                 center_lat, center_lng, updated_at)
             SELECT
                p.block_code,
                MAX(p.district_id),
                MAX(p.neighborhood_id),
                MAX(p.LayerID),
                COUNT(*),
                SUM(p.bld_count  > 0),
                SUM(p.apt_count  > 0),
                SUM(p.shop_count > 0),
                SUM(p.bld_count = 0 AND p.apt_count = 0 AND p.shop_count = 0),
                SUM(p.status = 'active'),
                SUM(p.status = 'prospect'),
                SUM(p.status = 'completed'),
                SUM(p.status = 'suspended'),
                AVG(p.lat),
                AVG(p.lng),
                NOW()
             FROM {$pre}gis_parcels p
             WHERE p.block_code IS NOT NULL
               AND p.status <> 'archived'
               AND p.district_id BETWEEN 1 AND 12
             GROUP BY p.block_code"
        );

        // (4) انبار تازه شد → کش داشبورد را باطل کن
        self::_bump_dashboard_version();

        return [
            'success'       => true,
            'blocks'        => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$pre}gis_block_stats" ),
            'inserted'      => (int) $inserted,
            'rebuilt_at'    => current_time( 'mysql' ),
        ];
    }

    /** endpoint دستی برای بازسازی انبار */
    public static function rebuild_stats_endpoint( WP_REST_Request $req ): WP_REST_Response {
        return new WP_REST_Response( self::rebuild_block_stats() );
    }

    /* ══════════════════════════════════════════════════════════
       DRILL-DOWN — منطقه (۱..۱۲) → محله → بلوک
       شمارش بر حسب «بلوک» (نه پارسل) در سطوح بالا.
       params:
         level         = district | neighborhood | block   (پیش‌فرض district)
         district      = شناسه منطقه (برای level=neighborhood|block)
         neighborhood  = شناسه محله  (برای level=block)
         فیلترها: layer_id, land_use, area_min, area_max, statuses, def_*
    ══════════════════════════════════════════════════════════ */
    public static function get_drilldown( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;

        $level        = sanitize_text_field( $req->get_param('level') ?: 'district' );
        if ( ! in_array($level, ['district','neighborhood','block'], true) ) $level = 'district';
        $district     = $req->get_param('district')     !== null ? (int) $req->get_param('district')     : null;
        $neighborhood = $req->get_param('neighborhood') !== null ? (int) $req->get_param('neighborhood') : null;

        $uid = acl_current_user_id();
        if ( $district !== null ) {
            $deny = acl_deny_scope( $uid, 'district', $district, 'gis.view' );
            if ( $deny ) return $deny;
        }

        /* ── فیلترها (مثل get_dashboard) ── */
        $layer_id = $req->get_param('layer_id') ? sanitize_text_field($req->get_param('layer_id')) : '';
        $land_use = $req->get_param('land_use') ? sanitize_text_field($req->get_param('land_use')) : '';
        $area_min = $req->get_param('area_min') ? (float)$req->get_param('area_min') : null;
        $area_max = $req->get_param('area_max') ? (float)$req->get_param('area_max') : null;
        $statuses = $req->get_param('statuses') ? array_map('sanitize_text_field', explode(',', $req->get_param('statuses'))) : [];
        $def_building = $req->get_param('def_building') !== null ? (int)$req->get_param('def_building') : -1;
        $def_units    = $req->get_param('def_units')    !== null ? (int)$req->get_param('def_units')    : -1;
        $def_shops    = $req->get_param('def_shops')    !== null ? (int)$req->get_param('def_shops')    : -1;

        /* ── آیا فیلتری اعمال شده؟ (def فقط وقتی >0 فیلتر است؛ 0/0/0 = همه) ── */
        $has_def  = ($def_building > 0 || $def_units > 0 || $def_shops > 0);
        $filtered = ( ! empty($statuses) || $layer_id !== '' || $land_use !== '' ||
                      $area_min !== null || $area_max !== null || $has_def );

        /* ── مسیر سریع: بدون فیلتر → از انبار gis_block_stats ── */
        if ( ! $filtered ) {
            return self::_drilldown_from_warehouse( $level, $district, $neighborhood );
        }

        /* ── مسیر منعطف: با فیلتر → کوئری زنده روی ستون‌های denormalize (بدون JOIN) ── */
        $wheres = ["p.status <> 'archived'", "p.district_id BETWEEN 1 AND 12", "p.block_code IS NOT NULL"];
        $args   = [];

        /* context drill */
        if ( in_array($level, ['neighborhood','block'], true) && $district !== null ) {
            $wheres[] = 'p.district_id = %d'; $args[] = $district;
        }
        if ( $level === 'block' && $neighborhood !== null ) {
            $wheres[] = 'p.neighborhood_id = %d'; $args[] = $neighborhood;
        }

        if ( ! acl_append_scope_where( $uid, 'district', 'p.district_id', $wheres, $args ) ) {
            return new WP_REST_Response( [ 'level' => $level, 'data' => [], 'source' => 'live' ] );
        }

        /* فیلترها — همه روی gis_parcels (denormalize‌شده) */
        if ($statuses) { $ph = implode(',', array_fill(0, count($statuses), '%s')); $wheres[] = "p.status IN ({$ph})"; foreach ($statuses as $s) $args[] = $s; }
        if ($layer_id) { $wheres[] = 'p.LayerID = %s';      $args[] = $layer_id; }
        if ($land_use) { $wheres[] = 'p.current_use = %s';  $args[] = $land_use; }
        if ($area_min !== null) { $wheres[] = 'p.area_sqm >= %f'; $args[] = $area_min; }
        if ($area_max !== null) { $wheres[] = 'p.area_sqm <= %f'; $args[] = $area_max; }
        if ($def_building > 0) { $wheres[] = 'p.bld_count  >= %d'; $args[] = $def_building; }
        if ($def_units    > 0) { $wheres[] = 'p.apt_count  >= %d'; $args[] = $def_units; }
        if ($def_shops    > 0) { $wheres[] = 'p.shop_count >= %d'; $args[] = $def_shops; }

        $where_sql = 'WHERE ' . implode(' AND ', $wheres);

        /* ── ثبات پلن کوئری: وقتی روی کاربری فیلتر می‌شود، بهینه‌ساز را
           مجبور به استفاده از ایندکس (current_use, status, id) می‌کنیم.
           بدون این، MySQL برای مقادیر مختلف current_use پلن‌های متفاوت
           انتخاب می‌کرد (بعضی full-scan) و کوئری روی برخی کاربری‌ها کند/معلق می‌ماند. */
        $force_idx = ( $land_use !== '' ) ? 'FORCE INDEX (idx_use_status_id)' : '';

        if ( $level === 'block' ) {
            /* ── سطح بلوک: هر سطر = یک بلوک، شمارش پارسل ── */
            $sql = "
                SELECT
                    p.block_code                         AS key_id,
                    p.block_code                         AS label,
                    MAX(p.district_id)                   AS district_id,
                    MAX(p.neighborhood_id)               AS neighborhood_id,
                    COUNT(*)                             AS total_parcels,
                    SUM(p.bld_count  > 0)                AS cnt_building,
                    SUM(p.apt_count  > 0)                AS cnt_apartment,
                    SUM(p.shop_count > 0)                AS cnt_shop,
                    SUM(p.bld_count = 0 AND p.apt_count = 0 AND p.shop_count = 0) AS cnt_land_only,
                    MAX(p.lat)                           AS lat,
                    MAX(p.lng)                           AS lng
                FROM {$pre}gis_parcels p {$force_idx}
                {$where_sql}
                GROUP BY p.block_code
                ORDER BY total_parcels DESC";
        } else {
            /* ── سطح منطقه یا محله: هر سطر = یک گروه، شمارش «بلوک» ── */
            $is_district = ($level === 'district');
            $group_col   = $is_district ? 'p.district_id'     : 'p.neighborhood_id';
            $name_col    = $is_district ? 'd.name'            : 'n.name';
            $name_join   = $is_district
                ? "LEFT JOIN {$pre}gis_districts d     ON d.id = p.district_id"
                : "LEFT JOIN {$pre}gis_neighborhoods n ON n.id = p.neighborhood_id";

            $sql = "
                SELECT
                    {$group_col}                                                   AS key_id,
                    MAX({$name_col})                                               AS label,
                    MAX(p.district_id)                                             AS district_id,
                    COUNT(DISTINCT p.block_code)          AS total_blocks,
                    COUNT(*)                              AS total_parcels,
                    SUM(p.bld_count  > 0)                 AS cnt_building,
                    SUM(p.apt_count  > 0)                 AS cnt_apartment,
                    SUM(p.shop_count > 0)                 AS cnt_shop,
                    SUM(p.bld_count = 0 AND p.apt_count = 0 AND p.shop_count = 0) AS cnt_land_only
                FROM {$pre}gis_parcels p {$force_idx}
                {$name_join}
                {$where_sql}
                GROUP BY {$group_col}
                ORDER BY total_blocks DESC";
        }

        $rows = $args
            ? $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A)
            : $wpdb->get_results($sql, ARRAY_A);

        return new WP_REST_Response([
            'level' => $level,
            'data'  => $rows ?? [],
            'source'=> 'live',
        ]);
    }

    /* ── drill-down از انبار gis_block_stats (مسیر سریع، بدون فیلتر) ── */
    private static function _drilldown_from_warehouse( string $level, ?int $district, ?int $neighborhood ): WP_REST_Response {
        global $wpdb;
        $pre = $wpdb->prefix;
        $uid = acl_current_user_id();

        if ( $level === 'block' ) {
            /* هر سطر = یک بلوک، مستقیم از انبار */
            $wheres = []; $args = [];
            if ( $district !== null )     { $wheres[] = 'bs.district_id = %d';     $args[] = $district; }
            if ( $neighborhood !== null ) { $wheres[] = 'bs.neighborhood_id = %d'; $args[] = $neighborhood; }
            if ( ! acl_append_scope_where( $uid, 'district', 'bs.district_id', $wheres, $args ) ) {
                return new WP_REST_Response( [ 'level' => $level, 'data' => [], 'source' => 'warehouse' ] );
            }
            $where_sql = $wheres ? 'WHERE ' . implode(' AND ', $wheres) : '';

            $sql = "
                SELECT
                    bs.block_code      AS key_id,
                    bs.block_code      AS label,
                    bs.district_id     AS district_id,
                    bs.neighborhood_id AS neighborhood_id,
                    bs.total_parcels   AS total_parcels,
                    bs.cnt_building    AS cnt_building,
                    bs.cnt_apartment   AS cnt_apartment,
                    bs.cnt_shop        AS cnt_shop,
                    bs.cnt_land_only   AS cnt_land_only,
                    bs.center_lat      AS lat,
                    bs.center_lng      AS lng
                FROM {$pre}gis_block_stats bs
                {$where_sql}
                ORDER BY bs.total_parcels DESC";
        } else {
            /* منطقه یا محله: rollup روی جدول کوچک انبار */
            $is_district = ($level === 'district');
            $group_col   = $is_district ? 'bs.district_id' : 'bs.neighborhood_id';
            $name_col    = $is_district ? 'd.name'         : 'n.name';
            $name_join   = $is_district
                ? "LEFT JOIN {$pre}gis_districts d     ON d.id = bs.district_id"
                : "LEFT JOIN {$pre}gis_neighborhoods n ON n.id = bs.neighborhood_id";

            $wheres = []; $args = [];
            if ( ! $is_district && $district !== null ) { $wheres[] = 'bs.district_id = %d'; $args[] = $district; }
            if ( ! acl_append_scope_where( $uid, 'district', 'bs.district_id', $wheres, $args ) ) {
                return new WP_REST_Response( [ 'level' => $level, 'data' => [], 'source' => 'warehouse' ] );
            }
            $where_sql = $wheres ? 'WHERE ' . implode(' AND ', $wheres) : '';

            $sql = "
                SELECT
                    {$group_col}                  AS key_id,
                    MAX({$name_col})              AS label,
                    MAX(bs.district_id)           AS district_id,
                    COUNT(*)                      AS total_blocks,
                    SUM(bs.total_parcels)         AS total_parcels,
                    SUM(bs.cnt_building)          AS cnt_building,
                    SUM(bs.cnt_apartment)         AS cnt_apartment,
                    SUM(bs.cnt_shop)              AS cnt_shop,
                    SUM(bs.cnt_land_only)         AS cnt_land_only
                FROM {$pre}gis_block_stats bs
                {$name_join}
                {$where_sql}
                GROUP BY {$group_col}
                ORDER BY total_blocks DESC";
        }

        $rows = $args
            ? $wpdb->get_results( $wpdb->prepare($sql, ...$args), ARRAY_A )
            : $wpdb->get_results( $sql, ARRAY_A );

        return new WP_REST_Response([
            'level'  => $level,
            'data'   => $rows ?? [],
            'source' => 'warehouse',
        ]);
    }

    /* ══════════════════════════════════════════════════════════
       پارسل‌های یک بلوک + پرچم is_match نسبت به فیلترها
       (برای نمایش دو-رنگه روی نقشه: مطابق فیلتر = برجسته، بقیه = کم‌رنگ)
       params: block (الزامی), layer_id, land_use, area_min, area_max, statuses, def_*
    ══════════════════════════════════════════════════════════ */
    public static function get_block_parcels( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre   = $wpdb->prefix;
        $block = sanitize_text_field( $req->get_param('block') ?? '' );
        if ( ! $block ) return new WP_REST_Response(['message'=>'block الزامی است'], 400);

        $deny = acl_deny_block_code( acl_current_user_id(), $block, 'gis.parcels.view' );
        if ( $deny ) return $deny;

        $layer_id = $req->get_param('layer_id') ? sanitize_text_field($req->get_param('layer_id')) : '';
        $land_use = $req->get_param('land_use') ? sanitize_text_field($req->get_param('land_use')) : '';
        $area_min = $req->get_param('area_min') !== null && $req->get_param('area_min') !== '' ? (float)$req->get_param('area_min') : null;
        $area_max = $req->get_param('area_max') !== null && $req->get_param('area_max') !== '' ? (float)$req->get_param('area_max') : null;
        $statuses = $req->get_param('statuses') ? array_map('sanitize_text_field', explode(',', $req->get_param('statuses'))) : [];
        $def_building = $req->get_param('def_building') !== null ? (int)$req->get_param('def_building') : -1;
        $def_units    = $req->get_param('def_units')    !== null ? (int)$req->get_param('def_units')    : -1;
        $def_shops    = $req->get_param('def_shops')    !== null ? (int)$req->get_param('def_shops')    : -1;

        /* ── شرایط match (همان منطق drilldown) → داخل CASE ── */
        $match  = [];
        $margs  = [];
        if ($statuses) { $ph = implode(',', array_fill(0, count($statuses), '%s')); $match[] = "p.status IN ({$ph})"; foreach ($statuses as $s) $margs[] = $s; }
        if ($land_use) { $match[] = 'pu.current_use = %s'; $margs[] = $land_use; }
        if ($area_min !== null) { $match[] = 'pl.area_sqm >= %f'; $margs[] = $area_min; }
        if ($area_max !== null) { $match[] = 'pl.area_sqm <= %f'; $margs[] = $area_max; }
        if ($def_building > 0) { $match[] = 'p.bld_count  >= %d'; $margs[] = $def_building; }
        if ($def_units    > 0) { $match[] = 'p.apt_count  >= %d'; $margs[] = $def_units; }
        if ($def_shops    > 0) { $match[] = 'p.shop_count >= %d'; $margs[] = $def_shops; }
        $match_expr = $match ? '(' . implode(' AND ', $match) . ')' : '1';

        /* ── WHERE اصلی ── */
        $wargs   = [ $block ];
        $where   = "p.block_code = %s AND p.status <> 'archived'";
        if ($layer_id) { $where .= ' AND p.LayerID = %s'; $wargs[] = $layer_id; }

        $sql = "
            SELECT p.id, p.code, p.lat, p.lng, p.geometry, p.status,
                   CASE WHEN {$match_expr} THEN 1 ELSE 0 END AS is_match
            FROM {$pre}gis_parcels p
            LEFT JOIN {$pre}gis_parcel_use  pu ON pu.parcel_id = p.id
            LEFT JOIN {$pre}gis_parcel_land pl ON pl.parcel_id = p.id
            WHERE {$where}
            ORDER BY p.code ASC
            LIMIT 2000";

        /* ترتیب آرگومان‌ها: اول CASE (match) بعد WHERE */
        $all_args = array_merge( $margs, $wargs );
        $rows = $all_args
            ? $wpdb->get_results( $wpdb->prepare($sql, ...$all_args), ARRAY_A )
            : $wpdb->get_results( $sql, ARRAY_A );

        $matched = 0;
        foreach ( ($rows ?? []) as $r ) { if ((int)$r['is_match'] === 1) $matched++; }

        return new WP_REST_Response([
            'block'       => $block,
            'data'        => $rows ?? [],
            'total'       => count($rows ?? []),
            'match_count' => $matched,
            'filtered'    => (bool) $match,   // آیا فیلتری اعمال شده؟
        ]);
    }

    public static function get_land_uses( WP_REST_Request $req ): WP_REST_Response {
        global $wpdb;
        $pre  = $wpdb->prefix;
        $uid  = acl_current_user_id();
        $wheres = [
            "current_use IS NOT NULL AND current_use != ''",
            "block_code IS NOT NULL",
        ];
        $args = [];
        if ( ! acl_append_scope_where( $uid, 'district', 'district_id', $wheres, $args ) ) {
            return new WP_REST_Response( [ 'data' => [] ] );
        }
        $where = 'WHERE ' . implode( ' AND ', $wheres );
        $sql   = "SELECT current_use AS value, COUNT(*) AS count
             FROM {$pre}gis_parcels FORCE INDEX (idx_use_block)
             {$where}
             GROUP BY current_use ORDER BY count DESC LIMIT 50";
        $rows = $args ? $wpdb->get_results( $wpdb->prepare( $sql, ...$args ), ARRAY_A )
                      : $wpdb->get_results( $sql, ARRAY_A );
        return new WP_REST_Response(['data' => $rows ?? []]);
    }
}
