-- Mise a jour SuitePro SaaS - Juin 2026
-- Permet d'assigner une intervention a plusieurs employes/admins.

CREATE TABLE IF NOT EXISTS intervention_staff (
  intervention_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (intervention_id, user_id),
  CONSTRAINT fk_intervention_staff_intervention FOREIGN KEY (intervention_id) REFERENCES interventions(id) ON DELETE CASCADE,
  CONSTRAINT fk_intervention_staff_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_intervention_staff_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reprend les anciennes assignations simples pour remplir l'equipe.
INSERT IGNORE INTO intervention_staff (intervention_id, user_id)
SELECT id, assigned_user_id
FROM interventions
WHERE assigned_user_id IS NOT NULL;