<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$username = $_SESSION['username'] ?? 'Guest';
$department_id = 3; // Housekeeping

// ==================== Filters ====================
$product_id = intval($_GET['product_id'] ?? 0);
$status_filter = $_GET['status'] ?? '';
$room_filter = intval($_GET['room_id'] ?? 0);
$from_date = $_GET['from_date'] ?? '';
$to_date = $_GET['to_date'] ?? '';

// ==================== Fetch Data for Filters ====================
$products_dropdown = $conn->query("
    SELECT p.id, p.name 
    FROM products p
    LEFT JOIN hk_requisition_items ri ON ri.product_id=p.id
    LEFT JOIN hk_requisitions r ON r.id=ri.requisition_id AND r.status='Approved'
    GROUP BY p.id
");

$rooms_dropdown = $conn->query("SELECT id, name FROM rooms ORDER BY name ASC");

// ==================== Dashboard Stats ====================
$total_approved_stock = $conn->query("
    SELECT COALESCE(SUM(ri.quantity),0) AS total
    FROM hk_requisition_items ri
    JOIN hk_requisitions r ON r.id = ri.requisition_id
    WHERE r.status='Approved'
")->fetch_assoc()['total'];

$total_dispatched = $conn->query("
    SELECT COALESCE(SUM(quantity),0) AS total
    FROM hk_dispatch_items
")->fetch_assoc()['total'];

$total_available = $total_approved_stock - $total_dispatched;

// Rooms Cleaned Today
$rooms_cleaned = $conn->query("
    SELECT COUNT(*) AS total 
    FROM housekeeping_status 
    WHERE DATE(last_cleaned)=CURDATE() AND housekeeping_status='Cleaned'
")->fetch_assoc()['total'];

// Rooms Pending Today
$rooms_pending = $conn->query("
    SELECT COUNT(*) AS total 
    FROM housekeeping_status 
    WHERE DATE(schedule_date)=CURDATE() AND housekeeping_status!='Cleaned'
")->fetch_assoc()['total'];

// Pending Maintenance Requests
$pending_maintenance = $conn->query("SELECT COUNT(*) AS total FROM maintenance_requests WHERE status='Pending'")->fetch_assoc()['total'];

// ==================== Fetch Approved Stock ====================
$approved_stock_query = "
    SELECT p.id, p.name, p.sku, p.selling_price,
           COALESCE(SUM(ri.quantity),0) AS approved_qty,
           COALESCE((SELECT SUM(quantity) FROM hk_dispatch_items WHERE product_id=p.id),0) AS dispatched_qty
    FROM products p
    LEFT JOIN hk_requisition_items ri ON ri.product_id=p.id
    LEFT JOIN hk_requisitions r ON r.id=ri.requisition_id AND r.status='Approved'
    GROUP BY p.id
    HAVING (approved_qty - dispatched_qty) > 0
    ORDER BY p.name ASC
";
$approved_stock = $conn->query($approved_stock_query);

// ==================== Dispatch Handler ====================
if(isset($_POST['dispatch'])){
    $product_id = intval($_POST['product_id']);
    $dispatch_qty = intval($_POST['dispatch_qty']);
    $recipient = $conn->real_escape_string($_POST['recipient']);

    $approved_qty = $conn->query("
        SELECT COALESCE(SUM(ri.quantity),0) AS total
        FROM hk_requisition_items ri
        JOIN hk_requisitions r ON r.id=ri.requisition_id
        WHERE r.status='Approved' AND ri.product_id=$product_id
    ")->fetch_assoc()['total'];

    $dispatched_qty = $conn->query("
        SELECT COALESCE(SUM(quantity),0) AS total 
        FROM hk_dispatch_items 
        WHERE product_id=$product_id
    ")->fetch_assoc()['total'];

    $available = $approved_qty - $dispatched_qty;

    if($dispatch_qty>0 && $dispatch_qty<=$available){
        $conn->query("
            INSERT INTO hk_dispatch_items (product_id, quantity, recipient, dispatched_by, dispatched_at)
            VALUES ($product_id,$dispatch_qty,'$recipient',{$_SESSION['user_id']},NOW())
        ");
        echo "<script>alert('Dispatched successfully'); window.location='housekeeping_dashboard.php';</script>";
        exit;
    } else {
        echo "<script>alert('Invalid dispatch quantity. Only $available available.'); window.location='housekeeping_dashboard.php';</script>";
        exit;
    }
}

// ==================== Fetch Dispatch Report ====================
$dispatch_report = $conn->query("
    SELECT d.*, p.name AS product_name, p.sku, u.username AS dispatched_by_name
    FROM hk_dispatch_items d
    LEFT JOIN products p ON p.id=d.product_id
    LEFT JOIN users u ON u.id=d.dispatched_by
    ORDER BY d.dispatched_at DESC
");

// ==================== Fetch Housekeeping Status ====================
$housekeeping_status = $conn->query("
    SELECT hs.*, r.name AS room_name
    FROM housekeeping_status hs
    LEFT JOIN rooms r ON r.id=hs.room_id
    ORDER BY hs.schedule_date DESC
");

// ==================== Fetch Maintenance Requests ====================
$maintenance_requests = $conn->query("
    SELECT m.*, r.name AS room_name, u.username AS staff_name
    FROM maintenance_requests m
    LEFT JOIN rooms r ON r.id=m.room_id
    LEFT JOIN users u ON u.id=m.staff_id
    ORDER BY m.request_date DESC
");

?>
<div class="container mt-4">
    <h3><i class="fa fa-home"></i> Housekeeping Dashboard</h3>

    <!-- Dashboard Cards -->
    <div class="row mb-4">
        <div class="col-md-2"><div class="card text-white bg-primary shadow-sm"><div class="card-body"><h5>Approved Stock</h5><h3><?= $total_approved_stock ?></h3></div></div></div>
        <div class="col-md-2"><div class="card text-white bg-success shadow-sm"><div class="card-body"><h5>Available Stock</h5><h3><?= $total_available ?></h3></div></div></div>
        <div class="col-md-2"><div class="card text-white bg-danger shadow-sm"><div class="card-body"><h5>Dispatched</h5><h3><?= $total_dispatched ?></h3></div></div></div>
        <div class="col-md-2"><div class="card text-white bg-warning shadow-sm"><div class="card-body"><h5>Rooms Cleaned Today</h5><h3><?= $rooms_cleaned ?></h3></div></div></div>
        <div class="col-md-2"><div class="card text-white bg-secondary shadow-sm"><div class="card-body"><h5>Rooms Pending</h5><h3><?= $rooms_pending ?></h3></div></div></div>
        <div class="col-md-2"><div class="card text-white bg-dark shadow-sm"><div class="card-body"><h5>Pending Maintenance</h5><h3><?= $pending_maintenance ?></h3></div></div></div>
    </div>

    <!-- Approved Stock Table with Dispatch -->
    <div class="card mb-4 shadow-sm">
        <div class="card-header bg-dark text-white"><i class="fa fa-boxes"></i> Approved Stock & Dispatch</div>
        <div class="card-body p-0">
            <table class="table table-bordered table-striped mb-0">
                <thead class="bg-secondary text-white">
                    <tr><th>#</th><th>SKU</th><th>Product</th><th>Approved Qty</th><th>Dispatched Qty</th><th>Available</th><th>Dispatch</th></tr>
                </thead>
                <tbody>
                    <?php $i=1; while($p=$approved_stock->fetch_assoc()):
                        $available = $p['approved_qty'] - $p['dispatched_qty'];
                    ?>
                    <tr>
                        <td><?= $i ?></td>
                        <td><?= htmlspecialchars($p['sku']) ?></td>
                        <td><?= htmlspecialchars($p['name']) ?></td>
                        <td><?= $p['approved_qty'] ?></td>
                        <td><?= $p['dispatched_qty'] ?></td>
                        <td <?= ($available<=0)?'class="text-danger fw-bold"':'' ?>><?= $available ?></td>
                        <td>
                            <?php if($available>0): ?>
                            <form method="POST" class="d-flex gap-1">
                                <input type="hidden" name="product_id" value="<?= $p['id'] ?>">
                                <input type="number" name="dispatch_qty" min="1" max="<?= $available ?>" class="form-control form-control-sm" placeholder="Qty" required>
                                <input type="text" name="recipient" class="form-control form-control-sm" placeholder="Recipient" required>
                                <button type="submit" name="dispatch" class="btn btn-success btn-sm"><i class="fa fa-truck"></i></button>
                            </form>
                            <?php else: ?>
                                <em class="text-muted">No stock</em>
                            <?php endif; ?>
                        </td>
                    </tr>
                    <?php $i++; endwhile; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- Housekeeping Status -->
    <div class="card mb-4 shadow-sm">
        <div class="card-header bg-dark text-white"><i class="fa fa-bed"></i> Housekeeping Status</div>
        <div class="card-body p-0">
            <table class="table table-bordered table-striped mb-0">
                <thead class="bg-secondary text-white">
                    <tr><th>Room</th><th>Status</th><th>Housekeeper</th><th>Last Cleaned</th><th>Schedule Date</th></tr>
                </thead>
                <tbody>
                    <?php while($hs=$housekeeping_status->fetch_assoc()): ?>
                        <tr>
                            <td><?= htmlspecialchars($hs['room_name']) ?></td>
                            <td><?= htmlspecialchars($hs['housekeeping_status']) ?></td>
                            <td><?= htmlspecialchars($hs['housekeeper']) ?></td>
                            <td><?= $hs['last_cleaned'] ?></td>
                            <td><?= $hs['schedule_date'] ?></td>
                        </tr>
                    <?php endwhile; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- Maintenance Requests -->
    <div class="card mb-4 shadow-sm">
        <div class="card-header bg-dark text-white"><i class="fa fa-tools"></i> Maintenance Requests</div>
        <div class="card-body p-0">
            <table class="table table-bordered table-striped mb-0">
                <thead class="bg-secondary text-white">
                    <tr><th>Room</th><th>Staff</th><th>Request Detail</th><th>Status</th><th>Request Date</th><th>Completion Date</th></tr>
                </thead>
                <tbody>
                    <?php while($mr=$maintenance_requests->fetch_assoc()): ?>
                        <tr>
                            <td><?= $mr['room_name'] ?></td>
                            <td><?= $mr['staff_name'] ?></td>
                            <td><?= $mr['request_detail'] ?></td>
                            <td><?= $mr['status'] ?></td>
                            <td><?= $mr['request_date'] ?></td>
                            <td><?= $mr['completion_date'] ?></td>
                        </tr>
                    <?php endwhile; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- Dispatch Report -->
    <div class="card mb-4 shadow-sm">
        <div class="card-header bg-dark text-white"><i class="fa fa-truck"></i> Dispatch Report</div>
        <div class="card-body p-0">
            <table class="table table-bordered table-striped mb-0">
                <thead class="bg-secondary text-white">
                    <tr><th>Product</th><th>SKU</th><th>Quantity</th><th>Recipient</th><th>Dispatched By</th><th>Dispatched At</th></tr>
                </thead>
                <tbody>
                    <?php while($d=$dispatch_report->fetch_assoc()): ?>
                        <tr>
                            <td><?= htmlspecialchars($d['product_name']) ?></td>
                            <td><?= htmlspecialchars($d['sku']) ?></td>
                            <td><?= $d['quantity'] ?></td>
                            <td><?= htmlspecialchars($d['recipient']) ?></td>
                            <td><?= htmlspecialchars($d['dispatched_by_name']) ?></td>
                            <td><?= $d['dispatched_at'] ?></td>
                        </tr>
                    <?php endwhile; ?>
                </tbody>
            </table>
        </div>
    </div>

</div>

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