"""SQLAlchemy database models."""

from uuid import uuid4

from sqlalchemy import (
    Boolean,
    Column,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    text,
)
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

from src.db.postgres.connection import Base


class User(Base):
    """User model."""
    __tablename__ = "users"

    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    fullname = Column(String, nullable=False)
    email = Column(String, nullable=False, unique=True, index=True)
    password = Column(String, nullable=False)  # bcrypt-hashed
    company = Column(String)
    company_size = Column(String)
    function = Column(String)
    site = Column(String)
    role = Column(String)
    status = Column(String, nullable=False, default="active")  # active | inactive
    created_at = Column(DateTime(timezone=True), server_default=func.now())


class Document(Base):
    """Document model."""
    __tablename__ = "documents"

    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String, nullable=False, index=True)
    filename = Column(String, nullable=False)
    blob_name = Column(String, nullable=False, unique=True)
    file_size = Column(Integer)
    file_type = Column(String)  # pdf, docx, txt, etc.
    status = Column(String, default="uploaded")  # uploaded, processing, completed, failed
    processed_at = Column(DateTime(timezone=True))
    error_message = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())


class Room(Base):
    """Room model for chat sessions."""
    __tablename__ = "rooms"

    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String, nullable=False, index=True)
    title = Column(String, default="New Chat")
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

    status = Column(String, nullable=False, default="active")  # active | inactive

    messages = relationship("ChatMessage", back_populates="room", cascade="all, delete-orphan")


class ChatMessage(Base):
    """Chat message model."""
    __tablename__ = "chat_messages"

    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    room_id = Column(String, ForeignKey("rooms.id"), nullable=False, index=True)
    role = Column(String, nullable=False)  # user, assistant
    content = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    room = relationship("Room", back_populates="messages")
    sources = relationship("MessageSource", back_populates="message", cascade="all, delete-orphan")


class MessageSource(Base):
    """Sources (RAG references) attached to an assistant message."""
    __tablename__ = "message_sources"

    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    message_id = Column(String, ForeignKey("chat_messages.id", ondelete="CASCADE"), nullable=False, index=True)
    document_id = Column(String)
    filename = Column(Text)
    page_label = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    message = relationship("ChatMessage", back_populates="sources")


class DatabaseClient(Base):
    """User-registered external database connections."""
    __tablename__ = "databases"

    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String, nullable=False, index=True)
    name = Column(String, nullable=False)       # display name, e.g. "Prod DB"
    db_type = Column(String, nullable=False)    # postgres|mysql|sqlserver|supabase|bigquery|snowflake
    credentials = Column(JSONB, nullable=False) # per-type JSON; sensitive fields Fernet-encrypted
    status = Column(String, nullable=False, default="active")  # active | inactive
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())


class Catalog(Base):
    """Data catalog — dedorch **`data_catalog`** (Go-owned; reconciled 2026-07-01).

    Mirrors Go migration `0001`/`0002`. One jsonb `catalog_payload` per scope:
    `scope_type='user'` rows are keyed by `user_id` (partial unique index),
    `scope_type='analysis'` rows by `analysis_id`. Python is **consumer-only** —
    Go's `catalog.Service` owns all writes (DB/file ingestion); `CatalogStore`
    reads the user-scoped catalog and its write methods are legacy.

    `catalog_payload` holds the full Pydantic Catalog (src/catalog/models.py:Catalog)
    serialized via `model_dump(mode="json")`; the read path rehydrates with
    `Catalog.model_validate(...)`. Go writes the same shape (json tags match).
    """
    __tablename__ = "data_catalog"

    id = Column(UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4()))
    scope_type = Column(String, nullable=False, default="user")  # 'user' | 'analysis'
    user_id = Column(String, nullable=False, index=True)
    analysis_id = Column(UUID(as_uuid=False), nullable=True)
    catalog_payload = Column(JSONB, nullable=False)
    schema_version = Column(String, nullable=False, default="1.0")
    generated_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())

    __table_args__ = (
        Index(
            "idx_data_catalog_user_scope",
            "user_id",
            unique=True,
            postgresql_where=text("scope_type = 'user'"),
        ),
        Index(
            "idx_data_catalog_analysis_scope",
            "analysis_id",
            unique=True,
            postgresql_where=text("scope_type = 'analysis'"),
        ),
    )


