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

// ================= FILTERS =================
$from_date = $_GET['from'] ?? '';
$to_date   = $_GET['to'] ?? '';
$method    = $_GET['method'] ?? '';
$invoice_status = $_GET['invoice_status'] ?? '';
$guest_id  = $_GET['guest_id'] ?? '';
$search    = $_GET['search'] ?? '';

// ================= BUILD WHERE =================
$where = [];

if($from_date){
    $where[] = "DATE(p.payment_date) >= '".$conn->real_escape_string($from_date)."'";
}
if($to_date){
    $where[] = "DATE(p.payment_date) <= '".$conn->real_escape_string($to_date)."'";
}
if($method){
    $where[] = "p.method = '".$conn->real_escape_string($method)."'";
}
if($invoice_status){
    $where[] = "i.status = '".$conn->real_escape_string($invoice_status)."'";
}
if($guest_id){
    $where[] = "p.guest_id = '".$conn->real_escape_string($guest_id)."'";
}
if($search){
    $s = $conn->real_escape_string($search);
    $where[] = "(i.invoice_number LIKE '%$s%' OR p.user_id LIKE '%$s%')";
}

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

// ================= FETCH PAYMENTS =================
$query = "
SELECT 
    i.invoice_number,
    i.guest_id,
    i.total_amount AS invoice_total,
    i.status AS invoice_status,
    i.issue_date,
    p.amount AS payment_amount,
    p.method AS payment_method,
    p.payment_date,
    p.user_id AS received_by
FROM payments p
INNER JOIN invoices i ON i.guest_id = p.guest_id
$where_sql
ORDER BY p.payment_date DESC
";

$result = $conn->query($query);

// ================= EXPORT =================
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=payments_against_invoices_" . date('Ymd_His') . ".xls");
header("Pragma: no-cache");
header("Expires: 0");

// ================= PRINT TABLE =================
echo "<table border='1'>";
echo "<tr>
        <th>Invoice Number</th>
        <th>Guest ID</th>
        <th>Invoice Total</th>
        <th>Invoice Status</th>
        <th>Issue Date</th>
        <th>Payment Amount</th>
        <th>Payment Method</th>
        <th>Payment Date</th>
        <th>Received By</th>
      </tr>";

if($result && $result->num_rows){
    while($row = $result->fetch_assoc()){
        echo "<tr>
                <td>".$row['invoice_number']."</td>
                <td>".$row['guest_id']."</td>
                <td>".$row['invoice_total']."</td>
                <td>".$row['invoice_status']."</td>
                <td>".$row['issue_date']."</td>
                <td>".$row['payment_amount']."</td>
                <td>".$row['payment_method']."</td>
                <td>".$row['payment_date']."</td>
                <td>".$row['received_by']."</td>
              </tr>";
    }
}else{
    echo "<tr><td colspan='9'>No records found</td></tr>";
}

// ================= TOTALS =================
$summary = $conn->query("
SELECT 
    IFNULL(SUM(p.amount),0) AS total_payments,
    IFNULL(SUM(i.total_amount),0) AS total_invoices
FROM payments p
INNER JOIN invoices i ON i.guest_id = p.guest_id
$where_sql
")->fetch_assoc();

echo "<tr>
        <th colspan='5'>TOTAL</th>
        <th>".$summary['total_payments']."</th>
        <th colspan='3'></th>
      </tr>";

echo "</table>";
exit;