<?php
session_start(); // Start the session
// Include the database connection file
include 'layouts/dbconn.php';

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

// Query to fetch user details
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT username, user_role, full_name, email FROM users WHERE user_id = $user_id";
$result_user_details = $conn->query($sql_user_details);

if ($result_user_details && $row_user_details = $result_user_details->fetch_assoc()) {
    $username = $row_user_details['username'];
    $user_role = $row_user_details['user_role'];
    $full_name = $row_user_details['full_name'];
    $email = $row_user_details['email'];
} else {
    // Handle the case if user details are not found
}

// Initialize metrics variables
$totalShipments = $activeShipments = $completedShipments = $pendingApprovals = 0;
$totalRevenueUSD = $totalRevenuePrevUSD = 0;
$totalRevenueTZS = $totalRevenuePrevTZS = 0;
$totalAgencyFeesUSD = $totalAgencyFeesTZS = 0;
$containerDeposits = 0;
$upcomingDeliveries = 0;
$deliveryPerformance = 0;
$currentMonth = date('m');
$currentYear = date('Y');
$prevMonth = ($currentMonth == 1) ? 12 : ($currentMonth - 1);
$prevYear = ($currentMonth == 1) ? ($currentYear - 1) : $currentYear;

// Initialize revenue breakdown variables
$preProformaRevenueUSD = $proformaRevenueUSD = $invoiceRevenueUSD = 0;
$preProformaRevenueTZS = $proformaRevenueTZS = $invoiceRevenueTZS = 0;
$preProformaRevenuePrevUSD = $proformaRevenuePrevUSD = $invoiceRevenuePrevUSD = 0;
$preProformaRevenuePrevTZS = $proformaRevenuePrevTZS = $invoiceRevenuePrevTZS = 0;

