from datetime import datetime
from typing import Optional

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

from src.core.models.base import Base


class Appellation(Base):
    __tablename__ = "appellations"

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

    country_id = Column(Integer, ForeignKey("countries.id"), nullable=False, index=True)
    region_id = Column(Integer, ForeignKey("regions.id"), nullable=False, index=True)
    sub_region_id = Column(Integer, ForeignKey("sub_regions.id"), nullable=False, index=True)
    date_created = Column(DateTime, default=datetime.utcnow, nullable=False)
    last_updated = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)
    deleted_at = Column(DateTime, nullable=True)

    country = relationship("Country", back_populates="appellations")
    region = relationship("Region", back_populates="appellations")

    sub_appellations = relationship(
        "SubAppellation",
        back_populates="appellation",
        cascade="all, delete-orphan",
        passive_deletes=True,
    )

    wines = relationship("Wine", back_populates="appellation")

    __table_args__ = (
        UniqueConstraint(
            "country_id", "region_id", "sub_region_id", "name", "deleted_at",
            name="uq_appellation_scope_name_deleted",
        ),
        Index("ix_appellations_scope", "country_id", "region_id", "sub_region_id"),
        Index("ix_appellations_name", "name"),
    )


class SubAppellation(Base):
    """
    Represents the 'site' level in your Groovy code.
    Belongs to an Appellation.
    """
    __tablename__ = "sub_appellations"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), nullable=False)
    appellation_id = Column(Integer, ForeignKey("appellations.id"), nullable=False, index=True)

    country_id = Column(Integer, ForeignKey("countries.id"), nullable=False, index=True)
    region_id = Column(Integer, ForeignKey("regions.id"), nullable=False, index=True)
    sub_region_id = Column(Integer, ForeignKey("sub_regions.id"), nullable=False, index=True)
    date_created = Column(DateTime, default=datetime.utcnow, nullable=False)
    last_updated = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)
    deleted_at = Column(DateTime, nullable=True)

    appellation = relationship("Appellation", back_populates="sub_appellations")
    country = relationship("Country", back_populates="sub_appellations")
    region = relationship("Region", back_populates="sub_appellations")

    __table_args__ = (
        UniqueConstraint(
            "appellation_id", "name", "deleted_at",
            name="uq_sub_appellation_appellation_name_deleted",
        ),
        Index("ix_sub_appellations_lineage", "country_id", "region_id", "sub_region_id", "appellation_id"),
        Index("ix_sub_appellations_name", "name"),
    )