-- 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
);

CREATE INDEX idx_email_logs_incident ON email_logs(incident_id);
CREATE INDEX idx_email_logs_date ON email_logs(created_at DESC);
