"""DB-driven Codegen provider/model catalog and workspace BYOK configuration.

Revision ID: 169
Revises: 168
"""
from uuid import uuid4

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects.postgresql import JSONB, UUID

revision = "169"
down_revision = "168"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "codegen_providers",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("slug", sa.String(64), nullable=False, unique=True),
        sa.Column("display_name", sa.String(120), nullable=False),
        sa.Column("adapter_key", sa.String(64), nullable=False),
        sa.Column("executable", sa.String(128), nullable=False),
        sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("available_p2hs", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("available_byok", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("install_strategy_key", sa.String(80), nullable=False),
        sa.Column("metadata_json", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )
    op.create_table(
        "codegen_models",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("provider_id", UUID(as_uuid=True), sa.ForeignKey("codegen_providers.id", ondelete="CASCADE"), nullable=False),
        sa.Column("model_key", sa.String(120), nullable=False),
        sa.Column("display_name", sa.String(160), nullable=False),
        sa.Column("cli_model_value", sa.String(160), nullable=False),
        sa.Column("enabled", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("available_p2hs", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("available_byok", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("is_default", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("is_recommended", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("context_window", sa.Integer(), nullable=True),
        sa.Column("capabilities_json", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint("provider_id", "model_key", name="uq_codegen_model_provider_key"),
    )
    op.create_table(
        "codegen_credentials",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("workspace_id", UUID(as_uuid=True), sa.ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False),
        sa.Column("provider_id", UUID(as_uuid=True), sa.ForeignKey("codegen_providers.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("label", sa.String(120), nullable=False),
        sa.Column("encrypted_secret", sa.Text(), nullable=False),
        sa.Column("secret_version", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("status", sa.String(24), nullable=False, server_default="active"),
        sa.Column("last_four", sa.String(4), nullable=True),
        sa.Column("validation_status", sa.String(24), nullable=False, server_default="unvalidated"),
        sa.Column("validated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_by", UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
    )
    for table, fk in (
        ("codegen_workspace_configs", "workspaces.id"),
        ("codegen_project_configs", "projects.id"),
    ):
        op.create_table(
            table,
            sa.Column("id", UUID(as_uuid=True), primary_key=True),
            sa.Column("workspace_id" if table.endswith("workspace_configs") else "project_id", UUID(as_uuid=True), sa.ForeignKey(fk, ondelete="CASCADE"), nullable=False, unique=True),
            sa.Column("provider_id", UUID(as_uuid=True), sa.ForeignKey("codegen_providers.id", ondelete="SET NULL"), nullable=True),
            sa.Column("model_id", UUID(as_uuid=True), sa.ForeignKey("codegen_models.id", ondelete="SET NULL"), nullable=True),
            sa.Column("credential_id", UUID(as_uuid=True), sa.ForeignKey("codegen_credentials.id", ondelete="SET NULL"), nullable=True),
            sa.Column("funding_source", sa.String(16), nullable=True if table.endswith("project_configs") else False, server_default="p2hs"),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        )
    op.create_table(
        "codegen_execution_snapshots",
        sa.Column("run_id", UUID(as_uuid=True), sa.ForeignKey("codegen_runs.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("provider_slug", sa.String(64), nullable=False),
        sa.Column("model_key", sa.String(120), nullable=False),
        sa.Column("funding_source", sa.String(16), nullable=False),
        sa.Column("credential_id", UUID(as_uuid=True), nullable=True),
        sa.Column("adapter_version", sa.String(64), nullable=True),
        sa.Column("cli_version", sa.String(128), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )
    op.create_table(
        "codegen_usage",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("run_id", UUID(as_uuid=True), sa.ForeignKey("codegen_runs.id", ondelete="CASCADE"), nullable=False, unique=True),
        sa.Column("funding_source", sa.String(16), nullable=False),
        sa.Column("usage_source", sa.String(24), nullable=False, server_default="unavailable"),
        sa.Column("input_tokens", sa.Integer(), nullable=True),
        sa.Column("output_tokens", sa.Integer(), nullable=True),
        sa.Column("cached_tokens", sa.Integer(), nullable=True),
        sa.Column("estimated_cost", sa.Integer(), nullable=True),
        sa.Column("wallet_debit", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("provider_request_id", sa.String(255), nullable=True),
        sa.Column("metadata_json", JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
    )

    providers = [
        ("cursor", "Cursor", "cursor_cli", "agent", "cursor_install"),
        ("openai", "OpenAI", "openai_api", "openai", "openai_api"),
        ("codex", "OpenAI Codex", "codex_cli", "codex", "codex_install"),
        ("claude", "Claude", "claude_cli", "claude", "claude_install"),
        ("gemini", "Gemini", "gemini_cli", "gemini", "gemini_install"),
        ("qwen", "Qwen", "qwen_cli", "qwen", "qwen_install"),
    ]
    provider_ids = {slug: uuid4() for slug, *_ in providers}
    implemented = {"cursor", "openai", "codex", "claude"}
    op.bulk_insert(
        sa.table("codegen_providers", *[sa.column(c) for c in ("id", "slug", "display_name", "adapter_key", "executable", "install_strategy_key", "enabled")]),
        [dict(id=provider_ids[slug], slug=slug, display_name=name, adapter_key=adapter, executable=exe, install_strategy_key=install, enabled=slug in implemented) for slug, name, adapter, exe, install in providers],
    )
    models = {
        "cursor": [("default", "Cursor default", "")],
        "openai": [("gpt-4o", "GPT-4o", "gpt-4o")],
        "codex": [("gpt-5.6", "GPT-5.6", "gpt-5.6")],
        "claude": [("claude-sonnet", "Claude Sonnet", "claude-sonnet-4-20250514")],
        "gemini": [("gemini-2.5-pro", "Gemini 2.5 Pro", "gemini-2.5-pro")],
        "qwen": [("qwen3-coder", "Qwen3 Coder", "qwen3-coder")],
    }
    model_rows = []
    for slug, rows in models.items():
        for key, name, cli_value in rows:
            model_rows.append(dict(id=uuid4(), provider_id=provider_ids[slug], model_key=key, display_name=name, cli_model_value=cli_value, enabled=slug in implemented, is_default=True, is_recommended=True))
    op.bulk_insert(sa.table("codegen_models", *[sa.column(c) for c in ("id", "provider_id", "model_key", "display_name", "cli_model_value", "enabled", "is_default", "is_recommended")]), model_rows)


def downgrade() -> None:
    for table in ("codegen_usage", "codegen_execution_snapshots", "codegen_project_configs", "codegen_workspace_configs", "codegen_credentials", "codegen_models", "codegen_providers"):
        op.drop_table(table)