<?php
declare(strict_types=1);

/**
 * ACC Dev Portal — knowledge base sync, code/db backups, auth helpers.
 */
if (!defined('ABSPATH') && !defined('ACC_BASE_PATH')) {
    exit;
}

final class Acc_Dev_Portal
{
    public const HANDOFF_REL = 'docs/PORTALS-DEV-HANDOFF.md';
    public const DOCS_SLUG   = 'portals-dev-handoff';

    /** @return string Absolute project root */
    public static function root(): string
    {
        return rtrim(defined('ACC_BASE_PATH') ? ACC_BASE_PATH : dirname(__DIR__, 2), '/\\');
    }

    public static function table(string $name): string
    {
        global $wpdb;
        return $wpdb->prefix . 'acc_dev_' . $name;
    }

    public static function backup_dir(string $type): string
    {
        $base = self::root() . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'backups';
        $dir  = $base . DIRECTORY_SEPARATOR . ($type === 'db' ? 'db' : 'code');
        if (!is_dir($dir)) {
            wp_mkdir_p($dir);
        }
        self::ensure_htaccess($base);
        return $dir;
    }

    public static function ensure_htaccess(string $base): void
    {
        $ht = $base . DIRECTORY_SEPARATOR . '.htaccess';
        if (!is_file($ht)) {
            if (!is_dir($base)) {
                wp_mkdir_p($base);
            }
            file_put_contents($ht, "Require all denied\nDeny from all\nOptions -Indexes\n");
        }
        foreach (['code', 'db'] as $sub) {
            $subDir = $base . DIRECTORY_SEPARATOR . $sub;
            if (!is_dir($subDir)) {
                wp_mkdir_p($subDir);
            }
            $subHt = $subDir . DIRECTORY_SEPARATOR . '.htaccess';
            if (!is_file($subHt)) {
                file_put_contents($subHt, "Require all denied\nDeny from all\nOptions -Indexes\n");
            }
            $idx = $subDir . DIRECTORY_SEPARATOR . 'index.html';
            if (!is_file($idx)) {
                file_put_contents($idx, '');
            }
        }
    }

    /* ── Schema ───────────────────────────────────────── */

    public static function install_tables(): array
    {
        global $wpdb;
        $charset = 'DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
        $created = [];

        $ddl = [
            'docs' => "CREATE TABLE IF NOT EXISTS " . self::table('docs') . " (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                slug VARCHAR(120) NOT NULL,
                title VARCHAR(255) NOT NULL DEFAULT '',
                body_md LONGTEXT NOT NULL,
                body_html LONGTEXT NULL,
                source_file VARCHAR(255) NOT NULL DEFAULT '',
                synced_at DATETIME NULL,
                version INT UNSIGNED NOT NULL DEFAULT 1,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uk_slug (slug)
            ) {$charset}",

            'doc_sections' => "CREATE TABLE IF NOT EXISTS " . self::table('doc_sections') . " (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                doc_id BIGINT UNSIGNED NOT NULL,
                portal_key VARCHAR(80) NOT NULL DEFAULT '',
                heading VARCHAR(255) NOT NULL DEFAULT '',
                heading_level TINYINT UNSIGNED NOT NULL DEFAULT 2,
                content_md LONGTEXT NOT NULL,
                content_html LONGTEXT NULL,
                sort_order INT NOT NULL DEFAULT 0,
                PRIMARY KEY (id),
                KEY idx_doc (doc_id),
                KEY idx_portal (portal_key)
            ) {$charset}",

