<?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;
}

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $type = $_GET['type'] ?? 'members';
    $start_date = $_GET['start_date'] ?? date('Y-m-01');
    $end_date = $_GET['end_date'] ?? date('Y-m-t');
    
    $filename = "report_{$type}_{$start_date}_to_{$end_date}.csv";
    
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Cache-Control: no-cache, must-revalidate');
    header('Expires: Sat, 26 Jul 1997 05:00:00 GMT');
    
    $output = fopen('php://output', 'w');
    
    switch ($type) {
        case 'members':
            fputcsv($output, ['ID', 'Name', 'Email', 'Membership Type', 'Status', 'Registration Date']);
            $stmt = $pdo->prepare("
                SELECT id, name, email, membership_type, status, created_at
                FROM members 
                WHERE created_at BETWEEN ? AND ?
                ORDER BY created_at DESC
            ");
            $stmt->execute([$start_date, $end_date]);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                fputcsv($output, [
                    $row['id'],
                    $row['name'],
                    $row['email'],
                    ucfirst($row['membership_type']),
                    ucfirst($row['status']),
                    date('Y-m-d H:i:s', strtotime($row['created_at']))
                ]);
            }
            break;
            
        case 'events':
            fputcsv($output, ['ID', 'Title', 'Date', 'Location', 'Registrations', 'Revenue', 'Status']);
            $stmt = $pdo->prepare("
                SELECT 
                    e.id, e.title, e.date, e.location, 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
                FROM events e
                LEFT JOIN event_registrations er ON e.id = er.event_id
                WHERE e.date BETWEEN ? AND ?
                GROUP BY e.id
                ORDER BY e.date DESC
            ");
            $stmt->execute([$start_date, $end_date]);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                fputcsv($output, [
                    $row['id'],
                    $row['title'],
                    $row['date'],
                    $row['location'],
                    $row['registrations'],
                    '$' . number_format($row['revenue'], 2),
                    ucfirst($row['status'])
                ]);
            }
            break;
            
        case 'finances':
            fputcsv($output, ['ID', 'Description', 'Type', 'Amount', 'Category', 'Date', 'Member']);
            $stmt = $pdo->prepare("
                SELECT 
                    t.id, t.description, t.type, t.amount, t.created_at,
                    m.name as member_name,
                    COALESCE(ec.name, 'General') as category_name
                FROM transactions t
                LEFT JOIN members m ON t.member_id = m.id
                LEFT JOIN expense_categories ec ON t.category_id = ec.id
                WHERE t.created_at BETWEEN ? AND ?
                ORDER BY t.created_at DESC
            ");
            $stmt->execute([$start_date, $end_date]);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                fputcsv($output, [
                    $row['id'],
                    $row['description'],
                    ucfirst($row['type']),
                    '$' . number_format($row['amount'], 2),
                    $row['category_name'],
                    date('Y-m-d H:i:s', strtotime($row['created_at'])),
                    $row['member_name'] ?: 'N/A'
                ]);
            }
            break;
            
        default:
            fputcsv($output, ['Error']);
            fputcsv($output, ['Invalid report type specified']);
    }
    
    fclose($output);
    
} catch (Exception $e) {
    header('Content-Type: text/plain');
    echo "Error generating report: " . $e->getMessage();
}
?>
