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

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

// 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

// Fleet overview statistics
$sql_fleet_overview = "
    SELECT 
        COUNT(DISTINCT v.vehicle_id) as total_vehicles,
        COUNT(DISTINCT CASE WHEN v.status = 'ACTIVE' THEN v.vehicle_id END) as active_vehicles,
        COUNT(DISTINCT CASE WHEN v.status = 'MAINTENANCE' THEN v.vehicle_id END) as maintenance_vehicles,
        COUNT(DISTINCT CASE WHEN v.status = 'INACTIVE' THEN v.vehicle_id END) as inactive_vehicles,
        COUNT(DISTINCT d.driver_id) as total_drivers,
        COUNT(DISTINCT CASE WHEN d.status = 'ACTIVE' THEN d.driver_id END) as active_drivers,
        SUM(v.capacity_weight) as total_capacity_weight,
        SUM(v.capacity_volume) as total_capacity_volume
    FROM vehicles v
    LEFT JOIN vehicle_assignments va ON v.vehicle_id = va.vehicle_id AND va.is_active = 1
    LEFT JOIN drivers d ON va.driver_id = d.driver_id";

$fleet_overview = $conn->query($sql_fleet_overview)->fetch_assoc();

// Vehicle utilization analytics
$sql_utilization = "
    SELECT 
        v.vehicle_id,
        v.vehicle_number,
        v.vehicle_type,
        v.make,
        v.model,
        v.capacity_weight,
        v.status,
        COUNT(DISTINCT sr.run_id) as total_runs,
        COUNT(DISTINCT CASE WHEN sr.status = 'COMPLETED' THEN sr.run_id END) as completed_runs,
        COUNT(DISTINCT DATE(sr.scheduled_departure)) as active_days,
        COALESCE(SUM(sr.capacity_used_weight), 0) as total_weight_carried,
        COALESCE(AVG(sr.capacity_used_weight), 0) as avg_weight_per_run,
        COALESCE(MAX(sr.capacity_used_weight), 0) as max_weight_carried,
        CASE 
            WHEN v.capacity_weight > 0 THEN (COALESCE(AVG(sr.capacity_used_weight), 0) / v.capacity_weight) * 100
            ELSE 0 
        END as utilization_percentage
    FROM vehicles v
    LEFT JOIN scheduled_runs sr ON v.vehicle_id = sr.vehicle_id 
        AND sr.scheduled_departure BETWEEN ? AND ?
    WHERE v.status IN ('ACTIVE', 'MAINTENANCE')
    GROUP BY v.vehicle_id, v.vehicle_number, v.vehicle_type, v.make, v.model, v.capacity_weight, v.status
    ORDER BY utilization_percentage DESC
    LIMIT 10";

$stmt_utilization = $conn->prepare($sql_utilization);
$stmt_utilization->bind_param("ss", $date_from, $date_to);
$stmt_utilization->execute();
$utilization_result = $stmt_utilization->get_result();

// Daily fleet performance
$sql_daily_performance = "
    SELECT 
        DATE(sr.scheduled_departure) as activity_date,
        COUNT(DISTINCT sr.run_id) as total_runs,
        COUNT(DISTINCT CASE WHEN sr.status = 'COMPLETED' THEN sr.run_id END) as completed_runs,
        COUNT(DISTINCT sr.vehicle_id) as vehicles_used,
        COALESCE(SUM(sr.capacity_used_weight), 0) as total_weight,
        COALESCE(AVG(sr.capacity_used_weight), 0) as avg_weight
    FROM scheduled_runs sr
    WHERE sr.scheduled_departure BETWEEN ? AND ?
    GROUP BY DATE(sr.scheduled_departure)
    ORDER BY activity_date DESC";

$stmt_daily = $conn->prepare($sql_daily_performance);
$stmt_daily->bind_param("ss", $date_from, $date_to);
$stmt_daily->execute();
$daily_performance = $stmt_daily->get_result();

// Vehicle type analysis
$sql_vehicle_types = "
    SELECT 
        v.vehicle_type,
        v.fuel_type,
        COUNT(v.vehicle_id) as vehicle_count,
        COUNT(CASE WHEN v.status = 'ACTIVE' THEN 1 END) as active_count,
        SUM(v.capacity_weight) as total_capacity,
        AVG(v.capacity_weight) as avg_capacity,
        COUNT(DISTINCT sr.run_id) as total_runs_period,
        COALESCE(SUM(sr.capacity_used_weight), 0) as weight_carried_period
    FROM vehicles v
    LEFT JOIN scheduled_runs sr ON v.vehicle_id = sr.vehicle_id 
        AND sr.scheduled_departure BETWEEN ? AND ?
    GROUP BY v.vehicle_type, v.fuel_type
    ORDER BY vehicle_count DESC";

$stmt_types = $conn->prepare($sql_vehicle_types);
$stmt_types->bind_param("ss", $date_from, $date_to);
$stmt_types->execute();
$vehicle_types = $stmt_types->get_result();

