"""Catálogos SAT (schema sat), conceptos de facturación, datos fiscales del emisor
y amarre de facturas y partidas a los catálogos.

Renumerada al integrar main: nació como e6f7a8b9c0d1, el mismo identificador que la
migración de catálogos del CRM, porque ambas ramas salieron de d5e6f7a8b9c0 sin verse. Se
renumera ésta y no la del CRM porque aquélla ya está en main y hay otra migración que la
referencia por id.

Revision ID: g1h2i3j4k5l6
Revises: c5d6e7f8a9b0
Create Date: 2026-08-07 00:00:00.000000

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

from api.v1.modules.fin.catalogs.seed_data import sync_catalogs

revision: str = "g1h2i3j4k5l6"
down_revision: Union[str, None] = "c5d6e7f8a9b0"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

# Índices únicos parciales: la baja lógica (deleted_at) libera la clave.
_ALIVE = "deleted_at IS NULL"

# Catálogos del SAT: (tabla, longitud de code, columnas propias del catálogo).
_SAT_CATALOGS: list[tuple[str, int, list[sa.Column]]] = [
    ("tax_regimes", 3, [
        sa.Column("applies_to_individual", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("applies_to_legal_entity", sa.Boolean(), nullable=False, server_default=sa.text("false")),
    ]),
    ("taxes", 3, [
        sa.Column("is_withholding", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("is_transferred", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("is_local", sa.Boolean(), nullable=False, server_default=sa.text("false")),
    ]),
    ("payment_forms", 2, []),
    ("units_of_measure", 20, [
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("symbol", sa.String(length=20), nullable=True),
    ]),
    ("products_services", 8, []),
    ("voucher_types", 1, []),
    ("payment_methods", 3, []),
    ("tax_objects", 2, []),
]

# units_of_measure guarda el nombre corto aparte, así que su description es opcional.
_NULLABLE_DESCRIPTION = {"units_of_measure"}


def _timestamp_columns(with_soft_delete: bool) -> list[sa.Column]:
    columns = [
        sa.Column("created_at", sa.DateTime(), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(), nullable=False, server_default=sa.text("now()")),
    ]
    if with_soft_delete:
        columns.append(sa.Column("deleted_at", sa.DateTime(), nullable=True))
    return columns


def upgrade() -> None:
    # ---------- Schema y catálogos globales del SAT ----------
    op.execute("CREATE SCHEMA IF NOT EXISTS sat")

    for table, code_length, extra_columns in _SAT_CATALOGS:
        op.create_table(
            table,
            sa.Column("id", sa.Integer(), nullable=False),
            sa.Column("code", sa.String(length=code_length), nullable=False),
            sa.Column(
                "description",
                sa.String(length=500),
                nullable=table in _NULLABLE_DESCRIPTION,
            ),
            sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("true")),
            *extra_columns,
            *_timestamp_columns(with_soft_delete=False),
            sa.PrimaryKeyConstraint("id"),
            schema="sat",
        )
        op.create_index(f"ix_sat_{table}_id", table, ["id"], schema="sat")
        # La clave oficial del SAT es única dentro de su catálogo.
        op.create_index(f"ix_sat_{table}_code", table, ["code"], unique=True, schema="sat")

    # Semillas de los catálogos (idempotente: puede volver a correrse sin duplicar).
    sync_catalogs(op.get_bind())

    # ---------- fin.concepts ----------
    op.create_table(
        "concepts",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("company_id", sa.Integer(), nullable=False),
        sa.Column("code", sa.String(length=40), nullable=False),
        sa.Column("description", sa.String(length=500), nullable=False),
        sa.Column("product_service_id", sa.Integer(), nullable=False),
        sa.Column("unit_of_measure_id", sa.Integer(), nullable=True),
        sa.Column("tax_object_id", sa.Integer(), nullable=True),
        sa.Column("unit_price", sa.Numeric(precision=14, scale=2), nullable=True),
        sa.Column("currency", sa.String(length=3), nullable=False, server_default=sa.text("'MXN'")),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("true")),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_by", sa.String(length=64), nullable=True),
        sa.Column("updated_by", sa.String(length=64), nullable=True),
        *_timestamp_columns(with_soft_delete=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"], name="fk_fin_concepts_tenant_id"),
        sa.ForeignKeyConstraint(
            ["product_service_id"], ["sat.products_services.id"], name="fk_fin_concepts_product_service_id"
        ),
        sa.ForeignKeyConstraint(
            ["unit_of_measure_id"], ["sat.units_of_measure.id"], name="fk_fin_concepts_unit_of_measure_id"
        ),
        sa.ForeignKeyConstraint(
            ["tax_object_id"], ["sat.tax_objects.id"], name="fk_fin_concepts_tax_object_id"
        ),
        schema="fin",
    )
    op.create_index("ix_fin_concepts_id", "concepts", ["id"], schema="fin")
    op.create_index("ix_fin_concepts_tenant_id", "concepts", ["tenant_id"], schema="fin")
    op.create_index("ix_fin_concepts_company_id", "concepts", ["company_id"], schema="fin")
    op.create_index("ix_fin_concepts_product_service_id", "concepts", ["product_service_id"], schema="fin")
    # La clave interna del concepto es única por empresa.
    op.create_index(
        "uq_fin_concepts_code", "concepts", ["tenant_id", "company_id", "code"],
        unique=True, schema="fin", postgresql_where=sa.text(_ALIVE),
    )
    # Relación 1:1 con c_ClaveProdServ: una clave del SAT no puede repetirse entre
    # los conceptos vigentes de la misma empresa.
    op.create_index(
        "uq_fin_concepts_product_service", "concepts", ["tenant_id", "company_id", "product_service_id"],
        unique=True, schema="fin", postgresql_where=sa.text(_ALIVE),
    )

    # ---------- fin.issuer_settings ----------
    op.create_table(
        "issuer_settings",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("company_id", sa.Integer(), nullable=False),
        sa.Column("legal_name", sa.String(length=255), nullable=False),
        sa.Column("rfc", sa.String(length=13), nullable=False),
        sa.Column("tax_regime_id", sa.Integer(), nullable=False),
        sa.Column("zip_code", sa.String(length=5), nullable=True),
        sa.Column("updated_by", sa.String(length=64), nullable=True),
        *_timestamp_columns(with_soft_delete=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"], name="fk_fin_issuer_settings_tenant_id"),
        sa.ForeignKeyConstraint(
            ["tax_regime_id"], ["sat.tax_regimes.id"], name="fk_fin_issuer_settings_tax_regime_id"
        ),
        schema="fin",
    )
    op.create_index("ix_fin_issuer_settings_id", "issuer_settings", ["id"], schema="fin")
    op.create_index("ix_fin_issuer_settings_tenant_id", "issuer_settings", ["tenant_id"], schema="fin")
    op.create_index("ix_fin_issuer_settings_company_id", "issuer_settings", ["company_id"], schema="fin")
    op.create_index("ix_fin_issuer_settings_tax_regime_id", "issuer_settings", ["tax_regime_id"], schema="fin")
    # Una sola configuración fiscal vigente por empresa.
    op.create_index(
        "uq_fin_issuer_settings_company", "issuer_settings", ["tenant_id", "company_id"],
        unique=True, schema="fin", postgresql_where=sa.text(_ALIVE),
    )

    # ---------- fin.invoice_item_taxes ----------
    op.create_table(
        "invoice_item_taxes",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("company_id", sa.Integer(), nullable=False),
        sa.Column("invoice_item_id", sa.Integer(), nullable=False),
        sa.Column("tax_id", sa.Integer(), nullable=False),
        sa.Column("is_withholding", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("rate", sa.Numeric(precision=8, scale=6), nullable=True),
        sa.Column("amount", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        *_timestamp_columns(with_soft_delete=True),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"], name="fk_fin_invoice_item_taxes_tenant_id"),
        sa.ForeignKeyConstraint(
            ["invoice_item_id"], ["fin.invoice_items.id"], name="fk_fin_invoice_item_taxes_invoice_item_id"
        ),
        sa.ForeignKeyConstraint(["tax_id"], ["sat.taxes.id"], name="fk_fin_invoice_item_taxes_tax_id"),
        schema="fin",
    )
    op.create_index("ix_fin_invoice_item_taxes_id", "invoice_item_taxes", ["id"], schema="fin")
    op.create_index("ix_fin_invoice_item_taxes_tenant_id", "invoice_item_taxes", ["tenant_id"], schema="fin")
    op.create_index("ix_fin_invoice_item_taxes_company_id", "invoice_item_taxes", ["company_id"], schema="fin")
    op.create_index(
        "ix_fin_invoice_item_taxes_invoice_item_id", "invoice_item_taxes", ["invoice_item_id"], schema="fin"
    )
    # Un mismo impuesto no puede declararse dos veces con el mismo rol en la partida.
    op.create_index(
        "uq_fin_invoice_item_taxes", "invoice_item_taxes", ["invoice_item_id", "tax_id", "is_withholding"],
        unique=True, schema="fin", postgresql_where=sa.text(_ALIVE),
    )

    # ---------- fin.invoices: claves fiscales del comprobante ----------
    # Todas nullable: las facturas ya emitidas no tienen estos datos.
    op.add_column("invoices", sa.Column("voucher_type_id", sa.Integer(), nullable=True), schema="fin")
    op.add_column("invoices", sa.Column("payment_form_id", sa.Integer(), nullable=True), schema="fin")
    op.add_column("invoices", sa.Column("payment_method_id", sa.Integer(), nullable=True), schema="fin")
    op.add_column("invoices", sa.Column("expedition_zip_code", sa.String(length=5), nullable=True), schema="fin")
    op.create_foreign_key(
        "fk_fin_invoices_voucher_type_id", "invoices", "voucher_types",
        ["voucher_type_id"], ["id"], source_schema="fin", referent_schema="sat",
    )
    op.create_foreign_key(
        "fk_fin_invoices_payment_form_id", "invoices", "payment_forms",
        ["payment_form_id"], ["id"], source_schema="fin", referent_schema="sat",
    )
    op.create_foreign_key(
        "fk_fin_invoices_payment_method_id", "invoices", "payment_methods",
        ["payment_method_id"], ["id"], source_schema="fin", referent_schema="sat",
    )

    # ---------- fin.invoice_items: claves fiscales de la partida ----------
    # La columna de texto libre `concept` se conserva intacta y obligatoria: la usa el
    # PDF actual de la factura.
    op.add_column("invoice_items", sa.Column("concept_id", sa.Integer(), nullable=True), schema="fin")
    op.add_column("invoice_items", sa.Column("product_service_id", sa.Integer(), nullable=True), schema="fin")
    op.add_column("invoice_items", sa.Column("unit_of_measure_id", sa.Integer(), nullable=True), schema="fin")
    op.add_column("invoice_items", sa.Column("tax_object_id", sa.Integer(), nullable=True), schema="fin")
    op.create_index("ix_fin_invoice_items_concept_id", "invoice_items", ["concept_id"], schema="fin")
    op.create_foreign_key(
        "fk_fin_invoice_items_concept_id", "invoice_items", "concepts",
        ["concept_id"], ["id"], source_schema="fin", referent_schema="fin",
    )
    op.create_foreign_key(
        "fk_fin_invoice_items_product_service_id", "invoice_items", "products_services",
        ["product_service_id"], ["id"], source_schema="fin", referent_schema="sat",
    )
    op.create_foreign_key(
        "fk_fin_invoice_items_unit_of_measure_id", "invoice_items", "units_of_measure",
        ["unit_of_measure_id"], ["id"], source_schema="fin", referent_schema="sat",
    )
    op.create_foreign_key(
        "fk_fin_invoice_items_tax_object_id", "invoice_items", "tax_objects",
        ["tax_object_id"], ["id"], source_schema="fin", referent_schema="sat",
    )


def downgrade() -> None:
    # fin.invoice_items
    for constraint in (
        "fk_fin_invoice_items_tax_object_id",
        "fk_fin_invoice_items_unit_of_measure_id",
        "fk_fin_invoice_items_product_service_id",
        "fk_fin_invoice_items_concept_id",
    ):
        op.drop_constraint(constraint, "invoice_items", schema="fin", type_="foreignkey")
    op.drop_index("ix_fin_invoice_items_concept_id", table_name="invoice_items", schema="fin")
    for column in ("tax_object_id", "unit_of_measure_id", "product_service_id", "concept_id"):
        op.drop_column("invoice_items", column, schema="fin")

    # fin.invoices
    for constraint in (
        "fk_fin_invoices_payment_method_id",
        "fk_fin_invoices_payment_form_id",
        "fk_fin_invoices_voucher_type_id",
    ):
        op.drop_constraint(constraint, "invoices", schema="fin", type_="foreignkey")
    for column in ("expedition_zip_code", "payment_method_id", "payment_form_id", "voucher_type_id"):
        op.drop_column("invoices", column, schema="fin")

    # Tablas nuevas (los índices caen con la tabla).
    op.drop_table("invoice_item_taxes", schema="fin")
    op.drop_table("issuer_settings", schema="fin")
    op.drop_table("concepts", schema="fin")

    # Catálogos del SAT: se va el schema completo.
    op.execute("DROP SCHEMA IF EXISTS sat CASCADE")
