"""integrations

Deux tables neuves, la migration unique de la tranche T8 (spec Imports §9) : `integrations`
(BYOK — clé chiffrée par `app.core.crypto`, jamais en clair en base) et `import_schedules`
(mode de synchro par (intégration, cible), avec l'échéance que le sweep du worker lit).

**Additive et réversible sans perte** : aucune table existante n'est touchée, aucune colonne
supprimée, aucune donnée réécrite. Le `downgrade` rend exactement l'état d'avant — il perd les
clés enregistrées entre-temps, ce qui est la définition même de « débrancher les intégrations »,
pas une perte silencieuse.

Revision ID: d5ae8fad262a
Revises: 542f48d3235f
Create Date: 2026-09-02 11:16:22.450202

"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = "d5ae8fad262a"
down_revision: str | Sequence[str] | None = "542f48d3235f"
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(
        "integrations",
        sa.Column("id", sa.UUID(), server_default=sa.text("uuidv7()"), nullable=False),
        sa.Column("org_id", sa.Text(), nullable=False),
        sa.Column("provider", sa.Text(), nullable=False),
        sa.Column("credentials", sa.Text(), nullable=False),
        sa.Column("credentials_hint", sa.Text(), nullable=False),
        sa.Column(
            "config",
            postgresql.JSONB(astext_type=sa.Text()),
            server_default=sa.text("'{}'::jsonb"),
            nullable=False,
        ),
        sa.Column("status", sa.Text(), server_default=sa.text("'unverified'"), nullable=False),
        sa.Column("status_error", sa.Text(), nullable=True),
        sa.Column("checked_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.CheckConstraint("provider IN ('mangools')", name="ck_integrations_provider"),
        sa.CheckConstraint(
            "status IN ('unverified', 'connected', 'error')", name="ck_integrations_status"
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("org_id", "provider", name="uq_integrations_org_provider"),
    )
    op.create_table(
        "import_schedules",
        sa.Column("id", sa.UUID(), server_default=sa.text("uuidv7()"), nullable=False),
        sa.Column("org_id", sa.Text(), nullable=False),
        sa.Column("integration_id", sa.UUID(), nullable=False),
        sa.Column("site_target_id", sa.UUID(), nullable=False),
        sa.Column("sync_mode", sa.Text(), server_default=sa.text("'manual'"), nullable=False),
        sa.Column("next_run_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("enabled", sa.Boolean(), server_default=sa.text("true"), nullable=False),
        sa.Column(
            "config",
            postgresql.JSONB(astext_type=sa.Text()),
            server_default=sa.text("'{}'::jsonb"),
            nullable=False,
        ),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.CheckConstraint(
            "(sync_mode = 'manual') = (next_run_at IS NULL)", name="ck_import_schedules_next_run_at"
        ),
        sa.CheckConstraint(
            "sync_mode IN ('manual', 'weekly', 'monthly')", name="ck_import_schedules_sync_mode"
        ),
        sa.ForeignKeyConstraint(
            ["integration_id"],
            ["integrations.id"],
            name="fk_import_schedules_integration_id",
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["site_target_id"],
            ["site_targets.id"],
            name="fk_import_schedules_site_target_id",
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "integration_id", "site_target_id", name="uq_import_schedules_integration_target"
        ),
    )
    op.create_index(
        op.f("ix_import_schedules_org_id"), "import_schedules", ["org_id"], unique=False
    )
    op.create_index(
        op.f("ix_import_schedules_site_target_id"),
        "import_schedules",
        ["site_target_id"],
        unique=False,
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema."""
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f("ix_import_schedules_site_target_id"), table_name="import_schedules")
    op.drop_index(op.f("ix_import_schedules_org_id"), table_name="import_schedules")
    op.drop_table("import_schedules")
    op.drop_table("integrations")
    # ### end Alembic commands ###