try {
    // 1. Count Total Shipments (Files)
    $sqlTotalShipments = "SELECT COUNT(*) AS total FROM files";
    $result = $conn->query($sqlTotalShipments);
    if ($result && $row = $result->fetch_assoc()) {
        $totalShipments = $row['total'];
    }
    
    // 2. Count Active Shipments (Files that are not closed)
    $sqlActiveShipments = "SELECT COUNT(*) AS active FROM files WHERE close_file IS NULL OR close_file != 'Yes'";
    $result = $conn->query($sqlActiveShipments);
    if ($result && $row = $result->fetch_assoc()) {
        $activeShipments = $row['active'];
    }
    
    // 3. Count Completed Shipments (Files that are marked as closed)
    $sqlCompletedShipments = "SELECT COUNT(*) AS completed FROM files WHERE close_file = 'Yes'";
    $result = $conn->query($sqlCompletedShipments);
    if ($result && $row = $result->fetch_assoc()) {
        $completedShipments = $row['completed'];
    }
    
    // 4. Count Pending Approvals
    $sqlPendingApprovals = "SELECT COUNT(*) AS pending FROM pending_approvals WHERE approved = 'Pending'";
    $result = $conn->query($sqlPendingApprovals);
    if ($result && $row = $result->fetch_assoc()) {
        $pendingApprovals = $row['pending'];
    }
    
    // 5. Calculate Total Revenue in USD from all relevant tables
    // Check if invoices table has a currency column
    $sqlCheckInvoiceCurrencyExists = "SHOW COLUMNS FROM invoices LIKE 'currency'";
    $resultCurrencyCheck = $conn->query($sqlCheckInvoiceCurrencyExists);
    $hasCurrencyColumn = ($resultCurrencyCheck && $resultCurrencyCheck->num_rows > 0);
    
    // Revenue from pre_proforma table
    $sqlPreProformaRevenueUSD = "SELECT COALESCE(SUM(all_in_amount), 0) as revenue 
                                FROM pre_proforma 
                                WHERE all_in = 'Yes' 
                                AND currency = '$' 
                                AND MONTH(created_at) = $currentMonth 
                                AND YEAR(created_at) = $currentYear";
    $result = $conn->query($sqlPreProformaRevenueUSD);
    if ($result && $row = $result->fetch_assoc()) {
        $preProformaRevenueUSD = $row['revenue'];
    }
    
    // Revenue from proforma table
    $sqlProformaRevenueUSD = "SELECT COALESCE(SUM(all_in_amount), 0) as revenue 
                              FROM proforma 
                              WHERE all_in = 'Yes' 
                              AND currency = '$' 
                              AND MONTH(created_at) = $currentMonth 
                              AND YEAR(created_at) = $currentYear";
    $result = $conn->query($sqlProformaRevenueUSD);
    if ($result && $row = $result->fetch_assoc()) {
        $proformaRevenueUSD = $row['revenue'];
    }
    
    // Revenue from invoices table
    if ($hasCurrencyColumn) {
        $sqlInvoiceRevenueUSD = "SELECT COALESCE(SUM(total_amount), 0) as revenue 
                                FROM invoices 
                                WHERE currency = '$' 
                                AND MONTH(issue_date) = $currentMonth 
                                AND YEAR(issue_date) = $currentYear
                                AND status != 'cancelled'";
    } else {
        // If no currency column, assume all invoices are in USD
        $sqlInvoiceRevenueUSD = "SELECT COALESCE(SUM(total_amount), 0) as revenue 
                                FROM invoices 
                                WHERE MONTH(issue_date) = $currentMonth 
                                AND YEAR(issue_date) = $currentYear
                                AND status != 'cancelled'";
    }
    $result = $conn->query($sqlInvoiceRevenueUSD);
    if ($result && $row = $result->fetch_assoc()) {
        $invoiceRevenueUSD = $row['revenue'];
    }
    
    // Calculate total USD revenue
    $totalRevenueUSD = $preProformaRevenueUSD + $proformaRevenueUSD + $invoiceRevenueUSD;
    
    // 6. Calculate Total Revenue in TZS from all relevant tables
    // Revenue from pre_proforma table
    $sqlPreProformaRevenueTZS = "SELECT COALESCE(SUM(all_in_amount), 0) as revenue 
                                FROM pre_proforma 
                                WHERE all_in = 'Yes' 
                                AND currency = 'Tsh.' 
                                AND MONTH(created_at) = $currentMonth 
                                AND YEAR(created_at) = $currentYear";
    $result = $conn->query($sqlPreProformaRevenueTZS);
    if ($result && $row = $result->fetch_assoc()) {
        $preProformaRevenueTZS = $row['revenue'];
    }
    
    // Revenue from proforma table
    $sqlProformaRevenueTZS = "SELECT COALESCE(SUM(all_in_amount), 0) as revenue 
                              FROM proforma 
                              WHERE all_in = 'Yes' 
                              AND currency = 'Tsh.' 
                              AND MONTH(created_at) = $currentMonth 
                              AND YEAR(created_at) = $currentYear";
    $result = $conn->query($sqlProformaRevenueTZS);
    if ($result && $row = $result->fetch_assoc()) {
        $proformaRevenueTZS = $row['revenue'];
    }
    
    // Revenue from invoices table
    if ($hasCurrencyColumn) {
        $sqlInvoiceRevenueTZS = "SELECT COALESCE(SUM(total_amount), 0) as revenue 
                                FROM invoices 
                                WHERE currency = 'Tsh.' 
                                AND MONTH(issue_date) = $currentMonth 
                                AND YEAR(issue_date) = $currentYear
                                AND status != 'cancelled'";
        $result = $conn->query($sqlInvoiceRevenueTZS);
        if ($result && $row = $result->fetch_assoc()) {
            $invoiceRevenueTZS = $row['revenue'];
        }
    } else {
        // If no currency column, assume all TZS revenue comes from pre_proforma and proforma
        $invoiceRevenueTZS = 0;
    }
    
    // Calculate total TZS revenue
    $totalRevenueTZS = $preProformaRevenueTZS + $proformaRevenueTZS + $invoiceRevenueTZS;
    
    // 7. Calculate Previous Month's Revenue in USD for comparison
    // Pre-proforma
    $sqlPreProformaRevenuePrevUSD = "SELECT COALESCE(SUM(all_in_amount), 0) as revenue 
                                    FROM pre_proforma 
                                    WHERE all_in = 'Yes' 
                                    AND currency = '$' 
                                    AND MONTH(created_at) = $prevMonth 
                                    AND YEAR(created_at) = $prevYear";
    $result = $conn->query($sqlPreProformaRevenuePrevUSD);
    if ($result && $row = $result->fetch_assoc()) {
        $preProformaRevenuePrevUSD = $row['revenue'];
    }
    
    // Proforma
    $sqlProformaRevenuePrevUSD = "SELECT COALESCE(SUM(all_in_amount), 0) as revenue 
                                  FROM proforma 
                                  WHERE all_in = 'Yes' 
                                  AND currency = '$' 
                                  AND MONTH(created_at) = $prevMonth 
                                  AND YEAR(created_at) = $prevYear";
    $result = $conn->query($sqlProformaRevenuePrevUSD);
    if ($result && $row = $result->fetch_assoc()) {
        $proformaRevenuePrevUSD = $row['revenue'];
    }
    
    // Invoices
    if ($hasCurrencyColumn) {
        $sqlInvoiceRevenuePrevUSD = "SELECT COALESCE(SUM(total_amount), 0) as revenue 
                                    FROM invoices 
                                    WHERE currency = '$' 
                                    AND MONTH(issue_date) = $prevMonth 
                                    AND YEAR(issue_date) = $prevYear
                                    AND status != 'cancelled'";
    } else {
        $sqlInvoiceRevenuePrevUSD = "SELECT COALESCE(SUM(total_amount), 0) as revenue 
                                    FROM invoices 
                                    WHERE MONTH(issue_date) = $prevMonth 
                                    AND YEAR(issue_date) = $prevYear
                                    AND status != 'cancelled'";
    }
    $result = $conn->query($sqlInvoiceRevenuePrevUSD);
    if ($result && $row = $result->fetch_assoc()) {
        $invoiceRevenuePrevUSD = $row['revenue'];
    }
    
    // Calculate total previous USD revenue
    $totalRevenuePrevUSD = $preProformaRevenuePrevUSD + $proformaRevenuePrevUSD + $invoiceRevenuePrevUSD;
    
    // 8. Calculate Previous Month's Revenue in TZS for comparison
    // Pre-proforma
    $sqlPreProformaRevenuePrevTZS = "SELECT COALESCE(SUM(all_in_amount), 0) as revenue 
                                    FROM pre_proforma 
                                    WHERE all_in = 'Yes' 
                                    AND currency = 'Tsh.' 
                                    AND MONTH(created_at) = $prevMonth 
                                    AND YEAR(created_at) = $prevYear";
    $result = $conn->query($sqlPreProformaRevenuePrevTZS);
    if ($result && $row = $result->fetch_assoc()) {
        $preProformaRevenuePrevTZS = $row['revenue'];
    }
    
    // Proforma
    $sqlProformaRevenuePrevTZS = "SELECT COALESCE(SUM(all_in_amount), 0) as revenue 
                                  FROM proforma 
                                  WHERE all_in = 'Yes' 
                                  AND currency = 'Tsh.' 
                                  AND MONTH(created_at) = $prevMonth 
                                  AND YEAR(created_at) = $prevYear";
    $result = $conn->query($sqlProformaRevenuePrevTZS);
    if ($result && $row = $result->fetch_assoc()) {
        $proformaRevenuePrevTZS = $row['revenue'];
    }
    
    // Invoices
    if ($hasCurrencyColumn) {
        $sqlInvoiceRevenuePrevTZS = "SELECT COALESCE(SUM(total_amount), 0) as revenue 
                                    FROM invoices 
                                    WHERE currency = 'Tsh.' 
                                    AND MONTH(issue_date) = $prevMonth 
                                    AND YEAR(issue_date) = $prevYear
                                    AND status != 'cancelled'";
        $result = $conn->query($sqlInvoiceRevenuePrevTZS);
        if ($result && $row = $result->fetch_assoc()) {
            $invoiceRevenuePrevTZS = $row['revenue'];
        }
    } else {
        // If no currency column, assume all TZS revenue comes from pre_proforma and proforma
        $invoiceRevenuePrevTZS = 0;
    }
    
    // Calculate total previous TZS revenue
    $totalRevenuePrevTZS = $preProformaRevenuePrevTZS + $proformaRevenuePrevTZS + $invoiceRevenuePrevTZS;
    
    // Calculate revenue change percentages
    if ($totalRevenuePrevUSD > 0) {
        $revenueChangeUSD = (($totalRevenueUSD - $totalRevenuePrevUSD) / $totalRevenuePrevUSD) * 100;
    } else {
        $revenueChangeUSD = 0;
    }
    
    if ($totalRevenuePrevTZS > 0) {
        $revenueChangeTZS = (($totalRevenueTZS - $totalRevenuePrevTZS) / $totalRevenuePrevTZS) * 100;
    } else {
        $revenueChangeTZS = 0;
    }
    
    // 9. Agency Fees (Calculate from Operations Category or specific cost_code)
    // Handle USD and TZS currencies separately
    $sqlAgencyFeesUSD = "SELECT COALESCE(SUM(charge), 0) as agency_fees 
                         FROM pre_proforma 
                         WHERE category = 'Operational' 
                         AND cost_code = '1-101' 
                         AND currency = '$'
                         AND MONTH(created_at) = $currentMonth 
                         AND YEAR(created_at) = $currentYear";
    $result = $conn->query($sqlAgencyFeesUSD);
    $totalAgencyFeesUSD = 0;
    if ($result && $row = $result->fetch_assoc()) {
        $totalAgencyFeesUSD = $row['agency_fees'];
    }
    
    $sqlAgencyFeesTZS = "SELECT COALESCE(SUM(charge), 0) as agency_fees 
                        FROM pre_proforma 
                        WHERE category = 'Operational' 
                        AND cost_code = '1-101' 
                        AND currency = 'Tsh' 
                        AND MONTH(created_at) = $currentMonth 
                        AND YEAR(created_at) = $currentYear";
    $result = $conn->query($sqlAgencyFeesTZS);
    $totalAgencyFeesTZS = 0;
    if ($result && $row = $result->fetch_assoc()) {
        $totalAgencyFeesTZS = $row['agency_fees'];
    }
    
    // 10. Container Deposits
    $sqlContainerDeposits = "SELECT COUNT(*) as deposit_count FROM container_deposit";
    $result = $conn->query($sqlContainerDeposits);
    if ($result && $row = $result->fetch_assoc()) {
        $containerDeposits = $row['deposit_count'];
    }
    
    // 11. Upcoming Deliveries (based on truck assignments with future dates)
    $sqlUpcomingDeliveries = "SELECT COUNT(*) as upcoming FROM truck_details 
                              WHERE dest_date > CURDATE() 
                              AND dest_date IS NOT NULL";
    $result = $conn->query($sqlUpcomingDeliveries);
    if ($result && $row = $result->fetch_assoc()) {
        $upcomingDeliveries = $row['upcoming'];
    }
    
    // 12. Delivery Performance (Percentage of on-time deliveries)
    $sqlDeliveryPerformance = "SELECT 
                               (COUNT(CASE WHEN actual_return_date <= empty_return_date THEN 1 END) * 100.0 / 
                               NULLIF(COUNT(*), 0)) as performance 
                               FROM shipping_line 
                               WHERE actual_return_date IS NOT NULL 
                               AND empty_return_date IS NOT NULL";
    $result = $conn->query($sqlDeliveryPerformance);
    if ($result && $row = $result->fetch_assoc()) {
        $deliveryPerformance = round($row['performance'] ?? 0);
    }
    
    // Get top transporters by number of assignments
    $sqlTopTransporters = "SELECT t.name as name, COUNT(td.id) as assignments 
                          FROM truck_details td 
                          JOIN transporter_info t ON td.transporter_name = t.name 
                          GROUP BY td.transporter_name 
                          ORDER BY assignments DESC 
                          LIMIT 5";
    $resultTopTransporters = $conn->query($sqlTopTransporters);
    $topTransporters = [];
    if ($resultTopTransporters && $resultTopTransporters->num_rows > 0) {
        while ($row = $resultTopTransporters->fetch_assoc()) {
            $topTransporters[] = $row;
        }
    }
    
    // Get most active routes based on files data
    $sqlPopularRoutes = "SELECT destination, COUNT(*) as shipment_count 
                         FROM files 
                         WHERE destination IS NOT NULL 
                         GROUP BY destination 
                         ORDER BY shipment_count DESC 
                         LIMIT 5";
    $resultPopularRoutes = $conn->query($sqlPopularRoutes);
    $popularRoutes = [];
    if ($resultPopularRoutes && $resultPopularRoutes->num_rows > 0) {
        while ($row = $resultPopularRoutes->fetch_assoc()) {
            $popularRoutes[] = $row;
        }
    }
    
    // PAGINATION FOR RECENT FILES (SHIPMENTS)
    $filesPerPage = 5; // Number of files per page
    $fileCurrentPage = isset($_GET['file_page']) ? (int)$_GET['file_page'] : 1;
    $fileSearchTerm = isset($_GET['file_search']) ? $conn->real_escape_string($_GET['file_search']) : '';
    
    // Build the SQL query for files with search and pagination
    $sqlFilesCount = "SELECT COUNT(*) as total FROM files f 
                     LEFT JOIN customers c ON f.customer = c.customer_id";
    
    // Add search condition if search term is provided
    if (!empty($fileSearchTerm)) {
        $sqlFilesCount .= " WHERE f.file_number LIKE '%$fileSearchTerm%' 
                           OR c.first_name LIKE '%$fileSearchTerm%' 
                           OR c.last_name LIKE '%$fileSearchTerm%' 
                           OR f.destination LIKE '%$fileSearchTerm%'
                           OR f.consignee_name LIKE '%$fileSearchTerm%'";
    }
    
    $resultFilesCount = $conn->query($sqlFilesCount);
    $totalFiles = ($resultFilesCount && $row = $resultFilesCount->fetch_assoc()) ? $row['total'] : 0;
    $totalFilePages = ceil($totalFiles / $filesPerPage);
    
    // Ensure current page is within valid range
    if ($fileCurrentPage < 1) $fileCurrentPage = 1;
    if ($fileCurrentPage > $totalFilePages && $totalFilePages > 0) $fileCurrentPage = $totalFilePages;
    
    $fileOffset = ($fileCurrentPage - 1) * $filesPerPage;
    
    // Get recent files (shipments) with pagination and search
    $sqlRecentFiles = "SELECT f.*, c.first_name, c.last_name 
                      FROM files f 
                      LEFT JOIN customers c ON f.customer = c.customer_id";
    
    // Add search condition if search term is provided
    if (!empty($fileSearchTerm)) {
        $sqlRecentFiles .= " WHERE f.file_number LIKE '%$fileSearchTerm%' 
                           OR c.first_name LIKE '%$fileSearchTerm%' 
                           OR c.last_name LIKE '%$fileSearchTerm%' 
                           OR f.destination LIKE '%$fileSearchTerm%'
                           OR f.consignee_name LIKE '%$fileSearchTerm%'";
    }
    
    $sqlRecentFiles .= " ORDER BY f.created_date DESC 
                        LIMIT $fileOffset, $filesPerPage";
    
    $resultRecentFiles = $conn->query($sqlRecentFiles);
    $recentFiles = [];
    if ($resultRecentFiles && $resultRecentFiles->num_rows > 0) {
        while ($row = $resultRecentFiles->fetch_assoc()) {
            $recentFiles[] = $row;
        }
    }
    
    // PAGINATION FOR TRUCK ASSIGNMENTS
    $assignmentsPerPage = 5; // Number of assignments per page
    $assignmentCurrentPage = isset($_GET['assignment_page']) ? (int)$_GET['assignment_page'] : 1;
    $assignmentSearchTerm = isset($_GET['assignment_search']) ? $conn->real_escape_string($_GET['assignment_search']) : '';
    
    // Build the SQL query for truck assignments with search and pagination
    $sqlAssignmentsCount = "SELECT COUNT(*) as total FROM truck_details td 
                           JOIN files f ON td.file_no = f.file_number";
    
    // Add search condition if search term is provided
    if (!empty($assignmentSearchTerm)) {
        $sqlAssignmentsCount .= " WHERE td.file_no LIKE '%$assignmentSearchTerm%' 
                                OR td.container_code LIKE '%$assignmentSearchTerm%' 
                                OR td.transporter_name LIKE '%$assignmentSearchTerm%' 
                                OR td.driver_name LIKE '%$assignmentSearchTerm%'
                                OR f.destination LIKE '%$assignmentSearchTerm%'";
    }
    
    $resultAssignmentsCount = $conn->query($sqlAssignmentsCount);
    $totalAssignments = ($resultAssignmentsCount && $row = $resultAssignmentsCount->fetch_assoc()) ? $row['total'] : 0;
    $totalAssignmentPages = ceil($totalAssignments / $assignmentsPerPage);
    
    // Ensure current page is within valid range
    if ($assignmentCurrentPage < 1) $assignmentCurrentPage = 1;
    if ($assignmentCurrentPage > $totalAssignmentPages && $totalAssignmentPages > 0) $assignmentCurrentPage = $totalAssignmentPages;
    
    $assignmentOffset = ($assignmentCurrentPage - 1) * $assignmentsPerPage;
    
    // Get latest truck assignments with pagination and search
    $sqlTruckAssignments = "SELECT td.*, f.file_number, f.destination 
                           FROM truck_details td 
                           JOIN files f ON td.file_no = f.file_number";
    
    // Add search condition if search term is provided
    if (!empty($assignmentSearchTerm)) {
        $sqlTruckAssignments .= " WHERE td.file_no LIKE '%$assignmentSearchTerm%' 
                                OR td.container_code LIKE '%$assignmentSearchTerm%' 
                                OR td.transporter_name LIKE '%$assignmentSearchTerm%' 
                                OR td.driver_name LIKE '%$assignmentSearchTerm%'
                                OR f.destination LIKE '%$assignmentSearchTerm%'";
    }
    
    $sqlTruckAssignments .= " ORDER BY td.created_at DESC 
                            LIMIT $assignmentOffset, $assignmentsPerPage";
    
    $resultTruckAssignments = $conn->query($sqlTruckAssignments);
    $truckAssignments = [];
    if ($resultTruckAssignments && $resultTruckAssignments->num_rows > 0) {
        while ($row = $resultTruckAssignments->fetch_assoc()) {
            $truckAssignments[] = $row;
        }
    }
    
} catch (Exception $e) {
    // Log the error but don't display it
    error_log("Error in dashboard metrics: " . $e->getMessage());
}

