"""add provider credential table

Revision ID: 2c57c41eca74
Revises: 3c6d6152046e
Create Date: 2026-03-27 18:04:34.178794

"""

from collections.abc import Sequence

import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "2c57c41eca74"
down_revision: str | Sequence[str] | None = "3c6d6152046e"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Upgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        "provider_credential",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("provider", sa.String(length=32), nullable=False),
        sa.Column("name", sa.String(length=128), nullable=False),
        sa.Column("api_key", sa.Text(), nullable=True),
        sa.Column("credentials_path", sa.Text(), nullable=True),
        sa.Column("base_url", sa.Text(), nullable=True),
        sa.Column("is_active", sa.Boolean(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    with op.batch_alter_table("provider_credential", schema=None) as batch_op:
        batch_op.create_index("ix_provider_credential_provider", ["provider"], unique=False)
        batch_op.create_index(
            "uq_provider_credential_one_active",
            ["provider"],
            unique=True,
            sqlite_where=sa.text("is_active = 1"),
            postgresql_where=sa.text("is_active"),
        )

    # ### end Alembic commands ###

    # 数据迁移：将 provider_config 中的凭证行迁入 provider_credential
    conn = op.get_bind()

    rows = conn.execute(
        sa.text(
            "SELECT provider, key, value FROM provider_config WHERE key IN ('api_key', 'credentials_path', 'base_url')"
        )
    ).fetchall()

    from collections import defaultdict

    provider_data: dict[str, dict[str, str]] = defaultdict(dict)
    for provider, key, value in rows:
        provider_data[provider][key] = value

    now = sa.func.now()
    cred_table = sa.table(
        "provider_credential",
        sa.column("provider", sa.String),
        sa.column("name", sa.String),
        sa.column("api_key", sa.Text),
        sa.column("credentials_path", sa.Text),
        sa.column("base_url", sa.Text),
        sa.column("is_active", sa.Boolean),
        sa.column("created_at", sa.DateTime),
        sa.column("updated_at", sa.DateTime),
    )

    for provider, data in provider_data.items():
        if not data.get("api_key") and not data.get("credentials_path"):
            continue
        conn.execute(
            cred_table.insert().values(
                provider=provider,
                name="默认密钥",
                api_key=data.get("api_key"),
                credentials_path=data.get("credentials_path"),
                base_url=data.get("base_url"),
                is_active=True,
                created_at=now,
                updated_at=now,
            )
        )

    conn.execute(sa.text("DELETE FROM provider_config WHERE key IN ('api_key', 'credentials_path', 'base_url')"))


def downgrade() -> None:
    """Downgrade schema."""
    # 反向迁移：将活跃凭证写回 provider_config
    conn = op.get_bind()
    rows = conn.execute(
        sa.text("SELECT provider, api_key, credentials_path, base_url FROM provider_credential WHERE is_active")
    ).fetchall()

    config_table = sa.table(
        "provider_config",
        sa.column("provider", sa.String),
        sa.column("key", sa.String),
        sa.column("value", sa.Text),
        sa.column("is_secret", sa.Boolean),
        sa.column("updated_at", sa.DateTime),
    )
    now = sa.func.now()
    for provider, api_key, cred_path, base_url in rows:
        if api_key:
            conn.execute(
                config_table.insert().values(
                    provider=provider,
                    key="api_key",
                    value=api_key,
                    is_secret=True,
                    updated_at=now,
                )
            )
        if cred_path:
            conn.execute(
                config_table.insert().values(
                    provider=provider,
                    key="credentials_path",
                    value=cred_path,
                    is_secret=False,
                    updated_at=now,
                )
            )
        if base_url:
            conn.execute(
                config_table.insert().values(
                    provider=provider,
                    key="base_url",
                    value=base_url,
                    is_secret=False,
                    updated_at=now,
                )
            )

    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table("provider_credential", schema=None) as batch_op:
        batch_op.drop_index("uq_provider_credential_one_active")
        batch_op.drop_index("ix_provider_credential_provider")

    op.drop_table("provider_credential")
    # ### end Alembic commands ###