class ReportInputRow(Base):
    """One row per completed slow-path analysis (the report's source of truth).

    `data` holds the full Pydantic AnalysisRecord
    (src/agents/slow_path/schemas.py:AnalysisRecord) serialized via
    `model_dump(mode="json")`; the read path rehydrates with
    `AnalysisRecord.model_validate(...)`. Many records accumulate per analysis
    session — `generate_report` reads them by `analysis_id`, oldest-first.

    `analysis_id` is nullable until the Analysis State (owned upstream) is wired
    into the slow path; records still persist (and carry `user_id`) before then.

    OWNERSHIP / HANDOFF (#21/#22, 2026-06-25 checkpoint): table **renamed `analysis_records`
    → `report_inputs`** — it holds the inputs report generation reads (the slow-path run
    records). "report_inputs" avoids clashing with Go's `analyses_messages` and with Langfuse
    observability. **Python-owned for now** (Python still creates it locally); the finalized
    schema goes to Harry so the dedorch migration creates it post-cutover (#22), where
    `id`/`analysis_id` will be `uuid` (+ FK to `analyses(id)`). The Pydantic `AnalysisRecord`
    (the in-memory run object) is intentionally kept. Slated to migrate to Go ownership later —
    keep this + DEV_PLAN #21/#22 as the handoff record. NOTE: dedorch currently still has the
    OLD `analysis_records` table (empty) until Harry's rename migration lands.
    """
    __tablename__ = "report_inputs"

    # id/analysis_id are `uuid` to match dedorch's `report_inputs` + the analysis-family
    # (analyses/reports/data_sources). No FK declared in Python (dedorch's migration owns it, #22).
    id = Column(UUID(as_uuid=False), primary_key=True)  # AnalysisRecord.record_id (uuid hex ok)
    analysis_id = Column(UUID(as_uuid=False), index=True)  # the analysis session id (nullable for now)
    user_id = Column(String, nullable=False, index=True)
    plan_id = Column(String, nullable=False)
    data = Column(JSONB, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())


class AnalysisReportRow(Base):
    """One immutable row per generated report version — dedorch `reports` (Go-owned).

    dedorch stores the rendered markdown `content` + `title` + `version` (no jsonb
    snapshot — markdown-only per the 2026-06-23 checkpoint). The read path rebuilds a
    minimal `AnalysisReport` (structured fields empty; `rendered_markdown` = content).
    Versions accumulate per analysis; versioning is serialized by a per-analysis
    advisory lock in `ReportStore`. Class name kept; table + shape changed for dedorch.
    """
    __tablename__ = "reports"

    id = Column(UUID(as_uuid=False), primary_key=True)  # AnalysisReport.report_id (uuid)
    analysis_id = Column(UUID(as_uuid=False), nullable=False, index=True)
    # Added by Go 2026-07 (text, like `analyses.user_id`); NOT NULL in some deployments,
    # nullable in others — declared nullable here so Python matches the loosest shape,
    # but ReportStore always writes it (a missing value 500s on the strict DBs).
    user_id = Column(Text)
    title = Column(String, nullable=False)
    content = Column(Text, nullable=False)  # rendered markdown
    generated_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())
    version = Column(Integer, nullable=False)


