<?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();
}

$user_id = $_SESSION['user_id'];

// Fetch user details
$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'];
}

// Get date range filters
$date_from = $_GET['date_from'] ?? date('Y-m-01'); // First day of current month
$date_to = $_GET['date_to'] ?? date('Y-m-d'); // Today
$location_filter = $_GET['location'] ?? '';
$service_filter = $_GET['service'] ?? '';
$customer_filter = $_GET['customer'] ?? '';

// Build WHERE clause for filters
$where_conditions = ["DATE(s.booking_date) >= ? AND DATE(s.booking_date) <= ?"];
$params = [$date_from, $date_to];
$param_types = 'ss';

if (!empty($location_filter)) {
    $where_conditions[] = "(s.origin_location_id = ? OR s.destination_location_id = ?)";
    $params[] = $location_filter;
    $params[] = $location_filter;
    $param_types .= 'ii';
}

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

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

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

// 1. Overall Statistics
$stats_sql = "SELECT 
    COUNT(*) as total_shipments,
    SUM(s.total_charges) as total_revenue,
    SUM(s.chargeable_weight) as total_weight,
    SUM(s.pieces) as total_pieces,
    AVG(s.total_charges) as avg_shipment_value,
    AVG(s.chargeable_weight) as avg_weight_per_shipment,
    COUNT(CASE WHEN s.status = 'DELIVERED' THEN 1 END) as delivered_count,
    COUNT(CASE WHEN s.status = 'CANCELLED' THEN 1 END) as cancelled_count,
    COUNT(CASE WHEN s.status IN ('DRAFT', 'BOOKED') THEN 1 END) as pending_count,
    COUNT(CASE WHEN s.delivery_date <= s.estimated_delivery AND s.status = 'DELIVERED' THEN 1 END) as on_time_deliveries,
    AVG(CASE WHEN s.delivery_date IS NOT NULL THEN TIMESTAMPDIFF(HOUR, s.booking_date, s.delivery_date) END) as avg_delivery_hours
FROM shipments s
$where_clause";

$stats_stmt = $conn->prepare($stats_sql);
$stats_stmt->bind_param($param_types, ...$params);
$stats_stmt->execute();
$overall_stats = $stats_stmt->get_result()->fetch_assoc();

// 2. Daily Shipment Trends (Last 30 days within selected range)
$trends_sql = "SELECT 
    DATE(s.booking_date) as shipment_date,
    COUNT(*) as daily_shipments,
    SUM(s.total_charges) as daily_revenue,
    SUM(s.chargeable_weight) as daily_weight,
    COUNT(CASE WHEN s.status = 'DELIVERED' THEN 1 END) as daily_delivered
FROM shipments s
$where_clause
GROUP BY DATE(s.booking_date)
ORDER BY shipment_date DESC
LIMIT 30";

$trends_stmt = $conn->prepare($trends_sql);
$trends_stmt->bind_param($param_types, ...$params);
$trends_stmt->execute();
$daily_trends = $trends_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// 3. Service Type Performance
$service_performance_sql = "SELECT 
    st.service_name,
    st.service_category,
    COUNT(s.shipment_id) 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,
    COUNT(CASE WHEN s.status = 'DELIVERED' THEN 1 END) as delivered_count,
    COUNT(CASE WHEN s.delivery_date <= s.estimated_delivery AND s.status = 'DELIVERED' THEN 1 END) as on_time_count,
    AVG(CASE WHEN s.delivery_date IS NOT NULL THEN TIMESTAMPDIFF(HOUR, s.booking_date, s.delivery_date) END) as avg_delivery_hours
FROM shipments s
INNER JOIN service_types st ON s.service_id = st.service_id
$where_clause
GROUP BY st.service_id, st.service_name, st.service_category
ORDER BY total_revenue DESC";

$service_stmt = $conn->prepare($service_performance_sql);
$service_stmt->bind_param($param_types, ...$params);
$service_stmt->execute();
$service_performance = $service_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// 4. Route Performance
$route_performance_sql = "SELECT 
    CONCAT(ol.location_name, ' → ', dl.location_name) as route_name,
    ol.location_name as origin_location,
    dl.location_name as destination_location,
    COUNT(s.shipment_id) 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,
    COUNT(CASE WHEN s.status = 'DELIVERED' THEN 1 END) as delivered_count,
    AVG(CASE WHEN s.delivery_date IS NOT NULL THEN TIMESTAMPDIFF(HOUR, s.booking_date, s.delivery_date) END) as avg_delivery_hours
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_clause
GROUP BY s.origin_location_id, s.destination_location_id, ol.location_name, dl.location_name
HAVING shipment_count >= 5
ORDER BY total_revenue DESC
LIMIT 15";

