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

$user_role = $_SESSION['role'] ?? '';
$user_id   = $_SESSION['user_id'] ?? 0;
$username  = $_SESSION['username'] ?? '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'];
    $status = ($action == 'approve') ? 'Approved' : 'Rejected';
    $approved_at = date('Y-m-d H:i:s');

    $conn->begin_transaction();

    try {

        $stmt = $conn->prepare("
            UPDATE r_requisitions
            SET status=?, approved_by=?, approved_at=?
            WHERE id=?
        ");
        $stmt->bind_param("sisi", $status, $user_id, $approved_at, $req_id);
        $stmt->execute();

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

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

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

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

                // MAIN STORE (5)
                $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 (Main Store) Product ID $pid");
                }

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

                // RESTAURANT (1)
                $rest = $conn->query("
                    SELECT * FROM product_stock 
                    WHERE product_id=$pid AND department_id=1
                ")->fetch_assoc();

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

        $conn->commit();

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

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

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

$where = "WHERE 1=1";

if($status){
    $where .= " AND rr.status='$status'";
}
$where .= " AND DATE(rr.created_at) BETWEEN '$from' AND '$to'";

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

/* ================= REPORT ================= */
$report = $conn->query("
    SELECT rr.*, 
           u.username AS requester,
           u2.username AS approver
    FROM r_requisitions rr
    LEFT JOIN users u ON rr.requested_by=u.id
    LEFT JOIN users u2 ON rr.approved_by=u2.id
    $where
    ORDER BY rr.id DESC
");
?>

<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">🍽️ Restaurant Requisitions System</h3>
<hr>

<!-- ================= DATE 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>
<button class="btn btn-info btn-sm" onclick="toggleItems(<?= $r['id'] ?>)">
View (<?= $r['item_count'] ?>)
</button>
</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>

<tr id="items_<?= $r['id'] ?>" style="display:none;">
<td colspan="5">
<?php
$items = $conn->query("
SELECT rri.*, p.name,
COALESCE(ps.quantity,0) AS stock
FROM r_requisition_items rri
LEFT JOIN products p ON rri.product_id=p.id
LEFT JOIN product_stock ps ON ps.product_id=p.id AND ps.department_id=5
WHERE rri.requisition_id={$r['id']}
");
?>
<table class="table table-sm table-bordered">
<tr class="bg-light">
<th>Product</th>
<th>Qty</th>
<th>Stock</th>
</tr>

<?php while($it=$items->fetch_assoc()): ?>
<tr>
<td><?= $it['name'] ?></td>
<td><?= $it['quantity'] ?></td>
<td><?= $it['stock'] ?></td>
</tr>
<?php endwhile; ?>

</table>
</td>
</tr>

<?php endwhile; ?>
</tbody>
</table>

<?php endif; ?>

<hr>

<!-- ================= PRINT BUTTON ================= -->
<div class="text-end mb-3">
    <a target="_blank"
       href="print_restaurant_requisitions.php?from=<?= $from ?>&to=<?= $to ?>"
       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>Requested By</th>
<th>Approved By</th>
<th>Date</th>
<th>Status</th>
<th>Items</th>
</tr>
</thead>

<tbody>

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

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

<tr>
<td>#<?= $r['id'] ?></td>
<td><?= $r['requester'] ?></td>
<td><?= $r['approver'] ?? '-' ?></td>
<td><?= $r['created_at'] ?></td>
<td><?= $r['status'] ?></td>
<td><?= $text ?></td>
</tr>

<?php endwhile; ?>

</tbody>
</table>

</div>

<script>
function toggleItems(id){
let row=document.getElementById('items_'+id);
row.style.display = (row.style.display==='none') ? 'table-row' : 'none';
}
</script>

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