<?php
session_start();
require_once '../assets/db_connect.php';

// Get search term
$search = isset($_GET['search']) ? trim($_GET['search']) : '';

// Build SQL
$sql = "
    SELECT b.*, p.name AS product_name, s.name AS supplier_name
    FROM product_batches b
    LEFT JOIN products p ON b.product_id = p.id
    LEFT JOIN suppliers s ON b.supplier_id = s.id
";

$params = [];
if (!empty($search)) {
    $sql .= " WHERE p.name LIKE :pname OR s.name LIKE :sname OR b.batch_no LIKE :bno";
    $params[':pname'] = "%$search%";
    $params[':sname'] = "%$search%";
    $params[':bno']   = "%$search%";
}

$sql .= " ORDER BY b.id DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Output table
?>
<table class="table table-bordered table-hover">
    <thead class="thead-light">
        <tr>
            <th>#</th>
            <th>Product</th>
            <th>Batch No</th>
            <th>Qty</th>
            <th>Cost Price</th>
            <th>Sell Price</th>
            <th>Expiry Date</th>
            <th>Supplier</th>
            <th>Created At</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        <?php if (count($rows) > 0): ?>
            <?php foreach ($rows as $row): ?>
            <tr>
                <td><?= $row['id'] ?></td>
                <td><?= htmlspecialchars($row['product_name']) ?></td>
                <td><?= htmlspecialchars($row['batch_no']) ?></td>
                <td><?= $row['qty'] ?></td>
                <td><?= number_format($row['cost_price'], 2) ?></td>
                <td><?= number_format($row['sell_price'], 2) ?></td>
                <td><?= $row['expiry_date'] ? date('d-M-Y', strtotime($row['expiry_date'])) : '-' ?></td>
                <td><?= htmlspecialchars($row['supplier_name'] ?? '-') ?></td>
                <td><?= date('d-M-Y H:i', strtotime($row['created_at'])) ?></td>
                <td>
                    <a href="edit_batch.php?id=<?= $row['id'] ?>" class="btn btn-sm btn-warning">Edit</a>
                    <a href="delete_batch.php?id=<?= $row['id'] ?>" class="btn btn-sm btn-danger" onclick="return confirm('Are you sure you want to delete this batch?')">Delete</a>
                </td>
            </tr>
            <?php endforeach; ?>
        <?php else: ?>
            <tr>
                <td colspan="10" class="text-center text-muted">No batches found.</td>
            </tr>
        <?php endif; ?>
    </tbody>
</table>
