<?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::getInvoiceSequencesTable(),
            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,
            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),
            deposit_date DATE NULL COMMENT 'تاریخ واریز',
            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 'مسیر تصویر چک',
            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 'آیا اولین پرداخت این معامله است؟',
            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_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 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 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";
    }
}
