<?php
namespace App\Database;

use App\Utils\Logger;
use App\Utils\DateNormalizer;
use App\Security\RbacCatalog;
use PDO;
use PDOException;

/**
 * Database Migration Manager
 */
class Migration
{
    private $connection;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    /**
     * Run all migrations
     */
    public function run()
    {
        $pdo = $this->connection->getPdo();
        $statements = Schema::getCreateTableStatements();

        foreach ($statements as $sql) {
            try {
                $pdo->exec($sql);
                // Logger::logInfo("Table created/verified successfully");
            } catch (PDOException $e) {
                // Ignore table exists errors
                if (strpos($e->getMessage(), 'already exists') === false &&
                    strpos($e->getMessage(), 'Duplicate') === false) {
                    Logger::logError("DB Error creating table", $e, ['sql' => substr($sql, 0, 100)]);
                }
            }
        }

        // Run additional migrations for existing tables
        $this->migrateAdditionalColumns();
        $this->migrateCustomerAccessRequests();
        $this->migrateCommissionAssignmentValidityColumns();
        $this->repairCommissionRuleValidityDates();
        $this->repairCommissionLineBillingMonths();
        $this->repairOrphanBalanceCommissionLines();
        $this->repairPendingCommissionPayoutCycleBillingMonths();
        $this->repairRbacTables();

        // Seed default lookup values if empty
        $this->seedLookups();
        $this->seedOperationManagerDefaults();

        // Seed field configurations
        $this->seedFieldConfigurations();

        // Seed RBAC foundation (system roles + capability catalog + default grants)
        $this->seedRbacFoundation();

        // Migrate payments from JSON to transactions table
        $this->migratePaymentsToTransactions();
        $this->repairDealPaymentRegisteredAtSnapshots();
    }

