from typing import Optional
from decimal import Decimal
from sqlalchemy import (
    ForeignKey,
    Integer,
    String,
    ForeignKeyConstraint,
    UniqueConstraint,
    Numeric,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from api.v1.common.base_models import TenantScopedMixin, TimestampMixin
from core.database import Base

# Import models referenced by TenantScopedMixin (Tenant, Company) to ensure they are loaded
from api.v1.modules.core.tenants.models import Tenant
from api.v1.modules.a76.general_catalogs.company.models import Company

# 1. GUniMedACE
class UnitOfMeasureACE(Base, TimestampMixin):
    __tablename__ = "unit_of_measure_ace"
    __table_args__ = (
        UniqueConstraint("code", name="uq_uom_ace_code"),
        {"schema": "a76", "extend_existing": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(4), nullable=False)  # CLAVEACE
    description: Mapped[Optional[str]] = mapped_column(String(49), nullable=True)


# 2. GUMOMA
class UnitOfMeasureOMA(Base, TimestampMixin):
    __tablename__ = "unit_of_measure_oma"
    __table_args__ = (
        UniqueConstraint("code", name="uq_uom_oma_code"),
        {"schema": "a76", "extend_existing": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(10), nullable=False)  # CLAVEUM
    description: Mapped[Optional[str]] = mapped_column(String(200), nullable=True)


# 3. GUMAme
class UnitOfMeasureAmerican(Base, TimestampMixin):
    __tablename__ = "unit_of_measure_american"
    __table_args__ = (
        UniqueConstraint("code", name="uq_uom_american_code"),
        {"schema": "a76", "extend_existing": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(3), nullable=False)  # CLAVE
    description: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)


# 4. GUMAduana
class UnitOfMeasureCustoms(Base, TimestampMixin):
    __tablename__ = "unit_of_measure_customs"
    __table_args__ = (
        UniqueConstraint("code", name="uq_uom_customs_code"),
        {"schema": "a76", "extend_existing": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(10), nullable=False)  # CLAVE
    description: Mapped[Optional[str]] = mapped_column(String(20), nullable=True)
    a76_unit_code: Mapped[Optional[str]] = mapped_column(String(5), nullable=True)  # UnidadSCAII


# 5. GUniMedida (Main)
class UnitOfMeasure(Base, TenantScopedMixin, TimestampMixin):
    __tablename__ = "units_of_measure"
    __table_args__ = (
        UniqueConstraint("code", "tenant_id", "company_id", name="uq_uom_code"),        
        {"schema": "a76", "extend_existing": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(5), nullable=False)  # CLAVEUNI
    description: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    description_en: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)

    customs_code: Mapped[Optional[str]] = mapped_column(ForeignKey("a76.unit_of_measure_customs.code"), nullable=True)  # CLAVE_AMEX
    american_code: Mapped[Optional[str]] = mapped_column(ForeignKey("a76.unit_of_measure_american.code"), nullable=True)  # CLAVE_AAMER
    ace_code: Mapped[Optional[str]] = mapped_column(ForeignKey("a76.unit_of_measure_ace.code"), nullable=True)  # CLAVEACE
    oma_code: Mapped[Optional[str]] = mapped_column(ForeignKey("a76.unit_of_measure_oma.code"), nullable=True)  # CLAVEOMA

    # Relationships omitted for simplicity or need explicit primaryjoin
    customs_unit: Mapped[Optional["UnitOfMeasureCustoms"]] = relationship()
    american_unit: Mapped[Optional["UnitOfMeasureAmerican"]] = relationship(overlaps="customs_unit")
    ace_unit: Mapped[Optional["UnitOfMeasureACE"]] = relationship(overlaps="american_unit,customs_unit")
    oma_unit: Mapped[Optional["UnitOfMeasureOMA"]] = relationship(overlaps="ace_unit,american_unit,customs_unit")


# 6. GUniMed (General/Conversion)
class UnitOfMeasureGeneral(Base, TenantScopedMixin, TimestampMixin):
    __tablename__ = "units_of_measure_general"
    __table_args__ = (
        UniqueConstraint("code", "tenant_id", "company_id", name="uq_uom_general_code"),
        ForeignKeyConstraint(
            ["customs_code"],
            ["a76.unit_of_measure_customs.code"],
            use_alter=True,
            name="fk_uom_general_customs",
        ),
        ForeignKeyConstraint(
            ["ace_code"],
            ["a76.unit_of_measure_ace.code"],
            use_alter=True,
            name="fk_uom_general_ace",
        ),
        {"schema": "a76", "extend_existing": True},
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    code: Mapped[str] = mapped_column(String(5), nullable=False)  # UNIDAD
    description: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    conversion_factor: Mapped[Optional[Decimal]] = mapped_column(
        Numeric(13, 6), nullable=True
    )
    mexico_unit: Mapped[Optional[str]] = mapped_column(String(5), nullable=True)
    # UNIDAD_AME (Note: GUniMed has UNIDAD_AME varchar(5), but GUMAme has CLAVE varchar(3). Keeping as string for now)
    american_unit_code: Mapped[Optional[str]] = mapped_column(ForeignKey("a76.unit_of_measure_american.code"), nullable=True)

    customs_code: Mapped[Optional[str]] = mapped_column(ForeignKey("a76.unit_of_measure_customs.code"), nullable=True)  # CLAVE_ADUANA
    ace_code: Mapped[Optional[str]] = mapped_column(ForeignKey("a76.unit_of_measure_ace.code"), nullable=True)  # CLAVEACE

    customs_unit: Mapped[Optional["UnitOfMeasureCustoms"]] = relationship()
    ace_unit: Mapped[Optional["UnitOfMeasureACE"]] = relationship(
        overlaps="customs_unit"
    )
