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

$pageTitle = 'GAR Report - Admin - PSAU GAD';
$db = getDB();

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

// Get activities data for 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 ASC
");
$stmt->execute([$year]);
$activities = $stmt->fetchAll();

// Calculate summary statistics
$totalActivities = count($activities);
$totalBudget = array_sum(array_column($activities, 'budget'));
$totalParticipants = array_sum(array_column($activities, 'total_attendance'));
$totalMale = array_sum(array_column($activities, 'male_attendance'));
$totalFemale = array_sum(array_column($activities, 'female_attendance'));

// Category breakdown
$categoryStats = [];
foreach ($activities as $activity) {
    $cat = $activity['category'];
    if (!isset($categoryStats[$cat])) {
        $categoryStats[$cat] = [
            'count' => 0,
            'budget' => 0,
            'participants' => 0
        ];
    }
    $categoryStats[$cat]['count']++;
    $categoryStats[$cat]['budget'] += $activity['budget'];
    $categoryStats[$cat]['participants'] += $activity['total_attendance'];
}

// Quarter breakdown
$quarterStats = [];
foreach ($activities as $activity) {
    $quarter = $activity['quarter'] ?? 'Unassigned';
    if (!isset($quarterStats[$quarter])) {
        $quarterStats[$quarter] = [
            'count' => 0,
            'budget' => 0,
            'participants' => 0
        ];
    }
    $quarterStats[$quarter]['count']++;
    $quarterStats[$quarter]['budget'] += $activity['budget'];
    $quarterStats[$quarter]['participants'] += $activity['total_attendance'];
}

// Export to Excel
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="GAR_Report_' . $year . '_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    // Header
    fputcsv($output, ['PAMPANGA STATE AGRICULTURAL UNIVERSITY']);
    fputcsv($output, ['GENDER AND DEVELOPMENT ACCOMPLISHMENT REPORT (GAR)']);
    fputcsv($output, ['Year: ' . $year]);
    fputcsv($output, []);
    
    // Summary
    fputcsv($output, ['SUMMARY']);
    fputcsv($output, ['Total Activities', $totalActivities]);
    fputcsv($output, ['Total Budget', '₱' . number_format($totalBudget, 2)]);
    fputcsv($output, ['Total Participants', $totalParticipants]);
    fputcsv($output, ['Male Participants', $totalMale]);
    fputcsv($output, ['Female Participants', $totalFemale]);
    fputcsv($output, []);
    
    // Category Breakdown
    fputcsv($output, ['CATEGORY BREAKDOWN']);
    fputcsv($output, ['Category', 'Activities', 'Budget', 'Participants']);
    foreach ($categoryStats as $category => $stats) {
        fputcsv($output, [
            $category,
            $stats['count'],
            '₱' . number_format($stats['budget'], 2),
            $stats['participants']
        ]);
    }
    fputcsv($output, []);
    
    // Activities List
    fputcsv($output, ['ACTIVITIES LIST']);
    fputcsv($output, ['Date', 'Title', 'Category', 'Quarter', 'Venue', 'Participants', 'Male', 'Female', 'Budget']);
    foreach ($activities as $activity) {
        fputcsv($output, [
            date('Y-m-d', strtotime($activity['activity_date'])),
            $activity['title'],
            $activity['category'],
            $activity['quarter'] ?? 'N/A',
            $activity['venue'],
            $activity['total_attendance'],
            $activity['male_attendance'],
            $activity['female_attendance'],
            '₱' . number_format($activity['budget'], 2)
        ]);
    }
    
    fclose($output);
    exit;
}

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

