<?php
require_once '../config/database.php';
require_once '../config/auth_enhanced.php';

if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized access']);
    exit;
}

header('Content-Type: application/json');

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $start_date = $_GET['start_date'] ?? date('Y-m-01');
    $end_date = $_GET['end_date'] ?? date('Y-m-t');
    
    // Fetch event reports data
    $stmt = $pdo->prepare("
        SELECT 
            e.id,
            e.title,
            e.date,
            e.location,
            e.registration_fee,
            e.capacity,
            e.status,
            COUNT(er.id) as registrations,
            COALESCE(SUM(CASE WHEN er.payment_status = 'paid' THEN er.amount_paid ELSE 0 END), 0) as revenue,
            ROUND(COUNT(er.id) / NULLIF(e.capacity, 0) * 100, 2) as capacity_percentage
        FROM events e
        LEFT JOIN event_registrations er ON e.id = er.event_id
        WHERE e.date BETWEEN ? AND ?
        GROUP BY e.id, e.title, e.date, e.location, e.registration_fee, e.capacity, e.status
        ORDER BY e.date DESC
    ");
    $stmt->execute([$start_date, $end_date]);
    $events = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Calculate summary statistics
    $totalEvents = count($events);
    $totalRegistrations = array_sum(array_column($events, 'registrations'));
    $totalRevenue = array_sum(array_column($events, 'revenue'));
    $avgCapacityUsage = $totalEvents > 0 ? round(array_sum(array_column($events, 'capacity_percentage')) / $totalEvents, 2) : 0;
    
    // Format data for DataTables
    $data = [];
    foreach ($events as $event) {
        $data[] = [
            'id' => $event['id'],
            'title' => $event['title'],
            'date' => date('M d, Y', strtotime($event['date'])),
            'location' => $event['location'],
            'registrations' => $event['registrations'],
            'capacity' => $event['capacity'],
            'capacity_usage' => $event['capacity_percentage'] . '%',
            'revenue' => '$' . number_format($event['revenue'], 2),
            'status' => ucfirst($event['status'])
        ];
    }
    
    echo json_encode([
        'data' => $data,
        'summary' => [
            'total_events' => $totalEvents,
            'total_registrations' => $totalRegistrations,
            'total_revenue' => $totalRevenue,
            'avg_capacity_usage' => $avgCapacityUsage
        ]
    ]);
    
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>
