<?php
declare(strict_types=1);

return function (\App\Core\Database $db): void {
    $db->raw("
        CREATE TABLE IF NOT EXISTS `regulatory_discounts` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `article_number` VARCHAR(20) NOT NULL COMMENT 'رقم المادة في اللائحة',
            `name_ar` VARCHAR(300) NOT NULL,
            `name_en` VARCHAR(300) NULL,
            `description` TEXT NULL,
            `discount_percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
            `max_discount_percentage` DECIMAL(5,2) NULL COMMENT 'الحد الأقصى للخصم (لمادة 102)',
            `eligibility_type` ENUM(
                'cross_branch_member',
                'government_employee',
                'ministry_youth_employee',
                'board_of_trustees',
                'club_employee',
                'group_membership'
            ) NOT NULL,
            `source_branch_id` BIGINT UNSIGNED NULL COMMENT 'فرع العضو الحالي (لمادة 97)',
            `target_branch_id` BIGINT UNSIGNED NULL COMMENT 'الفرع المستهدف بالخصم',
            `min_service_years` INT UNSIGNED NULL COMMENT 'الحد الأدنى لسنوات الخدمة (لمادة 102)',
            `allows_installment` TINYINT(1) NOT NULL DEFAULT 0,
            `installment_max_years` INT UNSIGNED NULL COMMENT 'أقصى مدة تقسيط بالسنوات',
            `installment_interest_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00 COMMENT 'نسبة الفائدة على التقسيط',
            `applies_to` ENUM('membership_fee','subscription','all') NOT NULL DEFAULT 'membership_fee',
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `effective_from` DATE NULL,
            `effective_to` DATE NULL,
            `board_decision_number` VARCHAR(50) NULL,
            `board_decision_date` DATE NULL,
            `notes` TEXT NULL,
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            `created_by` BIGINT UNSIGNED NULL,
            `updated_by` BIGINT UNSIGNED NULL,
            INDEX `idx_regulatory_eligibility` (`eligibility_type`, `is_active`),
            INDEX `idx_regulatory_target_branch` (`target_branch_id`, `is_active`),
            INDEX `idx_regulatory_article` (`article_number`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->raw("
        CREATE TABLE IF NOT EXISTS `regulatory_discount_group_tiers` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `regulatory_discount_id` BIGINT UNSIGNED NOT NULL,
            `min_quantity` INT UNSIGNED NOT NULL,
            `max_quantity` INT UNSIGNED NULL,
            `discount_percentage` DECIMAL(5,2) NOT NULL,
            `is_active` TINYINT(1) NOT NULL DEFAULT 1,
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            INDEX `idx_group_tier_discount` (`regulatory_discount_id`),
            CONSTRAINT `fk_group_tier_discount` FOREIGN KEY (`regulatory_discount_id`) REFERENCES `regulatory_discounts`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->raw("
        CREATE TABLE IF NOT EXISTS `regulatory_discount_applications` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            `regulatory_discount_id` BIGINT UNSIGNED NOT NULL,
            `member_id` BIGINT UNSIGNED NULL,
            `applicant_name` VARCHAR(200) NULL COMMENT 'الاسم (قبل إنشاء العضوية)',
            `discount_percentage` DECIMAL(5,2) NOT NULL,
            `original_amount` DECIMAL(15,2) NOT NULL,
            `discount_amount` DECIMAL(15,2) NOT NULL,
            `final_amount` DECIMAL(15,2) NOT NULL,
            `status` ENUM('pending','approved','rejected','applied') NOT NULL DEFAULT 'pending',
            `approved_by` BIGINT UNSIGNED NULL,
            `approved_at` TIMESTAMP NULL,
            `rejection_reason` TEXT NULL,
            `verification_data` JSON NULL COMMENT 'بيانات التحقق (HR، فرع المصدر، إلخ)',
            `installment_plan_id` BIGINT UNSIGNED NULL,
            `notes` TEXT NULL,
            `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            `created_by` BIGINT UNSIGNED NULL,
            INDEX `idx_rda_discount` (`regulatory_discount_id`),
            INDEX `idx_rda_member` (`member_id`),
            INDEX `idx_rda_status` (`status`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
};
