events/crud.py

import json
from datetime import datetime, timedelta, timezone
from typing import Optional

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

from .models import CreateEvent, Event, EventsSettings, Ticket, TicketExtra


def _parse_ticket_row(row) -> dict:
    """
    Parse a database row into a dict suitable for Ticket model creation.
    Handles:
    - Empty string to None conversion for name/email
    - JSON string to dict conversion for extra field
    """
    ticket_data = dict(row)

    # Convert empty strings back to None for the model
    if ticket_data.get("name") == "":
        ticket_data["name"] = None
    if ticket_data.get("email") == "":
        ticket_data["email"] = None

    # Parse extra field from JSON string if needed
    # (db.insert() serializes to JSON, but manual fetchone/fetchall returns string)
    extra = ticket_data.get("extra")
    if isinstance(extra, str):
        ticket_data["extra"] = json.loads(extra)

    return ticket_data

db = Database("ext_events")


async def create_ticket(
    payment_hash: str,
    wallet: str,
    event: str,
    name: Optional[str] = None,
    email: Optional[str] = None,
    user_id: Optional[str] = None,
    extra: Optional[dict] = None,
) -> Ticket:
    now = datetime.now(timezone.utc)

    # TODO: Check if this empty string workaround is still needed.
    # This converts None to empty strings for database storage because:
    # 1. Database may have NOT NULL constraints on name/email columns
    # 2. When user_id is provided, name/email are not used (mutually exclusive)
    # 3. The get_ticket() functions convert empty strings back to None when reading
    # Consider using nullable columns instead of this empty string pattern.
    if user_id:
        db_name = ""
        db_email = ""
    else:
        db_name = name or ""
        db_email = email or ""

    # Create ticket with database-compatible values for insertion
    # Using db.insert() ensures proper serialization of the extra field (TicketExtra)
    # across all database backends (SQLite, PostgreSQL, CockroachDB)
    db_ticket = Ticket(
        id=payment_hash,
        wallet=wallet,
        event=event,
        name=db_name,
        email=db_email,
        user_id=user_id,
        registered=False,
        paid=False,
        reg_timestamp=now,
        time=now,
        extra=TicketExtra(**extra) if extra else TicketExtra(),
    )

    await db.insert("events.ticket", db_ticket)

    # Return ticket with original name/email values (not empty strings)
    # This maintains consistency with how get_ticket() converts empty strings back to None
    return Ticket(
        id=payment_hash,
        wallet=wallet,
        event=event,
        name=name,
        email=email,
        user_id=user_id,
        registered=False,
        paid=False,
        reg_timestamp=now,
        time=now,
        extra=TicketExtra(**extra) if extra else TicketExtra(),
    )


async def update_ticket(ticket: Ticket) -> Ticket:
    # Create a new Ticket object with corrected values for database constraints
    ticket_dict = ticket.dict()

    # Convert None values to empty strings for database constraints
    if ticket_dict.get("name") is None:
        ticket_dict["name"] = ""
    if ticket_dict.get("email") is None:
        ticket_dict["email"] = ""

    # Create a new Ticket object with the corrected values
    corrected_ticket = Ticket(**ticket_dict)

    await db.update("events.ticket", corrected_ticket)
    return ticket


async def get_ticket(payment_hash: str) -> Optional[Ticket]:
    row = await db.fetchone(
        "SELECT * FROM events.ticket WHERE id = :id",
        {"id": payment_hash},
    )
    if not row:
        return None

    return Ticket(**_parse_ticket_row(row))


async def get_tickets(wallet_ids: str | list[str]) -> list[Ticket]:
    if isinstance(wallet_ids, str):
        wallet_ids = [wallet_ids]
    q = ",".join([f"'{wallet_id}'" for wallet_id in wallet_ids])
    rows = await db.fetchall(f"SELECT * FROM events.ticket WHERE wallet IN ({q})")

    return [Ticket(**_parse_ticket_row(row)) for row in rows]


