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

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

// Get user details
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT username, user_role, full_name, email, role_id 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'];
    $role_id = $row_user_details['role_id'];
}

// Check permissions
function hasPermission($role_id, $permission) {
    // Define permissions for different roles
    $permissions = [
        '1' => ['view', 'export'], // Admin has all permissions
        '2' => ['view', 'export'], // General Manager
        '3' => ['view', 'export'], // Operations
        '4' => ['view', 'export'], // Finance
        '5' => ['view'], // Declaration Officer
        '6' => ['view'], // Shipping Line Clerk
        '7' => ['view'], // Transport Supervisor
    ];
    
    if (isset($permissions[$role_id]) && in_array($permission, $permissions[$role_id])) {
        return true;
    }
    
    return false;
}

// Verify user has view permissions
if (!hasPermission($role_id, 'view')) {
    $_SESSION['error_message'] = "You don't have permission to view this report";
    header('Location: index.php');
    exit();
}

// Default values for filters
$current_year = date('Y');
$current_month = date('m');
$selected_year = isset($_GET['year']) ? $_GET['year'] : $current_year;
$selected_month = isset($_GET['month']) ? $_GET['month'] : 'all'; // Default to all months
$selected_customer = isset($_GET['customer']) ? $_GET['customer'] : 'all'; // Default to all customers
$selected_currency = isset($_GET['currency']) ? $_GET['currency'] : 'all'; // Default to all currencies
$selected_destination = isset($_GET['destination']) ? $_GET['destination'] : 'all'; // Default to all destinations

// Get available years for filtering (from files data)
$years_query = "SELECT DISTINCT YEAR(created_date) AS year FROM files ORDER BY year DESC";
$years_result = $conn->query($years_query);
$years = [];
if ($years_result && $years_result->num_rows > 0) {
    while ($row = $years_result->fetch_assoc()) {
        $years[] = $row['year'];
    }
} else {
    // If no data, at least include current year
    $years[] = $current_year;
}

// Get customers for filtering
$customers_query = "SELECT customer_id, first_name FROM customers WHERE status = 1 ORDER BY first_name";
$customers_result = $conn->query($customers_query);
$customers = [];
if ($customers_result && $customers_result->num_rows > 0) {
    while ($row = $customers_result->fetch_assoc()) {
        $customers[$row['customer_id']] = $row['first_name'];
    }
}

// Get unique destinations from files
$destinations_query = "SELECT DISTINCT destination FROM files WHERE destination IS NOT NULL AND destination != '' ORDER BY destination";
$destinations_result = $conn->query($destinations_query);
$destinations = [];
if ($destinations_result && $destinations_result->num_rows > 0) {
    while ($row = $destinations_result->fetch_assoc()) {
        $destinations[] = $row['destination'];
    }
}

// Get unique currencies from files
$currencies_query = "SELECT DISTINCT currency FROM files WHERE currency IS NOT NULL AND currency != '' ORDER BY currency";
$currencies_result = $conn->query($currencies_query);
$currencies = [];
if ($currencies_result && $currencies_result->num_rows > 0) {
    while ($row = $currencies_result->fetch_assoc()) {
        $currencies[] = $row['currency'];
    }
} else {
    // Default currencies if none found
    $currencies = ['$', '€', 'TZS', 'KES'];
}

// Build SQL query based on filters
$sql_conditions = "WHERE pp.category = 'Operational' AND pp.cost_code = '1-101' AND pp.cost_description = 'Agency Fee'";

if ($selected_year != 'all') {
    $sql_conditions .= " AND YEAR(f.created_date) = '$selected_year'";
}

if ($selected_month != 'all') {
    $sql_conditions .= " AND MONTH(f.created_date) = '$selected_month'";
}

if ($selected_customer != 'all') {
    $sql_conditions .= " AND f.customer = '$selected_customer'";
}

if ($selected_currency != 'all') {
    $sql_conditions .= " AND f.currency = '$selected_currency'";
}

if ($selected_destination != 'all') {
    $sql_conditions .= " AND f.destination = '$selected_destination'";
}

