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

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

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

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

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

// Get total active customers
$sql_customers = "SELECT COUNT(*) as total_customers FROM customers WHERE status = 'ACTIVE'";
$result_customers = $conn->query($sql_customers);
$total_customers = $result_customers->fetch_assoc()['total_customers'];

// Get total shipments
$sql_shipments = "SELECT COUNT(*) as total_shipments FROM shipments";
$result_shipments = $conn->query($sql_shipments);
$total_shipments = $result_shipments->fetch_assoc()['total_shipments'];

// Get total revenue from completed shipments
$sql_total_revenue = "SELECT COALESCE(SUM(total_charges), 0) as total_revenue 
                      FROM shipments WHERE payment_status = 'PAID'";
$result_total_revenue = $conn->query($sql_total_revenue);
$total_revenue = $result_total_revenue->fetch_assoc()['total_revenue'];

// Get today's shipments
$sql_today_shipments = "SELECT COUNT(*) as today_shipments FROM shipments 
                        WHERE DATE(booking_date) = CURDATE()";
$result_today_shipments = $conn->query($sql_today_shipments);
$today_shipments = $result_today_shipments->fetch_assoc()['today_shipments'];

// Get today's revenue
$sql_today_revenue = "SELECT COALESCE(SUM(total_charges), 0) as today_revenue 
                      FROM shipments 
                      WHERE DATE(booking_date) = CURDATE() AND payment_status = 'PAID'";
$result_today_revenue = $conn->query($sql_today_revenue);
$today_revenue = $result_today_revenue->fetch_assoc()['today_revenue'];

// Get pending shipments
$sql_pending_shipments = "SELECT COUNT(*) as pending_shipments 
                          FROM shipments WHERE status IN ('DRAFT', 'BOOKED')";
$result_pending_shipments = $conn->query($sql_pending_shipments);
$pending_shipments = $result_pending_shipments->fetch_assoc()['pending_shipments'];

// Get in-transit shipments
$sql_in_transit = "SELECT COUNT(*) as in_transit 
                   FROM shipments WHERE status = 'IN_TRANSIT'";
$result_in_transit = $conn->query($sql_in_transit);
$in_transit = $result_in_transit->fetch_assoc()['in_transit'];

// Get delivered shipments
$sql_delivered = "SELECT COUNT(*) as delivered 
                  FROM shipments WHERE status = 'DELIVERED'";
$result_delivered = $conn->query($sql_delivered);
$delivered = $result_delivered->fetch_assoc()['delivered'];

// Get daily shipment trends for the last 7 days
$sql_daily_shipments = "SELECT 
    DATE(booking_date) as shipment_date,
    COUNT(*) as daily_shipments,
    SUM(total_charges) as daily_revenue,
    SUM(chargeable_weight) as daily_weight
FROM shipments 
WHERE booking_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY DATE(booking_date)
ORDER BY shipment_date";
$result_daily_shipments = $conn->query($sql_daily_shipments);
$daily_shipment_data = [];
while ($row = $result_daily_shipments->fetch_assoc()) {
    $daily_shipment_data[] = $row;
}

// Get service type distribution
$sql_service_types = "SELECT 
    st.service_name,
    COUNT(s.shipment_id) as shipment_count,
    COALESCE(SUM(s.total_charges), 0) as total_revenue
FROM service_types st
LEFT JOIN shipments s ON st.service_id = s.service_id
WHERE st.is_active = 1
GROUP BY st.service_id, st.service_name
ORDER BY shipment_count DESC";
$result_service_types = $conn->query($sql_service_types);
$service_type_data = [];
while ($row = $result_service_types->fetch_assoc()) {
    $service_type_data[] = $row;
}

// Get recent shipments
$sql_recent_shipments = "SELECT 
    s.tracking_number,
    s.sender_name,
    s.receiver_name,
    s.receiver_city,
    s.receiver_country,
    s.status,
    s.total_charges,
    s.booking_date,
    st.service_name,
    ol.location_name as origin_location,
    dl.location_name as destination_location,
    u.full_name as created_by_name
