# retoor <retoor@molodetz.nl>

from __future__ import annotations

from ..spec import Action, Param
from ._shared import body, path, query


DBAPI_ACTIONS: tuple[Action, ...] = (
    Action(
        name="db_list_tables",
        method="GET",
        path="/dbapi/tables",
        summary="List database tables exposed by the database API (primary administrator only)",
        description=(
            "Returns every table reachable through the database API with its row count and "
            "whether it uses soft deletes. Use this to discover what data exists before "
            "querying or designing a query."
        ),
        params=(),
        requires_admin=True,
        requires_primary_admin=True,
    ),
    Action(
        name="db_table_schema",
        method="GET",
        path="/dbapi/{table}/schema",
        summary="Show a table's columns and types (primary administrator only)",
        description="Returns the column names, types, row count, and soft-delete flag for one table.",
        params=(path("table", "Table name (from db_list_tables)."),),
        requires_admin=True,
        requires_primary_admin=True,
    ),
    Action(
        name="db_list_rows",
        method="GET",
        path="/dbapi/{table}",
        summary="List rows of a table with keyset pagination (primary administrator only)",
        description=(
            "Browses rows newest-first. Soft-deleted rows are excluded unless include_deleted is "
            "true. For filtered or joined questions prefer db_query or db_design_query."
        ),
        params=(
            path("table", "Table name."),
            query("limit", "Maximum rows (1-500, default 25)."),
            query("search", "Free-text search over common text columns."),
            query("before", "Keyset cursor: return rows older than this created_at/id value."),
            Param(
                name="include_deleted",
                location="query",
                description="Include soft-deleted rows.",
                required=False,
                type="boolean",
            ),
        ),
        requires_admin=True,
        requires_primary_admin=True,
    ),
    Action(
        name="db_get_row",
        method="GET",
        path="/dbapi/{table}/{key}/{value}",
        summary="Fetch one row by a key column (primary administrator only)",
        description="Returns a single row where key column equals value (key is usually 'uid').",
        params=(
            path("table", "Table name."),
            path("key", "Key column to match (usually 'uid')."),
            path("value", "Value of the key column."),
        ),
        requires_admin=True,
        requires_primary_admin=True,
    ),
    Action(
        name="db_query",
        method="POST",
        path="/dbapi/query",
        summary="Run a read-only SQL SELECT and return rows (primary administrator only)",
        description=(
            "Executes a SINGLE validated SELECT statement read-only and returns the rows. Only "
            "SELECT is allowed; INSERT/UPDATE/DELETE/DDL are rejected. The database API is "
            "read-only and cannot change data in any way. The response may include a 'suspicious' "
            "list (e.g. a SELECT with no WHERE/JOIN/LIMIT that scans a whole table); when present, "
            "surface that warning to the user before trusting the results."
        ),
        params=(
            body("sql", "A single SELECT statement.", required=True),
            body("dialect", "Optional source SQL dialect (default sqlite)."),
        ),
        requires_admin=True,
        requires_primary_admin=True,
        read_only=True,
    ),
    Action(
        name="db_design_query",
        method="POST",
        path="/dbapi/nl",
        summary="Design a SQL SELECT from a natural-language question (primary administrator only)",
        description=(
            "Turns a plain-language question about one table into a validated read-only SELECT. "
            "It auto-adds 'deleted_at IS NULL' for soft-delete tables unless apply_soft_delete is "
            "false. By default it only returns the SQL; pass execute=true to also run it read-only "
            "and return rows. Show the user the SQL and any 'suspicious' notes."
        ),
        params=(
            body("question", "The natural-language request.", required=True),
            body("table", "Target table the question is about.", required=True),
            Param(
                name="apply_soft_delete",
                location="body",
                description="Add deleted_at IS NULL for soft-delete tables (default true).",
                required=False,
                type="boolean",
            ),
            body("dialect", "Optional target SQL dialect (default sqlite)."),
            Param(
                name="execute",
                location="body",
                description="Also run the validated query read-only and return rows.",
                required=False,
                type="boolean",
            ),
        ),
        requires_admin=True,
        requires_primary_admin=True,
        read_only=True,
    ),
)
