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


class Booking(Base):
    __tablename__ = "bookings"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    booking_number = Column(String(50), unique=True, nullable=False, index=True)
    customer_id = Column(String(36), ForeignKey("customers.id", ondelete="RESTRICT"), nullable=False, index=True)
    tour_schedule_id = Column(String(36), ForeignKey("tour_schedules.id", ondelete="RESTRICT"), nullable=False, index=True)
    booking_agent_id = Column(String(36), ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    discount_id = Column(String(36), ForeignKey("discounts.id", ondelete="SET NULL"), nullable=True, index=True)
    booking_date = Column(DateTime, nullable=False, default=datetime.utcnow)
    number_of_adults = Column(Integer, nullable=False, default=0)
    number_of_children = Column(Integer, nullable=False, default=0)
    number_of_infants = Column(Integer, nullable=False, default=0)
    subtotal = Column(Float, nullable=False, default=0.0)
    discount_amount = Column(Float, nullable=False, default=0.0)
    tax_amount = Column(Float, nullable=False, default=0.0)
    total_amount = Column(Float, nullable=False, default=0.0)
    amount_paid = Column(Float, nullable=False, default=0.0)
    amount_due = Column(Float, nullable=False, default=0.0)
    booking_status = Column(String(50), nullable=False, default="pending")
    payment_status = Column(String(50), nullable=False, default="unpaid")
    special_requests = Column(Text, nullable=True)
    cancellation_reason = Column(Text, nullable=True)
    cancelled_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    customer = relationship("Customer", back_populates="bookings")
    tour_schedule = relationship("Tourschedule", back_populates="bookings")
    booking_agent = relationship("User", foreign_keys=[booking_agent_id], back_populates="agent_bookings")
    discount = relationship("Discount", back_populates="bookings")
    travelers = relationship("Traveler", back_populates="booking", cascade="all, delete-orphan", passive_deletes=True)
    payments = relationship("Payment", back_populates="booking", cascade="all, delete-orphan", passive_deletes=True)
    invoice = relationship("Invoice", back_populates="booking", uselist=False, cascade="all, delete-orphan", passive_deletes=True)
    cancellation_request = relationship("Cancellationrequest", back_populates="booking", uselist=False, cascade="all, delete-orphan", passive_deletes=True)
    documents = relationship("Document", back_populates="booking", cascade="all, delete-orphan", passive_deletes=True)
    reviews = relationship("Review", back_populates="booking", cascade="all, delete-orphan", passive_deletes=True)
    commissions = relationship("Commission", back_populates="booking", cascade="all, delete-orphan", passive_deletes=True)

    __table_args__ = (
        Index("ix_bookings_status_date", "booking_status", "booking_date"),
        CheckConstraint("number_of_adults >= 0", name="ck_bookings_adults_nonneg"),
        CheckConstraint("number_of_children >= 0", name="ck_bookings_children_nonneg"),
        CheckConstraint("number_of_infants >= 0", name="ck_bookings_infants_nonneg"),
        CheckConstraint("subtotal >= 0", name="ck_bookings_subtotal_nonneg"),
        CheckConstraint("discount_amount >= 0", name="ck_bookings_discount_nonneg"),
        CheckConstraint("tax_amount >= 0", name="ck_bookings_tax_nonneg"),
        CheckConstraint("total_amount >= 0", name="ck_bookings_total_nonneg"),
        CheckConstraint("amount_paid >= 0", name="ck_bookings_paid_nonneg"),
        CheckConstraint("amount_due >= 0", name="ck_bookings_due_nonneg"),
    )


class Traveler(Base):
    __tablename__ = "travelers"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    booking_id = Column(String(36), ForeignKey("bookings.id", ondelete="CASCADE"), nullable=False, index=True)
    traveler_type = Column(String(50), nullable=False)
    first_name = Column(String(100), nullable=False)
    last_name = Column(String(100), nullable=False)
    date_of_birth = Column(Date, nullable=True)
    gender = Column(String(50), nullable=True)
    nationality = Column(String(100), nullable=True)
    passport_number = Column(String(50), nullable=True)
    passport_expiry = Column(Date, nullable=True)
    special_requirements = Column(Text, nullable=True)
    meal_preference = Column(String(100), nullable=True)
    is_primary = Column(Boolean, nullable=False, default=False)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    booking = relationship("Booking", back_populates="travelers")

    __table_args__ = (
        Index("ix_travelers_booking_primary", "booking_id", "is_primary"),
    )


class Tourschedule(Base):
    __tablename__ = "tour_schedules"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    tour_package_id = Column(String(36), ForeignKey("tour_packages.id", ondelete="RESTRICT"), nullable=False, index=True)
    tour_guide_id = Column(String(36), ForeignKey("tour_guides.id", ondelete="SET NULL"), nullable=True, index=True)
    departure_date = Column(Date, nullable=False)
    return_date = Column(Date, nullable=False)
    available_slots = Column(Integer, nullable=False, default=0)
    booked_slots = Column(Integer, nullable=False, default=0)
    status = Column(String(50), nullable=False, default="scheduled")
    meeting_point = Column(String(255), nullable=True)
    meeting_time = Column(Time, nullable=True)
    special_instructions = 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)

    tour_package = relationship("Tourpackage", back_populates="tour_schedules")
    tour_guide = relationship("Tourguide", back_populates="tour_schedules")
    tour_schedule_hotels = relationship("Tourschedulehotel", back_populates="tour_schedule", cascade="all, delete-orphan", passive_deletes=True)
    tour_schedule_transportations = relationship("Tourscheduletransportation", back_populates="tour_schedule", cascade="all, delete-orphan", passive_deletes=True)
    bookings = relationship("Booking", back_populates="tour_schedule", cascade="all, delete-orphan", passive_deletes=True)
    expenses = relationship("Expense", back_populates="tour_schedule", cascade="all, delete-orphan", passive_deletes=True)

    __table_args__ = (
        Index("ix_tour_schedules_status_departure", "status", "departure_date"),
        CheckConstraint("available_slots >= 0", name="ck_tour_schedules_available_nonneg"),
        CheckConstraint("booked_slots >= 0", name="ck_tour_schedules_booked_nonneg"),
        CheckConstraint("return_date >= departure_date", name="ck_tour_schedules_dates_valid"),
    )