// SQL to get earnings data
$sql = "SELECT 
            f.file_number,
            f.created_date,
            MONTH(f.created_date) AS month,
            YEAR(f.created_date) AS year,
            c.first_name AS customer_name,
            f.destination,
            f.currency,
            f.consignee_name,
            pp.charge AS agency_fee,
            CASE 
                WHEN f.`local/transit` = 1 THEN 'Local'
                WHEN f.`local/transit` = 2 THEN 'Transit'
                ELSE 'Unknown'
            END AS shipment_type
        FROM 
            files f
            LEFT JOIN customers c ON f.customer = c.customer_id
            LEFT JOIN pre_proforma pp ON f.file_number = pp.file_number 
        $sql_conditions
        ORDER BY 
            f.created_date DESC";

$result = $conn->query($sql);

// For the summary stats
$total_earnings = 0;
$earnings_by_month = array_fill(1, 12, 0);
$earnings_by_customer = [];
$earnings_by_destination = [];
$earnings_by_shipment_type = ['Local' => 0, 'Transit' => 0];
$earnings_by_currency = [];
$records = [];

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        // Store record for detailed view
        $records[] = $row;
        
        // Calculate totals and breakdowns
        $month = $row['month'];
        $customer = $row['customer_name'] ?: 'Unknown';
        $destination = $row['destination'] ?: 'Unknown';
        $shipment_type = $row['shipment_type'];
        $currency = $row['currency'] ?: '$';
        $fee = floatval($row['agency_fee']);
        
        // Only add to totals if fee is positive (some systems might have negative adjustments)
        if ($fee > 0) {
            // Track by currency
            if (!isset($earnings_by_currency[$currency])) {
                $earnings_by_currency[$currency] = 0;
            }
            $earnings_by_currency[$currency] += $fee;
            
            // For USD equivalent calculations (simplified example)
            if ($currency == '$' || $currency == 'USD') {
                $total_earnings += $fee;
                $earnings_by_month[$month] += $fee;
                
                // Track by customer
                if (!isset($earnings_by_customer[$customer])) {
                    $earnings_by_customer[$customer] = 0;
                }
                $earnings_by_customer[$customer] += $fee;
                
                // Track by destination
                if (!isset($earnings_by_destination[$destination])) {
                    $earnings_by_destination[$destination] = 0;
                }
                $earnings_by_destination[$destination] += $fee;
                
                // Track by shipment type
                $earnings_by_shipment_type[$shipment_type] += $fee;
            }
        }
    }
}

// Sort customer earnings for chart
arsort($earnings_by_customer);
arsort($earnings_by_destination);

