"""migration_import — initial schema.

Five tables on the dedicated ``migration_import`` Alembic branch:

- ``migration_import_jobs``            — one row per uploaded DPMF
- ``migration_import_entity_mappings`` — idempotency keystone
- ``migration_import_file_stagings``   — ``_files`` manifest staging
- ``migration_import_warnings``        — DPMF warnings + ours
- ``migration_import_raw_entities``    — forward-compat catch-all

``depends_on=("med_0002",)`` because ``file_stagings.resolved_document_id``
points at ``media.documents`` and that table must exist before us.

Revision ID: mig_0001
Revises: 0001
Create Date: 2026-05-17
"""

from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision: str = "mig_0001"
down_revision: str | None = "0001"
branch_labels: str | Sequence[str] | None = ("migration_import",)
depends_on: str | Sequence[str] | None = ("med_0002",)


def upgrade() -> None:
    op.create_table(
        "migration_import_jobs",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("clinic_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("created_by", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="uploaded"),
        sa.Column("error", sa.Text(), nullable=True),
        sa.Column("original_filename", sa.String(length=255), nullable=False),
        sa.Column("file_path", sa.String(length=500), nullable=False),
        sa.Column("file_size", sa.BigInteger(), nullable=False, server_default="0"),
        sa.Column("source_system", sa.String(length=50), nullable=True),
        sa.Column("source_adapter_version", sa.String(length=30), nullable=True),
        sa.Column("exporter_tool", sa.String(length=50), nullable=True),
        sa.Column("exporter_version", sa.String(length=30), nullable=True),
        sa.Column("format_version", sa.String(length=20), nullable=True),
        sa.Column("tenant_label", sa.String(length=255), nullable=True),
        sa.Column("integrity_hash_declared", sa.String(length=128), nullable=True),
        sa.Column("integrity_hash_computed", sa.String(length=128), nullable=True),
        sa.Column("total_entities", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("processed_entities", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("last_checkpoint", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column(
            "import_fiscal_compliance",
            sa.Boolean(),
            nullable=False,
            server_default="false",
        ),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["clinic_id"], ["clinics.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["created_by"], ["users.id"]),
        sa.PrimaryKeyConstraint("id"),
        sa.CheckConstraint(
            "status IN ('uploaded','validating','validated','previewing',"
            "'executing','completed','failed')",
            name="ck_migration_import_job_status",
        ),
    )
    op.create_index(
        op.f("ix_migration_import_jobs_clinic_id"),
        "migration_import_jobs",
        ["clinic_id"],
    )

    op.create_table(
        "migration_import_entity_mappings",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("clinic_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("job_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("source_system", sa.String(length=50), nullable=False),
        sa.Column("entity_type", sa.String(length=50), nullable=False),
        sa.Column("source_canonical_uuid", sa.String(length=36), nullable=False),
        sa.Column("dentalpin_table", sa.String(length=60), nullable=False),
        sa.Column("dentalpin_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["clinic_id"], ["clinics.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["job_id"], ["migration_import_jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "clinic_id",
            "source_system",
            "entity_type",
            "source_canonical_uuid",
            name="uq_migration_entity_mapping_lookup",
        ),
    )
    op.create_index(
        op.f("ix_migration_import_entity_mappings_clinic_id"),
        "migration_import_entity_mappings",
        ["clinic_id"],
    )
    op.create_index(
        op.f("ix_migration_import_entity_mappings_job_id"),
        "migration_import_entity_mappings",
        ["job_id"],
    )
    op.create_index(
        "ix_migration_entity_mapping_clinic_entity",
        "migration_import_entity_mappings",
        ["clinic_id", "entity_type"],
    )

    op.create_table(
        "migration_import_file_stagings",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("clinic_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("job_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("canonical_uuid", sa.String(length=36), nullable=False),
        sa.Column("parent_entity_type", sa.String(length=50), nullable=False),
        sa.Column("parent_canonical_uuid", sa.String(length=36), nullable=False),
        sa.Column("relative_path", sa.String(length=1024), nullable=False),
        sa.Column("declared_size_bytes", sa.BigInteger(), nullable=True),
        sa.Column("sha256", sa.String(length=64), nullable=True),
        sa.Column("mime_hint", sa.String(length=100), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="pending"),
        sa.Column("received_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("resolved_document_id", postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["clinic_id"], ["clinics.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["job_id"], ["migration_import_jobs.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["resolved_document_id"], ["documents.id"], ondelete="SET NULL"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("job_id", "canonical_uuid", name="uq_migration_file_staging_canonical"),
        sa.CheckConstraint(
            "status IN ('pending','received','missing')",
            name="ck_migration_file_staging_status",
        ),
    )
    op.create_index(
        op.f("ix_migration_import_file_stagings_clinic_id"),
        "migration_import_file_stagings",
        ["clinic_id"],
    )
    op.create_index(
        op.f("ix_migration_import_file_stagings_job_id"),
        "migration_import_file_stagings",
        ["job_id"],
    )
    op.create_index(
        "ix_migration_file_staging_lookup",
        "migration_import_file_stagings",
        ["job_id", "sha256"],
    )

    op.create_table(
        "migration_import_warnings",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("job_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("entity_type", sa.String(length=50), nullable=True),
        sa.Column("source_id", sa.String(length=100), nullable=True),
        sa.Column("severity", sa.String(length=10), nullable=False, server_default="info"),
        sa.Column("code", sa.String(length=80), nullable=False),
        sa.Column("message", sa.Text(), nullable=False),
        sa.Column("raw_data", postgresql.JSONB(astext_type=sa.Text()), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["job_id"], ["migration_import_jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.CheckConstraint(
            "severity IN ('info','warn','error')",
            name="ck_migration_import_warning_severity",
        ),
    )
    op.create_index(
        op.f("ix_migration_import_warnings_job_id"),
        "migration_import_warnings",
        ["job_id"],
    )

    op.create_table(
        "migration_import_raw_entities",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("clinic_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("job_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("entity_type", sa.String(length=50), nullable=False),
        sa.Column("canonical_uuid", sa.String(length=36), nullable=False),
        sa.Column("source_system", sa.String(length=50), nullable=False),
        sa.Column("source_id", sa.String(length=100), nullable=False),
        sa.Column("payload", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("raw_source_data", postgresql.JSONB(astext_type=sa.Text()), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["clinic_id"], ["clinics.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["job_id"], ["migration_import_jobs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "job_id", "entity_type", "canonical_uuid", name="uq_migration_raw_entity"
        ),
    )
    op.create_index(
        op.f("ix_migration_import_raw_entities_clinic_id"),
        "migration_import_raw_entities",
        ["clinic_id"],
    )
    op.create_index(
        op.f("ix_migration_import_raw_entities_job_id"),
        "migration_import_raw_entities",
        ["job_id"],
    )
    op.create_index(
        "ix_migration_raw_entity_clinic_type",
        "migration_import_raw_entities",
        ["clinic_id", "entity_type"],
    )


def downgrade() -> None:
    op.drop_index("ix_migration_raw_entity_clinic_type", table_name="migration_import_raw_entities")
    op.drop_index(
        op.f("ix_migration_import_raw_entities_job_id"),
        table_name="migration_import_raw_entities",
    )
    op.drop_index(
        op.f("ix_migration_import_raw_entities_clinic_id"),
        table_name="migration_import_raw_entities",
    )
    op.drop_table("migration_import_raw_entities")

    op.drop_index(
        op.f("ix_migration_import_warnings_job_id"), table_name="migration_import_warnings"
    )
    op.drop_table("migration_import_warnings")

    op.drop_index("ix_migration_file_staging_lookup", table_name="migration_import_file_stagings")
    op.drop_index(
        op.f("ix_migration_import_file_stagings_job_id"),
        table_name="migration_import_file_stagings",
    )
    op.drop_index(
        op.f("ix_migration_import_file_stagings_clinic_id"),
        table_name="migration_import_file_stagings",
    )
    op.drop_table("migration_import_file_stagings")

    op.drop_index(
        "ix_migration_entity_mapping_clinic_entity",
        table_name="migration_import_entity_mappings",
    )
    op.drop_index(
        op.f("ix_migration_import_entity_mappings_job_id"),
        table_name="migration_import_entity_mappings",
    )
    op.drop_index(
        op.f("ix_migration_import_entity_mappings_clinic_id"),
        table_name="migration_import_entity_mappings",
    )
    op.drop_table("migration_import_entity_mappings")

    op.drop_index(op.f("ix_migration_import_jobs_clinic_id"), table_name="migration_import_jobs")
    op.drop_table("migration_import_jobs")
