<?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 aging parameters from settings
$sql_turnaround = "SELECT setting_value FROM settings WHERE setting_key = 'default_turnaround_hours' LIMIT 1";
$result_turnaround = $conn->query($sql_turnaround);
$default_turnaround_hours = $result_turnaround->fetch_assoc()['setting_value'] ?? 24;

// Calculate aging buckets
$current_time = new DateTime();

// Summary statistics for aging orders
$sql_aging_summary = "SELECT 
    COUNT(CASE WHEN o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > 0 THEN 1 END) as overdue_ready_orders,
    COUNT(CASE WHEN o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) BETWEEN 1 AND 24 THEN 1 END) as overdue_1_24_hours,
    COUNT(CASE WHEN o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) BETWEEN 25 AND 72 THEN 1 END) as overdue_1_3_days,
    COUNT(CASE WHEN o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) BETWEEN 73 AND 168 THEN 1 END) as overdue_3_7_days,
    COUNT(CASE WHEN o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > 168 THEN 1 END) as overdue_over_7_days,
    AVG(CASE WHEN o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > 0 
        THEN TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) END) as avg_overdue_hours,
    SUM(CASE WHEN o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > 0 THEN o.total_amount ELSE 0 END) as overdue_revenue_at_risk
FROM orders o
WHERE o.status IN ('READY', 'IN_PROGRESS', 'PENDING')";

$result_summary = $conn->query($sql_aging_summary);
$aging_summary = $result_summary->fetch_assoc();

// Get detailed aging report
$sql_aging_details = "SELECT 
    o.order_id,
    o.order_number,
    o.order_date,
    o.estimated_delivery,
    o.actual_delivery,
    o.status,
    o.priority,
    o.total_pieces,
    o.total_amount,
    o.payment_status,
    o.special_instructions,
    c.customer_id,
    c.first_name,
    c.last_name,
    c.phone,
    c.customer_code,
    c.customer_type,
    u.full_name as processed_by_name,
    ua.full_name as assigned_to_name,
    TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) as hours_overdue,
    TIMESTAMPDIFF(DAY, o.estimated_delivery, NOW()) as days_overdue,
    CASE 
        WHEN TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) BETWEEN 1 AND 24 THEN '1-24 Hours'
        WHEN TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) BETWEEN 25 AND 72 THEN '1-3 Days'
        WHEN TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) BETWEEN 73 AND 168 THEN '3-7 Days'
        WHEN TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > 168 THEN 'Over 7 Days'
        ELSE 'Not Overdue'
    END as aging_bucket
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN users u ON o.created_by = u.user_id
LEFT JOIN users ua ON o.assigned_to = ua.user_id
WHERE o.status IN ('READY', 'IN_PROGRESS', 'PENDING')
    AND (
        (o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > 0)
        OR (o.status = 'IN_PROGRESS' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > -12)
        OR (o.status = 'PENDING' AND TIMESTAMPDIFF(HOUR, o.order_date, NOW()) > ?)
    )
ORDER BY 
    CASE o.status 
        WHEN 'READY' THEN 1 
        WHEN 'IN_PROGRESS' THEN 2 
        WHEN 'PENDING' THEN 3 
    END,
    TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) DESC";

$stmt_details = $conn->prepare($sql_aging_details);
$stmt_details->bind_param("i", $default_turnaround_hours);
$stmt_details->execute();
$aging_details = $stmt_details->get_result();

// Get orders by status for quick filters
$sql_status_counts = "SELECT 
    o.status,
    COUNT(*) as count,
    SUM(o.total_amount) as total_value
FROM orders o
WHERE o.status IN ('READY', 'IN_PROGRESS', 'PENDING')
    AND (
        (o.status = 'READY' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > 0)
        OR (o.status = 'IN_PROGRESS' AND TIMESTAMPDIFF(HOUR, o.estimated_delivery, NOW()) > -12)
        OR (o.status = 'PENDING' AND TIMESTAMPDIFF(HOUR, o.order_date, NOW()) > ?)
    )