class AnalysisStateRow(Base):
    """Per-analysis session state — the dedorch **`analyses`** table (plural; Go-owned).

    One session = one analysis = one conversation; `id` is the shared session id
    (canonical UUID). Verified against the dedorch DB 2026-06-25.

    dedorch `analyses` columns (reconciled 2026-07-01 — Harry's #3 landed): `id` (uuid),
    `analysis_title`, `objective` (text), `business_questions` (jsonb), `user_id` (text),
    `report_id` (uuid), `status` (text 'active'|'inactive' — soft-delete), `data_bind` (jsonb),
    `data_bind_version` (int), `report_collection` (jsonb), `created_at`, `updated_at`.

    `problem_statement`/`problem_validated` were DROPPED in dedorch (#3) and removed here;
    `objective` + `business_questions` (the user-entered goal, set at onboarding by Go) replace
    them. The FE/Go columns (`status`/`data_bind*`/`report_collection`) are carried to match
    dedorch but are NOT surfaced in the `AnalysisState` pydantic contract. `analysis` (singular)
    is the deprecated DUPLICATE table Harry will drop — never use it. Class name kept.
    """
    __tablename__ = "analyses"

    id = Column(UUID(as_uuid=False), primary_key=True)  # shared session id (uuid)
    analysis_title = Column(String, nullable=False, default="New analysis")
    objective = Column(Text, nullable=False, default="")
    business_questions = Column(JSONB, nullable=False, default=list)
    user_id = Column(String, nullable=False, index=True)  # was owner_id (dedorch uses user_id)
    report_id = Column(UUID(as_uuid=False), nullable=True)
    # dedorch `analyses` columns (FE/Go concerns; carried so create_all matches dedorch).
    status = Column(String, nullable=False, default="active")  # active | inactive (soft-delete)
    data_bind = Column(JSONB, nullable=False, default=list)
    data_bind_version = Column(Integer, nullable=False, default=1)
    report_collection = Column(JSONB, nullable=False, default=list)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class AnalysesMessageRow(Base):
    """One conversation message — dedorch `analyses_messages` (Go-owned table).

    The analysis chat room (user question + AI answer), replacing the deprecated
    `rooms`/`chat_messages`. Python is a **consumer/writer** here: it INSERTs and reads
    rows but does NOT own the table (Go's migration creates it). Shape mirrors the Go
    contract (`API_CONTRACT_BE_GOLANG.md` §Analysis Messages): `role ∈ user|ai`. RAG source
    citations are NOT persisted here — the old `message_sources` table is deprecated along
    with `chat_messages`.
    """
    __tablename__ = "analyses_messages"

    id = Column(UUID(as_uuid=False), primary_key=True)
    analysis_id = Column(UUID(as_uuid=False), nullable=False, index=True)
    user_id = Column(String, nullable=False, index=True)
    role = Column(String, nullable=False)  # user | ai
    content = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())


class MessageTraceabilityRow(Base):
    """One row per assistant turn — user-facing provenance (KM-691).

    `data` holds the full Pydantic `TraceabilityPayload`
    (src\\traceability\\schemas.py:TraceabilityPayload) serialized via
    `model_dump(mode="json", by_alias=True)`; the read path rehydrates with
    `TraceabilityPayload.model_validate(...)`. One row per assistant `message_id`
    (the Python-minted turn id, a UUID string), written before the `done` SSE event
    and served by `GET /api/v1/traceability`.

    OWNERSHIP / HANDOFF (KM-691): **Python-owned for now**, the same pattern as
    `report_inputs` (ReportInputRow). Post-cutover `init_db` no longer runs
    `create_all`, so the table is created by a one-time manual DDL run against
    dedorch (Rifqi, 2026-07-06); the finalized schema goes to Harry so the dedorch
    migration creates it later (`message_id`/`analysis_id` gain the FK to
    `analyses(id)` there). Distinct from Langfuse observability — this is unmasked,
    user-facing provenance, not engineering telemetry.
    """
    __tablename__ = "message_traceability"

    message_id = Column(String, primary_key=True)  # Python-minted turn id (UUID string)
    analysis_id = Column(UUID(as_uuid=False), nullable=False, index=True)  # analysis session id
    user_id = Column(String, nullable=False)
    intent = Column(String, nullable=False)
    data = Column(JSONB, nullable=False)  # full TraceabilityPayload (source of truth)
    created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())


