<?php
require_once 'auth_rbac.php';
require_once '../db_config.php';


// Require admin login
requirePermission('dashboard', 'view');

// Get statistics and data
try {
    $pdo = getDBConnection();
    
    // Get registration statistics
    $stats_query = "
        SELECT 
            COUNT(*) as total_registrations,
            SUM(delegate_count) as total_delegates,
            SUM(total_amount) as total_revenue,
            SUM(CASE WHEN payment_status = 'paid' THEN total_amount ELSE 0 END) as paid_revenue,
            SUM(CASE WHEN payment_status = 'paid' THEN delegate_count ELSE 0 END) as paid_delegates,
            COUNT(CASE WHEN payment_status = 'paid' THEN 1 END) as paid_registrations,
            -- Delegate Pass stats derived from registration_products for paid registrations only
            COALESCE((
                SELECT SUM(CASE WHEN rp.total_price > 0 THEN rp.quantity ELSE 0 END)
                FROM registration_products rp
                JOIN products p ON rp.product_id = p.id
                JOIN product_categories pc ON p.category_id = pc.id
                JOIN registrations r2 ON rp.registration_id = r2.id
                WHERE pc.name = 'Delegate Pass' AND r2.payment_status = 'paid'
                  AND (p.product_code = 'DELEGATE_EARLY_BIRD' OR rp.pricing_tier IN ('earlyBird','Early Bird'))
            ), 0) as paid_early_bird_delegates,
            COALESCE((
                SELECT SUM(CASE WHEN rp.total_price > 0 THEN rp.quantity ELSE 0 END)
                FROM registration_products rp
                JOIN products p ON rp.product_id = p.id
                JOIN product_categories pc ON p.category_id = pc.id
                JOIN registrations r2 ON rp.registration_id = r2.id
                WHERE pc.name = 'Delegate Pass' AND r2.payment_status = 'paid'
                  AND (p.product_code = 'DELEGATE_STANDARD' OR rp.pricing_tier IN ('standard','Standard'))
            ), 0) as paid_standard_delegates,
            COALESCE((
                SELECT SUM(CASE WHEN rp.total_price > 0 THEN rp.quantity ELSE 0 END)
                FROM registration_products rp
                JOIN products p ON rp.product_id = p.id
                JOIN product_categories pc ON p.category_id = pc.id
                JOIN registrations r2 ON rp.registration_id = r2.id
                WHERE pc.name = 'Delegate Pass' AND r2.payment_status = 'paid'
            ), 0) as paid_delegate_passes,
            COALESCE((
                SELECT SUM(rp.total_price)
                FROM registration_products rp
                JOIN products p ON rp.product_id = p.id
                JOIN product_categories pc ON p.category_id = pc.id
                JOIN registrations r2 ON rp.registration_id = r2.id
                WHERE pc.name = 'Delegate Pass' AND r2.payment_status = 'paid'
            ), 0) as delegate_revenue,
            COALESCE((SELECT SUM(rp.quantity) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      WHERE p.product_code = 'TABLETOP_STANDARD'), 0) as standard_tables,
            COALESCE((SELECT SUM(rp.quantity) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      WHERE p.product_code = 'TABLETOP_DOUBLE'), 0) as double_tables,
            COALESCE((SELECT SUM(rp.quantity) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      JOIN registrations r ON rp.registration_id = r.id
                      WHERE p.product_code = 'TABLETOP_STANDARD' AND r.payment_status = 'paid'), 0) as paid_standard_tables,
            COALESCE((SELECT SUM(rp.quantity) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      JOIN registrations r ON rp.registration_id = r.id
                      WHERE p.product_code = 'TABLETOP_DOUBLE' AND r.payment_status = 'paid'), 0) as paid_double_tables,
            COALESCE((SELECT SUM(rp.total_price) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      JOIN registrations r ON rp.registration_id = r.id
                      WHERE p.product_code IN ('TABLETOP_STANDARD', 'TABLETOP_DOUBLE') AND r.payment_status = 'paid'), 0) as table_revenue,
            -- Main Sponsor Packages
            COALESCE((SELECT SUM(rp.quantity) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      WHERE p.product_code IN ('PLATINUM_SPONSOR', 'GOLD_SPONSOR', 'SILVER_SPONSOR', 'BRONZE_SPONSOR')), 0) as total_sponsors,
            COALESCE((SELECT SUM(rp.quantity) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      JOIN registrations r ON rp.registration_id = r.id
                      WHERE p.product_code IN ('PLATINUM_SPONSOR', 'GOLD_SPONSOR', 'SILVER_SPONSOR', 'BRONZE_SPONSOR') AND r.payment_status = 'paid'), 0) as paid_sponsors,
            COALESCE((SELECT SUM(rp.total_price) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      JOIN registrations r ON rp.registration_id = r.id
                      WHERE p.product_code IN ('PLATINUM_SPONSOR', 'GOLD_SPONSOR', 'SILVER_SPONSOR', 'BRONZE_SPONSOR') AND r.payment_status = 'paid'), 0) as sponsor_revenue,
            -- Other Sponsorship Options
            COALESCE((SELECT SUM(rp.quantity) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      WHERE p.product_code IN ('ELEVATOR_PITCH', 'IN_AD_IN_PB', 'CONF_LANYARD', 'CONF_BAG')), 0) as total_other_sponsors,
            COALESCE((SELECT SUM(rp.quantity) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      JOIN registrations r ON rp.registration_id = r.id
                      WHERE p.product_code IN ('ELEVATOR_PITCH', 'IN_AD_IN_PB', 'CONF_LANYARD', 'CONF_BAG') AND r.payment_status = 'paid'), 0) as paid_other_sponsors,
            COALESCE((SELECT SUM(rp.total_price) FROM registration_products rp 
                      JOIN products p ON rp.product_id = p.id 
                      JOIN registrations r ON rp.registration_id = r.id
                      WHERE p.product_code IN ('ELEVATOR_PITCH', 'IN_AD_IN_PB', 'CONF_LANYARD', 'CONF_BAG') AND r.payment_status = 'paid'), 0) as other_sponsor_revenue
        FROM registrations
    ";
    $stats = $pdo->query($stats_query)->fetch(PDO::FETCH_ASSOC);
    
    // Get recent registrations
    $recent_query = "
        SELECT 
            registration_number,
            contact_full_name,
            contact_email,
            university_name,
            delegate_count,
            total_amount,
            payment_status,
            created_at
        FROM registrations 
        ORDER BY created_at DESC 
        LIMIT 10
    ";
    $recent_registrations = $pdo->query($recent_query)->fetchAll(PDO::FETCH_ASSOC);
    
    // Get payment status breakdown
    $payment_query = "
        SELECT 
            payment_status,
            COUNT(*) as count,
            SUM(total_amount) as amount
        FROM registrations 
        GROUP BY payment_status
    ";
    $payment_breakdown = $pdo->query($payment_query)->fetchAll(PDO::FETCH_ASSOC);
    
    // Get time range from URL parameter (default 30 days)
    $time_range = isset($_GET['range']) ? (int)$_GET['range'] : 30;
    $allowed_ranges = [7, 14, 30, 60, 90];
    if (!in_array($time_range, $allowed_ranges)) {
        $time_range = 30;
    }
    
    // Get registration timeline data for selected range
    $timeline_query = "
        SELECT 
            DATE(created_at) as date,
            COUNT(*) as registrations,
            SUM(delegate_count) as delegates,
            SUM(total_amount) as revenue
        FROM registrations 
        WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL {$time_range} DAY)
        GROUP BY DATE(created_at)
        ORDER BY date ASC
    ";
    $timeline_data = $pdo->query($timeline_query)->fetchAll(PDO::FETCH_ASSOC);
    
    // Fill in missing dates with zero values for a complete timeline
    $complete_timeline = [];
    $start_date = new DateTime("-{$time_range} days");
    $end_date = new DateTime();
    $timeline_lookup = [];
    
    // Create lookup array for existing data
    foreach ($timeline_data as $row) {
        $timeline_lookup[$row['date']] = $row;
    }
    
    // Generate complete timeline
    for ($date = clone $start_date; $date <= $end_date; $date->modify('+1 day')) {
        $date_str = $date->format('Y-m-d');
        if (isset($timeline_lookup[$date_str])) {
            $complete_timeline[] = $timeline_lookup[$date_str];
        } else {
            $complete_timeline[] = [
                'date' => $date_str,
                'registrations' => 0,
                'delegates' => 0,
                'revenue' => 0
            ];
        }
    }
    
} catch (Exception $e) {
    $error = "Database error: " . $e->getMessage();
}

