-- TO EXECUTE ON GAZELLE MASTER MODEL INSTANCE YOU WANT TO TAKE VALUES FROM
--This command will create a list of insert lines corresponding to audit messages. The actual transaction link will be done based on transaction keyword.
--Warning : a small unwanted header may be generated by the select command.

select 'INSERT INTO tf_audit_message (id, last_changed, last_modifier_id, audited_event, oid, audited_transaction, document_section, issuing_actor, comment, event_code_type) VALUES ('
|| id
|| ', '''
|| COALESCE(last_changed, last_changed, now())
|| ''', '''
|| COALESCE(last_modifier_id, last_modifier_id, 'NULL')
|| ''', '''
|| COALESCE(audited_event, audited_event, 123456)
|| ''', '''
|| COALESCE(oid, oid, 'NULL')
|| ''', '
|| 'get_transaction_id('''
|| (SELECT CASE WHEN (SELECT COUNT(*) FROM tf_transaction where id = audited_transaction) = 0 THEN 'null' else (SELECT COALESCE(keyword, keyword, 'NULL') FROM tf_transaction where id = audited_transaction) END)
|| '''), get_document_section_id('''
|| (SELECT CASE WHEN (SELECT COUNT(*) FROM tf_document_sections where id = document_section) = 0 THEN 'null' else (SELECT COALESCE(section, section, 'NULL') FROM tf_document_sections where id = document_section) END)
|| '''), get_actor_id('''
|| (SELECT CASE WHEN (SELECT COUNT(*) FROM tf_actor where id = issuing_actor) = 0 THEN 'null' else (SELECT COALESCE(keyword, keyword, 'NULL') FROM tf_actor where id = issuing_actor) END)
|| '''), '''
|| COALESCE(comment, comment, 'NULL')
|| ''', '''
|| COALESCE(event_code_type, event_code_type, 'NULL')
|| ''');' from tf_audit_message order by id;



delete from tf_audit_message where issuing_actor is null;
delete from tf_audit_message where audited_transaction is null;
update tf_audit_message SET comment = NULL where comment = 'NULL';
update tf_audit_message SET event_code_type = NULL where event_code_type = 'NULL';
update tf_audit_message SET oid = NULL where oid = 'NULL';
