<?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 date filters
$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';

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

// Set the header row
$sheet->setCellValue('A1', 'Cargo Bookings Report');
$sheet->mergeCells('A1:I1');
$sheet->getStyle('A1:I1')->getFont()->setBold(true)->setSize(14);
$sheet->getStyle('A1:I1')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

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

// Column headers
$sheet->setCellValue('A4', 'Booking No');
$sheet->setCellValue('B4', 'Date');
$sheet->setCellValue('C4', 'Sender');
$sheet->setCellValue('D4', 'Receiver');
$sheet->setCellValue('E4', 'Type');
$sheet->setCellValue('F4', 'Amount');
$sheet->setCellValue('G4', 'Paid');
$sheet->setCellValue('H4', 'Agency Fee');
$sheet->setCellValue('I4', 'Status');

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

// Fetch data
$sql = "SELECT 
            cb.*,
            CONCAT(sender.first_name, ' ', sender.last_name) as sender_name,
            CONCAT(receiver.first_name, ' ', receiver.last_name) as receiver_name
        FROM cargo_bookings cb
        LEFT JOIN customers sender ON cb.sender_customer_id = sender.customer_id
        LEFT JOIN customers receiver ON cb.receiver_customer_id = receiver.customer_id
        WHERE cb.booking_date BETWEEN ? AND ?";

if ($currency_filter !== 'all') {
    $sql .= " AND cb.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();

// Initialize row counter
$row = 5;

// Currency totals
$totals = [];

// Add data rows
while ($booking = $result->fetch_assoc()) {
    $sheet->setCellValue('A' . $row, $booking['booking_number']);
    $sheet->setCellValue('B' . $row, date('d M Y', strtotime($booking['booking_date'])));
    $sheet->setCellValue('C' . $row, $booking['sender_name']);
    $sheet->setCellValue('D' . $row, $booking['receiver_customer_id'] ? $booking['receiver_name'] : $booking['receiver_name']);
    $sheet->setCellValue('E' . $row, $booking['cargo_type']);
    $sheet->setCellValue('F' . $row, $booking['currency'] . ' ' . number_format($booking['total_amount'], 2));
    $sheet->setCellValue('G' . $row, $booking['currency'] . ' ' . number_format($booking['paid_amount'], 2));
    $sheet->setCellValue('H' . $row, $booking['currency'] . ' ' . number_format($booking['total_amount'] - $booking['paid_amount'], 2));
    $sheet->setCellValue('I' . $row, ucfirst($booking['booking_status']));

    // Track totals by currency
    if (!isset($totals[$booking['currency']])) {
        $totals[$booking['currency']] = [
            'total_amount' => 0,
            'paid_amount' => 0,
            'agency_fee' => 0
        ];
    }
    $totals[$booking['currency']]['total_amount'] += $booking['total_amount'];
    $totals[$booking['currency']]['paid_amount'] += $booking['paid_amount'];
    $totals[$booking['currency']]['agency_fee'] += ($booking['total_amount'] - $booking['paid_amount']);

    $row++;
}

// Add a gap before totals
$row += 2;

// Add totals section
foreach ($totals as $currency => $total) {
    $sheet->setCellValue('A' . $row, 'Totals for ' . $currency);
    $sheet->mergeCells('A' . $row . ':E' . $row);
    $sheet->setCellValue('F' . $row, $currency . ' ' . number_format($total['total_amount'], 2));
    $sheet->setCellValue('G' . $row, $currency . ' ' . number_format($total['paid_amount'], 2));
    $sheet->setCellValue('H' . $row, $currency . ' ' . number_format($total['agency_fee'], 2));
    $sheet->getStyle('A' . $row . ':I' . $row)->getFont()->setBold(true);
    $row++;
}

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

// Set borders
$sheet->getStyle('A4:I'.($row-1))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

// Create writer and output file
$writer = new Xlsx($spreadsheet);

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

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