# Marketplace Data Model

Entity relationships and database schema for the marketplace module.

## Entity Relationship Diagram

```
┌──────────────────────────┐
│        Store             │ (from tenancy module)
└──────┬───────────────────┘
       │ 1
       │
  ┌────┴──────────────────────────────────────────────┐
  │                    │                │              │
  ▼ 1                  ▼ *              ▼ 0..1         ▼ *
┌──────────────┐  ┌──────────────┐  ┌───────────────┐ ┌──────────────┐
│ Marketplace  │  │ Marketplace  │  │ StoreLetzshop │ │ Letzshop     │
│ ImportJob    │  │ Product      │  │ Credentials   │ │ Order        │
│              │  │              │  │               │ │              │
│ source_url   │  │ gtin, mpn    │  │ api_key_enc   │ │ letzshop_id  │
│ language     │  │ sku, brand   │  │ auto_sync     │ │ sync_status  │
│ status       │  │ price_cents  │  │ last_sync_at  │ │ customer     │
│ imported_cnt │  │ is_digital   │  │ default_      │ │ total_amount │
│ error_count  │  │ marketplace  │  │ carrier       │ │ tracking     │
└──────┬───────┘  └──────┬───────┘  └───────────────┘ └──────┬───────┘
       │                 │                                     │
       ▼ *               ▼ *                                   │
┌──────────────┐  ┌──────────────────┐                        │
│ Marketplace  │  │ Marketplace      │                        │
│ ImportError  │  │ Product          │                        │
│              │  │ Translation      │                        │
│ row_number   │  │                  │                        │
│ error_type   │  │ language         │                        │
│ error_msg    │  │ title            │                        │
│ row_data     │  │ description      │                        │
└──────────────┘  │ meta_title       │                        │
                  └──────────────────┘                        │
                                                              │
  ┌───────────────────────────────────────────────────────────┘
  │
  ▼ *                    ▼ *                    ▼ *
┌──────────────────┐  ┌──────────────────┐  ┌──────────────────┐
│ Letzshop         │  │ Letzshop         │  │ Letzshop         │
│ FulfillmentQueue │  │ SyncLog          │  │ Historical       │
│                  │  │                  │  │ ImportJob        │
│ operation        │  │ operation_type   │  │                  │
│ payload          │  │ direction        │  │ current_phase    │
│ status           │  │ status           │  │ current_page     │
│ attempts         │  │ records_*        │  │ orders_processed │
└──────────────────┘  └──────────────────┘  └──────────────────┘

┌──────────────────┐  ┌──────────────────┐
│ Letzshop         │  │ Store            │
│ StoreCache       │  │ Onboarding       │
│                  │  │                  │
│ letzshop_id      │  │ status           │
│ name, slug       │  │ current_step     │
│ claimed_by_      │  │ step_*_completed │
│ store_id         │  │ skipped_by_admin │
└──────────────────┘  └──────────────────┘
```

## Models

### Product Import Pipeline

#### MarketplaceProduct

Canonical product data imported from marketplace sources. Serves as a staging area — stores publish selected products to their own catalog.

| Field | Type | Description |
|-------|------|-------------|
| `marketplace_product_id` | String (unique) | Unique ID from marketplace |
| `marketplace` | String | Source marketplace (e.g., "Letzshop") |
| `gtin` | String | EAN/UPC barcode |
| `mpn` | String | Manufacturer Part Number |
| `sku` | String | Merchant's internal SKU |
| `store_name` | String | Store name from marketplace |
| `source_url` | String | CSV source URL |
| `product_type_enum` | Enum | physical, digital, service, subscription |
| `is_digital` | Boolean | Whether product is digital |
| `digital_delivery_method` | Enum | download, email, in_app, streaming, license_key |
| `brand` | String | Brand name |
| `google_product_category` | String | Google Shopping category |
| `condition` | String | new, used, refurbished |
| `price_cents` | Integer | Price in cents |
| `sale_price_cents` | Integer | Sale price in cents |
| `currency` | String | Currency code (default EUR) |
| `tax_rate_percent` | Decimal | VAT rate (default 17%) |
| `image_link` | String | Main product image |
| `additional_images` | JSON | Array of additional image URLs |
| `attributes` | JSON | Flexible attributes |
| `weight_grams` | Integer | Product weight |
| `is_active` | Boolean | Whether product is active |

