<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

if (!function_exists('h')) {
    function h($value): string { return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8'); }
}
if (!function_exists('money_fmt')) {
    function money_fmt($amount): string { return number_format((float)$amount, 2, '.', ','); }
}
if (!function_exists('xls_cell')) {
    function xls_cell($value, string $align = ''): string {
        $style = $align ? ' style="text-align:' . h($align) . ';"' : '';
        return '<td' . $style . '>' . h((string)$value) . '</td>';
    }
}
if (!function_exists('xls_money')) {
    function xls_money($value): string {
        return '<td style="text-align:right;mso-number-format:\'#,##0.00\';">' . money_fmt($value) . '</td>';
    }
}
if (!function_exists('xls_num')) {
    function xls_num($value): string {
        return '<td style="text-align:right;mso-number-format:\'0\';">' . h((string)(int)$value) . '</td>';
    }
}

$auditDate = trim((string)($_GET['audit_date'] ?? date('Y-m-d')));
if (strtotime($auditDate) === false) $auditDate = date('Y-m-d');
$propertyId = (int)($_GET['property_id'] ?? 0);

$propertyName = 'All Properties';
if ($propertyId > 0) {
    try {
        $stmt = $pdo->prepare("SELECT name, code FROM properties WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $propertyId]);
        $p = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($p) $propertyName = trim((string)$p['name'] . (!empty($p['code']) ? ' (' . $p['code'] . ')' : ''));
    } catch (Throwable $e) {}
}

$summary = [
    'rooms_total' => 0,
    'rooms_occupied' => 0,
    'rooms_vacant' => 0,
    'arrivals_pending' => 0,
    'departures_pending' => 0,
    'dirty_rooms' => 0,
    'ooo_rooms' => 0,
    'open_balance_bookings' => 0,
];

$arrivalsPending = [];
$departuresPending = [];
$openBalanceBookings = [];
$dirtyRooms = [];
$outOfOrderRooms = [];
$errors = [];

$roomPropertySql = '';
$orderPropertySql = '';
$paramsBase = [];
if ($propertyId > 0) {
    $roomPropertySql = " AND r.property_id = ? ";
    $orderPropertySql = " AND o.property_id = ? ";
    $paramsBase[] = $propertyId;
}

try {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM rooms r WHERE 1=1 $roomPropertySql");
    $stmt->execute($paramsBase);
    $summary['rooms_total'] = (int)$stmt->fetchColumn();
} catch (Throwable $e) { $errors[] = 'Rooms Total: ' . $e->getMessage(); }

try {
    $sql = "
        SELECT COUNT(DISTINCT rmg.room_id)
        FROM order_rooming rmg
        INNER JOIN orders o ON o.id = rmg.order_id
        INNER JOIN rooms r ON r.id = rmg.room_id
        WHERE DATE(o.check_in) <= ?
          AND DATE(o.check_out) >= ?
          AND rmg.room_id IS NOT NULL
          $orderPropertySql
    ";
    $params = [$auditDate, $auditDate];
    if ($propertyId > 0) $params[] = $propertyId;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $summary['rooms_occupied'] = (int)$stmt->fetchColumn();
} catch (Throwable $e) { $errors[] = 'Rooms Occupied: ' . $e->getMessage(); }

$summary['rooms_vacant'] = max(0, $summary['rooms_total'] - $summary['rooms_occupied']);

try {
    $sql = "
        SELECT o.*, p.name AS property_name, p.code AS property_code,
               g.firstname, g.lastname,
               CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')) AS agent_name,
               (SELECT COUNT(*) FROM order_rooming r1 WHERE r1.order_id = o.id) AS room_slots_total,
               (SELECT COUNT(*) FROM order_rooming r2 WHERE r2.order_id = o.id AND COALESCE(r2.checked_in_status,0)=1) AS room_slots_checked_in
        FROM orders o
        LEFT JOIN properties p ON p.id = o.property_id
        LEFT JOIN guests g ON g.id = o.guest_id
        LEFT JOIN agents a ON a.id = o.agent_id
        WHERE DATE(o.check_in) = ?
        $orderPropertySql
        ORDER BY p.name ASC, o.id DESC
    ";
    $params = [$auditDate];
    if ($propertyId > 0) $params[] = $propertyId;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $total = (int)($row['room_slots_total'] ?? 0);
        $checked = (int)($row['room_slots_checked_in'] ?? 0);
        if ($total === 0 || $checked < $total) $arrivalsPending[] = $row;
    }
    $summary['arrivals_pending'] = count($arrivalsPending);
} catch (Throwable $e) { $errors[] = 'Arrivals Pending: ' . $e->getMessage(); }

try {
    $sql = "
        SELECT o.*, p.name AS property_name, p.code AS property_code,
               g.firstname, g.lastname,
               CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')) AS agent_name,
               (SELECT COUNT(*) FROM order_rooming r1 WHERE r1.order_id = o.id) AS room_slots_total,
               (SELECT COUNT(*) FROM order_rooming r2 WHERE r2.order_id = o.id AND COALESCE(r2.checked_out_status,0)=1) AS room_slots_checked_out,
               (SELECT COALESCE(SUM(f.balance),0) FROM folios f WHERE f.order_id = o.id) AS booking_balance
        FROM orders o
        LEFT JOIN properties p ON p.id = o.property_id
        LEFT JOIN guests g ON g.id = o.guest_id
        LEFT JOIN agents a ON a.id = o.agent_id
        WHERE DATE(o.check_out) = ?
        $orderPropertySql
        ORDER BY p.name ASC, o.id DESC
    ";
    $params = [$auditDate];
    if ($propertyId > 0) $params[] = $propertyId;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        $total = (int)($row['room_slots_total'] ?? 0);
        $checked = (int)($row['room_slots_checked_out'] ?? 0);
        if ($total === 0 || $checked < $total) $departuresPending[] = $row;
    }
    $summary['departures_pending'] = count($departuresPending);
} catch (Throwable $e) { $errors[] = 'Departures Pending: ' . $e->getMessage(); }

