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

// Get search parameters
$search = isset($_GET['search']) ? $_GET['search'] : '';
$dateFrom = isset($_GET['date_from']) ? $_GET['date_from'] : '';
$dateTo = isset($_GET['date_to']) ? $_GET['date_to'] : '';

// Build query with proper escaping
$query = "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 1=1";

$params = [];
$types = "";

if (!empty($search)) {
    $query .= " AND (td.customer_name LIKE ? OR td.ticket_ref LIKE ? OR td.flight_route LIKE ? OR td.airline LIKE ?)";
    $searchParam = "%$search%";
    $params = array_merge($params, [$searchParam, $searchParam, $searchParam, $searchParam]);
    $types .= "ssss";
}

if (!empty($dateFrom)) {
    $query .= " AND DATE(td.flight_date) >= ?";
    $params[] = $dateFrom;
    $types .= "s";
}

if (!empty($dateTo)) {
    $query .= " AND DATE(td.flight_date) <= ?";
    $params[] = $dateTo;
    $types .= "s";
}

$query .= " ORDER BY td.created_date DESC";

$stmt = $conn->prepare($query);
if (!empty($params)) {
    $stmt->bind_param($types, ...$params);
}
$stmt->execute();
$result = $stmt->get_result();

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

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

$sheet->setCellValue('A3', 'Ticket Reference');
$sheet->setCellValue('B3', 'Customer Name');
$sheet->setCellValue('C3', 'Route');
$sheet->setCellValue('D3', 'Airline');
$sheet->setCellValue('E3', 'Booking Class');
$sheet->setCellValue('F3', 'Flight Date');
$sheet->setCellValue('G3', 'Ticket Cost');
$sheet->setCellValue('H3', 'Agency Fee');
$sheet->setCellValue('I3', 'Total Amount');
$sheet->setCellValue('J3', 'Currency');
$sheet->setCellValue('K3', 'Status');
$sheet->setCellValue('L3', 'Created By');

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

// Add data
$row = 4;
$totals = [
    'USD' => ['cost' => 0, 'fee' => 0, 'total' => 0],
    'KES' => ['cost' => 0, 'fee' => 0, 'total' => 0]
];

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

    $currency = $ticket['currency'];
    $totals[$currency]['cost'] += $ticket['ticket_cost'];
    $totals[$currency]['fee'] += $ticket['agency_fee'];
    $totals[$currency]['total'] += $ticket['total_amount'];

    $row++;
}

// Add totals
$row += 2;
foreach ($totals as $currency => $amounts) {
    if ($amounts['total'] > 0) {
        $sheet->setCellValue('A' . $row, 'Total for ' . $currency);
        $sheet->mergeCells('A' . $row . ':F' . $row);
        $sheet->setCellValue('G' . $row, $amounts['cost']);
        $sheet->setCellValue('H' . $row, $amounts['fee']);
        $sheet->setCellValue('I' . $row, $amounts['total']);
        $sheet->getStyle('A' . $row . ':I' . $row)->getFont()->setBold(true);
        $row++;
    }
}

// Format currency columns
$lastRow = $row - 1;
$sheet->getStyle('G4:I' . $lastRow)->getNumberFormat()->setFormatCode('#,##0.00');

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

// Add borders
$sheet->getStyle('A3:L' . $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="tickets_report_' . date('Y-m-d') . '.xlsx"');
header('Cache-Control: max-age=0');

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