// Export to PDF if requested
if (isset($_GET['export']) && $_GET['export'] == 'pdf' && hasPermission($role_id, 'export')) {
    require_once('fpdf/fpdf.php');
    
    class PDF extends FPDF {
        // Page header
        function Header() {
            // Logo
            $this->Image('assets/images/logo-icon-dark.png', 10, 10, 30);
            // Arial bold 15
            $this->SetFont('Arial', 'B', 15);
            // Move to the right
            $this->Cell(80);
            // Title
            $this->Cell(30, 10, 'Agency Earnings Report', 0, 0, 'C');
            // Date
            $this->SetFont('Arial', 'I', 10);
            $this->Cell(80, 10, 'Generated: ' . date('Y-m-d H:i:s'), 0, 0, 'R');
            // Line break
            $this->Ln(20);
        }

        // Page footer
        function Footer() {
            // Position at 1.5 cm from bottom
            $this->SetY(-15);
            // Arial italic 8
            $this->SetFont('Arial', 'I', 8);
            // Page number
            $this->Cell(0, 10, 'Page ' . $this->PageNo() . '/{nb}', 0, 0, 'C');
        }
        
        // Better table
        function FancyTable($header, $data) {
            // Colors, line width and bold font
            $this->SetFillColor(66, 139, 202);
            $this->SetTextColor(255);
            $this->SetDrawColor(66, 139, 202);
            $this->SetLineWidth(.3);
            $this->SetFont('', 'B');
            
            // Header
            $w = array(25, 50, 30, 25, 25, 35);
            for($i=0;$i<count($header);$i++)
                $this->Cell($w[$i], 7, $header[$i], 1, 0, 'C', true);
            $this->Ln();
            
            // Color and font restoration
            $this->SetFillColor(224, 235, 255);
            $this->SetTextColor(0);
            $this->SetFont('');
            
            // Data
            $fill = false;
            foreach($data as $row) {
                $this->Cell($w[0], 6, $row[0], 'LR', 0, 'L', $fill);
                $this->Cell($w[1], 6, $row[1], 'LR', 0, 'L', $fill);
                $this->Cell($w[2], 6, $row[2], 'LR', 0, 'C', $fill);
                $this->Cell($w[3], 6, $row[3], 'LR', 0, 'C', $fill);
                $this->Cell($w[4], 6, $row[4], 'LR', 0, 'C', $fill);
                $this->Cell($w[5], 6, $row[5], 'LR', 0, 'R', $fill);
                $this->Ln();
                $fill = !$fill;
            }
            
            // Closing line
            $this->Cell(array_sum($w), 0, '', 'T');
        }
    }

    // Create PDF object
    $pdf = new PDF('P', 'mm', 'A4');
    $pdf->AliasNbPages();
    $pdf->AddPage();
    $pdf->SetFont('Arial', '', 12);
    
    // Add report title and filters
    $pdf->SetFont('Arial', 'B', 14);
    $pdf->Cell(0, 10, 'Agency Earnings Report', 0, 1, 'C');
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(0, 6, 'Period: ' . ($selected_month != 'all' ? date('F', mktime(0, 0, 0, $selected_month, 1)) : 'All months') . ' ' . $selected_year, 0, 1);
    $pdf->Cell(0, 6, 'Currency: ' . ($selected_currency != 'all' ? $selected_currency : 'All currencies'), 0, 1);
    if ($selected_customer != 'all') {
        $pdf->Cell(0, 6, 'Customer: ' . ($customers[$selected_customer] ?? 'Unknown'), 0, 1);
    }
    if ($selected_destination != 'all') {
        $pdf->Cell(0, 6, 'Destination: ' . $selected_destination, 0, 1);
    }
    $pdf->Ln(5);
    
    // Add summary statistics
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(0, 10, 'Summary Statistics', 0, 1);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(100, 8, 'Total Earnings (USD):', 0, 0);
    $pdf->Cell(0, 8, '$' . number_format($total_earnings, 2), 0, 1);
    
    // Earnings by currency
    $pdf->SetFont('Arial', 'B', 11);
    $pdf->Cell(0, 10, 'Earnings by Currency', 0, 1);
    $pdf->SetFont('Arial', '', 10);
    foreach ($earnings_by_currency as $currency => $amount) {
        $pdf->Cell(100, 6, $currency . ':', 0, 0);
        $pdf->Cell(0, 6, $currency . ' ' . number_format($amount, 2), 0, 1);
    }
    $pdf->Ln(5);
    
    // Earnings by shipment type
    $pdf->SetFont('Arial', 'B', 11);
    $pdf->Cell(0, 10, 'Earnings by Shipment Type', 0, 1);
    $pdf->SetFont('Arial', '', 10);
    foreach ($earnings_by_shipment_type as $type => $amount) {
        if ($amount > 0) {
            $pdf->Cell(100, 6, $type . ':', 0, 0);
            $pdf->Cell(0, 6, '$' . number_format($amount, 2), 0, 1);
        }
    }
    $pdf->Ln(5);
    
    // Table with records
    $pdf->AddPage();
    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(0, 10, 'Detailed Earnings Report', 0, 1);
    
    $pdf->SetFont('Arial', '', 8);
    
    // Table header
    $header = array('File Number', 'Customer', 'Date', 'Type', 'Currency', 'Agency Fee');
    
    // Table data
    $tableData = array();
    foreach ($records as $record) {
        $tableData[] = array(
            $record['file_number'],
            $record['customer_name'] ?: 'Unknown',
            date('Y-m-d', strtotime($record['created_date'])),
            $record['shipment_type'],
            $record['currency'],
            $record['currency'] . ' ' . number_format($record['agency_fee'], 2)
        );
    }
    
    $pdf->FancyTable($header, $tableData);
    
    // Output PDF
    $pdf->Output('Agency_Earnings_Report_' . date('Y-m-d') . '.pdf', 'D');
    exit;
}