try {
    $sql = "
        SELECT o.*, p.name AS property_name, p.code AS property_code,
               g.firstname, g.lastname,
               CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')) AS agent_name,
               (SELECT COALESCE(SUM(f.total_charges),0) FROM folios f WHERE f.order_id = o.id) AS booking_charges,
               (SELECT COALESCE(SUM(f.total_payments),0) FROM folios f WHERE f.order_id = o.id) AS booking_payments,
               (SELECT COALESCE(SUM(f.balance),0) FROM folios f WHERE f.order_id = o.id) AS booking_balance
        FROM orders o
        LEFT JOIN properties p ON p.id = o.property_id
        LEFT JOIN guests g ON g.id = o.guest_id
        LEFT JOIN agents a ON a.id = o.agent_id
        WHERE DATE(o.check_in) <= ?
          AND DATE(o.check_out) >= ?
          $orderPropertySql
        ORDER BY p.name ASC, o.check_out ASC, o.id DESC
    ";
    $params = [$auditDate, $auditDate];
    if ($propertyId > 0) $params[] = $propertyId;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
        if (abs((float)($row['booking_balance'] ?? 0)) > 0.00001) $openBalanceBookings[] = $row;
    }
    $summary['open_balance_bookings'] = count($openBalanceBookings);
} catch (Throwable $e) { $errors[] = 'Open Balance Bookings: ' . $e->getMessage(); }

try {
    $sql = "
        SELECT r.*, p.name AS property_name, p.code AS property_code, rt.title AS room_type
        FROM rooms r
        LEFT JOIN properties p ON p.id = r.property_id
        LEFT JOIN room_types rt ON rt.id = r.room_type_id
        WHERE COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '') = 'dirty'
        $roomPropertySql
        ORDER BY p.name ASC, r.room_no ASC
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($paramsBase);
    $dirtyRooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $summary['dirty_rooms'] = count($dirtyRooms);
} catch (Throwable $e) { $errors[] = 'Dirty Rooms: ' . $e->getMessage(); }

try {
    $sql = "
        SELECT r.*, p.name AS property_name, p.code AS property_code, rt.title AS room_type
        FROM rooms r
        LEFT JOIN properties p ON p.id = r.property_id
        LEFT JOIN room_types rt ON rt.id = r.room_type_id
        WHERE COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '') IN ('out_of_order','ooo','maintenance','blocked')
        $roomPropertySql
        ORDER BY p.name ASC, r.room_no ASC
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($paramsBase);
    $outOfOrderRooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $summary['ooo_rooms'] = count($outOfOrderRooms);
} catch (Throwable $e) { $errors[] = 'Out Of Order Rooms: ' . $e->getMessage(); }

