oracle-db-delete-dbids-not-in-use.yml

name: "Oracle: Delete Old DBIDs"
run-name: "Oracle: ${{ github.event_name == 'workflow_dispatch' && format('{0}_{1}', github.event.inputs.TargetEnvironment, github.event.inputs.TargetHost) }}_delete_old_dbids"
on:
  workflow_dispatch:
    inputs:
      TargetEnvironment:
        description: "Target environment"
        required: true
        type: choice
        options:
          - "delius-core-dev"
          - "delius-core-test"
          - "delius-core-training"
          - "delius-core-stage"
          - "delius-core-preprod"
          - "delius-core-prod"
          - "delius-mis-dev"
          - "delius-mis-stage"
          - "delius-mis-preprod"
          - "delius-mis-prod"
      TargetHost:
        description: "Backup target host"
        required: true
        type: choice
        options: 
          - "delius_primarydb"
          - "mis_primarydb"
          - "boe_primarydb"
          - "dsd_primarydb"
      VerboseOutput: 
        description: "Verbose Output level"
        type: choice
        options:
          - ""
          - "-v"
          - "-vv"
          - "-vvv"
          - "-vvvv"
      SourceCodeVersion:
        description: "Source version for the hmpps-delius-operation-automation. Enter a pull request, branch, commit ID, tag, or reference."
        type: string
        default: "main"
      SourceConfigVersion:
        description: "Source version for the modernisation-platform-configuration-management. Enter a pull request, branch, commit ID, tag, or reference."
        type: string
        default: "main"
  workflow_call:
    inputs:
      TargetEnvironment:
        description: "Target environment"
        required: true
        type: string
      TargetHost:
        description: "Backup target host"
        required: true
        type: string

# Allow permissions on repository and docker image and OIDC token
permissions:
  contents: read
  packages: read
  id-token: write  # This is required for requesting the JWT

