restaurant/crud.py

"""
Async CRUD layer for the restaurant extension.

All functions are coroutines that hit the Database singleton initialized
at module import time. Pydantic models are passed to db.insert/update so
nested objects (OpenHours, SocialLinks, lists, etc.) are JSON-serialized
consistently across SQLite + Postgres backends.

A note on JSON columns: db.insert() / db.update() handle serialization,
but db.fetchone(model=Model) / db.fetchall(model=Model) reverse it via
the model's pre-validators (defined in models.py).
"""

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

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

from .models import (
    MAX_MENU_DEPTH,
    AvailabilityWindow,
    CreateAvailabilityWindow,
    CreateMenuItem,
    CreateMenuNode,
    CreateModifier,
    CreateModifierGroup,
    CreateRestaurant,
    MenuItem,
    MenuNode,
    MenuNodeRow,
    Modifier,
    ModifierGroup,
    Order,
    OrderItemRow,
    PrintJob,
    Restaurant,
    RestaurantSettings,
    SelectedModifier,
)

db = Database("ext_restaurant")


# --------------------------------------------------------------------- #
# Restaurants                                                           #
# --------------------------------------------------------------------- #


async def create_restaurant(wallet: str, data: CreateRestaurant) -> Restaurant:
    restaurant = Restaurant(
        id=urlsafe_short_hash(),
        wallet=wallet,
        time=datetime.now(timezone.utc),
        **{k: v for k, v in data.dict().items() if k != "wallet"},
    )
    await db.insert("restaurant.restaurants", restaurant)
    return restaurant


async def update_restaurant(restaurant: Restaurant) -> Restaurant:
    await db.update("restaurant.restaurants", restaurant)
    return restaurant


async def get_restaurant(restaurant_id: str) -> Optional[Restaurant]:
    return await db.fetchone(
        "SELECT * FROM restaurant.restaurants WHERE id = :id",
        {"id": restaurant_id},
        Restaurant,
    )


async def get_restaurant_by_slug(slug: str) -> Optional[Restaurant]:
    return await db.fetchone(
        "SELECT * FROM restaurant.restaurants WHERE slug = :slug",
        {"slug": slug},
        Restaurant,
    )


async def get_restaurants(wallet_ids: str | list[str]) -> list[Restaurant]:
    if isinstance(wallet_ids, str):
        wallet_ids = [wallet_ids]
    q = ",".join([f"'{w}'" for w in wallet_ids])
    return await db.fetchall(
        f"SELECT * FROM restaurant.restaurants WHERE wallet IN ({q}) ORDER BY time DESC",
        model=Restaurant,
    )


async def get_all_restaurants() -> list[Restaurant]:
    return await db.fetchall(
        "SELECT * FROM restaurant.restaurants ORDER BY time DESC",
        model=Restaurant,
    )


async def delete_restaurant(restaurant_id: str) -> None:
    # Cascade by app logic — relational FKs aren't enforced cross-backend,
    # so we manually clean dependent rows in the right order.
    await db.execute(
        """
        DELETE FROM restaurant.print_jobs
        WHERE order_id IN (
            SELECT id FROM restaurant.orders WHERE restaurant_id = :rid
        )
        """,
        {"rid": restaurant_id},
    )
    await db.execute(
        """
        DELETE FROM restaurant.order_items
        WHERE order_id IN (
            SELECT id FROM restaurant.orders WHERE restaurant_id = :rid
        )
        """,
        {"rid": restaurant_id},
    )
    await db.execute(
        "DELETE FROM restaurant.orders WHERE restaurant_id = :rid",
        {"rid": restaurant_id},
    )
    await db.execute(
        """
        DELETE FROM restaurant.modifiers
        WHERE group_id IN (
            SELECT mg.id FROM restaurant.modifier_groups mg
            JOIN restaurant.menu_items mi ON mg.menu_item_id = mi.id
            WHERE mi.restaurant_id = :rid
        )
        """,
        {"rid": restaurant_id},
    )
    await db.execute(
        """
        DELETE FROM restaurant.modifier_groups
        WHERE menu_item_id IN (
            SELECT id FROM restaurant.menu_items WHERE restaurant_id = :rid
        )
        """,
        {"rid": restaurant_id},
    )
    await db.execute(
        """
        DELETE FROM restaurant.availability_windows
        WHERE menu_item_id IN (
            SELECT id FROM restaurant.menu_items WHERE restaurant_id = :rid
        )
        """,
        {"rid": restaurant_id},
    )
    await db.execute(
        "DELETE FROM restaurant.menu_items WHERE restaurant_id = :rid",
        {"rid": restaurant_id},
    )
    await db.execute(
        "DELETE FROM restaurant.menu_nodes WHERE restaurant_id = :rid",
        {"rid": restaurant_id},
    )
    await db.execute(
        "DELETE FROM restaurant.restaurants WHERE id = :id",
        {"id": restaurant_id},
    )


