chatelet/crud.py

"""Chatelet CRUD + the availability arbiter.

The DB is the single source of truth for whether a range is free. Nostr
booking requests are just requests; `is_available` + the `held` write is
where a date range actually gets locked. Keep the check-then-hold path
tight so two concurrent requests can't both win the same nights.
"""

from datetime import date, datetime, timezone

from lnbits.db import Database
from lnbits.helpers import urlsafe_short_hash

from .models import (
    OCCUPYING_STATUSES,
    Block,
    Booking,
    BookingStatus,
    ChateletSettings,
    CreateBlockData,
    CreateRoomData,
    OperatorSettings,
    Room,
    RoomStatus,
)

db = Database("ext_chatelet")


# ---------------------------------------------------------------------------
# Settings
# ---------------------------------------------------------------------------


async def get_or_create_settings() -> ChateletSettings:
    row = await db.fetchone(
        "SELECT * FROM chatelet.settings LIMIT 1", model=ChateletSettings
    )
    if row:
        return row
    settings = ChateletSettings()
    await db.insert("chatelet.settings", settings)
    return settings


async def update_settings(settings: ChateletSettings) -> ChateletSettings:
    settings.updated_at = datetime.now(timezone.utc)
    await db.update("chatelet.settings", settings, "")
    return settings


# ---------------------------------------------------------------------------
# Operator settings (per LNbits user — multi-tenant)
# ---------------------------------------------------------------------------


async def get_or_create_operator_settings(user_id: str) -> OperatorSettings:
    row = await db.fetchone(
        "SELECT * FROM chatelet.operator_settings WHERE user_id = :uid",
        {"uid": user_id},
        OperatorSettings,
    )
    if row:
        return row
    ops = OperatorSettings(user_id=user_id)
    await db.insert("chatelet.operator_settings", ops)
    return ops


async def update_operator_settings(ops: OperatorSettings) -> OperatorSettings:
    ops.updated_at = datetime.now(timezone.utc)
    await db.update("chatelet.operator_settings", ops, "WHERE user_id = :user_id")
    return ops


# ---------------------------------------------------------------------------
# Rooms
# ---------------------------------------------------------------------------


async def create_room(data: CreateRoomData) -> Room:
    assert data.wallet, "wallet is required"
    room = Room(
        id=urlsafe_short_hash()[:8],
        wallet=data.wallet,
        title=data.title,
        description=data.description,
        price_amount=data.price_amount,
        price_currency=data.price_currency,
        price_frequency=data.price_frequency,
        max_guests=data.max_guests,
        min_nights=data.min_nights,
        amenities=data.amenities,
        location=data.location,
        geohash=data.geohash,
        images=data.images,
        status=RoomStatus.inactive,
    )
    await db.insert("chatelet.rooms", room)
    return room


async def get_room(room_id: str) -> Room | None:
    return await db.fetchone(
        "SELECT * FROM chatelet.rooms WHERE id = :id", {"id": room_id}, Room
    )


async def get_rooms() -> list[Room]:
    return await db.fetchall("SELECT * FROM chatelet.rooms", model=Room)


async def update_room(room: Room) -> Room:
    room.updated_at = datetime.now(timezone.utc)
    await db.update("chatelet.rooms", room)
    return room


async def delete_room(room_id: str) -> None:
    await db.execute("DELETE FROM chatelet.rooms WHERE id = :id", {"id": room_id})


# ---------------------------------------------------------------------------
# Bookings
# ---------------------------------------------------------------------------


async def get_booking(booking_id: str) -> Booking | None:
    return await db.fetchone(
        "SELECT * FROM chatelet.bookings WHERE id = :id", {"id": booking_id}, Booking
    )


async def get_booking_by_payment_hash(payment_hash: str) -> Booking | None:
    return await db.fetchone(
        "SELECT * FROM chatelet.bookings WHERE payment_hash = :ph",
        {"ph": payment_hash},
        Booking,
    )


async def get_bookings_for_guest(guest_pubkey: str, limit: int = 200) -> list[Booking]:
    """A guest's own stays, newest check-in first (idx_bookings_guest_pubkey)."""
    return await db.fetchall(
        """
        SELECT * FROM chatelet.bookings WHERE guest_pubkey = :pk
        ORDER BY check_in DESC LIMIT :limit
        """,
        {"pk": guest_pubkey, "limit": limit},
        Booking,
    )


async def get_bookings_for_room(room_id: str) -> list[Booking]:
    return await db.fetchall(
        "SELECT * FROM chatelet.bookings WHERE room_id = :rid",
        {"rid": room_id},
        Booking,
    )


async def create_booking(booking: Booking) -> Booking:
    await db.insert("chatelet.bookings", booking)
    return booking


async def update_booking(booking: Booking) -> Booking:
    booking.updated_at = datetime.now(timezone.utc)
    await db.update("chatelet.bookings", booking)
    return booking