// Initialize variables that might be used but not defined
if (!isset($recentFiles) || !is_array($recentFiles)) {
    $recentFiles = [];
}

if (!isset($totalFilePages)) {
    $totalFilePages = 0;
}

if (!isset($fileCurrentPage)) {
    $fileCurrentPage = 1;
}

if (!isset($truckAssignments) || !is_array($truckAssignments)) {
    $truckAssignments = [];
}

if (!isset($totalAssignmentPages)) {
    $totalAssignmentPages = 0;
}

if (!isset($assignmentCurrentPage)) {
    $assignmentCurrentPage = 1;
}

// Close the connection after all queries
$conn->close();

// REMOVED: Helper functions for pagination URLs as they are already defined elsewhere
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Dashboard - Royal Freight')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <!-- ApexCharts -->
    <link href="https://cdn.jsdelivr.net/npm/apexcharts@3.41.0/dist/apexcharts.min.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/apexcharts@3.41.0/dist/apexcharts.min.js"></script>
    
    <style>
        .search-box {
            position: relative;
            margin-bottom: 15px;
        }
        
        .pagination-container {
            display: flex;
            justify-content: center;
            margin-top: 20px;
        }
        
        .pagination-container .pagination {
            margin-bottom: 0;
        }
        
        .table-container {
            position: relative;
            min-height: 200px;
        }
        
        .table-loading-indicator {
            position: absolute;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            background-color: rgba(255, 255, 255, 0.7);
            display: flex;
            justify-content: center;
            align-items: center;
            z-index: 100;
            display: none;
        }
    </style>
