<?php
session_start();
include('includes/config.php');
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

// Helper function for ID creation
function getOrCreateId($dbh, $table, $nameColumn, $value, $idColumn)
{
    $query = "SELECT $idColumn FROM $table WHERE $nameColumn = :value AND is_deleted = 0 LIMIT 1";
    $stmt = $dbh->prepare($query);
    $stmt->bindParam(':value', $value);
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($result) {
        return $result[$idColumn];
    } else {
        $insert = "INSERT INTO $table ($nameColumn, is_deleted) VALUES (:value, 0)";
        $stmt = $dbh->prepare($insert);
        $stmt->bindParam(':value', $value);
        $stmt->execute();
        return $dbh->lastInsertId();
    }
}

if (isset($_POST['import'])) {
    $file_mimes = [
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'application/vnd.ms-excel'
    ];

    if (!empty($_FILES['excel_file']['name']) && in_array($_FILES['excel_file']['type'], $file_mimes)) {
        $filePath = $_FILES['excel_file']['tmp_name'];
        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        $imported = 0;
        $skipped = 0;

        for ($i = 1; $i < count($rows); $i++) { // Skip header
            $row = $rows[$i];

            $staffId = trim($row[1]);
            $fullName = trim($row[2]);
            $designationName = trim($row[3]);
            $departmentName = trim($row[4]);
            $qualification = trim($row[5]);
            $joiningDateRaw = trim($row[6]);
            $joiningDate = null;

            if (!empty($joiningDateRaw)) {
                // Try to detect and convert formats like 3.10.2024 or 03.10.2024
                $joiningDateObj = DateTime::createFromFormat('d.m.Y', $joiningDateRaw);
                if ($joiningDateObj) {
                    $joiningDate = $joiningDateObj->format('Y-m-d');
                } else {
                    // Try Excel numeric date (if date stored as number)
                    if (is_numeric($joiningDateRaw)) {
                        $joiningDate = date('Y-m-d', \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($joiningDateRaw));
                    } else {
                        // fallback: if not recognized, keep as NULL
                        $joiningDate = null;
                    }
                }
            }

            $mobile = trim($row[7]);
            $email = trim($row[8]);
            $address = trim($row[9]);

            if (empty($staffId) || empty($fullName))
                continue;

            // Check duplicate StaffId
            $check = $dbh->prepare("SELECT id FROM tblstaff WHERE StaffId = :staffId LIMIT 1");
            $check->bindParam(':staffId', $staffId);
            $check->execute();
            if ($check->fetch()) {
                $skipped++;
                continue;
            }

            // Get or create Designation and Department
            $designation_id = getOrCreateId($dbh, 'tblstaffdesignation', 'designation_name', $designationName, 'designation_id');
            $department_id = getOrCreateId($dbh, 'tbldepartment', 'department_name', $departmentName, 'department_id');

            $status = 1;
            $gender_id = 2;
            $regDate = date('Y-m-d H:i:s');
            $password = password_hash($mobile, PASSWORD_DEFAULT);

            $sql = "INSERT INTO tblstaff 
                    (StaffId, FullName, EmailId, MobileNumber, Password, 
                     Status, RegDate, Address, department_id, designation_id, 
                     Qualification, JoiningDate, gender_id)
                    VALUES 
                    (:staffId, :fullName, :email, :mobile, :password,
                     :status, :regDate, :address, :department_id, :designation_id,
                     :qualification, :joiningDate, :gender_id)";

            $stmt = $dbh->prepare($sql);
            $stmt->bindParam(':staffId', $staffId);
            $stmt->bindParam(':fullName', $fullName);
            $stmt->bindParam(':email', $email);
            $stmt->bindParam(':mobile', $mobile);
            $stmt->bindParam(':password', $password);
            $stmt->bindParam(':status', $status);
            $stmt->bindParam(':regDate', $regDate);
            $stmt->bindParam(':address', $address);
            $stmt->bindParam(':department_id', $department_id);
            $stmt->bindParam(':designation_id', $designation_id);
            $stmt->bindParam(':qualification', $qualification);
            $stmt->bindParam(':joiningDate', $joiningDate);
            $stmt->bindParam(':gender_id', $gender_id);
            $stmt->execute();
            $imported++;
        }

        $_SESSION['msg'] = "Imported: $imported staff, Skipped duplicates: $skipped";
        header('location:import_staff.php');
        exit;
    } else {
        $_SESSION['error'] = "Invalid file type. Please upload an Excel (.xlsx or .xls) file.";
        header('location:import_staff.php');
        exit;
    }
}
?>