"""
Modelos ORM para gestión de DODA (Documentos de Operación de Aduana)
"""

from datetime import datetime
from typing import Optional

from api.v1.common.base_models import TenantScopedMixin, TimestampMixin
from core.database import Base
from sqlalchemy import (
    ForeignKeyConstraint,
    DateTime,
    Integer,
    PrimaryKeyConstraint,
    String,
    Text,
    Numeric,
    Boolean,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship


class Doda(Base, TenantScopedMixin, TimestampMixin):
    """
    Modelo para la tabla Doda - Documentos de Operación de Aduana
    """

    __tablename__ = "doda"  # gDODA
    __table_args__ = (
        PrimaryKeyConstraint("id", name="doda_pkey"),
        {"schema": "a76"},
    )

    # Primary key
    id: Mapped[int] = mapped_column(
        Integer, primary_key=True, autoincrement=True)

    # Integration and timestamps
    integration_number: Mapped[Optional[str]] = mapped_column(String(30))
    doda_date: Mapped[Optional[int]] = mapped_column(Integer)
    doda_time: Mapped[Optional[int]] = mapped_column(Integer)

    # Customs information
    dispatch_customs: Mapped[Optional[str]] = mapped_column(String(3))
    customs_sections: Mapped[Optional[str]] = mapped_column(String(3))
    patent: Mapped[Optional[str]] = mapped_column(String(4))
    pedimentos: Mapped[Optional[str]] = mapped_column(String(80))
    caat: Mapped[Optional[str]] = mapped_column(String(10))

    # Transport and identifiers
    transport_identification: Mapped[Optional[str]] = mapped_column(String(20))
    fast_id: Mapped[Optional[str]] = mapped_column(String(20))
    operation_type: Mapped[Optional[str]] = mapped_column(String(1))

    # Selection info
    selected: Mapped[Optional[bool]] = mapped_column(Boolean)
    user_selected: Mapped[Optional[str]] = mapped_column(String(30))
    last_user: Mapped[Optional[str]] = mapped_column(String(30))

    # Responsible parties
    responsible: Mapped[Optional[str]] = mapped_column(String(14))
    carrier: Mapped[Optional[str]] = mapped_column(String(8))
    shipments: Mapped[Optional[str]] = mapped_column(String(80))
    pedimento_type: Mapped[Optional[str]] = mapped_column(String(30))

    # Digital signatures and certificates
    original_chain: Mapped[Optional[str]] = mapped_column(String(5000))
    serial_number: Mapped[Optional[str]] = mapped_column(String(21))
    electronic_signature: Mapped[Optional[str]] = mapped_column(String(2000))

    # Transaction info
    transaction_number: Mapped[Optional[str]] = mapped_column(String(30))
    status: Mapped[Optional[str]] = mapped_column(String(30))

    # SAT information
    linq_sat_qr: Mapped[Optional[str]] = mapped_column(String(1000))
    sat_certificate: Mapped[Optional[str]] = mapped_column(String(2001))
    sat_digital_seal: Mapped[Optional[Text]] = mapped_column(Text)

    # XML Paths
    xml_doda_sent_path: Mapped[Optional[str]] = mapped_column(String(1000))
    xml_doda_response_path: Mapped[Optional[str]] = mapped_column(String(1000))

    # Reporte PDF (S3) + caché por huella de contenido
    doda_report_pdf_path: Mapped[Optional[str]] = mapped_column(String(1000))
    doda_report_pdf_generated_at: Mapped[Optional[datetime]] = mapped_column(DateTime)
    doda_report_source_fingerprint: Mapped[Optional[str]] = mapped_column(String(64))

    # SAT original chain
    sat_original_chain: Mapped[Optional[Text]] = mapped_column(Text)

    # Customs clearance
    customs_clearance: Mapped[Optional[int]] = mapped_column(Integer)
    unique_badge_number: Mapped[Optional[str]] = mapped_column(String(250))

    # Relationships
    containers: Mapped[list["DodaContainer"]] = relationship(
        "DodaContainer", back_populates="doda", cascade="all, delete-orphan"
    )
    american_pedimentos: Mapped[list["DodaAmericanPedimento"]] = relationship(
        "DodaAmericanPedimento", back_populates="doda", cascade="all, delete-orphan"
    )
    pedimentos_detail: Mapped[list["DodaPedimento"]] = relationship(
        "DodaPedimento", back_populates="doda", cascade="all, delete-orphan"
    )

    def __repr__(self):
        return f"<Doda(id={self.id}, integration_number={self.integration_number}, status={self.status})>"


class DodaContainer(Base, TenantScopedMixin, TimestampMixin):
    """
    Modelo para la tabla DodaContainer - Contenedores en DODA
    """

    __tablename__ = "doda_containers"  # gDoda_Contenedores
    __table_args__ = (
        PrimaryKeyConstraint("id", name="doda_containers_pkey"),
        ForeignKeyConstraint(
            ["doda_id"], ["a76.doda.id"], name="fk_doda_containers_doda"
        ),
        {"schema": "a76"},
    )

    # Primary key
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)

    # Foreign key and line number
    doda_id: Mapped[int] = mapped_column(Integer, nullable=False)
    container_line: Mapped[int] = mapped_column(Integer, nullable=False)

    # Container information
    container_value: Mapped[Optional[str]] = mapped_column(String(20))
    seals: Mapped[Optional[str]] = mapped_column(String(254))

    # Relationships
    doda: Mapped["Doda"] = relationship("Doda", back_populates="containers")
    seals_detail: Mapped[list["DodaContainerSeal"]] = relationship(
        "DodaContainerSeal", back_populates="container", cascade="all, delete-orphan"
    )

    def __repr__(self):
        return f"<DodaContainer(id={self.id}, doda_id={self.doda_id}, container_line={self.container_line})>"