class MessageChartRow(Base):
    """One row per chart produced by `render_chart` — S2 visualization (SPINE_V2_PLAN §4.4).

    `spec` holds the full `dataeyond.chart.v1` envelope (SPINE_V2_PLAN §4.2) exactly as
    returned by the tool — `{schema, chart_type, title, plotly: {data, layout}}` — it is
    the source of truth the FE renders with
    `Plotly.newPlot(el, spec.plotly.data, spec.plotly.layout)`. Unlike
    `MessageTraceabilityRow` (one row per turn), **multiple rows can share one
    `message_id`** — one row per chart a turn produced. Written before the `done` SSE
    event and served by `GET /api/v1/charts` (SPINE_V2_PLAN §4.4/§4.5).

    OWNERSHIP / HANDOFF (SPINE_V2_PLAN §4.4, 2026-07-13): **Python-owned for now**, the
    same pattern as `report_inputs` / `message_traceability`. Post-cutover `init_db` no
    longer runs `create_all`, so the table is created by a one-time manual DDL run
    against dedorch (Rifqi, 2026-07-13); the finalized schema goes to Harry so the
    dedorch migration re-creates it later (`analysis_id` gains the FK to `analyses(id)`
    there).
    """
    __tablename__ = "message_charts"

    # Client-minted default — the DDL's `gen_random_uuid()` default never fires from
    # Python (we always pass `id` explicitly, but the default is here for parity).
    id = Column(UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4()))
    message_id = Column(String, nullable=False, index=True)  # turn id; multiple rows per turn
    analysis_id = Column(UUID(as_uuid=False), nullable=False, index=True)  # analysis session id
    user_id = Column(String, nullable=False)
    record_id = Column(String, nullable=True)
    chart_type = Column(String, nullable=False)
    title = Column(String, nullable=True)
    spec = Column(JSONB, nullable=False)  # full dataeyond.chart.v1 envelope (source of truth)
    created_at = Column(DateTime(timezone=True), nullable=False, server_default=func.now())


# -- Knowledge pipeline (N3, 2026-09-07) --------------------------------------
#
# Five tables backing the knowledge-ingestion pipeline. Schema of record:
# `docs/knowledge/KNOWLEDGE_PERSISTENCE_CONTRACT.md` section 2, executed exactly
# as `docs/knowledge/knowledge_tables.sql`.
#
# OWNERSHIP / HANDOFF: Python-owned for now, the same posture as `report_inputs`,
# `message_traceability` and `message_charts`. The tables were created by a
# one-time manual DDL run against dedorch (2026-09-07) to unblock the build;
# **Go still owns the migration** (CLAUDE.md 2.2) and Harry must fold the same
# SQL into the migration set, or a fresh environment comes up without them. That
# hand-over is the step the 2026-07-13 precedent skipped -- see contract 5 Q5.
#
# `scope_id` is the TENANT key (the company); `user_id` is the uploader. dedorch
# has no companies table yet, so `scope_id` currently holds a normalised
# `users.company` falling back to `user:<user_id>` -- a value that can be moved
# later precisely because it never enters the `entity_id` hash.
#
# Reads stay `getattr`-tolerant of pre- and post-migration shapes: when Harry's
# migration lands it may add columns, and nothing here may assume it has not.


class KnowledgeDocumentRow(Base):
    """One parsed-document artifact version -- the seam, persisted.

    Chunks live INSIDE `artifact` (jsonb) rather than in a child table: documents
    are small (9 pages = 31 chunks) and the artifact is versioned as a unit.
    Content-addressed on `(doc_id, content_hash, version)`, which is what makes
    re-ingesting an unchanged file free instead of a second parse.
    """

    __tablename__ = "knowledge_documents"

    id = Column(UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4()))
    # The pipeline key every `entity_id` derives from. Ingestion passes
    # `documents.id` so a file rename cannot orphan an expert's approvals.
    doc_id = Column(Text, nullable=False)
    document_id = Column(Text, ForeignKey("documents.id"), nullable=True)
    scope_id = Column(Text, nullable=False)
    user_id = Column(Text, nullable=False)
    source_path = Column(Text, nullable=False)
    content_hash = Column(Text, nullable=False)
    n_pages = Column(Integer, nullable=False)
    version = Column(Integer, nullable=False, default=1)
    schema_version = Column(Text, nullable=False)
    parser_name = Column(Text, nullable=False, default="mistral")
    parser_version = Column(Text)
    # Not cosmetic: the same parser can emit different text per backend, so a
    # shift in extraction output has to be attributable to one or the other.
    parser_backend = Column(Text)
    parser_config = Column(Text)
    normaliser_version = Column(Text)
    raw_output_dir = Column(Text)
    artifact = Column(JSONB, nullable=False)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    # i7 (2026-09-14): the source document has been removed. Null = live.
    # A withdrawn document stops counting towards the knowledge WITHOUT
    # anything being destroyed -- an approval is an act someone took, and the
    # record of what it referred to has to survive the source going away.
    withdrawn_at = Column(DateTime(timezone=True), nullable=True)


