Files
Menage/sql/migration_v3_period_key.sql
2026-11-11 09:35:51 +01:00

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;