-- Création de la base de données
CREATE DATABASE IF NOT EXISTS supervision_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE supervision_db;

-- Table des utilisateurs
CREATE TABLE IF NOT EXISTS utilisateurs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  prenom VARCHAR(100) NOT NULL,
  email VARCHAR(190) UNIQUE NOT NULL,
  mot_de_passe VARCHAR(255) NOT NULL,
  role ENUM('ASSISTANTE_DIRECTEUR', 'CHEF_DEPARTEMENT', 'DIRECTEUR', 'ADMIN', 'SECRETARIAT_DG') NOT NULL,
  departement VARCHAR(100),
  actif BOOLEAN DEFAULT true,
  photo_profil VARCHAR(255),
  telephone VARCHAR(20),
  date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  date_mise_a_jour TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table des départements
CREATE TABLE IF NOT EXISTS departements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) UNIQUE NOT NULL,
  description TEXT,
  chef_departement_id INT,
  FOREIGN KEY (chef_departement_id) REFERENCES utilisateurs(id) ON DELETE SET NULL
);

-- Table des dossiers
CREATE TABLE IF NOT EXISTS dossiers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(50) UNIQUE NOT NULL,
  numero_courrier_arrivee VARCHAR(100),
  objet TEXT NOT NULL,
  description TEXT,
  provenance VARCHAR(255) NOT NULL,
  type ENUM('COURRIER_ARRIVEE', 'COURRIER_DEPART', 'NOTE_SERVICE', 'RAPPORT', 'DEMANDE', 'AUTRE') NOT NULL,
  status ENUM('ENREGISTRE', 'EN_COURS_TRAITEMENT', 'EN_ATTENTE', 'TRAITE_ATTENTE_VALIDATION', 'VALIDE_PAR_DIRECTEUR', 'EN_COURS_SIGNATURE_DG', 'SIGNE_PAR_DG', 'TRANSMIS_AU_DESTINATAIRE', 'CLOTURE', 'EN_RETARD') NOT NULL DEFAULT 'ENREGISTRE',
  date_arrivee DATE NOT NULL,
  date_traitement DATE,
  date_validation DATE,
  date_signature DATE,
  date_transmission DATE,
  date_cloture DATE,
  delai_direction INT,
  delai_departement INT,
  date_limite_direction DATE,
  date_limite_departement DATE,
  instructions_dg TEXT,
  instructions_directeur TEXT,
  etat_traitement TEXT,
  actions_realisees TEXT,
  validation_directeur TEXT,
  is_en_retard BOOLEAN DEFAULT false,
  created_by_id INT NOT NULL,
  treated_by_id INT,
  validated_by_id INT,
  main_department_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by_id) REFERENCES utilisateurs(id) ON DELETE CASCADE,
  FOREIGN KEY (treated_by_id) REFERENCES utilisateurs(id) ON DELETE SET NULL,
  FOREIGN KEY (validated_by_id) REFERENCES utilisateurs(id) ON DELETE SET NULL,
  FOREIGN KEY (main_department_id) REFERENCES departements(id) ON DELETE SET NULL
);

-- Table des imputations
CREATE TABLE IF NOT EXISTS imputations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  dossier_id INT NOT NULL,
  departement_id VARCHAR(100) NOT NULL,
  instructions TEXT,
  date_limite TIMESTAMP NULL,
  etat_traitement TEXT,
  traite BOOLEAN DEFAULT false,
  date_traitement TIMESTAMP NULL,
  utilisateur_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE CASCADE,
  FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
);

-- Table des validations
CREATE TABLE IF NOT EXISTS validations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  dossier_id INT NOT NULL,
  utilisateur_id INT NOT NULL,
  niveau ENUM('chef_departement', 'directeur', 'juridique') NOT NULL,
  statut ENUM('en_attente', 'valide', 'refuse') NOT NULL DEFAULT 'en_attente',
  remarques TEXT,
  date_validation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE CASCADE,
  FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
);

-- Table de l'historique
CREATE TABLE IF NOT EXISTS historique (
  id INT AUTO_INCREMENT PRIMARY KEY,
  dossier_id INT NOT NULL,
  utilisateur_id INT NOT NULL,
  type ENUM('creation', 'modification', 'suppression', 'validation', 'refus', 'archivage', 'desarchivage', 'imputation', 'modification_imputation', 'suppression_imputation') NOT NULL,
  description TEXT NOT NULL,
  details JSON,
  date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE CASCADE,
  FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
);

-- Table des notifications
CREATE TABLE IF NOT EXISTS notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  utilisateur_id INT NOT NULL,
  type ENUM('INFO', 'WARNING', 'SUCCESS', 'ERROR') NOT NULL,
  titre VARCHAR(255) NOT NULL,
  message TEXT NOT NULL,
  lu BOOLEAN DEFAULT false,
  lien VARCHAR(255),
  date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
);

-- Table des pièces jointes
CREATE TABLE IF NOT EXISTS pieces_jointes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  dossier_id INT NOT NULL,
  nom VARCHAR(255) NOT NULL,
  chemin VARCHAR(255) NOT NULL,
  taille BIGINT,
  type_mime VARCHAR(100),
  utilisateur_id INT NOT NULL,
  date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE CASCADE,
  FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
);

-- Indexes pour améliorer les performances
CREATE INDEX idx_dossiers_status ON dossiers(status);
CREATE INDEX idx_dossiers_date_arrivee ON dossiers(date_arrivee);
CREATE INDEX idx_dossiers_main_department ON dossiers(main_department_id);
CREATE INDEX idx_historique_dossier ON historique(dossier_id);
CREATE INDEX idx_imputations_dossier ON imputations(dossier_id);
CREATE INDEX idx_validations_dossier ON validations(dossier_id);
CREATE INDEX idx_notifications_utilisateur ON notifications(utilisateur_id);
CREATE INDEX idx_pieces_jointes_dossier ON pieces_jointes(dossier_id);