<?php
// export_report.php
function exportFinancialReport($conn, $report_type, $date_from, $date_to) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="financial_report_' . $report_type . '_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    switch($report_type) {
        case 'revenue':
            fputcsv($output, ['Origin', 'Destination', 'Total Bookings', 'Total Revenue', 'Average Booking Value']);
            $result = $conn->query("SELECT 
                a1.airport_code AS origin,
                a2.airport_code AS destination,
                COUNT(b.booking_id) AS total_bookings,
                SUM(b.total_amount) AS total_revenue,
                AVG(b.total_amount) AS average_booking_value
            FROM bookings b
            JOIN flights f ON b.flight_id = f.flight_id
            JOIN airports a1 ON f.origin_id = a1.airport_id
            JOIN airports a2 ON f.destination_id = a2.airport_id
            WHERE b.booking_date BETWEEN '$date_from' AND '$date_to'
            GROUP BY a1.airport_code, a2.airport_code");
            break;

        case 'bookings':
            // Add similar structure for other report types
            break;
    }
    
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, $row);
    }
    fclose($output);
    exit;
}

// export_operational_report.php
function exportOperationalReport($conn, $report_type, $date_from, $date_to) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="operational_report_' . $report_type . '_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    switch($report_type) {
        case 'flights':
            fputcsv($output, ['Flight Number', 'Route', 'Total Flights', 'Average Available Seats', 
                             'Completed Flights', 'Cancelled Flights', 'Completion Rate']);
            $result = $conn->query("SELECT 
                f.flight_number,
                CONCAT(a1.airport_code, '-', a2.airport_code) as route,
                COUNT(f.flight_id) AS total_flights,
                AVG(f.available_seats) AS avg_available_seats,
                COUNT(CASE WHEN f.status = 'completed' THEN 1 END) as completed_flights,
                COUNT(CASE WHEN f.status = 'cancelled' THEN 1 END) as cancelled_flights
            FROM flights f
            JOIN airports a1 ON f.origin_id = a1.airport_id
            JOIN airports a2 ON f.destination_id = a2.airport_id
            WHERE f.departure_time BETWEEN '$date_from' AND '$date_to'
            GROUP BY f.flight_number, route");
            break;
    }
    
    while ($row = $result->fetch_assoc()) {
        $row['Completion Rate'] = ($row['completed_flights'] / $row['total_flights']) * 100 . '%';
        fputcsv($output, $row);
    }
    fclose($output);
    exit;
}

// export_cargo_report.php
function exportCargoReport($conn, $report_type, $date_from, $date_to) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="cargo_report_' . $report_type . '_' . date('Y-m-d') . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    switch($report_type) {
        case 'manifest':
            fputcsv($output, ['Booking Number', 'Sender', 'Receiver', 'Cargo Type', 'Weight', 
                             'Dimensions', 'Status', 'Est. Delivery', 'Amount']);
            $result = $conn->query("SELECT 
                cb.booking_number,
                CONCAT(c.first_name, ' ', c.last_name) as sender,
                cb.receiver_name,
                cb.cargo_type,
                cb.weight,
                cb.dimensions,
                cb.booking_status,
                cb.estimated_delivery_date,
                cb.total_amount
            FROM cargo_bookings cb
            JOIN customers c ON cb.sender_customer_id = c.customer_id
            WHERE cb.booking_date BETWEEN '$date_from' AND '$date_to'
            ORDER BY cb.booking_date DESC");
            break;
            
        case 'delivery_status':
            fputcsv($output, ['Booking Number', 'Status Type', 'Location', 'Status Date', 
                             'Notes', 'Updated By']);
            $result = $conn->query("SELECT 
                cb.booking_number,
                ds.status_type,
                ds.location,
                ds.status_date,
                ds.notes,
                u.full_name as updated_by
            FROM delivery_status ds
            JOIN cargo_bookings cb ON ds.booking_id = cb.booking_id
            LEFT JOIN users u ON ds.updated_by = u.user_id
            WHERE ds.status_date BETWEEN '$date_from' AND '$date_to'
            ORDER BY ds.status_date DESC");
            break;
            
        case 'performance':
            fputcsv($output, ['Month', 'Total Bookings', 'Delivered', 'Failed', 'Success Rate', 
                             'Avg Delay (Days)', 'Revenue']);
            $result = $conn->query("SELECT 
                DATE_FORMAT(booking_date, '%Y-%m') as month,
                COUNT(*) as total_bookings,
                COUNT(CASE WHEN booking_status = 'delivered' THEN 1 END) as delivered,
                COUNT(CASE WHEN booking_status = 'failed_delivery' THEN 1 END) as failed,
                AVG(CASE 
                    WHEN booking_status = 'delivered' 
                    THEN DATEDIFF(actual_delivery_date, estimated_delivery_date) 
                END) as avg_delivery_delay,
                SUM(total_amount) as revenue
            FROM cargo_bookings
            WHERE booking_date BETWEEN '$date_from' AND '$date_to'
            GROUP BY DATE_FORMAT(booking_date, '%Y-%m')
            ORDER BY month DESC");
            break;
    }
    
    while ($row = $result->fetch_assoc()) {
        if ($report_type == 'performance') {
            $row['Success Rate'] = ($row['delivered'] / $row['total_bookings']) * 100 . '%';
        }
        fputcsv($output, $row);
    }
    fclose($output);
    exit;
}

// Example usage in a controller file:
if (isset($_GET['report_type']) && isset($_GET['date_from']) && isset($_GET['date_to'])) {
    $report_type = $_GET['report_type'];
    $date_from = $_GET['date_from'];
    $date_to = $_GET['date_to'];
    
    switch($_GET['report']) {
        case 'financial':
            exportFinancialReport($conn, $report_type, $date_from, $date_to);
            break;
        case 'operational':
            exportOperationalReport($conn, $report_type, $date_from, $date_to);
            break;
        case 'cargo':
            exportCargoReport($conn, $report_type, $date_from, $date_to);
            break;
    }
}
?>