// Driver performance analytics
$sql_driver_performance = "
    SELECT 
        d.driver_id,
        d.first_name,
        d.last_name,
        d.driver_code,
        v.vehicle_number,
        COUNT(DISTINCT sr.run_id) as total_runs,
        COUNT(DISTINCT CASE WHEN sr.status = 'COMPLETED' THEN sr.run_id END) as completed_runs,
        COUNT(DISTINCT CASE WHEN sr.status = 'CANCELLED' THEN sr.run_id END) as cancelled_runs,
        COALESCE(SUM(sr.capacity_used_weight), 0) as total_weight_handled,
        COALESCE(AVG(sr.capacity_used_weight), 0) as avg_weight_per_run,
        CASE 
            WHEN COUNT(sr.run_id) > 0 THEN (COUNT(CASE WHEN sr.status = 'COMPLETED' THEN 1 END) / COUNT(sr.run_id)) * 100
            ELSE 0 
        END as completion_rate
    FROM drivers d
    LEFT JOIN vehicle_assignments va ON d.driver_id = va.driver_id AND va.is_active = 1
    LEFT JOIN vehicles v ON va.vehicle_id = v.vehicle_id
    LEFT JOIN scheduled_runs sr ON v.vehicle_id = sr.vehicle_id 
        AND sr.scheduled_departure BETWEEN ? AND ?
        AND sr.driver_id = d.driver_id
    WHERE d.status = 'ACTIVE'
    GROUP BY d.driver_id, d.first_name, d.last_name, d.driver_code, v.vehicle_number
    HAVING total_runs > 0
    ORDER BY completion_rate DESC, total_runs DESC
    LIMIT 10";

$stmt_drivers = $conn->prepare($sql_driver_performance);
$stmt_drivers->bind_param("ss", $date_from, $date_to);
$stmt_drivers->execute();
$driver_performance = $stmt_drivers->get_result();

// Route performance
$sql_route_performance = "
    SELECT 
        r.route_id,
        r.route_name,
        r.distance_km,
        ol.location_name as origin_location,
        dl.location_name as destination_location,
        COUNT(DISTINCT sr.run_id) as total_runs,
        COUNT(DISTINCT CASE WHEN sr.status = 'COMPLETED' THEN sr.run_id END) as completed_runs,
        COALESCE(SUM(sr.capacity_used_weight), 0) as total_weight,
        COALESCE(AVG(sr.capacity_used_weight), 0) as avg_weight_per_run,
        COUNT(DISTINCT sr.vehicle_id) as vehicles_used
    FROM routes r
    LEFT JOIN locations ol ON r.origin_location_id = ol.location_id
    LEFT JOIN locations dl ON r.destination_location_id = dl.location_id
    LEFT JOIN scheduled_runs sr ON r.route_id = sr.route_id 
        AND sr.scheduled_departure BETWEEN ? AND ?
    WHERE r.is_active = 1
    GROUP BY r.route_id, r.route_name, r.distance_km, ol.location_name, dl.location_name
    HAVING total_runs > 0
    ORDER BY total_runs DESC
    LIMIT 10";

$stmt_routes = $conn->prepare($sql_route_performance);
$stmt_routes->bind_param("ss", $date_from, $date_to);
$stmt_routes->execute();
$route_performance = $stmt_routes->get_result();

// Maintenance cost analysis
$sql_maintenance_costs = "
    SELECT 
        YEAR(vm.maintenance_date) as year,
        MONTH(vm.maintenance_date) as month,
        vm.maintenance_type,
        COUNT(vm.maintenance_id) as maintenance_count,
        SUM(vm.cost) as total_cost,
        AVG(vm.cost) as avg_cost,
        COUNT(DISTINCT vm.vehicle_id) as vehicles_affected
    FROM vehicle_maintenance vm
    WHERE vm.maintenance_date BETWEEN ? AND ?
    GROUP BY YEAR(vm.maintenance_date), MONTH(vm.maintenance_date), vm.maintenance_type
    ORDER BY year DESC, month DESC, total_cost DESC";

$stmt_maintenance = $conn->prepare($sql_maintenance_costs);
$stmt_maintenance->bind_param("ss", $date_from, $date_to);
$stmt_maintenance->execute();
$maintenance_costs = $stmt_maintenance->get_result();

// Fuel efficiency analysis (if fuel data exists)
$sql_fuel_analysis = "
    SELECT 
        v.vehicle_type,
        v.fuel_type,
        COUNT(DISTINCT v.vehicle_id) as vehicle_count,
        COUNT(DISTINCT sr.run_id) as total_runs,
        COALESCE(SUM(r.distance_km), 0) as total_distance
    FROM vehicles v
    LEFT JOIN scheduled_runs sr ON v.vehicle_id = sr.vehicle_id 
        AND sr.scheduled_departure BETWEEN ? AND ?
        AND sr.status = 'COMPLETED'
    LEFT JOIN routes r ON sr.route_id = r.route_id
    WHERE v.status IN ('ACTIVE', 'MAINTENANCE')
    GROUP BY v.vehicle_type, v.fuel_type
    ORDER BY vehicle_count DESC";

