<?php
/**
 * Product Analytics Dashboard
 * XChange 2025 Registration System
 */

require_once 'auth_rbac.php';
require_once '../db_config.php';
require_once 'product_helper.php';

// Get database connection
$conn = getDBConnection();

// Check authentication and permissions
$auth = new AdminAuth($conn);
$auth->requirePermission('registrations');

$productManager = new ProductManager($conn);

// Get analytics data
$summary = $productManager->getProductSummary();
$productSales = $productManager->getProductSales();
$categories = $productManager->getProductCategories();

// Get category performance
$categoryPerformance = [];
$sql = "SELECT 
            pc.name as category_name,
            COUNT(DISTINCT rp.id) as total_orders,
            SUM(rp.quantity) as total_quantity,
            SUM(rp.total_price) as total_revenue,
            COUNT(DISTINCT rp.registration_id) as unique_customers,
            AVG(rp.total_price) as avg_order_value
        FROM product_categories pc
        LEFT JOIN products p ON pc.id = p.category_id
        LEFT JOIN registration_products rp ON p.id = rp.product_id
        WHERE pc.is_active = 1
        GROUP BY pc.id, pc.name
        ORDER BY total_revenue DESC";
$stmt = $conn->prepare($sql);
$stmt->execute();
$categoryPerformance = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get monthly sales trend
$monthlySales = [];
$sql = "SELECT 
            DATE_FORMAT(rp.created_at, '%Y-%m') as month,
            COUNT(rp.id) as orders,
            SUM(rp.quantity) as quantity,
            SUM(rp.total_price) as revenue
        FROM registration_products rp
        WHERE rp.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
        GROUP BY DATE_FORMAT(rp.created_at, '%Y-%m')
        ORDER BY month ASC";
$stmt = $conn->prepare($sql);
$stmt->execute();
$monthlySales = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get top performing products
$topProducts = [];
$sql = "SELECT 
            p.name,
            p.product_code,
            COUNT(rp.id) as total_orders,
            SUM(rp.quantity) as total_quantity,
            SUM(rp.total_price) as total_revenue,
            AVG(rp.total_price) as avg_order_value,
            p.base_price,
            CASE 
                WHEN p.total_stock > 0 THEN p.available_stock
                ELSE NULL
            END as available_stock,
            p.total_stock
        FROM products p
        LEFT JOIN registration_products rp ON p.id = rp.product_id
        WHERE p.is_active = 1
        GROUP BY p.id, p.name, p.product_code, p.base_price, p.available_stock, p.total_stock
        ORDER BY total_revenue DESC
        LIMIT 10";
