<?php
session_start();

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

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

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

/*
    KPI DATA
*/

/* Today sales */
$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);

/* Month sales */
$stmt = $db->prepare("
    SELECT COALESCE(SUM(total), 0)
    FROM orders
    WHERE MONTH(order_date) = MONTH(CURDATE())
    AND YEAR(order_date) = YEAR(CURDATE())
    AND status = 'PAID'
");
$stmt->execute();
$monthSales = $stmt->fetchColumn();

/* Items */
$stmt = $db->prepare("SELECT COUNT(*) FROM items");
$stmt->execute();
$totalItems = $stmt->fetchColumn();

/* Categories */
$stmt = $db->prepare("SELECT COUNT(*) FROM categories");
$stmt->execute();
$totalCategories = $stmt->fetchColumn();

/* Active tables */
$stmt = $db->prepare("
    SELECT COUNT(*)
    FROM tables
    WHERE status = 'OCCUPIED'
");
$stmt->execute();
$activeTables = $stmt->fetchColumn();

/* Stock value using latest stock IN rates */
$stmt = $db->prepare("
    SELECT COALESCE(SUM(item_stock.stock_qty * item_stock.latest_rate), 0) AS stock_value
    FROM
    (
        SELECT
            i.id,
            i.stock AS stock_qty,

            COALESCE(
                (
                    SELECT se.rate
                    FROM stock_entries se
                    WHERE se.item_id = i.id
                    AND se.type = 'IN'
                    ORDER BY se.stock_date DESC, se.id DESC
                    LIMIT 1
                ),
                0
            ) AS latest_rate

        FROM items i
    ) item_stock
");
$stmt->execute();

$stockValue = $stmt->fetchColumn();

/*
    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 5
");
$stmt->execute();
$topItems = $stmt->fetchAll(PDO::FETCH_ASSOC);

/*
    LOW STOCK
*/
$stmt = $db->prepare("
    SELECT
        i.name,
        i.stock,
        u.name AS unit_name
    FROM items i
    LEFT JOIN units u ON i.unit_id = u.id
    WHERE i.stock <= 10
    ORDER BY i.stock ASC
    LIMIT 5
");
$stmt->execute();
$lowStock = $stmt->fetchAll(PDO::FETCH_ASSOC);

/*
    RECENT ORDERS
*/
$stmt = $db->prepare("
    SELECT
        o.*,
        '' as table_name
    FROM orders o
    ORDER BY o.order_date DESC
    LIMIT 8
");
$stmt->execute();
$recentOrders = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

<div class="container mt-4">

    <h2 class="mb-4">Dashboard</h2>

    <!-- KPI CARDS -->
    <div class="row g-3">

        <div class="col-md-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">
            <div class="card shadow-sm border-primary">
                <div class="card-body">
                    <h6>Today's Orders</h6>
                    <h3><?= $today['total_orders'] ?></h3>
                </div>
            </div>
        </div>

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

        <div class="col-md-3">
            <div class="card shadow-sm border-info">
                <div class="card-body">
                    <h6>Monthly Sales</h6>
                    <h3>₹ <?= number_format($monthSales, 2) ?></h3>
                </div>
            </div>
        </div>

    </div>

    <div class="row g-3 mt-2">

        <div class="col-md-3">
            <div class="card shadow-sm">
                <div class="card-body">
                    <h6>Total Items</h6>
                    <h3><?= $totalItems ?></h3>
                </div>
            </div>
        </div>

        <div class="col-md-3">
            <div class="card shadow-sm">
                <div class="card-body">
                    <h6>Categories</h6>
                    <h3><?= $totalCategories ?></h3>
                </div>
            </div>
        </div>

        <div class="col-md-3">
            <div class="card shadow-sm">
                <div class="card-body">
                    <h6>Active Tables</h6>
                    <h3><?= $activeTables ?></h3>
                </div>
            </div>
        </div>

        <div class="col-md-3">
            <div class="card shadow-sm">
                <div class="card-body">
                    <h6>Stock Value</h6>
                    <h3>₹ <?= number_format($stockValue, 2) ?></h3>
                </div>
            </div>
        </div>

    </div>

    <!-- TABLES -->
    <div class="row mt-4">

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

                <div class="card-body table-responsive">
                    <table class="table table-bordered">
                        <thead>
                            <tr>
                                <th>Item</th>
                                <th>Qty</th>
                                <th>Revenue</th>
                            </tr>
                        </thead>

                        <tbody>
                        <?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>

        <!-- LOW STOCK -->
        <div class="col-md-6">
            <div class="card shadow-sm">
                <div class="card-header bg-danger text-white">
                    Low Stock Alert
                </div>

                <div class="card-body table-responsive">
                    <table class="table table-bordered">
                        <thead>
                            <tr>
                                <th>Item</th>
                                <th>Stock</th>
                            </tr>
                        </thead>

                        <tbody>
                        <?php foreach ($lowStock as $item): ?>
                            <tr>
                                <td><?= htmlspecialchars($item['name']) ?></td>
                                <td>
                                    <?= number_format($item['stock'], 2) ?>
                                    <?= htmlspecialchars($item['unit_name']) ?>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

    </div>

    <!-- RECENT ORDERS -->
    <div class="card shadow-sm mt-4">
        <div class="card-header bg-primary text-white">
            Recent Orders
        </div>

        <div class="card-body table-responsive">
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>Invoice</th>
                        <th>Date</th>
                        <th>Table</th>
                        <th>Total</th>
                        <th>Status</th>
                        <th>Invoice</th>
                    </tr>
                </thead>

                <tbody>
                <?php foreach ($recentOrders as $order): ?>
                    <tr>
                        <td>#<?= $order['id'] ?></td>
                        <td><?= date('d-m-Y h:i A', strtotime($order['order_date'])) ?></td>
                        <td><?= htmlspecialchars($order['table_name'] ?? 'N/A') ?></td>
                        <td>₹ <?= number_format($order['total'], 2) ?></td>
                        <td><?= htmlspecialchars($order['status']) ?></td>
                        <td>
                            <a href="modules/invoices/view_invoice.php?id=<?= $order['id'] ?>"
                               class="btn btn-sm btn-primary">
                               View
                            </a>
                        </td>
                    </tr>
                <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- QUICK ACTIONS -->
    <div class="card shadow-sm mt-4 mb-5">
        <div class="card-header bg-success text-white">
            Quick Actions
        </div>

        <div class="card-body">

            <a href="modules/pos/index.php" class="btn btn-warning me-2">POS Billing</a>
            <a href="modules/items/create.php" class="btn btn-primary me-2">Add Item</a>
            <a href="modules/stock/stock_in.php" class="btn btn-success me-2">Stock IN</a>
            <a href="modules/stock/bulk_entry.php" class="btn btn-info me-2">Bulk Stock</a>
            <a href="modules/reports/daily_sales.php" class="btn btn-dark me-2">Sales Report</a>
            <a href="modules/settings/index.php" class="btn btn-secondary">Settings</a>

        </div>
    </div>

</div>

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