alter table tbl_project add column all_scenarios bool null;
alter table tbl_project add column auto_ticket_creation bool null;
alter table tbl_test_suite_execution add column all_scenarios_template bool null;
alter table tbl_test_suite_execution add column auto_ticket_creation_template bool null;
CREATE OR REPLACE VIEW vw_project
AS SELECT tp.project_id,
    tp.project_code,
    tp.project_desc,
    tp.project_name,
    tp.logo,
    tp.metadata,
    tp.created_by,
    tp.created_dttm,
    tp.last_modified_by,
    tp.last_modified_dttm,
    tp.jira_project_name,
    ( SELECT count(1) AS count
           FROM tbl_feature tf
          WHERE tf.project_id = tp.project_id) AS feature_count,
    ( SELECT count(1) AS count
           FROM tbl_scenario ts
          WHERE ts.project_id::bpchar = tp.project_id) AS scenario_count,
    ARRAY( SELECT tpesm.system_id
           FROM tbl_project_external_system_map tpesm
          WHERE tpesm.project_id = tp.project_id) AS system_ids,
    ( SELECT count(1) AS count
           FROM tbl_test_suite_template_dummy ttstd
          WHERE ttstd.project_id::bpchar = tp.project_id) AS template_count,
    array_length(ARRAY( SELECT tpesm.system_id
           FROM tbl_project_external_system_map tpesm
          WHERE tpesm.project_id = tp.project_id), 1) AS system_count,
          tp.auto_ticket_creation,
    tp.all_scenarios
   FROM tbl_project tp;

-- public.vw_external_system source

CREATE OR REPLACE VIEW vw_external_system
AS SELECT tes.system_name,
    tes.system_code,
    tes.system_id,
    tes.system_desc AS description,
    tes.logo,
    tes.created_by,
    tes.created_dttm,
    tes.last_modified_by,
    tes.last_modified_dttm,
    ( SELECT count(env.env_id) AS count
           FROM tbl_environment env
          WHERE env.system_id = tes.system_id
          GROUP BY env.system_id) AS environments_count,
    ( SELECT count(ts.svc_id) AS count
           FROM tbl_service ts
          WHERE ts.system_id = tes.system_id
          GROUP BY ts.system_id) AS services_count,
    ( SELECT count(tvs.vld_svc_id) AS count
           FROM tbl_validation_service tvs
          WHERE tvs.system_id = tes.system_id
          GROUP BY tvs.system_id) AS valid_services_count,
    NULLIF(( SELECT count(*) AS count
           FROM vw_project vp
          WHERE tes.system_id = ANY (vp.system_ids)), 0) AS projects_count,
    ( SELECT count(stp.step_id) AS count
           FROM tbl_step stp
          WHERE stp.system_id = tes.system_id
          GROUP BY stp.system_id) AS steps_count,
    ( SELECT te.base_url
           FROM tbl_environment te
          WHERE te.system_id = tes.system_id AND te.active = true) AS base_url
   FROM tbl_external_system tes;

update
tbl_filter_configuration
set
column_display_name
= 'ID/Key',
column_name
='ID/Key'
where
column_display_name
='ADO ID'
;

