"""crm catalogs: suppliers, addresses, documents + enrich accounts/contacts

Revision ID: a7b8c9d0e1f2
Revises: f1a2b3c4d5e6
Create Date: 2026-07-14 00:00:00.000000

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "a7b8c9d0e1f2"
down_revision: Union[str, None] = "f1a2b3c4d5e6"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

SCHEMA = "crm"


def _scoped_columns() -> list[sa.Column]:
    return [
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("company_id", sa.Integer(), nullable=False),
        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.Column("deleted_at", sa.DateTime(), nullable=True),
    ]


def _scoped_indexes(table: str) -> None:
    op.create_index(f"ix_{SCHEMA}_{table}_id", table, ["id"], schema=SCHEMA)
    op.create_index(f"ix_{SCHEMA}_{table}_tenant_id", table, ["tenant_id"], schema=SCHEMA)
    op.create_index(f"ix_{SCHEMA}_{table}_company_id", table, ["company_id"], schema=SCHEMA)


# Columnas nuevas de crm.accounts (Clientes/Prospectos enriquecido)
_ACCOUNT_COLUMNS = [
    ("curp", sa.String(length=18), {}),
    ("record_type", sa.String(length=20), {"nullable": False, "server_default": sa.text("'cliente'")}),
    ("person_type", sa.String(length=10), {}),
    ("commercial_classification", sa.String(length=20), {}),
    ("preferred_contact_method", sa.String(length=20), {}),
    ("language", sa.String(length=40), {}),
    ("tax_regime", sa.String(length=120), {}),
    ("cfdi_use", sa.String(length=60), {}),
    ("payment_method", sa.String(length=60), {}),
    ("payment_form", sa.String(length=60), {}),
    ("currency", sa.String(length=3), {}),
    ("credit_limit", sa.Numeric(precision=14, scale=2), {}),
    ("credit_days", sa.Integer(), {}),
    ("commercial_terms", sa.Text(), {}),
    ("internal_notes", sa.Text(), {}),
    ("created_by", sa.String(length=64), {}),
    ("updated_by", sa.String(length=64), {}),
]

# Columnas nuevas de crm.contacts (enriquecidas)
_CONTACT_COLUMNS = [
    ("supplier_id", sa.Integer(), {}),
    ("extension", sa.String(length=20), {}),
    ("whatsapp", sa.String(length=40), {}),
    ("area", sa.String(length=120), {}),
    ("receives_quotes", sa.Boolean(), {"nullable": False, "server_default": sa.text("false")}),
    ("receives_invoices", sa.Boolean(), {"nullable": False, "server_default": sa.text("false")}),
    ("receives_commercial_info", sa.Boolean(), {"nullable": False, "server_default": sa.text("false")}),
    ("status", sa.String(length=20), {"nullable": False, "server_default": sa.text("'active'")}),
]


def upgrade() -> None:
    # ----- suppliers (Proveedores) -----
    op.create_table(
        "suppliers",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("trade_name", sa.String(length=255), nullable=True),
        sa.Column("rfc", sa.String(length=13), nullable=True),
        sa.Column("curp", sa.String(length=18), nullable=True),
        sa.Column("person_type", sa.String(length=10), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'active'")),
        sa.Column("classifications", sa.JSON(), nullable=True),
        sa.Column("services_offered", sa.Text(), nullable=True),
        sa.Column("coverage", sa.String(length=20), nullable=True),
        sa.Column("countries", sa.JSON(), nullable=True),
        sa.Column("ports", sa.JSON(), nullable=True),
        sa.Column("airports", sa.JSON(), nullable=True),
        sa.Column("customs", sa.JSON(), nullable=True),
        sa.Column("business_hours", sa.String(length=255), nullable=True),
        sa.Column("quote_currency", sa.String(length=3), nullable=True),
        sa.Column("avg_response_time", sa.String(length=120), nullable=True),
        sa.Column("commercial_notes", sa.Text(), nullable=True),
        sa.Column("email", sa.String(length=255), nullable=True),
        sa.Column("phone", sa.String(length=40), nullable=True),
        sa.Column("website", sa.String(length=255), nullable=True),
        sa.Column("tax_regime", sa.String(length=120), nullable=True),
        sa.Column("payment_method", sa.String(length=60), nullable=True),
        sa.Column("payment_form", sa.String(length=60), nullable=True),
        sa.Column("credit_limit", sa.Numeric(precision=14, scale=2), nullable=True),
        sa.Column("credit_days", sa.Integer(), nullable=True),
        sa.Column("commercial_terms", sa.Text(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("internal_notes", sa.Text(), nullable=True),
        sa.Column("owner_user_id", sa.String(length=64), nullable=True),
        sa.Column("created_by", sa.String(length=64), nullable=True),
        sa.Column("updated_by", sa.String(length=64), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("suppliers")
    op.create_index("ix_crm_suppliers_rfc", "suppliers", ["rfc"], schema=SCHEMA)
    op.create_index("ix_crm_suppliers_owner_user_id", "suppliers", ["owner_user_id"], schema=SCHEMA)

    # ----- enrich accounts -----
    for name, coltype, kwargs in _ACCOUNT_COLUMNS:
        op.add_column("accounts", sa.Column(name, coltype, **kwargs), schema=SCHEMA)
    op.create_index("ix_crm_accounts_record_type", "accounts", ["record_type"], schema=SCHEMA)

    # ----- enrich contacts -----
    for name, coltype, kwargs in _CONTACT_COLUMNS:
        op.add_column("contacts", sa.Column(name, coltype, **kwargs), schema=SCHEMA)
    op.create_index("ix_crm_contacts_supplier_id", "contacts", ["supplier_id"], schema=SCHEMA)
    op.create_foreign_key(
        "contacts_supplier_id_fkey", "contacts", "suppliers",
        ["supplier_id"], ["id"], source_schema=SCHEMA, referent_schema=SCHEMA,
    )

    # ----- addresses -----
    op.create_table(
        "addresses",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("account_id", sa.Integer(), nullable=True),
        sa.Column("supplier_id", sa.Integer(), nullable=True),
        sa.Column("address_type", sa.String(length=20), nullable=False, server_default=sa.text("'fiscal'")),
        sa.Column("street", sa.String(length=255), nullable=True),
        sa.Column("ext_number", sa.String(length=30), nullable=True),
        sa.Column("int_number", sa.String(length=30), nullable=True),
        sa.Column("neighborhood", sa.String(length=120), nullable=True),
        sa.Column("postal_code", sa.String(length=10), nullable=True),
        sa.Column("city", sa.String(length=120), nullable=True),
        sa.Column("state", sa.String(length=120), nullable=True),
        sa.Column("country", sa.String(length=2), nullable=True, server_default=sa.text("'MX'")),
        sa.Column("reference_notes", sa.Text(), nullable=True),
        sa.Column("is_primary", sa.Boolean(), nullable=False, server_default=sa.text("false")),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["account_id"], [f"{SCHEMA}.accounts.id"]),
        sa.ForeignKeyConstraint(["supplier_id"], [f"{SCHEMA}.suppliers.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("addresses")
    op.create_index("ix_crm_addresses_account_id", "addresses", ["account_id"], schema=SCHEMA)
    op.create_index("ix_crm_addresses_supplier_id", "addresses", ["supplier_id"], schema=SCHEMA)

    # ----- documents -----
    op.create_table(
        "documents",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("account_id", sa.Integer(), nullable=True),
        sa.Column("supplier_id", sa.Integer(), nullable=True),
        sa.Column("doc_type", sa.String(length=60), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("file_key", sa.String(length=512), nullable=True),
        sa.Column("file_url", sa.String(length=1024), nullable=True),
        sa.Column("content_type", sa.String(length=120), nullable=True),
        sa.Column("size_bytes", sa.Integer(), nullable=True),
        sa.Column("uploaded_by", sa.String(length=64), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["account_id"], [f"{SCHEMA}.accounts.id"]),
        sa.ForeignKeyConstraint(["supplier_id"], [f"{SCHEMA}.suppliers.id"]),
        schema=SCHEMA,
    )
    _scoped_indexes("documents")
    op.create_index("ix_crm_documents_account_id", "documents", ["account_id"], schema=SCHEMA)
    op.create_index("ix_crm_documents_supplier_id", "documents", ["supplier_id"], schema=SCHEMA)


def downgrade() -> None:
    op.drop_table("documents", schema=SCHEMA)
    op.drop_table("addresses", schema=SCHEMA)

    op.drop_constraint("contacts_supplier_id_fkey", "contacts", schema=SCHEMA, type_="foreignkey")
    for name, _coltype, _kwargs in _CONTACT_COLUMNS:
        op.drop_column("contacts", name, schema=SCHEMA)

    op.drop_index("ix_crm_accounts_record_type", table_name="accounts", schema=SCHEMA)
    for name, _coltype, _kwargs in _ACCOUNT_COLUMNS:
        op.drop_column("accounts", name, schema=SCHEMA)

    op.drop_table("suppliers", schema=SCHEMA)
