-- Ajout des tables pour le module Incidents

-- Référentiels métier
CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL UNIQUE,
  contact_email VARCHAR(150) NULL,
  contact_phone VARCHAR(50) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  address TEXT NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incident_causes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  category VARCHAR(80) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS incident_statuses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(50) UNIQUE NOT NULL,
  label VARCHAR(100) NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  color VARCHAR(7) DEFAULT '#6c757d',
  sort_order INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table principale des incidents
CREATE TABLE IF NOT EXISTS incidents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id VARCHAR(40) UNIQUE NOT NULL,
  client_id INT NOT NULL,
  location_id INT NOT NULL,
  cause_id INT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  priority ENUM('Basse','Moyenne','Haute','Urgent') NOT NULL DEFAULT 'Moyenne',
  status_id INT NOT NULL,
  declared_by INT NOT NULL,
  declared_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expected_response_at TIMESTAMP NULL,
  expected_resolution_at TIMESTAMP NULL,
  resolved_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id),
  FOREIGN KEY (location_id) REFERENCES locations(id),
  FOREIGN KEY (cause_id) REFERENCES incident_causes(id),
  FOREIGN KEY (status_id) REFERENCES incident_statuses(id),
  FOREIGN KEY (declared_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Assignation des techniciens
CREATE TABLE IF NOT EXISTS incident_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id INT NOT NULL,
  user_id INT NOT NULL,
  assigned_by INT NOT NULL,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Historique des changements
CREATE TABLE IF NOT EXISTS incident_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id INT NOT NULL,
  field_name VARCHAR(50) NOT NULL,
  old_value TEXT NULL,
  new_value TEXT NULL,
  changed_by INT NOT NULL,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
  FOREIGN KEY (changed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Commentaires
CREATE TABLE IF NOT EXISTS incident_comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  incident_id INT NOT NULL,
  user_id INT NOT NULL,
  comment TEXT NOT NULL,
  is_internal TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (incident_id) REFERENCES incidents(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Statuts système obligatoires
INSERT IGNORE INTO incident_statuses (key_name, label, is_system, color, sort_order) VALUES
('open', 'Ouvert', 1, '#dc3545', 10),
('assigned', 'Assigné', 1, '#fd7e14', 20),
('in_progress', 'En cours', 1, '#0d6efd', 30),
('resolved_temp', 'Résolu provisoire', 1, '#198754', 40),
('resolved_final', 'Résolu définitif', 1, '#20c997', 50),
('cancelled', 'Annulé', 1, '#6c757d', 60);