UPDATE tbl_issue_tracking_endpoint
SET metadata =
        '{
            "input-template": "[\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/System.ChangedDate\",\n        \"from\": null,\n        \"value\": \"\"\n    }\n    #if ($StringUtil.hasLength($root.title))\n    ,{\n        \"op\": \"add\",\n        \"path\": \"/fields/System.Title\",\n        \"from\": null,\n        \"value\": $root.title\n    }\n    #end\n    #if ($StringUtil.hasLength($root.description))\n    ,{\n        \"op\": \"add\",\n        \"path\": \"/fields/System.Description\",\n        \"from\": null,\n        \"value\": $root.description\n    }\n    #end\n    #if($Objects.nonNull($root.labels))\n    , {\n        \"op\": \"replace\",\n        \"path\": \"/fields/System.Tags\",\n        \"from\": null,\n        \"value\": \"#foreach($label in $root.labels)$label.asText()#if($foreach.hasNext); #end#end\"\n    }\n    #end\n    #if ($StringUtil.hasLength($root.attachmentUrl))\n    , {\n        \"op\": \"add\",\n        \"path\": \"/relations/-\",\n        \"value\": {\n            \"rel\": \"AttachedFile\",\n            \"url\": $root.attachmentUrl,\n            \"attributes\": {\n                \"comment\": \"TA Attachment Added\"\n            }\n        }\n    }\n    #end\n    #if ($StringUtil.hasLength($root.fields.linkUrl))\n    , {\n        \"op\": \"add\",\n        \"path\": \"/relations/-\",\n        \"value\": {\n            \"rel\": $root.fields.linkType,\n            \"url\": $root.fields.linkUrl,\n            \"attributes\": {\n                \"comment\": \"Making a new link for the dependency\"\n            }\n        }\n    }\n    #end\n]",
            "output-template": "#macro(getDescription $fields)\n#if($StringUtil.hasLength($fields[\"Microsoft.VSTS.TCM.SystemInfo\"]))\n$fields[\"Microsoft.VSTS.TCM.SystemInfo\"]##\n#else\n#if($StringUtil.hasLength($fields[\"System.Description\"]))\n$fields[\"System.Description\"]##\n#else\"\"##\n#end\n#end\n#end\n#macro(getLabels $labels)\n#if($StringUtil.hasLength($root.fields[\"System.Tags\"]))\n#foreach($label in $labels.asText().split(\";\"))\"$StringUtil.trimWhitespace($label)\"#if($foreach.hasNext), #end#end\n#end\n#end\n{\n    \"key\": $root.id,\n    \"project\": $root.fields[\"System.TeamProject\"],\n    \"issueType\": $root.fields[\"System.WorkItemType\"],\n    \"title\": $root.fields[\"System.Title\"],\n    \"fields\":{\n        \"priority\": $root.fields[\"Microsoft.VSTS.Common.Priority\"],\n        \"severity\": $root.fields[\"Microsoft.VSTS.Common.Severity\"]\n    },\n    \"description\": #getDescription($root.fields),\n    \"labels\": [#getLabels($root.fields[\"System.Tags\"])],\n    \"status\": $root.fields[\"System.State\"],\n    \"createdBy\": $root.fields[\"System.CreatedBy\"].displayName,\n    \"createdDate\": $root.fields[\"System.CreatedDate\"],\n    \"avatar\": null\n    #if($Objects.nonNull($root.fields[\"System.AssignedTo\"]))\n    , \"assignee\": $root.fields[\"System.AssignedTo\"].displayName\n    #end\n    #if($baseUrl)\n    , \"selfUrl\": \"$baseUrl/$root.fields[\"System.TeamProject\"].asText()/_workitems/edit/$root.id.asText()/\"\n    #end\n}",
            "default-params": [
                {
                    "type": "query",
                    "name": "api-version",
                    "alias": "api-version",
                    "value": "7.0"
                }
            ],
            "input-params": [
                {
                    "type": "path",
                    "name": "id",
                    "alias": "id",
                    "required": "Y"
                }
            ]
        }'
WHERE provider = 1 AND tran_type = 'update-issue';
INSERT INTO public.tbl_issue_tracking_endpoint (issue_tracking_endpoint_id,http_method,metadata,provider,tran_type,url) VALUES
	 ('35daead53bfc4a108f9f30961a86d394','GET','{
            "input-template": "",
            "output-template": "{\n    \"fields\" : [\n    #foreach($type in $root.value)\n        {\n            \"name\": $type.name,\n            \"referenceName\": $type.referenceName, \n            \"attributes\": $type.attributes,\n            \"url\": $type.url\n        }#if($foreach.hasNext),#end\n    #end\n    ]\n}",
            "default-params": [
                {
                    "type": "query",
                    "name": "api-version",
                    "alias": "api-version",
                    "value": "7.0"
                }
            ],
            "input-params": [ ]
        }',1,'get-relation-types','/_apis/wit/workitemrelationtypes');
 UPDATE tbl_issue_tracking_endpoint
