<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';

$username = $_SESSION['username'] ?? 'Guest';

// ===================== FILTER INPUTS =====================
$from_date = $_GET['from_date'] ?? '';
$to_date   = $_GET['to_date'] ?? '';
$category  = $_GET['category'] ?? '';
$movement  = $_GET['movement'] ?? ''; // 'added', 'dispatched' or ''

// ===================== FETCH CATEGORIES =====================
$categories_result = $conn->query("SELECT * FROM categories ORDER BY name ASC");
$categories = [];
while($c = $categories_result->fetch_assoc()){
    $categories[] = $c;
}

// ===================== BUILD WHERE CLAUSE =====================
$where = [];
if($category){
    $where[] = "p.category_id = ".intval($category);
}

// Filter stock movements
$movement_where = '';
if($movement == 'added'){
    $movement_where = "AND ps.created_at BETWEEN '".($from_date ?: '1970-01-01')."' AND '".($to_date ?: date('Y-m-d'))."'";
} elseif($movement == 'dispatched'){
    $movement_where = "AND ps.dispatched_at BETWEEN '".($from_date ?: '1970-01-01')."' AND '".($to_date ?: date('Y-m-d'))."'";
}

// ===================== FETCH PRODUCTS WITH STOCK =====================
$sql_products = "
    SELECT p.*, c.name AS category_name,ps.created_at,
           COALESCE(SUM(ps.quantity),0) AS total_qty,
           (p.selling_price * COALESCE(SUM(ps.quantity),0)) AS total_value
    FROM products p
    LEFT JOIN categories c ON p.category_id=c.id
    LEFT JOIN product_stock ps ON p.id = ps.product_id
    WHERE 1 ".(!empty($where) ? " AND ".implode(" AND ", $where) : "")." $movement_where
    GROUP BY p.id
    ORDER BY p.name ASC
";

$products = $conn->query($sql_products);

$all_products = [];
$total_inventory = 0;
$low_stock_count = 0;

while($p = $products->fetch_assoc()){
    $total_inventory += $p['total_value'];
    if($p['total_qty'] <= $p['min_level']) $low_stock_count++;
    $all_products[] = $p;
}

