"""Record WHICH report a duplicate duplicates.

``status='duplicate'`` has been a dead end: the console could say "this
is a duplicate" and nothing could say of what. Two production rows are
in exactly that state, and on one of them the researcher pushed back —
"please confirm when the duplicate report was actually received, or add
me to that thread" — a question the platform could not answer.

Two columns, because the honest answer is not always another Helix row:

* ``duplicate_of_id`` — self-FK when the original IS in the ledger.
* ``duplicate_note``  — free text when it is not: an internal issue, a
  commit, or (both real cases here) an email that predates the bounty
  domain existing. Forcing an FK would push operators to leave the field
  empty, which is where we started.

Revision ID: 544
Revises: 543
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for

revision = "544"
down_revision = "543"
branch_labels = None
depends_on = None

TABLE = "bounty_reports"


def _column_exists(bind, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = DATABASE() AND table_name = :t "
            "AND column_name = :c"
        ),
        {"t": TABLE, "c": column},
    ).scalar() is not None


def _index_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.statistics "
            "WHERE table_schema = DATABASE() AND table_name = :t "
            "AND index_name = :n LIMIT 1"
        ),
        {"t": TABLE, "n": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _column_exists(bind, "duplicate_of_id"):
        # Self-referential: the parent is this same table, so the FK
        # charset question answers itself.
        op.add_column(
            TABLE, sa.Column("duplicate_of_id", id_type_for("identity_persons")),
        )
        op.create_foreign_key(
            "fk_bounty_reports_duplicate_of", TABLE, TABLE,
            ["duplicate_of_id"], ["id"], ondelete="SET NULL",
        )
        # Answers "what else was filed against this one" without a scan.
        op.create_index(
            "ix_bounty_reports_duplicate_of", TABLE, ["duplicate_of_id"],
        )
    if not _column_exists(bind, "duplicate_note"):
        op.add_column(TABLE, sa.Column("duplicate_note", sa.String(500)))


def downgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, "duplicate_note"):
        op.drop_column(TABLE, "duplicate_note")
    if _column_exists(bind, "duplicate_of_id"):
        # FK → index → column. The index covers the FK's leading column,
        # so dropping it first fails with 1553 (learned the hard way on
        # migration 542's downgrade).
        try:
            op.drop_constraint(
                "fk_bounty_reports_duplicate_of", TABLE, type_="foreignkey",
            )
        except Exception:  # pragma: no cover - already absent
            pass
        if _index_exists(bind, "ix_bounty_reports_duplicate_of"):
            op.drop_index("ix_bounty_reports_duplicate_of", table_name=TABLE)
        op.drop_column(TABLE, "duplicate_of_id")