<?php include 'layouts/session.php'; ?>
<?php include 'layouts/main.php'; ?>
<?php include 'layouts/dbconn.php'; ?>

<?php
// Initialize variables
$filter_type = isset($_GET['filter_type']) ? $_GET['filter_type'] : 'all';
$file_number = isset($_GET['file_number']) ? $_GET['file_number'] : '';
$date_from = isset($_GET['date_from']) ? $_GET['date_from'] : '';
$date_to = isset($_GET['date_to']) ? $_GET['date_to'] : '';
$customer_id = isset($_GET['customer_id']) ? $_GET['customer_id'] : '';
$destination = isset($_GET['destination']) ? $_GET['destination'] : '';
$is_export = isset($_GET['export']) && $_GET['export'] == 'true';

// Build query based on filters
$where_clauses = [];
$params = [];
$types = '';

if (!empty($file_number)) {
    $where_clauses[] = "f.file_number LIKE ?";
    $params[] = "%$file_number%";
    $types .= 's';
}

if (!empty($date_from)) {
    $where_clauses[] = "f.created_date >= ?";
    $params[] = $date_from . ' 00:00:00';
    $types .= 's';
}

if (!empty($date_to)) {
    $where_clauses[] = "f.created_date <= ?";
    $params[] = $date_to . ' 23:59:59';
    $types .= 's';
}

if (!empty($customer_id)) {
    $where_clauses[] = "f.customer = ?";
    $params[] = $customer_id;
    $types .= 'i';
}

if (!empty($destination)) {
    $where_clauses[] = "f.destination = ?";
    $params[] = $destination;
    $types .= 's';
}

// Filter by cargo type
if ($filter_type == 'container') {
    $where_clauses[] = "f.c_type = 'Container'";
} elseif ($filter_type == 'loose') {
    $where_clauses[] = "(f.c_type != 'Container' OR f.c_type IS NULL)";
}

// Construct the WHERE clause
$where_sql = !empty($where_clauses) ? 'WHERE ' . implode(' AND ', $where_clauses) : '';

// Get all unique destinations for filter dropdown
$dest_query = "SELECT DISTINCT destination FROM files WHERE destination IS NOT NULL AND destination != '' ORDER BY destination";
$dest_result = $conn->query($dest_query);
$destinations = [];
while ($dest_row = $dest_result->fetch_assoc()) {
    $destinations[] = $dest_row['destination'];
}

// Get all customers for filter dropdown
$customer_query = "SELECT customer_id, first_name, last_name FROM customers ORDER BY first_name";
$customer_result = $conn->query($customer_query);
$customers = [];
while ($cust_row = $customer_result->fetch_assoc()) {
    $customers[] = $cust_row;
}

