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

$pageTitle = 'Activity Analytics - Admin - PSAU GAD';
$db = getDB();

// Get date range filter (default: current year)
$year = $_GET['year'] ?? date('Y');
$category = $_GET['category'] ?? 'all';

// Get overall statistics
$stmt = $db->prepare("
    SELECT 
        COUNT(DISTINCT a.activity_id) as total_activities,
        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,
        AVG(a.target_participants) as avg_target,
        SUM(a.budget) as total_budget
    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
    WHERE YEAR(a.activity_date) = ? AND a.is_active = 1
");
$stmt->execute([$year]);
$stats = $stmt->fetch();

// Calculate attendance rate
$attendanceRate = $stats['approved_registrations'] > 0 
    ? round(($stats['total_attendance'] / $stats['approved_registrations']) * 100) 
    : 0;

// Get registration trends by month
$stmt = $db->prepare("
    SELECT 
        MONTH(ar.registration_date) as month,
        COUNT(*) as count
    FROM activity_registrations ar
    JOIN activities a ON ar.activity_id = a.activity_id
    WHERE YEAR(a.activity_date) = ? AND a.is_active = 1
    GROUP BY MONTH(ar.registration_date)
    ORDER BY month
");
$stmt->execute([$year]);
$monthlyRegistrations = $stmt->fetchAll();

// Create array for all 12 months
$registrationsByMonth = array_fill(1, 12, 0);
foreach ($monthlyRegistrations as $row) {
    $registrationsByMonth[$row['month']] = $row['count'];
}

// Get category breakdown
$stmt = $db->prepare("
    SELECT 
        a.category,
        COUNT(DISTINCT a.activity_id) as activity_count,
        COUNT(DISTINCT ar.registration_id) as registration_count,
        COUNT(DISTINCT aa.attendance_id) as attendance_count
    FROM activities a
    LEFT JOIN activity_registrations ar ON a.activity_id = ar.activity_id AND ar.status = 'approved'
    LEFT JOIN activity_attendance aa ON a.activity_id = aa.activity_id
    WHERE YEAR(a.activity_date) = ? AND a.is_active = 1
    GROUP BY a.category
    ORDER BY activity_count DESC
");
$stmt->execute([$year]);
$categoryStats = $stmt->fetchAll();

// Get department participation
$stmt = $db->prepare("
    SELECT 
        ep.department,
        COUNT(DISTINCT ar.registration_id) as registration_count,
        COUNT(DISTINCT aa.attendance_id) as attendance_count
    FROM activity_registrations ar
    JOIN activities a ON ar.activity_id = a.activity_id
    JOIN employee_profiles ep ON ar.user_id = ep.user_id
    LEFT JOIN activity_attendance aa ON ar.registration_id = aa.registration_id
    WHERE YEAR(a.activity_date) = ? AND ar.status = 'approved' AND a.is_active = 1
    GROUP BY ep.department
    ORDER BY registration_count DESC
    LIMIT 10
");
$stmt->execute([$year]);
$departmentStats = $stmt->fetchAll();

// Get top activities by participation
$stmt = $db->prepare("
    SELECT 
        a.title,
        a.activity_date,
        a.category,
        COUNT(DISTINCT ar.registration_id) as registration_count,
        COUNT(DISTINCT aa.attendance_id) as attendance_count,
        a.target_participants
    FROM activities a
    LEFT JOIN activity_registrations ar ON a.activity_id = ar.activity_id AND ar.status = 'approved'
    LEFT JOIN activity_attendance aa ON a.activity_id = aa.activity_id
    WHERE YEAR(a.activity_date) = ? AND a.is_active = 1
    GROUP BY a.activity_id
    ORDER BY registration_count DESC
    LIMIT 10
");
$stmt->execute([$year]);
$topActivities = $stmt->fetchAll();

// Get employee category participation
$stmt = $db->prepare("
    SELECT 
        ep.category,
        COUNT(DISTINCT ar.registration_id) as registration_count
    FROM activity_registrations ar
    JOIN activities a ON ar.activity_id = a.activity_id
    JOIN employee_profiles ep ON ar.user_id = ep.user_id
    WHERE YEAR(a.activity_date) = ? AND ar.status = 'approved' AND a.is_active = 1
    GROUP BY ep.category
    ORDER BY registration_count DESC
");
$stmt->execute([$year]);
$employeeCategoryStats = $stmt->fetchAll();

// Export to Excel
if (isset($_GET['export']) && $_GET['export'] === 'excel') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="activity_analytics_' . $year . '_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    // Overall Statistics
    fputcsv($output, ['Activity Analytics Report - Year ' . $year]);
    fputcsv($output, []);
    fputcsv($output, ['Overall Statistics']);
    fputcsv($output, ['Total Activities', $stats['total_activities']]);
    fputcsv($output, ['Total Registrations', $stats['total_registrations']]);
    fputcsv($output, ['Approved Registrations', $stats['approved_registrations']]);
    fputcsv($output, ['Total Attendance', $stats['total_attendance']]);
    fputcsv($output, ['Attendance Rate', $attendanceRate . '%']);
    fputcsv($output, ['Total Budget', '₱' . number_format($stats['total_budget'], 2)]);
    fputcsv($output, []);
    
    // Category Breakdown
    fputcsv($output, ['Category Breakdown']);
    fputcsv($output, ['Category', 'Activities', 'Registrations', 'Attendance']);
    foreach ($categoryStats as $cat) {
        fputcsv($output, [$cat['category'], $cat['activity_count'], $cat['registration_count'], $cat['attendance_count']]);
    }
    fputcsv($output, []);
    
    // Department Participation
    fputcsv($output, ['Department Participation']);
    fputcsv($output, ['Department', 'Registrations', 'Attendance']);
    foreach ($departmentStats as $dept) {
        fputcsv($output, [$dept['department'], $dept['registration_count'], $dept['attendance_count']]);
    }
    fputcsv($output, []);
    
    // Top Activities
    fputcsv($output, ['Top Activities by Participation']);
    fputcsv($output, ['Activity', 'Date', 'Category', 'Registrations', 'Attendance', 'Target']);
    foreach ($topActivities as $activity) {
        fputcsv($output, [
            $activity['title'],
            date('Y-m-d', strtotime($activity['activity_date'])),
            $activity['category'],
            $activity['registration_count'],
            $activity['attendance_count'],
            $activity['target_participants']
        ]);
    }
    
    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-chart-line text-admin mr-3"></i>
                        Activity Analytics
                    </h1>
                    <p class="text-gray-600 mt-2">Comprehensive insights into GAD activities and participation</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>
                    <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 to Reports
                    </a>
                </div>
            </div>
        </div>

        <!-- Filters -->
        <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">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>
                    Apply Filter
                </button>
            </form>
        </div>

        <!-- Statistics Cards -->
        <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-6 mb-8">
            <!-- Total Activities -->
            <div class="bg-white rounded-xl shadow-lg p-6 card-hover">
                <div class="flex items-center justify-between">
                    <div>
                        <p class="text-gray-500 text-sm font-semibold">Total Activities</p>
                        <h3 class="text-3xl font-bold text-gray-900 mt-2"><?php echo $stats['total_activities']; ?></h3>
                    </div>
                    <div class="bg-blue-100 p-4 rounded-full">
                        <i class="fas fa-calendar-alt text-admin text-2xl"></i>
                    </div>
                </div>
            </div>

            <!-- Total Registrations -->
            <div class="bg-white rounded-xl shadow-lg p-6 card-hover">
                <div class="flex items-center justify-between">
                    <div>
                        <p class="text-gray-500 text-sm font-semibold">Total Registrations</p>
                        <h3 class="text-3xl font-bold text-gray-900 mt-2"><?php echo $stats['approved_registrations']; ?></h3>
                        <p class="text-xs text-gray-500 mt-1">of <?php echo $stats['total_registrations']; ?> total</p>
                    </div>
                    <div class="bg-green-100 p-4 rounded-full">
                        <i class="fas fa-user-check text-green-600 text-2xl"></i>
                    </div>
                </div>
            </div>

            <!-- Total Attendance -->
            <div class="bg-white rounded-xl shadow-lg p-6 card-hover">
                <div class="flex items-center justify-between">
                    <div>
                        <p class="text-gray-500 text-sm font-semibold">Total Attendance</p>
                        <h3 class="text-3xl font-bold text-gray-900 mt-2"><?php echo $stats['total_attendance']; ?></h3>
                    </div>
                    <div class="bg-purple-100 p-4 rounded-full">
                        <i class="fas fa-users text-purple-600 text-2xl"></i>
                    </div>
                </div>
            </div>

            <!-- Attendance Rate -->
            <div class="bg-white rounded-xl shadow-lg p-6 card-hover">
                <div class="flex items-center justify-between">
                    <div>
                        <p class="text-gray-500 text-sm font-semibold">Attendance Rate</p>
                        <h3 class="text-3xl font-bold text-gray-900 mt-2"><?php echo $attendanceRate; ?>%</h3>
                    </div>
                    <div class="bg-yellow-100 p-4 rounded-full">
                        <i class="fas fa-percentage text-yellow-600 text-2xl"></i>
                    </div>
                </div>
            </div>
        </div>

        <!-- Registration Trends Chart -->
        <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-chart-area text-admin mr-2"></i>
                Registration Trends (<?php echo $year; ?>)
            </h2>
            <canvas id="registrationTrendsChart" height="80"></canvas>
        </div>

        <!-- Two Column Layout -->
        <div class="grid grid-cols-1 lg:grid-cols-2 gap-8 mb-8">

            <!-- Category Breakdown -->
            <div class="bg-white rounded-xl shadow-lg p-6">
                <h2 class="text-xl font-bold text-gray-900 mb-6">
                    <i class="fas fa-layer-group text-admin mr-2"></i>
                    Category Breakdown
                </h2>
                <div class="space-y-4">
                    <?php foreach ($categoryStats as $cat):
                        $maxCount = max(array_column($categoryStats, 'activity_count'));
                        $percentage = $maxCount > 0 ? ($cat['activity_count'] / $maxCount) * 100 : 0;
                        $catAttendanceRate = $cat['registration_count'] > 0
                            ? round(($cat['attendance_count'] / $cat['registration_count']) * 100)
                            : 0;
                    ?>
                        <div>
                            <div class="flex items-center justify-between mb-2">
                                <span class="font-semibold text-gray-700"><?php echo htmlspecialchars($cat['category']); ?></span>
                                <span class="text-sm text-gray-600">
                                    <?php echo $cat['activity_count']; ?> activities
                                </span>
                            </div>
                            <div class="w-full bg-gray-200 rounded-full h-3">
                                <div class="bg-gradient-to-r from-admin to-admin-light h-3 rounded-full transition-all duration-500"
                                     style="width: <?php echo $percentage; ?>%"></div>
                            </div>
                            <div class="flex items-center justify-between mt-1">
                                <span class="text-xs text-gray-500"><?php echo $cat['registration_count']; ?> registrations</span>
                                <span class="text-xs font-semibold <?php echo $catAttendanceRate >= 80 ? 'text-green-600' : ($catAttendanceRate >= 50 ? 'text-yellow-600' : 'text-red-600'); ?>">
                                    <?php echo $catAttendanceRate; ?>% attendance
                                </span>
                            </div>
                        </div>
                    <?php endforeach; ?>

                    <?php if (empty($categoryStats)): ?>
                        <div class="text-center py-8 text-gray-500">
                            <i class="fas fa-inbox text-4xl mb-3"></i>
                            <p>No activity data available for <?php echo $year; ?></p>
                        </div>
                    <?php endif; ?>
                </div>
            </div>

            <!-- Employee Category Participation -->
            <div class="bg-white rounded-xl shadow-lg p-6">
                <h2 class="text-xl font-bold text-gray-900 mb-6">
                    <i class="fas fa-users-cog text-admin mr-2"></i>
                    Employee Category Participation
                </h2>
                <canvas id="employeeCategoryChart" height="250"></canvas>
            </div>

        </div>

    </div>
</div>

<script>
// Registration Trends Chart
const registrationCtx = document.getElementById('registrationTrendsChart').getContext('2d');
new Chart(registrationCtx, {
    type: 'line',
    data: {
        labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
            label: 'Registrations',
            data: <?php echo json_encode(array_values($registrationsByMonth)); ?>,
            borderColor: '#3b82f6',
            backgroundColor: 'rgba(59, 130, 246, 0.1)',
            tension: 0.4,
            fill: true,
            pointBackgroundColor: '#3b82f6',
            pointBorderColor: '#fff',
            pointBorderWidth: 2,
            pointRadius: 5,
            pointHoverRadius: 7
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: true,
        plugins: {
            legend: {
                display: false
            },
            tooltip: {
                backgroundColor: 'rgba(0, 0, 0, 0.8)',
                padding: 12
            }
        },
        scales: {
            y: {
                beginAtZero: true,
                ticks: {
                    precision: 0
                }
            }
        }
    }
});

// Employee Category Chart
const employeeCategoryCtx = document.getElementById('employeeCategoryChart').getContext('2d');
new Chart(employeeCategoryCtx, {
    type: 'doughnut',
    data: {
        labels: <?php echo json_encode(array_column($employeeCategoryStats, 'category')); ?>,
        datasets: [{
            data: <?php echo json_encode(array_column($employeeCategoryStats, 'registration_count')); ?>,
            backgroundColor: ['#3b82f6', '#10b981', '#f59e0b', '#ef4444', '#8b5cf6', '#ec4899'],
            borderWidth: 2,
            borderColor: '#fff'
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: true,
        plugins: {
            legend: {
                position: 'bottom',
                labels: {
                    padding: 15,
                    font: {
                        size: 12
                    }
                }
            }
        }
    }
});
</script>

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