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

// ================= FILTERS =================
$status_filter = $_GET['status'] ?? '';
$guest_filter = $_GET['guest'] ?? '';
$room_filter = $_GET['room'] ?? '';
$from_date = $_GET['from'] ?? '';
$to_date = $_GET['to'] ?? '';
$sort_by = $_GET['sort_by'] ?? 'i.id';
$sort_order = $_GET['sort_order'] ?? 'DESC';

// Allowed sorting columns
$allowed_sort = ['i.id','i.invoice_number','total_amount','i.status','balance','checkout_date'];
if(!in_array($sort_by,$allowed_sort)) $sort_by = 'i.id';
$sort_order = strtoupper($sort_order)=='ASC'?'ASC':'DESC';

// ================= GET DROPDOWN OPTIONS =================
$status_options = ['Paid','Unpaid','Partially Paid'];

// Guests dropdown
$guests_res = $conn->query("SELECT id, name FROM guests ORDER BY name ASC");
$guest_options = [];
while($g = $guests_res->fetch_assoc()) $guest_options[$g['id']] = $g['name'];

// Rooms dropdown
$rooms_res = $conn->query("SELECT id, room_number FROM rooms ORDER BY room_number ASC");
$room_options = [];
while($r = $rooms_res->fetch_assoc()) $room_options[$r['id']] = $r['room_number'];

// ================= BUILD WHERE CLAUSE =================
$where = [];
if($status_filter) $where[] = "i.status='".$conn->real_escape_string($status_filter)."'";
if($guest_filter) $where[] = "g.id=".$conn->real_escape_string($guest_filter);
if($room_filter) $where[] = "rm.id=".$conn->real_escape_string($room_filter);
if($from_date) $where[] = "s.checkin_date >= '".$conn->real_escape_string($from_date)."'";
if($to_date) $where[] = "s.checkout_date <= '".$conn->real_escape_string($to_date)."'";

$where_sql = count($where) ? "WHERE ".implode(" AND ", $where) : "";

// ================= FETCH INVOICES =================
$query = "SELECT i.*, g.name AS guest_name, s.room_id, s.days, s.checkin_date, s.checkout_date, rm.price AS room_price
          FROM invoices i
          JOIN stays s ON i.stay_id = s.id
          JOIN rooms rm ON s.room_id = rm.id
          JOIN guests g ON i.guest_id = g.id
          $where_sql
          ORDER BY $sort_by $sort_order";
$invoices = $conn->query($query);

// ================= TOTAL SUMMARIES =================
$summary_query = "SELECT 
                    COUNT(*) AS total_invoices,
                    SUM(CASE WHEN i.status='Paid' THEN i.total_amount ELSE 0 END) AS paid_total,
                    SUM(CASE WHEN i.status='Unpaid' THEN i.total_amount ELSE 0 END) AS unpaid_total,
                    SUM(CASE WHEN i.status='Partially Paid' THEN i.total_amount ELSE 0 END) AS partial_total,
                    SUM(i.total_amount) AS total_revenue
                  FROM invoices i
                  JOIN stays s ON i.stay_id=s.id
                  JOIN guests g ON i.guest_id=g.id
                  JOIN rooms rm ON s.room_id=rm.id
                  $where_sql";
$summary = $conn->query($summary_query)->fetch_assoc();

$vat_rate = 0.0; 
$tourism_rate = 0.0; 
?>

<div class="container mt-3">
<h4>Invoices(Rooms-Restaurant-Lounge)</h4>

<!-- FILTER FORM -->
<form method="GET" class="row mb-3 g-2">
    <div class="col-md-2">
        <label>Status</label>
        <select name="status" class="form-control">
            <option value="">All</option>
            <?php foreach($status_options as $s): ?>
                <option value="<?= $s ?>" <?= $status_filter==$s?'selected':'' ?>><?= $s ?></option>
            <?php endforeach; ?>
        </select>
    </div>

    <div class="col-md-3">
        <label>Guest</label>
        <select name="guest" class="form-control">
            <option value="">All Guests</option>
            <?php foreach($guest_options as $id=>$name): ?>
                <option value="<?= $id ?>" <?= $guest_filter==$id?'selected':'' ?>><?= htmlspecialchars($name) ?></option>
            <?php endforeach; ?>
        </select>
    </div>

    <div class="col-md-2">
        <label>Room #</label>
        <select name="room" class="form-control">
            <option value="">All Rooms</option>
            <?php foreach($room_options as $id=>$number): ?>
                <option value="<?= $id ?>" <?= $room_filter==$id?'selected':'' ?>><?= htmlspecialchars($number) ?></option>
            <?php endforeach; ?>
        </select>
    </div>

    <div class="col-md-2">
        <label>From</label>
        <input type="date" name="from" class="form-control" value="<?= htmlspecialchars($from_date) ?>">
    </div>

    <div class="col-md-2">
        <label>To</label>
        <input type="date" name="to" class="form-control" value="<?= htmlspecialchars($to_date) ?>">
    </div>

    <div class="col-md-1 d-flex align-items-end">
        <button class="btn btn-primary form-control">Filter</button>
    </div>