// Export to Excel if requested
if (isset($_GET['export']) && $_GET['export'] == 'excel' && hasPermission($role_id, 'export')) {
    require 'vendor/phpoffice/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php';
    require 'vendor/phpoffice/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php';
    
    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    
    // Set document properties
    $spreadsheet->getProperties()
        ->setCreator($full_name)
        ->setLastModifiedBy($full_name)
        ->setTitle("Agency Earnings Report")
        ->setSubject("Agency Earnings")
        ->setDescription("Agency earnings report generated on " . date('Y-m-d H:i:s'));
    
    // Add header row
    $sheet->setCellValue('A1', 'File Number');
    $sheet->setCellValue('B1', 'Customer');
    $sheet->setCellValue('C1', 'Consignee');
    $sheet->setCellValue('D1', 'Date');
    $sheet->setCellValue('E1', 'Destination');
    $sheet->setCellValue('F1', 'Type');
    $sheet->setCellValue('G1', 'Currency');
    $sheet->setCellValue('H1', 'Agency Fee');
    
    // Style the header row
    $headerStyle = [
        'font' => [
            'bold' => true,
            'color' => ['rgb' => 'FFFFFF'],
        ],
        'fill' => [
            'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
            'startColor' => ['rgb' => '4285F4'],
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];
    
    $sheet->getStyle('A1:H1')->applyFromArray($headerStyle);
    
    // Add data rows
    $row = 2;
    foreach ($records as $record) {
        $sheet->setCellValue('A' . $row, $record['file_number']);
        $sheet->setCellValue('B' . $row, $record['customer_name'] ?: 'Unknown');
        $sheet->setCellValue('C' . $row, $record['consignee_name']);
        $sheet->setCellValue('D' . $row, date('Y-m-d', strtotime($record['created_date'])));
        $sheet->setCellValue('E' . $row, $record['destination']);
        $sheet->setCellValue('F' . $row, $record['shipment_type']);
        $sheet->setCellValue('G' . $row, $record['currency']);
        $sheet->setCellValue('H' . $row, $record['agency_fee']);
        
        // Format the currency column
        $sheet->getStyle('H' . $row)
            ->getNumberFormat()
            ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
        
        // Alternate row styling
        if ($row % 2 == 0) {
            $sheet->getStyle('A' . $row . ':H' . $row)->applyFromArray([
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => 'F3F3F3'],
                ],
            ]);
        }
        
        $row++;
    }
    
    // Add summary data
    $row += 2;
    $sheet->setCellValue('A' . $row, 'Summary Statistics');
    $sheet->getStyle('A' . $row)->getFont()->setBold(true);
    $sheet->getStyle('A' . $row)->getFont()->setSize(14);
    $sheet->mergeCells('A' . $row . ':H' . $row);
    
    $row += 2;
    $sheet->setCellValue('A' . $row, 'Total Earnings (USD):');
    $sheet->setCellValue('B' . $row, $total_earnings);
    $sheet->getStyle('B' . $row)
        ->getNumberFormat()
        ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
    
    $row += 2;
    $sheet->setCellValue('A' . $row, 'Earnings by Currency');
    $sheet->getStyle('A' . $row)->getFont()->setBold(true);
    $sheet->mergeCells('A' . $row . ':C' . $row);
    
    foreach ($earnings_by_currency as $currency => $amount) {
        $row++;
        $sheet->setCellValue('A' . $row, $currency);
        $sheet->setCellValue('B' . $row, $amount);
    }
    
    // Auto-size columns
    foreach (range('A', 'H') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }
    
    // Save spreadsheet and send to browser
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Agency_Earnings_Report_' . date('Y-m-d') . '.xlsx"');
    header('Cache-Control: max-age=0');
    $writer->save('php://output');
    exit;
}

// Format for monthly data (for chart)
$months_labels = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
$monthly_data = [];
for ($i = 1; $i <= 12; $i++) {
    $monthly_data[] = $earnings_by_month[$i];
}

// Format for customer data (for chart)
$customer_labels = array_keys($earnings_by_customer);
$customer_data = array_values($earnings_by_customer);

// Limit to top 5 customers
if (count($customer_labels) > 5) {
    $customer_labels = array_slice($customer_labels, 0, 5);
    $customer_data = array_slice($customer_data, 0, 5);
}

// Format for destination data (for chart)
$destination_labels = array_keys($earnings_by_destination);
$destination_data = array_values($earnings_by_destination);

// Limit to top 5 destinations
if (count($destination_labels) > 5) {
    $destination_labels = array_slice($destination_labels, 0, 5);
    $destination_data = array_slice($destination_data, 0, 5);
}

