Schéma de Base de Données

Ce document décrit le schéma de base de données complet de Bindr, optimisé pour répondre aux use cases V1.

---

Diagrammes ERD (Entity Relationship Diagrams)

Le schéma complet a été divisé en 3 diagrammes thématiques pour faciliter la lecture. Chaque diagramme peut être ouvert dans Mermaid Live Editor pour une visualisation interactive avec zoom et pan.

1. Utilisateurs & Organisations

Ce diagramme montre la gestion des comptes utilisateurs et des organisations.

📊 Ouvrir dans Mermaid Live Editor

erDiagram
    users ||--o| organizations : "has"
    organizations ||--o{ events : "organizes"

    users {
        TEXT id PK
        TEXT email UK "NOT NULL"
        TEXT password_hash "NOT NULL"
        TEXT user_type "ORGANIZER or CUSTOMER"
        TEXT first_name
        TEXT last_name
        TEXT created_at
        INTEGER is_active
    }

    organizations {
        TEXT id PK
        TEXT user_id UK "FK"
        TEXT organization_name "NOT NULL"
        TEXT logo_url
        TEXT primary_color
        TEXT iban
        TEXT bic
        TEXT billing_country
        TEXT vat_number
    }

    events {
        TEXT id PK
        TEXT organization_id "FK"
        TEXT slug UK
        TEXT name "NOT NULL"
        TEXT event_date
        TEXT visibility "PUBLIC, PRIVATE, UNLISTED"
        TEXT password_hash
        TEXT status "DRAFT, ACTIVE, ARCHIVED"
        INTEGER photo_count
        INTEGER sale_count
    }

2. Photos & Événements

Ce diagramme illustre la gestion des photos uploadées par les photographes.

📊 Ouvrir dans Mermaid Live Editor

erDiagram
    events ||--o{ photographer_access : "has"
    events ||--o{ photos : "contains"
    photographer_access ||--o{ photos : "uploads"

    events {
        TEXT id PK
        TEXT organization_id "FK"
        TEXT name "NOT NULL"
        TEXT event_date
        TEXT visibility
        TEXT password_hash
        INTEGER photo_count
    }

    photographer_access {
        TEXT id PK
        TEXT event_id "FK"
        TEXT email "NOT NULL"
        TEXT login UK "NOT NULL"
        TEXT password_hash
        INTEGER is_active
        TEXT last_login
    }

    photos {
        TEXT id PK
        TEXT event_id "FK"
        TEXT uploaded_by "FK"
        TEXT original_filename
        TEXT storage_path
        TEXT watermarked_url
        INTEGER file_size
        INTEGER width
        INTEGER height
        TEXT processing_status
        TEXT description
        TEXT tags
        TEXT capture_date
        TEXT capture_time
        REAL gps_latitude
        REAL gps_longitude
        TEXT zone "depart, km5, arrivee"
        INTEGER view_count
        INTEGER purchase_count
    }

3. Commandes & Produits

Ce diagramme présente le système de commandes et de gestion des produits avec commissions.

📊 Ouvrir dans Mermaid Live Editor

erDiagram
    photos ||--o{ order_items : "ordered in"
    products ||--o{ order_items : "included in"
    orders ||--o{ order_items : "contains"
    users ||--o{ orders : "places"
    organizations ||--o{ organization_product_commissions : "has commission"
    products ||--o{ organization_product_commissions : "has rate"

    users {
        TEXT id PK
        TEXT email UK
        TEXT user_type
    }

    organizations {
        TEXT id PK
        TEXT organization_name
        TEXT iban
        TEXT bic
    }

    photos {
        TEXT id PK
        TEXT event_id "FK"
        TEXT watermarked_url
    }

    products {
        TEXT id PK
        TEXT sku UK
        TEXT name "NOT NULL"
        TEXT type "DIGITAL or PRINT"
        TEXT size "10x15, 20x30, etc"
        INTEGER has_frame_option
        REAL frame_price
        REAL base_price
        TEXT currency
    }

    organization_product_commissions {
        TEXT organization_id PK_FK
        TEXT product_id PK_FK
        REAL commission_rate "DEFAULT 0.30"
    }

    orders {
        TEXT id PK
        TEXT order_number UK
        TEXT user_id "FK nullable"
        TEXT email
        TEXT status
        TEXT payment_status
        REAL total_amount
        TEXT shipping_address_line1
        TEXT shipping_city
        TEXT shipping_country
    }

    order_items {
        TEXT id PK
        TEXT order_id "FK"
        TEXT product_id "FK"
        TEXT photo_id "FK nullable"
        INTEGER quantity
        REAL unit_price
        INTEGER has_frame
        REAL line_total
        TEXT product_sku
    }

---

Tables détaillées

1. users

Description: Comptes utilisateurs de la plateforme.

Colonnes:

Colonne	Type	Contraintes	Description
id	TEXT	PRIMARY KEY	Identifiant unique (UUID)
email	TEXT	UNIQUE, NOT NULL	Email de l'utilisateur
password_hash	TEXT	NOT NULL	Hash du mot de passe (bcrypt/argon2)
user_type	TEXT	NOT NULL, CHECK	Type: ORGANIZER, CUSTOMER
first_name	TEXT	NULL	Prénom
last_name	TEXT	NULL	Nom de famille
created_at	TEXT	NOT NULL, DEFAULT	Date de création
updated_at	TEXT	NOT NULL, DEFAULT	Date de mise à jour
last_login	TEXT	NULL	Dernière connexion
is_active	INTEGER	DEFAULT 1	Compte actif (1) ou désactivé (0)
email_verified	INTEGER	DEFAULT 0	Email vérifié (1) ou non (0)
email_verified_at	TEXT	NULL	Date de vérification email

Index: - idx_users_email sur email

Notes: - PHOTOGRAPHER retiré de l'enum car les photographes n'ont pas de compte utilisateur - V1: Uniquement ORGANIZER (les CUSTOMER en V2 pour comptes clients)

---

2. organizations

Description: Profils des organisations d'événements.

Colonnes:

Colonne	Type	Contraintes	Description
id	TEXT	PRIMARY KEY	Identifiant unique (UUID)
user_id	TEXT	UNIQUE, NOT NULL, FK	Référence vers users.id
organization_name	TEXT	NOT NULL	Nom de l'organisation
logo_url	TEXT	NULL	URL du logo
primary_color	TEXT	DEFAULT '#53a0ec'	Couleur principale (hex)
website	TEXT	NULL	Site web
description	TEXT	NULL	Description
iban	TEXT	NULL	IBAN pour paiements
bic	TEXT	NULL	BIC/SWIFT
billing_address_line1	TEXT	NULL	Adresse ligne 1
billing_address_line2	TEXT	NULL	Adresse ligne 2
billing_postal_code	TEXT	NULL	Code postal
billing_city	TEXT	NULL	Ville
billing_country	TEXT	DEFAULT 'FR'	Pays (ISO 3166-1 alpha-2)
vat_number	TEXT	NULL	Numéro TVA intracommunautaire
created_at	TEXT	NOT NULL, DEFAULT	Date de création
updated_at	TEXT	NOT NULL, DEFAULT	Date de mise à jour

Clés étrangères: - user_id → users(id)

Notes: - Coordonnées bancaires ajoutées pour le paiement des commissions - Adresse de facturation pour génération des attestations fiscales

---

3. events

Description: Événements/galeries créés par les organisations.

Colonnes:

Colonne	Type	Contraintes	Description
id	TEXT	PRIMARY KEY	Identifiant unique (UUID)
organization_id	TEXT	NOT NULL, FK	Organisation propriétaire
slug	TEXT	UNIQUE, NOT NULL	Slug URL-friendly (SEO)
name	TEXT	NOT NULL	Nom de l'événement
description	TEXT	NULL	Description (markdown supporté)
event_date	TEXT	NOT NULL	Date de l'événement (ISO 8601)
location	TEXT	NULL	Lieu de l'événement
cover_image_url	TEXT	NULL	Image de couverture
og_image_url	TEXT	NULL	Image Open Graph (partage social)
visibility	TEXT	DEFAULT 'PUBLIC'	PUBLIC, PRIVATE, UNLISTED
password_hash	TEXT	NULL	Hash du mot de passe (si PRIVATE)
status	TEXT	DEFAULT 'DRAFT'	DRAFT, ACTIVE, ARCHIVED
is_purchasable	INTEGER	DEFAULT 1	Photos achetables (1) ou non (0)
custom_message	TEXT	NULL	Message personnalisé
photo_count	INTEGER	DEFAULT 0	Nombre de photos (cache)
view_count	INTEGER	DEFAULT 0	Nombre de vues (cache)
reaction_count	INTEGER	DEFAULT 0	Nombre de réactions (cache)
sale_count	INTEGER	DEFAULT 0	Nombre de ventes (cache)
created_at	TEXT	NOT NULL, DEFAULT	Date de création
updated_at	TEXT	NOT NULL, DEFAULT	Date de mise à jour
published_at	TEXT	NULL	Date de publication

Clés étrangères: - organization_id → organizations(id)

Index: - idx_events_slug sur slug - idx_events_organization_id sur organization_id

Notes: - password_hash ajouté pour albums protégés (mineurs, événements privés) - slug généré automatiquement depuis le nom (URL-friendly)

---

4. photographer_access

Description: Accès temporaires pour photographes (pas de compte utilisateur).

Colonnes:

Colonne	Type	Contraintes	Description
id	TEXT	PRIMARY KEY	Identifiant unique (UUID)
event_id	TEXT	NOT NULL, FK	Événement associé
email	TEXT	NOT NULL	Email du photographe
login	TEXT	UNIQUE, NOT NULL	Login généré (ex: bindr-evt-abc123)
password_hash	TEXT	NOT NULL	Hash du mot de passe généré
is_active	INTEGER	NOT NULL, DEFAULT 1	Accès actif (1) ou révoqué (0)
created_at	TEXT	NOT NULL, DEFAULT	Date de création
updated_at	TEXT	NOT NULL, DEFAULT	Date de mise à jour
last_login	TEXT	NULL	Dernière connexion

Clés étrangères: - event_id → events(id) ON DELETE CASCADE

Notes: - Simplifié: permissions granulaires retirées (can_upload, can_delete, etc.) - Un photographe peut avoir plusieurs accès (un par événement) - Login/mot de passe générés automatiquement par l'organisation

---

5. photos

Description: Photos uploadées par les photographes.

Colonnes:

Colonne	Type	Contraintes	Description
id	TEXT	PRIMARY KEY	Identifiant unique (UUID)
event_id	TEXT	NOT NULL, FK	Événement associé
uploaded_by	TEXT	FK, NULL	Photographe qui a uploadé
original_filename	TEXT	NOT NULL	Nom de fichier original
storage_path	TEXT	NOT NULL	Chemin stockage S3
thumbnail_url	TEXT	NOT NULL	URL thumbnail (100x100)
medium_url	TEXT	NOT NULL	URL moyenne résolution (1200px)
full_url	TEXT	NOT NULL	URL haute résolution
watermarked_url	TEXT	NOT NULL	URL avec watermark
file_size	INTEGER	NOT NULL	Taille en bytes
width	INTEGER	NOT NULL	Largeur en pixels
height	INTEGER	NOT NULL	Hauteur en pixels
mime_type	TEXT	NOT NULL	Type MIME (image/jpeg, etc.)
processing_status	TEXT	NOT NULL	PENDING, PROCESSING, COMPLETED, FAILED
processing_error	TEXT	NULL	Message d'erreur si échec
is_visible	INTEGER	NOT NULL, DEFAULT 1	Photo visible (1) ou masquée (0)
position	INTEGER	NOT NULL, DEFAULT 0	Position dans la galerie
view_count	INTEGER	NOT NULL, DEFAULT 0	Nombre de vues
reaction_count	INTEGER	NOT NULL, DEFAULT 0	Nombre de réactions (V2)
purchase_count	INTEGER	NOT NULL, DEFAULT 0	Nombre d'achats
description	TEXT	NULL	Description ajoutée par photographe
tags	TEXT	NULL	Tags (JSON array ou comma-separated)
capture_date	TEXT	NULL	Date de prise (EXIF)
capture_time	TEXT	NULL	Heure de prise (EXIF)
gps_latitude	REAL	NULL	Latitude GPS (EXIF)
gps_longitude	REAL	NULL	Longitude GPS (EXIF)
zone	TEXT	NULL	Zone (départ, km5, arrivée, etc.)
watermark_config	TEXT	NULL	Config watermark (JSON)
uploaded_at	TEXT	NOT NULL, DEFAULT	Date d'upload
processed_at	TEXT	NULL	Date de fin de processing

Clés étrangères: - event_id → events(id) ON DELETE CASCADE - uploaded_by → photographer_access(id) ON DELETE SET NULL

Notes: - Métadonnées EXIF extraites pour recherche (date/heure, GPS) - zone renseignée par photographe pour faciliter la recherche - tags stockés en JSON ou comma-separated pour recherche fulltext - Processing asynchrone (status tracking)

---

6. products

Description: Catalogue des produits vendables (photos numériques, impressions).

Colonnes:

Colonne	Type	Contraintes	Description
id	TEXT	PRIMARY KEY	Identifiant unique (UUID)
sku	TEXT	UNIQUE, NOT NULL	SKU unique (ex: DIGITAL-HD, PRINT-10x15)
name	TEXT	NOT NULL	Nom du produit
description	TEXT	NULL	Description
type	TEXT	NOT NULL, CHECK	DIGITAL, PRINT
size	TEXT	NULL	Taille (10x15, 20x30, etc.)
has_frame_option	INTEGER	DEFAULT 0	Cadre disponible (1) ou non (0)
frame_price	REAL	NULL	Prix du cadre (si applicable)
base_price	REAL	NOT NULL	Prix de base TTC
currency	TEXT	DEFAULT 'EUR'	Devise (ISO 4217)
is_active	INTEGER	DEFAULT 1	Produit actif (1) ou désactivé (0)
delivery_time_days	INTEGER	NULL	Délai de livraison (jours)
stock_managed	INTEGER	DEFAULT 0	Stock géré (1) ou non (0)
created_at	TEXT	NOT NULL, DEFAULT	Date de création
updated_at	TEXT	NOT NULL, DEFAULT	Date de mise à jour

Index: - idx_products_sku sur sku

Notes: - Prix fixés par la plateforme (non modifiables par organisations) - has_frame_option pour impressions avec/sans cadre

---

7. organization_product_commissions

Description: Taux de commission par organisation et par produit.

Colonnes:

Colonne	Type	Contraintes	Description
organization_id	TEXT	PK, FK	Organisation
product_id	TEXT	PK, FK	Produit
commission_rate	REAL	NOT NULL, DEFAULT 0.30	Taux (0.30 = 30%)
created_at	TEXT	NOT NULL, DEFAULT	Date de création
updated_at	TEXT	NOT NULL, DEFAULT	Date de mise à jour

Clés primaires composites: - PRIMARY KEY (organization_id, product_id)

Clés étrangères: - organization_id → organizations(id) - product_id → products(id)

Notes: - Commission par défaut: 30% - Éditable uniquement en DB (pas d'interface) - Permet de négocier des taux spécifiques pour gros événements - Si pas de ligne pour une organisation/produit: utiliser le taux par défaut (0.30)

---

8. orders

Description: Commandes passées par les clients.

Colonnes:

Colonne	Type	Contraintes	Description
id	TEXT	PRIMARY KEY	Identifiant unique (UUID)
order_number	TEXT	UNIQUE, NOT NULL	Numéro de commande (ex: SPT-20240315-A3K9)
user_id	TEXT	FK, NULL	Client (NULL si guest checkout)
email	TEXT	NOT NULL	Email du client
status	TEXT	DEFAULT 'PENDING'	État commande
payment_status	TEXT	DEFAULT 'PENDING'	État paiement
payment_method	TEXT	NULL	Méthode de paiement (card, paypal, etc.)
payment_intent_id	TEXT	NULL	ID Stripe PaymentIntent
subtotal	REAL	NOT NULL	Sous-total articles
shipping_cost	REAL	DEFAULT 0.00	Frais de port
tax_amount	REAL	DEFAULT 0.00	Montant TVA
total_amount	REAL	NOT NULL	Montant total TTC
currency	TEXT	DEFAULT 'EUR'	Devise
shipping_name	TEXT	NULL	Nom destinataire
shipping_address_line1	TEXT	NULL	Adresse livraison ligne 1
shipping_address_line2	TEXT	NULL	Adresse livraison ligne 2
shipping_postal_code	TEXT	NULL	Code postal livraison
shipping_city	TEXT	NULL	Ville livraison
shipping_country	TEXT	NULL	Pays livraison
shipping_phone	TEXT	NULL	Téléphone livraison
billing_name	TEXT	NULL	Nom facturation
billing_address_line1	TEXT	NULL	Adresse facturation ligne 1
billing_address_line2	TEXT	NULL	Adresse facturation ligne 2
billing_postal_code	TEXT	NULL	Code postal facturation
billing_city	TEXT	NULL	Ville facturation
billing_country	TEXT	NULL	Pays facturation
notes	TEXT	NULL	Notes internes
created_at	TEXT	NOT NULL, DEFAULT	Date de création
updated_at	TEXT	NOT NULL, DEFAULT	Date de mise à jour
paid_at	TEXT	NULL	Date de paiement
shipped_at	TEXT	NULL	Date d'expédition
delivered_at	TEXT	NULL	Date de livraison

Clés étrangères: - user_id → users(id) (NULL pour guest checkout)

Index: - idx_orders_order_number sur order_number

Enums: - status: PENDING, PAID, PROCESSING, SHIPPED, DELIVERED, CANCELLED, REFUNDED - payment_status: PENDING, AUTHORIZED, PAID, FAILED, REFUNDED

Notes: - Adresses de livraison et facturation ajoutées - Guest checkout supporté (user_id NULL) - Intégration Stripe via payment_intent_id

---

9. order_items

Description: Lignes de commande (articles achetés).

Colonnes:

Colonne	Type	Contraintes	Description
id	TEXT	PRIMARY KEY	Identifiant unique (UUID)
order_id	TEXT	NOT NULL, FK	Commande parente
product_id	TEXT	NOT NULL, FK	Produit acheté
photo_id	TEXT	FK, NULL	Photo (NULL si produit non personnalisé)
quantity	INTEGER	NOT NULL	Quantité
unit_price	REAL	NOT NULL	Prix unitaire
has_frame	INTEGER	NOT NULL, DEFAULT 0	Avec cadre (1) ou non (0)
frame_price	REAL	NULL	Prix du cadre
line_total	REAL	NOT NULL	Total ligne (unit_price × quantity + frame)
product_sku	TEXT	NOT NULL	SKU produit (snapshot)
product_name	TEXT	NOT NULL	Nom produit (snapshot)
product_size	TEXT	NULL	Taille produit (snapshot)
photo_url	TEXT	NULL	URL photo (snapshot)
notes	TEXT	NULL	Notes
created_at	TEXT	NOT NULL, DEFAULT	Date de création

Clés étrangères: - order_id → orders(id) ON DELETE CASCADE - product_id → products(id) ON DELETE RESTRICT - photo_id → photos(id) ON DELETE SET NULL

Index: - idx_order_items_order_id sur order_id - idx_order_items_product_id sur product_id - idx_order_items_photo_id sur photo_id

Notes: - photo_id nullable pour produits non personnalisés (V2: merchandising générique) - Snapshots des infos produit pour historique (prix, nom peuvent changer)

---

Scripts SQL de création

Script complet (SQLite)

-- 1. Users
CREATE TABLE users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    user_type TEXT NOT NULL CHECK(user_type IN ('ORGANIZER', 'CUSTOMER')),
    first_name TEXT,
    last_name TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login TEXT,
    is_active INTEGER DEFAULT 1,
    email_verified INTEGER DEFAULT 0,
    email_verified_at TEXT
);

CREATE INDEX idx_users_email ON users(email);

-- 2. Organizations
CREATE TABLE organizations (
    id TEXT PRIMARY KEY,
    user_id TEXT UNIQUE NOT NULL,
    organization_name TEXT NOT NULL,
    logo_url TEXT,
    primary_color TEXT DEFAULT '#53a0ec',
    website TEXT,
    description TEXT,
    iban TEXT,
    bic TEXT,
    billing_address_line1 TEXT,
    billing_address_line2 TEXT,
    billing_postal_code TEXT,
    billing_city TEXT,
    billing_country TEXT DEFAULT 'FR',
    vat_number TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 3. Events
CREATE TABLE events (
    id TEXT PRIMARY KEY,
    organization_id TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    event_date TEXT NOT NULL,
    location TEXT,
    cover_image_url TEXT,
    og_image_url TEXT,
    visibility TEXT DEFAULT 'PUBLIC' CHECK(visibility IN ('PUBLIC', 'PRIVATE', 'UNLISTED')),
    password_hash TEXT,
    status TEXT DEFAULT 'DRAFT' CHECK(status IN ('DRAFT', 'ACTIVE', 'ARCHIVED')),
    is_purchasable INTEGER DEFAULT 1,
    custom_message TEXT,
    photo_count INTEGER DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    reaction_count INTEGER DEFAULT 0,
    sale_count INTEGER DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TEXT,
    FOREIGN KEY (organization_id) REFERENCES organizations(id)
);

CREATE INDEX idx_events_slug ON events(slug);
CREATE INDEX idx_events_organization_id ON events(organization_id);

-- 4. Photographer Access
CREATE TABLE photographer_access (
    id TEXT PRIMARY KEY,
    event_id TEXT NOT NULL,
    email TEXT NOT NULL,
    login TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    is_active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login TEXT,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

-- 5. Photos
CREATE TABLE photos (
    id TEXT PRIMARY KEY,
    event_id TEXT NOT NULL,
    uploaded_by TEXT,
    original_filename TEXT NOT NULL,
    storage_path TEXT NOT NULL,
    thumbnail_url TEXT NOT NULL,
    medium_url TEXT NOT NULL,
    full_url TEXT NOT NULL,
    watermarked_url TEXT NOT NULL,
    file_size INTEGER NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    mime_type TEXT NOT NULL,
    processing_status TEXT NOT NULL,
    processing_error TEXT,
    is_visible INTEGER NOT NULL DEFAULT 1,
    position INTEGER NOT NULL DEFAULT 0,
    view_count INTEGER NOT NULL DEFAULT 0,
    reaction_count INTEGER NOT NULL DEFAULT 0,
    purchase_count INTEGER NOT NULL DEFAULT 0,
    description TEXT,
    tags TEXT,
    capture_date TEXT,
    capture_time TEXT,
    gps_latitude REAL,
    gps_longitude REAL,
    zone TEXT,
    watermark_config TEXT,
    uploaded_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at TEXT,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES photographer_access(id) ON DELETE SET NULL
);

-- 6. Products
CREATE TABLE products (
    id TEXT PRIMARY KEY,
    sku TEXT UNIQUE NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    type TEXT NOT NULL CHECK(type IN ('DIGITAL', 'PRINT')),
    size TEXT,
    has_frame_option INTEGER DEFAULT 0,
    frame_price REAL,
    base_price REAL NOT NULL,
    currency TEXT DEFAULT 'EUR',
    is_active INTEGER DEFAULT 1,
    delivery_time_days INTEGER,
    stock_managed INTEGER DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_sku ON products(sku);

-- 7. Organization Product Commissions
CREATE TABLE organization_product_commissions (
    organization_id TEXT NOT NULL,
    product_id TEXT NOT NULL,
    commission_rate REAL NOT NULL DEFAULT 0.30,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (organization_id, product_id),
    FOREIGN KEY (organization_id) REFERENCES organizations(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 8. Orders
CREATE TABLE orders (
    id TEXT PRIMARY KEY,
    order_number TEXT UNIQUE NOT NULL,
    user_id TEXT,
    email TEXT NOT NULL,
    status TEXT DEFAULT 'PENDING' CHECK(status IN ('PENDING', 'PAID', 'PROCESSING', 'SHIPPED', 'DELIVERED', 'CANCELLED', 'REFUNDED')),
    payment_status TEXT DEFAULT 'PENDING' CHECK(payment_status IN ('PENDING', 'AUTHORIZED', 'PAID', 'FAILED', 'REFUNDED')),
    payment_method TEXT,
    payment_intent_id TEXT,
    subtotal REAL NOT NULL,
    shipping_cost REAL DEFAULT 0.00,
    tax_amount REAL DEFAULT 0.00,
    total_amount REAL NOT NULL,
    currency TEXT DEFAULT 'EUR',
    shipping_name TEXT,
    shipping_address_line1 TEXT,
    shipping_address_line2 TEXT,
    shipping_postal_code TEXT,
    shipping_city TEXT,
    shipping_country TEXT,
    shipping_phone TEXT,
    billing_name TEXT,
    billing_address_line1 TEXT,
    billing_address_line2 TEXT,
    billing_postal_code TEXT,
    billing_city TEXT,
    billing_country TEXT,
    notes TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at TEXT,
    shipped_at TEXT,
    delivered_at TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_orders_order_number ON orders(order_number);

-- 9. Order Items
CREATE TABLE order_items (
    id TEXT PRIMARY KEY,
    order_id TEXT NOT NULL,
    product_id TEXT NOT NULL,
    photo_id TEXT,
    quantity INTEGER NOT NULL,
    unit_price REAL NOT NULL,
    has_frame INTEGER NOT NULL DEFAULT 0,
    frame_price REAL,
    line_total REAL NOT NULL,
    product_sku TEXT NOT NULL,
    product_name TEXT NOT NULL,
    product_size TEXT,
    photo_url TEXT,
    notes TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE SET NULL
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_order_items_photo_id ON order_items(photo_id);

---

Notes d'implémentation

Migrations depuis l'existant

Pour migrer depuis le schéma actuel vers le schéma optimisé:

-- 1. Modifier users: retirer PHOTOGRAPHER de l'enum
-- Attention: SQLite ne supporte pas ALTER TABLE pour modifier CHECK
-- Solution: Créer nouvelle table, migrer données, renommer

-- 2. Ajouter colonnes à organizations
ALTER TABLE organizations ADD COLUMN iban TEXT;
ALTER TABLE organizations ADD COLUMN bic TEXT;
ALTER TABLE organizations ADD COLUMN billing_address_line1 TEXT;
ALTER TABLE organizations ADD COLUMN billing_address_line2 TEXT;
ALTER TABLE organizations ADD COLUMN billing_postal_code TEXT;
ALTER TABLE organizations ADD COLUMN billing_city TEXT;
ALTER TABLE organizations ADD COLUMN billing_country TEXT DEFAULT 'FR';
ALTER TABLE organizations ADD COLUMN vat_number TEXT;

-- 3. Ajouter password_hash à events
ALTER TABLE events ADD COLUMN password_hash TEXT;

-- 4. Simplifier photographer_access
-- Supprimer les colonnes: can_delete, can_upload, can_view_stats, custom_message
-- Solution: Créer nouvelle table, migrer données essentielles

-- 5. Ajouter colonnes métadonnées à photos
ALTER TABLE photos ADD COLUMN description TEXT;
ALTER TABLE photos ADD COLUMN tags TEXT;
ALTER TABLE photos ADD COLUMN capture_date TEXT;
ALTER TABLE photos ADD COLUMN capture_time TEXT;
ALTER TABLE photos ADD COLUMN gps_latitude REAL;
ALTER TABLE photos ADD COLUMN gps_longitude REAL;
ALTER TABLE photos ADD COLUMN zone TEXT;
ALTER TABLE photos ADD COLUMN watermark_config TEXT;

-- 6. Créer table organization_product_commissions
-- (voir script SQL complet ci-dessus)

-- 7. Ajouter adresses à orders
ALTER TABLE orders ADD COLUMN shipping_name TEXT;
ALTER TABLE orders ADD COLUMN shipping_address_line1 TEXT;
ALTER TABLE orders ADD COLUMN shipping_address_line2 TEXT;
ALTER TABLE orders ADD COLUMN shipping_postal_code TEXT;
ALTER TABLE orders ADD COLUMN shipping_city TEXT;
ALTER TABLE orders ADD COLUMN shipping_country TEXT;
ALTER TABLE orders ADD COLUMN shipping_phone TEXT;
ALTER TABLE orders ADD COLUMN billing_name TEXT;
ALTER TABLE orders ADD COLUMN billing_address_line1 TEXT;
ALTER TABLE orders ADD COLUMN billing_address_line2 TEXT;
ALTER TABLE orders ADD COLUMN billing_postal_code TEXT;
ALTER TABLE orders ADD COLUMN billing_city TEXT;
ALTER TABLE orders ADD COLUMN billing_country TEXT;

Calcul des commissions

Logique pour calculer la commission d'une commande:

-- Pour chaque order_item, récupérer le taux de commission
SELECT 
    oi.order_id,
    oi.line_total,
    e.organization_id,
    COALESCE(opc.commission_rate, 0.30) as commission_rate,
    oi.line_total * COALESCE(opc.commission_rate, 0.30) as commission_amount
FROM order_items oi
JOIN photos p ON oi.photo_id = p.id
JOIN events e ON p.event_id = e.id
LEFT JOIN organization_product_commissions opc 
    ON opc.organization_id = e.organization_id 
    AND opc.product_id = oi.product_id
WHERE oi.order_id = ?;

Recherche de photos

Exemples de requêtes pour la recherche (use cases 12-13):

-- Recherche temporelle (±15 minutes autour de 10:30)
SELECT * FROM photos
WHERE event_id = ?
  AND capture_time BETWEEN '10:15:00' AND '10:45:00'
ORDER BY capture_time;

-- Recherche par tags
SELECT * FROM photos
WHERE event_id = ?
  AND (tags LIKE '%finisher%' OR tags LIKE '%arrivee%')
  AND is_visible = 1;

-- Recherche par zone
SELECT * FROM photos
WHERE event_id = ?
  AND zone = 'arrivee'
  AND is_visible = 1;

-- Combinaison zone + heure
SELECT * FROM photos
WHERE event_id = ?
  AND zone = 'km10'
  AND capture_time BETWEEN '09:00:00' AND '12:00:00'
  AND is_visible = 1
ORDER BY capture_time;

---

Évolutions futures (V2)

Tables additionnelles potentielles

1. Réactions emojis (V2):

CREATE TABLE photo_reactions (
    id TEXT PRIMARY KEY,
    photo_id TEXT NOT NULL,
    emoji TEXT NOT NULL,  -- heart, thumbs_up, fire, etc.
    session_id TEXT,       -- Pour users non connectés
    user_id TEXT,          -- Pour users connectés
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

2. Commission tracking (V2):

CREATE TABLE commission_payments (
    id TEXT PRIMARY KEY,
    organization_id TEXT NOT NULL,
    period_start TEXT NOT NULL,
    period_end TEXT NOT NULL,
    total_sales REAL NOT NULL,
    commission_amount REAL NOT NULL,
    status TEXT DEFAULT 'PENDING',  -- PENDING, PAID
    paid_at TEXT,
    payment_reference TEXT,
    FOREIGN KEY (organization_id) REFERENCES organizations(id)
);

3. FTP access (V2):

ALTER TABLE photographer_access ADD COLUMN ftp_enabled INTEGER DEFAULT 0;
ALTER TABLE photographer_access ADD COLUMN ftp_username TEXT;
ALTER TABLE photographer_access ADD COLUMN ftp_path TEXT;

---

Dernière mise à jour: 18 février 2025