check-database-migration-files.yml

name: Flyway 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 for new migration files
        run: |
          echo "Checking for new migration files..."

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

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

          # Initialize variable to hold the latest base file
          LATEST_BASE_FILE=""
          LATEST_BASE_TIMESTAMP=""

          # Determine the latest timestamp from the base branch
          for BASE_FILE in $BASE_MIGRATION_FILES; do
            BASE_TIMESTAMP=$(echo $BASE_FILE | grep -oP 'V\K[0-9_]{10,14}')

            # Check if this base timestamp is later than the current latest
            if [[ "$LATEST_BASE_TIMESTAMP" < "$BASE_TIMESTAMP" ]]; then
              LATEST_BASE_TIMESTAMP="$BASE_TIMESTAMP"
              LATEST_BASE_FILE="$BASE_FILE"
            fi
          done

          echo " "
          echo "Base migration file with the latest timestamp:"
          echo "    $LATEST_BASE_FILE"

          # Get the new migration files added in the current branch
          NEW_MIGRATION_FILES=$(git diff --name-only origin/${{ github.base_ref }} -- $MIGRATION_DIR)

          # Output new migration files found in the current branch
          echo " "
          if [[ -z "$NEW_MIGRATION_FILES" ]]; then
            echo "No new migration files found."
            exit 0
          else
            echo "New migration files in the current branch:"
            # Output the new migration files with indentation
            for FILE in $NEW_MIGRATION_FILES; do
              echo "    $FILE"
            done
          fi

          FORMAT_ERROR_FILES=()
          BASE_BRANCH_NAME=${{ github.base_ref }}
          CURRENT_BRANCH_NAME=${{ github.head_ref }}

          # First check for format validity
          for FILE in $NEW_MIGRATION_FILES; do
            if ! [[ "$FILE" =~ ^V[0-9]{4}_[0-9]{2}_[0-9]{2}_[0-9]{4}$ ]]; then
              FORMAT_ERROR_FILES+=("$FILE")  # Collect the file name for invalid format
            fi
          done

          # Output errors for format
          if [[ ${#FORMAT_ERROR_FILES[@]} -gt 0 ]]; then
            echo " "
            echo "Error: The following migration file names do not have a valid timestamp format:"
            for FORMAT_ERROR_FILE in "${FORMAT_ERROR_FILES[@]}"; do
              echo "    $FORMAT_ERROR_FILE"
            done
            exit 1  # Exit with an error code
          fi

          ERROR_FILES=()

          # Check for new migration files and their timestamps
          for FILE in $NEW_MIGRATION_FILES; do
            TIMESTAMP=$(echo $FILE | grep -oP 'V\K[0-9_]{10,14}')
            # Ensure the timestamp is valid
            if [[ -z "$TIMESTAMP" ]]; then
              echo "Error: File $FILE does not have a valid timestamp."
              exit 1
            fi

            # Validate against the latest base file timestamp
            if [[ "$TIMESTAMP" < "$LATEST_BASE_TIMESTAMP" ]]; then
              ERROR_FILES+=("$FILE")  # Collect the file name for later output
            fi
          done

          # Output errors for timestamps
          if [[ ${#ERROR_FILES[@]} -gt 0 ]]; then
            echo " "
            echo "Error: The following migration file names in the current branch [$CURRENT_BRANCH_NAME]:"
            for ERROR_FILE in "${ERROR_FILES[@]}"; do
              echo "    $ERROR_FILE"
            done
            echo "Contain timestamps earlier than the latest migration file from the base branch [$BASE_BRANCH_NAME]:"
            echo "    $LATEST_BASE_FILE"
            exit 1  # Exit with an error code
          fi

          echo "All new migration files are valid."