

-- 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_atal')),
    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)
);

-- Index pour améliorer les performances
CREATE INDEX idx_incidents_statut ON incidents(statut);
CREATE INDEX idx_incidents_created_at ON incidents(created_at DESC);
CREATE INDEX idx_incidents_incident_id ON incidents(incident_id);
CREATE INDEX idx_deleted_incidents_deleted_at ON deleted_incidents(deleted_at DESC);

-- Trigger 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';

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

-- Note: Le hash du mot de passe sera généré lors de la première utilisation
-- Pour générer un hash: password_hash('Admin123!', PASSWORD_DEFAULT)
