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

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

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

// Get user details and role verification
$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 || $user_details['role_name'] !== 'Super Admin') {
    header('Location: dashboard.php');
    exit();
}

$full_name = $user_details['full_name'];
$role_name = $user_details['role_name'];

// Date range parameters
$date_range = isset($_GET['range']) ? $_GET['range'] : '30';
$custom_from = isset($_GET['custom_from']) ? $_GET['custom_from'] : '';
$custom_to = isset($_GET['custom_to']) ? $_GET['custom_to'] : '';

// Calculate date range
switch ($date_range) {
    case '7':
        $start_date = date('Y-m-d', strtotime('-7 days'));
        $end_date = date('Y-m-d');
        break;
    case '30':
        $start_date = date('Y-m-d', strtotime('-30 days'));
        $end_date = date('Y-m-d');
        break;
    case '90':
        $start_date = date('Y-m-d', strtotime('-90 days'));
        $end_date = date('Y-m-d');
        break;
    case 'custom':
        $start_date = $custom_from ?: date('Y-m-d', strtotime('-30 days'));
        $end_date = $custom_to ?: date('Y-m-d');
        break;
    default:
        $start_date = date('Y-m-d', strtotime('-30 days'));
        $end_date = date('Y-m-d');
}

// Key metrics
$sql_metrics = "SELECT 
    COUNT(*) as total_orders,
    COUNT(CASE WHEN o.status = 'DELIVERED' THEN 1 END) as completed_orders,
    COUNT(CASE WHEN o.status = 'CANCELLED' THEN 1 END) as cancelled_orders,
    COUNT(CASE WHEN o.status IN ('PENDING', 'IN_PROGRESS', 'READY') THEN 1 END) as active_orders,
    COALESCE(SUM(o.total_amount), 0) as total_revenue,
    COALESCE(SUM(CASE WHEN o.status = 'DELIVERED' THEN o.total_amount ELSE 0 END), 0) as completed_revenue,
    COALESCE(AVG(o.total_amount), 0) as avg_order_value,
    COALESCE(SUM(o.total_pieces), 0) as total_pieces,
    COALESCE(AVG(o.total_pieces), 0) as avg_pieces_per_order,
    COUNT(DISTINCT o.customer_id) as unique_customers,
    COALESCE(AVG(TIMESTAMPDIFF(HOUR, o.order_date, o.estimated_delivery)), 0) as avg_turnaround_hours
FROM orders o
WHERE DATE(o.order_date) BETWEEN ? AND ?";

$stmt_metrics = $conn->prepare($sql_metrics);
$stmt_metrics->bind_param("ss", $start_date, $end_date);
$stmt_metrics->execute();
$metrics = $stmt_metrics->get_result()->fetch_assoc();

// Daily order trends
$sql_daily_trends = "SELECT 
    DATE(o.order_date) as order_date,
    COUNT(*) as order_count,
    COALESCE(SUM(o.total_amount), 0) as daily_revenue,
    COALESCE(SUM(o.total_pieces), 0) as daily_pieces,
    COUNT(CASE WHEN o.status = 'DELIVERED' THEN 1 END) as completed_count,
    COUNT(CASE WHEN o.status = 'CANCELLED' THEN 1 END) as cancelled_count
FROM orders o
WHERE DATE(o.order_date) BETWEEN ? AND ?
GROUP BY DATE(o.order_date)
ORDER BY order_date";

$stmt_trends = $conn->prepare($sql_daily_trends);
$stmt_trends->bind_param("ss", $start_date, $end_date);
$stmt_trends->execute();
$daily_trends = $stmt_trends->get_result();

$trend_data = [];
while ($row = $daily_trends->fetch_assoc()) {
    $trend_data[] = $row;
}

// Status distribution
$sql_status = "SELECT 
    o.status,
    COUNT(*) as count,
    COALESCE(SUM(o.total_amount), 0) as revenue,
    COALESCE(AVG(o.total_amount), 0) as avg_value
FROM orders o
WHERE DATE(o.order_date) BETWEEN ? AND ?
GROUP BY o.status
ORDER BY count DESC";

