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

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

if (strtotime($auditDate) === false) {
    exit('Invalid audit date.');
}

$paramsBase = [];
$orderPropertySql = '';
$roomPropertySql = '';

$propertyLabel = 'All Properties';
if ($propertyId > 0) {
    $paramsBase[':property_id'] = $propertyId;
    $orderPropertySql = " AND o.property_id = :property_id ";
    $roomPropertySql = " AND r.property_id = :property_id ";

    $stmt = $pdo->prepare("SELECT name, code FROM properties WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $propertyId]);
    $prop = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($prop) {
        $propertyLabel = trim((string)($prop['name'] ?? ''));
        if (!empty($prop['code'])) {
            $propertyLabel .= ' (' . $prop['code'] . ')';
        }
    }
}

$rows = [];

/* Pending arrivals */
$sql = "
    SELECT
        o.id,
        o.order_no,
        o.check_in,
        p.name AS property_name,
        p.code AS property_code,
        g.firstname,
        g.lastname,
        (
            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
    WHERE DATE(o.check_in) = :audit_date
    $orderPropertySql
    ORDER BY p.name ASC, o.id DESC
";
$params = $paramsBase;
$params[':audit_date'] = $auditDate;
$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) {
        $guestName = trim(($row['firstname'] ?? '') . ' ' . ($row['lastname'] ?? ''));
        $prop = trim((string)($row['property_name'] ?? ''));
        if (!empty($row['property_code'])) $prop .= ($prop !== '' ? ' (' . $row['property_code'] . ')' : $row['property_code']);

        $rows[] = [
            'Section' => 'Pending Arrival',
            'Property' => $prop !== '' ? $prop : $propertyLabel,
            'Audit Date' => $auditDate,
            'Booking' => $row['order_no'] ?: ('#' . $row['id']),
            'Guest' => $guestName !== '' ? $guestName : 'Guest',
            'Check In' => $row['check_in'],
            'Check Out' => '',
            'Rooms Total' => $total,
            'Checked Count' => $checked,
            'Balance' => '',
            'Room' => '',
            'Room Type' => '',
            'Status' => 'Pending Check-In',
        ];
    }
}

/* Pending departures */
$sql = "
    SELECT
        o.id,
        o.order_no,
        o.check_out,
        p.name AS property_name,
        p.code AS property_code,
        g.firstname,
        g.lastname,
        (
            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
    WHERE DATE(o.check_out) = :audit_date
    $orderPropertySql
    ORDER BY p.name ASC, o.id DESC
";
$params = $paramsBase;
$params[':audit_date'] = $auditDate;
$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) {
        $guestName = trim(($row['firstname'] ?? '') . ' ' . ($row['lastname'] ?? ''));
        $prop = trim((string)($row['property_name'] ?? ''));
        if (!empty($row['property_code'])) $prop .= ($prop !== '' ? ' (' . $row['property_code'] . ')' : $row['property_code']);

        $rows[] = [
            'Section' => 'Pending Departure',
            'Property' => $prop !== '' ? $prop : $propertyLabel,
            'Audit Date' => $auditDate,
            'Booking' => $row['order_no'] ?: ('#' . $row['id']),
            'Guest' => $guestName !== '' ? $guestName : 'Guest',
            'Check In' => '',
            'Check Out' => $row['check_out'],
            'Rooms Total' => $total,
            'Checked Count' => $checked,
            'Balance' => round((float)($row['booking_balance'] ?? 0), 2),
            'Room' => '',
            'Room Type' => '',
            'Status' => 'Pending Check-Out',
        ];
    }
}

/* Dirty rooms */
$sql = "
    SELECT r.room_no, rt.title AS room_type, p.name AS property_name, p.code AS property_code
    FROM rooms r
    LEFT JOIN room_types rt ON rt.id = r.room_type_id
    LEFT JOIN properties p ON p.id = r.property_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);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $prop = trim((string)($row['property_name'] ?? ''));
    if (!empty($row['property_code'])) $prop .= ($prop !== '' ? ' (' . $row['property_code'] . ')' : $row['property_code']);

    $rows[] = [
        'Section' => 'Dirty Room',
        'Property' => $prop !== '' ? $prop : $propertyLabel,
        'Audit Date' => $auditDate,
        'Booking' => '',
        'Guest' => '',
        'Check In' => '',
        'Check Out' => '',
        'Rooms Total' => '',
        'Checked Count' => '',
        'Balance' => '',
        'Room' => $row['room_no'] ?? '',
        'Room Type' => $row['room_type'] ?? '',
        'Status' => 'Dirty',
    ];
}

/* OOO rooms */
$sql = "
    SELECT r.room_no, rt.title AS room_type, p.name AS property_name, p.code AS property_code
    FROM rooms r
    LEFT JOIN room_types rt ON rt.id = r.room_type_id
    LEFT JOIN properties p ON p.id = r.property_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);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    $prop = trim((string)($row['property_name'] ?? ''));
    if (!empty($row['property_code'])) $prop .= ($prop !== '' ? ' (' . $row['property_code'] . ')' : $row['property_code']);

    $rows[] = [
        'Section' => 'Out Of Order Room',
        'Property' => $prop !== '' ? $prop : $propertyLabel,
        'Audit Date' => $auditDate,
        'Booking' => '',
        'Guest' => '',
        'Check In' => '',
        'Check Out' => '',
        'Rooms Total' => '',
        'Checked Count' => '',
        'Balance' => '',
        'Room' => $row['room_no'] ?? '',
        'Room Type' => $row['room_type'] ?? '',
        'Status' => 'Out Of Order',
    ];
}

$headers = [
    'Section',
    'Property',
    'Audit Date',
    'Booking',
    'Guest',
    'Check In',
    'Check Out',
    'Rooms Total',
    'Checked Count',
    'Balance',
    'Room',
    'Room Type',
    'Status',
];

$filename = 'night_audit_' . $auditDate . '.csv';
csv_out($headers, $rows, $filename);