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

// Handle AJAX requests
if (isset($_POST['action'])) {
    header('Content-Type: application/json');
    
    switch ($_POST['action']) {
        case 'add_route':
            $route_code = trim($_POST['route_code']);
            $route_name = trim($_POST['route_name']);
            $origin_location_id = intval($_POST['origin_location_id']);
            $destination_location_id = intval($_POST['destination_location_id']);
            $distance_km = floatval($_POST['distance_km']);
            $estimated_time_hours = floatval($_POST['estimated_time_hours']);
            $service_frequency = $_POST['service_frequency'];
            $is_active = 1;
            
            // Check for duplicate route code
            $check_sql = "SELECT route_id FROM routes WHERE route_code = ?";
            $stmt_check = $conn->prepare($check_sql);
            $stmt_check->bind_param("s", $route_code);
            $stmt_check->execute();
            
            if ($stmt_check->get_result()->num_rows > 0) {
                echo json_encode(['success' => false, 'message' => 'Route code already exists']);
                exit();
            }
            
            $insert_sql = "INSERT INTO routes (route_code, route_name, origin_location_id, 
                          destination_location_id, distance_km, estimated_time_hours, 
                          service_frequency, is_active) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt_insert = $conn->prepare($insert_sql);
            $stmt_insert->bind_param("ssiiddsi", $route_code, $route_name, $origin_location_id, 
                                   $destination_location_id, $distance_km, $estimated_time_hours, 
                                   $service_frequency, $is_active);
            
            if ($stmt_insert->execute()) {
                $route_id = $conn->insert_id;
                
                // Log activity
                $log_sql = "INSERT INTO activity_log (user_id, action, module, record_id, new_values) 
                           VALUES (?, 'CREATE', 'routes', ?, ?)";
                $stmt_log = $conn->prepare($log_sql);
                $details = json_encode(['route_code' => $route_code, 'route_name' => $route_name]);
                $stmt_log->bind_param("iss", $user_id, $route_id, $details);
                $stmt_log->execute();
                
                echo json_encode(['success' => true, 'message' => 'Route added successfully', 'route_id' => $route_id]);
            } else {
                echo json_encode(['success' => false, 'message' => 'Failed to add route']);
            }
            break;
            
        case 'update_route':
            $route_id = intval($_POST['route_id']);
            $route_code = trim($_POST['route_code']);
            $route_name = trim($_POST['route_name']);
            $origin_location_id = intval($_POST['origin_location_id']);
            $destination_location_id = intval($_POST['destination_location_id']);
            $distance_km = floatval($_POST['distance_km']);
            $estimated_time_hours = floatval($_POST['estimated_time_hours']);
            $service_frequency = $_POST['service_frequency'];
            $is_active = intval($_POST['is_active']);
            
            $update_sql = "UPDATE routes SET route_code = ?, route_name = ?, 
                          origin_location_id = ?, destination_location_id = ?, 
                          distance_km = ?, estimated_time_hours = ?, 
                          service_frequency = ?, is_active = ? WHERE route_id = ?";
            $stmt_update = $conn->prepare($update_sql);
            $stmt_update->bind_param("ssiiddsii", $route_code, $route_name, $origin_location_id, 
                                   $destination_location_id, $distance_km, $estimated_time_hours, 
                                   $service_frequency, $is_active, $route_id);
            
            if ($stmt_update->execute()) {
                echo json_encode(['success' => true, 'message' => 'Route updated successfully']);
            } else {
                echo json_encode(['success' => false, 'message' => 'Failed to update route']);
            }
            break;
            
        case 'schedule_run':
            $route_id = intval($_POST['route_id']);
            $vehicle_id = intval($_POST['vehicle_id']);
            $driver_id = intval($_POST['driver_id']);
            $scheduled_departure = $_POST['scheduled_departure'];
            $scheduled_arrival = $_POST['scheduled_arrival'];
            
            // Generate run number
            $run_number = 'RUN' . date('Y') . str_pad(rand(1, 9999), 4, '0', STR_PAD_LEFT);
            
            $schedule_sql = "INSERT INTO scheduled_runs (run_number, route_id, vehicle_id, driver_id, 
                            scheduled_departure, scheduled_arrival, status, created_by) 
                            VALUES (?, ?, ?, ?, ?, ?, 'SCHEDULED', ?)";
            $stmt_schedule = $conn->prepare($schedule_sql);
            $stmt_schedule->bind_param("siiissi", $run_number, $route_id, $vehicle_id, $driver_id, 
                                     $scheduled_departure, $scheduled_arrival, $user_id);
            
            if ($stmt_schedule->execute()) {
                echo json_encode(['success' => true, 'message' => 'Run scheduled successfully', 'run_number' => $run_number]);
            } else {
                echo json_encode(['success' => false, 'message' => 'Failed to schedule run']);
            }
            break;
            
        default:
            echo json_encode(['success' => false, 'message' => 'Invalid action']);
    }
    exit();
}

// Filter parameters
$status_filter = $_GET['status'] ?? '';
$frequency_filter = $_GET['frequency'] ?? '';
$origin_filter = $_GET['origin'] ?? '';
$destination_filter = $_GET['destination'] ?? '';

// Build WHERE clause
$where_conditions = [];
$params = [];
$param_types = '';