$stmt_fuel = $conn->prepare($sql_fuel_analysis);
$stmt_fuel->bind_param("ss", $date_from, $date_to);
$stmt_fuel->execute();
$fuel_analysis = $stmt_fuel->get_result();

$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Fleet Analytics Dashboard')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    
    <!-- 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">
    
    <!-- Remix Icons -->
    <link href="https://cdn.jsdelivr.net/npm/remixicon@3.5.0/fonts/remixicon.css" rel="stylesheet">
    
    <style>
        .analytics-header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 15px;
            padding: 2rem;
            margin-bottom: 2rem;
            position: relative;
            overflow: hidden;
        }
        
        .analytics-header::before {
            content: '';
            position: absolute;
            top: -50%;
            right: -50%;
            width: 200%;
            height: 200%;
            background: url('data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 100 100"><circle cx="50" cy="50" r="2" fill="rgba(255,255,255,0.1)"/></svg>') repeat;
            transform: rotate(45deg);
            animation: float 20s linear infinite;
        }
        
        @keyframes float {
            0% { transform: translateX(-100%) rotate(45deg); }
            100% { transform: translateX(100%) rotate(45deg); }
        }
        
        .analytics-card {
            background: white;
            border-radius: 15px;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
            border: 1px solid #e5e7eb;
            transition: all 0.3s ease;
            margin-bottom: 1.5rem;
            position: relative;
            overflow: hidden;
        }
        
        .analytics-card::before {
            content: '';
            position: absolute;
            top: 0;
            left: 0;
            width: 4px;
            height: 100%;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        }
        
        .analytics-card:hover {
            box-shadow: 0 10px 25px -5px rgba(0, 0, 0, 0.1);
            transform: translateY(-2px);
        }
        
        .metric-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            border-left: 4px solid #667eea;
            transition: all 0.3s ease;
            position: relative;
            overflow: hidden;
        }
        
        .metric-card::before {
            content: '';
            position: absolute;
            top: 0;
            right: 0;
            width: 60px;
            height: 60px;
            background: rgba(102, 126, 234, 0.1);
            border-radius: 50%;
            transform: translate(20px, -20px);
        }
        
        .metric-card:hover {
            transform: translateY(-2px);
            box-shadow: 0 4px 8px rgba(0,0,0,0.15);
        }
        
        .metric-value {
            font-size: 2.25rem;
            font-weight: 700;
            color: #1f2937;
            margin-bottom: 0.5rem;
        }
        
        .metric-label {
            font-size: 0.875rem;
            color: #6b7280;
            text-transform: uppercase;
            letter-spacing: 0.05em;
            font-weight: 500;
        }
        
        .chart-container {
            position: relative;
            height: 350px;
            margin: 1rem 0;
        }
        
        .small-chart-container {
            position: relative;
            height: 250px;
            margin: 1rem 0;
        }
        
        .utilization-bar {
            height: 8px;
            background: #e5e7eb;
            border-radius: 4px;
            overflow: hidden;
            margin: 0.5rem 0;
        }
        
        .utilization-fill {
            height: 100%;
            border-radius: 4px;
            transition: width 0.3s ease;
        }
        
        .utilization-excellent { background: linear-gradient(45deg, #10b981, #059669); }
        .utilization-good { background: linear-gradient(45deg, #3b82f6, #1d4ed8); }
        .utilization-average { background: linear-gradient(45deg, #f59e0b, #d97706); }
        .utilization-poor { background: linear-gradient(45deg, #ef4444, #dc2626); }
        
        .vehicle-avatar {
            width: 50px;
            height: 50px;
            border-radius: 50%;
            background: linear-gradient(45deg, #667eea, #764ba2);
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: 600;
            font-size: 1rem;
        }
        
        .driver-avatar {
            width: 40px;
            height: 40px;
            border-radius: 50%;
            background: linear-gradient(45deg, #10b981, #059669);
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: 600;
            font-size: 0.875rem;
        }
        
        .status-badge {
            padding: 0.25rem 0.5rem;
            border-radius: 12px;
            font-size: 0.7rem;
            font-weight: 600;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }
        
        .status-active { background: #d1fae5; color: #065f46; }
        .status-maintenance { background: #fef3c7; color: #92400e; }
        .status-inactive { background: #fee2e2; color: #991b1b; }
        
        .performance-indicator {
            display: flex;
            align-items: center;
            gap: 0.5rem;
            margin-bottom: 0.5rem;
        }
        
        .performance-dot {
            width: 12px;
            height: 12px;
            border-radius: 50%;
        }
        
        .performance-excellent { background: #10b981; }
        .performance-good { background: #3b82f6; }
        .performance-average { background: #f59e0b; }
        .performance-poor { background: #ef4444; }
        
        .filter-section {
            background: #f8f9fa;
            border-radius: 12px;
            padding: 1.5rem;
            margin-bottom: 1.5rem;
            border: 1px solid #e9ecef;
        }
        
        .analytics-tabs {
            border-bottom: 2px solid #e5e7eb;
            margin-bottom: 1.5rem;
        }
        
        .analytics-tab {
            padding: 1rem 1.5rem;
            background: none;
            border: none;
            color: #6b7280;
            font-weight: 500;
            cursor: pointer;
            transition: all 0.3s ease;
            border-bottom: 2px solid transparent;
        }
        
        .analytics-tab.active {
            color: #667eea;
            border-bottom-color: #667eea;
        }
        
        .analytics-tab:hover {
            color: #667eea;
        }
        
        .cost-display {
            font-size: 1.1rem;
            font-weight: 700;
        }
        
        .trend-indicator {
            font-size: 0.8rem;
            font-weight: 600;
            padding: 0.2rem 0.5rem;
            border-radius: 8px;
        }
        
        .trend-up { background: #d1fae5; color: #065f46; }
        .trend-down { background: #fee2e2; color: #991b1b; }
        .trend-stable { background: #f3f4f6; color: #374151; }
        
        @media (max-width: 768px) {
            .analytics-header {
                padding: 1.5rem;
                text-align: center;
            }
            
            .metric-card {
                margin-bottom: 1rem;
                text-align: center;
            }
            
            .metric-value {
                font-size: 1.75rem;
            }
            
            .chart-container {
                height: 250px;
            }
        }
    </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-bar-chart-line me-2"></i>Fleet Analytics 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"><a href="vehicles.php">Fleet</a></li>
                                        <li class="breadcrumb-item active">Analytics</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Analytics Header -->
                    <div class="analytics-header">
                        <div class="row align-items-center">
                            <div class="col-md-8">
                                <h2 class="mb-1">Fleet Performance Analytics</h2>
                                <p class="mb-0 opacity-90">
                                    Comprehensive insights into your fleet operations, utilization, and performance metrics
                                </p>
                            </div>
                            <div class="col-md-4 text-md-end">
                                <button class="btn btn-light btn-lg" onclick="exportAnalytics()">
                                    <i class="ri-download-line me-2"></i>Export Report
                                </button>
                            </div>
                        </div>
                    </div>

                    <!-- Date Filter -->
                    <div class="filter-section">
                        <form method="GET" class="row g-3 align-items-end">
                            <div class="col-md-3">
                                <label for="date_from" class="form-label">From Date</label>
                                <input type="date" class="form-control" name="date_from" id="date_from" value="<?php echo $date_from; ?>">
                            </div>
                            <div class="col-md-3">
                                <label for="date_to" class="form-label">To Date</label>
                                <input type="date" class="form-control" name="date_to" id="date_to" value="<?php echo $date_to; ?>">
                            </div>
                            <div class="col-md-2">
                                <button type="submit" class="btn btn-primary w-100">
                                    <i class="ri-search-line me-1"></i>Update
                                </button>
                            </div>
                            <div class="col-md-4 text-md-end">
                                <div class="btn-group" role="group">
                                    <button type="button" class="btn btn-outline-secondary" onclick="setDateRange('today')">Today</button>
                                    <button type="button" class="btn btn-outline-secondary" onclick="setDateRange('week')">This Week</button>
                                    <button type="button" class="btn btn-outline-secondary" onclick="setDateRange('month')">This Month</button>
                                    <button type="button" class="btn btn-outline-secondary" onclick="setDateRange('quarter')">This Quarter</button>
                                </div>
                            </div>
                        </form>
                    </div>

                    <!-- Fleet Overview Metrics -->
                    <div class="row mb-4">
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="metric-card" style="border-left-color: #667eea;">
                                <div class="metric-value"><?php echo number_format($fleet_overview['total_vehicles']); ?></div>
                                <div class="metric-label">Total Vehicles</div>
                            </div>
                        </div>
                        
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="metric-card" style="border-left-color: #10b981;">
                                <div class="metric-value"><?php echo number_format($fleet_overview['active_vehicles']); ?></div>
                                <div class="metric-label">Active Vehicles</div>
                            </div>
                        </div>
                        
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="metric-card" style="border-left-color: #f59e0b;">
                                <div class="metric-value"><?php echo number_format($fleet_overview['maintenance_vehicles']); ?></div>
                                <div class="metric-label">In Maintenance</div>
                            </div>
                        </div>
                        
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="metric-card" style="border-left-color: #06b6d4;">
                                <div class="metric-value"><?php echo number_format($fleet_overview['active_drivers']); ?></div>
                                <div class="metric-label">Active Drivers</div>
                            </div>
                        </div>
                        
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="metric-card" style="border-left-color: #8b5cf6;">
                                <div class="metric-value"><?php echo number_format($fleet_overview['total_capacity_weight']); ?>kg</div>
                                <div class="metric-label">Total Capacity</div>
                            </div>
                        </div>
                        
                        <div class="col-xl-2 col-md-4 col-6">
                            <div class="metric-card" style="border-left-color: #ef4444;">
                                <div class="metric-value">
                                    <?php 
                                    $fleet_utilization = $fleet_overview['active_vehicles'] > 0 ? 
                                        round(($fleet_overview['active_vehicles'] / $fleet_overview['total_vehicles']) * 100, 1) : 0;
                                    echo $fleet_utilization;
                                    ?>%
                                </div>
                                <div class="metric-label">Fleet Utilization</div>
                            </div>
                        </div>
                    </div>

                    <!-- Main Analytics Content -->
                    <div class="row">
                        <div class="col-xl-8">
                            <!-- Daily Performance Chart -->
                            <div class="analytics-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-line-chart-line me-2"></i>Daily Fleet Performance
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="chart-container">
                                        <canvas id="dailyPerformanceChart"></canvas>
                                    </div>
                                </div>
                            </div>

                            <!-- Vehicle Utilization Table -->
                            <div class="analytics-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-truck-line me-2"></i>Vehicle Utilization Analysis
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover" id="utilizationTable">
                                            <thead>
                                                <tr>
                                                    <th>Vehicle</th>
                                                    <th>Type</th>
                                                    <th>Capacity</th>
                                                    <th>Runs</th>
                                                    <th>Utilization</th>
                                                    <th>Avg Load</th>
                                                    <th>Status</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($vehicle = $utilization_result->fetch_assoc()): ?>
                                                <?php 
                                                $utilization_class = 'utilization-poor';
                                                $performance_class = 'performance-poor';
                                                if ($vehicle['utilization_percentage'] >= 80) {
                                                    $utilization_class = 'utilization-excellent';
                                                    $performance_class = 'performance-excellent';
                                                } elseif ($vehicle['utilization_percentage'] >= 60) {
                                                    $utilization_class = 'utilization-good';
                                                    $performance_class = 'performance-good';
                                                } elseif ($vehicle['utilization_percentage'] >= 40) {
                                                    $utilization_class = 'utilization-average';
                                                    $performance_class = 'performance-average';
                                                }
                                                ?>
                                                <tr>
                                                    <td>
                                                        <div class="d-flex align-items-center">
                                                            <div class="vehicle-avatar me-3" style="width: 40px; height: 40px; font-size: 0.8rem;">
                                                                <?php echo strtoupper(substr($vehicle['vehicle_number'], 0, 3)); ?>
                                                            </div>
                                                            <div>
                                                                <div class="fw-bold"><?php echo $vehicle['vehicle_number']; ?></div>
                                                                <small class="text-muted"><?php echo $vehicle['make'] . ' ' . $vehicle['model']; ?></small>
                                                            </div>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-secondary"><?php echo ucfirst($vehicle['vehicle_type']); ?></span>
                                                    </td>
                                                    <td class="fw-bold"><?php echo number_format($vehicle['capacity_weight']); ?>kg</td>
                                                    <td>
                                                        <div class="fw-bold"><?php echo $vehicle['total_runs']; ?></div>
                                                        <small class="text-success"><?php echo $vehicle['completed_runs']; ?> completed</small>
                                                    </td>
                                                    <td>
                                                        <div class="performance-indicator">
                                                            <div class="performance-dot <?php echo $performance_class; ?>"></div>
                                                            <span class="fw-bold"><?php echo number_format($vehicle['utilization_percentage'], 1); ?>%</span>
                                                        </div>
                                                        <div class="utilization-bar">
                                                            <div class="utilization-fill <?php echo $utilization_class; ?>" 
                                                                 style="width: <?php echo min($vehicle['utilization_percentage'], 100); ?>%;"></div>
                                                        </div>
                                                    </td>
                                                    <td class="fw-bold"><?php echo number_format($vehicle['avg_weight_per_run'], 1); ?>kg</td>
                                                    <td>
                                                        <span class="status-badge status-<?php echo strtolower($vehicle['status']); ?>">
                                                            <?php echo $vehicle['status']; ?>
                                                        </span>
                                                    </td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-4">
                            <!-- Vehicle Type Distribution -->
                            <div class="analytics-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-pie-chart-line me-2"></i>Fleet Composition
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="small-chart-container">
                                        <canvas id="vehicleTypesChart"></canvas>
                                    </div>
                                </div>
                            </div>

                            <!-- Top Performing Drivers -->
                            <div class="analytics-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-user-star-line me-2"></i>Top Performing Drivers
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <?php if ($driver_performance->num_rows > 0): ?>
                                        <?php while ($driver = $driver_performance->fetch_assoc()): ?>
                                        <div class="d-flex align-items-center mb-3">
                                            <div class="driver-avatar me-3">
                                                <?php echo strtoupper(substr($driver['first_name'], 0, 1) . substr($driver['last_name'], 0, 1)); ?>
                                            </div>
                                            <div class="flex-grow-1">
                                                <div class="fw-bold"><?php echo $driver['first_name'] . ' ' . $driver['last_name']; ?></div>
                                                <small class="text-muted"><?php echo $driver['driver_code']; ?> | <?php echo $driver['vehicle_number']; ?></small>
                                                <div class="d-flex justify-content-between mt-1">
                                                    <small class="text-muted"><?php echo $driver['total_runs']; ?> runs</small>
                                                    <small class="fw-bold text-success"><?php echo number_format($driver['completion_rate'], 1); ?>%</small>
                                                </div>
                                            </div>
                                        </div>
                                        <?php endwhile; ?>
                                    <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="ri-user-line fs-2 text-muted"></i>
                                        <p class="text-muted mt-2">No driver data available for the selected period</p>
                                    </div>
                                    <?php endif; ?>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Additional Analytics Sections -->
                    <div class="row">
                        <div class="col-xl-6">
                            <!-- Route Performance -->
                            <div class="analytics-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 Activity
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-sm">
                                            <thead>
                                                <tr>
                                                    <th>Route</th>
                                                    <th>Distance</th>
                                                    <th>Runs</th>
                                                    <th>Weight</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php if ($route_performance->num_rows > 0): ?>
                                                    <?php while ($route = $route_performance->fetch_assoc()): ?>
                                                    <tr>
                                                        <td>
                                                            <div class="fw-bold"><?php echo $route['route_name']; ?></div>
                                                            <small class="text-muted">
                                                                <?php echo $route['origin_location'] . ' → ' . $route['destination_location']; ?>
                                                            </small>
                                                        </td>
                                                        <td><?php echo number_format($route['distance_km']); ?>km</td>
                                                        <td class="fw-bold"><?php echo $route['total_runs']; ?></td>
                                                        <td><?php echo number_format($route['total_weight']); ?>kg</td>
                                                    </tr>
                                                    <?php endwhile; ?>
                                                <?php else: ?>
                                                    <tr>
                                                        <td colspan="4" class="text-center text-muted py-3">
                                                            No route data available for the selected period
                                                        </td>
                                                    </tr>
                                                <?php endif; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-6">
                            <!-- Maintenance Cost Analysis -->
                            <div class="analytics-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-tools-line me-2"></i>Maintenance Cost Analysis
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <?php if ($maintenance_costs->num_rows > 0): ?>
                                        <?php 
                                        $total_maintenance_cost = 0;
                                        $maintenance_data = [];
                                        while ($maintenance = $maintenance_costs->fetch_assoc()) {
                                            $total_maintenance_cost += $maintenance['total_cost'];
                                            $maintenance_data[] = $maintenance;
                                        }
                                        ?>
                                        <div class="row text-center mb-3">
                                            <div class="col-6">
                                                <div class="fw-bold h4 text-danger">KES <?php echo number_format($total_maintenance_cost); ?></div>
                                                <small class="text-muted">Total Cost</small>
                                            </div>
                                            <div class="col-6">
                                                <div class="fw-bold h4 text-primary"><?php echo count($maintenance_data); ?></div>
                                                <small class="text-muted">Maintenance Events</small>
                                            </div>
                                        </div>
                                        
                                        <div class="table-responsive">
                                            <table class="table table-sm">
                                                <thead>
                                                    <tr>
                                                        <th>Type</th>
                                                        <th>Count</th>
                                                        <th>Total Cost</th>
                                                        <th>Avg Cost</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php foreach ($maintenance_data as $maintenance): ?>
                                                    <tr>
                                                        <td><?php echo ucfirst(strtolower($maintenance['maintenance_type'])); ?></td>
                                                        <td><?php echo $maintenance['maintenance_count']; ?></td>
                                                        <td class="cost-display">KES <?php echo number_format($maintenance['total_cost']); ?></td>
                                                        <td>KES <?php echo number_format($maintenance['avg_cost']); ?></td>
                                                    </tr>
                                                    <?php endforeach; ?>
                                                </tbody>
                                            </table>
                                        </div>
                                    <?php else: ?>
                                    <div class="text-center py-4">
                                        <i class="ri-tools-line fs-2 text-muted"></i>
                                        <p class="text-muted mt-2">No maintenance data available for the selected period</p>
                                    </div>
                                    <?php endif; ?>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Fuel Efficiency Analysis -->
                    <div class="row">
                        <div class="col-12">
                            <div class="analytics-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-gas-station-line me-2"></i>Fleet Efficiency by Type & Fuel
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover" id="efficiencyTable">
                                            <thead>
                                                <tr>
                                                    <th>Vehicle Type</th>
                                                    <th>Fuel Type</th>
                                                    <th>Vehicle Count</th>
                                                    <th>Total Runs</th>
                                                    <th>Distance Covered</th>
                                                    <th>Efficiency Score</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php if ($fuel_analysis->num_rows > 0): ?>
                                                    <?php while ($fuel = $fuel_analysis->fetch_assoc()): ?>
                                                    <?php 
                                                    // Calculate efficiency score based on runs per vehicle
                                                    $efficiency_score = $fuel['vehicle_count'] > 0 ? 
                                                        ($fuel['total_runs'] / $fuel['vehicle_count']) : 0;
                                                    
                                                    $efficiency_class = 'text-muted';
                                                    if ($efficiency_score >= 20) $efficiency_class = 'text-success';
                                                    elseif ($efficiency_score >= 10) $efficiency_class = 'text-primary';
                                                    elseif ($efficiency_score >= 5) $efficiency_class = 'text-warning';
                                                    else $efficiency_class = 'text-danger';
                                                    ?>
                                                    <tr>
                                                        <td>
                                                            <span class="fw-bold"><?php echo ucfirst($fuel['vehicle_type']); ?></span>
                                                        </td>
                                                        <td>
                                                            <span class="badge bg-info"><?php echo ucfirst($fuel['fuel_type']); ?></span>
                                                        </td>
                                                        <td><?php echo $fuel['vehicle_count']; ?></td>
                                                        <td><?php echo number_format($fuel['total_runs']); ?></td>
                                                        <td><?php echo number_format($fuel['total_distance']); ?> km</td>
                                                        <td>
                                                            <span class="fw-bold <?php echo $efficiency_class; ?>">
                                                                <?php echo number_format($efficiency_score, 1); ?>
                                                            </span>
                                                            <small class="text-muted">runs/vehicle</small>
                                                        </td>
                                                    </tr>
                                                    <?php endwhile; ?>
                                                <?php else: ?>
                                                    <tr>
                                                        <td colspan="6" class="text-center text-muted py-3">
                                                            No fuel efficiency data available for the selected period
                                                        </td>
                                                    </tr>
                                                <?php endif; ?>
                                            </tbody>
                                        </table>
                                    </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>
    
    <script>
$(document).ready(function() {
    // Initialize DataTables
    $('#utilizationTable').DataTable({
        responsive: true,
        pageLength: 10,
        order: [[4, 'desc']], // Sort by utilization
        columnDefs: [
            { targets: [4], className: 'text-center' }
        ]
    });

    $('#efficiencyTable').DataTable({
        responsive: true,
        pageLength: 10,
        order: [[5, 'desc']] // Sort by efficiency score
    });

    // Initialize charts
    initializeDailyPerformanceChart();
    initializeVehicleTypesChart();
});

// Initialize daily performance chart
function initializeDailyPerformanceChart() {
    const ctx = document.getElementById('dailyPerformanceChart');
    if (!ctx) return;

    // Get data from PHP
    const dailyData = <?php 
    $daily_data = [];
    $daily_performance->data_seek(0);
    while ($row = $daily_performance->fetch_assoc()) {
        $daily_data[] = $row;
    }
    echo json_encode(array_reverse($daily_data)); // Reverse to show chronological order
    ?>;

    const labels = dailyData.map(item => {
        const date = new Date(item.activity_date);
        return date.toLocaleDateString('en-US', { month: 'short', day: 'numeric' });
    });

    new Chart(ctx, {
        type: 'line',
        data: {
            labels: labels,
            datasets: [{
                label: 'Total Runs',
                data: dailyData.map(item => parseInt(item.total_runs)),
                borderColor: '#667eea',
                backgroundColor: 'rgba(102, 126, 234, 0.1)',
                tension: 0.4,
                yAxisID: 'y'
            }, {
                label: 'Completed Runs',
                data: dailyData.map(item => parseInt(item.completed_runs)),
                borderColor: '#10b981',
                backgroundColor: 'rgba(16, 185, 129, 0.1)',
                tension: 0.4,
                yAxisID: 'y'
            }, {
                label: 'Vehicles Used',
                data: dailyData.map(item => parseInt(item.vehicles_used)),
                borderColor: '#f59e0b',
                backgroundColor: 'rgba(245, 158, 11, 0.1)',
                tension: 0.4,
                yAxisID: 'y'
            }, {
                label: 'Total Weight (kg)',
                data: dailyData.map(item => parseFloat(item.total_weight)),
                borderColor: '#ef4444',
                backgroundColor: 'rgba(239, 68, 68, 0.1)',
                tension: 0.4,
                yAxisID: 'y1'
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            interaction: {
                intersect: false,
                mode: 'index'
            },
            plugins: {
                legend: {
                    position: 'top'
                }
            },
            scales: {
                y: {
                    type: 'linear',
                    display: true,
                    position: 'left',
                    title: {
                        display: true,
                        text: 'Runs / Vehicles'
                    }
                },
                y1: {
                    type: 'linear',
                    display: true,
                    position: 'right',
                    title: {
                        display: true,
                        text: 'Weight (kg)'
                    },
                    grid: {
                        drawOnChartArea: false,
                    }
                }
            }
        }
    });
}

// Initialize vehicle types chart
function initializeVehicleTypesChart() {
    const ctx = document.getElementById('vehicleTypesChart');
    if (!ctx) return;

    // Get data from PHP
    const vehicleTypesData = <?php 
    $types_data = [];
    $vehicle_types->data_seek(0);
    while ($row = $vehicle_types->fetch_assoc()) {
        $types_data[] = $row;
    }
    echo json_encode($types_data);
    ?>;

    const labels = vehicleTypesData.map(item => item.vehicle_type + ' (' + item.fuel_type + ')');
    const data = vehicleTypesData.map(item => parseInt(item.vehicle_count));
    const colors = [
        'rgba(102, 126, 234, 0.8)',
        'rgba(16, 185, 129, 0.8)',
        'rgba(245, 158, 11, 0.8)',
        'rgba(239, 68, 68, 0.8)',
        'rgba(139, 92, 246, 0.8)',
        'rgba(6, 182, 212, 0.8)'
    ];

    new Chart(ctx, {
        type: 'doughnut',
        data: {
            labels: labels,
            datasets: [{
                data: data,
                backgroundColor: colors.slice(0, data.length),
                borderColor: colors.slice(0, data.length).map(color => color.replace('0.8', '1')),
                borderWidth: 2
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: {
                    position: 'bottom'
                }
            }
        }
    });
}

// Date range functions
function setDateRange(range) {
    const today = new Date();
    let fromDate, toDate;
    
    switch(range) {
        case 'today':
            fromDate = toDate = today.toISOString().split('T')[0];
            break;
        case 'week':
            const weekStart = new Date(today.setDate(today.getDate() - today.getDay()));
            fromDate = weekStart.toISOString().split('T')[0];
            toDate = new Date().toISOString().split('T')[0];
            break;
        case 'month':
            fromDate = new Date(today.getFullYear(), today.getMonth(), 1).toISOString().split('T')[0];
            toDate = new Date().toISOString().split('T')[0];
            break;
        case 'quarter':
            const quarter = Math.floor(today.getMonth() / 3);
            fromDate = new Date(today.getFullYear(), quarter * 3, 1).toISOString().split('T')[0];
            toDate = new Date().toISOString().split('T')[0];
            break;
    }
    
    document.getElementById('date_from').value = fromDate;
    document.getElementById('date_to').value = toDate;
    document.querySelector('form').submit();
}

// Export analytics
function exportAnalytics() {
    Swal.fire({
        title: 'Export Fleet Analytics',
        html: `
            <div class="mb-3">
                <label class="form-label">Report Type:</label>
                <select class="form-select" id="reportType">
                    <option value="summary">Executive Summary</option>
                    <option value="detailed">Detailed Analytics</option>
                    <option value="utilization">Utilization Report</option>
                    <option value="performance">Performance Report</option>
                    <option value="maintenance">Maintenance Analysis</option>
                    <option value="comprehensive">Comprehensive Report</option>
                </select>
            </div>
            <div class="mb-3">
                <label class="form-label">Format:</label>
                <select class="form-select" id="exportFormat">
                    <option value="pdf">PDF Report</option>
                    <option value="excel">Excel Workbook</option>
                    <option value="csv">CSV Data</option>
                </select>
            </div>
            <div class="mb-3">
                <label class="form-label">Include:</label>
                <div class="form-check">
                    <input class="form-check-input" type="checkbox" id="includeCharts" checked>
                    <label class="form-check-label" for="includeCharts">Charts & Graphs</label>
                </div>
                <div class="form-check">
                    <input class="form-check-input" type="checkbox" id="includeDetails" checked>
                    <label class="form-check-label" for="includeDetails">Detailed Tables</label>
                </div>
                <div class="form-check">
                    <input class="form-check-input" type="checkbox" id="includeRecommendations">
                    <label class="form-check-label" for="includeRecommendations">Recommendations</label>
                </div>
            </div>
        `,
        showCancelButton: true,
        confirmButtonText: 'Generate Report',
        preConfirm: () => {
            return {
                type: document.getElementById('reportType').value,
                format: document.getElementById('exportFormat').value,
                charts: document.getElementById('includeCharts').checked,
                details: document.getElementById('includeDetails').checked,
                recommendations: document.getElementById('includeRecommendations').checked
            };
        }
    }).then((result) => {
        if (result.isConfirmed) {
            const { type, format, charts, details, recommendations } = result.value;
            
            Swal.fire({
                title: 'Generating Report...',
                text: 'Please wait while we compile your analytics report.',
                icon: 'info',
                allowOutsideClick: false,
                showConfirmButton: false,
                didOpen: () => {
                    Swal.showLoading();
                    setTimeout(() => {
                        Swal.close();
                        
                        // Trigger download
                        const link = document.createElement('a');
                        const params = new URLSearchParams({
                            type: type,
                            format: format,
                            charts: charts,
                            details: details,
                            recommendations: recommendations,
                            date_from: '<?php echo $date_from; ?>',
                            date_to: '<?php echo $date_to; ?>'
                        });
                        link.href = `reports/fleet_analytics_report.php?${params.toString()}`;
                        link.download = `fleet_analytics_${type}_${new Date().toISOString().split('T')[0]}.${format}`;
                        link.click();
                        
                        Swal.fire({
                            title: 'Report Generated!',
                            text: 'Your fleet analytics report has been generated and downloaded.',
                            icon: 'success',
                            timer: 2000,
                            showConfirmButton: false
                        });
                    }, 3000);
                }
            });
        }
    });
}

// Initialize SweetAlert2 for export functionality
if (typeof Swal === 'undefined') {
    // Load SweetAlert2 if not already loaded
    const script = document.createElement('script');
    script.src = 'https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.min.js';
    document.head.appendChild(script);
    
    const link = document.createElement('link');
    link.rel = 'stylesheet';
    link.href = 'https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.min.css';
    document.head.appendChild(link);
}
</script>

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