server/.github/workflows/_move_edd_db_scripts.yml

name: _move_edd_db_scripts
run-name: Move EDD database scripts

on:
  workflow_call:

permissions:
  pull-requests: write
  contents: write

jobs:
  setup:
    name: Setup
    runs-on: ubuntu-22.04
    permissions:
      contents: read
      id-token: write
    outputs:
      migration_filename_prefix: ${{ steps.prefix.outputs.prefix }}
      copy_edd_scripts: ${{ steps.check-script-existence.outputs.copy_edd_scripts }}

    steps:
      - name: Log in to Azure
        uses: bitwarden/gh-actions/azure-login@main
        with:
          subscription_id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
          tenant_id: ${{ secrets.AZURE_TENANT_ID }}
          client_id: ${{ secrets.AZURE_CLIENT_ID }}

      - name: Retrieve secrets
        id: retrieve-secrets
        uses: bitwarden/gh-actions/get-keyvault-secrets@main
        with:
          keyvault: "bitwarden-ci"
          secrets: "github-pat-bitwarden-devops-bot-repo-scope"

      - name: Log out from Azure
        uses: bitwarden/gh-actions/azure-logout@main

      - name: Check out branch
        uses: actions/checkout@11bd71901bbe5b1630ceea73d27597364c9af683 # v4.2.2
        with:
          token: ${{ steps.retrieve-secrets.outputs.github-pat-bitwarden-devops-bot-repo-scope }}

      - name: Get script prefix
        id: prefix
        run: echo "prefix=$(date +'%Y-%m-%d')" >> $GITHUB_OUTPUT

      - name: Check if any files in DB transition or finalization directories
        id: check-script-existence
        run: |
          if [ -f util/Migrator/DbScripts_transition/* -o -f util/Migrator/DbScripts_finalization/* ]; then
            echo "copy_edd_scripts=true" >> $GITHUB_OUTPUT
          else
            echo "copy_edd_scripts=false" >> $GITHUB_OUTPUT
          fi          

  move-scripts:
    name: Move scripts
    runs-on: ubuntu-22.04
    needs: setup
    permissions:
      contents: write
      pull-requests: write
      id-token: write
      actions: read
    if: ${{ needs.setup.outputs.copy_edd_scripts == 'true' }}
    steps:
      - name: Check out repo
        uses: actions/checkout@11bd71901bbe5b1630ceea73d27597364c9af683 # v4.2.2
        with:
          fetch-depth: 0

      - name: Generate branch name
        id: branch_name
        env:
          PREFIX: ${{ needs.setup.outputs.migration_filename_prefix }}
        run: echo "branch_name=move_edd_db_scripts_$PREFIX" >> $GITHUB_OUTPUT

      - name: "Create branch"
        env:
          BRANCH: ${{ steps.branch_name.outputs.branch_name }}
        run: git switch -c $BRANCH

      - name: Move scripts and finalization database schema
        id: move-files
        env:
          PREFIX: ${{ needs.setup.outputs.migration_filename_prefix }}
        run: |
          # scripts
          moved_files="Migration scripts moved:\n\n"

          src_dirs="util/Migrator/DbScripts_transition,util/Migrator/DbScripts_finalization"
          dest_dir="util/Migrator/DbScripts"
          i=0

          for src_dir in ${src_dirs//,/ }; do
            for file in "$src_dir"/*; do
              filenumber=$(printf "%02d" $i)

              filename=$(basename "$file")
              new_filename="${PREFIX}_${filenumber}_${filename}"
              dest_file="$dest_dir/$new_filename"

              # Replace any finalization references due to the move
              sed -i -e 's/dbo_finalization/dbo/g' "$file"

              mv "$file" "$dest_file"
              moved_files="$moved_files \n $filename -> $new_filename"

              i=$((i+1))
            done
          done

          # schema
          moved_files="$moved_files\n\nFinalization scripts moved:\n\n"

          src_dir="src/Sql/dbo_finalization"
          dest_dir="src/Sql/dbo"

          # sync finalization schema back to dbo, maintaining structure
          rsync -r "$src_dir/" "$dest_dir/"
          rm -rf $src_dir/*

          # Replace any finalization references due to the move
          find ./src/Sql/dbo -name "*.sql" -type f -exec sed -i \
            -e 's/\[dbo_finalization\]/[dbo]/g' \
            -e 's/dbo_finalization\./dbo./g' {} +

          for file in "$src_dir"/**/*; do
            moved_files="$moved_files \n $file"
          done

          echo "moved_files=$moved_files" >> $GITHUB_OUTPUT          

      - name: Log in to Azure
        uses: bitwarden/gh-actions/azure-login@main
        with:
          subscription_id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}
          tenant_id: ${{ secrets.AZURE_TENANT_ID }}
          client_id: ${{ secrets.AZURE_CLIENT_ID }}

      - name: Retrieve secrets
        id: retrieve-secrets
        uses: bitwarden/gh-actions/get-keyvault-secrets@main
        with:
          keyvault: "bitwarden-ci"
          secrets: "github-gpg-private-key,
            github-gpg-private-key-passphrase,
            devops-alerts-slack-webhook-url"

      - name: Log out from Azure
        uses: bitwarden/gh-actions/azure-logout@main

      - name: Import GPG keys
        uses: crazy-max/ghaction-import-gpg@e89d40939c28e39f97cf32126055eeae86ba74ec # v6.3.0
        with:
          gpg_private_key: ${{ steps.retrieve-secrets.outputs.github-gpg-private-key }}
          passphrase: ${{ steps.retrieve-secrets.outputs.github-gpg-private-key-passphrase }}
          git_user_signingkey: true
          git_commit_gpgsign: true

      - name: Commit and push changes
        id: commit
        run: |
          git config --local user.email "106330231+bitwarden-devops-bot@users.noreply.github.com"
          git config --local user.name "bitwarden-devops-bot"
          if [ -n "$(git status --porcelain)" ]; then
            git add .
            git commit -m "Move EDD database scripts" -a
            git push -u origin ${{ steps.branch_name.outputs.branch_name }}
            echo "pr_needed=true" >> $GITHUB_OUTPUT
          else
            echo "No changes to commit!";
            echo "pr_needed=false" >> $GITHUB_OUTPUT
            echo "### :mega: No changes to commit! PR was ommited." >> $GITHUB_STEP_SUMMARY
          fi          

      - name: Create PR for ${{ steps.branch_name.outputs.branch_name }}
        if: ${{ steps.commit.outputs.pr_needed == 'true' }}
        id: create-pr
        env:
          BRANCH: ${{ steps.branch_name.outputs.branch_name }}
          GH_TOKEN: ${{ github.token }}
          MOVED_FILES: ${{ steps.move-files.outputs.moved_files }}
          TITLE: "Move EDD database scripts"
        run: |
          PR_URL=$(gh pr create --title "$TITLE" \
            --base "main" \
            --head "$BRANCH" \
            --label "automated pr" \
            --body "
              Automated movement of EDD database scripts.

              Files moved:
              $(echo -e "$MOVED_FILES")
              ")
          echo "pr_url=${PR_URL}" >> $GITHUB_OUTPUT          

      - name: Notify Slack about creation of PR
        if: ${{ steps.commit.outputs.pr_needed == 'true' }}
        uses: act10ns/slack@44541246747a30eb3102d87f7a4cc5471b0ffb7d # v2.1.0
        env:
          SLACK_WEBHOOK_URL: ${{ steps.retrieve-secrets.outputs.devops-alerts-slack-webhook-url }}
        with:
          message: "Created PR for moving EDD database scripts: ${{ steps.create-pr.outputs.pr_url }}"
          status: ${{ job.status }}