# --------------------------------------------------------------------- #
# Menu nodes (tree)                                                     #
# --------------------------------------------------------------------- #


async def create_menu_node(data: CreateMenuNode) -> MenuNode:
    """
    Insert a node. Depth + path are derived from parent.
    Raises ValueError if parent doesn't exist, lives on a different
    restaurant, or sits at MAX_MENU_DEPTH (would push the new node
    past the cap).
    """
    new_id = urlsafe_short_hash()
    if data.parent_id:
        parent = await get_menu_node(data.parent_id)
        if not parent or parent.restaurant_id != data.restaurant_id:
            raise ValueError("Parent not found or in another restaurant")
        if parent.depth >= MAX_MENU_DEPTH:
            raise ValueError(
                f"Cannot create node: depth {MAX_MENU_DEPTH + 1} exceeds "
                f"max depth ({MAX_MENU_DEPTH + 1})"
            )
        depth = parent.depth + 1
        path = f"{parent.path}/{new_id}"
    else:
        depth, path = 0, new_id

    row = MenuNodeRow(
        id=new_id,
        restaurant_id=data.restaurant_id,
        parent_id=data.parent_id,
        name=data.name,
        description=data.description,
        sort_order=data.sort_order,
        image_url=data.image_url,
        depth=depth,
        path=path,
        time=datetime.now(timezone.utc),
    )
    await db.insert("restaurant.menu_nodes", row)
    return MenuNode(**row.dict())


async def update_menu_node(node: MenuNodeRow | MenuNode) -> MenuNodeRow:
    """Update name / description / sort_order / image_url. Tree
    position changes go through move_menu_node."""
    row = MenuNodeRow(**{k: v for k, v in node.dict().items()
                          if k in MenuNodeRow.__fields__})
    await db.update("restaurant.menu_nodes", row)
    return row


async def get_menu_node(node_id: str) -> Optional[MenuNodeRow]:
    return await db.fetchone(
        "SELECT * FROM restaurant.menu_nodes WHERE id = :id",
        {"id": node_id},
        MenuNodeRow,
    )


async def get_menu_nodes(restaurant_id: str) -> list[MenuNodeRow]:
    return await db.fetchall(
        """
        SELECT * FROM restaurant.menu_nodes
        WHERE restaurant_id = :rid
        ORDER BY depth, sort_order, time
        """,
        {"rid": restaurant_id},
        model=MenuNodeRow,
    )


async def get_menu_tree(restaurant_id: str) -> list[MenuNode]:
    """
    Build the full hydrated tree for a restaurant: every node + every
    item, in one pair of queries, assembled in O(n+m) Python. For
    n=5..50 nodes and m=10..200 items this is microseconds — far
    simpler than recursive CTEs and identical on SQLite + Postgres.
    """
    rows = await get_menu_nodes(restaurant_id)
    items = await get_menu_items(restaurant_id)

    by_id: dict[str, MenuNode] = {
        r.id: MenuNode(**r.dict()) for r in rows
    }
    roots: list[MenuNode] = []
    for r in rows:
        node = by_id[r.id]
        if r.parent_id and r.parent_id in by_id:
            by_id[r.parent_id].children.append(node)
        else:
            roots.append(node)
    for it in items:
        if it.node_id and it.node_id in by_id:
            by_id[it.node_id].items.append(it)
    return roots


