"""crm catalog_items (catálogos de referencia) + columnas nuevas accounts/suppliers

Revision ID: e6f7a8b9c0d1
Revises: d5e6f7a8b9c0
Create Date: 2026-07-22 00:00:00.000000

Soporta T2026-07-081 (Clientes/Prospectos) y T2026-07-082 (Proveedores):
catálogos de referencia SAT/ISO + propios del cliente, y campos faltantes
(observaciones comerciales, "otro" de medio de contacto y de clasificación).
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "e6f7a8b9c0d1"
down_revision: Union[str, None] = "d5e6f7a8b9c0"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

SCHEMA = "crm"


def upgrade() -> None:
    # ----- crm.catalog_items -----
    op.create_table(
        "catalog_items",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("catalog", sa.String(length=60), nullable=False),
        sa.Column("code", sa.String(length=64), nullable=False),
        sa.Column("label", sa.String(length=255), nullable=False),
        sa.Column("parent_catalog", sa.String(length=60), nullable=True),
        sa.Column("parent_code", sa.String(length=64), nullable=True),
        # NULL = catálogo global (Aduanasoft); con valor = catálogo del tenant.
        sa.Column("tenant_id", sa.Integer(), nullable=True),
        sa.Column("sort_order", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.text("true")),
        sa.Column("is_system", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        sa.Column("extra", sa.JSON(), nullable=True),
        sa.Column("created_by", sa.String(length=64), nullable=True),
        sa.Column("updated_by", sa.String(length=64), nullable=True),
        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()")),
        sa.PrimaryKeyConstraint("id"),
        schema=SCHEMA,
    )
    op.create_index("ix_crm_catalog_items_id", "catalog_items", ["id"], schema=SCHEMA)
    op.create_index("ix_crm_catalog_items_catalog", "catalog_items", ["catalog"], schema=SCHEMA)
    op.create_index("ix_crm_catalog_items_tenant_id", "catalog_items", ["tenant_id"], schema=SCHEMA)
    op.create_index(
        "ix_crm_catalog_items_lookup", "catalog_items", ["catalog", "tenant_id", "is_active"], schema=SCHEMA
    )
    # Unicidad de clave por catálogo: global (tenant NULL) y por tenant, separadas.
    op.create_index(
        "uq_crm_catalog_items_global",
        "catalog_items",
        ["catalog", "code"],
        unique=True,
        schema=SCHEMA,
        postgresql_where=sa.text("tenant_id IS NULL"),
    )
    op.create_index(
        "uq_crm_catalog_items_tenant",
        "catalog_items",
        ["catalog", "code", "tenant_id"],
        unique=True,
        schema=SCHEMA,
        postgresql_where=sa.text("tenant_id IS NOT NULL"),
    )

    # ----- columnas nuevas -----
    # Clientes/Prospectos: observaciones comerciales + "otro" del medio de contacto.
    op.add_column("accounts", sa.Column("commercial_observations", sa.Text(), nullable=True), schema=SCHEMA)
    op.add_column("accounts", sa.Column("preferred_contact_other", sa.String(length=120), nullable=True), schema=SCHEMA)
    # Proveedores: "otro" de la clasificación múltiple.
    op.add_column("suppliers", sa.Column("classification_other", sa.String(length=120), nullable=True), schema=SCHEMA)


def downgrade() -> None:
    op.drop_column("suppliers", "classification_other", schema=SCHEMA)
    op.drop_column("accounts", "preferred_contact_other", schema=SCHEMA)
    op.drop_column("accounts", "commercial_observations", schema=SCHEMA)

    op.drop_index("uq_crm_catalog_items_tenant", table_name="catalog_items", schema=SCHEMA)
    op.drop_index("uq_crm_catalog_items_global", table_name="catalog_items", schema=SCHEMA)
    op.drop_index("ix_crm_catalog_items_lookup", table_name="catalog_items", schema=SCHEMA)
    op.drop_index("ix_crm_catalog_items_tenant_id", table_name="catalog_items", schema=SCHEMA)
    op.drop_index("ix_crm_catalog_items_catalog", table_name="catalog_items", schema=SCHEMA)
    op.drop_index("ix_crm_catalog_items_id", table_name="catalog_items", schema=SCHEMA)
    op.drop_table("catalog_items", schema=SCHEMA)
