# SPDX-License-Identifier: BUSL-1.1
# Copyright (c) 2024-2026 Ali Sadeghi Aghili
"""The application database's schema — SQLAlchemy Core ``Table`` objects.

Core, not the ORM: this codebase's existing SQLAlchemy usage
(``database/connection.py``, ``database/executor.py``) is Core-only
(``conn.exec_driver_sql``/``text()``), and the key store's access pattern
is simple row lookups and updates with no object graph worth mapping.
:data:`metadata` is what ``appdb/migrations/env.py`` points Alembic's
autogenerate at, and what a fresh SQLite deployment's tables are created
from directly (:func:`create_all`) without a migration tool in the loop at
all, matching ``session/persistence.py``'s own "create tables if missing,
unconditionally" behaviour for the zero-configuration path.

Two tables this phase needs:

``admin_api_keys``
    The key store (§3). One row per issued key, keyed by
    ``key_sha256`` — never the raw key (see ``security/auth.py``'s module
    docstring for why only the digest is ever persisted). Revocation is a
    tombstone (``revoked_at``, never a ``DELETE`` — §3.4): restoring this
    database to a point before a leaked key was revoked must not silently
    un-revoke it.

``admin_principal_roles``
    Role grants (§2) — ``(principal_id, capability)`` pairs. Presence of a
    row means the principal currently holds that capability; a revoke
    deletes the row. This table holds *current state* only — the
    who/when/why history of each grant and revoke lives in the separate,
    append-only admin-action log (:mod:`appdb.admin_audit`), not here,
    for the same reason that log is a file and not a database table (see
    this package's docstring).

Neither table names this deployment's warehouse in any way — both are
pure key/role bookkeeping, unconditionally the same shape regardless of
which warehouse this deployment queries.
"""

from __future__ import annotations

from sqlalchemy import (
    Column,
    Engine,
    Integer,
    MetaData,
    String,
    Table,
    Text,
    UniqueConstraint,
)

metadata = MetaData()

#: One row per issued API key. ``key_sha256`` is the primary key (lookups
#: are always by presented-key hash, and it is unique by construction --
#: two principals sharing a hash is exactly the ambiguity
#: ``security.auth._parse_api_keys`` already refuses for the environment
#: bootstrap path). ``principal_id`` is deliberately NOT unique: a
#: principal may hold more than one live key at once (rotation), and a
#: revoked row is never deleted (§3.4), so a principal issued a new key
#: after an old one was revoked would otherwise collide on a uniqueness
#: constraint that has nothing to do with the actual security question.
admin_api_keys = Table(
    "admin_api_keys",
    metadata,
    Column("key_sha256", String(64), primary_key=True),
    Column("principal_id", String(255), nullable=False),
    Column("name", String(255), nullable=False),
    #: JSON-encoded array of column names, matching
    #: ``API_KEYS_JSON``'s own ``denied_columns`` shape. Populated with
    #: *every* column this deployment's schema.yaml knows about at issue
    #: time (:mod:`appdb.key_store`'s restrictive default — see its
    #: docstring for why), never empty for a freshly issued key.
    Column("denied_columns_json", String, nullable=False),
    #: ``"issued"`` for a key minted via ``POST /admin/keys``, or
    #: ``"imported_from_env"`` for a row created once, at start-up, from
    #: an ``API_KEYS_JSON`` entry that predates this table (§3.3).
    #: Bookkeeping only -- both kinds behave identically once resolved.
    Column("source", String(32), nullable=False),
    Column("created_at", String(64), nullable=False),
    Column("updated_at", String(64), nullable=False),
    #: NULL while enabled. Set/cleared by ``POST /admin/keys/{id}/disable``
    #: and ``.../enable`` -- reversible, unlike ``revoked_at`` below.
    Column("disabled_at", String(64), nullable=True),
    #: NULL until revoked, then never cleared again (§3.4's tombstone --
    #: "never delete a key row"). A restore of this database to a point
    #: before this column was set must not un-revoke a key that leaked.
    Column("revoked_at", String(64), nullable=True),
)

