"""Campos del documento maestro de cotización en la solicitud + folios del ciclo comercial

Revision ID: b1c2d3e4f5a6
Revises: a0b1c2d3e4f5
Create Date: 2026-08-03 00:00:00.000000

Amplía crm.service_requests con los campos que exige el documento maestro de
cotización, agrega los back-links y la dirección impo/expo del ciclo
Oportunidad→Solicitud→Cotización→Operación, y crea crm.folio_counters para los
folios auto-generados ({LETRA}{AAAA}-{MM}-{NNN}-{DIR}).
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "b1c2d3e4f5a6"
down_revision: Union[str, None] = "a0b1c2d3e4f5"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

SCHEMA = "crm"

# Columnas nuevas de crm.service_requests (nombre, tipo, kwargs).
_SR_COLUMNS = [
    ("contact_id", sa.Integer(), {}),
    ("request_date", sa.Date(), {}),
    ("currency", sa.String(length=3), {}),
    ("priority", sa.String(length=20), {}),
    ("origin_country", sa.String(length=3), {}),
    ("origin_city", sa.String(length=120), {}),
    ("origin_port", sa.String(length=20), {}),
    ("destination_country", sa.String(length=3), {}),
    ("destination_city", sa.String(length=120), {}),
    ("destination_port", sa.String(length=20), {}),
    ("pickup_location", sa.String(length=255), {}),
    ("delivery_location", sa.String(length=255), {}),
    ("estimated_shipment_date", sa.Date(), {}),
    ("cargo_value", sa.Numeric(14, 2), {}),
    ("insurance_required", sa.Boolean(), {"server_default": sa.text("false")}),
    ("hs_code", sa.String(length=20), {}),
    ("goods_origin_country", sa.String(length=3), {}),
    ("hazardous_imo", sa.Boolean(), {"server_default": sa.text("false")}),
    ("refrigerated", sa.Boolean(), {"server_default": sa.text("false")}),
    ("stackable", sa.Boolean(), {"server_default": sa.text("false")}),
    ("pieces_count", sa.Integer(), {}),
    ("boxes_count", sa.Integer(), {}),
    ("pallets_count", sa.Integer(), {}),
    ("net_weight", sa.Numeric(14, 3), {}),
    ("length_cm", sa.Numeric(10, 2), {}),
    ("width_cm", sa.Numeric(10, 2), {}),
    ("height_cm", sa.Numeric(10, 2), {}),
    ("measurement_unit", sa.String(length=20), {}),
    ("container_count", sa.Integer(), {}),
    ("packaging_type", sa.String(length=20), {}),
    ("oversized", sa.Boolean(), {"server_default": sa.text("false")}),
    ("weight_per_pallet", sa.Numeric(14, 3), {}),
    ("volume_per_pallet", sa.Numeric(14, 3), {}),
    ("additional_services", sa.JSON(), {}),
    ("payment_method", sa.String(length=20), {}),
    ("client_notes", sa.Text(), {}),
    ("internal_notes", sa.Text(), {}),
]


def upgrade() -> None:
    # ----- crm.service_requests: campos del documento maestro de cotización -----
    for name, col_type, kwargs in _SR_COLUMNS:
        nullable = "server_default" not in kwargs  # los boolean quedan NOT NULL con default false
        op.add_column(
            "service_requests",
            sa.Column(name, col_type, nullable=nullable, **kwargs),
            schema=SCHEMA,
        )
    op.create_foreign_key(
        "fk_crm_service_requests_contact_id", "service_requests", "contacts",
        ["contact_id"], ["id"], source_schema=SCHEMA, referent_schema=SCHEMA,
    )
    op.create_index(
        "ix_crm_service_requests_contact_id", "service_requests", ["contact_id"], schema=SCHEMA
    )

    # ----- crm.documents: adjuntos de una solicitud -----
    op.add_column(
        "documents", sa.Column("service_request_id", sa.Integer(), nullable=True), schema=SCHEMA
    )
    op.create_foreign_key(
        "fk_crm_documents_service_request_id", "documents", "service_requests",
        ["service_request_id"], ["id"], source_schema=SCHEMA, referent_schema=SCHEMA,
    )
    op.create_index(
        "ix_crm_documents_service_request_id", "documents", ["service_request_id"], schema=SCHEMA
    )

    # ----- crm.opportunities: dirección impo/expo + folio + back-link a la solicitud -----
    op.add_column("opportunities", sa.Column("operation_type", sa.String(length=20), nullable=True), schema=SCHEMA)
    op.add_column("opportunities", sa.Column("reference", sa.String(length=40), nullable=True), schema=SCHEMA)
    op.add_column(
        "opportunities",
        sa.Column("converted_service_request_id", sa.Integer(), nullable=True),
        schema=SCHEMA,
    )
    op.create_foreign_key(
        "fk_crm_opportunities_converted_sr", "opportunities", "service_requests",
        ["converted_service_request_id"], ["id"], source_schema=SCHEMA, referent_schema=SCHEMA,
    )
    op.create_index(
        "ix_crm_opportunities_reference", "opportunities", ["reference"], schema=SCHEMA
    )

    # ----- crm.quotes: variante FCL/LCL para la comparación "Ambas" -----
    op.add_column("quotes", sa.Column("load_type", sa.String(length=10), nullable=True), schema=SCHEMA)

    # ----- crm.folio_counters: consecutivo mensual por compañía y entidad -----
    op.create_table(
        "folio_counters",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("entity", sa.String(length=4), nullable=False),
        sa.Column("period", sa.String(length=7), nullable=False),
        sa.Column("last_number", sa.Integer(), nullable=False, server_default=sa.text("0")),
        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.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.UniqueConstraint(
            "tenant_id", "company_id", "entity", "period", name="uq_crm_folio_counters_scope"
        ),
        schema=SCHEMA,
    )
    op.create_index("ix_crm_folio_counters_id", "folio_counters", ["id"], schema=SCHEMA)
    op.create_index("ix_crm_folio_counters_tenant_id", "folio_counters", ["tenant_id"], schema=SCHEMA)
    op.create_index("ix_crm_folio_counters_company_id", "folio_counters", ["company_id"], schema=SCHEMA)


def downgrade() -> None:
    op.drop_index("ix_crm_folio_counters_company_id", table_name="folio_counters", schema=SCHEMA)
    op.drop_index("ix_crm_folio_counters_tenant_id", table_name="folio_counters", schema=SCHEMA)
    op.drop_index("ix_crm_folio_counters_id", table_name="folio_counters", schema=SCHEMA)
    op.drop_table("folio_counters", schema=SCHEMA)

    op.drop_column("quotes", "load_type", schema=SCHEMA)

    op.drop_index("ix_crm_opportunities_reference", table_name="opportunities", schema=SCHEMA)
    op.drop_constraint("fk_crm_opportunities_converted_sr", "opportunities", schema=SCHEMA, type_="foreignkey")
    op.drop_column("opportunities", "converted_service_request_id", schema=SCHEMA)
    op.drop_column("opportunities", "reference", schema=SCHEMA)
    op.drop_column("opportunities", "operation_type", schema=SCHEMA)

    op.drop_index("ix_crm_documents_service_request_id", table_name="documents", schema=SCHEMA)
    op.drop_constraint("fk_crm_documents_service_request_id", "documents", schema=SCHEMA, type_="foreignkey")
    op.drop_column("documents", "service_request_id", schema=SCHEMA)

    op.drop_index("ix_crm_service_requests_contact_id", table_name="service_requests", schema=SCHEMA)
    op.drop_constraint("fk_crm_service_requests_contact_id", "service_requests", schema=SCHEMA, type_="foreignkey")
    for name, _col_type, _kwargs in reversed(_SR_COLUMNS):
        op.drop_column("service_requests", name, schema=SCHEMA)