class KnowledgeExtractionRunRow(Base):
    """One extraction of one document.

    Exists because a document is re-extracted many times as prompts are tuned:
    without a run row, entries from two runs are indistinguishable and a quality
    change cannot be attributed. `model_deployment` is our stable name for the
    model; `model_version` / `system_fingerprint` are what actually served the
    call and CAN move underneath a stable deployment name -- a run without those
    recorded is a measurement of an unknown.
    """

    __tablename__ = "knowledge_extraction_runs"

    id = Column(UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4()))
    knowledge_document_id = Column(
        UUID(as_uuid=False),
        ForeignKey("knowledge_documents.id", ondelete="CASCADE"),
        nullable=False,
    )
    doc_id = Column(Text, nullable=False)
    scope_id = Column(Text, nullable=False)
    user_id = Column(Text, nullable=False)
    status = Column(Text, nullable=False, default="running")
    branches = Column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    model_deployment = Column(Text)
    model_version = Column(Text)
    system_fingerprint = Column(Text)
    n_calls = Column(Integer, nullable=False, default=0)
    prompt_tokens = Column(Integer, nullable=False, default=0)
    cached_tokens = Column(Integer, nullable=False, default=0)
    completion_tokens = Column(Integer, nullable=False, default=0)
    usage = Column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    # What the span check refused. Reported rather than swallowed: a validation
    # stage that rejects nothing looks identical to one that is not running.
    rejected = Column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    counts = Column(JSONB, nullable=False, server_default=text("'{}'::jsonb"))
    error_message = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    completed_at = Column(DateTime(timezone=True))


class KnowledgeEntryRow(Base):
    """One candidate entry -- all four kinds, discriminated by `kind`.

    The promoted columns (`label`, `mention_count`, `extraction_status`,
    `diff_status`, `definition_conflict`) also live inside `payload`. They are
    lifted out so the review queue can be ordered and filtered in SQL without
    opening jsonb -- **`payload` remains the source of truth**, and a writer must
    set both from the same object in the same insert.

    Written per run and never upserted across runs: two runs of one document
    produce two full sets of rows sharing `entity_id`s. History is the point.
    """

    __tablename__ = "knowledge_entries"

    id = Column(UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4()))
    # Nullable ONLY for kind='domain'. Every other entry is produced by a run
    # from a document; the scope-level domain declaration is composed across all
    # of them and belongs to neither. A DB CHECK enforces that distinction so
    # nullability here cannot become a loophole for the rest.
    run_id = Column(
        UUID(as_uuid=False),
        ForeignKey("knowledge_extraction_runs.id", ondelete="CASCADE"),
        nullable=True,
    )
    knowledge_document_id = Column(
        UUID(as_uuid=False),
        ForeignKey("knowledge_documents.id", ondelete="CASCADE"),
        nullable=True,
    )
    doc_id = Column(Text, nullable=False)
    scope_id = Column(Text, nullable=False)
    user_id = Column(Text, nullable=False)
    # One of: glossary, rule, formula, document, domain.
    kind = Column(Text, nullable=False)
    # Deterministic, content-derived, document-scoped (`ids.py`). Stable across
    # re-runs, which is what lets an expert's approvals survive a prompt retune.
    entity_id = Column(Text, nullable=False)
    label = Column(Text)
    mention_count = Column(Integer, nullable=False, default=0)
    extraction_status = Column(Text)
    # One of: new, duplicate, conflicting -- the cross-document relationship.
    diff_status = Column(Text)
    definition_conflict = Column(Boolean, nullable=False, default=False)
    payload = Column(JSONB, nullable=False)
    # m3/m4/m5 (2026-09-08 discussion, migrated 2026-09-14). Promoted out of
    # `payload` so a consumer does not have to open jsonb to use them -- the
    # objection raised in that discussion was that too much JSON makes the data
    # contract hard and easy to break. `payload` REMAINS the source of truth;
    # these are written from the same object in the same insert, exactly as
    # `label`, `mention_count` and `diff_status` already are.
    #
    # `doc_ids` is the list of documents behind the entry. `doc_id` above stays
    # the primary source -- every existing filter and index uses it -- and the
    # two agree for an ordinary entry. They differ only when a definition is
    # carried forward onto a later document that re-mentions the term.
    doc_ids = Column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    content = Column(Text)
    provenance = Column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    created_at = Column(DateTime(timezone=True), server_default=func.now())