#: Current role-grant state (§2). One row per ``(principal_id,
#: capability)`` pair a principal currently holds; a revoke deletes the
#: row rather than tombstoning it -- see the module docstring for why the
#: history lives in the admin-action log instead.
admin_principal_roles = Table(
    "admin_principal_roles",
    metadata,
    Column("principal_id", String(255), primary_key=True),
    Column("capability", String(32), primary_key=True),
    Column("granted_at", String(64), nullable=False),
    #: The principal id that authorised this grant, or ``"bootstrap"`` for
    #: a role that predates any grant (there is no such row today -- every
    #: environment-bootstrapped role is resolved fresh from
    #: ``API_KEYS_JSON`` on every request, per
    #: ``docs/admin-panel-architecture.md`` §2.3's "never from a web
    #: flow" -- this column exists for the day a migration tool backfills
    #: one, not because this phase writes it).
    Column("granted_by", String(255), nullable=False),
)

#: Phase 3: the ``project_config/`` bundle's version history (§1, §6 of
#: ``docs/admin-panel-architecture.md`` and the phase 3 spec). One row per
#: bundle version -- a full snapshot of all nine ``project_config/*.yaml``
#: files, never a diff, so restoring any depth is a read rather than a
#: replay (spec §1). ``version_id`` is a plain autoincrementing integer:
#: the *order* versions were created in is exactly the information a
#: rollback and an audit trail both need, and an integer sequence is the
#: simplest thing that can never disagree with itself about that order
#: (unlike a timestamp, which two versions created within the same clock
#: tick could tie on).
#:
#: ``status`` is one of ``"applied"``, ``"draft"``, or ``"rejected"``
#: (spec §3.1's propose-and-approve flow). The *active* version is always
#: the highest ``version_id`` whose ``status`` is ``"applied"`` -- there is
#: deliberately no separate "is this the active one" flag to keep in sync;
#: see :mod:`appdb.config_versions` for why that single rule is sufficient
#: even across restores and approvals.
#:
#: ``content_json`` holds the full bundle as a JSON object
#: (``{filename: yaml_text}``, one entry per file) -- the same "JSON text
#: in a String/Text column" shape :data:`admin_api_keys`'s own
#: ``denied_columns_json`` already uses, chosen for the same reason: every
#: backend this application supports (SQLite, PostgreSQL, SQL Server,
#: MySQL) has a text column, and a bespoke JSON column type would be one
#: more thing :mod:`appdb.migrations` would need a backend-specific
#: mapping for (see the architecture's §5.4 on why type mapping across
#: backends must be explicit rather than incidental).
config_bundle_versions = Table(
    "config_bundle_versions",
    metadata,
    Column("version_id", Integer, primary_key=True, autoincrement=True),
    Column("status", String(16), nullable=False),
    Column("content_json", Text, nullable=False),
    #: sha256 hex digest of the canonical (sorted-key) JSON form of the
    #: content -- a cheap identity check (e.g. "did this restore actually
    #: change anything") without re-parsing every file.
    Column("content_hash", String(64), nullable=False),
    #: The version this row was optimistically based on (spec §8) -- the
    #: version the editor/reviewer had open when they saved. NULL only for
    #: the very first, bootstrap version, which has no prior version to be
    #: based on.
    Column("based_on_version", Integer, nullable=True),
    #: Set when this version's content is a revert of an earlier one
    #: (spec §2/§6.1's "revert, never reset") -- the version *number* that
    #: was restored from, never mutated itself.
    Column("restored_from_version", Integer, nullable=True),
    #: The single filename that was restored, when this version is a
    #: *per-file* restore (spec §1's "a single file may be restored from
    #: any version") rather than a restore of the whole bundle. NULL for a
    #: whole-bundle restore or an ordinary multi-file edit.
    Column("restored_file", String(64), nullable=True),
    Column("created_at", String(64), nullable=False),
    Column("created_by", String(255), nullable=False),
    #: Which capability authorised creating this version -- "operations"
    #: or "security" (never omitted, mirroring
    #: :attr:`appdb.admin_audit.AdminActionRecord.authorised_by`'s own
    #: reasoning: one principal may hold both roles at once).
    Column("created_by_capability", String(32), nullable=False),
    #: NULL until a draft is approved or rejected; the security admin who
    #: did so, thereafter (spec §3.1).
    Column("reviewed_by", String(255), nullable=True),
    Column("reviewed_at", String(64), nullable=True),
    Column("review_note", Text, nullable=True),
    #: Precomputed at creation time (never recomputed later, so a
    #: subsequent version's own diff/dry-run can never retroactively
    #: change what an earlier draft showed its reviewer): the structural
    #: diff (spec §4.3) and the offline dry-run summary (spec §5), each as
    #: a JSON object. NULL only for the bootstrap version, which has no
    #: prior version to diff or dry-run against.
    Column("diff_json", Text, nullable=True),
    Column("dry_run_json", Text, nullable=True),
)


