<?php
if (!function_exists('waitlist_generate_no')) {
    function waitlist_generate_no(PDO $pdo): string
    {
        do {
            $ref = 'WL-' . date('YmdHis') . '-' . random_int(100, 999);
            $stmt = $pdo->prepare("SELECT COUNT(*) FROM reservation_waitlist WHERE waitlist_no = :waitlist_no");
            $stmt->execute([':waitlist_no' => $ref]);
            $exists = (int)$stmt->fetchColumn() > 0;
        } while ($exists);

        return $ref;
    }
}

if (!function_exists('waitlist_table_exists')) {
    function waitlist_table_exists(PDO $pdo): bool
    {
        try {
            $pdo->query("SELECT 1 FROM reservation_waitlist LIMIT 1");
            return true;
        } catch (Throwable $e) {
            return false;
        }
    }
}

if (!function_exists('fetch_waitlist_by_id')) {
    function fetch_waitlist_by_id(PDO $pdo, int $waitlistId): ?array
    {
        if ($waitlistId <= 0 || !waitlist_table_exists($pdo)) {
            return null;
        }

        $stmt = $pdo->prepare("
            SELECT *
            FROM reservation_waitlist
            WHERE id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $waitlistId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;
    }
}

if (!function_exists('save_waitlist_entry')) {
    function save_waitlist_entry(PDO $pdo, array $data, int $userId = 0): int
    {
        if (!waitlist_table_exists($pdo)) {
            throw new RuntimeException('Waitlist table not found. Please run waitlist_schema.sql first.');
        }

        $waitlistId = (int)($data['id'] ?? 0);
        $guestName = trim((string)($data['guest_name'] ?? ''));
        $waitlistNo = trim((string)($data['waitlist_no'] ?? ''));
        if ($waitlistNo === '') {
            $waitlistNo = waitlist_generate_no($pdo);
        }

        $payload = [
            ':waitlist_no'        => $waitlistNo,
            ':property_id'        => (int)($data['property_id'] ?? 0),
            ':guest_id'           => (int)($data['guest_id'] ?? 0) > 0 ? (int)$data['guest_id'] : null,
            ':guest_name'         => $guestName !== '' ? $guestName : null,
            ':agent_id'           => (int)($data['agent_id'] ?? 0) > 0 ? (int)$data['agent_id'] : null,
            ':contact_id'         => (int)($data['contact_id'] ?? 0) > 0 ? (int)$data['contact_id'] : null,
            ':originating_agent'  => (int)($data['originating_agent'] ?? 0) > 0 ? (int)$data['originating_agent'] : null,
            ':voucher_no'         => trim((string)($data['voucher_no'] ?? '')) ?: null,
            ':occassion_id'       => (int)($data['occassion_id'] ?? 0) > 0 ? (int)$data['occassion_id'] : null,
            ':nationality_id'     => (int)($data['nationality_id'] ?? 0) > 0 ? (int)$data['nationality_id'] : null,
            ':reservation_notes'  => trim((string)($data['reservation_notes'] ?? '')) ?: null,
            ':internal_memo'      => trim((string)($data['internal_memo'] ?? '')) ?: null,
            ':reminder_info'      => trim((string)($data['reminder_info'] ?? '')) ?: null,
            ':check_in'           => trim((string)($data['check_in'] ?? '')),
            ':check_out'          => trim((string)($data['check_out'] ?? '')),
            ':room_type_id'       => (int)($data['room_type_id'] ?? 0),
            ':totalrooms'         => max(1, (int)($data['totalrooms'] ?? 1)),
            ':adults'             => max(1, (int)($data['adults'] ?? 1)),
            ':kids'               => max(0, (int)($data['kids'] ?? 0)),
            ':status'             => trim((string)($data['status'] ?? 'waiting')) ?: 'waiting',
            ':priority'           => max(0, (int)($data['priority'] ?? 0)),
            ':available_rooms_last' => max(0, (int)($data['available_rooms_last'] ?? 0)),
            ':availability_found_at' => !empty($data['availability_found_at']) ? $data['availability_found_at'] : null,
            ':notified_at'        => !empty($data['notified_at']) ? $data['notified_at'] : null,
            ':source'             => trim((string)($data['source'] ?? 'reservation_form')) ?: 'reservation_form',
            ':source_ref'         => trim((string)($data['source_ref'] ?? '')) ?: null,
            ':converted_order_id' => (int)($data['converted_order_id'] ?? 0) > 0 ? (int)$data['converted_order_id'] : null,
            ':requested_by'       => $userId > 0 ? $userId : null,
        ];

        if ($waitlistId > 0) {
            $payload[':id'] = $waitlistId;
            $stmt = $pdo->prepare("
                UPDATE reservation_waitlist
                SET property_id = :property_id,
                    guest_id = :guest_id,
                    guest_name = :guest_name,
                    agent_id = :agent_id,
                    contact_id = :contact_id,
                    originating_agent = :originating_agent,
                    voucher_no = :voucher_no,
                    occassion_id = :occassion_id,
                    nationality_id = :nationality_id,
                    reservation_notes = :reservation_notes,
                    internal_memo = :internal_memo,
                    reminder_info = :reminder_info,
                    check_in = :check_in,
                    check_out = :check_out,
                    room_type_id = :room_type_id,
                    totalrooms = :totalrooms,
                    adults = :adults,
                    kids = :kids,
                    status = :status,
                    priority = :priority,
                    available_rooms_last = :available_rooms_last,
                    availability_found_at = :availability_found_at,
                    notified_at = :notified_at,
                    source = :source,
                    source_ref = :source_ref,
                    converted_order_id = :converted_order_id,
                    updated_at = NOW()
                WHERE id = :id
            ");
            $stmt->execute($payload);
            return $waitlistId;
        }

        $stmt = $pdo->prepare("
            INSERT INTO reservation_waitlist
            (
                waitlist_no, property_id, guest_id, guest_name, agent_id, contact_id, originating_agent,
                voucher_no, occassion_id, nationality_id, reservation_notes, internal_memo, reminder_info,
                check_in, check_out, room_type_id, totalrooms, adults, kids, status, priority,
                available_rooms_last, availability_found_at, notified_at, source, source_ref,
                converted_order_id, requested_by, created_at, updated_at
            )
            VALUES
            (
                :waitlist_no, :property_id, :guest_id, :guest_name, :agent_id, :contact_id, :originating_agent,
                :voucher_no, :occassion_id, :nationality_id, :reservation_notes, :internal_memo, :reminder_info,
                :check_in, :check_out, :room_type_id, :totalrooms, :adults, :kids, :status, :priority,
                :available_rooms_last, :availability_found_at, :notified_at, :source, :source_ref,
                :converted_order_id, :requested_by, NOW(), NOW()
            )
        ");
        $stmt->execute($payload);

        return (int)$pdo->lastInsertId();
    }
}

if (!function_exists('refresh_waitlist_matches')) {
    function refresh_waitlist_matches(PDO $pdo, ?int $propertyId = null, ?int $roomTypeId = null): array
    {
        if (!waitlist_table_exists($pdo) || !function_exists('count_available_rooms_by_type')) {
            return [];
        }

        $sql = "
            SELECT *
            FROM reservation_waitlist
            WHERE status IN ('waiting','notified')
        ";
        $params = [];
        if ($propertyId !== null && $propertyId > 0) {
            $sql .= " AND property_id = :property_id ";
            $params[':property_id'] = $propertyId;
        }
        if ($roomTypeId !== null && $roomTypeId > 0) {
            $sql .= " AND room_type_id = :room_type_id ";
            $params[':room_type_id'] = $roomTypeId;
        }
        $sql .= " ORDER BY priority DESC, created_at ASC, id ASC ";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $matches = [];
        $upd = $pdo->prepare("
            UPDATE reservation_waitlist
            SET available_rooms_last = :available_rooms_last,
                status = :status,
                availability_found_at = :availability_found_at,
                notified_at = :notified_at,
                updated_at = NOW()
            WHERE id = :id
        ");

        foreach ($rows as $row) {
            $availability = count_available_rooms_by_type(
                $pdo,
                (int)$row['property_id'],
                (int)$row['room_type_id'],
                (string)$row['check_in'],
                (string)$row['check_out']
            );
            $availableRooms = (int)($availability['available_rooms'] ?? 0);
            $requestedRooms = max(1, (int)($row['totalrooms'] ?? 1));
            $canMatch = $availableRooms >= $requestedRooms;

            $newStatus = $canMatch ? 'notified' : 'waiting';
            $availabilityFoundAt = $row['availability_found_at'] ?? null;
            $notifiedAt = $row['notified_at'] ?? null;

            if ($canMatch && empty($availabilityFoundAt)) {
                $availabilityFoundAt = date('Y-m-d H:i:s');
            }
            if ($canMatch && empty($notifiedAt)) {
                $notifiedAt = date('Y-m-d H:i:s');
            }
            if (!$canMatch) {
                $availabilityFoundAt = null;
                $notifiedAt = null;
            }

            $upd->execute([
                ':available_rooms_last'   => $availableRooms,
                ':status'                 => $newStatus,
                ':availability_found_at'  => $availabilityFoundAt,
                ':notified_at'            => $notifiedAt,
                ':id'                     => (int)$row['id'],
            ]);

            if ($canMatch) {
                $row['available_rooms_last'] = $availableRooms;
                $row['status'] = $newStatus;
                $row['availability_found_at'] = $availabilityFoundAt;
                $row['notified_at'] = $notifiedAt;
                $matches[] = $row;
            }
        }

        return $matches;
    }
}

if (!function_exists('fetch_notified_waitlists')) {
    function fetch_notified_waitlists(PDO $pdo, int $limit = 10): array
    {
        if (!waitlist_table_exists($pdo)) {
            return [];
        }

        $stmt = $pdo->prepare("
            SELECT wl.*, p.name AS property_name, rt.title AS room_type_name
            FROM reservation_waitlist wl
            LEFT JOIN properties p ON p.id = wl.property_id
            LEFT JOIN room_types rt ON rt.id = wl.room_type_id
            WHERE wl.status = 'notified'
            ORDER BY wl.priority DESC, wl.availability_found_at ASC, wl.created_at ASC
            LIMIT " . max(1, (int)$limit)
        );
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}

if (!function_exists('mark_waitlist_converted')) {
    function mark_waitlist_converted(PDO $pdo, int $waitlistId, int $orderId): void
    {
        if ($waitlistId <= 0 || !waitlist_table_exists($pdo)) {
            return;
        }

        $stmt = $pdo->prepare("
            UPDATE reservation_waitlist
            SET status = 'converted',
                converted_order_id = :order_id,
                updated_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute([
            ':order_id' => $orderId,
            ':id'       => $waitlistId,
        ]);
    }
}

if (!function_exists('mark_waitlist_cancelled')) {
    function mark_waitlist_cancelled(PDO $pdo, int $waitlistId): void
    {
        if ($waitlistId <= 0 || !waitlist_table_exists($pdo)) {
            return;
        }

        $stmt = $pdo->prepare("
            UPDATE reservation_waitlist
            SET status = 'cancelled',
                updated_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute([':id' => $waitlistId]);
    }
}