**Relationships:** `translations` (MarketplaceProductTranslation), `store_products` (Product)

#### MarketplaceProductTranslation

Localized content for marketplace products.

| Field | Type | Description |
|-------|------|-------------|
| `marketplace_product_id` | FK | Parent product |
| `language` | String | Language code (en, fr, de, lb) |
| `title` | String | Product title |
| `description` | Text | Full description |
| `short_description` | Text | Short description |
| `meta_title` | String | SEO title |
| `meta_description` | String | SEO description |
| `url_slug` | String | URL-friendly slug |
| `source_import_id` | Integer | Import job that created this |
| `source_file` | String | Source CSV file |

**Constraint:** Unique (`marketplace_product_id`, `language`)

#### MarketplaceImportJob

Tracks CSV product import jobs with progress and metrics.

| Field | Type | Description |
|-------|------|-------------|
| `store_id` | FK | Target store |
| `user_id` | FK | Who triggered the import |
| `marketplace` | String | Source marketplace (default "Letzshop") |
| `source_url` | String | CSV feed URL |
| `language` | String | Import language for translations |
| `status` | String | pending, processing, completed, failed, completed_with_errors |
| `imported_count` | Integer | New products imported |
| `updated_count` | Integer | Existing products updated |
| `error_count` | Integer | Failed rows |
| `total_processed` | Integer | Total rows processed |
| `error_message` | Text | Error message if failed |
| `celery_task_id` | String | Background task ID |
| `started_at` | DateTime | Processing start time |
| `completed_at` | DateTime | Processing end time |

**Relationships:** `store`, `user`, `errors` (MarketplaceImportError, cascade delete)

#### MarketplaceImportError

Detailed error records for individual import failures.

| Field | Type | Description |
|-------|------|-------------|
| `import_job_id` | FK | Parent import job (cascade delete) |
| `row_number` | Integer | Row in source CSV |
| `identifier` | String | Product identifier (ID, GTIN, etc.) |
| `error_type` | String | missing_title, missing_id, parse_error, validation_error |
| `error_message` | String | Human-readable error |
| `row_data` | JSON | Snapshot of key fields from failing row |

### Letzshop Order Integration

#### StoreLetzshopCredentials

Encrypted API credentials and sync settings per store.

| Field | Type | Description |
|-------|------|-------------|
| `store_id` | FK (unique) | One credential set per store |
| `api_key_encrypted` | String | Fernet-encrypted API key |
| `api_endpoint` | String | GraphQL endpoint URL |
| `auto_sync_enabled` | Boolean | Enable automatic order sync |
| `sync_interval_minutes` | Integer | Auto-sync interval (5-1440) |
| `test_mode_enabled` | Boolean | Test mode flag |
| `default_carrier` | String | Default shipping carrier |
| `carrier_*_label_url` | String | Per-carrier label URL prefixes |
| `last_sync_at` | DateTime | Last sync timestamp |
| `last_sync_status` | String | success, failed, partial |
| `last_sync_error` | Text | Error message if failed |

#### LetzshopOrder

Tracks orders imported from Letzshop marketplace.

| Field | Type | Description |
|-------|------|-------------|
| `store_id` | FK | Store that owns this order |
| `letzshop_order_id` | String | Letzshop order GID |
| `letzshop_shipment_id` | String | Letzshop shipment GID |
| `letzshop_order_number` | String | Human-readable order number |
| `external_order_number` | String | Customer-facing reference |
| `shipment_number` | String | Carrier shipment number |
| `local_order_id` | FK | Linked local order (if any) |
| `letzshop_state` | String | Current Letzshop state |
| `customer_email` | String | Customer email |
| `customer_name` | String | Customer name |
| `customer_locale` | String | Customer language for invoicing |
| `total_amount` | String | Order total |
| `currency` | String | Currency code |
| `raw_order_data` | JSON | Full order data from Letzshop |
| `inventory_units` | JSON | List of inventory units |
| `sync_status` | String | pending, confirmed, rejected, shipped |
| `shipping_carrier` | String | Carrier code |
| `tracking_number` | String | Tracking number |
| `tracking_url` | String | Full tracking URL |
| `shipping_country_iso` | String | Shipping country |
| `billing_country_iso` | String | Billing country |
| `order_date` | DateTime | Original order date from Letzshop |

