<?php
declare(strict_types=1);

return function (\App\Core\Database $db): void {
    $now = date('Y-m-d H:i:s');

    // =====================================================================
    // 1A. Employee Profile — New Columns
    // =====================================================================
    $cols = [
        'workforce_type'           => "ALTER TABLE hr_employee_profiles ADD COLUMN workforce_type VARCHAR(30) NULL DEFAULT 'insured' AFTER employment_type",
        'fingerprint_code'         => "ALTER TABLE hr_employee_profiles ADD COLUMN fingerprint_code VARCHAR(50) NULL AFTER employee_number",
        'education_qualification'  => "ALTER TABLE hr_employee_profiles ADD COLUMN education_qualification VARCHAR(255) NULL AFTER nationality",
        'education_specialization' => "ALTER TABLE hr_employee_profiles ADD COLUMN education_specialization VARCHAR(255) NULL AFTER education_qualification",
        'education_graduation_year'=> "ALTER TABLE hr_employee_profiles ADD COLUMN education_graduation_year YEAR NULL AFTER education_specialization",
        'education_grade'          => "ALTER TABLE hr_employee_profiles ADD COLUMN education_grade VARCHAR(100) NULL AFTER education_graduation_year",
        'education_university'     => "ALTER TABLE hr_employee_profiles ADD COLUMN education_university VARCHAR(255) NULL AFTER education_grade",
    ];

    foreach ($cols as $col => $sql) {
        $exists = $db->selectOne(
            "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hr_employee_profiles' AND column_name = ?",
            [$col]
        );
        if (!$exists) {
            $db->raw($sql);
        }
    }

    // =====================================================================
    // 1B. Department Staffing Capacity
    // =====================================================================
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hr_departments' AND column_name = 'staffing_capacity'"
    );
    if (!$exists) {
        $db->raw("ALTER TABLE hr_departments ADD COLUMN staffing_capacity INT UNSIGNED NULL AFTER sort_order");
    }

    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'hr_department_positions'"
    );
    if (!$exists) {
        $db->raw("
            CREATE TABLE hr_department_positions (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                department_id BIGINT UNSIGNED NOT NULL,
                job_title_id BIGINT UNSIGNED NOT NULL,
                authorized_count INT UNSIGNED NOT NULL DEFAULT 1,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY uq_dept_job (department_id, job_title_id),
                KEY idx_department (department_id),
                KEY idx_job_title (job_title_id),
                CONSTRAINT fk_dept_pos_dept FOREIGN KEY (department_id) REFERENCES hr_departments(id) ON DELETE CASCADE,
                CONSTRAINT fk_dept_pos_job FOREIGN KEY (job_title_id) REFERENCES hr_job_titles(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    }

    // =====================================================================
    // 1C. Job Title — Description Fields
    // =====================================================================
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hr_job_titles' AND column_name = 'job_description_ar'"
    );
    if (!$exists) {
        $db->raw("ALTER TABLE hr_job_titles ADD COLUMN job_description_ar TEXT NULL");
        $db->raw("ALTER TABLE hr_job_titles ADD COLUMN job_description_en TEXT NULL");
    }

    // =====================================================================
    // 1D. Loans — External Entity Columns
    // =====================================================================
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hr_employee_loans' AND column_name = 'external_entity_name'"
    );
    if (!$exists) {
        $db->raw("ALTER TABLE hr_employee_loans ADD COLUMN external_entity_name VARCHAR(255) NULL AFTER loan_type");
        $db->raw("ALTER TABLE hr_employee_loans ADD COLUMN external_reference VARCHAR(100) NULL AFTER external_entity_name");
        $db->raw("ALTER TABLE hr_employee_loans ADD COLUMN monthly_salary_impact DECIMAL(12,2) NULL AFTER external_reference");
    }

    // Drop the old CHECK constraint and add new one with expanded types
    try {
        $db->raw("ALTER TABLE hr_employee_loans DROP CONSTRAINT chk_hr_loans_type");
    } catch (\Exception $e) {
        // Constraint may not exist or already dropped
    }
    try {
        $db->raw("ALTER TABLE hr_employee_loans ADD CONSTRAINT chk_hr_loans_type CHECK (loan_type IN ('salary_advance', 'personal_loan', 'emergency_loan', 'financing_company', 'bank_loan'))");
    } catch (\Exception $e) {
        // May already exist with new values
    }

    // =====================================================================
    // 1E. Documents — Validity Status
    // =====================================================================
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hr_employee_documents' AND column_name = 'validity_status'"
    );
    if (!$exists) {
        $db->raw("ALTER TABLE hr_employee_documents ADD COLUMN validity_status VARCHAR(20) NOT NULL DEFAULT 'valid' AFTER is_verified");
    }

    // =====================================================================
    // 1F. Disciplinary — Investigation Result
    // =====================================================================
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hr_disciplinary_actions' AND column_name = 'investigation_result'"
    );
    if (!$exists) {
        $db->raw("ALTER TABLE hr_disciplinary_actions ADD COLUMN investigation_result TEXT NULL AFTER investigation_notes");
    }

    // =====================================================================
    // 1G. Overtime — Add employee_profile_id Column
    // =====================================================================
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hr_overtime_requests' AND column_name = 'employee_profile_id'"
    );
    if (!$exists) {
        $db->raw("ALTER TABLE hr_overtime_requests ADD COLUMN employee_profile_id BIGINT UNSIGNED NULL AFTER employee_id");
        $db->raw("CREATE INDEX idx_ot_profile ON hr_overtime_requests (employee_profile_id)");
        // Backfill existing data
        $db->raw("
            UPDATE hr_overtime_requests r
            JOIN hr_employee_profiles p ON p.employee_id = r.employee_id
            SET r.employee_profile_id = p.id
            WHERE r.employee_profile_id IS NULL
        ");
    }

    // =====================================================================
    // 1H. Biometric Integration Tables
    // =====================================================================
    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'hr_biometric_devices'"
    );
    if (!$exists) {
        $db->raw("
            CREATE TABLE hr_biometric_devices (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                device_name VARCHAR(100) NOT NULL,
                device_type VARCHAR(20) NOT NULL DEFAULT 'fingerprint',
                ip_address VARCHAR(45) NULL,
                port INT UNSIGNED NULL,
                serial_number VARCHAR(100) NULL,
                location_description VARCHAR(255) NULL,
                branch_id BIGINT UNSIGNED NULL,
                is_active TINYINT(1) NOT NULL DEFAULT 1,
                last_sync_at DATETIME NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_active (is_active)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    }

    $exists = $db->selectOne(
        "SELECT 1 FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'hr_biometric_punches'"
    );
    if (!$exists) {
        $db->raw("
            CREATE TABLE hr_biometric_punches (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                device_id BIGINT UNSIGNED NOT NULL,
                fingerprint_code VARCHAR(50) NOT NULL,
                punch_time DATETIME NOT NULL,
                punch_type VARCHAR(10) NOT NULL DEFAULT 'unknown',
                processed TINYINT(1) NOT NULL DEFAULT 0,
                attendance_id BIGINT UNSIGNED NULL,
                error_message VARCHAR(500) NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY idx_fingerprint (fingerprint_code),
                KEY idx_punch_time (punch_time),
                KEY idx_processed (processed),
                KEY idx_device (device_id),
                CONSTRAINT fk_punch_device FOREIGN KEY (device_id) REFERENCES hr_biometric_devices(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    }

    // =====================================================================
    // 1I. Military Status Data Migration
    // =====================================================================
    $db->raw("UPDATE hr_employee_profiles SET military_status = 'served' WHERE military_status = 'completed'");
    $db->raw("UPDATE hr_employee_profiles SET military_status = 'final_exemption' WHERE military_status = 'exempted'");
    $db->raw("UPDATE hr_employee_profiles SET military_status = 'temporary_exemption' WHERE military_status = 'postponed'");
    $db->raw("UPDATE hr_employee_profiles SET military_status = 'female' WHERE military_status = 'not_applicable'");

    // =====================================================================
    // 1J. System Config Seeds for HR Deductions & Leave
    // =====================================================================
    $configs = [
        ['hr.deduction.martyrs_fund_amount', '0', 'float', 'hr', 'خصم صندوق الشهداء - مبلغ شهري ثابت'],
        ['hr.deduction.regular_stamp_duty', '0', 'float', 'hr', 'خصم الدمغة العادية - مبلغ شهري'],
        ['hr.deduction.additional_stamp_duty', '0', 'float', 'hr', 'خصم الدمغة الإضافية - مبلغ شهري'],
        ['hr.leave.annual_days_under_10y', '21', 'integer', 'hr', 'أيام الإجازة السنوية - أقل من 10 سنوات خدمة'],
        ['hr.leave.annual_days_over_10y', '30', 'integer', 'hr', 'أيام الإجازة السنوية - 10 سنوات فأكثر أو سن 50+'],
        ['hr.leave.maternity_days', '90', 'integer', 'hr', 'أيام إجازة الوضع'],
        ['hr.leave.maternity_max_career', '3', 'integer', 'hr', 'الحد الأقصى لإجازات الوضع خلال المسيرة المهنية'],
        ['hr.leave.breastfeeding_daily_hours', '1', 'float', 'hr', 'ساعة الرضاعة اليومية بدون خصم'],
        ['hr.leave.unpaid_max_days', '0', 'integer', 'hr', 'الحد الأقصى للإجازة بدون مرتب (0 = غير محدد)'],
    ];

    foreach ($configs as [$key, $value, $type, $group, $desc]) {
        $exists = $db->selectOne("SELECT 1 FROM system_config WHERE config_key = ?", [$key]);
        if (!$exists) {
            $db->insert('system_config', [
                'config_key'     => $key,
                'config_value'   => $value,
                'config_type'    => $type,
                'group_name'     => $group,
                'description_ar' => $desc,
                'is_editable'    => 1,
                'created_at'     => $now,
                'updated_at'     => $now,
            ]);
        }
    }

    // =====================================================================
    // 1K. Permission Seed — Assign All HR Permissions to Super Admin
    // =====================================================================
    $superAdmin = $db->selectOne("SELECT id FROM roles WHERE role_code = 'super_admin'");
    if ($superAdmin) {
        $roleId = (int) $superAdmin['id'];
        $hrPermissions = [
            'hr.overtime.view', 'hr.overtime.create', 'hr.overtime.approve', 'hr.overtime.manage',
            'hr.shifts.view', 'hr.shifts.manage',
            'hr.permissions.view', 'hr.permissions.create', 'hr.permissions.approve',
            'hr.performance.view', 'hr.performance.manage',
            'hr.disciplinary.view', 'hr.disciplinary.manage',
            'hr.department.view', 'hr.department.manage',
            'hr.job_title.view', 'hr.job_title.manage',
            'hr.employee.view', 'hr.employee.manage', 'hr.employee.view_salary', 'hr.employee.manage_salary',
            'hr.contract.view', 'hr.contract.manage',
            'hr.attendance.view', 'hr.attendance.manage', 'hr.attendance.approve',
            'hr.leave.view', 'hr.leave.request', 'hr.leave.approve', 'hr.leave.manage',
            'hr.payroll.view', 'hr.payroll.process', 'hr.payroll.approve', 'hr.payslip.view_own',
            'hr.insurance.view', 'hr.insurance.manage',
            'hr.tax.view',
            'hr.loan.view', 'hr.loan.manage', 'hr.loan.approve',
            'hr.eos.view', 'hr.eos.manage', 'hr.eos.approve',
            'hr.document.view', 'hr.document.manage',
            'hr.report.view',
            'hr.holiday.manage',
            'hr.schedule.manage',
            'hr.biometric.view', 'hr.biometric.manage',
        ];

        foreach ($hrPermissions as $key) {
            $exists = $db->selectOne(
                "SELECT 1 FROM role_permissions WHERE role_id = ? AND permission_key = ?",
                [$roleId, $key]
            );
            if (!$exists) {
                $db->insert('role_permissions', [
                    'role_id'        => $roleId,
                    'permission_key' => $key,
                    'granted_at'     => $now,
                ]);
            }
        }
    }
};