$stmt_status = $conn->prepare($sql_status);
$stmt_status->bind_param("ss", $start_date, $end_date);
$stmt_status->execute();
$status_data = $stmt_status->get_result();

$status_distribution = [];
while ($row = $status_data->fetch_assoc()) {
    $status_distribution[] = $row;
}

// Service performance
$sql_services = "SELECT 
    s.service_name,
    s.price as unit_price,
    COUNT(oi.item_id) as order_count,
    SUM(oi.quantity) as total_quantity,
    COALESCE(SUM(oi.total_price), 0) as total_revenue,
    COALESCE(AVG(oi.unit_price), 0) as avg_unit_price
FROM order_items oi
INNER JOIN services s ON oi.service_id = s.service_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE DATE(o.order_date) BETWEEN ? AND ?
GROUP BY s.service_id, s.service_name, s.price
ORDER BY total_revenue DESC
LIMIT 10";

$stmt_services = $conn->prepare($sql_services);
$stmt_services->bind_param("ss", $start_date, $end_date);
$stmt_services->execute();
$service_performance = $stmt_services->get_result();

// Customer analytics
$sql_customers = "SELECT 
    c.customer_type,
    COUNT(DISTINCT o.customer_id) as customer_count,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_revenue,
    COALESCE(AVG(o.total_amount), 0) as avg_order_value,
    COALESCE(AVG(customer_orders.order_frequency), 0) as avg_orders_per_customer
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN (
    SELECT customer_id, COUNT(*) as order_frequency 
    FROM orders 
    WHERE DATE(order_date) BETWEEN ? AND ?
    GROUP BY customer_id
) customer_orders ON o.customer_id = customer_orders.customer_id
WHERE DATE(o.order_date) BETWEEN ? AND ?
GROUP BY c.customer_type
ORDER BY total_revenue DESC";

$stmt_customers = $conn->prepare($sql_customers);
$stmt_customers->bind_param("ssss", $start_date, $end_date, $start_date, $end_date);
$stmt_customers->execute();
$customer_analytics = $stmt_customers->get_result();

// Payment method analysis
$sql_payments = "SELECT 
    pm.method_name,
    pm.method_type,
    COUNT(p.payment_id) as transaction_count,
    COALESCE(SUM(p.amount), 0) as total_amount,
    COALESCE(AVG(p.amount), 0) as avg_transaction_value
FROM payments p
INNER JOIN payment_methods pm ON p.payment_method_id = pm.method_id
INNER JOIN orders o ON p.order_id = o.order_id
WHERE DATE(o.order_date) BETWEEN ? AND ? AND p.status = 'COMPLETED'
GROUP BY pm.method_id, pm.method_name, pm.method_type
ORDER BY total_amount DESC";

$stmt_payments = $conn->prepare($sql_payments);
$stmt_payments->bind_param("ss", $start_date, $end_date);
$stmt_payments->execute();
$payment_methods = $stmt_payments->get_result();

// Peak hours analysis
$sql_peak_hours = "SELECT 
    HOUR(o.order_date) as hour_of_day,
    COUNT(*) as order_count,
    COALESCE(SUM(o.total_amount), 0) as hourly_revenue
FROM orders o
WHERE DATE(o.order_date) BETWEEN ? AND ?
GROUP BY HOUR(o.order_date)
ORDER BY hour_of_day";

$stmt_peak = $conn->prepare($sql_peak_hours);
$stmt_peak->bind_param("ss", $start_date, $end_date);
$stmt_peak->execute();
$peak_hours = $stmt_peak->get_result();

$hourly_data = [];
while ($row = $peak_hours->fetch_assoc()) {
    $hourly_data[] = $row;
}

