"""Let a report say "no internal system applies" and stop being a gap.

44 of 149 live reports have no linked registry system, and 44 of those 44
have no hostname either. They are not un-triaged — most are findings in
the **open-source** products (TiDB status port, BR credential logging,
Grafana metrics exposure) or in GitHub Actions workflows. There is no
internal system to link them to, and there never will be.

Without a way to record that, they sit in the "unlinked" gap forever:
the data-completeness read counts them, an operator looks, concludes
nothing can be done, and the number never moves. A metric that cannot go
to zero teaches people to ignore it.

``system_not_applicable`` is that record. It is deliberately NOT the same
as ``system_id IS NULL``:

* ``system_id IS NULL`` + flag false → nobody has looked yet. Work.
* ``system_id IS NULL`` + flag true  → somebody looked and there is no
  internal system. Done, and excluded from the gap.

A boolean rather than a free-text reason on purpose: the only decision
the gap read needs is "does this still need linking", and a nullable
reason column would immediately have been half-empty. The *why* belongs
in the report's own timeline, where the operator's note already goes.

Revision ID: 546
Revises: 545
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "546"
down_revision = "545"
branch_labels = None
depends_on = None

TABLE = "bounty_reports"
COLUMN = "system_not_applicable"


def _has_column(conn: sa.engine.Connection, table: str, column: str) -> bool:
    # information_schema without a schema qualifier matches same-named
    # columns in every database on the server; qualify it.
    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 _has_column(conn, TABLE, COLUMN):
        return
    op.add_column(
        TABLE,
        sa.Column(
            COLUMN,
            sa.Boolean(),
            nullable=False,
            server_default=sa.text("0"),
            comment="Somebody looked and no internal system applies "
                    "(open-source finding, CI workflow). Distinct from "
                    "system_id IS NULL, which means nobody has looked.",
        ),
    )
    # Partial-index equivalents do not exist here; the gap read filters on
    # (system_id IS NULL AND system_not_applicable = 0) alongside status,
    # and the existing status index carries that query.


def downgrade() -> None:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    if not _has_column(conn, TABLE, COLUMN):
        return
    op.drop_column(TABLE, COLUMN)