<?php
require_once __DIR__ . '/../includes/db.php';

try {
    echo "--- Employees and their Shift Assignments ---\n";
    $employees = fetchAll("
        SELECT e.id, e.name, s.name as shift_name, s.start_time, s.end_time 
        FROM employees e 
        LEFT JOIN shifts s ON e.shift_id = s.id
    ");
    foreach ($employees as $emp) {
        echo "Employee: [ID: {$emp['id']}] {$emp['name']}\n";
        if ($emp['shift_name']) {
            echo "  Shift: {$emp['shift_name']} ({$emp['start_time']} - {$emp['end_time']})\n";
        } else {
            echo "  Shift: None Tagged\n";
        }
    }
    
    echo "\n--- Recent Attendance Logs with shift joins ---\n";
    $logs = fetchAll("
        SELECT a.id, e.name as employee_name, a.date, a.clock_in, a.status, s.start_time as shift_start_time
        FROM attendance a
        JOIN employees e ON a.employee_id = e.id
        LEFT JOIN shifts s ON e.shift_id = s.id
        ORDER BY a.date DESC, a.clock_in DESC
        LIMIT 10
    ");
    foreach ($logs as $log) {
        $timePart = date('H:i:s', strtotime($log['clock_in']));
        $isLate = false;
        if ($log['shift_start_time'] && $timePart > $log['shift_start_time']) {
            $isLate = true;
        }
        echo "Log ID: {$log['id']} | {$log['employee_name']} | Date: {$log['date']} | Clock In: {$log['clock_in']} | DB Status: {$log['status']} | Shift Start: " . ($log['shift_start_time'] ?: 'None') . " | Calculated Late: " . ($isLate ? 'YES' : 'NO') . "\n";
    }
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
