-- Création de la base de données (optionnel, vous pouvez la créer via cPanel)
CREATE DATABASE IF NOT EXISTS btp_auto_controle
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE btp_auto_controle;

-- Table users
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('superadmin','admin','personnel') NOT NULL DEFAULT 'personnel',
    signature_path VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table password_history
CREATE TABLE password_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table projets
CREATE TABLE projets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    date_debut DATE,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
);

-- Table dossiers
CREATE TABLE dossiers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    projet_id INT NOT NULL,
    nom VARCHAR(100) NOT NULL,
    ordre INT DEFAULT 0,
    UNIQUE KEY unique_projet_dossier (projet_id, nom),
    FOREIGN KEY (projet_id) REFERENCES projets(id) ON DELETE CASCADE
);

-- Table sous_dossiers
CREATE TABLE sous_dossiers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dossier_id INT NOT NULL,
    nom VARCHAR(100) NOT NULL,
    ordre INT DEFAULT 0,
    UNIQUE KEY unique_dossier_sousdossier (dossier_id, nom),
    FOREIGN KEY (dossier_id) REFERENCES dossiers(id) ON DELETE CASCADE
);

-- Table fiches
CREATE TABLE fiches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sous_dossier_id INT NOT NULL,
    numero_fiche VARCHAR(50) NOT NULL UNIQUE,
    titre VARCHAR(200) NOT NULL,
    zone VARCHAR(255),
    observation TEXT,
    type_reserve ENUM('sans_reserve','avec_reserve') DEFAULT 'sans_reserve',
    description_reserve TEXT,
    contenu JSON NOT NULL,
    statut ENUM('brouillon','soumis','valide') DEFAULT 'brouillon',
    createur_id INT NOT NULL,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_soumission DATE NULL,
    responsable_id INT NULL,
    pdf_path VARCHAR(255) NULL,
    version INT DEFAULT 1,
    parent_id INT NULL,
    deleted_at TIMESTAMP NULL,
    deleted_by INT NULL,
    signature_emetteur VARCHAR(255) NULL,
    signature_responsable VARCHAR(255) NULL,
    signature_responsable_date TIMESTAMP NULL,
    FOREIGN KEY (sous_dossier_id) REFERENCES sous_dossiers(id) ON DELETE CASCADE,
    FOREIGN KEY (createur_id) REFERENCES users(id),
    FOREIGN KEY (responsable_id) REFERENCES users(id),
    FOREIGN KEY (parent_id) REFERENCES fiches(id) ON DELETE SET NULL,
    FOREIGN KEY (deleted_by) REFERENCES users(id)
);

-- Table templates (par sous-dossier)
CREATE TABLE templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sous_dossier_id INT UNIQUE NOT NULL,
    contenu_template JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (sous_dossier_id) REFERENCES sous_dossiers(id) ON DELETE CASCADE
);

-- Table templates_library
CREATE TABLE templates_library (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    description TEXT,
    contenu_template JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table logs
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(255) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Table compteurs_fiches
CREATE TABLE compteurs_fiches (
    projet_id INT NOT NULL,
    sous_dossier_id INT NOT NULL,
    prefixe VARCHAR(50) NOT NULL,
    dernier_numero INT DEFAULT 0,
    PRIMARY KEY (projet_id, sous_dossier_id),
    FOREIGN KEY (projet_id) REFERENCES projets(id) ON DELETE CASCADE,
    FOREIGN KEY (sous_dossier_id) REFERENCES sous_dossiers(id) ON DELETE CASCADE
);

-- Table fiches_images
CREATE TABLE fiches_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fiche_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    annotations JSON NULL,
    ordre INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fiche_id) REFERENCES fiches(id) ON DELETE CASCADE
);

-- Insertion du superadmin (remplacez le hash par un vrai)
-- Pour générer le hash : password_hash('CIBER@2026', PASSWORD_BCRYPT)
INSERT INTO users (username, nom, email, password_hash, role) 
VALUES ('kdorine', 'KOUAME DORINE', 'dorine.kouame@ciberci.com', '$2y$10$4/jTcaQI8Ukd/hBgf7FvTepyv8u2Sh0S2/UJ6g17r8PI1Yo1nE49O', 'superadmin');