-- =============================================================================
-- Migration Script: Add Parent Functionality
-- Description: Adds parent role, parent-children relationships, and chaperone features
-- Date: 2026-03-21
-- =============================================================================

USE matizi;

-- Add 'parent' role to users table
ALTER TABLE users MODIFY COLUMN role ENUM('responsable', 'production', 'academie', 'parent', 'spectateur') NOT NULL;

-- Create parent_children relationship table
CREATE TABLE IF NOT EXISTS parent_children (
  id INT PRIMARY KEY AUTO_INCREMENT,
  parent_id INT NOT NULL,
  child_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (child_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_parent_child (parent_id, child_id)
);

-- Add chaperone fields to events table
ALTER TABLE events 
  ADD COLUMN IF NOT EXISTS requires_chaperones BOOLEAN DEFAULT FALSE AFTER max_participants,
  ADD COLUMN IF NOT EXISTS max_chaperones INT DEFAULT 0 AFTER requires_chaperones;

-- Add 'accompagnateur' participation type
ALTER TABLE participations MODIFY COLUMN participation_type ENUM('production', 'academie', 'accompagnateur') NOT NULL;

-- =============================================================================
-- Verification Queries
-- =============================================================================
-- Run these queries after migration to verify:
-- SELECT * FROM users WHERE role = 'parent';
-- SELECT * FROM parent_children;
-- DESCRIBE events;
-- DESCRIBE participations;
