<?php
include 'db.php';
include 'auth_check.php';

// Filters
$filter_item = isset($_GET['item_name']) ? $_GET['item_name'] : '';
$filter_invoice = isset($_GET['invoice_number']) ? $_GET['invoice_number'] : '';
$filter_date = isset($_GET['incoming_date']) ? $_GET['incoming_date'] : '';

// Pagination
$limit = 10;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $limit;

// Base SQL with JOINs
$sql = "
        SELECT 
        p.id,
        p.item_name,
        p.particular_name,
        p.specification,
        p.model_number,
        p.invoice_number,
        p.incoming_date,
        p.courier_name,
        p.qty AS purchased_qty,
        COALESCE(f.fault_qty,0) AS fault_qty,
        COALESCE(o.outward_qty,0) AS outward_qty,
        (p.qty - COALESCE(f.fault_qty,0) - COALESCE(o.outward_qty,0)) AS available_qty
    FROM purchase_stock p
    LEFT JOIN (
        SELECT purchase_stock_id, SUM(qty) AS fault_qty
        FROM fault_stock
        GROUP BY purchase_stock_id
    ) f ON f.purchase_stock_id = p.id
    LEFT JOIN (
        SELECT purchase_stock_id, SUM(qty) AS outward_qty
        FROM outward_stock
        GROUP BY purchase_stock_id
    ) o ON o.purchase_stock_id = p.id
    WHERE 1=1
";

// Apply filters
if ($filter_item != '') $sql .= " AND p.item_name LIKE '%" . $conn->real_escape_string($filter_item) . "%'";
if ($filter_invoice != '') $sql .= " AND p.invoice_number LIKE '%" . $conn->real_escape_string($filter_invoice) . "%'";
if ($filter_date != '') $sql .= " AND p.incoming_date = '" . $conn->real_escape_string($filter_date) . "'";

$sql .= " ORDER BY p.item_name ASC";

// Pagination
$totalResult = $conn->query($sql);
$totalRecords = $totalResult->num_rows;
$totalPages = ceil($totalRecords / $limit);

$sql .= " LIMIT $start, $limit";
$result = $conn->query($sql);
// echo "Total rows: " . $result->num_rows;
// while ($row = $result->fetch_assoc()) {
//     echo "<pre>";
//     print_r($row);
//     echo "</pre>";
// }
?>

<!DOCTYPE html>
<html>
<head>
    <title>Inventory Stock</title>
    <link rel="icon" type="image/png" href="assets/logo/logo_icon.png">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body { display: flex; flex-direction: column; min-height: 100vh; }
        .flex-grow-1 { flex: 1; }
        footer { background: #f8f9fa; padding: 10px 0; }
    </style>
</head>
<body>
<?php
if (session_status() == PHP_SESSION_NONE) session_start();
include 'navbar.php';
?>

<div class="flex-grow-1">
    <div class="container mt-2">
        <h2 class="text-center text-success mb-4">Inventory Stock</h2>

        <!-- Filters -->
        <div class="d-flex justify-content-center">
            <form method="GET" class="row g-3 mb-4">
                <div class="col-md-3">
                    <input type="text" class="form-control" name="invoice_number" placeholder="Filter by Invoice" value="<?php echo htmlspecialchars($filter_invoice); ?>">
                </div>
                <div class="col-md-3">
                    <input type="text" class="form-control" name="item_name" placeholder="Filter by Item" value="<?php echo htmlspecialchars($filter_item); ?>">
                </div>
                <div class="col-md-3">
                    <input type="date" class="form-control" name="incoming_date" value="<?php echo htmlspecialchars($filter_date); ?>">
                </div>
                <div class="col-md-3 d-flex gap-2">
                    <button class="btn btn-success" type="submit">Filter</button>
                    <a href="inventory.php" class="btn btn-secondary">Reset</a>
                </div>
            </form>
        </div>

        <!-- Inventory Table -->
        <table class="table table-bordered table-striped shadow-sm">
            <thead class="table-dark text-center">
                <tr>
                    <th>Invoice</th>
                    <th>Date</th>
                    <th>Courier</th>
                    <th>Item</th>
                    <th>Particular</th>
                    <th>Specification</th>
                    <th>Model</th>
                    <!-- <th>Company</th> -->
                    <th>Purchased Qty</th>
                    <th>Outward Qty</th>
                    <th>Fault Qty</th>
                    <th>Available Stock</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php 
                if ($result->num_rows > 0) {
                    while ($row = $result->fetch_assoc()) {
                        $purchased = intval($row['purchased_qty']);
                        $outward = intval($row['outward_qty']);
                        $fault = intval($row['fault_qty']);
                        $available = $purchased - ($outward + $fault);
                ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['invoice_number'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($row['incoming_date'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($row['courier_name'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($row['item_name'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($row['particular_name'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($row['specification'] ?? ''); ?></td>
                    <td><?php echo htmlspecialchars($row['model_number'] ?? ''); ?></td>
                    <!-- <td><?php echo htmlspecialchars($row['company_name'] ?? ''); ?></td> -->
                    <td class="text-center"><?php echo $purchased; ?></td>
                    <td class="text-center"><?php echo $outward; ?></td>
                    <td class="text-center"><?php echo $fault; ?></td>
                    <td class="text-center fw-bold <?php echo ($available <= 0 ? 'text-danger' : 'text-success'); ?>">
                        <?php echo $available; ?>
                    </td>
                    <td class="text-center">
                        <a href="edit_product.php?id=<?php echo $row['id']; ?>" class="btn btn-sm btn-warning">Edit</a>
                        <a href="javascript:void(0);" 
                        onclick="confirmDelete(<?php echo (int)$row['id']; ?>,
                            '<?php echo addslashes($row['item_name']); ?>',
                            '<?php echo addslashes($row['particular_name']); ?>',
                            '<?php echo addslashes($row['specification']); ?>',
                            '<?php echo addslashes($row['model_number']); ?>')" 
                        class="btn btn-sm btn-danger">Delete</a>
                    </td>
                </tr>
                <?php
                    }
                } else {
                    echo '<tr><td colspan="13" class="text-center text-danger">No records found</td></tr>';
                }
                ?>
            </tbody>
        </table>
    </div>
</div>
<?php
if (session_status() == PHP_SESSION_NONE) session_start();
include 'footer.php';
?>

<script>
function confirmDelete(id, item, particular, spec, model) {
    item = item || '-';
    particular = particular || '-';
    spec = spec || '-';
    model = model || '-';
    let message = "Are you sure you want to delete this record?\n\n";
    message += "Item: " + item + "\n";
    message += "Particular: " + particular + "\n";
    message += "Specification: " + spec + "\n";
    message += "Model: " + model + "\n\n";
    message += "This action cannot be undone.";

    if (confirm(message)) {
        window.location.href = 'delete_product.php?id=' + encodeURIComponent(id);
    }
}
</script>

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