"""Promote all commit_meta fields to first-class columns; drop commit_meta.

Revision ID: 0020
Revises: 0019
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "0020"
down_revision = "0019"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column("musehub_commits", sa.Column("signature", sa.Text(), nullable=True, server_default=""))
    op.add_column("musehub_commits", sa.Column("signer_public_key", sa.Text(), nullable=True, server_default=""))
    op.add_column("musehub_commits", sa.Column("signer_key_id", sa.String(255), nullable=True, server_default=""))
    op.add_column("musehub_commits", sa.Column("toolchain_id", sa.String(255), nullable=True, server_default=""))
    op.add_column("musehub_commits", sa.Column("sem_ver_bump", sa.String(10), nullable=True, server_default="none"))
    op.add_column("musehub_commits", sa.Column("breaking_changes", sa.JSON(), nullable=True))
    op.add_column("musehub_commits", sa.Column("reviewed_by", sa.JSON(), nullable=True))
    op.add_column("musehub_commits", sa.Column("test_runs", sa.Integer(), nullable=True, server_default="0"))
    op.add_column("musehub_commits", sa.Column("prompt_hash", sa.String(255), nullable=True, server_default=""))

    # Backfill from commit_meta JSON
    op.execute("""
        UPDATE musehub_commits SET
            signature        = COALESCE(commit_meta->>'signature', ''),
            signer_public_key = COALESCE(commit_meta->>'signer_public_key', ''),
            signer_key_id    = COALESCE(commit_meta->>'signer_key_id', ''),
            toolchain_id     = COALESCE(commit_meta->>'toolchain_id', ''),
            sem_ver_bump     = COALESCE(NULLIF(commit_meta->>'sem_ver_bump', ''), 'none'),
            breaking_changes = COALESCE((commit_meta->'breaking_changes')::json, '[]'::json),
            reviewed_by      = COALESCE((commit_meta->'reviewed_by')::json, '[]'::json),
            test_runs        = COALESCE((commit_meta->>'test_runs')::int, 0),
            prompt_hash      = COALESCE(commit_meta->>'prompt_hash', '')
        WHERE commit_meta IS NOT NULL
    """)

    op.drop_column("musehub_commits", "commit_meta")


def downgrade() -> None:
    from sqlalchemy import text
    op.add_column("musehub_commits", sa.Column("commit_meta", sa.JSON(), nullable=False, server_default="{}"))
    for col in ("prompt_hash", "test_runs", "reviewed_by", "breaking_changes",
                "sem_ver_bump", "toolchain_id", "signer_key_id", "signer_public_key", "signature"):
        op.execute(text(f"ALTER TABLE musehub_commits DROP COLUMN IF EXISTS {col}"))