$route_stmt = $conn->prepare($route_performance_sql);
$route_stmt->bind_param($param_types, ...$params);
$route_stmt->execute();
$route_performance = $route_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// 5. Customer Analytics
$customer_analytics_sql = "SELECT 
    c.customer_id,
    c.customer_code,
    CASE 
        WHEN c.customer_type = 'CORPORATE' THEN c.company_name
        ELSE CONCAT(c.first_name, ' ', c.last_name)
    END as customer_name,
    c.customer_type,
    COUNT(s.shipment_id) as shipment_count,
    SUM(s.total_charges) as total_revenue,
    AVG(s.total_charges) as avg_shipment_value,
    SUM(s.chargeable_weight) as total_weight,
    COUNT(CASE WHEN s.status = 'DELIVERED' THEN 1 END) as delivered_count,
    MAX(s.booking_date) as last_shipment_date,
    COUNT(CASE WHEN DATE(s.booking_date) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1 END) as recent_shipments
FROM shipments s
INNER JOIN customers c ON s.sender_customer_id = c.customer_id
$where_clause
GROUP BY c.customer_id, c.customer_code, customer_name, c.customer_type
HAVING shipment_count > 0
ORDER BY total_revenue DESC
LIMIT 20";

$customer_stmt = $conn->prepare($customer_analytics_sql);
$customer_stmt->bind_param($param_types, ...$params);
$customer_stmt->execute();
$customer_analytics = $customer_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// 6. Status Distribution
$status_distribution_sql = "SELECT 
    s.status,
    COUNT(*) as status_count,
    SUM(s.total_charges) as status_revenue,
    AVG(s.total_charges) as avg_value
FROM shipments s
$where_clause
GROUP BY s.status
ORDER BY status_count DESC";

$status_stmt = $conn->prepare($status_distribution_sql);
$status_stmt->bind_param($param_types, ...$params);
$status_stmt->execute();
$status_distribution = $status_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// 7. Weight and Piece Analysis
$weight_analysis_sql = "SELECT 
    CASE 
        WHEN s.chargeable_weight <= 1 THEN '0-1 kg'
        WHEN s.chargeable_weight <= 5 THEN '1-5 kg'
        WHEN s.chargeable_weight <= 10 THEN '5-10 kg'
        WHEN s.chargeable_weight <= 25 THEN '10-25 kg'
        WHEN s.chargeable_weight <= 50 THEN '25-50 kg'
        ELSE '50+ kg'
    END as weight_range,
    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
$where_clause
GROUP BY weight_range
ORDER BY 
    CASE 
        WHEN weight_range = '0-1 kg' THEN 1
        WHEN weight_range = '1-5 kg' THEN 2
        WHEN weight_range = '5-10 kg' THEN 3
        WHEN weight_range = '10-25 kg' THEN 4
        WHEN weight_range = '25-50 kg' THEN 5
        ELSE 6
    END";

$weight_stmt = $conn->prepare($weight_analysis_sql);
$weight_stmt->bind_param($param_types, ...$params);
$weight_stmt->execute();
$weight_analysis = $weight_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// 8. Monthly Comparison (Current vs Previous Period)
$period_days = (strtotime($date_to) - strtotime($date_from)) / (60 * 60 * 24);
$previous_date_from = date('Y-m-d', strtotime($date_from . " -$period_days days"));
$previous_date_to = date('Y-m-d', strtotime($date_to . " -$period_days days"));

$comparison_sql = "SELECT 
    'current' as period,
    COUNT(*) as shipment_count,
    SUM(total_charges) as revenue,
    AVG(total_charges) as avg_value,
    COUNT(CASE WHEN status = 'DELIVERED' THEN 1 END) as delivered_count
FROM shipments s
WHERE DATE(s.booking_date) >= ? AND DATE(s.booking_date) <= ?
UNION ALL
SELECT 
    'previous' as period,
    COUNT(*) as shipment_count,
    SUM(total_charges) as revenue,
    AVG(total_charges) as avg_value,
    COUNT(CASE WHEN status = 'DELIVERED' THEN 1 END) as delivered_count
