from typing import TYPE_CHECKING, Optional, List
from datetime import date
from sqlalchemy import (
    Integer, String, Date, Numeric, Boolean, 
    PrimaryKeyConstraint, ForeignKeyConstraint
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from api.v1.common.base_models import TenantScopedMixin, TimestampMixin
from core.database import Base

if TYPE_CHECKING:
    from api.v1.modules.a24.inv.inv_parts.models import InvPart

class InvPartAphisGeneral(Base, TenantScopedMixin, TimestampMixin):
    """
    Tabla principal de Aphis vinculada a la parte.
    Pestaña: Información general de aphis
    """
    __tablename__ = "inv_aphis_general"
    __table_args__ = (
        PrimaryKeyConstraint("id", name="inv_aphis_general_pkey"),
        ForeignKeyConstraint(
            ["inv_part_id"], ["a24.inv_partes.id"], name="fk_aphis_general_inv_part"
        ),
        {"schema": "a24", "extend_existing": True},
    )

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

    # --- 31 CAMPOS INICIALES ---
    program_code: Mapped[Optional[str]] = mapped_column(String(10))
    processing_code: Mapped[Optional[str]] = mapped_column(String(10))
    aphis_type: Mapped[Optional[str]] = mapped_column(String(10))
    disclaimer: Mapped[Optional[str]] = mapped_column(String(10))
    electronic_image: Mapped[Optional[str]] = mapped_column(String(50))
    confidential: Mapped[Optional[str]] = mapped_column(String(1))
    global_product_id: Mapped[Optional[str]] = mapped_column(String(100))
    intended_use_code: Mapped[Optional[str]] = mapped_column(String(10))
    intended_use_description: Mapped[Optional[str]] = mapped_column(String(200))
    item_type: Mapped[Optional[str]] = mapped_column(String(20))
    product_code: Mapped[Optional[str]] = mapped_column(String(20))
    product_code_2: Mapped[Optional[str]] = mapped_column(String(20))
    product_code_3: Mapped[Optional[str]] = mapped_column(String(20))
    scientific_genus_name: Mapped[Optional[str]] = mapped_column(String(100))
    scientific_species_name: Mapped[Optional[str]] = mapped_column(String(100))
    scientific_sub_species_name: Mapped[Optional[str]] = mapped_column(String(100))
    common_name_specific: Mapped[Optional[str]] = mapped_column(String(200))
    common_name_general: Mapped[Optional[str]] = mapped_column(String(200))
    signed_doc: Mapped[Optional[str]] = mapped_column(String(100))
    signed_doc_date: Mapped[Optional[date]] = mapped_column(Date)
    signed_doc_id: Mapped[Optional[str]] = mapped_column(String(50))
    invoice_number: Mapped[Optional[str]] = mapped_column(String(50))
    quantity_1: Mapped[Optional[str]] = mapped_column(String(50))
    quantity_2: Mapped[Optional[str]] = mapped_column(String(50))
    quantity_3: Mapped[Optional[str]] = mapped_column(String(50))
    inspection: Mapped[Optional[str]] = mapped_column(String(200))
    inspection_date: Mapped[Optional[date]] = mapped_column(Date)
    inspection_loc_date: Mapped[Optional[date]] = mapped_column(Date)
    inspection_location: Mapped[Optional[str]] = mapped_column(String(200))
    country_production: Mapped[Optional[str]] = mapped_column(String(3))
    country_source: Mapped[Optional[str]] = mapped_column(String(3))

    # Relaciones
    inv_part: Mapped["InvPart"] = relationship("InvPart", back_populates="aphis_records")
    
    characteristics: Mapped[List["InvPartAphisCharacteristic"]] = relationship(back_populates="aphis_general", cascade="all, delete-orphan")
    stype_pitems: Mapped[List["InvPartAphisStypePitems"]] = relationship(back_populates="aphis_general", cascade="all, delete-orphan")
    lpcos: Mapped[List["InvPartAphisLpcos"]] = relationship(back_populates="aphis_general", cascade="all, delete-orphan")
    entities: Mapped[List["InvPartAphisEntities"]] = relationship(back_populates="aphis_general", cascade="all, delete-orphan")
    containers: Mapped[List["InvPartAphisContainers"]] = relationship(back_populates="aphis_general", cascade="all, delete-orphan")
    routing: Mapped[List["InvPartAphisRouting"]] = relationship(back_populates="aphis_general", cascade="all, delete-orphan")
