Issue Summary

Bamboo leaves orphan references in the "deployment_env_vcs_location table whenever a VCS task is added and removed from an environment

Steps to Reproduce

1. Create a Linked Repositorie (it can be 2 for better for visibility)
2. Create a Deployment Project + environment
3. Add "Source Code Checkout" task in the deployment environment and select one of the repositories created in the Step 1. 
4. Go to the repository settings and check if the Environment is listed on the "Usages" tab
5. Remove the "Source Code Checkout" task from the Environment
6. Check the repository settings "Usages" tab again and notice the Repository is still linked.

Expected Results

Once the Source Code Checkout task is removed from the deployment, it should not be shown under the Repository usage. 

Actual Results

Once the Source Code Checkout task is removed from the deployment, Bamboo UI still shows the repository is used by the deployment environment. This information is still persisted on the DB table deployment_env_vcs_location.

Workaround

1. Execute the below query in the database to get the repositories that are still shows as used by deployment environment, but actually they are not in use

   WITH
     REPO_USAGE AS (
       SELECT
         DE.ENVIRONMENT_ID,
         DE.NAME,
         CAST(
           UNNEST(
             XPATH(
               '//taskDefinition[pluginKey="com.atlassian.bamboo.plugins.vcs:task.vcs.checkout"]/config/item[starts-with(key, "selectedRepository_")]/value/text()',
               XML_DEFINITION_DATA::XML
             )
           )::TEXT AS BIGINT
         ) AS VCS_LOCATION_ID
       FROM
         DEPLOYMENT_ENVIRONMENT DE
       WHERE
         XML_DEFINITION_DATA LIKE '%com.atlassian.bamboo.plugins.vcs:task.vcs.checkout%'
     )
   SELECT
     DP.NAME AS DEPLOYMENT_PROJECT_NAME,
     DE.NAME AS DEPLOYMENT_ENVIRONMENT_NAME,
     VL.NAME AS REPOSITORY_NAME,
     DEVL.ENV_VCS_LOCATION_ID
   FROM
     DEPLOYMENT_ENV_VCS_LOCATION DEVL
     INNER JOIN VCS_LOCATION VL ON VL.VCS_LOCATION_ID = DEVL.VCS_LOCATION_ID
     INNER JOIN DEPLOYMENT_ENVIRONMENT DE ON DE.ENVIRONMENT_ID = DEVL.ENVIRONMENT_ID
     INNER JOIN DEPLOYMENT_PROJECT DP ON DE.PACKAGE_DEFINITION_ID = DP.DEPLOYMENT_PROJECT_ID
     LEFT OUTER JOIN REPO_USAGE RU ON RU.ENVIRONMENT_ID = DEVL.ENVIRONMENT_ID
     AND RU.VCS_LOCATION_ID = DEVL.VCS_LOCATION_ID
   WHERE
     RU.VCS_LOCATION_ID IS NULL;
   

2. Get all the ENV_VCS_LOCATION_ID from the previous query.
3. Delete the entries from DEPLOYMENT_ENV_VCS_LOCATION table using below query:

     DELETE from DEPLOYMENT_ENV_VCS_LOCATION where ENV_VCS_LOCATION_ID in (<ENV_VCS_LOCATION_ID>);