# CMS Data Model

Entity relationships and database schema for the CMS module.

## Entity Relationship Overview

```
Platform 1──* ContentPage
Store    1──* ContentPage
Store    1──* MediaFile
Store    1──1 StoreTheme
```

## Models

### ContentPage

Multi-language content pages with platform/store hierarchy. Pages can be platform marketing pages, store defaults, or store-specific overrides.

| Field | Type | Constraints | Description |
|-------|------|-------------|-------------|
| `id` | Integer | PK | Primary key |
| `platform_id` | Integer | FK, not null, indexed | Platform this page belongs to |
| `store_id` | Integer | FK, nullable, indexed | Store association (null = platform/default) |
| `is_platform_page` | Boolean | not null, default False | Platform marketing page vs store default |
| `slug` | String(100) | not null, indexed | Page identifier (about, faq, contact, etc.) |
| `title` | String(200) | not null | Page title |
| `content` | Text | not null | HTML or Markdown content |
| `content_format` | String(20) | default "html" | Format: html, markdown |
| `template` | String(50) | default "default" | Template: default, minimal, modern, full |
| `sections` | JSON | nullable | Structured homepage sections with i18n |
| `title_translations` | JSON | nullable | Language-keyed title dict {en, fr, de, lb} |
| `content_translations` | JSON | nullable | Language-keyed content dict {en, fr, de, lb} |
| `meta_description` | String(300) | nullable | SEO meta description |
| `meta_keywords` | String(300) | nullable | SEO keywords |
| `is_published` | Boolean | not null, default False | Publication status |
| `published_at` | DateTime | nullable, tz-aware | Publication timestamp |
| `display_order` | Integer | not null, default 0 | Menu/footer ordering |
| `show_in_footer` | Boolean | not null, default True | Footer visibility |
| `show_in_header` | Boolean | not null, default False | Header navigation |
| `show_in_legal` | Boolean | not null, default False | Legal bar visibility |
| `created_at` | DateTime | tz-aware | Record creation time |
| `updated_at` | DateTime | tz-aware | Record update time |
| `created_by` | Integer | FK, nullable | Creator user ID |
| `updated_by` | Integer | FK, nullable | Updater user ID |

**Unique Constraint**: `(platform_id, store_id, slug)`
**Composite Indexes**: `(platform_id, store_id, is_published)`, `(platform_id, slug, is_published)`, `(platform_id, is_platform_page)`

**Page tiers**: platform → store_default (store_id null, not platform) → store_override (store_id set)

### MediaFile

Media files (images, videos, documents) managed per-store.

| Field | Type | Constraints | Description |
|-------|------|-------------|-------------|
| `id` | Integer | PK | Primary key |
| `store_id` | Integer | FK, not null, indexed | Store owner |
| `filename` | String(255) | not null, indexed | UUID-based stored filename |
| `original_filename` | String(255) | nullable | Original upload name |
| `file_path` | String(500) | not null | Relative path from uploads/ |
| `media_type` | String(20) | not null | image, video, document |
| `mime_type` | String(100) | nullable | MIME type |
| `file_size` | Integer | nullable | File size in bytes |
| `width` | Integer | nullable | Image/video width in pixels |
| `height` | Integer | nullable | Image/video height in pixels |
| `thumbnail_path` | String(500) | nullable | Path to thumbnail |
| `alt_text` | String(500) | nullable | Alt text for images |
| `description` | Text | nullable | File description |
| `folder` | String(100) | default "general" | Folder: products, general, etc. |
| `tags` | JSON | nullable | Tags for categorization |
| `extra_metadata` | JSON | nullable | Additional metadata (EXIF, etc.) |
| `is_optimized` | Boolean | default False | Optimization status |
| `optimized_size` | Integer | nullable | Size after optimization |
| `usage_count` | Integer | default 0 | Usage tracking |
| `created_at` | DateTime | tz-aware | Record creation time |
| `updated_at` | DateTime | tz-aware | Record update time |

**Composite Indexes**: `(store_id, folder)`, `(store_id, media_type)`

### StoreTheme

Per-store theme configuration including colors, fonts, layout, and branding. One-to-one with Store.

| Field | Type | Constraints | Description |
|-------|------|-------------|-------------|
| `id` | Integer | PK | Primary key |
| `store_id` | Integer | FK, unique, not null | One-to-one with store |
| `theme_name` | String(100) | default "default" | Preset: default, modern, classic, minimal, vibrant |
| `is_active` | Boolean | default True | Theme active status |
| `colors` | JSON | default {...} | Color scheme: primary, secondary, accent, background, text, border |
| `font_family_heading` | String(100) | default "Inter, sans-serif" | Heading font |
| `font_family_body` | String(100) | default "Inter, sans-serif" | Body font |
| `logo_url` | String(500) | nullable | Store logo path |
| `logo_dark_url` | String(500) | nullable | Dark mode logo |
| `favicon_url` | String(500) | nullable | Favicon path |
| `banner_url` | String(500) | nullable | Homepage banner |
| `layout_style` | String(50) | default "grid" | Layout: grid, list, masonry |
| `header_style` | String(50) | default "fixed" | Header: fixed, static, transparent |
| `product_card_style` | String(50) | default "modern" | Card: modern, classic, minimal |
| `custom_css` | Text | nullable | Custom CSS overrides |
| `social_links` | JSON | default {} | Social media URLs |
| `meta_title_template` | String(200) | nullable | SEO title template |
| `meta_description` | Text | nullable | SEO meta description |
| `created_at` | DateTime | tz-aware | Record creation time |
| `updated_at` | DateTime | tz-aware | Record update time |

## Design Patterns

- **Three-tier content hierarchy**: Platform pages → store defaults → store overrides
- **JSON translations**: Title and content translations stored as JSON dicts with language keys
- **Media organization**: Files organized by store and folder with type classification
- **Theme presets**: Named presets with full customization via JSON color scheme and CSS overrides
- **SEO support**: Meta description, keywords, and title templates on pages and themes