from datetime import datetime, timezone

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

from src.core.models.base import Base


class Region(Base):
    __tablename__ = "regions"

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    region_code = Column(String(255), nullable=False)
    country_id = Column(Integer, ForeignKey("countries.id", ondelete="RESTRICT"), nullable=False, 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)
    deleted_at = Column(DateTime, nullable=True)

    # Relationships
    country = relationship("Country", back_populates="regions")
    locations = relationship("Location", back_populates="region", cascade="all, delete-orphan", passive_deletes=True)
    sub_regions = relationship("SubRegion", back_populates="region", cascade="all, delete-orphan", passive_deletes=True)
    appellations = relationship("Appellation", back_populates="region")
    sub_appellations = relationship("SubAppellation", back_populates="region")

    __table_args__ = (
        UniqueConstraint("country_id", "name", "deleted_at", name="uq_region_country_name_deleted"),
        UniqueConstraint("country_id", "region_code", "deleted_at", name="uq_region_country_code_deleted"),
        Index("ix_regions_name", "name"),
        Index("ix_regions_region_code", "region_code"),
    )


class SubRegion(Base):
    """
    Represents a 'sub-region' concept distinct from `Location`.
    If `Location` is your canonical subregion (preferred), keep this only if you
    truly need both. Otherwise consider migrating usages to `Location`.
    """
    __tablename__ = "sub_regions"

    id = Column(Integer, primary_key=True)
    name = Column(String(255), nullable=False)
    region_id = Column(Integer, ForeignKey("regions.id", ondelete="CASCADE"), nullable=False, 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)
    deleted_at = Column(DateTime, nullable=True)

    region = relationship("Region", back_populates="sub_regions")

    __table_args__ = (
        UniqueConstraint("region_id", "name", "deleted_at", name="uq_sub_region_region_name_deleted"),
        Index("ix_sub_regions_name", "name"),
    )