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

// Default to today if no filter
$start_date = $_GET['start_date'] ?? date('Y-m-d');
$end_date = $_GET['end_date'] ?? date('Y-m-d');
$product_id = $_GET['product_id'] ?? '';
$department_id = $_GET['department_id'] ?? '';

// Default department to Bar if not set
if(!$department_id) $department_id = 1; // Bar

// Fetch products and departments for filter dropdowns
$products_list = $conn->query("SELECT id, name FROM products ORDER BY name ASC");
$departments_list = $conn->query("SELECT id, name FROM departments ORDER BY name ASC");

// Build WHERE clause
$where = "WHERE pr.created_at BETWEEN '".$conn->real_escape_string($start_date)." 00:00:00' 
          AND '".$conn->real_escape_string($end_date)." 23:59:59'";
if($product_id) $where .= " AND pr.product_id=".intval($product_id);
if($department_id) $where .= " AND ps.department_id=".intval($department_id);

// Fetch report
$report = $conn->query("
    SELECT pr.*, p.name AS product_name, p.sku, p.buying_price, u.username, ps.department_id
    FROM bar_purchase_requests pr
    JOIN products p ON pr.product_id=p.id
    JOIN users u ON pr.requested_by=u.id
    JOIN product_stock ps ON ps.product_id=p.id
    $where
    ORDER BY pr.created_at DESC
");

// Initialize totals for cards
$total_amount = 0;
$total_qty = 0;
$total_requests = $report->num_rows;

// Pre-calculate totals
$report_data = [];
while($r = $report->fetch_assoc()){
    $amount = $r['buying_price'] * $r['quantity'];
    $total_amount += $amount;
    $total_qty += $r['quantity'];
    $report_data[] = $r;
}
?>

<div class="container mt-4">
    <h4 class="mb-3">🍸 Bar Purchase Request Report</h4>

    <!-- Filter Form -->
    <form method="GET" class="row g-3 mb-4">
        <div class="col-md-3">
            <label>Start Date</label>
            <input type="date" name="start_date" class="form-control" value="<?= htmlspecialchars($start_date) ?>">
        </div>
        <div class="col-md-3">
            <label>End Date</label>
            <input type="date" name="end_date" class="form-control" value="<?= htmlspecialchars($end_date) ?>">
        </div>
        <div class="col-md-3">
            <label>Product</label>
            <select name="product_id" class="form-control">
                <option value="">All Products</option>
                <?php while($p = $products_list->fetch_assoc()): ?>
                    <option value="<?= $p['id'] ?>" <?= $p['id']==$product_id?'selected':'' ?>><?= htmlspecialchars($p['name']) ?></option>
                <?php endwhile; ?>
            </select>
        </div>
        <div class="col-md-3">
            <label>Department</label>
            <select name="department_id" class="form-control">
                <option value="">All Departments</option>
                <?php while($d = $departments_list->fetch_assoc()): ?>
                    <option value="<?= $d['id'] ?>" <?= $d['id']==$department_id?'selected':'' ?>><?= htmlspecialchars($d['name']) ?></option>
                <?php endwhile; ?>
            </select>
        </div>
        <div class="col-md-12 mt-2">
            <button class="btn btn-primary">Filter Report</button>
            <a href="purchase_request_report_bar.php" class="btn btn-secondary">Reset</a>
        </div>
    </form>

    <!-- Quick Stats Cards -->
    <div class="row mb-4">
        <div class="col-md-4">
            <div class="card text-white bg-info mb-3">
                <div class="card-body">
                    <h5 class="card-title">Total Requests</h5>
                    <p class="card-text fs-4"><?= $total_requests ?></p>
                </div>
            </div>
        </div>
        <div class="col-md-4">
            <div class="card text-white bg-success mb-3">
                <div class="card-body">
                    <h5 class="card-title">Total Quantity</h5>
                    <p class="card-text fs-4"><?= $total_qty ?></p>
                </div>
            </div>
        </div>
        <div class="col-md-4">
            <div class="card text-white bg-warning mb-3">
                <div class="card-body">
                    <h5 class="card-title">Total Amount</h5>
                    <p class="card-text fs-4"><?= number_format($total_amount,2) ?></p>
                </div>
            </div>
        </div>
    </div>

    <!-- Report Table -->
    <table class="table table-bordered table-striped">
        <thead class="bg-dark text-white">
            <tr>
                <th>Date</th>
                <th>SKU</th>
                <th>Product</th>
                <th>Buying Price</th>
                <th>Quantity</th>
                <th>Amount</th>
                <th>Requested By</th>
               
            </tr>
        </thead>
        <tbody>
        <?php foreach($report_data as $r): 
            $amount = $r['buying_price'] * $r['quantity'];
        ?>
            <tr>
                <td><?= $r['created_at'] ?></td>
                <td><?= htmlspecialchars($r['sku']) ?></td>
                <td><?= htmlspecialchars($r['product_name']) ?></td>
                <td><?= number_format($r['buying_price'],2) ?></td>
                <td><?= intval($r['quantity']) ?></td>
                <td><?= number_format($amount,2) ?></td>
                <td><?= htmlspecialchars($r['username']) ?></td>
              
            </tr>
        <?php endforeach; ?>
            <tr class="table-success">
                <td colspan="5" class="text-end"><strong>Total Amount:</strong></td>
                <td colspan="3"><strong><?= number_format($total_amount,2) ?></strong></td>
            </tr>
        </tbody>
    </table>

    <button class="btn btn-secondary" onclick="window.print()"><i class="fa fa-print"></i> Print Report</button>
</div>

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