#### LetzshopFulfillmentQueue

Outbound operation queue with retry logic for Letzshop fulfillment.

| Field | Type | Description |
|-------|------|-------------|
| `store_id` | FK | Store |
| `order_id` | FK | Linked order |
| `operation` | String | confirm_item, decline_item, set_tracking |
| `payload` | JSON | Operation data |
| `status` | String | pending, processing, completed, failed |
| `attempts` | Integer | Retry count |
| `max_attempts` | Integer | Max retries (default 3) |
| `error_message` | Text | Last error |
| `response_data` | JSON | Response from Letzshop |

#### LetzshopSyncLog

Audit trail for all Letzshop sync operations.

| Field | Type | Description |
|-------|------|-------------|
| `store_id` | FK | Store |
| `operation_type` | String | order_import, confirm_inventory, set_tracking, etc. |
| `direction` | String | inbound, outbound |
| `status` | String | success, failed, partial |
| `records_processed` | Integer | Total records |
| `records_succeeded` | Integer | Successful records |
| `records_failed` | Integer | Failed records |
| `error_details` | JSON | Detailed error info |
| `started_at` | DateTime | Operation start |
| `completed_at` | DateTime | Operation end |
| `duration_seconds` | Integer | Total duration |
| `triggered_by` | String | user_id, scheduler, webhook |

#### LetzshopHistoricalImportJob

Tracks progress of historical order imports for real-time progress polling.

| Field | Type | Description |
|-------|------|-------------|
| `store_id` | FK | Store |
| `user_id` | FK | Who triggered |
| `status` | String | pending, fetching, processing, completed, failed |
| `current_phase` | String | confirmed, declined |
| `current_page` | Integer | Current pagination page |
| `total_pages` | Integer | Total pages |
| `shipments_fetched` | Integer | Shipments fetched so far |
| `orders_processed` | Integer | Orders processed |
| `orders_imported` | Integer | New orders imported |
| `orders_updated` | Integer | Updated existing orders |
| `orders_skipped` | Integer | Duplicate orders skipped |
| `products_matched` | Integer | Products matched by EAN |
| `products_not_found` | Integer | Products not found |
| `confirmed_stats` | JSON | Stats for confirmed phase |
| `declined_stats` | JSON | Stats for declined phase |
| `celery_task_id` | String | Background task ID |

### Supporting Models

#### LetzshopStoreCache

Cache of Letzshop marketplace store directory for browsing and claiming during signup.

| Field | Type | Description |
|-------|------|-------------|
| `letzshop_id` | String (unique) | Letzshop store identifier |
| `slug` | String | URL slug |
| `name` | String | Store name |
| `merchant_name` | String | Merchant name |
| `is_active` | Boolean | Active on Letzshop |
| `description_en/fr/de` | Text | Localized descriptions |
| `email`, `phone`, `website` | String | Contact info |
| `street`, `zipcode`, `city`, `country_iso` | String | Address |
| `latitude`, `longitude` | Float | Geo coordinates |
| `categories` | JSON | Category array |
| `social_media_links` | JSON | Social links |
| `claimed_by_store_id` | FK | Store that claimed this listing |
| `claimed_at` | DateTime | When claimed |
| `last_synced_at` | DateTime | Last directory sync |

#### StoreOnboarding

Tracks completion of mandatory onboarding steps for new stores.

| Field | Type | Description |
|-------|------|-------------|
| `store_id` | FK (unique) | One record per store |
| `status` | String | not_started, in_progress, completed, skipped |
| `current_step` | Integer | Current onboarding step |
| Step 1 | — | Merchant profile completion |
| Step 2 | — | Letzshop API key setup + verification |
| Step 3 | — | Product import (CSV URL set) |
| Step 4 | — | Order sync (first sync job) |
| `skipped_by_admin` | Boolean | Admin override |
| `skipped_reason` | Text | Reason for skip |