// Handle PDF export
if ($is_export) {
    require('fpdf/fpdf.php');

    class ManifestPDF extends FPDF {
        function Header() {
            // Logo
            $this->Image('assets/images/logo-rf.png', 10, 10, 50);
            // Company Info
            $this->SetFont('Arial', 'B', 12);
            $this->Cell(0, 10, 'ROYAL FREIGHT LTD', 0, 1, 'R');
            $this->SetFont('Arial', '', 10);
            $this->Cell(0, 6, 'Plot No: 995/149 Off, P.o.Box 4040 Uhuru Street', 0, 1, 'R');
            $this->Cell(0, 6, 'Dar es Salaam, Tanzania', 0, 1, 'R');
            $this->Cell(0, 6, 'Tel: +255 757 200 797', 0, 1, 'R');
            // Title
            $this->Ln(10);
            $this->SetFont('Arial', 'B', 14);
            $this->Cell(0, 10, 'CARGO MANIFEST', 0, 1, 'C');
            $this->Ln(5);
        }

        function Footer() {
            $this->SetY(-15);
            $this->SetFont('Arial', 'I', 8);
            $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
            $this->Cell(0, 10, 'Generated on: ' . date('Y-m-d H:i:s'), 0, 0, 'R');
        }
    }

    // Create PDF
    $pdf = new ManifestPDF();
    $pdf->AliasNbPages();
    $pdf->AddPage('L'); // Landscape orientation
    $pdf->SetFont('Arial', '', 10);

    // Add Filters Section
    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(0, 10, 'Applied Filters:', 0, 1);
    $pdf->SetFont('Arial', '', 9);
    
    $pdf->Cell(50, 6, 'File Number: ', 0, 0);
    $pdf->Cell(100, 6, !empty($file_number) ? $file_number : 'All', 0, 1);
    
    $pdf->Cell(50, 6, 'Date Range: ', 0, 0);
    $pdf->Cell(100, 6, (!empty($date_from) ? $date_from : 'Any') . ' to ' . (!empty($date_to) ? $date_to : 'Any'), 0, 1);
    
    $pdf->Cell(50, 6, 'Cargo Type: ', 0, 0);
    $pdf->Cell(100, 6, ucfirst($filter_type), 0, 1);
    
    $pdf->Cell(50, 6, 'Destination: ', 0, 0);
    $pdf->Cell(100, 6, !empty($destination) ? $destination : 'All', 0, 1);
    
    $pdf->Ln(5);

    // Prepare the main query
    $sql = "SELECT f.*, 
               c.first_name as customer_name,
               COALESCE(
                   (SELECT COUNT(*) FROM container_details WHERE file_number = f.file_number),
                   (SELECT COUNT(*) FROM loose_cargo_items WHERE file_number = f.file_number),
                   0
               ) as items_count
            FROM files f
            LEFT JOIN customers c ON f.customer = c.customer_id
            $where_sql
            ORDER BY f.created_date DESC";

    $stmt = $conn->prepare($sql);
    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }
    $stmt->execute();
    $result = $stmt->get_result();
    
    // Add table headers
    $pdf->SetFont('Arial', 'B', 9);
    $pdf->SetFillColor(230, 230, 230);
    $pdf->Cell(40, 10, 'File Number', 1, 0, 'C', true);
    $pdf->Cell(30, 10, 'Date', 1, 0, 'C', true);
    $pdf->Cell(50, 10, 'Customer', 1, 0, 'C', true);
    $pdf->Cell(30, 10, 'Destination', 1, 0, 'C', true);
    $pdf->Cell(30, 10, 'Cargo Type', 1, 0, 'C', true);
    $pdf->Cell(20, 10, 'Items', 1, 0, 'C', true);
    $pdf->Cell(60, 10, 'Consignee', 1, 0, 'C', true);
    $pdf->Cell(25, 10, 'Status', 1, 1, 'C', true);
    
    $pdf->SetFont('Arial', '', 9);
    
    // Add data rows
    $row_count = 0;
    while ($row = $result->fetch_assoc()) {
        $row_count++;
        // Alternate row colors
        $fill = $row_count % 2 == 0;
        
        $pdf->Cell(40, 8, $row['file_number'], 1, 0, 'L', $fill);
        $pdf->Cell(30, 8, date('Y-m-d', strtotime($row['created_date'])), 1, 0, 'C', $fill);
        $pdf->Cell(50, 8, $row['customer_name'] ?? 'N/A', 1, 0, 'L', $fill);
        $pdf->Cell(30, 8, $row['destination'] ?? 'N/A', 1, 0, 'L', $fill);
        $pdf->Cell(30, 8, $row['c_type'] ?? 'N/A', 1, 0, 'C', $fill);
        $pdf->Cell(20, 8, $row['items_count'], 1, 0, 'C', $fill);
        $pdf->Cell(60, 8, $row['consignee_name'] ?? 'N/A', 1, 0, 'L', $fill);
        $pdf->Cell(25, 8, !empty($row['close_file']) ? 'Closed' : 'Active', 1, 1, 'C', $fill);
    }
    
    // If no records found
    if ($row_count == 0) {
        $pdf->SetFont('Arial', 'I', 10);
        $pdf->Cell(0, 10, 'No records found matching the criteria.', 1, 1, 'C');
    }
    
    // Output the PDF
    $pdf->Output('Cargo_Manifest_' . date('Ymd_His') . '.pdf', 'D');
    exit;
}
?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Cargo Manifest Report')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <!-- DataTables CSS -->
    <link href="https://cdn.datatables.net/1.11.5/css/dataTables.bootstrap5.min.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/responsive/2.2.9/css/responsive.bootstrap.min.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.datatables.net/buttons/2.2.2/css/buttons.dataTables.min.css" rel="stylesheet" type="text/css" />
    <!-- Datepicker CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">
    <!-- Select2 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
    <style>
        .filter-card {
            border-radius: 0.25rem;
            box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
            margin-bottom: 1.5rem;
        }
        .filter-card .card-body {
            padding: 1.25rem;
        }
        .table-container {
            background-color: #fff;
            border-radius: 0.25rem;
            box-shadow: 0 0.125rem 0.25rem rgba(0, 0, 0, 0.075);
            padding: 1.25rem;
        }
        .dataTables_wrapper .dataTables_filter {
            margin-bottom: 1rem;
        }
        .action-buttons {
            margin-bottom: 1rem;
        }
        .select2-container .select2-selection--single {
            height: 38px !important;
            padding: 0.375rem 0.75rem;
        }
        .select2-container--default .select2-selection--single .select2-selection__arrow {
            height: 36px !important;
        }
        .select2-container--default .select2-selection--single .select2-selection__rendered {
            line-height: 24px !important;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Reports', 'title' => 'Cargo Manifest Report')); ?>
                    
                    <!-- Filters Section -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card filter-card">
                                <div class="card-header">
                                    <h4 class="card-title">Filter Options</h4>
                                </div>
                                <div class="card-body">
                                    <form method="GET" action="" id="filterForm">
                                        <div class="row">
                                            <div class="col-md-3">
                                                <div class="mb-3">
                                                    <label for="file_number" class="form-label">File Number</label>
                                                    <input type="text" class="form-control" id="file_number" name="file_number" value="<?php echo htmlspecialchars($file_number); ?>">
                                                </div>
                                            </div>
                                            <div class="col-md-3">
                                                <div class="mb-3">
                                                    <label for="date_from" class="form-label">Date From</label>
                                                    <input type="text" class="form-control datepicker" id="date_from" name="date_from" value="<?php echo htmlspecialchars($date_from); ?>">
                                                </div>
                                            </div>
                                            <div class="col-md-3">
                                                <div class="mb-3">
                                                    <label for="date_to" class="form-label">Date To</label>
                                                    <input type="text" class="form-control datepicker" id="date_to" name="date_to" value="<?php echo htmlspecialchars($date_to); ?>">
                                                </div>
                                            </div>
                                            <div class="col-md-3">
                                                <div class="mb-3">
                                                    <label for="filter_type" class="form-label">Cargo Type</label>
                                                    <select class="form-select" id="filter_type" name="filter_type">
                                                        <option value="all" <?php echo $filter_type == 'all' ? 'selected' : ''; ?>>All</option>
                                                        <option value="container" <?php echo $filter_type == 'container' ? 'selected' : ''; ?>>Container</option>
                                                        <option value="loose" <?php echo $filter_type == 'loose' ? 'selected' : ''; ?>>Loose Cargo</option>
                                                    </select>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="row">
                                            <div class="col-md-6">
                                                <div class="mb-3">
                                                    <label for="customer_id" class="form-label">Customer</label>
                                                    <select class="form-select select2" id="customer_id" name="customer_id">
                                                        <option value="">All Customers</option>
                                                        <?php foreach ($customers as $cust): ?>
                                                            <option value="<?php echo $cust['customer_id']; ?>" <?php echo $customer_id == $cust['customer_id'] ? 'selected' : ''; ?>>
                                                                <?php echo htmlspecialchars($cust['first_name'] . ' ' . $cust['last_name']); ?>
                                                            </option>
                                                        <?php endforeach; ?>
                                                    </select>
                                                </div>
                                            </div>
                                            <div class="col-md-6">
                                                <div class="mb-3">
                                                    <label for="destination" class="form-label">Destination</label>
                                                    <select class="form-select select2" id="destination" name="destination">
                                                        <option value="">All Destinations</option>
                                                        <?php foreach ($destinations as $dest): ?>
                                                            <option value="<?php echo $dest; ?>" <?php echo $destination == $dest ? 'selected' : ''; ?>>
                                                                <?php echo htmlspecialchars($dest); ?>
                                                            </option>
                                                        <?php endforeach; ?>
                                                    </select>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="row mt-2">
                                            <div class="col-12">
                                                <button type="submit" class="btn btn-primary">Apply Filters</button>
                                                <button type="button" class="btn btn-secondary" id="resetFilters">Reset Filters</button>
                                                <button type="button" class="btn btn-success" id="exportPDF">Export to PDF</button>
                                                <button type="button" class="btn btn-info" id="exportExcel">Export to Excel</button>
                                            </div>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Manifest Data Table -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title">Cargo Manifest</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table id="manifestTable" class="table table-bordered dt-responsive nowrap table-striped" style="width:100%">
                                            <thead>
                                                <tr>
                                                    <th>File Number</th>
                                                    <th>Date</th>
                                                    <th>Customer</th>
                                                    <th>Destination</th>
                                                    <th>Cargo Type</th>
                                                    <th>Items</th>
                                                    <th>Consignee</th>
                                                    <th>Status</th>
                                                    <th>Action</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php
                                                // Prepare the main query
                                                $sql = "SELECT f.*, 
                                                           c.first_name as customer_name,
                                                           COALESCE(
                                                               (SELECT COUNT(*) FROM container_details WHERE file_number = f.file_number),
                                                               (SELECT COUNT(*) FROM loose_cargo_items WHERE file_number = f.file_number),
                                                               0
                                                           ) as items_count
                                                        FROM files f
                                                        LEFT JOIN customers c ON f.customer = c.customer_id
                                                        $where_sql
                                                        ORDER BY f.created_date DESC";

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

                                                while ($row = $result->fetch_assoc()):
                                                ?>
                                                <tr>
                                                    <td><?php echo htmlspecialchars($row['file_number']); ?></td>
                                                    <td><?php echo date('Y-m-d', strtotime($row['created_date'])); ?></td>
                                                    <td><?php echo htmlspecialchars($row['customer_name'] ?? 'N/A'); ?></td>
                                                    <td><?php echo htmlspecialchars($row['destination'] ?? 'N/A'); ?></td>
                                                    <td><?php echo htmlspecialchars($row['c_type'] ?? 'N/A'); ?></td>
                                                    <td><?php echo intval($row['items_count']); ?></td>
                                                    <td><?php echo htmlspecialchars($row['consignee_name'] ?? 'N/A'); ?></td>
                                                    <td><?php echo !empty($row['close_file']) ? '<span class="badge bg-success">Closed</span>' : '<span class="badge bg-warning">Active</span>'; ?></td>
                                                    <td>
                                                        <button type="button" class="btn btn-sm btn-info view-details" data-id="<?php echo $row['file_number']; ?>">
                                                            <i class="bx bx-detail"></i> Details
                                                        </button>
                                                    </td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <!-- Manifest Details Modal -->
    <div class="modal fade" id="manifestDetailsModal" tabindex="-1" aria-labelledby="manifestDetailsModalLabel" aria-hidden="true">
        <div class="modal-dialog modal-xl">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="manifestDetailsModalLabel">Cargo Details</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                    <div class="container-fluid">
                        <div class="row">
                            <div class="col-md-6">
                                <h6 class="text-primary">File Information</h6>
                                <table class="table table-sm table-bordered">
                                    <tbody id="fileInfoTable">
                                        <!-- File info will be loaded here -->
                                    </tbody>
                                </table>
                            </div>
                            <div class="col-md-6">
                                <h6 class="text-primary">Customer & Consignee Information</h6>
                                <table class="table table-sm table-bordered">
                                    <tbody id="customerInfoTable">
                                        <!-- Customer info will be loaded here -->
                                    </tbody>
                                </table>
                            </div>
                        </div>
                        <div class="row mt-3">
                            <div class="col-12">
                                <h6 class="text-primary">Cargo Items</h6>
                                <div class="table-responsive">
                                    <table class="table table-sm table-bordered" id="cargoItemsTable">
                                        <!-- Cargo items will be loaded here -->
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                    <button type="button" class="btn btn-primary" id="printDetails">Print Details</button>
                </div>
            </div>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <!-- Required datatable js -->
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.2.2/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.print.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.html5.min.js"></script>
    
    <!-- Datepicker JS -->
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>
    
    <!-- Select2 JS -->
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    
    <!-- SheetJS (XLSX) -->
    <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>
    
    <script src="assets/js/app.js"></script>
    
    <script>
    $(document).ready(function() {
    // Helper function to escape HTML to prevent XSS
    function escapeHtml(text) {
        if (text === null || text === undefined) {
            return '';
        }
        return String(text)
            .replace(/&/g, '&amp;')
            .replace(/</g, '&lt;')
            .replace(/>/g, '&gt;')
            .replace(/"/g, '&quot;')
            .replace(/'/g, '&#039;');
    }

    // Initialize DataTable with advanced features
    var dataTable = $('#manifestTable').DataTable({
        lengthChange: true,
        lengthMenu: [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],
        pageLength: 25,
        responsive: true,
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'copy',
                exportOptions: {
                    columns: ':not(:last-child)'
                }
            },
            {
                extend: 'excel',
                title: 'Royal Freight - Cargo Manifest Report',
                messageTop: 'Generated on: ' + new Date().toLocaleString(),
                exportOptions: {
                    columns: ':not(:last-child)'
                }
            },
            {
                extend: 'pdf',
                title: 'Royal Freight - Cargo Manifest Report',
                messageTop: 'Generated on: ' + new Date().toLocaleString(),
                orientation: 'landscape',
                pageSize: 'A4',
                exportOptions: {
                    columns: ':not(:last-child)'
                }
            },
            {
                extend: 'print',
                title: 'Royal Freight - Cargo Manifest Report',
                messageTop: 'Generated on: ' + new Date().toLocaleString(),
                exportOptions: {
                    columns: ':not(:last-child)'
                }
            }
        ],
        order: [[1, 'desc']], // Sort by date column (index 1) in descending order
        columnDefs: [
            { targets: -1, orderable: false } // Disable sorting for the last column (actions)
        ]
    });
    
    // Initialize Datepickers with improved options
    $(".datepicker").flatpickr({
        dateFormat: "Y-m-d",
        allowInput: true,
        altInput: true,
        altFormat: "F j, Y",
        maxDate: new Date(),
        disableMobile: true,
        showMonths: 1,
        static: true
    });
    
    // Enable date range selection
    $("#date_from, #date_to").on("change", function() {
        var dateFrom = $("#date_from").val();
        var dateTo = $("#date_to").val();
        
        if (dateFrom && dateTo) {
            // Ensure date_to is not before date_from
            if (new Date(dateTo) < new Date(dateFrom)) {
                $("#date_to").val(dateFrom);
            }
        }
    });
    
    // Initialize Select2 with enhanced features
    $(".select2").select2({
        width: '100%',
        placeholder: "Select an option",
        allowClear: true,
        theme: "classic"
    });
    
    // Reset filters button with confirmation
    $("#resetFilters").click(function(e) {
        e.preventDefault();
        
        // Check if filters are active
        var hasActiveFilters = $("#file_number").val() || 
                               $("#date_from").val() || 
                               $("#date_to").val() || 
                               $("#filter_type").val() !== "all" ||
                               $("#customer_id").val() || 
                               $("#destination").val();
        
        if (hasActiveFilters) {
            if (confirm("Are you sure you want to reset all filters?")) {
                window.location.href = window.location.pathname;
            }
        } else {
            alert("No active filters to reset.");
        }
    });
    
    // Export to PDF button
    $("#exportPDF").click(function() {
        // Clone the form and add export parameter
        var form = $("#filterForm").clone();
        form.append('<input type="hidden" name="export" value="true">');
        form.attr('target', '_blank');
        $('body').append(form);
        
        // Submit the form
        form.submit();
        form.remove();
    });
    
    // Export to Excel button with improved formatting
    $("#exportExcel").click(function() {
        try {
            // Create a clean table representation without the action column
            var table = document.getElementById('manifestTable');
            var data = [];
            
            // Add header row
            var headerRow = [];
            for (var i = 0; i < table.tHead.rows[0].cells.length - 1; i++) {
                headerRow.push(table.tHead.rows[0].cells[i].textContent);
            }
            data.push(headerRow);
            
            // Add data rows
            for (var i = 0; i < table.tBodies[0].rows.length; i++) {
                var row = table.tBodies[0].rows[i];
                var rowData = [];
                for (var j = 0; j < row.cells.length - 1; j++) {
                    rowData.push(row.cells[j].textContent.trim());
                }
                data.push(rowData);
            }
            
            // Create workbook and worksheet
            var wb = XLSX.utils.book_new();
            var ws = XLSX.utils.aoa_to_sheet(data);
            
            // Set column widths
            var wscols = headerRow.map(function(h) { 
                return { wch: Math.max(h.length, 15) }; 
            });
            ws['!cols'] = wscols;
            
            // Add title and metadata
            var report_title = "Royal Freight - Cargo Manifest Report";
            var mergeCell = { s: {r: 0, c: 0}, e: {r: 0, c: headerRow.length - 1} };
            
            // Format title row
            ws.A1 = { 
                v: report_title, 
                t: 's',
                s: { 
                    font: { bold: true, sz: 14 },
                    alignment: { horizontal: 'center' }
                }
            };
            
            // Add merge information
            if (!ws['!merges']) ws['!merges'] = [];
            ws['!merges'].push(mergeCell);
            
            // Add the worksheet to the workbook
            XLSX.utils.book_append_sheet(wb, ws, "Cargo Manifest");
            
            // Generate filename with date
            var filename = 'Royal_Freight_Cargo_Manifest_' + new Date().toISOString().split('T')[0] + '.xlsx';
            
            // Write the file and trigger download
            XLSX.writeFile(wb, filename);
            
        } catch (error) {
            console.error("Excel export error:", error);
            alert("An error occurred while generating the Excel file. Please try again.");
        }
    });
    
    // View details button with enhanced modal loading
    $(document).on('click', '.view-details', function() {
        var fileNumber = $(this).data('id');
        var button = $(this);
        
        // Disable button and show loading state
        button.prop('disabled', true).html('<i class="bx bx-loader bx-spin"></i> Loading...');
        
        // Fetch file details via AJAX
        $.ajax({
            url: 'get_manifest_details.php',
            type: 'GET',
            data: {file_number: fileNumber},
            dataType: 'json',
            success: function(response) {
                // Re-enable button
                button.prop('disabled', false).html('<i class="bx bx-detail"></i> Details');
                
                if (response.success) {
                    var data = response.data;
                    
                    // Set modal title with file number
                    $('#manifestDetailsModalLabel').text('Cargo Details - ' + data.file.file_number);
                    
                    // Populate file info with enhanced formatting
                    var fileInfoHtml = '';
                    fileInfoHtml += '<tr><td><strong>File Number</strong></td><td>' + escapeHtml(data.file.file_number) + '</td></tr>';
                    fileInfoHtml += '<tr><td><strong>Created Date</strong></td><td>' + escapeHtml(data.file.created_date) + '</td></tr>';
                    fileInfoHtml += '<tr><td><strong>Destination</strong></td><td>' + escapeHtml(data.file.destination || 'N/A') + '</td></tr>';
                    fileInfoHtml += '<tr><td><strong>Cargo Type</strong></td><td>' + escapeHtml(data.file.c_type || 'N/A') + '</td></tr>';
                    fileInfoHtml += '<tr><td><strong>OBL Number</strong></td><td>' + escapeHtml(data.file.obl_no || 'N/A') + '</td></tr>';
                    fileInfoHtml += '<tr><td><strong>ETA</strong></td><td>' + escapeHtml(data.file.eta || 'N/A') + '</td></tr>';
                    fileInfoHtml += '<tr><td><strong>Status</strong></td><td>' + 
                        (data.file.close_file ? 
                        '<span class="badge bg-success">Closed</span>' : 
                        '<span class="badge bg-warning">Active</span>') + 
                        '</td></tr>';
                    
                    $('#fileInfoTable').html(fileInfoHtml);
                    
                    // Populate customer info
                    var customerInfoHtml = '';
                    customerInfoHtml += '<tr><td><strong>Customer</strong></td><td>' + escapeHtml(data.customer.name || 'N/A') + '</td></tr>';
                    customerInfoHtml += '<tr><td><strong>Consignee</strong></td><td>' + escapeHtml(data.file.consignee_name || 'N/A') + '</td></tr>';
                    customerInfoHtml += '<tr><td><strong>Contact</strong></td><td>' + escapeHtml(data.customer.phone_number || 'N/A') + '</td></tr>';
                    customerInfoHtml += '<tr><td><strong>Address</strong></td><td>' + escapeHtml(data.customer.address || 'N/A') + '</td></tr>';
                    customerInfoHtml += '<tr><td><strong>Country</strong></td><td>' + escapeHtml(data.customer.country || 'N/A') + '</td></tr>';
                    
                    $('#customerInfoTable').html(customerInfoHtml);
                    
                    // Populate cargo items
                    var cargoItemsHtml = '';
                    
                    if (data.file.c_type === 'Container' && data.containers && data.containers.length > 0) {
                        cargoItemsHtml += '<thead class="table-light"><tr>' +
                                          '<th>Container Code</th>' +
                                          '<th>Size</th>' +
                                          '<th>Transporter</th>' +
                                          '<th>Driver</th>' +
                                          '<th>Contact</th>' +
                                          '<th>License</th>' +
                                          '<th>Remarks</th>' +
                                          '</tr></thead><tbody>';
                        
                        data.containers.forEach(function(container) {
                            cargoItemsHtml += '<tr>' +
                                '<td>' + escapeHtml(container.container_code || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(container.container_size || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(container.transporter_name || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(container.driver_name || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(container.driver_phone_number || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(container.driver_license || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(container.remarks || '') + '</td>' +
                                '</tr>';
                        });
                        
                        cargoItemsHtml += '</tbody>';
                    } else if (data.loose_items && data.loose_items.length > 0) {
                        cargoItemsHtml += '<thead class="table-light"><tr>' +
                                          '<th>Chassis/Ref Number</th>' +
                                          '<th>Description</th>' +
                                          '<th>CBM</th>' +
                                          '<th>Transporter</th>' +
                                          '<th>Driver</th>' +
                                          '<th>Contact</th>' +
                                          '<th>Remarks</th>' +
                                          '</tr></thead><tbody>';
                        
                        data.loose_items.forEach(function(item) {
                            cargoItemsHtml += '<tr>' +
                                '<td>' + escapeHtml(item.chassis_number || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(item.car_model || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(item.cbm || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(item.transporter || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(item.driver_name || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(item.driver_phone || 'N/A') + '</td>' +
                                '<td>' + escapeHtml(item.remarks || '') + '</td>' +
                                '</tr>';
                        });
                        
                        cargoItemsHtml += '</tbody>';
                    } else {
                        cargoItemsHtml = '<tr><td colspan="7" class="text-center">No cargo items found</td></tr>';
                    }
                    
                    $('#cargoItemsTable').html(cargoItemsHtml);
                    
                    // Show the modal with animation
                    $('#manifestDetailsModal').modal('show');
                } else {
                    // Show error message
                    alert('Failed to load manifest details: ' + response.message);
                }
            },
            error: function(xhr, status, error) {
                // Re-enable button
                button.prop('disabled', false).html('<i class="bx bx-detail"></i> Details');
                
                console.error("AJAX Error:", status, error);
                
                // Show error message
                alert('An error occurred while fetching manifest details. Please try again.');
            }
        });
    });
    
    // Print details with enhanced formatting
    $('#printDetails').click(function() {
        var fileNumber = $('#manifestDetailsModalLabel').text().replace('Cargo Details - ', '');
        
        try {
            var printWindow = window.open('', '_blank');
            printWindow.document.write('<!DOCTYPE html>');
            printWindow.document.write('<html><head><title>Cargo Manifest Details - ' + fileNumber + '</title>');
            
            // Include stylesheets
            printWindow.document.write('<link href="assets/css/bootstrap.min.css" rel="stylesheet" type="text/css" />');
            printWindow.document.write('<style>');
            printWindow.document.write(`
                body { 
                    padding: 20px; 
                    font-family: Arial, sans-serif;
                    font-size: 12pt;
                }
                .print-header {
                    display: flex;
                    justify-content: space-between;
                    align-items: center;
                    margin-bottom: 30px;
                    border-bottom: 2px solid #000;
                    padding-bottom: 10px;
                }
                .company-info {
                    text-align: right;
                }
                .company-info h2 {
                    margin: 0;
                    font-size: 24pt;
                    font-weight: bold;
                }
                .company-info p {
                    margin: 0;
                    font-size: 10pt;
                }
                h4 {
                    color: #007bff;
                    border-bottom: 1px solid #dee2e6;
                    padding-bottom: 5px;
                    font-weight: bold;
                }
                table {
                    width: 100%;
                    border-collapse: collapse;
                    margin-bottom: 20px;
                }
                th, td {
                    border: 1px solid #dee2e6;
                    padding: 8px;
                }
                th {
                    background-color: #f8f9fa;
                    font-weight: bold;
                }
                tr:nth-child(even) {
                    background-color: #f8f9fa;
                }
                .info-table td:first-child {
                    width: 30%;
                    font-weight: bold;
                    background-color: #f8f9fa;
                }
                .badge {
                    padding: 5px 10px;
                    border-radius: 4px;
                    font-size: 85%;
                    font-weight: 700;
                }
                .bg-success {
                    background-color: #28a745 !important;
                    color: white;
                }
                .bg-warning {
                    background-color: #ffc107 !important;
                    color: #212529;
                }
                .page-footer {
                    margin-top: 30px;
                    border-top: 1px solid #dee2e6;
                    padding-top: 10px;
                    font-size: 10pt;
                    text-align: center;
                }
                @media print {
                    @page {
                        size: portrait;
                        margin: 1cm;
                    }
                }
            `);
            printWindow.document.write('</style>');
            printWindow.document.write('</head><body>');
            
            // Add header with logo and company information
            printWindow.document.write('<div class="print-header">');
            printWindow.document.write('<img src="assets/images/logo-rf.png" height="70">');
            printWindow.document.write('<div class="company-info">');
            printWindow.document.write('<h2>CARGO MANIFEST DETAILS</h2>');
            printWindow.document.write('<p>Royal Freight Ltd</p>');
            printWindow.document.write('<p>Plot No: 995/149 Off, P.o.Box 4040 Uhuru Street</p>');
            printWindow.document.write('<p>Dar es Salaam, Tanzania</p>');
            printWindow.document.write('<p>Tel: +255 757 200 797</p>');
            printWindow.document.write('</div>');
            printWindow.document.write('</div>');
            
            // Add information about the generated report
            printWindow.document.write('<div class="mb-4 text-right">');
            printWindow.document.write('<p><strong>File Number:</strong> ' + fileNumber + '</p>');
            printWindow.document.write('<p><strong>Generated On:</strong> ' + new Date().toLocaleString() + '</p>');
            printWindow.document.write('</div>');
            
            // Copy content from modal
            var fileInfo = document.getElementById('fileInfoTable').outerHTML;
            var customerInfo = document.getElementById('customerInfoTable').outerHTML;
            var cargoItems = document.getElementById('cargoItemsTable').outerHTML;
            
            // Add the formatted content sections
            printWindow.document.write('<div class="row">');
            printWindow.document.write('<div class="col-md-6">');
            printWindow.document.write('<h4>File Information</h4>');
            printWindow.document.write('<table class="info-table">' + fileInfo + '</table>');
            printWindow.document.write('</div>');
            printWindow.document.write('<div class="col-md-6">');
            printWindow.document.write('<h4>Customer & Consignee Information</h4>');
            printWindow.document.write('<table class="info-table">' + customerInfo + '</table>');
            printWindow.document.write('</div>');
            printWindow.document.write('</div>');
            
            printWindow.document.write('<h4>Cargo Items</h4>');
            printWindow.document.write('<div class="table-responsive">' + cargoItems + '</div>');
            
            // Add footer
            printWindow.document.write('<div class="page-footer">');
            printWindow.document.write('<p>Royal Freight Ltd | Plot No: 995/149 Off, P.o.Box 4040 Uhuru Street, Dar es Salaam, Tanzania</p>');
            printWindow.document.write('<p>Tel: +255 757 200 797 | Email: info@royalfreight.co.tz</p>');
            printWindow.document.write('<p>© ' + new Date().getFullYear() + ' Royal Freight Ltd. All rights reserved.</p>');
            printWindow.document.write('</div>');
            
            printWindow.document.write('</body></html>');
            printWindow.document.close();
            printWindow.focus();
            
            // Wait a moment for resources to load, then print
            setTimeout(function() {
                printWindow.print();
            }, 1000);
            
        } catch (error) {
            console.error("Print error:", error);
            alert("An error occurred while preparing the print view. Please try again.");
        }
        
        return true;
    });
    
    // Add event listeners for table search box styling
    $('.dataTables_filter input').addClass('form-control form-control-sm');
    $('.dataTables_length select').addClass('form-select form-select-sm');
    
    // Enhance table pagination controls
    $('.dataTables_paginate .paginate_button').addClass('btn btn-sm');
});
    </script>
</body>
</html>