# ---------------------------------------------------------------------------
# Blocks
# ---------------------------------------------------------------------------


async def create_block(data: CreateBlockData) -> Block:
    block = Block(
        id=urlsafe_short_hash()[:8],
        room_id=data.room_id,
        start_date=data.start_date,
        end_date=data.end_date,
        reason=data.reason,
    )
    await db.insert("chatelet.blocks", block)
    return block


async def get_blocks_for_room(room_id: str) -> list[Block]:
    return await db.fetchall(
        "SELECT * FROM chatelet.blocks WHERE room_id = :rid", {"rid": room_id}, Block
    )


async def delete_block(block_id: str) -> None:
    await db.execute("DELETE FROM chatelet.blocks WHERE id = :id", {"id": block_id})


# ---------------------------------------------------------------------------
# Availability — the arbiter
# ---------------------------------------------------------------------------


def _overlaps(a_start: str, a_end: str, b_start: str, b_end: str) -> bool:
    """Half-open interval overlap: [a_start, a_end) ∩ [b_start, b_end).

    check_out / end_date are exclusive, so a stay ending on the same day
    another begins does NOT overlap (back-to-back bookings are fine).
    """
    return a_start < b_end and b_start < a_end


async def is_available(room_id: str, check_in: str, check_out: str) -> bool:
    """True iff the room is active and no occupying booking or block
    overlaps [check_in, check_out). This is the authoritative check; call
    it inside the same request path that writes the `held` booking.

    NOTE: this read + the subsequent `held` write must be atomic per room.
    `services.request_booking` holds a per-room `asyncio.Lock` around this
    call and `create_booking` for exactly that reason — don't call this as
    the basis for a hold outside that lock. See docs/event-flow.md § Concurrency.
    """
    room = await get_room(room_id)
    if not room or room.status != RoomStatus.active:
        return False

    for b in await get_bookings_for_room(room_id):
        if b.status in OCCUPYING_STATUSES and _overlaps(
            check_in, check_out, b.check_in, b.check_out
        ):
            return False

    for blk in await get_blocks_for_room(room_id):
        if _overlaps(check_in, check_out, blk.start_date, blk.end_date):
            return False

    return True


def merge_ranges(
    ranges: list[tuple[str, str]], start: str, end: str
) -> list[tuple[str, str]]:
    """Clip half-open [s, e) spans to [start, end), drop empties, sort, and
    coalesce overlapping *and adjacent* spans (a stay ending the day another
    begins becomes one span). Pure, so the calendar shape is unit-testable
    without a DB. Adjacent merging is deliberate: it hides where one
    occupant's dates stop and the next's begin."""
    clipped = sorted(
        (max(s, start), min(e, end)) for s, e in ranges if max(s, start) < min(e, end)
    )
    merged: list[tuple[str, str]] = []
    for s, e in clipped:
        if merged and s <= merged[-1][1]:
            merged[-1] = (merged[-1][0], max(merged[-1][1], e))
        else:
            merged.append((s, e))
    return merged


async def get_occupied_ranges(
    room_id: str, start: str, end: str
) -> list[tuple[str, str]]:
    """Occupying bookings + blocks that touch [start, end), as merged spans.
    Same statuses and the same half-open rule as `is_available`, so a night
    the calendar shows as free is one the arbiter will accept."""
    bookings = await db.fetchall(
        f"""
        SELECT * FROM chatelet.bookings
        WHERE room_id = :rid AND check_in < :end AND check_out > :start
          AND status IN ({", ".join(f"'{st.value}'" for st in OCCUPYING_STATUSES)})
        """,
        {"rid": room_id, "start": start, "end": end},
        Booking,
    )
    blocks = await db.fetchall(
        """
        SELECT * FROM chatelet.blocks
        WHERE room_id = :rid AND start_date < :end AND end_date > :start
        """,
        {"rid": room_id, "start": start, "end": end},
        Block,
    )
    spans = [(b.check_in, b.check_out) for b in bookings]
    spans += [(blk.start_date, blk.end_date) for blk in blocks]
    return merge_ranges(spans, start, end)


def nights_between(check_in: str, check_out: str) -> int:
    d_in = date.fromisoformat(check_in)
    d_out = date.fromisoformat(check_out)
    return (d_out - d_in).days


async def expire_stale_holds() -> list[Booking]:
    """Flip `held`/`awaiting_payment` bookings whose hold has lapsed to
    `expired`, freeing their dates. Driven by tasks.py on a timer."""
    now = datetime.now(timezone.utc)
    stale = await db.fetchall(
        """
        SELECT * FROM chatelet.bookings
        WHERE status IN ('held', 'awaiting_payment')
          AND expires_at IS NOT NULL AND expires_at < :now
        """,
        {"now": now},
        Booking,
    )
    for b in stale:
        b.status = BookingStatus.expired
        await update_booking(b)
    return stale