class DodaContainerSeal(Base, TenantScopedMixin, TimestampMixin):
    """
    Modelo para la tabla DodaContainerSeal - Candados de Contenedores
    """

    __tablename__ = "doda_container_seals"  # gDoda_Contenedores_Candados
    __table_args__ = (
        PrimaryKeyConstraint("id", name="doda_container_seals_pkey"),
        ForeignKeyConstraint(
            ["container_id"], ["a76.doda_containers.id"], name="fk_doda_container_seals_container"
        ),
        {"schema": "a76"},
    )

   
    id: Mapped[int] = mapped_column(
        Integer, primary_key=True, autoincrement=True)


    container_id: Mapped[int] = mapped_column(Integer, nullable=False)
    doda_id: Mapped[int] = mapped_column(Integer, nullable=False)
    seal_line: Mapped[int] = mapped_column(Integer, nullable=False)

    
    seal_value: Mapped[Optional[str]] = mapped_column(String(21))

    
    container: Mapped["DodaContainer"] = relationship(
        "DodaContainer", back_populates="seals_detail"
    )

    def __repr__(self):
        return f"<DodaContainerSeal(id={self.id}, doda_id={self.doda_id}, seal_line={self.seal_line})>"


class DodaAmericanPedimento(Base, TenantScopedMixin, TimestampMixin):
    """
    Modelo para la tabla DodaAmericanPedimento - Pedimentos Americanos en DODA
    """

    __tablename__ = "doda_american_pedimentos"  # gDoda_PedimentoAmericano
    __table_args__ = (
        PrimaryKeyConstraint("id", name="doda_american_pedimentos_pkey"),
        ForeignKeyConstraint(
            ["doda_id"], ["a76.doda.id"], name="fk_doda_american_pedimentos_doda"
        ),
        {"schema": "a76"},
    )

    # Primary key
    id: Mapped[int] = mapped_column(
        Integer, primary_key=True, autoincrement=True)

    # Foreign key and line number
    doda_id: Mapped[int] = mapped_column(Integer, nullable=False)
    american_pedimento_line: Mapped[int] = mapped_column(
        Integer, nullable=False)

    # American pedimento information
    american_pedimento_type: Mapped[Optional[str]] = mapped_column(String(2))
    american_pedimento_value: Mapped[Optional[str]] = mapped_column(String(20))

    # Relationships
    doda: Mapped["Doda"] = relationship(
        "Doda", back_populates="american_pedimentos")

    def __repr__(self):
        return f"<DodaAmericanPedimento(id={self.id}, doda_id={self.doda_id}, american_pedimento_line={self.american_pedimento_line})>"


class DodaPedimento(Base, TenantScopedMixin, TimestampMixin):
    """
    Modelo para la tabla DodaPedimento - Pedimentos en DODA
    """

    __tablename__ = "doda_pedimentos"  # gDoda_Pedimentos
    __table_args__ = (
        PrimaryKeyConstraint("id", name="doda_pedimentos_pkey"),
        ForeignKeyConstraint(
            ["doda_id"], ["a76.doda.id"], name="fk_doda_pedimentos_doda"
        ),
        {"schema": "a76"},
    )

    # Primary key
    id: Mapped[int] = mapped_column(
        Integer, primary_key=True, autoincrement=True)

    # Foreign key and line number
    doda_id: Mapped[int] = mapped_column(Integer, nullable=False)
    pedimento_line: Mapped[int] = mapped_column(Integer, nullable=False)

    # Authorization and document info
    authorization_patent: Mapped[Optional[str]] = mapped_column(String(10))
    document: Mapped[Optional[str]] = mapped_column(String(50))
    shipment: Mapped[Optional[str]] = mapped_column(String(11))

    # Commercial information
    cove: Mapped[Optional[str]] = mapped_column(String(50))
    umc: Mapped[Optional[str]] = mapped_column(String(20))

    # Financial information
    effective_amount_usd: Mapped[Optional[Numeric]
                                 ] = mapped_column(Numeric(15, 2))
    difference_amount_usd: Mapped[Optional[Numeric]
                                  ] = mapped_column(Numeric(15, 2))

    # Additional identifiers
    dta_niu: Mapped[Optional[str]] = mapped_column(String(20))
    article_7: Mapped[Optional[bool]] = mapped_column(Boolean)
    pedimento_id: Mapped[Optional[int]] = mapped_column(Integer)
    invoice_line: Mapped[Optional[int]] = mapped_column(Integer)
    part_ii_line: Mapped[Optional[int]] = mapped_column(Integer)

    # Type and validation
    pedimento_type: Mapped[Optional[str]] = mapped_column(String(20))
    zero_packaging_validation: Mapped[Optional[bool]] = mapped_column(Boolean)

    # Relationships
    doda: Mapped["Doda"] = relationship(
        "Doda", back_populates="pedimentos_detail")

    def __repr__(self):
        return f"<DodaPedimento(id={self.id}, doda_id={self.doda_id}, pedimento_line={self.pedimento_line})>"
