"""Let two addresses be recorded as the same researcher.

Confirmed in production: ``suhaspandya55@gmail.com`` and
``suhaspandya124@gmail.com`` are one person, and Helix counts them as two.
Every per-researcher figure is therefore wrong for anyone who has ever
used a second address — "how many has he sent us", "how much have we paid
him", and the repeat-submitter signal the reply templates lean on.

The model is a merge POINTER, not a rewrite of history:

* ``merged_into_id`` on the alias row points at the canonical researcher.
* Reports keep ``researcher_id`` pointing at the address that actually
  sent them. That matters twice: a reply has to go back to the address the
  mail came from, and "which address did this arrive on" is a real
  question during a dispute.
* Aggregates resolve through the pointer, so the person's totals are
  right without any row being edited.

Rewriting ``researcher_id`` on the reports would have been less code and
would have destroyed both of those facts.

Chains are refused at the service layer (an alias may not itself be an
alias target), so resolution is always one hop — no recursive CTE, and no
possibility of a cycle making a query hang.

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

import sqlalchemy as sa

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

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

TABLE = "bounty_researchers"
COLUMN = "merged_into_id"
FK = "fk_bounty_researchers_merged_into"
IX = "ix_bounty_researchers_merged_into"


def _has_column(conn: sa.engine.Connection) -> 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 _has_column(conn):
        return

    op.add_column(
        TABLE,
        sa.Column(
            COLUMN,
            id_type_for(TABLE),
            nullable=True,
            comment="Canonical researcher this address is an alias of. "
                    "Reports keep pointing at the address that sent them; "
                    "aggregates resolve through this.",
        ),
    )
    op.create_index(IX, TABLE, [COLUMN])
    # SET NULL rather than CASCADE: deleting a canonical researcher must
    # not delete the alias rows, because the reports still reference them.
    op.create_foreign_key(
        FK, TABLE, TABLE, [COLUMN], ["id"], ondelete="SET NULL",
    )


def downgrade() -> None:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    if not _has_column(conn):
        return
    # FK → index → column. MySQL refuses to drop an index a foreign key
    # still needs (errno 1553), so the order is not cosmetic.
    op.drop_constraint(FK, TABLE, type_="foreignkey")
    op.drop_index(IX, table_name=TABLE)
    op.drop_column(TABLE, COLUMN)