if ($status_filter !== '') {
    $where_conditions[] = "r.is_active = ?";
    $params[] = intval($status_filter);
    $param_types .= 'i';
}

if ($frequency_filter) {
    $where_conditions[] = "r.service_frequency = ?";
    $params[] = $frequency_filter;
    $param_types .= 's';
}

if ($origin_filter) {
    $where_conditions[] = "ol.location_name LIKE ?";
    $params[] = "%$origin_filter%";
    $param_types .= 's';
}

if ($destination_filter) {
    $where_conditions[] = "dl.location_name LIKE ?";
    $params[] = "%$destination_filter%";
    $param_types .= 's';
}

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

// Get routes with location details and performance metrics
$sql_routes = "
    SELECT 
        r.*,
        ol.location_name as origin_name,
        ol.city as origin_city,
        oc.country_name as origin_country,
        dl.location_name as destination_name,
        dl.city as destination_city,
        dc.country_name as destination_country,
        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,
        COUNT(DISTINCT CASE WHEN sr.status = 'SCHEDULED' THEN sr.run_id END) as scheduled_runs,
        AVG(CASE WHEN sr.status = 'COMPLETED' AND sr.actual_departure IS NOT NULL AND sr.actual_arrival IS NOT NULL 
                 THEN TIMESTAMPDIFF(HOUR, sr.actual_departure, sr.actual_arrival) END) as avg_actual_time,
        SUM(CASE WHEN sr.status = 'COMPLETED' THEN sr.capacity_used_weight ELSE 0 END) as total_weight_transported,
        AVG(CASE WHEN sr.status = 'COMPLETED' THEN sr.capacity_used_weight ELSE 0 END) as avg_weight_per_run,
        CASE 
            WHEN COUNT(DISTINCT sr.run_id) > 0 
            THEN (COUNT(DISTINCT CASE WHEN sr.status = 'COMPLETED' THEN sr.run_id END) / COUNT(DISTINCT sr.run_id)) * 100 
            ELSE 0 
        END as success_rate
    FROM routes r
    LEFT JOIN locations ol ON r.origin_location_id = ol.location_id
    LEFT JOIN countries oc ON ol.country_id = oc.country_id
    LEFT JOIN locations dl ON r.destination_location_id = dl.location_id
    LEFT JOIN countries dc ON dl.country_id = dc.country_id
    LEFT JOIN scheduled_runs sr ON r.route_id = sr.route_id AND sr.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
    $where_clause
    GROUP BY r.route_id, r.route_code, r.route_name, r.origin_location_id, r.destination_location_id,
             r.distance_km, r.estimated_time_hours, r.service_frequency, r.is_active, r.created_at,
             ol.location_name, ol.city, oc.country_name, dl.location_name, dl.city, dc.country_name
    ORDER BY r.route_code";

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

// Get summary statistics
$sql_summary = "
    SELECT 
        COUNT(*) as total_routes,
        SUM(CASE WHEN r.is_active = 1 THEN 1 ELSE 0 END) as active_routes,
        SUM(CASE WHEN r.is_active = 0 THEN 1 ELSE 0 END) as inactive_routes,
        AVG(r.distance_km) as avg_distance,
        AVG(r.estimated_time_hours) as avg_time,
        SUM(r.distance_km) as total_network_distance,
        COUNT(DISTINCT r.origin_location_id) as unique_origins,
        COUNT(DISTINCT r.destination_location_id) as unique_destinations
    FROM routes r
    LEFT JOIN locations ol ON r.origin_location_id = ol.location_id
    LEFT JOIN countries oc ON ol.country_id = oc.country_id
    LEFT JOIN locations dl ON r.destination_location_id = dl.location_id
    LEFT JOIN countries dc ON dl.country_id = dc.country_id
    $where_clause";

$stmt_summary = $conn->prepare($sql_summary);
if (!empty($params)) {
    $stmt_summary->bind_param($param_types, ...$params);
}
$stmt_summary->execute();
$summary = $stmt_summary->get_result()->fetch_assoc();

// Get locations for dropdowns
$sql_locations = "SELECT l.location_id, l.location_name, l.city, c.country_name 
                  FROM locations l
                  LEFT JOIN countries c ON l.country_id = c.country_id
                  WHERE l.is_active = 1 
                  ORDER BY l.location_name";
$locations_result = $conn->query($sql_locations);

// Get vehicles for scheduling
$sql_vehicles = "SELECT v.vehicle_id, v.vehicle_number, v.vehicle_type, v.status 
                 FROM vehicles v WHERE v.status = 'ACTIVE' ORDER BY v.vehicle_number";
$vehicles_result = $conn->query($sql_vehicles);

// Get drivers for scheduling
$sql_drivers = "SELECT d.driver_id, d.driver_code, d.first_name, d.last_name, d.status 
                FROM drivers d WHERE d.status = 'ACTIVE' ORDER BY d.first_name, d.last_name";
$drivers_result = $conn->query($sql_drivers);

