"""function_library — expand metadata + add versions table + project_functions snapshot cols.

Adds the production-grade metadata columns (status, visibility, minimum_plan, schemas,
codegen hints, observability, billing, etc.), a function_library_versions table for
version history, and snapshot + editable columns to project_functions.

Revision ID: 050
Revises: 049
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB, UUID as PGUUID

revision = "050"
down_revision = "049"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── function_library: new columns ────────────────────────────────────────

    # Make code_bundle_ref nullable (seed-based entries use code_template instead)
    op.alter_column("function_library", "code_bundle_ref", nullable=True)

    op.add_column("function_library", sa.Column("category", sa.Text(), nullable=True))
    op.add_column("function_library", sa.Column(
        "status", sa.Text(), nullable=False, server_default="published"
    ))
    op.add_column("function_library", sa.Column(
        "visibility", sa.Text(), nullable=False, server_default="public"
    ))
    op.add_column("function_library", sa.Column(
        "minimum_plan", sa.Text(), nullable=False, server_default="free"
    ))
    op.add_column("function_library", sa.Column(
        "semver_channel", sa.Text(), nullable=False, server_default="stable"
    ))
    op.add_column("function_library", sa.Column(
        "execution_mode", sa.Text(), nullable=False, server_default="sync"
    ))
    op.add_column("function_library", sa.Column(
        "timeout_ms", sa.Integer(), nullable=False, server_default="10000"
    ))
    op.add_column("function_library", sa.Column(
        "max_retries", sa.Integer(), nullable=False, server_default="3"
    ))
    op.add_column("function_library", sa.Column(
        "idempotency_required", sa.Boolean(), nullable=False, server_default="false"
    ))
    op.add_column("function_library", sa.Column("supported_trigger_types", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("required_capabilities", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("required_bindings", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("input_schema", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("output_schema", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("permission_scope", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("external_access", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("trigger_config_schema", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("codegen_hints", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("observability_config", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("billing_config", JSONB(), nullable=True))
    op.add_column("function_library", sa.Column("deprecated_at", sa.DateTime(timezone=True), nullable=True))
    op.add_column("function_library", sa.Column("replacement_slug", sa.Text(), nullable=True))
    op.add_column("function_library", sa.Column(
        "install_count", sa.Integer(), nullable=False, server_default="0"
    ))
    op.add_column("function_library", sa.Column("code_template", sa.Text(), nullable=True))

    op.create_index("ix_function_library_status", "function_library", ["status"])
    op.create_index("ix_function_library_category", "function_library", ["category"])
    op.create_index("ix_function_library_minimum_plan", "function_library", ["minimum_plan"])

    # Migrate is_active → status (published/disabled) for existing rows
    op.execute(
        "UPDATE function_library SET status = CASE WHEN is_active THEN 'published' ELSE 'disabled' END"
    )

    # ── function_library_versions ─────────────────────────────────────────────

    op.create_table(
        "function_library_versions",
        sa.Column("id", PGUUID(as_uuid=True), primary_key=True),
        sa.Column(
            "library_function_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("function_library.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("version", sa.Text(), nullable=False),
        sa.Column("semver_channel", sa.Text(), nullable=False, server_default="stable"),
        sa.Column("code_template", sa.Text(), nullable=True),
        sa.Column("code_bundle_ref", sa.Text(), nullable=True),
        sa.Column("changelog", sa.Text(), nullable=True),
        sa.Column("published_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("library_function_id", "version", name="uq_fn_library_versions"),
    )
    op.create_index(
        "ix_fn_library_versions_library_function_id",
        "function_library_versions",
        ["library_function_id"],
    )

    # ── project_functions: snapshot + editability ─────────────────────────────

    op.add_column("project_functions", sa.Column(
        "editable", sa.Boolean(), nullable=False, server_default="true"
    ))
    op.add_column("project_functions", sa.Column("code_snapshot", sa.Text(), nullable=True))
    op.add_column("project_functions", sa.Column("config_snapshot", JSONB(), nullable=True))

    # ── project_function_versions: source library version FK ─────────────────

    op.add_column(
        "project_function_versions",
        sa.Column(
            "source_library_version_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("function_library_versions.id", ondelete="SET NULL"),
            nullable=True,
        ),
    )


def downgrade() -> None:
    op.drop_column("project_function_versions", "source_library_version_id")
    op.drop_column("project_functions", "config_snapshot")
    op.drop_column("project_functions", "code_snapshot")
    op.drop_column("project_functions", "editable")

    op.drop_table("function_library_versions")

    for col in [
        "code_template", "install_count", "replacement_slug", "deprecated_at",
        "billing_config", "observability_config", "codegen_hints", "trigger_config_schema",
        "external_access", "permission_scope", "output_schema", "input_schema",
        "required_bindings", "required_capabilities", "supported_trigger_types",
        "idempotency_required", "max_retries", "timeout_ms", "execution_mode",
        "semver_channel", "minimum_plan", "visibility", "status", "category",
    ]:
        op.drop_column("function_library", col)

    op.alter_column("function_library", "code_bundle_ref", nullable=False)