<div class="min-h-screen bg-gradient-to-br from-blue-50 via-white to-purple-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-file-alt text-admin mr-3"></i>
                        GAR Report
                    </h1>
                    <p class="text-gray-600 mt-2">Gender and Development Accomplishment Report</p>
                </div>
                <div class="flex gap-3">
                    <a href="?year=<?php echo $year; ?>&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 Report
                    </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>

        <!-- Year Filter -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8">
            <form method="GET" class="flex gap-4 items-end">
                <div class="flex-1">
                    <label class="block text-sm font-semibold text-gray-700 mb-2">Select Year</label>
                    <select name="year" class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:ring-2 focus:ring-admin 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>
                <button type="submit" class="bg-admin hover:bg-admin-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>
            </form>
        </div>

        <!-- Report Header (Print) -->
        <div class="bg-white rounded-xl shadow-lg p-8 mb-8 print:shadow-none">
            <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-admin mt-2">GENDER AND DEVELOPMENT ACCOMPLISHMENT REPORT (GAR)</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-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 $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 number_format($totalBudget, 0); ?></div>
                    <div class="text-sm text-gray-600 mt-1">Total Budget</div>
                </div>
                <div class="bg-purple-50 rounded-lg p-4 text-center">
                    <div class="text-3xl font-bold text-purple-600"><?php echo $totalParticipants; ?></div>
                    <div class="text-sm text-gray-600 mt-1">Total Participants</div>
                </div>
                <div class="bg-indigo-50 rounded-lg p-4 text-center">
                    <div class="text-3xl font-bold text-indigo-600"><?php echo $totalMale; ?></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 $totalFemale; ?></div>
                    <div class="text-sm text-gray-600 mt-1">Female</div>
                </div>
            </div>
        </div>

        <!-- Category Breakdown -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8">
            <h2 class="text-xl font-bold text-gray-900 mb-6">
                <i class="fas fa-layer-group text-admin mr-2"></i>
                Accomplishments by Category
            </h2>
            <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">Category</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Activities</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Budget</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Participants</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">% of Total</th>
                        </tr>
                    </thead>
                    <tbody class="bg-white divide-y divide-gray-200">
                        <?php foreach ($categoryStats as $category => $stats): 
                            $percentage = $totalActivities > 0 ? round(($stats['count'] / $totalActivities) * 100) : 0;
                        ?>
                            <tr class="hover:bg-gray-50">
                                <td class="px-6 py-4 font-semibold text-gray-900"><?php echo htmlspecialchars($category); ?></td>
                                <td class="px-6 py-4 text-gray-700"><?php echo $stats['count']; ?></td>
                                <td class="px-6 py-4 text-gray-700">₱<?php echo number_format($stats['budget'], 2); ?></td>
                                <td class="px-6 py-4 text-gray-700"><?php echo $stats['participants']; ?></td>
                                <td class="px-6 py-4">
                                    <div class="flex items-center">
                                        <div class="w-24 bg-gray-200 rounded-full h-2 mr-3">
                                            <div class="bg-admin h-2 rounded-full" style="width: <?php echo $percentage; ?>%"></div>
                                        </div>
                                        <span class="text-sm font-semibold text-gray-700"><?php echo $percentage; ?>%</span>
                                    </div>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>

        <!-- Quarterly Breakdown -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8">
            <h2 class="text-xl font-bold text-gray-900 mb-6">
                <i class="fas fa-calendar-alt text-admin mr-2"></i>
                Quarterly Distribution
            </h2>
            <div class="grid grid-cols-1 md:grid-cols-4 gap-4">
                <?php
                $quarters = ['Q1', 'Q2', 'Q3', 'Q4'];
                foreach ($quarters as $q):
                    $stats = $quarterStats[$q] ?? ['count' => 0, 'budget' => 0, 'participants' => 0];
                ?>
                    <div class="border border-gray-200 rounded-lg p-4">
                        <h3 class="text-lg font-bold text-admin mb-3"><?php echo $q; ?></h3>
                        <div class="space-y-2 text-sm">
                            <div class="flex justify-between">
                                <span class="text-gray-600">Activities:</span>
                                <span class="font-semibold"><?php echo $stats['count']; ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Budget:</span>
                                <span class="font-semibold">₱<?php echo number_format($stats['budget'], 0); ?></span>
                            </div>
                            <div class="flex justify-between">
                                <span class="text-gray-600">Participants:</span>
                                <span class="font-semibold"><?php echo $stats['participants']; ?></span>
                            </div>
                        </div>
                    </div>
                <?php endforeach; ?>
            </div>
        </div>

        <!-- Activities List -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8">
            <h2 class="text-xl font-bold text-gray-900 mb-6">
                <i class="fas fa-list text-admin mr-2"></i>
                Detailed Activities List
            </h2>
            <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">Category</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Quarter</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">Participants</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">Budget</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 $activity): ?>
                                <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($activity['activity_date'])); ?>
                                    </td>
                                    <td class="px-6 py-4">
                                        <div class="text-sm font-semibold text-gray-900"><?php echo htmlspecialchars($activity['title']); ?></div>
                                        <div class="text-xs text-gray-500"><?php echo htmlspecialchars(substr($activity['description'], 0, 60)); ?>...</div>
                                    </td>
                                    <td class="px-6 py-4 whitespace-nowrap">
                                        <span class="px-2 py-1 text-xs font-semibold rounded-full bg-purple-100 text-purple-800">
                                            <?php echo htmlspecialchars($activity['category']); ?>
                                        </span>
                                    </td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-700">
                                        <?php echo $activity['quarter'] ?? 'N/A'; ?>
                                    </td>
                                    <td class="px-6 py-4 text-sm text-gray-700">
                                        <?php echo htmlspecialchars($activity['venue']); ?>
                                    </td>
                                    <td class="px-6 py-4 whitespace-nowrap">
                                        <div class="text-sm">
                                            <div class="font-semibold text-gray-900"><?php echo $activity['total_attendance']; ?> total</div>
                                            <div class="text-xs text-gray-500">
                                                M: <?php echo $activity['male_attendance']; ?> |
                                                F: <?php echo $activity['female_attendance']; ?>
                                            </div>
                                        </div>
                                    </td>
                                    <td class="px-6 py-4 whitespace-nowrap text-sm font-semibold text-gray-900">
                                        ₱<?php echo number_format($activity['budget'], 2); ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                    <tfoot class="bg-gray-50">
                        <tr>
                            <td colspan="5" class="px-6 py-4 text-right font-bold text-gray-900">TOTAL:</td>
                            <td class="px-6 py-4 font-bold text-gray-900">
                                <?php echo $totalParticipants; ?> total<br>
                                <span class="text-xs font-normal text-gray-600">M: <?php echo $totalMale; ?> | F: <?php echo $totalFemale; ?></span>
                            </td>
                            <td class="px-6 py-4 font-bold text-gray-900">
                                ₱<?php echo number_format($totalBudget, 2); ?>
                            </td>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>

        <!-- Report Footer -->
        <div class="bg-white rounded-xl shadow-lg p-6 mb-8 print:block hidden">
            <div class="grid grid-cols-3 gap-8 mt-12">
                <div class="text-center">
                    <div class="border-t-2 border-gray-800 pt-2 mt-16">
                        <p class="font-semibold">Prepared by:</p>
                        <p class="text-sm text-gray-600">GAD Focal Person</p>
                    </div>
                </div>
                <div class="text-center">
                    <div class="border-t-2 border-gray-800 pt-2 mt-16">
                        <p class="font-semibold">Reviewed by:</p>
                        <p class="text-sm text-gray-600">GAD Coordinator</p>
                    </div>
                </div>
                <div class="text-center">
                    <div class="border-t-2 border-gray-800 pt-2 mt-16">
                        <p class="font-semibold">Approved by:</p>
                        <p class="text-sm text-gray-600">University President</p>
                    </div>
                </div>
            </div>
            <div class="text-center mt-8 text-sm text-gray-600">
                <p>Generated on: <?php echo date('F d, Y'); ?></p>
            </div>
        </div>

    </div>
</div>

<style>
@media print {
    .print\:hidden {
        display: none !important;
    }
    .print\:block {
        display: block !important;
    }
    .print\:shadow-none {
        box-shadow: none !important;
    }
    body {
        background: white !important;
    }
}
</style>

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