<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

// ---------------------------
// DB CONNECTION
// ---------------------------
require 'config.php';

// ---------------------------
// CSV FILE
// ---------------------------
$csvFile = 'attendance.csv';

if (!file_exists($csvFile)) {
    header("Location: attendance.php?csv_message=" . urlencode("CSV file not found!"));
    exit;
}

// ---------------------------
// HELPER: CLEAN STRING
// ---------------------------
function clean($value) {
    return trim($value, " \t\n\r\0\x0B'\"");
}

// ---------------------------
// LOAD EMPLOYEES (NORMALIZED)
// ---------------------------
$employees = [];

$res = $conn->query("SELECT id, name FROM employees");

while ($row = $res->fetch_assoc()) {
    // normalize key
    $key = strtolower(trim($row['name']));
    $employees[$key] = $row['id'];
}

// ---------------------------
// READ CSV
// ---------------------------
$rows = array_map('str_getcsv', file($csvFile));

if (empty($rows)) {
    header("Location: attendance.php?csv_message=" . urlencode("CSV is empty!"));
    exit;
}

$header = array_map('trim', array_shift($rows));
$indexes = array_flip($header);

// Validate required columns
$required = ['sName', 'Date', 'Time', 'AttendanceStatus'];

foreach ($required as $col) {
    if (!isset($indexes[$col])) {
        header("Location: attendance.php?csv_message=" . urlencode("Missing column: $col"));
        exit;
    }
}

// ---------------------------
// PREPARED STATEMENTS (REUSE)
// ---------------------------
$insertEmp = $conn->prepare("INSERT INTO employees (name) VALUES (?)");

/**
 * UPSERT STRATEGY: 
 * Inserts a new record, or updates the existing record if the employee already has an entry for that day.
 * The IF() checks preserve existing data so a check-out row doesn't overwrite an already stored check-in with NULL.
 */
$upsertAttendance = $conn->prepare("
    INSERT INTO attendance (employee_id, date, check_in, check_out)
    VALUES (?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE 
        check_in  = IF(VALUES(check_in) IS NOT NULL, VALUES(check_in), check_in),
        check_out = IF(VALUES(check_out) IS NOT NULL, VALUES(check_out), check_out)
");

// ---------------------------
// PROCESS CSV
// ---------------------------
foreach ($rows as $row) {

    $nameRaw = $row[$indexes['sName']] ?? '';
    $dateRaw = $row[$indexes['Date']] ?? '';
    $timeRaw = $row[$indexes['Time']] ?? '';
    $typeRaw = $row[$indexes['AttendanceStatus']] ?? '';

    $name = strtolower(clean($nameRaw));
    $date = clean($dateRaw);
    $time = clean($timeRaw);
    $type = strtolower(clean($typeRaw));

    // ---------------------------
    // NORMALIZE NAME
    // ---------------------------
    $name = strtolower($name);
    $name = preg_replace('/\s+/', ' ', $name);
    $name = trim($name);
    $name = preg_replace('/[^\p{L}\p{N}\s]/u', '', $name);

    // ---------------------------
    // BLOCKED NAMES
    // ---------------------------
    $blockedNames = [
        'abigail adunoye', 'dorgu josphine', 'edidiong okon', 'emeka onwuasor',
        'etimbuk christopher', 'etini eriti', 'felix ighose',
        'happines orlu', 'favour adema', 'irene david', 'janet anagbara',
        'nicholas taiwo', 'david ogunniran', 'orus ebimotimi anita','Ignatious Aneh',
        'philip oluwasheyi', 'chinyere anyalewechi','sarah w agada', 'stephen richard', 'tijani enizbe',
        'tomudo gift', 'victor efoagui', 'david macafebo', 'blessing anthony', 'favour kelvin', 'ignatious aneh', 'francis sunday',
        'alexander joseph','glory young', 'godbless emurarshe','ikeke kaidew', 'olamide afolabi', 'victor williams', 'unknown', 'null'
    ];

    // ---------------------------
    // EXACT FILTER
    // ---------------------------
    if (in_array($name, $blockedNames, true)) {
        continue;
    }

    if (!$name || !$date || !$time) continue;

    // ---------------------------
    // NORMALIZE DATE & TIME
    // ---------------------------
    $dateFormatted = date('Y-m-d', strtotime($date));
    $timeFormatted = date('H:i:s', strtotime($time));

    // ---------------------------
    // MAP TYPE
    // ---------------------------
    $checkIn = null;
    $checkOut = null;

    if ($type === 'checkin') {
        $checkIn = $timeFormatted;
    } elseif ($type === 'checkout') {
        $checkOut = $timeFormatted;
    } else {
        continue;
    }

    // ---------------------------
    // GET OR CREATE EMPLOYEE
    // ---------------------------
    if (!isset($employees[$name])) {
        $properName = ucwords($name);
        $insertEmp->bind_param("s", $properName);
        $insertEmp->execute();

        $employeeId = $insertEmp->insert_id;
        $employees[$name] = $employeeId;
    } else {
        $employeeId = $employees[$name];
    }

    // ---------------------------
    // EXECUTE MERGED INSERT / UPDATE
    // ---------------------------
    $upsertAttendance->bind_param(
        "isss",
        $employeeId,
        $dateFormatted,
        $checkIn,
        $checkOut
    );

    $upsertAttendance->execute();
}

// ---------------------------
// CLEANUP
// ---------------------------
$insertEmp->close();
$upsertAttendance->close();
$conn->close();

// ---------------------------
// DONE
// ---------------------------
header("Location: attendance.php?csv_message=" . urlencode("CSV Imported Successfully (Side-by-Side Mode)"));
exit;
?>