events/migrations.py

async def m001_initial(db):
    await db.execute(
        """
        CREATE TABLE events.events (
            id TEXT PRIMARY KEY,
            wallet TEXT NOT NULL,
            name TEXT NOT NULL,
            info TEXT NOT NULL,
            closing_date TEXT NOT NULL,
            event_start_date TEXT NOT NULL,
            event_end_date TEXT NOT NULL,
            amount_tickets INTEGER NOT NULL,
            price_per_ticket INTEGER NOT NULL,
            sold INTEGER NOT NULL,
            time TIMESTAMP NOT NULL DEFAULT """
        + db.timestamp_now
        + """
        );
    """
    )

    await db.execute(
        """
        CREATE TABLE events.tickets (
            id TEXT PRIMARY KEY,
            wallet TEXT NOT NULL,
            event TEXT NOT NULL,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            registered BOOLEAN NOT NULL,
            time TIMESTAMP NOT NULL DEFAULT """
        + db.timestamp_now
        + """
        );
    """
    )


async def m002_changed(db):
    await db.execute(
        """
        CREATE TABLE events.ticket (
            id TEXT PRIMARY KEY,
            wallet TEXT NOT NULL,
            event TEXT NOT NULL,
            name TEXT NOT NULL,
            email TEXT NOT NULL,
            registered BOOLEAN NOT NULL,
            paid BOOLEAN NOT NULL,
            time TIMESTAMP NOT NULL DEFAULT """
        + db.timestamp_now
        + """
        );
    """
    )

    for row in [list(row) for row in await db.fetchall("SELECT * FROM events.tickets")]:
        usescsv = ""

        for i in range(row[5]):
            if row[7]:
                usescsv += "," + str(i + 1)
            else:
                usescsv += "," + str(1)
        usescsv = usescsv[1:]
        await db.execute(
            """
            INSERT INTO events.ticket (
                id,
                wallet,
                event,
                name,
                email,
                registered,
                paid
            )
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (row[0], row[1], row[2], row[3], row[4], row[5], True),
        )
    await db.execute("DROP TABLE events.tickets")


async def m003_add_register_timestamp(db):
    """
    Add a column to register the timestamp of ticket register
    """
    await db.execute(
        "ALTER TABLE events.ticket ADD COLUMN reg_timestamp TIMESTAMP;"
    )  # NULL means not registered, or old ticket


async def m004_add_currency(db):
    """
    Add a currency table to allow fiat denomination
    of tickets. Make price a float.
    """
    await db.execute("ALTER TABLE events.events RENAME TO events_old")
    await db.execute(
        """
        CREATE TABLE events.events (
            id TEXT PRIMARY KEY,
            wallet TEXT NOT NULL,
            name TEXT NOT NULL,
            info TEXT NOT NULL,
            closing_date TEXT NOT NULL,
            event_start_date TEXT NOT NULL,
            event_end_date TEXT NOT NULL,
            currency TEXT NOT NULL,
            amount_tickets INTEGER NOT NULL,
            price_per_ticket REAL NOT NULL,
            sold INTEGER NOT NULL,
            time TIMESTAMP NOT NULL DEFAULT """
        + db.timestamp_now
        + """
        );
    """
    )

    for row in [
        list(row) for row in await db.fetchall("SELECT * FROM events.events_old")
    ]:
        await db.execute(
            """
                    INSERT INTO events.events (
                        id,
                        wallet,
                        name,
                        info,
                        closing_date,
                        event_start_date,
                        event_end_date,
                        currency,
                        amount_tickets,
                        price_per_ticket,
                        sold
                    )
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
            (
                row[0],
                row[1],
                row[2],
                row[3],
                row[4],
                row[5],
                row[6],
                "sat",
                row[7],
                row[8],
                row[9],
            ),
        )

    await db.execute("DROP TABLE events.events_old")


async def m005_add_image_banner(db):
    """
    Add a column to allow an image banner for the event
    """
    await db.execute("ALTER TABLE events.events ADD COLUMN banner TEXT;")


async def m006_add_user_id_support(db):
    """
    Add user_id column to tickets table to support LNbits user-id as identifier
    Make name and email optional when user_id is provided
    """
    await db.execute("ALTER TABLE events.ticket ADD COLUMN user_id TEXT;")
    
    # Since SQLite doesn't support changing column constraints directly,
    # we'll work around this by allowing the application logic to handle
    # the validation that either (name AND email) OR user_id is provided
    # The database will continue to expect name and email as NOT NULL
    # but we'll insert empty strings for user_id tickets

async def m007_add_extra_fields(db):
    """
    Add a canceled and 'extra' column to events and ticket tables
    to support promo codes and ticket metadata.
    """
    # Add canceled and 'extra' columns to events table
    # SQLite requires separate ALTER TABLE statements for each column
    await db.execute(
        "ALTER TABLE events.events ADD COLUMN canceled BOOLEAN NOT NULL DEFAULT FALSE;"
    )
    await db.execute(
        "ALTER TABLE events.events ADD COLUMN extra TEXT;"
    )

    # Add 'extra' column to ticket table
    await db.execute("ALTER TABLE events.ticket ADD COLUMN extra TEXT;")


async def m008_add_event_status(db):
    """
    Add status column to events table for proposal/approval workflow.
    Values: 'proposed', 'approved', 'rejected'.
    Default 'approved' for backward compatibility with existing events.
    """
    await db.execute(
        "ALTER TABLE events.events ADD COLUMN status TEXT NOT NULL DEFAULT 'approved';"
    )


async def m009_add_nostr_columns(db):
    """
    Add columns to track published NIP-52 Nostr calendar events.
    """
    await db.execute(
        "ALTER TABLE events.events ADD COLUMN nostr_event_id TEXT;"
    )
    await db.execute(
        "ALTER TABLE events.events ADD COLUMN nostr_event_created_at INTEGER;"
    )


async def m010_add_events_settings(db):
    """
    Create extension settings table for admin-configurable options.
    """
    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 OR IGNORE INTO events.settings (id, auto_approve) VALUES (1, FALSE);"
    )


async def m011_add_location_and_categories(db):
    """
    Add location and categories columns for NIP-52 calendar event support.
    """
    await db.execute(
        "ALTER TABLE events.events ADD COLUMN location TEXT;"
    )
    await db.execute(
        "ALTER TABLE events.events ADD COLUMN categories TEXT;"
    )