28 lines
1.1 KiB
SQL
28 lines
1.1 KiB
SQL
-- ============================================================
|
|
-- 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;
|