class Tourschedulehotel(Base):
    __tablename__ = "tour_schedule_hotels"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    tour_schedule_id = Column(String(36), ForeignKey("tour_schedules.id", ondelete="CASCADE"), nullable=False, index=True)
    hotel_id = Column(String(36), ForeignKey("hotels.id", ondelete="RESTRICT"), nullable=False, index=True)
    room_type_id = Column(String(36), ForeignKey("room_types.id", ondelete="SET NULL"), nullable=True, index=True)
    check_in_date = Column(Date, nullable=False)
    check_out_date = Column(Date, nullable=False)
    rooms_booked = Column(Integer, nullable=False, default=0)
    total_cost = Column(Float, nullable=False, default=0.0)
    booking_status = Column(String(50), nullable=False, default="pending")
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    tour_schedule = relationship("Tourschedule", back_populates="tour_schedule_hotels")
    hotel = relationship("Hotel", back_populates="tour_schedule_hotels")
    room_type = relationship("Roomtype", back_populates="tour_schedule_hotels")

    __table_args__ = (
        Index("ix_tour_schedule_hotels_status", "booking_status"),
        CheckConstraint("rooms_booked >= 0", name="ck_tour_schedule_hotels_rooms_nonneg"),
        CheckConstraint("total_cost >= 0", name="ck_tour_schedule_hotels_cost_nonneg"),
        CheckConstraint("check_out_date >= check_in_date", name="ck_tour_schedule_hotels_dates_valid"),
    )


class Tourscheduletransportation(Base):
    __tablename__ = "tour_schedule_transportations"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    tour_schedule_id = Column(String(36), ForeignKey("tour_schedules.id", ondelete="CASCADE"), nullable=False, index=True)
    transportation_id = Column(String(36), ForeignKey("transportations.id", ondelete="RESTRICT"), nullable=False, index=True)
    departure_location = Column(String(255), nullable=False)
    arrival_location = Column(String(255), nullable=False)
    departure_datetime = Column(DateTime, nullable=False)
    arrival_datetime = Column(DateTime, nullable=False)
    seats_booked = Column(Integer, nullable=False, default=0)
    total_cost = Column(Float, nullable=False, default=0.0)
    booking_status = Column(String(50), nullable=False, default="pending")
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    tour_schedule = relationship("Tourschedule", back_populates="tour_schedule_transportations")
    transportation = relationship("Transportation", back_populates="tour_schedule_transportations")

    __table_args__ = (
        Index("ix_tour_schedule_transportations_status", "booking_status"),
        CheckConstraint("seats_booked >= 0", name="ck_tour_schedule_transportations_seats_nonneg"),
        CheckConstraint("total_cost >= 0", name="ck_tour_schedule_transportations_cost_nonneg"),
        CheckConstraint("arrival_datetime >= departure_datetime", name="ck_tour_schedule_transportations_times_valid"),
    )