/* CAS SSO */
DELETE FROM app_configuration WHERE variable = 'cas_url';

/* Change in the SoapuiProject tables */
ALTER TABLE gwt_test_component ADD COLUMN new_project_id INTEGER;
ALTER TABLE gwt_test_component ADD COLUMN soapui_project INTEGER;
ALTER TABLE gwt_soapui_project ADD COLUMN gwt_project INTEGER;

CREATE OR REPLACE FUNCTION update_soapui_project(project_id_var integer) RETURNS boolean
LANGUAGE plpgsql
AS $_$BEGIN
  IF (SELECT gwt_project FROM gwt_soapui_project where id = project_id_var) IS NULL THEN
    INSERT INTO gwt_test_component(component_type, id, label, project_id, test_case_id, test_suite_id, new_project_id, soapui_project)
    SELECT 'project', nextval('gwt_test_component_id_seq'), label, NULL, NULL, NULL, NULL, project_id_var
    FROM gwt_soapui_project
    WHERE gwt_soapui_project.id = project_id_var;

    UPDATE gwt_soapui_project SET gwt_project = (SELECT max(id) FROM gwt_test_component) WHERE id=project_id_var;

    UPDATE gwt_test_component SET new_project_id = (SELECT max(id) FROM gwt_test_component) WHERE project_id = project_id_var;

    UPDATE gwt_custom_property SET test_component_id =(SELECT id FROM gwt_test_component WHERE gwt_test_component.soapui_project = project_id_var) WHERE project_id = project_id_var;

  END IF;

  return 1 ;
END;$_$;

CREATE OR REPLACE FUNCTION update_soapui_project_all() RETURNS boolean
LANGUAGE plpgsql
AS $_$
DECLARE
  idColumn integer;
BEGIN
  idColumn = (select min( id ) from gwt_soapui_project);

  WHILE idColumn is not null LOOP
    PERFORM update_soapui_project(idColumn);
    idColumn = (select min( id ) from gwt_soapui_project where id > idColumn);
  END LOOP;

  return 1 ;
END;$_$;

select update_soapui_project_all();

ALTER TABLE gwt_test_component DROP COLUMN project_id ;
ALTER TABLE gwt_test_component RENAME COLUMN new_project_id TO project_id ;


/* Add new columns in gwt_test_component */
ALTER TABLE gwt_test_component
  ADD COLUMN executable BOOLEAN;
UPDATE gwt_test_component
SET executable = TRUE
WHERE executable IS NULL;

ALTER TABLE gwt_test_component
  ADD COLUMN disable BOOLEAN;
UPDATE gwt_test_component
SET disable = FALSE
WHERE disable IS NULL;

/* Add new preference to activate or desactivate the eHealhSuisse module (execute instance)*/
INSERT INTO app_configuration (id, variable, value)
VALUES (nextval('app_configuration_id_seq'), 'ehealthsuisse', 'false');

/* PART 2 */

ALTER TABLE cmn_transaction_instance ALTER COLUMN standard type VARCHAR(64);
INSERT INTO app_configuration (id, variable, value)
VALUES (nextval('app_configuration_id_seq'), 'tool_instance_oid', 'oid');
INSERT INTO app_configuration (id, variable, value)
VALUES (nextval('app_configuration_id_seq'), 'evs_client_url', 'url');




