<?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 proforma export
    $file_number = $_GET['file_number'];
    
    // Get file details
    $file_query = "SELECT f.*, c.first_name, c.last_name,
                   i.invoice_number, i.issue_date, i.due_date, i.status
                   FROM files f
                   LEFT JOIN customers c ON f.customer = c.customer_id
                   LEFT JOIN invoices i ON f.file_number = i.file_number
                   WHERE f.file_number = ?
                   ORDER BY i.created_at DESC
                   LIMIT 1";
    $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 proforma details
    $query = "SELECT p.*, cr.description as cost_description 
              FROM 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 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('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', '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++;
    
    // Add invoice information if it exists
    if (!empty($file['invoice_number'])) {
        $rowIndex++;
        $sheet->setCellValue('A' . $rowIndex, 'Invoice Information');
        $sheet->mergeCells('A' . $rowIndex . ':F' . $rowIndex);
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true)->setSize(12);
        $sheet->getStyle('A' . $rowIndex)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('E0E0E0');
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, 'Invoice Number:');
        $sheet->setCellValue('B' . $rowIndex, $file['invoice_number']);
        $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, 'Issue Date:');
        $sheet->setCellValue('B' . $rowIndex, date('Y-m-d', strtotime($file['issue_date'])));
        $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, 'Due Date:');
        $sheet->setCellValue('B' . $rowIndex, date('Y-m-d', strtotime($file['due_date'])));
        $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, 'Status:');
        $sheet->setCellValue('B' . $rowIndex, ucfirst($file['status']));
        $sheet->mergeCells('B' . $rowIndex . ':F' . $rowIndex);
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        
        // Color the status for better visibility
        if ($file['status'] == 'paid') {
            $sheet->getStyle('B' . $rowIndex)->getFont()->getColor()->setARGB('008800');
        } elseif ($file['status'] == 'pending') {
            $sheet->getStyle('B' . $rowIndex)->getFont()->getColor()->setARGB('CC8800');
        } elseif ($file['status'] == 'cancelled') {
            $sheet->getStyle('B' . $rowIndex)->getFont()->getColor()->setARGB('CC0000');
        }
        
        $rowIndex++;
    }
    
    $rowIndex += 1;
    
    // 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 = '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 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_invoice_status = isset($_GET['invoice_status']) ? $_GET['invoice_status'] : '';
    $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,
                     (SELECT status FROM invoices WHERE file_number = p.file_number ORDER BY created_at DESC LIMIT 1) as invoice_status
              FROM 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_invoice_status)) {
        if ($filter_invoice_status == 'no_invoice') {
            $query .= " AND NOT EXISTS (SELECT 1 FROM invoices WHERE file_number = p.file_number) ";
        } else {
            $query .= " AND (SELECT status FROM invoices WHERE file_number = p.file_number ORDER BY created_at DESC LIMIT 1) = ? ";
            $params[] = $filter_invoice_status;
            $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 = [];
    $invoice_status_count = [
        'pending' => 0,
        'paid' => 0,
        'cancelled' => 0,
        'no_invoice' => 0
    ];
    
    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,
                'grand_total' => 0,
                'count' => 0
            ];
        }
        
        $currencies[$row['currency']]['charges'] += $row['total_charges'];
        $currencies[$row['currency']]['vat'] += $row['total_vat'];
        $currencies[$row['currency']]['grand_total'] += $row['total_charges'] + $row['total_vat'];
        $currencies[$row['currency']]['count']++;
        
        if ($row['all_in_amount'] > 0) {
            $currencies[$row['currency']]['all_in'] += $row['all_in_amount'];
        }
        
        // Count invoice statuses
        if (!empty($row['invoice_status'])) {
            $invoice_status_count[$row['invoice_status']]++;
        } else {
            $invoice_status_count['no_invoice']++;
        }
    }
    
    // Create Excel file
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('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);
    $sheet->getColumnDimension('K')->setWidth(15);
    
    // Title and report info
    $sheet->setCellValue('A1', 'PROFORMA TOTALS REPORT');
    $sheet->mergeCells('A1:K1');
    $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:K2');
    $sheet->getStyle('A2')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    
    $rowIndex = 4;
    
    // Filter information
    $sheet->setCellValue('A' . $rowIndex, 'Report Filters');
    $sheet->mergeCells('A' . $rowIndex . ':K' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A' . $rowIndex . ':K' . $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 . ':K' . $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 . ':K' . $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 . ':K' . $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 . ':K' . $rowIndex);
        $rowIndex++;
    }
    
    if (!empty($filter_invoice_status)) {
        $sheet->setCellValue('A' . $rowIndex, 'Invoice Status:');
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $filter_invoice_status == 'no_invoice' ? 'No Invoice' : ucfirst($filter_invoice_status));
        $sheet->mergeCells('B' . $rowIndex . ':K' . $rowIndex);
        $rowIndex++;
    }
    
    $rowIndex += 2;
    
    // Summary Statistics
    $sheet->setCellValue('A' . $rowIndex, 'Summary Statistics');
    $sheet->mergeCells('A' . $rowIndex . ':K' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A' . $rowIndex . ':K' . $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 . ':K' . $rowIndex);
    $rowIndex++;
    
    // Invoice status counts
    $sheet->setCellValue('A' . $rowIndex, 'Pending Invoices:');
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $sheet->setCellValue('B' . $rowIndex, $invoice_status_count['pending']);
    $sheet->mergeCells('B' . $rowIndex . ':K' . $rowIndex);
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'Paid Invoices:');
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $sheet->setCellValue('B' . $rowIndex, $invoice_status_count['paid']);
    $sheet->mergeCells('B' . $rowIndex . ':K' . $rowIndex);
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'Cancelled Invoices:');
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $sheet->setCellValue('B' . $rowIndex, $invoice_status_count['cancelled']);
    $sheet->mergeCells('B' . $rowIndex . ':K' . $rowIndex);
    $rowIndex++;
    
    $sheet->setCellValue('A' . $rowIndex, 'No Invoice:');
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
    $sheet->setCellValue('B' . $rowIndex, $invoice_status_count['no_invoice']);
    $sheet->mergeCells('B' . $rowIndex . ':K' . $rowIndex);
    $rowIndex++;
    
    $rowIndex++;
    
    // Currency totals
    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 . ':K' . $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 . ':K' . $rowIndex);
        $rowIndex++;
        
        $sheet->setCellValue('A' . $rowIndex, "Grand Total ($currency):");
        $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
        $sheet->setCellValue('B' . $rowIndex, $values['grand_total']);
        $sheet->getStyle('B' . $rowIndex)->getNumberFormat()->setFormatCode($currency . ' #,##0.00');
        $sheet->mergeCells('B' . $rowIndex . ':K' . $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 . ':K' . $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 . ':K' . $rowIndex);
        $rowIndex++;
    }
    
    $rowIndex += 2;
    
    // Detailed Data
    $sheet->setCellValue('A' . $rowIndex, 'Proforma Details');
    $sheet->mergeCells('A' . $rowIndex . ':K' . $rowIndex);
    $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true)->setSize(12);
    $sheet->getStyle('A' . $rowIndex . ':K' . $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', 'Invoice Status'];
    $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');
        $sheet->setCellValue('K' . $rowIndex, !empty($row['invoice_status']) ? ucfirst($row['invoice_status']) : 'No Invoice');
        
        // 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);
        }
        
        // Color the invoice status
        if (!empty($row['invoice_status'])) {
            if ($row['invoice_status'] == 'paid') {
                $sheet->getStyle('K' . $rowIndex)->getFont()->getColor()->setARGB('008800');
            } elseif ($row['invoice_status'] == 'pending') {
                $sheet->getStyle('K' . $rowIndex)->getFont()->getColor()->setARGB('CC8800');
            } elseif ($row['invoice_status'] == 'cancelled') {
                $sheet->getStyle('K' . $rowIndex)->getFont()->getColor()->setARGB('CC0000');
            }
        }
        
        // Add borders
        $sheet->getStyle('A' . $rowIndex . ':K' . $rowIndex)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
        
        $rowIndex++;
    }
    
    // Auto filter
    $sheet->setAutoFilter('A' . ($rowIndex - count($summary_data) - 1) . ':K' . ($rowIndex - 1));
    
    // Set filename
    $filename = '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;
}
?>