"""add integrations (tenant-level external system credentials)

Revision ID: 025
Revises: 024
Create Date: 2026-04-17
"""
import sqlalchemy as sa
from alembic import op

from app.core.migration_helpers import id_type_for, table_kw_for


revision = "025"
down_revision = "024"


def upgrade() -> None:
    ID = id_type_for("identity_persons")
    KW = table_kw_for("identity_persons")

    op.create_table(
        "integrations",
        sa.Column("id", ID, primary_key=True),
        sa.Column("provider_type", sa.String(32), nullable=False),
        sa.Column("name", sa.String(128), nullable=False),
        sa.Column("description", sa.Text, nullable=True),
        sa.Column("config_encrypted", sa.Text, nullable=True),
        sa.Column("config_nonce", sa.String(64), nullable=True),
        sa.Column("non_secret_config", sa.JSON, nullable=True),
        sa.Column("is_active", sa.Boolean, nullable=False,
                  server_default=sa.text("1")),
        sa.Column("last_test_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_test_success", sa.Boolean, nullable=True),
        sa.Column("last_test_error", sa.Text, nullable=True),
        sa.Column("created_by", ID,
                  sa.ForeignKey("identity_persons.id"), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False,
                  server_default=sa.func.now()),
        **KW,
    )
    op.create_index(
        "ix_integrations_provider_active",
        "integrations", ["provider_type", "is_active"],
    )
    op.create_unique_constraint(
        "uq_integrations_name",
        "integrations", ["name"],
    )

    op.add_column(
        "software_connectors",
        sa.Column("integration_id", ID,
                  sa.ForeignKey("integrations.id"), nullable=True),
    )


def downgrade() -> None:
    op.drop_column("software_connectors", "integration_id")
    op.drop_constraint("uq_integrations_name", "integrations", type_="unique")
    op.drop_index("ix_integrations_provider_active", table_name="integrations")
    op.drop_table("integrations")