chatelet/migrations.py

"""Chatelet schema migrations.

This is a brand-new aiolabs-original extension (not a fork of an upstream
LNbits extension), so there is no upstream migrations.py to stay byte-
identical with — we use ordinary migrations here and do NOT need the
migrations_fork.py split (that pattern only earns its keep on forks that
rebase onto upstream). Every migration is still written idempotently so a
partial/re-run boot can't wedge the schema.

Tables live in ext_chatelet.sqlite3 (SQLite) or the `chatelet` Postgres
schema. Availability is intentionally NOT a table — it is derived from
`bookings` + `blocks` at query time (see crud.is_available).
"""


async def m001_initial(db):
    """Settings, rooms, bookings, blocks."""

    # One row: castle/operator-wide config.
    await db.execute(
        f"""
        CREATE TABLE chatelet.settings (
            operator_id TEXT,
            relays TEXT NOT NULL DEFAULT '[]',
            default_hold_minutes INTEGER NOT NULL DEFAULT 30,
            deposit_percent INTEGER NOT NULL DEFAULT 100,
            checkin_time TEXT NOT NULL DEFAULT '15:00',
            checkout_time TEXT NOT NULL DEFAULT '11:00',
            cancellation_policy TEXT NOT NULL DEFAULT '',
            publish_availability BOOLEAN NOT NULL DEFAULT true,
            created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now},
            updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
        );
        """
    )

    # Rooms == NIP-99 kind:30402 listings. `id` doubles as the listing "d" tag.
    await db.execute(
        f"""
        CREATE TABLE chatelet.rooms (
            id TEXT PRIMARY KEY,
            wallet TEXT NOT NULL,
            title TEXT NOT NULL,
            description TEXT NOT NULL DEFAULT '',
            price_amount REAL NOT NULL,
            price_currency TEXT NOT NULL DEFAULT 'EUR',
            price_frequency TEXT NOT NULL DEFAULT 'night',
            max_guests INTEGER NOT NULL DEFAULT 2,
            min_nights INTEGER NOT NULL DEFAULT 1,
            amenities TEXT NOT NULL DEFAULT '[]',
            location TEXT NOT NULL DEFAULT '',
            geohash TEXT NOT NULL DEFAULT '',
            images TEXT NOT NULL DEFAULT '[]',
            status TEXT NOT NULL DEFAULT 'inactive',
            listing_event_id TEXT,
            created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now},
            updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
        );
        """
    )

    # Bookings == kind:30078 reservation objects. `amount_sat` is canonical.
    await db.execute(
        f"""
        CREATE TABLE chatelet.bookings (
            id TEXT PRIMARY KEY,
            room_id TEXT NOT NULL REFERENCES {db.references_schema}rooms (id),
            guest_pubkey TEXT NOT NULL,
            guest_contact TEXT,
            check_in TEXT NOT NULL,
            check_out TEXT NOT NULL,
            nights INTEGER NOT NULL,
            num_guests INTEGER NOT NULL DEFAULT 1,
            currency TEXT NOT NULL,
            price_fiat REAL NOT NULL,
            amount_sat {db.big_int} NOT NULL,
            deposit_sat {db.big_int} NOT NULL,
            status TEXT NOT NULL DEFAULT 'held',
            payment_hash TEXT,
            request_event_id TEXT,
            reservation_event_id TEXT,
            expires_at TIMESTAMP,
            created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now},
            updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
        );
        """
    )
    # Overlap checks scan by room + status; index the hot path.
    # NOTE: the schema qualifier goes on the INDEX name, not the table — SQLite
    # attaches the ext DB as schema `chatelet`, and `CREATE INDEX ON schema.table`
    # is a syntax error there. (Matches the restaurant extension's pattern.)
    await db.execute(
        "CREATE INDEX chatelet.idx_bookings_room_status "
        "ON bookings (room_id, status);"
    )
    await db.execute(
        "CREATE INDEX chatelet.idx_bookings_payment_hash "
        "ON bookings (payment_hash);"
    )

    # Manual owner-side blocks (maintenance, personal use).
    await db.execute(
        f"""
        CREATE TABLE chatelet.blocks (
            id TEXT PRIMARY KEY,
            room_id TEXT NOT NULL REFERENCES {db.references_schema}rooms (id),
            start_date TEXT NOT NULL,
            end_date TEXT NOT NULL,
            reason TEXT NOT NULL DEFAULT '',
            created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
        );
        """
    )
    await db.execute(
        "CREATE INDEX chatelet.idx_blocks_room ON blocks (room_id);"
    )


async def m002_room_checkin_instructions(db):
    """Private per-room access details (address, gate code), sent to the guest
    only in the encrypted NIP-17 check-in DM after payment — never public."""
    await db.execute(
        "ALTER TABLE chatelet.rooms ADD COLUMN checkin_instructions TEXT "
        "NOT NULL DEFAULT '';"
    )


async def m003_operator_settings_and_guest_index(db):
    """Chatelet is multi-tenant: every LNbits user may host rooms. What an
    operator decides for *all their rooms* — house rules and whether they
    take card payments — lives here, keyed by LNbits user id, created lazily.
    The single `chatelet.settings` row keeps only instance-wide knobs; its
    old house-rule columns stay in place but are no longer read.

    Also indexes bookings by guest so a guest can list their own stays."""
    await db.execute(f"""
        CREATE TABLE chatelet.operator_settings (
            user_id TEXT PRIMARY KEY,
            accept_fiat BOOLEAN NOT NULL DEFAULT false,
            checkin_time TEXT NOT NULL DEFAULT '15:00',
            checkout_time TEXT NOT NULL DEFAULT '11:00',
            cancellation_policy TEXT NOT NULL DEFAULT '',
            created_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now},
            updated_at TIMESTAMP NOT NULL DEFAULT {db.timestamp_now}
        );
        """)
    await db.execute(
        "CREATE INDEX chatelet.idx_bookings_guest_pubkey ON bookings (guest_pubkey);"
    )