#: Phase 4: wrong-answer feedback and its triage (spec §2, §3;
#: ``docs/admin-panel-architecture.md`` §3's tier-1 "closes a loop nothing
#: else closes"). One row per flag an analyst raised on one of their own
#: turns.
#:
#: Deliberately does NOT carry the question, the generated SQL, or any
#: result data -- spec §2.2: those already exist, keyed by the same
#: ``session_id``/``turn_id``, in the analyst audit log
#: (``observability/audit.py``, whose ``session_id``/``turn_id`` fields
#: exist precisely so this join is possible). Duplicating them here would
#: create a second place for the same content to leak from and a second
#: thing to keep consistent, and would put row-adjacent question text in
#: yet another store. ``request_id`` and ``config_version_id`` are resolved
#: from that same audit record at flag time (see :mod:`appdb.feedback`) and
#: stored here only as identifiers -- the same "an id, never the content it
#: names" category ``observability.audit.AuditRecord.session_id`` already
#: is.
turn_feedback = Table(
    "turn_feedback",
    metadata,
    Column("feedback_id", Integer, primary_key=True, autoincrement=True),
    Column("session_id", String(64), nullable=False),
    Column("turn_id", String(64), nullable=False),
    #: The audit record's own request id, resolved by joining on
    #: (session_id, turn_id) at flag time -- never supplied by the client.
    #: NULL only if no audit record could be found for this turn (e.g. the
    #: audit log has since rotated past it).
    Column("request_id", String(64), nullable=True),
    Column("reporter_principal_id", String(255), nullable=False),
    #: One of :data:`appdb.feedback.FEEDBACK_CATEGORIES`.
    Column("category", String(32), nullable=False),
    #: The analyst's optional free-text note (spec §2.1) -- never the
    #: question or the SQL, which the join above already supplies.
    Column("note", Text, nullable=True),
    #: The configuration version (:mod:`appdb.config_versions`) active when
    #: this turn's answer was produced, resolved from the audit record --
    #: never the version active at flag time, which may already differ.
    #: NULL when the audit record predates this field or carries no value.
    Column("config_version_id", Integer, nullable=True),
    Column("created_at", String(64), nullable=False),
    #: ``"open"`` until triaged, then ``"resolved"`` -- spec §3's "a triage
    #: queue that lets items vanish without a decision becomes an inbox
    #: nobody opens": there is no third state and no delete.
    Column("status", String(16), nullable=False),
    #: One of :data:`appdb.feedback.RESOLUTION_OUTCOMES`, set only once
    #: ``status`` becomes ``"resolved"``.
    Column("resolution_outcome", String(32), nullable=True),
    #: Free-text reason -- required for ``"not_a_defect"`` (spec §3.1:
    #: "recorded with a reason, not silently dropped").
    Column("resolution_note", Text, nullable=True),
    #: For ``"alias_fix"``/``"rule_fix"`` outcomes: the id of an existing
    #: ``config_bundle_versions`` row the admin made the actual edit
    #: through (``POST /admin/config/versions`` -- phase 3, unchanged by
    #: this phase) and is linking here for provenance. This table never
    #: creates or applies a configuration version itself -- see
    #: :mod:`appdb.feedback`'s module docstring for why that is what makes
    #: "nothing auto-applies" (spec §3.2) trivially true rather than merely
    #: asserted.
    Column("resolution_config_version_id", Integer, nullable=True),
    #: For the ``"golden_case"`` outcome: the id assigned to the case
    #: written to the golden-set file (spec §4). NULL for every other
    #: outcome.
    Column("resolution_golden_case_id", String(255), nullable=True),
    Column("resolved_by", String(255), nullable=True),
    Column("resolved_at", String(64), nullable=True),
)


