# 📊 Structure Base de Données FailDaily

**Date de génération**: 25 novembre 2025  
**Base de données**: MySQL 8.0  
**Environnement**: Production

---

## 📋 Tables Principales

### 👤 Gestion Utilisateurs

#### `users`
Table principale des utilisateurs (authentification)
- `id` (CHAR(36)) - UUID
- `email` (VARCHAR(255)) - Unique
- `password` (VARCHAR(255)) - Hash bcrypt
- `role` (ENUM) - user, moderator, admin, super_admin
- `email_confirmed` (TINYINT)
- `created_at`, `updated_at`

#### `profiles`
Profils publics des utilisateurs
- `id` (INT) - Auto-increment
- `user_id` (CHAR(36)) - FK → users.id
- `display_name` (VARCHAR(100))
- `bio` (TEXT)
- `avatar_url` (VARCHAR(500))
- `country_code` (CHAR(2))
- `birth_date` (DATE)
- `created_at`, `updated_at`

#### `user_privacy_settings`
Paramètres de confidentialité
- `id` (CHAR(36))
- `user_id` (CHAR(36)) - FK → users.id
- `profile_visibility` (ENUM) - public, friends, private
- `show_email` (BOOLEAN)
- `show_location` (BOOLEAN)
- `allow_messages` (BOOLEAN)
- `created_at`, `updated_at`

---

### 💔 Gestion des Fails

#### `fails`
Publications de fails
- `id` (CHAR(36)) - UUID
- `user_id` (CHAR(36)) - FK → users.id
- `title` (VARCHAR(200))
- `description` (TEXT)
- `category` (VARCHAR(50))
- `country_code` (CHAR(2))
- `is_anonyme` (TINYINT)
- `image_url` (VARCHAR(500))
- `created_at`, `updated_at`

#### `reactions`
Réactions sur les fails (courage, empathy, laugh, support)
- `id` (INT) - Auto-increment
- `fail_id` (CHAR(36)) - FK → fails.id
- `user_id` (CHAR(36)) - FK → users.id
- `reaction_type` (ENUM) - courage, empathy, laugh, support
- `created_at`
- **Index unique**: (fail_id, user_id) - 1 seule réaction par user/fail

#### `comments`
Commentaires sur les fails
- `id` (CHAR(36)) - UUID
- `fail_id` (CHAR(36)) - FK → fails.id
- `user_id` (CHAR(36)) - FK → users.id
- `content` (TEXT)
- `parent_comment_id` (CHAR(36)) - Pour réponses
- `created_at`, `updated_at`

#### `comment_reactions`
Réactions sur commentaires
- `id` (INT)
- `comment_id` (CHAR(36)) - FK → comments.id
- `user_id` (CHAR(36)) - FK → users.id
- `reaction_type` (ENUM)
- `created_at`

---

### 🛡️ Modération

#### `fail_moderation`
État de modération des fails
- `id` (INT)
- `fail_id` (CHAR(36)) - FK → fails.id
- `moderator_id` (CHAR(36)) - FK → users.id
- `status` (ENUM) - pending, approved, rejected, hidden
- `reason` (TEXT)
- `created_at`, `updated_at`

#### `fail_reports`
Signalements de fails
- `id` (INT)
- `fail_id` (CHAR(36)) - FK → fails.id
- `reporter_id` (CHAR(36)) - FK → users.id
- `reason` (VARCHAR(255))
- `description` (TEXT)
- `status` (ENUM) - pending, reviewed, dismissed
- `created_at`

#### `comment_moderation`
Modération des commentaires
- Similaire à fail_moderation

#### `comment_reports`
Signalements de commentaires
- Similaire à fail_reports

---

### 🏆 Gamification

#### `badge_definitions`
Définitions des badges disponibles
- `id` (INT)
- `name` (VARCHAR(100))
- `description` (TEXT)
- `icon_url` (VARCHAR(255))
- `requirement_type` (VARCHAR(50))
- `requirement_value` (INT)
- `created_at`

#### `user_badges`
Badges obtenus par utilisateurs
- `id` (INT)
- `user_id` (CHAR(36)) - FK → users.id
- `badge_id` (INT) - FK → badge_definitions.id
- `earned_at` (TIMESTAMP)

#### `user_points`
Points de courage des utilisateurs
- `user_id` (CHAR(36)) - PK, FK → users.id
- `total_points` (INT)
- `streak` (INT) - Jours consécutifs
- `last_activity` (DATE)
- `updated_at`

#### `user_point_events`
Historique attribution points
- `id` (INT)
- `user_id` (CHAR(36)) - FK → users.id
- `points` (INT)
- `reason` (VARCHAR(255))
- `created_at`

---

### 🔔 Notifications Push

#### `user_push_tokens`
Tokens FCM pour notifications
- `id` (INT)
- `user_id` (CHAR(36)) - FK → users.id
- `token` (VARCHAR(255))
- `platform` (ENUM) - web, android, ios
- `created_at`, `updated_at`

#### `push_errors`
Erreurs d'envoi de notifications
- `id` (INT)
- `user_id` (CHAR(36))
- `token` (VARCHAR(255))
- `error_message` (TEXT)
- `error_code` (VARCHAR(50))
- `created_at`

---

### 📧 Gestion Emails

