-- Migration: Système complet de suivi des dénonciations
-- =============================================

-- Table historique des actions sur les dénonciations
CREATE TABLE IF NOT EXISTS denonciation_historique (
    id INT PRIMARY KEY AUTO_INCREMENT,
    denonciation_id INT NOT NULL,
    user_id INT NULL,
    action VARCHAR(50) NOT NULL,
    ancien_statut VARCHAR(50) NULL,
    nouveau_statut VARCHAR(50) NULL,
    commentaire TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (denonciation_id) REFERENCES denonciations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Table des pièces jointes supplémentaires (documents ajoutés pendant le traitement)
CREATE TABLE IF NOT EXISTS denonciation_pieces (
    id INT PRIMARY KEY AUTO_INCREMENT,
    denonciation_id INT NOT NULL,
    user_id INT NULL,
    type ENUM('document', 'photo', 'audio', 'video', 'note') DEFAULT 'document',
    nom_fichier VARCHAR(255) NOT NULL,
    chemin_fichier VARCHAR(500) NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (denonciation_id) REFERENCES denonciations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Table des assignations (qui traite la dénonciation)
CREATE TABLE IF NOT EXISTS denonciation_assignations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    denonciation_id INT NOT NULL,
    user_id INT NOT NULL,
    assigne_par INT NULL,
    date_assignation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_fin DATETIME NULL,
    est_actif BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (denonciation_id) REFERENCES denonciations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigne_par) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Ajouter colonnes supplémentaires à la table denonciations
ALTER TABLE denonciations 
    ADD COLUMN IF NOT EXISTS numero_dossier VARCHAR(50) UNIQUE NULL,
    ADD COLUMN IF NOT EXISTS priorite ENUM('basse', 'normale', 'haute', 'urgente') DEFAULT 'normale',
    ADD COLUMN IF NOT EXISTS categorie VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS sous_categorie VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS date_cloture DATETIME NULL,
    ADD COLUMN IF NOT EXISTS resolution TEXT NULL,
    ADD COLUMN IF NOT EXISTS note_interne TEXT NULL,
    ADD COLUMN IF NOT EXISTS localisation VARCHAR(255) NULL,
    ADD COLUMN IF NOT EXISTS date_faits DATETIME NULL;

-- Index pour optimiser les recherches
CREATE INDEX IF NOT EXISTS idx_denonciation_numero ON denonciations(numero_dossier);
CREATE INDEX IF NOT EXISTS idx_denonciation_priorite ON denonciations(priorite);
CREATE INDEX IF NOT EXISTS idx_denonciation_categorie ON denonciations(categorie);
CREATE INDEX IF NOT EXISTS idx_historique_denonciation ON denonciation_historique(denonciation_id);
CREATE INDEX IF NOT EXISTS idx_assignation_denonciation ON denonciation_assignations(denonciation_id);
