-- Créer le schéma audit

CREATE SCHEMA IF NOT EXISTS audit;


-- Créer la table d'audit dans le schéma audit

CREATE TABLE audit.enquete_audit_log (
    audit_id SERIAL PRIMARY KEY,
    enquete_id BIGINT,
    action VARCHAR(10),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    old_data JSONB,
    new_data JSONB
);

-- Créer la fonction dans le schéma audit

CREATE OR REPLACE FUNCTION audit.audit_enquete_changes()
    RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit.enquete_audit_log (enquete_id, action, old_data, new_data)
        VALUES (
                   OLD.id,
                   TG_OP,
                   to_jsonb(OLD),
                   to_jsonb(NEW)
               );
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit.enquete_audit_log (enquete_id, action, new_data)
        VALUES (
                   NEW.id,
                   TG_OP,
                   to_jsonb(NEW)
               );
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO audit.enquete_audit_log (enquete_id, action, old_data)
        VALUES (
                   OLD.id,
                   TG_OP,
                   to_jsonb(OLD)
               );
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;




-- Créer le trigger

CREATE TRIGGER trigger_audit_enquete
    AFTER INSERT OR UPDATE OR DELETE ON enquete
    FOR EACH ROW
EXECUTE FUNCTION audit.audit_enquete_changes();


-- donner les droits si nécéssaires

GRANT USAGE ON SCHEMA audit TO infocad_user;
GRANT INSERT, SELECT ON audit.enquete_audit_log TO infocad_user;


-- Lister les audits

SELECT * FROM enquete_audit_log WHERE enquete_id = 42 ORDER BY changed_at DESC;

SELECT
    enquete_id,
    action,
    changed_at,
    old_data->>'status_enquete' AS status_before,
    new_data->>'status_enquete' AS status_after,
    old_data->>'updated_by' AS updated_by_before,
    new_data->>'updated_by' AS updated_by_after
FROM audit.enquete_audit_log
WHERE enquete_id = 5
ORDER BY changed_at DESC;