"""Promote agent_id, model_id, commit_branch to first-class columns on musehub_commits.

These were previously buried in the commit_meta JSON blob, making them
unqueryable without JSON extraction. Promoting them enables indexed lookups
and clean UI display.

Revision ID: 0019
Revises: 0018
Create Date: 2026-05-04
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

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


def upgrade() -> None:
    op.add_column(
        "musehub_commits",
        sa.Column("agent_id", sa.String(255), nullable=True, server_default=""),
    )
    op.add_column(
        "musehub_commits",
        sa.Column("model_id", sa.String(255), nullable=True, server_default=""),
    )
    op.add_column(
        "musehub_commits",
        sa.Column("commit_branch", sa.String(255), nullable=True),
    )
    # Backfill from commit_meta JSON for all existing rows
    op.execute(
        """
        UPDATE musehub_commits
        SET
            agent_id     = COALESCE(commit_meta->>'agent_id', ''),
            model_id     = COALESCE(commit_meta->>'model_id', ''),
            commit_branch = NULLIF(commit_meta->>'branch', '')
        WHERE agent_id IS NULL OR agent_id = ''
           OR model_id IS NULL OR model_id = ''
           OR commit_branch IS NULL
        """
    )


def downgrade() -> None:
    from sqlalchemy import text
    for col in ("commit_branch", "model_id", "agent_id"):
        op.execute(text(f"ALTER TABLE musehub_commits DROP COLUMN IF EXISTS {col}"))