"""Keep the AI's read of a report next to the report.

Once the researcher's own write-up is imported, the console has the text —
but the person triaging still has to read several hundred words of somebody
else's English to answer the only question that matters first: **is this
actually a problem?** 149 reports of that is the reason 118 never got an
answer.

So one column holds a structured assessment: a verdict with its reasoning,
how exploitable it looks, what it would actually cost us, the two-to-four
things to check to confirm it, and what to ask the researcher if it is not
checkable as written.

Stored as JSON rather than as columns because it is one artefact with one
provenance — the model and the timestamp belong to the whole reading, and a
re-run replaces it wholesale. Splitting it into a dozen columns would
invite half of them to be updated by hand until nobody knows which parts
the model said.

It changes nothing on its own. The verdict never sets the status, the
suggested level never becomes the grade, and the reply composer never reads
it. This is the same discipline as the rest of the domain: the AI reads,
a person decides. An LLM's opinion written silently into a ledger that
authorises payments is not a feature.

Revision ID: 552
Revises: 551
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "552"
down_revision = "551"
branch_labels = None
depends_on = None

TABLE = "bounty_reports"
COLUMNS = ("ai_assessment", "ai_assessed_at")


def _has_column(conn: sa.engine.Connection, column: str) -> bool:
    return bool(
        conn.execute(
            sa.text(
                "SELECT COUNT(*) FROM information_schema.columns "
                "WHERE table_schema = DATABASE() "
                "AND table_name = :t AND column_name = :c",
            ),
            {"t": TABLE, "c": column},
        ).scalar()
    )


def upgrade() -> None:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    if not _has_column(conn, "ai_assessment"):
        op.add_column(
            TABLE,
            sa.Column(
                "ai_assessment",
                sa.JSON(),
                nullable=True,
                comment=(
                    "The model's structured reading: verdict, reasoning, "
                    "exploitability, impact, what to check, what to ask. "
                    "Advisory only — nothing downstream reads it."
                ),
            ),
        )
    if not _has_column(conn, "ai_assessed_at"):
        op.add_column(
            TABLE,
            sa.Column(
                "ai_assessed_at",
                sa.DateTime(timezone=True),
                nullable=True,
                comment=(
                    "When the assessment above was produced. A reading of a "
                    "report that has since been edited is stale, and the "
                    "console has to be able to say so."
                ),
            ),
        )


def downgrade() -> None:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    # Neither column is indexed, so a plain drop is safe on TiDB too.
    for column in reversed(COLUMNS):
        if _has_column(conn, column):
            op.drop_column(TABLE, column)