<?php
session_start();
require_once '../config/db_connect.php';
require_once '../includes/header.php';
require_once '../includes/sidebar.php';

// Make sure user is logged in
if (!isset($_SESSION['user'])) {
    header("Location: ../auth/login.php");
    exit;
}

$user = $_SESSION['user'];
$shop_id = $user['shop_id']; // shop_id from session

// Today's date
$today = date('Y-m-d');
$in90days = date('Y-m-d', strtotime('+90 days'));

// --- Notifications Queries ---

// Low stock items
$lowStockStmt = $pdo->prepare("
    SELECT p.name, pb.qty 
    FROM product_batches pb
    JOIN products p ON pb.product_id = p.id
    WHERE pb.shop_id = ? AND pb.qty < 10
");
$lowStockStmt->execute([$shop_id]);
$lowStockItems = $lowStockStmt->fetchAll(PDO::FETCH_ASSOC);

// Near expiry items (within 90 days)
$nearExpiryStmt = $pdo->prepare("
    SELECT p.name, pb.expiry_date 
    FROM product_batches pb
    JOIN products p ON pb.product_id = p.id
    WHERE pb.shop_id = ? AND pb.expiry_date BETWEEN ? AND ?
");
$nearExpiryStmt->execute([$shop_id, $today, $in90days]);
$nearExpiryItems = $nearExpiryStmt->fetchAll(PDO::FETCH_ASSOC);

// Slow moving items (sold less than 5 units in last 30 days)
$last30days = date('Y-m-d', strtotime('-30 days'));
$slowMovingStmt = $pdo->prepare("
    SELECT p.name, COALESCE(SUM(sl.qty), 0) as total_sold
    FROM products p
    LEFT JOIN sale_lines sl ON p.id = sl.product_id AND sl.shop_id = ?
    LEFT JOIN sales s ON sl.sale_id = s.id AND s.shop_id = ?
    GROUP BY p.id
    HAVING total_sold < 5
");
$slowMovingStmt->execute([$shop_id, $shop_id]);
$slowMovingItems = $slowMovingStmt->fetchAll(PDO::FETCH_ASSOC);

// High stock value items (cost_price * qty > 50000)
$highStockStmt = $pdo->prepare("
    SELECT p.name, (pb.qty * pb.cost_price) as stock_value
    FROM product_batches pb
    JOIN products p ON pb.product_id = p.id
    WHERE pb.shop_id = ? AND (pb.qty * pb.cost_price) > 50000
");
$highStockStmt->execute([$shop_id]);
$highStockItems = $highStockStmt->fetchAll(PDO::FETCH_ASSOC);

// Latest sales (1 week & 1 month)
$oneWeekAgo = date('Y-m-d', strtotime('-7 days'));
$oneMonthAgo = date('Y-m-d', strtotime('-30 days'));

$latestSalesStmt = $pdo->prepare("
    SELECT invoice_no, sale_date, total 
    FROM sales
    WHERE shop_id = ? AND sale_date >= ?
    ORDER BY sale_date DESC
");
$latestSalesStmt->execute([$shop_id, $oneMonthAgo]);
$latestSales = $latestSalesStmt->fetchAll(PDO::FETCH_ASSOC);

?>

<div class="col-md-10 ml-sm-auto px-4 py-4">
    <div class="container-fluid">
        <div class="card shadow-sm p-4 mb-4">
            <h2 class="text-success">Welcome, <?= htmlspecialchars($user['fullname']) ?> 👋</h2>
            <p>You are logged in as <strong><?= ucfirst(htmlspecialchars($user['role'])) ?></strong>.</p>
            <p>Current Shop: <strong><?= htmlspecialchars($user['shop_name']) ?></strong></p>
            <p class="text-muted">Use the sidebar to navigate through Products, Sales, Purchases, Suppliers, and Reports.</p>
            <a href="../auth/logout.php" class="btn btn-danger mt-3 d-md-none">Logout</a>
        </div>

        <!-- Notifications -->
        <div class="row">
            <div class="col-md-6 col-lg-3 mb-3">
                <div class="card border-left-warning shadow-sm p-3">
                    <h5>Low Stock</h5>
                    <p><?= count($lowStockItems) ?> items</p>
                </div>
            </div>
            <div class="col-md-6 col-lg-3 mb-3">
                <div class="card border-left-danger shadow-sm p-3">
                    <h5>Near Expiry</h5>
                    <p><?= count($nearExpiryItems) ?> items</p>
                </div>
            </div>
            <div class="col-md-6 col-lg-3 mb-3">
                <div class="card border-left-info shadow-sm p-3">
                    <h5>Slow Moving</h5>
                    <p><?= count($slowMovingItems) ?> items</p>
                </div>
            </div>
            <div class="col-md-6 col-lg-3 mb-3">
                <div class="card border-left-success shadow-sm p-3">
                    <h5>High Stock Value</h5>
                    <p><?= count($highStockItems) ?> items</p>
                </div>
            </div>
        </div>

        <!-- Latest Sales Table -->
        <div class="card shadow-sm p-3 mt-4">
            <h5>Latest Sales (Last Month)</h5>
            <table class="table table-sm table-bordered table-striped">
                <thead>
                    <tr>
                        <th>Invoice</th>
                        <th>Date</th>
                        <th>Total</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($latestSales as $sale): ?>
                        <tr>
                            <td><?= htmlspecialchars($sale['invoice_no']) ?></td>
                            <td><?= htmlspecialchars($sale['sale_date']) ?></td>
                            <td><?= number_format($sale['total'], 2) ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>

    </div>
</div>

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