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

127 lines
5.5 KiB
SQL

-- ============================================================
-- Migration v2 → v3
-- Modèle : profiles (rôles) séparés des accounts (personnes)
-- ============================================================
-- Sauvegarde recommandée avant : mysqldump -u root -p menage > menage_v2_backup.sql
USE menage;
-- ── 1. Créer la table profiles ───────────────────────────────
CREATE TABLE IF NOT EXISTS profiles (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(255) NOT NULL DEFAULT '',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- ── 2. Migrer les anciens "users" comme profiles ─────────────
-- Chaque ancien profil ménage devient un profile
INSERT INTO profiles (id, name)
SELECT id, name FROM users;
-- ── 3. Créer les tables d'assignation par profile ────────────
CREATE TABLE IF NOT EXISTS profile_category_assignments (
profile_id INT NOT NULL,
category_id INT NOT NULL,
PRIMARY KEY (profile_id, category_id),
FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS profile_task_assignments (
profile_id INT NOT NULL,
task_id INT NOT NULL,
PRIMARY KEY (profile_id, task_id),
FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE,
FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
);
-- ── 4. Migrer les anciennes assignations tâches ──────────────
-- user_task_assignments → profile_task_assignments
INSERT IGNORE INTO profile_task_assignments (profile_id, task_id)
SELECT user_id, task_id FROM user_task_assignments;
-- ── 5. Ajouter les colonnes personnelles sur accounts ─────────
ALTER TABLE accounts
ADD COLUMN display_name VARCHAR(100) NOT NULL DEFAULT '' AFTER role,
ADD COLUMN color VARCHAR(7) NOT NULL DEFAULT '#6366f1' AFTER display_name,
ADD COLUMN avatar VARCHAR(2) NOT NULL DEFAULT '?' AFTER color,
ADD COLUMN email VARCHAR(150) NOT NULL DEFAULT '' AFTER avatar;
-- Renommer user_id → profile_id dans accounts
ALTER TABLE accounts
DROP FOREIGN KEY fk_account_user,
CHANGE COLUMN user_id profile_id INT NULL;
ALTER TABLE accounts
ADD CONSTRAINT fk_account_profile
FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE SET NULL;
-- ── 6. Récupérer display_name/color/avatar/email depuis users ─
UPDATE accounts a
JOIN users u ON u.id = a.profile_id
SET
a.display_name = u.name,
a.color = u.color,
a.avatar = u.avatar,
a.email = COALESCE(u.email, '');
-- ── 7. Migrer completions : user_id → account_id ─────────────
-- On cherche le compte qui avait ce user_id comme profile_id
ALTER TABLE completions ADD COLUMN account_id INT NULL AFTER user_id;
UPDATE completions c
JOIN accounts a ON a.profile_id = c.user_id
SET c.account_id = a.id;
-- Pour les completions orphelines (user sans compte), on garde user_id tel quel
-- en le faisant pointer vers le premier compte admin par défaut
UPDATE completions
SET account_id = (SELECT id FROM accounts WHERE role='admin' ORDER BY id LIMIT 1)
WHERE account_id IS NULL;
ALTER TABLE completions
DROP FOREIGN KEY completions_ibfk_2,
DROP COLUMN user_id,
MODIFY COLUMN account_id INT NOT NULL;
ALTER TABLE completions
ADD CONSTRAINT fk_completion_account
FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE;
-- ── 8. Nettoyer les anciennes tables ─────────────────────────
DROP TABLE IF EXISTS user_task_assignments;
DROP TABLE IF EXISTS users;
-- ── 9. Index utiles ──────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_completions_account ON completions(account_id);
CREATE INDEX IF NOT EXISTS idx_completions_week ON completions(week_key);
-- ── Vérification finale ──────────────────────────────────────
SELECT 'profiles' as t, COUNT(*) as n FROM profiles
UNION SELECT 'accounts', COUNT(*) FROM accounts
UNION SELECT 'profile_category_assign',COUNT(*) FROM profile_category_assignments
UNION SELECT 'profile_task_assign', COUNT(*) FROM profile_task_assignments
UNION SELECT 'completions', COUNT(*) FROM completions;
-- ============================================================
-- Migration v3.1 — period_key par fréquence
-- ============================================================
-- À exécuter sur une base v3 existante
USE menage;
-- 1. Renommer la colonne
ALTER TABLE completions CHANGE COLUMN week_key period_key VARCHAR(10) NOT NULL;
-- 2. Recalculer les period_key existants selon la fréquence de chaque tâche
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;