<?php
namespace App\Database;

use App\Utils\Logger;

/**
 * Database Schema Definitions for MySQL
 */
class Schema
{
    /**
     * Get all table creation SQL statements
     */
    public static function getCreateTableStatements()
    {
        return [
            self::getUsersTable(),
            self::getPersonsTable(),
            self::getDealsTable(),
            self::getActivitiesTable(),
            self::getVirtualStagesTable(),
            self::getLookupGroupsTable(),
            self::getLookupItemsTable(),
            self::getFieldConfigurationsTable(),
            self::getConfigTable(),
            self::getSyncLogTable(),
            self::getPipelineCacheTable(),
            self::getReferralsTable(),
            self::getCustomerSatisfactionsTable(),
            self::getCustomerAccessRequestsTable(),
            self::getNotificationsTable(),
            self::getActivityRemindersTable(),
            self::getProductsTable(),
            self::getDealProductsTable(),
            self::getTransactionsTable(),
            self::getChequeReviewsTable(),
            self::getPaymentReviewsTable(),
            self::getPaymentRecordsTable(),
            self::getPaymentRecordLinksTable(),
            self::getPaymentEventsTable(),
            self::getPaymentRefundsTable(),
            self::getUserHierarchyAssignmentsTable(),
            self::getInvoiceSequencesTable(),
            self::getCommissionRulesTable(),
            self::getCommissionRuleTiersTable(),
            self::getCommissionRuleAssignmentsTable(),
            self::getAssignmentProductsTable(),
            self::getDealPaymentsTable(),
            self::getFinanceApprovalsTable(),
            self::getDealCommissionsTable(),
            self::getCommissionLinesTable(),
            self::getCommissionPayoutCyclesTable(),
            self::getCommissionPayoutCycleLinesTable(),
            self::getRbacRolesTable(),
            self::getRbacCapabilitiesTable(),
            self::getRbacRoleCapabilitiesTable(),
            self::getRbacRoleDataScopesTable(),
            self::getRbacRoleScopeUsersTable(),
            self::getRbacUserCapabilitiesTable(),
            self::getRbacUserDataScopesTable(),
            self::getRbacUserScopeUsersTable(),
            self::getLeadPoolsTable(),
            self::getLeadOwnerHistoryTable(),
            self::getOperationRulesTable(),
            self::getOperationRunsTable(),
            self::getOperationRunItemsTable(),
            self::getKpiTargetsTable(),
            self::getAuditLogsTable(),
            self::getFieldAuditLogsTable(),
        ];
    }

