<?php
include_once __DIR__ . '/../config.php';
include_once __DIR__ . '/../functions.php';
include_once __DIR__ . '/../includes/auth_check.php';
// requireRole(['admin', 'supply_manager']);

$page_title = "รายงานสต็อกคงเหลือ";

// --- Fetch Categories for Filter ---
$categories = [];
$sql_cat = "SELECT id, category_name FROM categories ORDER BY category_name ASC";
$res_cat = mysqli_query($conn, $sql_cat);
if($res_cat) {
    while($row = mysqli_fetch_assoc($res_cat)) {
        $categories[] = $row;
    }
    mysqli_free_result($res_cat);
}

// --- Handle Filters ---
$filter_cat_id = isset($_GET['category_id']) ? intval($_GET['category_id']) : 0;
$filter_status = isset($_GET['status']) ? sanitize_input($conn, $_GET['status']) : 'active'; // Default to active

// --- Build WHERE clause ---
$where_clauses = [];
if ($filter_cat_id > 0) {
    $where_clauses[] = "s.category_id = " . $filter_cat_id;
}
if (!empty($filter_status) && $filter_status != 'all') {
    $where_clauses[] = "s.status = '" . $filter_status . "'";
}
$where_sql = count($where_clauses) > 0 ? "WHERE " . implode(' AND ', $where_clauses) : '';

// --- Fetch Supplies Data ---
$supplies = [];
// Using the view might be simpler here if it has all needed columns
$sql = "SELECT s.*, c.category_name
        FROM supplies s
        JOIN categories c ON s.category_id = c.id
        $where_sql
        ORDER BY s.category_id ASC, s.supply_code ASC";
$result = mysqli_query($conn, $sql);
if ($result) {
    while ($row = mysqli_fetch_assoc($result)) {
        $supplies[] = $row;
    }
    mysqli_free_result($result);
} else {
    echo "Error fetching stock data: " . mysqli_error($conn);
}
mysqli_close($conn);

// --- Include Header and Sidebar ---
include_once __DIR__ . '/../includes/header.php';
include_once __DIR__ . '/../includes/sidebar_supply.php';
?>

<div class="container-fluid">
    <h1 class="mt-4"><?php echo $page_title; ?></h1>

     <div class="card mb-4">
        <div class="card-header"><i class="bi bi-filter me-1"></i>ตัวกรองรายงาน</div>
        <div class="card-body">
            <form method="GET" action="" class="row g-3 align-items-end">
                <div class="col-md-5">
                    <label for="category_id" class="form-label">หมวดหมู่</label>
                    <select id="category_id" name="category_id" class="form-select">
                        <option value="0">-- ทุกหมวดหมู่ --</option>
                         <?php foreach($categories as $cat): ?>
                            <option value="<?php echo $cat['id']; ?>" <?php echo ($filter_cat_id == $cat['id']) ? 'selected' : ''; ?>>
                                <?php echo htmlspecialchars($cat['category_name']); ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                 <div class="col-md-5">
                    <label for="status" class="form-label">สถานะพัสดุ</label>
                    <select id="status" name="status" class="form-select">
                        <option value="active" <?php echo ($filter_status == 'active') ? 'selected' : ''; ?>>ใช้งาน</option>
                        <option value="inactive" <?php echo ($filter_status == 'inactive') ? 'selected' : ''; ?>>ไม่ใช้งาน</option>
                        <option value="all" <?php echo ($filter_status == 'all') ? 'selected' : ''; ?>>ทั้งหมด</option>
                    </select>
                </div>
                <div class="col-md-2">
                    <button type="submit" class="btn btn-primary w-100">
                        <i class="bi bi-search me-1"></i> กรองข้อมูล
                    </button>
                </div>
            </form>
        </div>
    </div>


    <div class="card mb-4">
        <div class="card-header">
            <i class="bi bi-table me-1"></i>
            ผลลัพธ์รายงานสต็อก
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-bordered table-striped table-hover table-sm">
                    <thead class="table-dark">
                        <tr>
                            <th>#</th>
                            <th>รหัส</th>
                            <th>ชื่อพัสดุ</th>
                            <th>หมวดหมู่</th>
                            <th>หน่วยนับ</th>
                            <th class="text-end">คงคลัง</th>
                            <th class="text-end">ราคาเฉลี่ย/หน่วย</th>
                            <th class="text-end">มูลค่ารวม</th>
                            <th>สถานะ</th>
                            <th>จุดสั่งซื้อ</th>
                            <th>ดำเนินการ</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (!empty($supplies)): ?>
                            <?php $counter = 1; $grand_total_value = 0; ?>
                            <?php foreach ($supplies as $item): ?>
                                <tr class="<?php echo ($item['status'] == 'inactive') ? 'table-secondary' : ''; ?> <?php echo ($item['status'] == 'active' && $item['quantity_in_stock'] <= $item['min_stock_level'] && $item['min_stock_level'] > 0) ? 'table-warning' : ''; ?>" >
                                    <td><?php echo $counter++; ?></td>
                                    <td><?php echo htmlspecialchars($item['supply_code']); ?></td>
                                    <td><?php echo htmlspecialchars($item['supply_name']); ?></td>
                                    <td><?php echo htmlspecialchars($item['category_name']); ?></td>
                                    <td><?php echo htmlspecialchars($item['unit']); ?></td>
                                    <td class="text-end fw-bold"><?php echo number_format($item['quantity_in_stock']); ?></td>
                                    <td class="text-end"><?php echo number_format($item['average_unit_price'], 2); ?></td>
                                    <td class="text-end"><?php echo number_format($item['total_value'], 2); ?></td>
                                    <td>
                                        <?php if ($item['status'] == 'active'): ?>
                                            <span class="badge bg-success">ใช้งาน</span>
                                        <?php else: ?>
                                            <span class="badge bg-secondary">ไม่ใช้งาน</span>
                                        <?php endif; ?>
                                         <?php if ($item['status'] == 'active' && $item['quantity_in_stock'] <= $item['min_stock_level'] && $item['min_stock_level'] > 0): ?>
                                             <span class="badge bg-danger text-white ms-1">ต่ำ</span>
                                        <?php endif; ?>
                                    </td>
                                     <td class="text-end"><?php echo number_format($item['min_stock_level']); ?></td>
                                     <td>
                                        <a href="../supplies/supply_view.php?id=<?php echo $item['id']; ?>" class="btn btn-sm btn-info" title="ดูรายละเอียด Batch">
                                            <i class="bi bi-search"></i>
                                        </a>
                                    </td>
                                </tr>
                                <?php if ($item['status'] == 'active') $grand_total_value += $item['total_value']; ?>
                            <?php endforeach; ?>
                        <?php else: ?>
                            <tr>
                                <td colspan="11" class="text-center">ไม่พบข้อมูลตามเงื่อนไขที่เลือก</td>
                            </tr>
                        <?php endif; ?>
                    </tbody>
                      <?php if (!empty($supplies)): ?>
                     <tfoot class="table-light fw-bold">
                         <tr>
                             <td colspan="7" class="text-end">มูลค่ารวม (เฉพาะที่ใช้งาน):</td>
                             <td class="text-end fs-5"><?php echo number_format($grand_total_value, 2); ?></td>
                             <td colspan="3"></td>
                         </tr>
                     </tfoot>
                     <?php endif; ?>
                </table>
            </div>
        </div
    </div>

</div>

<?php
// --- Include Footer ---
include_once __DIR__ . '/../includes/footer.php';
?>S