// ===================== FETCH STORES =====================
$stores = $conn->query("SELECT * FROM stores ORDER BY store_name ASC");
$store_data = [];
while ($s = $stores->fetch_assoc()) {
    $store_id = $s['id'];
    $stocks = $conn->query("
        SELECT ps.*, p.name AS product_name, p.sku, p.selling_price, c.name AS category_name
        FROM product_stock ps
        LEFT JOIN products p ON ps.product_id = p.id
        LEFT JOIN categories c ON p.category_id = c.id
        WHERE ps.store_id=$store_id
        ".($category ? " AND p.category_id=".intval($category) : "")."
        $movement_where
    ");
    $store_data[$store_id] = ['store_name' => $s['store_name'], 'stocks' => []];
    while($st = $stocks->fetch_assoc()){
        $store_data[$store_id]['stocks'][] = $st;
    }
}
?>

<div class="container mt-4">
    <div class="welcome-msg">
        <i class="fa fa-user-circle"></i> Welcome, <strong><?= htmlspecialchars($username) ?></strong>!
    </div>
    <button class="btn btn-secondary mb-3" onclick="history.back()"><i class="fa fa-arrow-left"></i> Back</button>

    <h3 class="text-center">📊 Inventory Report</h3>
    <hr>

    <!-- ===================== FILTER FORM ===================== -->
    <form method="GET" class="row g-3 mb-4">
        <div class="col-md-3">
            <label>From Date</label>
            <input type="date" name="from_date" class="form-control" value="<?= htmlspecialchars($from_date) ?>">
        </div>
        <div class="col-md-3">
            <label>To Date</label>
            <input type="date" name="to_date" class="form-control" value="<?= htmlspecialchars($to_date) ?>">
        </div>
        <div class="col-md-3">
            <label>Category</label>
            <select name="category" class="form-control">
                <option value="">-- All Categories --</option>
                <?php foreach($categories as $c): ?>
                    <option value="<?= $c['id'] ?>" <?= ($c['id']==$category)?'selected':'' ?>><?= htmlspecialchars($c['name']) ?></option>
                <?php endforeach; ?>
            </select>
        </div>
        <div class="col-md-3">
            <label>Stock Movement</label>
            <select name="movement" class="form-control">
                <option value="">-- All --</option>
                <option value="added" <?= $movement=='added'?'selected':'' ?>>Added</option>
                <option value="dispatched" <?= $movement=='dispatched'?'selected':'' ?>>Dispatched</option>
            </select>
        </div>
        <div class="col-12 mt-2">
            <button class="btn btn-primary"><i class="fa fa-filter"></i> Apply Filter</button>
            <a href="inventory_report.php" class="btn btn-secondary">Reset</a>
        </div>
    </form>

    <!-- ===================== SUMMARY ===================== -->
    <div class="row mb-3">
        <div class="col-md-4">
            <div class="alert alert-success text-center">
                <strong>Total Inventory Value:</strong><br>
                MK <?= number_format($total_inventory,2) ?>
            </div>
        </div>
        <div class="col-md-4">
            <div class="alert alert-danger text-center">
                <strong>Low Stock Items:</strong><br>
                <?= intval($low_stock_count) ?>
            </div>
        </div>
        <div class="col-md-4 text-end">
            <a href="export_inventory.php?from_date=<?= $from_date ?>&to_date=<?= $to_date ?>&category=<?= $category ?>&movement=<?= $movement ?>" class="btn btn-primary">
                <i class="fa fa-download"></i> Export CSV
            </a>
        </div>
    </div>

    <!-- ===================== MAIN STORE ===================== -->
    <h4>Main Store Inventory</h4>
    <table class="table table-bordered table-striped">
        <thead class="bg-dark text-white">
            <tr>
                <th>SKU</th><th>Name</th><th>Category</th><th>Quantity</th>
                <th>Min Level</th><th>Selling Price</th><th>Total Value</th><th>Low Stock</th>
                <th>Dated</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($all_products as $prod):
                $stock_main = $conn->query("SELECT quantity FROM product_stock WHERE product_id={$prod['id']} ")->fetch_assoc()['quantity'] ?? 0;
                $low_class = $stock_main <= $prod['min_level'] ? 'bg-danger text-white fw-bold' : '';
            ?>
            <tr>
                <td><?= $prod['sku'] ?></td>
                <td><?= $prod['name'] ?></td>
                <td><?= $prod['category_name'] ?></td>
                <td class="<?= $low_class ?>"><?= intval($stock_main) ?></td>
                <td><?= intval($prod['min_level']) ?></td>
                <td><?= $prod['selling_price'] ?></td>
                <td><?= number_format($prod['selling_price'] * $stock_main,2) ?></td>
                <td><?= $stock_main <= $prod['min_level'] ? 'Yes' : 'No' ?></td>
                     <td><?= $prod['created_at'] ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

    <!-- ===================== OTHER STORES ===================== -->
    <h4 class="mt-5">Other Stores Inventory</h4>
    <ul class="nav nav-tabs" id="storeTabs" role="tablist">
        <?php $active='active'; foreach($store_data as $id => $store): ?>
            <li class="nav-item" role="presentation">
                <button class="nav-link <?= $active ?>" id="store-<?= $id ?>-tab" data-bs-toggle="tab" data-bs-target="#store-<?= $id ?>" type="button" role="tab">
                    <?= htmlspecialchars($store['store_name']) ?>
                </button>
            </li>
        <?php $active=''; endforeach; ?>
    </ul>
    <div class="tab-content mt-3" id="storeTabsContent">
        <?php $active='show active'; foreach($store_data as $id => $store): ?>
            <div class="tab-pane fade <?= $active ?>" id="store-<?= $id ?>" role="tabpanel">
                <table class="table table-bordered table-striped">
                    <thead class="bg-secondary text-white">
                        <tr>
                            <th>SKU</th><th>Name</th><th>Category</th><th>Quantity</th><th>Selling Price</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach($store['stocks'] as $st): ?>
                        <tr>
                            <td><?= $st['sku'] ?></td>
                            <td><?= $st['product_name']?></td>
                            <td><?= htmlspecialchars($st['category_name'] ?? '') ?></td>
                            <td><?= intval($st['quantity']) ?></td>
                            <td><?= number_format($st['selling_price'],2) ?></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        <?php $active=''; endforeach; ?>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>

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