"""Greet the researcher by name in the four letters that did not.

Found by rendering the reply queue and reading what it produced. Seven of
the eleven shipped letters already opened with ``{researcher_name}``; the
other four opened "Dear submitter," / "Dear valued submitter," / "Hello," /
"Hi,". One of those four is ``under_evaluation`` — the acknowledgement that
goes to every unanswered report, so it is the highest-volume letter in the
system and the first thing most researchers ever receive from us. An
impersonal form letter is exactly what makes a bounty programme feel like a
black hole, and the name was already in the database.

The token degrades to the literal word "submitter" when no name is known
(``rendering.py``: ``name or "submitter"``), so the worst case is the
wording these letters already had. That is what makes this safe to change
rather than merely nicer.

**Diff-and-apply, not overwrite.** A row is updated only when its body is
still byte-identical to the old shipped default — i.e. nobody has edited
it. An operator who reworded their own acknowledgement keeps it; the
console's per-template 「恢复默认」 is how they opt in later. Overwriting
every row would have silently discarded operator work, and the operator
would have no way to know it happened.

Revision ID: 549
Revises: 548
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "549"
down_revision = "548"
branch_labels = None
depends_on = None

TABLE = "bounty_reply_templates"

#: (key, old opening line, new opening line). Matched as a PREFIX of the
#: body, so the rest of an unedited letter does not have to be restated
#: here — and an edited body simply fails to match and is left alone.
OPENINGS: tuple[tuple[str, str, str], ...] = (
    ("under_evaluation", "Dear submitter,", "Dear {researcher_name},"),
    ("thanks_generic", "Dear valued submitter,", "Dear {researcher_name},"),
    ("out_of_scope_tls", "Hello,", "Hello {researcher_name},"),
    ("duplicate_known", "Hi,", "Hi {researcher_name},"),
)


def _apply(
    conn: sa.engine.Connection,
    pairs: tuple[tuple[str, str, str], ...],
) -> None:
    for key, old_open, new_open in pairs:
        row = conn.execute(
            sa.text(
                f"SELECT id, body, variables FROM {TABLE} "  # noqa: S608
                "WHERE `key` = :k",
            ),
            {"k": key},
        ).first()
        if row is None:
            continue
        body = row[1] or ""
        if not body.startswith(old_open):
            # Edited by an operator (or already migrated). Leave it.
            continue
        conn.execute(
            sa.text(
                f"UPDATE {TABLE} SET body = :body "  # noqa: S608
                "WHERE id = :id",
            ),
            {"body": new_open + body[len(old_open):], "id": row[0]},
        )


def _sync_variables(conn: sa.engine.Connection, *, add: bool) -> None:
    """Keep the declared-token list truthful.

    ``variables`` is the console's insertable-token hint. It is NOT what
    the money guard reads — that calls ``declared_tokens(subject, body)``
    and parses the text, so it already sees the new opening without any
    help from here. Syncing anyway because a row whose body carries a
    token it does not declare is a quiet inconsistency, and the next
    person to trust this column should not have to re-derive that it lags
    the body.
    """
    import json

    for key, _old, _new in OPENINGS:
        row = conn.execute(
            sa.text(
                f"SELECT id, variables FROM {TABLE} "  # noqa: S608
                "WHERE `key` = :k",
            ),
            {"k": key},
        ).first()
        if row is None:
            continue
        raw = row[1]
        if isinstance(raw, str):
            try:
                current = json.loads(raw)
            except ValueError:
                continue
        else:
            current = raw
        if not isinstance(current, list):
            continue
        names = [str(v) for v in current]
        if add and "researcher_name" not in names:
            names.append("researcher_name")
        elif not add and "researcher_name" in names:
            names = [n for n in names if n != "researcher_name"]
        else:
            continue
        conn.execute(
            sa.text(
                f"UPDATE {TABLE} SET variables = :v "  # noqa: S608
                "WHERE id = :id",
            ),
            {"v": json.dumps(names), "id": row[0]},
        )


def upgrade() -> None:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    _apply(conn, OPENINGS)
    _sync_variables(conn, add=True)


def downgrade() -> None:
    conn = op.get_bind()
    if not sa.inspect(conn).has_table(TABLE):
        return
    # Reverse the same prefix swap, and only where the new opening is still
    # intact — symmetric with the upgrade, so a body edited after the
    # upgrade is not clobbered on the way back either.
    _apply(conn, tuple((k, new, old) for k, old, new in OPENINGS))
    _sync_variables(conn, add=False)