---- TRIGGER générique qui copie les données d'une table vers sa table "_history"

CREATE OR REPLACE FUNCTION gissmo_audit()
            RETURNS trigger AS $body$
            DECLARE
                name TEXT;
            BEGIN
                IF (TG_OP = 'UPDATE') THEN
                    name := TG_TABLE_NAME::TEXT || '_history';
                    EXECUTE format('INSERT INTO %I VALUES(($1).*)', name) USING OLD;
                    RETURN NEW;
                END IF;
            EXCEPTION
                WHEN data_exception THEN
                    RAISE WARNING '[gissmo_audit] - ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                WHEN unique_violation THEN
                    RAISE WARNING '[gissmo_audit] - ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                WHEN OTHERS THEN
                    RAISE WARNING '[gissmo_audit] - ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                    RETURN NULL;
            END;
            $body$ LANGUAGE plpgsql;

---- Ajout du TRIGGER générique sur la table equipment_equipment

CREATE TRIGGER equipment_equipment_audit
            AFTER UPDATE ON equipment_equipment
            FOR EACH ROW EXECUTE PROCEDURE gissmo_audit();

---- TRIGGER censé remplir le champ intervention_id lors de l'insertion d'une ligne d'historique (NE FONCTIONNE PAS :'()

CREATE OR REPLACE FUNCTION have_place_id_field_clustering()
            RETURNS trigger AS $body$
            DECLARE
                intervention_id INTEGER;
                given_date DATE;
            BEGIN
                IF (TG_OP = 'INSERT') AND (NEW.intervention_id IS NULL) THEN
                    given_date := date_trunc('day', NEW.insert_datetime);
                    INSERT INTO intervention_intervention AS intervention (id, date, place_id, confirmed) VALUES (DEFAULT, given_date, NEW.place_id, False)
                        ON CONFLICT (date, place_id) DO UPDATE SET
                            confirmed = 'f'
                        RETURNING id
                    INTO intervention_id;
                    NEW.intervention_id := (SELECT id FROM intervention_intervention WHERE date = given_date AND place_id = NEW.place_id LIMIT 1);
                    RETURN NEW;
                END IF;
            EXCEPTION
                WHEN data_exception THEN
                    RAISE WARNING '[have_place_id_field_clustering] - ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                WHEN unique_violation THEN
                    RAISE WARNING '[have_place_id_field_clustering] - ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                WHEN OTHERS THEN
                    RAISE WARNING '[have_place_id_field_clustering] - ERROR - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                    RETURN NULL;
            END;
            $body$ LANGUAGE plpgsql;