orion/app/modules/cms/docs/email-templates.md

# Email Template System

## Overview

The email template system provides comprehensive email customization for the Orion platform with the following features:

- **Platform-level templates** with store overrides
- **Orion branding** by default (removed for Enterprise whitelabel tier)
- **Platform-only templates** that cannot be overridden (billing, subscriptions)
- **Admin UI** for editing platform templates
- **Store UI** for customizing customer-facing emails
- **4-language support** (en, fr, de, lb)
- **Smart language resolution** (customer → store → platform default)

---

## Architecture

### Database Models

#### EmailTemplate (Platform Templates)
**File:** `models/database/email.py`

| Column | Type | Description |
|--------|------|-------------|
| `id` | Integer | Primary key |
| `code` | String(100) | Unique template identifier |
| `language` | String(5) | Language code (en, fr, de, lb) |
| `name` | String(255) | Human-readable name |
| `description` | Text | Template description |
| `category` | Enum | AUTH, ORDERS, BILLING, SYSTEM, MARKETING |
| `subject` | String(500) | Email subject line (Jinja2) |
| `body_html` | Text | HTML body (Jinja2) |
| `body_text` | Text | Plain text body (Jinja2) |
| `variables` | JSON | List of available variables |
| `is_platform_only` | Boolean | Cannot be overridden by stores |
| `required_variables` | Text | Comma-separated required variables |

**Key Methods:**
- `get_by_code_and_language(db, code, language)` - Get specific template
- `get_overridable_templates(db)` - Get templates stores can customize

#### StoreEmailTemplate (Store Overrides)
**File:** `models/database/store_email_template.py`

| Column | Type | Description |
|--------|------|-------------|
| `id` | Integer | Primary key |
| `store_id` | Integer | FK to stores.id |
| `template_code` | String(100) | References EmailTemplate.code |
| `language` | String(5) | Language code |
| `name` | String(255) | Custom name (optional) |
| `subject` | String(500) | Custom subject |
| `body_html` | Text | Custom HTML body |
| `body_text` | Text | Custom plain text body |
| `created_at` | DateTime | Creation timestamp |
| `updated_at` | DateTime | Last update timestamp |

**Key Methods:**
- `get_override(db, store_id, code, language)` - Get store override
- `create_or_update(db, store_id, code, language, ...)` - Upsert override
- `delete_override(db, store_id, code, language)` - Revert to platform default
- `get_all_overrides_for_store(db, store_id)` - List all store overrides

### Unique Constraint
```sql
UNIQUE (store_id, template_code, language)
```

---

## Email Template Service

**File:** `app/services/email_template_service.py`

The `EmailTemplateService` encapsulates all email template business logic, keeping API endpoints clean and focused on request/response handling.

### Admin Methods

| Method | Description |
|--------|-------------|
| `list_platform_templates()` | List all platform templates grouped by code |
| `get_template_categories()` | Get list of template categories |
| `get_platform_template(code)` | Get template with all language versions |
| `update_platform_template(code, language, data)` | Update platform template content |
| `preview_template(code, language, variables)` | Generate preview with sample data |
| `get_template_logs(code, limit)` | Get email logs for template |

### Store Methods

| Method | Description |
|--------|-------------|
| `list_overridable_templates(store_id)` | List templates store can customize |
| `get_store_template(store_id, code, language)` | Get template (override or platform default) |
| `create_or_update_store_override(store_id, code, language, data)` | Save store customization |
| `delete_store_override(store_id, code, language)` | Revert to platform default |
| `preview_store_template(store_id, code, language, variables)` | Preview with store branding |

### Usage Example

```python
from app.services.email_template_service import EmailTemplateService

service = EmailTemplateService(db)

# List templates for admin
templates = service.list_platform_templates()

# Get store's view of a template
template_data = service.get_store_template(store_id, "order_confirmation", "fr")

# Create store override
service.create_or_update_store_override(
    store_id=store.id,
    code="order_confirmation",
    language="fr",
    subject="Votre commande {{ order_number }}",
    body_html="<html>...</html>",
    body_text="Plain text...",
)
```

---

## Email Service

**File:** `app/services/email_service.py`

### Language Resolution

Priority order for determining email language:

1. **Customer preferred language** (if customer exists)
2. **Store storefront language** (store.storefront_language)
3. **Platform default** (`en`)

```python
def resolve_language(
    self,
    customer_id: int | None,
    store_id: int | None,
    explicit_language: str | None = None
) -> str
```

