<?php
require_once '../config/config.php';
requireRole('secretariat');

$pageTitle = 'Attendance Report - Secretariat - PSAU GAD';
$db = getDB();

// Get filters
$year = $_GET['year'] ?? date('Y');
$activity_id = $_GET['activity_id'] ?? '';
$status_filter = $_GET['status'] ?? '';

// Get activities for dropdown
$stmt = $db->prepare("SELECT activity_id, title, activity_date FROM activities WHERE YEAR(activity_date) = ? AND is_active = 1 ORDER BY activity_date DESC");
$stmt->execute([$year]);
$activities_list = $stmt->fetchAll();

// Build query
if ($activity_id) {
    // Single activity attendance
    $stmt = $db->prepare("
        SELECT 
            aa.*,
            ep.first_name,
            ep.last_name,
            ep.department,
            ep.category,
            ep.gender,
            a.title as activity_title,
            a.activity_date,
            a.attendance_cutoff_time
        FROM activity_attendance aa
        JOIN employee_profiles ep ON aa.user_id = ep.user_id
        JOIN activities a ON aa.activity_id = a.activity_id
        WHERE aa.activity_id = ?
        " . ($status_filter ? "AND aa.status = ?" : "") . "
        ORDER BY aa.check_in_time ASC
    ");
    
    if ($status_filter) {
        $stmt->execute([$activity_id, $status_filter]);
    } else {
        $stmt->execute([$activity_id]);
    }
    $attendance_records = $stmt->fetchAll();
    
    // Get activity details
    $stmt = $db->prepare("SELECT * FROM activities WHERE activity_id = ?");
    $stmt->execute([$activity_id]);
    $activity = $stmt->fetch();
    
    // Calculate statistics
    $total_attendance = count($attendance_records);
    $present_count = count(array_filter($attendance_records, fn($r) => $r['status'] === 'present'));
    $late_count = count(array_filter($attendance_records, fn($r) => $r['status'] === 'late'));
    $male_count = count(array_filter($attendance_records, fn($r) => $r['gender'] === 'Male'));
    $female_count = count(array_filter($attendance_records, fn($r) => $r['gender'] === 'Female'));
    
} else {
    // Summary report for all activities
    $stmt = $db->prepare("
        SELECT 
            a.activity_id,
            a.title,
            a.activity_date,
            a.venue,
            a.target_participants,
            COUNT(DISTINCT aa.attendance_id) as total_attendance,
            COUNT(DISTINCT CASE WHEN aa.status = 'present' THEN aa.attendance_id END) as present_count,
            COUNT(DISTINCT CASE WHEN aa.status = 'late' THEN aa.attendance_id END) as late_count,
            COUNT(DISTINCT CASE WHEN ep.gender = 'Male' THEN aa.user_id END) as male_count,
            COUNT(DISTINCT CASE WHEN ep.gender = 'Female' THEN aa.user_id END) as female_count
        FROM activities a
        LEFT JOIN activity_attendance aa ON a.activity_id = aa.activity_id
        LEFT JOIN employee_profiles ep ON aa.user_id = ep.user_id
        WHERE YEAR(a.activity_date) = ? AND a.is_active = 1
        GROUP BY a.activity_id
        ORDER BY a.activity_date DESC
    ");
    $stmt->execute([$year]);
    $activities = $stmt->fetchAll();
    
    // Calculate totals
    $totalActivities = count($activities);
    $totalAttendance = array_sum(array_column($activities, 'total_attendance'));
    $totalPresent = array_sum(array_column($activities, 'present_count'));
    $totalLate = array_sum(array_column($activities, 'late_count'));
}

// Export functionality
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="Attendance_Report_' . $year . '_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    fputcsv($output, ['PAMPANGA STATE AGRICULTURAL UNIVERSITY']);
    fputcsv($output, ['ATTENDANCE REPORT']);
    fputcsv($output, ['Year: ' . $year]);
    fputcsv($output, []);
    
    if ($activity_id && isset($activity)) {
        // Single activity export
        fputcsv($output, ['Activity:', $activity['title']]);
        fputcsv($output, ['Date:', date('F d, Y', strtotime($activity['activity_date']))]);
        fputcsv($output, []);
        fputcsv($output, ['Name', 'Department', 'Category', 'Gender', 'Check-in Time', 'Status', 'Type']);
        
        foreach ($attendance_records as $record) {
            fputcsv($output, [
                $record['first_name'] . ' ' . $record['last_name'],
                $record['department'],
                $record['category'],
                $record['gender'],
                date('g:i A', strtotime($record['check_in_time'])),
                ucfirst($record['status']),
                ucfirst(str_replace('_', ' ', $record['attendance_type']))
            ]);
        }
    } else {
        // Summary export
        fputcsv($output, ['Activity', 'Date', 'Venue', 'Target', 'Attendance', 'Present', 'Late', 'Male', 'Female']);
        foreach ($activities as $act) {
            fputcsv($output, [
                $act['title'],
                date('Y-m-d', strtotime($act['activity_date'])),
                $act['venue'],
                $act['target_participants'],
                $act['total_attendance'],
                $act['present_count'],
                $act['late_count'],
                $act['male_count'],
                $act['female_count']
            ]);
        }
    }
    
    fclose($output);
    exit;
}

include '../includes/header.php';
include '../includes/navbar.php';
?>

<div class="min-h-screen bg-gradient-to-br from-red-50 via-white to-orange-50 py-8">
    <div class="max-w-7xl mx-auto px-4 sm:px-6 lg:px-8">
        
        <!-- Header -->
        <div class="mb-8">
            <div class="flex items-center justify-between">
                <div>
                    <h1 class="text-3xl font-bold text-gray-900">
                        <i class="fas fa-user-check text-secretariat mr-3"></i>
                        Attendance Report
                    </h1>
                    <p class="text-gray-600 mt-2">Activity attendance summary and records</p>
                </div>
                <div class="flex gap-3">
                    <a href="?year=<?php echo $year; ?>&activity_id=<?php echo $activity_id; ?>&status=<?php echo $status_filter; ?>&export=excel" 
                       class="bg-green-600 hover:bg-green-700 text-white px-6 py-3 rounded-lg font-semibold transition btn-animate flex items-center gap-2">
                        <i class="fas fa-file-excel"></i>
                        Export to Excel
                    </a>
                    <button onclick="window.print()" 
                            class="bg-purple-600 hover:bg-purple-700 text-white px-6 py-3 rounded-lg font-semibold transition btn-animate flex items-center gap-2">
                        <i class="fas fa-print"></i>
                        Print
                    </button>
                    <a href="reports.php" 
                       class="bg-gray-600 hover:bg-gray-700 text-white px-6 py-3 rounded-lg font-semibold transition btn-animate">
                        <i class="fas fa-arrow-left mr-2"></i>
                        Back
                    </a>
                </div>
            </div>
        </div>

        <!-- Filters -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8">
            <form method="GET" class="grid grid-cols-1 md:grid-cols-4 gap-4">
                <div>
                    <label class="block text-sm font-semibold text-gray-700 mb-2">Year</label>
                    <select name="year" class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:ring-2 focus:ring-secretariat focus:border-transparent">
                        <?php for ($y = date('Y'); $y >= 2020; $y--): ?>
                            <option value="<?php echo $y; ?>" <?php echo $y == $year ? 'selected' : ''; ?>><?php echo $y; ?></option>
                        <?php endfor; ?>
                    </select>
                </div>
                <div>
                    <label class="block text-sm font-semibold text-gray-700 mb-2">Activity (Optional)</label>
                    <select name="activity_id" class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:ring-2 focus:ring-secretariat focus:border-transparent">
                        <option value="">All Activities (Summary)</option>
                        <?php foreach ($activities_list as $act): ?>
                            <option value="<?php echo $act['activity_id']; ?>" <?php echo $act['activity_id'] == $activity_id ? 'selected' : ''; ?>>
                                <?php echo htmlspecialchars($act['title']); ?> - <?php echo date('M d', strtotime($act['activity_date'])); ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div>
                    <label class="block text-sm font-semibold text-gray-700 mb-2">Status (Optional)</label>
                    <select name="status" class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:ring-2 focus:ring-secretariat focus:border-transparent">
                        <option value="">All Status</option>
                        <option value="present" <?php echo $status_filter === 'present' ? 'selected' : ''; ?>>Present</option>
                        <option value="late" <?php echo $status_filter === 'late' ? 'selected' : ''; ?>>Late</option>
                    </select>
                </div>
                <div class="flex items-end">
                    <button type="submit" class="w-full bg-secretariat hover:bg-secretariat-dark text-white px-6 py-2 rounded-lg font-semibold transition btn-animate">
                        <i class="fas fa-filter mr-2"></i>
                        Generate Report
                    </button>
                </div>
            </form>
        </div>

        <?php if ($activity_id && isset($activity)): ?>
            <!-- Single Activity Attendance Report -->
            <div class="bg-white rounded-xl shadow-lg p-8 mb-8">
                <div class="text-center mb-6">
                    <h2 class="text-2xl font-bold text-gray-900">PAMPANGA STATE AGRICULTURAL UNIVERSITY</h2>
                    <h3 class="text-xl font-bold text-secretariat mt-2">ATTENDANCE REPORT</h3>
                    <p class="text-lg text-gray-700 mt-2"><?php echo htmlspecialchars($activity['title']); ?></p>
                    <p class="text-gray-600"><?php echo date('F d, Y', strtotime($activity['activity_date'])); ?> | <?php echo htmlspecialchars($activity['venue']); ?></p>
                </div>

                <!-- Statistics -->
                <div class="grid grid-cols-1 md:grid-cols-5 gap-4 mb-8">
                    <div class="bg-blue-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-blue-600"><?php echo $total_attendance; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Total Attendance</div>
                    </div>
                    <div class="bg-green-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-green-600"><?php echo $present_count; ?></div>
                        <div class="text-sm text-gray-600 mt-1">On Time</div>
                    </div>
                    <div class="bg-yellow-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-yellow-600"><?php echo $late_count; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Late</div>
                    </div>
                    <div class="bg-indigo-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-indigo-600"><?php echo $male_count; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Male</div>
                    </div>
                    <div class="bg-pink-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-pink-600"><?php echo $female_count; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Female</div>
                    </div>
                </div>

                <!-- Attendance Records -->
                <div class="overflow-x-auto">
                    <table class="min-w-full divide-y divide-gray-200">
                        <thead class="bg-gray-50">
                            <tr>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">#</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Name</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Department</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Category</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Gender</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Check-in Time</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Status</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Type</th>
                            </tr>
                        </thead>
                        <tbody class="bg-white divide-y divide-gray-200">
                            <?php if (empty($attendance_records)): ?>
                                <tr>
                                    <td colspan="8" class="px-6 py-8 text-center text-gray-500">
                                        <i class="fas fa-inbox text-4xl mb-3"></i>
                                        <p>No attendance records found</p>
                                    </td>
                                </tr>
                            <?php else: ?>
                                <?php foreach ($attendance_records as $index => $record): ?>
                                    <tr class="hover:bg-gray-50">
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-500"><?php echo $index + 1; ?></td>
                                        <td class="px-6 py-4 whitespace-nowrap">
                                            <div class="text-sm font-semibold text-gray-900"><?php echo htmlspecialchars($record['first_name'] . ' ' . $record['last_name']); ?></div>
                                        </td>
                                        <td class="px-6 py-4 text-sm text-gray-700"><?php echo htmlspecialchars($record['department']); ?></td>
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700"><?php echo htmlspecialchars($record['category']); ?></td>
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700"><?php echo $record['gender']; ?></td>
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700"><?php echo date('g:i A', strtotime($record['check_in_time'])); ?></td>
                                        <td class="px-6 py-4 whitespace-nowrap">
                                            <?php if ($record['status'] === 'present'): ?>
                                                <span class="px-2 py-1 text-xs font-semibold rounded-full bg-green-100 text-green-800">On Time</span>
                                            <?php elseif ($record['status'] === 'late'): ?>
                                                <span class="px-2 py-1 text-xs font-semibold rounded-full bg-yellow-100 text-yellow-800">Late</span>
                                            <?php else: ?>
                                                <span class="px-2 py-1 text-xs font-semibold rounded-full bg-gray-100 text-gray-800"><?php echo ucfirst($record['status']); ?></span>
                                            <?php endif; ?>
                                        </td>
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700">
                                            <?php echo ucfirst(str_replace('_', ' ', $record['attendance_type'])); ?>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>

        <?php else: ?>
            <!-- Summary Report -->
            <div class="bg-white rounded-xl shadow-lg p-8 mb-8">
                <div class="text-center mb-6">
                    <h2 class="text-2xl font-bold text-gray-900">PAMPANGA STATE AGRICULTURAL UNIVERSITY</h2>
                    <h3 class="text-xl font-bold text-secretariat mt-2">ATTENDANCE SUMMARY REPORT</h3>
                    <p class="text-lg text-gray-700 mt-2">Calendar Year <?php echo $year; ?></p>
                </div>

                <!-- Summary Statistics -->
                <div class="grid grid-cols-1 md:grid-cols-4 gap-4 mb-8">
                    <div class="bg-blue-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-blue-600"><?php echo $totalActivities; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Total Activities</div>
                    </div>
                    <div class="bg-green-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-green-600"><?php echo $totalAttendance; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Total Attendance</div>
                    </div>
                    <div class="bg-purple-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-purple-600"><?php echo $totalPresent; ?></div>
                        <div class="text-sm text-gray-600 mt-1">On Time</div>
                    </div>
                    <div class="bg-yellow-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-yellow-600"><?php echo $totalLate; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Late</div>
                    </div>
                </div>

                <!-- Activities Table -->
                <div class="overflow-x-auto">
                    <table class="min-w-full divide-y divide-gray-200">
                        <thead class="bg-gray-50">
                            <tr>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Date</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Activity</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Venue</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Target</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Attendance</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Status</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Gender</th>
                            </tr>
                        </thead>
                        <tbody class="bg-white divide-y divide-gray-200">
                            <?php if (empty($activities)): ?>
                                <tr>
                                    <td colspan="7" class="px-6 py-8 text-center text-gray-500">
                                        <i class="fas fa-inbox text-4xl mb-3"></i>
                                        <p>No activities found for <?php echo $year; ?></p>
                                    </td>
                                </tr>
                            <?php else: ?>
                                <?php foreach ($activities as $act): ?>
                                    <tr class="hover:bg-gray-50">
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700">
                                            <?php echo date('M d, Y', strtotime($act['activity_date'])); ?>
                                        </td>
                                        <td class="px-6 py-4">
                                            <div class="text-sm font-semibold text-gray-900"><?php echo htmlspecialchars($act['title']); ?></div>
                                        </td>
                                        <td class="px-6 py-4 text-sm text-gray-700"><?php echo htmlspecialchars($act['venue']); ?></td>
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700"><?php echo $act['target_participants']; ?></td>
                                        <td class="px-6 py-4 whitespace-nowrap">
                                            <div class="text-sm font-semibold text-gray-900"><?php echo $act['total_attendance']; ?></div>
                                            <div class="text-xs text-gray-500">
                                                <?php 
                                                $attendance_rate = $act['target_participants'] > 0 ? round(($act['total_attendance'] / $act['target_participants']) * 100) : 0;
                                                echo $attendance_rate . '%';
                                                ?>
                                            </div>
                                        </td>
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700">
                                            <div>On Time: <?php echo $act['present_count']; ?></div>
                                            <div class="text-xs text-gray-500">Late: <?php echo $act['late_count']; ?></div>
                                        </td>
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700">
                                            <div>M: <?php echo $act['male_count']; ?></div>
                                            <div class="text-xs text-gray-500">F: <?php echo $act['female_count']; ?></div>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        <?php endif; ?>

    </div>
</div>

<?php include '../includes/footer.php'; ?>