SET metadata =
        '{
  "input-template": "#set($filterAdded = false)\n#macro(setWithFilter)#set($filterAdded = true)#end\n{\n    \"query\": \"Select [System.Id] From WorkItems Where#if($Objects.nonNull($root.project) && !$root.project.isEmpty)#setWithFilter() [System.TeamProject] IN (#foreach($project in $root.project)''$project.asText()''#if($foreach.hasNext), #end#end) #end#if($Objects.nonNull($root.issueType) && !$root.issueType.isEmpty)#if($filterAdded) AND #end#setWithFilter() [System.WorkItemType] IN (#foreach($issueType in $root.issueType)''$issueType.asText()''#if($foreach.hasNext), #end#end) #end#if($Objects.nonNull($root.title))#if($filterAdded) AND #end#setWithFilter() [System.Title] Contains ''$root.title.asText()'' #end#if($Objects.nonNull($root.id) && !$root.id.isEmpty)#if($filterAdded) AND #end#setWithFilter() [System.Id] IN (#foreach($id in $root.id)''$id.asText()''#if($foreach.hasNext), #end#end) #end#if($Objects.nonNull($root.status) && !$root.status.isEmpty)#if($filterAdded) AND #end#setWithFilter() [System.State] IN (#foreach($status in $root.status)''$status.asText()''#if($foreach.hasNext), #end#end) #end#if($Objects.nonNull($root.assigneeNameOrEmail) && !$root.assigneeNameOrEmail.isEmpty)#if($filterAdded) AND #end#setWithFilter() [System.AssignedTo] IN (#foreach($assignee in $root.assigneeNameOrEmail)''$assignee.asText()''#if($foreach.hasNext), #end#end) #end#if($Objects.nonNull($root.label) && !$root.label.isEmpty)#if($filterAdded) AND #end#setWithFilter() (#foreach($label in $root.label) [System.Tags] Contains ''$label.asText()''#if($foreach.hasNext) OR#end#end)#end Order By [System.Id] DESC\"\n}",
  "output-template": "{\n    \"ids\": \"#foreach($item in $root.workItems)$item.id#if($foreach.hasNext),#end#end\"\n}",
  "default-params": [
      {
          "type": "query",
          "name": "api-version",
          "alias": "api-version",
          "value": "7.0"
      },
      {
          "type": "query",
          "name": "$top",
          "alias": "$top",
          "value": "50"
      }
  ],
  "input-params": []
}'
WHERE provider = 1 AND tran_type = 'azure-wiql-search';
ALTER TABLE tbl_test_suite_execution ADD COLUMN parent_issue VARCHAR null;
ALTER TABLE tbl_ts_step_execution ADD COLUMN link_ticket VARCHAR null;

INSERT INTO public.tbl_filter_configuration (filter_id,data_type,field_name,field_desc,field_type,"operator",entity_id,column_display_name,column_name,value,selected) VALUES
	 ('40289de38f9ad1ae018f9afebf5b0003','AUTOCOMPLETE',NULL,NULL,NULL,5,'40289de38f9ad1ae018f9aee7ad00000','Issue Type','Issue Type',NULL,NULL),
	 ('40289de38f9ad1ae018f9afebf5b0004','AUTOCOMPLETE',NULL,NULL,NULL,5,'40289de38f9ad1ae018f9aee7ad00000','Title','Title',NULL,NULL),
	 ('40289de38f9ad1ae018f9afebf5b0006','AUTOCOMPLETE',NULL,NULL,NULL,5,'40289de38f9ad1ae018f9aee7ad00000','Label','Labels',NULL,NULL);
INSERT INTO public.tbl_filter_configuration (filter_id,data_type,field_name,field_desc,field_type,"operator",entity_id,column_display_name,column_name,value,selected) VALUES
	 ('40289de38f9ad1ae018f9afebf5a0002','AUTOCOMPLETE',NULL,NULL,NULL,5,'40289de38f9ad1ae018f9aee7ad00000','Keys','Keys',NULL,NULL);
INSERT INTO public.tbl_filter_configuration (filter_id,data_type,field_name,field_desc,field_type,"operator",entity_id,column_display_name,column_name,value,selected) VALUES
	 ('40289de38f9ee704018fa9688c770007','AUTOCOMPLETE',NULL,NULL,NULL,5,'40289de38f9ad1ae018f9aee7ad00000','Status','Status',NULL,NULL);

INSERT INTO public.tbl_entity (entity_id,entity_desc,entity_fields,entity_name) VALUES
	 ('40289de38f9ad1ae018f9aee7ad00000',NULL,NULL,'Issues Reported');

ALTER TABLE tbl_database_details ADD COLUMN "schemas" _text null;

