check-database-migration-files.yml

name: Validate Migration Files

on:
  pull_request:
    branches:
      - '**'

jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Current Branch
        uses: actions/checkout@v3

      - name: Fetch Base Branch
        run: |
          git fetch origin +refs/heads/${{ github.base_ref }}:refs/remotes/origin/${{ github.base_ref }}

      - name: Check Migration Issues
        run: |
          echo "Checking for migration issues..."

          # Set the directory for migrations
          MIGRATION_DIR=src/main/resources/db/migration

          # Create a temporary directory to hold combined migration files
          TEMP_DIR=$(mktemp -d)

          # Get new migration files in the current branch compared to the base branch
          NEW_MIGRATION_FILES=$(git diff --name-only origin/${{ github.base_ref }} -- $MIGRATION_DIR | grep .sql)

          # Merge new files with existing files from the base branch
          BASE_MIGRATION_FILES=$(git ls-tree -r --name-only origin/${{ github.base_ref }} -- $MIGRATION_DIR)

          # Copy new migration files to the temporary directory
          if [ ! -z "$NEW_MIGRATION_FILES" ]; then
            for FILE in $NEW_MIGRATION_FILES; do
              cp "$FILE" "$TEMP_DIR/"
            done
          fi

          # Copy existing migration files from the base branch to the temporary directory
          for FILE in $BASE_MIGRATION_FILES; do
            cp "$MIGRATION_DIR/$FILE" "$TEMP_DIR/"
          done

          # List all migration files in the temporary directory
          ALL_MIGRATION_FILES=$(ls "$TEMP_DIR")

          # Get the latest timestamp from the base branch
          LATEST_BASE_TIMESTAMP=$(echo "$BASE_MIGRATION_FILES" | awk -F'_' '{print $1}' | sort -n | tail -n 1)

          echo "Latest timestamp in base branch: $LATEST_BASE_TIMESTAMP"

          # Validate timestamps
          for FILE in "$TEMP_DIR"/*; do
            TIMESTAMP=$(echo "$(basename "$FILE")" | awk -F'_' '{print $1}')
            # Ensure the timestamp is valid
            if [[ -z "$TIMESTAMP" ]]; then
              echo "File $(basename "$FILE") does not have a valid timestamp."
              exit 1
            fi
            if [[ "$TIMESTAMP" < "$LATEST_BASE_TIMESTAMP" ]]; then
              echo "Timestamp $TIMESTAMP in $(basename "$FILE") is before the latest timestamp in the base branch."
              exit 1
            fi
          done

          echo "All new migration files are valid."

          # Clean up temporary directory
          rm -rf "$TEMP_DIR"