</form>

<!-- SUMMARY -->
<div class="mb-3">
    <strong>Summary:</strong> 
    Total Invoices: <?= $summary['total_invoices'] ?> | 
    Paid: <?= number_format($summary['paid_total'],2) ?> | 
    Unpaid: <?= number_format($summary['unpaid_total'],2) ?> | 
    Partial: <?= number_format($summary['partial_total'],2) ?> | 
    Total Revenue: <?= number_format($summary['total_revenue'],2) ?>
</div>

<!-- INVOICE TABLE -->
<?php $query_string = $_SERVER['QUERY_STRING']; ?>

<div class="mb-3">

<a href="export_excel_invoices.php?<?= $query_string ?>" class="btn btn-success">
<i class="fa fa-file-excel"></i> Export Excel
</a>

<a href="export_pdf_invoices.php?<?= $query_string ?>" class="btn btn-danger" target="_blank">
<i class="fa fa-file-pdf"></i> Export PDF
</a>

</div>
<table class="table table-bordered table-striped">
    <thead class="table-dark">
        <tr>
            <th>ID</th>
            <th>Invoice #</th>
            <th>Guest</th>
              <th>CheckIn</th>
            <th>Room Cost</th>
            <th>Services</th>
            <th>Total</th>
              <th>Paid</th>
            <th>Status</th>
            <th>Bal.</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        <?php if($invoices->num_rows==0): ?>
        <tr>
            <td colspan="9" class="text-center">No invoices found</td>
        </tr>
        <?php else: ?>
        <?php while($inv = $invoices->fetch_assoc()):
            $room_cost = $inv['room_price'] * $inv['days'];
            $service_total = 0; $service_details=[];
            $services_res = $conn->query("SELECT name, amount, quantity FROM charges WHERE guest_id={$inv['guest_id']}");
            while($s = $services_res->fetch_assoc()){
                $service_details[] = $s;
                $service_total += ($s['amount'] * $s['quantity']); 
            }
            $subtotal = $room_cost + $service_total;
            $total_amount = $subtotal ;
            $paid_sum = $conn->query("SELECT SUM(amount) as total FROM payments WHERE guest_id={$inv['guest_id']} AND status='Paid'")->fetch_assoc()['total'] ?? 0;
            $balance = max(0, $total_amount - $paid_sum);
            $services_summary = '';
            foreach($service_details as $s) $services_summary .= htmlspecialchars($s['name'])." (".number_format($s['amount'],2).") x ".$s['quantity']."<br>";
        ?>
        <tr class="<?= $balance>0 ? 'table-danger' : 'table-success' ?>">
            <td><?= $inv['id'] ?></td>
            <td><?= $inv['invoice_number'] ?></td>
            <td><?= htmlspecialchars($inv['guest_name']) ?></td>
<td><font size="2"><?= !empty($inv['checkin_date']) ? date('d/m/Y', strtotime($inv['checkin_date'])) : '-' ?>-

<?= !empty($inv['checkout_date']) ? date('d/m/Y', strtotime($inv['checkout_date'])) : '-' ?>
</font>
</td>
            <td><?= number_format($room_cost,2) ?></td>
            <td>
                <a href="#" data-bs-toggle="tooltip" data-bs-html="true" title="<?= $services_summary ?>">
                    <?= number_format($service_total,2) ?>
                </a>
            </td>
           <td><?= number_format($total_amount,2) ?></td>
<td class="text-success fw-bold"><?= number_format($paid_sum,2) ?></td>
<?php
if($paid_sum <= 0){
    $status = "Unpaid";
}elseif($paid_sum < $total_amount){
    $status = "Partially Paid";
}else{
    $status = "Paid";
}
?>

