_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
  id-token: write
  actions: read

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: Check out branch
        uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
        with:
          persist-credentials: false

      - 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
    if: ${{ needs.setup.outputs.copy_edd_scripts == 'true' }}
    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-secret
        uses: bitwarden/gh-actions/get-keyvault-secrets@main
        with:
          keyvault: gh-org-bitwarden
          secrets: "BW-GHAPP-ID,BW-GHAPP-KEY"

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

      - name: Generate GH App token
        uses: actions/create-github-app-token@29824e69f54612133e76f7eaac726eef6c875baf # v2.2.1
        id: app-token
        with:
          app-id: ${{ steps.retrieve-secret.outputs.BW-GHAPP-ID }}
          private-key: ${{ steps.retrieve-secret.outputs.BW-GHAPP-KEY }}
          owner: ${{ github.repository_owner }}

      - name: Check out repo
        uses: actions/checkout@de0fac2e4500dabe0009e67214ff5f5447ce83dd # v6.0.2
        with:
          fetch-depth: 0
          persist-credentials: true
          token: ${{ steps.app-token.outputs.token }}

      - 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"
          git push -u origin "$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: Check for changes
        id: commit
        run: |
          if [ -n "$(git status --porcelain)" ]; then
            echo "pr_needed=true" >> "$GITHUB_OUTPUT"
          else
            echo "pr_needed=false" >> "$GITHUB_OUTPUT"
            echo "No changes to commit!"
            echo "### :mega: No changes to commit! PR was omitted." >> "$GITHUB_STEP_SUMMARY"
          fi

      - name: Commit and push changes
        if: ${{ steps.commit.outputs.pr_needed == 'true' }}
        uses: bitwarden/gh-actions/api-commit@main
        with:
          token: ${{ steps.app-token.outputs.token }}
          branch: ${{ steps.branch_name.outputs.branch_name }}
          message: "Move EDD database scripts"

      - 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: ${{ steps.app-token.outputs.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"