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

// Set timezone to East Africa Time
date_default_timezone_set('Africa/Nairobi');

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

// Fetch user details
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT u.*, ur.role_name 
                     FROM users u 
                     INNER JOIN user_roles ur ON u.role_id = ur.role_id 
                     WHERE u.user_id = ?";
$stmt_user = $conn->prepare($sql_user_details);
$stmt_user->bind_param("i", $user_id);
$stmt_user->execute();
$user_details = $stmt_user->get_result()->fetch_assoc();

if ($user_details) {
    $username = $user_details['username'];
    $role_name = $user_details['role_name'];
    $full_name = $user_details['full_name'];
}

// Filter parameters
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-t');
$currency_filter = $_GET['currency'] ?? '';
$view_mode = $_GET['view_mode'] ?? 'detailed'; // detailed, summary, consolidated

// Get company base currency
$base_currency = $conn->query("SELECT setting_value FROM settings WHERE setting_key = 'default_currency'")->fetch_assoc()['setting_value'] ?? 'KES';

// Mock exchange rates (in a real system, these would come from an API or database)
$exchange_rates = [
    'KES' => 1.0000,
    'USD' => 0.0067,
    'EUR' => 0.0061,
    'GBP' => 0.0053,
    'UGX' => 24.85,
    'TZS' => 16.75,
    'RWF' => 8.95,
    'AED' => 0.0246,
    'CNY' => 0.048,
    'INR' => 0.558,
    'ZAR' => 0.122,
    'NGN' => 10.45,
    'GHS' => 0.081,
    'ETB' => 0.382,
    'EGP' => 0.328,
    'MAD' => 0.067,
    'DZD' => 0.905
];

// Function to convert currency
function convertCurrency($amount, $from_currency, $to_currency, $rates) {
    if ($from_currency === $to_currency) return $amount;
    
    // Convert to base currency (KES) first, then to target currency
    $kes_amount = $amount / ($rates[$from_currency] ?? 1);
    return $kes_amount * ($rates[$to_currency] ?? 1);
}

// Function to format currency with proper symbol
function formatCurrencyAmount($amount, $currency) {
    $symbols = [
        'KES' => 'KSh ',
        'USD' => '$',
        'EUR' => '€',
        'GBP' => '£',
        'UGX' => 'USh ',
        'TZS' => 'TSh ',
        'RWF' => 'RF ',
        'AED' => 'AED ',
        'CNY' => '¥',
        'INR' => '₹',
        'ZAR' => 'R ',
        'NGN' => '₦',
        'GHS' => 'GH₵',
        'ETB' => 'Br ',
        'EGP' => 'E£',
        'MAD' => 'MAD ',
        'DZD' => 'DA '
    ];
    
    $symbol = $symbols[$currency] ?? $currency . ' ';
    return $symbol . number_format($amount, 2);
}

// Build where conditions
$where_conditions = ["s.booking_date BETWEEN ? AND ?"];
$params = [$date_from, $date_to];
$param_types = 'ss';

if (!empty($currency_filter)) {
    $where_conditions[] = "s.currency = ?";
    $params[] = $currency_filter;
    $param_types .= 's';
}

$where_clause = 'WHERE ' . implode(' AND ', $where_conditions);

// Get revenue by currency from shipments
$sql_revenue = "SELECT 
                  s.currency,
                  COUNT(*) as shipment_count,
                  SUM(s.freight_charges) as freight_charges,
                  SUM(s.fuel_surcharge) as fuel_surcharge,
                  SUM(s.insurance_charges) as insurance_charges,
                  SUM(s.handling_charges) as handling_charges,
                  SUM(s.customs_charges) as customs_charges,
                  SUM(s.other_charges) as other_charges,
                  SUM(s.tax_amount) as tax_amount,
                  SUM(s.total_charges) as total_revenue,
                  SUM(CASE WHEN s.payment_status = 'PAID' THEN s.total_charges ELSE 0 END) as paid_revenue,
                  SUM(CASE WHEN s.payment_status IN ('PENDING', 'PARTIAL', 'OVERDUE') THEN s.total_charges ELSE 0 END) as outstanding_revenue
                FROM shipments s
                $where_clause
                GROUP BY s.currency
                ORDER BY s.currency";

