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


class EquivalencyItem(Base, TenantScopedMixin, TimestampMixin):
    """Pool global de mapeos campo_original → campo_externo.
    Un mismo item puede ser referenciado por múltiples Equivalency."""

    __tablename__ = "equivalency_items"
    __table_args__ = (
        UniqueConstraint(
            "original_field", "external_field", "tenant_id", "company_id",
            name="uq_equivalency_item_fields"
        ),
        {"schema": "a76"}
    )

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

    original_field: Mapped[str] = mapped_column(String(100), nullable=False)
    external_field: Mapped[str] = mapped_column(String(100), nullable=False)
    conversion_factor: Mapped[Optional[Decimal]] = mapped_column(
        Numeric(13, 6), nullable=True)

    equivalencies: Mapped[List["Equivalency"]] = relationship(
        back_populates="item", foreign_keys="[Equivalency.item_id]")


class Equivalency(Base, TenantScopedMixin, TimestampMixin):
    """Catálogo de equivalencias. Cada registro selecciona UN item del pool."""

    __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)

    item_id: Mapped[Optional[int]] = mapped_column(
        Integer,
        ForeignKey("a76.equivalency_items.id", ondelete="SET NULL"),
        nullable=True)

    item: Mapped[Optional["EquivalencyItem"]] = relationship(
        back_populates="equivalencies", foreign_keys=[item_id])
