"""Fix musehub_commits array columns missed by migration 0024.

Migration 0024 intended to convert parent_ids, breaking_changes, and
reviewed_by in musehub_commits from JSON to PostgreSQL ARRAY, but the
column list incorrectly referenced muse_commits (a different table with
no such columns). This migration applies the correct conversions.

Revision ID: 0028
Revises: 0027
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa


revision = "0028"
down_revision = "0027"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()

    conn.execute(sa.text("""
        CREATE OR REPLACE FUNCTION _muse_json_to_text_array(v text)
        RETURNS text[]
        LANGUAGE sql IMMUTABLE AS $$
            SELECT CASE WHEN v IS NULL THEN NULL
                        ELSE ARRAY(SELECT jsonb_array_elements_text(v::jsonb))
                   END
        $$
    """))

    conn.execute(sa.text(
        "ALTER TABLE musehub_commits ALTER COLUMN breaking_changes TYPE text[] "
        "USING _muse_json_to_text_array(breaking_changes::text)"
    ))
    conn.execute(sa.text(
        "ALTER TABLE musehub_commits ALTER COLUMN reviewed_by TYPE text[] "
        "USING _muse_json_to_text_array(reviewed_by::text)"
    ))
    conn.execute(sa.text(
        "ALTER TABLE musehub_commits ALTER COLUMN parent_ids TYPE varchar(128)[] "
        "USING _muse_json_to_text_array(parent_ids::text)::varchar(128)[]"
    ))

    conn.execute(sa.text("DROP FUNCTION _muse_json_to_text_array(text)"))


def downgrade() -> None:
    conn = op.get_bind()

    def _has_column(table: str, column: str) -> bool:
        r = conn.execute(sa.text(
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema='public' AND table_name=:t AND column_name=:c"
        ), {"t": table, "c": column})
        return r.fetchone() is not None

    for col in ("breaking_changes", "reviewed_by", "parent_ids"):
        if _has_column("musehub_commits", col):
            conn.execute(sa.text(
                f"ALTER TABLE musehub_commits ALTER COLUMN {col} TYPE json "
                f"USING to_json({col})"
            ))