### Template Resolution

```python
def resolve_template(
    self,
    template_code: str,
    language: str,
    store_id: int | None = None
) -> ResolvedTemplate
```

Resolution order:
1. If `store_id` provided and template **not** platform-only:
   - Look for `StoreEmailTemplate` override
   - Fall back to platform `EmailTemplate`
2. If no store or platform-only:
   - Use platform `EmailTemplate`
3. Language fallback: `requested_language` → `en`

### Branding Resolution

```python
def get_branding(self, store_id: int | None) -> BrandingContext
```

| Scenario | Platform Name | Platform Logo |
|----------|--------------|---------------|
| No store | Orion | Orion logo |
| Standard store | Orion | Orion logo |
| Whitelabel store | Store name | Store logo |

Whitelabel is determined by the `white_label` feature flag on the store.

---

## API Endpoints

### Admin API

**File:** `app/api/v1/admin/email_templates.py`

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/api/v1/admin/email-templates` | List all platform templates |
| GET | `/api/v1/admin/email-templates/categories` | Get template categories |
| GET | `/api/v1/admin/email-templates/{code}` | Get template (all languages) |
| GET | `/api/v1/admin/email-templates/{code}/{language}` | Get specific language version |
| PUT | `/api/v1/admin/email-templates/{code}/{language}` | Update template |
| POST | `/api/v1/admin/email-templates/{code}/preview` | Preview with sample data |
| POST | `/api/v1/admin/email-templates/{code}/test` | Send test email |
| GET | `/api/v1/admin/email-templates/{code}/logs` | View email logs for template |

### Store API

**File:** `app/api/v1/store/email_templates.py`

| Method | Endpoint | Description |
|--------|----------|-------------|
| GET | `/api/v1/store/email-templates` | List overridable templates |
| GET | `/api/v1/store/email-templates/{code}` | Get template with override status |
| GET | `/api/v1/store/email-templates/{code}/{language}` | Get specific language (override or default) |
| PUT | `/api/v1/store/email-templates/{code}/{language}` | Create/update override |
| DELETE | `/api/v1/store/email-templates/{code}/{language}` | Reset to platform default |
| POST | `/api/v1/store/email-templates/{code}/preview` | Preview with store branding |
| POST | `/api/v1/store/email-templates/{code}/test` | Send test email |

---

## User Interface

### Admin UI

**Page:** `/admin/email-templates`
**Template:** `app/templates/admin/email-templates.html`
**JavaScript:** `static/admin/js/email-templates.js`

Features:
- Template list with category filtering
- Edit modal with language tabs (en, fr, de, lb)
- Platform-only indicator badge
- Variable reference panel
- HTML preview in iframe
- Send test email functionality

### Store UI

**Page:** `/store/{store_code}/email-templates`
**Template:** `app/templates/store/email-templates.html`
**JavaScript:** `static/store/js/email-templates.js`

Features:
- List of overridable templates with customization status
- Language override badges (green = customized)
- Edit modal with:
  - Language tabs
  - Source indicator (store override vs platform default)
  - Platform template reference
  - Revert to default button
- Preview and test email functionality

---

## Template Categories

| Category | Description | Platform-Only |
|----------|-------------|---------------|
| AUTH | Authentication emails (welcome, password reset) | No |
| ORDERS | Order-related emails (confirmation, shipped) | No |
| BILLING | Subscription/payment emails | Yes |
| SYSTEM | System emails (team invites, alerts) | No |
| MARKETING | Marketing/promotional emails | No |

---

## Available Templates

### Customer-Facing (Overridable)

| Code | Category | Languages | Description |
|------|----------|-----------|-------------|
| `signup_welcome` | AUTH | en, fr, de, lb | Welcome email after store signup |
| `order_confirmation` | ORDERS | en, fr, de, lb | Order confirmation to customer |
| `password_reset` | AUTH | en, fr, de, lb | Password reset link |
| `team_invite` | SYSTEM | en | Team member invitation |

### Platform-Only (Not Overridable)

| Code | Category | Languages | Description |
|------|----------|-----------|-------------|
| `subscription_welcome` | BILLING | en | Subscription confirmation |
| `payment_failed` | BILLING | en | Failed payment notification |
| `subscription_cancelled` | BILLING | en | Cancellation confirmation |
| `trial_ending` | BILLING | en | Trial ending reminder |

---

## Template Variables

### Common Variables (Injected Automatically)

| Variable | Description |
|----------|-------------|
| `platform_name` | "Orion" or store name (whitelabel) |
| `platform_logo_url` | Platform logo URL |
| `support_email` | Support email address |
| `store_name` | Store business name |
| `store_logo_url` | Store logo URL |

### Template-Specific Variables

#### signup_welcome
- `first_name`, `merchant_name`, `email`, `store_code`
- `login_url`, `trial_days`, `tier_name`

#### order_confirmation
- `customer_name`, `order_number`, `order_total`
- `order_items_count`, `order_date`, `shipping_address`

#### password_reset
- `customer_name`, `reset_link`, `expiry_hours`

#### team_invite
- `invitee_name`, `inviter_name`, `store_name`
- `role`, `accept_url`, `expires_in_days`

---

## Migration

**File:** `alembic/versions/u9c0d1e2f3g4_add_store_email_templates.py`

Run migration:
```bash
alembic upgrade head
```

The migration:
1. Adds `is_platform_only` and `required_variables` columns to `email_templates`
2. Creates `store_email_templates` table
3. Adds unique constraint on `(store_id, template_code, language)`
4. Creates indexes for performance

---

## Seeding Templates

**File:** `scripts/seed/seed_email_templates.py`

Run seed script:
```bash
python scripts/seed/seed_email_templates.py
```

The script:
- Creates/updates all platform templates
- Supports all 4 languages for customer-facing templates
- Sets `is_platform_only` flag for billing templates

---

## Security Considerations

1. **XSS Prevention**: HTML templates are rendered server-side with Jinja2 escaping
2. **Access Control**: Stores can only view/edit their own overrides
3. **Platform-only Protection**: API enforces `is_platform_only` flag
4. **Template Validation**: Jinja2 syntax validated before save
5. **Rate Limiting**: Test email sending subject to rate limits
6. **Token Hashing**: Password reset tokens stored as SHA256 hashes

---

## Usage Examples

### Sending a Template Email

```python
from app.services.email_service import EmailService

