<?php
require_once __DIR__ . '/vendor/phpoffice/vendor/autoload.php';
include 'layouts/session.php';
include 'layouts/dbconn.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

function getAgencyEarnings($conn, $date_from, $date_to) {
    // Ticket earnings with currency
    $ticket_sql = "
        SELECT 
            DATE_FORMAT(created_date, '%Y-%m') as month,
            COUNT(*) as ticket_count,
            SUM(agency_fee) as ticket_fees,
            SUM(ticket_cost) as ticket_sales,
            booking_class,
            airline,
            currency,
            status
        FROM ticket_details
        WHERE created_date BETWEEN ? AND ?
        GROUP BY month, booking_class, airline, currency, status
        ORDER BY month DESC, airline, booking_class";
    
    $stmt = $conn->prepare($ticket_sql);
    $stmt->bind_param("ss", $date_from, $date_to);
    $stmt->execute();
    $ticket_earnings = $stmt->get_result();

    // Cargo earnings
    $cargo_sql = "
        SELECT 
            DATE_FORMAT(booking_date, '%Y-%m') as month,
            COUNT(*) as cargo_count,
            SUM(total_amount) as total_revenue,
            SUM(total_amount - paid_amount) as profit_margin,
            cargo_type,
            currency,
            booking_status
        FROM cargo_bookings
        WHERE booking_date BETWEEN ? AND ?
        AND booking_status != 'cancelled'
        GROUP BY month, cargo_type, currency, booking_status
        ORDER BY month DESC";
    
    $stmt = $conn->prepare($cargo_sql);
    $stmt->bind_param("ss", $date_from, $date_to);
    $stmt->execute();
    $cargo_earnings = $stmt->get_result();

    return [
        'tickets' => $ticket_earnings,
        'cargo' => $cargo_earnings
    ];
}

$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : date('Y-m-01');
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : date('Y-m-t');
$results = getAgencyEarnings($conn, $date_from, $date_to);

$spreadsheet = new Spreadsheet();

// Ticket Sales Sheet
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Ticket Sales');

$sheet->setCellValue('A1', 'Agency Earnings Report - Ticket Sales');
$sheet->mergeCells('A1:H1');
$sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

$sheet->setCellValue('A2', 'Period: ' . date('d M Y', strtotime($date_from)) . ' to ' . date('d M Y', strtotime($date_to)));
$sheet->mergeCells('A2:H2');

// Headers
$headers = ['Month', 'Airline', 'Class', 'Status', 'Tickets', 'Sales', 'Agency Fees', 'Currency', 'Fee %'];
$col = 'A';
$row = 4;
foreach ($headers as $header) {
    $sheet->setCellValue($col++ . $row, $header);
}

$sheet->getStyle('A4:I4')->getFont()->setBold(true);
$sheet->getStyle('A4:I4')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E6E6E6');

$row = 5;
$totals = [];

while ($ticket = $results['tickets']->fetch_assoc()) {
    $sheet->setCellValue('A' . $row, date('M Y', strtotime($ticket['month'] . '-01')));
    $sheet->setCellValue('B' . $row, $ticket['airline']);
    $sheet->setCellValue('C' . $row, $ticket['booking_class']);
    $sheet->setCellValue('D' . $row, $ticket['status']);
    $sheet->setCellValue('E' . $row, $ticket['ticket_count']);
    $sheet->setCellValue('F' . $row, $ticket['ticket_sales']);
    $sheet->setCellValue('G' . $row, $ticket['ticket_fees']);
    $sheet->setCellValue('H' . $row, $ticket['currency']);
    $sheet->setCellValue('I' . $row, ($ticket['ticket_fees'] / $ticket['ticket_sales']) * 100);
    
    $currency = $ticket['currency'];
    if (!isset($totals[$currency])) {
        $totals[$currency] = [
            'sales' => 0,
            'fees' => 0,
            'count' => 0
        ];
    }
    $totals[$currency]['sales'] += $ticket['ticket_sales'];
    $totals[$currency]['fees'] += $ticket['ticket_fees'];
    $totals[$currency]['count'] += $ticket['ticket_count'];
    
    $row++;
}

// Add totals
$row += 2;
foreach ($totals as $currency => $total) {
    $sheet->setCellValue('A' . $row, 'Total for ' . $currency);
    $sheet->mergeCells('A' . $row . ':D' . $row);
    $sheet->setCellValue('E' . $row, $total['count']);
    $sheet->setCellValue('F' . $row, $total['sales']);
    $sheet->setCellValue('G' . $row, $total['fees']);
    $sheet->setCellValue('H' . $row, $currency);
    $sheet->setCellValue('I' . $row, ($total['fees'] / $total['sales']) * 100);
    $sheet->getStyle('A' . $row . ':I' . $row)->getFont()->setBold(true);
    $row++;
}

// Format numbers
$sheet->getStyle('F5:G' . ($row-1))->getNumberFormat()->setFormatCode('#,##0.00');
$sheet->getStyle('I5:I' . ($row-1))->getNumberFormat()->setFormatCode('0.00"%"');

// Add Cargo Sheet
$cargoSheet = $spreadsheet->createSheet();
$cargoSheet->setTitle('Cargo Revenue');

$cargoSheet->setCellValue('A1', 'Agency Earnings Report - Cargo Revenue');
$cargoSheet->mergeCells('A1:F1');
$cargoSheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
$cargoSheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

$cargoHeaders = ['Month', 'Cargo Type', 'Status', 'Bookings', 'Revenue', 'Profit Margin', 'Currency'];
$col = 'A';
$row = 4;
foreach ($cargoHeaders as $header) {
    $cargoSheet->setCellValue($col++ . '4', $header);
}

$cargoSheet->getStyle('A4:G4')->getFont()->setBold(true);
$cargoSheet->getStyle('A4:G4')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E6E6E6');

$row = 5;
$cargoTotals = [];

while ($cargo = $results['cargo']->fetch_assoc()) {
    $cargoSheet->setCellValue('A' . $row, date('M Y', strtotime($cargo['month'] . '-01')));
    $cargoSheet->setCellValue('B' . $row, $cargo['cargo_type']);
    $cargoSheet->setCellValue('C' . $row, $cargo['booking_status']);
    $cargoSheet->setCellValue('D' . $row, $cargo['cargo_count']);
    $cargoSheet->setCellValue('E' . $row, $cargo['total_revenue']);
    $cargoSheet->setCellValue('F' . $row, $cargo['profit_margin']);
    $cargoSheet->setCellValue('G' . $row, $cargo['currency']);
    
    $currency = $cargo['currency'];
    if (!isset($cargoTotals[$currency])) {
        $cargoTotals[$currency] = [
            'revenue' => 0,
            'profit' => 0,
            'count' => 0
        ];
    }
    $cargoTotals[$currency]['revenue'] += $cargo['total_revenue'];
    $cargoTotals[$currency]['profit'] += $cargo['profit_margin'];
    $cargoTotals[$currency]['count'] += $cargo['cargo_count'];
    
    $row++;
}

// Format all sheets
foreach ($spreadsheet->getAllSheets() as $sheet) {
    foreach(range('A','I') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    $lastRow = $sheet->getHighestRow();
    $lastCol = $sheet->getHighestColumn();
    $sheet->getStyle('A4:' . $lastCol . $lastRow)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
}

$writer = new Xlsx($spreadsheet);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="agency_earnings_' . date('Y-m-d') . '.xlsx"');
header('Cache-Control: max-age=0');

$writer->save('php://output');
exit;
?>