function row_guest(array $row): string {
    $guest = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
    return $guest !== '' ? $guest : 'Guest';
}
function row_agent(array $row): string {
    $agent = trim((string)($row['agent_name'] ?? ''));
    return $agent !== '' ? $agent : 'Direct';
}
function row_property(array $row): string {
    $label = trim((string)($row['property_name'] ?? ''));
    if (!empty($row['property_code'])) $label .= ($label !== '' ? ' (' . $row['property_code'] . ')' : $row['property_code']);
    return $label ?: '—';
}

$filename = 'night_audit_' . preg_replace('/[^0-9A-Za-z_-]/', '_', $auditDate) . '.xls';
header('Content-Type: application/vnd.ms-excel; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $filename . '"');
header('Pragma: no-cache');
header('Expires: 0');

echo "\xEF\xBB\xBF";
?>
<html>
<head>
<meta charset="UTF-8">
<style>
body{font-family:Arial,sans-serif;font-size:11px;color:#111827;}
table{border-collapse:collapse;width:100%;margin-bottom:18px;}
th{background:#374151;color:#fff;font-weight:bold;border:1px solid #d1d5db;padding:6px;}
td{border:1px solid #d1d5db;padding:5px;vertical-align:top;}
.title{font-size:20px;font-weight:bold;color:#111827;margin-bottom:4px;}
.sub{font-size:12px;color:#4b5563;margin-bottom:12px;}
.section{background:#e5e7eb;color:#111827;font-weight:bold;font-size:14px;}
.metric th{background:#111827;}
.metric td{font-size:15px;font-weight:bold;text-align:right;}
.warning{background:#fef3c7;}
.error{background:#fee2e2;}
</style>
</head>
<body>
<div class="title">Night Audit Report</div>
<div class="sub">Audit Date: <?php echo h($auditDate); ?> | Property: <?php echo h($propertyName); ?> | Generated: <?php echo h(date('Y-m-d H:i:s')); ?></div>

<?php if ($errors): ?>
<table>
<tr><th colspan="2">Warnings / Query Errors</th></tr>
<?php foreach ($errors as $err): ?><tr class="error"><td colspan="2"><?php echo h($err); ?></td></tr><?php endforeach; ?>
</table>
<?php endif; ?>

<table class="metric">
<tr><th colspan="8">Summary</th></tr>
<tr>
    <th>Total Rooms</th><th>Occupied</th><th>Vacant</th><th>Arrivals Pending</th><th>Departures Pending</th><th>Open Balances</th><th>Dirty Rooms</th><th>Out of Order</th>
</tr>
<tr>
    <?php echo xls_num($summary['rooms_total']); ?>
    <?php echo xls_num($summary['rooms_occupied']); ?>
    <?php echo xls_num($summary['rooms_vacant']); ?>
    <?php echo xls_num($summary['arrivals_pending']); ?>
    <?php echo xls_num($summary['departures_pending']); ?>
    <?php echo xls_num($summary['open_balance_bookings']); ?>
    <?php echo xls_num($summary['dirty_rooms']); ?>
    <?php echo xls_num($summary['ooo_rooms']); ?>
</tr>
</table>

<table>
<tr><td class="section" colspan="7">Pending Arrivals</td></tr>
<tr><th>Booking</th><th>Property</th><th>Guest</th><th>Agent</th><th>Rooms</th><th>Checked In</th><th>Check In Date</th></tr>
<?php if (!$arrivalsPending): ?>
<tr><td colspan="7">No pending arrivals.</td></tr>
<?php else: foreach ($arrivalsPending as $row): ?>
<tr>
    <?php echo xls_cell($row['order_no'] ?: ('#' . $row['id'])); ?>
    <?php echo xls_cell(row_property($row)); ?>
    <?php echo xls_cell(row_guest($row)); ?>
    <?php echo xls_cell(row_agent($row)); ?>
    <?php echo xls_num($row['room_slots_total'] ?? 0); ?>
    <?php echo xls_cell((int)($row['room_slots_checked_in'] ?? 0) . '/' . (int)($row['room_slots_total'] ?? 0), 'right'); ?>
    <?php echo xls_cell($row['check_in'] ?? ''); ?>
</tr>
<?php endforeach; endif; ?>
</table>

<table>
<tr><td class="section" colspan="8">Pending Departures</td></tr>
<tr><th>Booking</th><th>Property</th><th>Guest</th><th>Agent</th><th>Checked Out</th><th>Check Out Date</th><th>Balance</th><th>Status</th></tr>
<?php if (!$departuresPending): ?>
<tr><td colspan="8">No pending departures.</td></tr>
<?php else: foreach ($departuresPending as $row): ?>
<tr>
    <?php echo xls_cell($row['order_no'] ?: ('#' . $row['id'])); ?>
    <?php echo xls_cell(row_property($row)); ?>
    <?php echo xls_cell(row_guest($row)); ?>
    <?php echo xls_cell(row_agent($row)); ?>
    <?php echo xls_cell((int)($row['room_slots_checked_out'] ?? 0) . '/' . (int)($row['room_slots_total'] ?? 0), 'right'); ?>
    <?php echo xls_cell($row['check_out'] ?? ''); ?>
    <?php echo xls_money($row['booking_balance'] ?? 0); ?>
    <?php echo xls_cell(abs((float)($row['booking_balance'] ?? 0)) > 0.00001 ? 'Balance Open' : 'Clear'); ?>
</tr>
<?php endforeach; endif; ?>
</table>

<table>
<tr><td class="section" colspan="8">In-House Bookings With Open Balances</td></tr>
<tr><th>Booking</th><th>Property</th><th>Guest</th><th>Agent</th><th>Check In</th><th>Check Out</th><th>Charges</th><th>Payments</th><th>Balance</th></tr>
<?php if (!$openBalanceBookings): ?>
<tr><td colspan="9">No open balances for in-house bookings.</td></tr>
<?php else: foreach ($openBalanceBookings as $row): ?>
<tr>
    <?php echo xls_cell($row['order_no'] ?: ('#' . $row['id'])); ?>
    <?php echo xls_cell(row_property($row)); ?>
    <?php echo xls_cell(row_guest($row)); ?>
    <?php echo xls_cell(row_agent($row)); ?>
    <?php echo xls_cell($row['check_in'] ?? ''); ?>
    <?php echo xls_cell($row['check_out'] ?? ''); ?>
    <?php echo xls_money($row['booking_charges'] ?? 0); ?>
    <?php echo xls_money($row['booking_payments'] ?? 0); ?>
    <?php echo xls_money($row['booking_balance'] ?? 0); ?>
</tr>
<?php endforeach; endif; ?>
</table>

<table>
<tr><td class="section" colspan="4">Dirty Rooms</td></tr>
<tr><th>Room</th><th>Property</th><th>Room Type</th><th>Status</th></tr>
<?php if (!$dirtyRooms): ?>
<tr><td colspan="4">No dirty rooms.</td></tr>
<?php else: foreach ($dirtyRooms as $row): ?>
<tr>
    <?php echo xls_cell($row['room_no'] ?? ''); ?>
    <?php echo xls_cell(row_property($row)); ?>
    <?php echo xls_cell($row['room_type'] ?? ''); ?>
    <?php echo xls_cell($row['housekeeping_status'] ?? ($row['status'] ?? 'dirty')); ?>
</tr>
<?php endforeach; endif; ?>
</table>

<table>
<tr><td class="section" colspan="4">Out Of Order / Maintenance Rooms</td></tr>
<tr><th>Room</th><th>Property</th><th>Room Type</th><th>Status</th></tr>
<?php if (!$outOfOrderRooms): ?>
<tr><td colspan="4">No out of order rooms.</td></tr>
<?php else: foreach ($outOfOrderRooms as $row): ?>
<tr>
    <?php echo xls_cell($row['room_no'] ?? ''); ?>
    <?php echo xls_cell(row_property($row)); ?>
    <?php echo xls_cell($row['room_type'] ?? ''); ?>
    <?php echo xls_cell($row['housekeeping_status'] ?? ($row['status'] ?? 'ooo')); ?>
</tr>
<?php endforeach; endif; ?>
</table>

<table>
<tr><td class="section" colspan="4">Manager Sign-Off</td></tr>
<tr><th>Prepared By</th><th>Reviewed By</th><th>Time</th><th>Signature</th></tr>
<tr><td style="height:36px;"></td><td></td><td></td><td></td></tr>
</table>
</body>
</html>