FROM shipments s
WHERE DATE(s.booking_date) >= ? AND DATE(s.booking_date) <= ?";

$comparison_stmt = $conn->prepare($comparison_sql);
$comparison_stmt->bind_param('ssss', $date_from, $date_to, $previous_date_from, $previous_date_to);
$comparison_stmt->execute();
$comparison_data = $comparison_stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Get filter dropdown data
$locations_sql = "SELECT location_id, location_name, city FROM locations WHERE is_active = 1 ORDER BY location_name";
$locations_result = $conn->query($locations_sql);

$services_sql = "SELECT service_id, service_name, service_category FROM service_types WHERE is_active = 1 ORDER BY service_name";
$services_result = $conn->query($services_sql);

$customers_sql = "SELECT customer_id, customer_code, 
                  CASE 
                      WHEN customer_type = 'CORPORATE' THEN company_name
                      ELSE CONCAT(first_name, ' ', last_name)
                  END as customer_name
                  FROM customers WHERE status = 'ACTIVE' ORDER BY customer_name LIMIT 100";
$customers_result = $conn->query($customers_sql);

$conn->close();

// Calculate performance metrics
$delivery_rate = $overall_stats['total_shipments'] > 0 ? 
    round(($overall_stats['delivered_count'] / $overall_stats['total_shipments']) * 100, 1) : 0;

$on_time_rate = $overall_stats['delivered_count'] > 0 ? 
    round(($overall_stats['on_time_deliveries'] / $overall_stats['delivered_count']) * 100, 1) : 0;

$cancellation_rate = $overall_stats['total_shipments'] > 0 ? 
    round(($overall_stats['cancelled_count'] / $overall_stats['total_shipments']) * 100, 1) : 0;

// Calculate period comparison
$current_period = array_filter($comparison_data, function($item) { return $item['period'] === 'current'; })[0] ?? [];
$previous_period = array_filter($comparison_data, function($item) { return $item['period'] === 'previous'; })[0] ?? [];

function calculateGrowth($current, $previous) {
    if ($previous == 0) return $current > 0 ? 100 : 0;
    return round((($current - $previous) / $previous) * 100, 1);
}

