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

// Set timezone
date_default_timezone_set('Africa/Nairobi');

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

// Check user permissions
$user_id = $_SESSION['user_id'];
$user_role = $_SESSION['role_name'] ?? 'User';

// Fetch user details
$sql_user = "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);
$stmt_user->bind_param("i", $user_id);
$stmt_user->execute();
$user_details = $stmt_user->get_result()->fetch_assoc();

if (!$user_details) {
    header('Location: logout.php');
    exit();
}

// Get currency conversion rates (simplified - in production, use real-time rates)
$currency_rates = [
    'USD' => 1.00,      // Base currency
    'KES' => 130.00,    // 1 USD = 130 KES
    'EUR' => 0.85,      // 1 USD = 0.85 EUR
    'GBP' => 0.73,      // 1 USD = 0.73 GBP
    'UGX' => 3700.00,   // 1 USD = 3700 UGX
    'TZS' => 2300.00,   // 1 USD = 2300 TZS
    'RWF' => 1250.00,   // 1 USD = 1250 RWF
    'AED' => 3.67       // 1 USD = 3.67 AED
];

// Convert to USD function
function convertToUSD($amount, $currency) {
    global $currency_rates;
    if ($currency === 'USD') return $amount;
    return $amount / ($currency_rates[$currency] ?? 1);
}

// Convert from USD function
function convertFromUSD($amount, $target_currency) {
    global $currency_rates;
    if ($target_currency === 'USD') return $amount;
    return $amount * ($currency_rates[$target_currency] ?? 1);
}

// Date filter handling
$date_filter = $_GET['period'] ?? 'today';
$custom_start = $_GET['start_date'] ?? '';
$custom_end = $_GET['end_date'] ?? '';

// Build date condition
$date_condition = "";
$params = [];
$param_types = "";

switch ($date_filter) {
    case 'today':
        $date_condition = "AND DATE(s.booking_date) = CURDATE()";
        break;
    case 'yesterday':
        $date_condition = "AND DATE(s.booking_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
        break;
    case 'this_week':
        $date_condition = "AND YEARWEEK(s.booking_date, 1) = YEARWEEK(CURDATE(), 1)";
        break;
    case 'last_week':
        $date_condition = "AND YEARWEEK(s.booking_date, 1) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 1 WEEK), 1)";
        break;
    case 'this_month':
        $date_condition = "AND MONTH(s.booking_date) = MONTH(CURDATE()) AND YEAR(s.booking_date) = YEAR(CURDATE())";
        break;
    case 'last_month':
        $date_condition = "AND MONTH(s.booking_date) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) AND YEAR(s.booking_date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))";
        break;
    case 'this_year':
        $date_condition = "AND YEAR(s.booking_date) = YEAR(CURDATE())";
        break;
    case 'custom':
        if ($custom_start && $custom_end) {
            $date_condition = "AND DATE(s.booking_date) BETWEEN ? AND ?";
            $params[] = $custom_start;
            $params[] = $custom_end;
            $param_types .= "ss";
        }
        break;
}

// Get revenue metrics by currency
$sql_revenue_by_currency = "
    SELECT 
        s.currency,
        COUNT(*) as total_shipments,
        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 = 'PENDING' THEN s.total_charges ELSE 0 END) as pending_revenue,
        SUM(CASE WHEN s.payment_status = 'PARTIAL' THEN s.total_charges ELSE 0 END) as partial_revenue,
        SUM(CASE WHEN s.payment_status = 'OVERDUE' THEN s.total_charges ELSE 0 END) as overdue_revenue,
        AVG(s.total_charges) as avg_shipment_value,
        SUM(s.actual_weight) as total_weight
    FROM shipments s 
    WHERE 1=1 $date_condition
    GROUP BY s.currency
    ORDER BY total_revenue DESC";

$stmt_revenue = $conn->prepare($sql_revenue_by_currency);
if (!empty($params)) {
    $stmt_revenue->bind_param($param_types, ...$params);
}
$stmt_revenue->execute();
$revenue_by_currency = $stmt_revenue->get_result();

// Calculate totals in USD
$total_usd_revenue = 0;
$total_usd_paid = 0;
$total_shipments = 0;
$currency_data = [];

while ($row = $revenue_by_currency->fetch_assoc()) {
    $usd_total = convertToUSD($row['total_revenue'], $row['currency']);
    $usd_paid = convertToUSD($row['paid_revenue'], $row['currency']);
    
    $total_usd_revenue += $usd_total;
    $total_usd_paid += $usd_paid;
    $total_shipments += $row['total_shipments'];
    
    $currency_data[] = array_merge($row, [
        'usd_total_revenue' => $usd_total,
        'usd_paid_revenue' => $usd_paid
    ]);
}