async def move_menu_node(
    node_id: str, new_parent_id: Optional[str]
) -> MenuNodeRow:
    """
    Move a node (and its entire subtree) under a new parent, or to
    the root if new_parent_id is None.

    Single-statement subtree path rewrite using SUBSTR + concat:
    `path = new_prefix || SUBSTR(path, len(old_path) + 1)`. SQLite's
    SUBSTR is 1-indexed (matches Postgres).

    Raises ValueError on:
      * missing node / parent
      * cross-restaurant move
      * cycle (new_parent_id is in the moved node's subtree)
      * any descendant would exceed MAX_MENU_DEPTH after the move
    """
    node = await get_menu_node(node_id)
    if not node:
        raise ValueError("Node not found")

    if new_parent_id:
        parent = await get_menu_node(new_parent_id)
        if not parent or parent.restaurant_id != node.restaurant_id:
            raise ValueError("Parent not found or in another restaurant")
        # Cycle prevention: parent must not be inside node's subtree.
        if node_id in parent.path.split("/"):
            raise ValueError("Cannot move a node into its own subtree")
        new_depth = parent.depth + 1
        new_prefix = f"{parent.path}/{node_id}"
    else:
        new_depth = 0
        new_prefix = node_id

    # Reject if any descendant would exceed MAX_MENU_DEPTH.
    max_d_row = await db.fetchone(
        """
        SELECT MAX(depth) AS max_depth FROM restaurant.menu_nodes
        WHERE path = :p OR path LIKE :p || '/%'
        """,
        {"p": node.path},
    )
    max_d = (max_d_row["max_depth"] if max_d_row else None) or node.depth
    delta_depth = new_depth - node.depth
    if max_d + delta_depth > MAX_MENU_DEPTH:
        raise ValueError(
            f"Move would exceed max depth ({MAX_MENU_DEPTH + 1})"
        )

    old_path = node.path
    await db.execute(
        """
        UPDATE restaurant.menu_nodes
        SET    path  = :new_prefix || SUBSTR(path, :old_len + 1),
               depth = depth + :delta
        WHERE  path = :old_path
            OR path LIKE :old_path || '/%'
        """,
        {
            "new_prefix": new_prefix,
            "old_len": len(old_path),
            "delta": delta_depth,
            "old_path": old_path,
        },
    )
    await db.execute(
        "UPDATE restaurant.menu_nodes SET parent_id = :pid WHERE id = :id",
        {"pid": new_parent_id, "id": node_id},
    )

    refreshed = await get_menu_node(node_id)
    assert refreshed
    return refreshed


async def delete_menu_node(node_id: str, cascade: bool = False) -> None:
    """
    Delete a node. If it has children or items:
      * cascade=False (default): raise ValueError. The CMS prompts
        the operator to confirm before passing cascade=True.
      * cascade=True: delete the entire subtree of nodes, but
        DETACH items (set node_id NULL) rather than wipe them.
        Items carry nostr_event_ids and are revenue-bearing —
        orphaning them so the operator can re-home is friendlier
        than deleting.
    """
    node = await get_menu_node(node_id)
    if not node:
        return

    has_children_row = await db.fetchone(
        "SELECT 1 AS one FROM restaurant.menu_nodes WHERE parent_id = :id LIMIT 1",
        {"id": node_id},
    )
    has_items_row = await db.fetchone(
        "SELECT 1 AS one FROM restaurant.menu_items WHERE node_id = :id LIMIT 1",
        {"id": node_id},
    )
    if (has_children_row or has_items_row) and not cascade:
        raise ValueError(
            "Node has children or items; pass cascade=true to delete"
        )

    if cascade:
        # Detach items in the entire subtree.
        await db.execute(
            """
            UPDATE restaurant.menu_items
            SET    node_id = NULL
            WHERE  node_id IN (
                SELECT id FROM restaurant.menu_nodes
                WHERE  path = :p OR path LIKE :p || '/%'
            )
            """,
            {"p": node.path},
        )
        await db.execute(
            """
            DELETE FROM restaurant.menu_nodes
            WHERE  path = :p OR path LIKE :p || '/%'
            """,
            {"p": node.path},
        )
    else:
        await db.execute(
            "DELETE FROM restaurant.menu_nodes WHERE id = :id",
            {"id": node_id},
        )


# --------------------------------------------------------------------- #
# Menu items                                                            #
# --------------------------------------------------------------------- #


async def create_menu_item(data: CreateMenuItem) -> MenuItem:
    item = MenuItem(
        id=urlsafe_short_hash(),
        time=datetime.now(timezone.utc),
        **data.dict(),
    )
    await db.insert("restaurant.menu_items", item)
    return item


async def update_menu_item(item: MenuItem) -> MenuItem:
    await db.update("restaurant.menu_items", item)
    return item


async def get_menu_item(item_id: str) -> Optional[MenuItem]:
    return await db.fetchone(
        "SELECT * FROM restaurant.menu_items WHERE id = :id",
        {"id": item_id},
        MenuItem,
    )


async def get_menu_items(restaurant_id: str) -> list[MenuItem]:
    return await db.fetchall(
        """
        SELECT * FROM restaurant.menu_items
        WHERE restaurant_id = :rid
        ORDER BY sort_order, time
        """,
        {"rid": restaurant_id},
        model=MenuItem,
    )


