<?php
namespace App\Database;

use App\Utils\Logger;
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();

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

        // 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();
    }

    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()
            ]);
        }
    }

    /**
     * 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',
            '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)',
        ];

        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()]);
                }
            }
        }

        // 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',
            'is_legacy_didar' => 'TINYINT(1) DEFAULT 0',
            'pipeline_stage' => 'VARCHAR(100)',
	            '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()]);
                }
            }
        }

        // 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()]);
                    }
                }
            }
        }

	        // 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()]);
                }
            }
        }
        if ($this->connection->hasColumn('deals', 'invoice_code')) {
            $this->ensureIndex('deals', 'idx_invoice_code', 'invoice_code');
        }

        // 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();
        }

        // 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', 'deposit_date')) {
            try {
                $pdo->exec("ALTER TABLE transactions ADD COLUMN deposit_date DATE NULL AFTER payment_date");
                $pdo->exec("UPDATE transactions SET deposit_date = payment_date WHERE deposit_date IS NULL");
                Logger::logInfo("Added column to transactions table", ['column' => 'deposit_date']);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", ['table' => 'transactions', 'column' => 'deposit_date', 'error' => $e->getMessage()]);
            }
        }
        if ($this->connection->hasColumn('transactions', 'id') && !$this->connection->hasColumn('transactions', 'is_accepted_by_finance')) {
            try {
                $pdo->exec("ALTER TABLE transactions ADD COLUMN is_accepted_by_finance TINYINT(1) NOT NULL DEFAULT 1 AFTER is_first_payment");
                $pdo->exec("UPDATE transactions SET is_accepted_by_finance = CASE WHEN status IN ('confirmed','settled','cleared') THEN 1 ELSE 0 END");
                Logger::logInfo("Added column to transactions table", ['column' => 'is_accepted_by_finance']);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", ['table' => 'transactions', 'column' => 'is_accepted_by_finance', 'error' => $e->getMessage()]);
            }
        }
        if ($this->connection->hasColumn('transactions', 'deposit_date')) {
            $this->ensureIndex('transactions', 'idx_transaction_deposit_date', 'deposit_date');
        }
        if ($this->connection->hasColumn('transactions', 'is_accepted_by_finance')) {
            $this->ensureIndex('transactions', 'idx_transaction_finance_acceptance', 'is_accepted_by_finance');
        }
        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()]);
            }
        }
        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 and indexes for commission_rules table
        if ($this->connection->hasColumn('commission_rules', 'id')) {
            $commissionRuleColumns = [
                'title' => 'VARCHAR(255) NULL',
                'salesperson_user_id' => 'INT NULL',
                'product_id' => 'INT NULL',
                'rule_type' => "ENUM('FIXED_RULE','TIERED_RULE') NOT NULL DEFAULT 'TIERED_RULE'",
                'formula_type' => "ENUM('FIXED','TIERED_MARGINAL') NOT NULL DEFAULT 'TIERED_MARGINAL'",
                'base_rate_percent' => 'DECIMAL(8,4) NOT NULL DEFAULT 0',
                'fixed_budget_amount' => 'DECIMAL(15,2) NULL',
                'base_amount' => 'DECIMAL(15,2) NULL',
                'reward_percentage' => 'DECIMAL(8,4) NULL',
                'standard_tier_rate_percent' => 'DECIMAL(8,4) NULL',
                'collection_modifier_percent' => 'DECIMAL(8,4) NULL',
                'allow_collection_modifier_override' => 'TINYINT(1) NOT NULL DEFAULT 0',
                'target_users' => 'TEXT NULL',
                'requested_services' => 'TEXT NULL',
                'valid_from' => 'DATE NULL',
                'valid_to' => 'DATE NULL',
                'is_active' => 'TINYINT(1) DEFAULT 1',
                'status' => "ENUM('active','inactive') NOT NULL DEFAULT 'active'",
                'created_by_user_id' => 'INT NULL',
                'updated_by_user_id' => 'INT NULL',
                'deleted_by_user_id' => 'INT NULL',
                'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
                'updated_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
                'deleted_at' => 'DATETIME NULL',
            ];
            foreach ($commissionRuleColumns as $column => $type) {
                if (!$this->connection->hasColumn('commission_rules', $column)) {
                    try {
                        $pdo->exec("ALTER TABLE commission_rules ADD COLUMN {$column} {$type}");
                        Logger::logInfo("Added column to commission_rules table", ['column' => $column]);
                    } catch (PDOException $e) {
                        Logger::logWarning("Failed to add column", ['table' => 'commission_rules', 'column' => $column, 'error' => $e->getMessage()]);
                    }
                }
            }
            $this->ensureIndex('commission_rules', 'idx_salesperson_user_id', 'salesperson_user_id');
            $this->ensureIndex('commission_rules', 'idx_product_id', 'product_id');
            $this->ensureIndex('commission_rules', 'idx_rule_type', 'rule_type');
            $this->ensureIndex('commission_rules', 'idx_rule_formula_type', 'formula_type');
            $this->ensureIndex('commission_rules', 'idx_rule_status', 'status');
            $this->ensureIndex('commission_rules', 'idx_rule_validity', 'valid_from, valid_to');
            $this->ensureIndex('commission_rules', 'idx_rule_active', 'is_active');
            $this->ensureIndex('commission_rules', 'idx_rule_deleted_at', 'deleted_at');
            $this->normalizeCommissionRuleEnums();
        }

        $this->ensureCommissionSupportTables();

        if ($this->connection->hasColumn('deal_payments', 'id')) {
            $dealPaymentColumns = [
                'payment_type' => "ENUM('CASH','TRANSFER','CHEQUE') NOT NULL DEFAULT 'CASH'",
                'payment_status' => "ENUM('PENDING','RECEIVED','CHEQUE_PENDING','CHEQUE_CASHED','REJECTED') NOT NULL DEFAULT 'PENDING'",
                'payment_amount' => 'DECIMAL(15,2) NOT NULL DEFAULT 0',
                'receipt_date' => 'DATETIME NULL',
                'deposit_date' => 'DATE NULL',
                'cheque_due_date' => 'DATETIME NULL',
                'cheque_cashed_date' => 'DATETIME NULL',
                'is_accepted_by_finance' => 'TINYINT(1) NOT NULL DEFAULT 0',
                'accepted_by_user_id' => 'INT NULL',
                'accepted_at' => 'DATETIME NULL',
                'rejected_by_user_id' => 'INT NULL',
                'rejected_at' => 'DATETIME NULL',
            ];
            foreach ($dealPaymentColumns as $column => $type) {
                if (!$this->connection->hasColumn('deal_payments', $column)) {
                    try {
                        $pdo->exec("ALTER TABLE deal_payments ADD COLUMN {$column} {$type}");
                        Logger::logInfo("Added column to deal_payments table", ['column' => $column]);
                    } catch (PDOException $e) {
                        Logger::logWarning("Failed to add column", ['table' => 'deal_payments', 'column' => $column, 'error' => $e->getMessage()]);
                    }
                }
            }
            if ($this->connection->hasColumn('deal_payments', 'deposit_date')) {
                try {
                    $pdo->exec("
                        UPDATE deal_payments
                        SET deposit_date = DATE(COALESCE(cheque_cashed_date, receipt_date))
                        WHERE deposit_date IS NULL
                          AND COALESCE(cheque_cashed_date, receipt_date) IS NOT NULL
                    ");
                } catch (PDOException $e) {
                    Logger::logWarning('Failed to backfill deal_payments.deposit_date', ['error' => $e->getMessage()]);
                }
                $this->ensureIndex('deal_payments', 'idx_deal_payment_deposit_date', 'deposit_date');
            }
            if ($this->connection->hasColumn('deal_payments', 'payment_status')) {
                $this->ensureIndex('deal_payments', 'idx_deal_payment_status', 'payment_status');
            }
            if ($this->connection->hasColumn('deal_payments', 'is_accepted_by_finance')) {
                $this->ensureIndex('deal_payments', 'idx_deal_payment_finance_acceptance', 'is_accepted_by_finance');
            }
        }

        // Additional columns and indexes for deal_commissions table
        if ($this->connection->hasColumn('deal_commissions', 'id')) {
            $dealCommissionColumns = [
                'transaction_id' => 'INT NULL',
                'deal_payment_id' => 'INT NULL',
                'deposit_date' => 'DATE NULL',
                'billing_month' => 'CHAR(7) NULL',
                'commission_rule_id' => 'INT NULL',
                'commission_rule_assignment_id' => 'INT NULL',
                'payout_cycle_id' => 'INT NULL',
                'manager_reviewed_by_user_id' => 'INT NULL',
                'manager_reviewed_at' => 'DATETIME NULL',
                'finance_reviewed_by_user_id' => 'INT NULL',
                'finance_reviewed_at' => 'DATETIME NULL',
                'settled_by_user_id' => 'INT NULL',
                'settled_at' => 'DATETIME NULL',
                'deleted_by_user_id' => 'INT NULL',
                'notes' => 'TEXT NULL',
                'updated_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
                'deleted_at' => 'DATETIME NULL',
            ];
            foreach ($dealCommissionColumns as $column => $type) {
                if (!$this->connection->hasColumn('deal_commissions', $column)) {
                    try {
                        $pdo->exec("ALTER TABLE deal_commissions ADD COLUMN {$column} {$type}");
                        Logger::logInfo("Added column to deal_commissions table", ['column' => $column]);
                    } catch (PDOException $e) {
                        Logger::logWarning("Failed to add column", ['table' => 'deal_commissions', 'column' => $column, 'error' => $e->getMessage()]);
                    }
                }
            }
            $this->ensureIndex('deal_commissions', 'idx_salesperson_user_id', 'salesperson_user_id');
            $this->ensureIndex('deal_commissions', 'idx_commission_status', 'status');
            $this->ensureIndex('deal_commissions', 'idx_commission_created_at', 'created_at');
            $this->ensureIndex('deal_commissions', 'idx_commission_deposit_month', 'deposit_date, billing_month');
            $this->ensureIndex('deal_commissions', 'idx_commission_deleted_at', 'deleted_at');
        }

        if ($this->connection->hasColumn('commission_payout_cycles', 'id')) {
            $payoutColumns = [
                'override_amount' => 'DECIMAL(15,2) NULL',
                'final_payable_amount' => 'DECIMAL(15,2) NOT NULL DEFAULT 0',
                'manager_id' => 'INT NULL',
                'override_reason' => 'TEXT NULL',
                'approval_deadline' => 'DATETIME NULL',
                'escalate_to_finance' => 'TINYINT(1) NOT NULL DEFAULT 0',
                'updated_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
            ];
            foreach ($payoutColumns as $column => $type) {
                if (!$this->connection->hasColumn('commission_payout_cycles', $column)) {
                    try {
                        $pdo->exec("ALTER TABLE commission_payout_cycles ADD COLUMN {$column} {$type}");
                        Logger::logInfo("Added column to commission_payout_cycles table", ['column' => $column]);
                    } catch (PDOException $e) {
                        Logger::logWarning("Failed to add column", ['table' => 'commission_payout_cycles', 'column' => $column, 'error' => $e->getMessage()]);
                    }
                }
            }
            $this->ensureIndex('commission_payout_cycles', 'idx_billing_month', 'billing_month');
            $this->ensureIndex('commission_payout_cycles', 'idx_cycle_status', 'status');
            $this->ensureIndex('commission_payout_cycles', 'idx_approval_deadline', 'approval_deadline');
        }

        // Additional columns for activities table
        $activitiesColumns = [
	            'is_scheduled' => 'TINYINT(1) DEFAULT 0',
	            'reminder_enabled' => 'TINYINT(1) DEFAULT 0',
	            'duration' => 'INT DEFAULT 0',
	            'failure_reason_code' => 'VARCHAR(50)',
	            'failure_reason_description' => 'TEXT',
	        ];

        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
        }

        // 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')) {
            $idxStmt = $pdo->prepare("
                SELECT COUNT(*) AS cnt
                FROM information_schema.STATISTICS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = 'persons'
                  AND INDEX_NAME = 'uniq_persons_customer_code'
            ");
            $idxStmt->execute();
            $hasUnique = ((int)($idxStmt->fetch(PDO::FETCH_ASSOC)['cnt'] ?? 0)) > 0;
            if (!$hasUnique) {
                try {
                    $pdo->exec("ALTER TABLE persons ADD UNIQUE KEY uniq_persons_customer_code (customer_code)");
                } catch (PDOException $e) {
                    // Only warn for real errors; duplicate-name should be ignored silently.
                    if (strpos((string)$e->getMessage(), '1061') === false) {
                        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 normalizeCommissionRuleEnums(): void
    {
        $pdo = $this->connection->getPdo();
        try {
            if ($this->connection->hasColumn('commission_rules', 'rule_type')) {
                $pdo->exec("
                    UPDATE commission_rules
                    SET rule_type = CASE
                        WHEN UPPER(COALESCE(rule_type, '')) = 'FIXED_RULE' THEN 'FIXED_RULE'
                        ELSE 'TIERED_RULE'
                    END
                ");
                $this->alterColumnType('commission_rules', 'rule_type', "ENUM('FIXED_RULE','TIERED_RULE') NOT NULL DEFAULT 'TIERED_RULE'");
            }
            if ($this->connection->hasColumn('commission_rules', 'formula_type')) {
                $pdo->exec("
                    UPDATE commission_rules
                    SET formula_type = CASE
                        WHEN UPPER(COALESCE(rule_type, '')) = 'FIXED_RULE' THEN 'FIXED'
                        ELSE 'TIERED_MARGINAL'
                    END
                ");
                $this->alterColumnType('commission_rules', 'formula_type', "ENUM('FIXED','TIERED_MARGINAL') NOT NULL DEFAULT 'TIERED_MARGINAL'");
            }
        } catch (PDOException $e) {
            Logger::logWarning('Failed to normalize commission rule enum values', [
                'error' => $e->getMessage()
            ]);
        }
    }

    private function ensureCommissionSupportTables(): void
    {
        $pdo = $this->connection->getPdo();
        $tables = [
            'commission_rule_tiers' => Schema::getCommissionRuleTiersTableSql(),
            'commission_rule_assignments' => Schema::getCommissionRuleAssignmentsTableSql(),
            'assignment_products' => Schema::getAssignmentProductsTableSql(),
        ];

        foreach ($tables as $table => $sql) {
            try {
                $pdo->exec($sql);
            } catch (PDOException $e) {
                Logger::logWarning('Failed to create commission support table', [
                    'table' => $table,
                    'error' => $e->getMessage()
                ]);
            }
        }

        if ($this->connection->hasColumn('commission_rule_tiers', 'id')) {
            $tierColumns = [
                'rule_id' => 'INT NOT NULL',
                'min_amount' => 'DECIMAL(15,2) NULL',
                'max_amount' => 'DECIMAL(15,2) NULL',
                'min_realization_percent' => 'DECIMAL(8,4) NULL',
                'max_realization_percent' => 'DECIMAL(8,4) NULL',
                'allocated_commission_percent' => 'DECIMAL(8,4) NOT NULL DEFAULT 0',
                'commission_percent' => 'DECIMAL(8,4) NULL',
                'sort_order' => 'INT NOT NULL DEFAULT 0',
                'created_by_user_id' => 'INT NULL',
                'updated_by_user_id' => 'INT NULL',
                'deleted_by_user_id' => 'INT NULL',
                'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
                'updated_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
                'deleted_at' => 'DATETIME NULL',
            ];
            foreach ($tierColumns as $column => $type) {
                $this->ensureColumn('commission_rule_tiers', $column, $type);
            }
            $this->ensureIndex('commission_rule_tiers', 'idx_tier_rule_id', 'rule_id');
            $this->ensureIndex('commission_rule_tiers', 'idx_tier_amount_band', 'min_amount, max_amount');
            $this->ensureIndex('commission_rule_tiers', 'idx_tier_realization_band', 'min_realization_percent, max_realization_percent');
            $this->ensureIndex('commission_rule_tiers', 'idx_tier_deleted_at', 'deleted_at');
        }

        if ($this->connection->hasColumn('commission_rule_assignments', 'id')) {
            $assignmentColumns = [
                'rule_id' => 'INT NOT NULL',
                'salesperson_user_id' => 'INT NULL',
                'scope_type' => "ENUM('ALL','USER','ROLE') NOT NULL DEFAULT 'USER'",
                'assigned_at' => 'DATETIME NOT NULL',
                'disabled_at' => 'DATETIME NULL',
                'assigned_by_user_id' => 'INT NULL',
                'disabled_by_user_id' => 'INT NULL',
                'disable_reason' => 'TEXT NULL',
                'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
                'updated_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
            ];
            foreach ($assignmentColumns as $column => $type) {
                $this->ensureColumn('commission_rule_assignments', $column, $type);
            }
            $this->ensureIndex('commission_rule_assignments', 'idx_assignment_rule_id', 'rule_id');
            $this->ensureIndex('commission_rule_assignments', 'idx_assignment_salesperson', 'salesperson_user_id');
            $this->ensureIndex('commission_rule_assignments', 'idx_assignment_window', 'assigned_at, disabled_at');
        }

        if ($this->connection->hasColumn('assignment_products', 'id')) {
            $this->ensureColumn('assignment_products', 'assignment_id', 'INT NOT NULL');
            $this->ensureColumn('assignment_products', 'product_id', 'INT NOT NULL');
            $this->ensureColumn('assignment_products', 'created_at', 'DATETIME DEFAULT CURRENT_TIMESTAMP');
            $this->ensureUniqueIndex('assignment_products', 'uniq_assignment_product', 'assignment_id, product_id');
            $this->ensureIndex('assignment_products', 'idx_assignment_products_assignment', 'assignment_id');
            $this->ensureIndex('assignment_products', 'idx_assignment_products_product', 'product_id');
        }
    }

    private function ensureUniqueIndex(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;
            }
            $pdo->exec("ALTER TABLE {$table} ADD UNIQUE KEY {$indexName} ({$columns})");
        } catch (PDOException $e) {
            Logger::logWarning("Failed to create unique index", [
                'table' => $table,
                'index' => $indexName,
                'error' => $e->getMessage()
            ]);
        }
    }

    private function ensureColumn(string $table, string $column, string $type): void
    {
        if (!$this->connection->hasColumn($table, $column)) {
            try {
                $this->connection->getPdo()->exec("ALTER TABLE {$table} ADD COLUMN {$column} {$type}");
                Logger::logInfo("Added column", ['table' => $table, 'column' => $column]);
            } catch (PDOException $e) {
                Logger::logWarning("Failed to add column", [
                    '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 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' => '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' => '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 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}');
        $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' => 'check', 'title' => 'چک', 'value' => 'check'],
            ['code' => 'purchase_order', 'title' => 'سفارش خرید', 'value' => 'purchase_order'],
            ['code' => 'other', 'title' => 'سایر', 'value' => 'other'],
        ];
        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]);
            }
        }

        $banksGroupId = (int)$pdo->query("SELECT id FROM lookup_groups WHERE code = 'payment_destination_banks'")->fetchColumn();
        $banks = [
            ['code' => 'parsian', 'title' => 'بانک پارسیان', 'value' => 'parsian'],
            ['code' => 'pasargad', 'title' => 'بانک پاسارگاد', 'value' => 'pasargad'],
            ['code' => 'melli', 'title' => 'بانک ملی', 'value' => 'melli'],
            ['code' => 'ayandeh', 'title' => 'بانک آینده', 'value' => 'ayandeh'],
            ['code' => 'melat', 'title' => 'بانک ملت', 'value' => 'melat'],
            ['code' => 'tejarat', 'title' => 'بانک تجارت', 'value' => 'tejarat'],
            ['code' => 'other', 'title' => 'سایر', 'value' => 'other'],
        ];
        foreach ($banks as $i => $b) {
            $stmt = $pdo->prepare("SELECT id FROM lookup_items WHERE group_id = ? AND (code = ? OR value = ?)");
            $stmt->execute([$banksGroupId, $b['code'], $b['value']]);
            if (!$stmt->fetch()) {
                $pdo->prepare("INSERT INTO lookup_items (group_id, code, title, value, sort_order, is_active) VALUES (?, ?, ?, ?, ?, 1)")
                    ->execute([$banksGroupId, $b['code'], $b['title'], $b['value'], $i+1]);
            }
        }

        // 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' => 'عنوان معامله پس از ثبت قابل ویرایش نیست'
            ],
        ];

        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, lookup_group_code, sort_order, help_text, validation_pattern, conditional_logic) 
                    VALUES (?, ?, ?, 'deal', 0, 1, ?, 1, 0, ?, ?, ?, ?, ?, ?, ?)")
                    ->execute([
                        $c['field_name'], 
                        $c['field_label'], 
                        $c['type'], 
                        $c['editable_after_create'] ?? 1,
                        json_encode($c['edit_roles'] ?? []), 
                        json_encode($c['delete_roles'] ?? []),
                        $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");
    }

    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");
        } catch (\Throwable $e) {
            return;
        }

        $roles = [
            ['role_key' => 'admin', 'title' => 'مدیر', 'is_system' => 1],
            ['role_key' => 'agent', 'title' => 'کارشناس', 'is_system' => 1],
            ['role_key' => 'crm_specialist', 'title' => 'کارشناس CRM', 'is_system' => 1],
            ['role_key' => 'accountant', '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), is_active = 1, updated_at = NOW()
        ");
        foreach ($roles as $role) {
            $roleStmt->execute([$role['role_key'], $role['title'], $role['is_system']]);
        }

        $caps = [
            // core panels / menus
            ['key' => 'menu.dashboard', 'title' => 'منو: داشبورد', 'cat' => 'menu', 'module' => 'dashboard', 'parent' => null, 'sort' => 10],
            ['key' => 'menu.performance_dashboard', 'title' => 'منو: داشبورد عملکرد', 'cat' => 'menu', 'module' => 'performance_dashboard', 'parent' => null, 'sort' => 20],
            ['key' => 'menu.leads', 'title' => 'منو: لیدها', 'cat' => 'menu', 'module' => 'leads', 'parent' => null, 'sort' => 30],
            ['key' => 'menu.blataklif_leads', 'title' => 'منو: لیدهای بلاتکلیف', 'cat' => 'menu', 'module' => 'blataklif_leads', 'parent' => null, 'sort' => 40],
            ['key' => 'menu.deals', 'title' => 'منو: معاملات', 'cat' => 'menu', 'module' => 'deals', 'parent' => null, 'sort' => 50],
            ['key' => 'menu.legacy_deals', 'title' => 'منو: معاملات گذشته دیدار', 'cat' => 'menu', 'module' => 'legacy_deals', 'parent' => null, 'sort' => 60],
            ['key' => 'menu.referrals', 'title' => 'منو: ارجاعات', 'cat' => 'menu', 'module' => 'referrals', 'parent' => null, 'sort' => 70],
            ['key' => 'menu.persons', 'title' => 'منو: اشخاص', 'cat' => 'menu', 'module' => 'persons', 'parent' => null, 'sort' => 80],
            ['key' => 'menu.reminders', 'title' => 'منو: یادآوری‌ها', 'cat' => 'menu', 'module' => 'reminders', 'parent' => null, 'sort' => 90],
            ['key' => 'menu.settings', 'title' => 'منو: تنظیمات', 'cat' => 'menu', 'module' => 'settings', 'parent' => null, 'sort' => 100],
            ['key' => 'menu.lookups', 'title' => 'منو: Lookup Manager', 'cat' => 'menu', 'module' => 'lookups', 'parent' => null, 'sort' => 110],
            ['key' => 'menu.exports', 'title' => 'منو: گزارشات/خروجی', 'cat' => 'menu', 'module' => 'exports', 'parent' => null, 'sort' => 120],
            ['key' => 'menu.audit_logs', 'title' => 'منو: لاگ فعالیت کاربران', 'cat' => 'menu', 'module' => 'audit_logs', 'parent' => null, 'sort' => 130],
            ['key' => 'menu.users', 'title' => 'منو: کاربران سیستم', 'cat' => 'menu', 'module' => 'users', 'parent' => null, 'sort' => 140],
            ['key' => 'menu.financial_dashboard', 'title' => 'منو: داشبورد مالی', 'cat' => 'menu', 'module' => 'financial_dashboard', 'parent' => null, 'sort' => 150],
            ['key' => 'menu.kpi_dashboard', 'title' => 'منو: داشبورد KPI', 'cat' => 'menu', 'module' => 'kpi_dashboard', 'parent' => null, 'sort' => 160],
            // analytics / dashboards
            ['key' => 'kpi.view', 'title' => 'مشاهده داشبورد KPI', 'cat' => 'kpi', 'module' => 'kpi', 'parent' => 'menu.kpi_dashboard', 'sort' => 170],
            ['key' => 'kpi.drilldown', 'title' => 'Drilldown داشبورد KPI', 'cat' => 'kpi', 'module' => 'kpi', 'parent' => 'kpi.view', 'sort' => 171],
            ['key' => 'kpi.targets.manage', 'title' => 'مدیریت تارگت KPI', 'cat' => 'kpi', 'module' => 'kpi', 'parent' => 'kpi.view', 'sort' => 172],
            ['key' => 'kpi.deals.details', 'title' => 'جزئیات معاملات KPI', 'cat' => 'kpi', 'module' => 'kpi', 'parent' => 'kpi.view', 'sort' => 173],
            ['key' => 'performance.view', 'title' => 'مشاهده داشبورد عملکرد', 'cat' => 'performance', 'module' => 'performance', 'parent' => 'menu.performance_dashboard', 'sort' => 180],
            ['key' => 'performance.drilldown', 'title' => 'Drilldown داشبورد عملکرد', 'cat' => 'performance', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 181],
            ['key' => 'performance.chart.daily_sales', 'title' => 'نمودار عملکرد: فروش روزانه', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 182],
            ['key' => 'performance.chart.daily_activities', 'title' => 'نمودار عملکرد: فعالیت روزانه', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 183],
            ['key' => 'performance.chart.deal_status', 'title' => 'نمودار عملکرد: ترکیب وضعیت معاملات', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 184],
            ['key' => 'performance.chart.product_share', 'title' => 'نمودار عملکرد: سهم فروش محصولات', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 185],
            ['key' => 'performance.chart.conversion_funnel', 'title' => 'نمودار عملکرد: قیف تبدیل', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 186],
            ['key' => 'performance.card.sales_total', 'title' => 'کارت عملکرد: فروش کل', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 187],
            ['key' => 'performance.card.outstanding_amount_sum', 'title' => 'کارت عملکرد: مانده حساب', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 188],
            ['key' => 'performance.card.settled_count', 'title' => 'کارت عملکرد: تسویه‌شده کامل', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 189],
            ['key' => 'performance.card.lost_count', 'title' => 'کارت عملکرد: فروش ناموفق', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 190],
            ['key' => 'performance.card.won_count', 'title' => 'کارت عملکرد: فروش موفق', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 191],
            ['key' => 'performance.card.open_pipeline_count', 'title' => 'کارت عملکرد: فروش جاری', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 192],
            ['key' => 'performance.card.without_payment_count', 'title' => 'کارت عملکرد: معاملات بدون پرداخت', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 193],
            ['key' => 'performance.table.activity_summary', 'title' => 'جدول عملکرد: خلاصه فعالیت', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 194],
            ['key' => 'performance.table.activity_type_details', 'title' => 'جدول عملکرد: جزئیات نوع فعالیت', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 195],
            ['key' => 'performance.table.deal_status', 'title' => 'جدول عملکرد: وضعیت معاملات', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 196],
            ['key' => 'performance.table.lead_status', 'title' => 'جدول عملکرد: وضعیت لیدها', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 197],
            ['key' => 'performance.table.conversion_status', 'title' => 'جدول عملکرد: نرخ تبدیل', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 198],
            ['key' => 'performance.table.payment_mix', 'title' => 'جدول عملکرد: پرداخت‌ها', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 199],
            ['key' => 'performance.table.payment_status', 'title' => 'جدول عملکرد: وضعیت پرداخت‌ها', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 200],
            ['key' => 'performance.table.product_status', 'title' => 'جدول عملکرد: وضعیت محصولات', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 201],
            ['key' => 'performance.table.product_sold_by_owner_day', 'title' => 'جدول عملکرد: محصولات فروخته‌شده', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 202],
            ['key' => 'performance.table.product_requests_by_owner_day', 'title' => 'جدول عملکرد: محصولات درخواستی', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 203],
            ['key' => 'performance.table.product_failed_by_owner_day', 'title' => 'جدول عملکرد: محصولات فروش‌نرفته', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 204],
            ['key' => 'performance.table.failure_reasons', 'title' => 'جدول عملکرد: دلایل شکست', 'cat' => 'performance_widget', 'module' => 'performance', 'parent' => 'performance.view', 'sort' => 205],
            // exports
            ['key' => 'export.catalog', 'title' => 'مشاهده کاتالوگ خروجی‌ها', 'cat' => 'export', 'module' => 'exports', 'parent' => null, 'sort' => 200],
            ['key' => 'export.dataset.leads', 'title' => 'خروجی لیدها', 'cat' => 'export', 'module' => 'exports', 'parent' => 'export.catalog', 'sort' => 210],
            ['key' => 'export.dataset.deals', 'title' => 'خروجی معاملات', 'cat' => 'export', 'module' => 'exports', 'parent' => 'export.catalog', 'sort' => 220],
            ['key' => 'export.dataset.payments', 'title' => 'خروجی پرداخت‌ها', 'cat' => 'export', 'module' => 'exports', 'parent' => 'export.catalog', 'sort' => 230],
            // actions (v1 seeds)
            ['key' => 'action.leads.create', 'title' => 'ایجاد لید', 'cat' => 'action', 'module' => 'leads', 'parent' => null, 'sort' => 300],
            ['key' => 'action.leads.edit', 'title' => 'ویرایش لید', 'cat' => 'action', 'module' => 'leads', 'parent' => null, 'sort' => 301],
            ['key' => 'action.leads.delete', 'title' => 'حذف لید', 'cat' => 'action', 'module' => 'leads', 'parent' => null, 'sort' => 302],
            ['key' => 'action.deals.create', 'title' => 'ایجاد معامله', 'cat' => 'action', 'module' => 'deals', 'parent' => null, 'sort' => 310],
            ['key' => 'action.deals.edit', 'title' => 'ویرایش معامله', 'cat' => 'action', 'module' => 'deals', 'parent' => null, 'sort' => 311],
            ['key' => 'action.deals.delete', 'title' => 'حذف معامله', 'cat' => 'action', 'module' => 'deals', 'parent' => null, 'sort' => 312],
            ['key' => 'action.payments.create', 'title' => 'ثبت پرداخت', 'cat' => 'action', 'module' => 'payments', 'parent' => null, 'sort' => 320],
            ['key' => 'action.payments.edit', 'title' => 'ویرایش پرداخت', 'cat' => 'action', 'module' => 'payments', 'parent' => null, 'sort' => 321],
            ['key' => 'action.payments.delete', 'title' => 'حذف پرداخت', 'cat' => 'action', 'module' => 'payments', 'parent' => null, 'sort' => 322],
        ];
        $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']]);
        }

        $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 = [
            'admin' => array_keys($capIds),
            'accountant' => ['menu.exports', 'export.catalog', 'export.dataset.payments'],
            'crm_specialist' => [
                'menu.dashboard','menu.leads','menu.blataklif_leads','menu.deals','menu.legacy_deals',
                'menu.referrals','menu.persons','menu.reminders','menu.settings','menu.exports',
                'export.catalog','export.dataset.payments','action.deals.edit','action.payments.create','action.payments.edit'
            ],
            'agent' => [
                'menu.dashboard','menu.performance_dashboard','menu.leads','menu.blataklif_leads',
                'menu.deals','menu.legacy_deals','menu.persons','menu.reminders','menu.settings',
                'action.leads.edit','action.deals.create','action.deals.edit','action.payments.create','action.payments.edit',
                'performance.view','performance.drilldown',
                'performance.chart.daily_sales','performance.chart.daily_activities','performance.chart.deal_status','performance.chart.product_share','performance.chart.conversion_funnel',
                'performance.card.sales_total','performance.card.outstanding_amount_sum','performance.card.settled_count','performance.card.lost_count','performance.card.won_count','performance.card.open_pipeline_count','performance.card.without_payment_count',
                'performance.table.activity_summary','performance.table.activity_type_details','performance.table.deal_status','performance.table.lead_status','performance.table.conversion_status','performance.table.payment_mix','performance.table.payment_status','performance.table.product_status','performance.table.product_sold_by_owner_day','performance.table.product_requests_by_owner_day','performance.table.product_failed_by_owner_day','performance.table.failure_reasons'
            ],
        ];

        $grantStmt = $pdo->prepare("
            INSERT INTO rbac_role_capabilities (role_id, capability_id, effect, created_at)
            VALUES (?, ?, 'allow', NOW())
            ON DUPLICATE KEY UPDATE effect = 'allow'
        ");
        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 = VALUES(scope_type), updated_at = NOW()
        ");
        $defaultScopes = [
            'admin' => 'all',
            'crm_specialist' => 'all',
            'accountant' => 'all',
            'agent' => 'self',
        ];
        foreach ($defaultScopes as $roleKey => $scopeType) {
            if (empty($roleIds[$roleKey])) continue;
            foreach (['leads', 'deals', 'payments'] 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)
        ]);
    }

    /**
     * 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];
    }
}