// Format for shipment type data (for chart)
$shipment_type_labels = array_keys($earnings_by_shipment_type);
$shipment_type_data = array_values($earnings_by_shipment_type);
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Agency Earnings Report')); ?>
    <?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>
        .card-header .form-select, .card-header .form-control {
            margin-bottom: 0.5rem;
        }
        
        .statistic-card {
            transition: transform 0.3s, box-shadow 0.3s;
        }
        
        .statistic-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 20px rgba(0, 0, 0, 0.1);
        }
        
        .currency-badge {
            font-size: 0.9rem;
            padding: 0.25rem 0.5rem;
            border-radius: 0.25rem;
        }
        
        /* Custom color for positive values */
        .text-earnings {
            color: #2E8B57;
        }
        
        /* Export buttons */
        .export-button {
            margin-left: 0.5rem;
        }
        
        /* Chart height */
        .chart-container {
            height: 350px;
        }
        
        /* Card styles */
        .card-hover {
            transition: all 0.3s;
        }
        
        .card-hover:hover {
            transform: translateY(-5px);
            box-shadow: 0 5px 15px rgba(0, 0, 0, 0.1);
        }
        
        /* Summary statistic icon sizes */
        .summary-icon {
            font-size: 1.8rem;
        }
        
        /* Override opacity on hover for better readability */
        .table-hover tbody tr:hover {
            background-color: rgba(0, 0, 0, 0.05) !important;
        }
    </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' => 'Reports', 'title' => 'Agency Earnings Report')); ?>

                    <!-- Report filters -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header bg-light">
                                    <h4 class="card-title mb-3">Report Filters</h4>
                                    <form method="GET" action="" class="row align-items-end">
                                        <div class="col-md-2">
                                            <label for="year" class="form-label">Year</label>
                                            <select class="form-select" id="year" name="year">
                                                <option value="all">All Years</option>
                                                <?php foreach ($years as $year): ?>
                                                    <option value="<?php echo $year; ?>" <?php echo ($selected_year == $year) ? 'selected' : ''; ?>>
                                                        <?php echo $year; ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>
                                        <div class="col-md-2">
                                            <label for="month" class="form-label">Month</label>
                                            <select class="form-select" id="month" name="month">
                                                <option value="all">All Months</option>
                                                <?php for ($i = 1; $i <= 12; $i++): ?>
                                                    <option value="<?php echo $i; ?>" <?php echo ($selected_month == $i) ? 'selected' : ''; ?>>
                                                        <?php echo date('F', mktime(0, 0, 0, $i, 1)); ?>
                                                    </option>
                                                <?php endfor; ?>
                                            </select>
                                        </div>
                                        <div class="col-md-2">
                                            <label for="customer" class="form-label">Customer</label>
                                            <select class="form-select" id="customer" name="customer">
                                                <option value="all">All Customers</option>
                                                <?php foreach ($customers as $id => $name): ?>
                                                    <option value="<?php echo $id; ?>" <?php echo ($selected_customer == $id) ? 'selected' : ''; ?>>
                                                        <?php echo $name; ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>
                                        <div class="col-md-2">
                                            <label for="destination" class="form-label">Destination</label>
                                            <select class="form-select" id="destination" name="destination">
                                                <option value="all">All Destinations</option>
                                                <?php foreach ($destinations as $destination): ?>
                                                    <option value="<?php echo $destination; ?>" <?php echo ($selected_destination == $destination) ? 'selected' : ''; ?>>
                                                        <?php echo $destination; ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>
                                        <div class="col-md-2">
                                            <label for="currency" class="form-label">Currency</label>
                                            <select class="form-select" id="currency" name="currency">
                                                <option value="all">All Currencies</option>
                                                <?php foreach ($currencies as $currency): ?>
                                                    <option value="<?php echo $currency; ?>" <?php echo ($selected_currency == $currency) ? 'selected' : ''; ?>>
                                                        <?php echo $currency; ?>
                                                    </option>
                                                <?php endforeach; ?>
                                            </select>
                                        </div>
                                        <div class="col-md-2">
                                            <button type="submit" class="btn btn-primary w-100">
                                                <i class="ri-filter-2-line align-middle me-1"></i> Apply Filters
                                            </button>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Summary Statistics -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="card card-hover border-primary">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-md flex-shrink-0">
                                            <div class="avatar-title bg-light text-primary rounded-circle">
                                                <i class="ri-money-dollar-circle-line summary-icon"></i>
                                            </div>
                                        </div>
                                        <div class="flex-grow-1 ms-3">
                                            <p class="text-muted mb-2">Total Agency Earnings</p>
                                            <h4 class="mb-0 text-earnings">$<?php echo number_format($total_earnings, 2); ?></h4>
                                            <p class="text-muted mt-2 mb-0">
                                                <span class="text-primary">
                                                    <i class="ri-calendar-line align-middle me-1"></i>
                                                    <?php echo ($selected_month != 'all' ? date('F', mktime(0, 0, 0, $selected_month, 1)) : 'All months'); ?> 
                                                    <?php echo $selected_year; ?>
                                                </span>
                                            </p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-hover border-success">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-md flex-shrink-0">
                                            <div class="avatar-title bg-light text-success rounded-circle">
                                                <i class="ri-ship-line summary-icon"></i>
                                            </div>
                                        </div>
                                        <div class="flex-grow-1 ms-3">
                                            <p class="text-muted mb-2">Files Processed</p>
                                            <h4 class="mb-0"><?php echo count($records); ?></h4>
                                            <p class="text-muted mt-2 mb-0">
                                                <?php if(count($records) > 0): ?>
                                                    <span class="text-success">
                                                        <i class="ri-exchange-dollar-line align-middle me-1"></i>
                                                        Avg: $<?php echo number_format($total_earnings / count($records), 2); ?> per file
                                                    </span>
                                                <?php else: ?>
                                                    <span class="text-muted">
                                                        <i class="ri-information-line align-middle me-1"></i>
                                                        No data available
                                                    </span>
                                                <?php endif; ?>
                                            </p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-hover border-info">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-md flex-shrink-0">
                                            <div class="avatar-title bg-light text-info rounded-circle">
                                                <i class="ri-truck-line summary-icon"></i>
                                            </div>
                                        </div>
                                        <div class="flex-grow-1 ms-3">
                                            <p class="text-muted mb-2">Shipment Types</p>
                                            <div class="mb-0">
                                                <span class="badge bg-soft-primary me-1">Transit: $<?php echo number_format($earnings_by_shipment_type['Transit'], 2); ?></span>
                                                <span class="badge bg-soft-success me-1">Local: $<?php echo number_format($earnings_by_shipment_type['Local'], 2); ?></span>
                                            </div>
                                            <p class="text-muted mt-2 mb-0">
                                                <span class="text-info">
                                                    <i class="ri-globe-line align-middle me-1"></i>
                                                    <?php echo count($destinations); ?> destinations served
                                                </span>
                                            </p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card card-hover border-warning">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="avatar-md flex-shrink-0">
                                            <div class="avatar-title bg-light text-warning rounded-circle">
                                                <i class="ri-currency-line summary-icon"></i>
                                            </div>
                                        </div>
                                        <div class="flex-grow-1 ms-3">
                                            <p class="text-muted mb-2">Earnings by Currency</p>
                                            <div class="mb-0">
                                                <?php foreach ($earnings_by_currency as $currency => $amount): ?>
                                                    <span class="badge bg-soft-warning me-1 currency-badge">
                                                        <?php echo $currency; ?>: <?php echo number_format($amount, 2); ?>
                                                    </span>
                                                <?php endforeach; ?>
                                            </div>
                                            <p class="text-muted mt-2 mb-0">
                                                <span class="text-warning">
                                                    <i class="ri-exchange-dollar-line align-middle me-1"></i>
                                                    <?php echo count($earnings_by_currency); ?> currencies
                                                </span>
                                            </p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Export buttons -->
                    <?php if (hasPermission($role_id, 'export')): ?>
                    <div class="row mb-3">
                        <div class="col-12 text-end">
                            <a href="<?php echo $_SERVER['REQUEST_URI'] . (strpos($_SERVER['REQUEST_URI'], '?') !== false ? '&' : '?') . 'export=pdf'; ?>" class="btn btn-danger export-button">
                                <i class="ri-file-pdf-line align-middle me-1"></i> Export to PDF
                            </a>
                            <a href="<?php echo $_SERVER['REQUEST_URI'] . (strpos($_SERVER['REQUEST_URI'], '?') !== false ? '&' : '?') . 'export=excel'; ?>" class="btn btn-success export-button">
                                <i class="ri-file-excel-line align-middle me-1"></i> Export to Excel
                            </a>
                        </div>
                    </div>
                    <?php endif; ?>

                    <!-- Charts section -->
                    <div class="row">
                        <!-- Monthly earnings chart -->
                        <div class="col-xl-6">
                            <div class="card card-hover">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Monthly Earnings Trend</h4>
                                    <div id="monthlyEarningsChart" class="chart-container"></div>
                                </div>
                            </div>
                        </div>

                        <!-- Top customers chart -->
                        <div class="col-xl-6">
                            <div class="card card-hover">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Top Customers by Earnings</h4>
                                    <div id="topCustomersChart" class="chart-container"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <!-- Top destinations chart -->
                        <div class="col-xl-6">
                            <div class="card card-hover">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Top Destinations by Earnings</h4>
                                    <div id="topDestinationsChart" class="chart-container"></div>
                                </div>
                            </div>
                        </div>

                        <!-- Shipment type chart -->
                        <div class="col-xl-6">
                            <div class="card card-hover">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Earnings by Shipment Type</h4>
                                    <div id="shipmentTypeChart" class="chart-container"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Detailed data table -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card card-hover">
                                <div class="card-body">
                                    <h4 class="card-title mb-4">Detailed Earnings Data</h4>
                                    
                                    <table id="earningsTable" class="table table-striped table-bordered dt-responsive nowrap">
                                        <thead class="table-light">
                                            <tr>
                                                <th>File Number</th>
                                                <th>Customer</th>
                                                <th>Consignee</th>
                                                <th>Date</th>
                                                <th>Destination</th>
                                                <th>Type</th>
                                                <th>Currency</th>
                                                <th>Agency Fee</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php if (count($records) > 0): ?>
                                                <?php foreach ($records as $record): ?>
                                                <tr>
                                                    <td>
                                                        <a href="view_file.php?id=<?php echo $record['file_number']; ?>" class="text-primary">
                                                            <?php echo $record['file_number']; ?>
                                                        </a>
                                                    </td>
                                                    <td><?php echo $record['customer_name'] ?: 'Unknown'; ?></td>
                                                    <td><?php echo $record['consignee_name']; ?></td>
                                                    <td><?php echo date('Y-m-d', strtotime($record['created_date'])); ?></td>
                                                    <td><?php echo $record['destination']; ?></td>
                                                    <td>
                                                        <span class="badge bg-<?php echo $record['shipment_type'] == 'Transit' ? 'primary' : 'success'; ?>">
                                                            <?php echo $record['shipment_type']; ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-soft-warning text-warning">
                                                            <?php echo $record['currency']; ?>
                                                        </span>
                                                    </td>
                                                    <td class="text-end fw-medium text-earnings">
                                                        <?php echo $record['currency']; ?> <?php echo number_format($record['agency_fee'], 2); ?>
                                                    </td>
                                                </tr>
                                                <?php endforeach; ?>
                                            <?php else: ?>
                                                <tr>
                                                    <td colspan="8" class="text-center">No data available for the selected filters</td>
                                                </tr>
                                            <?php endif; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>
    
    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <script src="assets/js/app.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<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.5.0/js/dataTables.responsive.min.js"></script>