#: ADR-004 part 1: "Request access" from a denied-column guard rejection
#: (``docs/design/DESIGN-INVARIANTS.md`` §8's failure-anatomy table). One
#: row per request an analyst raised for one denied column.
#:
#: Deliberately does NOT carry the question or the SQL -- the same "an id,
#: never the content it names" reasoning :data:`turn_feedback` already
#: documents above: ``session_id``/``turn_id`` let a reviewer join back to
#: the audit record (``observability/audit.py``) for the question, and
#: ``column_name`` is resolved from that same audit record's
#: ``guard.subject`` at submit time (:mod:`appdb.access_requests`) --
#: never accepted from the client, so this column can never be forged into
#: naming a column the turn was never actually denied.
access_requests = Table(
    "access_requests",
    metadata,
    Column("request_id", Integer, primary_key=True, autoincrement=True),
    Column("session_id", String(64), nullable=False),
    Column("turn_id", String(64), nullable=False),
    #: Stamped server-side from the authenticated principal -- never
    #: accepted from the request body (see :mod:`appdb.access_requests`).
    Column("requester_principal_id", String(255), nullable=False),
    #: Resolved server-side from the joined audit record's
    #: ``guard.subject`` -- see the table comment above.
    Column("column_name", String(255), nullable=False),
    Column("created_at", String(64), nullable=False),
    #: ``"open"`` until a security admin decides it, then ``"approved"``
    #: or ``"denied"`` -- no third state, and no delete, mirroring
    #: :data:`turn_feedback.status`'s own "no dismiss without a decision".
    Column("status", String(16), nullable=False),
    #: Free-text reason. Required (non-blank) for a ``"denied"``
    #: resolution -- visible to the requester, never to another analyst.
    #: NULL for an open or approved request.
    Column("resolution_note", Text, nullable=True),
    Column("resolved_by", String(255), nullable=True),
    Column("resolved_at", String(64), nullable=True),
)


#: "This answer is correct" (پاسخ درست است): one analyst's confirmation of
#: one turn's answer. The positive counterpart of :data:`turn_feedback`,
#: and bound by the same rule: **identifiers only**. A row never carries
#: the question, the SQL or any result data -- those already exist, keyed
#: by the same ``session_id``/``turn_id``, in the analyst audit log
#: (``observability/audit.py``), and ``request_id`` is resolved from that
#: audit record at confirmation time (:mod:`appdb.confirmations`), never
#: accepted from the client.
#:
#: At most one row per ``(session_id, turn_id, principal_id)`` -- enforced
#: by the unique constraint below, not only by the module's
#: select-then-insert, so two concurrent clicks cannot store two rows.
#: Withdrawing a confirmation deletes the row (current state only, like
#: :data:`admin_principal_roles`): there is no tombstone, because nothing
#: downstream needs the history of a click that was taken back.
turn_confirmations = Table(
    "turn_confirmations",
    metadata,
    Column("confirmation_id", Integer, primary_key=True, autoincrement=True),
    Column("session_id", String(64), nullable=False),
    Column("turn_id", String(64), nullable=False),
    #: Stamped server-side from the authenticated principal -- never
    #: accepted from the request body.
    Column("principal_id", String(255), nullable=False),
    #: The audit record's own request id, resolved by joining on
    #: (session_id, turn_id) when the confirmation was stored.
    Column("request_id", String(64), nullable=True),
    Column("created_at", String(64), nullable=False),
    UniqueConstraint(
        "session_id", "turn_id", "principal_id", name="uq_turn_confirmations_turn_principal",
    ),
)


def create_all(engine: Engine) -> None:
    """Create every table in :data:`metadata` that does not already exist.

    ``checkfirst=True`` (the default) makes this the SQLAlchemy-Core
    equivalent of ``session/persistence.py``'s pre-Alembic ``CREATE TABLE
    IF NOT EXISTS`` -- the zero-configuration path (unset ``APP_DB_URL``,
    a SQLite file) needs no migration tool at all. A deployment on a
    managed backend that goes through Alembic instead
    (``appdb/migrations/``) never calls this directly; Alembic's own
    ``env.py`` points at this same :data:`metadata` so the two paths can
    never define the schema differently.
    """
    metadata.create_all(engine)