"""crm commercial (service_requests, rate_requests, quotes, quote_items) + ops (shipments, documents)

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

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

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


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, schema: 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)


def upgrade() -> None:
    # ---------- crm.service_requests ----------
    op.create_table(
        "service_requests",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("reference", sa.String(length=40), nullable=True),
        sa.Column("account_id", sa.Integer(), nullable=True),
        sa.Column("operation_type", sa.String(length=20), nullable=False),
        sa.Column("transport_mode", sa.String(length=20), nullable=True),
        sa.Column("service_type", sa.String(length=20), nullable=True),
        sa.Column("incoterm", sa.String(length=10), nullable=True),
        sa.Column("origin", sa.String(length=160), nullable=True),
        sa.Column("destination", sa.String(length=160), nullable=True),
        sa.Column("cargo_type", sa.String(length=120), nullable=True),
        sa.Column("weight", sa.Numeric(precision=14, scale=3), nullable=True),
        sa.Column("volume", sa.Numeric(precision=14, scale=3), nullable=True),
        sa.Column("load_type", sa.String(length=10), nullable=True),
        sa.Column("container_equipment", sa.String(length=120), nullable=True),
        sa.Column("commodity", sa.Text(), nullable=True),
        sa.Column("required_date", sa.Date(), nullable=True),
        sa.Column("destination_agent_id", sa.Integer(), nullable=True),
        sa.Column("requirements", sa.Text(), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'nueva'")),
        sa.Column("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"]),
        sa.ForeignKeyConstraint(["account_id"], ["crm.accounts.id"]),
        sa.ForeignKeyConstraint(["destination_agent_id"], ["crm.suppliers.id"]),
        schema="crm",
    )
    _scoped_indexes("service_requests", "crm")
    op.create_index("ix_crm_service_requests_reference", "service_requests", ["reference"], schema="crm")
    op.create_index("ix_crm_service_requests_account_id", "service_requests", ["account_id"], schema="crm")
    op.create_index("ix_crm_service_requests_status", "service_requests", ["status"], schema="crm")
    op.create_index("ix_crm_service_requests_owner_user_id", "service_requests", ["owner_user_id"], schema="crm")

    # ---------- crm.rate_requests ----------
    op.create_table(
        "rate_requests",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("service_request_id", sa.Integer(), nullable=False),
        sa.Column("supplier_id", sa.Integer(), nullable=True),
        sa.Column("concept", sa.String(length=60), nullable=False),
        sa.Column("description", sa.String(length=255), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'solicitada'")),
        sa.Column("rate_amount", sa.Numeric(precision=14, scale=2), nullable=True),
        sa.Column("currency", sa.String(length=3), nullable=True),
        sa.Column("valid_until", sa.Date(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["service_request_id"], ["crm.service_requests.id"]),
        sa.ForeignKeyConstraint(["supplier_id"], ["crm.suppliers.id"]),
        schema="crm",
    )
    _scoped_indexes("rate_requests", "crm")
    op.create_index("ix_crm_rate_requests_service_request_id", "rate_requests", ["service_request_id"], schema="crm")
    op.create_index("ix_crm_rate_requests_supplier_id", "rate_requests", ["supplier_id"], schema="crm")

    # ---------- crm.quotes ----------
    op.create_table(
        "quotes",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("reference", sa.String(length=40), nullable=True),
        sa.Column("service_request_id", sa.Integer(), nullable=True),
        sa.Column("account_id", sa.Integer(), nullable=True),
        sa.Column("currency", sa.String(length=3), nullable=False, server_default=sa.text("'USD'")),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'borrador'")),
        sa.Column("issue_date", sa.Date(), nullable=True),
        sa.Column("valid_until", sa.Date(), nullable=True),
        sa.Column("total_cost", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("total_sale", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("sent_at", sa.DateTime(), nullable=True),
        sa.Column("accepted_at", sa.DateTime(), nullable=True),
        sa.Column("rejected_at", sa.DateTime(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("terms", 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"]),
        sa.ForeignKeyConstraint(["service_request_id"], ["crm.service_requests.id"]),
        sa.ForeignKeyConstraint(["account_id"], ["crm.accounts.id"]),
        schema="crm",
    )
    _scoped_indexes("quotes", "crm")
    op.create_index("ix_crm_quotes_reference", "quotes", ["reference"], schema="crm")
    op.create_index("ix_crm_quotes_service_request_id", "quotes", ["service_request_id"], schema="crm")
    op.create_index("ix_crm_quotes_account_id", "quotes", ["account_id"], schema="crm")
    op.create_index("ix_crm_quotes_status", "quotes", ["status"], schema="crm")
    op.create_index("ix_crm_quotes_owner_user_id", "quotes", ["owner_user_id"], schema="crm")

    # ---------- crm.quote_items ----------
    op.create_table(
        "quote_items",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("quote_id", sa.Integer(), nullable=False),
        sa.Column("concept", sa.String(length=60), nullable=False),
        sa.Column("description", sa.String(length=255), nullable=True),
        sa.Column("supplier_id", sa.Integer(), nullable=True),
        sa.Column("quantity", sa.Numeric(precision=12, scale=2), nullable=False, server_default=sa.text("1")),
        sa.Column("unit_cost", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("unit_sale", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("currency", sa.String(length=3), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["quote_id"], ["crm.quotes.id"]),
        sa.ForeignKeyConstraint(["supplier_id"], ["crm.suppliers.id"]),
        schema="crm",
    )
    _scoped_indexes("quote_items", "crm")
    op.create_index("ix_crm_quote_items_quote_id", "quote_items", ["quote_id"], schema="crm")

    # ---------- schema ops ----------
    op.execute("CREATE SCHEMA IF NOT EXISTS ops")

    op.create_table(
        "shipments",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("reference", sa.String(length=40), nullable=True),
        sa.Column("quote_id", sa.Integer(), nullable=True),
        sa.Column("service_request_id", sa.Integer(), nullable=True),
        sa.Column("account_id", sa.Integer(), nullable=True),
        sa.Column("operation_type", sa.String(length=20), nullable=True),
        sa.Column("transport_mode", sa.String(length=20), nullable=True),
        sa.Column("service_type", sa.String(length=20), nullable=True),
        sa.Column("incoterm", sa.String(length=10), nullable=True),
        sa.Column("origin", sa.String(length=160), nullable=True),
        sa.Column("destination", sa.String(length=160), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'abierta'")),
        sa.Column("booking_number", sa.String(length=60), nullable=True),
        sa.Column("carrier_supplier_id", sa.Integer(), nullable=True),
        sa.Column("customs_agent_id", sa.Integer(), nullable=True),
        sa.Column("destination_agent_id", sa.Integer(), nullable=True),
        sa.Column("cutoff_date", sa.DateTime(), nullable=True),
        sa.Column("etd", sa.Date(), nullable=True),
        sa.Column("eta", sa.Date(), nullable=True),
        sa.Column("vessel_flight", sa.String(length=120), nullable=True),
        sa.Column("container_number", sa.String(length=60), nullable=True),
        sa.Column("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"]),
        sa.ForeignKeyConstraint(["quote_id"], ["crm.quotes.id"]),
        sa.ForeignKeyConstraint(["service_request_id"], ["crm.service_requests.id"]),
        sa.ForeignKeyConstraint(["account_id"], ["crm.accounts.id"]),
        sa.ForeignKeyConstraint(["carrier_supplier_id"], ["crm.suppliers.id"]),
        sa.ForeignKeyConstraint(["customs_agent_id"], ["crm.suppliers.id"]),
        sa.ForeignKeyConstraint(["destination_agent_id"], ["crm.suppliers.id"]),
        schema="ops",
    )
    _scoped_indexes("shipments", "ops")
    op.create_index("ix_ops_shipments_reference", "shipments", ["reference"], schema="ops")
    op.create_index("ix_ops_shipments_quote_id", "shipments", ["quote_id"], schema="ops")
    op.create_index("ix_ops_shipments_account_id", "shipments", ["account_id"], schema="ops")
    op.create_index("ix_ops_shipments_status", "shipments", ["status"], schema="ops")
    op.create_index("ix_ops_shipments_owner_user_id", "shipments", ["owner_user_id"], schema="ops")

    op.create_table(
        "shipment_documents",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("shipment_id", sa.Integer(), nullable=False),
        sa.Column("doc_kind", sa.String(length=10), nullable=False, server_default=sa.text("'otro'")),
        sa.Column("doc_type", sa.String(length=30), nullable=False),
        sa.Column("number", sa.String(length=80), nullable=True),
        sa.Column("issue_date", sa.Date(), nullable=True),
        sa.Column("file_url", sa.String(length=1024), nullable=True),
        sa.Column("file_key", sa.String(length=512), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["shipment_id"], ["ops.shipments.id"]),
        schema="ops",
    )
    _scoped_indexes("shipment_documents", "ops")
    op.create_index("ix_ops_shipment_documents_shipment_id", "shipment_documents", ["shipment_id"], schema="ops")


def downgrade() -> None:
    op.drop_table("shipment_documents", schema="ops")
    op.drop_table("shipments", schema="ops")
    op.execute("DROP SCHEMA IF EXISTS ops")
    op.drop_table("quote_items", schema="crm")
    op.drop_table("quotes", schema="crm")
    op.drop_table("rate_requests", schema="crm")
    op.drop_table("service_requests", schema="crm")
