from typing import Optional, List
from sqlalchemy import Integer, String, ForeignKey, UniqueConstraint, ForeignKeyConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship
from api.v1.common.base_models import TenantScopedMixin, TimestampMixin
from core.database import Base
from api.v1.modules.a76.general_catalogs.units_of_measure.models import UnitOfMeasure


class Equivalency(Base, TenantScopedMixin, TimestampMixin):
    __tablename__ = "equivalencies"
    __table_args__ = (
        UniqueConstraint("identifier", "tenant_id", "company_id",
                         name="uq_equivalency_identifier"),
        {"schema": "a76"}
    )

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

    identifier: Mapped[str] = mapped_column(String(10), nullable=False)
    
    description: Mapped[Optional[str]] = mapped_column(
        String(200), nullable=True)

    items: Mapped[List["EquivalencyItem"]] = relationship(
        back_populates="equivalency", cascade="all, delete-orphan")


class EquivalencyItem(Base, TenantScopedMixin, TimestampMixin):
    __tablename__ = "equivalency_items"
    __table_args__ = (
        UniqueConstraint("equivalency_id", "original_field",
                         "external_field", "tenant_id", "company_id", name="uq_equivalency_item_fields"),
        ForeignKeyConstraint(
            ["original_field", "tenant_id", "company_id"],
            ["a76.units_of_measure.code", "a76.units_of_measure.tenant_id",
                "a76.units_of_measure.company_id"]
        ),
        {"schema": "a76"}
    )

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

    equivalency_id: Mapped[int] = mapped_column(
        Integer, ForeignKey("a76.equivalencies.id"), nullable=False)

    original_field: Mapped[str] = mapped_column(
        String(100), nullable=False)  # Relation to Unit of Measure

    external_field: Mapped[str] = mapped_column(String(100), nullable=False)

    equivalency: Mapped["Equivalency"] = relationship(back_populates="items")
    
    unit_of_measure: Mapped["UnitOfMeasure"] = relationship()