class KnowledgeReviewRow(Base):
    """One expert decision. APPEND-ONLY -- never UPDATE, never DELETE.

    A changed mind is a NEW row; current state is the latest row per
    `entity_id`. Keyed on `entity_id` + `content_hash` rather than on `entry_id`
    because re-extracting mints new entry rows while keeping the same entity
    ids, so prior decisions still resolve.

    The FK is `ON DELETE RESTRICT`, not CASCADE: a cascade would let entry
    pruning silently delete the rulings attached to them, destroying the audit
    trail the keying exists to protect.
    """

    __tablename__ = "knowledge_reviews"

    id = Column(UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4()))
    entry_id = Column(
        UUID(as_uuid=False),
        ForeignKey("knowledge_entries.id", ondelete="RESTRICT"),
        nullable=False,
    )
    entity_id = Column(Text, nullable=False)
    doc_id = Column(Text, nullable=False)
    scope_id = Column(Text, nullable=False)
    # WHICH artifact version this decision was made against.
    content_hash = Column(Text, nullable=False)
    # One of: approved, edited, rejected.
    decision = Column(Text, nullable=False)
    edited_payload = Column(JSONB)
    note = Column(Text)
    reviewer_id = Column(Text, nullable=False)
    # r2/r3. `decided_by` says WHETHER A HUMAN LOOKED; `authorised_by` says
    # on whose authority it happened. They are separate because the admin who
    # clicks ingest and the expert who reviews can be different people, and in
    # "llm" mode there is no reviewer at all -- so without the clicker's id
    # reaching this row the decision would have no owner. Together they read:
    # approved by LLM with permission of <person>.
    decided_by = Column(Text, nullable=False, server_default=text("'human'"))
    authorised_by = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())


class KnowledgeJobRow(Base):
    """One ingestion batch -- the unit the user waits on.

    One click covers N documents, so the job outlives any single one. A run row
    is created only once extraction begins, which is why a PARSE failure has no
    other home. A `blocked` status is not a failure: it means the pre-flight
    estimate exceeded the spend ceiling and the job declined to continue.
    """

    __tablename__ = "knowledge_jobs"

    id = Column(UUID(as_uuid=False), primary_key=True, default=lambda: str(uuid4()))
    scope_id = Column(Text, nullable=False)
    user_id = Column(Text, nullable=False)
    status = Column(Text, nullable=False, default="queued")
    stage = Column(Text)
    # r1 (2026-09-08 discussion): which review mode this batch was ingested
    # under. "expert" leaves entries waiting for a human; "llm" takes them
    # straight to approved with no reviewer step at all. Nullable so jobs
    # written before the column existed stay legal.
    review_mode = Column(Text)
    document_ids = Column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    n_requested = Column(Integer, nullable=False, default=0)
    n_succeeded = Column(Integer, nullable=False, default=0)
    n_failed = Column(Integer, nullable=False, default=0)
    results = Column(JSONB, nullable=False, server_default=text("'[]'::jsonb"))
    estimated_cost = Column(JSONB)
    error_message = Column(Text)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    started_at = Column(DateTime(timezone=True))
    completed_at = Column(DateTime(timezone=True))