<?php
session_start();

require_once '../../config/database.php';
require_once '../../includes/auth.php';

$basePath = '../../';
include '../../includes/header.php';

$db = (new Database())->connect();

$stmt = $db->prepare("
    SELECT
        COUNT(*) AS total_orders,
        COALESCE(SUM(total), 0) AS total_sales,
        COALESCE(SUM(gst), 0) AS total_gst
    FROM orders
    WHERE DATE(order_date) = CURDATE()
    AND status = 'PAID'
");
$stmt->execute();
$today = $stmt->fetch(PDO::FETCH_ASSOC);

/*
    YESTERDAY SALES
*/
$stmt = $db->prepare("
    SELECT
        COALESCE(SUM(total), 0) AS total_sales
    FROM orders
    WHERE DATE(order_date) = CURDATE() - INTERVAL 1 DAY
    AND status = 'PAID'
");
$stmt->execute();
$yesterday = $stmt->fetch(PDO::FETCH_ASSOC);

/*
    MONTH SALES
*/
$stmt = $db->prepare("
    SELECT
        COALESCE(SUM(total), 0) AS total_sales
    FROM orders
    WHERE MONTH(order_date) = MONTH(CURDATE())
    AND YEAR(order_date) = YEAR(CURDATE())
    AND status = 'PAID'
");
$stmt->execute();
$month = $stmt->fetch(PDO::FETCH_ASSOC);

/*
    AVG BILL
*/
$avgBill = 0;

if ($today['total_orders'] > 0) {
    $avgBill = $today['total_sales'] / $today['total_orders'];
}

/*
    TOP SELLING ITEMS
*/
$stmt = $db->prepare("
    SELECT
        i.name,
        SUM(oi.quantity) AS qty_sold,
        SUM(oi.quantity * oi.price) AS revenue
    FROM order_items oi
    INNER JOIN items i ON oi.item_id = i.id
    INNER JOIN orders o ON oi.order_id = o.id
    WHERE o.status = 'PAID'
    GROUP BY oi.item_id
    ORDER BY qty_sold DESC
    LIMIT 10
");
$stmt->execute();
$topItems = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<div class="container mt-4">

    <h3 class="mb-4">Reports Dashboard</h3>

    <!-- SUMMARY CARDS -->
    <div class="row">

        <div class="col-md-3 mb-3">
            <div class="card shadow-sm border-success">
                <div class="card-body">
                    <h6>Today's Sales</h6>
                    <h3>₹ <?= number_format($today['total_sales'], 2) ?></h3>
                </div>
            </div>
        </div>

        <div class="col-md-3 mb-3">
            <div class="card shadow-sm border-primary">
                <div class="card-body">
                    <h6>Yesterday Sales</h6>
                    <h3>₹ <?= number_format($yesterday['total_sales'], 2) ?></h3>
                </div>
            </div>
        </div>

        <div class="col-md-3 mb-3">
            <div class="card shadow-sm border-warning">
                <div class="card-body">
                    <h6>This Month Sales</h6>
                    <h3>₹ <?= number_format($month['total_sales'], 2) ?></h3>
                </div>
            </div>
        </div>

        <div class="col-md-3 mb-3">
            <div class="card shadow-sm border-info">
                <div class="card-body">
                    <h6>Total Orders Today</h6>
                    <h3><?= $today['total_orders'] ?></h3>
                </div>
            </div>
        </div>

    </div>

    <div class="row">

        <div class="col-md-6 mb-3">
            <div class="card shadow-sm border-secondary">
                <div class="card-body">
                    <h6>GST Collected Today</h6>
                    <h3>₹ <?= number_format($today['total_gst'], 2) ?></h3>
                </div>
            </div>
        </div>

        <div class="col-md-6 mb-3">
            <div class="card shadow-sm border-dark">
                <div class="card-body">
                    <h6>Average Bill Value Today</h6>
                    <h3>₹ <?= number_format($avgBill, 2) ?></h3>
                </div>
            </div>
        </div>

    </div>

    <!-- TOP ITEMS -->
    <div class="card shadow-sm mt-4">
        <div class="card-header bg-dark text-white">
            Top Selling Items
        </div>

        <div class="card-body table-responsive">

            <table class="table table-bordered table-hover">

                <thead>
                    <tr>
                        <th>Item Name</th>
                        <th>Qty Sold</th>
                        <th>Revenue</th>
                    </tr>
                </thead>

                <tbody>

                <?php if (empty($topItems)): ?>
                    <tr>
                        <td colspan="3" class="text-center">
                            No sales data found.
                        </td>
                    </tr>
                <?php endif; ?>

                <?php foreach ($topItems as $item): ?>
                    <tr>
                        <td><?= htmlspecialchars($item['name']) ?></td>
                        <td><?= number_format($item['qty_sold'], 2) ?></td>
                        <td>₹ <?= number_format($item['revenue'], 2) ?></td>
                    </tr>
                <?php endforeach; ?>

                </tbody>

            </table>

        </div>
    </div>

</div>

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