orion/app/modules/cms/models/content_page.py

# app/modules/cms/models/content_page.py
"""
Content Page Model

Manages static content pages (About, FAQ, Contact, Shipping, Returns, etc.)
with a three-tier hierarchy:

1. Platform Marketing Pages (is_platform_page=True, store_id=NULL)
   - Homepage, pricing, platform about, contact
   - Describes the platform/business offering itself

2. Store Default Pages (is_platform_page=False, store_id=NULL)
   - Generic storefront pages that all stores inherit
   - About Us, Shipping Policy, Return Policy, etc.

3. Store Override/Custom Pages (is_platform_page=False, store_id=set)
   - Store-specific customizations
   - Either overrides a default or is a completely custom page

Features:
- Multi-platform support (each platform has its own pages)
- Three-tier content resolution
- Rich text content (HTML/Markdown)
- SEO metadata
- Published/Draft status
- Navigation placement (header, footer, legal)
"""

from datetime import UTC, datetime

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

from app.core.database import Base


class ContentPage(Base):
    """
    Content pages with three-tier hierarchy.

    Page Types:
    1. Platform Marketing Page: platform_id=X, store_id=NULL, is_platform_page=True
       - Platform's own pages (homepage, pricing, about)
    2. Store Default Page: platform_id=X, store_id=NULL, is_platform_page=False
       - Fallback pages for stores who haven't customized
    3. Store Override/Custom: platform_id=X, store_id=Y, is_platform_page=False
       - Store-specific content

    Resolution Logic:
    1. Check for store override (platform_id + store_id + slug)
    2. Fall back to store default (platform_id + store_id=NULL + is_platform_page=False)
    3. If neither exists, return 404
    """

    __tablename__ = "content_pages"

    id = Column(Integer, primary_key=True, index=True)

    # Platform association (REQUIRED - determines which platform this page belongs to)
    platform_id = Column(
        Integer,
        ForeignKey("platforms.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
        comment="Platform this page belongs to",
    )

    # Store association (NULL = platform page or store default)
    store_id = Column(
        Integer,
        ForeignKey("stores.id", ondelete="CASCADE"),
        nullable=True,
        index=True,
        comment="Store this page belongs to (NULL for platform/default pages)",
    )

    # Distinguish platform marketing pages from store defaults
    is_platform_page = Column(
        Boolean,
        default=False,
        nullable=False,
        comment="True = platform marketing page (homepage, pricing); False = store default or override",
    )

    # Page identification
    slug = Column(
        String(100), nullable=False, index=True
    )  # about, faq, contact, shipping, returns, etc.
    title = Column(String(200), nullable=False)

    # Content
    content = Column(Text, nullable=False)  # HTML or Markdown
    content_format = Column(String(20), default="html")  # html, markdown

    # Template selection (for landing pages)
    # Options: 'default', 'minimal', 'modern', 'full'
    # Only used for landing pages (slug='landing' or 'home')
    template = Column(String(50), default="default", nullable=False)

    # Homepage sections (structured JSON for section-based editing)
    # Only used for homepage (slug='home'). Contains hero, features, pricing, cta sections
    # with multi-language support via TranslatableText pattern
    sections = Column(
        JSON,
        nullable=True,
        default=None,
        comment="Structured homepage sections (hero, features, pricing, cta) with i18n",
    )

    # Multi-language title and content (JSON dicts keyed by language code)
    # e.g. {"en": "About Us", "fr": "À propos", "de": "Über uns", "lb": "Iwwer eis"}
    title_translations = Column(
        JSON,
        nullable=True,
        default=None,
        comment="Language-keyed title dict for multi-language support",
    )
    content_translations = Column(
        JSON,
        nullable=True,
        default=None,
        comment="Language-keyed content dict for multi-language support",
    )

    # SEO
    meta_description = Column(String(300), nullable=True)
    meta_description_translations = Column(
        JSON,
        nullable=True,
        default=None,
        comment="Language-keyed meta description dict for multi-language SEO",
    )

    # Publishing
    is_published = Column(Boolean, default=False, nullable=False)
    published_at = Column(DateTime(timezone=True), nullable=True)

    # Ordering (for menus, footers)
    display_order = Column(Integer, default=0, nullable=False)
    show_in_footer = Column(Boolean, default=True, nullable=False)
    show_in_header = Column(Boolean, default=False, nullable=False)
    show_in_legal = Column(Boolean, default=False, nullable=False)  # Bottom bar with copyright

    # Timestamps
    created_at = Column(
        DateTime(timezone=True),
        default=lambda: datetime.now(UTC),
        nullable=False,
    )
    updated_at = Column(
        DateTime(timezone=True),
        default=lambda: datetime.now(UTC),
        onupdate=lambda: datetime.now(UTC),
        nullable=False,
    )

    # Author tracking (admin or store user who created/updated)
    created_by = Column(
        Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    updated_by = Column(
        Integer, ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )

    # Relationships
    platform = relationship("Platform", back_populates="content_pages")
    store = relationship("Store", back_populates="content_pages")
    creator = relationship("User", foreign_keys=[created_by])
    updater = relationship("User", foreign_keys=[updated_by])

    # Constraints
    __table_args__ = (
        # Unique combination: platform + store + slug
        # Platform pages: platform_id + store_id=NULL + is_platform_page=True
        # Store defaults: platform_id + store_id=NULL + is_platform_page=False
        # Store overrides: platform_id + store_id + slug
        UniqueConstraint("platform_id", "store_id", "slug", name="uq_platform_store_slug"),
        # Indexes for performance
        Index("idx_platform_store_published", "platform_id", "store_id", "is_published"),
        Index("idx_platform_slug_published", "platform_id", "slug", "is_published"),
        Index("idx_platform_page_type", "platform_id", "is_platform_page"),
    )

    def __repr__(self):
        store_name = self.store.name if self.store else "PLATFORM"
        return f"<ContentPage(id={self.id}, store={store_name}, slug={self.slug}, title={self.title})>"

    @property
    def is_store_default(self):
        """Check if this is a store default page (fallback for all stores)."""
        return self.store_id is None and not self.is_platform_page

    @property
    def is_store_override(self):
        """Check if this is a store-specific override or custom page."""
        return self.store_id is not None

    @property
    def page_tier(self) -> str:
        """Get the tier level of this page for display purposes."""
        if self.is_platform_page:
            return "platform"
        if self.store_id is None:
            return "store_default"
        return "store_override"

    def get_translated_title(self, lang: str, default_lang: str = "fr") -> str:
        """Get title in the given language, falling back to default_lang then self.title."""
        if self.title_translations:
            return (
                self.title_translations.get(lang)
                or self.title_translations.get(default_lang)
                or self.title
            )
        return self.title

    def get_translated_content(self, lang: str, default_lang: str = "fr") -> str:
        """Get content in the given language, falling back to default_lang then self.content."""
        if self.content_translations:
            return (
                self.content_translations.get(lang)
                or self.content_translations.get(default_lang)
                or self.content
            )
        return self.content

    def get_translated_meta_description(self, lang: str, default_lang: str = "fr") -> str:
        """Get meta description in the given language, falling back to default_lang then self.meta_description."""
        if self.meta_description_translations:
            return (
                self.meta_description_translations.get(lang)
                or self.meta_description_translations.get(default_lang)
                or self.meta_description or ""
            )
        return self.meta_description or ""

    def to_dict(self):
        """Convert to dictionary for API responses."""
        return {
            "id": self.id,
            "platform_id": self.platform_id,
            "platform_code": self.platform.code if self.platform else None,
            "platform_name": self.platform.name if self.platform else None,
            "store_id": self.store_id,
            "store_name": self.store.name if self.store else None,
            "slug": self.slug,
            "title": self.title,
            "title_translations": self.title_translations,
            "content": self.content,
            "content_translations": self.content_translations,
            "content_format": self.content_format,
            "template": self.template,
            "sections": self.sections,
            "meta_description": self.meta_description,
            "meta_description_translations": self.meta_description_translations,
            "is_published": self.is_published,
            "published_at": (
                self.published_at.isoformat() if self.published_at else None
            ),
            "display_order": self.display_order,
            "show_in_footer": self.show_in_footer or False,
            "show_in_header": self.show_in_header or False,
            "show_in_legal": self.show_in_legal or False,
            "is_platform_page": self.is_platform_page,
            "is_store_default": self.is_store_default,
            "is_store_override": self.is_store_override,
            "page_tier": self.page_tier,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
            "created_by": self.created_by,
            "updated_by": self.updated_by,
        }