FROM shipments s
LEFT JOIN service_types st ON s.service_id = st.service_id
LEFT JOIN locations ol ON s.origin_location_id = ol.location_id
LEFT JOIN locations dl ON s.destination_location_id = dl.location_id
LEFT JOIN users u ON s.created_by = u.user_id
ORDER BY s.created_at DESC
LIMIT 8";
$result_recent_shipments = $conn->query($sql_recent_shipments);

// Get top customers by shipment volume
$sql_top_customers = "SELECT 
    c.customer_id,
    c.customer_code,
    c.company_name,
    c.first_name,
    c.last_name,
    c.customer_type,
    COUNT(s.shipment_id) as total_shipments,
    COALESCE(SUM(s.total_charges), 0) as total_revenue,
    COUNT(CASE WHEN DATE(s.booking_date) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1 END) as recent_shipments
FROM customers c
LEFT JOIN shipments s ON c.customer_id = s.sender_customer_id
WHERE c.status = 'ACTIVE'
GROUP BY c.customer_id, c.customer_code, c.company_name, c.first_name, c.last_name, c.customer_type
HAVING total_shipments > 0
ORDER BY total_revenue DESC
LIMIT 6";
$result_top_customers = $conn->query($sql_top_customers);

// Get location performance
$sql_location_performance = "SELECT 
    l.location_name,
    l.city,
    c.country_name,
    COUNT(CASE WHEN s.origin_location_id = l.location_id THEN 1 END) as origin_shipments,
    COUNT(CASE WHEN s.destination_location_id = l.location_id THEN 1 END) as destination_shipments,
    COUNT(s.shipment_id) as total_shipments
FROM locations l
LEFT JOIN countries c ON l.country_id = c.country_id
LEFT JOIN shipments s ON l.location_id IN (s.origin_location_id, s.destination_location_id)
WHERE l.is_active = 1
GROUP BY l.location_id, l.location_name, l.city, c.country_name
HAVING total_shipments > 0
ORDER BY total_shipments DESC
LIMIT 6";
$result_location_performance = $conn->query($sql_location_performance);

// Get monthly revenue and volume
$sql_monthly_stats = "SELECT 
    COALESCE(SUM(total_charges), 0) as monthly_revenue,
    COUNT(*) as monthly_shipments,
    SUM(chargeable_weight) as monthly_weight
FROM shipments 
WHERE MONTH(booking_date) = MONTH(CURRENT_DATE()) 
AND YEAR(booking_date) = YEAR(CURRENT_DATE())";
$result_monthly_stats = $conn->query($sql_monthly_stats);
$monthly_stats = $result_monthly_stats->fetch_assoc();

// Get status distribution for pie chart
$sql_status_distribution = "SELECT 
    status,
    COUNT(*) as status_count
FROM shipments 
GROUP BY status
ORDER BY status_count DESC";
$result_status_distribution = $conn->query($sql_status_distribution);
$status_data = [];
while ($row = $result_status_distribution->fetch_assoc()) {
    $status_data[] = $row;
}