// Get service type performance
$sql_service_performance = "
    SELECT 
        st.service_name,
        st.service_code,
        COUNT(s.shipment_id) as shipment_count,
        s.currency,
        SUM(s.total_charges) as service_revenue,
        AVG(s.total_charges) as avg_revenue,
        SUM(s.actual_weight) as total_weight
    FROM shipments s
    INNER JOIN service_types st ON s.service_id = st.service_id
    WHERE 1=1 $date_condition
    GROUP BY st.service_id, st.service_name, st.service_code, s.currency
    ORDER BY service_revenue DESC";

$stmt_service = $conn->prepare($sql_service_performance);
if (!empty($params)) {
    $stmt_service->bind_param($param_types, ...$params);
}
$stmt_service->execute();
$service_performance = $stmt_service->get_result();

// Get daily revenue trends (last 30 days)
$sql_daily_trends = "
    SELECT 
        DATE(s.booking_date) as trend_date,
        s.currency,
        COUNT(*) as daily_shipments,
        SUM(s.total_charges) as daily_revenue,
        SUM(CASE WHEN s.payment_status = 'PAID' THEN s.total_charges ELSE 0 END) as daily_paid
    FROM shipments s
    WHERE s.booking_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY DATE(s.booking_date), s.currency
    ORDER BY trend_date DESC, s.currency";

$result_trends = $conn->query($sql_daily_trends);
$daily_trends = [];
while ($row = $result_trends->fetch_assoc()) {
    $daily_trends[] = $row;
}

// Get route performance
$sql_route_performance = "
    SELECT 
        CONCAT(ol.location_name, ' → ', dl.location_name) as route,
        ol.location_name as origin,
        dl.location_name as destination,
        COUNT(s.shipment_id) as shipment_count,
        s.currency,
        SUM(s.total_charges) as route_revenue,
        AVG(s.total_charges) as avg_revenue,
        SUM(s.actual_weight) as total_weight
    FROM shipments s
    INNER JOIN locations ol ON s.origin_location_id = ol.location_id
    INNER JOIN locations dl ON s.destination_location_id = dl.location_id
    WHERE 1=1 $date_condition
    GROUP BY ol.location_id, dl.location_id, ol.location_name, dl.location_name, s.currency
    ORDER BY route_revenue DESC
    LIMIT 10";

$stmt_routes = $conn->prepare($sql_route_performance);
if (!empty($params)) {
    $stmt_routes->bind_param($param_types, ...$params);
}
$stmt_routes->execute();
$route_performance = $stmt_routes->get_result();

// Get payment status distribution
$sql_payment_status = "
    SELECT 
        s.payment_status,
        s.currency,
        COUNT(*) as status_count,
        SUM(s.total_charges) as status_revenue
    FROM shipments s
    WHERE 1=1 $date_condition
    GROUP BY s.payment_status, s.currency
    ORDER BY s.payment_status";

$stmt_payment = $conn->prepare($sql_payment_status);
if (!empty($params)) {
    $stmt_payment->bind_param($param_types, ...$params);
}
$stmt_payment->execute();
$payment_status_data = $stmt_payment->get_result();

// Get top customers by revenue
$sql_top_customers = "
    SELECT 
        c.customer_code,
        c.company_name,
        c.first_name,
        c.last_name,
        c.customer_type,
        COUNT(s.shipment_id) as shipment_count,
        s.currency,
        SUM(s.total_charges) as customer_revenue,
        AVG(s.total_charges) as avg_shipment_value,
        MAX(s.booking_date) as last_shipment_date
    FROM shipments s
    LEFT JOIN customers c ON s.sender_customer_id = c.customer_id
    WHERE s.sender_customer_id IS NOT NULL $date_condition
    GROUP BY s.sender_customer_id, s.currency
    ORDER BY customer_revenue DESC
    LIMIT 15";

$stmt_customers = $conn->prepare($sql_top_customers);
if (!empty($params)) {
    $stmt_customers->bind_param($param_types, ...$params);
}
$stmt_customers->execute();
$top_customers = $stmt_customers->get_result();

$conn->close();

