<?php
session_start();
include 'layouts/dbconn.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Include PhpSpreadsheet
require_once 'vendor/phpoffice/vendor/phpoffice/phpspreadsheet/src/Bootstrap.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;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

// Check if file number is provided for individual export
if (isset($_GET['file_number']) && !empty($_GET['file_number'])) {
    // Individual pre-proforma export
    $file_number = $_GET['file_number'];
    
    // Get file details
    $file_query = "SELECT f.*, c.first_name, c.last_name
                   FROM files f
                   LEFT JOIN customers c ON f.customer = c.customer_id
                   WHERE f.file_number = ?";
    $file_stmt = $conn->prepare($file_query);
    $file_stmt->bind_param('s', $file_number);
    $file_stmt->execute();
    $file_result = $file_stmt->get_result();
    
    if ($file_result->num_rows === 0) {
        die("File not found");
    }
    
    $file = $file_result->fetch_assoc();
    
    // Get pre-proforma details
    $query = "SELECT p.*, cr.description as cost_description 
              FROM pre_proforma p 
              LEFT JOIN cost_reference cr ON p.cost_code = cr.code
              WHERE p.file_number = ?
              ORDER BY p.category, p.created_at";
    $stmt = $conn->prepare($query);
    $stmt->bind_param('s', $file_number);
    $stmt->execute();
    $result = $stmt->get_result();
    
    $charges_data = [];
    $categories = [];
    $category_totals = [];
    
    // Group by category and calculate subtotals
    while ($row = $result->fetch_assoc()) {
        if (!in_array($row['category'], $categories)) {
            $categories[] = $row['category'];
            $category_totals[$row['category']] = [
                'charges' => 0,
                'vat' => 0
            ];
        }
        
        $category_totals[$row['category']]['charges'] += $row['charge'];
        $category_totals[$row['category']]['vat'] += $row['vat_amount'];
        
        $charges_data[] = $row;
    }
    
    // Calculate totals
    $totals_query = "SELECT SUM(charge) as total_charges, 
                            SUM(vat_amount) as total_vat,
                            MAX(CASE WHEN all_in = 'Yes' THEN all_in_amount ELSE 0 END) as all_in_amount
                     FROM pre_proforma 
                     WHERE file_number = ?";
    $totals_stmt = $conn->prepare($totals_query);
    $totals_stmt->bind_param('s', $file_number);
    $totals_stmt->execute();
    $totals_result = $totals_stmt->get_result();
    $totals = $totals_result->fetch_assoc();
    
    // Create Excel file
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Pre-Proforma Details');
    
    // Set column widths
    $sheet->getColumnDimension('A')->setWidth(20);
    $sheet->getColumnDimension('B')->setWidth(15);
    $sheet->getColumnDimension('C')->setWidth(40);
    $sheet->getColumnDimension('D')->setWidth(15);
    $sheet->getColumnDimension('E')->setWidth(15);
    $sheet->getColumnDimension('F')->setWidth(15);
    
    // Title
    $sheet->setCellValue('A1', 'PRE-PROFORMA INVOICE');
    $sheet->mergeCells('A1:F1');
    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
    $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    
    // File Information
    $sheet->setCellValue('A3', 'File Information');
    $sheet->mergeCells('A3:F3');
    $sheet->getStyle('A3')->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A3:F3')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
    
    $rowIndex = 4;
    
    // Add file details
    $sheet->setCellValue('A' . $rowIndex, 'File Number:');
    $sheet->setCellValue('B' . $rowIndex, $file['file_number']);
    $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'Client:');
    $sheet->setCellValue('B' . $rowIndex, $file['first_name'] . ' ' . $file['last_name']);
    $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'Consignee:');
    $sheet->setCellValue('B' . $rowIndex, $file['consignee_name']);
    $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'Destination:');
    $sheet->setCellValue('B' . $rowIndex, $file['destination']);
    $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'Currency:');
    $sheet->setCellValue('B' . $rowIndex, $file['currency']);
    $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'Shipment Type:');
    $sheet->setCellValue('B' . $rowIndex, ($file['local/transit'] == 1) ? 'Local' : 'Transit');
    $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $rowIndex += 2;
    
    // Charges Table Header
    $sheet->setCellValue('A' . $rowIndex, 'Charges Details');
    $sheet->mergeCells('A' . $rowIndex . ':F' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
    $rowIndex += 2;
    
    // Table Headers
    $headers = ['Category', 'Cost Code', 'Description', 'Vatable', 'Charge', 'VAT Amount'];
    $columnIndex = 'A';
    foreach ($headers as $header) {
        $sheet->setCellValue($columnIndex . $rowIndex, $header);
        $sheet->getStyle($columnIndex . $rowIndex)->getFont()->setBold(true);
        $sheet->getStyle($columnIndex . $rowIndex)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle($columnIndex . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('F0F0F0');
        $sheet->getStyle($columnIndex . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $columnIndex++;
    }
    $rowIndex++;
    
    // Data Rows
    $current_category = '';
    foreach ($charges_data as $row) {
        // If we have a new category, print the category header
        if ($current_category != $row['category']) {
            $current_category = $row['category'];
            $sheet->setCellValue('A' . $rowIndex, $row['category']);
            $sheet->mergeCells('A' . $rowIndex . ':F' . $rowIndex);
            $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
            $sheet->getStyle('A' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('F8F8F8');
            $sheet->getStyle('A' . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
            $rowIndex++;
        }
        
        $sheet->setCellValue('A' . $rowIndex, '');
        $sheet->setCellValue('B' . $rowIndex, $row['cost_code']);
        $sheet->setCellValue('C' . $rowIndex, $row['cost_description'] ?: $row['cost_code']);
        $sheet->setCellValue('D' . $rowIndex, $row['vatable']);
        $sheet->setCellValue('E' . $rowIndex, $row['charge']);
        $sheet->setCellValue('F' . $rowIndex, $row['vat_amount']);
        
        // Format currency cells
        $sheet->getStyle('E' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('F' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        
        // Add borders
        $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        
        $rowIndex++;
    }
    
    // Category Subtotals
    foreach ($categories as $category) {
        $charges = $category_totals[$category]['charges'];
        $vat = $category_totals[$category]['vat'];
        
        $sheet->setCellValue('A' . $rowIndex, $category . ' Subtotal:');
        $sheet->mergeCells('A' . $rowIndex . ':D' . $rowIndex);
        $sheet->setCellValue('E' . $rowIndex, $charges);
        $sheet->setCellValue('F' . $rowIndex, $vat);
        
        // Format and style
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->getStyle('A' . $rowIndex)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('E' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('F' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('F0F0F0');
        $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        
        $rowIndex++;
    }
    
    // Grand Total
    $sheet->setCellValue('A' . $rowIndex, 'Grand Total:');
    $sheet->mergeCells('A' . $rowIndex . ':D' . $rowIndex);
    $sheet->setCellValue('E' . $rowIndex, $totals['total_charges']);
    $sheet->setCellValue('F' . $rowIndex, $totals['total_vat']);
    
    // Format and style
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $sheet->getStyle('A' . $rowIndex)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
    $sheet->getStyle('E' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
    $sheet->getStyle('F' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
    $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
    $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    $sheet->getStyle('E' . $rowIndex . ':F' . $rowIndex)->getFont()->setBold(true);
    
    $rowIndex++;
    
    // Total Including VAT
    $sheet->setCellValue('A' . $rowIndex, 'Total Including VAT:');
    $sheet->mergeCells('A' . $rowIndex . ':D' . $rowIndex);
    $sheet->setCellValue('E' . $rowIndex, $totals['total_charges'] + $totals['total_vat']);
    $sheet->mergeCells('E' . $rowIndex . ':F' . $rowIndex);
    
    // Format and style
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $sheet->getStyle('A' . $rowIndex)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
    $sheet->getStyle('E' . $rowIndex)->getNumberFormat()->setFormatCode($file['currency'] . ' #,##0.00');
    $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
    $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
    $sheet->getStyle('E' . $rowIndex)->getFont()->setBold(true);
    
    $rowIndex++;
    
    // All-In Amount if present
    if ($totals['all_in_amount'] > 0) {
        $sheet->setCellValue('A' . $rowIndex, 'All-In Amount:');
        $sheet->mergeCells('A' . $rowIndex . ':D' . $rowIndex);
        $sheet->setCellValue('E' . $rowIndex, $totals['all_in_amount']);
        $sheet->mergeCells('E' . $rowIndex . ':F' . $rowIndex);
        
        // Format and style
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->getStyle('A' . $rowIndex)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
        $sheet->getStyle('E' . $rowIndex)->getNumberFormat()->setFormatCode($file['currency'] . ' #,##0.00');
        $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('D0E0FF');
        $sheet->getStyle('A' . $rowIndex . ':F' . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $sheet->getStyle('E' . $rowIndex)->getFont()->setBold(true);
    }
    
    // Set filename and headers for download
    $filename = 'Pre-Proforma_' . $file_number . '.xlsx';
    
    // 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="' . $filename . '"');
    header('Cache-Control: max-age=0');
    
    // Save to php output
    $writer->save('php://output');
    exit;
    
} else {
    // Bulk export of pre-proforma totals
    
    // Apply filters if provided
    $filter_customer = isset($_GET['customer']) ? intval($_GET['customer']) : 0;
    $filter_date_from = isset($_GET['date_from']) ? $_GET['date_from'] : '';
    $filter_date_to = isset($_GET['date_to']) ? $_GET['date_to'] : '';
    $filter_file_number = isset($_GET['file_number']) ? $_GET['file_number'] : '';
    $filter_destination = isset($_GET['destination']) ? $_GET['destination'] : '';
    
    // Build the query with filters
    $query = "SELECT p.file_number, 
                     f.consignee_name, 
                     f.destination, 
                     f.customer,
                     c.first_name,
                     c.last_name,
                     MIN(p.created_at) as created_date,
                     f.currency,
                     SUM(p.charge) as total_charges,
                     SUM(p.vat_amount) as total_vat,
                     MAX(CASE WHEN p.all_in = 'Yes' THEN p.all_in_amount ELSE 0 END) as all_in_amount
              FROM pre_proforma p
              JOIN files f ON p.file_number = f.file_number
              LEFT JOIN customers c ON f.customer = c.customer_id
              WHERE 1=1 ";
    
    $params = [];
    $param_types = "";
    
    // Add filters to query
    if ($filter_customer > 0) {
        $query .= " AND f.customer = ? ";
        $params[] = $filter_customer;
        $param_types .= "i";
    }
    
    if (!empty($filter_date_from)) {
        $query .= " AND DATE(p.created_at) >= ? ";
        $params[] = $filter_date_from;
        $param_types .= "s";
    }
    
    if (!empty($filter_date_to)) {
        $query .= " AND DATE(p.created_at) <= ? ";
        $params[] = $filter_date_to;
        $param_types .= "s";
    }
    
    if (!empty($filter_file_number)) {
        $query .= " AND p.file_number LIKE ? ";
        $params[] = "%$filter_file_number%";
        $param_types .= "s";
    }
    
    if (!empty($filter_destination)) {
        $query .= " AND f.destination LIKE ? ";
        $params[] = "%$filter_destination%";
        $param_types .= "s";
    }
    
    // Group by file
    $query .= " GROUP BY p.file_number, f.consignee_name, f.destination, f.customer, c.first_name, c.last_name, f.currency";
    
    // Add sorting
    $query .= " ORDER BY p.created_at DESC";
    
    // Prepare and execute the query
    $stmt = $conn->prepare($query);
    if (!empty($params)) {
        $stmt->bind_param($param_types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    
    // Calculate summary data
    $summary_data = [];
    $currencies = [];
    
    while ($row = $result->fetch_assoc()) {
        $summary_data[] = $row;
        
        // Keep track of totals per currency
        if (!isset($currencies[$row['currency']])) {
            $currencies[$row['currency']] = [
                'charges' => 0,
                'vat' => 0,
                'all_in' => 0,
                'count' => 0
            ];
        }
        
        $currencies[$row['currency']]['charges'] += $row['total_charges'];
        $currencies[$row['currency']]['vat'] += $row['total_vat'];
        $currencies[$row['currency']]['count']++;
        
        if ($row['all_in_amount'] > 0) {
            $currencies[$row['currency']]['all_in'] += $row['all_in_amount'];
        }
    }
    
    // Create Excel file
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('Pre-Proforma Totals');
    
    // Set column widths
    $sheet->getColumnDimension('A')->setWidth(20);
    $sheet->getColumnDimension('B')->setWidth(25);
    $sheet->getColumnDimension('C')->setWidth(25);
    $sheet->getColumnDimension('D')->setWidth(20);
    $sheet->getColumnDimension('E')->setWidth(15);
    $sheet->getColumnDimension('F')->setWidth(12);
    $sheet->getColumnDimension('G')->setWidth(15);
    $sheet->getColumnDimension('H')->setWidth(15);
    $sheet->getColumnDimension('I')->setWidth(15);
    $sheet->getColumnDimension('J')->setWidth(15);
    
    // Title and report info
    $sheet->setCellValue('A1', 'PRE-PROFORMA TOTALS REPORT');
    $sheet->mergeCells('A1:J1');
    $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
    $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    
    $sheet->setCellValue('A2', 'Generated on: ' . date('Y-m-d H:i:s'));
    $sheet->mergeCells('A2:J2');
    $sheet->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    
    $rowIndex = 4;
    
    // Filter information
    $sheet->setCellValue('A' . $rowIndex, 'Report Filters');
    $sheet->mergeCells('A' . $rowIndex . ':J' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A' . $rowIndex . ':J' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
    $rowIndex++;
    
    if (!empty($filter_date_from) || !empty($filter_date_to)) {
        $sheet->setCellValue('A' . $rowIndex, 'Date Range:');
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, 
            (!empty($filter_date_from) ? $filter_date_from : 'All') . ' to ' . 
            (!empty($filter_date_to) ? $filter_date_to : 'All')
        );
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
    }
    
    if ($filter_customer > 0) {
        // Get customer name
        $customer_query = "SELECT CONCAT(first_name, ' ', last_name) as name FROM customers WHERE customer_id = ?";
        $customer_stmt = $conn->prepare($customer_query);
        $customer_stmt->bind_param('i', $filter_customer);
        $customer_stmt->execute();
        $customer_result = $customer_stmt->get_result();
        $customer_name = ($customer_result->fetch_assoc())['name'];
        
        $sheet->setCellValue('A' . $rowIndex, 'Customer:');
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $customer_name);
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
    }
    
    if (!empty($filter_file_number)) {
        $sheet->setCellValue('A' . $rowIndex, 'File Number:');
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $filter_file_number);
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
    }
    
    if (!empty($filter_destination)) {
        $sheet->setCellValue('A' . $rowIndex, 'Destination:');
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $filter_destination);
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
    }
    
    $rowIndex += 2;
    
    // Summary Statistics
    $sheet->setCellValue('A' . $rowIndex, 'Summary Statistics');
    $sheet->mergeCells('A' . $rowIndex . ':J' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A' . $rowIndex . ':J' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'Total Files:');
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $sheet->setCellValue('B' . $rowIndex, count($summary_data));
    $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
    $rowIndex++;
    
    foreach ($currencies as $currency => $values) {
        $sheet->setCellValue('A' . $rowIndex, "Total Charges ($currency):");
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $values['charges']);
        $sheet->getStyle('B' . $rowIndex)->getNumberFormat()->setFormatCode($currency . ' #,##0.00');
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, "Total VAT ($currency):");
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $values['vat']);
        $sheet->getStyle('B' . $rowIndex)->getNumberFormat()->setFormatCode($currency . ' #,##0.00');
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, "Grand Total ($currency):");
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $values['charges'] + $values['vat']);
        $sheet->getStyle('B' . $rowIndex)->getNumberFormat()->setFormatCode($currency . ' #,##0.00');
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, "Total All-In ($currency):");
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $values['all_in']);
        $sheet->getStyle('B' . $rowIndex)->getNumberFormat()->setFormatCode($currency . ' #,##0.00');
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, "Average Charge per File ($currency):");
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $values['count'] > 0 ? $values['charges'] / $values['count'] : 0);
        $sheet->getStyle('B' . $rowIndex)->getNumberFormat()->setFormatCode($currency . ' #,##0.00');
        $sheet->mergeCells('B' . $rowIndex . ':J' . $rowIndex);
        $rowIndex++;
    }
    
    $rowIndex += 2;
    
    // Detailed Data
    $sheet->setCellValue('A' . $rowIndex, 'Pre-Proforma Details');
    $sheet->mergeCells('A' . $rowIndex . ':J' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A' . $rowIndex . ':J' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
    $rowIndex++;
    
    // Headers
    $headers = ['File Number', 'Client', 'Consignee', 'Destination', 'Date Created', 'Currency', 'Total Charges', 'Total VAT', 'Grand Total', 'All-In Amount'];
    $colIndex = 'A';
    
    foreach ($headers as $header) {
        $sheet->setCellValue($colIndex . $rowIndex, $header);
        $sheet->getStyle($colIndex . $rowIndex)->getFont()->setBold(true);
        $sheet->getStyle($colIndex . $rowIndex)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle($colIndex . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('F0F0F0');
        $sheet->getStyle($colIndex . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        $colIndex++;
    }
    $rowIndex++;
    
    // Data rows
    foreach ($summary_data as $row) {
        $grand_total = $row['total_charges'] + $row['total_vat'];
        $all_in_amount = $row['all_in_amount'];
        
        $sheet->setCellValue('A' . $rowIndex, $row['file_number']);
        $sheet->setCellValue('B' . $rowIndex, $row['first_name'] . ' ' . $row['last_name']);
        $sheet->setCellValue('C' . $rowIndex, $row['consignee_name']);
        $sheet->setCellValue('D' . $rowIndex, $row['destination']);
        $sheet->setCellValue('E' . $rowIndex, date('Y-m-d', strtotime($row['created_date'])));
        $sheet->setCellValue('F' . $rowIndex, $row['currency']);
        $sheet->setCellValue('G' . $rowIndex, $row['total_charges']);
        $sheet->setCellValue('H' . $rowIndex, $row['total_vat']);
        $sheet->setCellValue('I' . $rowIndex, $grand_total);
        $sheet->setCellValue('J' . $rowIndex, $all_in_amount > 0 ? $all_in_amount : 'N/A');
        
        // Format numbers
        $sheet->getStyle('G' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('H' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        $sheet->getStyle('I' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        if ($all_in_amount > 0) {
            $sheet->getStyle('J' . $rowIndex)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
        }
        
        // Add borders
        $sheet->getStyle('A' . $rowIndex . ':J' . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        
        $rowIndex++;
    }
    
    // Auto filter
    $sheet->setAutoFilter('A' . ($rowIndex - count($summary_data) - 1) . ':J' . ($rowIndex - 1));
    
    // Set filename
    $filename = 'Pre-Proforma_Totals_Report_' . date('Y-m-d') . '.xlsx';
    
    // 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="' . $filename . '"');
    header('Cache-Control: max-age=0');
    
    // Save to php output
    $writer->save('php://output');
    exit;
}
?>