events/migrations_fork.py

"""
Fork-specific database migrations for the aiolabs events extension.

These migrations are tracked separately under `events_fork` in the
`dbversions` table (loaded by `lnbits/core/helpers.py:migrate_extension_database`),
so they do not collide with upstream's `m{NNN}_*` numbering in
`migrations.py`. Keeping the upstream-tracked file untouched means
`git pull upstream` stays rebase-clean for schema changes.

Conventions:
  - Sequential numbering starting from m001.
  - Each migration is `async def m{NNN}_<description>(db)`.
  - DDL must be idempotent: a fresh install runs every migration; an
    install that previously ran the OLD versions of these as
    `m007-m011` in `migrations.py` has the columns/tables already.
    Use `_alter_add_column_safe` / `_create_table_safe` so re-runs are
    no-ops instead of crashes.

History compressed into m001 (was m007-m011 in migrations.py pre-v1.6
rebase):
  - m007 add_user_id_support      (ticket.user_id column)
  - m008 add_event_status         (events.status column)
  - m009 add_nostr_columns        (events.nostr_event_id + created_at)
  - m010 add_events_settings      (events.settings singleton table)
  - m011 add_location_and_categories (events.location + categories)
"""


async def _alter_add_column_safe(db, sql: str) -> None:
    """ALTER TABLE ADD COLUMN that swallows duplicate-column errors.

    Re-running the squashed migration on a database that already has
    these columns (from the pre-squash `m007-m011` in migrations.py)
    must be a silent no-op. Same swallow we used in the old migrations.
    """
    try:
        await db.execute(sql)
    except Exception as exc:
        msg = str(exc).lower()
        if "duplicate column" in msg or "already exists" in msg:
            return
        raise


async def m001_aio_event_schema(db):
    """
    Apply every aiolabs schema delta on top of upstream events v1.3.0.

    This is the squashed equivalent of the pre-v1.6 sequence
    m007 → m011. Order matters for the settings table seed insert
    but the individual column adds are independent and idempotent.
    """

    # --- ticket.user_id ----------------------------------------------
    # Lets a ticket reference an LNbits user id instead of (name, email).
    # Application logic enforces that exactly one identifier scheme is
    # used per ticket.
    await _alter_add_column_safe(
        db, "ALTER TABLE events.ticket ADD COLUMN user_id TEXT"
    )

    # --- events.status -----------------------------------------------
    # Proposal / approval workflow. Existing rows default to 'approved'
    # so they stay visible after upgrade.
    await _alter_add_column_safe(
        db,
        "ALTER TABLE events.events ADD COLUMN status TEXT NOT NULL DEFAULT 'approved'",
    )

    # --- events.nostr_event_id, nostr_event_created_at ---------------
    # Track the most recent NIP-52 calendar event we published, so
    # subsequent edits can issue replaceable updates and NIP-09 deletes
    # against the right addressable coordinate.
    await _alter_add_column_safe(
        db, "ALTER TABLE events.events ADD COLUMN nostr_event_id TEXT"
    )
    await _alter_add_column_safe(
        db, "ALTER TABLE events.events ADD COLUMN nostr_event_created_at INTEGER"
    )

    # --- events.settings ---------------------------------------------
    # Singleton settings row used by the admin UI to toggle e.g.
    # auto_approve. CREATE TABLE IF NOT EXISTS + a guarded seed keeps
    # this idempotent.
    await db.execute("""
        CREATE TABLE IF NOT EXISTS events.settings (
            id INTEGER PRIMARY KEY DEFAULT 1,
            auto_approve BOOLEAN NOT NULL DEFAULT FALSE
        )
        """)
    await db.execute(
        "INSERT INTO events.settings (id, auto_approve) "
        "SELECT 1, FALSE WHERE NOT EXISTS "
        "(SELECT 1 FROM events.settings WHERE id = 1)"
    )

    # --- events.location, events.categories --------------------------
    # NIP-52 calendar metadata. `categories` carries a JSON-encoded
    # list of hashtags (the NIP-52 `t` tags).
    await _alter_add_column_safe(
        db, "ALTER TABLE events.events ADD COLUMN location TEXT"
    )
    await _alter_add_column_safe(
        db, "ALTER TABLE events.events ADD COLUMN categories TEXT"
    )


async def m002_ticket_payment_hash(db):
    """
    Add `ticket.payment_hash` for multi-ticket purchases.

    Multi-ticket purchases land as N rows sharing one LNbits invoice
    (so each attendee gets a distinct scannable QR but the buyer
    pays once). `ticket.id` stays the row primary key — for legacy
    single-purchase rows it equals payment_hash; for multi-purchase
    children it's a uuid generated at create-time. `payment_hash`
    is the new join key for invoice lookup.

    Backfill existing rows from id so the
    GET-tickets-by-payment-hash path keeps working for pre-migration
    data (id was the payment_hash by invariant before this column).
    """
    await _alter_add_column_safe(
        db, "ALTER TABLE events.ticket ADD COLUMN payment_hash TEXT"
    )
    await db.execute(
        "UPDATE events.ticket SET payment_hash = id "
        "WHERE payment_hash IS NULL OR payment_hash = ''"
    )


async def m003_event_nostr_publish_pending(db):
    """
    Add `events.nostr_publish_pending` — the marker that makes NIP-52
    publish drift queryable instead of invisible.

    Inventory reaches clients only through the republished calendar
    event. When that publish doesn't land, the relay keeps serving the
    counts it last saw and nothing anywhere records the divergence; it
    has twice been caught only by a human reading a public page
    (aiolabs/events#35, #51).

    The flag is set before each publish attempt and cleared only on a
    confirmed success, so it covers *both* observed failure shapes:
    an attempt that raised (a signer outage) and an attempt that was
    never made at all (no signer resolved, no NostrClient). A periodic
    sweep republishes whatever is still marked.

    Existing rows default to FALSE rather than TRUE: on upgrade we have
    no evidence they're stale, and marking the whole table pending would
    stampede the signer with a full-table republish on first boot.
    `/republish-all` is the deliberate way to force that.
    """
    await _alter_add_column_safe(
        db,
        "ALTER TABLE events.events "
        "ADD COLUMN nostr_publish_pending BOOLEAN NOT NULL DEFAULT FALSE",
    )