from typing import Optional, List, Dict, Any
from datetime import date
from sqlalchemy import Integer, String, Date, PrimaryKeyConstraint, JSON
from sqlalchemy.orm import Mapped, mapped_column
from core.database import Base


class AphisCatalog(Base):
    """
    Catálogo global de registros APHIS por empresa.
    Soporta las 7 pestañas de información (General + 6 detalles via JSON).
    """
    __tablename__ = "inv_aphis_catalog"
    __table_args__ = (
        PrimaryKeyConstraint("id", name="inv_aphis_catalog_pkey"),
        {"schema": "a24", "extend_existing": True},
    )

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

    # --- Pestaña 1: General (Campos principales) ---
    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))

    # --- Pestañas 2-7: Detalles (Almacenados como JSON por flexibilidad) ---
    characteristics: Mapped[Optional[List[Dict[str, Any]]]] = mapped_column(JSON, default=list)
    pitems: Mapped[Optional[List[Dict[str, Any]]]] = mapped_column(JSON, default=list)
    lpcos: Mapped[Optional[List[Dict[str, Any]]]] = mapped_column(JSON, default=list)
    entities: Mapped[Optional[List[Dict[str, Any]]]] = mapped_column(JSON, default=list)
    containers: Mapped[Optional[List[Dict[str, Any]]]] = mapped_column(JSON, default=list)
    routing: Mapped[Optional[List[Dict[str, Any]]]] = mapped_column(JSON, default=list)