// Format currency function
/*function formatCurrency($amount, $currency = 'USD') {
    $symbols = [
        'USD' => '$',
        'KES' => 'KSh ',
        'EUR' => '€',
        'GBP' => '£',
        'UGX' => 'USh ',
        'TZS' => 'TSh ',
        'RWF' => 'RF ',
        'AED' => 'AED '
    ];
    
    $symbol = $symbols[$currency] ?? $currency . ' ';
    return $symbol . number_format($amount, 2);
}*/

// Get period display name
function getPeriodName($period) {
    $periods = [
        'today' => 'Today',
        'yesterday' => 'Yesterday', 
        'this_week' => 'This Week',
        'last_week' => 'Last Week',
        'this_month' => 'This Month',
        'last_month' => 'Last Month',
        'this_year' => 'This Year',
        'custom' => 'Custom Period'
    ];
    return $periods[$period] ?? 'All Time';
}
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Revenue Dashboard')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <!-- DataTables CSS -->
    <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap5.min.css" rel="stylesheet">
    
    <!-- ApexCharts CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.css">
    
    <!-- SweetAlert2 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.min.css" rel="stylesheet">
    
    <!-- Remix Icons -->
    <link href="https://cdn.jsdelivr.net/npm/remixicon@3.5.0/fonts/remixicon.css" rel="stylesheet">
    
    <style>
        .revenue-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 2px 8px rgba(0,0,0,0.1);
            border: 1px solid #e3e6f0;
            transition: all 0.3s ease;
            margin-bottom: 1.5rem;
        }
        
        .revenue-card:hover {
            box-shadow: 0 4px 15px rgba(0,0,0,0.15);
            transform: translateY(-2px);
        }
        
        .metric-card {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 15px;
            padding: 1.5rem;
            position: relative;
            overflow: hidden;
        }
        
        .metric-card::before {
            content: '';
            position: absolute;
            top: 0;
            right: 0;
            width: 100px;
            height: 100px;
            background: rgba(255,255,255,0.1);
            border-radius: 50%;
            transform: translate(30px, -30px);
        }
        
        .metric-value {
            font-size: 2rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
        }
        
        .metric-label {
            font-size: 0.9rem;
            opacity: 0.9;
            margin-bottom: 0.5rem;
        }
        
        .metric-change {
            font-size: 0.8rem;
            padding: 0.25rem 0.5rem;
            border-radius: 12px;
            background: rgba(255,255,255,0.2);
        }
        
        .currency-badge {
            display: inline-block;
            padding: 0.25rem 0.75rem;
            border-radius: 15px;
            font-size: 0.75rem;
            font-weight: 600;
            margin: 0.1rem;
        }
        
        .currency-usd { background: #e3f2fd; color: #1976d2; }
        .currency-kes { background: #e8f5e8; color: #2e7d32; }
        .currency-eur { background: #fff3e0; color: #ef6c00; }
        .currency-gbp { background: #f3e5f5; color: #7b1fa2; }
        
        .status-badge {
            padding: 0.25rem 0.75rem;
            border-radius: 12px;
            font-size: 0.75rem;
            font-weight: 600;
            text-transform: uppercase;
        }
        
        .status-paid { background: #d4edda; color: #155724; }
        .status-pending { background: #fff3cd; color: #856404; }
        .status-partial { background: #d1ecf1; color: #0c5460; }
        .status-overdue { background: #f8d7da; color: #721c24; }
        
        .chart-container {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 2px 8px rgba(0,0,0,0.1);
            margin-bottom: 1.5rem;
        }
        
        .filter-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 2px 8px rgba(0,0,0,0.1);
            margin-bottom: 1.5rem;
        }
        
        .export-btn {
            background: linear-gradient(45deg, #28a745, #20c997);
            color: white;
            border: none;
            padding: 0.5rem 1rem;
            border-radius: 8px;
            font-weight: 600;
            transition: all 0.3s ease;
        }
        
        .export-btn:hover {
            transform: translateY(-2px);
            box-shadow: 0 4px 12px rgba(40,167,69,0.3);
            color: white;
        }
        
        @media (max-width: 768px) {
            .metric-card {
                margin-bottom: 1rem;
            }
            
            .metric-value {
                font-size: 1.5rem;
            }
        }
    </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 Header -->
                    <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"><i class="ri-line-chart-line me-2"></i>Revenue Dashboard</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">Revenue Dashboard</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Filters -->
                    <div class="row">
                        <div class="col-12">
                            <div class="filter-card">
                                <form method="GET" class="row g-3 align-items-end">
                                    <div class="col-md-3">
                                        <label for="period" class="form-label">Time Period</label>
                                        <select class="form-select" id="period" name="period" onchange="toggleCustomDates()">
                                            <option value="today" <?php echo $date_filter == 'today' ? 'selected' : ''; ?>>Today</option>
                                            <option value="yesterday" <?php echo $date_filter == 'yesterday' ? 'selected' : ''; ?>>Yesterday</option>
                                            <option value="this_week" <?php echo $date_filter == 'this_week' ? 'selected' : ''; ?>>This Week</option>
                                            <option value="last_week" <?php echo $date_filter == 'last_week' ? 'selected' : ''; ?>>Last Week</option>
                                            <option value="this_month" <?php echo $date_filter == 'this_month' ? 'selected' : ''; ?>>This Month</option>
                                            <option value="last_month" <?php echo $date_filter == 'last_month' ? 'selected' : ''; ?>>Last Month</option>
                                            <option value="this_year" <?php echo $date_filter == 'this_year' ? 'selected' : ''; ?>>This Year</option>
                                            <option value="custom" <?php echo $date_filter == 'custom' ? 'selected' : ''; ?>>Custom Range</option>
                                        </select>
                                    </div>
                                    
                                    <div class="col-md-3" id="start-date-group" style="display: <?php echo $date_filter == 'custom' ? 'block' : 'none'; ?>;">
                                        <label for="start_date" class="form-label">Start Date</label>
                                        <input type="date" class="form-control" id="start_date" name="start_date" value="<?php echo $custom_start; ?>">
                                    </div>
                                    
                                    <div class="col-md-3" id="end-date-group" style="display: <?php echo $date_filter == 'custom' ? 'block' : 'none'; ?>;">
                                        <label for="end_date" class="form-label">End Date</label>
                                        <input type="date" class="form-control" id="end_date" name="end_date" value="<?php echo $custom_end; ?>">
                                    </div>
                                    
                                    <div class="col-md-3">
                                        <button type="submit" class="btn btn-primary me-2">
                                            <i class="ri-filter-3-line me-1"></i>Filter
                                        </button>
                                        <a href="revenue_dashboard.php" class="btn btn-secondary">
                                            <i class="ri-refresh-line me-1"></i>Reset
                                        </a>
                                    </div>
                                </form>
                            </div>
                        </div>
                    </div>

                    <!-- Summary Metrics -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="metric-card" style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);">
                                <div class="metric-value">$<?php echo number_format($total_usd_revenue, 2); ?></div>
                                <div class="metric-label">Total Revenue (USD Equivalent)</div>
                                <div class="metric-change">
                                    <i class="ri-arrow-up-line"></i> <?php echo getPeriodName($date_filter); ?>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="metric-card" style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);">
                                <div class="metric-value">$<?php echo number_format($total_usd_paid, 2); ?></div>
                                <div class="metric-label">Paid Revenue (USD Equivalent)</div>
                                <div class="metric-change">
                                    <i class="ri-check-line"></i> <?php echo $total_usd_revenue > 0 ? round(($total_usd_paid/$total_usd_revenue)*100, 1) : 0; ?>% Collection Rate
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="metric-card" style="background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);">
                                <div class="metric-value"><?php echo number_format($total_shipments); ?></div>
                                <div class="metric-label">Total Shipments</div>
                                <div class="metric-change">
                                    <i class="ri-ship-line"></i> Active Shipments
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="metric-card" style="background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%);">
                                <div class="metric-value">$<?php echo $total_shipments > 0 ? number_format($total_usd_revenue/$total_shipments, 2) : '0.00'; ?></div>
                                <div class="metric-label">Avg Revenue per Shipment (USD)</div>
                                <div class="metric-change">
                                    <i class="ri-calculator-line"></i> Average Value
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Currency Breakdown -->
                    <div class="row">
                        <div class="col-12">
                            <div class="revenue-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-money-dollar-circle-line me-2"></i>Revenue by Currency
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover" id="currencyTable">
                                            <thead>
                                                <tr>
                                                    <th>Currency</th>
                                                    <th>Total Revenue</th>
                                                    <th>USD Equivalent</th>
                                                    <th>Paid Revenue</th>
                                                    <th>Pending</th>
                                                    <th>Shipments</th>
                                                    <th>Avg Value</th>
                                                    <th>Weight (KG)</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach ($currency_data as $data): ?>
                                                <tr>
                                                    <td>
                                                        <span class="currency-badge currency-<?php echo strtolower($data['currency']); ?>">
                                                            <?php echo $data['currency']; ?>
                                                        </span>
                                                    </td>
                                                    <td class="fw-bold"><?php echo formatCurrency($data['total_revenue'], $data['currency']); ?></td>
                                                    <td class="text-muted"><?php echo formatCurrency($data['usd_total_revenue'], 'USD'); ?></td>
                                                    <td class="text-success"><?php echo formatCurrency($data['paid_revenue'], $data['currency']); ?></td>
                                                    <td class="text-warning"><?php echo formatCurrency($data['pending_revenue'], $data['currency']); ?></td>
                                                    <td><?php echo number_format($data['total_shipments']); ?></td>
                                                    <td><?php echo formatCurrency($data['avg_shipment_value'], $data['currency']); ?></td>
                                                    <td><?php echo number_format($data['total_weight'], 2); ?></td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Charts Row -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="chart-container">
                                <h5 class="mb-3"><i class="ri-pie-chart-line me-2"></i>Payment Status Distribution</h5>
                                <div id="paymentStatusChart" style="height: 350px;"></div>
                            </div>
                        </div>
                        
                        <div class="col-xl-6">
                            <div class="chart-container">
                                <h5 class="mb-3"><i class="ri-bar-chart-line me-2"></i>Service Type Performance</h5>
                                <div id="servicePerformanceChart" style="height: 350px;"></div>
                            </div>
                        </div>
                    </div>

                    <!-- Revenue Trends -->
                    <div class="row">
                        <div class="col-12">
                            <div class="chart-container">
                                <h5 class="mb-3"><i class="ri-line-chart-line me-2"></i>Daily Revenue Trends (Last 30 Days)</h5>
                                <div id="revenueTrendsChart" style="height: 400px;"></div>
                            </div>
                        </div>
                    </div>

                    <!-- Performance Tables -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="revenue-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-route-line me-2"></i>Top Routes by Revenue
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover" id="routeTable">
                                            <thead>
                                                <tr>
                                                    <th>Route</th>
                                                    <th>Currency</th>
                                                    <th>Revenue</th>
                                                    <th>Shipments</th>
                                                    <th>Avg Value</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($route = $route_performance->fetch_assoc()): ?>
                                                <tr>
                                                    <td class="fw-bold"><?php echo $route['route']; ?></td>
                                                    <td>
                                                        <span class="currency-badge currency-<?php echo strtolower($route['currency']); ?>">
                                                            <?php echo $route['currency']; ?>
                                                        </span>
                                                    </td>
                                                    <td class="fw-bold text-success"><?php echo formatCurrency($route['route_revenue'], $route['currency']); ?></td>
                                                    <td><?php echo number_format($route['shipment_count']); ?></td>
                                                    <td><?php echo formatCurrency($route['avg_revenue'], $route['currency']); ?></td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-6">
                            <div class="revenue-card">
                                <div class="card-header bg-transparent border-bottom d-flex justify-content-between align-items-center">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-user-star-line me-2"></i>Top Customers by Revenue
                                    </h5>
                                    <button class="btn btn-sm export-btn" onclick="exportCustomers()">
                                        <i class="ri-download-line me-1"></i>Export
                                    </button>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover" id="customerTable">
                                            <thead>
                                                <tr>
                                                    <th>Customer</th>
                                                    <th>Type</th>
                                                    <th>Currency</th>
                                                    <th>Revenue</th>
                                                    <th>Shipments</th>
                                                    <th>Last Activity</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($customer = $top_customers->fetch_assoc()): ?>
                                                <tr>
                                                    <td>
                                                        <div class="d-flex align-items-center">
                                                            <div class="avatar-sm bg-primary rounded-circle d-flex align-items-center justify-content-center me-2">
                                                                <span class="text-white fw-bold">
                                                                    <?php 
                                                                    if ($customer['customer_type'] == 'CORPORATE') {
                                                                        echo strtoupper(substr($customer['company_name'] ?? 'C', 0, 2));
                                                                    } else {
                                                                        echo strtoupper(substr($customer['first_name'] ?? 'I', 0, 1) . substr($customer['last_name'] ?? 'N', 0, 1));
                                                                    }
                                                                    ?>
                                                                </span>
                                                            </div>
                                                            <div>
                                                                <div class="fw-bold">
                                                                    <?php 
                                                                    echo $customer['customer_type'] == 'CORPORATE' 
                                                                        ? ($customer['company_name'] ?? 'Company') 
                                                                        : ($customer['first_name'] ?? 'Individual') . ' ' . ($customer['last_name'] ?? ''); 
                                                                    ?>
                                                                </div>
                                                                <small class="text-muted"><?php echo $customer['customer_code'] ?? 'N/A'; ?></small>
                                                            </div>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-info">
                                                            <?php echo ucfirst(strtolower($customer['customer_type'] ?? 'INDIVIDUAL')); ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <span class="currency-badge currency-<?php echo strtolower($customer['currency']); ?>">
                                                            <?php echo $customer['currency']; ?>
                                                        </span>
                                                    </td>
                                                    <td class="fw-bold text-success"><?php echo formatCurrency($customer['customer_revenue'], $customer['currency']); ?></td>
                                                    <td><?php echo number_format($customer['shipment_count']); ?></td>
                                                    <td class="text-muted">
                                                        <?php echo $customer['last_shipment_date'] ? date('M j, Y', strtotime($customer['last_shipment_date'])) : 'N/A'; ?>
                                                    </td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Export and Actions -->
                    <div class="row">
                        <div class="col-12">
                            <div class="revenue-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-download-cloud-line me-2"></i>Export & Reports
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="row">
                                        <div class="col-md-3">
                                            <button class="btn export-btn w-100 mb-2" onclick="exportRevenueSummary()">
                                                <i class="ri-file-excel-line me-2"></i>Revenue Summary
                                            </button>
                                        </div>
                                        <div class="col-md-3">
                                            <button class="btn export-btn w-100 mb-2" onclick="exportCurrencyBreakdown()">
                                                <i class="ri-money-dollar-circle-line me-2"></i>Currency Breakdown
                                            </button>
                                        </div>
                                        <div class="col-md-3">
                                            <button class="btn export-btn w-100 mb-2" onclick="exportServiceAnalysis()">
                                                <i class="ri-service-line me-2"></i>Service Analysis
                                            </button>
                                        </div>
                                        <div class="col-md-3">
                                            <button class="btn export-btn w-100 mb-2" onclick="exportDetailedReport()">
                                                <i class="ri-file-chart-line me-2"></i>Detailed Report
                                            </button>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <!-- 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/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>
    
    <!-- ApexCharts JS -->
    <script src="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.min.js"></script>
    
    <!-- SweetAlert2 JS -->
    <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.min.js"></script>
    
    <script>
        $(document).ready(function() {
            // Initialize DataTables
            $('#currencyTable').DataTable({
                responsive: true,
                pageLength: 10,
                order: [[2, 'desc']],
                columnDefs: [
                    { targets: [1, 2, 3, 4, 6], className: 'text-end' },
                    { targets: [7], className: 'text-center' }
                ]
            });
            
            $('#routeTable').DataTable({
                responsive: true,
                pageLength: 10,
                order: [[2, 'desc']],
                columnDefs: [
                    { targets: [2, 4], className: 'text-end' }
                ]
            });
            
            $('#customerTable').DataTable({
                responsive: true,
                pageLength: 10,
                order: [[3, 'desc']],
                columnDefs: [
                    { targets: [3], className: 'text-end' },
                    { targets: [4], className: 'text-center' }
                ]
            });

            // Initialize Charts
            initializeCharts();
        });

        function toggleCustomDates() {
            const period = document.getElementById('period').value;
            const startDateGroup = document.getElementById('start-date-group');
            const endDateGroup = document.getElementById('end-date-group');
            
            if (period === 'custom') {
                startDateGroup.style.display = 'block';
                endDateGroup.style.display = 'block';
            } else {
                startDateGroup.style.display = 'none';
                endDateGroup.style.display = 'none';
            }
        }

        function initializeCharts() {
            // Payment Status Distribution Chart
            const paymentStatusData = <?php
                $payment_data = [];
                while ($status = $payment_status_data->fetch_assoc()) {
                    $payment_data[] = $status;
                }
                echo json_encode($payment_data);
            ?>;
            
            // Group payment data by status
            const statusGroups = {};
            paymentStatusData.forEach(item => {
                if (!statusGroups[item.payment_status]) {
                    statusGroups[item.payment_status] = 0;
                }
                statusGroups[item.payment_status] += parseFloat(item.status_revenue);
            });

            const paymentStatusOptions = {
                series: Object.values(statusGroups),
                chart: {
                    type: 'donut',
                    height: 350
                },
                labels: Object.keys(statusGroups),
                colors: ['#28a745', '#ffc107', '#17a2b8', '#dc3545'],
                legend: {
                    position: 'bottom'
                },
                plotOptions: {
                    pie: {
                        donut: {
                            size: '65%'
                        }
                    }
                },
                dataLabels: {
                    enabled: true,
                    formatter: function(val, opts) {
                        return val.toFixed(1) + '%';
                    }
                },
                tooltip: {
                    y: {
                        formatter: function(value) {
                            return ' + value.toLocaleString();
                        }
                    }
                }
            };

            const paymentStatusChart = new ApexCharts(document.querySelector("#paymentStatusChart"), paymentStatusOptions);
            paymentStatusChart.render();

            // Service Performance Chart
            const serviceData = <?php
                $service_data = [];
                while ($service = $service_performance->fetch_assoc()) {
                    $service_data[] = $service;
                }
                echo json_encode($service_data);
            ?>;
            
            // Group service data
            const serviceGroups = {};
            serviceData.forEach(item => {
                const key = item.service_name;
                if (!serviceGroups[key]) {
                    serviceGroups[key] = 0;
                }
                serviceGroups[key] += parseFloat(item.service_revenue);
            });

            const servicePerformanceOptions = {
                series: [{
                    name: 'Revenue',
                    data: Object.values(serviceGroups)
                }],
                chart: {
                    type: 'bar',
                    height: 350,
                    toolbar: {
                        show: false
                    }
                },
                plotOptions: {
                    bar: {
                        horizontal: true,
                        borderRadius: 4,
                        dataLabels: {
                            position: 'top',
                        },
                    }
                },
                dataLabels: {
                    enabled: true,
                    offsetX: 30,
                    style: {
                        fontSize: '12px',
                        colors: ["#304758"]
                    }
                },
                xaxis: {
                    categories: Object.keys(serviceGroups)
                },
                colors: ['#667eea'],
                tooltip: {
                    y: {
                        formatter: function(value) {
                            return ' + value.toLocaleString();
                        }
                    }
                }
            };

            const serviceChart = new ApexCharts(document.querySelector("#servicePerformanceChart"), servicePerformanceOptions);
            serviceChart.render();

            // Daily Revenue Trends Chart
            const trendsData = <?php echo json_encode($daily_trends); ?>;
            
            // Process trends data by currency
            const trendsByDate = {};
            trendsData.forEach(item => {
                if (!trendsByDate[item.trend_date]) {
                    trendsByDate[item.trend_date] = {};
                }
                trendsByDate[item.trend_date][item.currency] = parseFloat(item.daily_revenue);
            });

            const dates = Object.keys(trendsByDate).sort();
            const currencies = [...new Set(trendsData.map(item => item.currency))];
            
            const series = currencies.map(currency => ({
                name: currency + ' Revenue',
                data: dates.map(date => trendsByDate[date][currency] || 0)
            }));

            const trendsOptions = {
                series: series,
                chart: {
                    type: 'line',
                    height: 400,
                    zoom: {
                        enabled: true
                    }
                },
                dataLabels: {
                    enabled: false
                },
                stroke: {
                    curve: 'smooth',
                    width: 3
                },
                xaxis: {
                    categories: dates.map(date => new Date(date).toLocaleDateString()),
                    title: {
                        text: 'Date'
                    }
                },
                yaxis: {
                    title: {
                        text: 'Revenue'
                    }
                },
                colors: ['#667eea', '#f093fb', '#4facfe', '#43e97b', '#ffa726'],
                legend: {
                    position: 'top'
                },
                grid: {
                    borderColor: '#e7e7e7',
                    row: {
                        colors: ['#f3f3f3', 'transparent'],
                        opacity: 0.5
                    }
                },
                tooltip: {
                    shared: true,
                    intersect: false,
                    y: {
                        formatter: function(value) {
                            return ' + value.toLocaleString();
                        }
                    }
                }
            };

            const trendsChart = new ApexCharts(document.querySelector("#revenueTrendsChart"), trendsOptions);
            trendsChart.render();
        }

        // Export Functions
        function exportRevenueSummary() {
            Swal.fire({
                title: 'Export Revenue Summary',
                text: 'Generating comprehensive revenue summary report...',
                icon: 'info',
                showConfirmButton: false,
                allowOutsideClick: false,
                didOpen: () => {
                    Swal.showLoading();
                    setTimeout(() => {
                        Swal.close();
                        // Simulate export
                        const link = document.createElement('a');
                        link.href = 'exports/revenue_summary.php?period=<?php echo $date_filter; ?>&start=<?php echo $custom_start; ?>&end=<?php echo $custom_end; ?>';
                        link.download = 'revenue_summary_<?php echo date("Y-m-d"); ?>.xlsx';
                        link.click();
                    }, 2000);
                }
            });
        }

        function exportCurrencyBreakdown() {
            Swal.fire({
                title: 'Export Currency Breakdown',
                text: 'Generating multi-currency analysis report...',
                icon: 'info',
                showConfirmButton: false,
                allowOutsideClick: false,
                didOpen: () => {
                    Swal.showLoading();
                    setTimeout(() => {
                        Swal.close();
                        const link = document.createElement('a');
                        link.href = 'exports/currency_breakdown.php?period=<?php echo $date_filter; ?>';
                        link.download = 'currency_breakdown_<?php echo date("Y-m-d"); ?>.xlsx';
                        link.click();
                    }, 2000);
                }
            });
        }

        function exportServiceAnalysis() {
            Swal.fire({
                title: 'Export Service Analysis',
                text: 'Generating service type performance report...',
                icon: 'info',
                showConfirmButton: false,
                allowOutsideClick: false,
                didOpen: () => {
                    Swal.showLoading();
                    setTimeout(() => {
                        Swal.close();
                        const link = document.createElement('a');
                        link.href = 'exports/service_analysis.php?period=<?php echo $date_filter; ?>';
                        link.download = 'service_analysis_<?php echo date("Y-m-d"); ?>.xlsx';
                        link.click();
                    }, 2000);
                }
            });
        }

        function exportDetailedReport() {
            Swal.fire({
                title: 'Export Detailed Report',
                html: `
                    <div class="mb-3">
                        <label class="form-label">Report Format:</label>
                        <select class="form-select" id="reportFormat">
                            <option value="excel">Excel Workbook (.xlsx)</option>
                            <option value="pdf">PDF Report (.pdf)</option>
                            <option value="csv">CSV Data (.csv)</option>
                        </select>
                    </div>
                    <div class="mb-3">
                        <div class="form-check">
                            <input class="form-check-input" type="checkbox" id="includeCharts" checked>
                            <label class="form-check-label" for="includeCharts">
                                Include Charts and Visualizations
                            </label>
                        </div>
                    </div>
                `,
                showCancelButton: true,
                confirmButtonText: 'Generate Report',
                preConfirm: () => {
                    const format = document.getElementById('reportFormat').value;
                    const includeCharts = document.getElementById('includeCharts').checked;
                    return { format, includeCharts };
                }
            }).then((result) => {
                if (result.isConfirmed) {
                    const { format, includeCharts } = result.value;
                    
                    Swal.fire({
                        title: 'Generating Report',
                        text: 'Please wait while we prepare your detailed revenue report...',
                        icon: 'info',
                        showConfirmButton: false,
                        allowOutsideClick: false,
                        didOpen: () => {
                            Swal.showLoading();
                            setTimeout(() => {
                                Swal.close();
                                const link = document.createElement('a');
                                link.href = `exports/detailed_report.php?format=${format}&charts=${includeCharts}&period=<?php echo $date_filter; ?>`;
                                link.download = `detailed_revenue_report_<?php echo date("Y-m-d"); ?>.${format}`;
                                link.click();
                                
                                Swal.fire({
                                    title: 'Report Generated!',
                                    text: 'Your detailed revenue report has been generated and downloaded.',
                                    icon: 'success',
                                    timer: 3000,
                                    showConfirmButton: false
                                });
                            }, 3000);
                        }
                    });
                }
            });
        }

        function exportCustomers() {
            Swal.fire({
                title: 'Export Customer Revenue Data',
                text: 'Generating customer revenue analysis...',
                icon: 'info',
                showConfirmButton: false,
                allowOutsideClick: false,
                didOpen: () => {
                    Swal.showLoading();
                    setTimeout(() => {
                        Swal.close();
                        const link = document.createElement('a');
                        link.href = 'exports/customer_revenue.php?period=<?php echo $date_filter; ?>';
                        link.download = 'customer_revenue_<?php echo date("Y-m-d"); ?>.xlsx';
                        link.click();
                    }, 2000);
                }
            });
        }

        // Real-time updates
        setInterval(function() {
            // Update time display if needed
            const now = new Date();
            document.querySelectorAll('.last-updated').forEach(el => {
                el.textContent = 'Last updated: ' + now.toLocaleTimeString();
            });
        }, 30000);

        // Currency conversion tooltip
        document.querySelectorAll('[data-bs-toggle="tooltip"]').forEach(function(element) {
            new bootstrap.Tooltip(element);
        });
    </script>

    <!-- App js -->
    <script src="assets/js/app.js"></script>
</body>
</html>