name: Validate SQL

on:
  pull_request:
    paths:
      - 'sql/pgsql/schema.sql'
      - 'sql/pgsql/migrations/*.sql'
      - '.github/workflows/validate-sql.yml'
  # Allow manual triggering
  workflow_dispatch:
  # Allow other workflows (e.g. Publish) to invoke this one.
  workflow_call:


permissions:
  contents: read


concurrency:
  group: validate-sql-${{ github.ref }}
  cancel-in-progress: true


jobs:
  validate-base-schema:
    name: Validate base schema execution
    runs-on: ubuntu-latest
    timeout-minutes: 10

    env:
      POSTGRES_VERSION: '15' # Keep in sync with service version
      PGPASSWORD: postgres
      PGHOST: localhost
      PGPORT: 5432
      PGUSER: postgres
      PGDATABASE: ttrss_validate

    services:
      postgres:
        image: postgres:15-alpine
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: ttrss_validate
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

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

      - name: Wait for PostgreSQL
        run: |
          until pg_isready; do
            echo 'Waiting for PostgreSQL...'
            sleep 2
          done

      - name: Validate base schema execution
        run: |
          echo "Executing schema.sql against PostgreSQL ${POSTGRES_VERSION}..."
          # Run the schema script with error checking - will fail on any SQL errors
          if psql -v ON_ERROR_STOP=1 -f sql/pgsql/schema.sql; then
            echo '✅ Base schema executed successfully'
          else
            echo '❌ Base schema execution failed'
            exit 1
          fi

  validate-migration-files:
    name: Validate migration files
    runs-on: ubuntu-latest
    timeout-minutes: 5

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

      - name: Validate migration files
        run: |
          # Basic validation of migration files
          # Note: These are incremental updates for older database states.
          # Comment-only files are allowed, but empty/whitespace-only files are treated as errors

          migration_count=0
          sql_files=0
          empty_files=0
          comment_only_files=0
          failed_count=0

          echo 'Validating migration files...'
          for migration in sql/pgsql/migrations/*.sql; do
            if [ -f "$migration" ]; then
              migration_count=$((migration_count + 1))
              migration_name=$(basename "$migration")

              # Check if file is empty or contains only whitespace
              if [ ! -s "$migration" ] || ! grep -q '[^[:space:]]' "$migration"; then
                # File is empty or whitespace-only - treat as error
                echo "❌ $migration_name (empty or whitespace-only)"
                empty_files=$((empty_files + 1))
                failed_count=$((failed_count + 1))
              elif grep -q ';' "$migration"; then
                # Check if semicolons are only in comments
                if grep -v '^\s*--' "$migration" | grep -q ';'; then
                  echo "✅ $migration_name (contains SQL)"
                  sql_files=$((sql_files + 1))
                else
                  echo "✅ $migration_name (comments only - no changes)"
                  comment_only_files=$((comment_only_files + 1))
                fi
              else
                # File has content but no semicolons - check if it's only comments
                if grep -v '^\s*--' "$migration" | grep -q '[^[:space:]]'; then
                  # File has non-comment, non-whitespace content without semicolons - invalid
                  echo "❌ $migration_name (contains content without semicolons - invalid SQL)"
                  empty_files=$((empty_files + 1))
                  failed_count=$((failed_count + 1))
                else
                  # File only has comments or whitespace after removing comment lines
                  echo "✅ $migration_name (comments only - no changes)"
                  comment_only_files=$((comment_only_files + 1))
                fi
              fi
            fi
          done

          echo ''
          echo 'Migration file validation complete:'
          echo "  Total files: $migration_count"
          echo "  With SQL statements: $sql_files"
          echo "  Comments only: $comment_only_files"
          echo "  Empty/whitespace-only: $empty_files"

          if [ $failed_count -gt 0 ]; then
            echo "❌ $failed_count migration files are empty or contain only whitespace"
            exit 1
          else
            echo '✅ All migration files have content'
          fi