from typing import Optional
from decimal import Decimal
from sqlalchemy import 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
from api.v1.modules.a76.general_catalogs.units_of_measure.models import UnitOfMeasure


class UnitConversion(Base, TenantScopedMixin, TimestampMixin):
    __tablename__ = "unit_conversions"
    __table_args__ = (
        UniqueConstraint("from_unit_code", "to_unit_code", "tenant_id", "company_id",
                         name="uq_unit_conversion_pair"),
        ForeignKeyConstraint(
            ["from_unit_code", "tenant_id", "company_id"],
            ["a76.units_of_measure.code", "a76.units_of_measure.tenant_id",
                "a76.units_of_measure.company_id"],
        ),
        ForeignKeyConstraint(
            ["to_unit_code", "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)

    from_unit_code: Mapped[str] = mapped_column(String(5), nullable=False)

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

    # Relationships
    # Note: Complex composite foreign keys might require explicit primaryjoin if used
