"""fin (invoices, invoice_items, payments) + ops.shipment_events

Revision ID: c4d5e6f7a8b9
Revises: b3c4d5e6f7a8
Create Date: 2026-07-15 00:00:00.000000

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "c4d5e6f7a8b9"
down_revision: Union[str, None] = "b3c4d5e6f7a8"
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:
    # ---------- schema fin ----------
    op.execute("CREATE SCHEMA IF NOT EXISTS fin")

    op.create_table(
        "invoices",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("reference", sa.String(length=40), nullable=True),
        sa.Column("shipment_id", sa.Integer(), nullable=True),
        sa.Column("quote_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("'MXN'")),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'borrador'")),
        sa.Column("issue_date", sa.Date(), nullable=True),
        sa.Column("due_date", sa.Date(), nullable=True),
        sa.Column("subtotal", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("tax_rate", sa.Numeric(precision=5, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("tax_amount", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("total", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("paid_amount", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("balance", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        sa.Column("bank_info", sa.Text(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("sent_at", sa.DateTime(), nullable=True),
        sa.Column("paid_at", sa.DateTime(), 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(["shipment_id"], ["ops.shipments.id"]),
        sa.ForeignKeyConstraint(["quote_id"], ["crm.quotes.id"]),
        sa.ForeignKeyConstraint(["account_id"], ["crm.accounts.id"]),
        schema="fin",
    )
    _scoped_indexes("invoices", "fin")
    op.create_index("ix_fin_invoices_reference", "invoices", ["reference"], schema="fin")
    op.create_index("ix_fin_invoices_shipment_id", "invoices", ["shipment_id"], schema="fin")
    op.create_index("ix_fin_invoices_account_id", "invoices", ["account_id"], schema="fin")
    op.create_index("ix_fin_invoices_status", "invoices", ["status"], schema="fin")
    op.create_index("ix_fin_invoices_owner_user_id", "invoices", ["owner_user_id"], schema="fin")

    op.create_table(
        "invoice_items",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("invoice_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("quantity", sa.Numeric(precision=12, scale=2), nullable=False, server_default=sa.text("1")),
        sa.Column("unit_amount", sa.Numeric(precision=14, scale=2), nullable=False, server_default=sa.text("0")),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["invoice_id"], ["fin.invoices.id"]),
        schema="fin",
    )
    _scoped_indexes("invoice_items", "fin")
    op.create_index("ix_fin_invoice_items_invoice_id", "invoice_items", ["invoice_id"], schema="fin")

    op.create_table(
        "payments",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("invoice_id", sa.Integer(), nullable=False),
        sa.Column("amount", sa.Numeric(precision=14, scale=2), nullable=False),
        sa.Column("payment_date", sa.Date(), nullable=True),
        sa.Column("method", sa.String(length=40), nullable=True),
        sa.Column("reference", sa.String(length=120), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        *_scoped_columns(),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["tenant_id"], ["core.tenants.id"]),
        sa.ForeignKeyConstraint(["invoice_id"], ["fin.invoices.id"]),
        schema="fin",
    )
    _scoped_indexes("payments", "fin")
    op.create_index("ix_fin_payments_invoice_id", "payments", ["invoice_id"], schema="fin")

    # ---------- ops.shipment_events ----------
    op.create_table(
        "shipment_events",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("shipment_id", sa.Integer(), nullable=False),
        sa.Column("event_type", sa.String(length=60), nullable=True),
        sa.Column("title", sa.String(length=160), nullable=False),
        sa.Column("status", sa.String(length=20), nullable=False, server_default=sa.text("'pendiente'")),
        sa.Column("position", sa.Integer(), nullable=False, server_default=sa.text("0")),
        sa.Column("planned_date", sa.DateTime(), nullable=True),
        sa.Column("actual_date", sa.DateTime(), 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_events", "ops")
    op.create_index("ix_ops_shipment_events_shipment_id", "shipment_events", ["shipment_id"], schema="ops")


def downgrade() -> None:
    op.drop_table("shipment_events", schema="ops")
    op.drop_table("payments", schema="fin")
    op.drop_table("invoice_items", schema="fin")
    op.drop_table("invoices", schema="fin")
    op.execute("DROP SCHEMA IF EXISTS fin")
