-- ============================================================ -- Migration v3 → v3.1 — period_key par fréquence -- À exécuter si vous avez déjà la v3 installée -- ============================================================ USE menage; -- 1. Renommer week_key → period_key ALTER TABLE completions CHANGE COLUMN week_key period_key VARCHAR(10) NOT NULL; -- 2. Recalculer les valeurs existantes selon la fréquence UPDATE completions c JOIN tasks t ON t.id = c.task_id SET c.period_key = CASE t.frequency WHEN 'quotidien' THEN DATE_FORMAT(c.completed_at, '%Y-%m-%d') WHEN 'hebdomadaire' THEN CONCAT(YEAR(c.completed_at), '-', LPAD(WEEK(c.completed_at, 3), 2, '0')) WHEN 'mensuel' THEN DATE_FORMAT(c.completed_at, '%Y-%m') WHEN 'trimestriel' THEN CONCAT(YEAR(c.completed_at), '-Q', QUARTER(c.completed_at)) ELSE DATE_FORMAT(c.completed_at, '%Y-%W') END; -- Vérification SELECT t.frequency, c.period_key, COUNT(*) as n FROM completions c JOIN tasks t ON t.id = c.task_id GROUP BY t.frequency, c.period_key ORDER BY t.frequency, c.period_key DESC;