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

$username  = $_SESSION['username'] ?? 'Guest';
$user_role = $_SESSION['role'] ?? 'guest';

if(!in_array($user_role, ['admin','manager'])){
    die("<div class='alert alert-danger'>Access denied.</div>");
}

/* ================= APPROVE / REJECT ================= */
if(isset($_POST['action'], $_POST['req_id'])){

    $req_id = intval($_POST['req_id']);
    $action = $_POST['action'] === 'approve' ? 'Approved' : 'Rejected';

    $conn->begin_transaction();

    try {

        $conn->query("
            UPDATE bar_requisitions 
            SET status='$action' 
            WHERE id=$req_id
        ");

        /* ================= STOCK TRANSFER ================= */
        if($action === 'Approved'){

            $items = $conn->query("
                SELECT * FROM bar_requisition_items 
                WHERE requisition_id=$req_id
            ");

            while($item = $items->fetch_assoc()){

                $pid = intval($item['product_id']);
                $qty = intval($item['quantity']);

                // MAIN STORE
                $main = $conn->query("
                    SELECT * FROM product_stock 
                    WHERE product_id=$pid AND department_id=5
                ")->fetch_assoc();

                if(!$main || $main['quantity'] < $qty){
                    throw new Exception("Insufficient stock for Product ID $pid");
                }

                $conn->query("
                    UPDATE product_stock 
                    SET quantity = quantity - $qty 
                    WHERE id=".$main['id']
                );

                // BAR STORE (department 1)
                $bar = $conn->query("
                    SELECT * FROM product_stock 
                    WHERE product_id=$pid AND department_id=1
                ")->fetch_assoc();

                if($bar){
                    $conn->query("
                        UPDATE product_stock 
                        SET quantity = quantity + $qty 
                        WHERE id=".$bar['id']
                    );
                } else {
                    $conn->query("
                        INSERT INTO product_stock(product_id, department_id, quantity)
                        VALUES($pid, 1, $qty)
                    ");
                }
            }
        }

        $conn->commit();

        echo "<script>
            alert('Requisition $action successfully');
            window.location='bar_requisitions.php';
        </script>";
        exit;

    } catch(Exception $e){
        $conn->rollback();
        echo "<div class='alert alert-danger'>".$e->getMessage()."</div>";
    }
}

/* ================= FILTER ================= */
$from   = $_GET['from'] ?? date('Y-m-01');
$to     = $_GET['to'] ?? date('Y-m-d');
$status = $_GET['status'] ?? '';

$where = "WHERE DATE(br.created_at) BETWEEN '$from' AND '$to'";

if($status != ''){
    $where .= " AND br.status='$status'";
}

/* ================= PENDING ================= */
$pending = $conn->query("
    SELECT br.*, u.username,
    (SELECT COUNT(*) FROM bar_requisition_items WHERE requisition_id=br.id) AS item_count
    FROM bar_requisitions br
    LEFT JOIN users u ON br.requested_by=u.id
    WHERE br.status='Pending'
    ORDER BY br.id DESC
");

/* ================= REPORT ================= */
$sql = "
    SELECT br.*, u.username
    FROM bar_requisitions br
    LEFT JOIN users u ON br.requested_by=u.id
    $where
    ORDER BY br.created_at DESC
";

$report = $conn->query($sql);

/* ================= SAFETY CHECK ================= */
if(!$report){
    die("<div style='color:red;padding:10px'>
        SQL ERROR: ".$conn->error."
    </div>");
}
?>

<div class="container mt-4">

<button class="btn btn-secondary mb-3" onclick="history.back()">
    <i class="fa fa-arrow-left"></i> Back
</button>

<h3 class="text-center">🍺 Bar Requisitions Management</h3>
<hr>

<!-- ================= FILTER ================= -->
<form method="GET" class="row g-2 mb-3">

    <div class="col-md-3">
        <label>From</label>
        <input type="date" name="from" class="form-control" value="<?= $from ?>">
    </div>

    <div class="col-md-3">
        <label>To</label>
        <input type="date" name="to" class="form-control" value="<?= $to ?>">
    </div>

    <div class="col-md-3">
        <label>Status</label>
        <select name="status" class="form-control">
            <option value="">All</option>
            <option value="Approved" <?= $status=='Approved'?'selected':'' ?>>Approved</option>
            <option value="Rejected" <?= $status=='Rejected'?'selected':'' ?>>Rejected</option>
        </select>
    </div>

    <div class="col-md-3 d-flex align-items-end">
        <button class="btn btn-primary w-100">
            <i class="fa fa-search"></i> Filter
        </button>
    </div>

</form>

<!-- ================= PENDING ================= -->
<h5>Pending Requisitions</h5>

<?php if($pending->num_rows == 0): ?>
    <div class="alert alert-info">No pending requisitions.</div>
<?php else: ?>

<table class="table table-bordered table-striped">
<thead class="bg-dark text-white">
<tr>
    <th>ID</th>
    <th>Requested By</th>
    <th>Date</th>
    <th>Items</th>
    <th>Actions</th>
</tr>
</thead>

<tbody>
<?php while($r=$pending->fetch_assoc()): ?>
<tr>
    <td>#<?= $r['id'] ?></td>
    <td><?= $r['username'] ?></td>
    <td><?= date('d-m-Y H:i', strtotime($r['created_at'])) ?></td>
    <td><?= $r['item_count'] ?></td>
    <td>
        <form method="POST">
            <input type="hidden" name="req_id" value="<?= $r['id'] ?>">
            <button name="action" value="approve" class="btn btn-success btn-sm">Approve</button>
            <button name="action" value="reject" class="btn btn-danger btn-sm">Reject</button>
        </form>
    </td>
</tr>
<?php endwhile; ?>
</tbody>
</table>

<?php endif; ?>

<hr>

<!-- ================= PRINT BUTTON ================= -->
<div class="text-end mb-3">
    <a href="print_bar_requisitions.php?from=<?= $from ?>&to=<?= $to ?>&status=<?= $status ?>"
       target="_blank"
       class="btn btn-dark">
        <i class="fa fa-print"></i> Print Report
    </a>
</div>

<!-- ================= REPORT ================= -->
<h5>Requisition Report</h5>

<table class="table table-bordered">
<thead class="bg-success text-white">
<tr>
    <th>ID</th>
    <th>User</th>
    <th>Date</th>
    <th>Status</th>
    <th>Items</th>
</tr>
</thead>

<tbody>

<?php if($report->num_rows > 0): ?>

    <?php while($r=$report->fetch_assoc()): ?>

    <?php
    $items = $conn->query("
        SELECT bri.quantity, p.name
        FROM bar_requisition_items bri
        LEFT JOIN products p ON bri.product_id=p.id
        WHERE bri.requisition_id={$r['id']}
    ");

    $text = "";
    while($it=$items->fetch_assoc()){
        $text .= $it['name']." (".$it['quantity'].")<br>";
    }
    ?>

    <tr>
        <td>#<?= $r['id'] ?></td>
        <td><?= $r['username'] ?></td>
        <td><?= date('d-m-Y', strtotime($r['created_at'])) ?></td>
        <td><?= $r['status'] ?></td>
        <td><?= $text ?></td>
    </tr>

    <?php endwhile; ?>

<?php else: ?>
<tr>
    <td colspan="5" style="text-align:center;">No records found</td>
</tr>
<?php endif; ?>

</tbody>
</table>

</div>

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