feature-tests.yml

name: PostgreSQL Feature Tests

on:
  push:
    branches: [main]
    paths:
      - 'Dockerfile*'
      - 'scripts/**'
      - 'docker-entrypoint.sh'
      - 'docker-compose.test.yml'
      - 'test/**'
      - '.github/workflows/feature-tests.yml'
  pull_request:
    branches: [main]
    paths:
      - 'Dockerfile*'
      - 'scripts/**'
      - 'docker-entrypoint.sh'
      - 'docker-compose.test.yml'
      - 'test/**'
      - '.github/workflows/feature-tests.yml'
  workflow_dispatch:

env:
  GO_VERSION: '1.21'
  IMAGE_NAME: 'postgres-enhanced-test'
  POSTGRES_VERSION: '17'

jobs:
  build-test-image:
    runs-on: ubuntu-latest
    outputs:
      image-tag: ${{ steps.image.outputs.tag }}
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Set up Docker Buildx
        uses: docker/setup-buildx-action@v3

      - name: Build test image
        uses: docker/build-push-action@v5
        with:
          context: .
          file: Dockerfile
          push: false
          tags: ${{ env.IMAGE_NAME }}:${{ github.sha }}
          cache-from: type=gha
          cache-to: type=gha,mode=max

      - name: Output image tag
        id: image
        run: echo "tag=${{ env.IMAGE_NAME }}:${{ github.sha }}" >> $GITHUB_OUTPUT

  extension-tests:
    needs: build-test-image
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        test-type:
          - "basic-extensions"
          - "vector-functionality"
          - "crypto-extensions"
          - "json-extensions"
          - "audit-monitoring"
    name: Extension Tests - ${{ matrix.test-type }}
    
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Setup Go
        uses: actions/setup-go@v5
        with:
          go-version: ${{ env.GO_VERSION }}
          cache: true
          cache-dependency-path: test/go.sum

      - name: Install Task
        run: |
          sh -c "$(curl --location https://taskfile.dev/install.sh)" -- -d -b /usr/local/bin

      - name: Update Docker Compose image
        run: |
          sed -i "s|ghcr.io/flanksource/postgres:17-latest|${{ needs.build-test-image.outputs.image-tag }}|g" docker-compose.test.yml

      - name: Run extension tests
        run: |
          case "${{ matrix.test-type }}" in
            basic-extensions)
              task test:test-extensions
              ;;
            vector-functionality)
              task test:verify-extensions
              ;;
            crypto-extensions)
              # Test pgsodium, pgjwt
              docker-compose -f docker-compose.test.yml up -d postgres-test
              sleep 30
              docker-compose -f docker-compose.test.yml exec -T postgres-test-client psql -c "SELECT pgsodium.crypto_secretbox('test', 'key');"
              docker-compose -f docker-compose.test.yml exec -T postgres-test-client psql -c "SELECT extensions.sign('{}', 'secret');"
              docker-compose -f docker-compose.test.yml down -v
              ;;
            json-extensions)
              # Test jsonschema, hashids
              docker-compose -f docker-compose.test.yml up -d postgres-test
              sleep 30
              docker-compose -f docker-compose.test.yml exec -T postgres-test-client psql -c "SELECT jsonschema.json_matches_schema('{}', '{}');"
              docker-compose -f docker-compose.test.yml exec -T postgres-test-client psql -c "SELECT hashids.encode(123, 'salt', 8);"
              docker-compose -f docker-compose.test.yml down -v
              ;;
            audit-monitoring)
              # Test pgaudit, pg_stat_monitor
              docker-compose -f docker-compose.test.yml up -d postgres-test
              sleep 30
              docker-compose -f docker-compose.test.yml exec -T postgres-test-client psql -c "SELECT * FROM pg_extension WHERE extname IN ('pgaudit', 'pg_stat_monitor');"
              docker-compose -f docker-compose.test.yml down -v
              ;;
          esac

      - name: Cleanup on failure
        if: failure()
        run: |
          docker-compose -f docker-compose.test.yml down -v || true
          docker system prune -f || true

  service-tests:
    needs: build-test-image
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        service:
          - "pgbouncer"
          - "postgrest"
          - "walg"
          - "s6-overlay"
    name: Service Tests - ${{ matrix.service }}
    
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Setup Go
        uses: actions/setup-go@v5
        with:
          go-version: ${{ env.GO_VERSION }}
          cache: true
          cache-dependency-path: test/go.sum

      - name: Install Task
        run: |
          sh -c "$(curl --location https://taskfile.dev/install.sh)" -- -d -b /usr/local/bin

      - name: Update Docker Compose image
        run: |
          sed -i "s|ghcr.io/flanksource/postgres:17-latest|${{ needs.build-test-image.outputs.image-tag }}|g" docker-compose.test.yml

      - name: Run service tests
        timeout-minutes: 15
        run: |
          case "${{ matrix.service }}" in
            pgbouncer)
              task test:verify-services
              # Additional PgBouncer specific tests
              docker-compose -f docker-compose.test.yml up -d postgres-test
              sleep 30
              # Test connection through PgBouncer
              docker-compose -f docker-compose.test.yml exec -T postgres-test-client psql -h postgres-test -p 6432 -c "SELECT 1;"
              # Check pool status
              docker-compose -f docker-compose.test.yml exec -T postgres-test-client psql -h postgres-test -p 6432 -d pgbouncer -c "SHOW POOLS;"
              docker-compose -f docker-compose.test.yml down -v
              ;;
            postgrest)
              docker-compose -f docker-compose.test.yml up -d postgres-test
              sleep 30
              # Test PostgREST API
              curl -f http://localhost:13000/ || (echo "PostgREST API test failed" && exit 1)
              curl -f http://localhost:13000/pg_extension || (echo "PostgREST pg_extension endpoint test failed" && exit 1)
              docker-compose -f docker-compose.test.yml down -v
              ;;
            walg)
              docker-compose -f docker-compose.test.yml up -d postgres-test
              sleep 30
              # Test WAL-G binary availability
              docker-compose -f docker-compose.test.yml exec -T postgres-test which wal-g
              docker-compose -f docker-compose.test.yml exec -T postgres-test wal-g --version
              docker-compose -f docker-compose.test.yml down -v
              ;;
            s6-overlay)
              docker-compose -f docker-compose.test.yml up -d postgres-test
              sleep 30
              # Test s6-overlay service supervision
              docker-compose -f docker-compose.test.yml exec -T postgres-test pgrep -f "s6-supervise"
              docker-compose -f docker-compose.test.yml exec -T postgres-test /scripts/service-health.sh
              docker-compose -f docker-compose.test.yml down -v
              ;;
          esac

      - name: Cleanup on failure
        if: failure()
        run: |
          docker-compose -f docker-compose.test.yml down -v || true
          docker system prune -f || true

  load-tests:
    needs: build-test-image
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        load-test:
          - "pgbouncer-load"
          - "extension-load"
          - "concurrent-vectors"
    name: Load Tests - ${{ matrix.load-test }}
    
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Install Task
        run: |
          sh -c "$(curl --location https://taskfile.dev/install.sh)" -- -d -b /usr/local/bin

      - name: Update Docker Compose image
        run: |
          sed -i "s|ghcr.io/flanksource/postgres:17-latest|${{ needs.build-test-image.outputs.image-tag }}|g" docker-compose.test.yml

      - name: Run load tests
        timeout-minutes: 20
        run: |
          case "${{ matrix.load-test }}" in
            pgbouncer-load)
              task test:test-load
              ;;
            extension-load)
              docker-compose -f docker-compose.test.yml --profile load-testing up -d
              sleep 45
              docker-compose -f docker-compose.test.yml exec -T load-tester bash /load-tests/extension-load-test.sh
              docker-compose -f docker-compose.test.yml down -v
              ;;
            concurrent-vectors)
              docker-compose -f docker-compose.test.yml --profile load-testing up -d
              sleep 45
              # Run multiple concurrent vector operations
              for i in {1..5}; do
                docker-compose -f docker-compose.test.yml exec -T load-tester bash -c "
                  PGPASSWORD=testpass psql -h postgres-test -U testuser -d testdb -c '
                  WITH query_vec AS (
                    SELECT ARRAY(SELECT random()::float4 FROM generate_series(1, 128))::vector(128) as vec
                  )
                  SELECT COUNT(*) FROM (
                    SELECT embedding <-> query_vec.vec as distance
                    FROM load_test_vectors, query_vec
                    ORDER BY embedding <-> query_vec.vec
                    LIMIT 10
                  ) t;'" &
              done
              wait
              docker-compose -f docker-compose.test.yml down -v
              ;;
          esac

      - name: Cleanup on failure
        if: failure()
        run: |
          docker-compose -f docker-compose.test.yml down -v || true
          docker system prune -f || true

  integration-tests:
    needs: build-test-image
    runs-on: ubuntu-latest
    name: Docker Integration Tests
    
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Setup Go
        uses: actions/setup-go@v5
        with:
          go-version: ${{ env.GO_VERSION }}
          cache: true
          cache-dependency-path: test/go.sum

      - name: Install Task
        run: |
          sh -c "$(curl --location https://taskfile.dev/install.sh)" -- -d -b /usr/local/bin

      - name: Update Docker Compose image
        run: |
          sed -i "s|ghcr.io/flanksource/postgres:17-latest|${{ needs.build-test-image.outputs.image-tag }}|g" docker-compose.test.yml

      - name: Run comprehensive integration tests
        timeout-minutes: 25
        run: |
          # Build test binaries
          cd test
          go mod download
          go build -o ../bin/integration-test ./postgres_integration_test.go
          cd ..
          
          # Run Docker-based integration tests
          task test:test-integration-docker

      - name: Generate test report
        if: always()
        run: |
          echo "## Integration Test Results" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "### Services Tested:" >> $GITHUB_STEP_SUMMARY
          echo "- ✅ PostgreSQL with 16 extensions" >> $GITHUB_STEP_SUMMARY
          echo "- ✅ PgBouncer connection pooling" >> $GITHUB_STEP_SUMMARY
          echo "- ✅ PostgREST automatic API generation" >> $GITHUB_STEP_SUMMARY
          echo "- ✅ WAL-G backup utility" >> $GITHUB_STEP_SUMMARY
          echo "- ✅ s6-overlay service supervision" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "### Extensions Tested:" >> $GITHUB_STEP_SUMMARY
          echo "- pgvector, pgsodium, pgjwt, pgaudit, pg_tle" >> $GITHUB_STEP_SUMMARY
          echo "- pg_stat_monitor, pg_repack, pg_plan_filter, pg_net" >> $GITHUB_STEP_SUMMARY
          echo "- pg_jsonschema, pg_hashids, pg_cron, pg-safeupdate" >> $GITHUB_STEP_SUMMARY
          echo "- index_advisor, wal2json, hypopg" >> $GITHUB_STEP_SUMMARY

      - name: Cleanup
        if: always()
        run: |
          docker-compose -f docker-compose.test.yml down -v || true
          docker system prune -f || true

  upgrade-with-extensions:
    needs: build-test-image
    runs-on: ubuntu-latest
    strategy:
      fail-fast: false
      matrix:
        upgrade-path:
          - { from: "14", to: "17", extensions: "pgvector,pgaudit,pg_cron" }
          - { from: "15", to: "17", extensions: "pgvector,pgsodium,pgjwt" }
          - { from: "16", to: "17", extensions: "pgvector,pg_stat_monitor,pg_net" }
    name: Upgrade Tests - PostgreSQL ${{ matrix.upgrade-path.from }} to ${{ matrix.upgrade-path.to }} with extensions
    
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Setup Go
        uses: actions/setup-go@v5
        with:
          go-version: ${{ env.GO_VERSION }}
          cache: true
          cache-dependency-path: test/go.sum

      - name: Install Task
        run: |
          sh -c "$(curl --location https://taskfile.dev/install.sh)" -- -d -b /usr/local/bin

      - name: Run upgrade tests with extensions
        timeout-minutes: 20
        run: |
          cd test
          go mod download
          
          # Run upgrade test with extensions enabled
          go test -v -timeout 15m -run TestPostgresUpgrade \
            -args \
            -from=${{ matrix.upgrade-path.from }} \
            -to=${{ matrix.upgrade-path.to }} \
            -extensions="${{ matrix.upgrade-path.extensions }}"

      - name: Cleanup
        if: always()
        run: |
          docker system prune -f || true

  test-summary:
    needs: [extension-tests, service-tests, load-tests, integration-tests, upgrade-with-extensions]
    runs-on: ubuntu-latest
    if: always()
    
    steps:
      - name: Generate summary
        run: |
          echo "# PostgreSQL Feature Test Results" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          echo "## Test Matrix Completion" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          
          # Check job results
          echo "### Extension Tests" >> $GITHUB_STEP_SUMMARY
          echo "Result: ${{ needs.extension-tests.result }}" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          
          echo "### Service Tests" >> $GITHUB_STEP_SUMMARY
          echo "Result: ${{ needs.service-tests.result }}" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          
          echo "### Load Tests" >> $GITHUB_STEP_SUMMARY
          echo "Result: ${{ needs.load-tests.result }}" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          
          echo "### Integration Tests" >> $GITHUB_STEP_SUMMARY
          echo "Result: ${{ needs.integration-tests.result }}" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          
          echo "### Upgrade with Extensions Tests" >> $GITHUB_STEP_SUMMARY
          echo "Result: ${{ needs.upgrade-with-extensions.result }}" >> $GITHUB_STEP_SUMMARY
          echo "" >> $GITHUB_STEP_SUMMARY
          
          # Overall status
          if [[ "${{ needs.extension-tests.result }}" == "success" && 
                "${{ needs.service-tests.result }}" == "success" && 
                "${{ needs.load-tests.result }}" == "success" && 
                "${{ needs.integration-tests.result }}" == "success" && 
                "${{ needs.upgrade-with-extensions.result }}" == "success" ]]; then
            echo "## ✅ All Feature Tests Passed" >> $GITHUB_STEP_SUMMARY
          else
            echo "## ❌ Some Feature Tests Failed" >> $GITHUB_STEP_SUMMARY
          fi