</head>

<body>
    <!-- Begin page -->
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>

        <!-- ============================================================== -->
        <!-- Start right Content here -->
        <!-- ============================================================== -->
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Dashboard', 'title' => 'Royal Freight Management System')); ?>

                    <div class="row">
                        <!-- USD Revenue Card -->
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-success-subtle text-success rounded-2 fs-2">
                                                <i data-feather="dollar-sign" class="text-success"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-semibold text-muted text-truncate mb-3">USD Revenue</p>
                                            <div class="d-flex align-items-center mb-3">
                                                <h4 class="fs-4 flex-grow-1 mb-0">$<?php echo number_format($totalRevenueUSD, 2); ?></h4>
                                                <span class="badge <?php echo $revenueChangeUSD >= 0 ? 'bg-success-subtle text-success' : 'bg-danger-subtle text-danger'; ?> fs-12">
                                                    <i class="ri-arrow-<?php echo $revenueChangeUSD >= 0 ? 'up' : 'down'; ?>-s-line fs-13 align-middle me-1"></i>
                                                    <?php echo number_format(abs($revenueChangeUSD), 2); ?>%
                                                </span>
                                            </div>
                                            <p class="text-muted text-truncate mb-0">USD revenue this month</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- TZS Revenue Card -->
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-primary-subtle text-primary rounded-2 fs-2">
                                                <i class="ri-money-dollar-circle-line text-primary"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-semibold text-muted text-truncate mb-3">TSH Revenue</p>
                                            <div class="d-flex align-items-center mb-3">
                                                <h4 class="fs-4 flex-grow-1 mb-0">TSH <?php echo number_format($totalRevenueTZS, 0); ?></h4>
                                                <span class="badge <?php echo $revenueChangeTZS >= 0 ? 'bg-success-subtle text-success' : 'bg-danger-subtle text-danger'; ?> fs-12">
                                                    <i class="ri-arrow-<?php echo $revenueChangeTZS >= 0 ? 'up' : 'down'; ?>-s-line fs-13 align-middle me-1"></i>
                                                    <?php echo number_format(abs($revenueChangeTZS), 2); ?>%
                                                </span>
                                            </div>
                                            <p class="text-muted text-truncate mb-0">TSH revenue this month</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Total Shipments Card -->
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-warning-subtle text-warning rounded-2 fs-2">
                                                <i data-feather="package" class="text-warning"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-semibold text-muted text-truncate mb-3">Total Shipments</p>
                                            <div class="d-flex align-items-center mb-3">
                                                <h4 class="fs-4 flex-grow-1 mb-0"><?php echo $totalShipments; ?></h4>
                                            </div>
                                            <p class="text-muted text-truncate mb-0">
                                                <span class="text-success"><?php echo $activeShipments; ?> Active</span> | 
                                                <span class="text-muted"><?php echo $completedShipments; ?> Completed</span>
                                            </p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Container Deposits Card -->
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-info-subtle text-info rounded-2 fs-2">
                                                <i data-feather="archive" class="text-info"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-semibold text-muted text-truncate mb-3">Container Deposits</p>
                                            <div class="d-flex align-items-center mb-3">
                                                <h4 class="fs-4 flex-grow-1 mb-0"><?php echo $containerDeposits; ?></h4>
                                            </div>
                                            <?php
                                            // Calculate count of fixed vs normal deposits
                                            include('layouts/dbconn.php');
                                            $sqlDepositTypes = "SELECT 
                                                SUM(CASE WHEN type = 'normal' THEN 1 ELSE 0 END) as normal_count,
                                                SUM(CASE WHEN type = 'fixed' THEN 1 ELSE 0 END) as fixed_count
                                                FROM container_deposit";
                                            $resultDepositTypes = $conn->query($sqlDepositTypes);
                                            $normalDeposits = 0;
                                            $fixedDeposits = 0;
                                            
                                            if ($resultDepositTypes && $row = $resultDepositTypes->fetch_assoc()) {
                                                $normalDeposits = $row['normal_count'];
                                                $fixedDeposits = $row['fixed_count'];
                                            }
                                            $conn->close();
                                            ?>
                                            <p class="text-muted text-truncate mb-0">
                                                <span class="badge bg-info-subtle text-info"><?php echo $normalDeposits; ?> Normal</span>
                                                <span class="badge bg-primary-subtle text-primary"><?php echo $fixedDeposits; ?> Fixed</span>
                                            </p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <!-- Pending Approvals Card -->
                        <div class="col-xl-4 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-danger-subtle text-danger rounded-2 fs-2">
                                                <i data-feather="alert-circle" class="text-danger"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-semibold text-muted text-truncate mb-3">Pending Approvals</p>
                                            <div class="d-flex align-items-center mb-3">
                                                <h4 class="fs-4 flex-grow-1 mb-0"><?php echo $pendingApprovals; ?></h4>
                                            </div>
                                            <p class="text-muted text-truncate mb-0">Waiting for approval</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Agency Fees Card -->
                        <div class="col-xl-4 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-success-subtle text-success rounded-2 fs-2">
                                                <i data-feather="trending-up" class="text-success"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-semibold text-muted text-truncate mb-3">Agency Fees</p>
                                            <div class="d-flex align-items-center mb-3">
                                                <h4 class="fs-4 flex-grow-1 mb-0">$<?php echo number_format($totalAgencyFeesUSD, 2); ?></h4>
                                            </div>
                                            <div class="d-flex justify-content-between mb-2">
                                                <span class="badge bg-primary-subtle text-primary">
                                                    USD: $<?php echo number_format($totalAgencyFeesUSD, 2); ?>
                                                </span>
                                                <span class="badge bg-info-subtle text-info">
                                                    TSH: <?php echo number_format($totalAgencyFeesTZS, 0); ?>
                                                </span>
                                            </div>
                                            <p class="text-muted text-truncate mb-0">Total agency fees this month</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Upcoming Deliveries Card -->
                        <div class="col-xl-4 col-md-6">
                            <div class="card card-animate">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-sm flex-shrink-0">
                                            <span class="avatar-title bg-primary-subtle text-primary rounded-2 fs-2">
                                                <i data-feather="truck" class="text-primary"></i>
                                            </span>
                                        </div>
                                        <div class="flex-grow-1 overflow-hidden ms-3">
                                            <p class="text-uppercase fw-semibold text-muted text-truncate mb-3">Upcoming Deliveries</p>
                                            <div class="d-flex align-items-center mb-3">
                                                <h4 class="fs-4 flex-grow-1 mb-0"><?php echo $upcomingDeliveries; ?></h4>
                                            </div>
                                            <p class="text-muted text-truncate mb-0">
                                                Delivery performance: <?php echo $deliveryPerformance; ?>% on-time
                                            </p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <!-- Recent Shipments Table -->
                        <div class="col-xl-8">
                            <div class="card">
                                <div class="card-header d-flex align-items-center">
                                    <h4 class="card-title mb-0 flex-grow-1">Recent Shipments</h4>
                                    <div class="flex-shrink-0">
                                        <button type="button" class="btn btn-soft-info btn-sm" onclick="refreshShipments()">
                                            <i class="ri-refresh-line align-middle"></i> Refresh
                                        </button>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="search-box mb-3">
                                        <form id="fileSearchForm" method="GET" action="">
                                            <div class="input-group">
                                                <input type="text" class="form-control" placeholder="Search file number, customer, destination..." 
                                                    name="file_search" id="fileSearchInput" value="<?php echo htmlspecialchars($fileSearchTerm ?? ''); ?>">
                                                <button class="btn btn-primary" type="submit"><i class="ri-search-line"></i></button>
                                                <?php if (!empty($fileSearchTerm)): ?>
                                                <button class="btn btn-danger" type="button" onclick="clearFileSearch()"><i class="ri-close-line"></i></button>
                                                <?php endif; ?>
                                            </div>
                                            <!-- Preserve other GET parameters if they exist -->
                                            <?php if (isset($_GET['assignment_page'])): ?>
                                            <input type="hidden" name="assignment_page" value="<?php echo $_GET['assignment_page']; ?>">
                                            <?php endif; ?>
                                            <?php if (isset($_GET['assignment_search'])): ?>
                                            <input type="hidden" name="assignment_search" value="<?php echo htmlspecialchars($_GET['assignment_search']); ?>">
                                            <?php endif; ?>
                                        </form>
                                    </div>
                                    
                                    <div class="table-container">
                                        <div class="table-loading-indicator" id="fileTableLoading">
                                            <div class="spinner-border text-primary" role="status">
                                                <span class="visually-hidden">Loading...</span>
                                            </div>
                                        </div>
                                        <div class="table-responsive">
                                            <table class="table table-hover table-centered align-middle table-nowrap mb-0">
                                                <thead>
                                                    <tr>
                                                        <th scope="col">File #</th>
                                                        <th scope="col">Customer</th>
                                                        <th scope="col">Destination</th>
                                                        <th scope="col">Created Date</th>
                                                        <th scope="col">Status</th>
                                                        <th scope="col">Action</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach ($recentFiles as $file): ?>
                                                    <tr>
                                                        <td><?php echo $file['file_number']; ?></td>
                                                        <td>
                                                            <?php 
                                                            echo !empty($file['first_name']) ? 
                                                                 $file['first_name'] . ' ' . $file['last_name'] : 
                                                                 'Customer #' . $file['customer']; 
                                                            ?>
                                                        </td>
                                                        <td><?php echo $file['destination'] ?? 'N/A'; ?></td>
                                                        <td><?php echo date('d M Y', strtotime($file['created_date'])); ?></td>
                                                        <td>
                                                            <?php if($file['close_file'] == 'Yes'): ?>
                                                                <span class="badge bg-success">Completed</span>
                                                            <?php else: ?>
                                                                <span class="badge bg-info">Active</span>
                                                            <?php endif; ?>
                                                        </td>
                                                        <td>
                                                            <a href="view_file.php?id=<?php echo $file['id']; ?>" class="btn btn-sm btn-soft-primary">
                                                                <i class="ri-eye-line align-middle"></i>
                                                            </a>
                                                        </td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                    <?php if (empty($recentFiles)): ?>
                                                    <tr>
                                                        <td colspan="6" class="text-center">No recent shipments found</td>
                                                    </tr>
                                                    <?php endif; ?>
                                                </tbody>
                                            </table>
                                        </div>
                                        
                                        <!-- Pagination for Files -->
                                        <?php if ($totalFilePages > 1): ?>
                                        <div class="pagination-container">
                                            <ul class="pagination">
                                                <!-- First Page Link -->
                                                <li class="page-item <?php echo ($fileCurrentPage <= 1) ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="<?php echo generateFilePaginationUrl(1); ?>" aria-label="First">
                                                        <i class="ri-arrow-left-double-line"></i>
                                                    </a>
                                                </li>
                                                
                                                <!-- Previous Page Link -->
                                                <li class="page-item <?php echo ($fileCurrentPage <= 1) ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="<?php echo generateFilePaginationUrl($fileCurrentPage - 1); ?>" aria-label="Previous">
                                                        <i class="ri-arrow-left-s-line"></i>
                                                    </a>
                                                </li>
                                                
                                                <!-- Page Numbers -->
                                                <?php
                                                $startPage = max(1, $fileCurrentPage - 2);
                                                $endPage = min($totalFilePages, $fileCurrentPage + 2);
                                                
                                                // Always show first page
                                                if ($startPage > 1) {
                                                    echo '<li class="page-item"><a class="page-link" href="' . generateFilePaginationUrl(1) . '">1</a></li>';
                                                    if ($startPage > 2) {
                                                        echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                                                    }
                                                }
                                                
                                                // Display page numbers
                                                for ($i = $startPage; $i <= $endPage; $i++) {
                                                    echo '<li class="page-item ' . ($i == $fileCurrentPage ? 'active' : '') . '">';
                                                    echo '<a class="page-link" href="' . generateFilePaginationUrl($i) . '">' . $i . '</a>';
                                                    echo '</li>';
                                                }
                                                
                                                // Always show last page
                                                if ($endPage < $totalFilePages) {
                                                    if ($endPage < $totalFilePages - 1) {
                                                        echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                                                    }
                                                    echo '<li class="page-item"><a class="page-link" href="' . generateFilePaginationUrl($totalFilePages) . '">' . $totalFilePages . '</a></li>';
                                                }
                                                ?>
                                                
                                                <!-- Next Page Link -->
                                                <li class="page-item <?php echo ($fileCurrentPage >= $totalFilePages) ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="<?php echo generateFilePaginationUrl($fileCurrentPage + 1); ?>" aria-label="Next">
                                                        <i class="ri-arrow-right-s-line"></i>
                                                    </a>
                                                </li>
                                                
                                                <!-- Last Page Link -->
                                                <li class="page-item <?php echo ($fileCurrentPage >= $totalFilePages) ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="<?php echo generateFilePaginationUrl($totalFilePages); ?>" aria-label="Last">
                                                        <i class="ri-arrow-right-double-line"></i>
                                                    </a>
                                                </li>
                                            </ul>
                                        </div>
                                        <?php endif; ?>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Top Transporters and Popular Routes Cards -->
                        <div class="col-xl-4">
                            <!-- Top Transporters -->
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Top Transporters</h4>
                                </div>
                                <div class="card-body">
                                    <?php if (!empty($topTransporters)): ?>
                                    <div class="mx-n3">
                                        <?php foreach ($topTransporters as $index => $transporter): ?>
                                        <div class="d-flex p-3 border-top border-top-dashed">
                                            <div class="avatar-sm me-3 flex-shrink-0">
                                                <div class="avatar-title bg-light rounded-circle fs-18">
                                                    <span class="fw-semibold"><?php echo $index + 1; ?></span>
                                                </div>
                                            </div>
                                            <div class="flex-grow-1">
                                                <h5 class="fs-16 mb-1"><?php echo htmlspecialchars($transporter['name']); ?></h5>
                                                <p class="text-muted mb-0"><?php echo $transporter['assignments']; ?> assignments</p>
                                            </div>
                                        </div>
                                        <?php endforeach; ?>
                                    </div>
                                    <?php else: ?>
                                    <div class="text-center">
                                        <p class="text-muted">No transporter data available</p>
                                    </div>
                                    <?php endif; ?>
                                </div>
                            </div>
                            
                            <!-- Popular Routes -->
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Popular Destinations</h4>
                                </div>
                                <div class="card-body">
                                    <?php if (!empty($popularRoutes)): ?>
                                    <div id="routesChart" class="apex-charts"></div>
                                    <div class="mt-3">
                                        <ul class="list-group list-group-flush">
                                            <?php foreach ($popularRoutes as $route): ?>
                                            <li class="list-group-item px-0 py-2">
                                                <div class="d-flex justify-content-between align-items-center">
                                                    <span><?php echo htmlspecialchars($route['destination'] ?? 'Unknown'); ?></span>
                                                    <span class="badge bg-info-subtle text-info"><?php echo $route['shipment_count']; ?> shipments</span>
                                                </div>
                                            </li>
                                            <?php endforeach; ?>
                                        </ul>
                                    </div>
                                    <?php else: ?>
                                    <div class="text-center">
                                        <p class="text-muted">No route data available</p>
                                    </div>
                                    <?php endif; ?>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Latest Truck Assignments -->
                    <div class="row">
                        <div class="col-lg-12">
                            <div class="card">
                                <div class="card-header d-flex align-items-center">
                                    <h4 class="card-title mb-0 flex-grow-1">Latest Truck Assignments</h4>
                                    <div class="flex-shrink-0">
                                        <button type="button" class="btn btn-soft-info btn-sm" onclick="refreshAssignments()">
                                            <i class="ri-refresh-line align-middle"></i> Refresh
                                        </button>
                                    </div>
                                </div>
                                <div class="card-body">
                                    <div class="search-box mb-3">
                                        <form id="assignmentSearchForm" method="GET" action="">
                                            <div class="input-group">
                                                <input type="text" class="form-control" placeholder="Search file number, container, transporter..." 
                                                    name="assignment_search" id="assignmentSearchInput" value="<?php echo htmlspecialchars($assignmentSearchTerm ?? ''); ?>">
                                                <button class="btn btn-primary" type="submit"><i class="ri-search-line"></i></button>
                                                <?php if (!empty($assignmentSearchTerm)): ?>
                                                <button class="btn btn-danger" type="button" onclick="clearAssignmentSearch()"><i class="ri-close-line"></i></button>
                                                <?php endif; ?>
                                            </div>
                                            <!-- Preserve other GET parameters if they exist -->
                                            <?php if (isset($_GET['file_page'])): ?>
                                            <input type="hidden" name="file_page" value="<?php echo $_GET['file_page']; ?>">
                                            <?php endif; ?>
                                            <?php if (isset($_GET['file_search'])): ?>
                                            <input type="hidden" name="file_search" value="<?php echo htmlspecialchars($_GET['file_search']); ?>">
                                            <?php endif; ?>
                                        </form>
                                    </div>
                                    
                                    <div class="table-container">
                                        <div class="table-loading-indicator" id="assignmentTableLoading">
                                            <div class="spinner-border text-primary" role="status">
                                                <span class="visually-hidden">Loading...</span>
                                            </div>
                                        </div>
                                        <div class="table-responsive">
                                            <table class="table table-hover table-striped align-middle table-nowrap mb-0">
                                                <thead class="table-light">
                                                    <tr>
                                                        <th scope="col">Assignment ID</th>
                                                        <th scope="col">File Number</th>
                                                        <th scope="col">Container</th>
                                                        <th scope="col">Transporter</th>
                                                        <th scope="col">Driver</th>
                                                        <th scope="col">Destination</th>
                                                        <th scope="col">Load Date</th>
                                                        <th scope="col">Status</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach ($truckAssignments as $assignment): ?>
                                                    <tr>
                                                        <td><?php echo $assignment['id']; ?></td>
                                                        <td>
                                                            <a href="view_file.php?number=<?php echo urlencode($assignment['file_no']); ?>">
                                                                <?php echo $assignment['file_no']; ?>
                                                            </a>
                                                        </td>
                                                        <td><?php echo $assignment['container_code']; ?></td>
                                                        <td><?php echo $assignment['transporter_name']; ?></td>
                                                        <td><?php echo $assignment['driver_name'] ?? 'N/A'; ?></td>
                                                        <td><?php echo $assignment['destination'] ?? 'N/A'; ?></td>
                                                        <td><?php echo !empty($assignment['load_date']) ? date('d M Y', strtotime($assignment['load_date'])) : 'Not set'; ?></td>
                                                        <td>
                                                            <?php 
                                                            $status = 'Pending';
                                                            $statusBadge = 'bg-warning';
                                                            
                                                            if (!empty($assignment['empty_return_date'])) {
                                                                $status = 'Completed';
                                                                $statusBadge = 'bg-success';
                                                            } elseif (!empty($assignment['delivery_date'])) {
                                                                $status = 'Delivered';
                                                                $statusBadge = 'bg-primary';
                                                            } elseif (!empty($assignment['border_crossing_date'])) {
                                                                $status = 'Crossing Border';
                                                                $statusBadge = 'bg-info';
                                                            } elseif (!empty($assignment['port_date'])) {
                                                                $status = 'Left Port';
                                                                $statusBadge = 'bg-secondary';
                                                            }
                                                            ?>
                                                            <span class="badge <?php echo $statusBadge; ?>"><?php echo $status; ?></span>
                                                        </td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                    <?php if (empty($truckAssignments)): ?>
                                                    <tr>
                                                        <td colspan="8" class="text-center">No truck assignments found</td>
                                                    </tr>
                                                    <?php endif; ?>
                                                </tbody>
                                            </table>
                                        </div>
                                        
                                        <!-- Pagination for Truck Assignments -->
                                        <?php if ($totalAssignmentPages > 1): ?>
                                        <div class="pagination-container">
                                            <ul class="pagination">
                                                <!-- First Page Link -->
                                                <li class="page-item <?php echo ($assignmentCurrentPage <= 1) ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="<?php echo generateAssignmentPaginationUrl(1); ?>" aria-label="First">
                                                        <i class="ri-arrow-left-double-line"></i>
                                                    </a>
                                                </li>
                                                
                                                <!-- Previous Page Link -->
                                                <li class="page-item <?php echo ($assignmentCurrentPage <= 1) ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="<?php echo generateAssignmentPaginationUrl($assignmentCurrentPage - 1); ?>" aria-label="Previous">
                                                        <i class="ri-arrow-left-s-line"></i>
                                                    </a>
                                                </li>
                                                
                                                <!-- Page Numbers -->
                                                <?php
                                                $startPage = max(1, $assignmentCurrentPage - 2);
                                                $endPage = min($totalAssignmentPages, $assignmentCurrentPage + 2);
                                                
                                                // Always show first page
                                                if ($startPage > 1) {
                                                    echo '<li class="page-item"><a class="page-link" href="' . generateAssignmentPaginationUrl(1) . '">1</a></li>';
                                                    if ($startPage > 2) {
                                                        echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                                                    }
                                                }
                                                
                                                // Display page numbers
                                                for ($i = $startPage; $i <= $endPage; $i++) {
                                                    echo '<li class="page-item ' . ($i == $assignmentCurrentPage ? 'active' : '') . '">';
                                                    echo '<a class="page-link" href="' . generateAssignmentPaginationUrl($i) . '">' . $i . '</a>';
                                                    echo '</li>';
                                                }
                                                
                                                // Always show last page
                                                if ($endPage < $totalAssignmentPages) {
                                                    if ($endPage < $totalAssignmentPages - 1) {
                                                        echo '<li class="page-item disabled"><span class="page-link">...</span></li>';
                                                    }
                                                    echo '<li class="page-item"><a class="page-link" href="' . generateAssignmentPaginationUrl($totalAssignmentPages) . '">' . $totalAssignmentPages . '</a></li>';
                                                }
                                                ?>
                                                
                                                <!-- Next Page Link -->
                                                <li class="page-item <?php echo ($assignmentCurrentPage >= $totalAssignmentPages) ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="<?php echo generateAssignmentPaginationUrl($assignmentCurrentPage + 1); ?>" aria-label="Next">
                                                        <i class="ri-arrow-right-s-line"></i>
                                                    </a>
                                                </li>
                                                
                                                <!-- Last Page Link -->
                                                <li class="page-item <?php echo ($assignmentCurrentPage >= $totalAssignmentPages) ? 'disabled' : ''; ?>">
                                                    <a class="page-link" href="<?php echo generateAssignmentPaginationUrl($totalAssignmentPages); ?>" aria-label="Last">
                                                        <i class="ri-arrow-right-double-line"></i>
                                                    </a>
                                                </li>
                                            </ul>
                                        </div>
                                        <?php endif; ?>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                <!-- container-fluid -->
            </div>
            <!-- End Page-content -->

            <?php include 'layouts/footer.php'; ?>
        </div>
        <!-- end main content-->
    </div>
    <!-- END layout-wrapper -->

    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <!-- apexcharts -->
    <script src="assets/libs/apexcharts/apexcharts.min.js"></script>

    <script>
        // Helper function to generate pagination URL for files
        <?php
        function generateFilePaginationUrl($page) {
            $params = $_GET;
            $params['file_page'] = $page;
            return '?' . http_build_query($params);
        }
        
        // Helper function to generate pagination URL for assignments
        function generateAssignmentPaginationUrl($page) {
            $params = $_GET;
            $params['assignment_page'] = $page;
            return '?' . http_build_query($params);
        }
        ?>
        
        // Initialize the Popular Routes chart
        document.addEventListener("DOMContentLoaded", function() {
            <?php if (!empty($popularRoutes)): ?>
            // Prepare data for chart
            var routeNames = [];
            var routeCounts = [];
            
            <?php foreach ($popularRoutes as $route): ?>
            routeNames.push("<?php echo addslashes($route['destination'] ?? 'Unknown'); ?>");
            routeCounts.push(<?php echo $route['shipment_count']; ?>);
            <?php endforeach; ?>
            
            // Chart options
            var options = {
                series: [{
                    name: 'Shipments',
                    data: routeCounts
                }],
                chart: {
                    type: 'bar',
                    height: 250,
                    toolbar: {
                        show: false
                    }
                },
                plotOptions: {
                    bar: {
                        borderRadius: 4,
                        distributed: true,
                        horizontal: true,
                        dataLabels: {
                            position: 'top'
                        },
                    }
                },
                colors: ['#0ab39c', '#299cdb', '#f7b84b', '#405189', '#f06548'],
                dataLabels: {
                    enabled: true,
                    offsetX: 32,
                    style: {
                        fontSize: '12px',
                        fontWeight: 400,
                        colors: ['#adb5bd']
                    }
                },
                xaxis: {
                    categories: routeNames,
                    axisBorder: {
                        show: false
                    },
                    axisTicks: {
                        show: false
                    }
                },
                yaxis: {
                    labels: {
                        show: false
                    }
                },
                grid: {
                    show: false
                },
                legend: {
                    show: false
                }
            };
            
            var chart = new ApexCharts(document.querySelector("#routesChart"), options);
            chart.render();
            <?php endif; ?>
        });
        
        // Clear file search function
        function clearFileSearch() {
            document.getElementById('fileSearchInput').value = '';
            document.getElementById('fileSearchForm').submit();
        }
        
        // Clear assignment search function
        function clearAssignmentSearch() {
            document.getElementById('assignmentSearchInput').value = '';
            document.getElementById('assignmentSearchForm').submit();
        }
        
        // Refresh shipments table
        function refreshShipments() {
            document.getElementById('fileTableLoading').style.display = 'flex';
            setTimeout(function() {
                window.location.reload();
            }, 500);
        }
        
        // Refresh assignments table
        function refreshAssignments() {
            document.getElementById('assignmentTableLoading').style.display = 'flex';
            setTimeout(function() {
                window.location.reload();
            }, 500);
        }
        
        // Function to navigate to view file page
        function viewFile(fileNumber) {
            window.location.href = 'view_file.php?number=' + encodeURIComponent(fileNumber);
        }
        
        // Function to navigate to truck assignment view
        function viewTruckAssignment(id) {
            window.location.href = 'truck_details.php?id=' + id;
        }
    </script>
    
    <!-- Custom dashboard metrics chart -->
    <script>
        document.addEventListener("DOMContentLoaded", function() {
            // Calculate performance metrics - actual vs target
            var targetDeliveryRate = 95; // 95% on-time delivery target
            var actualDeliveryRate = <?php echo $deliveryPerformance; ?>;
            var deliveryPerformancePercentage = (actualDeliveryRate / targetDeliveryRate) * 100;
            
            if (document.getElementById("delivery-performance-chart")) {
                var options = {
                    series: [Math.min(deliveryPerformancePercentage, 100)],
                    chart: {
                        height: 150,
                        type: 'radialBar',
                    },
                    plotOptions: {
                        radialBar: {
                            hollow: {
                                size: '60%',
                            },
                            dataLabels: {
                                name: {
                                    show: false,
                                },
                                value: {
                                    offsetY: 5,
                                    fontSize: '16px',
                                    fontWeight: 'bold'
                                }
                            }
                        }
                    },
                    colors: ['#0ab39c'],
                    labels: ['Delivery Performance'],
                };
                
                var chart = new ApexCharts(document.querySelector("#delivery-performance-chart"), options);
                chart.render();
            }
        });
    </script>
    
    <!-- App js -->
    <script src="assets/js/app.js"></script>
</body>
</html>