<?php
require 'config.php';
require 'vendor/autoload.php';

ini_set('display_errors', 1);
error_reporting(E_ALL);

use PhpOffice\PhpSpreadsheet\IOFactory;

if (isset($_FILES['excel_file']['name'])) {

    $fileName = $_FILES['excel_file']['tmp_name'];

    $spreadsheet = IOFactory::load($fileName);
    $sheet = $spreadsheet->getActiveSheet();
    $rows = $sheet->toArray();

    $success = 0;
    $failed = 0;

    // ✅ Helper function (NULL handler)
    function val($v) {
        return isset($v) && trim($v) !== '' ? $v : NULL;
    }

    // ✅ PREPARED STATEMENT
    $stmt = $conn->prepare("INSERT INTO users (
        id, username, password, first_name, last_name,
        email, location, division, designation,
        appointment_type, status, date_of_joining,
        reporting_manager, date_of_birth, gender,
        marital_status, bloodgroup, mobileno,
        aadharcard, pancard, uan_no,
        bank_name, account_number, ifsc_code,
        father_name
    ) VALUES (
        ?, ?, ?, ?, ?,
        ?, ?, ?, ?,
        ?, ?, ?,
        ?, ?, ?,
        ?, ?, ?,
        ?, ?, ?,
        ?, ?, ?,
        ?
    )
    ON DUPLICATE KEY UPDATE
        username=VALUES(username),
        password=VALUES(password),
        first_name=VALUES(first_name),
        last_name=VALUES(last_name),
        email=VALUES(email),
        location=VALUES(location),
        division=VALUES(division),
        designation=VALUES(designation),
        appointment_type=VALUES(appointment_type),
        status=VALUES(status),
        date_of_joining=VALUES(date_of_joining),
        reporting_manager=VALUES(reporting_manager),
        date_of_birth=VALUES(date_of_birth),
        gender=VALUES(gender),
        marital_status=VALUES(marital_status),
        bloodgroup=VALUES(bloodgroup),
        mobileno=VALUES(mobileno),
        aadharcard=VALUES(aadharcard),
        pancard=VALUES(pancard),
        uan_no=VALUES(uan_no),
        bank_name=VALUES(bank_name),
        account_number=VALUES(account_number),
        ifsc_code=VALUES(ifsc_code),
        father_name=VALUES(father_name)
    ");

    foreach ($rows as $index => $row) {

        if ($index == 0) continue;

        // ✅ ID
        $id = val($row[0]);
        if (!$id) {
            $id = 'EMP' . date('YmdHis') . rand(100,999);
        }

        // ✅ Username mandatory
        $username = val($row[1]);
        if (!$username) continue;

        $password = !empty($row[2]) ? password_hash($row[2], PASSWORD_DEFAULT) : NULL;
        $first_name = val($row[3]);
        $last_name = val($row[4]);
        $location = val($row[6]);
        $email = val($row[7]);
        $division = val($row[9]);
        $designation = val($row[10]);

        $appointment_type = (!empty($row[12]) && strtolower($row[12]) == 'permanent') ? 'regular' : 'contract';
        $status = (!empty($row[13]) && strtolower($row[13]) == 'active') ? 'active' : 'resigned';

        $date_of_joining = !empty($row[15]) ? date('Y-m-d', strtotime($row[15])) : NULL;
        $reporting_manager = val($row[18]);
        $date_of_birth = !empty($row[19]) ? date('Y-m-d', strtotime($row[19])) : NULL;

        // ⚠️ NOT NULL FIELDS → give default if empty
        $gender = val($row[21]) ?? 'Others';
        $marital_status = val($row[22]) ?? 'Single';
        $father_name = val($row[38]) ?? 'N/A';
        $bank_name = val($row[41]) ?? 'N/A';
        $account_number = val($row[43]) ?? '0';
        $ifsc = val($row[44]) ?? 'N/A';

        $bloodgroup = val($row[24]);
        $mobileno = !empty($row[26]) ? preg_replace('/[^0-9]/', '', $row[26]) : NULL;
        $aadhar = val($row[34]);
        $pan = val($row[35]);
        $uan = val($row[36]);

        // ✅ BIND
        $stmt->bind_param(
            "sssssssssssssssssssssssss",
            $id, $username, $password, $first_name, $last_name,
            $email, $location, $division, $designation,
            $appointment_type, $status, $date_of_joining,
            $reporting_manager, $date_of_birth, $gender,
            $marital_status, $bloodgroup, $mobileno,
            $aadhar, $pan, $uan,
            $bank_name, $account_number, $ifsc,
            $father_name
        );

        if ($stmt->execute()) {
            $success++;
        } else {
            echo "Error: " . $stmt->error . "<br>";
            $failed++;
        }
    }

    echo "<h3>Import Completed</h3>";
    echo "✅ Processed: $success <br>";
    echo "❌ Failed: $failed <br>";
}
?>