email_svc = EmailService(db)
email_log = email_svc.send_template(
    template_code="order_confirmation",
    to_email="customer@example.com",
    variables={
        "customer_name": "John Doe",
        "order_number": "ORD-12345",
        "order_total": "€99.99",
        "order_items_count": "3",
        "order_date": "2024-01-15",
        "shipping_address": "123 Main St, Luxembourg"
    },
    store_id=store.id,  # Optional: enables store override lookup
    customer_id=customer.id,  # Optional: for language resolution
    language="fr"  # Optional: explicit language override
)
```

### Creating a Store Override

```python
from models.database.store_email_template import StoreEmailTemplate

override = StoreEmailTemplate.create_or_update(
    db=db,
    store_id=store.id,
    template_code="order_confirmation",
    language="fr",
    subject="Confirmation de votre commande {{ order_number }}",
    body_html="<html>...</html>",
    body_text="Plain text version..."
)
db.commit()
```

### Reverting to Platform Default

```python
StoreEmailTemplate.delete_override(
    db=db,
    store_id=store.id,
    template_code="order_confirmation",
    language="fr"
)
db.commit()
```

---

## File Structure

```
├── alembic/versions/
│   └── u9c0d1e2f3g4_add_store_email_templates.py
├── app/
│   ├── api/v1/
│   │   ├── admin/
│   │   │   └── email_templates.py
│   │   └── store/
│   │       └── email_templates.py
│   ├── routes/
│   │   ├── admin_pages.py  (route added)
│   │   └── store_pages.py (route added)
│   ├── services/
│   │   ├── email_service.py (enhanced)
│   │   └── email_template_service.py (new - business logic)
│   └── templates/
│       ├── admin/
│       │   ├── email-templates.html
│       │   └── partials/sidebar.html (link added)
│       └── store/
│           ├── email-templates.html
│           └── partials/sidebar.html (link added)
├── models/
│   ├── database/
│   │   ├── email.py (enhanced)
│   │   └── store_email_template.py
│   └── schema/
│       └── email.py
├── scripts/
│   └── seed_email_templates.py (enhanced)
└── static/
    ├── admin/js/
    │   └── email-templates.js
    └── store/js/
        └── email-templates.js
```

---

## Related Documentation

- [Email Templates User Guide](email-templates-guide.md) - How to use the email template system
- [Password Reset Implementation](../../implementation/password-reset-implementation.md) - Password reset feature using email templates
- [Architecture Fixes (January 2026)](../../development/architecture-fixes-2026-01.md) - Architecture validation fixes