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

$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-01');
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-t');
$currency_filter = isset($_GET['currency']) ? $_GET['currency'] : 'all';

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$sheet->setCellValue('A1', 'Ticket Sales Report');
$sheet->mergeCells('A1:L1');
$sheet->getStyle('A1:L1')->getFont()->setBold(true)->setSize(14);
$sheet->getStyle('A1:L1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

$sheet->setCellValue('A2', 'Period: ' . date('d M Y', strtotime($start_date)) . ' to ' . date('d M Y', strtotime($end_date)));
$sheet->mergeCells('A2:L2');

// Headers
$sheet->setCellValue('A4', 'Ticket ID');
$sheet->setCellValue('B4', 'Date');
$sheet->setCellValue('C4', 'Customer Name');
$sheet->setCellValue('D4', 'Ticket Reference');
$sheet->setCellValue('E4', 'Booking Class');
$sheet->setCellValue('F4', 'Airline');
$sheet->setCellValue('G4', 'Route');
$sheet->setCellValue('H4', 'Flight Date');
$sheet->setCellValue('I4', 'Ticket Cost');
$sheet->setCellValue('J4', 'Agency Fee');
$sheet->setCellValue('K4', 'Total Amount');
$sheet->setCellValue('L4', 'Status');

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

$sql = "SELECT td.*, CONCAT(td.salutation, ' ', td.customer_name) as full_name,
        u.full_name as created_by_name
        FROM ticket_details td
        LEFT JOIN users u ON td.created_by = u.user_id
        WHERE td.flight_date BETWEEN ? AND ?";

if ($currency_filter !== 'all') {
    $sql .= " AND td.currency = ?";
}

$stmt = $conn->prepare($sql);
if ($currency_filter !== 'all') {
    $stmt->bind_param("sss", $start_date, $end_date, $currency_filter);
} else {
    $stmt->bind_param("ss", $start_date, $end_date);
}

$stmt->execute();
$result = $stmt->get_result();

$row = 5;
$totals = [];

while ($ticket = $result->fetch_assoc()) {
    $sheet->setCellValue('A' . $row, $ticket['ticket_id']);
    $sheet->setCellValue('B' . $row, date('d M Y', strtotime($ticket['created_date'])));
    $sheet->setCellValue('C' . $row, $ticket['full_name']);
    $sheet->setCellValue('D' . $row, $ticket['ticket_ref']);
    $sheet->setCellValue('E' . $row, $ticket['booking_class']);
    $sheet->setCellValue('F' . $row, $ticket['airline']);
    $sheet->setCellValue('G' . $row, $ticket['flight_route']);
    $sheet->setCellValue('H' . $row, date('d M Y H:i', strtotime($ticket['flight_date'])));
    $sheet->setCellValue('I' . $row, $ticket['currency'] . ' ' . number_format($ticket['ticket_cost'], 2));
    $sheet->setCellValue('J' . $row, $ticket['currency'] . ' ' . number_format($ticket['agency_fee'], 2));
    $sheet->setCellValue('K' . $row, $ticket['currency'] . ' ' . number_format($ticket['total_amount'], 2));
    $sheet->setCellValue('L' . $row, $ticket['status']);

    if (!isset($totals[$ticket['currency']])) {
        $totals[$ticket['currency']] = [
            'ticket_cost' => 0,
            'agency_fee' => 0,
            'total_amount' => 0
        ];
    }
    $totals[$ticket['currency']]['ticket_cost'] += $ticket['ticket_cost'];
    $totals[$ticket['currency']]['agency_fee'] += $ticket['agency_fee'];
    $totals[$ticket['currency']]['total_amount'] += $ticket['total_amount'];

    $row++;
}

$row += 2;

foreach ($totals as $currency => $total) {
    $sheet->setCellValue('A' . $row, 'Totals for ' . $currency);
    $sheet->mergeCells('A' . $row . ':H' . $row);
    $sheet->setCellValue('I' . $row, $currency . ' ' . number_format($total['ticket_cost'], 2));
    $sheet->setCellValue('J' . $row, $currency . ' ' . number_format($total['agency_fee'], 2));
    $sheet->setCellValue('K' . $row, $currency . ' ' . number_format($total['total_amount'], 2));
    $sheet->getStyle('A' . $row . ':L' . $row)->getFont()->setBold(true);
    $row++;
}

foreach(range('A','L') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}

$sheet->getStyle('A4:L'.($row-1))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

$writer = new Xlsx($spreadsheet);

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

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