            'doc_versions' => "CREATE TABLE IF NOT EXISTS " . self::table('doc_versions') . " (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                doc_id BIGINT UNSIGNED NOT NULL,
                version INT UNSIGNED NOT NULL,
                body_md LONGTEXT NOT NULL,
                synced_at DATETIME NOT NULL,
                created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
                PRIMARY KEY (id),
                KEY idx_doc_ver (doc_id, version)
            ) {$charset}",

            'assets' => "CREATE TABLE IF NOT EXISTS " . self::table('assets') . " (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                portal VARCHAR(80) NOT NULL DEFAULT '',
                kind VARCHAR(40) NOT NULL DEFAULT 'path',
                path_or_name VARCHAR(500) NOT NULL DEFAULT '',
                notes VARCHAR(500) NOT NULL DEFAULT '',
                source_section VARCHAR(120) NOT NULL DEFAULT '',
                synced_at DATETIME NULL,
                PRIMARY KEY (id),
                KEY idx_portal_kind (portal, kind)
            ) {$charset}",

            'backups' => "CREATE TABLE IF NOT EXISTS " . self::table('backups') . " (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                type VARCHAR(20) NOT NULL,
                filename VARCHAR(255) NOT NULL,
                path VARCHAR(500) NOT NULL,
                size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                created_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
                meta_json LONGTEXT NULL,
                PRIMARY KEY (id),
                KEY idx_type_created (type, created_at)
            ) {$charset}",

            'table_meta' => "CREATE TABLE IF NOT EXISTS " . self::table('table_meta') . " (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                table_name VARCHAR(191) NOT NULL,
                module_key VARCHAR(80) NOT NULL DEFAULT '',
                title_fa VARCHAR(255) NOT NULL DEFAULT '',
                description_fa TEXT NULL,
                source VARCHAR(40) NOT NULL DEFAULT 'manual',
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uk_table (table_name),
                KEY idx_module (module_key)
            ) {$charset}",

            'column_meta' => "CREATE TABLE IF NOT EXISTS " . self::table('column_meta') . " (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                table_name VARCHAR(191) NOT NULL,
                column_name VARCHAR(191) NOT NULL,
                description_fa TEXT NULL,
                related_table VARCHAR(191) NOT NULL DEFAULT '',
                related_column VARCHAR(191) NOT NULL DEFAULT '',
                source VARCHAR(40) NOT NULL DEFAULT 'manual',
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uk_table_col (table_name, column_name),
                KEY idx_table (table_name)
            ) {$charset}",

            'code_assets' => "CREATE TABLE IF NOT EXISTS " . self::table('code_assets') . " (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                portal_key VARCHAR(80) NOT NULL DEFAULT '',
                kind VARCHAR(40) NOT NULL DEFAULT 'other',
                path VARCHAR(500) NOT NULL DEFAULT '',
                size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
                mtime DATETIME NULL,
                notes VARCHAR(500) NOT NULL DEFAULT '',
                critical_path TINYINT(1) NOT NULL DEFAULT 0,
                is_summary TINYINT(1) NOT NULL DEFAULT 0,
                file_count INT UNSIGNED NOT NULL DEFAULT 0,
                synced_at DATETIME NULL,
                PRIMARY KEY (id),
                UNIQUE KEY uk_path (path(191)),
                KEY idx_portal_kind (portal_key, kind),
                KEY idx_critical (critical_path),
                KEY idx_size (size_bytes)
            ) {$charset}",
        ];

        foreach ($ddl as $key => $sql) {
            $wpdb->query($sql);
            $created[] = self::table($key);
        }

        self::ensure_htaccess(self::root() . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'backups');
        return $created;
    }

    /* ── Auth ─────────────────────────────────────────── */

    public static function session_start(): void
    {
        if (session_status() === PHP_SESSION_NONE) {
            session_name('acc_dev_portal');
            session_start([
                'cookie_httponly' => true,
                'cookie_samesite' => 'Lax',
                'use_strict_mode' => true,
            ]);
        }
        if (empty($_SESSION['csrf'])) {
            $_SESSION['csrf'] = bin2hex(random_bytes(16));
        }
    }

    public static function csrf_token(): string
    {
        self::session_start();
        return (string) ($_SESSION['csrf'] ?? '');
    }

    public static function verify_csrf(?string $token): bool
    {
        self::session_start();
        $expected = (string) ($_SESSION['csrf'] ?? '');
        return $expected !== '' && is_string($token) && hash_equals($expected, $token);
    }

    public static function current_user_id(): int
    {
        self::session_start();
        return (int) ($_SESSION['acc_dev_uid'] ?? 0);
    }

    public static function current_user(): ?array
    {
        $uid = self::current_user_id();
        if ($uid <= 0) {
            return null;
        }
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT ID, user_login, display_name, user_email FROM {$wpdb->users} WHERE ID = %d LIMIT 1",
                $uid
            ),
            ARRAY_A
        );
        return $row ?: null;
    }

    public static function user_is_privileged(int $user_id): bool
    {
        if ($user_id <= 0) {
            return false;
        }
        global $wpdb;

        $login = (string) $wpdb->get_var($wpdb->prepare(
            "SELECT user_login FROM {$wpdb->users} WHERE ID = %d",
            $user_id
        ));
        if ($login !== '' && in_array(strtolower($login), ['ostadowner', 'admin', 'administrator'], true)) {
            return true;
        }

        if (function_exists('acl_user_is_super_admin') && acl_user_is_super_admin($user_id)) {
            return true;
        }

        $roles_tbl = $wpdb->prefix . 'acl_roles';
        $ur_tbl    = $wpdb->prefix . 'acl_user_roles';
        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $roles_tbl))) {
            $ok = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$ur_tbl} ur
                 INNER JOIN {$roles_tbl} r ON r.id = ur.role_id
                 WHERE ur.user_id = %d AND r.slug IN ('super_admin','administrator','owner')",
                $user_id
            ));
            if ($ok > 0) {
                return true;
            }
        }

        return false;
    }

    public static function require_auth(): bool
    {
        $uid = self::current_user_id();
        return $uid > 0 && self::user_is_privileged($uid);
    }

    /** @return array{ok:bool,message:string,user?:array} */
    public static function login(string $username, string $password): array
    {
        $username = trim($username);
        if ($username === '' || $password === '') {
            return ['ok' => false, 'message' => 'نام کاربری و رمز عبور الزامی است.'];
        }

        $user = wp_authenticate($username, $password);
        if (is_wp_error($user)) {
            return ['ok' => false, 'message' => 'نام کاربری یا رمز عبور اشتباه است.'];
        }

        $uid = (int) $user->ID;
        if (!self::user_is_privileged($uid)) {
            return ['ok' => false, 'message' => 'دسترسی فقط برای مدیر / مالک سیستم مجاز است.'];
        }

        self::session_start();
        session_regenerate_id(true);
        $_SESSION['acc_dev_uid']   = $uid;
        $_SESSION['acc_dev_login'] = (string) ($user->user_login ?? $username);

        return [
            'ok'      => true,
            'message' => 'ورود موفق',
            'user'    => [
                'id'       => $uid,
                'username' => (string) ($user->user_login ?? $username),
                'name'     => (string) ($user->display_name ?? $username),
            ],
        ];
    }

    public static function logout(): void
    {
        self::session_start();
        $_SESSION = [];
        if (ini_get('session.use_cookies')) {
            $p = session_get_cookie_params();
            setcookie(session_name(), '', time() - 42000, $p['path'], $p['domain'], (bool) $p['secure'], (bool) $p['httponly']);
        }
        session_destroy();
    }

    /* ── Markdown ─────────────────────────────────────── */

    public static function md_to_html(string $md): string
    {
        $md = str_replace(["\r\n", "\r"], "\n", $md);
        $parts = preg_split('/(```[\s\S]*?```)/u', $md, -1, PREG_SPLIT_DELIM_CAPTURE);
        $html  = '';
        foreach ($parts as $part) {
            if (preg_match('/^```(\w*)\n?([\s\S]*?)```$/u', $part, $m)) {
                $code = htmlspecialchars($m[2], ENT_QUOTES, 'UTF-8');
                $html .= '<pre class="dev-code"><code>' . $code . '</code></pre>';
                continue;
            }
            $html .= self::md_inline_block($part);
        }
        return $html;
    }

    private static function md_inline_block(string $text): string
    {
        $lines = explode("\n", $text);
        $out   = [];
        $inUl  = false;
        $inOl  = false;
        $inTbl = false;
        $para  = [];

        $flushPara = static function () use (&$para, &$out): void {
            if (!$para) {
                return;
            }
            $chunk = trim(implode("\n", $para));
            $para  = [];
            if ($chunk === '') {
                return;
            }
            $out[] = '<p>' . self::md_inline(esc_html($chunk)) . '</p>';
        };
        $closeLists = static function () use (&$inUl, &$inOl, &$out): void {
            if ($inUl) {
                $out[] = '</ul>';
                $inUl  = false;
            }
            if ($inOl) {
                $out[] = '</ol>';
                $inOl  = false;
            }
        };
        $closeTbl = static function () use (&$inTbl, &$out): void {
            if ($inTbl) {
                $out[] = '</tbody></table>';
                $inTbl = false;
            }
        };

        foreach ($lines as $line) {
            $trim = rtrim($line);
            if ($trim === '') {
                $flushPara();
                $closeLists();
                $closeTbl();
                continue;
            }

            if (preg_match('/^(#{1,4})\s+(.+)$/u', $trim, $m)) {
                $flushPara();
                $closeLists();
                $closeTbl();
                $lvl = strlen($m[1]);
                $out[] = '<h' . $lvl . '>' . self::md_inline(esc_html($m[2])) . '</h' . $lvl . '>';
                continue;
            }

            if (preg_match('/^\|(.+)\|$/u', $trim)) {
                $flushPara();
                $closeLists();
                $cells = array_map('trim', explode('|', trim($trim, '|')));
                if (preg_match('/^[\s\-|:]+$/u', implode('', $cells))) {
                    continue;
                }
                if (!$inTbl) {
                    $out[] = '<table class="dev-table"><thead><tr>';
                    foreach ($cells as $c) {
                        $out[] = '<th>' . self::md_inline(esc_html($c)) . '</th>';
                    }
                    $out[] = '</tr></thead><tbody>';
                    $inTbl = true;
                } else {
                    $out[] = '<tr>';
                    foreach ($cells as $c) {
                        $out[] = '<td>' . self::md_inline(esc_html($c)) . '</td>';
                    }
                    $out[] = '</tr>';
                }
                continue;
            }

            if (preg_match('/^[-*]\s+(.+)$/u', $trim, $m)) {
                $flushPara();
                $closeTbl();
                if ($inOl) {
                    $out[] = '</ol>';
                    $inOl  = false;
                }
                if (!$inUl) {
                    $out[] = '<ul>';
                    $inUl  = true;
                }
                $out[] = '<li>' . self::md_inline(esc_html($m[1])) . '</li>';
                continue;
            }

            if (preg_match('/^\d+\.\s+(.+)$/u', $trim, $m)) {
                $flushPara();
                $closeTbl();
                if ($inUl) {
                    $out[] = '</ul>';
                    $inUl  = false;
                }
                if (!$inOl) {
                    $out[] = '<ol>';
                    $inOl  = true;
                }
                $out[] = '<li>' . self::md_inline(esc_html($m[1])) . '</li>';
                continue;
            }

            if (preg_match('/^>\s?(.*)$/u', $trim, $m)) {
                $flushPara();
                $closeLists();
                $closeTbl();
                $out[] = '<blockquote>' . self::md_inline(esc_html($m[1])) . '</blockquote>';
                continue;
            }

            if (preg_match('/^(-{3,}|\*{3,}|_{3,})$/u', $trim)) {
                $flushPara();
                $closeLists();
                $closeTbl();
                $out[] = '<hr>';
                continue;
            }

            $closeLists();
            $closeTbl();
            $para[] = $trim;
        }

        $flushPara();
        $closeLists();
        $closeTbl();

        return implode("\n", $out);
    }

    private static function md_inline(string $escaped): string
    {
        // $escaped is already HTML-escaped; apply limited markdown transforms carefully.
        $s = $escaped;
        $s = preg_replace('/`([^`]+)`/u', '<code>$1</code>', $s) ?? $s;
        $s = preg_replace('/\*\*([^*]+)\*\*/u', '<strong>$1</strong>', $s) ?? $s;
        $s = preg_replace('/(?<!\*)\*([^*]+)\*(?!\*)/u', '<em>$1</em>', $s) ?? $s;
        $s = preg_replace(
            '/\[([^\]]+)\]\((https?:\/\/[^)\s]+|\/[^)\s]*)\)/u',
            '<a href="$2" rel="noopener noreferrer">$1</a>',
            $s
        ) ?? $s;
        return $s;
    }

    /* ── Sync handoff MD ──────────────────────────────── */

    public static function handoff_path(): string
    {
        return self::root() . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, self::HANDOFF_REL);
    }

    /** @return array{ok:bool,message:string,doc_id?:int,version?:int,sections?:int,assets?:int} */
    public static function sync_handoff(?int $user_id = null): array
    {
        global $wpdb;
        $path = self::handoff_path();
        if (!is_file($path)) {
            return ['ok' => false, 'message' => 'فایل handoff یافت نشد: ' . self::HANDOFF_REL];
        }

        $md = file_get_contents($path);
        if ($md === false || $md === '') {
            return ['ok' => false, 'message' => 'خواندن فایل handoff ناموفق بود.'];
        }

        // Strip secrets-like lines from knowledge UI body (defensive)
        $safe_md = preg_replace('/^(.*(?:password|secret|api[_-]?key|ACC_JWT_SECRET|DB_PASS).*$)/imu', '> [حذف‌شده — اطلاعات حساس]', $md) ?? $md;

        $title = 'راهنمای تحویل توسعه‌دهندگان پورتال‌ها';
        if (preg_match('/^#\s+(.+)$/mu', $safe_md, $tm)) {
            $title = trim($tm[1]);
        }

        $html     = self::md_to_html($safe_md);
        $sections = self::parse_sections($safe_md);
        $assets   = self::parse_assets($safe_md);
        $now      = current_time('mysql');
        $docs     = self::table('docs');

        $existing = $wpdb->get_row(
            $wpdb->prepare("SELECT id, version FROM {$docs} WHERE slug = %s", self::DOCS_SLUG),
            ARRAY_A
        );

        if ($existing) {
            $doc_id  = (int) $existing['id'];
            $version = (int) $existing['version'] + 1;
            $wpdb->insert(self::table('doc_versions'), [
                'doc_id'     => $doc_id,
                'version'    => (int) $existing['version'],
                'body_md'    => (string) $wpdb->get_var($wpdb->prepare("SELECT body_md FROM {$docs} WHERE id = %d", $doc_id)),
                'synced_at'  => $now,
                'created_by' => (int) ($user_id ?? 0),
            ]);
            $wpdb->update($docs, [
                'title'       => $title,
                'body_md'     => $safe_md,
                'body_html'   => $html,
                'source_file' => self::HANDOFF_REL,
                'synced_at'   => $now,
                'version'     => $version,
            ], ['id' => $doc_id]);
        } else {
            $version = 1;
            $wpdb->insert($docs, [
                'slug'        => self::DOCS_SLUG,
                'title'       => $title,
                'body_md'     => $safe_md,
                'body_html'   => $html,
                'source_file' => self::HANDOFF_REL,
                'synced_at'   => $now,
                'version'     => $version,
            ]);
            $doc_id = (int) $wpdb->insert_id;
        }

        $sec_tbl = self::table('doc_sections');
        $wpdb->query($wpdb->prepare("DELETE FROM {$sec_tbl} WHERE doc_id = %d", $doc_id));
        $sort = 0;
        foreach ($sections as $sec) {
            $wpdb->insert($sec_tbl, [
                'doc_id'         => $doc_id,
                'portal_key'     => $sec['portal_key'],
                'heading'        => $sec['heading'],
                'heading_level'  => $sec['level'],
                'content_md'     => $sec['content_md'],
                'content_html'   => self::md_to_html($sec['content_md']),
                'sort_order'     => $sort++,
            ]);
        }

        $ast_tbl = self::table('assets');
        $wpdb->query("TRUNCATE TABLE {$ast_tbl}");
        foreach ($assets as $a) {
            $wpdb->insert($ast_tbl, [
                'portal'          => $a['portal'],
                'kind'            => $a['kind'],
                'path_or_name'    => $a['path_or_name'],
                'notes'           => $a['notes'],
                'source_section'  => $a['source_section'],
                'synced_at'       => $now,
            ]);
        }

        return [
            'ok'       => true,
            'message'  => 'همگام‌سازی انجام شد.',
            'doc_id'   => $doc_id,
            'version'  => $version,
            'sections' => count($sections),
            'assets'   => count($assets),
        ];
    }

    /** @return list<array{heading:string,level:int,portal_key:string,content_md:string}> */
    public static function parse_sections(string $md): array
    {
        $md = str_replace(["\r\n", "\r"], "\n", $md);
        $lines = explode("\n", $md);
        $sections = [];
        $cur = null;

        foreach ($lines as $line) {
            if (preg_match('/^(#{2,3})\s+(.+)$/u', $line, $m)) {
                if ($cur !== null) {
                    $sections[] = $cur;
                }
                $heading = trim($m[2]);
                $cur = [
                    'heading'     => $heading,
                    'level'       => strlen($m[1]),
                    'portal_key'  => self::guess_portal_key($heading),
                    'content_md'  => '',
                ];
                continue;
            }
            if ($cur !== null) {
                $cur['content_md'] .= $line . "\n";
            }
        }
        if ($cur !== null) {
            $sections[] = $cur;
        }
        return $sections;
    }

    public static function guess_portal_key(string $heading): string
    {
        $h = mb_strtolower($heading);
        $map = [
            'erp'        => ['erp', 'holding', 'spa'],
            'melk'       => ['melk', 'ملک'],
            'wholesale'  => ['wholesale', 'عمده'],
            'election'   => ['election', 'هوادار', 'انتخاب'],
            'service'    => ['service', 'hvac', 'سرویس'],
            'education'  => ['education', 'edubrain', 'آموزش'],
            'shop'       => ['shop', 'arag', 'عرقیات'],
            'growth'     => ['growth', 'سفیر', 'رشدگاه'],
            'offline'    => ['آفلاین', 'offline', 'منابع'],
            'images'     => ['تصاویر', 'مسیر تصاویر', 'image'],
            'shared'     => ['مشترک', 'هسته', 'جداول'],
            'profile'    => ['پروفایل', 'نکات کلی'],
            'entries'    => ['entry', 'سایر'],
            'risk'       => ['ریسک', 'first-load', 'مقایسه'],
        ];
        foreach ($map as $key => $needles) {
            foreach ($needles as $n) {
                if (mb_strpos($h, $n) !== false) {
                    return $key;
                }
            }
        }
        return 'general';
    }

    /** @return list<array{portal:string,kind:string,path_or_name:string,notes:string,source_section:string}> */
    public static function parse_assets(string $md): array
    {
        $assets = [];
        $sections = self::parse_sections($md);

        foreach ($sections as $sec) {
            $portal = $sec['portal_key'];
            $body   = $sec['content_md'];

            // Tables with path-like cells
            if (preg_match_all('/^\|([^|\n]+)\|([^|\n]+)\|/mu', $body, $rows, PREG_SET_ORDER)) {
                foreach ($rows as $row) {
                    $c1 = trim($row[1]);
                    $c2 = trim($row[2]);
                    if ($c1 === '' || str_starts_with($c1, '---') || $c1 === 'مورد' || $c1 === 'نقش' || $c1 === 'کاربرد' || $c1 === 'پورتال') {
                        continue;
                    }
                    $kind = 'note';
                    $path = $c2;
                    if (preg_match('/\.(php|js|css|jpg|jpeg|png|webp|woff2?|svg)(\?|$)/i', $c2)
                        || str_contains($c2, '/')
                        || str_contains($c2, '\\')
                    ) {
                        if (preg_match('/\.(jpg|jpeg|png|webp|gif|svg)/i', $c2)) {
                            $kind = 'image';
                        } elseif (preg_match('/\.css/i', $c2)) {
                            $kind = 'css';
                        } elseif (preg_match('/\.js/i', $c2)) {
                            $kind = 'js';
                        } elseif (preg_match('/\.php/i', $c2)) {
                            $kind = 'entry';
                        } else {
                            $kind = 'path';
                        }
                        $assets[] = [
                            'portal'         => $portal,
                            'kind'           => $kind,
                            'path_or_name'   => preg_replace('/\s+/', ' ', $path) ?? $path,
                            'notes'          => $c1,
                            'source_section' => $sec['heading'],
                        ];
                    } elseif (preg_match('/^(wp_)?[a-z0-9_]+$/i', $c2) && (str_contains($c1, 'جدول') || str_contains(mb_strtolower($c2), '_'))) {
                        $assets[] = [
                            'portal'         => $portal,
                            'kind'           => 'table',
                            'path_or_name'   => $c2,
                            'notes'          => $c1,
                            'source_section' => $sec['heading'],
                        ];
                    }
                }
            }

            // Bare image / asset paths in prose
            if (preg_match_all('#(?:/acc-engine/)?assets/[^\s\)\|`"\']+#u', $body, $pm)) {
                foreach ($pm[0] as $p) {
                    $kind = 'path';
                    if (preg_match('/\.(jpg|jpeg|png|webp|gif)/i', $p)) {
                        $kind = 'image';
                    } elseif (preg_match('/\.css/i', $p)) {
                        $kind = 'css';
                    } elseif (preg_match('/\.js/i', $p)) {
                        $kind = 'js';
                    }
                    $assets[] = [
                        'portal'         => $portal,
                        'kind'           => $kind,
                        'path_or_name'   => $p,
                        'notes'          => '',
                        'source_section' => $sec['heading'],
                    ];
                }
            }
        }

        // Deduplicate
        $seen = [];
        $unique = [];
        foreach ($assets as $a) {
            $k = $a['portal'] . '|' . $a['kind'] . '|' . $a['path_or_name'];
            if (isset($seen[$k])) {
                continue;
            }
            $seen[$k] = true;
            $unique[] = $a;
        }
        return $unique;
    }

    /* ── Docs queries ─────────────────────────────────── */

    public static function get_doc(?string $slug = null): ?array
    {
        global $wpdb;
        $slug = $slug ?: self::DOCS_SLUG;
        $row  = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM ' . self::table('docs') . ' WHERE slug = %s', $slug),
            ARRAY_A
        );
        return $row ?: null;
    }

    /** @return list<array> */
    public static function get_sections(int $doc_id, ?string $portal = null, ?string $q = null): array
    {
        global $wpdb;
        $tbl = self::table('doc_sections');
        $sql = "SELECT * FROM {$tbl} WHERE doc_id = %d";
        $args = [$doc_id];
        if ($portal && $portal !== 'all') {
            $sql .= ' AND portal_key = %s';
            $args[] = $portal;
        }
        if ($q !== null && $q !== '') {
            $like = '%' . $wpdb->esc_like($q) . '%';
            $sql .= ' AND (heading LIKE %s OR content_md LIKE %s)';
            $args[] = $like;
            $args[] = $like;
        }
        $sql .= ' ORDER BY sort_order ASC';
        return $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A) ?: [];
    }

    /** @return list<array> */
    public static function get_assets(?string $portal = null, ?string $kind = null): array
    {
        global $wpdb;
        $tbl = self::table('assets');
        $sql = "SELECT * FROM {$tbl} WHERE 1=1";
        $args = [];
        if ($portal && $portal !== 'all') {
            $sql .= ' AND portal = %s';
            $args[] = $portal;
        }
        if ($kind && $kind !== 'all') {
            $sql .= ' AND kind = %s';
            $args[] = $kind;
        }
        $sql .= ' ORDER BY portal, kind, path_or_name';
        if ($args) {
            return $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A) ?: [];
        }
        return $wpdb->get_results($sql, ARRAY_A) ?: [];
    }

    /* ── Code backup ──────────────────────────────────── */

    /** @return list<string> relative exclude path prefixes / globs */
    public static function default_code_exclusions(bool $include_fonts = true): array
    {
        $ex = [
            'assets/img',
            'assets/video',
            'assets/vendor/leaflet/tiles',
            'assets/vendor/leaflet/tiles_esri',
            'assets/vendor/leaflet/tiles_osm',
            'node_modules',
            'vite-src/node_modules',
            'storage/backups',
            '.git',
            '.claude',
            'uploads',
            'logs',
            'wp-content/uploads',
            'tabulator-master/node_modules',
        ];
        if (!$include_fonts) {
            $ex[] = 'assets/fonts';
        }
        return $ex;
    }

    public static function should_exclude_file(string $rel, array $excludes, bool $exclude_env = true): bool
    {
        $rel = str_replace('\\', '/', $rel);
        $rel = ltrim($rel, '/');
        $base = basename($rel);

        if ($exclude_env && (preg_match('/^\.env(\.|$)/', $base) || $base === '.env')) {
            return true;
        }
        if (preg_match('/\.(log|tmp|bak)$/i', $base)) {
            return true;
        }
        if (preg_match('/\.(jpg|jpeg|png|gif|webp|bmp|tiff|ico|mp4|webm|mov|avi|mkv)$/i', $base)
            && (str_starts_with($rel, 'assets/img/') || str_contains($rel, '/tiles/') || str_contains($rel, '/tiles_'))
        ) {
            return true;
        }

        foreach ($excludes as $ex) {
            $ex = str_replace('\\', '/', trim($ex, '/'));
            if ($ex === '') {
                continue;
            }
            if ($rel === $ex || str_starts_with($rel, $ex . '/')) {
                return true;
            }
        }
        return false;
    }

    /**
     * @param array{include_fonts?:bool,exclude_env?:bool} $opts
     * @return array{ok:bool,message:string,backup?:array}
     */
    public static function create_code_backup(array $opts = [], ?int $user_id = null): array
    {
        @set_time_limit(900);
        $include_fonts = !array_key_exists('include_fonts', $opts) || !empty($opts['include_fonts']);
        $exclude_env   = !array_key_exists('exclude_env', $opts) || !empty($opts['exclude_env']);
        $excludes      = self::default_code_exclusions($include_fonts);

        $root = self::root();
        $dir  = self::backup_dir('code');
        $ts   = date('Ymd-His');
        $name = "code-{$ts}.tar.gz";
        $abs  = $dir . DIRECTORY_SEPARATOR . $name;

        $tar = self::find_tar();
        if (!$tar) {
            return ['ok' => false, 'message' => 'ابزار tar در سیستم یافت نشد.'];
        }

        // Count included files (for meta); prune heavy excluded directories early
        $count = 0;
        $bytes = 0;
        $dirIterator = new RecursiveDirectoryIterator($root, FilesystemIterator::SKIP_DOTS);
        $filter = new RecursiveCallbackFilterIterator(
            $dirIterator,
            static function (SplFileInfo $current, string $key, RecursiveDirectoryIterator $iterator) use ($root, $excludes, $exclude_env): bool {
                $full = $current->getPathname();
                $rel  = substr($full, strlen($root) + 1);
                $relN = str_replace('\\', '/', $rel);
                if ($current->isDir()) {
                    // Prune excluded directory trees
                    foreach ($excludes as $ex) {
                        $ex = str_replace('\\', '/', trim($ex, '/'));
                        if ($relN === $ex || str_starts_with($relN, $ex . '/')) {
                            return false;
                        }
                    }
                    return true;
                }
                return !Acc_Dev_Portal::should_exclude_file($relN, $excludes, $exclude_env);
            }
        );
        $iterator = new RecursiveIteratorIterator($filter);
        foreach ($iterator as $file) {
            /** @var SplFileInfo $file */
            if (!$file->isFile()) {
                continue;
            }
            $count++;
            $bytes += (int) $file->getSize();
        }

        if ($count === 0) {
            return ['ok' => false, 'message' => 'هیچ فایلی برای پشتیبان یافت نشد.'];
        }

        // Prefer --exclude (Windows bsdtar); avoid -T path-list encoding issues
        $cmd = [is_file((string) $tar) ? $tar : 'tar', '-czf', $abs, '-C', $root];
        foreach ($excludes as $ex) {
            $cmd[] = '--exclude=' . str_replace('\\', '/', $ex);
            $cmd[] = '--exclude=' . str_replace('\\', '/', $ex) . '/*';
        }
        // Extra patterns
        $cmd[] = '--exclude=*.log';
        $cmd[] = '--exclude=*.tmp';
        if ($exclude_env) {
            $cmd[] = '--exclude=.env';
            $cmd[] = '--exclude=.env.*';
        }
        // Exclude image extensions under assets/img (belt-and-suspenders)
        $cmd[] = '--exclude=assets/img/*';
        $cmd[] = '.';

        $descriptors = [
            1 => ['pipe', 'w'],
            2 => ['pipe', 'w'],
        ];
        $proc = proc_open($cmd, $descriptors, $pipes, null, null, ['bypass_shell' => true]);
        if (!is_resource($proc)) {
            return ['ok' => false, 'message' => 'اجرای tar ناموفق بود.'];
        }
        $stdout = stream_get_contents($pipes[1]);
        $stderr = stream_get_contents($pipes[2]);
        fclose($pipes[1]);
        fclose($pipes[2]);
        $code = proc_close($proc);

        if ($code !== 0 || !is_file($abs) || filesize($abs) < 64) {
            @unlink($abs);
            $msg = trim((string) $stderr . ' ' . (string) $stdout);
            return [
                'ok'      => false,
                'message' => 'ساخت آرشیو ناموفق بود: ' . mb_substr($msg !== '' ? $msg : 'unknown', 0, 400),
            ];
        }

        $size = (int) filesize($abs);
        $meta = [
            'exclusions'    => $excludes,
            'exclude_env'   => $exclude_env,
            'include_fonts' => $include_fonts,
            'file_count'    => $count,
            'source_bytes'  => $bytes,
            'format'        => 'tar.gz',
            'tool'          => is_string($tar) ? $tar : 'tar',
        ];

        global $wpdb;
        $wpdb->insert(self::table('backups'), [
            'type'       => 'code',
            'filename'   => $name,
            'path'       => 'storage/backups/code/' . $name,
            'size_bytes' => $size,
            'created_by' => (int) ($user_id ?? 0),
            'meta_json'  => wp_json_encode($meta, JSON_UNESCAPED_UNICODE),
        ]);

        return [
            'ok'      => true,
            'message' => "پشتیبان کد ساخته شد (~{$count} فایل).",
            'backup'  => [
                'id'       => (int) $wpdb->insert_id,
                'filename' => $name,
                'size'     => $size,
                'meta'     => $meta,
            ],
        ];
    }

    public static function find_tar(): ?string
    {
        $candidates = [
            'C:\\Windows\\System32\\tar.exe',
            'tar.exe',
            'tar',
        ];
        foreach ($candidates as $c) {
            if ($c === 'tar' || $c === 'tar.exe') {
                return $c;
            }
            if (is_file($c)) {
                return $c;
            }
        }
        return null;
    }

    /* ── DB backup ────────────────────────────────────── */

    /**
     * Known high-volume GIS tables (relative names without prefix).
     * Used as a curated hint list; live discovery covers all {prefix}gis_*.
     *
     * @return list<string>
     */
    public static function known_heavy_gis_suffixes(): array
    {
        return [
            'gis_vote_result',
            'gis_ownership_history',
            'gis_parcels',
            'gis_vote',
            'gis_vote_vs_trespass',
            'gis_vote_vs_trepass', // actual DB spelling
            'gis_trespass',
            'gis_trepass', // actual DB spelling
            'gis_commission',
            'gis_parcel_owners',
            'gis_parcel_use',
            'gis_nosazi_land',
        ];
    }

    /**
     * All GIS tables in the current DB ({prefix}gis_*).
     *
     * @return list<string>
     */
    public static function list_gis_table_names(): array
    {
        $out = [];
        foreach (self::list_gis_tables_with_sizes() as $row) {
            $out[] = (string) $row['name'];
        }
        return $out;
    }

    /**
     * GIS tables with approximate on-disk size (data + index) from INFORMATION_SCHEMA.
     *
     * @return list<array{name:string,size_bytes:int,known_heavy:bool}>
     */
    public static function list_gis_tables_with_sizes(): array
    {
        global $wpdb;
        $db = defined('ACC_DB_NAME') ? ACC_DB_NAME : (string) $wpdb->dbname;
        if ($db === '') {
            return [];
        }
        $prefix = $wpdb->prefix . 'gis_';
        $heavy  = [];
        foreach (self::known_heavy_gis_suffixes() as $suf) {
            $heavy[$wpdb->prefix . $suf] = true;
        }

        $rows = $wpdb->get_results(
            $wpdb->prepare(
                'SELECT TABLE_NAME AS name,
                        COALESCE(DATA_LENGTH, 0) + COALESCE(INDEX_LENGTH, 0) AS size_bytes
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = %s AND TABLE_NAME LIKE %s
                 ORDER BY size_bytes DESC, TABLE_NAME ASC',
                $db,
                $wpdb->esc_like($prefix) . '%'
            ),
            ARRAY_A
        ) ?: [];

        $out = [];
        foreach ($rows as $row) {
            $name = (string) ($row['name'] ?? '');
            if ($name === '') {
                continue;
            }
            $out[] = [
                'name'         => $name,
                'size_bytes'   => (int) ($row['size_bytes'] ?? 0),
                'known_heavy'  => isset($heavy[$name]),
            ];
        }
        return $out;
    }

    /**
     * Resolve which tables to skip for a DB backup.
     *
     * Defaults: exclude all {prefix}gis_* tables.
     * Opt-in full GIS: exclude_gis=false OR include_gis=true.
     * Per-table include: include_tables keeps those even when exclude_gis is on.
     *
     * @param array{exclude_gis?:bool,include_gis?:bool,include_tables?:list<string>|string} $opts
     * @return list<string>
     */
    public static function resolve_db_exclusions(array $opts = []): array
    {
        $includeGis = !empty($opts['include_gis']);
        $excludeGis = array_key_exists('exclude_gis', $opts)
            ? !empty($opts['exclude_gis'])
            : true;
        if ($includeGis) {
            $excludeGis = false;
        }
        if (!$excludeGis) {
            return [];
        }

        $forceInclude = [];
        $raw = $opts['include_tables'] ?? [];
        if (is_string($raw)) {
            $raw = array_filter(array_map('trim', explode(',', $raw)));
        }
        if (is_array($raw)) {
            foreach ($raw as $t) {
                $t = (string) $t;
                if ($t !== '') {
                    $forceInclude[$t] = true;
                }
            }
        }

        $excluded = [];
        foreach (self::list_gis_table_names() as $table) {
            if (isset($forceInclude[$table])) {
                continue;
            }
            $excluded[] = $table;
        }
        return $excluded;
    }

    /**
     * @param array{exclude_gis?:bool,include_gis?:bool,include_tables?:list<string>|string} $opts
     * @return array{ok:bool,message:string,backup?:array}
     */
    public static function create_db_backup(array $opts = [], ?int $user_id = null): array
    {
        @set_time_limit(900);
        $dir  = self::backup_dir('db');
        $ts   = date('Ymd-His');
        $name = "db-{$ts}.sql.gz";
        $abs  = $dir . DIRECTORY_SEPARATOR . $name;

        $excluded = self::resolve_db_exclusions($opts);
        $includeGis = empty($excluded);

        $dump = self::stream_mysqldump_to_gzip($abs, $excluded);
        if (!$dump['ok']) {
            $phpDump = self::stream_php_dump_to_gzip($abs, $excluded);
            if (!$phpDump['ok']) {
                @unlink($abs);
                return [
                    'ok'      => false,
                    'message' => 'mysqldump و PHP dump هر دو ناموفق: ' . ($dump['message'] ?? '') . ' / ' . ($phpDump['message'] ?? ''),
                ];
            }
            $method = 'php-stream';
        } else {
            $method = 'mysqldump-stream';
        }

        $size = is_file($abs) ? (int) filesize($abs) : 0;
        if ($size < 64) {
            @unlink($abs);
            return ['ok' => false, 'message' => 'فایل dump خالی یا ناقص است.'];
        }

        $gisSizes = self::list_gis_tables_with_sizes();
        $excludedBytes = 0;
        $excludedSet = array_fill_keys($excluded, true);
        foreach ($gisSizes as $row) {
            if (isset($excludedSet[$row['name']])) {
                $excludedBytes += (int) $row['size_bytes'];
            }
        }

        $meta = [
            'method'              => $method,
            'db_name'             => defined('ACC_DB_NAME') ? ACC_DB_NAME : '',
            'host'                => defined('ACC_DB_HOST') ? ACC_DB_HOST : '',
            'exclude_gis'         => !$includeGis,
            'include_gis'         => $includeGis,
            'excluded_tables'     => $excluded,
            'excluded_count'      => count($excluded),
            'excluded_approx_bytes' => $excludedBytes,
        ];

        global $wpdb;
        $wpdb->insert(self::table('backups'), [
            'type'       => 'db',
            'filename'   => $name,
            'path'       => 'storage/backups/db/' . $name,
            'size_bytes' => $size,
            'created_by' => (int) ($user_id ?? 0),
            'meta_json'  => wp_json_encode($meta, JSON_UNESCAPED_UNICODE),
        ]);

        $msg = 'پشتیبان دیتابیس ساخته شد (' . $method . ').';
        if (count($excluded) > 0) {
            $msg .= ' ' . count($excluded) . ' جدول GIS حذف شد.';
        } else {
            $msg .= ' شامل جداول GIS.';
        }

        return [
            'ok'      => true,
            'message' => $msg,
            'backup'  => [
                'id'       => (int) $wpdb->insert_id,
                'filename' => $name,
                'size'     => $size,
                'meta'     => $meta,
            ],
        ];
    }

    /**
     * Stream mysqldump stdout → gzip file (avoids loading full SQL in RAM).
     *
     * @param list<string> $ignoreTables Full table names to pass as --ignore-table
     */
    public static function stream_mysqldump_to_gzip(string $absGz, array $ignoreTables = []): array
    {
        $bin = 'C:\\xampp\\mysql\\bin\\mysqldump.exe';
        if (!is_file($bin)) {
            return ['ok' => false, 'message' => 'mysqldump.exe یافت نشد.'];
        }

        $host = defined('ACC_DB_HOST') ? ACC_DB_HOST : 'localhost';
        $user = defined('ACC_DB_USER') ? ACC_DB_USER : 'root';
        $pass = defined('ACC_DB_PASS') ? ACC_DB_PASS : '';
        $name = defined('ACC_DB_NAME') ? ACC_DB_NAME : '';
        if ($name === '') {
            return ['ok' => false, 'message' => 'نام دیتابیس خالی است.'];
        }

        $cnf = tempnam(sys_get_temp_dir(), 'accdump');
        if ($cnf === false) {
            return ['ok' => false, 'message' => 'temp file failed'];
        }
        // Escape password for my.cnf double quotes
        $passSafe = str_replace(['\\', '"'], ['\\\\', '\\"'], $pass);
        file_put_contents($cnf, "[client]\nuser={$user}\npassword=\"{$passSafe}\"\nhost={$host}\n");

        $cmd = [
            $bin,
            '--defaults-extra-file=' . $cnf,
            '--single-transaction',
            '--routines',
            '--triggers',
            '--default-character-set=utf8mb4',
        ];
        foreach ($ignoreTables as $table) {
            $table = (string) $table;
            if ($table === '' || str_contains($table, "\0")) {
                continue;
            }
            $cmd[] = '--ignore-table=' . $name . '.' . $table;
        }
        $cmd[] = $name;

        $gz = gzopen($absGz, 'wb6');
        if ($gz === false) {
            @unlink($cnf);
            return ['ok' => false, 'message' => 'باز کردن فایل gzip ناموفق.'];
        }

        $descriptors = [
            1 => ['pipe', 'w'],
            2 => ['pipe', 'w'],
        ];
        $proc = proc_open($cmd, $descriptors, $pipes, null, null, ['bypass_shell' => true]);
        if (!is_resource($proc)) {
            gzclose($gz);
            @unlink($cnf);
            @unlink($absGz);
            return ['ok' => false, 'message' => 'اجرای mysqldump ناموفق.'];
        }

        $written = 0;
        while (!feof($pipes[1])) {
            $chunk = fread($pipes[1], 1024 * 256);
            if ($chunk === false || $chunk === '') {
                break;
            }
            gzwrite($gz, $chunk);
            $written += strlen($chunk);
        }
        $err = stream_get_contents($pipes[2]);
        fclose($pipes[1]);
        fclose($pipes[2]);
        $code = proc_close($proc);
        gzclose($gz);
        @unlink($cnf);

        if ($code !== 0 || $written < 32) {
            @unlink($absGz);
            return ['ok' => false, 'message' => trim((string) $err) ?: 'mysqldump empty/failed'];
        }

        return ['ok' => true, 'bytes' => $written];
    }

    /**
     * Streaming PHP dump for key ACC/WP tables when mysqldump is unavailable.
     * Prefers prefix tables + users/usermeta/options to keep memory bounded.
     *
     * @param list<string> $excludeTables Tables to skip entirely
     */
    public static function stream_php_dump_to_gzip(string $absGz, array $excludeTables = []): array
    {
        global $wpdb;
        try {
            $all = $wpdb->get_col('SHOW TABLES') ?: [];
            if (!$all) {
                return ['ok' => false, 'message' => 'جدولی یافت نشد.'];
            }

            $skip = [];
            foreach ($excludeTables as $t) {
                $skip[(string) $t] = true;
            }

            $pre = $wpdb->prefix;
            $priority = [];
            $rest = [];
            foreach ($all as $table) {
                $t = (string) $table;
                if (isset($skip[$t])) {
                    continue;
                }
                if (
                    str_starts_with($t, $pre)
                    || in_array($t, ['users', 'usermeta', 'options'], true)
                ) {
                    $priority[] = $t;
                } else {
                    $rest[] = $t;
                }
            }
            // Prefer prefixed; include rest only if total under soft cap of tables
            $tables = count($priority) ? $priority : $all;
            if (count($tables) < 5) {
                $tables = [];
                foreach ($all as $table) {
                    $t = (string) $table;
                    if (!isset($skip[$t])) {
                        $tables[] = $t;
                    }
                }
            }

            $gz = gzopen($absGz, 'wb6');
            if ($gz === false) {
                return ['ok' => false, 'message' => 'باز کردن gzip ناموفق.'];
            }

            $w = static function (string $s) use ($gz): void {
                gzwrite($gz, $s);
            };

            $w("-- ACC Dev Portal PHP dump (streamed)\n-- " . date('c') . "\nSET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS=0;\n\n");
            if ($skip) {
                $w('-- Excluded tables: ' . implode(', ', array_keys($skip)) . "\n\n");
            }

            foreach ($tables as $table) {
                $safe = str_replace('`', '``', $table);
                $create = $wpdb->get_row("SHOW CREATE TABLE `{$safe}`", ARRAY_N);
                if (!$create) {
                    continue;
                }
                $w("DROP TABLE IF EXISTS `{$safe}`;\n");
                $w($create[1] . ";\n\n");

                $offset = 0;
                $batch  = 100;
                while (true) {
                    $rows = $wpdb->get_results("SELECT * FROM `{$safe}` LIMIT {$batch} OFFSET {$offset}", ARRAY_A);
                    if (!$rows) {
                        break;
                    }
                    foreach ($rows as $row) {
                        $cols = array_map(static fn($c) => '`' . str_replace('`', '``', (string) $c) . '`', array_keys($row));
                        $vals = [];
                        foreach ($row as $v) {
                            if ($v === null) {
                                $vals[] = 'NULL';
                            } else {
                                $vals[] = "'" . addslashes((string) $v) . "'";
                            }
                        }
                        $w('INSERT INTO `' . $safe . '` (' . implode(',', $cols) . ') VALUES (' . implode(',', $vals) . ");\n");
                    }
                    $offset += $batch;
                    if (count($rows) < $batch) {
                        break;
                    }
                    // Free row memory between batches
                    unset($rows);
                }
                $w("\n");
            }
            $w("SET FOREIGN_KEY_CHECKS=1;\n");
            gzclose($gz);
            return ['ok' => true];
        } catch (Throwable $e) {
            @unlink($absGz);
            return ['ok' => false, 'message' => $e->getMessage()];
        }
    }

    /* ── Backup list / download / delete ──────────────── */

    /** @return list<array> */
    public static function list_backups(?string $type = null): array
    {
        global $wpdb;
        $tbl = self::table('backups');
        if ($type) {
            return $wpdb->get_results(
                $wpdb->prepare("SELECT * FROM {$tbl} WHERE type = %s ORDER BY created_at DESC, id DESC", $type),
                ARRAY_A
            ) ?: [];
        }
        return $wpdb->get_results("SELECT * FROM {$tbl} ORDER BY created_at DESC, id DESC", ARRAY_A) ?: [];
    }

    public static function get_backup(int $id): ?array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM ' . self::table('backups') . ' WHERE id = %d', $id),
            ARRAY_A
        );
        return $row ?: null;
    }

    public static function delete_backup(int $id): array
    {
        $row = self::get_backup($id);
        if (!$row) {
            return ['ok' => false, 'message' => 'پشتیبان یافت نشد.'];
        }
        $abs = self::root() . DIRECTORY_SEPARATOR . str_replace(['/', '\\'], DIRECTORY_SEPARATOR, $row['path']);
        // Safety: only under storage/backups
        $backupsRoot = realpath(self::root() . DIRECTORY_SEPARATOR . 'storage' . DIRECTORY_SEPARATOR . 'backups');
        $real = realpath($abs);
        if ($real && $backupsRoot && str_starts_with($real, $backupsRoot) && is_file($real)) {
            @unlink($real);
        }
        global $wpdb;
        $wpdb->delete(self::table('backups'), ['id' => $id]);
        return ['ok' => true, 'message' => 'حذف شد.'];
    }

    public static function format_bytes(int $bytes): string
    {
        if ($bytes < 1024) {
            return $bytes . ' B';
        }
        if ($bytes < 1048576) {
            return round($bytes / 1024, 1) . ' KB';
        }
        if ($bytes < 1073741824) {
            return round($bytes / 1048576, 2) . ' MB';
        }
        return round($bytes / 1073741824, 2) . ' GB';
    }

    /* ── DB catalog (INFORMATION_SCHEMA) ──────────────── */

    public static function db_name(): string
    {
        global $wpdb;
        if (defined('ACC_DB_NAME') && ACC_DB_NAME !== '') {
            return (string) ACC_DB_NAME;
        }
        return (string) ($wpdb->dbname ?? '');
    }

    public static function ensure_catalog_meta_tables(): void
    {
        static $done = false;
        if ($done) {
            return;
        }
        self::install_tables();
        $done = true;
    }

    /** @return array<string, array{key:string,label:string,prefix:string}> */
    public static function catalog_module_map(): array
    {
        return [
            'acc'       => ['key' => 'acc', 'label' => 'حسابداری / هسته ACC', 'prefix' => 'acc_'],
            'acc_dev'   => ['key' => 'acc_dev', 'label' => 'Dev Hub', 'prefix' => 'acc_dev_'],
            'gis'       => ['key' => 'gis', 'label' => 'GIS / املاک شهری', 'prefix' => 'gis_'],
            'election'  => ['key' => 'election', 'label' => 'انتخابات', 'prefix' => 'election_'],
            'melk'      => ['key' => 'melk', 'label' => 'Melk اقتصاد ملک', 'prefix' => 'melk_'],
            'svc'       => ['key' => 'svc', 'label' => 'Service / HVAC', 'prefix' => 'svc_'],
            'edu'       => ['key' => 'edu', 'label' => 'Education', 'prefix' => 'edu_'],
            'arag'      => ['key' => 'arag', 'label' => 'Shop / عرقیات', 'prefix' => 'arag_'],
            'growth'    => ['key' => 'growth', 'label' => 'Growth / سفیران', 'prefix' => 'growth_'],
            'wholesale' => ['key' => 'wholesale', 'label' => 'عمده‌فروشی', 'prefix' => 'wholesale_'],
            'erp'       => ['key' => 'erp', 'label' => 'ERP Runtime', 'prefix' => 'erp_'],
            'ai'        => ['key' => 'ai', 'label' => 'AI Accounting', 'prefix' => 'ai_'],
            'inv'       => ['key' => 'inv', 'label' => 'انبار', 'prefix' => 'inv_'],
            'cp'        => ['key' => 'cp', 'label' => 'CPM پروژه', 'prefix' => 'cp_'],
            'pc'        => ['key' => 'pc', 'label' => 'PC پروژه', 'prefix' => 'pc_'],
            'hr'        => ['key' => 'hr', 'label' => 'منابع انسانی', 'prefix' => 'hr_'],
            'wf'        => ['key' => 'wf', 'label' => 'Workflow', 'prefix' => 'wf_'],
            'ship'      => ['key' => 'ship', 'label' => 'حمل‌ونقل', 'prefix' => 'ship_'],
            'asset'     => ['key' => 'asset', 'label' => 'دارایی ثابت', 'prefix' => 'asset_'],
            'chat'      => ['key' => 'chat', 'label' => 'چت', 'prefix' => 'chat_'],
            'acl'       => ['key' => 'acl', 'label' => 'ACL دسترسی', 'prefix' => 'acl_'],
            'crm'       => ['key' => 'crm', 'label' => 'CRM', 'prefix' => 'crm_'],
            'dj'        => ['key' => 'dj', 'label' => 'دفتر روزنامه', 'prefix' => 'dj_'],
            'accj'      => ['key' => 'accj', 'label' => 'ACCJ', 'prefix' => 'accj_'],
            'doc'       => ['key' => 'doc', 'label' => 'مستندات schema', 'prefix' => 'doc_'],
            'wc'        => ['key' => 'wc', 'label' => 'WooCommerce', 'prefix' => 'wc_'],
            'woocommerce' => ['key' => 'woocommerce', 'label' => 'WooCommerce', 'prefix' => 'woocommerce_'],
            'wp'        => ['key' => 'wp', 'label' => 'هسته WordPress', 'prefix' => ''],
            'other'     => ['key' => 'other', 'label' => 'سایر', 'prefix' => ''],
        ];
    }

    /** @return array{key:string,label:string,prefix:string} */
    public static function catalog_tag_table(string $table): array
    {
        global $wpdb;
        $map = self::catalog_module_map();
        $p = $wpdb->prefix;
        if (str_starts_with($table, $p)) {
            $rest = substr($table, strlen($p));
            // Longer prefixes first
            $keys = ['acc_dev', 'woocommerce', 'wholesale', 'election', 'actionscheduler', 'arag', 'growth', 'melk', 'gis', 'edu', 'svc', 'erp', 'inv', 'chat', 'ship', 'asset', 'acl', 'crm', 'accj', 'doc', 'wf', 'hr', 'cp', 'pc', 'dj', 'ai', 'wc', 'acc'];
            foreach ($keys as $k) {
                if (isset($map[$k]) && str_starts_with($rest, $map[$k]['prefix']) && $map[$k]['prefix'] !== '') {
                    return $map[$k];
                }
            }
            // Core WP tables
            $core = ['users', 'usermeta', 'posts', 'postmeta', 'options', 'comments', 'commentmeta', 'terms', 'termmeta', 'term_taxonomy', 'term_relationships', 'links'];
            $short = $rest;
            if (in_array($short, $core, true) || str_starts_with($short, 'user_')) {
                return $map['wp'];
            }
            return $map['other'];
        }
        return $map['other'];
    }

    public static function catalog_safe_table(string $table): ?string
    {
        global $wpdb;
        $db = self::db_name();
        $table = trim($table);
        if ($table === '' || !preg_match('/^[A-Za-z0-9_]+$/', $table)) {
            return null;
        }
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA = %s AND TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = %s",
            $db,
            $table
        ));
        return $exists ? (string) $exists : null;
    }

    /** @return array{ok:bool,db?:string,modules?:list,items?:list,total?:int,message?:string} */
    public static function db_catalog_list(array $opts = []): array
    {
        global $wpdb;
        self::ensure_catalog_meta_tables();
        $db = self::db_name();
        if ($db === '') {
            return ['ok' => false, 'message' => 'نام دیتابیس مشخص نیست.'];
        }

        $prefix = trim((string) ($opts['prefix'] ?? ''));
        $module = trim((string) ($opts['module'] ?? ''));
        $q = trim((string) ($opts['q'] ?? ''));
        $minSize = (int) ($opts['min_size'] ?? 0);
        $maxSize = (int) ($opts['max_size'] ?? 0);
        $sort = (string) ($opts['sort'] ?? 'name');

        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT TABLE_NAME AS name, ENGINE AS engine, TABLE_ROWS AS table_rows,
                    COALESCE(DATA_LENGTH,0) AS data_length,
                    COALESCE(INDEX_LENGTH,0) AS index_length,
                    TABLE_COLLATION AS collation,
                    TABLE_COMMENT AS comment
             FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA = %s AND TABLE_TYPE = 'BASE TABLE'
             ORDER BY TABLE_NAME",
            $db
        ), ARRAY_A) ?: [];

        $metaRows = $wpdb->get_results(
            'SELECT table_name, module_key, title_fa, description_fa FROM ' . self::table('table_meta'),
            ARRAY_A
        ) ?: [];
        $metaBy = [];
        foreach ($metaRows as $m) {
            $metaBy[(string) $m['table_name']] = $m;
        }

        $items = [];
        foreach ($rows as $r) {
            $name = (string) $r['name'];
            $tag = self::catalog_tag_table($name);
            $dataLen = (int) $r['data_length'];
            $idxLen = (int) $r['index_length'];
            $total = $dataLen + $idxLen;
            $meta = $metaBy[$name] ?? null;
            $comment = (string) ($r['comment'] ?? '');
            $desc = $meta['description_fa'] ?? '';
            $title = $meta['title_fa'] ?? '';

            if ($prefix !== '' && $prefix !== 'all' && !str_starts_with($name, $prefix)) {
                continue;
            }
            if ($module !== '' && $module !== 'all' && $tag['key'] !== $module) {
                continue;
            }
            if ($minSize > 0 && $total < $minSize) {
                continue;
            }
            if ($maxSize > 0 && $total > $maxSize) {
                continue;
            }
            if ($q !== '') {
                $hay = mb_strtolower($name . ' ' . $comment . ' ' . $title . ' ' . $desc . ' ' . $tag['label'], 'UTF-8');
                if (!str_contains($hay, mb_strtolower($q, 'UTF-8'))) {
                    continue;
                }
            }

            $items[] = [
                'name'         => $name,
                'engine'       => (string) ($r['engine'] ?? ''),
                'rows'         => (int) ($r['table_rows'] ?? 0),
                'data_length'  => $dataLen,
                'index_length' => $idxLen,
                'total_size'   => $total,
                'collation'    => (string) ($r['collation'] ?? ''),
                'comment'      => $comment,
                'title_fa'     => (string) $title,
                'description'  => (string) ($desc !== '' ? $desc : $comment),
                'module'       => $tag['key'],
                'module_label' => $tag['label'],
            ];
        }

        usort($items, static function ($a, $b) use ($sort) {
            return match ($sort) {
                'size' => $b['total_size'] <=> $a['total_size'],
                'rows' => $b['rows'] <=> $a['rows'],
                'module' => ($a['module_label'] . $a['name']) <=> ($b['module_label'] . $b['name']),
                default => $a['name'] <=> $b['name'],
            };
        });

        $modules = [];
        foreach (self::catalog_module_map() as $m) {
            $modules[] = ['key' => $m['key'], 'label' => $m['label']];
        }

        $prefixes = [];
        global $wpdb;
        $p = $wpdb->prefix;
        foreach (['acc_', 'gis_', 'election_', 'melk_', 'svc_', 'edu_', 'arag_', 'growth_', 'wholesale_', 'erp_', 'ai_', 'inv_', 'cp_', 'hr_', 'wf_', 'chat_', 'acl_', 'wc_', 'woocommerce_'] as $suf) {
            $prefixes[] = $p . $suf;
        }

        return [
            'ok'       => true,
            'db'       => $db,
            'prefix'   => $p,
            'modules'  => $modules,
            'prefixes' => $prefixes,
            'items'    => $items,
            'total'    => count($items),
        ];
    }

    /** @return list<array<string,mixed>> */
    public static function catalog_columns(string $table): array
    {
        global $wpdb;
        $db = self::db_name();
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT COLUMN_NAME AS name, COLUMN_TYPE AS type, IS_NULLABLE AS nullable,
                    COLUMN_DEFAULT AS col_default, EXTRA AS extra, COLUMN_KEY AS col_key,
                    COLUMN_COMMENT AS comment, CHARACTER_SET_NAME AS charset,
                    COLLATION_NAME AS collation, ORDINAL_POSITION AS pos
             FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
             ORDER BY ORDINAL_POSITION",
            $db,
            $table
        ), ARRAY_A) ?: [];

        $metaRows = $wpdb->get_results($wpdb->prepare(
            'SELECT column_name, description_fa, related_table, related_column FROM '
            . self::table('column_meta') . ' WHERE table_name = %s',
            $table
        ), ARRAY_A) ?: [];
        $metaBy = [];
        foreach ($metaRows as $m) {
            $metaBy[(string) $m['column_name']] = $m;
        }

        $out = [];
        foreach ($rows as $r) {
            $cn = (string) $r['name'];
            $m = $metaBy[$cn] ?? null;
            $comment = (string) ($r['comment'] ?? '');
            $desc = $m['description_fa'] ?? '';
            $out[] = [
                'name'           => $cn,
                'type'           => (string) $r['type'],
                'nullable'       => (string) $r['nullable'] === 'YES',
                'default'        => $r['col_default'],
                'extra'          => (string) ($r['extra'] ?? ''),
                'key'            => (string) ($r['col_key'] ?? ''),
                'comment'        => $comment,
                'description'    => (string) ($desc !== '' ? $desc : $comment),
                'charset'        => (string) ($r['charset'] ?? ''),
                'collation'      => (string) ($r['collation'] ?? ''),
                'related_table'  => (string) ($m['related_table'] ?? ''),
                'related_column' => (string) ($m['related_column'] ?? ''),
            ];
        }
        return $out;
    }

    /** @return list<array<string,mixed>> */
    public static function catalog_indexes(string $table): array
    {
        global $wpdb;
        $db = self::db_name();
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT INDEX_NAME AS name, NON_UNIQUE AS non_unique, INDEX_TYPE AS type,
                    SEQ_IN_INDEX AS seq, COLUMN_NAME AS column_name, CARDINALITY AS cardinality
             FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s
             ORDER BY INDEX_NAME, SEQ_IN_INDEX",
            $db,
            $table
        ), ARRAY_A) ?: [];

        $grouped = [];
        foreach ($rows as $r) {
            $n = (string) $r['name'];
            if (!isset($grouped[$n])) {
                $grouped[$n] = [
                    'name'        => $n,
                    'unique'      => ((int) $r['non_unique'] === 0),
                    'type'        => (string) ($r['type'] ?? 'BTREE'),
                    'columns'     => [],
                    'cardinality' => null,
                ];
            }
            $grouped[$n]['columns'][] = (string) $r['column_name'];
            if ($r['cardinality'] !== null) {
                $grouped[$n]['cardinality'] = (int) $r['cardinality'];
            }
        }
        return array_values($grouped);
    }

    /** @return array{from:list,to:list} */
    public static function catalog_foreign_keys(string $table): array
    {
        global $wpdb;
        $db = self::db_name();

        $from = $wpdb->get_results($wpdb->prepare(
            "SELECT rc.CONSTRAINT_NAME AS constraint_name,
                    kcu.COLUMN_NAME AS column_name,
                    kcu.REFERENCED_TABLE_NAME AS referenced_table,
                    kcu.REFERENCED_COLUMN_NAME AS referenced_column,
                    rc.DELETE_RULE AS on_delete,
                    rc.UPDATE_RULE AS on_update
             FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
             JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
               ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
              AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
              AND rc.TABLE_NAME = kcu.TABLE_NAME
             WHERE rc.CONSTRAINT_SCHEMA = %s AND rc.TABLE_NAME = %s
             ORDER BY rc.CONSTRAINT_NAME, kcu.ORDINAL_POSITION",
            $db,
            $table
        ), ARRAY_A) ?: [];

        $to = $wpdb->get_results($wpdb->prepare(
            "SELECT rc.CONSTRAINT_NAME AS constraint_name,
                    kcu.TABLE_NAME AS from_table,
                    kcu.COLUMN_NAME AS column_name,
                    kcu.REFERENCED_COLUMN_NAME AS referenced_column,
                    rc.DELETE_RULE AS on_delete,
                    rc.UPDATE_RULE AS on_update
             FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
             JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
               ON rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
              AND rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
              AND rc.TABLE_NAME = kcu.TABLE_NAME
             WHERE rc.CONSTRAINT_SCHEMA = %s AND kcu.REFERENCED_TABLE_NAME = %s
             ORDER BY kcu.TABLE_NAME, rc.CONSTRAINT_NAME, kcu.ORDINAL_POSITION",
            $db,
            $table
        ), ARRAY_A) ?: [];

        return ['from' => $from, 'to' => $to];
    }

    /**
     * Infer logical relations from *_id column naming (not enforced FKs).
     * @return list<array<string,mixed>>
     */
    public static function catalog_inferred_relations(string $table, array $columns): array
    {
        global $wpdb;
        $db = self::db_name();
        $allTables = $wpdb->get_col($wpdb->prepare(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA = %s AND TABLE_TYPE = 'BASE TABLE'",
            $db
        )) ?: [];
        $tableSet = array_fill_keys(array_map('strval', $allTables), true);
        $p = $wpdb->prefix;

        $known = [
            'user_id'     => [$p . 'users', 'ID'],
            'post_id'     => [$p . 'posts', 'ID'],
            'comment_id'  => [$p . 'comments', 'comment_ID'],
            'term_id'     => [$p . 'terms', 'term_id'],
            'parcel_id'   => [$p . 'gis_parcels', 'id'],
            'district_id' => [$p . 'gis_districts', 'id'],
            'block_id'    => [$p . 'gis_blocks', 'id'],
            'account_id'  => [$p . 'acc_accounts', 'id'],
            'voucher_id'  => [$p . 'acc_vouchers', 'id'],
            'project_id'  => [$p . 'acc_projects', 'id'],
            'order_id'    => [$p . 'arag_orders', 'id'],
            'product_id'  => [$p . 'inv_products', 'id'],
            'warehouse_id'=> [$p . 'inv_warehouses', 'id'],
            'employee_id' => [$p . 'hr_employees', 'id'],
            'role_id'     => [$p . 'acl_roles', 'id'],
            'room_id'     => [$p . 'chat_rooms', 'id'],
            'doc_id'      => [$p . 'acc_dev_docs', 'id'],
        ];

        $out = [];
        foreach ($columns as $col) {
            $cn = (string) ($col['name'] ?? '');
            if ($cn === '' || $cn === 'id' || $cn === 'ID') {
                continue;
            }
            if (!str_ends_with($cn, '_id') && $cn !== 'post_id' && $cn !== 'user_id') {
                continue;
            }

            $targetTable = '';
            $targetCol = 'id';
            $confidence = 'low';
            $note = '';

            if (isset($known[$cn]) && isset($tableSet[$known[$cn][0]])) {
                $targetTable = $known[$cn][0];
                $targetCol = $known[$cn][1];
                $confidence = 'high';
                $note = 'نگاشت شناخته‌شده';
            } elseif ($cn === 'parent_id') {
                $targetTable = $table;
                $targetCol = 'id';
                $confidence = 'medium';
                $note = 'سلسله‌مراتب خودارجاع';
            } else {
                $stem = preg_replace('/_id$/', '', $cn);
                $candidates = [
                    $p . $stem . 's',
                    $p . $stem . 'es',
                    $p . $stem,
                    $p . 'acc_' . $stem . 's',
                    $p . 'gis_' . $stem . 's',
                    $p . 'gis_' . $stem,
                ];
                // Same module family: wp_melk_case_notes.case_id → wp_melk_cases
                if (preg_match('/^(wp_[a-z]+_)/', $table, $m)) {
                    $mod = $m[1];
                    $candidates[] = $mod . $stem . 's';
                    $candidates[] = $mod . $stem . 'es';
                    $candidates[] = $mod . $stem;
                }
                foreach ($candidates as $cand) {
                    if (isset($tableSet[$cand])) {
                        $targetTable = $cand;
                        $confidence = 'medium';
                        $note = 'حدس از نام ستون';
                        break;
                    }
                }
            }

            if ($targetTable === '') {
                $out[] = [
                    'column'           => $cn,
                    'referenced_table' => null,
                    'referenced_column'=> null,
                    'kind'             => 'inferred',
                    'confidence'      => 'none',
                    'note'             => 'ستون شناسه بدون جدول هدف مشخص',
                ];
                continue;
            }

            $out[] = [
                'column'            => $cn,
                'referenced_table'  => $targetTable,
                'referenced_column' => $targetCol,
                'kind'              => 'inferred',
                'confidence'       => $confidence,
                'note'              => $note,
            ];
        }
        return $out;
    }

    /** @return list<array{table:string,column:string,kind:string}> */
    public static function catalog_referencing_hint(string $table, array $fkTo, array $allInferredCache = []): array
    {
        global $wpdb;
        $db = self::db_name();
        $hints = [];
        foreach ($fkTo as $fk) {
            $hints[] = [
                'table'  => (string) ($fk['from_table'] ?? ''),
                'column' => (string) ($fk['column_name'] ?? ''),
                'kind'   => 'fk',
            ];
        }

        // Inferred: other tables with columns that likely point here
        $short = $table;
        $p = $wpdb->prefix;
        if (str_starts_with($table, $p)) {
            $short = substr($table, strlen($p));
        }
        $stem = preg_replace('/s$/', '', $short);
        $stem = preg_replace('/^(gis_|acc_|melk_|election_|svc_|edu_|arag_|growth_|inv_|hr_|cp_|erp_|acl_|chat_)/', '', (string) $stem);
        $wantCols = array_unique(array_filter([
            $stem . '_id',
            preg_replace('/s$/', '', (string) $stem) . '_id',
            'parcel_id',
        ]));

        if ($table === $p . 'users') {
            $wantCols[] = 'user_id';
        }
        if ($table === $p . 'posts') {
            $wantCols[] = 'post_id';
        }
        if ($table === $p . 'gis_parcels') {
            $wantCols[] = 'parcel_id';
        }

        $placeholders = implode(',', array_fill(0, count($wantCols), '%s'));
        if ($placeholders !== '') {
            $args = array_merge([$db, $table], array_values($wantCols));
            $sql = "SELECT TABLE_NAME AS tbl, COLUMN_NAME AS col
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_SCHEMA = %s AND TABLE_NAME <> %s
                      AND COLUMN_NAME IN ($placeholders)
                    ORDER BY TABLE_NAME LIMIT 80";
            $rows = $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A) ?: [];
            $seen = [];
            foreach ($fkTo as $fk) {
                $seen[$fk['from_table'] . '.' . $fk['column_name']] = true;
            }
            foreach ($rows as $r) {
                $k = $r['tbl'] . '.' . $r['col'];
                if (isset($seen[$k])) {
                    continue;
                }
                $hints[] = [
                    'table'  => (string) $r['tbl'],
                    'column' => (string) $r['col'],
                    'kind'   => 'inferred',
                ];
            }
        }
        return $hints;
    }

    public static function catalog_create_sql(string $table): string
    {
        global $wpdb;
        $row = $wpdb->get_row('SHOW CREATE TABLE `' . str_replace('`', '``', $table) . '`', ARRAY_A);
        if (!$row) {
            return '';
        }
        foreach ($row as $k => $v) {
            if (stripos((string) $k, 'Create') !== false) {
                return (string) $v;
            }
        }
        return (string) (array_values($row)[1] ?? '');
    }

    /** @return array{ok:bool,message?:string,table?:array} */
    public static function db_catalog_table(string $tableName, bool $withCreate = true): array
    {
        self::ensure_catalog_meta_tables();
        $table = self::catalog_safe_table($tableName);
        if (!$table) {
            return ['ok' => false, 'message' => 'جدول یافت نشد.'];
        }

        global $wpdb;
        $db = self::db_name();
        $info = $wpdb->get_row($wpdb->prepare(
            "SELECT TABLE_NAME AS name, ENGINE AS engine, TABLE_ROWS AS table_rows,
                    COALESCE(DATA_LENGTH,0) AS data_length,
                    COALESCE(INDEX_LENGTH,0) AS index_length,
                    TABLE_COLLATION AS collation, TABLE_COMMENT AS comment
             FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
            $db,
            $table
        ), ARRAY_A);

        $meta = $wpdb->get_row($wpdb->prepare(
            'SELECT title_fa, description_fa, module_key FROM ' . self::table('table_meta') . ' WHERE table_name = %s',
            $table
        ), ARRAY_A);

        $columns = self::catalog_columns($table);
        $indexes = self::catalog_indexes($table);
        $fks = self::catalog_foreign_keys($table);
        $inferred = self::catalog_inferred_relations($table, $columns);
        $refs = self::catalog_referencing_hint($table, $fks['to']);
        $tag = self::catalog_tag_table($table);

        return [
            'ok'    => true,
            'table' => [
                'name'         => $table,
                'engine'       => (string) ($info['engine'] ?? ''),
                'rows_approx'  => (int) ($info['table_rows'] ?? 0),
                'data_length'  => (int) ($info['data_length'] ?? 0),
                'index_length' => (int) ($info['index_length'] ?? 0),
                'total_size'   => (int) ($info['data_length'] ?? 0) + (int) ($info['index_length'] ?? 0),
                'collation'    => (string) ($info['collation'] ?? ''),
                'comment'      => (string) ($info['comment'] ?? ''),
                'title_fa'     => (string) ($meta['title_fa'] ?? ''),
                'description'  => (string) (($meta['description_fa'] ?? '') !== '' ? $meta['description_fa'] : ($info['comment'] ?? '')),
                'module'       => $tag['key'],
                'module_label' => $tag['label'],
                'columns'      => $columns,
                'indexes'      => $indexes,
                'foreign_keys' => $fks,
                'inferred'     => $inferred,
                'referenced_by'=> $refs,
                'create_sql'   => $withCreate ? self::catalog_create_sql($table) : '',
            ],
        ];
    }

    /** @return array{ok:bool,message?:string,count?:int,rows?:list,columns?:list} */
    public static function db_catalog_sample(string $tableName, int $limit = 20, bool $exactCount = false): array
    {
        global $wpdb;
        $table = self::catalog_safe_table($tableName);
        if (!$table) {
            return ['ok' => false, 'message' => 'جدول یافت نشد.'];
        }
        $limit = max(1, min(50, $limit));
        $safe = '`' . str_replace('`', '``', $table) . '`';

        $count = null;
        if ($exactCount) {
            $count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$safe}");
        }

        $raw = $wpdb->get_results("SELECT * FROM {$safe} LIMIT {$limit}", ARRAY_A) ?: [];
        $cols = [];
        $rows = [];
        if ($raw) {
            $cols = array_keys($raw[0]);
            foreach ($raw as $row) {
                $out = [];
                foreach ($row as $k => $v) {
                    if ($v === null) {
                        $out[$k] = null;
                    } elseif (is_string($v) && strlen($v) > 200) {
                        $out[$k] = mb_substr($v, 0, 200, 'UTF-8') . '…';
                    } else {
                        $out[$k] = $v;
                    }
                }
                $rows[] = $out;
            }
        } else {
            $cols = array_column(self::catalog_columns($table), 'name');
        }

        return [
            'ok'      => true,
            'table'   => $table,
            'count'   => $count,
            'limit'   => $limit,
            'columns' => $cols,
            'rows'    => $rows,
        ];
    }

    /** Enrich meta from INFORMATION_SCHEMA comments + wp_doc_db_schema if present. */
    public static function db_catalog_refresh_meta(): array
    {
        global $wpdb;
        self::ensure_catalog_meta_tables();
        $db = self::db_name();
        $tm = self::table('table_meta');
        $cm = self::table('column_meta');
        $tablesUpdated = 0;
        $colsUpdated = 0;

        $builtin = [
            $wpdb->prefix . 'gis_parcels' => 'پارسل‌های GIS',
            $wpdb->prefix . 'gis_vote' => 'آراء GIS',
            $wpdb->prefix . 'gis_vote_result' => 'نتایج رای GIS',
            $wpdb->prefix . 'acc_accounts' => 'سرفصل‌های حسابداری',
            $wpdb->prefix . 'acc_vouchers' => 'اسناد حسابداری',
            $wpdb->prefix . 'acc_articles' => 'آرتیکل‌های اسناد',
            $wpdb->prefix . 'users' => 'کاربران',
            $wpdb->prefix . 'posts' => 'محتوای وردپرس',
            $wpdb->prefix . 'options' => 'تنظیمات وردپرس',
            $wpdb->prefix . 'election_supporters' => 'هواداران انتخابات',
            $wpdb->prefix . 'melk_listings' => 'آگهی‌های Melk',
            $wpdb->prefix . 'arag_orders' => 'سفارش‌های فروشگاه',
            $wpdb->prefix . 'svc_requests' => 'درخواست‌های خدمات',
            $wpdb->prefix . 'edu_learners' => 'فراگیران آموزش',
            $wpdb->prefix . 'wholesale_orders' => 'سفارش عمده',
            $wpdb->prefix . 'chat_messages' => 'پیام‌های چت',
            $wpdb->prefix . 'inv_products' => 'کالای انبار',
            $wpdb->prefix . 'acc_dev_docs' => 'مستندات Dev Hub',
            $wpdb->prefix . 'acc_dev_backups' => 'فهرست پشتیبان‌ها',
        ];

        $infoTables = $wpdb->get_results($wpdb->prepare(
            "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA = %s AND TABLE_TYPE = 'BASE TABLE'",
            $db
        ), ARRAY_A) ?: [];

        foreach ($infoTables as $t) {
            $name = (string) $t['TABLE_NAME'];
            $comment = trim((string) ($t['TABLE_COMMENT'] ?? ''));
            $title = $builtin[$name] ?? ($comment !== '' ? $comment : '');
            $desc = $comment !== '' ? $comment : ($builtin[$name] ?? '');
            if ($title === '' && $desc === '') {
                continue;
            }
            $tag = self::catalog_tag_table($name);
            $wpdb->query($wpdb->prepare(
                "INSERT INTO {$tm} (table_name, module_key, title_fa, description_fa, source)
                 VALUES (%s, %s, %s, %s, 'refresh')
                 ON DUPLICATE KEY UPDATE
                   module_key = VALUES(module_key),
                   title_fa = IF(VALUES(title_fa) <> '', VALUES(title_fa), title_fa),
                   description_fa = IF(VALUES(description_fa) <> '', VALUES(description_fa), description_fa),
                   source = 'refresh'",
                $name,
                $tag['key'],
                $title,
                $desc
            ));
            $tablesUpdated++;
        }

        // Import from wp_doc_db_schema when available
        $docTbl = $wpdb->prefix . 'doc_db_schema';
        $hasDoc = $wpdb->get_var($wpdb->prepare(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
            $db,
            $docTbl
        ));
        if ($hasDoc) {
            $docRows = $wpdb->get_results(
                "SELECT table_name, column_name, table_description, column_description, related_table, related_column, module
                 FROM `{$docTbl}`",
                ARRAY_A
            ) ?: [];
            $seenTables = [];
            foreach ($docRows as $r) {
                $tn = (string) $r['table_name'];
                $cn = (string) $r['column_name'];
                if ($tn === '') {
                    continue;
                }
                if (!isset($seenTables[$tn]) && !empty($r['table_description'])) {
                    $tag = self::catalog_tag_table($tn);
                    $wpdb->query($wpdb->prepare(
                        "INSERT INTO {$tm} (table_name, module_key, title_fa, description_fa, source)
                         VALUES (%s, %s, %s, %s, 'doc_db_schema')
                         ON DUPLICATE KEY UPDATE
                           description_fa = VALUES(description_fa),
                           title_fa = IF(title_fa = '', VALUES(title_fa), title_fa),
                           module_key = VALUES(module_key),
                           source = 'doc_db_schema'",
                        $tn,
                        $tag['key'],
                        (string) ($r['module'] ?? ''),
                        (string) $r['table_description']
                    ));
                    $seenTables[$tn] = true;
                    $tablesUpdated++;
                }
                if ($cn !== '' && !empty($r['column_description'])) {
                    $wpdb->query($wpdb->prepare(
                        "INSERT INTO {$cm} (table_name, column_name, description_fa, related_table, related_column, source)
                         VALUES (%s, %s, %s, %s, %s, 'doc_db_schema')
                         ON DUPLICATE KEY UPDATE
                           description_fa = VALUES(description_fa),
                           related_table = VALUES(related_table),
                           related_column = VALUES(related_column),
                           source = 'doc_db_schema'",
                        $tn,
                        $cn,
                        (string) $r['column_description'],
                        (string) ($r['related_table'] ?? ''),
                        (string) ($r['related_column'] ?? '')
                    ));
                    $colsUpdated++;
                }
            }
        }

        // Column comments from INFORMATION_SCHEMA
        $colComments = $wpdb->get_results($wpdb->prepare(
            "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
             FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = %s AND COLUMN_COMMENT <> ''",
            $db
        ), ARRAY_A) ?: [];
        foreach ($colComments as $c) {
            $wpdb->query($wpdb->prepare(
                "INSERT INTO {$cm} (table_name, column_name, description_fa, source)
                 VALUES (%s, %s, %s, 'schema_comment')
                 ON DUPLICATE KEY UPDATE
                   description_fa = IF(description_fa = '' OR description_fa IS NULL, VALUES(description_fa), description_fa),
                   source = IF(source = 'manual', source, 'schema_comment')",
                (string) $c['TABLE_NAME'],
                (string) $c['COLUMN_NAME'],
                (string) $c['COLUMN_COMMENT']
            ));
            $colsUpdated++;
        }

        return [
            'ok'              => true,
            'message'         => "متای جداول به‌روز شد ({$tablesUpdated} جدول، {$colsUpdated} ستون).",
            'tables_updated'  => $tablesUpdated,
            'columns_updated' => $colsUpdated,
        ];
    }

    /* ── Code catalog (filesystem) ────────────────────── */

    public static function ensure_code_assets_table(): void
    {
        global $wpdb;
        $tbl = self::table('code_assets');
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
            self::db_name(),
            $tbl
        ));
        if ($exists) {
            return;
        }
        $charset = 'DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
        $wpdb->query("CREATE TABLE IF NOT EXISTS {$tbl} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            portal_key VARCHAR(80) NOT NULL DEFAULT '',
            kind VARCHAR(40) NOT NULL DEFAULT 'other',
            path VARCHAR(500) NOT NULL DEFAULT '',
            size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
            mtime DATETIME NULL,
            notes VARCHAR(500) NOT NULL DEFAULT '',
            critical_path TINYINT(1) NOT NULL DEFAULT 0,
            is_summary TINYINT(1) NOT NULL DEFAULT 0,
            file_count INT UNSIGNED NOT NULL DEFAULT 0,
            synced_at DATETIME NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uk_path (path(191)),
            KEY idx_portal_kind (portal_key, kind),
            KEY idx_critical (critical_path),
            KEY idx_size (size_bytes)
        ) {$charset}");
    }

    /** @return list<string> */
    public static function code_catalog_skip_dir_names(): array
    {
        return [
            'node_modules',
            '.git',
            '.claude',
            'backups',
            '__pycache__',
            '.idea',
            '.vscode',
        ];
    }

    public static function code_is_tiles_dir(string $name): bool
    {
        $n = strtolower($name);
        return $n === 'tiles' || str_starts_with($n, 'tiles_') || str_starts_with($n, 'tiles-');
    }

    public static function code_guess_portal(string $rel): string
    {
        $rel = strtolower(str_replace('\\', '/', $rel));
        $base = basename($rel);
        $stem = preg_replace('/\.(php|js|css|md|html?)$/i', '', $base) ?? $base;

        $exactEntries = [
            'index' => 'erp',
            'spa' => 'erp',
            'acc-engine' => 'erp',
            'melk' => 'melk',
            'wholesale' => 'wholesale',
            'election' => 'election',
            'service' => 'service',
            'education' => 'education',
            'shop' => 'shop',
            'growth' => 'growth',
            'dev' => 'dev',
            'platform' => 'shared',
            'account' => 'shared',
            'api' => 'shared',
            'token' => 'shared',
        ];
        if (isset($exactEntries[$stem])) {
            return $exactEntries[$stem];
        }

        $needles = [
            'melk' => 'melk',
            'wholesale' => 'wholesale',
            'election' => 'election',
            'service' => 'service',
            'svc-' => 'service',
            'education' => 'education',
            'edu-' => 'education',
            'shop' => 'shop',
            'arag' => 'shop',
            'mizaj' => 'shop',
            'growth' => 'growth',
            'dev' => 'dev',
            'erp' => 'erp',
            'gis' => 'erp',
            'chat-widget' => 'shared',
            'landing' => 'erp',
            'home-' => 'erp',
            'fonts-local' => 'shared',
            'leaflet' => 'shared',
            'qrcode' => 'shared',
        ];
        foreach ($needles as $n => $portal) {
            if (str_contains($rel, $n) || str_contains($stem, rtrim($n, '-'))) {
                return $portal;
            }
        }
        if (str_starts_with($rel, 'docs/')) {
            return 'docs';
        }
        if (str_starts_with($rel, 'run-')) {
            return 'script';
        }
        if (str_starts_with($rel, 'includes/')) {
            return 'shared';
        }
        return 'shared';
    }

    public static function code_guess_kind(string $rel, bool $isSummary = false): string
    {
        $rel = str_replace('\\', '/', $rel);
        $base = basename($rel);
        $ext = strtolower(pathinfo($base, PATHINFO_EXTENSION));

        if ($isSummary || self::code_is_tiles_dir($base) || preg_match('#/tiles(_[^/]+)?/?$#', $rel)) {
            return 'vendor';
        }
        if (preg_match('#^run-.+\.php$#i', $base)) {
            return 'script';
        }
        if (str_starts_with($rel, 'docs/') || $ext === 'md') {
            return 'doc';
        }
        if (str_starts_with($rel, 'templates/')) {
            return 'template';
        }
        if (str_starts_with($rel, 'includes/controllers/')) {
            return 'controller';
        }
        if (str_starts_with($rel, 'includes/helpers/') || str_starts_with($rel, 'includes/')) {
            return 'helper';
        }
        if (str_starts_with($rel, 'assets/img/') || in_array($ext, ['jpg', 'jpeg', 'png', 'gif', 'webp', 'svg', 'ico', 'bmp'], true)) {
            if (str_starts_with($rel, 'assets/img/') || str_contains($rel, '/img/')) {
                return 'image';
            }
        }
        if (str_starts_with($rel, 'assets/fonts/') || in_array($ext, ['woff', 'woff2', 'ttf', 'otf', 'eot'], true)) {
            return 'font';
        }
        if (str_starts_with($rel, 'assets/vendor/')) {
            return 'vendor';
        }
        if ($ext === 'css' || str_starts_with($rel, 'assets/css/')) {
            return 'css';
        }
        if ($ext === 'js' || str_starts_with($rel, 'assets/js/') || str_starts_with($rel, 'assets/dist/') || str_contains($rel, 'vite')) {
            return 'js';
        }
        if ($ext === 'php' && !str_contains($rel, '/')) {
            return 'entry';
        }
        if ($ext === 'php') {
            return 'other';
        }
        return 'other';
    }

    public static function code_kind_label_fa(string $kind): string
    {
        $map = [
            'entry' => 'ورود پورتال',
            'template' => 'قالب',
            'css' => 'CSS',
            'js' => 'جاوااسکریپت',
            'image' => 'تصویر',
            'font' => 'فونت',
            'vendor' => 'Vendor',
            'helper' => 'Helper',
            'controller' => 'کنترلر',
            'doc' => 'مستند',
            'script' => 'اسکریپت نصب/seed',
            'other' => 'سایر',
        ];
        return $map[$kind] ?? $kind;
    }

    /** @return array{files:int,bytes:int} */
    public static function code_dir_stats(string $abs): array
    {
        $files = 0;
        $bytes = 0;
        if (!is_dir($abs)) {
            return ['files' => 0, 'bytes' => 0];
        }
        try {
            $it = new RecursiveIteratorIterator(
                new RecursiveDirectoryIterator($abs, FilesystemIterator::SKIP_DOTS | FilesystemIterator::FOLLOW_SYMLINKS),
                RecursiveIteratorIterator::SELF_FIRST
            );
            foreach ($it as $f) {
                /** @var SplFileInfo $f */
                if ($f->isFile()) {
                    $files++;
                    $bytes += (int) $f->getSize();
                }
            }
        } catch (Throwable $e) {
            // ignore unreadable trees
        }
        return ['files' => $files, 'bytes' => $bytes];
    }

    /**
     * Collect filesystem rows (not yet persisted).
     * @return list<array{portal_key:string,kind:string,path:string,size_bytes:int,mtime:?string,notes:string,critical_path:int,is_summary:int,file_count:int}>
     */
    public static function code_catalog_collect(): array
    {
        $root = self::root();
        $rows = [];
        $seen = [];

        $add = static function (array $row) use (&$rows, &$seen): void {
            $path = str_replace('\\', '/', (string) $row['path']);
            $path = ltrim($path, '/');
            if ($path === '' || isset($seen[$path])) {
                return;
            }
            $seen[$path] = true;
            $row['path'] = $path;
            $rows[] = $row;
        };

        $addFile = function (string $rel, string $notes = '') use ($root, $add): void {
            $rel = str_replace('\\', '/', ltrim($rel, '/\\'));
            $abs = $root . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, $rel);
            if (!is_file($abs)) {
                return;
            }
            $mtime = @filemtime($abs);
            $add([
                'portal_key'     => self::code_guess_portal($rel),
                'kind'           => self::code_guess_kind($rel, false),
                'path'           => $rel,
                'size_bytes'     => (int) (@filesize($abs) ?: 0),
                'mtime'          => $mtime ? date('Y-m-d H:i:s', $mtime) : null,
                'notes'          => $notes,
                'critical_path'  => 0,
                'is_summary'     => 0,
                'file_count'     => 1,
            ]);
        };

        $addSummary = function (string $rel, int $files, int $bytes, string $notes) use ($root, $add): void {
            $rel = str_replace('\\', '/', rtrim(ltrim($rel, '/\\'), '/'));
            $abs = $root . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, $rel);
            $mtime = is_dir($abs) ? (@filemtime($abs) ?: null) : null;
            $add([
                'portal_key'     => self::code_guess_portal($rel),
                'kind'           => 'vendor',
                'path'           => $rel . '/',
                'size_bytes'     => $bytes,
                'mtime'          => $mtime ? date('Y-m-d H:i:s', $mtime) : null,
                'notes'          => $notes,
                'critical_path'  => 0,
                'is_summary'     => 1,
                'file_count'     => $files,
            ]);
        };

        $skipNames = array_flip(self::code_catalog_skip_dir_names());

        $walk = function (string $relDir, ?callable $fileFilter = null) use ($root, $addFile, $addSummary, $skipNames, &$walk): void {
            $absDir = $root . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, $relDir);
            if (!is_dir($absDir)) {
                return;
            }
            $entries = @scandir($absDir);
            if ($entries === false) {
                return;
            }
            foreach ($entries as $name) {
                if ($name === '.' || $name === '..') {
                    continue;
                }
                if (isset($skipNames[$name])) {
                    continue;
                }
                $rel = ($relDir === '' ? $name : rtrim($relDir, '/') . '/' . $name);
                $abs = $absDir . DIRECTORY_SEPARATOR . $name;
                if (is_dir($abs)) {
                    if (self::code_is_tiles_dir($name)) {
                        $stats = self::code_dir_stats($abs);
                        $mb = round($stats['bytes'] / 1048576, 1);
                        $addSummary(
                            $rel,
                            $stats['files'],
                            $stats['bytes'],
                            sprintf('بسته کاشی نقشه — %d فایل / %s MB (فهرست جزئی رد شد)', $stats['files'], $mb)
                        );
                        continue;
                    }
                    $walk($rel, $fileFilter);
                    continue;
                }
                if (is_file($abs)) {
                    if ($fileFilter && !$fileFilter($rel, $name)) {
                        continue;
                    }
                    $addFile($rel);
                }
            }
        };

        // Portal entries: root *.php (skip run-* and install noise handled separately)
        foreach (glob($root . DIRECTORY_SEPARATOR . '*.php') ?: [] as $abs) {
            $base = basename($abs);
            if (str_starts_with($base, 'run-') || str_starts_with($base, '_')) {
                continue;
            }
            $addFile($base, 'فایل ورود ریشه پورتال');
        }

        $walk('templates');
        $walk('assets/css/base');
        // top-level applied CSS outside base
        foreach (glob($root . DIRECTORY_SEPARATOR . 'assets' . DIRECTORY_SEPARATOR . 'css' . DIRECTORY_SEPARATOR . '*.css') ?: [] as $abs) {
            $addFile('assets/css/' . basename($abs));
        }
        $walk('assets/js/views');
        $walk('assets/js/modules');
        $addFile('assets/js/app.js', 'ورود Vite / باندل سورس');
        $addFile('assets/dist/acc-app.js', 'باندل تولیدشده Vite');
        $addFile('vite-src/vite.config.js', 'پیکربندی Vite');
        $walk('assets/img');
        $walk('assets/fonts');
        $walk('assets/vendor');
        $walk('includes/controllers');
        $walk('includes/helpers');
        $walk('docs');

        foreach (glob($root . DIRECTORY_SEPARATOR . 'run-*.php') ?: [] as $abs) {
            $addFile(basename($abs), 'اسکریپت نصب / seed / مهاجرت');
        }

        // Critical-path from templates
        $critical = self::code_parse_critical_paths();
        foreach ($rows as &$r) {
            $norm = rtrim($r['path'], '/');
            if (isset($critical[$norm]) || isset($critical[$r['path']])) {
                $r['critical_path'] = 1;
                $portalHint = $critical[$norm] ?? $critical[$r['path']] ?? '';
                if ($portalHint !== '' && ($r['portal_key'] === 'shared' || $r['portal_key'] === 'general')) {
                    $r['portal_key'] = $portalHint;
                }
                if ($r['notes'] === '') {
                    $r['notes'] = 'مسیر critical first-load (از قالب)';
                }
            }
        }
        unset($r);

        // Enrich notes from handoff assets table when present
        try {
            $handoff = self::get_assets();
            $byPath = [];
            foreach ($handoff as $a) {
                $p = (string) ($a['path_or_name'] ?? '');
                $p = preg_replace('#^/acc-engine/#', '', $p) ?? $p;
                $p = preg_replace('#\?.*$#', '', $p) ?? $p;
                $p = str_replace('\\', '/', ltrim($p, '/'));
                if ($p === '') {
                    continue;
                }
                $note = trim((string) ($a['notes'] ?? ''));
                $sec = trim((string) ($a['source_section'] ?? ''));
                $blob = trim($note . ($sec !== '' ? ' ← ' . $sec : ''));
                if ($blob === '') {
                    continue;
                }
                if (!isset($byPath[$p]) || mb_strlen($blob) > mb_strlen($byPath[$p])) {
                    $byPath[$p] = mb_substr($blob, 0, 500);
                }
            }
            foreach ($rows as &$r) {
                $p = rtrim($r['path'], '/');
                if ($r['notes'] === '' && isset($byPath[$p])) {
                    $r['notes'] = $byPath[$p];
                } elseif ($r['notes'] === '' && isset($byPath[$r['path']])) {
                    $r['notes'] = $byPath[$r['path']];
                }
            }
            unset($r);
        } catch (Throwable $e) {
            // handoff table may be empty
        }

        return $rows;
    }

    /**
     * Parse link/script/preload tags from portal templates.
     * @return array<string,string> path => portal_key
     */
    public static function code_parse_critical_paths(): array
    {
        $root = self::root();
        $out = [];
        $files = array_merge(
            glob($root . DIRECTORY_SEPARATOR . 'templates' . DIRECTORY_SEPARATOR . '*-page.php') ?: [],
            glob($root . DIRECTORY_SEPARATOR . 'templates' . DIRECTORY_SEPARATOR . 'spa.php') ?: []
        );
        foreach ($files as $abs) {
            $base = basename($abs);
            $portal = 'shared';
            if ($base === 'spa.php') {
                $portal = 'erp';
            } elseif (preg_match('/^([a-z0-9]+)/i', $base, $m)) {
                $portal = self::code_guess_portal($m[1] . '.php');
            }
            $html = @file_get_contents($abs);
            if ($html === false || $html === '') {
                continue;
            }
            // Only head-ish first 120 lines for speed, but also catch defer scripts near end
            if (preg_match_all(
                '#(?:href|src)=["\']([^"\']+)["\']#i',
                $html,
                $matches
            )) {
                foreach ($matches[1] as $url) {
                    $url = html_entity_decode($url, ENT_QUOTES | ENT_HTML5, 'UTF-8');
                    if (!preg_match('#(?:/acc-engine/)?(assets/[^?\s\'"]+|tabulator-master/[^?\s\'"]+)#i', $url, $pm)) {
                        continue;
                    }
                    $path = str_replace('\\', '/', $pm[1]);
                    $path = preg_replace('#\?.*$#', '', $path) ?? $path;
                    if (!isset($out[$path])) {
                        $out[$path] = $portal;
                    }
                }
            }
            // Also mark the template itself as critical for that portal
            $tplRel = 'templates/' . $base;
            $out[$tplRel] = $portal;
        }
        return $out;
    }

    public static function code_catalog_scan(): array
    {
        global $wpdb;
        self::ensure_code_assets_table();
        $tbl = self::table('code_assets');
        $now = current_time('mysql');
        $rows = self::code_catalog_collect();

        $wpdb->query("TRUNCATE TABLE {$tbl}");

        $inserted = 0;
        foreach ($rows as $r) {
            $ok = $wpdb->insert($tbl, [
                'portal_key'    => $r['portal_key'],
                'kind'          => $r['kind'],
                'path'          => $r['path'],
                'size_bytes'    => (int) $r['size_bytes'],
                'mtime'         => $r['mtime'],
                'notes'         => mb_substr((string) $r['notes'], 0, 500),
                'critical_path' => (int) $r['critical_path'],
                'is_summary'    => (int) $r['is_summary'],
                'file_count'    => (int) $r['file_count'],
                'synced_at'     => $now,
            ]);
            if ($ok) {
                $inserted++;
            }
        }

        $stats = self::code_catalog_stats_from_rows($rows);

        return [
            'ok'        => true,
            'message'   => "کاتالوگ کد بازخوانی شد ({$inserted} مورد).",
            'count'     => $inserted,
            'synced_at' => $now,
            'stats'     => $stats,
        ];
    }

    /**
     * @param list<array> $rows
     * @return array{by_kind:array<string,array{count:int,size:int}>,total_count:int,total_size:int,total_size_ex_tiles:int,critical_count:int,synced_at:?string}
     */
    public static function code_catalog_stats_from_rows(array $rows): array
    {
        $byKind = [];
        $total = 0;
        $totalEx = 0;
        $critical = 0;
        $synced = null;
        foreach ($rows as $r) {
            $kind = (string) ($r['kind'] ?? 'other');
            if (!isset($byKind[$kind])) {
                $byKind[$kind] = ['count' => 0, 'size' => 0, 'label' => self::code_kind_label_fa($kind)];
            }
            $size = (int) ($r['size_bytes'] ?? 0);
            $byKind[$kind]['count']++;
            $byKind[$kind]['size'] += $size;
            $total += $size;
            $isTile = !empty($r['is_summary']) || str_contains((string) ($r['path'] ?? ''), '/tiles');
            if (!$isTile) {
                $totalEx += $size;
            }
            if (!empty($r['critical_path'])) {
                $critical++;
            }
            if (!empty($r['synced_at'])) {
                $synced = $r['synced_at'];
            }
        }
        return [
            'by_kind'              => $byKind,
            'total_count'          => count($rows),
            'total_size'           => $total,
            'total_size_ex_tiles'  => $totalEx,
            'critical_count'       => $critical,
            'synced_at'            => $synced,
        ];
    }

    public static function code_catalog_list(array $opts = []): array
    {
        global $wpdb;
        self::ensure_code_assets_table();
        $tbl = self::table('code_assets');

        $count = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tbl}");
        if ($count === 0) {
            $scan = self::code_catalog_scan();
            if (empty($scan['ok'])) {
                return $scan;
            }
        }

        $portal = (string) ($opts['portal'] ?? 'all');
        $kind = (string) ($opts['kind'] ?? 'all');
        $q = trim((string) ($opts['q'] ?? ''));
        $criticalOnly = !empty($opts['critical_only']);
        $excludeTiles = !empty($opts['exclude_tiles']);
        $sort = (string) ($opts['sort'] ?? 'path');

        $sql = "SELECT * FROM {$tbl} WHERE 1=1";
        $args = [];
        if ($portal !== '' && $portal !== 'all') {
            $sql .= ' AND portal_key = %s';
            $args[] = $portal;
        }
        if ($kind !== '' && $kind !== 'all') {
            $sql .= ' AND kind = %s';
            $args[] = $kind;
        }
        if ($q !== '') {
            $like = '%' . $wpdb->esc_like($q) . '%';
            $sql .= ' AND (path LIKE %s OR notes LIKE %s)';
            $args[] = $like;
            $args[] = $like;
        }
        if ($criticalOnly) {
            $sql .= ' AND critical_path = 1';
        }
        if ($excludeTiles) {
            $sql .= " AND is_summary = 0 AND path NOT LIKE %s AND path NOT LIKE %s";
            $args[] = '%/tiles/%';
            $args[] = '%/tiles_%';
        }

        $order = match ($sort) {
            'size' => 'size_bytes DESC, path ASC',
            'mtime' => 'mtime DESC, path ASC',
            'kind' => 'kind ASC, path ASC',
            'portal' => 'portal_key ASC, path ASC',
            default => 'path ASC',
        };
        $sql .= ' ORDER BY ' . $order;

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

        // Full stats (unfiltered) for bar
        $all = $wpdb->get_results("SELECT kind, path, size_bytes, critical_path, is_summary, synced_at FROM {$tbl}", ARRAY_A) ?: [];
        $stats = self::code_catalog_stats_from_rows($all);

        // Critical paths grouped by portal (from filtered or all)
        $critSql = "SELECT portal_key, path, size_bytes, kind FROM {$tbl} WHERE critical_path = 1";
        $critArgs = [];
        if ($portal !== '' && $portal !== 'all') {
            $critSql .= ' AND portal_key = %s';
            $critArgs[] = $portal;
        }
        $critSql .= ' ORDER BY portal_key, kind, path';
        $critical = $critArgs
            ? ($wpdb->get_results($wpdb->prepare($critSql, ...$critArgs), ARRAY_A) ?: [])
            : ($wpdb->get_results($critSql, ARRAY_A) ?: []);

        $portals = $wpdb->get_col("SELECT DISTINCT portal_key FROM {$tbl} ORDER BY portal_key") ?: [];
        $kinds = $wpdb->get_col("SELECT DISTINCT kind FROM {$tbl} ORDER BY kind") ?: [];

        foreach ($items as &$it) {
            $it['kind_label'] = self::code_kind_label_fa((string) $it['kind']);
            $it['critical_path'] = (int) $it['critical_path'];
            $it['is_summary'] = (int) $it['is_summary'];
            $it['size_bytes'] = (int) $it['size_bytes'];
            $it['file_count'] = (int) $it['file_count'];
        }
        unset($it);

        return [
            'ok'        => true,
            'items'     => $items,
            'stats'     => $stats,
            'critical'  => $critical,
            'portals'   => $portals,
            'kinds'     => array_map(static fn ($k) => [
                'key'   => $k,
                'label' => self::code_kind_label_fa((string) $k),
            ], $kinds),
            'handoff'   => self::HANDOFF_REL,
            'count'     => count($items),
        ];
    }

    public static function code_catalog_file(string $path): array
    {
        global $wpdb;
        self::ensure_code_assets_table();
        $path = str_replace('\\', '/', ltrim($path, '/\\'));
        if ($path === '' || str_contains($path, '..')) {
            return ['ok' => false, 'message' => 'مسیر نامعتبر'];
        }

        $tbl = self::table('code_assets');
        $row = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$tbl} WHERE path = %s OR path = %s LIMIT 1", $path, rtrim($path, '/') . '/'),
            ARRAY_A
        );

        $root = self::root();
        $abs = $root . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, rtrim($path, '/'));
        $isSummary = $row && (int) ($row['is_summary'] ?? 0) === 1;

        if (!$row) {
            // Allow ad-hoc detail for existing files not yet scanned
            if (!is_file($abs)) {
                return ['ok' => false, 'message' => 'فایل در کاتالوگ نیست'];
            }
            $row = [
                'id'            => 0,
                'portal_key'    => self::code_guess_portal($path),
                'kind'          => self::code_guess_kind($path),
                'path'          => $path,
                'size_bytes'    => (int) filesize($abs),
                'mtime'         => date('Y-m-d H:i:s', (int) filemtime($abs)),
                'notes'         => '',
                'critical_path' => 0,
                'is_summary'    => 0,
                'file_count'    => 1,
                'synced_at'     => null,
            ];
        }

        $row['kind_label'] = self::code_kind_label_fa((string) $row['kind']);
        $row['critical_path'] = (int) $row['critical_path'];
        $row['is_summary'] = (int) $row['is_summary'];
        $row['size_bytes'] = (int) $row['size_bytes'];

        $snippet = null;
        $snippetNote = null;
        $ext = strtolower(pathinfo(rtrim($path, '/'), PATHINFO_EXTENSION));
        $textExts = ['php', 'js', 'css', 'md', 'json', 'html', 'htm', 'txt', 'sql', 'svg'];
        if (!$isSummary && is_file($abs) && in_array($ext, $textExts, true)) {
            $size = (int) filesize($abs);
            if ($size > 512 * 1024) {
                $snippetNote = 'فایل بزرگ است — فقط متا نمایش داده می‌شود.';
            } else {
                $raw = @file_get_contents($abs, false, null, 0, 12000);
                if ($raw !== false) {
                    $lines = preg_split("/\r\n|\n|\r/", $raw) ?: [];
                    $lines = array_slice($lines, 0, 40);
                    $snippet = implode("\n", $lines);
                    if (count(preg_split("/\r\n|\n|\r/", $raw) ?: []) > 40 || $size > 12000) {
                        $snippetNote = 'نمایش ۴۰ خط اول (محدود)';
                    }
                }
            }
        } elseif ($isSummary) {
            $snippetNote = (string) ($row['notes'] ?? 'بسته خلاصه‌شده — محتوای باینری فهرست نشده');
        } elseif (in_array($ext, ['jpg', 'jpeg', 'png', 'gif', 'webp', 'woff', 'woff2', 'ttf', 'otf'], true)) {
            $snippetNote = 'فایل باینری — بدون پیش‌نمایش محتوا';
        }

        // Related handoff sections mentioning this path
        $handoffHits = [];
        try {
            $assets = self::get_assets();
            $needle = rtrim($path, '/');
            foreach ($assets as $a) {
                $p = preg_replace('#^/acc-engine/#', '', (string) ($a['path_or_name'] ?? '')) ?? '';
                $p = preg_replace('#\?.*$#', '', $p) ?? $p;
                $p = str_replace('\\', '/', ltrim($p, '/'));
                if ($p === $needle || str_contains($p, $needle) || str_contains($needle, $p)) {
                    $handoffHits[] = [
                        'portal'  => $a['portal'] ?? '',
                        'kind'    => $a['kind'] ?? '',
                        'notes'   => $a['notes'] ?? '',
                        'section' => $a['source_section'] ?? '',
                    ];
                    if (count($handoffHits) >= 8) {
                        break;
                    }
                }
            }
        } catch (Throwable $e) {
        }

        return [
            'ok'           => true,
            'file'         => $row,
            'snippet'      => $snippet,
            'snippet_note' => $snippetNote,
            'handoff'      => $handoffHits,
            'handoff_doc'  => self::HANDOFF_REL,
            'exists'       => is_file($abs) || is_dir($abs),
        ];
    }
}

if (!function_exists('wp_mkdir_p')) {
    function wp_mkdir_p(string $target): bool
    {
        if (is_dir($target)) {
            return true;
        }
        return mkdir($target, 0755, true);
    }
}

if (!function_exists('current_time')) {
    function current_time(string $type = 'mysql')
    {
        return $type === 'mysql' ? date('Y-m-d H:i:s') : time();
    }
}
