from __future__ import annotations
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, JSON
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

from app.database import Base


class ProductConfig(Base):
    __tablename__ = "product_config"

    id = Column(Integer, primary_key=True, autoincrement=True)
    narrative = Column(Text)
    pricing_philosophy = Column(Text)
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    versions = relationship("ProductVersion", back_populates="config", cascade="all, delete-orphan", order_by="ProductVersion.sort_order")
    summer_camp = relationship("SummerCamp", back_populates="config", uselist=False, cascade="all, delete-orphan")
    selection_guide = relationship("SelectionGuide", back_populates="config", uselist=False, cascade="all, delete-orphan")


class ProductVersion(Base):
    __tablename__ = "product_versions"

    id = Column(Integer, primary_key=True, autoincrement=True)
    config_id = Column(Integer, ForeignKey("product_config.id", ondelete="CASCADE"), nullable=False)
    version_id = Column(String(50), unique=True, nullable=False)
    name = Column(String(200), nullable=False)
    days = Column(Integer, nullable=False)
    nights = Column(Integer, nullable=False)
    audience = Column(String(255))
    description = Column(Text)
    tag = Column(String(50))
    sort_order = Column(Integer, default=0)

    config = relationship("ProductConfig", back_populates="versions")
    highlights = relationship("ProductVersionHighlight", back_populates="version", cascade="all, delete-orphan", order_by="ProductVersionHighlight.sort_order")


class ProductVersionHighlight(Base):
    __tablename__ = "product_version_highlights"

    id = Column(Integer, primary_key=True, autoincrement=True)
    version_id = Column(Integer, ForeignKey("product_versions.id", ondelete="CASCADE"), nullable=False)
    text = Column(Text, nullable=False)
    sort_order = Column(Integer, default=0)

    version = relationship("ProductVersion", back_populates="highlights")


class SummerCamp(Base):
    __tablename__ = "summer_camp"

    id = Column(Integer, primary_key=True, autoincrement=True)
    config_id = Column(Integer, ForeignKey("product_config.id", ondelete="CASCADE"), nullable=False)
    name = Column(String(200))
    positioning = Column(Text)
    days = Column(Integer)
    nights = Column(Integer)
    sessions_json = Column(JSON)
    difference_from_v9 = Column(Text)
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    config = relationship("ProductConfig", back_populates="summer_camp")
    principles = relationship("SummerCampPrinciple", back_populates="camp", cascade="all, delete-orphan", order_by="SummerCampPrinciple.sort_order")
    activities = relationship("SummerCampActivity", back_populates="camp", cascade="all, delete-orphan", order_by="SummerCampActivity.sort_order")
    itinerary = relationship("SummerCampItinerary", back_populates="camp", cascade="all, delete-orphan", order_by="SummerCampItinerary.sort_order")
    faq = relationship("SummerCampFaq", back_populates="camp", cascade="all, delete-orphan", order_by="SummerCampFaq.sort_order")


class SummerCampPrinciple(Base):
    __tablename__ = "summer_camp_principles"

    id = Column(Integer, primary_key=True, autoincrement=True)
    camp_id = Column(Integer, ForeignKey("summer_camp.id", ondelete="CASCADE"), nullable=False)
    text = Column(String(255), nullable=False)
    sort_order = Column(Integer, default=0)

    camp = relationship("SummerCamp", back_populates="principles")


class SummerCampActivity(Base):
    __tablename__ = "summer_camp_activities"

    id = Column(Integer, primary_key=True, autoincrement=True)
    camp_id = Column(Integer, ForeignKey("summer_camp.id", ondelete="CASCADE"), nullable=False)
    text = Column(Text, nullable=False)
    sort_order = Column(Integer, default=0)

    camp = relationship("SummerCamp", back_populates="activities")


class SummerCampItinerary(Base):
    __tablename__ = "summer_camp_itinerary"

    id = Column(Integer, primary_key=True, autoincrement=True)
    camp_id = Column(Integer, ForeignKey("summer_camp.id", ondelete="CASCADE"), nullable=False)
    text = Column(Text, nullable=False)
    sort_order = Column(Integer, default=0)

    camp = relationship("SummerCamp", back_populates="itinerary")


class SummerCampFaq(Base):
    __tablename__ = "summer_camp_faq"

    id = Column(Integer, primary_key=True, autoincrement=True)
    camp_id = Column(Integer, ForeignKey("summer_camp.id", ondelete="CASCADE"), nullable=False)
    question = Column(Text, nullable=False)
    answer = Column(Text, nullable=False)
    sort_order = Column(Integer, default=0)

    camp = relationship("SummerCamp", back_populates="faq")


class SelectionGuide(Base):
    __tablename__ = "selection_guide"

    id = Column(Integer, primary_key=True, autoincrement=True)
    config_id = Column(Integer, ForeignKey("product_config.id", ondelete="CASCADE"), nullable=False)
    by_vacation_length = Column(JSON)
    by_child_age = Column(JSON)
    by_preference = Column(JSON)

    config = relationship("ProductConfig", back_populates="selection_guide")