# Modèle Physique de Données — SchotzimmO

> **Source de vérité physique** des bases de données SchotzimmO. Document **transversal** à toute la plateforme. Ce document complète le modèle conceptuel (diagrammes ER des `design.md`) en décrivant le **détail physique** : tables, colonnes, types MariaDB, nullabilité, clés, index, relations et règles.
>
> **Architecture à deux niveaux de base** (voir `socle-plateforme/design.md`) :
> - **Base Socle** (centrale, unique) : authentification, référentiel des réseaux et leurs informations de connexion, abonnements, catalogue des modules/packs, administration, KPI d'usage remontés par réseau. **Aucune donnée métier produite par les mandataires.**
> - **Base Réseau** (une par réseau) : toutes les données métier cloisonnées d'un réseau — profil mandataire, parrainage/hiérarchie, et les données des Modules (Carnet d'adresses, KMS, CRM, Agenda, Facturation…).
>
> Le document grandit **module par module** : les sections Socle et Réseau sont posées en premier, puis une sous-section Base Réseau est ajoutée pour chaque Module.

---

## Conventions générales

- **SGBD** : MariaDB / MySQL, via le provider EF Core `Pomelo.EntityFrameworkCore.MySql`.
- **Moteur & encodage** : toutes les tables en **InnoDB**, charset **`utf8mb4`**, collation **`utf8mb4_unicode_ci`**.
- **Nommage** : tables en **`snake_case` pluriel** ; colonnes en **`snake_case`**. Mapping entité C# (PascalCase) vers table/colonne assuré par EF Core.
- **Clé primaire** : colonne **`id`**, type **`INT UNSIGNED`**, **`AUTO_INCREMENT`**, sauf exception documentée (ex. `mandataire_profiles.account_id` réutilise l'identifiant du socle).
- **Clés étrangères** : nommées **`<entité>_id`**. Toute FK **au sein d'une même base** est une vraie contrainte FK indexée. Les références **entre bases différentes** (ex. `account_id` du socle utilisé dans une base réseau) ne sont **pas** des contraintes FK MariaDB : ce sont des **références logiques** (mêmes valeurs d'identifiant), dont la cohérence est assurée **applicativement**.
- **Référence inter-bases** : `account_id` est généré par la Base Socle (`accounts.id`) et **réutilisé tel quel** comme identifiant du mandataire dans sa Base Réseau. Il n'y a jamais de jointure SQL traversant deux bases.
- **Dates & heures** : type **`DATETIME`**, valeurs stockées en **UTC** ; horodatage de création par défaut `CURRENT_TIMESTAMP`.
- **Chaînes** : **`VARCHAR(n)`** dimensionné (e-mail 255, nom/prénom 100…) ; **`TEXT`** pour le texte libre long.
- **Booléens** : **`TINYINT(1)`** (0/1), mappés sur `bool` C#.
- **Énumérations** : **`VARCHAR`** à valeurs contraintes applicativement (ex. `role`, `status`), plutôt que le type `ENUM` natif.
- **Montants** : **`DECIMAL(12,2)`**. Distances/quantités : `DECIMAL` dimensionné selon le Module.
- **Secrets** : mots de passe et jetons stockés **hachés**. **Aucun secret de connexion** (chaîne de connexion d'une base réseau) n'est stocké en clair en base : la Base Socle ne stocke qu'une **clé logique** de connexion, résolue vers un secret fourni par configuration.
- **Suppressions** : par défaut physiques. Les cas de conservation (rétrogradation d'abonnement) reposent sur la présence des données et l'état d'accès calculé.

### Légende des tableaux de colonnes

- **Clé** : `PK` = clé primaire, `FK` = clé étrangère (même base), `FK*` = référence logique inter-bases (non contrainte), `UK` = unicité.
- **Null** : `NON` = `NOT NULL`, `OUI` = nullable.

---

# PARTIE A — Base Socle (centrale, unique)

Référence : `socle-plateforme/design.md`. Cette base ne contient aucune donnée métier produite par les mandataires.

Vue d'ensemble :

| Table | Rôle |
|---|---|
| `networks` | Référentiel des réseaux + clé logique de connexion à leur base réseau + paramétrage |
| `accounts` | Comptes (authentification minimale) ; pivot d'identité, rattaché à un réseau |
| `packs` | Formules d'abonnement (Starter / Pro / Premium) |
| `module_catalog` | Catalogue des modules et pack requis pour chacun |
| `subscriptions` | Abonnement courant d'un compte à un pack |
| `password_resets` | Jetons de réinitialisation de mot de passe (hachés) |
| `network_usage_kpis` | Indicateurs d'usage remontés par chaque réseau (pas de donnée nominative) |

---

### Table `networks`

Référentiel des réseaux. Chaque réseau pointe vers sa propre base de données via une **clé logique de connexion** et porte son paramétrage de personnalisation.

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant du réseau |
| `code` | `VARCHAR(40)` | NON | UK | — | Code stable du réseau (ex. `iad`, `demo`) |
| `name` | `VARCHAR(100)` | NON | UK | — | Libellé affiché (ex. « IAD ») |
| `db_connection_key` | `VARCHAR(60)` | NON | UK | — | Clé logique résolvant la chaîne de connexion de la base réseau via la configuration (jamais la chaîne elle-même) |
| `theme_settings` | `TEXT` | OUI | — | `NULL` | Paramétrage de personnalisation (thème, libellés) au format JSON |
| `is_active` | `TINYINT(1)` | NON | — | `1` | Réseau ouvert à l'inscription si actif et initialisé |
| `is_provisioned` | `TINYINT(1)` | NON | — | `0` | Base réseau initialisée (schéma + données de référence) |
| `created_at` | `DATETIME` | NON | — | `CURRENT_TIMESTAMP` | Date de création (UTC) |

**Index** : `PRIMARY (id)`, `UNIQUE (code)`, `UNIQUE (name)`, `UNIQUE (db_connection_key)`.
**Relations** : `1 — N` vers `accounts.network_id` (même base). Référencé logiquement par toutes les tables des bases réseau.
**Données de référence (seed)** : `iad` (« IAD ») et `demo` (réseau de démonstration / tests).
**Règles** : un réseau n'est ouvert à l'inscription que si `is_active = 1` ET `is_provisioned = 1` (onboarding réseau mené par l'Administrateur au préalable).

---

### Table `accounts`

Comptes des mandataires — **authentification minimale uniquement**. Le profil métier et le parrainage vivent dans la Base Réseau (`mandataire_profiles`). L'`id` est réutilisé comme `account_id` côté réseau.

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant du compte ; réutilisé comme `account_id` dans la base réseau |
| `network_id` | `INT UNSIGNED` | NON | FK | — | Réseau de rattachement (non modifiable après création) |
| `email` | `VARCHAR(255)` | NON | UK | — | Adresse e-mail (login), normalisée en minuscules. Unique sur toute la plateforme |
| `password_hash` | `VARCHAR(255)` | NON | — | — | Empreinte du mot de passe (salée) ; jamais en clair |
| `role` | `VARCHAR(20)` | NON | — | `'mandataire'` | Rôle : `mandataire` ou `admin` |
| `is_active` | `TINYINT(1)` | NON | — | `1` | Compte actif (désactivation = `0`) |
| `created_at` | `DATETIME` | NON | — | `CURRENT_TIMESTAMP` | Date de création (UTC) |

**Index** : `PRIMARY (id)`, `UNIQUE (email)`, `INDEX (network_id)`.
**Relations** : `network_id` vers `networks.id` (même base). `id` référencé logiquement par `mandataire_profiles.account_id` dans la base réseau.
**Règles** :
- L'e-mail est unique sur l'ensemble de la plateforme (un e-mail = un seul compte, un seul réseau).
- Un rôle `admin` est un administrateur SchotzimmO (transverse) ; il n'est pas rattaché aux données métier d'un réseau.
- La désactivation d'un compte doit être propagée applicativement vers le profil réseau correspondant.

---

### Table `packs`

Formules d'abonnement. Trois packs ordonnés par niveau, **inclusifs**.

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant du pack |
| `pack_key` | `VARCHAR(20)` | NON | UK | — | Clé stable : `starter`, `pro`, `premium` |
| `label` | `VARCHAR(50)` | NON | — | — | Libellé affiché |
| `level` | `TINYINT UNSIGNED` | NON | UK | — | Niveau d'inclusion : `0` < `1` < `2` |
| `is_free` | `TINYINT(1)` | NON | — | `0` | Pack gratuit (vrai pour Starter) |

**Index** : `PRIMARY (id)`, `UNIQUE (pack_key)`, `UNIQUE (level)`.
**Données de référence (seed)** : `starter` (level 0, gratuit), `pro` (level 1), `premium` (level 2).

---

### Table `module_catalog`

Catalogue des modules et pack minimal requis.

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant de l'entrée catalogue |
| `module_key` | `VARCHAR(40)` | NON | UK | — | Clé stable du module (ex. `carnet-adresses`, `kms`) |
| `label` | `VARCHAR(80)` | NON | — | — | Libellé affiché |
| `required_pack_id` | `INT UNSIGNED` | NON | FK | — | Pack minimal débloquant le module |
| `is_active` | `TINYINT(1)` | NON | — | `1` | Module proposé/activé sur la plateforme |

**Index** : `PRIMARY (id)`, `UNIQUE (module_key)`, `INDEX (required_pack_id)`.
**Relations** : `required_pack_id` vers `packs.id`.
**Données de référence (seed)** : `carnet-adresses`, `kms`, `facturation` vers `starter` ; `crm`, `agenda` vers `pro`.

---

### Table `subscriptions`

Abonnement d'un compte à un pack. Un seul abonnement actif par compte.

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant de l'abonnement |
| `account_id` | `INT UNSIGNED` | NON | FK | — | Compte abonné (même base socle) |
| `pack_id` | `INT UNSIGNED` | NON | FK | — | Pack souscrit |
| `starts_at` | `DATETIME` | NON | — | `CURRENT_TIMESTAMP` | Début d'effet (UTC) |
| `ends_at` | `DATETIME` | OUI | — | `NULL` | Fin d'effet (UTC) ; `NULL` = sans échéance |
| `status` | `VARCHAR(10)` | NON | — | `'actif'` | `actif` ou `expire` |

**Index** : `PRIMARY (id)`, `INDEX (account_id)`, `INDEX (pack_id)`, `INDEX (account_id, status)`.
**Relations** : `account_id` vers `accounts.id` ; `pack_id` vers `packs.id` (même base).
**Règles** : au plus un abonnement `actif` par compte ; rétrogradation conserve les données réseau (consultation seule déduite de la présence de données).

---

### Table `password_resets`

Jetons de réinitialisation de mot de passe, stockés **hachés**, à usage unique et durée de vie limitée.

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant du jeton |
| `account_id` | `INT UNSIGNED` | NON | FK | — | Compte concerné |
| `token_hash` | `VARCHAR(255)` | NON | — | — | Empreinte du jeton |
| `expires_at` | `DATETIME` | NON | — | — | Échéance d'expiration (UTC) |
| `used` | `TINYINT(1)` | NON | — | `0` | Jeton déjà consommé |
| `created_at` | `DATETIME` | NON | — | `CURRENT_TIMESTAMP` | Date de création (UTC) |

**Index** : `PRIMARY (id)`, `INDEX (account_id)`, `INDEX (token_hash)`.
**Relations** : `account_id` vers `accounts.id`.

---

### Table `network_usage_kpis`

Indicateurs d'usage **remontés par chaque réseau** vers le socle pour les tableaux de bord d'administration. Contient des **agrégats non nominatifs** (jamais de donnée métier individuelle ; pas de jointure inter-bases).

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant de la mesure |
| `network_id` | `INT UNSIGNED` | NON | FK | — | Réseau concerné |
| `metric_key` | `VARCHAR(60)` | NON | — | — | Clé de l'indicateur (ex. `nb_mandataires_actifs`, `ca_total`) |
| `metric_value` | `DECIMAL(14,2)` | NON | — | — | Valeur agrégée |
| `period` | `VARCHAR(7)` | OUI | — | `NULL` | Période éventuelle au format `AAAA-MM` |
| `collected_at` | `DATETIME` | NON | — | `CURRENT_TIMESTAMP` | Date de remontée (UTC) |

**Index** : `PRIMARY (id)`, `INDEX (network_id)`, `INDEX (network_id, metric_key, period)`.
**Relations** : `network_id` vers `networks.id`.
**Règles** : alimentée par un processus de remontée par réseau ; ne contient aucune donnée individuelle de mandataire.

---

## Diagramme physique — Base Socle

```mermaid
erDiagram
    networks ||--o{ accounts : "network_id"
    networks ||--o{ network_usage_kpis : "network_id"
    accounts ||--|| subscriptions : "account_id"
    packs ||--o{ subscriptions : "pack_id"
    packs ||--o{ module_catalog : "required_pack_id"
    accounts ||--o{ password_resets : "account_id"
```

---

# PARTIE B — Base Réseau (une par réseau)

Référence : `socle-plateforme/design.md`. Chaque réseau dispose d'une instance de ce schéma. Toutes les tables sont **implicitement cloisonnées** : la base entière appartient à un seul réseau, il n'y a donc **pas** de colonne `network_id` dans les tables réseau (le cloisonnement est physique). Les identifiants `account_id` proviennent de la Base Socle (`FK*`, référence logique).

Vue d'ensemble (Socle réseau ; les Modules ajouteront leurs tables) :

| Table | Rôle |
|---|---|
| `mandataire_profiles` | Profil métier du mandataire ; pivot du parrainage (auto-référence `sponsor_account_id`) |
| `declared_members` | Membres déclarés (parrain/filleul) non encore inscrits |
| `affiliation_requests` | Demandes de confirmation d'affiliation entre mandataires inscrits |

---

### Table `mandataire_profiles`

Profil métier d'un mandataire au sein de son réseau. La clé primaire **réutilise l'`account_id`** de la Base Socle (pas d'auto-incrément). Porte l'**arbre de parrainage** via l'auto-référence `sponsor_account_id`.

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `account_id` | `INT UNSIGNED` | NON | PK, FK* | — | Identifiant du compte (provient de `accounts.id` du socle) |
| `first_name` | `VARCHAR(100)` | NON | — | — | Prénom |
| `last_name` | `VARCHAR(100)` | NON | — | — | Nom |
| `network_member_id` | `VARCHAR(50)` | NON | — | — | Identifiant du mandataire propre à son réseau (obligatoire) |
| `legal_status` | `VARCHAR(30)` | OUI | — | `NULL` | Statut juridique (ex. `auto-entrepreneur`, `eirl`) |
| `siren` | `VARCHAR(14)` | OUI | — | `NULL` | Numéro SIREN/RSAC, optionnel |
| `sponsor_account_id` | `INT UNSIGNED` | OUI | FK | `NULL` | Parrain (auto-référence vers `mandataire_profiles.account_id`) ; `NULL` = racine |
| `created_at` | `DATETIME` | NON | — | `CURRENT_TIMESTAMP` | Date de création (UTC) |

**Index** : `PRIMARY (account_id)`, `UNIQUE (network_member_id)`, `INDEX (sponsor_account_id)`.
**Relations** :
- `account_id` : référence logique vers `accounts.id` (Base Socle), non contrainte en SQL.
- `sponsor_account_id` vers `mandataire_profiles.account_id` (auto-référence, même base réseau) : **vraie FK**, intégrité du parrainage garantie par la base.

**Règles** :
- Comme la base est propre au réseau, parrain et filleul sont **nécessairement** du même réseau (cloisonnement physique).
- Absence de cycle vérifiée applicativement à l'écriture (parcours ascendant).
- Au plus un `sponsor_account_id` validé par mandataire.

---

### Table `declared_members`

Membres déclarés dans l'arbre de parrainage mais **non encore inscrits**. Sans donnée métier. Convertis en `mandataire_profiles` lors du Rapprochement_Email à l'inscription (le compte étant alors créé côté socle).

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant du membre déclaré |
| `email` | `VARCHAR(255)` | NON | — | — | E-mail normalisé, clé de Rapprochement_Email |
| `first_name` | `VARCHAR(100)` | OUI | — | `NULL` | Prénom déclaré (si connu) |
| `last_name` | `VARCHAR(100)` | OUI | — | `NULL` | Nom déclaré (si connu) |
| `declared_by_account_id` | `INT UNSIGNED` | NON | FK | — | Mandataire ayant déclaré (référence `mandataire_profiles.account_id`) |
| `relation` | `VARCHAR(10)` | NON | — | — | Lien déclaré : `parrain` ou `filleul` |
| `status` | `VARCHAR(10)` | NON | — | `'declare'` | `declare` (supposé) ou `invite` (invitation envoyée) |
| `created_at` | `DATETIME` | NON | — | `CURRENT_TIMESTAMP` | Date de création (UTC) |

**Index** : `PRIMARY (id)`, `INDEX (declared_by_account_id)`, `INDEX (email)`.
**Relations** : `declared_by_account_id` vers `mandataire_profiles.account_id` (même base).
**Règles** : clôturé/supprimé lors de la conversion en `mandataire_profiles` par Rapprochement_Email.

---

### Table `affiliation_requests`

Demandes de **confirmation d'affiliation** entre deux mandataires déjà inscrits (cas de « perte de lien »).

| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
| `id` | `INT UNSIGNED` | NON | PK | auto | Identifiant de la demande |
| `requester_account_id` | `INT UNSIGNED` | NON | FK | — | Mandataire initiant la demande |
| `target_account_id` | `INT UNSIGNED` | NON | FK | — | Mandataire dont l'approbation est requise |
| `proposed_relation` | `VARCHAR(10)` | NON | — | — | Lien proposé : `parrain` ou `filleul` |
| `status` | `VARCHAR(12)` | NON | — | `'en_attente'` | `en_attente`, `approuvee` ou `refusee` |
| `created_at` | `DATETIME` | NON | — | `CURRENT_TIMESTAMP` | Date de création (UTC) |
| `resolved_at` | `DATETIME` | OUI | — | `NULL` | Date d'approbation/refus (UTC) |

**Index** : `PRIMARY (id)`, `INDEX (requester_account_id)`, `INDEX (target_account_id)`, `INDEX (status)`.
**Relations** : `requester_account_id` et `target_account_id` vers `mandataire_profiles.account_id` (même base).
**Règles** : l'approbation ne doit pas créer de cycle (vérifié à la résolution). Le cloisonnement réseau est garanti par construction (base propre au réseau).

---

## Diagramme physique — Base Réseau (socle réseau)

```mermaid
erDiagram
    mandataire_profiles ||--o| mandataire_profiles : "sponsor_account_id"
    mandataire_profiles ||--o{ declared_members : "declared_by_account_id"
    mandataire_profiles ||--o{ affiliation_requests : "requester / target"
```

> **Note inter-bases** : `mandataire_profiles.account_id` correspond à `accounts.id` de la Base Socle (référence logique `FK*`, jamais une FK SQL ni une jointure traversant deux bases).

---

## Sections à venir (ajoutées au fil des Modules — tables de la Base Réseau)

- **Carnet d'adresses** *(à venir)*
- **KMS** *(à venir)*
- **CRM** *(à venir)*
- **Agenda** *(à venir)*
- **Facturation** *(à venir)*