-- ==========================================
-- Script d'installation complet de la base de données
-- Registre Santé et Sécurité au Travail
-- Evreux Portes de Normandie / Ville d'Évreux
-- ==========================================

-- Création de la base de données (à exécuter en tant que superuser PostgreSQL)
-- CREATE DATABASE "registre-sst";
-- \c registre-sst;

-- ==========================================
-- 1. TABLES PRINCIPALES
-- ==========================================

-- Table des utilisateurs (gestionnaires et admins)
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('gestionnaire', 'admin')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des incidents
CREATE TABLE IF NOT EXISTS incidents (
    id SERIAL PRIMARY KEY,
    incident_id VARCHAR(50) NOT NULL UNIQUE, -- Format: SST-YYYY-XXXX
    nom_agent VARCHAR(100) NOT NULL,
    prenom_agent VARCHAR(100) NOT NULL,
    email_agent VARCHAR(255) NOT NULL,
    objet VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    photo_path VARCHAR(500),
    lieu_texte VARCHAR(500),
    lieu_latitude DECIMAL(10, 8),
    lieu_longitude DECIMAL(11, 8),
    statut VARCHAR(20) NOT NULL DEFAULT 'en_cours' CHECK (statut IN ('en_cours', 'cloture', 'envoye_eproxy')),
    commentaire_gestionnaire TEXT,
    date_envoi_atal TIMESTAMP,
    traite_par INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des incidents supprimés (corbeille)
CREATE TABLE IF NOT EXISTS deleted_incidents (
    id SERIAL PRIMARY KEY,
    incident_id VARCHAR(50) NOT NULL,
    nom_agent VARCHAR(100) NOT NULL,
    prenom_agent VARCHAR(100) NOT NULL,
    email_agent VARCHAR(255) NOT NULL,
    objet VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    photo_path VARCHAR(500),
    lieu_texte VARCHAR(500),
    lieu_latitude DECIMAL(10, 8),
    lieu_longitude DECIMAL(11, 8),
    statut VARCHAR(20) NOT NULL,
    commentaire_gestionnaire TEXT,
    date_envoi_atal TIMESTAMP,
    traite_par INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_by INTEGER REFERENCES users(id)
);

-- Table pour le journal des emails envoyés
CREATE TABLE IF NOT EXISTS email_logs (
    id SERIAL PRIMARY KEY,
    incident_id INTEGER NOT NULL REFERENCES incidents(id) ON DELETE CASCADE,
    incident_ref VARCHAR(50) NOT NULL,
    destinataire VARCHAR(255) NOT NULL,
    sujet TEXT NOT NULL,
    contenu TEXT NOT NULL,
    statut VARCHAR(20) NOT NULL CHECK (statut IN ('succes', 'echec')),
    erreur TEXT,
    envoye_par INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ==========================================
-- 2. INDEX POUR PERFORMANCES
-- ==========================================

CREATE INDEX IF NOT EXISTS idx_incidents_statut ON incidents(statut);
CREATE INDEX IF NOT EXISTS idx_incidents_created_at ON incidents(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_incidents_incident_id ON incidents(incident_id);
CREATE INDEX IF NOT EXISTS idx_deleted_incidents_deleted_at ON deleted_incidents(deleted_at DESC);
CREATE INDEX IF NOT EXISTS idx_email_logs_incident ON email_logs(incident_id);
CREATE INDEX IF NOT EXISTS idx_email_logs_date ON email_logs(created_at DESC);

-- ==========================================
-- 3. TRIGGERS
-- ==========================================

-- Fonction pour mettre à jour updated_at automatiquement
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Triggers pour updated_at
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_incidents_updated_at ON incidents;
CREATE TRIGGER update_incidents_updated_at BEFORE UPDATE ON incidents
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ==========================================
-- 4. DONNÉES INITIALES
-- ==========================================

-- Insertion d'un utilisateur admin par défaut
-- Mot de passe: Admin123!
-- Note: Vous devez générer le hash avec le script create_admin.php
INSERT INTO users (nom, prenom, email, password_hash, role) 
VALUES (
    'Admin', 
    'Système', 
    'admin@evreux.fr', 
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- Hash de "Admin123!"
    'admin'
)
ON CONFLICT (email) DO NOTHING;

-- ==========================================
-- VÉRIFICATIONS FINALES
-- ==========================================

-- Afficher les tables créées
\dt

-- Afficher les utilisateurs
SELECT id, nom, prenom, email, role FROM users;

-- Message de succès
\echo '✅ Installation terminée avec succès!'
\echo ''
\echo 'Prochaines étapes:'
\echo '1. Exécutez le script create_admin.php pour générer un mot de passe sécurisé'
\echo '2. Connectez-vous sur http://localhost/registre-sst/backend/login.php'
\echo '3. Email: admin@evreux.fr | Mot de passe: Admin123!'
\echo ''