$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Order Analytics - Laundry Management')); ?>
    <!-- apexcharts css -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.css">
    <?php include 'layouts/head-css.php'; ?>
    
    <style>
        body {
            background-color: #f8fafc;
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
        }
        
        .page-content {
            padding: 1.5rem;
        }
        
        /* Header Section */
        .analytics-header {
            background: linear-gradient(135deg, #7c3aed 0%, #a855f7 100%);
            color: white;
            border-radius: 12px;
            padding: 2rem;
            margin-bottom: 2rem;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
        }
        
        .header-content {
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 1rem;
        }
        
        .header-title h1 {
            font-size: 1.875rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
        }
        
        .header-subtitle {
            opacity: 0.9;
            font-size: 1rem;
        }
        
        .date-range-selector {
            display: flex;
            gap: 0.5rem;
            background: rgba(255, 255, 255, 0.1);
            border-radius: 8px;
            padding: 0.25rem;
        }
        
        .date-range-btn {
            padding: 0.5rem 1rem;
            border: none;
            background: transparent;
            color: white;
            border-radius: 6px;
            cursor: pointer;
            transition: all 0.2s ease;
            text-decoration: none;
            font-size: 0.875rem;
            font-weight: 500;
        }
        
        .date-range-btn:hover,
        .date-range-btn.active {
            background: rgba(255, 255, 255, 0.2);
            color: white;
        }
        
        /* Key Metrics */
        .metrics-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(220px, 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-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 1rem;
        }
        
        .metric-title {
            font-size: 0.875rem;
            font-weight: 500;
            color: #64748b;
            text-transform: uppercase;
            letter-spacing: 0.025em;
        }
        
        .metric-icon {
            width: 40px;
            height: 40px;
            border-radius: 8px;
            display: flex;
            align-items: center;
            justify-content: center;
            font-size: 1.25rem;
        }
        
        .metric-value {
            font-size: 2rem;
            font-weight: 700;
            color: #1e293b;
            margin-bottom: 0.5rem;
        }
        
        .metric-change {
            font-size: 0.875rem;
            color: #64748b;
        }
        
        /* Chart Cards */
        .chart-section {
            display: grid;
            grid-template-columns: 2fr 1fr;
            gap: 2rem;
            margin-bottom: 2rem;
        }
        
        .chart-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            overflow: hidden;
        }
        
        .chart-header {
            padding: 1.5rem;
            border-bottom: 1px solid #e2e8f0;
            background: #f8fafc;
        }
        
        .chart-title {
            font-size: 1.125rem;
            font-weight: 700;
            color: #1e293b;
            margin: 0;
        }
        
        .chart-body {
            padding: 1rem;
        }
        
        /* Data Tables */
        .analytics-table {
            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: 700;
            color: #1e293b;
            margin: 0;
        }
        
        .data-table {
            width: 100%;
            border-collapse: collapse;
        }
        
        .data-table th {
            background: #f8fafc;
            padding: 1rem;
            text-align: left;
            font-weight: 600;
            font-size: 0.875rem;
            color: #374151;
            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 Cards */
        .status-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
            gap: 1rem;
            margin-bottom: 2rem;
        }
        
        .status-card {
            background: white;
            border-radius: 8px;
            padding: 1.5rem;
            text-align: center;
            border: 1px solid #e2e8f0;
        }
        
        .status-count {
            font-size: 2rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
        }
        
        .status-label {
            font-size: 0.875rem;
            color: #64748b;
            text-transform: uppercase;
            letter-spacing: 0.025em;
        }
        
        .status-pending .status-count { color: #d97706; }
        .status-in_progress .status-count { color: #3b82f6; }
        .status-ready .status-count { color: #059669; }
        .status-delivered .status-count { color: #10b981; }
        .status-cancelled .status-count { color: #dc2626; }
        
        /* Progress Bars */
        .progress-bar {
            width: 100%;
            height: 8px;
            background: #f1f5f9;
            border-radius: 4px;
            overflow: hidden;
            margin-bottom: 0.5rem;
        }
        
        .progress-fill {
            height: 100%;
            background: #3b82f6;
            border-radius: 4px;
            transition: width 0.3s ease;
        }
        
        /* Custom Date Range */
        .custom-date-section {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            margin-bottom: 2rem;
            border: 1px solid #e2e8f0;
        }
        
        .date-inputs {
            display: grid;
            grid-template-columns: auto auto auto auto;
            gap: 1rem;
            align-items: end;
        }
        
        .form-group {
            margin-bottom: 0;
        }
        
        .form-label {
            font-size: 0.875rem;
            font-weight: 600;
            color: #374151;
            margin-bottom: 0.5rem;
        }
        
        .form-control {
            border: 1px solid #d1d5db;
            border-radius: 8px;
            padding: 0.75rem;
            font-size: 0.875rem;
        }
        
        .btn-apply {
            background: #7c3aed;
            border: 1px solid #7c3aed;
            color: white;
            padding: 0.75rem 1.5rem;
            border-radius: 8px;
            text-decoration: none;
            font-weight: 600;
            cursor: pointer;
            transition: all 0.2s ease;
        }
        
        .btn-apply:hover {
            background: #6d28d9;
            border-color: #6d28d9;
            transform: translateY(-1px);
        }
        
        /* Responsive Design */
        @media (max-width: 768px) {
            .chart-section {
                grid-template-columns: 1fr;
            }
            
            .metrics-grid {
                grid-template-columns: 1fr;
            }
            
            .date-inputs {
                grid-template-columns: 1fr;
            }
            
            .date-range-selector {
                flex-direction: column;
            }
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Analytics Header -->
                    <div class="analytics-header">
                        <div class="header-content">
                            <div class="header-title">
                                <h1>Order Analytics Dashboard</h1>
                                <div class="header-subtitle">
                                    Comprehensive order insights and performance metrics
                                </div>
                            </div>
                            <div class="date-range-selector">
                                <a href="?range=7" class="date-range-btn <?php echo $date_range === '7' ? 'active' : ''; ?>">7 Days</a>
                                <a href="?range=30" class="date-range-btn <?php echo $date_range === '30' ? 'active' : ''; ?>">30 Days</a>
                                <a href="?range=90" class="date-range-btn <?php echo $date_range === '90' ? 'active' : ''; ?>">90 Days</a>
                                <a href="?range=custom" class="date-range-btn <?php echo $date_range === 'custom' ? 'active' : ''; ?>">Custom</a>
                            </div>
                        </div>
                    </div>

                    <!-- Custom Date Range -->
                    <?php if ($date_range === 'custom'): ?>
                    <div class="custom-date-section">
                        <form method="GET" class="date-inputs">
                            <input type="hidden" name="range" value="custom">
                            <div class="form-group">
                                <label class="form-label">From Date</label>
                                <input type="date" name="custom_from" class="form-control" value="<?php echo $custom_from; ?>">
                            </div>
                            <div class="form-group">
                                <label class="form-label">To Date</label>
                                <input type="date" name="custom_to" class="form-control" value="<?php echo $custom_to; ?>">
                            </div>
                            <div class="form-group">
                                <button type="submit" class="btn-apply">Apply Range</button>
                            </div>
                            <div class="form-group">
                                <div style="font-size: 0.875rem; color: #64748b; padding: 0.75rem 0;">
                                    <?php echo date('M j, Y', strtotime($start_date)); ?> - <?php echo date('M j, Y', strtotime($end_date)); ?>
                                </div>
                            </div>
                        </form>
                    </div>
                    <?php endif; ?>

                    <!-- Key Metrics -->
                    <div class="metrics-grid">
                        <div class="metric-card">
                            <div class="metric-header">
                                <div class="metric-title">Total Orders</div>
                                <div class="metric-icon" style="background: #dbeafe; color: #1d4ed8;">
                                    📋
                                </div>
                            </div>
                            <div class="metric-value"><?php echo number_format($metrics['total_orders']); ?></div>
                            <div class="metric-change">
                                <?php echo number_format(($metrics['total_orders'] / max(1, (strtotime($end_date) - strtotime($start_date)) / 86400)), 1); ?> orders/day avg
                            </div>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-header">
                                <div class="metric-title">Total Revenue</div>
                                <div class="metric-icon" style="background: #dcfce7; color: #059669;">
                                    💰
                                </div>
                            </div>
                            <div class="metric-value">KES <?php echo number_format($metrics['total_revenue']); ?></div>
                            <div class="metric-change">
                                KES <?php echo number_format($metrics['avg_order_value'], 2); ?> avg per order
                            </div>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-header">
                                <div class="metric-title">Completion Rate</div>
                                <div class="metric-icon" style="background: #f0fdf4; color: #16a34a;">
                                    ✅
                                </div>
                            </div>
                            <div class="metric-value">
                                <?php echo $metrics['total_orders'] > 0 ? number_format(($metrics['completed_orders'] / $metrics['total_orders']) * 100, 1) : 0; ?>%
                            </div>
                            <div class="metric-change">
                                <?php echo number_format($metrics['completed_orders']); ?> of <?php echo number_format($metrics['total_orders']); ?> orders completed
                            </div>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-header">
                                <div class="metric-title">Total Pieces</div>
                                <div class="metric-icon" style="background: #fef3c7; color: #d97706;">
                                    👕
                                </div>
                            </div>
                            <div class="metric-value"><?php echo number_format($metrics['total_pieces']); ?></div>
                            <div class="metric-change">
                                <?php echo number_format($metrics['avg_pieces_per_order'], 1); ?> pieces per order avg
                            </div>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-header">
                                <div class="metric-title">Unique Customers</div>
                                <div class="metric-icon" style="background: #e0e7ff; color: #4f46e5;">
                                    👥
                                </div>
                            </div>
                            <div class="metric-value"><?php echo number_format($metrics['unique_customers']); ?></div>
                            <div class="metric-change">
                                <?php echo $metrics['unique_customers'] > 0 ? number_format($metrics['total_orders'] / $metrics['unique_customers'], 1) : 0; ?> orders per customer
                            </div>
                        </div>
                        
                        <div class="metric-card">
                            <div class="metric-header">
                                <div class="metric-title">Avg Turnaround</div>
                                <div class="metric-icon" style="background: #fce7f3; color: #be185d;">
                                    ⏱️
                                </div>
                            </div>
                            <div class="metric-value"><?php echo number_format($metrics['avg_turnaround_hours']); ?>h</div>
                            <div class="metric-change">
                                Average processing time
                            </div>
                        </div>
                    </div>

                    <!-- Order Status Distribution -->
                    <div class="status-grid">
                        <?php 
                        $status_colors = [
                            'PENDING' => '#d97706',
                            'IN_PROGRESS' => '#3b82f6', 
                            'READY' => '#059669',
                            'DELIVERED' => '#10b981',
                            'CANCELLED' => '#dc2626'
                        ];
                        foreach ($status_distribution as $status): 
                        ?>
                        <div class="status-card status-<?php echo strtolower($status['status']); ?>">
                            <div class="status-count"><?php echo number_format($status['count']); ?></div>
                            <div class="status-label"><?php echo str_replace('_', ' ', $status['status']); ?></div>
                            <div style="font-size: 0.75rem; color: #64748b; margin-top: 0.25rem;">
                                KES <?php echo number_format($status['revenue']); ?>
                            </div>
                        </div>
                        <?php endforeach; ?>
                    </div>

                    <!-- Charts Section -->
                    <div class="chart-section">
                        <div class="chart-card">
                            <div class="chart-header">
                                <h3 class="chart-title">Daily Order Trends</h3>
                            </div>
                            <div class="chart-body">
                                <div id="daily-trends-chart" style="height: 350px;"></div>
                            </div>
                        </div>
                        
                        <div class="chart-card">
                            <div class="chart-header">
                                <h3 class="chart-title">Order Status Distribution</h3>
                            </div>
                            <div class="chart-body">
                                <div id="status-pie-chart" style="height: 350px;"></div>
                            </div>
                        </div>
                    </div>

                    <!-- Performance Tables -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="analytics-table">
                                <div class="table-header">
                                    <h3 class="table-title">Top Performing Services</h3>
                                </div>
                                <table class="data-table">
                                    <thead>
                                        <tr>
                                            <th>Service Name</th>
                                            <th>Orders</th>
                                            <th>Quantity</th>
                                            <th>Revenue</th>
                                            <th>Avg Price</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while($service = $service_performance->fetch_assoc()): ?>
                                        <tr>
                                            <td style="font-weight: 600;"><?php echo htmlspecialchars($service['service_name']); ?></td>
                                            <td><?php echo number_format($service['order_count']); ?></td>
                                            <td><?php echo number_format($service['total_quantity']); ?></td>
                                            <td style="font-weight: 600; color: #059669;">
                                                KES <?php echo number_format($service['total_revenue']); ?>
                                            </td>
                                            <td style="font-family: 'Courier New', monospace;">
                                                KES <?php echo number_format($service['avg_unit_price'], 2); ?>
                                            </td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                        
                        <div class="col-xl-6">
                            <div class="analytics-table">
                                <div class="table-header">
                                    <h3 class="table-title">Customer Type Performance</h3>
                                </div>
                                <table class="data-table">
                                    <thead>
                                        <tr>
                                            <th>Customer Type</th>
                                            <th>Customers</th>
                                            <th>Orders</th>
                                            <th>Revenue</th>
                                            <th>Avg Order Value</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php while($customer = $customer_analytics->fetch_assoc()): ?>
                                        <tr>
                                            <td>
                                                <span class="status-badge status-<?php echo strtolower($customer['customer_type']); ?>">
                                                    <?php echo $customer['customer_type']; ?>
                                                </span>
                                            </td>
                                            <td><?php echo number_format($customer['customer_count']); ?></td>
                                            <td><?php echo number_format($customer['order_count']); ?></td>
                                            <td style="font-weight: 600; color: #059669;">
                                                KES <?php echo number_format($customer['total_revenue']); ?>
                                            </td>
                                            <td style="font-family: 'Courier New', monospace;">
                                                KES <?php echo number_format($customer['avg_order_value'], 2); ?>
                                            </td>
                                        </tr>
                                        <?php endwhile; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>

                    <!-- Payment Methods Analysis -->
                    <div class="analytics-table">
                        <div class="table-header">
                            <h3 class="table-title">Payment Method Performance</h3>
                        </div>
                        <table class="data-table">
                            <thead>
                                <tr>
                                    <th>Payment Method</th>
                                    <th>Type</th>
                                    <th>Transactions</th>
                                    <th>Total Amount</th>
                                    <th>Avg Transaction</th>
                                    <th>Share</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php 
                                $total_payment_amount = 0;
                                $payment_data = [];
                                while($payment = $payment_methods->fetch_assoc()) {
                                    $payment_data[] = $payment;
                                    $total_payment_amount += $payment['total_amount'];
                                }
                                foreach($payment_data as $payment): 
                                    $share_percentage = $total_payment_amount > 0 ? ($payment['total_amount'] / $total_payment_amount) * 100 : 0;
                                ?>
                                <tr>
                                    <td style="font-weight: 600;"><?php echo htmlspecialchars($payment['method_name']); ?></td>
                                    <td>
                                        <span class="status-badge status-<?php echo strtolower($payment['method_type']); ?>">
                                            <?php echo $payment['method_type']; ?>
                                        </span>
                                    </td>
                                    <td><?php echo number_format($payment['transaction_count']); ?></td>
                                    <td style="font-weight: 600; color: #059669;">
                                        KES <?php echo number_format($payment['total_amount']); ?>
                                    </td>
                                    <td style="font-family: 'Courier New', monospace;">
                                        KES <?php echo number_format($payment['avg_transaction_value'], 2); ?>
                                    </td>
                                    <td>
                                        <div class="progress-bar">
                                            <div class="progress-fill" style="width: <?php echo $share_percentage; ?>%;"></div>
                                        </div>
                                        <?php echo number_format($share_percentage, 1); ?>%
                                    </td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Peak Hours Chart -->
                    <div class="chart-card">
                        <div class="chart-header">
                            <h3 class="chart-title">Order Volume by Hour of Day</h3>
                        </div>
                        <div class="chart-body">
                            <div id="peak-hours-chart" style="height: 300px;"></div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.min.js"></script>
    
    <script>
document.addEventListener('DOMContentLoaded', function() {
    // Daily Trends Chart
    var trendsOptions = {
        series: [{
            name: 'Orders',
            type: 'column',
            data: <?php echo json_encode(array_column($trend_data, 'order_count')); ?>
        }, {
            name: 'Revenue (KES)',
            type: 'line',
            data: <?php echo json_encode(array_column($trend_data, 'daily_revenue')); ?>
        }, {
            name: 'Pieces',
            type: 'area',
            data: <?php echo json_encode(array_column($trend_data, 'daily_pieces')); ?>
        }],
        chart: {
            height: 350,
            type: 'line',
            toolbar: { show: false }
        },
        stroke: {
            width: [0, 4, 2],
            curve: 'smooth'
        },
        plotOptions: {
            bar: {
                columnWidth: '50%'
            }
        },
        fill: {
            opacity: [0.85, 1, 0.25],
            gradient: {
                inverseColors: false,
                shade: 'light',
                type: "vertical",
                opacityFrom: 0.85,
                opacityTo: 0.55,
                stops: [0, 100, 100, 100]
            }
        },
        labels: <?php echo json_encode(array_map(function($item) { 
            return date('M j', strtotime($item['order_date'])); 
        }, $trend_data)); ?>,
        markers: { size: [0, 5, 0] },
        xaxis: {
            type: 'category'
        },
        yaxis: [{
            title: { text: 'Orders / Pieces' }
        }, {
            opposite: true,
            title: { text: 'Revenue (KES)' }
        }],
        colors: ['#3b82f6', '#059669', '#d97706'],
        tooltip: {
            shared: true,
            intersect: false,
            y: [{
                formatter: function (y) {
                    return typeof y !== "undefined" ? y + " orders" : y;
                }
            }, {
                formatter: function (y) {
                    return typeof y !== "undefined" ? "KES " + y.toLocaleString() : y;
                }
            }, {
                formatter: function (y) {
                    return typeof y !== "undefined" ? y + " pieces" : y;
                }
            }]
        }
    };

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

    // Status Distribution Pie Chart
    var statusOptions = {
        series: <?php echo json_encode(array_column($status_distribution, 'count')); ?>,
        chart: {
            type: 'donut',
            height: 350
        },
        labels: <?php echo json_encode(array_map(function($item) {
            return str_replace('_', ' ', ucfirst(strtolower($item['status'])));
        }, $status_distribution)); ?>,
        colors: ['#d97706', '#3b82f6', '#059669', '#10b981', '#dc2626'],
        plotOptions: {
            pie: {
                donut: {
                    size: '70%',
                    labels: {
                        show: true,
                        total: {
                            show: true,
                            label: 'Total Orders',
                            formatter: function (w) {
                                return w.globals.seriesTotals.reduce((a, b) => {
                                    return a + b;
                                }, 0);
                            }
                        }
                    }
                }
            }
        },
        legend: {
            position: 'bottom'
        },
        tooltip: {
            y: {
                formatter: function(value) {
                    return value + " orders";
                }
            }
        }
    };

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

    // Peak Hours Chart
    var peakHoursOptions = {
        series: [{
            name: 'Orders',
            data: <?php echo json_encode(array_column($hourly_data, 'order_count')); ?>
        }],
        chart: {
            type: 'bar',
            height: 300,
            toolbar: { show: false }
        },
        plotOptions: {
            bar: {
                borderRadius: 4,
                horizontal: false,
                columnWidth: '60%'
            }
        },
        dataLabels: {
            enabled: false
        },
        xaxis: {
            categories: <?php echo json_encode(array_map(function($item) {
                return sprintf('%02d:00', $item['hour_of_day']);
            }, $hourly_data)); ?>,
            title: { text: 'Hour of Day' }
        },
        yaxis: {
            title: { text: 'Number of Orders' }
        },
        colors: ['#7c3aed'],
        tooltip: {
            y: {
                formatter: function(value) {
                    return value + " orders";
                }
            }
        },
        grid: {
            borderColor: '#e2e8f0'
        }
    };

    var peakChart = new ApexCharts(document.querySelector("#peak-hours-chart"), peakHoursOptions);
    peakChart.render();
});
</script>

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