    private static function getUsersTable()
    {
        return "CREATE TABLE IF NOT EXISTS users (
            id INT PRIMARY KEY AUTO_INCREMENT,
            didar_user_id VARCHAR(255) UNIQUE NOT NULL,
            email VARCHAR(255) UNIQUE NOT NULL,
            first_name VARCHAR(255),
            last_name VARCHAR(255),
            display_name VARCHAR(255),
            username VARCHAR(255) UNIQUE,
            password VARCHAR(255),
            role VARCHAR(100) DEFAULT 'agent',
            is_active TINYINT(1) DEFAULT 1,
            api_key TEXT,
            last_sync DATETIME,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_didar_user_id (didar_user_id),
            INDEX idx_email (email),
            INDEX idx_username (username)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getPersonsTable()
    {
        return "CREATE TABLE IF NOT EXISTS persons (
            id INT PRIMARY KEY AUTO_INCREMENT,
            didar_contact_id VARCHAR(255) UNIQUE NOT NULL,
            owner_didar_id VARCHAR(255),
            previous_owner_id VARCHAR(255),
            previous_owner_history TEXT,
            code VARCHAR(255),
            customer_code VARCHAR(32),
            first_name VARCHAR(255),
            last_name VARCHAR(255),
            mobile_phone VARCHAR(50),
            secondary_mobile_phone VARCHAR(50),
            mobile_phone_3 VARCHAR(50),
            mobile_phone_4 VARCHAR(50),
            work_phone VARCHAR(50),
            email VARCHAR(255),
            contact_type VARCHAR(100),
            city VARCHAR(100),
            job_title VARCHAR(255),
            job_description TEXT,
            acquaintance_source VARCHAR(255),
            acquaintance_detail TEXT,
            acquaintance_duration VARCHAR(100),
            content_topic TEXT,
            created_by_didar_id VARCHAR(255),
            created_by_name VARCHAR(255),
            created_via VARCHAR(50),
            national_id VARCHAR(50),
            has_previous_purchase TINYINT(1) DEFAULT 0,
            sale_status TINYINT(1) DEFAULT 0,
            birth_date DATE,
            register_time_jalali VARCHAR(50),
            register_time DATETIME,
            visibility_type VARCHAR(50),
            background_info TEXT,
            extra_info TEXT,
            customer_products TEXT,
            financial_level VARCHAR(100),
            asset_estimation VARCHAR(100),
            income_estimation VARCHAR(100),
            requested_services TEXT,
            customer_level ENUM('طلایی', 'نقره‌ای', 'برنز') DEFAULT NULL,
            customer_level_notes TEXT,
            lead_pool_id INT NULL,
            pool_entered_at DATETIME NULL,
            pool_reason VARCHAR(255) NULL,
            last_sync DATETIME,
            raw_json LONGTEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_didar_contact_id (didar_contact_id),
            INDEX idx_owner_didar_id (owner_didar_id),
            INDEX idx_previous_owner_id (previous_owner_id),
            INDEX idx_mobile_phone (mobile_phone),
            UNIQUE KEY uniq_persons_customer_code (customer_code),
            INDEX idx_mobile_phone_3 (mobile_phone_3),
            INDEX idx_mobile_phone_4 (mobile_phone_4),
            INDEX idx_email (email),
            INDEX idx_sale_status (sale_status),
            INDEX idx_register_time (register_time),
            INDEX idx_lead_pool_id (lead_pool_id),
            FOREIGN KEY (owner_didar_id) REFERENCES users(didar_user_id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

	    private static function getDealsTable()
	    {
	        return "CREATE TABLE IF NOT EXISTS deals (
	            id INT PRIMARY KEY AUTO_INCREMENT,
	            didar_deal_id VARCHAR(255) UNIQUE NOT NULL,
	            owner_didar_id VARCHAR(255),
	            contact_didar_id VARCHAR(255),
	            person_id INT(11) NULL,
	            title VARCHAR(500),
	            code VARCHAR(255),
	            status ENUM('Pending', 'Won', 'Lost'),
	            pipeline_stage VARCHAR(100),
	            failure_reason_code VARCHAR(50),
	            failure_reason_text VARCHAR(255),
	            failure_reason_description TEXT,
	            pipeline_stage_id VARCHAR(255),
	            pipeline_stage_title VARCHAR(255),
	            estimated_price DECIMAL(15,2),
	            final_price DECIMAL(15,2),
	            probability INT,
            is_paid TINYINT(1) DEFAULT 0,
            payment_short_link TEXT,
            service_name VARCHAR(255),
            service_cost DECIMAL(15,2),
            price_list_code VARCHAR(255),
	            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,
            invoice_code VARCHAR(32),
            payment_amount DECIMAL(15,2) DEFAULT 0,
            payment_method VARCHAR(100),
            payment_description TEXT,
            payments TEXT,
            acquaintance_source VARCHAR(255),
            acquaintance_detail TEXT,
            content_topic TEXT,
            is_legacy_didar TINYINT(1) DEFAULT 0,
            register_time DATETIME,
            last_update_time DATETIME,
            won_time DATETIME,
            lost_time DATETIME,
            visibility_type VARCHAR(50),
            last_sync DATETIME,
            raw_json LONGTEXT,
            created_by_didar_id VARCHAR(255),
            created_by_name VARCHAR(255),
            created_via VARCHAR(50),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_didar_deal_id (didar_deal_id),
            INDEX idx_owner_didar_id (owner_didar_id),
            INDEX idx_contact_didar_id (contact_didar_id),
            INDEX idx_status (status),
            INDEX idx_pipeline_stage (pipeline_stage),
            INDEX idx_register_time (register_time),
            UNIQUE KEY uniq_deals_invoice_code (invoice_code),
            FOREIGN KEY (owner_didar_id) REFERENCES users(didar_user_id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (contact_didar_id) REFERENCES persons(didar_contact_id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

	    private static function getActivitiesTable()
	    {
	        return "CREATE TABLE IF NOT EXISTS activities (
	            id INT PRIMARY KEY AUTO_INCREMENT,
	            didar_activity_id VARCHAR(255) UNIQUE NOT NULL,
	            activity_type_id VARCHAR(255) NOT NULL,
	            activity_type_title VARCHAR(255),
	            owner_didar_id VARCHAR(255),
	            deal_didar_id VARCHAR(255),
	            contact_didar_id VARCHAR(255),
	            title VARCHAR(500),
	            note TEXT,
	            result_note TEXT,
	            failure_reason_code VARCHAR(50),
	            failure_reason_description TEXT,
	            is_done TINYINT(1) DEFAULT 0,
	            due_date DATETIME,
	            done_date DATETIME,
	            duration INT,
            call_type VARCHAR(20),
            activity_category VARCHAR(100),
            direction ENUM('incoming', 'outgoing'),
            stage VARCHAR(100),
            is_scheduled TINYINT(1) DEFAULT 0,
            reminder_enabled TINYINT(1) DEFAULT 0,
            register_date DATETIME,
            last_update_time DATETIME,
            last_sync DATETIME,
            raw_json LONGTEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_didar_activity_id (didar_activity_id),
            INDEX idx_owner_didar_id (owner_didar_id),
            INDEX idx_deal_didar_id (deal_didar_id),
            INDEX idx_contact_didar_id (contact_didar_id),
            INDEX idx_register_date (register_date),
            INDEX idx_due_date (due_date),
            INDEX idx_is_scheduled (is_scheduled),
            FOREIGN KEY (owner_didar_id) REFERENCES users(didar_user_id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (deal_didar_id) REFERENCES deals(didar_deal_id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (contact_didar_id) REFERENCES persons(didar_contact_id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getVirtualStagesTable()
    {
        return "CREATE TABLE IF NOT EXISTS virtual_stages (
            id INT PRIMARY KEY AUTO_INCREMENT,
            contact_didar_id VARCHAR(255) NOT NULL,
            stage_name ENUM(
                'with_purchase', 'without_purchase',
                'new', 'contact_no_answer', 'contact_followup', 'deal_registered', 
                'payment', 'payment_followup', 'course_delivered', 'support', 'refer_crm'
            ),
            entered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_activity_id VARCHAR(255),
            notes TEXT,
            UNIQUE KEY unique_contact_stage (contact_didar_id, stage_name),
            INDEX idx_contact_didar_id (contact_didar_id),
            INDEX idx_stage_name (stage_name),
            INDEX idx_entered_at (entered_at),
            FOREIGN KEY (contact_didar_id) REFERENCES persons(didar_contact_id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getLookupGroupsTable()
    {
        return "CREATE TABLE IF NOT EXISTS lookup_groups (
            id INT PRIMARY KEY AUTO_INCREMENT,
            code VARCHAR(100) UNIQUE NOT NULL,
            title VARCHAR(255) NOT NULL,
            description TEXT,
            sort_order INT DEFAULT 0,
            is_active TINYINT(1) DEFAULT 1,
            is_system TINYINT(1) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_code (code),
            INDEX idx_active (is_active),
            INDEX idx_sort (sort_order)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getLookupItemsTable()
    {
        return "CREATE TABLE IF NOT EXISTS lookup_items (
            id INT PRIMARY KEY AUTO_INCREMENT,
            group_id INT NOT NULL,
            parent_id INT NULL,
            code VARCHAR(100) NULL,
            title VARCHAR(255) NOT NULL,
            value VARCHAR(255) NULL,
            meta_json TEXT NULL,
            sort_order INT DEFAULT 0,
            is_active TINYINT(1) DEFAULT 1,
            is_visible TINYINT(1) DEFAULT 1,
            is_system TINYINT(1) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_group (group_id),
            INDEX idx_parent (parent_id),
            INDEX idx_active (is_active),
            INDEX idx_visible (is_visible),
            INDEX idx_sort (sort_order),
            FOREIGN KEY (group_id) REFERENCES lookup_groups(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (parent_id) REFERENCES lookup_items(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getFieldConfigurationsTable()
    {
        return "CREATE TABLE IF NOT EXISTS field_configurations (
            id INT PRIMARY KEY AUTO_INCREMENT,
            field_name VARCHAR(100) UNIQUE NOT NULL COMMENT 'Payment method, card_last4, bank, etc',
            field_label VARCHAR(255) NOT NULL COMMENT 'Display label in Persian',
            field_type ENUM('text', 'number', 'select', 'textarea', 'date', 'email', 'checkbox') DEFAULT 'text',
            entity_type VARCHAR(50) NOT NULL COMMENT 'deal, person, activity, payment, etc',
            module_name VARCHAR(100) NULL COMMENT 'Module/Modal name: deal_modal, person_modal, etc',
            is_required TINYINT(1) DEFAULT 0 COMMENT 'Is field mandatory?',
            is_editable TINYINT(1) DEFAULT 1 COMMENT 'Can be edited at all?',
            is_editable_after_create TINYINT(1) DEFAULT 1 COMMENT 'Can be edited after first save?',
            is_active TINYINT(1) DEFAULT 1 COMMENT 'Is field enabled?',
            is_system TINYINT(1) DEFAULT 0 COMMENT 'System field - cannot delete',
            edit_allow_roles VARCHAR(500) NULL COMMENT 'JSON array of allowed roles for editing',
            delete_allow_roles VARCHAR(500) NULL COMMENT 'JSON array of allowed roles for deleting',
            view_allow_roles VARCHAR(500) NULL COMMENT 'JSON array of allowed roles for viewing',
            lookup_group_code VARCHAR(100) NULL COMMENT 'Reference to lookup group if select type',
            sort_order INT DEFAULT 0 COMMENT 'Display order',
            help_text TEXT NULL COMMENT 'Help text/tooltip for the field',
            default_value VARCHAR(255) NULL COMMENT 'Default value for new records',
            validation_pattern VARCHAR(255) NULL COMMENT 'Regex pattern for validation',
            validation_message VARCHAR(255) NULL COMMENT 'Custom validation error message',
            conditional_logic TEXT NULL COMMENT 'JSON logic for dynamic visibility',
            group_name VARCHAR(100) NULL COMMENT 'Tab/Section group name',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_field (entity_type, field_name),
            INDEX idx_entity_type (entity_type),
            INDEX idx_module_name (module_name),
            INDEX idx_active (is_active),
            INDEX idx_field_name (field_name),
            INDEX idx_group_name (group_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getFieldAuditLogsTable()
    {
        return "CREATE TABLE IF NOT EXISTS field_audit_logs (
            id INT PRIMARY KEY AUTO_INCREMENT,
            entity_type VARCHAR(50) NOT NULL,
            entity_id VARCHAR(255) NOT NULL,
            field_name VARCHAR(100) NOT NULL,
            old_value TEXT,
            new_value TEXT,
            actor_didar_id VARCHAR(255),
            actor_name VARCHAR(255),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_entity (entity_type, entity_id),
            INDEX idx_field (field_name),
            INDEX idx_created (created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getConfigTable()
    {
        return "CREATE TABLE IF NOT EXISTS config (
            `key` VARCHAR(255) PRIMARY KEY,
            value TEXT,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_key (`key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getSyncLogTable()
    {
        return "CREATE TABLE IF NOT EXISTS sync_log (
            id INT PRIMARY KEY AUTO_INCREMENT,
            entity_type ENUM('user', 'person', 'deal', 'activity'),
            entity_id VARCHAR(255),
            action ENUM('fetch', 'create', 'update'),
            status ENUM('success', 'failed'),
            error_message TEXT,
            http_code INT,
            response_data LONGTEXT,
            synced_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_entity_type (entity_type),
            INDEX idx_entity_id (entity_id),
            INDEX idx_status (status),
            INDEX idx_synced_at (synced_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getPipelineCacheTable()
    {
        return "CREATE TABLE IF NOT EXISTS pipeline_cache (
            id INT PRIMARY KEY AUTO_INCREMENT,
            pipeline_id VARCHAR(255) UNIQUE NOT NULL,
            pipeline_title VARCHAR(255),
            stages_json LONGTEXT,
            cached_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_pipeline_id (pipeline_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getReferralsTable()
    {
        return "CREATE TABLE IF NOT EXISTS referrals (
            id INT PRIMARY KEY AUTO_INCREMENT,
            contact_didar_id VARCHAR(255) NOT NULL,
            referrer_didar_id VARCHAR(255),
            note TEXT,
            phones TEXT,
            status VARCHAR(50) DEFAULT 'pending',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_contact_didar_id (contact_didar_id),
            INDEX idx_referrer_didar_id (referrer_didar_id),
            FOREIGN KEY (contact_didar_id) REFERENCES persons(didar_contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (referrer_didar_id) REFERENCES users(didar_user_id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getCustomerSatisfactionsTable()
    {
        return "CREATE TABLE IF NOT EXISTS customer_satisfactions (
            id INT PRIMARY KEY AUTO_INCREMENT,
            contact_didar_id VARCHAR(255) NOT NULL,
            satisfaction_score TINYINT CHECK(satisfaction_score BETWEEN 1 AND 5),
            satisfaction_note TEXT,
            registered_by_didar_id VARCHAR(255),
            register_date DATETIME DEFAULT CURRENT_TIMESTAMP,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_contact_didar_id (contact_didar_id),
            INDEX idx_registered_by_didar_id (registered_by_didar_id),
            FOREIGN KEY (contact_didar_id) REFERENCES persons(didar_contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (registered_by_didar_id) REFERENCES users(didar_user_id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getCustomerAccessRequestsTable()
    {
        // Lightweight ticketing: sales expert (agent) requests customer access from
        // the support team. Snapshots deal/owner/product so the request stays stable
        // even if the underlying deal changes later.
        return "CREATE TABLE IF NOT EXISTS customer_access_requests (
            id INT PRIMARY KEY AUTO_INCREMENT,
            contact_didar_id VARCHAR(255) NOT NULL COMMENT 'مشتری/لید',
            deal_id INT NULL COMMENT 'شناسه محلی معامله انتخابی',
            deal_didar_id VARCHAR(255) NULL,
            deal_title VARCHAR(500) NULL COMMENT 'snapshot عنوان معامله',
            product_type VARCHAR(500) NULL COMMENT 'snapshot نوع محصول/خدمت معامله',
            selected_phone VARCHAR(50) NULL COMMENT 'تلفن انتخاب‌شده مشتری',
            deal_owner_didar_id VARCHAR(255) NULL COMMENT 'مسئول معامله',
            deal_owner_name VARCHAR(255) NULL COMMENT 'snapshot نام مسئول معامله',
            description TEXT COMMENT 'توضیحات درخواست',
            status ENUM('pending','done','not_done') DEFAULT 'pending' COMMENT 'در انتظار/انجام‌شده/انجام‌نشده',
            requested_by_didar_id VARCHAR(255) NULL COMMENT 'کارشناس فروش درخواست‌دهنده',
            requested_by_name VARCHAR(255) NULL,
            resolved_by_didar_id VARCHAR(255) NULL COMMENT 'کارشناس پشتیبانی رسیدگی‌کننده',
            resolved_at DATETIME NULL,
            resolution_note TEXT NULL COMMENT 'یادداشت پشتیبانی',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'تاریخ درخواست',
            updated_at DATETIME NULL,
            INDEX idx_car_contact (contact_didar_id),
            INDEX idx_car_status (status),
            INDEX idx_car_requested_by (requested_by_didar_id),
            INDEX idx_car_created_at (created_at),
            FOREIGN KEY (contact_didar_id) REFERENCES persons(didar_contact_id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (requested_by_didar_id) REFERENCES users(didar_user_id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getNotificationsTable()
    {
        return "CREATE TABLE IF NOT EXISTS notifications (
            id INT PRIMARY KEY AUTO_INCREMENT,
            user_didar_id VARCHAR(255) NOT NULL,
            type VARCHAR(100) NOT NULL,
            title VARCHAR(500) NOT NULL,
            message TEXT,
            entity_type VARCHAR(50),
            entity_id VARCHAR(255),
            is_read TINYINT(1) DEFAULT 0,
            read_at DATETIME NULL,
            show_popup TINYINT(1) DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_didar_id (user_didar_id),
            INDEX idx_is_read (is_read),
            INDEX idx_created_at (created_at),
            INDEX idx_type (type),
            FOREIGN KEY (user_didar_id) REFERENCES users(didar_user_id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getActivityRemindersTable()
    {
        return "CREATE TABLE IF NOT EXISTS activity_reminders (
            id INT PRIMARY KEY AUTO_INCREMENT,
            activity_didar_id VARCHAR(255) NOT NULL,
            reminder_time DATETIME NOT NULL,
            reminder_sent TINYINT(1) DEFAULT 0,
            reminder_offset_minutes INT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_reminder_time (reminder_time),
            INDEX idx_reminder_sent (reminder_sent),
            INDEX idx_activity_didar_id (activity_didar_id),
            FOREIGN KEY (activity_didar_id) REFERENCES activities(didar_activity_id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getProductsTable()
    {
        return "CREATE TABLE IF NOT EXISTS products (
            id INT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL,
            service_lookup_item_id INT NULL,
            is_deleted TINYINT(1) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_product_name (name),
            INDEX idx_products_deleted (is_deleted),
            INDEX idx_service_lookup_item_id (service_lookup_item_id),
            FOREIGN KEY (service_lookup_item_id) REFERENCES lookup_items(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getDealProductsTable()
    {
        return "CREATE TABLE IF NOT EXISTS deal_products (
            id INT PRIMARY KEY AUTO_INCREMENT,
            deal_id INT NOT NULL,
            product_id INT NOT NULL,
            product_name_snapshot VARCHAR(255) NULL,
            service_lookup_item_id_snapshot INT NULL,
            service_title_snapshot VARCHAR(255) NULL,
            quantity INT DEFAULT 1,
            price DECIMAL(15,2) DEFAULT 0,
            total DECIMAL(15,2) DEFAULT 0,
            unit_price_snapshot DECIMAL(15,2) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_deal_id (deal_id),
            INDEX idx_product_id (product_id),
            INDEX idx_service_lookup_item_id_snapshot (service_lookup_item_id_snapshot),
            FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getTransactionsTable()
    {
        return "CREATE TABLE IF NOT EXISTS transactions (
            id INT PRIMARY KEY AUTO_INCREMENT,
            deal_id INT NOT NULL,
            amount DECIMAL(15,2) NOT NULL,
            payment_date DATE NOT NULL,
            deposit_date DATE NULL,
            payment_time DATETIME DEFAULT CURRENT_TIMESTAMP,
            method VARCHAR(100) COMMENT 'card_to_card, payment_gateway, check, etc',
            card_last4 VARCHAR(4),
            destination_bank VARCHAR(100),
            destination_card_number VARCHAR(20) COMMENT 'شماره کارت/بانک مقصد هنگام انتخاب «سایر»',
            account_owner_name VARCHAR(255) COMMENT 'نام صاحب حساب مقصد هنگام انتخاب «سایر»',
            depositor_name VARCHAR(255) COMMENT 'نام و نام خانوادگی واریز‌کننده (روش‌های کارتی)',
            tracking_code_last4 VARCHAR(4),
            check_owner_name VARCHAR(255),
            check_bank_name VARCHAR(255) COMMENT 'نام بانک مبدا چک',
            check_number VARCHAR(50) COMMENT 'شماره چک',
            check_date DATE NULL COMMENT 'تاریخ پرداخت/سررسید روی چک',
            check_due_date DATE NULL COMMENT 'تاریخ سررسید چک',
            sayadi_date VARCHAR(20) COMMENT 'تاریخ ثبت صیادی چک (جلالی)',
            check_delivery_date DATE NULL COMMENT 'تاریخ تحویل چک (اختیاری، پس از ثبت قفل)',
            check_recipient_name VARCHAR(255) COMMENT 'نام تحویل‌گیرنده چک (اختیاری، پس از ثبت قفل)',
            receipt_image_path VARCHAR(500) COMMENT 'مسیر تصویر فیش واریزی',
            check_image_path VARCHAR(500) COMMENT 'مسیر تصویر چک',
            payment_sequence INT NULL COMMENT 'شماره پایدار ردیف پرداخت در JSON معامله',
            payment_source_hash CHAR(64) NULL COMMENT 'هش پایدار منبع پرداخت برای upsert بدون بازسازی مخرب',
            payment_record_id BIGINT NULL COMMENT 'شناسه payment_records در ledger جدید پرداخت',
            payment_uuid CHAR(36) NULL COMMENT 'شناسه پایدار پرداخت در ledger جدید',
            payment_owner_user_id INT NULL COMMENT 'شناسه داخلی مسئول پرداخت snapshot شده',
            payment_owner_didar_id VARCHAR(255) NULL COMMENT 'شناسه دیدار مسئول پرداخت snapshot شده',
            payment_owner_name_snapshot VARCHAR(255) NULL COMMENT 'نام مسئول پرداخت هنگام ثبت/بک‌فیل',
            payment_owner_locked_at DATETIME NULL COMMENT 'زمان قفل شدن مسئول پرداخت',
            commission_status VARCHAR(50) DEFAULT 'PENDING' COMMENT 'وضعیت compatibility پورسانت برای projection قدیمی',
            commission_effective_date DATE NULL COMMENT 'تاریخ موثر پورسانت در projection قدیمی',
            registered_by_didar_id VARCHAR(255) NULL COMMENT 'مسئول پرداخت snapshot شده هنگام ثبت',
            registered_by_name VARCHAR(255) NULL COMMENT 'نام مسئول پرداخت snapshot شده هنگام ثبت',
            refund_amount DECIMAL(15,2) DEFAULT 0 COMMENT 'مبلغ عودت ثبت‌شده روی همین پرداخت',
            refund_count INT DEFAULT 0 COMMENT 'تعداد عودت‌های ثبت‌شده روی همین پرداخت',
            refund_description TEXT NULL COMMENT 'توضیحات عودت همین پرداخت',
            check_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending' COMMENT 'وضعیت چک: تایید/رد توسط مالی',
            check_status_note TEXT COMMENT 'توضیحات تایید/رد چک',
            check_status_at DATETIME NULL COMMENT 'زمان ثبت وضعیت چک',
            check_status_by VARCHAR(255) COMMENT 'کاربر ثبت‌کننده وضعیت چک (didar_user_id)',
            collection_status ENUM('pending', 'collected', 'returned', 'blataklif') DEFAULT 'pending' COMMENT 'وضعیت وصول: وصول/برگشت/بلاتکلیف',
            collection_date DATE NULL COMMENT 'تاریخ وضعیت وصول',
            description TEXT,
            invoice_code VARCHAR(32) NULL,
            is_first_payment TINYINT(1) DEFAULT 0 COMMENT 'آیا اولین پرداخت این معامله است؟',
            is_accepted_by_finance TINYINT(1) NOT NULL DEFAULT 1,
            status ENUM('pending', 'confirmed', 'cancelled', 'refunded') DEFAULT 'confirmed',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_deal_id (deal_id),
            INDEX idx_payment_date (payment_date),
            INDEX idx_transaction_deposit_date (deposit_date),
            INDEX idx_transaction_payment_source_hash (payment_source_hash),
            INDEX idx_transaction_payment_record_id (payment_record_id),
            INDEX idx_transaction_payment_uuid (payment_uuid),
            INDEX idx_transaction_payment_owner_user_id (payment_owner_user_id),
            INDEX idx_transaction_commission_status (commission_status),
            INDEX idx_transaction_registered_by_didar_id (registered_by_didar_id),
            INDEX idx_transaction_finance_acceptance (is_accepted_by_finance),
            INDEX idx_status (status),
            INDEX idx_method (method),
            INDEX idx_is_first_payment (is_first_payment),
            INDEX idx_invoice_code (invoice_code),
            INDEX idx_check_status (check_status),
            INDEX idx_collection_status (collection_status),
            FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getChequeReviewsTable()
    {
        // C9: durable financial cheque workflow, keyed by transaction_id.
        // Decoupled from `transactions` row *contents* (which are rebuilt on every
        // deal save) but addressed by the stable transactions.id so financial
        // decisions survive sales-side re-saves AND remain addressable even when
        // check_number is empty (the common case for synced cheques).
        return "CREATE TABLE IF NOT EXISTS cheque_reviews (
            id INT PRIMARY KEY AUTO_INCREMENT,
            transaction_id BIGINT NULL,
            deal_id INT NOT NULL,
            check_number VARCHAR(50) NULL,
            amount DECIMAL(15,2) DEFAULT 0,
            check_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending' COMMENT 'وضعیت چک: تایید/رد',
            check_status_note TEXT COMMENT 'توضیحات تایید/رد',
            reviewed_by VARCHAR(255) COMMENT 'کاربر تعیین وضعیت چک (didar_user_id)',
            reviewed_at DATETIME NULL,
            collection_status ENUM('pending', 'collected', 'returned', 'blataklif') DEFAULT 'pending' COMMENT 'وضعیت وصول',
            collection_date DATE NULL COMMENT 'تاریخ وضعیت وصول',
            collection_by VARCHAR(255) COMMENT 'کاربر تعیین وضعیت وصول',
            collection_at DATETIME NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_cheque_review_txn (transaction_id),
            INDEX idx_cr_deal (deal_id),
            INDEX idx_cr_check_status (check_status),
            INDEX idx_cr_collection_status (collection_status),
            FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getPaymentReviewsTable()
    {
        return "CREATE TABLE IF NOT EXISTS payment_reviews (
            id INT PRIMARY KEY AUTO_INCREMENT,
            transaction_id BIGINT NULL,
            deal_id INT NOT NULL,
            amount DECIMAL(15,2) DEFAULT 0,
            sepidar_registered_at DATETIME NULL COMMENT 'زمان ثبت پرداخت در سیستم سپیدار',
            registered_by VARCHAR(255) COMMENT 'کاربر مالی ثبت‌کننده در سپیدار',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_payment_review_txn (transaction_id),
            INDEX idx_pr_deal (deal_id),
            INDEX idx_pr_sepidar (sepidar_registered_at),
            FOREIGN KEY (deal_id) REFERENCES deals(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getPaymentRecordsTable()
    {
        return "CREATE TABLE IF NOT EXISTS payment_records (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            source_type ENUM('DEAL_JSON','TRANSACTION','DEAL_PAYMENT','MANUAL') NOT NULL,
            source_id VARCHAR(100) NULL,
            source_hash CHAR(64) NOT NULL,
            deal_id INT NULL,
            deal_didar_id VARCHAR(255) NULL,
            contact_didar_id VARCHAR(255) NULL,
            payment_uuid CHAR(36) NOT NULL,
            payment_sequence INT NULL,
            amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            currency VARCHAR(10) DEFAULT 'IRR',
            method VARCHAR(100) NULL,
            payment_date DATE NULL,
            deposit_date DATE NULL,
            effective_date DATE NULL,
            registered_at DATETIME NULL,
            is_deleted_placeholder TINYINT(1) DEFAULT 0,
            is_refunded TINYINT(1) DEFAULT 0,
            refunded_amount DECIMAL(15,2) DEFAULT 0,
            refund_status ENUM('NONE','PARTIAL','FULL') DEFAULT 'NONE',
            payment_owner_user_id INT NULL,
            payment_owner_didar_id VARCHAR(255) NULL,
            payment_owner_name_snapshot VARCHAR(255) NULL,
            payment_owner_locked_at DATETIME NULL,
            payment_owner_source ENUM('SESSION','DEAL_OWNER_BACKFILL','ADMIN_OVERRIDE','UNKNOWN') DEFAULT 'UNKNOWN',
            supervisor_user_id_snapshot INT NULL,
            manager_user_id_snapshot INT NULL,
            hierarchy_snapshot_at DATE NULL,
            cheque_status ENUM('not_cheque','pending','approved','rejected') DEFAULT 'not_cheque',
            cheque_collection_status ENUM('not_cheque','pending','collected','returned','blataklif') DEFAULT 'not_cheque',
            cheque_due_date DATE NULL,
            cheque_collection_date DATE NULL,
            finance_status ENUM('NONE','SEPIDAR_PENDING','SEPIDAR_REGISTERED') DEFAULT 'NONE',
            sepidar_registered_at DATETIME NULL,
            commission_status ENUM('NOT_ELIGIBLE','PENDING','CALCULATED','APPROVED','READY_FOR_PAYOUT','PAID','REFUNDED','CLAWBACK_PENDING','CLAWED_BACK','VOID') DEFAULT 'PENDING',
            raw_snapshot_json LONGTEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_payment_uuid (payment_uuid),
            UNIQUE KEY uniq_source_hash (source_hash),
            INDEX idx_payment_record_deal (deal_id),
            INDEX idx_payment_record_deal_didar (deal_didar_id),
            INDEX idx_payment_record_owner (payment_owner_user_id),
            INDEX idx_payment_record_supervisor (supervisor_user_id_snapshot),
            INDEX idx_payment_record_manager (manager_user_id_snapshot),
            INDEX idx_payment_record_effective_date (effective_date),
            INDEX idx_payment_record_commission_status (commission_status),
            INDEX idx_payment_record_cheque_status (cheque_status, cheque_collection_status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getUserHierarchyAssignmentsTable()
    {
        return "CREATE TABLE IF NOT EXISTS user_hierarchy_assignments (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            user_id INT NOT NULL,
            supervisor_user_id INT NULL,
            manager_user_id INT NULL,
            supervisor_commission_rate_percent DECIMAL(7,4) NULL,
            manager_commission_rate_percent DECIMAL(7,4) NULL,
            effective_from DATE NOT NULL,
            effective_to DATE NULL,
            created_by_user_id INT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_effective (user_id, effective_from, effective_to),
            INDEX idx_supervisor (supervisor_user_id),
            INDEX idx_manager (manager_user_id),
            INDEX idx_effective_window (effective_from, effective_to)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getPaymentRecordLinksTable()
    {
        return "CREATE TABLE IF NOT EXISTS payment_record_links (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            payment_record_id BIGINT NOT NULL,
            linked_table VARCHAR(100) NOT NULL,
            linked_id VARCHAR(100) NOT NULL,
            link_type ENUM('SOURCE','PROJECTION','REVIEW','COMMISSION','LEGACY') NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_payment_record_link (linked_table, linked_id, link_type),
            INDEX idx_payment_record_links_record (payment_record_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getPaymentEventsTable()
    {
        return "CREATE TABLE IF NOT EXISTS payment_events (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            payment_record_id BIGINT NOT NULL,
            event_type VARCHAR(100) NOT NULL,
            event_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            actor_user_id INT NULL,
            actor_didar_id VARCHAR(255) NULL,
            old_snapshot_json LONGTEXT NULL,
            new_snapshot_json LONGTEXT NULL,
            metadata_json LONGTEXT NULL,
            INDEX idx_payment_events_record (payment_record_id, event_at),
            INDEX idx_payment_events_type (event_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getPaymentRefundsTable()
    {
        return "CREATE TABLE IF NOT EXISTS payment_refunds (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            payment_record_id BIGINT NOT NULL,
            transaction_id INT NULL,
            amount DECIMAL(15,2) NOT NULL,
            refund_date DATE NOT NULL,
            reason TEXT NULL,
            status ENUM('PENDING','APPROVED','APPLIED','REJECTED','VOID') DEFAULT 'PENDING',
            affects_commission TINYINT(1) DEFAULT 1,
            clawback_status ENUM('NONE','PENDING','APPLIED','WAIVED') DEFAULT 'PENDING',
            created_by_user_id INT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_payment_refunds_record (payment_record_id),
            INDEX idx_payment_refunds_date (refund_date),
            INDEX idx_payment_refunds_clawback (clawback_status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getInvoiceSequencesTable()
    {
        return "CREATE TABLE IF NOT EXISTS invoice_sequences (
            sequence_key VARCHAR(20) PRIMARY KEY,
            last_serial INT NOT NULL DEFAULT 0,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getRbacRolesTable()
    {
        return "CREATE TABLE IF NOT EXISTS rbac_roles (
            id INT PRIMARY KEY AUTO_INCREMENT,
            role_key VARCHAR(100) UNIQUE NOT NULL,
            title VARCHAR(255) NOT NULL,
            is_system TINYINT(1) DEFAULT 0,
            is_active TINYINT(1) DEFAULT 1,
            template_from_role_key VARCHAR(100) NULL,
            meta_json TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_role_key (role_key),
            INDEX idx_is_system (is_system),
            INDEX idx_is_active (is_active)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getCommissionRulesTable()
    {
        return "CREATE TABLE IF NOT EXISTS commission_rules (
            id INT PRIMARY KEY AUTO_INCREMENT,
            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,
            INDEX idx_salesperson_user_id (salesperson_user_id),
            INDEX idx_product_id (product_id),
            INDEX idx_rule_type (rule_type),
            INDEX idx_rule_formula_type (formula_type),
            INDEX idx_rule_status (status),
            INDEX idx_rule_validity (valid_from, valid_to),
            INDEX idx_rule_active (is_active),
            INDEX idx_rule_deleted_at (deleted_at),
            FOREIGN KEY (salesperson_user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (updated_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (deleted_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    public static function getCommissionRuleTiersTableSql()
    {
        return self::getCommissionRuleTiersTable();
    }

    public static function getCommissionRuleAssignmentsTableSql()
    {
        return self::getCommissionRuleAssignmentsTable();
    }

    public static function getAssignmentProductsTableSql()
    {
        return self::getAssignmentProductsTable();
    }

    private static function getCommissionRuleTiersTable()
    {
        return "CREATE TABLE IF NOT EXISTS commission_rule_tiers (
            id INT PRIMARY KEY AUTO_INCREMENT,
            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,
            INDEX idx_tier_rule_id (rule_id),
            INDEX idx_tier_amount_band (min_amount, max_amount),
            INDEX idx_tier_realization_band (min_realization_percent, max_realization_percent),
            INDEX idx_tier_deleted_at (deleted_at),
            FOREIGN KEY (rule_id) REFERENCES commission_rules(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (updated_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (deleted_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getCommissionRuleAssignmentsTable()
    {
        return "CREATE TABLE IF NOT EXISTS commission_rule_assignments (
            id INT PRIMARY KEY AUTO_INCREMENT,
            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,
            valid_from DATE NULL,
            valid_to DATE 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,
            INDEX idx_assignment_rule_id (rule_id),
            INDEX idx_assignment_salesperson (salesperson_user_id),
            INDEX idx_assignment_window (assigned_at, disabled_at),
            INDEX idx_assignment_validity (valid_from, valid_to),
            FOREIGN KEY (rule_id) REFERENCES commission_rules(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (salesperson_user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (assigned_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (disabled_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getAssignmentProductsTable()
    {
        return "CREATE TABLE IF NOT EXISTS assignment_products (
            id INT PRIMARY KEY AUTO_INCREMENT,
            assignment_id INT NOT NULL,
            product_id INT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_assignment_product (assignment_id, product_id),
            INDEX idx_assignment_products_assignment (assignment_id),
            INDEX idx_assignment_products_product (product_id),
            FOREIGN KEY (assignment_id) REFERENCES commission_rule_assignments(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getDealPaymentsTable()
    {
        return "CREATE TABLE IF NOT EXISTS deal_payments (
            id INT PRIMARY KEY AUTO_INCREMENT,
            deal_didar_id VARCHAR(255) NOT NULL,
            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,
            reference_no VARCHAR(100) NULL,
            notes TEXT NULL,
            created_by_user_id INT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_deal_payment_deal (deal_didar_id),
            INDEX idx_deal_payment_type (payment_type),
            INDEX idx_deal_payment_status (payment_status),
            INDEX idx_deal_payment_receipt_date (receipt_date),
            INDEX idx_deal_payment_deposit_date (deposit_date),
            INDEX idx_deal_payment_finance_acceptance (is_accepted_by_finance),
            FOREIGN KEY (deal_didar_id) REFERENCES deals(didar_deal_id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (accepted_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (rejected_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getFinanceApprovalsTable()
    {
        return "CREATE TABLE IF NOT EXISTS finance_approvals (
            id INT PRIMARY KEY AUTO_INCREMENT,
            deal_payment_id INT NOT NULL,
            deal_didar_id VARCHAR(255) NOT NULL,
            status ENUM('PENDING_FINANCE_APPROVAL','APPROVED','REJECTED','CASHED') NOT NULL DEFAULT 'PENDING_FINANCE_APPROVAL',
            approved_amount DECIMAL(15,2) NULL,
            approver_user_id INT NULL,
            approved_at DATETIME NULL,
            rejection_reason TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_finance_approval_status (status),
            INDEX idx_finance_approval_deal (deal_didar_id),
            INDEX idx_finance_approval_payment (deal_payment_id),
            FOREIGN KEY (deal_payment_id) REFERENCES deal_payments(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (deal_didar_id) REFERENCES deals(didar_deal_id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getDealCommissionsTable()
    {
        return "CREATE TABLE IF NOT EXISTS deal_commissions (
            id INT PRIMARY KEY AUTO_INCREMENT,
            deal_didar_id VARCHAR(255) NOT NULL,
            transaction_id INT NULL,
            deal_payment_id INT NULL,
            payment_record_id BIGINT NULL,
            deposit_date DATE NULL,
            billing_month CHAR(7) NULL,
            salesperson_user_id INT NOT NULL,
            product_id INT NULL,
            commission_rule_id INT NULL,
            commission_rule_assignment_id INT NULL,
            ncr_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            applied_rate_percent DECIMAL(8,4) NOT NULL DEFAULT 0,
            calculated_commission DECIMAL(15,2) NOT NULL DEFAULT 0,
            payout_cycle_id INT NULL,
            status ENUM('PENDING_MANAGER_REVIEW','PENDING_FINANCE','PENDING_FINANCE_APPROVAL','READY_FOR_PAYOUT','APPROVED','SETTLED','REJECTED') NOT NULL DEFAULT 'PENDING_MANAGER_REVIEW',
            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,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            deleted_at DATETIME NULL,
            INDEX idx_deal_commission_deal (deal_didar_id),
            UNIQUE KEY uniq_deal_commission_transaction (transaction_id),
            UNIQUE KEY uniq_deal_commission_payment (deal_payment_id),
            UNIQUE KEY uniq_deal_commission_payment_record (payment_record_id),
            INDEX idx_salesperson_user_id (salesperson_user_id),
            INDEX idx_commission_status (status),
            INDEX idx_commission_created_at (created_at),
            INDEX idx_commission_deposit_month (deposit_date, billing_month),
            INDEX idx_commission_deleted_at (deleted_at),
            FOREIGN KEY (deal_didar_id) REFERENCES deals(didar_deal_id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (transaction_id) REFERENCES transactions(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (deal_payment_id) REFERENCES deal_payments(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (salesperson_user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (commission_rule_id) REFERENCES commission_rules(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (commission_rule_assignment_id) REFERENCES commission_rule_assignments(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (manager_reviewed_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (finance_reviewed_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (settled_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (deleted_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getCommissionLinesTable()
    {
        return "CREATE TABLE IF NOT EXISTS commission_lines (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            payment_record_id BIGINT NOT NULL,
            deal_didar_id VARCHAR(255) NOT NULL,
            beneficiary_user_id INT NOT NULL,
            beneficiary_type ENUM('DIRECT_PAYMENT_OWNER','SUPERVISOR_OF_PAYMENT_OWNER','MANAGER_OF_PAYMENT_OWNER','MANAGER_OF_SUPERVISOR_COMMISSION','CLAWBACK') NOT NULL DEFAULT 'DIRECT_PAYMENT_OWNER',
            payment_owner_user_id INT NOT NULL,
            product_id INT NULL,
            commission_rule_id INT NULL,
            commission_rule_assignment_id INT NULL,
            payment_amount_snapshot DECIMAL(15,2) NOT NULL DEFAULT 0,
            commission_base_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            applied_rate_percent DECIMAL(8,4) NOT NULL DEFAULT 0,
            calculated_commission DECIMAL(15,2) NOT NULL DEFAULT 0,
            effective_date DATE NOT NULL,
            billing_month CHAR(7) NOT NULL,
            rule_snapshot_json LONGTEXT NULL,
            tier_snapshot_json LONGTEXT NULL,
            payment_snapshot_json LONGTEXT NULL,
            product_snapshot_json LONGTEXT NULL,
            hierarchy_snapshot_json LONGTEXT NULL,
            status ENUM('CALCULATED','MANAGER_APPROVED','FINANCE_APPROVED','READY_FOR_PAYOUT','SETTLED','PAID','REVERSED','VOID') NOT NULL DEFAULT 'CALCULATED',
            is_reversal TINYINT(1) NOT NULL DEFAULT 0,
            reversal_of_line_id BIGINT NULL,
            refund_id BIGINT NULL,
            calculation_run_id VARCHAR(64) NULL,
            source_hash CHAR(64) NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_commission_line_source_hash (source_hash),
            INDEX idx_commission_line_payment_record (payment_record_id),
            INDEX idx_commission_line_beneficiary (beneficiary_user_id, billing_month),
            INDEX idx_commission_line_owner (payment_owner_user_id),
            INDEX idx_commission_line_status (status),
            INDEX idx_commission_line_effective (effective_date, billing_month),
            INDEX idx_commission_line_reversal (reversal_of_line_id),
            FOREIGN KEY (payment_record_id) REFERENCES payment_records(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (beneficiary_user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (payment_owner_user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (commission_rule_id) REFERENCES commission_rules(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (commission_rule_assignment_id) REFERENCES commission_rule_assignments(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (reversal_of_line_id) REFERENCES commission_lines(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (refund_id) REFERENCES payment_refunds(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getCommissionPayoutCyclesTable()
    {
        return "CREATE TABLE IF NOT EXISTS commission_payout_cycles (
            id INT PRIMARY KEY AUTO_INCREMENT,
            user_id INT NOT NULL,
            billing_month CHAR(7) NOT NULL COMMENT 'YYYY-MM',
            calculated_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            override_amount DECIMAL(15,2) NULL,
            final_payable_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            manager_id INT NULL,
            status ENUM('PENDING_APPROVAL','APPROVED','OVERRIDDEN','REJECTED','FINANCE_APPROVED','READY_FOR_PAYOUT','PAID','SETTLED','TRANSFERRED_TO_PAYROLL') NOT NULL DEFAULT 'PENDING_APPROVAL',
            override_reason TEXT NULL,
            approval_deadline DATETIME NULL,
            escalate_to_finance TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_user_billing_month (user_id, billing_month),
            INDEX idx_billing_month (billing_month),
            INDEX idx_cycle_status (status),
            INDEX idx_approval_deadline (approval_deadline),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getCommissionPayoutCycleLinesTable()
    {
        return "CREATE TABLE IF NOT EXISTS commission_payout_cycle_lines (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            payout_cycle_id INT NOT NULL,
            commission_line_id BIGINT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_cycle_line (payout_cycle_id, commission_line_id),
            UNIQUE KEY uniq_commission_line_payout (commission_line_id),
            INDEX idx_payout_cycle_lines_cycle (payout_cycle_id),
            INDEX idx_payout_cycle_lines_line (commission_line_id),
            FOREIGN KEY (payout_cycle_id) REFERENCES commission_payout_cycles(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (commission_line_id) REFERENCES commission_lines(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getRbacCapabilitiesTable()
    {
        return "CREATE TABLE IF NOT EXISTS rbac_capabilities (
            id INT PRIMARY KEY AUTO_INCREMENT,
            capability_key VARCHAR(150) UNIQUE NOT NULL,
            title VARCHAR(255) NOT NULL,
            category VARCHAR(50) NOT NULL,
            module VARCHAR(100) NULL,
            parent_key VARCHAR(150) NULL,
            meta_json TEXT NULL,
            sort_order INT DEFAULT 0,
            is_system TINYINT(1) DEFAULT 1,
            is_active TINYINT(1) DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_capability_key (capability_key),
            INDEX idx_category (category),
            INDEX idx_module (module),
            INDEX idx_parent_key (parent_key),
            INDEX idx_active (is_active)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getRbacRoleCapabilitiesTable()
    {
        return "CREATE TABLE IF NOT EXISTS rbac_role_capabilities (
            id INT PRIMARY KEY AUTO_INCREMENT,
            role_id INT NOT NULL,
            capability_id INT NOT NULL,
            effect ENUM('allow','deny') DEFAULT 'allow',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_role_cap (role_id, capability_id),
            INDEX idx_role_id (role_id),
            INDEX idx_capability_id (capability_id),
            INDEX idx_effect (effect),
            FOREIGN KEY (role_id) REFERENCES rbac_roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (capability_id) REFERENCES rbac_capabilities(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getRbacRoleDataScopesTable()
    {
        return "CREATE TABLE IF NOT EXISTS rbac_role_data_scopes (
            id INT PRIMARY KEY AUTO_INCREMENT,
            role_id INT NOT NULL,
            resource_key VARCHAR(100) NOT NULL,
            scope_type ENUM('none','self','all','selected_users') NOT NULL DEFAULT 'self',
            meta_json TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_role_resource (role_id, resource_key),
            INDEX idx_role_id (role_id),
            INDEX idx_resource_key (resource_key),
            INDEX idx_scope_type (scope_type),
            FOREIGN KEY (role_id) REFERENCES rbac_roles(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getRbacRoleScopeUsersTable()
    {
        return "CREATE TABLE IF NOT EXISTS rbac_role_scope_users (
            id INT PRIMARY KEY AUTO_INCREMENT,
            role_data_scope_id INT NOT NULL,
            user_id INT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_scope_user (role_data_scope_id, user_id),
            INDEX idx_role_data_scope_id (role_data_scope_id),
            INDEX idx_user_id (user_id),
            FOREIGN KEY (role_data_scope_id) REFERENCES rbac_role_data_scopes(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getRbacUserCapabilitiesTable()
    {
        return "CREATE TABLE IF NOT EXISTS rbac_user_capabilities (
            id INT PRIMARY KEY AUTO_INCREMENT,
            user_id INT NOT NULL,
            capability_id INT NOT NULL,
            effect ENUM('allow','deny') DEFAULT 'allow',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_user_cap (user_id, capability_id),
            INDEX idx_user_id (user_id),
            INDEX idx_capability_id (capability_id),
            INDEX idx_effect (effect),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (capability_id) REFERENCES rbac_capabilities(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getRbacUserDataScopesTable()
    {
        return "CREATE TABLE IF NOT EXISTS rbac_user_data_scopes (
            id INT PRIMARY KEY AUTO_INCREMENT,
            user_id INT NOT NULL,
            resource_key VARCHAR(100) NOT NULL,
            scope_type ENUM('none','self','all','selected_users') NOT NULL DEFAULT 'self',
            meta_json TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_user_resource (user_id, resource_key),
            INDEX idx_user_id (user_id),
            INDEX idx_resource_key (resource_key),
            INDEX idx_scope_type (scope_type),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getRbacUserScopeUsersTable()
    {
        return "CREATE TABLE IF NOT EXISTS rbac_user_scope_users (
            id INT PRIMARY KEY AUTO_INCREMENT,
            user_data_scope_id INT NOT NULL,
            selected_user_id INT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_user_scope_user (user_data_scope_id, selected_user_id),
            INDEX idx_user_data_scope_id (user_data_scope_id),
            INDEX idx_selected_user_id (selected_user_id),
            FOREIGN KEY (user_data_scope_id) REFERENCES rbac_user_data_scopes(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (selected_user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getLeadPoolsTable()
    {
        return "CREATE TABLE IF NOT EXISTS lead_pools (
            id INT PRIMARY KEY AUTO_INCREMENT,
            pool_key VARCHAR(100) UNIQUE NOT NULL,
            title VARCHAR(255) NOT NULL,
            description TEXT NULL,
            is_system TINYINT(1) DEFAULT 0,
            is_active TINYINT(1) DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_pool_key (pool_key),
            INDEX idx_active (is_active)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getLeadOwnerHistoryTable()
    {
        return "CREATE TABLE IF NOT EXISTS lead_owner_history (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            person_id INT NULL,
            contact_didar_id VARCHAR(255) NOT NULL,
            from_owner_didar_id VARCHAR(255) NULL,
            from_owner_name VARCHAR(255) NULL,
            to_owner_didar_id VARCHAR(255) NULL,
            to_owner_name VARCHAR(255) NULL,
            changed_by_user_id INT NULL,
            changed_by_didar_id VARCHAR(255) NULL,
            changed_by_name VARCHAR(255) NULL,
            change_source VARCHAR(50) NOT NULL DEFAULT 'manual',
            operation_run_id BIGINT NULL,
            rule_id INT NULL,
            reason VARCHAR(255) NULL,
            metadata_json LONGTEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_contact_created (contact_didar_id, created_at),
            INDEX idx_from_owner (from_owner_didar_id),
            INDEX idx_to_owner (to_owner_didar_id),
            INDEX idx_operation_run (operation_run_id),
            INDEX idx_rule_id (rule_id),
            FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getOperationRulesTable()
    {
        return "CREATE TABLE IF NOT EXISTS operation_rules (
            id INT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL,
            description TEXT NULL,
            is_active TINYINT(1) DEFAULT 1,
            entity_scope VARCHAR(50) NOT NULL DEFAULT 'lead',
            filter_json LONGTEXT NULL,
            condition_json LONGTEXT NULL,
            operation_json LONGTEXT NOT NULL,
            schedule_type VARCHAR(50) NOT NULL DEFAULT 'manual',
            schedule_config_json LONGTEXT NULL,
            dry_run_default TINYINT(1) DEFAULT 1,
            last_run_at DATETIME NULL,
            next_run_at DATETIME NULL,
            created_by_user_id INT NULL,
            updated_by_user_id INT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_active_next_run (is_active, next_run_at),
            INDEX idx_entity_scope (entity_scope),
            INDEX idx_schedule_type (schedule_type),
            FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (updated_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getOperationRunsTable()
    {
        return "CREATE TABLE IF NOT EXISTS operation_runs (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            rule_id INT NULL,
            run_type VARCHAR(50) NOT NULL DEFAULT 'manual',
            status VARCHAR(50) NOT NULL DEFAULT 'pending',
            requested_by_user_id INT NULL,
            started_at DATETIME NULL,
            finished_at DATETIME NULL,
            matched_count INT DEFAULT 0,
            selected_count INT DEFAULT 0,
            affected_count INT DEFAULT 0,
            failed_count INT DEFAULT 0,
            filter_snapshot_json LONGTEXT NULL,
            operation_snapshot_json LONGTEXT NULL,
            error_summary TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_rule_id (rule_id),
            INDEX idx_status (status),
            INDEX idx_created_at (created_at),
            FOREIGN KEY (rule_id) REFERENCES operation_rules(id) ON DELETE SET NULL ON UPDATE CASCADE,
            FOREIGN KEY (requested_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getOperationRunItemsTable()
    {
        return "CREATE TABLE IF NOT EXISTS operation_run_items (
            id BIGINT PRIMARY KEY AUTO_INCREMENT,
            operation_run_id BIGINT NOT NULL,
            entity_type VARCHAR(50) NOT NULL,
            entity_id VARCHAR(255) NULL,
            contact_didar_id VARCHAR(255) NULL,
            deal_didar_id VARCHAR(255) NULL,
            before_json LONGTEXT NULL,
            after_json LONGTEXT NULL,
            status VARCHAR(50) NOT NULL DEFAULT 'pending',
            error_message TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_operation_run (operation_run_id),
            INDEX idx_entity (entity_type, entity_id),
            INDEX idx_contact (contact_didar_id),
            INDEX idx_deal (deal_didar_id),
            FOREIGN KEY (operation_run_id) REFERENCES operation_runs(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getKpiTargetsTable()
    {
        return "CREATE TABLE IF NOT EXISTS kpi_targets (
            id INT PRIMARY KEY AUTO_INCREMENT,
            user_id INT NOT NULL COMMENT 'users table foreign key',
            year INT NOT NULL COMMENT 'سال میلادی (2025, 2026, ...)',
            month INT NOT NULL COMMENT 'ماه (1-12)',
            target_amount DECIMAL(15,2) NOT NULL COMMENT 'مبلغ هدف فروش',
            target_deal_count INT DEFAULT 0 COMMENT 'تعداد معامله هدف',
            is_active TINYINT(1) DEFAULT 1,
            notes TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_user_target (user_id, year, month),
            INDEX idx_user_id (user_id),
            INDEX idx_year_month (year, month),
            INDEX idx_is_active (is_active),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }

    private static function getAuditLogsTable()
    {
        return "CREATE TABLE IF NOT EXISTS audit_logs (
            id INT PRIMARY KEY AUTO_INCREMENT,
            actor_user_id INT NULL,
            actor_didar_id VARCHAR(255) NULL,
            actor_name VARCHAR(255) NULL,
            actor_ip VARCHAR(100) NULL,
            action VARCHAR(50) NOT NULL,
            entity_type VARCHAR(50) NOT NULL,
            entity_id VARCHAR(255) NULL,
            related_person_didar_id VARCHAR(255) NULL,
            snapshot_json LONGTEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_actor_user_id (actor_user_id),
            INDEX idx_actor_didar_id (actor_didar_id),
            INDEX idx_actor_name (actor_name),
            INDEX idx_actor_ip (actor_ip),
            INDEX idx_entity (entity_type, entity_id),
            INDEX idx_related_person (related_person_didar_id),
            INDEX idx_created_at (created_at),
            FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    }
}