async def get_menu_item_by_nostr_event(event_id: str) -> Optional[MenuItem]:
    return await db.fetchone(
        "SELECT * FROM restaurant.menu_items WHERE nostr_event_id = :nid",
        {"nid": event_id},
        MenuItem,
    )


async def delete_menu_item(item_id: str) -> None:
    await db.execute(
        """
        DELETE FROM restaurant.modifiers
        WHERE group_id IN (
            SELECT id FROM restaurant.modifier_groups WHERE menu_item_id = :mid
        )
        """,
        {"mid": item_id},
    )
    await db.execute(
        "DELETE FROM restaurant.modifier_groups WHERE menu_item_id = :mid",
        {"mid": item_id},
    )
    await db.execute(
        "DELETE FROM restaurant.availability_windows WHERE menu_item_id = :mid",
        {"mid": item_id},
    )
    await db.execute(
        "DELETE FROM restaurant.menu_items WHERE id = :id",
        {"id": item_id},
    )


async def adjust_stock(item_id: str, delta: int) -> Optional[MenuItem]:
    """Decrement (negative delta) or increment stock atomically."""
    item = await get_menu_item(item_id)
    if not item or item.stock is None:
        return item
    item.stock = max(0, item.stock + delta)
    return await update_menu_item(item)


# --------------------------------------------------------------------- #
# Modifier groups + modifiers                                           #
# --------------------------------------------------------------------- #


async def create_modifier_group(data: CreateModifierGroup) -> ModifierGroup:
    grp = ModifierGroup(
        id=urlsafe_short_hash(),
        time=datetime.now(timezone.utc),
        **data.dict(),
    )
    await db.insert("restaurant.modifier_groups", grp)
    return grp


async def update_modifier_group(grp: ModifierGroup) -> ModifierGroup:
    await db.update("restaurant.modifier_groups", grp)
    return grp


async def get_modifier_groups(menu_item_id: str) -> list[ModifierGroup]:
    return await db.fetchall(
        """
        SELECT * FROM restaurant.modifier_groups
        WHERE menu_item_id = :mid
        ORDER BY sort_order, time
        """,
        {"mid": menu_item_id},
        model=ModifierGroup,
    )


async def delete_modifier_group(group_id: str) -> None:
    await db.execute(
        "DELETE FROM restaurant.modifiers WHERE group_id = :gid",
        {"gid": group_id},
    )
    await db.execute(
        "DELETE FROM restaurant.modifier_groups WHERE id = :id",
        {"id": group_id},
    )


async def create_modifier(data: CreateModifier) -> Modifier:
    mod = Modifier(
        id=urlsafe_short_hash(),
        time=datetime.now(timezone.utc),
        **data.dict(),
    )
    await db.insert("restaurant.modifiers", mod)
    return mod


async def update_modifier(mod: Modifier) -> Modifier:
    await db.update("restaurant.modifiers", mod)
    return mod


async def get_modifiers(group_id: str) -> list[Modifier]:
    return await db.fetchall(
        """
        SELECT * FROM restaurant.modifiers
        WHERE group_id = :gid
        ORDER BY sort_order, time
        """,
        {"gid": group_id},
        model=Modifier,
    )


async def delete_modifier(modifier_id: str) -> None:
    await db.execute(
        "DELETE FROM restaurant.modifiers WHERE id = :id",
        {"id": modifier_id},
    )


# --------------------------------------------------------------------- #
# Availability windows                                                  #
# --------------------------------------------------------------------- #


async def create_availability_window(
    data: CreateAvailabilityWindow,
) -> AvailabilityWindow:
    win = AvailabilityWindow(
        id=urlsafe_short_hash(),
        time=datetime.now(timezone.utc),
        **data.dict(),
    )
    await db.insert("restaurant.availability_windows", win)
    return win


async def get_availability_windows(menu_item_id: str) -> list[AvailabilityWindow]:
    return await db.fetchall(
        """
        SELECT * FROM restaurant.availability_windows
        WHERE menu_item_id = :mid
        ORDER BY weekday NULLS FIRST, start_time
        """,
        {"mid": menu_item_id},
        model=AvailabilityWindow,
    )


async def delete_availability_window(window_id: str) -> None:
    await db.execute(
        "DELETE FROM restaurant.availability_windows WHERE id = :id",
        {"id": window_id},
    )


# --------------------------------------------------------------------- #
# Orders + order items                                                  #
# --------------------------------------------------------------------- #


async def create_order(order: Order) -> Order:
    """Insert an Order row. Caller must construct the Order with id set
    (typically id = payment_hash so we can look it up from the invoice
    listener with no extra metadata)."""
    await db.insert("restaurant.orders", order)
    return order


