name: Schema Drift Check

# Run this workflow:
# - Daily at 8am UTC to catch manual database changes
# - On demand via workflow_dispatch
# - On pull requests that modify schema files
on:
  schedule:
    - cron: '0 8 * * *'
  workflow_dispatch:
  pull_request:
    paths:
      - 'schema/**/*.sql'
      - '.github/workflows/drift-check*.yml'

jobs:
  drift-check:
    runs-on: ubuntu-latest

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

      - name: Check for schema drift
        uses: fmguerreiro/pgmold/.github/actions/drift-check@main
        with:
          schema: 'sql:schema/'
          database: ${{ secrets.DATABASE_URL }}
          target-schemas: 'public'
          fail-on-drift: 'true'

      - name: Post drift report as comment (on PRs)
        if: failure() && github.event_name == 'pull_request'
        uses: actions/github-script@v7
        with:
          script: |
            const report = ${{ steps.drift.outputs.report }};
            const comment = `## Schema Drift Detected

            Expected fingerprint: \`${report.expected_fingerprint}\`
            Actual fingerprint: \`${report.actual_fingerprint}\`

            **Differences:**
            ${report.differences.map(d => `- ${d}`).join('\n')}
            `;

            github.rest.issues.createComment({
              owner: context.repo.owner,
              repo: context.repo.repo,
              issue_number: context.issue.number,
              body: comment
            });

  # Example: Check drift against multiple environments
  drift-check-staging:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v4

      - name: Check staging environment
        uses: fmguerreiro/pgmold/.github/actions/drift-check@main
        with:
          schema: 'sql:schema/'
          database: ${{ secrets.STAGING_DATABASE_URL }}
          target-schemas: 'public,auth,api'

  drift-check-production:
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v4

      - name: Check production environment
        uses: fmguerreiro/pgmold/.github/actions/drift-check@main
        with:
          schema: 'sql:schema/'
          database: ${{ secrets.PRODUCTION_DATABASE_URL }}
          target-schemas: 'public,auth,api'
          version: 'v0.14.2'  # Pin to specific version for production
