pgschema-multifile-apply.yml

name: pgschema Apply - Multi File

on:
  pull_request:
    types: [closed]
    branches:
      - main
    paths:
      - "multifile/**"
      - ".github/workflows/pgschema-multifile-apply.yml"

permissions:
  contents: read
  pull-requests: write

jobs:
  pgschema-apply-multi:
    runs-on: ubuntu-latest
    if: github.event.pull_request.merged == true

    env:
      PGPASSWORD: postgres

    services:
      postgres:
        image: postgres:17
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_USER: postgres
          POSTGRES_DB: testdb
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Setup Go
        uses: actions/setup-go@v5
        with:
          go-version: "stable"

      - name: Install pgschema
        run: go install github.com/pgplex/pgschema@latest

      - name: Download plan.json artifact
        uses: dawidd6/action-download-artifact@v6
        with:
          workflow: pgschema-multifile-plan.yml
          pr: ${{ github.event.pull_request.number }}
          name: pgschema-plan-${{ github.event.pull_request.number }}
          path: .

      - name: Validate plan.json
        run: |
          if [ ! -f "plan.json" ]; then
            echo "❌ Error: plan.json not found. Make sure the plan workflow completed successfully."
            exit 1
          fi

          echo "✅ plan.json found and ready for apply"
          echo "Plan file size: $(stat -f%z plan.json 2>/dev/null || stat -c%s plan.json) bytes"

      - name: Load baseline schema
        run: |
          echo "::group::Loading baseline schema to emulate remote database"
          PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d testdb -f baseline.sql
          echo "::endgroup::"

      - name: Run pgschema apply
        id: apply
        run: |
          echo "::group::Applying schema changes using plan.json"
          echo "Running pgschema apply with pre-generated plan..."

          # Enable detailed error reporting
          set -x  # Show commands as they execute

          # Run pgschema apply using the plan.json file
          APPLY_OUTPUT=$(pgschema apply \
            --debug \
            --host localhost \
            --port 5432 \
            --db testdb \
            --user postgres \
            --plan plan.json \
            --lock-timeout "30s" \
            --application-name "pgschema-github-action-apply" \
            --auto-approve \
            2>&1)

          APPLY_EXIT_CODE=$?

          set +x  # Disable command tracing

          echo "Apply exit code: $APPLY_EXIT_CODE"
          echo "Apply output:"
          echo "$APPLY_OUTPUT"

          echo "::endgroup::"

          # Set outputs for potential future use
          echo "output<<EOF" >> $GITHUB_OUTPUT
          echo "$APPLY_OUTPUT" >> $GITHUB_OUTPUT
          echo "EOF" >> $GITHUB_OUTPUT

          echo "exit_code=$APPLY_EXIT_CODE" >> $GITHUB_OUTPUT

          # Exit with the same code as pgschema
          exit $APPLY_EXIT_CODE

      - name: Report Success
        if: success()
        run: |
          echo "✅ Schema changes applied successfully!"
          echo ""
          echo "Applied to database: testdb"
          echo "Application name: pgschema-github-action-apply"
          echo "Lock timeout: 30s"

      - name: Report Failure
        if: failure()
        run: |
          echo "❌ Failed to apply schema changes!"
          echo ""
          echo "Please check the logs above for details."

      - name: Comment on PR with migration results
        if: always()
        uses: actions/github-script@v7
        with:
          script: |
            // Get the apply output from the previous step
            const applyOutput = `${{ steps.apply.outputs.output }}` || 'No output captured';

            // Determine if migration was successful based on job outcome
            const wasSuccessful = `${{ job.status }}` === 'success';

            let commentBody;

            if (wasSuccessful) {
              commentBody = `## ✅ Schema Changes Applied Successfully!

            📋 **Applied using plan:** \`pgschema-plan-${{ github.event.pull_request.number }}\`

            <details>
            <summary>📋 Applied Changes</summary>

            \`\`\`
            ${applyOutput}
            \`\`\`

            </details>

            **Database:** testdb

            ---
            *This comment was automatically generated by the [pgschema](https://www.pgschema.com) Multi File Apply workflow.*`;
            } else {
              commentBody = `## ❌ Schema Migration Failed!

            The multi-file schema migration failed after merging this PR using plan \`pgschema-plan-${{ github.event.pull_request.number }}\`.

            This could indicate:
            - Database schema changed between plan and apply (fingerprint mismatch)
            - Invalid plan.json file
            - Database connectivity issues

            Please review the error details below:

            <details>
            <summary>🔍 Error Details</summary>

            \`\`\`
            ${applyOutput}
            \`\`\`

            </details>

            **Database:** testdb

            ---
            *This comment was automatically generated by the [pgschema](https://www.pgschema.com) Multi File Apply workflow.*`;
            }

            // Try to find existing comment
            const { data: comments } = await github.rest.issues.listComments({
              ...context.repo,
              issue_number: context.issue.number,
            });

            const botComment = comments.find(comment => 
              comment.user.type === 'Bot' && 
              comment.body.includes('pgschema Multi File Apply workflow')
            );

            if (botComment) {
              // Update existing comment
              await github.rest.issues.updateComment({
                ...context.repo,
                comment_id: botComment.id,
                body: commentBody
              });
            } else {
              // Create new comment
              await github.rest.issues.createComment({
                ...context.repo,
                issue_number: context.issue.number,
                body: commentBody
              });
            }