from __future__ import annotations

from sqlalchemy import (
    Column, Integer, String, DateTime, ForeignKey, CheckConstraint,
    Index, text, Text, Boolean, Time, Numeric
)
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

# Adjust this import to match where your Base lives
from shared.db.base import Base

from datetime import datetime, timezone


def utcnow() -> datetime:
    return datetime.now(timezone.utc)



class Calendar(Base):
    __tablename__ = "calendars"

    id = Column(Integer, primary_key=True)
    container_type = Column(String(32), nullable=False, server_default=text("'page'"))
    container_id = Column(Integer, nullable=False)
    name = Column(String(255), nullable=False)
    description = Column(Text, nullable=True)
    slug = Column(String(255), nullable=False)

    created_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    updated_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    # relationships
    entries = relationship(
        "CalendarEntry",
        back_populates="calendar",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="CalendarEntry.start_at",
    )

    slots = relationship(
        "CalendarSlot",
        back_populates="calendar",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="CalendarSlot.time_start",
    )

    # Indexes / constraints
    __table_args__ = (
        Index("ix_calendars_container", "container_type", "container_id"),
        Index("ix_calendars_name", "name"),
        Index("ix_calendars_slug", "slug"),
        # Soft-delete-aware uniqueness: one active calendar per container/slug
        Index(
            "ux_calendars_container_slug_active",
            "container_type",
            "container_id",
            func.lower(slug),
            unique=True,
            postgresql_where=text("deleted_at IS NULL"),
        ),
    )


