<?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::getNotificationsTable(),
            self::getActivityRemindersTable(),
            self::getProductsTable(),
            self::getDealProductsTable(),
            self::getTransactionsTable(),
            self::getInvoiceSequencesTable(),
            self::getCommissionRulesTable(),
            self::getDealCommissionsTable(),
            self::getCommissionPayoutCyclesTable(),
            self::getRbacRolesTable(),
            self::getRbacCapabilitiesTable(),
            self::getRbacRoleCapabilitiesTable(),
            self::getRbacRoleDataScopesTable(),
            self::getRbacRoleScopeUsersTable(),
            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 ENUM('admin', 'agent', 'crm_specialist') 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,
            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,
            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),
            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,
            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_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,
            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_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_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 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,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_product_name (name),
            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,
            quantity INT DEFAULT 1,
            price DECIMAL(15,2) DEFAULT 0,
            total DECIMAL(15,2) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_deal_id (deal_id),
            INDEX idx_product_id (product_id),
            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),
            tracking_code_last4 VARCHAR(4),
            check_owner_name VARCHAR(255),
            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),
            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 getCommissionRulesTable()
    {
        return "CREATE TABLE IF NOT EXISTS commission_rules (
            id INT PRIMARY KEY AUTO_INCREMENT,
            salesperson_user_id INT NOT NULL,
            product_id INT NOT NULL,
            base_rate_percent DECIMAL(8,4) NOT NULL,
            valid_from DATE NOT NULL,
            valid_to DATE NULL,
            is_active TINYINT(1) DEFAULT 1,
            created_by_user_id INT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_salesperson_user_id (salesperson_user_id),
            INDEX idx_product_id (product_id),
            INDEX idx_rule_validity (valid_from, valid_to),
            INDEX idx_rule_active (is_active),
            FOREIGN KEY (salesperson_user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE ON UPDATE CASCADE,
            FOREIGN KEY (created_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 getDealCommissionsTable()
    {
        return "CREATE TABLE IF NOT EXISTS deal_commissions (
            id INT PRIMARY KEY AUTO_INCREMENT,
            deal_didar_id VARCHAR(255) NOT NULL,
            salesperson_user_id INT NOT NULL,
            product_id INT NULL,
            commission_rule_id INT NULL,
            ncr_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
            applied_rate_percent DECIMAL(8,4) NOT NULL DEFAULT 0,
            kpi_multiplier DECIMAL(8,4) NOT NULL DEFAULT 1,
            calculated_commission DECIMAL(15,2) NOT NULL DEFAULT 0,
            status ENUM('PENDING_MANAGER_REVIEW','PENDING_FINANCE','READY_FOR_PAYOUT','SETTLED') 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,
            notes TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_deal_commission (deal_didar_id),
            INDEX idx_salesperson_user_id (salesperson_user_id),
            INDEX idx_commission_status (status),
            INDEX idx_commission_created_at (created_at),
            FOREIGN KEY (deal_didar_id) REFERENCES deals(didar_deal_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 (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
        ) 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','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 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('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 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";
    }
}
