127 lines
5.5 KiB
SQL
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;
|