"""
Modelo para el catálogo de claves FDA
"""
from typing import Optional, List
from sqlalchemy import String, Integer, Numeric, Date, ForeignKey, UniqueConstraint, PrimaryKeyConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship
from core.database import Base
from api.v1.common.base_models import TenantScopedMixin, TimestampMixin


class FDACatalog(Base, TenantScopedMixin, TimestampMixin):
    """Catálogo de claves FDA"""
    __tablename__ = "fda_catalog"
    __table_args__ = (
        PrimaryKeyConstraint("id", name="fda_catalog_pkey"),
        UniqueConstraint('tenant_id', 'company_id', 'fda_key', name='idx_fda_catalog_unique'),
        {'schema': 'a76'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    fda_key: Mapped[str] = mapped_column(String(20), nullable=False, index=True)
    description: Mapped[str] = mapped_column(String(500), nullable=False, index=True)
    fda_code: Mapped[Optional[str]] = mapped_column(String(50))
    requirements: Mapped[Optional[str]] = mapped_column(String(500))
    manufacturer_number: Mapped[Optional[str]] = mapped_column(String(50))
    country_of_production: Mapped[Optional[str]] = mapped_column(String(100))
    storage_status: Mapped[Optional[str]] = mapped_column(String(100))
    warehouse_code: Mapped[Optional[str]] = mapped_column(String(20))
    call_atl: Mapped[Optional[str]] = mapped_column(String(20))

    # Relationships
    specifications: Mapped[Optional["FDASpecifications"]] = relationship("FDASpecifications", back_populates="fda_catalog", cascade="all, delete-orphan", uselist=False)
    constituent_elements: Mapped[List["FDAConstituentElements"]] = relationship("FDAConstituentElements", back_populates="fda_catalog", cascade="all, delete-orphan")
    affirmation_codes: Mapped[List["FDAAffirmationCodes"]] = relationship("FDAAffirmationCodes", back_populates="fda_catalog", cascade="all, delete-orphan")
    lot_productions: Mapped[List["FDALotProduction"]] = relationship("FDALotProduction", back_populates="fda_catalog", cascade="all, delete-orphan")


class FDASpecifications(Base, TenantScopedMixin, TimestampMixin):
    """FDA01 - Especificaciones del Producto FDA"""
    __tablename__ = "fda_specifications"
    __table_args__ = (
        PrimaryKeyConstraint("id", name="fda_specifications_pkey"),
        {'schema': 'a76'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    fda_catalog_id: Mapped[int] = mapped_column(ForeignKey("a76.fda_catalog.id", ondelete="CASCADE"), nullable=False, index=True)

    prod_code: Mapped[Optional[str]] = mapped_column(String(50))
    commodity_desc: Mapped[Optional[str]] = mapped_column(String(200))
    brand_name: Mapped[Optional[str]] = mapped_column(String(100))
    disclaimer: Mapped[Optional[str]] = mapped_column(String(100))
    pgm_code: Mapped[Optional[str]] = mapped_column(String(50))
    proc_code: Mapped[Optional[str]] = mapped_column(String(50))
    intnd_use_code: Mapped[Optional[str]] = mapped_column(String(50))
    intnd_use_desc: Mapped[Optional[str]] = mapped_column(String(200))
    temp_qual: Mapped[Optional[str]] = mapped_column(String(50))
    temp_type: Mapped[Optional[str]] = mapped_column(String(50))
    temp_degrees: Mapped[Optional[float]] = mapped_column(Numeric(10, 2))
    temp_negative: Mapped[Optional[float]] = mapped_column(Numeric(10, 2))
    temp_location: Mapped[Optional[str]] = mapped_column(String(100))
    
    quantity_1: Mapped[Optional[float]] = mapped_column(Numeric(15, 2))
    qty_uom_1: Mapped[Optional[str]] = mapped_column(String(20))
    quantity_2: Mapped[Optional[float]] = mapped_column(Numeric(15, 2))
    qty_uom_2: Mapped[Optional[str]] = mapped_column(String(20))
    quantity_3: Mapped[Optional[float]] = mapped_column(Numeric(15, 2))
    qty_uom_3: Mapped[Optional[str]] = mapped_column(String(20))

    ctry_prod: Mapped[Optional[str]] = mapped_column(String(50))
    ctry_source: Mapped[Optional[str]] = mapped_column(String(50))
    ctry_growth: Mapped[Optional[str]] = mapped_column(String(50))
    ctry_refusal: Mapped[Optional[str]] = mapped_column(String(50))
    ctry_shipping: Mapped[Optional[str]] = mapped_column(String(50))

    manuf_key: Mapped[Optional[str]] = mapped_column(String(50))
    shipper_key: Mapped[Optional[str]] = mapped_column(String(50))
    ult_cons_key: Mapped[Optional[str]] = mapped_column(String(50))
    fda_imp_key: Mapped[Optional[str]] = mapped_column(String(50))
    pn_subm_key: Mapped[Optional[str]] = mapped_column(String(50))
    consol_key: Mapped[Optional[str]] = mapped_column(String(50))
    producer_key: Mapped[Optional[str]] = mapped_column(String(50))
    owner_key: Mapped[Optional[str]] = mapped_column(String(50))
    deli_party_key: Mapped[Optional[str]] = mapped_column(String(50))
    grower_key: Mapped[Optional[str]] = mapped_column(String(50))
    dev_ini_imp_key: Mapped[Optional[str]] = mapped_column(String(50))
    
    lacf_cont_1: Mapped[Optional[str]] = mapped_column(String(100))
    lacf_cont_2: Mapped[Optional[str]] = mapped_column(String(100))
    lacf_cont_3: Mapped[Optional[str]] = mapped_column(String(100))
    pn_transmitter_key: Mapped[Optional[str]] = mapped_column(String(50))

    fda_catalog: Mapped["FDACatalog"] = relationship("FDACatalog", back_populates="specifications")


class FDAConstituentElements(Base, TenantScopedMixin, TimestampMixin):
    """FDA04 - Constituent Elements"""
    __tablename__ = "fda_constituent_elements"
    __table_args__ = (
        PrimaryKeyConstraint("id", name="fda_constituent_elements_pkey"),
        {'schema': 'a76'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    fda_catalog_id: Mapped[int] = mapped_column(ForeignKey("a76.fda_catalog.id", ondelete="CASCADE"), nullable=False, index=True)

    line: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    ele_name: Mapped[str] = mapped_column(String(200), nullable=False)
    ele_qty: Mapped[Optional[float]] = mapped_column(Numeric(15, 2))
    ele_qty_uom: Mapped[Optional[str]] = mapped_column(String(20))
    ele_pctg: Mapped[Optional[float]] = mapped_column(Numeric(15, 2))

    fda_catalog: Mapped["FDACatalog"] = relationship("FDACatalog", back_populates="constituent_elements")


class FDAAffirmationCodes(Base, TenantScopedMixin, TimestampMixin):
    """FDA23 - Affirmation of Compliance"""
    __tablename__ = "fda_affirmation_codes"
    __table_args__ = (
        PrimaryKeyConstraint("id", name="fda_affirmation_codes_pkey"),
        {'schema': 'a76'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    fda_catalog_id: Mapped[int] = mapped_column(ForeignKey("a76.fda_catalog.id", ondelete="CASCADE"), nullable=False, index=True)

    line: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    aoc_code: Mapped[str] = mapped_column(String(50), nullable=False)
    aoc_qual: Mapped[Optional[str]] = mapped_column(String(100))

    fda_catalog: Mapped["FDACatalog"] = relationship("FDACatalog", back_populates="affirmation_codes")


class FDALotProduction(Base, TenantScopedMixin, TimestampMixin):
    """FDA25 - Lot and Production Dates"""
    __tablename__ = "fda_lot_production"
    __table_args__ = (
        PrimaryKeyConstraint("id", name="fda_lot_production_pkey"),
        {'schema': 'a76'}
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    fda_catalog_id: Mapped[int] = mapped_column(ForeignKey("a76.fda_catalog.id", ondelete="CASCADE"), nullable=False, index=True)

    line: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    lot_number: Mapped[str] = mapped_column(String(100), nullable=False)
    production_start_date: Mapped[Optional[str]] = mapped_column(String(50))
    production_end_date: Mapped[Optional[str]] = mapped_column(String(50))

    fda_catalog: Mapped["FDACatalog"] = relationship("FDACatalog", back_populates="lot_productions")
