<?php
// hr_api/import_employees.php

// Include the reusable CORS handler
require_once __DIR__ . '/cors.php';

header('Content-Type: application/json'); // Always send JSON response

// Handle pre-flight OPTIONS request
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit();
}

require_once __DIR__ . '/db.php'; // Your database connection (PDO)

// Function to generate the next employee number
function getNextEmployeeNumber($pdo) {
    try {
        $stmt = $pdo->query("SELECT emp_no FROM employee_details ORDER BY id DESC LIMIT 1");
        $lastEmpNoRow = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($lastEmpNoRow && isset($lastEmpNoRow['emp_no'])) {
            $lastEmpNo = $lastEmpNoRow['emp_no'];
            if (preg_match('/MCDO\/(\d+)/i', $lastEmpNo, $matches)) {
                $lastNumber = (int)$matches[1];
            } else {
                error_log("Existing emp_no '{$lastEmpNo}' does not match expected pattern in getNextEmployeeNumber.");
                $lastNumber = 0;
            }
            $nextNumber = $lastNumber + 1;
            $nextEmpNo = 'MCDO/' . str_pad($nextNumber, 5, '0', STR_PAD_LEFT);
        } else {
            $nextEmpNo = 'MCDO/00001';
        }
        return $nextEmpNo;
    } catch (PDOException $e) {
        error_log("Error getting next employee number: " . $e->getMessage());
        return false;
    }
}

// Get the raw POST data (JSON from frontend)
$json_data = file_get_contents('php://input');
$employeesData = json_decode($json_data, true);

if (json_last_error() !== JSON_ERROR_NONE) {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'Invalid JSON data received.']);
    exit();
}

if (!is_array($employeesData) || empty($employeesData)) {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'No employee data provided for import.']);
    exit();
}

$insertedCount = 0;
$failedRecords = [];
$insertedEmployees = [];

// NEW MAPPING: Based on your provided 'CREATE TABLE' schema and the debug log output
// Note: The Excel headers appear to be in snake_case already, so we'll use that as the key.
$excelToDbMap = [
    // Personal Details
    'surname' => 'surname',
    'given_name' => 'given_name',
    'othername' => 'othername',
    'nin' => 'nin',
    'gender' => 'gender',
    'marital_status' => 'marital_status',
    'date_of_birth' => 'date_of_birth',
    'religion' => 'religion',
    'nationality' => 'nationality',
    'employee_address' => 'employee_address',
    'phone' => 'phone',
    'email' => 'email',
    'no_of_dependants' => 'no_of_dependants',
    'no_of_children' => 'no_of_children',
    'highest_level_of_education' => 'highest_level_of_education',
    'photo' => 'photo',
    
    // Emergency Next of Kin
    'emmergency_next_of_kin_name' => 'emmergency_next_of_kin_name',
    'emmergency_next_of_kin_contact' => 'emmergency_next_of_kin_contact',
    'emmergency_next_of_kin_address' => 'emmergency_next_of_kin_address',
    'emmergency_next_of_kin_relationship' => 'emmergency_next_of_kin_relationship',

    // Benefit Next of Kin
    'benefit_next_of_kin_name' => 'benefit_next_of_kin_name',
    'benefit_next_of_kin_contact' => 'benefit_next_of_kin_contact',
    'benefit_next_of_kin_address' => 'benefit_next_of_kin_address',
    'benefit_next_of_kin_relationship' => 'benefit_next_of_kin_relationship',

    // Spouse
    'spouse_name' => 'spouse_name',
    'spouse_religion' => 'spouse_religion',
    'spouse_status' => 'spouse_status',
    'spouse_contact' => 'spouse_contact',
    'spouse_address' => 'spouse_address',

    // Family (Father & Mother)
    'father_name' => 'father_name',
    'father_contact' => 'father_contact',
    'father_address' => 'father_address',
    'father_status' => 'father_status',
    'mother_name' => 'mother_name',
    'mother_contact' => 'mother_contact',
    'mother_address' => 'mother_address',
    'mother_status' => 'mother_status',
    
    // Employment & Contract Details
    'department' => 'department',
    'role' => 'role',
    'region' => 'region',
    'status' => 'status',
    'supervisor' => 'supervisor',
    'branch_region' => 'branch_region',
    'contract_period' => 'contract_period',
    'contract_type' => 'contract_type',
    'contract_start_date' => 'contract_start_date',
    'contract_end_date' => 'contract_end_date',
    'probation_period_months' => 'probation_period_months',
    'contract_signed' => 'contract_signed',
    'contract_file_url' => 'contract_file_url',
    'contract_last_renewed_on' => 'contract_last_renewed_on',
    'date_of_hire' => 'date_of_hire',
    'payroll_type' => 'payroll_type',
    
    // Professional Details
    'profession' => 'profession',
    'profession_license_no' => 'profession_license_no',
    'profession_license_expiry' => 'profession_license_expiry',
    'profession_license_status' => 'profession_license_status',

    // Payroll & Financial
    'bank_name' => 'bank_name',
    'ac_name' => 'ac_name',
    'ac_no' => 'ac_no',
    'nssf_no' => 'nssf_no',
    'tin_no' => 'tin_no',
    'basic_salary' => 'basic_salary',
    'transport_allowance' => 'transport_allowance',
    'medical_allowance' => 'medical_allowance',
    'facilitation_allowance' => 'facilitation_allowance',
    'housing_allowance' => 'housing_allowance',
    'bonuses' => 'bonuses',
    'savings' => 'savings',
    'notes' => 'notes',
    'medical_info' => 'medical_info',
    'attendance_days' => 'attendance_days',
    'attendance_hours' => 'attendance_hours',
];