$stmt_revenue = $conn->prepare($sql_revenue);
$stmt_revenue->bind_param($param_types, ...$params);
$stmt_revenue->execute();
$revenue_data = $stmt_revenue->get_result();

// Get service-wise revenue breakdown
$sql_service_revenue = "SELECT 
                         s.currency,
                         st.service_name,
                         st.service_category,
                         COUNT(*) as shipment_count,
                         SUM(s.total_charges) as total_revenue,
                         AVG(s.total_charges) as avg_revenue,
                         SUM(s.chargeable_weight) as total_weight
                       FROM shipments s
                       LEFT JOIN service_types st ON s.service_id = st.service_id
                       $where_clause
                       GROUP BY s.currency, st.service_id, st.service_name, st.service_category
                       ORDER BY s.currency, total_revenue DESC";

$stmt_service = $conn->prepare($sql_service_revenue);
$stmt_service->bind_param($param_types, ...$params);
$stmt_service->execute();
$service_revenue = $stmt_service->get_result();

// Get route-wise profitability
$sql_route_profit = "SELECT 
                       s.currency,
                       ol.location_name as origin,
                       ol.city as origin_city,
                       oc.country_name as origin_country,
                       dl.location_name as destination,
                       dl.city as destination_city,
                       dc.country_name as destination_country,
                       COUNT(*) as shipment_count,
                       SUM(s.total_charges) as total_revenue,
                       AVG(s.total_charges) as avg_revenue_per_shipment,
                       SUM(s.chargeable_weight) as total_weight,
                       AVG(s.chargeable_weight) as avg_weight_per_shipment
                     FROM shipments s
                     LEFT JOIN locations ol ON s.origin_location_id = ol.location_id
                     LEFT JOIN countries oc ON ol.country_id = oc.country_id
                     LEFT JOIN locations dl ON s.destination_location_id = dl.location_id
                     LEFT JOIN countries dc ON dl.country_id = dc.country_id
                     $where_clause
                     GROUP BY s.currency, s.origin_location_id, s.destination_location_id
                     HAVING shipment_count > 0
                     ORDER BY s.currency, total_revenue DESC";

$stmt_route = $conn->prepare($sql_route_profit);
$stmt_route->bind_param($param_types, ...$params);
$stmt_route->execute();
$route_profit = $stmt_route->get_result();

// Calculate consolidated totals in base currency
$consolidated_totals = [
    'total_revenue' => 0,
    'total_freight' => 0,
    'total_surcharges' => 0,
    'total_tax' => 0,
    'paid_revenue' => 0,
    'outstanding_revenue' => 0,
    'shipment_count' => 0
];

// Store individual currency data
$currency_data = [];

// Get unique currencies for filter dropdown
$sql_currencies = "SELECT DISTINCT currency FROM shipments WHERE currency IS NOT NULL ORDER BY currency";
$currencies = $conn->query($sql_currencies);

