<?php
declare(strict_types=1);

use App\Core\Database;

return function (Database $db): void {
    // ────────────────────────────────────────────────────────────
    // 2025 Labour Law — Leave Type Updates
    // ────────────────────────────────────────────────────────────

    // 1A. Annual leave: set default_days_per_year to NULL (dynamic by tenure)
    //     and update rules_json with tier logic
    $db->query(
        "UPDATE hr_leave_types SET
            default_days_per_year = NULL,
            rules_json = ?,
            legal_reference = 'Art. 47-50 (2025 Law)'
         WHERE code = 'annual' AND is_archived = 0",
        [json_encode([
            'tiers' => [
                ['min_years' => 0, 'max_years' => 1, 'days' => 15, 'label' => 'السنة الأولى'],
                ['min_years' => 1, 'max_years' => 10, 'days' => 21, 'label' => 'بعد سنة'],
                ['min_years' => 10, 'max_years' => null, 'days' => 30, 'label' => 'بعد 10 سنوات'],
            ],
            'age_50_plus_days' => 30,
            'disability_days' => 45,
            'entitled_from_day_1' => true,
            'usable_after_months' => 6,
        ], JSON_UNESCAPED_UNICODE)]
    );

    // 1B. Casual leave: max 2 consecutive days
    $db->query(
        "UPDATE hr_leave_types SET max_consecutive_days = 2 WHERE code = 'casual' AND is_archived = 0",
        []
    );

    // 1C. Maternity: 120 days (4 months)
    $db->query(
        "UPDATE hr_leave_types SET
            default_days_per_year = 120.0,
            max_days_per_year = 120.0,
            legal_reference = 'Art. 50 (2025 Law)',
            rules_json = ?
         WHERE code = 'maternity' AND is_archived = 0",
        [json_encode([
            'pre_delivery_days' => 45,
            'pay_basis' => 'comprehensive_salary',
            'termination_protection' => true,
        ], JSON_UNESCAPED_UNICODE)]
    );

    // 1D. Hajj: now PAID, requires 5 years service
    $db->query(
        "UPDATE hr_leave_types SET
            is_paid = 1,
            pay_percentage = 100.00,
            min_service_months = 60,
            deduct_from_salary = 0,
            legal_reference = 'Art. 53 (2025 Law)',
            rules_json = ?
         WHERE code = 'hajj' AND is_archived = 0",
        [json_encode([
            'not_deducted_from_annual' => true,
        ], JSON_UNESCAPED_UNICODE)]
    );

    // 1E. Childcare leave (NEW)
    $exists = $db->selectOne(
        "SELECT 1 FROM hr_leave_types WHERE code = 'childcare' LIMIT 1",
        []
    );
    if (!$exists) {
        $db->insert('hr_leave_types', [
            'code'                  => 'childcare',
            'name_ar'               => 'إجازة رعاية الطفل',
            'name_en'               => 'Childcare Leave',
            'category'              => 'unpaid',
            'default_days_per_year' => null,
            'max_days_per_year'     => 730.0,
            'is_paid'               => 0,
            'pay_percentage'        => 0.00,
            'requires_approval'     => 1,
            'requires_attachment'   => 0,
            'min_days_per_request'  => null,
            'max_days_per_request'  => null,
            'max_consecutive_days'  => null,
            'max_per_occurrence'    => null,
            'max_times_in_career'   => null,
            'min_service_months'    => 12,
            'advance_notice_days'   => 30,
            'gender_restriction'    => 'female',
            'carry_over_allowed'    => 0,
            'is_accumulative'       => 0,
            'career_max_times'      => 3,
            'career_max_days'       => null,
            'deduct_from_salary'    => 0,
            'legal_reference'       => 'Art. 53 (2025 Law)',
            'rules_json'            => json_encode([
                'min_establishment_workers' => 50,
                'min_gap_months_between_uses' => 24,
            ], JSON_UNESCAPED_UNICODE),
            'is_active'             => 1,
            'sort_order'            => 11,
            'created_at'            => date('Y-m-d H:i:s'),
            'updated_at'            => date('Y-m-d H:i:s'),
        ]);
    }

    // 1F. Paternity leave (NEW)
    $exists = $db->selectOne(
        "SELECT 1 FROM hr_leave_types WHERE code = 'paternity' LIMIT 1",
        []
    );
    if (!$exists) {
        $db->insert('hr_leave_types', [
            'code'                  => 'paternity',
            'name_ar'               => 'إجازة أبوة',
            'name_en'               => 'Paternity Leave',
            'category'              => 'special',
            'default_days_per_year' => 1.0,
            'max_days_per_year'     => 1.0,
            'is_paid'               => 1,
            'pay_percentage'        => 100.00,
            'requires_approval'     => 1,
            'requires_attachment'   => 1,
            'min_days_per_request'  => null,
            'max_days_per_request'  => null,
            'max_consecutive_days'  => 1,
            'max_per_occurrence'    => 1,
            'max_times_in_career'   => null,
            'min_service_months'    => 0,
            'advance_notice_days'   => 0,
            'gender_restriction'    => 'male',
            'carry_over_allowed'    => 0,
            'is_accumulative'       => 0,
            'career_max_times'      => 3,
            'career_max_days'       => 3.0,
            'deduct_from_salary'    => 0,
            'legal_reference'       => 'Art. 52 (2025 Law)',
            'rules_json'            => json_encode([
                'on_child_birthday' => true,
            ], JSON_UNESCAPED_UNICODE),
            'is_active'             => 1,
            'sort_order'            => 12,
            'created_at'            => date('Y-m-d H:i:s'),
            'updated_at'            => date('Y-m-d H:i:s'),
        ]);
    }

    // 1G. Exam leave (NEW)
    $exists = $db->selectOne(
        "SELECT 1 FROM hr_leave_types WHERE code = 'exam' LIMIT 1",
        []
    );
    if (!$exists) {
        $db->insert('hr_leave_types', [
            'code'                  => 'exam',
            'name_ar'               => 'إجازة امتحانات',
            'name_en'               => 'Exam Leave',
            'category'              => 'special',
            'default_days_per_year' => null,
            'max_days_per_year'     => null,
            'is_paid'               => 1,
            'pay_percentage'        => 100.00,
            'requires_approval'     => 1,
            'requires_attachment'   => 1,
            'min_days_per_request'  => null,
            'max_days_per_request'  => null,
            'max_consecutive_days'  => null,
            'max_per_occurrence'    => null,
            'max_times_in_career'   => null,
            'min_service_months'    => 0,
            'advance_notice_days'   => 10,
            'gender_restriction'    => null,
            'carry_over_allowed'    => 0,
            'is_accumulative'       => 0,
            'career_max_times'      => null,
            'career_max_days'       => null,
            'deduct_from_salary'    => 0,
            'legal_reference'       => '2025 Law',
            'rules_json'            => json_encode([
                'not_deducted_from_annual' => true,
                'requires_exam_schedule' => true,
                'actual_exam_days_only' => true,
            ], JSON_UNESCAPED_UNICODE),
            'is_active'             => 1,
            'sort_order'            => 13,
            'created_at'            => date('Y-m-d H:i:s'),
            'updated_at'            => date('Y-m-d H:i:s'),
        ]);
    }

    // ────────────────────────────────────────────────────────────
    // System Config Updates for 2025 Law + Payroll Alignment
    // ────────────────────────────────────────────────────────────

    // Update maternity days
    $db->query(
        "UPDATE system_config SET config_value = '120' WHERE config_key = 'hr.leave.maternity_days'",
        []
    );

    // Add first-year annual days config
    $exists = $db->selectOne(
        "SELECT 1 FROM system_config WHERE config_key = 'hr.leave.annual_first_year' LIMIT 1",
        []
    );
    if (!$exists) {
        $db->insert('system_config', [
            'config_key'     => 'hr.leave.annual_first_year',
            'config_value'   => '15',
            'config_type'    => 'integer',
            'group_name'     => 'hr',
            'description_ar' => 'أيام الإجازة السنوية للسنة الأولى',
            'description_en' => 'Annual leave days for first year',
            'is_editable'    => 1,
            'created_at'     => date('Y-m-d H:i:s'),
            'updated_at'     => date('Y-m-d H:i:s'),
        ]);
    }

    // Add disability annual days config
    $exists = $db->selectOne(
        "SELECT 1 FROM system_config WHERE config_key = 'hr.leave.annual_disability' LIMIT 1",
        []
    );
    if (!$exists) {
        $db->insert('system_config', [
            'config_key'     => 'hr.leave.annual_disability',
            'config_value'   => '45',
            'config_type'    => 'integer',
            'group_name'     => 'hr',
            'description_ar' => 'أيام الإجازة السنوية لذوي الإعاقة',
            'description_en' => 'Annual leave days for employees with disability',
            'is_editable'    => 1,
            'created_at'     => date('Y-m-d H:i:s'),
            'updated_at'     => date('Y-m-d H:i:s'),
        ]);
    }

    // Payroll: stamp duty (3% of net)
    $db->query(
        "UPDATE system_config SET config_value = '0.03' WHERE config_key = 'hr.deduction.regular_stamp_duty'",
        []
    );

    // Solidarity fund rate (2.5‰ = 0.0025)
    $exists = $db->selectOne(
        "SELECT 1 FROM system_config WHERE config_key = 'hr.deduction.solidarity_fund_rate' LIMIT 1",
        []
    );
    if (!$exists) {
        $db->insert('system_config', [
            'config_key'     => 'hr.deduction.solidarity_fund_rate',
            'config_value'   => '0.0025',
            'config_type'    => 'float',
            'group_name'     => 'hr',
            'description_ar' => 'نسبة مساهمة صندوق التكافل الاجتماعي',
            'description_en' => 'Solidarity fund contribution rate',
            'is_editable'    => 1,
            'created_at'     => date('Y-m-d H:i:s'),
            'updated_at'     => date('Y-m-d H:i:s'),
        ]);
    }

    // Emergency fund rate (1% of total basic salaries)
    $exists = $db->selectOne(
        "SELECT 1 FROM system_config WHERE config_key = 'hr.deduction.emergency_fund_rate' LIMIT 1",
        []
    );
    if (!$exists) {
        $db->insert('system_config', [
            'config_key'     => 'hr.deduction.emergency_fund_rate',
            'config_value'   => '0.01',
            'config_type'    => 'float',
            'group_name'     => 'hr',
            'description_ar' => 'نسبة صندوق الطوارئ',
            'description_en' => 'Emergency fund rate',
            'is_editable'    => 1,
            'created_at'     => date('Y-m-d H:i:s'),
            'updated_at'     => date('Y-m-d H:i:s'),
        ]);
    }

    // VAT rate on payroll invoice
    $exists = $db->selectOne(
        "SELECT 1 FROM system_config WHERE config_key = 'hr.payroll.vat_rate' LIMIT 1",
        []
    );
    if (!$exists) {
        $db->insert('system_config', [
            'config_key'     => 'hr.payroll.vat_rate',
            'config_value'   => '0.14',
            'config_type'    => 'float',
            'group_name'     => 'hr',
            'description_ar' => 'نسبة ضريبة القيمة المضافة على فاتورة الرواتب',
            'description_en' => 'VAT rate on payroll invoice',
            'is_editable'    => 1,
            'created_at'     => date('Y-m-d H:i:s'),
            'updated_at'     => date('Y-m-d H:i:s'),
        ]);
    }

    // ────────────────────────────────────────────────────────────
    // Employee Profile: add variable_salary and total_allowances
    // ────────────────────────────────────────────────────────────

    $colExists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns
         WHERE table_schema = DATABASE() AND table_name = 'hr_employee_profiles' AND column_name = 'variable_salary'",
        []
    );
    if (!$colExists) {
        $db->raw("ALTER TABLE hr_employee_profiles ADD COLUMN variable_salary DECIMAL(15,2) NOT NULL DEFAULT 0.00 AFTER basic_salary");
    }

    $colExists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns
         WHERE table_schema = DATABASE() AND table_name = 'hr_employee_profiles' AND column_name = 'total_allowances'",
        []
    );
    if (!$colExists) {
        $db->raw("ALTER TABLE hr_employee_profiles ADD COLUMN total_allowances DECIMAL(15,2) NOT NULL DEFAULT 0.00 AFTER variable_salary");
    }
};