class CalendarEntry(Base):
    __tablename__ = "calendar_entries"

    id = Column(Integer, primary_key=True)
    calendar_id = Column(
        Integer,
        ForeignKey("calendars.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    # Ownership (cross-domain — no FK constraint to users table)
    user_id = Column(Integer, nullable=True, index=True)
    session_id = Column(String(64), nullable=True, index=True)
    order_id = Column(Integer, nullable=True, index=True)

    # NEW: slot link
    slot_id = Column(Integer, ForeignKey("calendar_slots.id", ondelete="SET NULL"), nullable=True, index=True)

    # details
    name = Column(String(255), nullable=False)
    start_at = Column(DateTime(timezone=True), nullable=False, index=True)
    end_at = Column(DateTime(timezone=True), nullable=True)

    # NEW: booking state + cost
    state = Column(
        String(20),
        nullable=False,
        server_default=text("'pending'"),
    )
    cost = Column(Numeric(10, 2), nullable=False, server_default=text("10"))

    # Ticket configuration
    ticket_price = Column(Numeric(10, 2), nullable=True)  # Price per ticket (NULL = no tickets)
    ticket_count = Column(Integer, nullable=True)  # Total available tickets (NULL = unlimited)

    created_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    updated_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    __table_args__ = (
        CheckConstraint(
            "(end_at IS NULL) OR (end_at >= start_at)",
            name="ck_calendar_entries_end_after_start",
        ),
        Index("ix_calendar_entries_name", "name"),
        Index("ix_calendar_entries_start_at", "start_at"),
        Index("ix_calendar_entries_user_id", "user_id"),
        Index("ix_calendar_entries_session_id", "session_id"),
        Index("ix_calendar_entries_state", "state"),
        Index("ix_calendar_entries_order_id", "order_id"),
        Index("ix_calendar_entries_slot_id", "slot_id"),
    )

    calendar = relationship("Calendar", back_populates="entries")
    slot = relationship("CalendarSlot", back_populates="entries", lazy="selectin")
    posts = relationship("CalendarEntryPost", back_populates="entry", cascade="all, delete-orphan")
    ticket_types = relationship(
        "TicketType",
        back_populates="entry",
        cascade="all, delete-orphan",
        passive_deletes=True,
        order_by="TicketType.name",
        lazy="selectin",
    )

DAY_LABELS = [
    ("mon", "Mon"),
    ("tue", "Tue"),
    ("wed", "Wed"),
    ("thu", "Thu"),
    ("fri", "Fri"),
    ("sat", "Sat"),
    ("sun", "Sun"),
]


class CalendarSlot(Base):
    __tablename__ = "calendar_slots"

    id = Column(Integer, primary_key=True)
    calendar_id = Column(
        Integer,
        ForeignKey("calendars.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    name = Column(String(255), nullable=False)
    description = Column(Text, nullable=True)

    mon = Column(Boolean, nullable=False, default=False)
    tue = Column(Boolean, nullable=False, default=False)
    wed = Column(Boolean, nullable=False, default=False)
    thu = Column(Boolean, nullable=False, default=False)
    fri = Column(Boolean, nullable=False, default=False)
    sat = Column(Boolean, nullable=False, default=False)
    sun = Column(Boolean, nullable=False, default=False)

    # NEW: whether bookings can be made at flexible times within this band
    flexible = Column(
        Boolean,
        nullable=False,
        server_default=text("false"),
        default=False,
    )

    @property
    def days_display(self) -> str:
        days = [label for attr, label in DAY_LABELS if getattr(self, attr)]
        if len(days) == len(DAY_LABELS):
            # all days selected
            return "All"  # or "All days" if you prefer
        return ", ".join(days) if days else "—"

    time_start = Column(Time(timezone=False), nullable=False)
    time_end = Column(Time(timezone=False), nullable=False)

    cost = Column(Numeric(10, 2), nullable=True)

    created_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    updated_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    __table_args__ = (
        CheckConstraint(
            "(time_end > time_start)",
            name="ck_calendar_slots_time_end_after_start",
        ),
        Index("ix_calendar_slots_calendar_id", "calendar_id"),
        Index("ix_calendar_slots_time_start", "time_start"),
    )

    calendar = relationship("Calendar", back_populates="slots")
    entries = relationship("CalendarEntry", back_populates="slot")


class TicketType(Base):
    __tablename__ = "ticket_types"

    id = Column(Integer, primary_key=True)
    entry_id = Column(
        Integer,
        ForeignKey("calendar_entries.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    name = Column(String(255), nullable=False)
    cost = Column(Numeric(10, 2), nullable=False)
    count = Column(Integer, nullable=False)

    created_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    updated_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    __table_args__ = (
        Index("ix_ticket_types_entry_id", "entry_id"),
        Index("ix_ticket_types_name", "name"),
    )

    entry = relationship("CalendarEntry", back_populates="ticket_types")


class Ticket(Base):
    __tablename__ = "tickets"

    id = Column(Integer, primary_key=True)
    entry_id = Column(
        Integer,
        ForeignKey("calendar_entries.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    ticket_type_id = Column(
        Integer,
        ForeignKey("ticket_types.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
    )
    user_id = Column(Integer, nullable=True, index=True)
    session_id = Column(String(64), nullable=True, index=True)
    order_id = Column(Integer, nullable=True, index=True)

    code = Column(String(64), unique=True, nullable=False)  # QR/barcode value
    state = Column(
        String(20),
        nullable=False,
        server_default=text("'reserved'"),
    )  # reserved, confirmed, checked_in, cancelled

    created_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    checked_in_at = Column(DateTime(timezone=True), nullable=True)

    __table_args__ = (
        Index("ix_tickets_entry_id", "entry_id"),
        Index("ix_tickets_ticket_type_id", "ticket_type_id"),
        Index("ix_tickets_user_id", "user_id"),
        Index("ix_tickets_session_id", "session_id"),
        Index("ix_tickets_order_id", "order_id"),
        Index("ix_tickets_code", "code", unique=True),
        Index("ix_tickets_state", "state"),
    )

    entry = relationship("CalendarEntry", backref="tickets")
    ticket_type = relationship("TicketType", backref="tickets")


class CalendarEntryPost(Base):
    """Junction between calendar entries and content (posts, etc.)."""
    __tablename__ = "calendar_entry_posts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    entry_id = Column(Integer, ForeignKey("calendar_entries.id", ondelete="CASCADE"), nullable=False)
    content_type = Column(String(32), nullable=False, server_default=text("'post'"))
    content_id = Column(Integer, nullable=False)

    created_at = Column(DateTime(timezone=True), nullable=False, default=utcnow)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    __table_args__ = (
        Index("ix_entry_posts_entry_id", "entry_id"),
        Index("ix_entry_posts_content", "content_type", "content_id"),
    )

    entry = relationship("CalendarEntry", back_populates="posts")


__all__ = ["Calendar", "CalendarEntry", "CalendarSlot", "TicketType", "Ticket", "CalendarEntryPost"]