foreach ($employeesData as $index => $employee) {
    // ---- START DEBUGGING BLOCK ----
    // This line is no longer needed but kept for reference on how we found the problem.
    // error_log("Excel headers found: " . implode(', ', array_keys($employee)));
    // ---- END DEBUGGING BLOCK ----
    
    $rowNumber = $index + 2;
    $processedEmployee = [];
    $isRowEmpty = true;

    foreach ($employee as $excelHeader => $value) {
        $dbColumnName = null;

        // The Excel headers are already in snake_case, so we can use them directly
        if (isset($excelToDbMap[$excelHeader])) {
            $dbColumnName = $excelToDbMap[$excelHeader];
        } 
        // Handle dynamic child/dependant columns with or without status
        elseif (preg_match('/^(child|dependant)_(\d+)_status$/i', $excelHeader, $matches)) {
            $dbColumnName = $matches[1] . '_' . $matches[2] . '_status';
        }
        elseif (preg_match('/^(child|dependant)_(\d+)$/i', $excelHeader, $matches)) {
            $dbColumnName = $matches[1] . '_' . $matches[2];
        } else {
            continue; // Skip unknown columns
        }

        $processedValue = trim($value);

        // Check if the value is not empty or if it's a number, which indicates meaningful data
        if (!empty($processedValue) || is_numeric($processedValue) || $processedValue === '0' || $processedValue === '0.00') {
            $isRowEmpty = false;
        }

        // Convert Excel Date Numbers to YYYY-MM-DD strings
        if (in_array($dbColumnName, ['date_of_birth', 'date_of_hire', 'profession_license_expiry', 'contract_start_date', 'contract_end_date', 'contract_last_renewed_on']) && is_numeric($processedValue) && $processedValue > 1) {
            $unixTimestamp = ($processedValue - 25569) * 86400;
            $date = new DateTime("@$unixTimestamp");
            $processedValue = $date->format('Y-m-d');
        } 
        // Convert numeric fields to float/int
        elseif (in_array($dbColumnName, ['no_of_dependants', 'no_of_children', 'basic_salary', 'transport_allowance', 'housing_allowance', 'facilitation_allowance', 'medical_allowance', 'bonuses', 'attendance_days', 'attendance_hours', 'savings', 'probation_period_months'])) {
            $processedValue = is_numeric($processedValue) ? (float)$processedValue : 0.00;
        }
        // Handle boolean fields like 'contract_signed'
        elseif ($dbColumnName === 'contract_signed') {
            $processedValue = in_array(strtolower($processedValue), ['yes', 'true', '1']) ? 1 : 0;
        }
        // Handle enum fields with specific values
        elseif (in_array($dbColumnName, ['gender', 'marital_status', 'father_status', 'mother_status', 'spouse_status', 'status', 'profession_license_status'])) {
            $processedValue = ucfirst(strtolower($processedValue));
        }

        $processedEmployee[$dbColumnName] = $processedValue;
    }

    // Skip rows that are completely empty
    if ($isRowEmpty) {
        continue;
    }

    try {
        $nextEmpNo = getNextEmployeeNumber($pdo);
        if ($nextEmpNo === false) {
            $failedRecords[] = ['row' => $rowNumber, 'message' => 'Failed to generate unique employee number.'];
            continue;
        }
        $processedEmployee['emp_no'] = $nextEmpNo;

        // CORRECTED: Explicitly set a default value for payroll_type if it's empty
        // to prevent the 'NOT NULL' error.
        if (empty($processedEmployee['payroll_type'])) {
            $processedEmployee['payroll_type'] = 'Salary';
        }

        // Construct dynamic SQL INSERT statement
        $columns = implode(', ', array_keys($processedEmployee));
        $placeholders = implode(', ', array_fill(0, count($processedEmployee), '?'));

        $sql = "INSERT INTO employee_details ($columns) VALUES ($placeholders)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array_values($processedEmployee));

        $insertedCount++;
        $processedEmployee['id'] = $pdo->lastInsertId();
        $insertedEmployees[] = $processedEmployee;

    } catch (PDOException $e) {
        $failedRecords[] = ['row' => $rowNumber, 'message' => 'Database insert failed: ' . $e->getMessage()];
        error_log("Failed to insert employee from Excel row {$rowNumber}: " . $e->getMessage());
    }
}

echo json_encode([
    'success' => true,
    'message' => "Batch import completed. Successfully imported {$insertedCount} employees. " . count($failedRecords) . " failed.",
    'inserted_count' => $insertedCount,
    'failed_records' => $failedRecords,
    'inserted_employees' => $insertedEmployees
]);
exit();
?>