<td>
<span class="badge bg-<?= $status=='Paid'?'success':($status=='Partially Paid'?'warning':'danger') ?>">
<?= $status ?>
</span>
</td>
<td class="<?= $balance>0 ? 'text-danger fw-bold' : 'text-success fw-bold' ?>">
            <td>
                <a href="invoice.php?stay_id=<?= $inv['stay_id'] ?>" class="btn btn-sm btn-primary" target="_blank">
                    <i class="fa fa-file-invoice-dollar"></i> Invoice
                </a>
                <?php if($balance>0): ?>
                    <a href="receive_payment.php?guest_id=<?= $inv['guest_id'] ?>" class="btn btn-sm btn-success">
                        <i class="fa fa-money-bill-wave"></i> Receive Payment
                    </a>
                <?php else: ?>
                    <a href="receipt_thermal.php?stay_id=<?= $inv['stay_id'] ?>" target="_blank" class="btn btn-sm btn-secondary">
                        <i class="fa fa-receipt"></i> Receipt
                    </a>
                <?php endif; ?>
            </td>
        </tr>
        <?php endwhile; ?>
        <?php endif; ?>
    </tbody>
</table>

<script>
document.addEventListener('DOMContentLoaded', function(){
    var tooltipTriggerList = [].slice.call(document.querySelectorAll('[data-bs-toggle="tooltip"]'))
    tooltipTriggerList.map(function (tooltipTriggerEl) {
        return new bootstrap.Tooltip(tooltipTriggerEl)
    })
});
</script>
<style>
.hr-nice {
    border: none;
    height: 2px;
    background: linear-gradient(to right, #28a745, #a8e6b0);
    margin: 20px 0;
    border-radius: 5px;
}
</style>

<hr class="hr-nice">

<div class="container mt-3">
<h4>Invoices(Conference Invoices)</h4>
 <table class="table table-striped table-bordered mt-3">
        <thead class="table-dark">
            <tr>
                <th>#</th>
                <th>Invoice Number</th>
                <th>Guest Name</th>
                <th>Function Date</th>
                <th>Total Amount (MWK)</th>
                <th>Status</th>
                <th>Actions</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $invoices = $conn->query("
                SELECT i.*, g.name AS guest_name,cb.client_name, cb.booking_date
                FROM invoices i
                LEFT JOIN conference_bookings cb ON i.stay_id = cb.id
                LEFT JOIN guests g ON i.guest_id = g.id WHERE i.conf <> 2
                ORDER BY i.created_at DESC
            ");

            $count = 1;
            while($inv = $invoices->fetch_assoc()):
            ?>
            <tr>
                <td><?= $count++ ?></td>
                <td><?= htmlspecialchars($inv['invoice_number']) ?></td>
                <td><?= htmlspecialchars($inv['client_name'] ?? '-') ?></td>
                <td><?= isset($inv['booking_date']) ? date('d/m/Y', strtotime($inv['booking_date'])) : '-' ?></td>
                <td><?= number_format($inv['total_amount'],2) ?></td>
                <td>
                    <span class="badge bg-<?= $inv['status']=='Paid'?'success':($inv['status']=='Partially Paid'?'warning':'danger') ?>">
                        <?= $inv['status'] ?>
                    </span>
                </td>
 <td>
    <!-- View Invoice -->
    <a href="view_invoice.php?invoice_id=<?= $inv['id'] ?>" class="btn btn-sm btn-primary">
        <i class="fa fa-eye"></i> View
    </a>

   

    <!-- Receive Payment -->
      <?php if(!empty($inv['guest_id']) && $inv['status'] != 'Paid'): ?>
    <a href="receive_pay_conf.php?invoice_id=<?= $inv['id'] ?>" class="btn btn-sm btn-success">
        <i class="fa fa-money-bill-wave"></i> Receive Payment
    </a>
    <?php endif; ?>

    <!-- Print Receipt (only for Paid invoices) -->
    <?php if($inv['status']=='Paid'): ?>
    <a href="receipt_cthermal.php?invoice_id=<?= $inv['id'] ?>" target="_blank" class="btn btn-sm btn-warning">
        <i class="fa fa-receipt"></i> Print Receipt
    </a>
    <?php endif; ?>
</td>



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

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