UPDATE tbl_issue_tracking_endpoint
SET metadata =
        '{[\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/System.Title\",\n        \"from\": null,\n        \"value\": $root.title\n    },\n    {\n		\"op\": \"add\",\n		\"path\": #if($root.issueType.asText().equals(\"Bug\"))\"/fields/Microsoft.VSTS.TCM.ReproSteps\"#else\"/fields/System.Description\"#end,\n		\"from\": null,\n		\"value\": $root.description\n	},\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/Microsoft.VSTS.Common.ValueArea\",\n        \"from\": null,\n        \"value\": \"Business\"\n    },\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/System.AreaPath\",\n        \"from\": null,\n        \"value\": \"$root.project.asText()\\\\FAAD\\\\Fee Operations and Billing\"\n    },\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/NT.Test.DefectType\",\n        \"from\": null,\n        \"value\": \"Functional\"\n    },\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/NT.Release.DetectedInRelease\",\n        \"from\": null,\n        \"value\": \"2024-02-13T06:00:00Z\"\n    },\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/NT.App.PrimaryTLA\",\n        \"from\": null,\n        \"value\": \"GFB*Global Fee Billing*APM0002620\"\n    },\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/NT.App.HotFix\",\n        \"from\": null,\n        \"value\": false\n    },\n    {\n        \"op\": \"add\",\n        \"path\": \"/fields/NT.App.Releasable\",\n        \"from\": null,\n        \"value\": true\n    }\n    #if($Objects.nonNull($root.labels) && !$root.labels.isEmpty())\n    , {\n        \"op\": \"add\",\n        \"path\": \"/fields/System.Tags\",\n        \"from\": null,\n        \"value\": \"#foreach($label in $root.labels)$label.asText()#if($foreach.hasNext); #end#end\"\n    }\n    #end\n    ,\n    {\n		\"op\": \"add\",\n		\"path\": \"/fields/Microsoft.VSTS.Common.Priority\",\n		\"from\": null,\n		\"value\": $root.fields.priority\n	},\n	{\n		\"op\": \"add\",\n		\"path\": \"/fields/Microsoft.VSTS.Common.Severity\",\n		\"from\": null,\n		\"value\": $root.fields.severity\n	}\n    ]",
            "output-template": "#macro(getDescription $fields)\n#if($StringUtil.hasLength($fields[\"Microsoft.VSTS.TCM.SystemInfo\"]))\n$fields[\"Microsoft.VSTS.TCM.SystemInfo\"]##\n#else\n#if($StringUtil.hasLength($fields[\"System.Description\"]))\n$fields[\"System.Description\"]##\n#else\"\"##\n#end\n#end\n#end\n#macro(getLabels $labels)\n#if($StringUtil.hasLength($root.fields[\"System.Tags\"]))\n#foreach($label in $labels.asText().split(\";\"))\"$StringUtil.trimWhitespace($label)\"#if($foreach.hasNext), #end#end\n#end\n#end\n{\n    \"key\": $root.id,\n    \"project\": $root.fields[\"System.TeamProject\"],\n    \"issueType\": $root.fields[\"System.WorkItemType\"],\n    \"title\": $root.fields[\"System.Title\"],\n    \"fields\":{\n        \"priority\": $root.fields[\"Microsoft.VSTS.Common.Priority\"],\n        \"severity\": $root.fields[\"Microsoft.VSTS.Common.Severity\"]\n    },\n    \"description\": #getDescription($root.fields),\n    \"labels\": [#getLabels($root.fields[\"System.Tags\"])],\n    \"status\": $root.fields[\"System.State\"],\n    \"createdBy\": $root.fields[\"System.CreatedBy\"].displayName,\n    \"createdDate\": $root.fields[\"System.CreatedDate\"],\n    \"avatar\": null\n    #if($Objects.nonNull($root.fields[\"System.AssignedTo\"]))\n    , \"assignee\": $root.fields[\"System.AssignedTo\"].displayName\n    #end\n    #if($baseUrl)\n    , \"selfUrl\": \"$baseUrl/$root.fields[\"System.TeamProject\"].asText()/_workitems/edit/$root.id.asText()/\"\n    #end\n}",
            "default-params": [
                {
                    "type": "query",
                    "name": "api-version",
                    "alias": "api-version",
                    "value": "7.0"
                }
            ],
            "input-params": [
                {
                    "type": "path",
                    "name": "project",
                    "alias": "project",
                    "required": "Y"
                },
                {
                    "type": "path",
                    "name": "type",
                    "alias": "type",
                    "required": "Y"
                }
            ]
        }'