$stmt = $conn->prepare($sql);
$stmt->execute();
$topProducts = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Product Analytics - XChange 2025 Admin</title>
    <?php include '../includes/favicon.php'; ?>
    <link rel="stylesheet" href="../styles.css">
    <link rel="stylesheet" href="admin.css">
    <style>
        .analytics-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
            gap: 24px;
            margin-bottom: 32px;
        }
        
        .analytics-card {
            background: white;
            border-radius: 12px;
            padding: 24px;
            box-shadow: 0 2px 8px rgba(0,0,0,0.1);
            border: 1px solid #e5e7eb;
        }
        
        .analytics-card h3 {
            margin: 0 0 16px 0;
            color: #1f2937;
            font-size: 1.1rem;
            font-weight: 600;
        }
        
        .analytics-card .section-header {
            margin-bottom: 16px;
        }
        
        .metric-row {
            display: flex;
            justify-content: space-between;
            align-items: center;
            padding: 12px 0;
            border-bottom: 1px solid #f3f4f6;
        }
        
        .metric-row:last-child {
            border-bottom: none;
        }
        
        .metric-label {
            color: #6b7280;
            font-size: 14px;
        }
        
        .metric-value {
            font-weight: 600;
            color: #1f2937;
        }
        
        .revenue-value {
            color: #059669;
        }
        
        .orders-value {
            color: #3b82f6;
        }
        
        .quantity-value {
            color: #8b5cf6;
        }
        
        .product-grid {
            display: grid;
            grid-template-columns: repeat(4, 1fr);
            gap: 20px;
            margin-top: 24px;
        }
        
        @media (max-width: 1400px) {
            .product-grid {
                grid-template-columns: repeat(3, 1fr);
            }
        }
        
        @media (max-width: 1024px) {
            .product-grid {
                grid-template-columns: repeat(2, 1fr);
            }
        }
        
        @media (max-width: 768px) {
            .product-grid {
                grid-template-columns: 1fr;
            }
        }
        
        .product-analytics-card {
            background: white;
            border-radius: 8px;
            padding: 20px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            border: 1px solid #e5e7eb;
            transition: transform 0.2s, box-shadow 0.2s;
        }
        
        .product-analytics-card:hover {
            transform: translateY(-2px);
            box-shadow: 0 4px 12px rgba(0,0,0,0.15);
        }
        
        .product-header {
            display: flex;
            justify-content: between;
            align-items: flex-start;
            margin-bottom: 16px;
        }
        
        .product-name {
            font-weight: 600;
            color: #1f2937;
            margin: 0 0 4px 0;
            font-size: 16px;
        }
        
        .product-code {
            color: #6b7280;
            font-size: 12px;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }
        
        .product-metrics {
            display: grid;
            grid-template-columns: 1fr 1fr;
            gap: 12px;
        }
        
        .metric-item {
            text-align: center;
        }
        
        .metric-number {
            display: block;
            font-size: 20px;
            font-weight: 700;
            color: #1f2937;
            margin-bottom: 4px;
        }
        
        .metric-text {
            font-size: 12px;
            color: #6b7280;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }
        
        .stock-status {
            margin-top: 12px;
            padding: 6px 12px;
            border-radius: 6px;
            font-size: 12px;
            font-weight: 500;
            text-align: center;
        }
        
        .stock-high {
            background: #d1fae5;
            color: #065f46;
        }
        
        .stock-low {
            background: #fef3c7;
            color: #92400e;
        }
        
        .stock-out {
            background: #fee2e2;
            color: #991b1b;
        }
        
        .stock-unlimited {
            background: #e0e7ff;
            color: #3730a3;
        }
        
        .category-performance {
            margin-top: 32px;
        }
        
        .performance-table {
            width: 100%;
            background: white;
            border-radius: 8px;
            overflow: hidden;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }
        
        .performance-table th,
        .performance-table td {
            padding: 12px 16px;
            text-align: left;
            border-bottom: 1px solid #f3f4f6;
        }
        
        .performance-table th {
            background: #f9fafb;
            font-weight: 600;
            color: #374151;
            font-size: 14px;
        }
        
        .performance-table td {
            color: #6b7280;
        }
        
        .performance-table tr:hover {
            background: #f9fafb;
        }
        
        .chart-placeholder {
            height: 200px;
            background: #f9fafb;
            border: 2px dashed #d1d5db;
            border-radius: 8px;
            display: flex;
            align-items: center;
            justify-content: center;
            color: #6b7280;
            font-style: italic;
        }
        
        /* Help Tooltip Styles */
        .section-header {
            display: flex;
            align-items: center;
            gap: 8px;
            margin-bottom: 16px;
        }
        
        .section-header h2 {
            margin: 0;
            line-height: 1.2;
        }
        
        .section-header h3 {
            margin: 0;
            line-height: 1.2;
            font-size: 1.1rem;
        }
        
        .help-icon {
            display: inline-flex;
            align-items: center;
            justify-content: center;
            width: 16px;
            height: 16px;
            background: #9ca3af;
            color: white;
            border-radius: 50%;
            font-size: 10px;
            font-weight: bold;
            cursor: help;
            position: relative;
            transition: all 0.2s ease;
            flex-shrink: 0;
            align-self: center;
            opacity: 0.7;
        }
        
        .help-icon:hover {
            background: #6b7280;
            transform: scale(1.1);
            opacity: 1;
        }
        
        .tooltip {
            position: relative;
            display: inline-block;
        }
        
        .tooltip .tooltiptext {
            visibility: hidden;
            width: 320px;
            background-color: #1f2937;
            color: white;
            text-align: left;
            border-radius: 8px;
            padding: 16px;
            position: absolute;
            z-index: 1000;
            bottom: 125%;
            left: 50%;
            margin-left: -160px;
            opacity: 0;
            transition: opacity 0.3s;
            box-shadow: 0 4px 12px rgba(0,0,0,0.15);
            font-size: 13px;
            line-height: 1.4;
        }
        
        .analytics-grid .tooltip .tooltiptext {
            bottom: auto;
            top: 125%;
        }
        
        .section-header .tooltip .tooltiptext {
            left: auto;
            right: 0;
            margin-left: 0;
        }
        
        .tooltip .tooltiptext::after {
            content: "";
            position: absolute;
            top: 100%;
            left: 50%;
            margin-left: -5px;
            border-width: 5px;
            border-style: solid;
            border-color: #1f2937 transparent transparent transparent;
        }
        
        .tooltip:hover .tooltiptext {
            visibility: visible;
            opacity: 1;
        }
        
        .metric-help {
            display: inline-flex;
            align-items: center;
            gap: 4px;
        }
        
        .metric-help-icon {
            width: 12px;
            height: 12px;
            background: #9ca3af;
            color: white;
            border-radius: 50%;
            font-size: 8px;
            font-weight: bold;
            cursor: help;
            display: inline-flex;
            align-items: center;
            justify-content: center;
            margin-left: 4px;
            transition: all 0.2s ease;
            opacity: 0.6;
        }
        
        .metric-help-icon:hover {
            background: #6b7280;
            transform: scale(1.1);
            opacity: 1;
        }
        
        .tooltip-content {
            margin-bottom: 8px;
        }
        
        .tooltip-content:last-child {
            margin-bottom: 0;
        }
        
        .tooltip-title {
            font-weight: 600;
            color: #60a5fa;
            margin-bottom: 4px;
        }
    </style>
</head>
<body>
    <?php include 'includes/admin_nav.php'; ?>
    
    <div class="admin-content">
        <div class="page-header">
            <h1>📊 Product Analytics Dashboard</h1>
            <p>Comprehensive insights into product performance and sales trends</p>
        </div>

        <!-- Overall Summary -->
        <div class="analytics-grid">
            <div class="analytics-card">
                <div class="section-header">
                    <h3>📈 Sales Overview</h3>
                    <div class="tooltip">
                        <div class="help-icon">?</div>
                        <span class="tooltiptext">
                            <div class="tooltip-content">
                                <div class="tooltip-title">📊 Total Orders</div>
                                Number of individual purchase transactions across all products
                            </div>
                            <div class="tooltip-content">
                                <div class="tooltip-title">💰 Total Revenue</div>
                                Sum of all confirmed sales revenue from product purchases
                            </div>
                            <div class="tooltip-content">
                                <div class="tooltip-title">👥 Unique Customers</div>
                                Number of different customers who made purchases
                            </div>
                            <div class="tooltip-content">
                                <div class="tooltip-title">📈 Avg Order Value</div>
                                Average amount spent per order (Total Revenue ÷ Total Orders)
                            </div>
                        </span>
                    </div>
                </div>
                <div class="metric-row">
                    <span class="metric-label">Total Orders</span>
                    <span class="metric-value orders-value"><?php echo number_format($summary['total_purchases']); ?></span>
                </div>
                <div class="metric-row">
                    <span class="metric-label">Total Revenue</span>
                    <span class="metric-value revenue-value">$<?php echo number_format($summary['total_revenue'], 2); ?></span>
                </div>
                <div class="metric-row">
                    <span class="metric-label">Unique Customers</span>
                    <span class="metric-value"><?php echo number_format($summary['unique_registrations']); ?></span>
                </div>
                <div class="metric-row">
                    <span class="metric-label">Avg Order Value</span>
                    <span class="metric-value">$<?php echo $summary['total_purchases'] > 0 ? number_format($summary['total_revenue'] / $summary['total_purchases'], 2) : '0.00'; ?></span>
                </div>
            </div>
            
            <div class="analytics-card">
                <div class="section-header">
                    <h3>📦 Order Status</h3>
                    <div class="tooltip">
                        <div class="help-icon">?</div>
                        <span class="tooltiptext">
                            <div class="tooltip-content">
                                <div class="tooltip-title">✅ Confirmed Orders</div>
                                Orders that have been successfully processed and confirmed
                            </div>
                            <div class="tooltip-content">
                                <div class="tooltip-title">⏳ Pending Orders</div>
                                Orders awaiting payment confirmation or processing
                            </div>
                            <div class="tooltip-content">
                                <div class="tooltip-title">❌ Cancelled Orders</div>
                                Orders that were cancelled or failed to complete
                            </div>
                            <div class="tooltip-content">
                                <div class="tooltip-title">📊 Confirmation Rate</div>
                                Percentage of orders successfully confirmed (Confirmed ÷ Total Orders)
                            </div>
                        </span>
                    </div>
                </div>
                <div class="metric-row">
                    <span class="metric-label">Confirmed Orders</span>
                    <span class="metric-value orders-value"><?php echo number_format($summary['confirmed_purchases']); ?></span>
                </div>
                <div class="metric-row">
                    <span class="metric-label">Pending Orders</span>
                    <span class="metric-value quantity-value"><?php echo number_format($summary['pending_purchases']); ?></span>
                </div>
                <div class="metric-row">
                    <span class="metric-label">Cancelled Orders</span>
                    <span class="metric-value"><?php echo number_format($summary['cancelled_purchases']); ?></span>
                </div>
                <div class="metric-row">
                    <span class="metric-label">Confirmation Rate</span>
                    <span class="metric-value"><?php echo $summary['total_purchases'] > 0 ? number_format(($summary['confirmed_purchases'] / $summary['total_purchases']) * 100, 1) : '0'; ?>%</span>
                </div>
            </div>
            
            <div class="analytics-card">
                <div class="section-header">
                    <h3>📊 Sales Trend</h3>
                    <div class="tooltip">
                        <div class="help-icon">?</div>
                        <span class="tooltiptext">
                            <strong>Monthly Sales Visualization:</strong> This section will display a chart showing sales trends over time. Chart.js integration is recommended for interactive graphs showing revenue patterns, seasonal trends, and growth metrics.
                        </span>
                    </div>
                </div>
                <div class="chart-placeholder">
                    Monthly sales chart would go here
                    <br><small>(Chart.js integration recommended)</small>
                </div>
            </div>
        </div>

        <!-- Top Performing Products -->
        <div class="section">
            <div class="section-header">
                <h2>🏆 Top Performing Products</h2>
                <div class="tooltip">
                    <div class="help-icon">?</div>
                    <span class="tooltiptext">
                        <div class="tooltip-content">
                            <div class="tooltip-title">📊 Orders</div>
                            Total number of individual purchase transactions for this product
                        </div>
                        <div class="tooltip-content">
                            <div class="tooltip-title">📦 Quantity</div>
                            Total units sold across all orders (some orders may contain multiple units)
                        </div>
                        <div class="tooltip-content">
                            <div class="tooltip-title">💰 Revenue</div>
                            Total income generated from all sales of this product
                        </div>
                        <div class="tooltip-content">
                            <div class="tooltip-title">📈 Avg Value</div>
                            Average revenue per order (Revenue ÷ Orders)
                        </div>
                        <div class="tooltip-content">
                            <div class="tooltip-title">📋 Stock Status</div>
                            Current inventory level: High Stock (>20%), Low Stock (≤20%), Out of Stock (0), or Unlimited
                        </div>
                    </span>
                </div>
            </div>
            <div class="product-grid">
                <?php foreach ($topProducts as $product): ?>
                    <div class="product-analytics-card">
                        <div class="product-header">
                            <div>
                                <h3 class="product-name"><?php echo htmlspecialchars($product['name']); ?></h3>
                                <div class="product-code"><?php echo htmlspecialchars($product['product_code']); ?></div>
                            </div>
                        </div>
                        
                        <div class="product-metrics">
                            <div class="metric-item">
                                <span class="metric-number orders-value"><?php echo $product['total_orders'] ?: 0; ?></span>
                                <div class="metric-help">
                                    <span class="metric-text">Orders</span>
                                    <div class="tooltip">
                                        <div class="metric-help-icon">?</div>
                                        <span class="tooltiptext">
                                            <strong>Total Orders:</strong> Number of individual purchase transactions. Each time someone buys this product, it counts as one order regardless of quantity.
                                        </span>
                                    </div>
                                </div>
                            </div>
                            <div class="metric-item">
                                <span class="metric-number quantity-value"><?php echo $product['total_quantity'] ?: 0; ?></span>
                                <div class="metric-help">
                                    <span class="metric-text">Quantity</span>
                                    <div class="tooltip">
                                        <div class="metric-help-icon">?</div>
                                        <span class="tooltiptext">
                                            <strong>Total Quantity:</strong> Total units sold. If someone orders 3 units in one transaction, this adds 3 to the quantity count.
                                        </span>
                                    </div>
                                </div>
                            </div>
                            <div class="metric-item">
                                <span class="metric-number revenue-value">$<?php echo number_format($product['total_revenue'] ?: 0, 0); ?></span>
                                <div class="metric-help">
                                    <span class="metric-text">Revenue</span>
                                    <div class="tooltip">
                                        <div class="metric-help-icon">?</div>
                                        <span class="tooltiptext">
                                            <strong>Total Revenue:</strong> Total income generated from all sales of this product. Calculated as quantity × unit price for each order.
                                        </span>
                                    </div>
                                </div>
                            </div>
                            <div class="metric-item">
                                <span class="metric-number">$<?php echo number_format($product['avg_order_value'] ?: 0, 0); ?></span>
                                <div class="metric-help">
                                    <span class="metric-text">Avg Value</span>
                                    <div class="tooltip">
                                        <div class="metric-help-icon">?</div>
                                        <span class="tooltiptext">
                                            <strong>Average Order Value:</strong> Average revenue per order (Total Revenue ÷ Total Orders). Shows how much customers typically spend per transaction.
                                        </span>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <?php if ($product['total_stock']): ?>
                            <?php 
                            $stockLevel = 'high';
                            if ($product['available_stock'] == 0) $stockLevel = 'out';
                            elseif ($product['available_stock'] <= ($product['total_stock'] * 0.2)) $stockLevel = 'low';
                            ?>
                            <div class="stock-status stock-<?php echo $stockLevel; ?>">
                                <?php if ($stockLevel == 'out'): ?>
                                    ⚠️ Out of Stock
                                <?php elseif ($stockLevel == 'low'): ?>
                                    ⚠️ Low Stock (<?php echo $product['available_stock']; ?> left)
                                <?php else: ?>
                                    ✅ In Stock (<?php echo $product['available_stock']; ?> available)
                                <?php endif; ?>
                            </div>
                        <?php else: ?>
                            <div class="stock-status stock-unlimited">
                                ∞ Unlimited Stock
                            </div>
                        <?php endif; ?>
                    </div>
                <?php endforeach; ?>
            </div>
        </div>

        <!-- Category Performance -->
        <div class="category-performance">
            <div class="section-header">
                <h2>📂 Category Performance</h2>
                <div class="tooltip">
                    <div class="help-icon">?</div>
                    <span class="tooltiptext">
                        <div class="tooltip-content">
                            <div class="tooltip-title">📊 Orders</div>
                            Total number of orders for products in this category
                        </div>
                        <div class="tooltip-content">
                            <div class="tooltip-title">📦 Quantity Sold</div>
                            Total units sold across all products in this category
                        </div>
                        <div class="tooltip-content">
                            <div class="tooltip-title">💰 Revenue</div>
                            Total income generated from this product category
                        </div>
                        <div class="tooltip-content">
                            <div class="tooltip-title">👥 Customers</div>
                            Number of unique customers who purchased from this category
                        </div>
                        <div class="tooltip-content">
                            <div class="tooltip-title">📈 Avg Order Value</div>
                            Average revenue per order for this category
                        </div>
                    </span>
                </div>
            </div>
            <table class="performance-table">
                <thead>
                    <tr>
                        <th>Category</th>
                        <th>Orders</th>
                        <th>Quantity Sold</th>
                        <th>Revenue</th>
                        <th>Customers</th>
                        <th>Avg Order Value</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($categoryPerformance as $category): ?>
                        <tr>
                            <td><strong><?php echo htmlspecialchars($category['category_name']); ?></strong></td>
                            <td><?php echo number_format($category['total_orders'] ?: 0); ?></td>
                            <td><?php echo number_format($category['total_quantity'] ?: 0); ?></td>
                            <td class="revenue-value">$<?php echo number_format($category['total_revenue'] ?: 0, 2); ?></td>
                            <td><?php echo number_format($category['unique_customers'] ?: 0); ?></td>
                            <td>$<?php echo number_format($category['avg_order_value'] ?: 0, 2); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</body>
</html>