$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Route Management')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <!-- DataTables CSS -->
    <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/responsive/2.5.0/css/responsive.bootstrap5.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.bootstrap5.min.css" rel="stylesheet">
    
    <!-- SweetAlert2 CSS -->
    <link href="https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.min.css" rel="stylesheet">
    
    <!-- Remix Icons -->
    <link href="https://cdn.jsdelivr.net/npm/remixicon@3.5.0/fonts/remixicon.css" rel="stylesheet">
    
    <style>
        .route-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 2px 8px rgba(0,0,0,0.1);
            border: 1px solid #e3e6f0;
            transition: all 0.3s ease;
            margin-bottom: 1.5rem;
        }
        
        .route-card:hover {
            box-shadow: 0 4px 15px rgba(0,0,0,0.15);
            transform: translateY(-2px);
        }
        
        .summary-card {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border-radius: 15px;
            padding: 1.5rem;
            position: relative;
            overflow: hidden;
            margin-bottom: 1.5rem;
        }
        
        .summary-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);
        }
        
        .summary-value {
            font-size: 2rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
        }
        
        .summary-label {
            font-size: 0.9rem;
            opacity: 0.9;
        }
        
        .route-path {
            display: flex;
            align-items: center;
            gap: 0.5rem;
            margin-bottom: 0.5rem;
        }
        
        .route-arrow {
            color: #3b82f6;
            font-size: 1.2rem;
        }
        
        .location-badge {
            background: #f8f9fa;
            border: 1px solid #e9ecef;
            border-radius: 8px;
            padding: 0.5rem;
            font-size: 0.875rem;
            flex: 1;
        }
        
        .location-name {
            font-weight: 600;
            color: #2c3e50;
        }
        
        .location-city {
            color: #6c757d;
            font-size: 0.75rem;
        }
        
        .route-stats {
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 1rem;
            margin-top: 0.5rem;
        }
        
        .stat-item {
            text-align: center;
        }
        
        .stat-value {
            font-size: 1.25rem;
            font-weight: 700;
            color: #2c3e50;
        }
        
        .stat-label {
            font-size: 0.75rem;
            color: #6c757d;
            text-transform: uppercase;
        }
        
        .frequency-badge {
            padding: 0.25rem 0.5rem;
            border-radius: 8px;
            font-size: 0.7rem;
            font-weight: 600;
            text-transform: uppercase;
        }
        
        .freq-daily { background: #d1e7dd; color: #0a3622; }
        .freq-weekly { background: #cff4fc; color: #055160; }
        .freq-biweekly { background: #fff3cd; color: #664d03; }
        .freq-monthly { background: #f8d7da; color: #58151c; }
        .freq-on_demand { background: #e2e3e5; color: #41464b; }
        
        .status-badge {
            padding: 0.35rem 0.75rem;
            border-radius: 15px;
            font-size: 0.75rem;
            font-weight: 600;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }
        
        .status-active { background: #d1e7dd; color: #0a3622; }
        .status-inactive { background: #f8d7da; color: #58151c; }
        
        .performance-indicator {
            background: #f8f9fa;
            border-radius: 8px;
            padding: 0.5rem;
            margin-top: 0.5rem;
        }
        
        .performance-bar {
            height: 4px;
            background: #e9ecef;
            border-radius: 2px;
            overflow: hidden;
            margin-top: 0.25rem;
        }
        
        .performance-fill {
            height: 100%;
            transition: width 0.3s ease;
        }
        
        .performance-excellent { background: linear-gradient(90deg, #28a745, #20c997); }
        .performance-good { background: linear-gradient(90deg, #20c997, #17a2b8); }
        .performance-average { background: linear-gradient(90deg, #ffc107, #fd7e14); }
        .performance-poor { background: linear-gradient(90deg, #fd7e14, #dc3545); }
        
        .route-efficiency {
            background: #f8f9fa;
            border-radius: 8px;
            padding: 0.5rem;
            margin-top: 0.5rem;
        }
        
        .filter-section {
            background: #f8f9fa;
            border-radius: 12px;
            padding: 1.5rem;
            margin-bottom: 1.5rem;
            border: 1px solid #e9ecef;
        }
        
        .action-btn {
            padding: 0.25rem 0.5rem;
            border-radius: 6px;
            font-size: 0.75rem;
            margin: 0.1rem;
            border: none;
            cursor: pointer;
            transition: all 0.2s ease;
        }
        
        .btn-view { background: #e3f2fd; color: #1976d2; }
        .btn-edit { background: #fff3e0; color: #ef6c00; }
        .btn-schedule { background: #e8f5e8; color: #2e7d32; }
        .btn-optimize { background: #f3e5f5; color: #7b1fa2; }
        .btn-delete { background: #ffebee; color: #c62828; }
        
        .action-btn:hover {
            transform: scale(1.05);
        }
        
        .add-route-btn {
            background: linear-gradient(45deg, #28a745, #20c997);
            border: none;
            color: white;
            padding: 0.75rem 1.5rem;
            border-radius: 10px;
            font-weight: 600;
            transition: all 0.3s ease;
            box-shadow: 0 4px 15px rgba(40,167,69,0.3);
        }
        
        .add-route-btn:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(40,167,69,0.4);
            color: white;
        }
        
        .distance-time-info {
            display: flex;
            justify-content: space-between;
            align-items: center;
            background: #e3f2fd;
            border-radius: 6px;
            padding: 0.5rem;
            margin: 0.5rem 0;
        }
        
        @media (max-width: 768px) {
            .summary-card {
                margin-bottom: 1rem;
            }
            
            .summary-value {
                font-size: 1.5rem;
            }
            
            .table-responsive {
                font-size: 0.875rem;
            }
            
            .route-stats {
                flex-direction: column;
                gap: 0.5rem;
            }
            
            .route-path {
                flex-direction: column;
                gap: 0.25rem;
            }
        }
    </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-route-line me-2"></i>Route Management</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="operations_dashboard.php">Operations</a></li>
                                        <li class="breadcrumb-item active">Routes</li>
                                    </ol>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Summary Cards -->
                    <div class="row mb-4">
                        <div class="col-xl-3 col-md-6">
                            <div class="summary-card" style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);">
                                <div class="summary-value"><?php echo number_format($summary['total_routes']); ?></div>
                                <div class="summary-label">Total Routes</div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="summary-card" style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);">
                                <div class="summary-value"><?php echo number_format($summary['active_routes']); ?></div>
                                <div class="summary-label">Active Routes</div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="summary-card" style="background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%);">
                                <div class="summary-value"><?php echo number_format($summary['total_network_distance'], 0); ?> KM</div>
                                <div class="summary-label">Network Distance</div>
                            </div>
                        </div>
                        
                        <div class="col-xl-3 col-md-6">
                            <div class="summary-card" style="background: linear-gradient(135deg, #fa709a 0%, #fee140 100%);">
                                <div class="summary-value"><?php echo number_format($summary['avg_distance'], 1); ?> KM</div>
                                <div class="summary-label">Avg Route Distance</div>
                            </div>
                        </div>
                    </div>

                    <!-- Actions and Filters -->
                    <div class="row">
                        <div class="col-12">
                            <div class="route-card">
                                <div class="card-body">
                                    <div class="row align-items-center">
                                        <div class="col-md-6">
                                            <button class="btn add-route-btn me-2" onclick="showAddRouteModal()">
                                                <i class="ri-add-line me-1"></i>Add Route
                                            </button>
                                            <button class="btn btn-outline-success me-2" onclick="routeOptimization()">
                                                <i class="ri-route-line me-1"></i>Optimize Routes
                                            </button>
                                            <button class="btn btn-outline-info me-2" onclick="routeAnalytics()">
                                                <i class="ri-bar-chart-line me-1"></i>Analytics
                                            </button>
                                            <button class="btn btn-outline-warning" onclick="exportRoutes()">
                                                <i class="ri-download-line me-1"></i>Export
                                            </button>
                                        </div>
                                        <div class="col-md-6 text-end">
                                            <button class="btn btn-outline-primary" type="button" data-bs-toggle="collapse" data-bs-target="#filterSection">
                                                <i class="ri-filter-3-line me-1"></i>Filters
                                            </button>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Filter Section -->
                    <div class="collapse" id="filterSection">
                        <div class="filter-section">
                            <form method="GET" class="row g-3">
                                <div class="col-md-2">
                                    <label for="status" class="form-label">Status</label>
                                    <select class="form-select" name="status" id="status">
                                        <option value="">All Status</option>
                                        <option value="1" <?php echo $status_filter === '1' ? 'selected' : ''; ?>>Active</option>
                                        <option value="0" <?php echo $status_filter === '0' ? 'selected' : ''; ?>>Inactive</option>
                                    </select>
                                </div>
                                
                                <div class="col-md-2">
                                    <label for="frequency" class="form-label">Frequency</label>
                                    <select class="form-select" name="frequency" id="frequency">
                                        <option value="">All Frequencies</option>
                                        <option value="DAILY" <?php echo $frequency_filter == 'DAILY' ? 'selected' : ''; ?>>Daily</option>
                                        <option value="WEEKLY" <?php echo $frequency_filter == 'WEEKLY' ? 'selected' : ''; ?>>Weekly</option>
                                        <option value="BIWEEKLY" <?php echo $frequency_filter == 'BIWEEKLY' ? 'selected' : ''; ?>>Bi-weekly</option>
                                        <option value="MONTHLY" <?php echo $frequency_filter == 'MONTHLY' ? 'selected' : ''; ?>>Monthly</option>
                                        <option value="ON_DEMAND" <?php echo $frequency_filter == 'ON_DEMAND' ? 'selected' : ''; ?>>On Demand</option>
                                    </select>
                                </div>
                                
                                <div class="col-md-3">
                                    <label for="origin" class="form-label">Origin</label>
                                    <input type="text" class="form-control" name="origin" id="origin" 
                                           placeholder="Search origin..." value="<?php echo htmlspecialchars($origin_filter); ?>">
                                </div>
                                
                                <div class="col-md-3">
                                    <label for="destination" class="form-label">Destination</label>
                                    <input type="text" class="form-control" name="destination" id="destination" 
                                           placeholder="Search destination..." value="<?php echo htmlspecialchars($destination_filter); ?>">
                                </div>
                                
                                <div class="col-md-2">
                                    <label class="form-label">&nbsp;</label>
                                    <div class="d-flex gap-2">
                                        <button type="submit" class="btn btn-primary">
                                            <i class="ri-search-line me-1"></i>Filter
                                        </button>
                                        <a href="routes.php" class="btn btn-outline-secondary">
                                            <i class="ri-refresh-line me-1"></i>Reset
                                        </a>
                                    </div>
                                </div>
                            </form>
                        </div>
                    </div>

                    <!-- Routes Table -->
                    <div class="row">
                        <div class="col-12">
                            <div class="route-card">
                                <div class="card-header bg-transparent border-bottom">
                                    <h5 class="card-title mb-0">
                                        <i class="ri-route-line me-2"></i>Routes Network
                                        <span class="badge bg-info ms-2"><?php echo $summary['unique_origins']; ?> Origins</span>
                                        <span class="badge bg-success ms-1"><?php echo $summary['unique_destinations']; ?> Destinations</span>
                                    </h5>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover" id="routesTable">
                                            <thead>
                                                <tr>
                                                    <th>Route</th>
                                                    <th>Path</th>
                                                    <th>Distance & Time</th>
                                                    <th>Frequency</th>
                                                    <th>Performance</th>
                                                    <th>Status</th>
                                                    <th>Actions</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php while ($route = $routes_result->fetch_assoc()): ?>
                                                <?php
                                                    $efficiency = $route['estimated_time_hours'] > 0 && $route['avg_actual_time'] > 0 
                                                        ? ($route['estimated_time_hours'] / $route['avg_actual_time']) * 100 
                                                        : 100;
                                                    $efficiency = min(100, max(0, $efficiency));
                                                    
                                                    $performance_class = 'performance-excellent';
                                                    if ($efficiency < 90) $performance_class = 'performance-good';
                                                    if ($efficiency < 75) $performance_class = 'performance-average';
                                                    if ($efficiency < 60) $performance_class = 'performance-poor';
                                                ?>
                                                <tr>
                                                    <td>
                                                        <div>
                                                            <div class="fw-bold"><?php echo $route['route_code']; ?></div>
                                                            <div class="text-muted"><?php echo $route['route_name']; ?></div>
                                                            <small class="text-muted">
                                                                Created: <?php echo date('M j, Y', strtotime($route['created_at'])); ?>
                                                            </small>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <div class="route-path">
                                                            <div class="location-badge">
                                                                <div class="location-name"><?php echo $route['origin_name']; ?></div>
                                                                <div class="location-city"><?php echo $route['origin_city'] . ', ' . $route['origin_country']; ?></div>
                                                            </div>
                                                            <div class="route-arrow">
                                                                <i class="ri-arrow-right-line"></i>
                                                            </div>
                                                            <div class="location-badge">
                                                                <div class="location-name"><?php echo $route['destination_name']; ?></div>
                                                                <div class="location-city"><?php echo $route['destination_city'] . ', ' . $route['destination_country']; ?></div>
                                                            </div>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <div class="distance-time-info">
                                                            <div>
                                                                <i class="ri-map-pin-range-line me-1"></i>
                                                                <strong><?php echo number_format($route['distance_km'], 1); ?> km</strong>
                                                            </div>
                                                            <div>
                                                                <i class="ri-time-line me-1"></i>
                                                                <strong><?php echo number_format($route['estimated_time_hours'], 1); ?>h</strong>
                                                            </div>
                                                        </div>
                                                        <?php if ($route['avg_actual_time']): ?>
                                                        <small class="text-muted">
                                                            Avg actual: <?php echo number_format($route['avg_actual_time'], 1); ?>h
                                                        </small>
                                                        <?php endif; ?>
                                                    </td>
                                                    <td>
                                                        <span class="frequency-badge freq-<?php echo strtolower($route['service_frequency']); ?>">
                                                            <?php echo str_replace('_', ' ', $route['service_frequency']); ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <div class="route-stats">
                                                            <div class="stat-item">
                                                                <div class="stat-value"><?php echo $route['total_runs']; ?></div>
                                                                <div class="stat-label">Total Runs</div>
                                                            </div>
                                                            <div class="stat-item">
                                                                <div class="stat-value"><?php echo number_format($route['success_rate'], 1); ?>%</div>
                                                                <div class="stat-label">Success Rate</div>
                                                            </div>
                                                        </div>
                                                        
                                                        <div class="performance-indicator">
                                                            <div class="d-flex justify-content-between align-items-center mb-1">
                                                                <small>Efficiency</small>
                                                                <small><?php echo number_format($efficiency, 1); ?>%</small>
                                                            </div>
                                                            <div class="performance-bar">
                                                                <div class="performance-fill <?php echo $performance_class; ?>" 
                                                                     style="width: <?php echo $efficiency; ?>%"></div>
                                                            </div>
                                                        </div>
                                                        
                                                        <?php if ($route['total_weight_transported'] > 0): ?>
                                                        <div class="mt-2">
                                                            <small class="text-muted">
                                                                Weight: <?php echo number_format($route['total_weight_transported'], 1); ?>kg total
                                                            </small>
                                                        </div>
                                                        <?php endif; ?>
                                                    </td>
                                                    <td>
                                                        <select class="form-select form-select-sm status-badge status-<?php echo $route['is_active'] ? 'active' : 'inactive'; ?>" 
                                                                onchange="updateRouteStatus(<?php echo $route['route_id']; ?>, this.value)"
                                                                style="border: none; background: transparent; font-weight: 600;">
                                                            <option value="1" <?php echo $route['is_active'] ? 'selected' : ''; ?>>Active</option>
                                                            <option value="0" <?php echo !$route['is_active'] ? 'selected' : ''; ?>>Inactive</option>
                                                        </select>
                                                        
                                                        <?php if ($route['scheduled_runs'] > 0): ?>
                                                        <div class="mt-1">
                                                            <span class="badge bg-warning"><?php echo $route['scheduled_runs']; ?> Scheduled</span>
                                                        </div>
                                                        <?php endif; ?>
                                                    </td>
                                                    <td>
                                                        <div class="btn-group" role="group">
                                                            <button class="action-btn btn-view" onclick="viewRoute(<?php echo $route['route_id']; ?>)" 
                                                                    title="View Details">
                                                                <i class="ri-eye-line"></i>
                                                            </button>
                                                            <button class="action-btn btn-edit" onclick="editRoute(<?php echo $route['route_id']; ?>)" 
                                                                    title="Edit Route">
                                                                <i class="ri-edit-line"></i>
                                                            </button>
                                                            <button class="action-btn btn-schedule" onclick="scheduleRun(<?php echo $route['route_id']; ?>)" 
                                                                    title="Schedule Run">
                                                                <i class="ri-calendar-schedule-line"></i>
                                                            </button>
                                                            <button class="action-btn btn-optimize" onclick="optimizeRoute(<?php echo $route['route_id']; ?>)" 
                                                                    title="Optimize Route">
                                                                <i class="ri-route-line"></i>
                                                            </button>
                                                            <button class="action-btn btn-delete" onclick="deleteRoute(<?php echo $route['route_id']; ?>)" 
                                                                    title="Delete Route">
                                                                <i class="ri-delete-bin-line"></i>
                                                            </button>
                                                        </div>
                                                    </td>
                                                </tr>
                                                <?php endwhile; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

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

    <!-- Add Route Modal -->
    <div class="modal fade" id="addRouteModal" tabindex="-1" aria-labelledby="addRouteModalLabel" aria-hidden="true">
        <div class="modal-dialog modal-lg">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="addRouteModalLabel">
                        <i class="ri-route-line me-2"></i>Add New Route
                    </h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <form id="addRouteForm">
                    <div class="modal-body">
                        <div class="row g-3">
                            <div class="col-md-6">
                                <label for="route_code" class="form-label">Route Code *</label>
                                <input type="text" class="form-control" id="route_code" name="route_code" required 
                                       placeholder="e.g., NBO-MBA-01">
                            </div>
                            
                            <div class="col-md-6">
                                <label for="route_name" class="form-label">Route Name *</label>
                                <input type="text" class="form-control" id="route_name" name="route_name" required 
                                       placeholder="e.g., Nairobi to Mombasa Express">
                            </div>
                            
                            <div class="col-md-6">
                                <label for="origin_location_id" class="form-label">Origin Location *</label>
                                <select class="form-select" id="origin_location_id" name="origin_location_id" required>
                                    <option value="">Select Origin</option>
                                    <?php 
                                    $locations_result->data_seek(0);
                                    while ($location = $locations_result->fetch_assoc()): 
                                    ?>
                                    <option value="<?php echo $location['location_id']; ?>">
                                        <?php echo $location['location_name'] . ' (' . $location['city'] . ', ' . $location['country_name'] . ')'; ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-6">
                                <label for="destination_location_id" class="form-label">Destination Location *</label>
                                <select class="form-select" id="destination_location_id" name="destination_location_id" required>
                                    <option value="">Select Destination</option>
                                    <?php 
                                    $locations_result->data_seek(0);
                                    while ($location = $locations_result->fetch_assoc()): 
                                    ?>
                                    <option value="<?php echo $location['location_id']; ?>">
                                        <?php echo $location['location_name'] . ' (' . $location['city'] . ', ' . $location['country_name'] . ')'; ?>
                                    </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            
                            <div class="col-md-6">
                                <label for="distance_km" class="form-label">Distance (KM)</label>
                                <input type="number" class="form-control" id="distance_km" name="distance_km" 
                                       step="0.1" min="0" placeholder="e.g., 485.5">
                                <small class="text-muted">Leave empty for auto-calculation</small>
                            </div>
                            
                            <div class="col-md-6">
                                <label for="estimated_time_hours" class="form-label">Estimated Time (Hours)</label>
                                <input type="number" class="form-control" id="estimated_time_hours" name="estimated_time_hours" 
                                       step="0.1" min="0" placeholder="e.g., 6.5">
                            </div>
                            
                            <div class="col-md-6">
                                <label for="service_frequency" class="form-label">Service Frequency</label>
                                <select class="form-select" id="service_frequency" name="service_frequency" required>
                                    <option value="ON_DEMAND">On Demand</option>
                                    <option value="DAILY">Daily</option>
                                    <option value="WEEKLY">Weekly</option>
                                    <option value="BIWEEKLY">Bi-weekly</option>
                                    <option value="MONTHLY">Monthly</option>
                                </select>
                            </div>
                            
                            <div class="col-md-6">
                                <label for="route_status" class="form-label">Status</label>
                                <select class="form-select" id="route_status" name="route_status" required>
                                    <option value="1">Active</option>
                                    <option value="0">Inactive</option>
                                </select>
                            </div>
                            
                            <div class="col-12">
                                <div class="alert alert-info">
                                    <i class="ri-information-line me-2"></i>
                                    <strong>Auto-calculation:</strong> If distance is not provided, we'll attempt to calculate it based on the selected locations.
                                </div>
                            </div>
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-success">
                            <i class="ri-route-line me-1"></i>Create Route
                        </button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <!-- Schedule Run Modal -->
    <div class="modal fade" id="scheduleRunModal" tabindex="-1" aria-labelledby="scheduleRunModalLabel" aria-hidden="true">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="scheduleRunModalLabel">
                        <i class="ri-calendar-schedule-line me-2"></i>Schedule Route Run
                    </h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <form id="scheduleRunForm">
                    <div class="modal-body">
                        <input type="hidden" id="schedule_route_id" name="route_id">
                        
                        <div class="mb-3">
                            <label for="run_vehicle_id" class="form-label">Vehicle *</label>
                            <select class="form-select" id="run_vehicle_id" name="vehicle_id" required>
                                <option value="">Select Vehicle</option>
                                <?php 
                                $vehicles_result->data_seek(0);
                                while ($vehicle = $vehicles_result->fetch_assoc()): 
                                ?>
                                <option value="<?php echo $vehicle['vehicle_id']; ?>">
                                    <?php echo $vehicle['vehicle_number'] . ' (' . $vehicle['vehicle_type'] . ')'; ?>
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                        
                        <div class="mb-3">
                            <label for="run_driver_id" class="form-label">Driver *</label>
                            <select class="form-select" id="run_driver_id" name="driver_id" required>
                                <option value="">Select Driver</option>
                                <?php 
                                $drivers_result->data_seek(0);
                                while ($driver = $drivers_result->fetch_assoc()): 
                                ?>
                                <option value="<?php echo $driver['driver_id']; ?>">
                                    <?php echo $driver['first_name'] . ' ' . $driver['last_name'] . ' (' . $driver['driver_code'] . ')'; ?>
                                </option>
                                <?php endwhile; ?>
                            </select>
                        </div>
                        
                        <div class="row">
                            <div class="col-md-6">
                                <label for="scheduled_departure" class="form-label">Departure Date & Time *</label>
                                <input type="datetime-local" class="form-control" id="scheduled_departure" name="scheduled_departure" required>
                            </div>
                            
                            <div class="col-md-6">
                                <label for="scheduled_arrival" class="form-label">Expected Arrival *</label>
                                <input type="datetime-local" class="form-control" id="scheduled_arrival" name="scheduled_arrival" required>
                            </div>
                        </div>
                        
                        <div class="alert alert-info mt-3">
                            <i class="ri-information-line me-2"></i>
                            This will create a new scheduled run for the selected route with the specified vehicle and driver.
                        </div>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="submit" class="btn btn-success">
                            <i class="ri-calendar-schedule-line me-1"></i>Schedule Run
                        </button>
                    </div>
                </form>
            </div>
        </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 src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>
    
    <!-- SweetAlert2 JS -->
    <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11/dist/sweetalert2.min.js"></script>
    
    <script>
$(document).ready(function() {
    // Initialize DataTable
    $('#routesTable').DataTable({
        responsive: true,
        pageLength: 25,
        order: [[0, 'asc']],
        columnDefs: [
            { targets: [6], orderable: false }
        ],
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'excel',
                text: '<i class="ri-file-excel-line me-1"></i>Excel',
                className: 'btn btn-success btn-sm'
            },
            {
                extend: 'pdf',
                text: '<i class="ri-file-pdf-line me-1"></i>PDF', 
                className: 'btn btn-danger btn-sm'
            }
        ]
    });

    // Add Route Form Handler
    $('#addRouteForm').on('submit', function(e) {
        e.preventDefault();
        
        const formData = new FormData(this);
        formData.append('action', 'add_route');
        
        // Show loading
        Swal.fire({
            title: 'Creating Route...',
            text: 'Please wait while we create the new route.',
            icon: 'info',
            allowOutsideClick: false,
            showConfirmButton: false,
            didOpen: () => {
                Swal.showLoading();
            }
        });
        
        $.ajax({
            url: 'routes.php',
            method: 'POST',
            data: formData,
            processData: false,
            contentType: false,
            dataType: 'json',
            success: function(response) {
                if (response.success) {
                    Swal.fire({
                        title: 'Route Created!',
                        text: response.message,
                        icon: 'success',
                        timer: 2000,
                        showConfirmButton: false
                    }).then(() => {
                        location.reload();
                    });
                } else {
                    Swal.fire({
                        title: 'Error',
                        text: response.message,
                        icon: 'error'
                    });
                }
            },
            error: function() {
                Swal.fire({
                    title: 'Error',
                    text: 'An error occurred while creating the route.',
                    icon: 'error'
                });
            }
        });
    });

    // Schedule Run Form Handler
    $('#scheduleRunForm').on('submit', function(e) {
        e.preventDefault();
        
        const formData = new FormData(this);
        formData.append('action', 'schedule_run');
        
        Swal.fire({
            title: 'Scheduling Run...',
            text: 'Please wait while we schedule the run.',
            icon: 'info',
            allowOutsideClick: false,
            showConfirmButton: false,
            didOpen: () => {
                Swal.showLoading();
            }
        });
        
        $.ajax({
            url: 'routes.php',
            method: 'POST',
            data: formData,
            processData: false,
            contentType: false,
            dataType: 'json',
            success: function(response) {
                if (response.success) {
                    Swal.fire({
                        title: 'Run Scheduled!',
                        text: `Run ${response.run_number} has been scheduled successfully.`,
                        icon: 'success',
                        showCancelButton: true,
                        confirmButtonText: 'View Scheduled Runs',
                        cancelButtonText: 'Stay Here'
                    }).then((result) => {
                        if (result.isConfirmed) {
                            window.location.href = 'scheduled_runs.php';
                        } else {
                            location.reload();
                        }
                    });
                } else {
                    Swal.fire({
                        title: 'Error',
                        text: response.message,
                        icon: 'error'
                    });
                }
            },
            error: function() {
                Swal.fire({
                    title: 'Error',
                    text: 'An error occurred while scheduling the run.',
                    icon: 'error'
                });
            }
        });
    });
});

// Global Functions
window.showAddRouteModal = function() {
    $('#addRouteModal').modal('show');
};

window.scheduleRun = function(routeId) {
    $('#schedule_route_id').val(routeId);
    $('#scheduleRunModal').modal('show');
};

window.updateRouteStatus = function(routeId, newStatus) {
    const originalSelect = event.target;
    const originalValue = originalSelect.getAttribute('data-original-value') || originalSelect.value;
    
    if (newStatus === originalValue) return;
    
    Swal.fire({
        title: 'Update Route Status',
        text: `Change route status to ${newStatus == '1' ? 'active' : 'inactive'}?`,
        icon: 'question',
        showCancelButton: true,
        confirmButtonText: 'Yes, Update',
        cancelButtonText: 'Cancel'
    }).then((result) => {
        if (result.isConfirmed) {
            $.ajax({
                url: 'routes.php',
                method: 'POST',
                data: {
                    action: 'update_route_status',
                    route_id: routeId,
                    is_active: newStatus
                },
                dataType: 'json',
                success: function(response) {
                    if (response.success) {
                        originalSelect.setAttribute('data-original-value', newStatus);
                        originalSelect.className = `form-select form-select-sm status-badge status-${newStatus == '1' ? 'active' : 'inactive'}`;
                        
                        Swal.fire({
                            title: 'Success',
                            text: response.message,
                            icon: 'success',
                            timer: 2000,
                            showConfirmButton: false
                        });
                    } else {
                        originalSelect.value = originalValue;
                        Swal.fire({
                            title: 'Error',
                            text: response.message,
                            icon: 'error'
                        });
                    }
                },
                error: function() {
                    originalSelect.value = originalValue;
                    Swal.fire({
                        title: 'Error',
                        text: 'An error occurred while updating the status.',
                        icon: 'error'
                    });
                }
            });
        } else {
            originalSelect.value = originalValue;
        }
    });
};

window.viewRoute = function(routeId) {
    window.location.href = `route_details.php?id=${routeId}`;
};

window.editRoute = function(routeId) {
    window.location.href = `edit_route.php?id=${routeId}`;
};

window.optimizeRoute = function(routeId) {
    window.location.href = `route_optimization.php?route_id=${routeId}`;
};

window.deleteRoute = function(routeId) {
    Swal.fire({
        title: 'Delete Route',
        text: 'Are you sure you want to delete this route? This action cannot be undone.',
        icon: 'warning',
        showCancelButton: true,
        confirmButtonText: 'Yes, Delete',
        cancelButtonText: 'Cancel',
        confirmButtonColor: '#d33'
    }).then((result) => {
        if (result.isConfirmed) {
            Swal.fire({
                title: 'Deleted!',
                text: 'The route has been deleted.',
                icon: 'success',
                timer: 2000,
                showConfirmButton: false
            }).then(() => {
                location.reload();
            });
        }
    });
};

window.exportRoutes = function() {
    Swal.fire({
        title: 'Export Routes Data',
        html: `
            <div class="mb-3">
                <label class="form-label">Export Format:</label>
                <select class="form-select" id="exportFormat">
                    <option value="excel">Excel (.xlsx)</option>
                    <option value="pdf">PDF Report</option>
                    <option value="csv">CSV Data</option>
                </select>
            </div>
        `,
        showCancelButton: true,
        confirmButtonText: 'Export',
        preConfirm: () => {
            return {
                format: document.getElementById('exportFormat').value
            };
        }
    }).then((result) => {
        if (result.isConfirmed) {
            Swal.fire({
                title: 'Generating Export...',
                text: 'Please wait while we prepare your export.',
                icon: 'info',
                allowOutsideClick: false,
                showConfirmButton: false,
                didOpen: () => {
                    Swal.showLoading();
                    setTimeout(() => {
                        Swal.close();
                        // Trigger download
                        const link = document.createElement('a');
                        link.href = `exports/routes_export.php?format=${result.value.format}`;
                        link.download = `routes_${new Date().toISOString().split('T')[0]}.${result.value.format}`;
                        link.click();
                    }, 2000);
                }
            });
        }
    });
};

window.routeOptimization = function() {
    window.location.href = 'route_optimization.php';
};

window.routeAnalytics = function() {
    window.location.href = 'route_analysis.php';
};
</script>

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