<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';
$username = $_SESSION['username'] ?? 'Guest';
// Enable error reporting (remove in production)
error_reporting(E_ALL);
ini_set('display_errors', 1);

// ===== DASHBOARD QUERIES =====

// Total reservations
$resReservations = $conn->query("SELECT COUNT(*) AS total FROM reservations");
$totalReservations = $resReservations->fetch_assoc()['total'] ?? 0;

// Active stays
$resStays = $conn->query("
    SELECT COUNT(*) AS total 
    FROM stays 
    WHERE checkin_date <= NOW()
    AND checkout_date >= NOW()
");
$totalStays = $resStays->fetch_assoc()['total'] ?? 0;

// Total revenue from payments
$resRevenue = $conn->query("SELECT IFNULL(SUM(amount),0) AS total FROM payments");
$totalRevenue = $resRevenue->fetch_assoc()['total'] ?? 0;

// Total sales
$resSales = $conn->query("SELECT IFNULL(SUM(total),0) AS total FROM sales");
$totalSales = $resSales->fetch_assoc()['total'] ?? 0;

// Revenue breakdown by type
$resBreakdown = $conn->query("
    SELECT type, IFNULL(SUM(amount),0) AS total
    FROM payments
    GROUP BY type
");
$revenueBreakdown = [];
while($row = $resBreakdown->fetch_assoc()){
    $revenueBreakdown[$row['type']] = $row['total'];
}

// Top-paying guests (5)
$topGuests = $conn->query("
    SELECT g.name, IFNULL(SUM(p.amount),0) AS total_paid
    FROM payments p
    JOIN guests g ON g.id = p.guest_id
    GROUP BY p.guest_id
    ORDER BY total_paid DESC
    LIMIT 5
");

// Recent reservations
$recentReservations = $conn->query("
    SELECT r.id, g.name, r.date_in, r.date_out
    FROM reservations r
    JOIN guests g ON g.id = r.guest_id
    ORDER BY r.created_at DESC
    LIMIT 5
");

// Recent transactions (payments + sales)
$recent = $conn->query("
    SELECT 'Payment' AS type, amount, payment_date AS date
    FROM payments
    UNION ALL
    SELECT 'Sale' AS type, total AS amount, sale_date AS date
    FROM sales
    ORDER BY date DESC
    LIMIT 8
");
?>

<!DOCTYPE html>
<html>
<head>
    <title>Back Office Dashboard</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">

    <style>
        body{font-family: Arial, sans-serif; background:#f4f6f9; margin:0; padding:0;}
        .header{background:#1b4d32; color:#fff; padding:15px; text-align:center; font-size:22px; font-weight:bold;}
        .container{padding:20px;}
        .cards{display:grid; grid-template-columns: repeat(auto-fit,minmax(180px,1fr)); gap:20px; margin-bottom:30px;}
        .card{background:#fff; padding:15px; border-radius:10px; box-shadow:0 2px 8px rgba(0,0,0,0.08); text-align:center;}
        .card h2{margin:0; font-size:24px; color:#1b4d32;}
        .card p{margin-top:5px; color:#777; font-size:0.9rem;}
        .card i{font-size:24px; margin-bottom:5px; color:#1b4d32;}
        table{width:100%; border-collapse:collapse; background:#fff; border-radius:8px; overflow:hidden; margin-bottom:30px;}
        table th{background:#1b4d32; color:#fff; padding:10px; text-align:left;}
        table td{padding:10px; border-bottom:1px solid #eee;}
        .amount{font-weight:bold; color:#1b4d32;}
        @media(max-width:600px){.header{font-size:18px;}}
        .section-title{margin-bottom:10px; font-size:18px; color:#1b4d32;}
    </style>
</head>
<body>
<div class="welcome-msg">
        <i class="fa fa-user-circle"></i> Welcome, <strong><?= htmlspecialchars($username) ?></strong>!
    </div>

<div class="header">Back Office Dashboard</div><br/>
 <center><a href="sales_report.php" class="btn btn-sm btn-primary">Sales Reports</a>


<a href="bar_tab_pos.php" class="btn btn-sm btn-primary">Tabs</a>



</center>
<div class="container">

    <!-- SUMMARY CARDS -->
    <div class="cards">
        <div class="card"><i class="fa fa-calendar-check"></i><h2><?= number_format($totalReservations); ?></h2><p>Total Reservations</p></div>
        <div class="card"><i class="fa fa-bed"></i><h2><?= number_format($totalStays); ?></h2><p>Active Stays</p></div>
        <div class="card"><i class="fa fa-money-bill-wave"></i><h2>MWK <?= number_format($totalRevenue,2); ?></h2><p>Total Revenue</p></div>
        <div class="card"><i class="fa fa-shopping-cart"></i><h2>MWK <?= number_format($totalSales,2); ?></h2><p>Total Sales</p></div>
    </div>

    <!-- REVENUE BREAKDOWN -->
    <div class="section-title">Revenue Breakdown by Type</div>
    <table>
        <thead>
            <tr>
                <th>Type</th>
                <th>Total (MK)</th>
            </tr>
        </thead>
        <tbody>
        <?php foreach($revenueBreakdown as $type => $amount): ?>
            <tr>
                <td><?= ucfirst($type) ?></td>
                <td class="amount">MWK <?= number_format($amount,2) ?></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table>

    <!-- TOP PAYING GUESTS -->
    <div class="section-title">Top 5 Paying Guests</div>
    <table>
        <thead>
            <tr>
                <th>Guest</th>
                <th>Total Paid (MK)</th>
            </tr>
        </thead>
        <tbody>
        <?php if($topGuests && $topGuests->num_rows > 0): ?>
            <?php while($g = $topGuests->fetch_assoc()): ?>
            <tr>
                <td><?= htmlspecialchars($g['name']) ?></td>
                <td class="amount">MWK <?= number_format($g['total_paid'],2) ?></td>
            </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="2" style="text-align:center;">No data</td></tr>
        <?php endif; ?>
        </tbody>
    </table>

    <!-- RECENT RESERVATIONS -->
    <div class="section-title">Recent Reservations</div>
    <table>
        <thead>
            <tr>
                <th>Reservation ID</th>
                <th>Guest</th>
                <th>Check-in</th>
                <th>Check-out</th>
            </tr>
        </thead>
        <tbody>
        <?php if($recentReservations && $recentReservations->num_rows > 0): ?>
            <?php while($r = $recentReservations->fetch_assoc()): ?>
            <tr>
                <td><?= $r['id'] ?></td>
                <td><?= htmlspecialchars($r['name']) ?></td>
                <td><?= date('d M Y', strtotime($r['date_in'])) ?></td>
                <td><?= date('d M Y', strtotime($r['date_out'])) ?></td>
            </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="4" style="text-align:center;">No reservations</td></tr>
        <?php endif; ?>
        </tbody>
    </table>

    <!-- RECENT TRANSACTIONS -->
    <div class="section-title">Recent Transactions</div>
    <table>
        <thead>
            <tr>
                <th>Type</th>
                <th>Amount (MK)</th>
                <th>Date</th>
            </tr>
        </thead>
        <tbody>
        <?php if ($recent && $recent->num_rows > 0): ?>
            <?php while($row = $recent->fetch_assoc()): ?>
            <tr>
                <td><?= htmlspecialchars($row['type']); ?></td>
                <td class="amount"><?= number_format($row['amount'],2); ?></td>
                <td><?= date('d M Y H:i', strtotime($row['date'])); ?></td>
            </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="3" style="text-align:center;">No transactions found</td></tr>
        <?php endif; ?>
        </tbody>
    </table>

</div>

</body>
</html>
