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

$pageTitle = 'Training/Seminar Report - Secretariat - PSAU GAD';
$db = getDB();

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

// 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 based on filters
if ($activity_id) {
    // Single activity detailed report
    $stmt = $db->prepare("
        SELECT 
            a.*,
            COUNT(DISTINCT ar.registration_id) as total_registrations,
            COUNT(DISTINCT CASE WHEN ar.status = 'approved' THEN ar.registration_id END) as approved_registrations,
            COUNT(DISTINCT aa.attendance_id) as total_attendance,
            COUNT(DISTINCT CASE WHEN ep.gender = 'Male' THEN aa.user_id END) as male_attendance,
            COUNT(DISTINCT CASE WHEN ep.gender = 'Female' THEN aa.user_id END) as female_attendance,
            AVG(CASE WHEN er.rating IS NOT NULL THEN er.rating END) as avg_rating
        FROM activities a
        LEFT JOIN activity_registrations ar ON a.activity_id = ar.activity_id
        LEFT JOIN activity_attendance aa ON a.activity_id = aa.activity_id
        LEFT JOIN employee_profiles ep ON aa.user_id = ep.user_id
        LEFT JOIN evaluation_responses er ON a.activity_id = er.activity_id
        WHERE a.activity_id = ?
        GROUP BY a.activity_id
    ");
    $stmt->execute([$activity_id]);
    $activity = $stmt->fetch();
    
    // Get pre-test results
    $stmt = $db->prepare("
        SELECT AVG(ptr.score) as avg_score, COUNT(DISTINCT ptr.user_id) as total_takers
        FROM pre_test_responses ptr
        JOIN pre_tests pt ON ptr.test_id = pt.test_id
        WHERE pt.activity_id = ?
    ");
    $stmt->execute([$activity_id]);
    $pretest_data = $stmt->fetch();
    
    // Get post-test results
    $stmt = $db->prepare("
        SELECT AVG(ptr.score) as avg_score, COUNT(DISTINCT ptr.user_id) as total_takers
        FROM post_test_responses ptr
        JOIN post_tests pt ON ptr.test_id = pt.test_id
        WHERE pt.activity_id = ?
    ");
    $stmt->execute([$activity_id]);
    $posttest_data = $stmt->fetch();
    
    // Get attendees by category
    $stmt = $db->prepare("
        SELECT ep.category, COUNT(DISTINCT aa.user_id) as count
        FROM activity_attendance aa
        JOIN employee_profiles ep ON aa.user_id = ep.user_id
        WHERE aa.activity_id = ?
        GROUP BY ep.category
    ");
    $stmt->execute([$activity_id]);
    $attendees_by_category = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
    
} else {
    // Summary report for all activities in the year
    $stmt = $db->prepare("
        SELECT 
            a.*,
            COUNT(DISTINCT ar.registration_id) as total_registrations,
            COUNT(DISTINCT CASE WHEN ar.status = 'approved' THEN ar.registration_id END) as approved_registrations,
            COUNT(DISTINCT aa.attendance_id) as total_attendance,
            COUNT(DISTINCT CASE WHEN ep.gender = 'Male' THEN aa.user_id END) as male_attendance,
            COUNT(DISTINCT CASE WHEN ep.gender = 'Female' THEN aa.user_id END) as female_attendance
        FROM activities a
        LEFT JOIN activity_registrations ar ON a.activity_id = ar.activity_id
        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);
    $totalParticipants = array_sum(array_column($activities, 'total_attendance'));
    $totalMale = array_sum(array_column($activities, 'male_attendance'));
    $totalFemale = array_sum(array_column($activities, 'female_attendance'));
    $totalHours = array_sum(array_column($activities, 'training_hours'));
}

// Export functionality
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="Training_Report_' . $year . '_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    fputcsv($output, ['PAMPANGA STATE AGRICULTURAL UNIVERSITY']);
    fputcsv($output, ['TRAINING AND SEMINAR 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, ['Venue:', $activity['venue']]);
        fputcsv($output, ['Training Hours:', $activity['training_hours']]);
        fputcsv($output, []);
        fputcsv($output, ['Total Attendance:', $activity['total_attendance']]);
        fputcsv($output, ['Male:', $activity['male_attendance']]);
        fputcsv($output, ['Female:', $activity['female_attendance']]);
    } else {
        // Summary export
        fputcsv($output, ['SUMMARY']);
        fputcsv($output, ['Total Activities', $totalActivities]);
        fputcsv($output, ['Total Participants', $totalParticipants]);
        fputcsv($output, ['Total Training Hours', $totalHours]);
        fputcsv($output, []);
        
        fputcsv($output, ['Date', 'Title', 'Venue', 'Hours', 'Participants', 'Male', 'Female']);
        foreach ($activities as $act) {
            fputcsv($output, [
                date('Y-m-d', strtotime($act['activity_date'])),
                $act['title'],
                $act['venue'],
                $act['training_hours'],
                $act['total_attendance'],
                $act['male_attendance'],
                $act['female_attendance']
            ]);
        }
    }
    
    fclose($output);
    exit;
}

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

<div class="min-h-screen bg-gradient-to-br from-green-50 via-white to-emerald-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-graduation-cap text-secretariat mr-3"></i>
                        Training/Seminar Report
                    </h1>
                    <p class="text-gray-600 mt-2">Comprehensive training and seminar data</p>
                </div>
                <div class="flex gap-3">
                    <a href="?year=<?php echo $year; ?>&activity_id=<?php echo $activity_id; ?>&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-3 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, Y', strtotime($act['activity_date'])); ?>
                            </option>
                        <?php endforeach; ?>
                    </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 Detailed 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">TRAINING/SEMINAR REPORT</h3>
                    <p class="text-lg text-gray-700 mt-2"><?php echo htmlspecialchars($activity['title']); ?></p>
                </div>

                <!-- Activity Details -->
                <div class="grid grid-cols-1 md:grid-cols-2 gap-6 mb-8">
                    <div>
                        <h4 class="font-semibold text-gray-700 mb-2">Activity Information</h4>
                        <div class="space-y-2 text-sm">
                            <div class="flex justify-between">
                                <span class="text-gray-600">Date:</span>
                                <span class="font-semibold"><?php echo date('F d, Y', strtotime($activity['activity_date'])); ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Time:</span>
                                <span class="font-semibold"><?php echo date('g:i A', strtotime($activity['start_time'])); ?> - <?php echo date('g:i A', strtotime($activity['end_time'])); ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Venue:</span>
                                <span class="font-semibold"><?php echo htmlspecialchars($activity['venue']); ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Training Hours:</span>
                                <span class="font-semibold"><?php echo $activity['training_hours']; ?> hours</span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Category:</span>
                                <span class="font-semibold"><?php echo $activity['category']; ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Level:</span>
                                <span class="font-semibold"><?php echo $activity['level']; ?></span>
                            </div>
                        </div>
                    </div>
                    <div>
                        <h4 class="font-semibold text-gray-700 mb-2">Participation Statistics</h4>
                        <div class="space-y-2 text-sm">
                            <div class="flex justify-between">
                                <span class="text-gray-600">Total Registrations:</span>
                                <span class="font-semibold"><?php echo $activity['total_registrations']; ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Approved:</span>
                                <span class="font-semibold"><?php echo $activity['approved_registrations']; ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Actual Attendance:</span>
                                <span class="font-semibold"><?php echo $activity['total_attendance']; ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Male:</span>
                                <span class="font-semibold"><?php echo $activity['male_attendance']; ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Female:</span>
                                <span class="font-semibold"><?php echo $activity['female_attendance']; ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Target Participants:</span>
                                <span class="font-semibold"><?php echo $activity['target_participants']; ?></span>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Test Results -->
                <?php if ($pretest_data['total_takers'] > 0 || $posttest_data['total_takers'] > 0): ?>
                    <div class="mb-8">
                        <h4 class="font-semibold text-gray-700 mb-4">Assessment Results</h4>
                        <div class="grid grid-cols-1 md:grid-cols-3 gap-4">
                            <div class="bg-blue-50 rounded-lg p-4">
                                <div class="text-sm text-gray-600 mb-1">Pre-Test Average</div>
                                <div class="text-2xl font-bold text-blue-600"><?php echo $pretest_data['avg_score'] ? number_format($pretest_data['avg_score'], 1) . '%' : 'N/A'; ?></div>
                                <div class="text-xs text-gray-500"><?php echo $pretest_data['total_takers']; ?> takers</div>
                            </div>
                            <div class="bg-green-50 rounded-lg p-4">
                                <div class="text-sm text-gray-600 mb-1">Post-Test Average</div>
                                <div class="text-2xl font-bold text-green-600"><?php echo $posttest_data['avg_score'] ? number_format($posttest_data['avg_score'], 1) . '%' : 'N/A'; ?></div>
                                <div class="text-xs text-gray-500"><?php echo $posttest_data['total_takers']; ?> takers</div>
                            </div>
                            <div class="bg-purple-50 rounded-lg p-4">
                                <div class="text-sm text-gray-600 mb-1">Improvement</div>
                                <div class="text-2xl font-bold text-purple-600">
                                    <?php 
                                    if ($pretest_data['avg_score'] && $posttest_data['avg_score']) {
                                        $improvement = $posttest_data['avg_score'] - $pretest_data['avg_score'];
                                        echo ($improvement >= 0 ? '+' : '') . number_format($improvement, 1) . '%';
                                    } else {
                                        echo 'N/A';
                                    }
                                    ?>
                                </div>
                                <div class="text-xs text-gray-500">Score change</div>
                            </div>
                        </div>
                    </div>
                <?php endif; ?>

                <!-- Attendees by Category -->
                <?php if (!empty($attendees_by_category)): ?>
                    <div class="mb-8">
                        <h4 class="font-semibold text-gray-700 mb-4">Attendees by Employee Category</h4>
                        <div class="grid grid-cols-1 md:grid-cols-3 gap-4">
                            <?php foreach ($attendees_by_category as $category => $count): ?>
                                <div class="border border-gray-200 rounded-lg p-4">
                                    <div class="text-sm text-gray-600"><?php echo $category; ?></div>
                                    <div class="text-2xl font-bold text-gray-900"><?php echo $count; ?></div>
                                </div>
                            <?php endforeach; ?>
                        </div>
                    </div>
                <?php endif; ?>

                <!-- Evaluation Rating -->
                <?php if ($activity['avg_rating']): ?>
                    <div class="mb-8">
                        <h4 class="font-semibold text-gray-700 mb-4">Evaluation Rating</h4>
                        <div class="bg-yellow-50 rounded-lg p-4 inline-block">
                            <div class="text-sm text-gray-600 mb-1">Average Rating</div>
                            <div class="text-3xl font-bold text-yellow-600">
                                <?php echo number_format($activity['avg_rating'], 1); ?> / 5.0
                                <span class="text-lg">⭐</span>
                            </div>
                        </div>
                    </div>
                <?php endif; ?>
            </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">TRAINING AND SEMINAR 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 $totalParticipants; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Total Participants</div>
                    </div>
                    <div class="bg-purple-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-purple-600"><?php echo number_format($totalHours, 1); ?></div>
                        <div class="text-sm text-gray-600 mt-1">Training Hours</div>
                    </div>
                    <div class="bg-pink-50 rounded-lg p-4 text-center">
                        <div class="text-3xl font-bold text-pink-600"><?php echo $totalActivities > 0 ? number_format($totalParticipants / $totalActivities, 0) : 0; ?></div>
                        <div class="text-sm text-gray-600 mt-1">Avg per Activity</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 Title</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">Hours</th>
                                <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Participants</th>
                            </tr>
                        </thead>
                        <tbody class="bg-white divide-y divide-gray-200">
                            <?php if (empty($activities)): ?>
                                <tr>
                                    <td colspan="5" 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>
                                            <div class="text-xs text-gray-500"><?php echo $act['category']; ?></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['training_hours']; ?> hrs
                                        </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">M: <?php echo $act['male_attendance']; ?> | F: <?php echo $act['female_attendance']; ?></div>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        <?php endif; ?>

    </div>
</div>

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