    private function ensureIndex(string $table, string $indexName, string $columns): void
    {
        $pdo = $this->connection->getPdo();
        try {
            $stmt = $pdo->prepare(
                "SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = ? AND index_name = ?"
            );
            $stmt->execute([$table, $indexName]);
            if ((int)$stmt->fetchColumn() > 0) {
                return;
            }
            $normalizedExpected = array_values(array_filter(array_map('trim', explode(',', $columns))));
            if (!empty($normalizedExpected)) {
                $statsStmt = $pdo->prepare(
                    "SELECT index_name, column_name, seq_in_index
                     FROM information_schema.statistics
                     WHERE table_schema = DATABASE() AND table_name = ?
                     ORDER BY index_name, seq_in_index"
                );
                $statsStmt->execute([$table]);
                $indexColumns = [];
                foreach (($statsStmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $row) {
                    $name = (string)($row['index_name'] ?? '');
                    if ($name === '' || $name === 'PRIMARY') {
                        continue;
                    }
                    $columnName = trim((string)($row['column_name'] ?? ''));
                    if ($columnName === '') {
                        continue;
                    }
                    if (!isset($indexColumns[$name])) {
                        $indexColumns[$name] = [];
                    }
                    $indexColumns[$name][] = $columnName;
                }
                foreach ($indexColumns as $existingCols) {
                    if (array_values($existingCols) === $normalizedExpected) {
                        return;
                    }
                }
            }
            $pdo->exec("CREATE INDEX {$indexName} ON {$table} ({$columns})");
        } catch (PDOException $e) {
            Logger::logWarning("Failed to create index", [
                'table' => $table,
                'index' => $indexName,
                'error' => $e->getMessage()
            ]);
        }
    }

    private function tableExists(string $table): bool
    {
        $pdo = $this->connection->getPdo();
        try {
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = ?");
            $stmt->execute([$table]);
            return (int)$stmt->fetchColumn() > 0;
        } catch (PDOException $e) {
            return false;
        }
    }

    private function addColumnIfMissing(string $table, string $column, string $definition): void
    {
        if ($this->connection->hasColumn($table, $column)) {
            return;
        }
        try {
            $this->connection->getPdo()->exec("ALTER TABLE {$table} ADD COLUMN {$column} {$definition}");
        } catch (PDOException $e) {
            Logger::logWarning("Failed to add column", [
                'table' => $table,
                'column' => $column,
                'error' => $e->getMessage()
            ]);
        }
    }

    private function repairRbacTables(): void
    {
        $pdo = $this->connection->getPdo();
        try {
            if ($this->tableExists('users') && $this->connection->hasColumn('users', 'role')) {
                $pdo->exec("ALTER TABLE users MODIFY role VARCHAR(100) DEFAULT 'agent'");
            }
            if ($this->tableExists('rbac_role_data_scopes')) {
                $this->addColumnIfMissing('rbac_role_data_scopes', 'meta_json', 'TEXT NULL');
                $pdo->exec("ALTER TABLE rbac_role_data_scopes MODIFY scope_type ENUM('none','self','all','selected_users') NOT NULL DEFAULT 'self'");
            }
            if ($this->tableExists('rbac_user_data_scopes')) {
                $this->addColumnIfMissing('rbac_user_data_scopes', 'meta_json', 'TEXT NULL');
                $pdo->exec("ALTER TABLE rbac_user_data_scopes MODIFY scope_type ENUM('none','self','all','selected_users') NOT NULL DEFAULT 'self'");
            }
        } catch (PDOException $e) {
            Logger::logWarning("Failed to repair RBAC scope tables", ['error' => $e->getMessage()]);
        }
    }

    private function migrateCustomerAccessRequests(): void
    {
        if (!$this->tableExists('customer_access_requests')) {
            return;
        }
        $this->addColumnIfMissing(
            'customer_access_requests',
            'request_type',
            "ENUM('grant','revoke') NOT NULL DEFAULT 'grant' AFTER contact_didar_id"
        );
        $this->ensureIndex('customer_access_requests', 'idx_car_request_type', 'request_type');
    }

    /**
     * Migrate additional columns that might be missing
     */
    private function migrateAdditionalColumns()
    {
        $pdo = $this->connection->getPdo();
        
        // Additional columns for persons table
        $personsColumns = [
            'work_phone' => 'VARCHAR(50)',
            'secondary_mobile_phone' => 'VARCHAR(50)',
            'mobile_phone_3' => 'VARCHAR(50)',
            'mobile_phone_4' => 'VARCHAR(50)',
            'city' => 'VARCHAR(100)',
            'job_title' => 'VARCHAR(255)',
            'job_description' => 'TEXT',
            'acquaintance_duration' => 'VARCHAR(255)',
            'extra_info' => 'TEXT',
            'financial_level' => 'VARCHAR(50)',
            'asset_estimation' => 'VARCHAR(50)',
            'income_estimation' => 'VARCHAR(50)',
            'requested_services' => 'TEXT',
            'acquaintance_source' => 'VARCHAR(255)',
            'acquaintance_detail' => 'TEXT',
            'content_topic' => 'TEXT',
            'created_by_didar_id' => 'VARCHAR(255)',
            'created_by_name' => 'VARCHAR(255)',
            'created_via' => 'VARCHAR(50)',
            'national_id' => 'VARCHAR(50)',
            'customer_products' => 'TEXT',
            'background_info' => 'TEXT',
            'previous_owner_id' => 'VARCHAR(255)',
            'previous_owner_history' => 'TEXT',
            'customer_code' => 'VARCHAR(32)',
            'sale_status' => 'TINYINT(1) DEFAULT 0',
            'register_time_jalali' => 'VARCHAR(50)',
            'lead_pool_id' => 'INT NULL',
            'pool_entered_at' => 'DATETIME NULL',
            'pool_reason' => 'VARCHAR(255) NULL',
        ];

        foreach ($personsColumns as $column => $type) {
            if (!$this->connection->hasColumn('persons', $column)) {
                try {
                    $pdo->exec("ALTER TABLE persons ADD COLUMN $column $type");
                    Logger::logInfo("Added column to persons table", ['column' => $column]);
                } catch (PDOException $e) {
                    Logger::logWarning("Failed to add column", ['column' => $column, 'error' => $e->getMessage()]);
                }
            }
        }
        if ($this->connection->hasColumn('persons', 'created_via')) {
            try {
                $pdo->exec("UPDATE persons SET created_via = 'legacy' WHERE created_via IS NULL OR created_via = ''");
            } catch (PDOException $e) {
                Logger::logWarning("Failed to backfill persons.created_via", ['error' => $e->getMessage()]);
            }
        }

        // Additional columns for deals table
        $dealsColumns = [
	            'final_price' => 'DECIMAL(15,2)',
	            'is_paid' => 'TINYINT(1) DEFAULT 0',
	            'payment_short_link' => 'TEXT',
	            'requested_services' => 'TEXT',
	            'service_name' => 'VARCHAR(255)',
	            'service_cost' => 'DECIMAL(15,2)',
	            'price_list_code' => 'VARCHAR(255)',
                'invoice_code' => 'VARCHAR(32)',
	            'has_discount' => 'TINYINT(1) DEFAULT 0',
	            'discount_type' => 'VARCHAR(100)',
	            'discount_occasion' => 'VARCHAR(255)',
	            'discount_amount' => 'DECIMAL(15,2) DEFAULT 0',
	            'refund_amount' => 'DECIMAL(15,2) DEFAULT 0',
	            'refund_description' => 'TEXT',
	            'payable_amount' => 'DECIMAL(15,2)',
	            'payment_conditions' => 'TEXT',
	            'payment_amount' => 'DECIMAL(15,2) DEFAULT 0',
            'payment_method' => 'VARCHAR(100)',
            'payment_description' => 'TEXT',
            'payments' => 'TEXT',
            'acquaintance_source' => 'VARCHAR(255)',
            'acquaintance_detail' => 'TEXT',
            'content_topic' => 'TEXT',
            'created_via' => 'VARCHAR(50)',
            'is_legacy_didar' => 'TINYINT(1) DEFAULT 0',
            'pipeline_stage' => 'VARCHAR(100)',
            'pipeline_bucket' => "VARCHAR(20) NOT NULL DEFAULT 'active'",
            'parked_from_stage' => 'VARCHAR(100) NULL',
            'parked_at' => 'DATETIME NULL',
            'parked_by_didar_id' => 'VARCHAR(255) NULL',
            'parked_reason' => 'TEXT NULL',
            'park_count' => 'INT NOT NULL DEFAULT 0',
	            'failure_reason_code' => 'VARCHAR(50)',
	            'failure_reason_text' => 'VARCHAR(255)',
	            'failure_reason_description' => 'TEXT',
	            'city' => 'VARCHAR(100)',
	            'job_title' => 'VARCHAR(255)',
	            'job_description' => 'TEXT',
	            'acquaintance_duration' => 'VARCHAR(255)',
	            'extra_info' => 'TEXT',
            'financial_level' => 'VARCHAR(50)',
            'asset_estimation' => 'VARCHAR(50)',
            'income_estimation' => 'VARCHAR(50)',
	            'has_previous_purchase' => 'TINYINT(1) NULL',
	            'person_id' => 'INT(11) NULL',
	        ];

        foreach ($dealsColumns as $column => $type) {
            if (!$this->connection->hasColumn('deals', $column)) {
                try {
                    $pdo->exec("ALTER TABLE deals ADD COLUMN $column $type");
                    Logger::logInfo("Added column to deals table", ['column' => $column]);
                } catch (PDOException $e) {
                    Logger::logWarning("Failed to add column", ['column' => $column, 'error' => $e->getMessage()]);
                }
            }
        }
        if ($this->connection->hasColumn('deals', 'created_via')) {
            try {
                $pdo->exec("UPDATE deals SET created_via = 'legacy' WHERE created_via IS NULL OR created_via = ''");
            } catch (PDOException $e) {
                Logger::logWarning("Failed to backfill deals.created_via", ['error' => $e->getMessage()]);
            }
        }

        // Additional columns for sync_log table
        if (!$this->connection->hasColumn('sync_log', 'http_code')) {
            try {
                $pdo->exec("ALTER TABLE sync_log ADD COLUMN http_code INT");
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add http_code column", ['error' => $e->getMessage()]);
            }
        }

        if (!$this->connection->hasColumn('sync_log', 'response_data')) {
            try {
                $pdo->exec("ALTER TABLE sync_log ADD COLUMN response_data LONGTEXT");
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add response_data column", ['error' => $e->getMessage()]);
            }
        }

        // Additional columns for audit_logs table
        if ($this->connection->hasColumn('audit_logs', 'id')) {
            $auditColumns = [
                'actor_name' => 'VARCHAR(255) NULL',
                'actor_ip' => 'VARCHAR(100) NULL',
                'related_person_didar_id' => 'VARCHAR(255) NULL',
            ];
            foreach ($auditColumns as $column => $type) {
                if (!$this->connection->hasColumn('audit_logs', $column)) {
                    try {
                        $pdo->exec("ALTER TABLE audit_logs ADD COLUMN $column $type");
                        Logger::logInfo("Added column to audit_logs table", ['column' => $column]);
                    } catch (PDOException $e) {
                        Logger::logWarning("Failed to add column", ['table' => 'audit_logs', 'column' => $column, 'error' => $e->getMessage()]);
                    }
                }
            }
            $this->ensureIndex('audit_logs', 'idx_action_created', 'action, created_at');
            $this->ensureIndex('audit_logs', 'idx_entity_created', 'entity_type, created_at');
        }

	        // Additional columns for field_configurations
        $fcColumns = [
            'help_text' => 'TEXT',
            'default_value' => 'VARCHAR(255)',
            'validation_pattern' => 'VARCHAR(255)',
            'validation_message' => 'VARCHAR(255)',
            'conditional_logic' => 'TEXT',
            'group_name' => 'VARCHAR(100)',
            'module_name' => 'VARCHAR(100)',
            'is_editable_after_create' => 'TINYINT(1) DEFAULT 1',
            'delete_allow_roles' => 'VARCHAR(500)',
            'view_allow_roles' => 'VARCHAR(500)',
        ];

        foreach ($fcColumns as $column => $type) {
            if (!$this->connection->hasColumn('field_configurations', $column)) {
                try {
                    $pdo->exec("ALTER TABLE field_configurations ADD COLUMN $column $type");
                    Logger::logInfo("Added column to field_configurations", ['column' => $column]);
                } catch (PDOException $e) {
                    Logger::logWarning("Failed to add column to field_configurations", ['column' => $column, 'error' => $e->getMessage()]);
                }
            }
        }
        // PERF (phase 2): columns may have just been added by the loops above; refresh the
        // cached column metadata so the hasColumn()-guarded index creation below sees them.
        // (Without this, a stale per-process column set silently skips brand-new indexes.)
        Connection::flushColumnCache();

        if ($this->connection->hasColumn('deals', 'invoice_code')) {
            $this->ensureIndex('deals', 'idx_invoice_code', 'invoice_code');
        }
        if ($this->connection->hasColumn('deals', 'pipeline_bucket')) {
            try {
                $pdo->exec("UPDATE deals SET pipeline_bucket = 'active' WHERE pipeline_bucket IS NULL OR pipeline_bucket = ''");
            } catch (PDOException $e) {
                Logger::logWarning("Failed to backfill deals.pipeline_bucket", ['error' => $e->getMessage()]);
            }
            $this->ensureIndex('deals', 'idx_deals_pipeline_bucket', 'pipeline_bucket');
        }
        if ($this->connection->hasColumn('deals', 'parked_from_stage')) {
            $this->ensureIndex('deals', 'idx_deals_parked_from_stage', 'parked_from_stage');
        }

        // PERF (phase 2): backfill the legacy flag so the bare predicate stays index-usable.
        if ($this->connection->hasColumn('deals', 'is_legacy_didar')) {
            try {
                $pdo->exec("UPDATE deals SET is_legacy_didar = 0 WHERE is_legacy_didar IS NULL");
            } catch (PDOException $e) {
                Logger::logWarning("Failed to backfill deals.is_legacy_didar", ['error' => $e->getMessage()]);
            }
        }

        // PERF (phase 2): composite indexes for the dashboard aggregates and deals list.
        $this->ensureIndex('deals', 'idx_deals_owner_status_stage', 'owner_didar_id, status, pipeline_stage');
        if ($this->connection->hasColumn('deals', 'parked_from_stage')) {
            $this->ensureIndex('deals', 'idx_deals_parked_status', 'parked_from_stage, status');
        }
        $this->ensureIndex('deals', 'idx_deals_regtime_id', 'register_time, didar_deal_id');

        // PERF (phase 2): dashboard persons counts (owner + sale_status) and leads filters.
        $this->ensureIndex('persons', 'idx_persons_owner_sale', 'owner_didar_id, sale_status');
        if ($this->connection->hasColumn('persons', 'customer_level')) {
            $this->ensureIndex('persons', 'idx_persons_customer_level', 'customer_level');
        }

        // PERF (phase 2): de-correlate cost of last-activity lookups (leads list + deals
        // activity-date filter) by making the MAX(register_date) per-key an index seek.
        if ($this->tableExists('activities')) {
            $this->ensureIndex('activities', 'idx_act_contact_regdate', 'contact_didar_id, register_date');
            $this->ensureIndex('activities', 'idx_act_deal_regdate', 'deal_didar_id, register_date');
            // Performance dashboard groups activities by owner + date; composite seek for it.
            $this->ensureIndex('activities', 'idx_act_owner_regdate', 'owner_didar_id, register_date');
        }

        // PERF (phase 2b): owner+date grouped scans on the performance dashboard (persons),
        // product filter EXISTS lookups (deal_products), and KPI target upserts/reads.
        $this->ensureIndex('persons', 'idx_persons_owner_regtime', 'owner_didar_id, register_time');
        if ($this->tableExists('deal_products')) {
            $this->ensureIndex('deal_products', 'idx_dp_deal_product', 'deal_id, product_id');
        }
        if ($this->tableExists('kpi_targets')) {
            $this->ensureIndex('kpi_targets', 'idx_kpi_targets_user_period', 'user_id, year, month');
        }

        // NOTE: deal-creator resolution in findByDidarId (ORDER BY id LIMIT 1 over
        // audit_logs/field_audit_logs by entity_type+entity_id) is already served by the
        // existing idx_entity(entity_type, entity_id) + InnoDB's implicit PK suffix, so no
        // extra audit index is added here (verified via EXPLAIN: ref idx_entity, rows=1).

        // Additional columns for products table
        if ($this->connection->hasColumn('products', 'id') && !$this->connection->hasColumn('products', 'service_lookup_item_id')) {
            try {
                $pdo->exec("ALTER TABLE products ADD COLUMN service_lookup_item_id INT NULL");
                Logger::logInfo("Added column to products table", ['column' => 'service_lookup_item_id']);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", ['table' => 'products', 'column' => 'service_lookup_item_id', 'error' => $e->getMessage()]);
            }
        }
        if ($this->connection->hasColumn('products', 'service_lookup_item_id')) {
            $this->ensureIndex('products', 'idx_service_lookup_item_id', 'service_lookup_item_id');
            $this->backfillProductServiceLinks();
        }
        if ($this->connection->hasColumn('products', 'id')) {
            $this->addColumnIfMissing('products', 'is_deleted', 'TINYINT(1) DEFAULT 0 AFTER service_lookup_item_id');
            if ($this->connection->hasColumn('products', 'is_deleted')) {
                $this->ensureIndex('products', 'idx_products_deleted', 'is_deleted');
            }
        }

        if ($this->connection->hasColumn('lookup_items', 'id')) {
            $this->addColumnIfMissing('lookup_items', 'is_visible', 'TINYINT(1) DEFAULT 1 AFTER is_active');
            if ($this->connection->hasColumn('lookup_items', 'is_visible')) {
                $this->ensureIndex('lookup_items', 'idx_lookup_items_visible', 'is_visible');
            }
        }

        if ($this->connection->hasColumn('deal_products', 'id')) {
            $this->addColumnIfMissing('deal_products', 'product_name_snapshot', 'VARCHAR(255) NULL AFTER product_id');
            $this->addColumnIfMissing('deal_products', 'service_lookup_item_id_snapshot', 'INT NULL AFTER product_name_snapshot');
            $this->addColumnIfMissing('deal_products', 'service_title_snapshot', 'VARCHAR(255) NULL AFTER service_lookup_item_id_snapshot');
            $this->addColumnIfMissing('deal_products', 'unit_price_snapshot', 'DECIMAL(15,2) DEFAULT 0 AFTER total');
            if ($this->connection->hasColumn('deal_products', 'service_lookup_item_id_snapshot')) {
                $this->ensureIndex('deal_products', 'idx_service_lookup_item_id_snapshot', 'service_lookup_item_id_snapshot');
            }
            $this->backfillDealProductSnapshots();
        }

        // Additional columns for transactions table
        if ($this->connection->hasColumn('transactions', 'id') && !$this->connection->hasColumn('transactions', 'invoice_code')) {
            try {
                $pdo->exec("ALTER TABLE transactions ADD COLUMN invoice_code VARCHAR(32) NULL");
                Logger::logInfo("Added column to transactions table", ['column' => 'invoice_code']);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", ['table' => 'transactions', 'column' => 'invoice_code', 'error' => $e->getMessage()]);
            }
        }
        if ($this->connection->hasColumn('transactions', 'invoice_code')) {
            $this->ensureIndex('transactions', 'idx_invoice_code', 'invoice_code');
        }
        if ($this->connection->hasColumn('transactions', 'id') && !$this->connection->hasColumn('transactions', 'check_owner_name')) {
            try {
                $pdo->exec("ALTER TABLE transactions ADD COLUMN check_owner_name VARCHAR(255) NULL AFTER destination_bank");
                Logger::logInfo("Added column to transactions table", ['column' => 'check_owner_name']);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", ['table' => 'transactions', 'column' => 'check_owner_name', 'error' => $e->getMessage()]);
            }
        }
        if ($this->connection->hasColumn('transactions', 'id') && !$this->connection->hasColumn('transactions', 'tracking_code_last4')) {
            try {
                $pdo->exec("ALTER TABLE transactions ADD COLUMN tracking_code_last4 VARCHAR(4) NULL AFTER destination_bank");
                Logger::logInfo("Added column to transactions table", ['column' => 'tracking_code_last4']);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", ['table' => 'transactions', 'column' => 'tracking_code_last4', 'error' => $e->getMessage()]);
            }
        }

        // S0 sprint 2026-05-31: structured check/payment columns for card-to-card "Other",
        // deposit/cheque dates, cheque details, image uploads, and the financial cheque workflow.
        if ($this->connection->hasColumn('transactions', 'id')) {
            $transactionsColumns = [
                'destination_card_number' => "VARCHAR(20) NULL AFTER destination_bank",
                'destination_bank_title'  => "VARCHAR(255) NULL AFTER destination_card_number",
                'destination_card_full'   => "VARCHAR(32) NULL AFTER destination_bank_title",
                'destination_account_number' => "VARCHAR(64) NULL AFTER destination_card_full",
                'destination_sheba'       => "VARCHAR(34) NULL AFTER destination_account_number",
                'destination_tafsili_code' => "VARCHAR(32) NULL AFTER destination_sheba",
                'destination_account_owner' => "VARCHAR(255) NULL AFTER destination_sheba",
                'account_owner_name'      => "VARCHAR(255) NULL AFTER destination_account_owner",
                'depositor_name'          => "VARCHAR(255) NULL AFTER account_owner_name",
                'deposit_date'            => "DATE NULL AFTER tracking_code_last4",
                'payment_screen_at'       => "DATETIME NULL AFTER payment_time",
                'payment_screen_date'     => "DATE NULL AFTER payment_screen_at",
                'is_cheque_payment'       => "TINYINT(1) NOT NULL DEFAULT 0 AFTER method",
                'check_bank_name'         => "VARCHAR(255) NULL AFTER check_owner_name",
                'check_number'            => "VARCHAR(50) NULL AFTER check_bank_name",
                'check_date'              => "DATE NULL AFTER check_number",
                'check_due_date'          => "DATE NULL AFTER check_date",
                'sayadi_date'             => "VARCHAR(20) NULL AFTER check_due_date",
                'check_delivery_date'     => "DATE NULL AFTER sayadi_date",
                'check_recipient_name'    => "VARCHAR(255) NULL AFTER check_delivery_date",
                'receipt_image_path'      => "VARCHAR(500) NULL AFTER check_recipient_name",
                'check_image_path'        => "VARCHAR(500) NULL AFTER receipt_image_path",
                'payment_sequence'        => "INT NULL AFTER check_image_path",
                'payment_source_hash'     => "CHAR(64) NULL AFTER payment_sequence",
                'payment_record_id'       => "BIGINT NULL AFTER payment_source_hash",
                'payment_uuid'            => "CHAR(36) NULL AFTER payment_record_id",
                'payment_owner_user_id'   => "INT NULL AFTER payment_uuid",
                'payment_owner_didar_id'  => "VARCHAR(255) NULL AFTER payment_owner_user_id",
                'payment_owner_name_snapshot' => "VARCHAR(255) NULL AFTER payment_owner_didar_id",
                'payment_owner_locked_at' => "DATETIME NULL AFTER payment_owner_name_snapshot",
                'commission_status'       => "VARCHAR(50) DEFAULT 'PENDING' AFTER payment_owner_locked_at",
                'commission_effective_date' => "DATE NULL AFTER commission_status",
                'registered_by_didar_id'  => "VARCHAR(255) NULL AFTER commission_effective_date",
                'registered_by_name'      => "VARCHAR(255) NULL AFTER registered_by_didar_id",
                'refund_amount'           => "DECIMAL(15,2) DEFAULT 0 AFTER registered_by_name",
                'refund_count'            => "INT DEFAULT 0 AFTER refund_amount",
                'refund_description'      => "TEXT NULL AFTER refund_count",
                'check_status'            => "ENUM('pending','approved','rejected') DEFAULT 'pending' AFTER check_image_path",
                'check_status_note'       => "TEXT NULL AFTER check_status",
                'check_status_at'         => "DATETIME NULL AFTER check_status_note",
                'check_status_by'         => "VARCHAR(255) NULL AFTER check_status_at",
                'collection_status'       => "ENUM('pending','collected','returned','blataklif') DEFAULT 'pending' AFTER check_status_by",
                'collection_date'         => "DATE NULL AFTER collection_status",
            ];
            foreach ($transactionsColumns as $column => $definition) {
                $this->addColumnIfMissing('transactions', $column, $definition);
            }
            if ($this->connection->hasColumn('transactions', 'check_status')) {
                $this->ensureIndex('transactions', 'idx_check_status', 'check_status');
            }
            if ($this->connection->hasColumn('transactions', 'collection_status')) {
                $this->ensureIndex('transactions', 'idx_collection_status', 'collection_status');
            }
            if ($this->connection->hasColumn('transactions', 'payment_source_hash')) {
                $this->ensureIndex('transactions', 'idx_transaction_payment_source_hash', 'payment_source_hash');
            }
            if ($this->connection->hasColumn('transactions', 'payment_record_id')) {
                $this->ensureIndex('transactions', 'idx_transaction_payment_record_id', 'payment_record_id');
            }
            if ($this->connection->hasColumn('transactions', 'payment_uuid')) {
                $this->ensureIndex('transactions', 'idx_transaction_payment_uuid', 'payment_uuid');
            }
            if ($this->connection->hasColumn('transactions', 'payment_owner_user_id')) {
                $this->ensureIndex('transactions', 'idx_transaction_payment_owner_user_id', 'payment_owner_user_id');
            }
            if ($this->connection->hasColumn('transactions', 'commission_status')) {
                $this->ensureIndex('transactions', 'idx_transaction_commission_status', 'commission_status');
            }
            if ($this->connection->hasColumn('transactions', 'registered_by_didar_id')) {
                $this->ensureIndex('transactions', 'idx_transaction_registered_by_didar_id', 'registered_by_didar_id');
            }
            if ($this->connection->hasColumn('transactions', 'destination_tafsili_code')) {
                $this->ensureIndex('transactions', 'idx_transaction_destination_tafsili', 'destination_tafsili_code');
            }
            if ($this->connection->hasColumn('transactions', 'payment_screen_at') && $this->connection->hasColumn('transactions', 'is_cheque_payment')) {
                $this->ensureIndex('transactions', 'idx_transaction_financial_list', 'is_cheque_payment, payment_screen_at, id');
            }
            if ($this->connection->hasColumn('transactions', 'payment_screen_date') && $this->connection->hasColumn('transactions', 'is_cheque_payment')) {
                $this->ensureIndex('transactions', 'idx_transaction_financial_date', 'is_cheque_payment, payment_screen_date, id');
            }
            if ($this->connection->hasColumn('transactions', 'check_due_date') && $this->connection->hasColumn('transactions', 'is_cheque_payment')) {
                $this->ensureIndex('transactions', 'idx_transaction_cheque_due', 'is_cheque_payment, check_due_date, check_date, id');
            }
            $this->backfillTransactionFinancialCacheColumns($pdo);
        }

        if ($this->tableExists('payment_records')) {
            $paymentRecordColumns = [
                'supervisor_user_id_snapshot' => 'INT NULL AFTER payment_owner_source',
                'manager_user_id_snapshot' => 'INT NULL AFTER supervisor_user_id_snapshot',
                'hierarchy_snapshot_at' => 'DATE NULL AFTER manager_user_id_snapshot',
            ];
            foreach ($paymentRecordColumns as $column => $definition) {
                $this->addColumnIfMissing('payment_records', $column, $definition);
            }
            if ($this->connection->hasColumn('payment_records', 'supervisor_user_id_snapshot')) {
                $this->ensureIndex('payment_records', 'idx_payment_record_supervisor', 'supervisor_user_id_snapshot');
            }
            if ($this->connection->hasColumn('payment_records', 'manager_user_id_snapshot')) {
                $this->ensureIndex('payment_records', 'idx_payment_record_manager', 'manager_user_id_snapshot');
            }
            $this->repairSudranPaymentRecordCommissionStatus($pdo);
        }

        if ($this->tableExists('user_hierarchy_assignments')) {
            $this->addColumnIfMissing(
                'user_hierarchy_assignments',
                'supervisor_commission_rate_percent',
                'DECIMAL(7,4) NULL AFTER manager_user_id'
            );
            $this->addColumnIfMissing(
                'user_hierarchy_assignments',
                'manager_commission_rate_percent',
                'DECIMAL(7,4) NULL AFTER supervisor_commission_rate_percent'
            );
        }

        if ($this->tableExists('commission_lines') && $this->connection->hasColumn('commission_lines', 'beneficiary_type')) {
            try {
                $pdo->exec("ALTER TABLE commission_lines MODIFY beneficiary_type ENUM('DIRECT_PAYMENT_OWNER','SUPERVISOR_OF_PAYMENT_OWNER','MANAGER_OF_PAYMENT_OWNER','MANAGER_OF_SUPERVISOR_COMMISSION','CLAWBACK') NOT NULL DEFAULT 'DIRECT_PAYMENT_OWNER'");
            } catch (\Throwable $e) {
                // Keep migrations idempotent on databases that already use a wider compatible type.
            }
        }

        if ($this->tableExists('deal_commissions')) {
            $this->addColumnIfMissing('deal_commissions', 'payment_record_id', 'BIGINT NULL AFTER deal_payment_id');
            try {
                $stmt = $pdo->prepare(
                    "SELECT COUNT(*) AS col_count,
                            SUM(CASE WHEN column_name = 'deal_didar_id' THEN 1 ELSE 0 END) AS deal_col_count,
                            MAX(non_unique) AS non_unique
                     FROM information_schema.statistics
                     WHERE table_schema = DATABASE()
                       AND table_name = 'deal_commissions'
                       AND index_name = 'uniq_deal_commission'"
                );
                $stmt->execute();
                $idx = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
                if ((int)($idx['col_count'] ?? 0) === 1 && (int)($idx['deal_col_count'] ?? 0) === 1 && (int)($idx['non_unique'] ?? 1) === 0) {
                    $pdo->exec('ALTER TABLE deal_commissions DROP INDEX uniq_deal_commission');
                }
            } catch (PDOException $e) {
                Logger::logWarning('Failed to drop legacy deal_commissions deal-level unique index', ['error' => $e->getMessage()]);
            }
            if ($this->connection->hasColumn('deal_commissions', 'payment_record_id')) {
                try {
                    $stmt = $pdo->prepare(
                        "SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'deal_commissions' AND index_name = 'uniq_deal_commission_payment_record'"
                    );
                    $stmt->execute();
                    if ((int)$stmt->fetchColumn() === 0) {
                        $pdo->exec('CREATE UNIQUE INDEX uniq_deal_commission_payment_record ON deal_commissions (payment_record_id)');
                    }
                } catch (PDOException $e) {
                    Logger::logWarning('Failed to create deal_commissions.payment_record_id unique index', ['error' => $e->getMessage()]);
                }
            }
        }

        if ($this->tableExists('commission_payout_cycles')) {
            try {
                $pdo->exec("
                    ALTER TABLE commission_payout_cycles
                    MODIFY status ENUM(
                        'PENDING_APPROVAL',
                        'APPROVED',
                        'OVERRIDDEN',
                        'REJECTED',
                        'FINANCE_APPROVED',
                        'READY_FOR_PAYOUT',
                        'PAID',
                        'SETTLED',
                        'TRANSFERRED_TO_PAYROLL'
                    ) NOT NULL DEFAULT 'PENDING_APPROVAL'
                ");
            } catch (PDOException $e) {
                Logger::logWarning('Failed to extend commission_payout_cycles.status enum', ['error' => $e->getMessage()]);
            }
        }

        if (!$this->tableExists('commission_payout_cycle_lines')) {
            try {
                $pdo->exec("
                    CREATE TABLE IF NOT EXISTS commission_payout_cycle_lines (
                        id BIGINT PRIMARY KEY AUTO_INCREMENT,
                        payout_cycle_id INT NOT NULL,
                        commission_line_id BIGINT NOT NULL,
                        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                        UNIQUE KEY uniq_cycle_line (payout_cycle_id, commission_line_id),
                        UNIQUE KEY uniq_commission_line_payout (commission_line_id),
                        INDEX idx_payout_cycle_lines_cycle (payout_cycle_id),
                        INDEX idx_payout_cycle_lines_line (commission_line_id),
                        FOREIGN KEY (payout_cycle_id) REFERENCES commission_payout_cycles(id) ON DELETE CASCADE ON UPDATE CASCADE,
                        FOREIGN KEY (commission_line_id) REFERENCES commission_lines(id) ON DELETE CASCADE ON UPDATE CASCADE
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                ");
            } catch (PDOException $e) {
                Logger::logWarning('Failed to create commission_payout_cycle_lines table', ['error' => $e->getMessage()]);
            }
        } else {
            $this->ensureIndex('commission_payout_cycle_lines', 'idx_payout_cycle_lines_cycle', 'payout_cycle_id');
            $this->ensureIndex('commission_payout_cycle_lines', 'idx_payout_cycle_lines_line', 'commission_line_id');
            try {
                $stmt = $pdo->prepare(
                    "SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'commission_payout_cycle_lines' AND index_name = 'uniq_commission_line_payout'"
                );
                $stmt->execute();
                if ((int)$stmt->fetchColumn() === 0) {
                    $pdo->exec('ALTER TABLE commission_payout_cycle_lines ADD UNIQUE KEY uniq_commission_line_payout (commission_line_id)');
                }
            } catch (PDOException $e) {
                Logger::logWarning('Failed to add commission_payout_cycle_lines commission_line unique key', ['error' => $e->getMessage()]);
            }
        }

        // C9 fix: re-key cheque_reviews on the stable transactions.id instead of
        // (deal_id, check_number). Synced cheques almost always have a NULL
        // check_number, which made the old key unaddressable and broke status
        // round-tripping (the review never re-joined to its transaction row).
        if ($this->tableExists('cheque_reviews')) {
            $this->addColumnIfMissing('cheque_reviews', 'transaction_id', 'BIGINT NULL AFTER id');
            // check_number is no longer required as a key; relax NOT NULL if present.
            if ($this->connection->hasColumn('cheque_reviews', 'check_number')) {
                try {
                    $pdo->exec("ALTER TABLE cheque_reviews MODIFY check_number VARCHAR(50) NULL");
                } catch (PDOException $e) {
                    Logger::logWarning('Failed to relax cheque_reviews.check_number', ['error' => $e->getMessage()]);
                }
            }
            // Backfill transaction_id for legacy rows that were keyed by check_number.
            if ($this->connection->hasColumn('cheque_reviews', 'transaction_id')) {
                try {
                    $pdo->exec("
                        UPDATE cheque_reviews cr
                        JOIN transactions t
                          ON t.deal_id = cr.deal_id
                         AND t.check_number = cr.check_number
                         AND t.method IN ('check','cheque','چک')
                        SET cr.transaction_id = t.id
                        WHERE cr.transaction_id IS NULL AND cr.check_number IS NOT NULL AND cr.check_number <> ''
                    ");
                } catch (PDOException $e) {
                    Logger::logWarning('Failed to backfill cheque_reviews.transaction_id', ['error' => $e->getMessage()]);
                }
                // Drop the now-obsolete (deal_id, check_number) unique key and add
                // the transaction_id unique key that ON DUPLICATE KEY relies on.
                try {
                    $hasOld = $pdo->query("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'cheque_reviews' AND index_name = 'uniq_cheque_review'")->fetchColumn();
                    if ((int)$hasOld > 0) {
                        $pdo->exec("ALTER TABLE cheque_reviews DROP INDEX uniq_cheque_review");
                    }
                } catch (PDOException $e) {
                    Logger::logWarning('Failed to drop legacy cheque_reviews unique key', ['error' => $e->getMessage()]);
                }
                try {
                    $hasNew = $pdo->query("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'cheque_reviews' AND index_name = 'uniq_cheque_review_txn'")->fetchColumn();
                    if ((int)$hasNew === 0) {
                        $pdo->exec("ALTER TABLE cheque_reviews ADD UNIQUE KEY uniq_cheque_review_txn (transaction_id)");
                    }
                } catch (PDOException $e) {
                    Logger::logWarning('Failed to add cheque_reviews.transaction_id unique key', ['error' => $e->getMessage()]);
                }
            }
        }

        if ($this->tableExists('payment_reviews')) {
            $this->addColumnIfMissing('payment_reviews', 'transaction_id', 'BIGINT NULL AFTER id');
            $this->addColumnIfMissing('payment_reviews', 'deal_id', 'INT NOT NULL AFTER transaction_id');
            $this->addColumnIfMissing('payment_reviews', 'amount', 'DECIMAL(15,2) DEFAULT 0 AFTER deal_id');
            $this->addColumnIfMissing('payment_reviews', 'sepidar_registered_at', 'DATETIME NULL AFTER amount');
            $this->addColumnIfMissing('payment_reviews', 'registered_by', 'VARCHAR(255) NULL AFTER sepidar_registered_at');
            try {
                $hasPaymentReviewUnique = $pdo->query("SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'payment_reviews' AND index_name = 'uniq_payment_review_txn'")->fetchColumn();
                if ((int)$hasPaymentReviewUnique === 0) {
                    $pdo->exec("ALTER TABLE payment_reviews ADD UNIQUE KEY uniq_payment_review_txn (transaction_id)");
                }
            } catch (PDOException $e) {
                Logger::logWarning('Failed to add payment_reviews.transaction_id unique key', ['error' => $e->getMessage()]);
            }
            $this->ensureIndex('payment_reviews', 'idx_pr_deal', 'deal_id');
            $this->ensureIndex('payment_reviews', 'idx_pr_sepidar', 'sepidar_registered_at');
        }
        if ($this->connection->hasColumn('deals', 'id') && !$this->connection->hasColumn('deals', 'created_by_didar_id')) {
            try {
                $pdo->exec("ALTER TABLE deals ADD COLUMN created_by_didar_id VARCHAR(255) NULL AFTER raw_json");
                Logger::logInfo("Added column to deals table", ['column' => 'created_by_didar_id']);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", ['table' => 'deals', 'column' => 'created_by_didar_id', 'error' => $e->getMessage()]);
            }
        }
        if ($this->connection->hasColumn('deals', 'id') && !$this->connection->hasColumn('deals', 'created_by_name')) {
            try {
                $pdo->exec("ALTER TABLE deals ADD COLUMN created_by_name VARCHAR(255) NULL AFTER created_by_didar_id");
                Logger::logInfo("Added column to deals table", ['column' => 'created_by_name']);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", ['table' => 'deals', 'column' => 'created_by_name', 'error' => $e->getMessage()]);
            }
        }

        // Additional columns for activities table
        $activitiesColumns = [
	            'is_scheduled' => 'TINYINT(1) DEFAULT 0',
	            'reminder_enabled' => 'TINYINT(1) DEFAULT 0',
	            'duration' => 'INT DEFAULT 0',
	            'call_type' => 'VARCHAR(20) NULL',
	            'failure_reason_code' => 'VARCHAR(50)',
	            'failure_reason_description' => 'TEXT',
	            'activity_owner_didar_id' => "VARCHAR(255) NULL COMMENT 'شناسه دیدار کارشناس فعالیت هنگام ثبت'",
	            'activity_owner_name_snapshot' => "VARCHAR(255) NULL COMMENT 'نام قفل‌شده کارشناس فعالیت هنگام ثبت'",
	            'activity_owner_locked_at' => "DATETIME NULL COMMENT 'زمان قفل شدن کارشناس فعالیت'",
	        ];

        foreach ($activitiesColumns as $column => $type) {
            if (!$this->connection->hasColumn('activities', $column)) {
                try {
                    $pdo->exec("ALTER TABLE activities ADD COLUMN $column $type");
                    Logger::logInfo("Added column to activities table", ['column' => $column]);
                } catch (PDOException $e) {
                    Logger::logWarning("Failed to add column", ['column' => $column, 'error' => $e->getMessage()]);
                }
            }
        }

        // Add index for due_date if not exists
        try {
            $pdo->exec("CREATE INDEX IF NOT EXISTS idx_due_date ON activities(due_date)");
        } catch (PDOException $e) {
            // Index might already exist, ignore
        }

        try {
            $pdo->exec("CREATE INDEX IF NOT EXISTS idx_is_scheduled ON activities(is_scheduled)");
        } catch (PDOException $e) {
            // Index might already exist, ignore
        }

        if ($this->connection->hasColumn('activities', 'activity_owner_didar_id')) {
            $this->ensureIndex('activities', 'idx_activity_owner_didar_id', 'activity_owner_didar_id');
        }
        if ($this->connection->hasColumn('activities', 'activity_owner_name_snapshot')) {
            try {
                $nameExpr = $this->connection->hasColumn('users', 'display_name')
                    ? "COALESCE(NULLIF(u.display_name, ''), NULLIF(TRIM(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))), ''), u.email, a.owner_didar_id)"
                    : "COALESCE(NULLIF(TRIM(CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))), ''), u.email, a.owner_didar_id)";
                $lockedAtExpr = $this->connection->hasColumn('activities', 'created_at')
                    ? "COALESCE(a.created_at, a.register_date, NOW())"
                    : "COALESCE(a.register_date, NOW())";
                $pdo->exec("
                    UPDATE activities a
                    LEFT JOIN users u ON a.owner_didar_id = u.didar_user_id
                    SET a.activity_owner_didar_id = COALESCE(NULLIF(a.activity_owner_didar_id, ''), a.owner_didar_id),
                        a.activity_owner_name_snapshot = COALESCE(NULLIF(a.activity_owner_name_snapshot, ''), {$nameExpr}),
                        a.activity_owner_locked_at = COALESCE(a.activity_owner_locked_at, {$lockedAtExpr})
                    WHERE (a.activity_owner_didar_id IS NULL OR a.activity_owner_didar_id = ''
                        OR a.activity_owner_name_snapshot IS NULL OR a.activity_owner_name_snapshot = '')
                      AND a.owner_didar_id IS NOT NULL
                      AND a.owner_didar_id <> ''
                ");
            } catch (PDOException $e) {
                Logger::logWarning('Failed to backfill activity owner snapshots', ['error' => $e->getMessage()]);
            }
        }

        // Add indexes for faster deal/person queries
        $this->ensureIndex('deals', 'idx_deals_legacy', 'is_legacy_didar');
        $this->ensureIndex('deals', 'idx_deals_contact_didar', 'contact_didar_id');
        $this->ensureIndex('deals', 'idx_deals_owner', 'owner_didar_id');
        $this->ensureIndex('deals', 'idx_deals_register_time', 'register_time');
        $this->ensureIndex('deals', 'idx_deals_status', 'status');
        $this->ensureIndex('deals', 'idx_deals_pipeline', 'pipeline_stage');
        $this->ensureIndex('deals', 'idx_deals_person_id', 'person_id');
        $this->ensureIndex('persons', 'idx_persons_didar_contact_id', 'didar_contact_id');
        if ($this->connection->hasColumn('persons', 'customer_code')) {
            try {
                $stmt = $pdo->prepare(
                    "SELECT COUNT(*) FROM information_schema.statistics
                     WHERE table_schema = DATABASE()
                       AND table_name = 'persons'
                       AND index_name = 'uniq_persons_customer_code'"
                );
                $stmt->execute();
                if ((int)$stmt->fetchColumn() === 0) {
                    $pdo->exec("ALTER TABLE persons ADD UNIQUE KEY uniq_persons_customer_code (customer_code)");
                }
            } catch (PDOException $e) {
                Logger::logWarning('Failed to add unique key for persons.customer_code', ['error' => $e->getMessage()]);
            }
        }

        // Relax enums to allow lookup-managed values
        $this->alterColumnType('users', 'role', "VARCHAR(50) DEFAULT 'agent'");
        $this->alterColumnType('deals', 'status', "VARCHAR(50) NULL");
        $this->alterColumnType('activities', 'direction', "VARCHAR(20) NULL");
        $this->alterColumnType('virtual_stages', 'stage_name', "VARCHAR(100) NULL");
        $this->alterColumnType('persons', 'customer_level', "VARCHAR(100) NULL");
    }

    private function alterColumnType(string $table, string $column, string $type): void
    {
        $pdo = $this->connection->getPdo();
        if (!$this->connection->hasColumn($table, $column)) {
            return;
        }
        try {
            $pdo->exec("ALTER TABLE {$table} MODIFY {$column} {$type}");
        } catch (PDOException $e) {
            Logger::logWarning("Failed to alter column type", [
                'table' => $table,
                'column' => $column,
                'error' => $e->getMessage()
            ]);
        }
    }

    private function backfillProductServiceLinks(): void
    {
        $pdo = $this->connection->getPdo();
        if (
            !$this->connection->hasColumn('products', 'service_lookup_item_id') ||
            !$this->connection->hasColumn('products', 'name') ||
            !$this->connection->hasColumn('lookup_groups', 'code') ||
            !$this->connection->hasColumn('lookup_items', 'group_id')
        ) {
            return;
        }

        try {
            $stmt = $pdo->exec("
                UPDATE products p
                INNER JOIN lookup_items li
                    ON COALESCE(NULLIF(li.value, ''), li.title) = p.name
                INNER JOIN lookup_groups lg
                    ON lg.id = li.group_id AND lg.code = 'allowed_services'
                SET p.service_lookup_item_id = li.id
                WHERE p.service_lookup_item_id IS NULL
            ");
            if ((int)$stmt > 0) {
                Logger::logInfo('Backfilled products.service_lookup_item_id by exact name match', [
                    'updated_rows' => (int)$stmt
                ]);
            }
        } catch (PDOException $e) {
            Logger::logWarning('Failed to backfill product service links', [
                'error' => $e->getMessage()
            ]);
        }
    }

    private function backfillDealProductSnapshots(): void
    {
        if (
            !$this->connection->hasColumn('deal_products', 'product_name_snapshot') ||
            !$this->connection->hasColumn('deal_products', 'service_lookup_item_id_snapshot') ||
            !$this->connection->hasColumn('deal_products', 'service_title_snapshot') ||
            !$this->connection->hasColumn('products', 'name')
        ) {
            return;
        }

        $pdo = $this->connection->getPdo();
        $hasProductServiceLink = $this->connection->hasColumn('products', 'service_lookup_item_id');
        $serviceLookupExpr = $hasProductServiceLink ? 'p.service_lookup_item_id' : 'NULL';
        $serviceTitleExpr = $hasProductServiceLink
            ? "COALESCE(NULLIF(li.title, ''), NULLIF(li.value, ''), p.name)"
            : "p.name";
        $lookupJoin = $hasProductServiceLink
            ? "LEFT JOIN lookup_items li ON li.id = p.service_lookup_item_id"
            : "";
        try {
            $stmt = $pdo->exec("
                UPDATE deal_products dp
                INNER JOIN products p ON p.id = dp.product_id
                {$lookupJoin}
                SET
                    dp.product_name_snapshot = COALESCE(NULLIF(dp.product_name_snapshot, ''), p.name),
                    dp.service_lookup_item_id_snapshot = COALESCE(dp.service_lookup_item_id_snapshot, {$serviceLookupExpr}),
                    dp.service_title_snapshot = COALESCE(
                        NULLIF(dp.service_title_snapshot, ''),
                        {$serviceTitleExpr}
                    )
                WHERE dp.product_name_snapshot IS NULL
                   OR dp.product_name_snapshot = ''
                   OR dp.service_lookup_item_id_snapshot IS NULL
                   OR dp.service_title_snapshot IS NULL
                   OR dp.service_title_snapshot = ''
            ");
            if ((int)$stmt > 0) {
                Logger::logInfo('Backfilled deal_products snapshot columns', [
                    'updated_rows' => (int)$stmt
                ]);
            }
        } catch (PDOException $e) {
            Logger::logWarning('Failed to backfill deal product snapshots', [
                'error' => $e->getMessage()
            ]);
        }
    }

    private function repairSudranPaymentRecordCommissionStatus(\PDO $pdo): void
    {
        if (!$this->tableExists('payment_records')
            || !$this->connection->hasColumn('payment_records', 'method')
            || !$this->connection->hasColumn('payment_records', 'commission_status')) {
            return;
        }
        try {
            $rawSnapshotChecks = '';
            if ($this->connection->hasColumn('payment_records', 'raw_snapshot_json')) {
                $rawSnapshotChecks = "
                    OR LOWER(COALESCE(raw_snapshot_json, '')) LIKE '%sudran%'
                    OR LOWER(COALESCE(raw_snapshot_json, '')) LIKE '%soodran%'
                    OR LOWER(COALESCE(raw_snapshot_json, '')) LIKE '%sodran%'
                    OR COALESCE(raw_snapshot_json, '') LIKE '%سودران%'
                ";
            }
            $pdo->exec("
                UPDATE payment_records
                SET commission_status = 'NOT_ELIGIBLE', updated_at = NOW()
                WHERE (
                    LOWER(REPLACE(REPLACE(REPLACE(TRIM(COALESCE(method, '')), ' ', '_'), '-', '_'), '/', '_')) IN ('sudran_payment_gateway', 'sudran_gateway', 'soodran_payment_gateway', 'soodran_gateway', 'sodran_payment_gateway')
                    OR COALESCE(method, '') LIKE '%سودران%'
                    {$rawSnapshotChecks}
                )
                AND COALESCE(commission_status, '') <> 'NOT_ELIGIBLE'
            ");
        } catch (\Throwable $e) {
            Logger::logWarning('Failed to repair Sudran payment record commission status', ['error' => $e->getMessage()]);
        }
    }

    private function seedLookups(): void
    {
        $pdo = $this->connection->getPdo();
        try {
            $count = (int)$pdo->query("SELECT COUNT(*) FROM lookup_groups")->fetchColumn();
        } catch (PDOException $e) {
            return;
        }
        if ($count > 0) {
            $this->ensureAdditionalLookups($pdo);
            return;
        }

        $groups = [
            ['code' => 'lead_sources', 'title' => 'شیوه‌های آشنایی', 'sort' => 10],
            ['code' => 'lead_source_details', 'title' => 'جزئیات شیوه‌های آشنایی', 'sort' => 20],
            ['code' => 'content_topics', 'title' => 'موضوعات محتوا', 'sort' => 30],
            ['code' => 'pipeline_stages', 'title' => 'مراحل پایپ‌لاین', 'sort' => 40],
            ['code' => 'allowed_services', 'title' => 'خدمات قابل ارائه', 'sort' => 50],
            ['code' => 'failure_reasons', 'title' => 'دلایل شکست', 'sort' => 60],
            ['code' => 'customer_levels', 'title' => 'سطوح مشتری', 'sort' => 70],
            ['code' => 'financial_levels', 'title' => 'سطوح مالی', 'sort' => 80],
            ['code' => 'asset_ranges', 'title' => 'بازه دارایی', 'sort' => 90],
            ['code' => 'income_ranges', 'title' => 'بازه درآمد', 'sort' => 100],
            ['code' => 'discount_types', 'title' => 'انواع تخفیف', 'sort' => 110],
            ['code' => 'discount_occasions', 'title' => 'مناسبت‌های تخفیف', 'sort' => 120],
            ['code' => 'user_roles', 'title' => 'نقش‌های کاربری', 'sort' => 130],
            ['code' => 'deal_statuses', 'title' => 'وضعیت معاملات', 'sort' => 140],
            ['code' => 'activity_stages', 'title' => 'مراحل فعالیت', 'sort' => 150],
            ['code' => 'reminder_hours', 'title' => 'ساعت‌های یادآوری', 'sort' => 160],
            ['code' => 'reminder_minutes', 'title' => 'دقیقه‌های یادآوری', 'sort' => 170],
            ['code' => 'activity_types_fallback', 'title' => 'انواع فعالیت (Fallback)', 'sort' => 180],
            ['code' => 'virtual_stages', 'title' => 'مراحل مجازی', 'sort' => 190],
            ['code' => 'activity_directions', 'title' => 'جهت فعالیت', 'sort' => 200]
        ];

        $groupStmt = $pdo->prepare("INSERT INTO lookup_groups (code, title, sort_order, is_active, is_system, created_at, updated_at)
                                    VALUES (?, ?, ?, 1, 1, NOW(), NOW())");
        $groupIds = [];
        foreach ($groups as $group) {
            $groupStmt->execute([$group['code'], $group['title'], $group['sort']]);
            $groupIds[$group['code']] = (int)$pdo->lastInsertId();
        }

        $items = [
            ['group' => 'lead_sources', 'value' => 'instagram', 'title' => 'اینستاگرام', 'sort' => 10],
            ['group' => 'lead_sources', 'value' => 'telegram', 'title' => 'تلگرام', 'sort' => 20],
            ['group' => 'lead_sources', 'value' => 'book', 'title' => 'کتاب', 'sort' => 30],
            ['group' => 'lead_sources', 'value' => 'website', 'title' => 'سایت', 'sort' => 40],
            ['group' => 'lead_sources', 'value' => 'whatsapp', 'title' => 'واتساپ', 'sort' => 50],
            ['group' => 'lead_sources', 'value' => 'campaign', 'title' => 'کمپین', 'sort' => 60],
            ['group' => 'lead_sources', 'value' => 'event', 'title' => 'ایونت', 'sort' => 70],
            ['group' => 'lead_sources', 'value' => 'support', 'title' => 'پشتیبانی', 'sort' => 80],
            ['group' => 'lead_sources', 'value' => 'incoming_call', 'title' => 'تماس ورودی', 'sort' => 90],
            ['group' => 'lead_sources', 'value' => 'visit', 'title' => 'مراجعه حضوری', 'sort' => 100],
            ['group' => 'lead_sources', 'value' => 'referral', 'title' => 'معرف', 'sort' => 110],
            ['group' => 'lead_sources', 'value' => 'other', 'title' => 'سایر', 'sort' => 120],

            ['group' => 'lead_source_details', 'value' => 'instagram_direct', 'title' => 'دایرکت', 'parent_value' => 'instagram', 'sort' => 10],
            ['group' => 'lead_source_details', 'value' => 'instagram_box', 'title' => 'باکس', 'parent_value' => 'instagram', 'sort' => 20],
            ['group' => 'lead_source_details', 'value' => 'instagram_highlight', 'title' => 'هایلایت', 'parent_value' => 'instagram', 'sort' => 30],
            ['group' => 'lead_source_details', 'value' => 'instagram_channel', 'title' => 'کانال', 'parent_value' => 'instagram', 'sort' => 40],
            ['group' => 'lead_source_details', 'value' => 'instagram_auto', 'title' => 'اتوحنفی', 'parent_value' => 'instagram', 'sort' => 50],
            ['group' => 'lead_source_details', 'value' => 'whatsapp_marketing', 'title' => 'مارکتینگ', 'parent_value' => 'whatsapp', 'sort' => 60],
            ['group' => 'lead_source_details', 'value' => 'website_signup', 'title' => 'ثبت نام', 'parent_value' => 'website', 'sort' => 70],
            ['group' => 'lead_source_details', 'value' => 'website_course_signup', 'title' => 'ثبت نام دوره', 'parent_value' => 'website', 'sort' => 80],
            ['group' => 'lead_source_details', 'value' => 'website_failed_purchase', 'title' => 'خرید ناموفق', 'parent_value' => 'website', 'sort' => 90],
            ['group' => 'lead_source_details', 'value' => 'campaign_sms', 'title' => 'پیامک', 'parent_value' => 'campaign', 'sort' => 100],
            ['group' => 'lead_source_details', 'value' => 'campaign_divar', 'title' => 'دیوار', 'parent_value' => 'campaign', 'sort' => 110],
            ['group' => 'lead_source_details', 'value' => 'campaign_porsline', 'title' => 'پرسلاین', 'parent_value' => 'campaign', 'sort' => 120],
            ['group' => 'lead_source_details', 'value' => 'campaign_web', 'title' => 'وب', 'parent_value' => 'campaign', 'sort' => 130],
            ['group' => 'lead_source_details', 'value' => 'event_live', 'title' => 'لایو', 'parent_value' => 'event', 'sort' => 140],
            ['group' => 'lead_source_details', 'value' => 'event_meetup', 'title' => 'دورهمی', 'parent_value' => 'event', 'sort' => 150],
            ['group' => 'lead_source_details', 'value' => 'event_course', 'title' => 'دوره', 'parent_value' => 'event', 'sort' => 160],
            ['group' => 'lead_source_details', 'value' => 'referral_internal', 'title' => 'همکاران داخلی', 'parent_value' => 'referral', 'sort' => 170],
            ['group' => 'lead_source_details', 'value' => 'referral_external', 'title' => 'همکاران خارجی', 'parent_value' => 'referral', 'sort' => 180],
            ['group' => 'lead_source_details', 'value' => 'referral_teacher', 'title' => 'استاد', 'parent_value' => 'referral', 'sort' => 190],
            ['group' => 'lead_source_details', 'value' => 'referral_customer', 'title' => 'مشتری قدیم', 'parent_value' => 'referral', 'sort' => 200],

            ['group' => 'content_topics', 'value' => 'car_intelligence', 'title' => 'هوش خودرو', 'sort' => 10],
            ['group' => 'content_topics', 'value' => 'in_person_inspection', 'title' => 'کارشناسی حضوری', 'sort' => 20],
            ['group' => 'content_topics', 'value' => 'business', 'title' => 'کسب و کار', 'sort' => 30],
            ['group' => 'content_topics', 'value' => 'mini_course', 'title' => 'مینی دوره هوش خودرو', 'sort' => 40],
            ['group' => 'content_topics', 'value' => 'vip_channel', 'title' => 'کانال VIP', 'sort' => 50],
            ['group' => 'content_topics', 'value' => 'budgeting', 'title' => 'بودجه بندی', 'sort' => 60],
            ['group' => 'content_topics', 'value' => 'online_exhibition', 'title' => 'نمایشگاه آنلاین', 'sort' => 70],
            ['group' => 'content_topics', 'value' => 'consulting', 'title' => 'مشاوره با استاد', 'sort' => 80],
            ['group' => 'content_topics', 'value' => 'inspection_device', 'title' => 'دستگاه کارشناسی', 'sort' => 90],
            ['group' => 'content_topics', 'value' => 'car_intelligence_test', 'title' => 'تست هوش خودرو', 'sort' => 100],
            ['group' => 'content_topics', 'value' => 'financial_personality', 'title' => 'تست تیپ شخصیت مالی', 'sort' => 110],
            ['group' => 'content_topics', 'value' => 'smart_trader', 'title' => 'معامله گر هوشمند', 'sort' => 120],
            ['group' => 'content_topics', 'value' => 'detailing_training', 'title' => 'آموزش دیتیلینگ', 'sort' => 130],
            ['group' => 'content_topics', 'value' => 'divar_car', 'title' => 'دیوار خودرو', 'sort' => 140],
            ['group' => 'content_topics', 'value' => 'inspection_test', 'title' => 'تست کارشناسی', 'sort' => 150],
            ['group' => 'content_topics', 'value' => 'book', 'title' => 'کتاب هوش خودرو', 'sort' => 160],
            ['group' => 'content_topics', 'value' => 'cip', 'title' => 'CIP', 'sort' => 170],

            ['group' => 'pipeline_stages', 'title' => 'بلاتکلیف', 'value' => 'بلاتکلیف', 'sort' => 10],
            ['group' => 'pipeline_stages', 'title' => 'معامله جدید', 'value' => 'معامله جدید', 'sort' => 20],
            ['group' => 'pipeline_stages', 'title' => 'تماس عدم پاسخ', 'value' => 'تماس عدم پاسخ', 'sort' => 30],
            ['group' => 'pipeline_stages', 'title' => 'تماس پیگیری', 'value' => 'تماس پیگیری', 'sort' => 40],
            ['group' => 'pipeline_stages', 'title' => 'ارسال لینک پرداخت', 'value' => 'ارسال لینک پرداخت', 'sort' => 50],
            ['group' => 'pipeline_stages', 'title' => 'پرداخت', 'value' => 'پرداخت', 'sort' => 60],
            ['group' => 'pipeline_stages', 'title' => 'پیگیری مانده حساب', 'value' => 'پیگیری مانده حساب', 'sort' => 70],
            ['group' => 'pipeline_stages', 'title' => 'برگزاری دوره', 'value' => 'برگزاری دوره', 'sort' => 80],
            ['group' => 'pipeline_stages', 'title' => 'پشتیبانی', 'value' => 'پشتیبانی', 'sort' => 90],
            ['group' => 'pipeline_stages', 'title' => 'ارجاع به CRM', 'value' => 'ارجاع به CRM', 'sort' => 100],

            ['group' => 'allowed_services', 'title' => 'هوش خودرو', 'value' => 'هوش خودرو', 'sort' => 10],
            ['group' => 'allowed_services', 'title' => 'کارشناسی حضوری', 'value' => 'کارشناسی حضوری', 'sort' => 20],
            ['group' => 'allowed_services', 'title' => 'کانال VIP', 'value' => 'کانال VIP', 'sort' => 30],
            ['group' => 'allowed_services', 'title' => 'دستگاه کارشناسی', 'value' => 'دستگاه کارشناسی', 'sort' => 40],
            ['group' => 'allowed_services', 'title' => 'مشاوره', 'value' => 'مشاوره', 'sort' => 50],
            ['group' => 'allowed_services', 'title' => 'پکیج مشاوره', 'value' => 'پکیج مشاوره', 'sort' => 60],
            ['group' => 'allowed_services', 'title' => 'چکاپ کسب و کار', 'value' => 'چکاپ کسب و کار', 'sort' => 70],
            ['group' => 'allowed_services', 'title' => 'شتاب کسب و کار', 'value' => 'شتاب کسب و کار', 'sort' => 80],
            ['group' => 'allowed_services', 'title' => 'مربی شخصی', 'value' => 'مربی شخصی', 'sort' => 90],
            ['group' => 'allowed_services', 'title' => 'آموزش دیتیلینگ', 'value' => 'آموزش دیتیلینگ', 'sort' => 100],
            ['group' => 'allowed_services', 'title' => 'نمایشگاه آنلاین', 'value' => 'نمایشگاه آنلاین', 'sort' => 110],
            ['group' => 'allowed_services', 'title' => 'دوره دیوار خودرو', 'value' => 'دوره دیوار خودرو', 'sort' => 120],
            ['group' => 'allowed_services', 'title' => 'بودجه بندی', 'value' => 'بودجه بندی', 'sort' => 130],
            ['group' => 'allowed_services', 'title' => 'کارشناسی برتر', 'value' => 'کارشناسی برتر', 'sort' => 140],
            ['group' => 'allowed_services', 'title' => 'مدرک کارشناسی', 'value' => 'مدرک کارشناسی', 'sort' => 150],
            ['group' => 'allowed_services', 'title' => 'CIP', 'value' => 'CIP', 'sort' => 160],

            ['group' => 'failure_reasons', 'value' => 'no_financial_ability', 'title' => 'عدم توان مالی', 'sort' => 10],
            ['group' => 'failure_reasons', 'value' => 'no_purchase_intent', 'title' => 'فعلا قصد خرید ندارد', 'sort' => 20],
            ['group' => 'failure_reasons', 'value' => 'competitor_purchase', 'title' => 'خرید از رقبا', 'sort' => 30],
            ['group' => 'failure_reasons', 'value' => 'wrong_number', 'title' => 'شماره اشتباه', 'sort' => 40],
            ['group' => 'failure_reasons', 'value' => 'irrelevant_lead', 'title' => 'لید نامرتبط', 'sort' => 50],
            ['group' => 'failure_reasons', 'value' => 'sales_agent_underperformance', 'title' => 'عدم توانمندی کارشناس فروش', 'sort' => 55],
            ['group' => 'failure_reasons', 'value' => 'other', 'title' => 'سایر', 'sort' => 60],

            ['group' => 'customer_levels', 'value' => 'طلایی', 'title' => 'طلایی', 'sort' => 10],
            ['group' => 'customer_levels', 'value' => 'نقره‌ای', 'title' => 'نقره‌ای', 'sort' => 20],
            ['group' => 'customer_levels', 'value' => 'برنز', 'title' => 'برنز', 'sort' => 30],

            ['group' => 'financial_levels', 'value' => 'بالا', 'title' => 'بالا', 'sort' => 10],
            ['group' => 'financial_levels', 'value' => 'متوسط رو به بالا', 'title' => 'متوسط رو به بالا', 'sort' => 20],
            ['group' => 'financial_levels', 'value' => 'متوسط رو به پایین', 'title' => 'متوسط رو به پایین', 'sort' => 30],
            ['group' => 'financial_levels', 'value' => 'پایین', 'title' => 'پایین', 'sort' => 40],

            ['group' => 'asset_ranges', 'value' => '0-50', 'title' => '0 تا 50 میلیون تومان', 'sort' => 10],
            ['group' => 'asset_ranges', 'value' => '50-200', 'title' => '50 تا 200 میلیون تومان', 'sort' => 20],
            ['group' => 'asset_ranges', 'value' => '200-500', 'title' => '200 تا 500 میلیون تومان', 'sort' => 30],
            ['group' => 'asset_ranges', 'value' => '500-1000', 'title' => '500 تا 1000 میلیون تومان', 'sort' => 40],
            ['group' => 'asset_ranges', 'value' => '1000-plus', 'title' => 'بیش از 1 میلیارد تومان', 'sort' => 50],

            ['group' => 'income_ranges', 'value' => '0-20', 'title' => '0 تا 20 میلیون تومان', 'sort' => 10],
            ['group' => 'income_ranges', 'value' => '20-100', 'title' => '20 تا 100 میلیون تومان', 'sort' => 20],
            ['group' => 'income_ranges', 'value' => '100-200', 'title' => '100 تا 200 میلیون تومان', 'sort' => 30],
            ['group' => 'income_ranges', 'value' => '200-500', 'title' => '200 تا 500 میلیون تومان', 'sort' => 40],
            ['group' => 'income_ranges', 'value' => '500-plus', 'title' => 'بیش از 500 میلیون تومان', 'sort' => 50],

            ['group' => 'discount_types', 'value' => 'occasion', 'title' => 'مناسبتی', 'sort' => 10],
            ['group' => 'discount_types', 'value' => 'individual', 'title' => 'فردی', 'sort' => 20],
            ['group' => 'discount_types', 'value' => 'refund', 'title' => 'مرجوعی', 'sort' => 30],

            ['group' => 'discount_occasions', 'value' => 'black_friday', 'title' => 'بلک فرایدی', 'sort' => 10],
            ['group' => 'discount_occasions', 'value' => 'teacher_birthday', 'title' => 'تولد استاد', 'sort' => 20],
            ['group' => 'discount_occasions', 'value' => 'nowruz', 'title' => 'عید نوروز', 'sort' => 30],
            ['group' => 'discount_occasions', 'value' => 'other', 'title' => 'سایر', 'sort' => 40],

            ['group' => 'user_roles', 'value' => 'admin', 'title' => 'مدیر', 'sort' => 10],
            ['group' => 'user_roles', 'value' => 'agent', 'title' => 'کارشناس', 'sort' => 20],
            ['group' => 'user_roles', 'value' => 'crm_specialist', 'title' => 'کارشناس CRM', 'sort' => 30],

            ['group' => 'deal_statuses', 'value' => 'pending', 'title' => 'معامله جاری', 'sort' => 10],
            ['group' => 'deal_statuses', 'value' => 'won', 'title' => 'معامله موفق', 'sort' => 20],
            ['group' => 'deal_statuses', 'value' => 'lost', 'title' => 'معامله ناموفق', 'sort' => 30],

            ['group' => 'activity_stages', 'value' => 'تماس عدم پاسخ', 'title' => 'تماس عدم پاسخ', 'sort' => 10],
            ['group' => 'activity_stages', 'value' => 'تماس پیگیری', 'title' => 'تماس پیگیری', 'sort' => 20],
            ['group' => 'activity_stages', 'value' => 'ارسال لینک پرداخت', 'title' => 'ارسال لینک پرداخت', 'sort' => 30],
            ['group' => 'activity_stages', 'value' => 'پرداخت', 'title' => 'پرداخت', 'sort' => 40],
            ['group' => 'activity_stages', 'value' => 'پیگیری مانده حساب', 'title' => 'پیگیری مانده حساب', 'sort' => 50],
            ['group' => 'activity_stages', 'value' => 'برگزاری دوره', 'title' => 'برگزاری دوره', 'sort' => 60],
            ['group' => 'activity_stages', 'value' => 'پشتیبانی', 'title' => 'پشتیبانی', 'sort' => 70],
            ['group' => 'activity_stages', 'value' => 'ارجاع به CRM', 'title' => 'ارجاع به CRM', 'sort' => 80],
            ['group' => 'activity_stages', 'value' => 'deal_failed', 'title' => 'معامله ناموفق', 'sort' => 90],
            ['group' => 'activity_stages', 'value' => 'deal_success', 'title' => 'معامله موفق', 'sort' => 100],

            ['group' => 'reminder_hours', 'value' => '0', 'title' => '0 ساعت', 'sort' => 0],
            ['group' => 'reminder_hours', 'value' => '1', 'title' => '1 ساعت', 'sort' => 10],
            ['group' => 'reminder_hours', 'value' => '2', 'title' => '2 ساعت', 'sort' => 20],
            ['group' => 'reminder_hours', 'value' => '4', 'title' => '4 ساعت', 'sort' => 30],
            ['group' => 'reminder_hours', 'value' => '8', 'title' => '8 ساعت', 'sort' => 40],
            ['group' => 'reminder_hours', 'value' => '12', 'title' => '12 ساعت', 'sort' => 50],
            ['group' => 'reminder_hours', 'value' => '24', 'title' => '24 ساعت', 'sort' => 60],
            ['group' => 'reminder_hours', 'value' => '168', 'title' => '1 هفته', 'sort' => 70],

            ['group' => 'reminder_minutes', 'value' => '0', 'title' => '0 دقیقه', 'sort' => 0],
            ['group' => 'reminder_minutes', 'value' => '5', 'title' => '5 دقیقه', 'sort' => 10],
            ['group' => 'reminder_minutes', 'value' => '10', 'title' => '10 دقیقه', 'sort' => 20],
            ['group' => 'reminder_minutes', 'value' => '15', 'title' => '15 دقیقه', 'sort' => 30],
            ['group' => 'reminder_minutes', 'value' => '30', 'title' => '30 دقیقه', 'sort' => 40],

            ['group' => 'activity_types_fallback', 'value' => '2a335dc1-4834-484b-b6dc-9ddd97eef67e', 'title' => 'تماس ورودی', 'meta' => '{"direction":"incoming","category":"call"}', 'sort' => 10],
            ['group' => 'activity_types_fallback', 'value' => '790079e8-74ac-4760-9527-097627d3e7ec', 'title' => 'تماس خروجی', 'meta' => '{"direction":"outgoing","category":"call"}', 'sort' => 20],
            ['group' => 'activity_types_fallback', 'value' => 'voip_call', 'title' => 'تماس با VOIP', 'meta' => '{"direction":"outgoing","category":"call","channel":"voip"}', 'sort' => 25],
            ['group' => 'activity_types_fallback', 'value' => 'mobile_phone_call', 'title' => 'تماس با تلفن همراه', 'meta' => '{"direction":"outgoing","category":"call","channel":"mobile"}', 'sort' => 26],
            ['group' => 'activity_types_fallback', 'value' => '3b446dc2-5945-595c-c7ed-0eee08ff78fd', 'title' => 'چت ورودی', 'meta' => '{"direction":"incoming","category":"chat"}', 'sort' => 30],
            ['group' => 'activity_types_fallback', 'value' => '4c557ed3-6a56-606d-d8fe-1fff19gg89ge', 'title' => 'چت خروجی', 'meta' => '{"direction":"outgoing","category":"chat"}', 'sort' => 40],

            ['group' => 'virtual_stages', 'value' => 'with_purchase', 'title' => 'with_purchase', 'sort' => 10],
            ['group' => 'virtual_stages', 'value' => 'without_purchase', 'title' => 'without_purchase', 'sort' => 20],
            ['group' => 'virtual_stages', 'value' => 'new', 'title' => 'new', 'sort' => 30],
            ['group' => 'virtual_stages', 'value' => 'contact_no_answer', 'title' => 'contact_no_answer', 'sort' => 40],
            ['group' => 'virtual_stages', 'value' => 'contact_followup', 'title' => 'contact_followup', 'sort' => 50],
            ['group' => 'virtual_stages', 'value' => 'deal_registered', 'title' => 'deal_registered', 'sort' => 60],
            ['group' => 'virtual_stages', 'value' => 'payment', 'title' => 'payment', 'sort' => 70],
            ['group' => 'virtual_stages', 'value' => 'payment_followup', 'title' => 'payment_followup', 'sort' => 80],
            ['group' => 'virtual_stages', 'value' => 'course_delivered', 'title' => 'course_delivered', 'sort' => 90],
            ['group' => 'virtual_stages', 'value' => 'support', 'title' => 'support', 'sort' => 100],
            ['group' => 'virtual_stages', 'value' => 'refer_crm', 'title' => 'refer_crm', 'sort' => 110],

            ['group' => 'activity_directions', 'value' => 'incoming', 'title' => 'incoming', 'sort' => 10],
            ['group' => 'activity_directions', 'value' => 'outgoing', 'title' => 'outgoing', 'sort' => 20]
        ];

        $itemStmt = $pdo->prepare("INSERT INTO lookup_items (group_id, parent_id, code, title, value, meta_json, sort_order, is_active, is_system, created_at, updated_at)
                                   VALUES (?, ?, ?, ?, ?, ?, ?, 1, 1, NOW(), NOW())");
        $itemIndex = [];
        foreach ($items as $item) {
            $groupId = $groupIds[$item['group']] ?? null;
            if (!$groupId) {
                continue;
            }
            $parentId = null;
            if (!empty($item['parent_value'])) {
                $parentId = $itemIndex['lead_sources'][$item['parent_value']] ?? null;
            }
            $itemStmt->execute([
                $groupId,
                $parentId,
                $item['code'] ?? null,
                $item['title'],
                $item['value'] ?? $item['title'],
                $item['meta'] ?? null,
                $item['sort'] ?? 0
            ]);
            $insertedId = (int)$pdo->lastInsertId();
            if ($item['group'] === 'lead_sources') {
                $itemIndex['lead_sources'][$item['value']] = $insertedId;
            }
        }

        $this->ensureAdditionalLookups($pdo);
    }

    private function seedOperationManagerDefaults(): void
    {
        $pdo = $this->connection->getPdo();
        try {
            if ($this->tableExists('lead_pools')) {
                $stmt = $pdo->prepare("INSERT INTO lead_pools (pool_key, title, description, is_system, is_active, created_at, updated_at)
                    VALUES ('crm_leads', 'CRM Leads', 'Unowned CRM lead pool for operation manager automation', 1, 1, NOW(), NOW())
                    ON DUPLICATE KEY UPDATE title = VALUES(title), description = VALUES(description), is_system = 1, is_active = 1, updated_at = NOW()");
                $stmt->execute();
            }

            if ($this->tableExists('lookup_groups') && $this->tableExists('lookup_items')) {
                $this->ensureLookupItemByCode('failure_reasons', 'sales_agent_underperformance', 'عدم توانمندی کارشناس فروش', 55, null);
                $this->ensureLookupItemByCode('activity_types_fallback', 'voip_call', 'تماس با VOIP', 25, '{"direction":"outgoing","category":"call","channel":"voip"}');
                $this->ensureLookupItemByCode('activity_types_fallback', 'mobile_phone_call', 'تماس با تلفن همراه', 26, '{"direction":"outgoing","category":"call","channel":"mobile"}');
            }
        } catch (PDOException $e) {
            Logger::logWarning('Failed to seed operation manager defaults', ['error' => $e->getMessage()]);
        }
    }

    private function ensureLookupItemByCode(string $groupCode, string $value, string $title, int $sortOrder, ?string $metaJson): void
    {
        $pdo = $this->connection->getPdo();
        $groupStmt = $pdo->prepare("SELECT id FROM lookup_groups WHERE code = ? LIMIT 1");
        $groupStmt->execute([$groupCode]);
        $groupId = (int)$groupStmt->fetchColumn();
        if ($groupId <= 0) {
            return;
        }
        $exists = $pdo->prepare("SELECT id FROM lookup_items WHERE group_id = ? AND (code = ? OR value = ?) LIMIT 1");
        $exists->execute([$groupId, $value, $value]);
        if ((int)$exists->fetchColumn() > 0) {
            return;
        }
        $insert = $pdo->prepare("INSERT INTO lookup_items (group_id, code, title, value, meta_json, sort_order, is_active, is_visible, is_system, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, 1, 1, 1, NOW(), NOW())");
        $insert->execute([$groupId, $value, $title, $value, $metaJson, $sortOrder]);
    }

    private function ensureAdditionalLookups($pdo): void
    {
        try {
            $rows = $pdo->query("SELECT id, code FROM lookup_groups")->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        } catch (PDOException $e) {
            return;
        }

        $groupIds = [];
        foreach ($rows as $row) {
            $groupIds[$row['code']] = (int)$row['id'];
        }

        $ensureGroup = function (string $code, string $title, int $sort) use ($pdo, &$groupIds): int {
            if (isset($groupIds[$code])) {
                return $groupIds[$code];
            }
            $stmt = $pdo->prepare("INSERT INTO lookup_groups (code, title, sort_order, is_active, is_system, created_at, updated_at)
                                   VALUES (?, ?, ?, 1, 1, NOW(), NOW())");
            $stmt->execute([$code, $title, $sort]);
            $groupIds[$code] = (int)$pdo->lastInsertId();
            return $groupIds[$code];
        };

        $ensureItem = function (int $groupId, string $value, string $title, int $sort, ?string $meta = null, ?string $code = null) use ($pdo): void {
            if ($code !== null && $code !== '') {
                $stmt = $pdo->prepare("SELECT id FROM lookup_items WHERE group_id = ? AND (code = ? OR value = ?) LIMIT 1");
                $stmt->execute([$groupId, $code, $value]);
            } else {
                $stmt = $pdo->prepare("SELECT id FROM lookup_items WHERE group_id = ? AND value = ? LIMIT 1");
                $stmt->execute([$groupId, $value]);
            }
            if ($stmt->fetchColumn()) {
                return;
            }
            $insert = $pdo->prepare("INSERT INTO lookup_items (group_id, parent_id, code, title, value, meta_json, sort_order, is_active, is_system, created_at, updated_at)
                                     VALUES (?, NULL, ?, ?, ?, ?, ?, 1, 1, NOW(), NOW())");
            $insert->execute([$groupId, $code, $title, $value, $meta, $sort]);
        };

        $paymentMethodsGroupId = $ensureGroup('payment_methods', 'Payment Methods', 210);
        $ensureItem($paymentMethodsGroupId, 'card_reader', 'کارتخوان', 10, '{"requires_card_last4":true,"requires_bank_destination":true}');
        $ensureItem($paymentMethodsGroupId, 'card_to_card', 'کارت به کارت', 20, '{"requires_card_last4":true,"requires_bank_destination":true}');
        $ensureItem($paymentMethodsGroupId, 'card_to_card_lookup', 'کارت به کارت (Lookup)', 25, '{"requires_card_last4":true,"requires_bank_destination":true}');
        $sudranMeta = '{"requires_card_last4":false,"requires_bank_destination":false,"commission_excluded":true,"admin_only":true}';
        $ensureItem($paymentMethodsGroupId, 'sudran_payment_gateway', 'درگاه پرداخت سودران', 28, $sudranMeta, 'sudran_payment_gateway');
        $visibleSql = $this->connection->hasColumn('lookup_items', 'is_visible') ? ', is_visible = 1' : '';
        $pdo->prepare("UPDATE lookup_items SET code = ?, title = ?, meta_json = ?, sort_order = ?, is_active = 1{$visibleSql} WHERE group_id = ? AND value = ?")
            ->execute(['sudran_payment_gateway', 'درگاه پرداخت سودران', $sudranMeta, 28, $paymentMethodsGroupId, 'sudran_payment_gateway']);
        $ensureItem($paymentMethodsGroupId, 'check', 'چک', 30, '{"requires_card_last4":false,"requires_bank_destination":false}');

        $banksGroupId = $ensureGroup('payment_destination_banks', 'Payment Destination Banks', 220);
        $ensureItem($banksGroupId, 'parsian', 'پارسیان', 10);
        $ensureItem($banksGroupId, 'pasargad', 'پاسارگاد', 20);
        $ensureItem($banksGroupId, 'melli', 'ملی', 30);
        $ensureItem($banksGroupId, 'ayandeh', 'آینده', 40);
        $ensureItem($banksGroupId, 'mellat', 'ملت', 50);
        $ensureItem($banksGroupId, 'tejarat', 'تجارت', 60);
        $ensureItem($banksGroupId, 'other', 'سایر (درج در توضیحات)', 70);

        $rulesGroupId = $ensureGroup('field_rules', 'Field Rules', 230);
        $ensureItem($rulesGroupId, 'deal.payments.card_last4', '4 رقم آخر کارت پرداخت کننده', 10, '{"required":true,"editable":true,"editable_after_create":true,"editable_roles":[]}');
        $ensureItem($rulesGroupId, 'deal.payments.bank_destination', 'بانک مقصد پرداخت', 20, '{"required":false,"editable":true,"editable_after_create":true,"editable_roles":[]}');
        $ensureItem($rulesGroupId, 'deal.payments.method', 'نحوه پرداخت', 30, '{"required":true,"editable":true,"editable_after_create":true,"editable_roles":[]}');
    }

    private function seedFieldConfigurations(): void
    {
        $pdo = $this->connection->getPdo();

        // 1. Ensure Lookup Groups exist with unified codes
        $groups = [
            ['code' => 'payment_methods', 'title' => 'نحوه‌های پرداخت', 'sort' => 1],
            ['code' => 'payment_destination_banks', 'title' => 'بانک‌های مقصد پرداخت', 'sort' => 2]
        ];

        foreach ($groups as $g) {
            $stmt = $pdo->prepare("SELECT id FROM lookup_groups WHERE code = ?");
            $stmt->execute([$g['code']]);
            if (!$stmt->fetch()) {
                $pdo->prepare("INSERT INTO lookup_groups (code, title, sort_order, is_active, is_system) VALUES (?, ?, ?, 1, 0)")
                    ->execute([$g['code'], $g['title'], $g['sort']]);
            }
        }

        // 2. Ensure Lookup Items exist
        $paymentMethodsGroupId = (int)$pdo->query("SELECT id FROM lookup_groups WHERE code = 'payment_methods'")->fetchColumn();
        $methods = [
            ['code' => 'card_reader', 'title' => 'کارتخوان', 'value' => 'card_reader'],
            ['code' => 'card_to_card', 'title' => 'کارت به کارت', 'value' => 'card_to_card'],
            ['code' => 'card_to_card_lookup', 'title' => 'کارت به کارت (Lookup)', 'value' => 'card_to_card_lookup'],
            ['code' => 'payment_gateway', 'title' => 'درگاه پرداخت', 'value' => 'payment_gateway'],
            ['code' => 'sudran_payment_gateway', 'title' => 'درگاه پرداخت سودران', 'value' => 'sudran_payment_gateway'],
            ['code' => 'check', 'title' => 'چک', 'value' => 'check'],
            ['code' => 'purchase_order', 'title' => 'سفارش خرید', 'value' => 'purchase_order'],
            // C1: "سایر/Other" payment method intentionally not seeded (hidden in UI).
        ];
        foreach ($methods as $i => $m) {
            $stmt = $pdo->prepare("SELECT id FROM lookup_items WHERE group_id = ? AND (code = ? OR value = ?)");
            $stmt->execute([$paymentMethodsGroupId, $m['code'], $m['value']]);
            if (!$stmt->fetch()) {
                $pdo->prepare("INSERT INTO lookup_items (group_id, code, title, value, sort_order, is_active) VALUES (?, ?, ?, ?, ?, 1)")
                    ->execute([$paymentMethodsGroupId, $m['code'], $m['title'], $m['value'], $i+1]);
            }
        }
        $visibleSql = $this->connection->hasColumn('lookup_items', 'is_visible') ? ', is_visible = 1' : '';
        $pdo->prepare("UPDATE lookup_items SET code = ?, title = ?, meta_json = ?, sort_order = ?, is_active = 1{$visibleSql} WHERE group_id = ? AND value = ?")
            ->execute([
                'sudran_payment_gateway',
                'درگاه پرداخت سودران',
                '{"requires_card_last4":false,"requires_bank_destination":false,"commission_excluded":true,"admin_only":true}',
                28,
                $paymentMethodsGroupId,
                'sudran_payment_gateway',
            ]);

        // C1 (2026-05-31): retire the legacy "سایر/Other" payment METHOD from operational
        // dropdowns on existing databases. Kept active for historical label resolution; only
        // hidden (is_visible=0) so it no longer appears as a selectable method.
        if ($paymentMethodsGroupId > 0 && $this->connection->hasColumn('lookup_items', 'is_visible')) {
            try {
                $pdo->prepare("UPDATE lookup_items SET is_visible = 0
                    WHERE group_id = ? AND (LOWER(value) = 'other' OR title = 'سایر')")
                    ->execute([$paymentMethodsGroupId]);
            } catch (\PDOException $e) {
                Logger::logWarning('Failed to hide legacy other payment method', ['error' => $e->getMessage()]);
            }
        }

        $banksGroupId = (int)$pdo->query("SELECT id FROM lookup_groups WHERE code = 'payment_destination_banks'")->fetchColumn();
        $banks = [
            ['code' => 'holding_tejarat_current', 'title' => 'تجارت (جاری)', 'value' => 'holding_tejarat_current', 'sort' => 10, 'meta' => '{"bank_name":"تجارت","account_type":"جاری","card_number":"5859831127202942","account_number":"3233510024","sheba":"IR380180000000003233510024","account_owner":"محمدحسین حنفی","tafsili_code":"001"}'],
            ['code' => 'holding_tejarat_savings', 'title' => 'تجارت (پس انداز)', 'value' => 'holding_tejarat_savings', 'sort' => 20, 'active' => 0, 'meta' => '{"bank_name":"تجارت","account_type":"پس انداز","card_number":"5859831180066341","account_number":"7028008","sheba":"IR660180000000000007028008","account_owner":"محمدحسین حنفی"}'],
            ['code' => 'holding_mellat_current', 'title' => 'ملت (جاری)', 'value' => 'holding_mellat_current', 'sort' => 30, 'meta' => '{"bank_name":"ملت","account_type":"جاری","card_number":"6104331095822004","account_number":"7520229337","sheba":"IR350120000000007520229337","account_owner":"محمدحسین حنفی","tafsili_code":"024"}'],
            ['code' => 'holding_mellat_savings', 'title' => 'ملت (پس انداز)', 'value' => 'holding_mellat_savings', 'sort' => 40, 'active' => 0, 'meta' => '{"bank_name":"ملت","account_type":"پس انداز","card_number":"61043379415988888","account_number":"4638773864","sheba":"IR040120020000004638773864","account_owner":"محمدحسین حنفی"}'],
            ['code' => 'holding_melli_current', 'title' => 'ملی (جاری)', 'value' => 'holding_melli_current', 'sort' => 50, 'meta' => '{"bank_name":"ملی","account_type":"جاری","card_number":"6362141141000118","account_number":"121514626007","sheba":"IR590170000000121514626007","account_owner":"محمدحسین حنفی","tafsili_code":"022"}'],
            ['code' => 'holding_melli_savings', 'title' => 'ملی (پس انداز)', 'value' => 'holding_melli_savings', 'sort' => 60, 'active' => 0, 'meta' => '{"bank_name":"ملی","account_type":"پس انداز","card_number":"6037991528588282","account_number":"368170571001","sheba":"IR280170000000368170571001","account_owner":"محمدحسین حنفی"}'],
            ['code' => 'parsian', 'title' => 'بانک پارسیان', 'value' => 'parsian', 'sort' => 110, 'meta' => null],
            ['code' => 'pasargad', 'title' => 'بانک پاسارگاد', 'value' => 'pasargad', 'sort' => 120, 'meta' => null],
            ['code' => 'melli', 'title' => 'بانک ملی', 'value' => 'melli', 'sort' => 130, 'meta' => null],
            ['code' => 'ayandeh', 'title' => 'بانک آینده', 'value' => 'ayandeh', 'sort' => 140, 'meta' => null],
            ['code' => 'melat', 'title' => 'بانک ملت', 'value' => 'melat', 'sort' => 150, 'meta' => null],
            ['code' => 'tejarat', 'title' => 'بانک تجارت', 'value' => 'tejarat', 'sort' => 160, 'meta' => null],
            ['code' => 'other', 'title' => 'سایر', 'value' => 'other', 'sort' => 170, 'meta' => null],
        ];
        $hasVisibleColumn = $this->connection->hasColumn('lookup_items', 'is_visible');
        // Track whether the holding accounts are being seeded for the first time. The
        // legacy-bank hide below must run only on that first seed; otherwise an admin who
        // intentionally re-enables a legacy bank would have it silently re-hidden on every
        // subsequent migration run.
        $holdingJustSeeded = false;
        foreach ($banks as $i => $b) {
            $isHolding = !empty($b['meta']);
            $stmt = $pdo->prepare("SELECT id FROM lookup_items WHERE group_id = ? AND (code = ? OR value = ?)");
            $stmt->execute([$banksGroupId, $b['code'], $b['value']]);
            // پس‌انداز (savings) holding accounts are seeded as inactive so they are not
            // offered as selectable destination cards; admins can enable them per-card.
            $isActiveSeed = isset($b['active']) ? (int)$b['active'] : 1;
            if (!$stmt->fetch()) {
                $pdo->prepare("INSERT INTO lookup_items (group_id, code, title, value, meta_json, sort_order, is_active) VALUES (?, ?, ?, ?, ?, ?, ?)")
                    ->execute([$banksGroupId, $b['code'], $b['title'], $b['value'], $b['meta'] ?? null, $b['sort'] ?? ($i + 1), $isActiveSeed]);
                if ($isHolding) {
                    $holdingJustSeeded = true;
                }
            } elseif ($isHolding) {
                // Refresh holding-account metadata + the seeded active state, only forcing
                // visibility when the column exists. This keeps savings cards inactive.
                $visibleSql = $hasVisibleColumn ? ', is_visible = 1' : '';
                $pdo->prepare("UPDATE lookup_items SET title = ?, meta_json = ?, sort_order = ?, is_active = ?{$visibleSql} WHERE group_id = ? AND (code = ? OR value = ?)")
                    ->execute([$b['title'], $b['meta'], $b['sort'] ?? ($i + 1), $isActiveSeed, $banksGroupId, $b['code'], $b['value']]);
            }
        }
        if ($holdingJustSeeded && $banksGroupId > 0 && $hasVisibleColumn) {
            $legacyCodes = ['parsian', 'pasargad', 'melli', 'ayandeh', 'melat', 'tejarat', 'other'];
            $placeholders = implode(',', array_fill(0, count($legacyCodes), '?'));
            $pdo->prepare("UPDATE lookup_items SET is_visible = 0 WHERE group_id = ? AND code IN ($placeholders)")
                ->execute(array_merge([$banksGroupId], $legacyCodes));
        }
        $this->backfillTransactionDestinationSnapshots($pdo, $banksGroupId);

        // 3. Seed default field configurations with all new fields
        $configs = [
            [
                'field_name' => 'payment_method', 
                'field_label' => 'نحوه پرداخت', 
                'type' => 'select', 
                'lookup' => 'payment_methods', 
                'edit_roles' => ['admin', 'crm_specialist', 'agent'],
                'delete_roles' => ['admin'],
                'editable_after_create' => 1,
                'help_text' => 'نحوه دریافت وجه از مشتری'
            ],
            [
                'field_name' => 'payment_card_last4', 
                'field_label' => 'آخر ۴ رقم کارت', 
                'type' => 'text', 
                'lookup' => null, 
                'edit_roles' => ['admin', 'crm_specialist', 'agent'],
                'delete_roles' => ['admin'],
                'editable_after_create' => 1,
                'validation_pattern' => '^\d{4}$',
                'help_text' => '۴ رقم آخر شماره کارت پرداخت‌کننده',
                'conditional_logic' => '{"field":"payment_method","operator":"in","value":["card_to_card","card_reader","card_to_card_lookup"]}'
            ],
            [
                'field_name' => 'destination_bank', 
                'field_label' => 'بانک مقصد پرداخت', 
                'type' => 'select', 
                'lookup' => 'payment_destination_banks', 
                'edit_roles' => ['admin', 'crm_specialist'],
                'delete_roles' => ['admin'],
                'editable_after_create' => 1,
                'help_text' => 'بانکی که وجه به آن واریز می‌شود',
                'conditional_logic' => '{"field":"payment_method","operator":"in","value":["card_to_card","card_to_card_lookup"]}'
            ],
            [
                'field_name' => 'payment_delete', 
                'field_label' => 'حذف پرداخت', 
                'type' => 'text', 
                'lookup' => null, 
                'edit_roles' => ['admin'],
                'delete_roles' => ['admin'],
                'editable_after_create' => 1
            ],
            [
                'field_name' => 'deal_title', 
                'field_label' => 'عنوان معامله', 
                'type' => 'text', 
                'lookup' => null, 
                'edit_roles' => ['admin'],
                'delete_roles' => ['admin'],
                'editable_after_create' => 0,
                'help_text' => 'عنوان معامله پس از ثبت قابل ویرایش نیست'
            ],
            [
                'field_name' => 'deal_acquaintance_source',
                'field_label' => 'شیوه آشنایی معامله',
                'type' => 'select',
                'lookup' => 'lead_sources',
                'required' => 1,
                'edit_roles' => ['admin', 'agent'],
                'delete_roles' => ['admin'],
                'view_roles' => ['admin'],
                'editable_after_create' => 1,
                'help_text' => 'این مقدار برای هر معامله جدا از پروفایل لید ذخیره می‌شود'
            ],
            [
                'field_name' => 'deal_acquaintance_detail',
                'field_label' => 'جزئیات شیوه آشنایی معامله',
                'type' => 'select',
                'lookup' => 'lead_source_details',
                'required' => 0,
                'edit_roles' => ['admin', 'agent'],
                'delete_roles' => ['admin'],
                'view_roles' => ['admin'],
                'editable_after_create' => 1,
                'help_text' => 'این مقدار برای هر معامله جدا از پروفایل لید ذخیره می‌شود'
            ],
            [
                'field_name' => 'deal_content_topic',
                'field_label' => 'موضوع محتوا معامله',
                'type' => 'select',
                'lookup' => 'content_topics',
                'required' => 1,
                'edit_roles' => ['admin', 'agent'],
                'delete_roles' => ['admin'],
                'view_roles' => ['admin'],
                'editable_after_create' => 1,
                'help_text' => 'این مقدار برای هر معامله جدا از پروفایل لید ذخیره می‌شود'
            ],
        ];

        foreach ($configs as $i => $c) {
            $stmt = $pdo->prepare("SELECT id FROM field_configurations WHERE field_name = ?");
            $stmt->execute([$c['field_name']]);
            if (!$stmt->fetch()) {
                $pdo->prepare("INSERT INTO field_configurations 
                    (field_name, field_label, field_type, entity_type, is_required, is_editable, is_editable_after_create, is_active, is_system, edit_allow_roles, delete_allow_roles, view_allow_roles, lookup_group_code, sort_order, help_text, validation_pattern, conditional_logic) 
                    VALUES (?, ?, ?, 'deal', ?, 1, ?, 1, 0, ?, ?, ?, ?, ?, ?, ?, ?)")
                    ->execute([
                        $c['field_name'], 
                        $c['field_label'], 
                        $c['type'], 
                        $c['required'] ?? 0,
                        $c['editable_after_create'] ?? 1,
                        json_encode($c['edit_roles'] ?? []), 
                        json_encode($c['delete_roles'] ?? []),
                        json_encode($c['view_roles'] ?? ['admin', 'crm_specialist', 'agent']),
                        $c['lookup'], 
                        $i + 1,
                        $c['help_text'] ?? null,
                        $c['validation_pattern'] ?? null,
                        $c['conditional_logic'] ?? null
                    ]);
            }
        }
        // Logger::logInfo("Database seeded successfully with unified lookup codes");
    }

    /**
     * Backfill the destination-account snapshot columns on historical transactions.
     *
     * Transactions written before the snapshot feature only carry `destination_bank`
     * (the lookup value, e.g. "holding_tejarat_current"). When that bank is later hidden
     * in the lookup manager the live lookup can no longer resolve it for reporting/export.
     * Here we copy the bank's title and meta (card/account/sheba) onto rows that are still
     * empty. Idempotent: only fills NULL/empty columns, only for banks that carry meta
     * (legacy banks have no meta and are intentionally left untouched). Best-effort and
     * non-fatal — older MySQL without JSON functions simply skips the backfill.
     */
    private function backfillTransactionDestinationSnapshots(\PDO $pdo, int $banksGroupId): void
    {
        if ($banksGroupId <= 0) {
            return;
        }
        if (!$this->connection->hasColumn('transactions', 'destination_card_full')
            || !$this->connection->hasColumn('transactions', 'destination_bank')) {
            return;
        }
        try {
            $sql = "UPDATE transactions t
                    JOIN lookup_items i
                      ON i.group_id = ?
                     AND i.value = t.destination_bank
                     AND i.meta_json IS NOT NULL
                     AND i.meta_json <> ''
                     AND JSON_VALID(i.meta_json)
                    SET
                      t.destination_bank_title = COALESCE(NULLIF(t.destination_bank_title, ''), i.title),
                      t.destination_card_full = COALESCE(NULLIF(t.destination_card_full, ''), JSON_UNQUOTE(JSON_EXTRACT(i.meta_json, '$.card_number'))),
                      t.destination_account_number = COALESCE(NULLIF(t.destination_account_number, ''), JSON_UNQUOTE(JSON_EXTRACT(i.meta_json, '$.account_number'))),
                      t.destination_sheba = COALESCE(NULLIF(t.destination_sheba, ''), JSON_UNQUOTE(JSON_EXTRACT(i.meta_json, '$.sheba'))),
                      t.destination_account_owner = COALESCE(NULLIF(t.destination_account_owner, ''), JSON_UNQUOTE(JSON_EXTRACT(i.meta_json, '$.account_owner')))
                    WHERE (t.destination_card_full IS NULL OR t.destination_card_full = '')
                      AND t.destination_bank IS NOT NULL
                      AND t.destination_bank <> ''";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$banksGroupId]);
            $affected = $stmt->rowCount();
            if ($affected > 0) {
                Logger::logInfo("Backfilled transaction destination snapshots", ['rows' => $affected]);
            }
        } catch (\Throwable $e) {
            Logger::logWarning("Failed to backfill transaction destination snapshots", ['error' => $e->getMessage()]);
        }
    }

    private function repairCommissionRuleValidityDates(): void
    {
        if (!$this->tableExists('commission_rules')) {
            return;
        }

        $pdo = $this->connection->getPdo();
        foreach (['valid_from', 'valid_to'] as $column) {
            if (!$this->connection->hasColumn('commission_rules', $column)) {
                continue;
            }

            try {
                $stmt = $pdo->query("SELECT id, {$column} FROM commission_rules WHERE {$column} IS NOT NULL");
                $update = $pdo->prepare("UPDATE commission_rules SET {$column} = ? WHERE id = ?");
                foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
                    $raw = trim((string)($row[$column] ?? ''));
                    $normalized = DateNormalizer::normalizeGregorianOrJalaliDate($raw);
                    $target = $normalized ?: null;
                    $current = $raw === '' ? null : $raw;
                    if ($target !== $current) {
                        $update->execute([$target, (int)$row['id']]);
                    }
                }
            } catch (PDOException $e) {
                Logger::logWarning('Failed to repair commission rule validity dates', [
                    'column' => $column,
                    'error' => $e->getMessage(),
                ]);
            }
        }

        $this->repairCommissionRuleAssignmentStartDates();
    }

    private function repairCommissionLineBillingMonths(): void
    {
        if (!$this->tableExists('commission_lines')
            || !$this->connection->hasColumn('commission_lines', 'billing_month')
            || !$this->connection->hasColumn('commission_lines', 'effective_date')) {
            return;
        }

        $pdo = $this->connection->getPdo();
        try {
            $stmt = $pdo->query("
                SELECT id, effective_date, billing_month
                FROM commission_lines
                WHERE effective_date IS NOT NULL
            ");
            $touchUpdatedAt = $this->connection->hasColumn('commission_lines', 'updated_at');
            $sql = $touchUpdatedAt
                ? "UPDATE commission_lines SET billing_month = ?, updated_at = NOW() WHERE id = ?"
                : "UPDATE commission_lines SET billing_month = ? WHERE id = ?";
            $update = $pdo->prepare($sql);
            $repaired = 0;

            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
                $target = $this->billingMonthFromCommissionEffectiveDate((string)($row['effective_date'] ?? ''));
                if ($target === '') {
                    continue;
                }
                $current = trim((string)($row['billing_month'] ?? ''));
                if ($current !== $target) {
                    $update->execute([$target, (int)$row['id']]);
                    $repaired++;
                }
            }

            if ($repaired > 0) {
                Logger::logInfo('Repaired commission line billing months', ['rows' => $repaired]);
            }
        } catch (\Throwable $e) {
            Logger::logWarning('Failed to repair commission line billing months', [
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function billingMonthFromCommissionEffectiveDate(string $effectiveDate): string
    {
        $effectiveDate = trim(substr($effectiveDate, 0, 10));
        if (preg_match('/^(1[34]\d{2})[-\/](0?[1-9]|1[0-2])[-\/]\d{1,2}$/', $effectiveDate, $m)) {
            return sprintf('%04d-%02d', (int)$m[1], (int)$m[2]);
        }

        $normalized = DateNormalizer::normalizeGregorianOrJalaliDate($effectiveDate);
        if ($normalized === null || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $normalized)) {
            return '';
        }

        try {
            [$jy, $jm] = DateNormalizer::gregorianToJalaliParts($normalized);
            return sprintf('%04d-%02d', $jy, $jm);
        } catch (\Throwable $e) {
            return substr($normalized, 0, 7);
        }
    }

    private function repairPendingCommissionPayoutCycleBillingMonths(): void
    {
        if (!$this->tableExists('commission_payout_cycles')
            || !$this->tableExists('commission_payout_cycle_lines')
            || !$this->tableExists('commission_lines')
            || !$this->connection->hasColumn('commission_payout_cycles', 'billing_month')
            || !$this->connection->hasColumn('commission_lines', 'billing_month')) {
            return;
        }

        $pdo = $this->connection->getPdo();
        try {
            $stmt = $pdo->query("
                SELECT cpc.id,
                       cpc.user_id,
                       cpc.billing_month,
                       MIN(cl.billing_month) AS target_month,
                       COUNT(DISTINCT cl.billing_month) AS month_count,
                       SUM(cl.calculated_commission) AS calculated_amount
                FROM commission_payout_cycles cpc
                INNER JOIN commission_payout_cycle_lines cpcl ON cpcl.payout_cycle_id = cpc.id
                INNER JOIN commission_lines cl ON cl.id = cpcl.commission_line_id
                WHERE cpc.status = 'PENDING_APPROVAL'
                  AND cl.billing_month IS NOT NULL
                  AND cl.billing_month <> ''
                GROUP BY cpc.id, cpc.user_id, cpc.billing_month
                HAVING month_count = 1
                   AND target_month <> cpc.billing_month
            ");
            $updateCycle = $pdo->prepare("
                UPDATE commission_payout_cycles
                SET billing_month = ?,
                    calculated_amount = ?,
                    final_payable_amount = CASE
                        WHEN override_amount IS NULL THEN ?
                        ELSE final_payable_amount
                    END,
                    updated_at = NOW()
                WHERE id = ?
            ");
            $moveLines = $pdo->prepare("
                UPDATE IGNORE commission_payout_cycle_lines
                SET payout_cycle_id = ?
                WHERE payout_cycle_id = ?
            ");
            $deleteCycle = $pdo->prepare("DELETE FROM commission_payout_cycles WHERE id = ?");
            $findTarget = $pdo->prepare("
                SELECT id
                FROM commission_payout_cycles
                WHERE user_id = ?
                  AND billing_month = ?
                  AND status = 'PENDING_APPROVAL'
                  AND id <> ?
                LIMIT 1
            ");
            $refreshTarget = $pdo->prepare("
                UPDATE commission_payout_cycles cpc
                SET calculated_amount = (
                        SELECT COALESCE(SUM(cl.calculated_commission), 0)
                        FROM commission_payout_cycle_lines cpcl
                        INNER JOIN commission_lines cl ON cl.id = cpcl.commission_line_id
                        WHERE cpcl.payout_cycle_id = cpc.id
                    ),
                    final_payable_amount = CASE
                        WHEN override_amount IS NULL THEN (
                            SELECT COALESCE(SUM(cl2.calculated_commission), 0)
                            FROM commission_payout_cycle_lines cpcl2
                            INNER JOIN commission_lines cl2 ON cl2.id = cpcl2.commission_line_id
                            WHERE cpcl2.payout_cycle_id = cpc.id
                        )
                        ELSE final_payable_amount
                    END,
                    updated_at = NOW()
                WHERE cpc.id = ?
            ");

            $repaired = 0;
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
                $cycleId = (int)($row['id'] ?? 0);
                $userId = (int)($row['user_id'] ?? 0);
                $targetMonth = trim((string)($row['target_month'] ?? ''));
                if ($cycleId <= 0 || $userId <= 0 || !preg_match('/^1[34]\d{2}-\d{2}$/', $targetMonth)) {
                    continue;
                }

                $findTarget->execute([$userId, $targetMonth, $cycleId]);
                $targetCycleId = (int)$findTarget->fetchColumn();
                if ($targetCycleId > 0) {
                    $moveLines->execute([$targetCycleId, $cycleId]);
                    $refreshTarget->execute([$targetCycleId]);
                    $deleteCycle->execute([$cycleId]);
                } else {
                    $amount = round((float)($row['calculated_amount'] ?? 0), 2);
                    $updateCycle->execute([$targetMonth, $amount, $amount, $cycleId]);
                }
                $repaired++;
            }

            if ($repaired > 0) {
                Logger::logInfo('Repaired pending commission payout cycle billing months', ['cycles' => $repaired]);
            }
        } catch (\Throwable $e) {
            Logger::logWarning('Failed to repair pending commission payout cycle billing months', [
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function repairOrphanBalanceCommissionLines(): void
    {
        if (!$this->tableExists('commission_lines')
            || !$this->tableExists('payment_records')
            || !$this->connection->hasColumn('commission_lines', 'status')
            || !$this->connection->hasColumn('commission_lines', 'payment_record_id')
            || !$this->connection->hasColumn('payment_records', 'payment_sequence')) {
            return;
        }

        $pdo = $this->connection->getPdo();
        try {
            $pdo->exec("DROP TEMPORARY TABLE IF EXISTS tmp_orphan_balance_commission_lines");
            $pdo->exec("
                CREATE TEMPORARY TABLE tmp_orphan_balance_commission_lines (
                    id BIGINT PRIMARY KEY,
                    payment_record_id BIGINT NOT NULL
                ) ENGINE=MEMORY
            ");
            $pdo->exec("
                INSERT INTO tmp_orphan_balance_commission_lines (id, payment_record_id)
                SELECT cl.id, cl.payment_record_id
                FROM commission_lines cl
                INNER JOIN payment_records pr ON pr.id = cl.payment_record_id
                WHERE cl.status = 'CALCULATED'
                  AND COALESCE(pr.payment_sequence, 1) > 1
                  AND NOT EXISTS (
                      SELECT 1
                      FROM payment_records prev
                      INNER JOIN commission_lines prior_line
                        ON prior_line.payment_record_id = prev.id
                       AND COALESCE(prior_line.status, 'CALCULATED') <> 'VOID'
                       AND prior_line.calculated_commission > 0
                       AND prior_line.id < cl.id
                      WHERE (
                              (pr.deal_id IS NOT NULL AND prev.deal_id = pr.deal_id)
                           OR (COALESCE(pr.deal_didar_id, '') <> '' AND prev.deal_didar_id = pr.deal_didar_id)
                      )
                        AND prev.id <> pr.id
                        AND prev.amount > 0
                        AND UPPER(COALESCE(prev.refund_status, 'NONE')) <> 'FULL'
                        AND COALESCE(prev.is_deleted_placeholder, 0) = 0
                        AND COALESCE(prev.payment_sequence, 1) <= 1
                        AND COALESCE(DATE(prev.effective_date), DATE(prev.deposit_date), DATE(prev.payment_date), DATE(prev.cheque_collection_date), DATE(prev.registered_at), DATE(prev.created_at))
                            <= COALESCE(DATE(pr.effective_date), DATE(pr.deposit_date), DATE(pr.payment_date), DATE(pr.cheque_collection_date), DATE(pr.registered_at), DATE(pr.created_at))
                  )
            ");

            $countStmt = $pdo->query("SELECT COUNT(*) FROM tmp_orphan_balance_commission_lines");
            $orphanCount = (int)$countStmt->fetchColumn();

            $cycleIds = [];
            if ($orphanCount > 0 && $this->tableExists('commission_payout_cycles') && $this->tableExists('commission_payout_cycle_lines')) {
                $stmt = $pdo->query("
                    SELECT DISTINCT cpc.id
                    FROM commission_payout_cycles cpc
                    INNER JOIN commission_payout_cycle_lines cpcl ON cpcl.payout_cycle_id = cpc.id
                    INNER JOIN tmp_orphan_balance_commission_lines tmp ON tmp.id = cpcl.commission_line_id
                    WHERE cpc.status = 'PENDING_APPROVAL'
                ");
                $cycleIds = array_values(array_filter(array_map('intval', $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [])));

                $pdo->exec("
                    DELETE cpcl
                    FROM commission_payout_cycle_lines cpcl
                    INNER JOIN commission_payout_cycles cpc ON cpc.id = cpcl.payout_cycle_id
                    INNER JOIN tmp_orphan_balance_commission_lines tmp ON tmp.id = cpcl.commission_line_id
                    WHERE cpc.status = 'PENDING_APPROVAL'
                ");
            }

            $affected = 0;
            if ($orphanCount > 0) {
                $affected = $pdo->exec("
                    UPDATE commission_lines cl
                    INNER JOIN tmp_orphan_balance_commission_lines tmp ON tmp.id = cl.id
                    INNER JOIN payment_records pr ON pr.id = tmp.payment_record_id
                    SET cl.status = 'VOID',
                        cl.updated_at = NOW(),
                        pr.commission_status = CASE
                            WHEN pr.commission_status IN ('PENDING', 'CALCULATED') THEN 'VOID'
                            ELSE pr.commission_status
                        END,
                        pr.updated_at = NOW()
                ");
            }

            $pdo->exec("
                UPDATE payment_records pr
                SET pr.commission_status = 'VOID',
                    pr.updated_at = NOW()
                WHERE pr.commission_status IN ('PENDING', 'CALCULATED')
                  AND COALESCE(pr.payment_sequence, 1) > 1
                  AND EXISTS (
                      SELECT 1
                      FROM commission_lines cl
                      WHERE cl.payment_record_id = pr.id
                        AND cl.status = 'VOID'
                  )
                  AND NOT EXISTS (
                      SELECT 1
                      FROM commission_lines active_line
                      WHERE active_line.payment_record_id = pr.id
                        AND active_line.status <> 'VOID'
                  )
            ");

            if (!empty($cycleIds)) {
                $refresh = $pdo->prepare("
                    UPDATE commission_payout_cycles cpc
                    SET calculated_amount = (
                            SELECT COALESCE(SUM(cl.calculated_commission), 0)
                            FROM commission_payout_cycle_lines cpcl
                            INNER JOIN commission_lines cl ON cl.id = cpcl.commission_line_id
                            WHERE cpcl.payout_cycle_id = cpc.id
                              AND cl.status = 'CALCULATED'
                        ),
                        final_payable_amount = CASE
                            WHEN override_amount IS NULL THEN (
                                SELECT COALESCE(SUM(cl2.calculated_commission), 0)
                                FROM commission_payout_cycle_lines cpcl2
                                INNER JOIN commission_lines cl2 ON cl2.id = cpcl2.commission_line_id
                                WHERE cpcl2.payout_cycle_id = cpc.id
                                  AND cl2.status = 'CALCULATED'
                            )
                            ELSE final_payable_amount
                        END,
                        updated_at = NOW()
                    WHERE cpc.id = ?
                ");
                foreach ($cycleIds as $cycleId) {
                    $refresh->execute([$cycleId]);
                }
                $pdo->exec("
                    DELETE cpc
                    FROM commission_payout_cycles cpc
                    LEFT JOIN commission_payout_cycle_lines cpcl ON cpcl.payout_cycle_id = cpc.id
                    WHERE cpc.status = 'PENDING_APPROVAL'
                      AND cpcl.id IS NULL
                ");
            }

            if ((int)$affected > 0) {
                Logger::logInfo('Voided orphan balance commission lines', ['lines' => (int)$affected]);
            }
        } catch (\Throwable $e) {
            Logger::logWarning('Failed to repair orphan balance commission lines', [
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function migrateCommissionAssignmentValidityColumns(): void
    {
        if (!$this->tableExists('commission_rule_assignments')) {
            return;
        }

        $this->addColumnIfMissing('commission_rule_assignments', 'valid_from', 'DATE NULL AFTER assigned_at');
        $this->addColumnIfMissing('commission_rule_assignments', 'valid_to', 'DATE NULL AFTER valid_from');
        $this->addColumnIfMissing('commission_rule_assignments', 'source_type', "ENUM('INDIVIDUAL','TEAM') NOT NULL DEFAULT 'INDIVIDUAL' AFTER valid_to");
        $this->addColumnIfMissing('commission_rule_assignments', 'source_team_supervisor_user_id', 'INT NULL AFTER source_type');
        $this->ensureIndex('commission_rule_assignments', 'idx_assignment_validity', 'valid_from, valid_to');
        $this->ensureIndex('commission_rule_assignments', 'idx_assignment_source_team', 'source_type, source_team_supervisor_user_id');
    }

    private function repairCommissionRuleAssignmentStartDates(): void
    {
        if (!$this->tableExists('commission_rules') || !$this->tableExists('commission_rule_assignments')) {
            return;
        }
        if (
            !$this->connection->hasColumn('commission_rules', 'valid_from') ||
            !$this->connection->hasColumn('commission_rule_assignments', 'assigned_at')
        ) {
            return;
        }

        $pdo = $this->connection->getPdo();
        try {
            $where = [
                'cr.valid_from IS NOT NULL',
                'DATE(cra.assigned_at) > cr.valid_from',
            ];
            if ($this->connection->hasColumn('commission_rule_assignments', 'disabled_at')) {
                $where[] = 'cra.disabled_at IS NULL';
            }
            $stmt = $pdo->query("
                SELECT cra.id, cr.valid_from
                FROM commission_rule_assignments cra
                INNER JOIN commission_rules cr ON cr.id = cra.rule_id
                WHERE " . implode(' AND ', $where)
            );
            $update = $pdo->prepare('UPDATE commission_rule_assignments SET assigned_at = ? WHERE id = ?');
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
                $validFrom = DateNormalizer::normalizeGregorianOrJalaliDate($row['valid_from'] ?? '');
                if ($validFrom === null) {
                    continue;
                }
                $update->execute([$validFrom . ' 00:00:00', (int)$row['id']]);
            }
        } catch (PDOException $e) {
            Logger::logWarning('Failed to repair commission rule assignment start dates', [
                'error' => $e->getMessage(),
            ]);
        }
    }

    /**
     * Populate derived financial-listing cache columns on existing transactions.
     *
     * These columns are additive read-model fields used to avoid DATE(COALESCE(...))
     * and method IN/NOT IN expressions on every finance screen load. They can always
     * be rebuilt from the source transaction columns, so this update is idempotent.
     */
    private function backfillTransactionFinancialCacheColumns(\PDO $pdo): void
    {
        if (!$this->connection->hasColumn('transactions', 'payment_screen_at')
            || !$this->connection->hasColumn('transactions', 'payment_screen_date')
            || !$this->connection->hasColumn('transactions', 'is_cheque_payment')) {
            return;
        }

        try {
            $affected = (int)$pdo->exec("
                UPDATE transactions
                SET
                    payment_screen_at = COALESCE(created_at, payment_time, TIMESTAMP(payment_date)),
                    payment_screen_date = DATE(COALESCE(created_at, payment_time, TIMESTAMP(payment_date))),
                    is_cheque_payment = CASE
                        WHEN method IN ('check', 'cheque', 'چک') THEN 1
                        ELSE 0
                    END
                WHERE payment_screen_at IS NULL
                   OR payment_screen_date IS NULL
                   OR is_cheque_payment <> CASE
                        WHEN method IN ('check', 'cheque', 'چک') THEN 1
                        ELSE 0
                    END
            ");
            if ($affected > 0) {
                Logger::logInfo('Backfilled transaction financial cache columns', ['rows' => $affected]);
            }
        } catch (\Throwable $e) {
            Logger::logWarning('Failed to backfill transaction financial cache columns', ['error' => $e->getMessage()]);
        }
    }

    /**
     * Public entry point to (re)sync the RBAC catalog into the DB on demand:
     * upserts roles/capabilities/default grants/default scopes and deactivates
     * capabilities removed from the catalog. Idempotent and customization-safe
     * (existing grant/scope edits are preserved via no-op ON DUPLICATE KEY UPDATE).
     */
    public function reconcileRbac(): void
    {
        $this->seedRbacFoundation();
    }

    private function seedRbacFoundation(): void
    {
        $pdo = $this->connection->getPdo();
        try {
            $pdo->query("SELECT 1 FROM rbac_roles LIMIT 1");
            $pdo->query("SELECT 1 FROM rbac_capabilities LIMIT 1");
            $pdo->query("SELECT 1 FROM rbac_role_data_scopes LIMIT 1");
            $pdo->query("SELECT 1 FROM rbac_role_scope_users LIMIT 1");
        } catch (\Throwable $e) {
            return;
        }

        $roles = [
            ['role_key' => 'admin', 'title' => 'مدیر عامل', 'is_system' => 1],
            ['role_key' => 'sales_manager', 'title' => 'مدیر فروش', 'is_system' => 1],
            ['role_key' => 'management', 'title' => 'مدیریت', 'is_system' => 1],
            ['role_key' => 'crm_specialist', 'title' => 'کارشناس CRM', 'is_system' => 1],
            ['role_key' => 'agent', 'title' => 'کارشناس فروش', 'is_system' => 1],
            ['role_key' => 'accountant', 'title' => 'مالی', 'is_system' => 1],
            ['role_key' => 'support', 'title' => 'پشتیبانی', 'is_system' => 1],
            ['role_key' => 'system', 'title' => 'سیستم', 'is_system' => 1],
        ];
        $roleStmt = $pdo->prepare("
            INSERT INTO rbac_roles (role_key, title, is_system, is_active, created_at, updated_at)
            VALUES (?, ?, ?, 1, NOW(), NOW())
            ON DUPLICATE KEY UPDATE title = VALUES(title), is_system = VALUES(is_system), updated_at = NOW()
        ");
        foreach ($roles as $role) {
            $roleStmt->execute([$role['role_key'], $role['title'], $role['is_system']]);
        }

        $caps = RbacCatalog::capabilities();
        $capStmt = $pdo->prepare("
            INSERT INTO rbac_capabilities (capability_key, title, category, module, parent_key, sort_order, is_system, is_active, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, 1, 1, NOW(), NOW())
            ON DUPLICATE KEY UPDATE
                title = VALUES(title),
                category = VALUES(category),
                module = VALUES(module),
                parent_key = VALUES(parent_key),
                sort_order = VALUES(sort_order),
                is_active = 1,
                updated_at = NOW()
        ");
        foreach ($caps as $cap) {
            $capStmt->execute([$cap['key'], $cap['title'], $cap['cat'], $cap['module'], $cap['parent'], $cap['sort']]);
        }

        // Deprovision: deactivate any capability still in the DB that is no longer in the
        // catalog. getCapabilities()/can() both require is_active = 1, so a removed
        // capability stops granting access (no privilege drift) while its grant rows stay
        // inert and self-heal if the capability is later re-added to the catalog.
        $catalogKeys = array_map(static fn($c) => (string)$c['key'], $caps);
        if (!empty($catalogKeys)) {
            $ph = implode(',', array_fill(0, count($catalogKeys), '?'));
            $deactivate = $pdo->prepare(
                "UPDATE rbac_capabilities SET is_active = 0, updated_at = NOW() WHERE capability_key NOT IN ($ph) AND is_active = 1"
            );
            $deactivate->execute($catalogKeys);
        }

        $roleRows = $pdo->query("SELECT id, role_key FROM rbac_roles")->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $capRows = $pdo->query("SELECT id, capability_key FROM rbac_capabilities")->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $roleIds = [];
        foreach ($roleRows as $r) $roleIds[(string)$r['role_key']] = (int)$r['id'];
        $capIds = [];
        foreach ($capRows as $c) $capIds[(string)$c['capability_key']] = (int)$c['id'];

        $grantMap = RbacCatalog::defaultGrants();

        $grantStmt = $pdo->prepare("
            INSERT INTO rbac_role_capabilities (role_id, capability_id, effect, created_at)
            VALUES (?, ?, 'allow', NOW())
            ON DUPLICATE KEY UPDATE effect = effect
        ");
        foreach ($grantMap as $roleKey => $capKeys) {
            if (empty($roleIds[$roleKey])) continue;
            foreach ($capKeys as $capKey) {
                if (empty($capIds[$capKey])) continue;
                $grantStmt->execute([$roleIds[$roleKey], $capIds[$capKey]]);
            }
        }

        $scopeStmt = $pdo->prepare("
            INSERT INTO rbac_role_data_scopes (role_id, resource_key, scope_type, created_at, updated_at)
            VALUES (?, ?, ?, NOW(), NOW())
            ON DUPLICATE KEY UPDATE scope_type = scope_type
        ");
        foreach (array_keys($roleIds) as $roleKey) {
            if (empty($roleIds[$roleKey])) continue;
            $scopeType = RbacCatalog::defaultScopeTypeForRole($roleKey);
            foreach (RbacCatalog::resourceKeys() as $resourceKey) {
                $scopeStmt->execute([$roleIds[$roleKey], $resourceKey, $scopeType]);
            }
        }
    }

    /**
     * Migrate payments from JSON to transactions table
     */
    public function migratePaymentsToTransactions(): void
    {
        $pdo = $this->connection->getPdo();

        // Check if migration already ran
        $stmt = $pdo->query("SELECT COUNT(*) FROM transactions");
        $existingCount = $stmt->fetchColumn();

        if ($existingCount > 0) {
            // Logger::logInfo("Payments migration already completed - skipped", ['existing_transactions' => $existingCount]);
            return;
        }

        // Get all deals with payments
        $stmt = $pdo->prepare("SELECT id, payments, won_time FROM deals WHERE payments IS NOT NULL AND payments != '' AND payments != 'null'");
        $stmt->execute();
        $deals = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $migrated = 0;
        $skipped = 0;
        $totalAmount = 0;

        Logger::logInfo("Starting payments migration", ['deals_with_payments' => count($deals)]);

        foreach ($deals as $deal) {
            try {
                $paymentsJson = $deal['payments'];
                $payments = json_decode($paymentsJson, true);

                if (!is_array($payments) || empty($payments)) {
                    $skipped++;
                    continue;
                }

                // Sort payments by date to find first payment
                usort($payments, function($a, $b) {
                    $dateA = $a['date'] ?? ($a['date_persian'] ?? '1970-01-01');
                    $dateB = $b['date'] ?? ($b['date_persian'] ?? '1970-01-01');
                    return strtotime($dateA) - strtotime($dateB);
                });

                foreach ($payments as $index => $payment) {
                    $amount = floatval($payment['amount'] ?? 0);
                    if ($amount <= 0) {
                        continue;
                    }

                    // Convert Persian date to Gregorian if needed
                    $dateStr = $payment['date'] ?? ($payment['date_persian'] ?? '');
                    if (preg_match('/^\d{4}\/\d{2}\/\d{2}$/', $dateStr)) {
                        // Persian date: convert
                        $parts = explode('/', $dateStr);
                        $gregorian = $this->jalaliToGregorian($parts[0], $parts[1], $parts[2]);
                        $dateStr = sprintf('%04d-%02d-%02d', $gregorian[0], $gregorian[1], $gregorian[2]);
                    } else {
                        // Already Gregorian or invalid - try to validate
                        $testDate = \DateTime::createFromFormat('Y-m-d', $dateStr);
                        if (!$testDate) {
                            // If invalid, use won_time or current date
                            if ($deal['won_time']) {
                                $dateStr = substr($deal['won_time'], 0, 10);
                            } else {
                                $dateStr = date('Y-m-d');
                            }
                        }
                    }

                    $isFirstPayment = ($index === 0) ? 1 : 0;

                    // Normalize payment fields
                    $method = $payment['method'] ?? $payment['payment_method'] ?? null;
                    $cardLast4 = $payment['card_last4'] ?? $payment['card_last_4'] ?? null;
                    $destinationBank = $payment['destination_bank'] ?? $payment['bank_destination'] ?? null;
                    $description = $payment['description'] ?? null;

                    $stmt = $pdo->prepare("
                        INSERT INTO transactions (deal_id, amount, payment_date, method, card_last4, destination_bank, description, is_first_payment)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    ");

                    $stmt->execute([
                        $deal['id'],
                        $amount,
                        $dateStr,
                        $method,
                        $cardLast4,
                        $destinationBank,
                        $description,
                        $isFirstPayment
                    ]);

                    $migrated++;
                    $totalAmount += $amount;
                }

            } catch (\Exception $e) {
                Logger::logError("Failed to migrate payments for deal {$deal['id']}", $e);
                $skipped++;
            }
        }

        Logger::logInfo("Payment migration completed", [
            'migrated' => $migrated,
            'skipped' => $skipped,
            'total_amount' => $totalAmount,
            'total_deals' => count($deals)
        ]);
    }

    private function paymentSnapshotMatchKey(array $payment): string
    {
        return implode('|', [
            number_format((float)($payment['amount'] ?? 0), 2, '.', ''),
            $this->normalizePaymentSnapshotDate($this->firstNonEmptyPaymentSnapshotValue($payment, ['date', 'payment_date', 'date_persian', 'payment_date_persian'])),
            strtolower(trim((string)($payment['method'] ?? $payment['payment_method'] ?? ''))),
        ]);
    }

    private function firstNonEmptyPaymentSnapshotValue(array $payment, array $keys): string
    {
        foreach ($keys as $key) {
            $value = trim((string)($payment[$key] ?? ''));
            if ($value !== '') {
                return $value;
            }
        }
        return '';
    }

    private function normalizePaymentSnapshotDate(string $value): string
    {
        $value = strtr(trim($value), [
            '۰' => '0', '۱' => '1', '۲' => '2', '۳' => '3', '۴' => '4',
            '۵' => '5', '۶' => '6', '۷' => '7', '۸' => '8', '۹' => '9',
            '٠' => '0', '١' => '1', '٢' => '2', '٣' => '3', '٤' => '4',
            '٥' => '5', '٦' => '6', '٧' => '7', '٨' => '8', '٩' => '9',
        ]);
        if ($value === '') {
            return '';
        }

        $normalized = str_replace('-', '/', $value);
        if (preg_match('/^(1[34]\d{2}|15\d{2})\/(\d{1,2})\/(\d{1,2})/', $normalized, $m)) {
            $gregorian = $this->jalaliToGregorian((int)$m[1], (int)$m[2], (int)$m[3]);
            return sprintf('%04d-%02d-%02d', $gregorian[0], $gregorian[1], $gregorian[2]);
        }

        if (preg_match('/^(\d{4})\/(\d{1,2})\/(\d{1,2})/', $normalized, $m)) {
            return sprintf('%04d-%02d-%02d', (int)$m[1], (int)$m[2], (int)$m[3]);
        }

        $timestamp = strtotime($value);
        return $timestamp ? date('Y-m-d', $timestamp) : '';
    }

    private function repairDealPaymentRegisteredAtSnapshots(): void
    {
        if (!$this->tableExists('deals') || !$this->tableExists('transactions')) {
            return;
        }
        if (!$this->connection->hasColumn('deals', 'payments') || !$this->connection->hasColumn('transactions', 'deal_id')) {
            return;
        }

        $pdo = $this->connection->getPdo();
        try {
            $columns = ['id', 'amount', 'payment_date', 'method'];
            foreach (['payment_sequence', 'payment_screen_at', 'created_at', 'registered_by_didar_id', 'registered_by_name', 'payment_owner_locked_at'] as $column) {
                if ($this->connection->hasColumn('transactions', $column)) {
                    $columns[] = $column;
                }
            }

            $dealStmt = $pdo->query("SELECT id, payments FROM deals WHERE payments IS NOT NULL AND payments <> '' AND payments <> 'null'");
            $txStmt = $pdo->prepare('SELECT ' . implode(', ', array_unique($columns)) . ' FROM transactions WHERE deal_id = ? ORDER BY id ASC');
            $updateStmt = $pdo->prepare('UPDATE deals SET payments = ? WHERE id = ?');

            $dealsUpdated = 0;
            $paymentsUpdated = 0;

            foreach (($dealStmt->fetchAll(PDO::FETCH_ASSOC) ?: []) as $deal) {
                $payments = json_decode((string)($deal['payments'] ?? ''), true);
                if (!is_array($payments) || empty($payments)) {
                    continue;
                }

                $txStmt->execute([(int)$deal['id']]);
                $transactions = $txStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

                $bySequence = [];
                $byMatch = [];
                foreach ($transactions as $transaction) {
                    $sequence = (int)($transaction['payment_sequence'] ?? 0);
                    if ($sequence > 0 && !isset($bySequence[$sequence])) {
                        $bySequence[$sequence] = $transaction;
                    }

                    $key = implode('|', [
                        number_format((float)($transaction['amount'] ?? 0), 2, '.', ''),
                        substr(trim((string)($transaction['payment_date'] ?? '')), 0, 10),
                        strtolower(trim((string)($transaction['method'] ?? ''))),
                    ]);
                    if (!isset($byMatch[$key])) {
                        $byMatch[$key] = $transaction;
                    }
                }

                $changed = false;
                foreach ($payments as $idx => $payment) {
                    if (!is_array($payment) || !empty($payment['deleted']) || !empty($payment['is_deleted'])) {
                        continue;
                    }
                    if (trim((string)($payment['registered_at'] ?? '')) !== '') {
                        continue;
                    }

                    $sequence = (int)($payment['payment_sequence'] ?? 0);
                    $transaction = $sequence > 0 ? ($bySequence[$sequence] ?? null) : null;
                    if (!$transaction) {
                        $transaction = $byMatch[$this->paymentSnapshotMatchKey($payment)] ?? null;
                    }
                    if (!$transaction && isset($transactions[$idx])) {
                        $transaction = $transactions[$idx];
                    }
                    $registeredAt = $transaction
                        ? trim((string)($transaction['payment_screen_at'] ?? $transaction['created_at'] ?? ''))
                        : '';
                    if ($registeredAt === '') {
                        $paymentDate = $this->normalizePaymentSnapshotDate(
                            $this->firstNonEmptyPaymentSnapshotValue($payment, ['date', 'payment_date', 'date_persian', 'payment_date_persian'])
                        );
                        if ($paymentDate !== '') {
                            $registeredAt = $paymentDate . ' 00:00:00';
                        }
                    }
                    if ($registeredAt === '') {
                        continue;
                    }

                    $payments[$idx]['registered_at'] = $registeredAt;
                    if ($transaction) {
                        foreach (['registered_by_didar_id', 'registered_by_name', 'payment_owner_locked_at'] as $field) {
                            if (trim((string)($payments[$idx][$field] ?? '')) === '' && trim((string)($transaction[$field] ?? '')) !== '') {
                                $payments[$idx][$field] = trim((string)$transaction[$field]);
                            }
                        }
                    }
                    $paymentsUpdated++;
                    $changed = true;
                }

                if ($changed) {
                    $updateStmt->execute([
                        json_encode($payments, JSON_UNESCAPED_UNICODE),
                        (int)$deal['id'],
                    ]);
                    $dealsUpdated++;
                }
            }

            if ($paymentsUpdated > 0) {
                Logger::logInfo('Repaired deal payment registered_at snapshots', [
                    'deals' => $dealsUpdated,
                    'payments' => $paymentsUpdated,
                ]);
            }
        } catch (PDOException $e) {
            Logger::logWarning('Failed to repair deal payment registered_at snapshots', ['error' => $e->getMessage()]);
        }
    }

    /**
     * Convert Jalali date to Gregorian
     */
    private function jalaliToGregorian($jy, $jm, $jd): array
    {
        // Simple conversion - can be improved with proper library
        // For now, using basic algorithm
        $jy = (int) $jy;
        $jm = (int) $jm;
        $jd = (int) $jd;

        // Check if jy is 1404 or 1405 and convert approximately
        // 1404/09/01 ≈ 2024-11-21
        // This is a simplified approach
        if ($jy >= 1400) {
            $gy = $jy - 621;
            if ($jm > 3) {
                $gy++;
            }
        } else {
            $gy = $jy + 621;
        }

        // Approximate month conversion
        $gm = $jm;
        if ($gm > 3) {
            $gm = $gm - 3;
        } else {
            $gm = $gm + 9;
            $gy--;
        }

        $gd = min($jd, 31);

        // Validate and adjust
        $checkDate = \DateTime::createFromFormat('Y-m-d', sprintf('%04d-%02d-%02d', $gy, $gm, $gd));
        if (!$checkDate) {
            // Fallback to a reasonable date
            return [2024, 11, 1];
        }

        return [$gy, $gm, $gd];
    }
}