<script src="https://cdn.datatables.net/responsive/2.5.0/js/responsive.bootstrap5.min.js"></script>
<link href="assets/libs/sweetalert2/sweetalert2.min.css" rel="stylesheet">
<script src="assets/libs/sweetalert2/sweetalert2.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>
    
    <script>
        $(document).ready(function() {
            // Initialize DataTable
            $('#earningsTable').DataTable({
                lengthChange: true,
                buttons: ['copy', 'excel', 'pdf', 'colvis'],
                responsive: true,
                order: [[3, 'desc']], // Order by date by default
                language: {
                    search: "<i class='ri-search-line'></i>",
                    searchPlaceholder: "Search records...",
                    paginate: {
                        previous: "<i class='mdi mdi-chevron-left'>",
                        next: "<i class='mdi mdi-chevron-right'>"
                    }
                },
                drawCallback: function() {
                    $('.dataTables_paginate > .pagination').addClass('pagination-rounded');
                    $('.dataTables_length select').addClass('form-select form-select-sm');
                }
            });
            
            // Monthly Earnings Chart
            var monthlyOptions = {
                series: [{
                    name: 'Agency Earnings',
                    data: <?php echo json_encode($monthly_data); ?>
                }],
                chart: {
                    type: 'bar',
                    height: 350,
                    toolbar: {
                        show: false
                    }
                },
                plotOptions: {
                    bar: {
                        borderRadius: 4,
                        columnWidth: '60%',
                    }
                },
                dataLabels: {
                    enabled: false
                },
                xaxis: {
                    categories: <?php echo json_encode($months_labels); ?>,
                    axisBorder: {
                        show: false
                    }
                },
                yaxis: {
                    title: {
                        text: 'Earnings (USD)',
                    },
                    labels: {
                        formatter: function(val) {
                            return '$' + val.toFixed(2);
                        }
                    }
                },
                colors: ['#3b82f6'],
                fill: {
                    opacity: 0.9
                },
                grid: {
                    borderColor: '#f1f1f1',
                    padding: {
                        bottom: 10
                    }
                },
                tooltip: {
                    y: {
                        formatter: function(val) {
                            return '$' + val.toFixed(2);
                        }
                    }
                }
            };
            
            var monthlyChart = new ApexCharts(
                document.querySelector("#monthlyEarningsChart"), 
                monthlyOptions
            );
            monthlyChart.render();
            
            // Top Customers Chart
            var customersOptions = {
                series: [{
                    name: 'Earnings',
                    data: <?php echo json_encode($customer_data); ?>
                }],
                chart: {
                    type: 'bar',
                    height: 350,
                    toolbar: {
                        show: false
                    }
                },
                plotOptions: {
                    bar: {
                        horizontal: true,
                        borderRadius: 4,
                        barHeight: '70%',
                        distributed: true
                    }
                },
                dataLabels: {
                    enabled: false
                },
                xaxis: {
                    categories: <?php echo json_encode($customer_labels); ?>,
                    labels: {
                        formatter: function(val) {
                            return '$' + val.toFixed(2);
                        }
                    }
                },
                colors: ['#0891b2', '#0e7490', '#155e75', '#164e63', '#083344'],
                legend: {
                    show: false
                },
                grid: {
                    borderColor: '#f1f1f1',
                    padding: {
                        left: 10
                    }
                },
                tooltip: {
                    y: {
                        formatter: function(val) {
                            return '$' + val.toFixed(2);
                        }
                    }
                }
            };
            
            var customersChart = new ApexCharts(
                document.querySelector("#topCustomersChart"), 
                customersOptions
            );
            customersChart.render();
            
            // Top Destinations Chart
            var destinationsOptions = {
                series: [{
                    name: 'Earnings',
                    data: <?php echo json_encode($destination_data); ?>
                }],
                chart: {
                    type: 'bar',
                    height: 350,
                    toolbar: {
                        show: false
                    }
                },
                plotOptions: {
                    bar: {
                        horizontal: true,
                        borderRadius: 4,
                        barHeight: '70%',
                        distributed: true
                    }
                },
                dataLabels: {
                    enabled: false
                },
                xaxis: {
                    categories: <?php echo json_encode($destination_labels); ?>,
                    labels: {
                        formatter: function(val) {
                            return '$' + val.toFixed(2);
                        }
                    }
                },
                colors: ['#14b8a6', '#0d9488', '#0f766e', '#115e59', '#134e4a'],
                legend: {
                    show: false
                },
                grid: {
                    borderColor: '#f1f1f1',
                    padding: {
                        left: 10
                    }
                },
                tooltip: {
                    y: {
                        formatter: function(val) {
                            return '$' + val.toFixed(2);
                        }
                    }
                }
            };
            
            var destinationsChart = new ApexCharts(
                document.querySelector("#topDestinationsChart"), 
                destinationsOptions
            );
            destinationsChart.render();
            
            // Shipment Type Chart
            var shipmentTypeOptions = {
                series: <?php echo json_encode($shipment_type_data); ?>,
                chart: {
                    type: 'donut',
                    height: 350
                },
                labels: <?php echo json_encode($shipment_type_labels); ?>,
                colors: ['#3b82f6', '#22c55e'],
                plotOptions: {
                    pie: {
                        donut: {
                            size: '65%',
                            labels: {
                                show: true,
                                name: {
                                    show: true
                                },
                                value: {
                                    show: true,
                                    formatter: function(val) {
                                        return '$' + val.toFixed(2);
                                    }
                                },
                                total: {
                                    show: true,
                                    label: 'Total',
                                    formatter: function(w) {
                                        return '$' + w.globals.seriesTotals.reduce((a, b) => a + b, 0).toFixed(2);
                                    }
                                }
                            }
                        }
                    }
                },
                dataLabels: {
                    enabled: false
                },
                legend: {
                    position: 'bottom'
                },
                responsive: [{
                    breakpoint: 480,
                    options: {
                        chart: {
                            width: 320
                        },
                        legend: {
                            position: 'bottom'
                        }
                    }
                }]
            };
            
            var shipmentTypeChart = new ApexCharts(
                document.querySelector("#shipmentTypeChart"), 
                shipmentTypeOptions
            );
            shipmentTypeChart.render();
        });
    </script>
</body>
</html>