#### `email_verification_tokens`
Tokens de vérification email
- `id` (INT)
- `user_id` (CHAR(36)) - FK → users.id
- `token` (VARCHAR(255))
- `expires_at` (DATETIME)
- `created_at`

#### `password_reset_tokens`
Tokens de réinitialisation mot de passe
- `id` (INT)
- `user_id` (CHAR(36)) - FK → users.id
- `token` (VARCHAR(255))
- `expires_at` (DATETIME)
- `created_at`

---

### 📜 Légal & Conformité

#### `user_consents`
Consentements utilisateurs (RGPD)
- `id` (INT)
- `user_id` (CHAR(36)) - FK → users.id
- `consent_type` (VARCHAR(50)) - terms, privacy, marketing
- `version` (VARCHAR(20))
- `granted` (TINYINT)
- `created_at`

#### `legal_documents`
Documents légaux (CGU, Politique confidentialité)
- `id` (INT)
- `type` (VARCHAR(50))
- `version` (VARCHAR(20))
- `content` (LONGTEXT)
- `effective_date` (DATE)
- `created_at`

#### `parental_consents`
Consentements parentaux (mineurs)
- `id` (INT)
- `user_id` (CHAR(36)) - FK → users.id
- `parent_email` (VARCHAR(255))
- `consent_token` (VARCHAR(255))
- `status` (ENUM) - pending, approved, rejected
- `created_at`, `updated_at`

---

### 💬 Support

#### `support_messages`
Messages support utilisateur
- `id` (CHAR(36))
- `user_id` (CHAR(36)) - FK → users.id
- `subject` (VARCHAR(255))
- `message` (TEXT)
- `status` (ENUM) - open, in_progress, resolved, closed
- `priority` (ENUM) - low, medium, high, urgent
- `created_at`, `updated_at`

---

### 📊 Logs & Monitoring

#### `activity_logs`
Logs d'activité utilisateur
- `id` (BIGINT)
- `user_id` (CHAR(36))
- `action` (VARCHAR(100))
- `resource_type` (VARCHAR(50))
- `resource_id` (VARCHAR(255))
- `details` (JSON)
- `ip_address` (VARCHAR(45))
- `user_agent` (TEXT)
- `created_at`

#### `error_logs`
Logs d'erreurs applicatives
- `id` (BIGINT)
- `user_id` (CHAR(36))
- `error_type` (VARCHAR(100))
- `error_message` (TEXT)
- `stack_trace` (TEXT)
- `request_url` (VARCHAR(500))
- `created_at`

#### `system_logs`
Logs système
- `id` (BIGINT)
- `level` (ENUM) - info, warning, error, critical
- `category` (VARCHAR(100))
- `message` (TEXT)
- `metadata` (JSON)
- `created_at`

#### `request_logs`
Logs requêtes HTTP
- `id` (BIGINT)
- `user_id` (CHAR(36))
- `method` (VARCHAR(10))
- `path` (VARCHAR(500))
- `status_code` (INT)
- `response_time` (INT) - ms
- `ip_address` (VARCHAR(45))
- `created_at`

---

### ⚙️ Configuration

#### `app_config`
Configuration application
- `id` (INT)
- `key` (VARCHAR(100)) - Unique
- `value` (TEXT)
- `description` (TEXT)
- `updated_at`

#### `user_preferences`
Préférences utilisateur
- `id` (INT)
- `user_id` (CHAR(36)) - FK → users.id
- `theme` (VARCHAR(20))
- `language` (VARCHAR(10))
- `notifications_enabled` (TINYINT)
- `created_at`, `updated_at`

---

## 🔗 Relations Principales

```
users (1) ──┬─ (N) profiles
            ├─ (N) fails
            ├─ (N) reactions
            ├─ (N) comments
            ├─ (N) user_badges
            ├─ (N) user_push_tokens
            └─ (1) user_points

fails (1) ──┬─ (N) reactions
            ├─ (N) comments
            ├─ (N) fail_reports
            └─ (1) fail_moderation

comments (1) ──┬─ (N) comment_reactions
               ├─ (N) comment_reports
               └─ (1) comment_moderation

badge_definitions (1) ── (N) user_badges
```

---

## 📈 Statistiques

**Total Tables**: 38  
**Tables principales**: 8  
**Tables de relation**: 6  
**Tables de logs**: 5  
**Tables de modération**: 4  

---

## 🔒 Sécurité

- **UUID** pour identifiants utilisateurs (évite énumération)
- **Indexes** sur toutes les foreign keys
- **Soft deletes** via champs `deleted_at` (où applicable)
- **Timestamps** systématiques (created_at, updated_at)
- **Contraintes** UNIQUE sur (fail_id, user_id) pour réactions

---

## 📝 Notes Importantes

1. **Colonne `allow_comments` SUPPRIMÉE** de la table `fails` - Les commentaires sont toujours activés
2. **Catégories valides** (français) : courage, humour, entraide, perseverance, special, travail, sport, cuisine, transport, technologie, relations, finances, bricolage, apprentissage, santé, voyage, maison, nature, culture
3. **Types de réactions** : courage, empathy, laugh, support
4. **Rôles utilisateurs** : user, moderator, admin, super_admin

---

**Fichiers générés** :
- `database-schema.sql` - Structure SQL complète
- `database-structure-detailed.txt` - Détails tables avec counts