<?php

require_once __DIR__ . '/../app/config/config.php';
require_once __DIR__ . '/../app/config/database.php';
require_once __DIR__ . '/../app/helpers/functions.php';
require_once __DIR__ . '/../app/helpers/auth.php';
require_once __DIR__ . '/../app/helpers/settings.php';

require_admin();

$pageTitle = 'Reports';

$totalSales = $pdo->query("
    SELECT COALESCE(SUM(total_amount), 0)
    FROM orders
    WHERE payment_status = 'paid'
")->fetchColumn();

$totalOrders = $pdo->query("
    SELECT COUNT(*)
    FROM orders
")->fetchColumn();

$paidOrders = $pdo->query("
    SELECT COUNT(*)
    FROM orders
    WHERE payment_status = 'paid'
")->fetchColumn();

$pendingOrders = $pdo->query("
    SELECT COUNT(*)
    FROM orders
    WHERE order_status = 'pending'
")->fetchColumn();

$totalCustomers = $pdo->query("
    SELECT COUNT(*)
    FROM customers
")->fetchColumn();

$totalProducts = $pdo->query("
    SELECT COUNT(*)
    FROM products
")->fetchColumn();

$monthlyRevenue = $pdo->query("
    SELECT 
        DATE_FORMAT(created_at, '%Y-%m') AS month_label,
        COALESCE(SUM(total_amount), 0) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE payment_status = 'paid'
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
    ORDER BY month_label DESC
    LIMIT 12
")->fetchAll();

$recentOrders = $pdo->query("
    SELECT *
    FROM orders
    ORDER BY id DESC
    LIMIT 10
")->fetchAll();

$topProducts = $pdo->query("
    SELECT 
        oi.product_id,
        oi.product_name,
        SUM(oi.quantity) AS total_quantity,
        SUM(oi.total_price) AS total_revenue
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.id
    WHERE o.payment_status = 'paid'
    GROUP BY oi.product_id, oi.product_name
    ORDER BY total_quantity DESC
    LIMIT 10
")->fetchAll();

$couponUsage = $pdo->query("
    SELECT 
        coupon_code,
        coupon_type,
        coupon_value,
        used_count,
        status
    FROM coupons
    ORDER BY used_count DESC, id DESC
    LIMIT 10
")->fetchAll();

$lowStockProducts = $pdo->query("
    SELECT 
        product_name,
        sku,
        stock_quantity,
        stock_status
    FROM products
    WHERE stock_quantity <= 5
      AND status = 'active'
    ORDER BY stock_quantity ASC
    LIMIT 10
")->fetchAll();

$orderStatusBreakdown = $pdo->query("
    SELECT 
        order_status,
        COUNT(*) AS total
    FROM orders
    GROUP BY order_status
    ORDER BY total DESC
")->fetchAll();

$paymentStatusBreakdown = $pdo->query("
    SELECT 
        payment_status,
        COUNT(*) AS total
    FROM orders
    GROUP BY payment_status
    ORDER BY total DESC
")->fetchAll();

require_once __DIR__ . '/includes/header.php';
require_once __DIR__ . '/includes/sidebar.php';
?>

<main class="admin-main">
    <div class="admin-topbar">
        <div>
            <h1>Reports</h1>
            <div class="admin-user">View sales, orders, customers, stock, coupons, and product performance.</div>
        </div>
    </div>

    <section class="admin-cards">
        <div class="admin-card">
            <span>Total Sales</span>
            <strong><?= e(format_price($totalSales)); ?></strong>
        </div>

        <div class="admin-card">
            <span>Total Orders</span>
            <strong><?= e($totalOrders); ?></strong>
        </div>

        <div class="admin-card">
            <span>Paid Orders</span>
            <strong><?= e($paidOrders); ?></strong>
        </div>

        <div class="admin-card">
            <span>Pending Orders</span>
            <strong><?= e($pendingOrders); ?></strong>
        </div>

        <div class="admin-card">
            <span>Customers</span>
            <strong><?= e($totalCustomers); ?></strong>
        </div>

        <div class="admin-card">
            <span>Products</span>
            <strong><?= e($totalProducts); ?></strong>
        </div>
    </section>

    <div style="display:grid; grid-template-columns: 1fr 1fr; gap:24px; align-items:start;">
        <section class="admin-panel" style="margin-bottom:24px;">
            <h2>Monthly Revenue</h2>

            <table class="admin-table">
                <thead>
                    <tr>
                        <th>Month</th>
                        <th>Orders</th>
                        <th>Revenue</th>
                    </tr>
                </thead>

                <tbody>
                    <?php if (!$monthlyRevenue): ?>
                        <tr>
                            <td colspan="3">No paid sales yet.</td>
                        </tr>
                    <?php endif; ?>

                    <?php foreach ($monthlyRevenue as $row): ?>
                        <tr>
                            <td><?= e($row['month_label']); ?></td>
                            <td><?= e($row['order_count']); ?></td>
                            <td><?= e(format_price($row['revenue'])); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </section>

        <section class="admin-panel" style="margin-bottom:24px;">
            <h2>Order Status Breakdown</h2>

            <table class="admin-table">
                <thead>
                    <tr>
                        <th>Status</th>
                        <th>Total</th>
                    </tr>
                </thead>

                <tbody>
                    <?php if (!$orderStatusBreakdown): ?>
                        <tr>
                            <td colspan="2">No orders yet.</td>
                        </tr>
                    <?php endif; ?>

                    <?php foreach ($orderStatusBreakdown as $row): ?>
                        <tr>
                            <td><?= e(ucfirst($row['order_status'])); ?></td>
                            <td><?= e($row['total']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>

            <h2 style="margin-top:28px;">Payment Status Breakdown</h2>

            <table class="admin-table">
                <thead>
                    <tr>
                        <th>Status</th>
                        <th>Total</th>
                    </tr>
                </thead>

                <tbody>
                    <?php if (!$paymentStatusBreakdown): ?>
                        <tr>
                            <td colspan="2">No payments yet.</td>
                        </tr>
                    <?php endif; ?>

                    <?php foreach ($paymentStatusBreakdown as $row): ?>
                        <tr>
                            <td><?= e(ucfirst($row['payment_status'])); ?></td>
                            <td><?= e($row['total']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </section>
    </div>

    <section class="admin-panel" style="margin-bottom:24px;">
        <h2>Recent Orders</h2>

        <table class="admin-table">
            <thead>
                <tr>
                    <th>Order</th>
                    <th>Customer</th>
                    <th>Total</th>
                    <th>Payment</th>
                    <th>Status</th>
                    <th>Shipping</th>
                    <th>Date</th>
                    <th>Action</th>
                </tr>
            </thead>

            <tbody>
                <?php if (!$recentOrders): ?>
                    <tr>
                        <td colspan="8">No orders yet.</td>
                    </tr>
                <?php endif; ?>

                <?php foreach ($recentOrders as $order): ?>
                    <tr>
                        <td><strong><?= e($order['order_number']); ?></strong></td>
                        <td>
                            <?= e($order['customer_email']); ?><br>
                            <small><?= e($order['customer_phone'] ?? '-'); ?></small>
                        </td>
                        <td><?= e(format_price($order['total_amount'], $order['currency_code'])); ?></td>
                        <td><?= e(ucfirst($order['payment_status'])); ?></td>
                        <td><?= e(ucfirst($order['order_status'])); ?></td>
                        <td><?= e(ucwords(str_replace('_', ' ', $order['shipping_status']))); ?></td>
                        <td><?= e(date('d M Y', strtotime($order['created_at']))); ?></td>
                        <td>
                            <a class="admin-btn admin-btn-light" href="<?= e(ADMIN_URL); ?>/orders.php?view=<?= e($order['id']); ?>">View</a>
                        </td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </section>

    <div style="display:grid; grid-template-columns: 1fr 1fr; gap:24px; align-items:start;">
        <section class="admin-panel" style="margin-bottom:24px;">
            <h2>Top Selling Products</h2>

            <table class="admin-table">
                <thead>
                    <tr>
                        <th>Product</th>
                        <th>Qty Sold</th>
                        <th>Revenue</th>
                    </tr>
                </thead>

                <tbody>
                    <?php if (!$topProducts): ?>
                        <tr>
                            <td colspan="3">No paid product sales yet.</td>
                        </tr>
                    <?php endif; ?>

                    <?php foreach ($topProducts as $product): ?>
                        <tr>
                            <td><?= e($product['product_name']); ?></td>
                            <td><?= e($product['total_quantity']); ?></td>
                            <td><?= e(format_price($product['total_revenue'])); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </section>

        <section class="admin-panel" style="margin-bottom:24px;">
            <h2>Coupon Usage</h2>

            <table class="admin-table">
                <thead>
                    <tr>
                        <th>Code</th>
                        <th>Type</th>
                        <th>Value</th>
                        <th>Used</th>
                        <th>Status</th>
                    </tr>
                </thead>

                <tbody>
                    <?php if (!$couponUsage): ?>
                        <tr>
                            <td colspan="5">No coupons created yet.</td>
                        </tr>
                    <?php endif; ?>

                    <?php foreach ($couponUsage as $coupon): ?>
                        <tr>
                            <td><strong><?= e($coupon['coupon_code']); ?></strong></td>
                            <td><?= e(ucfirst($coupon['coupon_type'])); ?></td>
                            <td>
                                <?php if ($coupon['coupon_type'] === 'percentage'): ?>
                                    <?= e(number_format((float)$coupon['coupon_value'], 2)); ?>%
                                <?php else: ?>
                                    <?= e(format_price($coupon['coupon_value'])); ?>
                                <?php endif; ?>
                            </td>
                            <td><?= e($coupon['used_count']); ?></td>
                            <td><?= e(ucfirst($coupon['status'])); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </section>
    </div>

    <section class="admin-panel">
        <h2>Low Stock Products</h2>

        <table class="admin-table">
            <thead>
                <tr>
                    <th>Product</th>
                    <th>SKU</th>
                    <th>Stock</th>
                    <th>Status</th>
                </tr>
            </thead>

            <tbody>
                <?php if (!$lowStockProducts): ?>
                    <tr>
                        <td colspan="4">No low stock products.</td>
                    </tr>
                <?php endif; ?>

                <?php foreach ($lowStockProducts as $product): ?>
                    <tr>
                        <td><?= e($product['product_name']); ?></td>
                        <td><?= e($product['sku'] ?? '-'); ?></td>
                        <td><?= e($product['stock_quantity']); ?></td>
                        <td><?= e(ucwords(str_replace('_', ' ', $product['stock_status']))); ?></td>
                    </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
    </section>
</main>

<?php require_once __DIR__ . '/includes/footer.php'; ?>