-- Coordination Terrain Montréal
-- Version structurée sans CREATE DATABASE

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin_international','admin_national','admin_city','admin','coordinateur','agent','user') NOT NULL DEFAULT 'user',
  country VARCHAR(120) NULL,
  city VARCHAR(120) NULL,
  administered_legislation VARCHAR(190) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS portable_toilets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  arrondissement VARCHAR(120) NOT NULL,
  country VARCHAR(120) NULL DEFAULT 'Canada',
  city VARCHAR(120) NULL DEFAULT 'Montreal',
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  zone_priority ENUM('critique','haute','moyenne','basse') NOT NULL DEFAULT 'moyenne',
  units_count INT UNSIGNED NOT NULL DEFAULT 1,
  toilet_type ENUM('standard','lavabo','pmr','hiver','remorque') NOT NULL DEFAULT 'standard',
  access_24_7 TINYINT(1) NOT NULL DEFAULT 1,
  maintenance_status ENUM('ok','a_vidanger','hors_service','inspection') NOT NULL DEFAULT 'ok',
  usage_level ENUM('faible','moyen','fort','critique') NOT NULL DEFAULT 'moyen',
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS field_resources (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(140) NOT NULL,
  resource_type ENUM('refuge','soupe_populaire','clinique','centre_de_jour','eau','douche','vestiaire','autre') NOT NULL DEFAULT 'autre',
  arrondissement VARCHAR(120) NOT NULL,
  country VARCHAR(120) NULL DEFAULT 'Canada',
  city VARCHAR(120) NULL DEFAULT 'Montreal',
  address VARCHAR(255),
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  open_status ENUM('ouvert','ferme','horaire_variable') NOT NULL DEFAULT 'ouvert',
  capacity_total INT,
  capacity_available INT,
  phone VARCHAR(50),
  opening_hours VARCHAR(255),
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS incidents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  incident_type ENUM('toilette','hygiene','sante','securite','surpopulation','maintenance','approvisionnement','eau','nourriture','abri','vetements','douche','violence','sante_mentale','overdose','dechets','autre') NOT NULL DEFAULT 'autre',
  severity ENUM('faible','moyenne','haute','critique') NOT NULL DEFAULT 'moyenne',
  arrondissement VARCHAR(120) NOT NULL,
  country VARCHAR(120) NULL DEFAULT 'Canada',
  city VARCHAR(120) NULL DEFAULT 'Montreal',
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  related_toilet_id INT UNSIGNED,
  status ENUM('nouveau','en_cours','resolu','rejete') NOT NULL DEFAULT 'nouveau',
  assigned_team VARCHAR(120),
  reported_by VARCHAR(120),
  created_by_user_id INT UNSIGNED,
  photo_path VARCHAR(255),
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS mobile_interventions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_name VARCHAR(140) NOT NULL,
  intervention_type ENUM('entretien','approvisionnement','medical','social','inspection','reparation') NOT NULL DEFAULT 'inspection',
  status ENUM('planifiee','en_route','sur_site','terminee','annulee') NOT NULL DEFAULT 'planifiee',
  priority ENUM('basse','moyenne','haute','critique') NOT NULL DEFAULT 'moyenne',
  target_type ENUM('toilette','ressource','incident') NOT NULL DEFAULT 'incident',
  target_id INT UNSIGNED,
  arrondissement VARCHAR(120) NOT NULL,
  country VARCHAR(120) NULL DEFAULT 'Canada',
  city VARCHAR(120) NULL DEFAULT 'Montreal',
  scheduled_at DATETIME,
  completed_at DATETIME,
  created_by_user_id INT UNSIGNED,
  notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED,
  action_type VARCHAR(80) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id INT UNSIGNED,
  details TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE IF NOT EXISTS agent_positions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  status_note VARCHAR(255),
  mobility_mode ENUM('vehicule','transport') NOT NULL DEFAULT 'vehicule',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sender_user_id INT UNSIGNED NOT NULL,
  receiver_user_id INT UNSIGNED NULL,
  receiver_role ENUM('admin_international','admin_national','admin_city','coordinateur','agent','all') DEFAULT NULL,
  subject VARCHAR(160) NOT NULL,
  body TEXT NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);


ALTER TABLE mobile_interventions
  ADD COLUMN assigned_user_id INT UNSIGNED NULL,
  ADD COLUMN agent_response ENUM('pending','accepted','refused') NOT NULL DEFAULT 'pending';

ALTER TABLE messages
  ADD COLUMN intervention_id INT UNSIGNED NULL;


CREATE TABLE IF NOT EXISTS evaluation_themes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(100) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS evaluation_nodes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  theme_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  level_order INT NOT NULL DEFAULT 1,
  points INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_eval_nodes_theme FOREIGN KEY (theme_id) REFERENCES evaluation_themes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS evaluation_questions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  node_id INT UNSIGNED NOT NULL UNIQUE,
  question_text TEXT NOT NULL,
  explanation TEXT NOT NULL,
  correct_answer_key CHAR(1) NOT NULL,
  CONSTRAINT fk_eval_questions_node FOREIGN KEY (node_id) REFERENCES evaluation_nodes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS evaluation_answers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  question_id INT UNSIGNED NOT NULL,
  answer_key CHAR(1) NOT NULL,
  answer_text VARCHAR(255) NOT NULL,
  CONSTRAINT fk_eval_answers_question FOREIGN KEY (question_id) REFERENCES evaluation_questions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS evaluation_prerequisites (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  node_id INT UNSIGNED NOT NULL,
  required_node_id INT UNSIGNED NOT NULL,
  CONSTRAINT fk_eval_pr_node FOREIGN KEY (node_id) REFERENCES evaluation_nodes(id) ON DELETE CASCADE,
  CONSTRAINT fk_eval_pr_required FOREIGN KEY (required_node_id) REFERENCES evaluation_nodes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_evaluation_progress (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  node_id INT UNSIGNED NOT NULL,
  score_awarded INT NOT NULL DEFAULT 0,
  completed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_node (user_id, node_id),
  CONSTRAINT fk_eval_progress_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_eval_progress_node FOREIGN KEY (node_id) REFERENCES evaluation_nodes(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS agent_applications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL UNIQUE,
  status ENUM('in_progress','submitted','approved','rejected') NOT NULL DEFAULT 'in_progress',
  score_total INT NOT NULL DEFAULT 0,
  completion_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
  motivation TEXT,
  reviewer_notes TEXT,
  submitted_at DATETIME NULL,
  reviewed_at DATETIME NULL,
  reviewed_by_user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_agent_app_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_agent_app_reviewer FOREIGN KEY (reviewed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
);