GROUP BY o.status";

$stmt_status = $conn->prepare($sql_status_counts);
$stmt_status->bind_param("i", $default_turnaround_hours);
$stmt_status->execute();
$status_counts = $stmt_status->get_result();
$status_data = [];
while ($row = $status_counts->fetch_assoc()) {
    $status_data[$row['status']] = $row;
}

// Get customer notifications sent for overdue orders
$sql_notification_stats = "SELECT 
    COUNT(DISTINCT n.notification_id) as notifications_sent,
    COUNT(DISTINCT CASE WHEN n.is_read = 0 THEN n.notification_id END) as unread_notifications
FROM notifications n
WHERE n.notification_type = 'ORDER_OVERDUE'
    AND DATE(n.created_at) = CURDATE()";

$result_notifications = $conn->query($sql_notification_stats);
$notification_stats = $result_notifications->fetch_assoc();

$conn->close();
?>

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

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Orders Aging Report - Laundry Management System')); ?>
    <!-- DataTables CSS -->
    <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">
    <link href="https://cdn.datatables.net/buttons/2.4.1/css/buttons.bootstrap5.min.css" rel="stylesheet">
    <!-- apexcharts css -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/apexcharts@latest/dist/apexcharts.css">
    <?php include 'layouts/head-css.php'; ?>
    
    <style>
        /* Professional Dashboard Styling - Laundry Management Theme */
        body {
            background-color: #f8fafc;
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
        }
        
        .page-content {
            padding: 1.5rem;
        }
        
        /* Header Section */
        .report-header {
            background: linear-gradient(135deg, #dc2626 0%, #ef4444 100%);
            color: white;
            border-radius: 12px;
            padding: 2rem;
            margin-bottom: 2rem;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
        }
        
        .report-content {
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 1rem;
        }
        
        .report-text h2 {
            font-size: 1.75rem;
            font-weight: 600;
            margin-bottom: 0.5rem;
        }
        
        .report-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-card.critical {
            border-left: 4px solid #dc2626;
        }
        
        .metric-card.warning {
            border-left: 4px solid #d97706;
        }
        
        .metric-card.info {
            border-left: 4px solid #3b82f6;
        }
        
        .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-value.critical { color: #dc2626; }
        .metric-value.warning { color: #d97706; }
        .metric-value.info { color: #3b82f6; }
        
        .metric-change {
            font-size: 0.875rem;
            font-weight: 500;
            color: #64748b;
        }
        
        /* 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-ready { background: #dcfce7; color: #166534; }
        .status-in_progress { background: #fef3c7; color: #92400e; }
        .status-pending { background: #fee2e2; color: #991b1b; }
        .status-delivered { background: #dbeafe; color: #1d4ed8; }
        .status-cancelled { background: #f3f4f6; color: #374151; }
        
        .priority-normal { background: #f1f5f9; color: #64748b; }
        .priority-high { background: #fef3c7; color: #92400e; }
        .priority-urgent { background: #fee2e2; color: #991b1b; }
        
        .aging-1-24-hours { background: #fef3c7; color: #92400e; }
        .aging-1-3-days { background: #fed7aa; color: #c2410c; }
        .aging-3-7-days { background: #fecaca; color: #dc2626; }
        .aging-over-7-days { background: #fee2e2; color: #991b1b; }
        
        /* 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;
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 1rem;
        }
        
        .table-title {
            font-size: 1.125rem;
            font-weight: 600;
            color: #1e293b;
            margin: 0;
        }
        
        .table-actions {
            display: flex;
            gap: 0.5rem;
            align-items: center;
        }
        
        .filter-btn {
            padding: 0.5rem 1rem;
            border: 1px solid #e2e8f0;
            background: white;
            border-radius: 6px;
            font-size: 0.875rem;
            font-weight: 500;
            color: #64748b;
            text-decoration: none;
            transition: all 0.2s ease;
        }
        
        .filter-btn:hover, .filter-btn.active {
            background: #059669;
            border-color: #059669;
            color: white;
        }
        
        .export-btn {
            padding: 0.5rem 1rem;
            background: #059669;
            color: white;
            border: none;
            border-radius: 6px;
            font-size: 0.875rem;
            font-weight: 500;
            text-decoration: none;
            transition: all 0.2s ease;
        }
        
        .export-btn:hover {
            background: #047857;
            color: white;
        }
        
        /* Customer Info */
        .customer-info {
            display: flex;
            align-items: center;
        }
        
        .customer-avatar {
            width: 36px;
            height: 36px;
            border-radius: 50%;
            background: #059669;
            color: white;
            display: flex;
            align-items: center;
            justify-content: center;
            font-weight: 600;
            font-size: 0.75rem;
            margin-right: 0.75rem;
        }
        
        .customer-details {
            display: flex;
            flex-direction: column;
        }
        
        .customer-name {
            font-weight: 600;
            color: #1e293b;
            font-size: 0.875rem;
            margin-bottom: 0.25rem;
        }
        
        .customer-meta {
            font-size: 0.75rem;
            color: #64748b;
        }
        
        /* Order Info */
        .order-number {
            font-weight: 600;
            color: #059669;
            text-decoration: none;
        }
        
        .order-number:hover {
            color: #047857;
            text-decoration: underline;
        }
        
        .order-meta {
            font-size: 0.75rem;
            color: #64748b;
            margin-top: 0.25rem;
        }
        
        /* Aging indicators */
        .aging-indicator {
            display: flex;
            align-items: center;
            gap: 0.5rem;
        }
        
        .aging-value {
            font-weight: 600;
        }
        
        .aging-critical { color: #dc2626; }
        .aging-warning { color: #d97706; }
        .aging-normal { color: #64748b; }
        
        /* Chart containers */
        .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;
        }
        
        /* Responsive Design */
        @media (max-width: 768px) {
            .page-content {
                padding: 1rem;
            }
            
            .report-content {
                flex-direction: column;
                text-align: center;
            }
            
            .datetime-display {
                min-width: auto;
                width: 100%;
            }
            
            .metrics-row {
                grid-template-columns: 1fr;
            }
            
            .table-header {
                flex-direction: column;
                align-items: flex-start;
            }
            
            .table-actions {
                width: 100%;
                justify-content: space-between;
            }
        }
        
        /* DataTable overrides */
        .dataTables_wrapper .dataTables_length,
        .dataTables_wrapper .dataTables_filter,
        .dataTables_wrapper .dataTables_info,
        .dataTables_wrapper .dataTables_paginate {
            margin-bottom: 1rem;
        }
        
        .dataTables_wrapper .dataTables_paginate .paginate_button {
            padding: 0.375rem 0.75rem;
            margin-left: 0.25rem;
            border: 1px solid #e2e8f0;
            border-radius: 0.375rem;
            background: white;
            color: #64748b;
        }
        
        .dataTables_wrapper .dataTables_paginate .paginate_button:hover {
            background: #f8fafc;
            border-color: #059669;
            color: #059669;
        }
        
        .dataTables_wrapper .dataTables_paginate .paginate_button.current {
            background: #059669;
            border-color: #059669;
            color: white;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Report Header -->
                    <div class="report-header">
                        <div class="report-content">
                            <div class="report-text">
                                <h2>📊 Orders Aging Report</h2>
                                <p>Monitor overdue orders and collection delays - Real-time aging analysis for <?php echo date('l, M j, Y'); ?></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 critical">
                            <div class="metric-title">Overdue Ready Orders</div>
                            <div class="metric-value critical"><?php echo number_format($aging_summary['overdue_ready_orders'] ?? 0); ?></div>
                            <div class="metric-change">Orders ready for collection</div>
                        </div>
                        
                        <div class="metric-card warning">
                            <div class="metric-title">Avg Overdue Time</div>
                            <div class="metric-value warning">
                                <?php 
                                $avg_hours = $aging_summary['avg_overdue_hours'] ?? 0;
                                if ($avg_hours > 24) {
                                    echo number_format($avg_hours / 24, 1) . ' days';
                                } else {
                                    echo number_format($avg_hours, 1) . ' hrs';
                                }
                                ?>
                            </div>
                            <div class="metric-change">Average delay time</div>
                        </div>
                        
                        <div class="metric-card critical">
                            <div class="metric-title">Revenue at Risk</div>
                            <div class="metric-value critical">KES <?php echo number_format($aging_summary['overdue_revenue_at_risk'] ?? 0); ?></div>
                            <div class="metric-change">From overdue orders</div>
                        </div>
                        
                        <div class="metric-card warning">
                            <div class="metric-title">1-24 Hours Overdue</div>
                            <div class="metric-value warning"><?php echo number_format($aging_summary['overdue_1_24_hours'] ?? 0); ?></div>
                            <div class="metric-change">Recent delays</div>
                        </div>
                        
                        <div class="metric-card critical">
                            <div class="metric-title">3-7 Days Overdue</div>
                            <div class="metric-value critical"><?php echo number_format($aging_summary['overdue_3_7_days'] ?? 0); ?></div>
                            <div class="metric-change">Significant delays</div>
                        </div>
                        
                        <div class="metric-card info">
                            <div class="metric-title">Customer Notifications</div>
                            <div class="metric-value info"><?php echo number_format($notification_stats['notifications_sent'] ?? 0); ?></div>
                            <div class="metric-change">Sent today</div>
                        </div>
                    </div>

                    <!-- Charts Section -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="chart-card">
                                <div class="chart-header">
                                    <h3 class="chart-title">Aging Distribution</h3>
                                </div>
                                <div class="chart-body">
                                    <div id="aging-distribution" style="height: 350px;"></div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="col-xl-6">
                            <div class="chart-card">
                                <div class="chart-header">
                                    <h3 class="chart-title">Orders by Status</h3>
                                </div>
                                <div class="chart-body">
                                    <div id="status-distribution" style="height: 350px;"></div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Detailed Aging Report -->
                    <div class="table-card">
                        <div class="table-header">
                            <h3 class="table-title">Detailed Aging Report</h3>
                            <div class="table-actions">
                                <button class="filter-btn active" onclick="filterTable('all')">All Orders</button>
                                <button class="filter-btn" onclick="filterTable('READY')">Ready (<?php echo $status_data['READY']['count'] ?? 0; ?>)</button>
                                <button class="filter-btn" onclick="filterTable('IN_PROGRESS')">In Progress (<?php echo $status_data['IN_PROGRESS']['count'] ?? 0; ?>)</button>
                                <button class="filter-btn" onclick="filterTable('PENDING')">Pending (<?php echo $status_data['PENDING']['count'] ?? 0; ?>)</button>
                                <a href="export_aging_report.php" class="export-btn">📊 Export Report</a>
                            </div>
                        </div>
                        <div class="table-responsive">
                            <table id="aging-table" class="table table-nowrap table-striped">
                                <thead>
                                    <tr>
                                        <th>Order Details</th>
                                        <th>Customer</th>
                                        <th>Status</th>
                                        <th>Priority</th>
                                        <th>Expected Delivery</th>
                                        <th>Aging</th>
                                        <th>Amount</th>
                                        <th>Payment</th>
                                        <th>Assigned To</th>
                                        <th>Actions</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php while($row = $aging_details->fetch_assoc()): ?>
                                    <tr data-status="<?php echo $row['status']; ?>" data-aging="<?php echo $row['hours_overdue']; ?>">
                                        <td>
                                            <div>
                                                <a href="view_order.php?id=<?php echo $row['order_id']; ?>" class="order-number">
                                                    <?php echo $row['order_number']; ?>
                                                </a>
                                                <div class="order-meta">
                                                    <?php echo $row['total_pieces']; ?> pieces • 
                                                    <?php echo date('M j, Y', strtotime($row['order_date'])); ?>
                                                </div>
                                            </div>
                                        </td>
                                        <td>
                                            <div class="customer-info">
                                                <div class="customer-avatar">
                                                    <?php echo strtoupper(substr($row['first_name'], 0, 1) . substr($row['last_name'], 0, 1)); ?>
                                                </div>
                                                <div class="customer-details">
                                                    <div class="customer-name"><?php echo $row['first_name'] . ' ' . $row['last_name']; ?></div>
                                                    <div class="customer-meta">
                                                        <?php echo $row['customer_code']; ?> • 
                                                        <span class="status-badge status-<?php echo strtolower($row['customer_type']); ?>">
                                                            <?php echo $row['customer_type']; ?>
                                                        </span>
                                                    </div>
                                                </div>
                                            </div>
                                        </td>
                                        <td>
                                            <span class="status-badge status-<?php echo strtolower($row['status']); ?>">
                                                <?php echo ucfirst(strtolower(str_replace('_', ' ', $row['status']))); ?>
                                            </span>
                                        </td>
                                        <td>
                                            <span class="status-badge priority-<?php echo strtolower($row['priority']); ?>">
                                                <?php echo $row['priority']; ?>
                                            </span>
                                        </td>
                                        <td>
                                            <div style="font-weight: 500;">
                                                <?php if($row['estimated_delivery']): ?>
                                                    <?php echo date('M j, Y', strtotime($row['estimated_delivery'])); ?>
                                                    <div style="font-size: 0.75rem; color: #64748b;">
                                                        <?php echo date('g:i A', strtotime($row['estimated_delivery'])); ?>
                                                    </div>
                                                <?php else: ?>
                                                    <span style="color: #64748b;">Not set</span>
                                                <?php endif; ?>
                                            </div>
                                        </td>
                                        <td>
                                            <div class="aging-indicator">
                                                <?php 
                                                $hours_overdue = $row['hours_overdue'];
                                                $aging_class = 'aging-normal';
                                                $aging_text = '';
                                                
                                                if ($hours_overdue > 168) {
                                                    $aging_class = 'aging-critical';
                                                    $aging_text = number_format($hours_overdue / 24, 1) . ' days';
                                                } elseif ($hours_overdue > 72) {
                                                    $aging_class = 'aging-critical';
                                                    $aging_text = number_format($hours_overdue / 24, 1) . ' days';
                                                } elseif ($hours_overdue > 24) {
                                                    $aging_class = 'aging-warning';
                                                    $aging_text = number_format($hours_overdue / 24, 1) . ' days';
                                                } elseif ($hours_overdue > 0) {
                                                    $aging_class = 'aging-warning';
                                                    $aging_text = $hours_overdue . ' hrs';
                                                } else {
                                                    $aging_class = 'aging-normal';
                                                    $aging_text = 'On time';
                                                }
                                                ?>
                                                <span class="aging-value <?php echo $aging_class; ?>">
                                                    <?php echo $aging_text; ?>
                                                </span>
                                                <span class="status-badge <?php echo str_replace('_', '-', strtolower($row['aging_bucket'])); ?>">
                                                    <?php echo $row['aging_bucket']; ?>
                                                </span>
                                            </div>
                                        </td>
                                        <td style="font-weight: 600; color: #059669;">
                                            KES <?php echo number_format($row['total_amount'], 2); ?>
                                        </td>
                                        <td>
                                            <?php 
                                            $payment_class = match($row['payment_status']) {
                                                'PAID' => 'status-ready',
                                                'PARTIAL' => 'status-in_progress',
                                                'PENDING' => 'status-pending',
                                                'REFUNDED' => 'status-cancelled',
                                                default => 'status-pending'
                                            };
                                            ?>
                                            <span class="status-badge <?php echo $payment_class; ?>">
                                                <?php echo ucfirst(strtolower($row['payment_status'])); ?>
                                            </span>
                                        </td>
                                        <td style="color: #64748b;">
                                            <?php echo $row['assigned_to_name'] ?: 'Unassigned'; ?>
                                        </td>
                                        <td>
                                            <div class="d-flex gap-1">
                                                <a href="view_order.php?id=<?php echo $row['order_id']; ?>" 
                                                   class="btn btn-sm btn-outline-primary" 
                                                   title="View Order">
                                                    <i class="ph-eye"></i>
                                                </a>
                                                <?php if($row['status'] == 'READY'): ?>
                                                <a href="deliver_order.php?id=<?php echo $row['order_id']; ?>" 
                                                   class="btn btn-sm btn-outline-success" 
                                                   title="Mark as Delivered">
                                                    <i class="ph-check"></i>
                                                </a>
                                                <?php endif; ?>
                                                <a href="contact_customer.php?id=<?php echo $row['customer_id']; ?>&order=<?php echo $row['order_id']; ?>" 
                                                   class="btn btn-sm btn-outline-warning" 
                                                   title="Contact Customer">
                                                    <i class="ph-phone"></i>
                                                </a>
                                            </div>
                                        </td>
                                    </tr>
                                    <?php endwhile; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>

                    <!-- Action Recommendations -->
                    <div class="row">
                        <div class="col-xl-8">
                            <div class="table-card">
                                <div class="table-header">
                                    <h3 class="table-title">🎯 Recommended Actions</h3>
                                </div>
                                <div class="p-3">
                                    <div class="row">
                                        <div class="col-md-6">
                                            <div class="alert alert-danger" role="alert">
                                                <h6 class="alert-heading"><i class="ph-warning-circle"></i> Critical Items (<?php echo $aging_summary['overdue_over_7_days'] ?? 0; ?>)</h6>
                                                <p class="mb-2">Orders overdue by more than 7 days require immediate attention:</p>
                                                <ul class="mb-0">
                                                    <li>Contact customers immediately</li>
                                                    <li>Verify contact information</li>
                                                    <li>Consider storage fees</li>
                                                    <li>Escalate to management</li>
                                                </ul>
                                            </div>
                                        </div>
                                        <div class="col-md-6">
                                            <div class="alert alert-warning" role="alert">
                                                <h6 class="alert-heading"><i class="ph-clock"></i> Follow-up Required (<?php echo ($aging_summary['overdue_1_3_days'] ?? 0) + ($aging_summary['overdue_3_7_days'] ?? 0); ?>)</h6>
                                                <p class="mb-2">Orders overdue by 1-7 days need follow-up:</p>
                                                <ul class="mb-0">
                                                    <li>Send reminder notifications</li>
                                                    <li>Call customers</li>
                                                    <li>Update delivery preferences</li>
                                                    <li>Schedule pickup reminders</li>
                                                </ul>
                                            </div>
                                        </div>
                                    </div>
                                    <div class="row mt-3">
                                        <div class="col-md-4">
                                            <a href="bulk_notify_customers.php?type=overdue" class="btn btn-warning w-100">
                                                <i class="ph-bell"></i> Send Overdue Notifications
                                            </a>
                                        </div>
                                        <div class="col-md-4">
                                            <a href="update_delivery_preferences.php" class="btn btn-info w-100">
                                                <i class="ph-calendar"></i> Schedule Follow-ups
                                            </a>
                                        </div>
                                        <div class="col-md-4">
                                            <a href="escalate_overdue_orders.php" class="btn btn-danger w-100">
                                                <i class="ph-arrow-up"></i> Escalate Critical Orders
                                            </a>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-4">
                            <div class="chart-card">
                                <div class="chart-header">
                                    <h3 class="chart-title">📈 Aging Trends</h3>
                                </div>
                                <div class="chart-body">
                                    <div class="row text-center">
                                        <div class="col-6 mb-3">
                                            <div style="background: #fee2e2; border-radius: 8px; padding: 1rem;">
                                                <div style="font-size: 1.5rem; font-weight: 700; color: #dc2626;"><?php echo $aging_summary['overdue_over_7_days'] ?? 0; ?></div>
                                                <div style="font-size: 0.875rem; color: #64748b;">Over 7 Days</div>
                                            </div>
                                        </div>
                                        <div class="col-6 mb-3">
                                            <div style="background: #fecaca; border-radius: 8px; padding: 1rem;">
                                                <div style="font-size: 1.5rem; font-weight: 700; color: #dc2626;"><?php echo $aging_summary['overdue_3_7_days'] ?? 0; ?></div>
                                                <div style="font-size: 0.875rem; color: #64748b;">3-7 Days</div>
                                            </div>
                                        </div>
                                        <div class="col-6">
                                            <div style="background: #fed7aa; border-radius: 8px; padding: 1rem;">
                                                <div style="font-size: 1.5rem; font-weight: 700; color: #c2410c;"><?php echo $aging_summary['overdue_1_3_days'] ?? 0; ?></div>
                                                <div style="font-size: 0.875rem; color: #64748b;">1-3 Days</div>
                                            </div>
                                        </div>
                                        <div class="col-6">
                                            <div style="background: #fef3c7; border-radius: 8px; padding: 1rem;">
                                                <div style="font-size: 1.5rem; font-weight: 700; color: #d97706;"><?php echo $aging_summary['overdue_1_24_hours'] ?? 0; ?></div>
                                                <div style="font-size: 0.875rem; color: #64748b;">1-24 Hours</div>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    
    <!-- Required Scripts -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <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/buttons/2.4.1/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.bootstrap5.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.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();

    // Initialize DataTable
    var agingTable = $('#aging-table').DataTable({
        dom: 'Bfrtip',
        buttons: [
            {
                extend: 'excel',
                text: '<i class="ph-microsoft-excel-logo"></i> Excel',
                className: 'btn btn-success btn-sm',
                exportOptions: {
                    columns: [0, 1, 2, 3, 4, 5, 6, 7, 8]
                }
            },
            {
                extend: 'pdf',
                text: '<i class="ph-file-pdf"></i> PDF',
                className: 'btn btn-danger btn-sm',
                exportOptions: {
                    columns: [0, 1, 2, 3, 4, 5, 6, 7, 8]
                }
            }
        ],
        pageLength: 25,
        responsive: true,
        order: [[5, 'desc']], // Order by aging column
        columnDefs: [
            { targets: [9], orderable: false }, // Disable ordering on actions column
            { targets: [0, 1], className: 'text-nowrap' },
            { targets: [6], className: 'text-end' }
        ],
        language: {
            search: "Search orders:",
            lengthMenu: "Show _MENU_ orders per page",
            info: "Showing _START_ to _END_ of _TOTAL_ overdue orders",
            infoEmpty: "No overdue orders found",
            infoFiltered: "(filtered from _MAX_ total orders)",
            zeroRecords: "No matching overdue orders found"
        },
        initComplete: function() {
            // Add custom search functionality
            $('#aging-table_filter input').attr('placeholder', 'Search by order number, customer name...');
        }
    });

    // Filter functionality
    window.filterTable = function(status) {
        // Update active filter button
        document.querySelectorAll('.filter-btn').forEach(btn => btn.classList.remove('active'));
        event.target.classList.add('active');
        
        if (status === 'all') {
            agingTable.column(2).search('').draw();
        } else {
            agingTable.column(2).search(status).draw();
        }
    };

    // Aging Distribution Chart
    var agingData = [
        <?php echo $aging_summary['overdue_1_24_hours'] ?? 0; ?>,
        <?php echo $aging_summary['overdue_1_3_days'] ?? 0; ?>,
        <?php echo $aging_summary['overdue_3_7_days'] ?? 0; ?>,
        <?php echo $aging_summary['overdue_over_7_days'] ?? 0; ?>
    ];

    var agingOptions = {
        series: agingData,
        chart: {
            type: 'donut',
            height: 350,
            animations: { enabled: true }
        },
        labels: ['1-24 Hours', '1-3 Days', '3-7 Days', 'Over 7 Days'],
        colors: ['#fbbf24', '#f97316', '#ef4444', '#dc2626'],
        plotOptions: {
            pie: {
                donut: {
                    size: '60%',
                    labels: {
                        show: true,
                        total: {
                            show: true,
                            label: 'Total Overdue',
                            formatter: function(w) {
                                return w.globals.seriesTotals.reduce((a, b) => a + b, 0);
                            }
                        }
                    }
                }
            }
        },
        legend: {
            position: 'bottom',
            horizontalAlign: 'center'
        },
        tooltip: {
            y: {
                formatter: function(value) {
                    return value + " orders";
                }
            }
        },
        responsive: [{
            breakpoint: 480,
            options: {
                chart: {
                    height: 300
                },
                legend: {
                    position: 'bottom'
                }
            }
        }]
    };

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

    // Status Distribution Chart
    var statusData = [
        <?php echo $status_data['READY']['count'] ?? 0; ?>,
        <?php echo $status_data['IN_PROGRESS']['count'] ?? 0; ?>,
        <?php echo $status_data['PENDING']['count'] ?? 0; ?>
    ];

    var statusOptions = {
        series: [{
            name: 'Orders',
            data: statusData
        }],
        chart: {
            type: 'bar',
            height: 350,
            toolbar: { show: false }
        },
        plotOptions: {
            bar: {
                horizontal: false,
                columnWidth: '55%',
                endingShape: 'rounded',
                borderRadius: 4
            }
        },
        colors: ['#10b981', '#f59e0b', '#ef4444'],
        xaxis: {
            categories: ['Ready', 'In Progress', 'Pending']
        },
        yaxis: {
            title: {
                text: 'Number of Orders'
            }
        },
        tooltip: {
            y: {
                formatter: function(value) {
                    return value + " orders";
                }
            }
        },
        grid: {
            borderColor: '#e2e8f0',
            strokeDashArray: 3
        }
    };

    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>';
    }

    // Auto-refresh data every 5 minutes
    setInterval(function() {
        // You can add AJAX call here to refresh data without page reload
        console.log('Auto-refreshing aging data...');
        // location.reload(); // Uncomment to enable auto-refresh
    }, 300000); // 5 minutes

    // Add click handlers for action buttons
    document.querySelectorAll('[title="Contact Customer"]').forEach(button => {
        button.addEventListener('click', function(e) {
            e.preventDefault();
            const customerId = this.href.split('id=')[1].split('&')[0];
            const orderId = this.href.split('order=')[1];
            
            // You can implement a modal or redirect to contact form
            if (confirm('Would you like to call this customer about their overdue order?')) {
                window.location.href = this.href;
            }
        });
    });

    // Highlight critical rows
    document.querySelectorAll('tr[data-aging]').forEach(row => {
        const aging = parseInt(row.dataset.aging);
        if (aging > 168) { // Over 7 days
            row.style.backgroundColor = '#fef2f2';
            row.style.borderLeft = '3px solid #dc2626';
        } else if (aging > 72) { // 3-7 days
            row.style.backgroundColor = '#fffbeb';
            row.style.borderLeft = '3px solid #f59e0b';
        }
    });
});
</script>

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