# Pack de reconstitution — indicateurs d'usage Efektiv Académie

*Réponse à la question : « n'y a-t-il vraiment aucun moyen de retrouver téléchargements, connexions, vues ? »*
*Analyse du 05/08/2026. Tout ce qui suit est en **lecture seule** (SELECT SQL, lecture de logs) — aucune modification.*

## Verdict d'ensemble

| Indicateur demandé | Reconstituable ? | Source | Profondeur historique |
|---|---|---|---|
| **Nombre de connexions par utilisateur** | ✅ Oui (approximation basse) | Table `personal_access_tokens` (1 token créé par login) | Depuis la création¹ |
| **Dernière activité par utilisateur** | ✅ Oui | `personal_access_tokens.last_used_at` | État actuel |
| **« Vues » : activité datée par leçon/module** | ✅ Partiellement | `learner_lessons.created_at` (chaque validation de leçon est datée !) | Depuis la création |
| **One-shot vs récurrent** | ✅ Proxy solide | Nb de **jours actifs distincts** par apprenant (validations de leçons + quiz + complétions datées) | Depuis la création |
| **Dates réelles de complétion (nominatif)** | ✅ Oui | `completed_courses.completed_at` (en base, jamais exposé par l'API admin) | Depuis la création |
| **Téléchargements de ressources** | ⚠️ Partiellement | **Logs d'accès Apache** du serveur (VPS Ubuntu/Apache 2.4.52) | Fenêtre de rétention des logs seulement (souvent 14 j à quelques mois selon logrotate) |
| **Téléchargements de certificats** | ⚠️ Partiellement mais **attribuable** | Logs Apache : l'URL `/learner/certificates/download/{id}` contient l'id → nominatif | Fenêtre de rétention |
| **Visites / sessions globales du site** | ✅ Probablement | **Sentry** (intégré au front : suivi de sessions anonymes actif par défaut) + logs Apache du front | Selon plan Sentry (90 j typiquement) |
| **Temps passé, parcours de navigation** | ❌ Non | Rien ne l'enregistre | — |

¹ *Approximation basse : les tokens sont tous supprimés quand l'utilisateur clique « déconnexion ». Comme la plupart ne se déconnectent jamais, l'historique est largement conservé — mais c'est un plancher, pas un compte exact.*

**La découverte clé : `learner_lessons.created_at`.** Chaque validation de leçon est horodatée en base depuis le début. Cette donnée n'est exploitée nulle part dans le dashboard, mais elle permet de reconstituer nominativement : qui a travaillé quel module, quels jours, en combien de sessions distinctes → c'est exactement le « one-shot vs récurrent » demandé par Pierre (pour les apprenants qui valident leurs leçons).

---

## A. Requêtes SQL prêtes à l'emploi (lecture seule)

À exécuter sur la base MySQL de production (accès DBA/phpMyAdmin/tunnel SSH). Aucune écriture.

```sql
-- A1. CONNEXIONS PAR UTILISATEUR (plancher) + dernière activité
SELECT u.id,
       CONCAT(u.first_name,' ',u.last_name)          AS apprenant,
       u.email,
       COUNT(t.id)                                    AS nb_connexions_min,
       MIN(t.created_at)                              AS premiere_connexion_connue,
       MAX(COALESCE(t.last_used_at, t.created_at))    AS derniere_activite
FROM users u
LEFT JOIN personal_access_tokens t
       ON t.tokenable_type = 'App\\Models\\User' AND t.tokenable_id = u.id
GROUP BY u.id, apprenant, u.email
ORDER BY nb_connexions_min DESC;
```

```sql
-- A2. ONE-SHOT vs RÉCURRENT : jours d'activité distincts par apprenant
SELECT ll.learner_id,
       CONCAT(u.first_name,' ',u.last_name)   AS apprenant,
       COUNT(*)                                AS lecons_validees,
       COUNT(DISTINCT DATE(ll.created_at))     AS jours_actifs_distincts,   -- 1 = one-shot, >1 = récurrent
       MIN(ll.created_at)                      AS premiere_activite,
       MAX(ll.created_at)                      AS derniere_activite
FROM learner_lessons ll
JOIN users u ON u.id = ll.learner_id
GROUP BY ll.learner_id, apprenant
ORDER BY jours_actifs_distincts DESC, lecons_validees DESC;
```

```sql
-- A3. ACTIVITÉ DATÉE PAR MODULE (heatmap module × jour)
SELECT c.name                                  AS module,
       DATE(ll.created_at)                     AS jour,
       COUNT(*)                                AS lecons_validees,
       COUNT(DISTINCT ll.learner_id)           AS apprenants_actifs
FROM learner_lessons ll
JOIN lessons  l ON l.id = ll.lesson_id
JOIN sections s ON s.id = l.section_id
JOIN courses  c ON c.id = s.course_id
GROUP BY c.name, DATE(ll.created_at)
ORDER BY jour, module;
```

```sql
-- A4. COMPLÉTIONS NOMINATIVES AVEC DATE RÉELLE (jamais exposé par l'API)
SELECT cc.completed_at,
       CONCAT(u.first_name,' ',u.last_name)    AS apprenant,
       u.email,
       c.name                                   AS module,
       cc.certificate_path IS NOT NULL          AS certificat
FROM completed_courses cc
JOIN users   u ON u.id = cc.learner_id
JOIN courses c ON c.id = cc.course_id
ORDER BY cc.completed_at;
```

```sql
-- A5. DÉTAIL NOMINATIF PAR APPRENANT : quelles leçons, quand
SELECT CONCAT(u.first_name,' ',u.last_name)    AS apprenant,
       c.name AS module, l.name AS lecon,
       ll.created_at                            AS validee_le
FROM learner_lessons ll
JOIN users    u ON u.id = ll.learner_id
JOIN lessons  l ON l.id = ll.lesson_id
JOIN sections s ON s.id = l.section_id
JOIN courses  c ON c.id = s.course_id
ORDER BY apprenant, ll.created_at;
```

```sql
-- A6. ACTIVITÉ QUIZ DATÉE (dernière tentative uniquement — les précédentes sont écrasées)
SELECT qs.learner_id,
       CONCAT(u.first_name,' ',u.last_name)    AS apprenant,
       q.name                                   AS quiz,
       MAX(qs.created_at)                       AS derniere_tentative
FROM quiz_scores qs
JOIN users   u ON u.id = qs.learner_id
JOIN quizzes q ON q.id = qs.quiz_id
GROUP BY qs.learner_id, apprenant, q.name
ORDER BY derniere_tentative DESC;
```

```sql
-- A7. PART DE L'HISTORIQUE PORTÉE PAR DES COMPTES SUPPRIMÉS (contrôle de cohérence)
SELECT COUNT(*) AS inscriptions_orphelines
FROM enrollments e LEFT JOIN users u ON u.id = e.learner_id
WHERE u.id IS NULL;
SELECT COUNT(*) AS completions_orphelines
FROM completed_courses cc LEFT JOIN users u ON u.id = cc.learner_id
WHERE u.id IS NULL;
```

```sql
-- A8. (contrôle) La table sessions Laravel est-elle alimentée ?
SELECT COUNT(*) AS lignes, MAX(last_activity) AS derniere FROM sessions;
```

## B. Logs Apache du VPS (SSH, lecture seule) — la seule piste « téléchargements »

Le serveur (front + API) est un Ubuntu avec Apache 2.4.52. Les ressources de cours partent en lien direct `/storage/course_resources/...` : **chaque téléchargement est donc dans les access logs Apache** (IP, date/heure, fichier, user-agent) — dans la limite de la rétention logrotate.

```bash
# 1) Mesurer la profondeur d'historique disponible
ls -lh /var/log/apache2/

# 2) Téléchargements de ressources de cours (fichier par fichier, daté, par IP)
zgrep -h "GET .*storage/course_resources" /var/log/apache2/*access*log* \
  | awk '{print $4, $1, $7}' | sort

# 3) Téléchargements de certificats — ATTRIBUABLES (l'id apprenant est dans l'URL)
zgrep -h "certificates/download" /var/log/apache2/*access*log*

# 4) Volume de visites du front par jour (toutes pages)
zgrep -h "GET / " /var/log/apache2/*access*log* | awk '{print substr($4,2,11)}' | sort | uniq -c
```

*Attribution IP → personne : approximative, par recoupement de l'IP et de l'heure avec l'activité en base (A2/A5/A6) sur la même fenêtre. Faisable au cas par cas, pas industrialisable.*
*Vérifier aussi `/etc/logrotate.d/apache2` (rétention) et l'éventuel panneau de l'hébergeur (AWStats, métriques) + les crons de sauvegarde MySQL : des dumps datés permettent des instantanés historiques supplémentaires.*

## C. Sentry (déjà en place sur le front)

Le front intègre Sentry (erreurs + widget de feedback, `sendDefaultPii: true`). Par défaut le SDK compte aussi les **sessions anonymes** (Release Health). À vérifier dans le compte Sentry (org `o4509547224104960`, région DE, projet `4510312056946768`) :
- **Releases / Session Health** → nombre de sessions par jour ≈ courbe de fréquentation du site ;
- Les événements d'erreur contiennent IP + URL + horodatage → traces d'usage incidentes ;
- Les feedbacks utilisateurs sont nominatifs (nom + email exigés par le widget).

## D. Ce qui reste réellement impossible sur l'existant

- **Vues de leçons sans validation** (lecture passive) : rien ne l'enregistre nulle part.
- **Temps passé** par leçon/module : idem.
- **Téléchargements au-delà de la rétention des logs** : perdus.
- **Historique des tentatives de quiz** : seule la dernière subsiste (les précédentes sont physiquement supprimées).

D'où le besoin d'évolution « pilotage » (voir doc dédiée + prompt CDC).