"""Close-task → Helix-native signal binding (book-close PR5).

Adds a nullable ``signal_key`` column to ``finance_close_task_templates`` and
``finance_close_tasks``: when set, the console shows that signal's live
green/amber/red state next to the task as decision support (it never
auto-completes the task). Backfills the curated default bindings on both
tables (templates by source_key, instances by template_source_key) so
existing periods light up without a re-instantiate. Idempotent.

Revision ID: 357
Revises: 356
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.domains.finance.close_signal_keys import DEFAULT_SIGNAL_BINDINGS

revision = "357"
down_revision = "356"
branch_labels = None
depends_on = None


def _has_column(bind, table: str, column: str) -> bool:
    row = 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},
    ).fetchone()
    return row is not None


def upgrade() -> None:
    bind = op.get_bind()
    for table in ("finance_close_task_templates", "finance_close_tasks"):
        if not _has_column(bind, table, "signal_key"):
            op.add_column(table, sa.Column("signal_key", sa.String(length=64), nullable=True))

    # Backfill curated bindings (idempotent — only fills NULLs).
    for source_key, signal_key in DEFAULT_SIGNAL_BINDINGS.items():
        bind.execute(
            sa.text(
                "UPDATE finance_close_task_templates SET signal_key = :sk "
                "WHERE source_key = :src AND signal_key IS NULL"
            ),
            {"sk": signal_key, "src": source_key},
        )
        bind.execute(
            sa.text(
                "UPDATE finance_close_tasks SET signal_key = :sk "
                "WHERE template_source_key = :src AND signal_key IS NULL"
            ),
            {"sk": signal_key, "src": source_key},
        )


def downgrade() -> None:
    bind = op.get_bind()
    for table in ("finance_close_tasks", "finance_close_task_templates"):
        if _has_column(bind, table, "signal_key"):
            op.drop_column(table, "signal_key")