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

$username = $_SESSION['username'] ?? 'Guest';

// ================= DATE RANGE FILTER =================
$default_from = date('Y-m-01'); // first day of month
$default_to   = date('Y-m-d');

$from_date = $_GET['from'] ?? $default_from;
$to_date   = $_GET['to'] ?? $default_to;

$where_date = "";
if(isset($_GET['from']) && isset($_GET['to'])){
    $from = $conn->real_escape_string($from_date);
    $to   = $conn->real_escape_string($to_date);
    $where_date = " AND s.checkout_date BETWEEN '$from' AND '$to' ";
}

// ================= FETCH STAYS =================
$stays = $conn->query("
    SELECT s.*, r.room_number, r.room_type, IFNULL(r.price,0) AS room_price,
           g.name AS guest_name
    FROM stays s
    JOIN rooms r ON s.room_id=r.id
    JOIN guests g ON s.guest_id=g.id
    WHERE s.checkout_date IS NOT NULL $where_date
    ORDER BY s.checkout_date DESC
");

// ================= ANALYTICS =================
$analytics = [
    'total_rooms_occupied' => 0,
    'total_days' => 0,
    'total_room_cost' => 0,
    'total_paid' => 0,
    'total_balance' => 0,
    'discrepancies' => 0
];

$room_stats = []; 
$daily_stats = []; // For line chart: check-ins vs check-outs
$stay_data = [];

while($s = $stays->fetch_assoc()){
    $days = $s['days'];
    $room_cost = $s['room_price'] * $days;
    $paid = floatval($conn->query("SELECT COALESCE(SUM(amount),0) AS total_paid FROM payments WHERE guest_id={$s['guest_id']} AND status='Paid'")->fetch_assoc()['total_paid']);
    $balance = $room_cost - $paid;

    // Discrepancy detection
    $discrepancy = $balance != 0 ? 1 : 0;
    $analytics['discrepancies'] += $discrepancy;

    // Aggregate totals
    $analytics['total_rooms_occupied']++;
    $analytics['total_days'] += $days;
    $analytics['total_room_cost'] += $room_cost;
    $analytics['total_paid'] += $paid;
    $analytics['total_balance'] += $balance;

    // Room-wise stats
    if(!isset($room_stats[$s['room_number']])){
        $room_stats[$s['room_number']] = ['days'=>0,'total_cost'=>0,'total_paid'=>0,'balance'=>0];
    }
    $room_stats[$s['room_number']]['days'] += $days;
    $room_stats[$s['room_number']]['total_cost'] += $room_cost;
    $room_stats[$s['room_number']]['total_paid'] += $paid;
    $room_stats[$s['room_number']]['balance'] += $balance;

    // Daily stats
    $checkin_day = date('Y-m-d', strtotime($s['checkin_date']));
    $checkout_day = date('Y-m-d', strtotime($s['checkout_date']));
    if(!isset($daily_stats[$checkin_day])) $daily_stats[$checkin_day] = ['checkin'=>0,'checkout'=>0];
    if(!isset($daily_stats[$checkout_day])) $daily_stats[$checkout_day] = ['checkin'=>0,'checkout'=>0];
    $daily_stats[$checkin_day]['checkin']++;
    $daily_stats[$checkout_day]['checkout']++;

    $stay_data[] = [
        'guest_name'=>$s['guest_name'],
        'room_number'=>$s['room_number'],
        'room_type'=>$s['room_type'],
        'days'=>$days,
        'room_cost'=>$room_cost,
        'paid'=>$paid,
        'balance'=>$balance,
        'checkin'=>$s['checkin_date'],
        'checkout'=>$s['checkout_date'],
        'discrepancy'=>$discrepancy
    ];
}

// Prepare chart data
$chart_labels = array_keys($daily_stats);
$chart_checkins = array_map(fn($d)=>$d['checkin'],$daily_stats);
$chart_checkouts = array_map(fn($d)=>$d['checkout'],$daily_stats);
?>

<div class="container mt-4">
     <button class="btn btn-secondary btn-custom" onclick="history.back()">
        <i class="fa fa-arrow-left"></i> Back
    </button>
    <h3 class="text-center">📊 Hotel Analytics & Discrepancy Report</h3>
    <hr>

    <!-- Date Filter -->
    <form method="get" class="row g-2 mb-3">
        <div class="col-md-3">
            <input type="date" name="from" class="form-control" value="<?= htmlspecialchars($from_date) ?>">
        </div>
        <div class="col-md-3">
            <input type="date" name="to" class="form-control" value="<?= htmlspecialchars($to_date) ?>">
        </div>
        <div class="col-md-2">
            <button type="submit" class="btn btn-primary"><i class="fa fa-filter"></i> Filter</button>
        </div>
        <div class="col-md-2">
            <a href="export_analytics_excel.php?from=<?= $from_date ?>&to=<?= $to_date ?>" class="btn btn-success">
                <i class="fa fa-file-excel"></i> Export Excel
            </a>
        </div>
        <div class="col-md-2">
            <a href="export_analytics_pdf.php?from=<?= $from_date ?>&to=<?= $to_date ?>" class="btn btn-danger">
                <i class="fa fa-file-pdf"></i> Export PDF
            </a>
        </div>
    </form>

    <!-- Summary Cards -->
    <div class="row mb-4">
        <div class="col-md-2"><div class="alert alert-success text-center"><strong>Total Rooms Occupied:</strong><br><?= $analytics['total_rooms_occupied'] ?></div></div>
        <div class="col-md-2"><div class="alert alert-info text-center"><strong>Total Stay Days:</strong><br><?= $analytics['total_days'] ?></div></div>
        <div class="col-md-2"><div class="alert alert-warning text-center"><strong>Total Room Cost:</strong><br><?= number_format($analytics['total_room_cost'],2) ?> MK</div></div>
        <div class="col-md-2"><div class="alert alert-primary text-center"><strong>Total Paid:</strong><br><?= number_format($analytics['total_paid'],2) ?> MK</div></div>
        <div class="col-md-2"><div class="alert alert-danger text-center"><strong>Total Balance:</strong><br><?= number_format($analytics['total_balance'],2) ?> MK</div></div>
        <div class="col-md-2"><div class="alert alert-dark text-center"><strong>Discrepancies:</strong><br><?= $analytics['discrepancies'] ?></div></div>
    </div>

    <!-- Room-wise Summary -->
    <h5>🏨 Room-wise Summary</h5>
    <table class="table table-bordered table-striped">
        <thead class="bg-secondary text-white">
            <tr>
                <th>Room Number</th>
                <th>Total Days</th>
                <th>Total Room Cost</th>
                <th>Total Paid</th>
                <th>Balance</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($room_stats as $room => $r): ?>
            <tr class="<?= $r['balance']>0?'table-warning':'table-success' ?>">
                <td><?= $room ?></td>
                <td><?= $r['days'] ?></td>
                <td><?= number_format($r['total_cost'],2) ?></td>
                <td><?= number_format($r['total_paid'],2) ?></td>
                <td><?= number_format($r['balance'],2) ?></td>
                <td>
                    <?php if($r['balance']>0): ?>
                        <a href="reconcile_room.php?room=<?= $room ?>&from=<?= $from_date ?>&to=<?= $to_date ?>" class="btn btn-sm btn-danger">
                            Reconcile
                        </a>
                    <?php else: ?>
                        <span class="text-success">OK</span>
                    <?php endif; ?>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

    <!-- Line Chart for Check-ins vs Check-outs -->
    <h5 class="mt-4"><i class="fa fa-chart-line"></i> Daily Check-ins vs Check-outs</h5>
    <canvas id="checkinChart" height="150"></canvas>

    <!-- Detailed Stays -->
    <h5 class="mt-4">📝 Detailed Stays</h5>
    <div class="table-responsive">
        <table class="table table-bordered table-striped table-sm">
            <thead class="bg-dark text-white sticky-top">
                <tr>
                    <th>#</th>
                    <th>Guest</th>
                    <th>Room</th>
                    <th>Room Type</th>
                    <th>Days</th>
                    <th>Room Cost</th>
                    <th>Paid</th>
                    <th>Balance</th>
                    <th>Check-In</th>
                    <th>Check-Out</th>
                    <th>Discrepancy</th>
                </tr>
            </thead>
            <tbody>
                <?php $i=1; foreach($stay_data as $s): ?>
                <tr class="<?= $s['balance']>0?'table-warning':'table-success' ?>">
                    <td><?= $i ?></td>
                    <td><?= htmlspecialchars($s['guest_name']) ?></td>
                    <td><?= $s['room_number'] ?></td>
                    <td><?= $s['room_type'] ?></td>
                    <td><?= $s['days'] ?></td>
                    <td><?= number_format($s['room_cost'],2) ?></td>
                    <td><?= number_format($s['paid'],2) ?></td>
                    <td><?= number_format($s['balance'],2) ?></td>
                    <td><?= date('Y-m-d', strtotime($s['checkin'])) ?></td>
                    <td><?= date('Y-m-d', strtotime($s['checkout'])) ?></td>
                    <td><?= $s['discrepancy']?'⚠️':'✔️' ?></td>
                </tr>
                <?php $i++; endforeach; ?>
            </tbody>
        </table>
    </div>
</div>

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
const ctx = document.getElementById('checkinChart').getContext('2d');
const checkinChart = new Chart(ctx, {
    type: 'line',
    data: {
        labels: <?= json_encode($chart_labels) ?>,
        datasets: [
            {
                label: 'Check-ins',
                data: <?= json_encode($chart_checkins) ?>,
                borderColor: 'rgba(75, 192, 192, 1)',
                backgroundColor: 'rgba(75, 192, 192, 0.2)',
                tension: 0.3
            },
            {
                label: 'Check-outs',
                data: <?= json_encode($chart_checkouts) ?>,
                borderColor: 'rgba(255, 99, 132,1)',
                backgroundColor: 'rgba(255, 99, 132, 0.2)',
                tension: 0.3
            }
        ]
    },
    options: {
        responsive:true,
        plugins:{ legend:{ position:'top' } },
        scales: { y: { beginAtZero:true, title:{ display:true, text:'Number of Guests' } } }
    }
});
</script>

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