jobs:
  build_rman_target_name:
    runs-on: ubuntu-latest
    outputs:
      RmanTarget: ${{ steps.preparermantargetname.outputs.RmanTarget }}
      TargetEnvironment: ${{ steps.preparermantargetname.outputs.TargetEnvironment }}-preapproved
      SourceCodeVersion: ${{ steps.preparermantargetname.outputs.SourceCodeVersion }}
      SourceConfigVersion: ${{ steps.preparermantargetname.outputs.SourceCodeVersion }}
      SlackChannel: ${{ steps.prepare-slack-channel-name.outputs.SlackChannel }}
    steps:
      - name: Prepare Rman Target
        id: preparermantargetname
        run: |
            if [[ "${{ github.event_name }}" == "workflow_dispatch" ]]
            then
              TargetEnvironment="${{ github.event.inputs.TargetEnvironment }}"
              TargetHost="${{ github.event.inputs.TargetHost }}"
              VerboseOutput="${{ github.event.inputs.VerboseOutput }}"
              SourceCodeVersion="${{ github.event.inputs.SourceCodeVersion }}"
              SourceConfigVersion="${{ github.event.inputs.SourceConfigVersion }}"
            elif [[ "${{ github.event_name }}" == "schedule" ]]
            then
              TargetEnvironment="${{ inputs.TargetEnvironment }}"
              TargetHost="${{ inputs.TargetHost }}"
            fi
            RmanTarget="environment_name_$(echo $TargetEnvironment | sed 's/dev/development_dev/;s/test/test_test/;s/training/test_training/;s/stage/preproduction_stage/;s/pre-prod/preproduction_pre_prod/;s/-prod/_production_prod/;s/-/_/g')_$TargetHost"
            echo "RmanTarget=$RmanTarget" >> $GITHUB_OUTPUT
            echo "TargetEnvironment=$TargetEnvironment" >> $GITHUB_OUTPUT

      - name: Prepare Slack Channel Name
        id: prepare-slack-channel-name
        run: |
            if [[ "${{ github.event.inputs.TargetEnvironment }}" == "delius-core-prod" ]]; then
              echo "SlackChannel=delius-aws-oracle-prod-alerts" >> $GITHUB_OUTPUT
            else
              echo "SlackChannel=delius-aws-oracle-dev-alerts" >> $GITHUB_OUTPUT
            fi

  oracle-db-delete-dbids-not-in-use:
    name: oracle-db-delete-dbids-not-in-use
    needs: build_rman_target_name
    environment: ${{needs.build_rman_target_name.outputs.TargetEnvironment}}
    runs-on: ubuntu-latest
    container:
      image: ghcr.io/ministryofjustice/hmpps-delius-operational-automation:0.78.0
    timeout-minutes: 30
    env:
      delete_dbids_command: ansible-playbook operations/playbooks/oracle_backup/delete_dbids_not_in_use.yml
      inventory: inventory/ansible
      RmanTarget: "${{needs.build_rman_target_name.outputs.RmanTarget}}"
      TargetEnvironment: "${{needs.build_rman_target_name.outputs.TargetEnvironment}}"
      SourceCodeVersion:  "${{needs.build_rman_target_name.outputs.SourceCodeVersion}}"
      SourceConfigVersion:  "${{needs.build_rman_target_name.outputs.SourceConfigVersion}}"
      SlackChannel: ${{ needs.build_rman_target_name.outputs.SlackChannel }}
      ansible_config: operations/playbooks/ansible.cfg
    continue-on-error: false
    steps:

      - name: Checkout hmpps-delius-operation-automation
        uses: actions/checkout@v4
        with:
          sparse-checkout-cone-mode: false
          sparse-checkout: |
            playbooks/oracle_backup
            playbooks/ansible.cfg
          path: operations
          ref: "${{ env.SourceCodeVersion }}"
          fetch-depth: 0

      - name: Checkout Ansible Inventory From modernisation-platform-configuration-management
        uses: actions/checkout@v4
        with:
          repository: ministryofjustice/modernisation-platform-configuration-management
          sparse-checkout-cone-mode: false
          sparse-checkout: |
            ansible/hosts
            ansible/group_vars
          path: inventory
          ref: "${{ env.SourceCodeVersion }}"
          fetch-depth: 0

      - name: Checkout Ansible Required Roles From modernisation-platform-configuration-management
        uses: actions/checkout@v4
        with:
          repository: ministryofjustice/modernisation-platform-configuration-management
          sparse-checkout-cone-mode: false
          sparse-checkout: |
            ansible/roles/secretsmanager-passwords
            ansible/roles/get-modernisation-platform-facts
          path: roles
          ref: "${{ env.SourceConfigVersion }}"
          fetch-depth: 0

      - name: Configure AWS Credentials
        id: login-aws
        uses: aws-actions/configure-aws-credentials@v4
        with:
          role-to-assume: "arn:aws:iam::${{ vars.AWS_ACCOUNT_ID }}:role/modernisation-platform-oidc-cicd"
          role-session-name: "hmpps-delius-operational-automation-${{ github.run_number }}"
          role-duration-seconds: 21600
          aws-region: "eu-west-2"

      - name: Get Slack Token
        id: get-slack-token
        run: |
              # The OEM Account ID which is associated with this Delius account is held in the SSM Parameter Store.
              OEM_ACCOUNT_ID=$(aws ssm get-parameter --name account_ids --region eu-west-2 --with-decryption  --output json | \
                    jq '.Parameter.Value' | tr -d '\' | sed 's/^\"//' | sed 's/\"$//' | \
                    jq -r 'to_entries | map(select(.key | contains("hmpps-oem-"))) | first' | jq -r '.value' )
              # We can then access the Slack Token from the AWS Secrets for that OEM Account
              SECRET_ARN="arn:aws:secretsmanager:eu-west-2:${OEM_ACCOUNT_ID}:secret:/oracle/database/EMREP/shared-passwords"
              SECRET_VALUE=$(aws secretsmanager get-secret-value --secret-id "${SECRET_ARN}" --query SecretString --output json)
              SLACK_TOKEN=$(echo ${SECRET_VALUE} | jq -r | jq -r 'to_entries[] | select(.key=="slack_token").value')
              echo "slack_token=${SLACK_TOKEN}" >> $GITHUB_OUTPUT

      - name: Start Ansible Delete DBIDs Not In Use
        run: |
          export ANSIBLE_CONFIG=$ansible_config
          # We run Ansible play which deletes any backups associated with Oracle DBIDs which are no longer active.
          # These typically result from a refresh of an environment where the previous database is simply 
          # replaced with a new copy from live.  DBIDs targeted for deletion are those which have no recent
          # backup pieces in the S3 backup bucket for the current environment.  We run the logic from the
          # primary database host in the environment.  Any such databases are also de-registered from the
          # RMAN catalog if still present.   (This needs to be run on a database host, rather than the
          # Ansible controller as an Oracle client is required for some steps).
          # It is expected this job is run periodically on a schedule.
          ln -s $PWD/roles/ansible/roles $PWD/operations/playbooks/oracle_backup/roles
          $delete_dbids_command -i $inventory -e ansible_aws_ssm_bucket_name=${{ vars.ANSIBLE_AWS_SSM_BUCKET_NAME }} -e rman_target=$RmanTarget $VerboseOutput
        
      - name: Slack Failure Notification
        id: slack
        if: failure()
        uses: slackapi/slack-github-action@v1.26.0
        with:
            channel-id: "${{ env.SlackChannel }}"
            payload: |
              {"icon_emoji":"large_purple_circle","text":"Failed to Delete Usused DBID Backups","blocks":[{"type": "section","text": {"type": "mrkdwn","text": "Failed to Delete Unused DBID Backups:"}},{"type": "section","fields":[{"type": "mrkdwn","text": "*Workflow:*\n<${{ github.server_url }}/${{ github.repository }}/actions/runs/${{ github.run_id }}|${{ github.workflow }}>"},{"type": "mrkdwn","text": "*Job:*\n${{ github.job }}"},{"type": "mrkdwn","text": "*Repo:*\n${{ github.repository }}"},{"type": "mrkdwn","text": "*Rman Target:*\n${{ env.RmanTarget }}"}]}]}
        env:
          SLACK_BOT_TOKEN: ${{ steps.get-slack-token.outputs.slack_token }}