async def get_tickets_by_user_id(user_id: str) -> list[Ticket]:
    """Get all tickets for a specific user by their user_id"""
    rows = await db.fetchall(
        "SELECT * FROM events.ticket WHERE user_id = :user_id ORDER BY time DESC",
        {"user_id": user_id}
    )

    return [Ticket(**_parse_ticket_row(row)) for row in rows]


async def delete_ticket(payment_hash: str) -> None:
    await db.execute("DELETE FROM events.ticket WHERE id = :id", {"id": payment_hash})


async def delete_event_tickets(event_id: str) -> None:
    await db.execute(
        "DELETE FROM events.ticket WHERE event = :event", {"event": event_id}
    )


async def purge_unpaid_tickets(event_id: str) -> None:
    time_diff = datetime.now() - timedelta(hours=24)
    await db.execute(
        f"""
        DELETE FROM events.ticket WHERE event = :event AND paid = false
        AND time < {db.timestamp_placeholder("time")}
        """,
        {"time": time_diff.timestamp(), "event": event_id},
    )


async def create_event(data: CreateEvent) -> Event:
    event_id = urlsafe_short_hash()
    # Default end date to start date if not provided
    if not data.event_end_date:
        data.event_end_date = data.event_start_date
    # Default closing date to end date if not provided
    if not data.closing_date:
        data.closing_date = data.event_end_date
    event = Event(id=event_id, time=datetime.now(timezone.utc), **data.dict())
    await db.insert("events.events", event)
    return event


async def update_event(event: Event) -> Event:
    await db.update("events.events", event)
    return event


async def get_event(event_id: str) -> Event | None:
    return await db.fetchone(
        "SELECT * FROM events.events WHERE id = :id",
        {"id": event_id},
        Event,
    )


async def get_events(wallet_ids: str | list[str]) -> list[Event]:
    if isinstance(wallet_ids, str):
        wallet_ids = [wallet_ids]
    q = ",".join([f"'{wallet_id}'" for wallet_id in wallet_ids])
    return await db.fetchall(
        f"SELECT * FROM events.events WHERE wallet IN ({q})",
        model=Event,
    )


async def get_all_events() -> list[Event]:
    """Get all events from the database without wallet filtering."""
    return await db.fetchall(
        "SELECT * FROM events.events ORDER BY time DESC",
        model=Event,
    )


async def get_public_events() -> list[Event]:
    """Get approved, non-canceled events for public display."""
    return await db.fetchall(
        """
        SELECT * FROM events.events
        WHERE status = 'approved' AND canceled = FALSE
        ORDER BY event_start_date ASC
        """,
        model=Event,
    )


async def get_pending_events() -> list[Event]:
    """Get proposed events awaiting admin approval."""
    return await db.fetchall(
        "SELECT * FROM events.events WHERE status = 'proposed' ORDER BY time DESC",
        model=Event,
    )


async def get_settings() -> EventsSettings:
    """Get extension settings (single row, always exists after migration)."""
    row = await db.fetchone("SELECT * FROM events.settings WHERE id = 1")
    if row:
        return EventsSettings(**dict(row))
    return EventsSettings()


async def update_settings(settings: EventsSettings) -> EventsSettings:
    """Update extension settings."""
    await db.execute(
        """
        UPDATE events.settings
        SET auto_approve = :auto_approve
        WHERE id = 1
        """,
        {"auto_approve": settings.auto_approve},
    )
    return settings


async def delete_event(event_id: str) -> None:
    await db.execute("DELETE FROM events.events WHERE id = :id", {"id": event_id})


async def get_event_tickets(event_id: str) -> list[Ticket]:
    rows = await db.fetchall(
        "SELECT * FROM events.ticket WHERE event = :event",
        {"event": event_id},
    )

    return [Ticket(**_parse_ticket_row(row)) for row in rows]