-- this script has to be executed if some test steps do not have any version logged in the audit table. In that case, TM reports such error message
-- in the logs: "a problem on the revNumbers of teststeps" or "there are no revNumbers for tespsteps"

-- rev column in tf_transaction_aud is a foreign key to revinfo.rev; we must first populate the revinfo table
CREATE OR REPLACE FUNCTION populate_revinfo_transaction()
  RETURNS VOID AS
$BODY$
DECLARE revnumber INTEGER := (select last_value from hibernate_sequence) + 1;
DECLARE endnumber_tmp INTEGER := revnumber + (select count(id) from tf_transaction where id not in (select id from tf_transaction_aud)) ;
DECLARE endnumber INTEGER := endnumber_tmp + (select count(id) from tm_test_steps_aud where transaction_id in (select id from tf_transaction where id not in (select id from tf_transaction_aud))) ;
BEGIN
  LOOP
    EXIT when revnumber = endnumber;
    insert into revinfo (rev, revtstmp) values (revnumber, 1550915431299);
    revnumber := revnumber + 1;
  END LOOP;
  RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

-- execute the function we have just created
select populate_revinfo_transaction();

-- create temp table to store tf_transaction without revision
CREATE TABLE temp_transaction (
    id integer NOT NULL
);

INSERT INTO temp_transaction (id) SELECT id from tf_transaction where id not in (select id from tf_transaction_aud);

-- populate the tf_transaction_aud for tf_transaction which id is not present in tm_test_steps_aud table
insert into tf_transaction_aud (id, rev, revtype, description, keyword, name, reference, documentsection, transaction_status_type_id)
  SELECT id, nextval('hibernate_sequence'), 1, description, keyword, name, reference, documentsection, transaction_status_type_id
  from tf_transaction where id not in (select id from tf_transaction_aud);


-- create temp table to store failed tm_test_steps_aud
CREATE TABLE temp_test_steps(
    id integer NOT NULL
);

INSERT INTO temp_test_steps (id) SELECT id from tm_test_steps_aud where transaction_id in (select id from temp_transaction);

-- create new revision for tm_steps for all the transaction that have just been corrected
insert into tm_test_steps_aud (id, rev, revtype, description, message_type, secured, step_index, test_roles_initiator_id, test_roles_responder_id, test_steps_option_id, transaction_id, ws_transaction_usage_id)
SELECT id, nextval('hibernate_sequence'), revtype, description, message_type, secured, step_index, test_roles_initiator_id, test_roles_responder_id, test_steps_option_id, transaction_id, ws_transaction_usage_id
from tm_test_steps_aud where transaction_id in (select id from temp_transaction);

-- edit test instance to use the new revision of steps
update tm_test_steps_instance set test_steps_version = (select max(rev) from tm_test_steps_aud where tm_test_steps_aud.id = tm_test_steps_instance.test_steps_id) where test_steps_id in (select id from temp_test_steps);

-- remove temp table
DROP TABLE temp_transaction;
DROP TABLE temp_test_steps;
