-- =============================================================================
-- Matizi - Circus Event Management Application
-- Database Schema and Seed Data
-- =============================================================================

SET CHARACTER SET utf8mb4;

CREATE DATABASE IF NOT EXISTS matizi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE matizi;

-- =============================================================================
-- TABLE: users
-- =============================================================================


CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  phone VARCHAR(20),
  role ENUM('responsable', 'production', 'academie', 'parent', 'spectateur') NOT NULL,
  profile_picture VARCHAR(255),
  pending_email VARCHAR(255) NULL DEFAULT NULL,
  email_change_token VARCHAR(64) NULL DEFAULT NULL,
  email_change_expires DATETIME NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================================================
-- TABLE: user_specialties
-- =============================================================================

CREATE TABLE user_specialties (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user_specialty (user_id, name)
);

-- =============================================================================
-- TABLE: parent_children
-- =============================================================================

CREATE TABLE 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)
);

-- =============================================================================
-- TABLE: events
-- =============================================================================

CREATE TABLE events (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  event_type ENUM('spectacle', 'repetition', 'formation', 'autre') DEFAULT 'spectacle',
  location VARCHAR(255),
  event_date DATETIME NOT NULL,
  end_time DATETIME NULL,
  max_participants INT DEFAULT 0,
  requires_chaperones BOOLEAN DEFAULT FALSE,
  max_chaperones INT DEFAULT 0,
  is_ticketing_enabled BOOLEAN DEFAULT FALSE,
  ticket_price_adult DECIMAL(10, 2) DEFAULT 0.00,
  ticket_price_child DECIMAL(10, 2) DEFAULT 0.00,
  available_seats INT DEFAULT 0,
  arrival_time_piste TIME NULL,
  arrival_time_venue TIME NULL,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

-- =============================================================================
-- TABLE: participations
-- =============================================================================

CREATE TABLE participations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_id INT NOT NULL,
  user_id INT NOT NULL,
  status ENUM('assigne', 'confirme', 'refuse', 'en_attente') DEFAULT 'en_attente',
  participation_type ENUM('production', 'academie', 'accompagnateur') NOT NULL,
  notes TEXT,
  arrival_choice ENUM('piste', 'venue') NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_participation (event_id, user_id)
);

-- =============================================================================
-- TABLE: absences
-- =============================================================================