$pageTitle = 'Dashboard';
$currentPage = 'dashboard';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo $pageTitle; ?> - XChange Admin</title>
    <?php include '../includes/favicon.php'; ?>
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@400;600;700;800;900&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="../styles.css">
    <link rel="stylesheet" href="admin.css">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <?php include 'includes/admin_nav.php'; ?>

    <div class="admin-content">
        <?php if (isset($error)): ?>
            <div class="alert alert-error mb-4"><?php echo htmlspecialchars($error); ?></div>
        <?php else: ?>
            
        <!-- Statistics Overview -->
        <div class="stats-grid">
            <div class="stat-card">
                <h3>Delegate Pass Sales</h3>
                <div class="stat-layout">
                    <div>
                        <p class="stat-number"><?php echo number_format($stats['paid_delegate_passes'] ?? 0); ?></p>
                        <p class="stat-label">Paid Passes</p>
                    </div>
                    <div class="stat-right-align">
                        <p class="stat-revenue">$<?php echo number_format($stats['delegate_revenue'] ?? 0); ?></p>
                        <p class="stat-label">Pass Revenue</p>
                    </div>
                </div>
                <p class="stat-breakdown">
                    <?php echo number_format($stats['paid_early_bird_delegates'] ?? 0); ?> Early Bird ($500)
                    <?php if (($stats['paid_standard_delegates'] ?? 0) > 0): ?>
                        • <?php echo number_format($stats['paid_standard_delegates'] ?? 0); ?> Standard ($550)
                    <?php endif; ?>
                </p>
            </div>
            
            <div class="stat-card">
                <h3>Table-top Presentations</h3>
                <div class="stat-layout">
                    <div>
                        <p class="stat-number"><?php echo number_format(($stats['paid_standard_tables'] ?? 0) + ($stats['paid_double_tables'] ?? 0)); ?></p>
                        <p class="stat-label">Paid Tables</p>
                    </div>
                    <div class="stat-right-align">
                        <p class="stat-revenue">$<?php echo number_format($stats['table_revenue'] ?? 0); ?></p>
                        <p class="stat-label">Table Revenue</p>
                    </div>
                </div>
                <p class="stat-breakdown">
                    <?php echo number_format($stats['paid_standard_tables'] ?? 0); ?> Standard ($550)
                    <?php if (($stats['paid_double_tables'] ?? 0) > 0): ?>
                        • <?php echo number_format($stats['paid_double_tables'] ?? 0); ?> Double ($850)
                    <?php endif; ?>
                </p>
            </div>
            
            <div class="stat-card">
                <h3>Sponsors</h3>
                <div class="stat-layout">
                    <div>
                        <p class="stat-number"><?php echo number_format($stats['paid_sponsors'] ?? 0); ?></p>
                        <p class="stat-label">Paid Sponsors</p>
                    </div>
                    <div class="stat-right-align">
                        <p class="stat-revenue">$<?php echo number_format($stats['sponsor_revenue'] ?? 0); ?></p>
                        <p class="stat-label">Sponsor Revenue</p>
                    </div>
                </div>
                <p class="stat-breakdown">
                    Platinum • Gold • Silver • Bronze
                </p>
            </div>
            
            <div class="stat-card">
                <h3>Other Sponsorships</h3>
                <div class="stat-layout">
                    <div>
                        <p class="stat-number"><?php echo number_format($stats['paid_other_sponsors'] ?? 0); ?></p>
                        <p class="stat-label">Paid Items</p>
                    </div>
                    <div class="stat-right-align">
                        <p class="stat-revenue">$<?php echo number_format($stats['other_sponsor_revenue'] ?? 0); ?></p>
                        <p class="stat-label">Other Revenue</p>
                    </div>
                </div>
                <p class="stat-breakdown">
                    Elevator Pitch • Program Book Ad • Lanyard • Conference Bag
                </p>
            </div>
        </div>

        <!-- Registration Timeline Chart -->
        <div class="data-table mb-6">
            <div class="chart-header">
                <h2>Registration Timeline (Last <?php echo $time_range; ?> Days)</h2>
                <div class="time-range-selector">
                    <a href="?range=7" class="range-btn <?php echo $time_range == 7 ? 'active' : ''; ?>">7D</a>
                    <a href="?range=14" class="range-btn <?php echo $time_range == 14 ? 'active' : ''; ?>">14D</a>
                    <a href="?range=30" class="range-btn <?php echo $time_range == 30 ? 'active' : ''; ?>">30D</a>
                    <a href="?range=60" class="range-btn <?php echo $time_range == 60 ? 'active' : ''; ?>">60D</a>
                    <a href="?range=90" class="range-btn <?php echo $time_range == 90 ? 'active' : ''; ?>">90D</a>
                </div>
            </div>
            <div class="chart-container">
                <canvas id="registrationChart" width="400" height="150"></canvas>
            </div>
        </div>

        <!-- Payment Status Breakdown -->
        <div class="data-table mb-6">
            <h2>Payment Status Overview</h2>
            <div class="table-container">
                <table class="table">
                    <thead>
                        <tr>
                            <th>Status</th>
                            <th>Count</th>
                            <th>Amount</th>
                            <th>Percentage</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($payment_breakdown as $status): ?>
                        <tr>
                            <td>
                                <span class="status-badge status-<?php echo $status['payment_status']; ?>">
                                    <?php echo ucfirst($status['payment_status']); ?>
                                </span>
                            </td>
                            <td><?php echo number_format($status['count']); ?></td>
                            <td>$<?php echo number_format($status['amount']); ?></td>
                            <td><?php echo $stats['total_registrations'] > 0 ? round(($status['count'] / $stats['total_registrations']) * 100, 1) : 0; ?>%</td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>


        <!-- Recent Registrations -->
        <div class="data-table">
            <h2>Recent Registrations</h2>
            <div class="table-container">
                <table class="table">
                    <thead>
                        <tr>
                            <th>Registration #</th>
                            <th>Contact Name</th>
                            <th>University</th>
                            <th>Delegates</th>
                            <th>Amount</th>
                            <th>Status</th>
                            <th>Date</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($recent_registrations as $reg): ?>
                        <tr>
                            <td>
                                <a href="registration-details.php?id=<?php echo urlencode($reg['registration_number']); ?>" 
                                   class="link">
                                    <?php echo htmlspecialchars($reg['registration_number']); ?>
                                </a>
                            </td>
                            <td><?php echo htmlspecialchars($reg['contact_full_name']); ?></td>
                            <td><?php echo htmlspecialchars($reg['university_name']); ?></td>
                            <td><?php echo number_format($reg['delegate_count']); ?></td>
                            <td>$<?php echo number_format($reg['total_amount']); ?></td>
                            <td>
                                <span class="status-badge status-<?php echo $reg['payment_status']; ?>">
                                    <?php echo ucfirst($reg['payment_status']); ?>
                                </span>
                            </td>
                            <td><?php echo date('M j, Y', strtotime($reg['created_at'])); ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
        
        <?php endif; ?>
    </div>

    <script>
    // Registration Timeline Chart
    const ctx = document.getElementById('registrationChart').getContext('2d');
    const chartData = <?php echo json_encode($complete_timeline); ?>;
    
    const labels = chartData.map(item => {
        const date = new Date(item.date);
        return date.toLocaleDateString('en-US', { month: 'short', day: 'numeric' });
    });
    
    const registrationData = chartData.map(item => parseInt(item.registrations));
    const delegateData = chartData.map(item => parseInt(item.delegates));
    const revenueData = chartData.map(item => parseFloat(item.revenue));
    
    new Chart(ctx, {
        type: 'line',
        data: {
            labels: labels,
            datasets: [{
                label: 'Registrations',
                data: registrationData,
                borderColor: '#001b42',
                backgroundColor: 'rgba(0, 27, 66, 0.1)',
                borderWidth: 3,
                fill: true,
                tension: 0.4,
                pointBackgroundColor: '#001b42',
                pointBorderColor: '#ffffff',
                pointBorderWidth: 2,
                pointRadius: 5
            }, {
                label: 'Delegates',
                data: delegateData,
                borderColor: '#2563eb',
                backgroundColor: 'rgba(37, 99, 235, 0.1)',
                borderWidth: 3,
                fill: false,
                tension: 0.4,
                pointBackgroundColor: '#2563eb',
                pointBorderColor: '#ffffff',
                pointBorderWidth: 2,
                pointRadius: 5
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: {
                    position: 'top',
                    labels: {
                        usePointStyle: true,
                        padding: 20,
                        font: {
                            size: 14,
                            weight: '600'
                        }
                    }
                },
                tooltip: {
                    mode: 'index',
                    intersect: false,
                    backgroundColor: 'rgba(255, 255, 255, 0.95)',
                    titleColor: '#1f2937',
                    bodyColor: '#374151',
                    borderColor: '#e5e7eb',
                    borderWidth: 1,
                    cornerRadius: 8,
                    displayColors: true,
                    callbacks: {
                        afterBody: function(context) {
                            const dataIndex = context[0].dataIndex;
                            const revenue = revenueData[dataIndex];
                            return revenue > 0 ? `Revenue: $${revenue.toLocaleString()}` : '';
                        }
                    }
                }
            },
            scales: {
                x: {
                    grid: {
                        display: false
                    },
                    ticks: {
                        font: {
                            size: 12
                        },
                        color: '#6b7280'
                    }
                },
                y: {
                    beginAtZero: true,
                    grid: {
                        color: 'rgba(0, 0, 0, 0.05)'
                    },
                    ticks: {
                        font: {
                            size: 12
                        },
                        color: '#6b7280',
                        callback: function(value) {
                            return Number.isInteger(value) ? value : '';
                        }
                    }
                }
            },
            interaction: {
                mode: 'nearest',
                axis: 'x',
                intersect: false
            },
            elements: {
                point: {
                    hoverRadius: 8
                }
            }
        }
    });
    </script>

<?php include 'includes/footer.php'; ?>
