database-schema.yml

name: MySQL Schema Dumper

on: [push, pull_request]

jobs:
  schema-dump:
    runs-on: ubuntu-22.04

    services:
      mysql:
        image: mysql:8.0
        env:
          MYSQL_ALLOW_EMPTY_PASSWORD: yes
          MYSQL_DATABASE: unit3d
        ports:
          - 3306:3306
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
      redis:
        image: redis:7.2.1
        ports:
          - 6379:6379
        options: >-
          --health-cmd "redis-cli ping"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - name: Checkout code
        uses: actions/checkout@v4
        with:
          fetch-depth: 0
          ref: development

      - name: Setup PHP 8.4
        uses: shivammathur/setup-php@v2
        with:
          php-version: '8.4'
          extensions: curl, dom, gd, libxml, mbstring, zip, mysql, xml, intl, bcmath, redis-phpredis/phpredis@6.0.1
          tools: composer:v2
          coverage: none
        env:
          REDIS_CONFIGURE_OPTS: --enable-redis

      - name: Configure Bun
        uses: oven-sh/setup-bun@v1
        with:
          bun-version: latest

      - name: Install Composer Dependencies
        env:
          COMPOSER_AUTH: ${{ secrets.COMPOSER_AUTH }}
        run: composer install --no-ansi --no-interaction --no-scripts --no-progress --prefer-dist --optimize-autoloader

      - name: Prepare The Laravel Environment
        run: cp .env.example .env

      - name: Generate Application Key
        run: php artisan key:generate

      - name: Get latest commit that modified migrations
        id: find-commit
        run: |
          LATEST_COMMIT=$(git log --pretty=format:"%H %s" -n 100 | grep -E "migration|schema" | head -n 1 | cut -d' ' -f1)
          COMMIT_MSG=$(git log --pretty=format:"%s" -n 1 $LATEST_COMMIT)
          echo "commit_id=$LATEST_COMMIT" >> $GITHUB_OUTPUT
          echo "commit_msg=$COMMIT_MSG" >> $GITHUB_OUTPUT
          if [ -z "$LATEST_COMMIT" ]; then
            echo "No relevant commit found, using latest commit"
            echo "commit_id=$(git rev-parse HEAD)" >> $GITHUB_OUTPUT
            echo "commit_msg=$(git log --pretty=format:"%s" -n 1 HEAD)" >> $GITHUB_OUTPUT
          fi

      - name: Run Migrations
        run: php artisan migrate --force
        env:
          DB_CONNECTION: mysql
          DB_PORT: ${{ job.services.mysql.ports['3306'] }}
          DB_USERNAME: root
          DB_DATABASE: unit3d
          DB_PASSWORD: null

      - name: Generate Schema Dump
        run: php artisan schema:dump --database=mysql --path=database/schema/mysql-schema-new.sql
        env:
          DB_CONNECTION: mysql
          DB_PORT: ${{ job.services.mysql.ports['3306'] }}
          DB_USERNAME: root
          DB_DATABASE: unit3d
          DB_PASSWORD: null

      - name: Check if schema has changed
        id: diff
        run: |
          if [ -f database/schema/mysql-schema.sql ] && diff -q database/schema/mysql-schema.sql database/schema/mysql-schema-new.sql > /dev/null; then
            echo "No changes detected in schema"
            echo "has_changes=false" >> $GITHUB_OUTPUT
          else
            echo "Changes detected in schema"
            echo "has_changes=true" >> $GITHUB_OUTPUT
            # Copy the new schema file to the final location
            cp database/schema/mysql-schema-new.sql database/schema/mysql-schema.sql
          fi

      - name: Check for existing PRs
        id: check-pr
        if: steps.diff.outputs.has_changes == 'true'
        run: |
          PR_EXISTS=$(gh pr list --base development --head "update-schema-dump*" --json number --jq 'length')
          if [ "$PR_EXISTS" -gt "0" ]; then
            echo "existing_pr=true" >> $GITHUB_OUTPUT
            PR_NUMBER=$(gh pr list --base development --head "update-schema-dump*" --json number --jq '.[0].number')
            BRANCH_NAME=$(gh pr view $PR_NUMBER --json headRefName --jq '.headRefName')
            echo "branch_name=$BRANCH_NAME" >> $GITHUB_OUTPUT
          else
            echo "existing_pr=false" >> $GITHUB_OUTPUT
          fi
        env:
          GH_TOKEN: ${{ secrets.GITHUB_TOKEN }}

      - name: Update existing PR branch
        if: steps.diff.outputs.has_changes == 'true' && steps.check-pr.outputs.existing_pr == 'true'
        run: |
          git config --local user.email "hdinnovations@protonmail.com"
          git config --local user.name "HDVinnie"
          git fetch origin
          git checkout ${{ steps.check-pr.outputs.branch_name }}
          git add database/schema/mysql-schema.sql
          git commit -m "update: MySQL schema dump from commit ${{ steps.find-commit.outputs.commit_id }}"
          git push origin ${{ steps.check-pr.outputs.branch_name }}
        env:
          GH_TOKEN: ${{ secrets.PERSONAL_ACCESS_TOKEN }}

      - name: Create new PR with schema changes
        if: steps.diff.outputs.has_changes == 'true' && steps.check-pr.outputs.existing_pr == 'false'
        uses: peter-evans/create-pull-request@v5
        with:
          token: ${{ secrets.PERSONAL_ACCESS_TOKEN }}
          commit-message: "update: MySQL schema dump from commit ${{ steps.find-commit.outputs.commit_id }}"
          title: "(Update) MySQL schema dump"
          body: |
            This PR updates the MySQL schema dump with the latest migration changes.

            Related to commit: ${{ steps.find-commit.outputs.commit_id }}
            Commit message: ${{ steps.find-commit.outputs.commit_msg }}

            [View commit](https://github.com/HDInnovations/UNIT3D/commit/${{ steps.find-commit.outputs.commit_id }})
          branch: update-schema-dump
          branch-suffix: timestamp
          delete-branch: true
          base: development
          path: .
          add-paths: |
            database/schema/mysql-schema.sql
          author: HDVinnie <hdinnovations@protonmail.com>
          committer: HDVinnie <hdinnovations@protonmail.com>
          draft: false