from sqlalchemy import Column, String, Integer, Boolean, Text, Date, Float, Time, ForeignKey, UniqueConstraint, Index, CheckConstraint, Enum as SqlEnum
from sqlalchemy.orm import relationship
from datetime import datetime, date, time
import uuid
from utils.utils import Base


class Category(Base):
    __tablename__ = "categories"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = Column(String(255), nullable=False, unique=True, index=True)
    slug = Column(String(255), nullable=False, unique=True, index=True)
    description = Column(Text, nullable=True)
    icon_url = Column(String(500), nullable=True)
    is_active = Column(Boolean, nullable=False, default=True)
    display_order = Column(Integer, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    # Relationships
    tour_packages = relationship("Tourpackage", back_populates="category")


class Destination(Base):
    __tablename__ = "destinations"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = Column(String(255), nullable=False)
    slug = Column(String(255), nullable=False, unique=True, index=True)
    country = Column(String(100), nullable=False, index=True)
    state = Column(String(100), nullable=True)
    city = Column(String(100), nullable=True)
    description = Column(Text, nullable=True)
    attractions = Column(Text, nullable=True)
    best_time_to_visit = Column(String(255), nullable=True)
    climate_info = Column(Text, nullable=True)
    latitude = Column(Float, nullable=True)
    longitude = Column(Float, nullable=True)
    image_url = Column(String(500), nullable=True)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    # Relationships
    tour_package_destinations = relationship("Tourpackagedestination", back_populates="destination")


class Season(Base):
    __tablename__ = "seasons"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = Column(String(255), nullable=False)
    season_type = Column(String(50), nullable=False, index=True)
    start_date = Column(Date, nullable=False)
    end_date = Column(Date, nullable=False)
    price_multiplier = Column(Float, nullable=False)
    description = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    __table_args__ = (
        CheckConstraint("price_multiplier >= 0", name="ck_season_price_multiplier_nonneg"),
        Index("ix_seasons_dates", "start_date", "end_date"),
    )


class Amenity(Base):
    __tablename__ = "amenities"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = Column(String(255), nullable=False, unique=True, index=True)
    description = Column(Text, nullable=True)
    icon_url = Column(String(500), nullable=True)
    amenity_type = Column(String(50), nullable=False, index=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    # Relationships
    tour_package_amenities = relationship("Tourpackageamenity", back_populates="amenity")
    hotel_amenities = relationship("Hotelamenity", back_populates="amenity")


class Tourpackage(Base):
    __tablename__ = "tour_packages"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    category_id = Column(String(36), ForeignKey("categories.id", ondelete="RESTRICT"), nullable=False, index=True)
    name = Column(String(255), nullable=False)
    slug = Column(String(255), nullable=False, unique=True, index=True)
    description = Column(Text, nullable=True)
    duration_days = Column(Integer, nullable=False)
    duration_nights = Column(Integer, nullable=False)
    difficulty_level = Column(String(50), nullable=True)
    min_age = Column(Integer, nullable=True)
    max_age = Column(Integer, nullable=True)
    min_group_size = Column(Integer, nullable=False)
    max_group_size = Column(Integer, nullable=False)
    base_price = Column(Float, nullable=False)
    child_price = Column(Float, nullable=True)
    single_supplement = Column(Float, nullable=True)
    inclusions = Column(Text, nullable=True)
    exclusions = Column(Text, nullable=True)
    terms_and_conditions = Column(Text, nullable=True)
    cancellation_policy = Column(Text, nullable=True)
    is_active = Column(Boolean, nullable=False, default=True)
    is_featured = Column(Boolean, nullable=False, default=False)
    rating = Column(Float, nullable=True)
    total_reviews = Column(Integer, nullable=False, default=0)
    image_url = Column(String(500), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    __table_args__ = (
        CheckConstraint("duration_days >= 0", name="ck_tourpackage_duration_days_nonneg"),
        CheckConstraint("duration_nights >= 0", name="ck_tourpackage_duration_nights_nonneg"),
        CheckConstraint("min_group_size >= 0", name="ck_tourpackage_min_group_size_nonneg"),
        CheckConstraint("max_group_size >= min_group_size", name="ck_tourpackage_max_group_size_gte_min"),
        CheckConstraint("base_price >= 0", name="ck_tourpackage_base_price_nonneg"),
        CheckConstraint("rating IS NULL OR (rating >= 0 AND rating <= 5)", name="ck_tourpackage_rating_range"),
        CheckConstraint("total_reviews >= 0", name="ck_tourpackage_total_reviews_nonneg"),
        Index("ix_tour_packages_active_featured", "is_active", "is_featured"),
    )

    # Relationships
    category = relationship("Category", back_populates="tour_packages")
    itineraries = relationship("Itinerary", back_populates="tour_package", cascade="all, delete-orphan", passive_deletes=True)
    tour_package_destinations = relationship("Tourpackagedestination", back_populates="tour_package", cascade="all, delete-orphan", passive_deletes=True)
    tour_package_amenities = relationship("Tourpackageamenity", back_populates="tour_package", cascade="all, delete-orphan", passive_deletes=True)
    tour_schedules = relationship("Tourschedule", back_populates="tour_package")
    reviews = relationship("Review", back_populates="tour_package")


class Tourpackagedestination(Base):
    __tablename__ = "tour_package_destinations"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    tour_package_id = Column(String(36), ForeignKey("tour_packages.id", ondelete="CASCADE"), nullable=False, index=True)
    destination_id = Column(String(36), ForeignKey("destinations.id", ondelete="RESTRICT"), nullable=False, index=True)
    visit_order = Column(Integer, nullable=False)
    duration_hours = Column(Integer, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    __table_args__ = (
        UniqueConstraint("tour_package_id", "destination_id", name="uq_tour_package_destination"),
        CheckConstraint("visit_order >= 0", name="ck_tourpackagedestination_visit_order_nonneg"),
        CheckConstraint("duration_hours IS NULL OR duration_hours >= 0", name="ck_tourpackagedestination_duration_hours_nonneg"),
    )

    # Relationships
    tour_package = relationship("Tourpackage", back_populates="tour_package_destinations")
    destination = relationship("Destination", back_populates="tour_package_destinations")


class Tourpackageamenity(Base):
    __tablename__ = "tour_package_amenities"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    tour_package_id = Column(String(36), ForeignKey("tour_packages.id", ondelete="CASCADE"), nullable=False, index=True)
    amenity_id = Column(String(36), ForeignKey("amenities.id", ondelete="RESTRICT"), nullable=False, index=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    __table_args__ = (
        UniqueConstraint("tour_package_id", "amenity_id", name="uq_tour_package_amenity"),
    )

    # Relationships
    tour_package = relationship("Tourpackage", back_populates="tour_package_amenities")
    amenity = relationship("Amenity", back_populates="tour_package_amenities")


class Itinerary(Base):
    __tablename__ = "itineraries"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    tour_package_id = Column(String(36), ForeignKey("tour_packages.id", ondelete="CASCADE"), nullable=False, index=True)
    day_number = Column(Integer, nullable=False)
    title = Column(String(255), nullable=False)
    description = Column(Text, nullable=True)
    activities = Column(Text, nullable=True)
    meals_included = Column(String(255), nullable=True)
    accommodation_type = Column(String(100), nullable=True)
    start_time = Column(Time, nullable=True)
    end_time = Column(Time, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    __table_args__ = (
        UniqueConstraint("tour_package_id", "day_number", name="uq_itinerary_tour_package_day"),
        CheckConstraint("day_number >= 1", name="ck_itinerary_day_number_positive"),
    )

    # Relationships
    tour_package = relationship("Tourpackage", back_populates="itineraries")