<?php
declare(strict_types=1);

use App\Core\Database;

return function (Database $db): void {
    $jsonPath = __DIR__ . '/data/employees_registry.json';
    $bankPath = __DIR__ . '/data/bank_accounts.json';

    if (!file_exists($jsonPath)) {
        echo "ERROR: employees_registry.json not found\n";
        return;
    }

    $employees = json_decode(file_get_contents($jsonPath), true);
    $bankData = file_exists($bankPath) ? json_decode(file_get_contents($bankPath), true) : [];

    // Index bank accounts by national_id
    $bankByNid = [];
    foreach ($bankData as $b) {
        $nid = trim($b['national_id'] ?? '');
        if ($nid) {
            $bankByNid[$nid] = $b['bank_account'] ?? '';
        }
    }

    // Cache department IDs by name_ar
    $deptRows = $db->select("SELECT id, name_ar FROM hr_departments WHERE is_archived = 0", []);
    $deptMap = [];
    foreach ($deptRows as $row) {
        $deptMap[$row['name_ar']] = (int) $row['id'];
    }

    // Cache job title IDs by name_ar
    $titleRows = $db->select("SELECT id, name_ar FROM hr_job_titles WHERE is_archived = 0", []);
    $titleMap = [];
    foreach ($titleRows as $row) {
        $titleMap[$row['name_ar']] = (int) $row['id'];
    }

    // Default password hash (will force change on first login)
    $defaultPassword = password_hash('Club@2026', PASSWORD_BCRYPT);

    // Get next employee_number sequence
    $lastNum = $db->selectOne(
        "SELECT employee_number FROM hr_employee_profiles ORDER BY CAST(SUBSTRING(employee_number, 4) AS UNSIGNED) DESC LIMIT 1",
        []
    );
    $nextSeq = 1;
    if ($lastNum && preg_match('/EMP(\d+)/', $lastNum['employee_number'], $m)) {
        $nextSeq = (int) $m[1] + 1;
    }

    $imported = 0;
    $skipped = 0;

    foreach ($employees as $emp) {
        $nationalId = trim($emp['national_id'] ?? '');

        // Skip employees without hire date (ministry secondees without data)
        if (empty($emp['hire_date']) || $emp['hire_date'] === 'وزارة') {
            $skipped++;
            continue;
        }

        // Skip if already exists by national_id
        if ($nationalId) {
            $exists = $db->selectOne(
                "SELECT id FROM hr_employee_profiles WHERE national_id = ? AND is_archived = 0",
                [$nationalId]
            );
            if ($exists) {
                $skipped++;
                continue;
            }
        }

        // Parse name (full name in one field)
        $fullName = trim($emp['name']);
        // Clean leading title prefixes (ك/, ك /)
        $fullName = preg_replace('/^ك\s*\/\s*/', '', $fullName);
        $fullName = trim($fullName);
        $nameParts = preg_split('/\s+/', $fullName, 2);
        $firstName = $nameParts[0] ?? $fullName;
        $lastName = $nameParts[1] ?? '';

        // Department lookup
        $deptId = $deptMap[$emp['department']] ?? null;

        // Job title lookup
        $jobTitleId = $titleMap[$emp['job_title']] ?? null;

        // Employee number
        $employeeNumber = 'EMP' . str_pad((string) $nextSeq, 4, '0', STR_PAD_LEFT);

        // Generate unique username from employee code or number
        $username = 'emp' . str_pad((string) $nextSeq, 4, '0', STR_PAD_LEFT);
        $existingUser = $db->selectOne("SELECT id FROM employees WHERE username = ?", [$username]);
        if ($existingUser) {
            $username = 'emp' . $nextSeq . '_' . time();
        }

        $nextSeq++;

        // Create system login account (employees table)
        $employeeId = $db->insert('employees', [
            'username'              => $username,
            'password_hash'         => $defaultPassword,
            'full_name_ar'          => $fullName,
            'phone'                 => $emp['phone'] ?: null,
            'is_active'             => 1,
            'force_password_change' => 1,
            'failed_login_count'    => 0,
            'created_at'            => date('Y-m-d H:i:s'),
            'updated_at'            => date('Y-m-d H:i:s'),
        ]);

        // Workforce type mapping
        $workforceType = match ($emp['workforce_type']) {
            'insured' => 'insured',
            'part_time' => 'part_time',
            'seconded' => 'part_time',
            default => 'insured',
        };

        // Bank info
        $bankAccount = $bankByNid[$nationalId] ?? '';

        // Calculate insurable salary (same as basic for insured, 0 for others)
        $salary = (float) ($emp['salary'] ?? 0);
        $insurableSalary = ($workforceType === 'insured' && $salary > 0) ? $salary : 0.00;

        $hireDate = $emp['hire_date'];
        if (strlen($hireDate) > 10) {
            $hireDate = substr($hireDate, 0, 10);
        }

        $insuranceDate = $emp['insurance_date'] ?? '';
        if (strlen($insuranceDate) > 10) {
            $insuranceDate = substr($insuranceDate, 0, 10);
        }

        $dob = $emp['date_of_birth'] ?? '';
        if (strlen($dob) > 10) {
            $dob = substr($dob, 0, 10);
        }

        $gradYear = $emp['graduation_year'] ?? null;
        if ($gradYear && (int) $gradYear > 2030) {
            $gradYear = null;
        }

        $data = [
            'employee_id'              => $employeeId,
            'employee_number'          => $employeeNumber,
            'fingerprint_code'         => $emp['employee_code'] ?: null,
            'first_name_ar'            => $firstName,
            'last_name_ar'             => $lastName,
            'phone'                    => $emp['phone'] ?: null,
            'national_id'              => $nationalId ?: null,
            'date_of_birth'            => $dob ?: null,
            'gender'                   => $emp['gender'],
            'nationality'              => 'egyptian',
            'education_qualification'  => $emp['qualification'] ?: null,
            'education_graduation_year' => $gradYear ? (int) $gradYear : null,
            'education_university'     => $emp['university'] ?: null,
            'education_grade'          => $emp['grade'] ?: null,
            'address'                  => $emp['address'] ?: null,
            'department_id'            => $deptId,
            'job_title_id'             => $jobTitleId,
            'hire_date'                => $hireDate,
            'employment_type'          => 'full_time',
            'workforce_type'           => $workforceType,
            'employment_status'        => 'active',
            'basic_salary'             => number_format($salary, 2, '.', ''),
            'insurable_salary'         => number_format($insurableSalary, 2, '.', ''),
            'bank_name'                => $bankAccount ? 'بنك الاسكندرية' : null,
            'bank_account_number'      => $bankAccount ?: null,
            'insurance_number'         => $emp['insurance_number'] ?: null,
            'insurance_start_date'     => $insuranceDate ?: null,
            'military_status'          => $emp['military_status'] ?: null,
            'has_disability'           => 0,
            'created_at'               => date('Y-m-d H:i:s'),
            'updated_at'               => date('Y-m-d H:i:s'),
        ];

        $db->insert('hr_employee_profiles', $data);
        $imported++;
    }

    echo "Employee import complete: {$imported} imported, {$skipped} skipped\n";
};