WHERE provider = 1 AND tran_type = 'create-issue';

INSERT INTO public.tbl_issue_tracking_endpoint (issue_tracking_endpoint_id,http_method,metadata,provider,tran_type,url) VALUES
	 ('5f44fc32e4ea11edb5ea0242ac120003','GET','{
            "input-template": "",
            "output-template": "{\n    \"fields\" : [\n    #foreach($type in $root.value)\n        {\n            \"name\": $type.name,\n            \"referenceName\": $type.referenceName, \n            \"alwaysRequired\": $type.alwaysRequired,\n            \"defaultValue\": $type.defaultValue,\n            \"allowedValues\": $type.allowedValues,\n            \"dependentFields\": $type.dependentFields,\n            \"url\": $type.url\n        }#if($foreach.hasNext),#end\n    #end\n    ]\n}",
            "default-params": [
				{
                    "type": "query",
                    "name": "$expand",
                    "alias": "$expand",
                    "value": "All"
                },
                {
                    "type": "query",
                    "name": "api-version",
                    "alias": "api-version",
                    "value": "7.0"
                }
            ],
            "input-params": [
                {
                    "type": "path",
                    "name": "project",
                    "alias": "project",
                    "required": "Y"
                },
				{
                    "type": "path",
                    "name": "type",
                    "alias": "type",
                    "required": "Y"
                }
            ]
        }',1,'get-work-item-fields','/{project}/_apis/wit/workitemtypes/{type}/fields');

ALTER TABLE tbl_document add data_bkup oid ;
ALTER TABLE tbl_document ADD COLUMN data_new BYTEA;
UPDATE tbl_document SET data_new = lo_get(data);
ALTER TABLE tbl_document DROP COLUMN data;
ALTER TABLE tbl_document RENAME COLUMN data_new TO data;

UPDATE tbl_step SET step_template = jsonb_set( step_template::jsonb, '{params}','{}'::jsonb,false) where step_type = 'UI' and step_template not like '%"params":{}%'and step_template not like '%"params": {"headers": null, "pathParams": null, "queryParams": null}%';

ALTER TABLE tbl_downloaded_files ADD COLUMN byte_download_data bytea;
UPDATE tbl_downloaded_files SET byte_download_data = lo_get(download_data)::bytea;
ALTER TABLE tbl_downloaded_files RENAME COLUMN download_data TO download_data_bkup;
ALTER TABLE tbl_downloaded_files RENAME COLUMN byte_download_data TO download_data;
ALTER TABLE tbl_document ADD COLUMN byte_data bytea;
UPDATE tbl_document SET byte_data = lo_get("data")::bytea;
ALTER TABLE tbl_document RENAME COLUMN "data" TO data_bkup;
ALTER TABLE tbl_document RENAME COLUMN byte_data TO "data";

ALTER TABLE tbl_files_dto ADD COLUMN byte_upload_data bytea;
UPDATE tbl_files_dto SET byte_upload_data = lo_get(upload_data)::bytea;
ALTER TABLE tbl_files_dto RENAME COLUMN upload_data TO upload_data_bkup;
ALTER TABLE tbl_files_dto RENAME COLUMN byte_upload_data TO upload_data;

CREATE OR REPLACE FUNCTION refresh_vw_grouped_scenario()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
REFRESH MATERIALIZED VIEW vw_grouped_scenario;
RETURN NULL;
END $$;

CREATE TRIGGER refresh_vw_grouped_scenario
AFTER INSERT OR UPDATE OR DELETE OR TRUNCATE
ON tbl_ts_scenario_execution
FOR EACH STATEMENT
EXECUTE PROCEDURE refresh_vw_grouped_scenario();

uidriver:
  autodownload: false

ocular:
  enabled: false

nohup java -jar /usr/appl/gfb/riata/2.8.0/sql-api.jar -Dspring.profiles.active=uat -Dspring.config.location/usr/appl/gfb/riata/2.8.0/application-uat.yml > /usr/appl/gfb/riata/2.8.0/logs/sql-api.log 2>&1 &