CREATE TABLE absences (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  reason TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- =============================================================================
-- TABLE: event_subgroups
-- =============================================================================

CREATE TABLE event_subgroups (
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  description TEXT,
  category ENUM('organisation', 'numero') NOT NULL DEFAULT 'organisation',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

-- =============================================================================
-- TABLE: subgroup_members
-- =============================================================================

CREATE TABLE subgroup_members (
  id INT PRIMARY KEY AUTO_INCREMENT,
  subgroup_id INT NOT NULL,
  user_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (subgroup_id) REFERENCES event_subgroups(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_subgroup_member (subgroup_id, user_id)
);

-- =============================================================================
-- TABLE: notifications
-- =============================================================================

CREATE TABLE notifications (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  event_id INT,
  type ENUM('assignation', 'opportunite', 'modification', 'annulation', 'reponse_participation') NOT NULL,
  message TEXT NOT NULL,
  is_read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL
);

-- =============================================================================
-- TABLE: bookings
-- =============================================================================

CREATE TABLE bookings (
  id INT PRIMARY KEY AUTO_INCREMENT,
  event_id INT NOT NULL,
  user_id INT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  num_tickets_adult INT DEFAULT 0,
  num_tickets_child INT DEFAULT 0,
  num_tickets INT NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  status ENUM('PENDING', 'CONFIRMED', 'CANCELLED') DEFAULT 'PENDING',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- =============================================================================
-- TABLE: tickets
-- =============================================================================

CREATE TABLE tickets (
  id INT PRIMARY KEY AUTO_INCREMENT,
  booking_id INT NOT NULL,
  unique_token VARCHAR(255) UNIQUE NOT NULL,
  ticket_type ENUM('adult', 'child') NOT NULL,
  is_validated BOOLEAN DEFAULT FALSE,
  validated_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  token VARCHAR(255) UNIQUE NOT NULL,
  expires_at DATETIME NOT NULL,
  used BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- =============================================================================
-- SEED DATA
-- All passwords are bcrypt hashes of "password123" (10 salt rounds)
-- =============================================================================

-- Users
-- 1 responsable, 3 production artists, 3 academie artists, 2 parents

INSERT INTO users (email, password, first_name, last_name, phone, role) VALUES
  -- Responsable
  ('responsable@matizi.com',  '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'David',    'Bouhon',   '0601020304', 'responsable'),

  -- Production artists
  ('jongleur@matizi.com',     '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'Lucas',    'Martin',   '0611223344', 'production'),
  ('acrobate@matizi.com',     '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'Sophie',   'Bernard',  '0622334455', 'production'),
  ('filaeriste@matizi.com',   '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'Thomas',   'Leroy',    '0633445566', 'production'),

  -- Academie artists
  ('clown@matizi.com',        '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'Emma',     'Moreau',   '0644556677', 'academie'),
  ('trapeze@matizi.com',      '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'Hugo',     'Simon',    '0655667788', 'academie'),
  ('equilibre@matizi.com',    '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'Camille',  'Laurent',  '0666778899', 'academie'),

  -- Parents
  ('parent1@matizi.com',      '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'Marie',    'Moreau',   '0677889900', 'parent'),
  ('parent2@matizi.com',      '$2a$10$fUalj55K2MTktWNCEx1/2O4FolzU06WV4yr7f1zuIabqMUjqkzhFe', 'Pierre',   'Simon',    '0688990011', 'parent');

-- Specialties (multiple tags per artist)
INSERT INTO user_specialties (user_id, name) VALUES
  (2, 'jonglerie'),
  (3, 'acrobatie'),
  (4, 'fil de fer'),
  (5, 'clown'),
  (6, 'trapèze'),
  (7, 'équilibre');

-- Parent-Children relationships
INSERT INTO parent_children (parent_id, child_id) VALUES
  (8, 5),  -- Marie Moreau is parent of Emma Moreau (clown)
  (9, 6);  -- Pierre Simon is parent of Hugo Simon (trapeze)

-- Events
-- 1 future spectacle, 1 past repetition, 1 event requiring chaperones

INSERT INTO events (title, description, event_type, location, event_date, max_participants, requires_chaperones, max_chaperones, created_by) VALUES
  (
    'Grand Spectacle de Printemps',
    'Le spectacle annuel de printemps du cirque Matizi. Représentation complète avec toutes les disciplines.',
    'spectacle',
    'Chapiteau Matizi - Paris 11e',
    DATE_ADD(CURDATE(), INTERVAL 30 DAY) + INTERVAL 20 HOUR,
    6,
    TRUE,
    3,
    1  -- created by David Bouhon (responsable)
  ),
  (
    'Répétition Générale - Numéro Collectif',
    'Répétition générale du numéro collectif pour le spectacle de printemps.',
    'repetition',
    'Salle de répétition Matizi - Paris 11e',
    DATE_SUB(CURDATE(), INTERVAL 7 DAY) + INTERVAL 14 HOUR,
    4,
    FALSE,
    0,
    1  -- created by David Bouhon (responsable)
  );

-- Participations
-- Assign artists to events

INSERT INTO participations (event_id, user_id, status, participation_type, notes) VALUES
  -- Grand Spectacle de Printemps (event 1)
  (1, 2, 'confirme',   'production', 'Numéro de jonglerie en ouverture.'),
  (1, 3, 'confirme',   'production', 'Numéro acrobatique solo.'),
  (1, 4, 'en_attente', 'production', 'Disponibilité à confirmer.'),
  (1, 5, 'confirme',   'academie',   'Intermède clownesque entre les numéros.'),
  (1, 6, 'confirme',   'academie',   'Numéro de trapèze volant.'),
  (1, 7, 'en_attente', 'academie',   NULL),

  -- Répétition Générale (event 2)
  (2, 2, 'confirme',   'production', NULL),
  (2, 3, 'confirme',   'production', NULL),
  (2, 6, 'confirme',   'academie',   NULL),
  (2, 7, 'refuse',     'academie',   'Indisponible ce jour-là.');

-- Absences

INSERT INTO absences (user_id, start_date, end_date, reason) VALUES
  (4, DATE_ADD(CURDATE(), INTERVAL 25 DAY), DATE_ADD(CURDATE(), INTERVAL 28 DAY), 'Blessure légère au genou - repos prescrit.'),
  (7, DATE_SUB(CURDATE(), INTERVAL 10 DAY), DATE_SUB(CURDATE(), INTERVAL 6 DAY),  'Congés personnels.'),
  (3, DATE_ADD(CURDATE(), INTERVAL 60 DAY), DATE_ADD(CURDATE(), INTERVAL 65 DAY), 'Formation externe - acrobatie aérienne.');

-- Notifications

INSERT INTO notifications (user_id, event_id, type, message, is_read) VALUES
  (2, 1, 'assignation',          'Vous êtes assigné au spectacle "Grand Spectacle de Printemps".', FALSE),
  (3, 1, 'assignation',          'Vous êtes assigné au spectacle "Grand Spectacle de Printemps".', TRUE),
  (4, 1, 'opportunite',          'Opportunité de participation disponible : "Grand Spectacle de Printemps".', FALSE),
  (5, 1, 'assignation',          'Vous êtes assigné au spectacle "Grand Spectacle de Printemps".', TRUE),
  (6, 1, 'assignation',          'Vous êtes assigné au spectacle "Grand Spectacle de Printemps".', FALSE),
  (7, 1, 'opportunite',          'Opportunité de participation disponible : "Grand Spectacle de Printemps".', FALSE),
  (1, 2, 'reponse_participation', 'Camille Laurent a refusé sa participation à la "Répétition Générale - Numéro Collectif".', FALSE),
  (2, 1, 'modification',          'L\'événement "Grand Spectacle de Printemps" a été mis à jour.', FALSE);
