from datetime import datetime, timezone

from sqlalchemy import (
    Column,
    DateTime,
    ForeignKey,
    Integer,
    String,
    UniqueConstraint,
    Index,
    CheckConstraint,
)
from sqlalchemy.orm import relationship

from src.core.models.base import Base


class WineAppellation(Base):
    """
    Tree index/lookup for the wine geography hierarchy.

    Exactly ONE of the level FKs below must be non-null, representing what this
    row points to:
        - country_id
        - region_id
        - sub_region_id
        - appellation_id
        - sub_appellation_id
    """
    __tablename__ = "wine_appellations"

    id = Column(Integer, primary_key=True)
    title = Column(String(255), nullable=False)

    country_id = Column(Integer, ForeignKey("countries.id", ondelete="RESTRICT"), nullable=True, index=True)
    region_id = Column(Integer, ForeignKey("regions.id", ondelete="RESTRICT"), nullable=True, index=True)
    sub_region_id = Column(Integer, ForeignKey("sub_regions.id", ondelete="RESTRICT"), nullable=True, index=True)
    appellation_id = Column(Integer, ForeignKey("appellations.id", ondelete="RESTRICT"), nullable=True, index=True)
    sub_appellation_id = Column(Integer, ForeignKey("sub_appellations.id", ondelete="RESTRICT"), nullable=True, index=True)

    created_at = Column(DateTime, default=datetime.now(timezone.utc), nullable=False)
    updated_at = Column(DateTime, default=datetime.now(timezone.utc), onupdate=datetime.now(timezone.utc), nullable=False)

    country = relationship("Country", lazy="joined")
    region = relationship("Region", lazy="joined")
    sub_region = relationship("SubRegion", lazy="joined")
    appellation = relationship("Appellation", lazy="joined")
    sub_appellation = relationship("SubAppellation", lazy="joined")

    __table_args__ = (
        CheckConstraint(
            "(country_id IS NOT NULL)::int + "
            "(region_id IS NOT NULL)::int + "
            "(sub_region_id IS NOT NULL)::int + "
            "(appellation_id IS NOT NULL)::int + "
            "(sub_appellation_id IS NOT NULL)::int = 1",
            name="ck_wine_appellations_exactly_one_level",
        ),
        # Optional: avoid duplicate titles at same level
        UniqueConstraint("title", "country_id", name="uq_wa_title_country"),
        UniqueConstraint("title", "region_id", name="uq_wa_title_region"),
        UniqueConstraint("title", "sub_region_id", name="uq_wa_title_sub_region"),
        UniqueConstraint("title", "appellation_id", name="uq_wa_title_appellation"),
        UniqueConstraint("title", "sub_appellation_id", name="uq_wa_title_sub_appellation"),
        # Helpful indexes
        Index("ix_wine_appellations_title", "title"),
        Index("ix_wine_appellations_level_picker",
              "country_id", "region_id", "sub_region_id", "appellation_id", "sub_appellation_id"),
    )