$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Profit & Loss Statement - Freight Management System')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <!-- DataTables CSS -->
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.bootstrap5.min.css">
    <!-- SweetAlert2 CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.min.css">
    <!-- Remix Icons -->
    <link href="https://cdn.jsdelivr.net/npm/remixicon@3.5.0/fonts/remixicon.css" rel="stylesheet">
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    
    <style>
        .page-content {
            padding: 1.5rem;
        }
        
        .card {
            border: none;
            box-shadow: 0 0 20px rgba(0,0,0,0.1);
            border-radius: 10px;
        }
        
        .card-header {
            background: linear-gradient(45deg, #f8f9fa, #e9ecef);
            border-bottom: 1px solid #dee2e6;
            border-radius: 10px 10px 0 0;
        }
        
        .filter-card {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 12px;
        }
        
        .currency-card {
            background: white;
            border: 2px solid #e9ecef;
            border-radius: 12px;
            padding: 1.5rem;
            margin-bottom: 1.5rem;
            transition: all 0.3s ease;
            position: relative;
        }
        
        .currency-card:hover {
            transform: translateY(-2px);
            box-shadow: 0 8px 25px rgba(0,0,0,0.15);
        }
        
        .currency-card.base-currency {
            border-color: #28a745;
            background: linear-gradient(135deg, #f8fff9 0%, #e8f5e8 100%);
        }
        
        .currency-header {
            display: flex;
            justify-content: between;
            align-items: center;
            margin-bottom: 1rem;
        }
        
        .currency-flag {
            width: 40px;
            height: 30px;
            border-radius: 4px;
            margin-right: 1rem;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: bold;
            color: white;
            font-size: 0.8rem;
        }
        
        .flag-kes { background: #006633; }
        .flag-usd { background: #b22234; }
        .flag-eur { background: #003399; }
        .flag-gbp { background: #012169; }
        .flag-ugx { background: #d90000; }
        .flag-tzs { background: #1eb53a; }
        .flag-rwf { background: #00a1de; }
        .flag-default { background: #6c757d; }
        
        .revenue-breakdown {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(120px, 1fr));
            gap: 1rem;
            margin-bottom: 1rem;
        }
        
        .breakdown-item {
            text-align: center;
            padding: 0.75rem;
            background: #f8f9fa;
            border-radius: 6px;
        }
        
        .breakdown-amount {
            font-family: 'Courier New', monospace;
            font-weight: 700;
            font-size: 1.1rem;
            margin-bottom: 0.25rem;
        }
        
        .breakdown-label {
            font-size: 0.8rem;
            color: #6c757d;
        }
        
        .consolidated-summary {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 12px;
            padding: 2rem;
            margin-bottom: 2rem;
        }
        
        .table th {
            background-color: #f8f9fa;
            font-weight: 600;
            border-top: none;
        }
        
        .amount-cell {
            font-family: 'Courier New', monospace;
            font-weight: 600;
        }
        
        .positive-amount { color: #28a745; }
        .negative-amount { color: #dc3545; }
        .neutral-amount { color: #6c757d; }
        
        .exchange-rate-info {
            position: absolute;
            top: 10px;
            right: 15px;
            font-size: 0.75rem;
            color: #6c757d;
            background: rgba(255,255,255,0.9);
            padding: 0.25rem 0.5rem;
            border-radius: 4px;
        }
        
        .chart-container {
            position: relative;
            height: 400px;
            margin: 1rem 0;
        }
        
        @media print {
            .no-print { display: none !important; }
            .currency-card { page-break-inside: avoid; }
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Page Title -->
                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Profit & Loss Statement</h4>
                                <div class="page-title-right">
                                    <ol class="breadcrumb m-0">
                                        <li class="breadcrumb-item"><a href="index.php">Dashboard</a></li>
                                        <li class="breadcrumb-item active">P&L Statement</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Filters -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card filter-card no-print">
                                <div class="card-header">
                                    <h4 class="card-title mb-0 text-white">
                                        <i class="ri-filter-line me-2"></i>Report Filters
                                    </h4>
                                </div>
                                <div class="card-body">
                                    <form method="GET" class="row g-3">
                                        <div class="col-md-3">
                                            <label class="form-label text-white">Date From</label>
                                            <input type="date" class="form-control" name="date_from" value="<?php echo $date_from; ?>">
                                        </div>
                                        <div class="col-md-3">
                                            <label class="form-label text-white">Date To</label>
                                            <input type="date" class="form-control" name="date_to" value="<?php echo $date_to; ?>">
                                        </div>
                                        <div class="col-md-3">
                                            <label class="form-label text-white">Currency</label>
                                            <select class="form-control" name="currency">
                                                <option value="">All Currencies</option>
                                                <?php while ($curr = $currencies->fetch_assoc()): ?>
                                                <option value="<?php echo $curr['currency']; ?>" <?php echo $currency_filter === $curr['currency'] ? 'selected' : ''; ?>>
                                                    <?php echo $curr['currency']; ?>
                                                </option>
                                                <?php endwhile; ?>
                                            </select>
                                        </div>
                                        <div class="col-md-3">
                                            <label class="form-label text-white">View Mode</label>
                                            <select class="form-control" name="view_mode">
                                                <option value="detailed" <?php echo $view_mode === 'detailed' ? 'selected' : ''; ?>>Detailed</option>
                                                <option value="summary" <?php echo $view_mode === 'summary' ? 'selected' : ''; ?>>Summary</option>
                                                <option value="consolidated" <?php echo $view_mode === 'consolidated' ? 'selected' : ''; ?>>Consolidated (<?php echo $base_currency; ?>)</option>
                                            </select>
                                        </div>
                                        <div class="col-12">
                                            <button type="submit" class="btn btn-light">
                                                <i class="ri-search-line me-1"></i> Generate Report
                                            </button>
                                            <button type="button" class="btn btn-outline-light" onclick="exportReport()">
                                                <i class="ri-download-line me-1"></i> Export
                                            </button>
                                            <button type="button" class="btn btn-outline-light" onclick="window.print()">
                                                <i class="ri-printer-line me-1"></i> Print
                                            </button>
                                        </div>
                                    </form>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Exchange Rates Information -->
                    <div class="row no-print">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">
                                        <i class="ri-exchange-line me-2"></i>Current Exchange Rates
                                        <small class="text-muted">(Base: <?php echo $base_currency; ?>)</small>
                                    </h4>
                                </div>
                                <div class="card-body">
                                    <div class="row">
                                        <?php foreach (array_slice($exchange_rates, 0, 8, true) as $curr => $rate): ?>
                                        <div class="col-xl-3 col-md-6 col-6 mb-3">
                                            <div class="text-center p-2 border rounded">
                                                <div class="currency-flag flag-<?php echo strtolower($curr); ?>"><?php echo $curr; ?></div>
                                                <small class="text-muted">1 <?php echo $base_currency; ?> = <?php echo number_format($rate, 4); ?> <?php echo $curr; ?></small>
                                            </div>
                                        </div>
                                        <?php endforeach; ?>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Revenue by Currency -->
                    <div class="row">
                        <div class="col-12">
                            <h5 class="mb-3">Revenue Analysis by Currency</h5>
                        </div>
                        
                        <?php 
                        $revenue_data->data_seek(0); // Reset pointer
                        while ($revenue = $revenue_data->fetch_assoc()): 
                            $currency = $revenue['currency'];
                            $is_base = ($currency === $base_currency);
                            
                            // Store for consolidation
                            $currency_data[$currency] = $revenue;
                            
                            // Add to consolidated totals
                            $converted_revenue = convertCurrency($revenue['total_revenue'], $currency, $base_currency, $exchange_rates);
                            $consolidated_totals['total_revenue'] += $converted_revenue;
                            $consolidated_totals['total_freight'] += convertCurrency($revenue['freight_charges'], $currency, $base_currency, $exchange_rates);
                            $consolidated_totals['paid_revenue'] += convertCurrency($revenue['paid_revenue'], $currency, $base_currency, $exchange_rates);
                            $consolidated_totals['outstanding_revenue'] += convertCurrency($revenue['outstanding_revenue'], $currency, $base_currency, $exchange_rates);
                            $consolidated_totals['shipment_count'] += $revenue['shipment_count'];
                        ?>
                        <div class="col-xl-6 col-lg-12">
                            <div class="currency-card <?php echo $is_base ? 'base-currency' : ''; ?>">
                                <?php if (!$is_base): ?>
                                <div class="exchange-rate-info">
                                    1 <?php echo $currency; ?> = <?php echo number_format(1 / $exchange_rates[$currency], 4); ?> <?php echo $base_currency; ?>
                                </div>
                                <?php endif; ?>
                                
                                <div class="currency-header">
                                    <div class="d-flex align-items-center">
                                        <div class="currency-flag flag-<?php echo strtolower($currency); ?>"><?php echo $currency; ?></div>
                                        <div>
                                            <h5 class="mb-0"><?php echo $currency; ?> Operations</h5>
                                            <small class="text-muted"><?php echo number_format($revenue['shipment_count']); ?> shipments</small>
                                        </div>
                                    </div>
                                    <?php if ($is_base): ?>
                                    <span class="badge bg-success">Base Currency</span>
                                    <?php endif; ?>
                                </div>
                                
                                <div class="revenue-breakdown">
                                    <div class="breakdown-item">
                                        <div class="breakdown-amount positive-amount">
                                            <?php echo formatCurrencyAmount($revenue['total_revenue'], $currency); ?>
                                        </div>
                                        <div class="breakdown-label">Total Revenue</div>
                                    </div>
                                    
                                    <div class="breakdown-item">
                                        <div class="breakdown-amount">
                                            <?php echo formatCurrencyAmount($revenue['freight_charges'], $currency); ?>
                                        </div>
                                        <div class="breakdown-label">Freight Charges</div>
                                    </div>
                                    
                                    <div class="breakdown-item">
                                        <div class="breakdown-amount">
                                            <?php echo formatCurrencyAmount($revenue['fuel_surcharge'] + $revenue['other_charges'], $currency); ?>
                                        </div>
                                        <div class="breakdown-label">Surcharges</div>
                                    </div>
                                    
                                    <div class="breakdown-item">
                                        <div class="breakdown-amount">
                                            <?php echo formatCurrencyAmount($revenue['tax_amount'], $currency); ?>
                                        </div>
                                        <div class="breakdown-label">Taxes</div>
                                    </div>
                                </div>
                                
                                <div class="row">
                                    <div class="col-6">
                                        <div class="breakdown-item">
                                            <div class="breakdown-amount positive-amount">
                                                <?php echo formatCurrencyAmount($revenue['paid_revenue'], $currency); ?>
                                            </div>
                                            <div class="breakdown-label">Collected Revenue</div>
                                        </div>
                                    </div>
                                    <div class="col-6">
                                        <div class="breakdown-item">
                                            <div class="breakdown-amount <?php echo $revenue['outstanding_revenue'] > 0 ? 'negative-amount' : 'neutral-amount'; ?>">
                                                <?php echo formatCurrencyAmount($revenue['outstanding_revenue'], $currency); ?>
                                            </div>
                                            <div class="breakdown-label">Outstanding</div>
                                        </div>
                                    </div>
                                </div>
                                
                                <?php if (!$is_base): ?>
                                <div class="mt-3 pt-3 border-top">
                                    <small class="text-muted">
                                        <strong>Equivalent in <?php echo $base_currency; ?>:</strong>
                                        <?php echo formatCurrencyAmount(convertCurrency($revenue['total_revenue'], $currency, $base_currency, $exchange_rates), $base_currency); ?>
                                    </small>
                                </div>
                                <?php endif; ?>
                            </div>
                        </div>
                        <?php endwhile; ?>
                    </div>

                    <!-- Consolidated Summary -->
                    <?php if ($view_mode === 'consolidated' || $view_mode === 'detailed'): ?>
                    <div class="row">
                        <div class="col-12">
                            <div class="consolidated-summary">
                                <h4 class="text-white mb-3">
                                    <i class="ri-global-line me-2"></i>Consolidated Summary (<?php echo $base_currency; ?>)
                                </h4>
                                <div class="row">
                                    <div class="col-xl-3 col-md-6 mb-3">
                                        <div class="text-center">
                                            <h3 class="text-white mb-1"><?php echo formatCurrencyAmount($consolidated_totals['total_revenue'], $base_currency); ?></h3>
                                            <p class="text-white-50 mb-0">Total Revenue</p>
                                        </div>
                                    </div>
                                    <div class="col-xl-3 col-md-6 mb-3">
                                        <div class="text-center">
                                            <h3 class="text-white mb-1"><?php echo formatCurrencyAmount($consolidated_totals['paid_revenue'], $base_currency); ?></h3>
                                            <p class="text-white-50 mb-0">Collected Revenue</p>
                                        </div>
                                    </div>
                                    <div class="col-xl-3 col-md-6 mb-3">
                                        <div class="text-center">
                                            <h3 class="text-white mb-1"><?php echo formatCurrencyAmount($consolidated_totals['outstanding_revenue'], $base_currency); ?></h3>
                                            <p class="text-white-50 mb-0">Outstanding</p>
                                        </div>
                                    </div>
                                    <div class="col-xl-3 col-md-6 mb-3">
                                        <div class="text-center">
                                            <h3 class="text-white mb-1"><?php echo number_format($consolidated_totals['shipment_count']); ?></h3>
                                            <p class="text-white-50 mb-0">Total Shipments</p>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                    <?php endif; ?>

                    <!-- Service-wise Revenue -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">
                                        <i class="ri-service-line me-2"></i>Service-wise Revenue Analysis
                                    </h4>
                                </div>
                                <div class="card-body">
                                    <table id="serviceRevenueTable" class="table table-striped table-hover dt-responsive nowrap w-100">
                                        <thead>
                                            <tr>
                                                <th>Currency</th>
                                                <th>Service</th>
                                                <th>Category</th>
                                                <th>Shipments</th>
                                                <th>Total Revenue</th>
                                                <th>Avg Revenue</th>
                                                <th>Total Weight (KG)</th>
                                                <th>Revenue per KG</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php while ($service = $service_revenue->fetch_assoc()): ?>
                                            <tr>
                                                <td>
                                                    <span class="currency-flag flag-<?php echo strtolower($service['currency']); ?>"><?php echo $service['currency']; ?></span>
                                                </td>
                                                <td><?php echo $service['service_name']; ?></td>
                                                <td>
                                                    <span class="badge bg-primary"><?php echo $service['service_category']; ?></span>
                                                </td>
                                                <td><?php echo number_format($service['shipment_count']); ?></td>
                                                <td class="amount-cell positive-amount">
                                                    <?php echo formatCurrencyAmount($service['total_revenue'], $service['currency']); ?>
                                                </td>
                                                <td class="amount-cell">
                                                    <?php echo formatCurrencyAmount($service['avg_revenue'], $service['currency']); ?>
                                                </td>
                                                <td><?php echo number_format($service['total_weight'], 2); ?></td>
                                                <td class="amount-cell">
                                                <td class="amount-cell">
                                                    <?php 
                                                    $revenue_per_kg = $service['total_weight'] > 0 ? $service['total_revenue'] / $service['total_weight'] : 0;
                                                    echo formatCurrencyAmount($revenue_per_kg, $service['currency']);
                                                    ?>
                                                </td>
                                            </tr>
                                            <?php endwhile; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Route Profitability Analysis -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">
                                        <i class="ri-route-line me-2"></i>Route Profitability Analysis
                                    </h4>
                                </div>
                                <div class="card-body">
                                    <table id="routeProfitTable" class="table table-striped table-hover dt-responsive nowrap w-100">
                                        <thead>
                                            <tr>
                                                <th>Currency</th>
                                                <th>Origin</th>
                                                <th>Destination</th>
                                                <th>Shipments</th>
                                                <th>Total Revenue</th>
                                                <th>Avg Revenue</th>
                                                <th>Total Weight</th>
                                                <th>Avg Weight</th>
                                                <th>Revenue/KG</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php while ($route = $route_profit->fetch_assoc()): ?>
                                            <tr>
                                                <td>
                                                    <span class="currency-flag flag-<?php echo strtolower($route['currency']); ?>"><?php echo $route['currency']; ?></span>
                                                </td>
                                                <td>
                                                    <strong><?php echo $route['origin']; ?></strong>
                                                    <br><small class="text-muted"><?php echo $route['origin_city']; ?>, <?php echo $route['origin_country']; ?></small>
                                                </td>
                                                <td>
                                                    <strong><?php echo $route['destination']; ?></strong>
                                                    <br><small class="text-muted"><?php echo $route['destination_city']; ?>, <?php echo $route['destination_country']; ?></small>
                                                </td>
                                                <td><?php echo number_format($route['shipment_count']); ?></td>
                                                <td class="amount-cell positive-amount">
                                                    <?php echo formatCurrencyAmount($route['total_revenue'], $route['currency']); ?>
                                                </td>
                                                <td class="amount-cell">
                                                    <?php echo formatCurrencyAmount($route['avg_revenue_per_shipment'], $route['currency']); ?>
                                                </td>
                                                <td><?php echo number_format($route['total_weight'], 2); ?></td>
                                                <td><?php echo number_format($route['avg_weight_per_shipment'], 2); ?></td>
                                                <td class="amount-cell">
                                                    <?php 
                                                    $route_revenue_per_kg = $route['total_weight'] > 0 ? $route['total_revenue'] / $route['total_weight'] : 0;
                                                    echo formatCurrencyAmount($route_revenue_per_kg, $route['currency']);
                                                    ?>
                                                </td>
                                            </tr>
                                            <?php endwhile; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Revenue Charts -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Currency Distribution</h4>
                                </div>
                                <div class="card-body">
                                    <div class="chart-container">
                                        <canvas id="currencyChart"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Revenue Components</h4>
                                </div>
                                <div class="card-body">
                                    <div class="chart-container">
                                        <canvas id="revenueChart"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <!-- DataTables JS -->
    <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.print.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
    
    <!-- SweetAlert2 JS -->
    <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>
    
    <script>
        $(document).ready(function() {
            // Initialize DataTables
            $('#serviceRevenueTable').DataTable({
                responsive: true,
                pageLength: 25,
                order: [[4, 'desc']], // Sort by total revenue
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'excel',
                        text: '<i class="ri-file-excel-line"></i> Excel',
                        className: 'btn btn-success btn-sm'
                    },
                    {
                        extend: 'csv',
                        text: '<i class="ri-file-text-line"></i> CSV',
                        className: 'btn btn-info btn-sm'
                    },
                    {
                        extend: 'print',
                        text: '<i class="ri-printer-line"></i> Print',
                        className: 'btn btn-secondary btn-sm'
                    }
                ],
                columnDefs: [
                    { targets: [3, 6], className: 'text-center' },
                    { targets: [4, 5, 7], className: 'text-end' }
                ]
            });
            
            $('#routeProfitTable').DataTable({
                responsive: true,
                pageLength: 25,
                order: [[4, 'desc']], // Sort by total revenue
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'excel',
                        text: '<i class="ri-file-excel-line"></i> Excel',
                        className: 'btn btn-success btn-sm'
                    },
                    {
                        extend: 'csv',
                        text: '<i class="ri-file-text-line"></i> CSV',
                        className: 'btn btn-info btn-sm'
                    }
                ],
                columnDefs: [
                    { targets: [3], className: 'text-center' },
                    { targets: [4, 5, 6, 7, 8], className: 'text-end' }
                ]
            });
            
            // Initialize Charts
            initializeCharts();
        });
        
        function initializeCharts() {
            // Currency Distribution Chart
            const currencyCtx = document.getElementById('currencyChart').getContext('2d');
            const currencyData = <?php 
                $revenue_data->data_seek(0);
                $chart_data = [];
                while ($row = $revenue_data->fetch_assoc()) {
                    $chart_data[] = [
                        'currency' => $row['currency'],
                        'revenue' => $row['total_revenue'],
                        'shipments' => $row['shipment_count']
                    ];
                }
                echo json_encode($chart_data);
            ?>;
            
            new Chart(currencyCtx, {
                type: 'doughnut',
                data: {
                    labels: currencyData.map(item => item.currency),
                    datasets: [{
                        data: currencyData.map(item => item.revenue),
                        backgroundColor: [
                            '#667eea',
                            '#764ba2', 
                            '#f093fb',
                            '#f5576c',
                            '#4facfe',
                            '#00f2fe',
                            '#43e97b',
                            '#38f9d7'
                        ],
                        borderWidth: 2,
                        borderColor: '#fff'
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    plugins: {
                        legend: {
                            position: 'bottom'
                        },
                        tooltip: {
                            callbacks: {
                                label: function(context) {
                                    const currency = currencyData[context.dataIndex].currency;
                                    const revenue = context.parsed;
                                    const shipments = currencyData[context.dataIndex].shipments;
                                    return `${currency}: ${revenue.toLocaleString()} (${shipments} shipments)`;
                                }
                            }
                        }
                    }
                }
            });
            
            // Revenue Components Chart
            const revenueCtx = document.getElementById('revenueChart').getContext('2d');
            const consolidatedData = {
                freight: <?php echo $consolidated_totals['total_freight']; ?>,
                surcharges: <?php echo $consolidated_totals['total_revenue'] - $consolidated_totals['total_freight']; ?>,
                collected: <?php echo $consolidated_totals['paid_revenue']; ?>,
                outstanding: <?php echo $consolidated_totals['outstanding_revenue']; ?>
            };
            
            new Chart(revenueCtx, {
                type: 'bar',
                data: {
                    labels: ['Freight Charges', 'Surcharges & Fees', 'Collected Revenue', 'Outstanding'],
                    datasets: [{
                        label: 'Amount (<?php echo $base_currency; ?>)',
                        data: [consolidatedData.freight, consolidatedData.surcharges, consolidatedData.collected, consolidatedData.outstanding],
                        backgroundColor: ['#28a745', '#17a2b8', '#007bff', '#dc3545'],
                        borderColor: ['#1e7e34', '#138496', '#0056b3', '#bd2130'],
                        borderWidth: 1
                    }]
                },
                options: {
                    responsive: true,
                    maintainAspectRatio: false,
                    plugins: {
                        legend: {
                            display: false
                        }
                    },
                    scales: {
                        y: {
                            beginAtZero: true,
                            ticks: {
                                callback: function(value) {
                                    return value.toLocaleString();
                                }
                            }
                        }
                    }
                }
            });
        }
        
        function exportReport() {
            Swal.fire({
                title: 'Export P&L Report',
                text: 'Choose export format:',
                icon: 'question',
                showCancelButton: true,
                confirmButtonText: 'Excel',
                cancelButtonText: 'PDF',
                showDenyButton: true,
                denyButtonText: 'CSV'
            }).then((result) => {
                if (result.isConfirmed) {
                    // Export to Excel
                    exportToExcel();
                } else if (result.isDenied) {
                    // Export to CSV
                    exportToCSV();
                } else if (result.dismiss === Swal.DismissReason.cancel) {
                    // Export to PDF
                    Swal.fire('PDF export functionality can be implemented here', '', 'info');
                }
            });
        }
        
        function exportToExcel() {
            // Trigger Excel export from DataTables
            $('#serviceRevenueTable').DataTable().button('.buttons-excel').trigger();
        }
        
        function exportToCSV() {
            // Trigger CSV export from DataTables
            $('#serviceRevenueTable').DataTable().button('.buttons-csv').trigger();
        }
    </script>
    
    <script src="assets/js/app.js"></script>
</body>
</html>