async def update_order(order: Order) -> Order:
    await db.update("restaurant.orders", order)
    return order


async def get_order(order_id: str) -> Optional[Order]:
    return await db.fetchone(
        "SELECT * FROM restaurant.orders WHERE id = :id",
        {"id": order_id},
        Order,
    )


async def get_order_by_payment_hash(payment_hash: str) -> Optional[Order]:
    return await db.fetchone(
        "SELECT * FROM restaurant.orders WHERE payment_hash = :ph",
        {"ph": payment_hash},
        Order,
    )


async def get_orders(
    restaurant_id: str,
    statuses: Optional[list[str]] = None,
    limit: int = 200,
) -> list[Order]:
    if statuses:
        placeholders = ",".join([f"'{s}'" for s in statuses])
        return await db.fetchall(
            f"""
            SELECT * FROM restaurant.orders
            WHERE restaurant_id = :rid AND status IN ({placeholders})
            ORDER BY time DESC
            LIMIT {int(limit)}
            """,
            {"rid": restaurant_id},
            model=Order,
        )
    return await db.fetchall(
        f"""
        SELECT * FROM restaurant.orders
        WHERE restaurant_id = :rid
        ORDER BY time DESC
        LIMIT {int(limit)}
        """,
        {"rid": restaurant_id},
        model=Order,
    )


async def create_order_item(item: OrderItemRow) -> OrderItemRow:
    await db.insert("restaurant.order_items", item)
    return item


async def get_order_items(order_id: str) -> list[OrderItemRow]:
    rows = await db.fetchall(
        "SELECT * FROM restaurant.order_items WHERE order_id = :oid ORDER BY time",
        {"oid": order_id},
    )
    out: list[OrderItemRow] = []
    for row in rows:
        d = dict(row)
        # selected_modifiers comes back as JSON string from db; parse here.
        sm = d.get("selected_modifiers")
        if isinstance(sm, str):
            try:
                d["selected_modifiers"] = [
                    SelectedModifier(**m) for m in (json.loads(sm) if sm else [])
                ]
            except json.JSONDecodeError:
                d["selected_modifiers"] = []
        out.append(OrderItemRow(**d))
    return out


# --------------------------------------------------------------------- #
# Print jobs                                                            #
# --------------------------------------------------------------------- #


async def create_print_job(restaurant_id: str, order_id: str) -> PrintJob:
    job = PrintJob(
        id=urlsafe_short_hash(),
        restaurant_id=restaurant_id,
        order_id=order_id,
        time=datetime.now(timezone.utc),
    )
    await db.insert("restaurant.print_jobs", job)
    return job


async def update_print_job(job: PrintJob) -> PrintJob:
    await db.update("restaurant.print_jobs", job)
    return job


async def get_print_job(job_id: str) -> Optional[PrintJob]:
    return await db.fetchone(
        "SELECT * FROM restaurant.print_jobs WHERE id = :id",
        {"id": job_id},
        PrintJob,
    )


async def get_print_jobs(
    restaurant_id: str, status: Optional[str] = None
) -> list[PrintJob]:
    if status:
        return await db.fetchall(
            """
            SELECT * FROM restaurant.print_jobs
            WHERE restaurant_id = :rid AND status = :status
            ORDER BY time DESC
            """,
            {"rid": restaurant_id, "status": status},
            model=PrintJob,
        )
    return await db.fetchall(
        """
        SELECT * FROM restaurant.print_jobs
        WHERE restaurant_id = :rid
        ORDER BY time DESC
        """,
        {"rid": restaurant_id},
        model=PrintJob,
    )


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


async def get_settings() -> RestaurantSettings:
    row = await db.fetchone("SELECT * FROM restaurant.settings WHERE id = 1")
    if row:
        d = dict(row)
        d.pop("id", None)
        return RestaurantSettings(**d)
    return RestaurantSettings()


async def update_settings(settings: RestaurantSettings) -> RestaurantSettings:
    await db.execute(
        """
        UPDATE restaurant.settings
        SET nostr_publish_enabled = :npe,
            nostr_orders_enabled = :noe,
            invoice_expiry_seconds = :ies,
            auto_accept_orders = :aao
        WHERE id = 1
        """,
        {
            "npe": settings.nostr_publish_enabled,
            "noe": settings.nostr_orders_enabled,
            "ies": settings.invoice_expiry_seconds,
            "aao": settings.auto_accept_orders,
        },
    )
    return settings