"""Fix musehub_proposals from/to_snapshot_id naming bug -- add from/to_commit_id

musehub#144 review of proposal #9 traced a real naming bug back to #0049
("proposal_snapshot_anchors"): that migration's own docstring says it
"captures the HEAD commit ID of each branch" but named the columns
from_snapshot_id/to_snapshot_id. Every row ever written to these columns
holds a commit ID, never a Muse Snapshot (manifest) ID -- confirmed via
musehub_proposals.create_proposal, which has always populated them from
branch.head_commit_id.

Fix: add from_commit_id/to_commit_id (String(128), nullable), backfill them
from the existing from_snapshot_id/to_snapshot_id values (which already hold
commit IDs for every existing row), then correct from_snapshot_id/
to_snapshot_id in place to hold the real snapshot_id looked up from each
referenced commit. Existing rows whose commit_id no longer resolves in
musehub_commits (e.g. GC'd/unreachable history) are left with a null
snapshot_id rather than a guessed value.

Revision ID: 0075
Revises: 0074
"""
from __future__ import annotations

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision: str = '0075'
down_revision: Union[str, None] = '0074'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.add_column('musehub_proposals', sa.Column('from_commit_id', sa.String(length=128), nullable=True))
    op.add_column('musehub_proposals', sa.Column('to_commit_id', sa.String(length=128), nullable=True))

    # Backfill the new, correctly-named columns from the existing (mislabeled)
    # columns -- every existing row's from_snapshot_id/to_snapshot_id is
    # already a commit ID.
    op.execute(
        "UPDATE musehub_proposals "
        "SET from_commit_id = from_snapshot_id, to_commit_id = to_snapshot_id"
    )

    # Now correct from_snapshot_id/to_snapshot_id in place to hold the real
    # snapshot_id of the commit they reference. Rows whose commit no longer
    # resolves (unreachable/GC'd history) fall back to NULL rather than a
    # stale commit ID masquerading as a snapshot ID.
    op.execute(
        "UPDATE musehub_proposals p "
        "SET from_snapshot_id = c.snapshot_id "
        "FROM musehub_commits c "
        "WHERE c.commit_id = p.from_commit_id"
    )
    op.execute(
        "UPDATE musehub_proposals p "
        "SET from_snapshot_id = NULL "
        "WHERE p.from_commit_id IS NOT NULL "
        "AND NOT EXISTS (SELECT 1 FROM musehub_commits c WHERE c.commit_id = p.from_commit_id)"
    )
    op.execute(
        "UPDATE musehub_proposals p "
        "SET to_snapshot_id = c.snapshot_id "
        "FROM musehub_commits c "
        "WHERE c.commit_id = p.to_commit_id"
    )
    op.execute(
        "UPDATE musehub_proposals p "
        "SET to_snapshot_id = NULL "
        "WHERE p.to_commit_id IS NOT NULL "
        "AND NOT EXISTS (SELECT 1 FROM musehub_commits c WHERE c.commit_id = p.to_commit_id)"
    )


def downgrade() -> None:
    # Restore the pre-fix semantics: from/to_snapshot_id hold the commit ID.
    op.execute(
        "UPDATE musehub_proposals "
        "SET from_snapshot_id = from_commit_id, to_snapshot_id = to_commit_id"
    )
    op.drop_column('musehub_proposals', 'to_commit_id')
    op.drop_column('musehub_proposals', 'from_commit_id')