$shipment_growth = calculateGrowth($current_period['shipment_count'] ?? 0, $previous_period['shipment_count'] ?? 0);
$revenue_growth = calculateGrowth($current_period['revenue'] ?? 0, $previous_period['revenue'] ?? 0);
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Shipment Analytics - Freight Management System')); ?>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.css">
    <?php include 'layouts/head-css.php'; ?>
    
    <style>
        .analytics-card {
            background: white;
            border: 1px solid #e2e8f0;
            border-radius: 12px;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            margin-bottom: 1.5rem;
            overflow: hidden;
        }
        
        .analytics-header {
            padding: 1.5rem;
            border-bottom: 1px solid #f1f5f9;
            background: #f8fafc;
        }
        
        .analytics-body {
            padding: 1.5rem;
        }
        
        .metric-card {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 12px;
            padding: 1.5rem;
            margin-bottom: 1rem;
            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: 2.5rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
        }
        
        .metric-label {
            font-size: 0.875rem;
            opacity: 0.9;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
        
        .metric-change {
            font-size: 0.875rem;
            margin-top: 0.5rem;
            display: flex;
            align-items: center;
        }
        
        .metric-change.positive { color: #10b981; }
        .metric-change.negative { color: #f87171; }
        .metric-change.neutral { color: #94a3b8; }
        
        .kpi-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 1rem;
            margin-bottom: 2rem;
        }
        
        .kpi-card {
            background: white;
            border: 1px solid #e2e8f0;
            border-radius: 8px;
            padding: 1.5rem;
            text-align: center;
        }
        
        .kpi-value {
            font-size: 2rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
        }
        
        .kpi-label {
            font-size: 0.875rem;
            color: #64748b;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
        
        .filters-section {
            background: white;
            border: 1px solid #e2e8f0;
            border-radius: 12px;
            padding: 1.5rem;
            margin-bottom: 2rem;
        }
        
        .data-table {
            width: 100%;
            border-collapse: collapse;
        }
        
        .data-table th {
            background: #f8fafc;
            padding: 1rem;
            text-align: left;
            font-weight: 600;
            font-size: 0.875rem;
            color: #64748b;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .data-table td {
            padding: 1rem;
            border-bottom: 1px solid #f1f5f9;
            font-size: 0.875rem;
        }
        
        .data-table tbody tr:hover {
            background: #f8fafc;
        }
        
        .performance-indicator {
            display: inline-block;
            padding: 0.25rem 0.75rem;
            border-radius: 9999px;
            font-size: 0.75rem;
            font-weight: 600;
        }
        
        .performance-excellent { background: #dcfce7; color: #166534; }
        .performance-good { background: #dbeafe; color: #1d4ed8; }
        .performance-average { background: #fef3c7; color: #d97706; }
        .performance-poor { background: #fee2e2; color: #991b1b; }
        
        .chart-container {
            min-height: 350px;
        }
        
        @media (max-width: 768px) {
            .kpi-grid {
                grid-template-columns: repeat(2, 1fr);
            }
            
            .metric-value {
                font-size: 2rem;
            }
            
            .analytics-header,
            .analytics-body {
                padding: 1rem;
            }
        }
        
        .export-buttons {
            display: flex;
            gap: 0.5rem;
            margin-bottom: 1rem;
        }
        
        .summary-stats {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
            gap: 1rem;
            margin-bottom: 1rem;
        }
        
        .summary-item {
            text-align: center;
            padding: 1rem;
            background: #f8fafc;
            border-radius: 8px;
        }
        
        .summary-value {
            font-size: 1.5rem;
            font-weight: 600;
            color: #1e293b;
        }
        
        .summary-label {
            font-size: 0.75rem;
            color: #64748b;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
    </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">Shipment Analytics</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"><a href="shipments.php">Shipments</a></li>
                                        <li class="breadcrumb-item active">Analytics</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Filters Section -->
                    <div class="filters-section">
                        <form method="GET" class="row g-3">
                            <div class="col-md-3">
                                <label class="form-label">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">Date To</label>
                                <input type="date" class="form-control" name="date_to" value="<?php echo $date_to; ?>">
                            </div>
                            
                            <div class="col-md-2">
                                <label class="form-label">Service Type</label>
                                <select class="form-select" name="service">
                                    <option value="">All Services</option>
                                    <?php while($service = $services_result->fetch_assoc()): ?>
                                    <option value="<?php echo $service['service_id']; ?>" <?php echo $service_filter == $service['service_id'] ? 'selected' : ''; ?>>
                                        <?php echo $service['service_name']; ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-2">
                                <label class="form-label">Location</label>
                                <select class="form-select" name="location">
                                    <option value="">All Locations</option>
                                    <?php while($location = $locations_result->fetch_assoc()): ?>
                                    <option value="<?php echo $location['location_id']; ?>" <?php echo $location_filter == $location['location_id'] ? 'selected' : ''; ?>>
                                        <?php echo $location['location_name']; ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-2">
                                <label class="form-label">&nbsp;</label>
                                <button type="submit" class="btn btn-primary d-block w-100">Apply Filters</button>
                            </div>
                        </form>
                        
                        <div class="export-buttons mt-3">
                            <button onclick="exportAnalytics('pdf')" class="btn btn-outline-danger btn-sm">
                                <i class="bx bxs-file-pdf"></i> Export PDF
                            </button>
                            <button onclick="exportAnalytics('excel')" class="btn btn-outline-success btn-sm">
                                <i class="bx bxs-file-doc"></i> Export Excel
                            </button>
                            <button onclick="window.print()" class="btn btn-outline-secondary btn-sm">
                                <i class="bx bx-printer"></i> Print
                            </button>
                        </div>
                    </div>

                    <!-- Key Performance Indicators -->
                    <div class="kpi-grid">
                        <div class="kpi-card">
                            <div class="kpi-value" style="color: #3b82f6;">
                                <?php echo number_format($overall_stats['total_shipments']); ?>
                            </div>
                            <div class="kpi-label">Total Shipments</div>
                            <?php if ($shipment_growth != 0): ?>
                            <div class="metric-change <?php echo $shipment_growth > 0 ? 'positive' : 'negative'; ?>">
                                <i class="bx bx-<?php echo $shipment_growth > 0 ? 'trending-up' : 'trending-down'; ?>"></i>
                                <?php echo abs($shipment_growth); ?>% vs previous period
                            </div>
                            <?php endif; ?>
                        </div>
                        
                        <div class="kpi-card">
                            <div class="kpi-value" style="color: #10b981;">
                                KES <?php echo number_format($overall_stats['total_revenue'], 0); ?>
                            </div>
                            <div class="kpi-label">Total Revenue</div>
                            <?php if ($revenue_growth != 0): ?>
                            <div class="metric-change <?php echo $revenue_growth > 0 ? 'positive' : 'negative'; ?>">
                                <i class="bx bx-<?php echo $revenue_growth > 0 ? 'trending-up' : 'trending-down'; ?>"></i>
                                <?php echo abs($revenue_growth); ?>% vs previous period
                            </div>
                            <?php endif; ?>
                        </div>
                        
                        <div class="kpi-card">
                            <div class="kpi-value" style="color: #f59e0b;">
                                <?php echo $delivery_rate; ?>%
                            </div>
                            <div class="kpi-label">Delivery Rate</div>
                        </div>
                        
                        <div class="kpi-card">
                            <div class="kpi-value" style="color: #8b5cf6;">
                                <?php echo $on_time_rate; ?>%
                            </div>
                            <div class="kpi-label">On-Time Delivery</div>
                        </div>
                        
                        <div class="kpi-card">
                            <div class="kpi-value" style="color: #64748b;">
                                KES <?php echo number_format($overall_stats['avg_shipment_value'], 0); ?>
                            </div>
                            <div class="kpi-label">Avg Shipment Value</div>
                        </div>
                        
                        <div class="kpi-card">
                            <div class="kpi-value" style="color: #06b6d4;">
                                <?php echo number_format($overall_stats['avg_delivery_hours'], 1); ?>h
                            </div>
                            <div class="kpi-label">Avg Delivery Time</div>
                        </div>
                    </div>

                    <!-- Charts Row -->
                    <div class="row">
                        <!-- Daily Trends Chart -->
                        <div class="col-xl-8">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Daily Shipment Trends</h5>
                                </div>
                                <div class="analytics-body">
                                    <div id="daily-trends-chart" class="chart-container"></div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Status Distribution -->
                        <div class="col-xl-4">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Status Distribution</h5>
                                </div>
                                <div class="analytics-body">
                                    <div id="status-chart" class="chart-container"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Service Performance and Weight Analysis -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Service Type Performance</h5>
                                </div>
                                <div class="analytics-body">
                                    <div class="table-responsive">
                                        <table class="data-table">
                                            <thead>
                                                <tr>
                                                    <th>Service</th>
                                                    <th>Shipments</th>
                                                    <th>Revenue</th>
                                                    <th>Avg Value</th>
                                                    <th>Performance</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach($service_performance as $service): ?>
                                                <?php 
                                                $delivery_rate = $service['shipment_count'] > 0 ? 
                                                    round(($service['delivered_count'] / $service['shipment_count']) * 100, 1) : 0;
                                                $performance_class = $delivery_rate >= 95 ? 'excellent' : 
                                                    ($delivery_rate >= 85 ? 'good' : 
                                                    ($delivery_rate >= 70 ? 'average' : 'poor'));
                                                ?>
                                                <tr>
                                                    <td>
                                                        <strong><?php echo htmlspecialchars($service['service_name']); ?></strong>
                                                        <br><small class="text-muted"><?php echo $service['service_category']; ?></small>
                                                    </td>
                                                    <td><?php echo number_format($service['shipment_count']); ?></td>
                                                    <td>KES <?php echo number_format($service['total_revenue']); ?></td>
                                                    <td>KES <?php echo number_format($service['avg_revenue_per_shipment']); ?></td>
                                                    <td>
                                                        <span class="performance-indicator performance-<?php echo $performance_class; ?>">
                                                            <?php echo $delivery_rate; ?>%
                                                        </span>
                                                    </td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-6">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Weight Distribution Analysis</h5>
                                </div>
                                <div class="analytics-body">
                                    <div id="weight-analysis-chart" class="chart-container"></div>
                                    
                                    <div class="summary-stats mt-3">
                                        <?php foreach($weight_analysis as $weight): ?>
                                        <div class="summary-item">
                                            <div class="summary-value"><?php echo number_format($weight['shipment_count']); ?></div>
                                            <div class="summary-label"><?php echo $weight['weight_range']; ?></div>
                                        </div>
                                        <?php endforeach; ?>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Route Performance and Customer Analytics -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Top Routes by Revenue</h5>
                                </div>
                                <div class="analytics-body">
                                    <div class="table-responsive">
                                        <table class="data-table">
                                            <thead>
                                                <tr>
                                                    <th>Route</th>
                                                    <th>Shipments</th>
                                                    <th>Revenue</th>
                                                    <th>Avg Time</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach($route_performance as $route): ?>
                                                <tr>
                                                    <td>
                                                        <strong><?php echo htmlspecialchars($route['route_name']); ?></strong>
                                                    </td>
                                                    <td><?php echo number_format($route['shipment_count']); ?></td>
                                                    <td>KES <?php echo number_format($route['total_revenue']); ?></td>
                                                    <td><?php echo number_format($route['avg_delivery_hours'], 1); ?>h</td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-6">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Top Customers by Revenue</h5>
                                </div>
                                <div class="analytics-body">
                                    <div class="table-responsive">
                                        <table class="data-table">
                                            <thead>
                                                <tr>
                                                    <th>Customer</th>
                                                    <th>Shipments</th>
                                                    <th>Revenue</th>
                                                    <th>Last Activity</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php foreach($customer_analytics as $customer): ?>
                                                <tr>
                                                    <td>
                                                        <strong><?php echo htmlspecialchars($customer['customer_name']); ?></strong>
                                                        <br><small class="text-muted"><?php echo $customer['customer_code']; ?> • <?php echo $customer['customer_type']; ?></small>
                                                    </td>
                                                    <td>
                                                        <?php echo number_format($customer['shipment_count']); ?>
                                                        <?php if ($customer['recent_shipments'] > 0): ?>
                                                        <br><small class="text-success"><?php echo $customer['recent_shipments']; ?> recent</small>
                                                        <?php endif; ?>
                                                    </td>
                                                    <td>KES <?php echo number_format($customer['total_revenue']); ?></td>
                                                    <td><?php echo date('M j, Y', strtotime($customer['last_shipment_date'])); ?></td>
                                                </tr>
                                                <?php endforeach; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Additional Analytics -->
                    <div class="row">
                        <div class="col-xl-4">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Service Category Revenue</h5>
                                </div>
                                <div class="analytics-body">
                                    <div id="service-category-chart" class="chart-container"></div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-4">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Monthly Trends</h5>
                                </div>
                                <div class="analytics-body">
                                    <div id="monthly-trends-chart" class="chart-container"></div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-4">
                            <div class="analytics-card">
                                <div class="analytics-header">
                                    <h5 class="mb-0">Performance Summary</h5>
                                </div>
                                <div class="analytics-body">
                                    <div class="summary-stats">
                                        <div class="summary-item">
                                            <div class="summary-value" style="color: #10b981;"><?php echo number_format($overall_stats['total_pieces']); ?></div>
                                            <div class="summary-label">Total Pieces</div>
                                        </div>
                                        <div class="summary-item">
                                            <div class="summary-value" style="color: #3b82f6;"><?php echo number_format($overall_stats['total_weight'], 1); ?> KG</div>
                                            <div class="summary-label">Total Weight</div>
                                        </div>
                                        <div class="summary-item">
                                            <div class="summary-value" style="color: #f59e0b;"><?php echo number_format($overall_stats['avg_weight_per_shipment'], 1); ?> KG</div>
                                            <div class="summary-label">Avg Weight/Shipment</div>
                                        </div>
                                        <div class="summary-item">
                                            <div class="summary-value" style="color: #ef4444;"><?php echo $cancellation_rate; ?>%</div>
                                            <div class="summary-label">Cancellation Rate</div>
                                        </div>
                                    </div>
                                    
                                    <div class="mt-3">
                                        <h6>Key Insights</h6>
                                        <ul class="list-unstyled">
                                            <?php if ($delivery_rate >= 95): ?>
                                            <li class="text-success"><i class="bx bx-check-circle"></i> Excellent delivery performance</li>
                                            <?php elseif ($delivery_rate >= 85): ?>
                                            <li class="text-warning"><i class="bx bx-info-circle"></i> Good delivery performance</li>
                                            <?php else: ?>
                                            <li class="text-danger"><i class="bx bx-error-circle"></i> Delivery performance needs improvement</li>
                                            <?php endif; ?>
                                            
                                            <?php if ($revenue_growth > 10): ?>
                                            <li class="text-success"><i class="bx bx-trending-up"></i> Strong revenue growth</li>
                                            <?php elseif ($revenue_growth > 0): ?>
                                            <li class="text-info"><i class="bx bx-trending-up"></i> Positive revenue growth</li>
                                            <?php elseif ($revenue_growth < 0): ?>
                                            <li class="text-danger"><i class="bx bx-trending-down"></i> Revenue decline detected</li>
                                            <?php endif; ?>
                                            
                                            <?php if ($cancellation_rate < 5): ?>
                                            <li class="text-success"><i class="bx bx-check-circle"></i> Low cancellation rate</li>
                                            <?php elseif ($cancellation_rate > 10): ?>
                                            <li class="text-warning"><i class="bx bx-error-circle"></i> High cancellation rate</li>
                                            <?php endif; ?>
                                        </ul>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.min.js"></script>
    
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            // Daily Trends Chart
            var dailyTrendsOptions = {
                series: [{
                    name: 'Shipments',
                    type: 'column',
                    data: <?php echo json_encode(array_reverse(array_column($daily_trends, 'daily_shipments'))); ?>
                }, {
                    name: 'Revenue (KES)',
                    type: 'line',
                    data: <?php echo json_encode(array_reverse(array_column($daily_trends, 'daily_revenue'))); ?>
                }],
                chart: {
                    height: 350,
                    type: 'line',
                    toolbar: { show: true }
                },
                colors: ['#3b82f6', '#10b981'],
                stroke: {
                    width: [0, 3]
                },
                xaxis: {
                    categories: <?php echo json_encode(array_reverse(array_map(function($item) { 
                        return date('M j', strtotime($item['shipment_date'])); 
                    }, $daily_trends))); ?>,
                    type: 'category'
                },
                yaxis: [{
                    title: { text: 'Shipments' }
                }, {
                    opposite: true,
                    title: { text: 'Revenue (KES)' }
                }],
                tooltip: {
                    shared: true,
                    intersect: false
                },
                legend: { position: 'top' }
            };

            var dailyTrendsChart = new ApexCharts(document.querySelector("#daily-trends-chart"), dailyTrendsOptions);
            dailyTrendsChart.render();

            // Status Distribution Chart
            var statusOptions = {
                series: <?php echo json_encode(array_column($status_distribution, 'status_count')); ?>,
                chart: {
                    type: 'donut',
                    height: 350
                },
                labels: <?php echo json_encode(array_map(function($item) {
                    return ucfirst(strtolower(str_replace('_', ' ', $item['status'])));
                }, $status_distribution)); ?>,
                colors: ['#3b82f6', '#10b981', '#f59e0b', '#ef4444', '#8b5cf6'],
                legend: { position: 'bottom' },
                plotOptions: {
                    pie: {
                        donut: { size: '60%' }
                    }
                }
            };

            var statusChart = new ApexCharts(document.querySelector("#status-chart"), statusOptions);
            statusChart.render();

            // Weight Analysis Chart
            var weightOptions = {
                series: [{
                    data: <?php echo json_encode(array_column($weight_analysis, 'shipment_count')); ?>
                }],
                chart: {
                    type: 'bar',
                    height: 300
                },
                colors: ['#06b6d4'],
                xaxis: {
                    categories: <?php echo json_encode(array_column($weight_analysis, 'weight_range')); ?>
                },
                title: {
                    text: 'Shipments by Weight Range'
                }
            };

            var weightChart = new ApexCharts(document.querySelector("#weight-analysis-chart"), weightOptions);
            weightChart.render();

            // Service Category Chart
            var serviceCategoryData = {};
            <?php foreach($service_performance as $service): ?>
            if (!serviceCategoryData['<?php echo $service['service_category']; ?>']) {
                serviceCategoryData['<?php echo $service['service_category']; ?>'] = 0;
            }
            serviceCategoryData['<?php echo $service['service_category']; ?>'] += <?php echo $service['total_revenue']; ?>;
            <?php endforeach; ?>

            var serviceCategoryOptions = {
                series: Object.values(serviceCategoryData),
                chart: {
                    type: 'pie',
                    height: 300
                },
                labels: Object.keys(serviceCategoryData),
                colors: ['#3b82f6', '#10b981', '#f59e0b', '#ef4444'],
                legend: { position: 'bottom' }
            };

            var serviceCategoryChart = new ApexCharts(document.querySelector("#service-category-chart"), serviceCategoryOptions);
            serviceCategoryChart.render();

            // Monthly Trends (simplified)
            var monthlyOptions = {
                series: [{
                    name: 'Current Period',
                    data: [<?php echo $current_period['shipment_count'] ?? 0; ?>]
                }, {
                    name: 'Previous Period',
                    data: [<?php echo $previous_period['shipment_count'] ?? 0; ?>]
                }],
                chart: {
                    type: 'bar',
                    height: 300
                },
                colors: ['#3b82f6', '#94a3b8'],
                xaxis: {
                    categories: ['Shipments']
                }
            };

            var monthlyChart = new ApexCharts(document.querySelector("#monthly-trends-chart"), monthlyOptions);
            monthlyChart.render();
        });

        // Export Functions
        function exportAnalytics(format) {
            const params = new URLSearchParams(window.location.search);
            params.set('export', format);
            
            if (format === 'pdf') {
                window.open('analytics_export.php?' + params.toString() + '&format=pdf', '_blank');
            } else if (format === 'excel') {
                window.location.href = 'analytics_export.php?' + params.toString() + '&format=excel';
            }
        }

        // Auto-refresh data every 5 minutes
        setInterval(function() {
            // Only refresh if no specific filters are applied
            if (window.location.search === '' || 
                (window.location.search.includes('date_from') && window.location.search.includes('date_to'))) {
                location.reload();
            }
        }, 300000); // 5 minutes

        // Enhanced date picker functionality
        document.querySelector('input[name="date_from"]').addEventListener('change', function() {
            const dateFrom = new Date(this.value);
            const dateTo = new Date(document.querySelector('input[name="date_to"]').value);
            
            if (dateFrom > dateTo) {
                document.querySelector('input[name="date_to"]').value = this.value;
            }
        });

        // Quick date range buttons
        const filtersSection = document.querySelector('.filters-section form');
        const quickRanges = document.createElement('div');
        quickRanges.className = 'mb-3';
        quickRanges.innerHTML = `
            <label class="form-label">Quick Ranges:</label>
            <div class="btn-group" role="group">
                <button type="button" class="btn btn-outline-secondary btn-sm" onclick="setDateRange('today')">Today</button>
                <button type="button" class="btn btn-outline-secondary btn-sm" onclick="setDateRange('week')">This Week</button>
                <button type="button" class="btn btn-outline-secondary btn-sm" onclick="setDateRange('month')">This Month</button>
                <button type="button" class="btn btn-outline-secondary btn-sm" onclick="setDateRange('quarter')">This Quarter</button>
                <button type="button" class="btn btn-outline-secondary btn-sm" onclick="setDateRange('year')">This Year</button>
            </div>
        `;
        filtersSection.insertBefore(quickRanges, filtersSection.firstChild);

        function setDateRange(range) {
            const today = new Date();
            let fromDate, toDate = today;

            switch(range) {
                case 'today':
                    fromDate = today;
                    break;
                case 'week':
                    fromDate = new Date(today.getFullYear(), today.getMonth(), today.getDate() - today.getDay());
                    break;
                case 'month':
                    fromDate = new Date(today.getFullYear(), today.getMonth(), 1);
                    break;
                case 'quarter':
                    const quarter = Math.floor(today.getMonth() / 3);
                    fromDate = new Date(today.getFullYear(), quarter * 3, 1);
                    break;
                case 'year':
                    fromDate = new Date(today.getFullYear(), 0, 1);
                    break;
            }

            document.querySelector('input[name="date_from"]').value = fromDate.toISOString().split('T')[0];
            document.querySelector('input[name="date_to"]').value = toDate.toISOString().split('T')[0];
        }

        // Print optimization
        window.addEventListener('beforeprint', function() {
            // Hide charts temporarily for better print layout
            document.querySelectorAll('.chart-container').forEach(chart => {
                chart.style.height = '200px';
            });
        });

        window.addEventListener('afterprint', function() {
            // Restore chart heights
            document.querySelectorAll('.chart-container').forEach(chart => {
                chart.style.height = '350px';
            });
        });
    </script>
    
    <!-- Print Styles -->
    <style media="print">
        .filters-section, .export-buttons, .btn, .page-title-right {
            display: none !important;
        }
        
        .analytics-card {
            break-inside: avoid;
            margin-bottom: 20px;
        }
        
        .chart-container {
            height: 200px !important;
        }
        
        @page {
            margin: 1cm;
            size: A4;
        }
    </style>

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