$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Dashboard - Freight Management System')); ?>
    <!-- apexcharts css -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.css">
    <?php include 'layouts/head-css.php'; ?>
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    
    <style>
        /* Professional Dashboard Styling - Freight Management Theme */
        body {
            background-color: #f8fafc;
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
        }
        
        .page-content {
            padding: 1.5rem;
        }
        
        /* Welcome Header - Freight Management Theme */
        .welcome-header {
            background: linear-gradient(135deg, #1e40af 0%, #3b82f6 100%);
            color: white;
            border-radius: 12px;
            padding: 2rem;
            margin-bottom: 2rem;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
        }
        
        .welcome-content {
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 1rem;
        }
        
        .welcome-text h2 {
            font-size: 1.75rem;
            font-weight: 600;
            margin-bottom: 0.5rem;
        }
        
        .welcome-text p {
            opacity: 0.9;
            margin: 0;
        }
        
        .datetime-display {
            text-align: right;
            background: rgba(255, 255, 255, 0.1);
            backdrop-filter: blur(10px);
            border-radius: 12px;
            padding: 1rem;
            min-width: 200px;
        }
        
        .current-time {
            font-size: 1.5rem;
            font-weight: 700;
            font-family: 'Courier New', monospace;
        }
        
        .current-date {
            font-size: 0.9rem;
            opacity: 0.8;
        }
        
        /* Metrics Cards */
        .metrics-row {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 1.5rem;
            margin-bottom: 2rem;
        }
        
        .metric-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            transition: all 0.2s ease;
        }
        
        .metric-card:hover {
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
            transform: translateY(-1px);
        }
        
        .metric-title {
            font-size: 0.875rem;
            font-weight: 500;
            color: #64748b;
            text-transform: uppercase;
            letter-spacing: 0.05em;
            margin-bottom: 0.5rem;
        }
        
        .metric-value {
            font-size: 2rem;
            font-weight: 700;
            color: #1e293b;
            margin-bottom: 0.5rem;
        }
        
        .metric-change {
            font-size: 0.875rem;
            font-weight: 500;
        }
        
        .metric-change.positive { color: #059669; }
        .metric-change.negative { color: #dc2626; }
        .metric-change.neutral { color: #64748b; }
        
        .metric-link {
            display: inline-block;
            margin-top: 0.5rem;
            color: #1e40af;
            text-decoration: none;
            font-size: 0.875rem;
            font-weight: 500;
        }
        
        .metric-link:hover {
            color: #1d4ed8;
            text-decoration: underline;
        }
        
        /* Chart Cards */
        .chart-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            margin-bottom: 2rem;
        }
        
        .chart-header {
            padding: 1.5rem 1.5rem 0;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .chart-title {
            font-size: 1.125rem;
            font-weight: 600;
            color: #1e293b;
            margin: 0 0 1rem 0;
        }
        
        .chart-body {
            padding: 1rem 1.5rem 1.5rem;
        }
        
        /* Tables */
        .table-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            overflow: hidden;
            margin-bottom: 2rem;
        }
        
        .table-header {
            padding: 1.5rem;
            border-bottom: 1px solid #e2e8f0;
            background: #f8fafc;
        }
        
        .table-title {
            font-size: 1.125rem;
            font-weight: 600;
            color: #1e293b;
            margin: 0;
        }
        
        .data-table {
            width: 100%;
            border-collapse: collapse;
            margin: 0;
        }
        
        .data-table th {
            background: #f8fafc;
            padding: 1rem;
            text-align: left;
            font-weight: 600;
            font-size: 0.875rem;
            color: #64748b;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .data-table td {
            padding: 1rem;
            border-bottom: 1px solid #f1f5f9;
            font-size: 0.875rem;
        }
        
        .data-table tbody tr:hover {
            background: #f8fafc;
        }
        
        /* Status Badges */
        .status-badge {
            padding: 0.25rem 0.75rem;
            border-radius: 9999px;
            font-size: 0.75rem;
            font-weight: 600;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
        
        .status-draft { background: #f3f4f6; color: #374151; }
        .status-booked { background: #dbeafe; color: #1d4ed8; }
        .status-collected { background: #d1fae5; color: #059669; }
        .status-in_transit { background: #fef3c7; color: #d97706; }
        .status-out_for_delivery { background: #e0e7ff; color: #5b21b6; }
        .status-delivered { background: #dcfce7; color: #166534; }
        .status-returned { background: #fee2e2; color: #991b1b; }
        .status-cancelled { background: #fecaca; color: #7f1d1d; }
        
        /* Quick Actions */
        .quick-actions {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
            gap: 1rem;
            margin-bottom: 2rem;
        }
        
        .quick-action {
            background: white;
            border: 1px solid #e2e8f0;
            border-radius: 12px;
            padding: 1.5rem;
            text-align: center;
            text-decoration: none;
            color: #1e293b;
            transition: all 0.2s ease;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
        }
        
        .quick-action:hover {
            color: #1e40af;
            border-color: #1e40af;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
            transform: translateY(-1px);
        }
        
        .quick-action-icon {
            font-size: 2rem;
            margin-bottom: 0.5rem;
            color: #1e40af;
        }
        
        .quick-action-title {
            font-weight: 600;
            font-size: 0.875rem;
        }
        
        /* Financial Summary */
        .financial-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 1rem;
        }
        
        .financial-item {
            background: #f8fafc;
            border: 1px solid #e2e8f0;
            border-radius: 8px;
            padding: 1.5rem;
            text-align: center;
        }
        
        .financial-label {
            font-size: 0.875rem;
            color: #64748b;
            margin-bottom: 0.5rem;
        }
        
        .financial-value {
            font-size: 1.5rem;
            font-weight: 700;
        }
        
        /* Customer Avatar */
        .customer-avatar {
            width: 40px;
            height: 40px;
            border-radius: 50%;
            background: #1e40af;
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: 600;
            font-size: 0.875rem;
        }
        
        /* Responsive Design */
        @media (max-width: 768px) {
            .page-content {
                padding: 1rem;
            }
            
            .welcome-content {
                flex-direction: column;
                text-align: center;
            }
            
            .datetime-display {
                min-width: auto;
                width: 100%;
            }
            
            .metrics-row {
                grid-template-columns: 1fr;
            }
            
            .metric-value {
                font-size: 1.5rem;
            }
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Welcome Header -->
                    <div class="welcome-header">
                        <div class="welcome-content">
                            <div class="welcome-text">
                                <h2>
                                    <?php 
                                    $hour = date('H');
                                    $greeting = $hour < 12 ? 'Good Morning' : ($hour < 17 ? 'Good Afternoon' : 'Good Evening');
                                    echo $greeting . ', ' . $full_name;
                                    ?>
                                </h2>
                                <p>Freight Management Dashboard - <?php echo $role_name; ?>
                                    <?php if($phone): ?> | <?php echo $phone; ?><?php endif; ?>
                                </p>
                            </div>
                            <div class="datetime-display d-none d-md-block">
                                <div class="current-time" id="currentTime"><?php echo date('h:i A'); ?></div>
                                <div class="current-date" id="currentDate"><?php echo date('l, M j, Y'); ?></div>
                            </div>
                        </div>
                    </div>

                    <!-- Key Metrics -->
                    <div class="metrics-row">
                        <div class="metric-card">
                            <div class="metric-title">Total Customers</div>
                            <div class="metric-value"><?php echo number_format($total_customers); ?></div>
                            <div class="metric-change neutral">Active customers</div>
                            <a href="customers.php" class="metric-link">View All →</a>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-title">Total Shipments</div>
                            <div class="metric-value"><?php echo number_format($total_shipments); ?></div>
                            <div class="metric-change neutral">All time</div>
                            <a href="shipments.php" class="metric-link">View Shipments →</a>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-title">Total Revenue</div>
                            <div class="metric-value"><?php echo $shipment['currency']; ?> <?php echo number_format($total_revenue); ?></div>
                            <div class="metric-change positive">Paid shipments</div>
                            <a href="revenue_report.php" class="metric-link">View Details →</a>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-title">Today's Shipments</div>
                            <div class="metric-value"><?php echo number_format($today_shipments); ?></div>
                            <div class="metric-change neutral">Booked today</div>
                            <a href="daily_shipments.php" class="metric-link">View Today →</a>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-title">In Transit</div>
                            <div class="metric-value"><?php echo number_format($in_transit); ?></div>
                            <div class="metric-change <?php echo $in_transit > 0 ? 'positive' : 'neutral'; ?>">Active shipments</div>
                            <a href="tracking.php" class="metric-link">Track Shipments →</a>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-title">Pending Shipments</div>
                            <div class="metric-value"><?php echo number_format($pending_shipments); ?></div>
                            <div class="metric-change <?php echo $pending_shipments > 0 ? 'negative' : 'neutral'; ?>">Awaiting processing</div>
                            <a href="pending_shipments.php" class="metric-link">Review Pending →</a>
                        </div>
                    </div>

                    <!-- Charts Section -->
                    <div class="row">
                        <div class="col-xl-8">
                            <div class="chart-card">
                                <div class="chart-header">
                                    <h3 class="chart-title">Shipment Trends (Last 7 Days)</h3>
                                </div>
                                <div class="chart-body">
                                    <div id="shipment-trends" style="height: 350px;"></div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-4">
                            <div class="chart-card">
                                <div class="chart-header">
                                    <h3 class="chart-title">Service Types</h3>
                                </div>
                                <div class="chart-body">
                                    <div id="service-types" style="height: 350px;"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Quick Actions -->
                    <div class="quick-actions">
                        <a href="create_shipment.php" class="quick-action">
                            <div class="quick-action-icon">📦</div>
                            <div class="quick-action-title">Create Shipment</div>
                        </a>
                        <a href="track_shipment.php" class="quick-action">
                            <div class="quick-action-icon">🔍</div>
                            <div class="quick-action-title">Track Shipment</div>
                        </a>
                        <a href="add_customer.php" class="quick-action">
                            <div class="quick-action-icon">👤</div>
                            <div class="quick-action-title">Add Customer</div>
                        </a>
                        <a href="create_manifest.php" class="quick-action">
                            <div class="quick-action-icon">📋</div>
                            <div class="quick-action-title">Create Manifest</div>
                        </a>
                        <a href="rate_calculator.php" class="quick-action">
                            <div class="quick-action-icon">💰</div>
                            <div class="quick-action-title">Rate Calculator</div>
                        </a>
                        <a href="reports.php" class="quick-action">
                            <div class="quick-action-icon">📊</div>
                            <div class="quick-action-title">View Reports</div>
                        </a>
                    </div>

                    <!-- Data Tables -->
                    <div class="row">
                        <div class="col-xl-8">
                            <div class="table-card">
                                <div class="table-header">
                                    <h3 class="table-title">Recent Shipments</h3>
                                </div>
                                <table class="data-table">
                                    <thead>
                                        <tr>
                                            <th>Tracking #</th>
                                            <th>Sender/Receiver</th>
                                            <th>Route</th>
                                            <th>Service</th>
                                            <th>Status</th>
                                            <th>Amount</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while($row = $result_recent_shipments->fetch_assoc()): ?>
                                        <tr>
                                            <td>
                                                <a href="view_shipment.php?tracking=<?php echo $row['tracking_number']; ?>" style="color: #1e40af; text-decoration: none; font-weight: 600;">
                                                    <?php echo $row['tracking_number']; ?>
                                                </a>
                                            </td>
                                            <td>
                                                <div>
                                                    <div style="font-weight: 600;"><?php echo $row['sender_name']; ?></div>
                                                    <div style="color: #64748b; font-size: 0.75rem;">to <?php echo $row['receiver_name']; ?></div>
                                                </div>
                                            </td>
                                            <td>
                                                <div>
                                                    <div style="font-weight: 500;"><?php echo $row['origin_location']; ?></div>
                                                    <div style="color: #64748b; font-size: 0.75rem;">to <?php echo $row['destination_location']; ?></div>
                                                </div>
                                            </td>
                                            <td>
                                                <span style="font-weight: 500;"><?php echo $row['service_name']; ?></span>
                                            </td>
                                            <td>
                                                <?php 
                                                $status_class = 'status-' . strtolower(str_replace(' ', '_', $row['status']));
                                                ?>
                                                <span class="status-badge <?php echo $status_class; ?>">
                                                    <?php echo ucfirst(strtolower(str_replace('_', ' ', $row['status']))); ?>
                                                </span>
                                            </td>
                                            <td style="font-weight: 600;"><?php echo $shipment['currency']; ?> <?php echo number_format($row['total_charges'], 2); ?></td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>

                        <div class="col-xl-4">
                            <div class="chart-card">
                                <div class="chart-header">
                                    <h3 class="chart-title">Shipment Status</h3>
                                </div>
                                <div class="chart-body">
                                    <div id="status-distribution" style="height: 350px;"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Top Customers and Location Performance -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="table-card">
                                <div class="table-header">
                                    <h3 class="table-title">Top Customers</h3>
                                </div>
                                <table class="data-table">
                                    <thead>
                                        <tr>
                                            <th>Customer</th>
                                            <th>Type</th>
                                            <th>Shipments</th>
                                            <th>Revenue</th>
                                            <th>Recent Activity</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while($row = $result_top_customers->fetch_assoc()): ?>
                                        <tr>
                                            <td>
                                                <div class="d-flex align-items-center">
                                                    <div class="customer-avatar me-3">
                                                        <?php 
                                                        if($row['customer_type'] == 'CORPORATE') {
                                                            echo strtoupper(substr($row['company_name'], 0, 2));
                                                        } else {
                                                            echo strtoupper(substr($row['first_name'], 0, 1) . substr($row['last_name'], 0, 1));
                                                        }
                                                        ?>
                                                    </div>
                                                    <div>
                                                        <div style="font-weight: 600;">
                                                            <?php 
                                                            echo $row['customer_type'] == 'CORPORATE' 
                                                                ? $row['company_name'] 
                                                                : $row['first_name'] . ' ' . $row['last_name']; 
                                                            ?>
                                                        </div>
                                                        <div style="color: #64748b; font-size: 0.75rem;"><?php echo $row['customer_code']; ?></div>
                                                    </div>
                                                </div>
                                            </td>
                                            <td>
                                                <span class="status-badge status-booked">
                                                    <?php echo ucfirst(strtolower($row['customer_type'])); ?>
                                                </span>
                                            </td>
                                            <td style="font-weight: 600;"><?php echo number_format($row['total_shipments']); ?></td>
                                            <td style="font-weight: 600; color: #1e40af;"><?php echo $shipment['currency']; ?> <?php echo number_format($row['total_revenue']); ?></td>
                                            <td style="font-weight: 600;"><?php echo $row['recent_shipments']; ?> this month</td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>

                        <div class="col-xl-6">
                            <div class="table-card">
                                <div class="table-header">
                                    <h3 class="table-title">Location Performance</h3>
                                </div>
                                <table class="data-table">
                                    <thead>
                                        <tr>
                                            <th>Location</th>
                                            <th>Country</th>
                                            <th>Origin</th>
                                            <th>Destination</th>
                                            <th>Total</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while($row = $result_location_performance->fetch_assoc()): ?>
                                        <tr>
                                            <td>
                                                <div>
                                                    <div style="font-weight: 600;"><?php echo $row['location_name']; ?></div>
                                                    <div style="color: #64748b; font-size: 0.75rem;"><?php echo $row['city']; ?></div>
                                                </div>
                                            </td>
                                            <td style="font-weight: 500;"><?php echo $row['country_name']; ?></td>
                                            <td style="font-weight: 600; color: #059669;"><?php echo number_format($row['origin_shipments']); ?></td>
                                            <td style="font-weight: 600; color: #dc2626;"><?php echo number_format($row['destination_shipments']); ?></td>
                                            <td style="font-weight: 600;"><?php echo number_format($row['total_shipments']); ?></td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>

                    <!-- Financial Summary and Quick Stats -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="chart-card">
                                <div class="chart-header">
                                    <h3 class="chart-title">Monthly Performance</h3>
                                </div>
                                <div class="chart-body">
                                    <div class="financial-grid">
                                        <div class="financial-item">
                                            <div class="financial-label">Monthly Revenue</div>
                                            <div class="financial-value" style="color: #1e40af;"><?php echo $shipment['currency']; ?> <?php echo number_format($monthly_stats['monthly_revenue']); ?></div>
                                        </div>
                                        <div class="financial-item">
                                            <div class="financial-label">Monthly Shipments</div>
                                            <div class="financial-value" style="color: #059669;"><?php echo number_format($monthly_stats['monthly_shipments']); ?></div>
                                        </div>
                                        <div class="financial-item">
                                            <div class="financial-label">Total Weight (KG)</div>
                                            <div class="financial-value" style="color: #d97706;"><?php echo number_format($monthly_stats['monthly_weight'], 2); ?></div>
                                        </div>
                                        <div class="financial-item">
                                            <div class="financial-label">Today's Revenue</div>
                                            <div class="financial-value" style="color: #7c3aed;"><?php echo $shipment['currency']; ?> <?php echo number_format($today_revenue); ?></div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-6">
                            <div class="chart-card">
                                <div class="chart-header">
                                    <h3 class="chart-title">Quick Stats</h3>
                                </div>
                                <div class="chart-body">
                                    <div class="row text-center">
                                        <div class="col-6 mb-3">
                                            <div style="background: #eff6ff; border-radius: 8px; padding: 1rem;">
                                                <div style="font-size: 1.5rem; font-weight: 700; color: #1e40af;"><?php echo $total_customers; ?></div>
                                                <div style="font-size: 0.875rem; color: #64748b;">Active Customers</div>
                                            </div>
                                        </div>
                                        <div class="col-6 mb-3">
                                            <div style="background: #f0fdf4; border-radius: 8px; padding: 1rem;">
                                                <div style="font-size: 1.5rem; font-weight: 700; color: #059669;"><?php echo $delivered; ?></div>
                                                <div style="font-size: 0.875rem; color: #64748b;">Delivered Shipments</div>
                                            </div>
                                        </div>
                                        <div class="col-6">
                                            <div style="background: <?php echo $pending_shipments > 0 ? '#fef3c7' : '#f0fdf4'; ?>; border-radius: 8px; padding: 1rem;">
                                                <div style="font-size: 1.5rem; font-weight: 700; color: <?php echo $pending_shipments > 0 ? '#d97706' : '#059669'; ?>;"><?php echo $pending_shipments; ?></div>
                                                <div style="font-size: 0.875rem; color: #64748b;">Pending Shipments</div>
                                            </div>
                                        </div>
                                        <div class="col-6">
                                            <div style="background: #f3e8ff; border-radius: 8px; padding: 1rem;">
                                                <div style="font-size: 1.5rem; font-weight: 700; color: #7c3aed;"><?php echo $in_transit; ?></div>
                                                <div style="font-size: 0.875rem; color: #64748b;">In Transit</div>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.min.js"></script>
    
    <script>
document.addEventListener('DOMContentLoaded', function() {
    // Update time and date
    function updateDateTime() {
        const now = new Date();
        
        document.getElementById('currentTime').textContent = now.toLocaleTimeString('en-US', {
            hour: '2-digit',
            minute: '2-digit',
            hour12: true
        });
        
        document.getElementById('currentDate').textContent = now.toLocaleDateString('en-US', {
            weekday: 'long',
            month: 'short',
            day: 'numeric',
            year: 'numeric'
        });
    }
    
    // Update every minute
    setInterval(updateDateTime, 60000);
    updateDateTime();

    // Shipment Trends Chart
    var shipmentOptions = {
        series: [{
            name: 'Daily Shipments',
            type: 'column',
            data: <?php echo json_encode(array_column($daily_shipment_data, 'daily_shipments')); ?>
        }, {
            name: 'Daily Revenue (KES)',
            type: 'line',
            data: <?php echo json_encode(array_column($daily_shipment_data, 'daily_revenue')); ?>
        }, {
            name: 'Weight (KG)',
            type: 'area',
            data: <?php echo json_encode(array_column($daily_shipment_data, 'daily_weight')); ?>
        }],
        chart: {
            height: 350,
            type: 'line',
            toolbar: { show: false },
            animations: { enabled: false }
        },
        stroke: {
            curve: 'smooth',
            width: [0, 3, 0]
        },
        fill: {
            type: ['solid', 'solid', 'gradient'],
            gradient: {
                shadeIntensity: 1,
                type: 'vertical',
                colorStops: [
                    { offset: 0, color: '#1e40af', opacity: 0.7 },
                    { offset: 100, color: '#1e40af', opacity: 0.1 }
                ]
            }
        },
        colors: ['#1e40af', '#059669', '#d97706'],
        labels: <?php echo json_encode(array_map(function($item) { 
            return date('M j', strtotime($item['shipment_date'])); 
        }, $daily_shipment_data)); ?>,
        markers: { size: 4 },
        xaxis: { type: 'category' },
        yaxis: [{
            title: { text: 'Shipments / Weight' },
        }, {
            opposite: true,
            title: { text: 'Revenue (KES)' }
        }],
        tooltip: {
            shared: true,
            intersect: false,
            y: [{
                formatter: function (y) {
                    return typeof y !== "undefined" ? y.toFixed(0) + " shipments" : y;
                }
            }, {
                formatter: function (y) {
                    return typeof y !== "undefined" ? "KES " + y.toLocaleString() : y;
                }
            }, {
                formatter: function (y) {
                    return typeof y !== "undefined" ? y.toFixed(2) + " KG" : y;
                }
            }]
        },
        grid: {
            borderColor: '#e2e8f0',
            strokeDashArray: 3
        }
    };

    try {
        var shipmentChart = new ApexCharts(document.querySelector("#shipment-trends"), shipmentOptions);
        shipmentChart.render();
    } catch(e) {
        console.error("Error rendering shipment chart:", e);
        document.querySelector("#shipment-trends").innerHTML = '<div class="text-center p-4"><p style="color: #64748b;">Unable to load chart data</p></div>';
    }

    // Service Types Chart
    var serviceOptions = {
        series: <?php echo json_encode(array_column($service_type_data, 'shipment_count')); ?>,
        chart: {
            type: 'donut',
            height: 350,
            animations: { enabled: false }
        },
        labels: <?php echo json_encode(array_column($service_type_data, 'service_name')); ?>,
        plotOptions: {
            pie: {
                donut: { size: '70%' }
            }
        },
        legend: { position: 'bottom' },
        colors: ['#1e40af', '#059669', '#d97706', '#7c3aed', '#dc2626', '#0891b2'],
        tooltip: {
            y: {
                formatter: function(value) {
                    return value.toLocaleString() + " shipments";
                }
            }
        }
    };

    try {
        var serviceChart = new ApexCharts(document.querySelector("#service-types"), serviceOptions);
        serviceChart.render();
    } catch(e) {
        console.error("Error rendering service chart:", e);
        document.querySelector("#service-types").innerHTML = '<div class="text-center p-4"><p style="color: #64748b;">Unable to load chart data</p></div>';
    }

    // Status Distribution Chart
    var statusOptions = {
        series: <?php echo json_encode(array_column($status_data, 'status_count')); ?>,
        chart: {
            type: 'pie',
            height: 350,
            animations: { enabled: false }
        },
        labels: <?php echo json_encode(array_map(function($item) {
            return ucfirst(strtolower(str_replace('_', ' ', $item['status'])));
        }, $status_data)); ?>,
        legend: { position: 'bottom' },
        colors: ['#1e40af', '#059669', '#d97706', '#7c3aed', '#dc2626', '#f59e0b', '#8b5cf6', '#06b6d4'],
        tooltip: {
            y: {
                formatter: function(value) {
                    return value.toLocaleString() + " shipments";
                }
            }
        }
    };

    try {
        var statusChart = new ApexCharts(document.querySelector("#status-distribution"), statusOptions);
        statusChart.render();
    } catch(e) {
        console.error("Error rendering status chart:", e);
        document.querySelector("#status-distribution").innerHTML = '<div class="text-center p-4"><p style="color: #64748b;">Unable to load chart data</p></div>';
    }
});
</script>

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