---
name: CI

on:
  # Only trigger on pull_request events to avoid duplicate runs
  # Push events to dev/master are disabled because they conflict with PR events
  # PR events provide better context and eliminate duplicate workflow runs
  # If you need CI on direct pushes (without PR), you can re-enable push events
  # but be aware that duplicate runs may occur when both push and PR events fire
  pull_request:
    types: [opened, synchronize, reopened]
    # PRs targeting any branch will trigger CI
    # This includes PRs to master/dev and feature branch PRs

# Prevent duplicate runs when pushing to a branch with an open PR
# This ensures only one workflow runs at a time for the same commit
concurrency:
  # Use commit SHA and branch to unify push and PR events for the same commit
  # This prevents duplicate runs when both push and PR events fire for the same commit
  # For PRs: uses base branch (target) and head SHA (the commit being tested)
  # For pushes: uses branch name and commit SHA from the push event
  # The group ensures push and PR events for the same branch+commit are grouped together
  # If both push and PR events fire for the same commit on the same branch, only one will run
  group: ${{ github.workflow }}-${{ github.event.pull_request.base.ref || github.ref_name }}-${{ github.event.pull_request.head.sha || github.event.after || github.sha }}
  cancel-in-progress: true

jobs:
  # Check if CI should be skipped based on branch name or commit message
  skip-ci-check:
    runs-on: ubuntu-latest
    timeout-minutes: 5
    outputs:
      should-skip: ${{ steps.check.outputs.skip }}
    steps:
      - name: Check out code (for commit message)
        uses: actions/checkout@v4
        with:
          fetch-depth: 1
          submodules: false
          persist-credentials: false
          clean: true

      - name: Check if CI should be skipped
        id: check
        run: |
          # Simple skip pattern: @skipci (case-insensitive)
          SKIP_PATTERN="@skipci"

          # Get branch name (works for both push and PR)
          BRANCH_NAME="${GITHUB_HEAD_REF:-${GITHUB_REF#refs/heads/}}"

          # Get commit message (works for both push and PR)
          COMMIT_MSG="${GITHUB_EVENT_HEAD_COMMIT_MESSAGE:-}"
          if [ -z "$COMMIT_MSG" ]; then
            COMMIT_MSG="${GITHUB_EVENT_PULL_REQUEST_HEAD_COMMIT_MESSAGE:-}"
          fi
          if [ -z "$COMMIT_MSG" ]; then
            COMMIT_MSG=$(git log -1 --pretty=%B 2>/dev/null || echo "")
          fi

          SKIP=0

          # Note: Push events are disabled in workflow triggers to prevent duplicates
          # Only pull_request events trigger this workflow now
          # If push events are re-enabled, add skip logic here to prevent duplicates

          # Check branch name (case-insensitive)
          if [ $SKIP -eq 0 ] && echo "$BRANCH_NAME" | grep -qiF "$SKIP_PATTERN"; then
            echo "Skipping CI: branch name contains '$SKIP_PATTERN'"
            SKIP=1
          fi

          # Check commit message (case-insensitive)
          if [ $SKIP -eq 0 ] && [ -n "$COMMIT_MSG" ]; then
            if echo "$COMMIT_MSG" | grep -qiF "$SKIP_PATTERN"; then
              echo "Skipping CI: commit message contains '$SKIP_PATTERN'"
              SKIP=1
            fi
          fi

          echo "skip=$SKIP" >> $GITHUB_OUTPUT
          echo "Branch: $BRANCH_NAME"
          echo "Event: ${GITHUB_EVENT_NAME}"
          echo "Commit: ${COMMIT_MSG:0:50}..."
          echo "Skip CI: $SKIP"

  lint-and-type-check:
    needs: skip-ci-check
    runs-on: ubuntu-latest
    if: needs.skip-ci-check.outputs.should-skip != '1'
    container:
      image: node:20-bullseye
    steps:
      - name: Check out code
        uses: actions/checkout@v4

      - name: Install admin-frontend dependencies
        run: |
          cd admin-frontend
          npm ci

      - name: Audit admin-frontend dependencies
        run: |
          cd admin-frontend
          npm audit --audit-level=moderate || true
        continue-on-error: true

      - name: Run ESLint (admin-frontend)
        id: eslint-check
        run: |
          cd admin-frontend
          npm run lint > /tmp/eslint-output.txt 2>&1 || true
        continue-on-error: true

      - name: Install viewer-frontend dependencies
        run: |
          cd viewer-frontend
          npm ci

      - name: Generate Prisma Clients (for type-check)
        run: |
          cd viewer-frontend
          npm run prisma:generate:all || true
        continue-on-error: true

      - name: Audit viewer-frontend dependencies
        run: |
          cd viewer-frontend
          npm audit --audit-level=moderate || true
        continue-on-error: true

      - name: Type check (viewer-frontend)
        id: type-check
        run: |
          cd viewer-frontend
          npm run type-check > /tmp/typecheck-output.txt 2>&1 || true
        continue-on-error: true

      - name: Check for lint/type-check failures
        if: always()
        run: |
          echo "═══════════════════════════════════════════════════════════════"
          echo "📋 LINT AND TYPE-CHECK SUMMARY"
          echo "═══════════════════════════════════════════════════════════════"
          echo ""
          
          FAILED=false
          
          # ESLint summary
          echo "## ESLint (admin-frontend) Results"
          if [ "x${{ steps.eslint-check.outcome }}" = "xfailure" ]; then
            echo "❌ ESLint check failed (errors found)"
            FAILED=true
          else
            echo "✅ ESLint check passed (warnings may be present)"
          fi
          
          echo ""
          echo "### ESLint Output:"
          if [ -f /tmp/eslint-output.txt ] && [ -s /tmp/eslint-output.txt ]; then
            cat /tmp/eslint-output.txt
          else
            echo "No errors or warnings found."
          fi
          
          echo ""
          echo "---"
          echo ""
          
          # Type check summary
          echo "## Type Check (viewer-frontend) Results"
          if [ "x${{ steps.type-check.outcome }}" = "xfailure" ]; then
            echo "❌ Type check failed (errors found)"
            FAILED=true
          else
            echo "✅ Type check passed"
          fi
          
          echo ""
          echo "### Type Check Output:"
          if [ -f /tmp/typecheck-output.txt ] && [ -s /tmp/typecheck-output.txt ]; then
            cat /tmp/typecheck-output.txt
          else
            echo "No errors found."
          fi
          
          echo ""
          echo "═══════════════════════════════════════════════════════════════"
          
          if [ "$FAILED" = "true" ]; then
            echo "❌ One or more checks failed. Failing job."
            exit 1
          else
            echo "✅ All checks passed"
          fi

  python-lint:
    needs: skip-ci-check
    runs-on: ubuntu-latest
    if: needs.skip-ci-check.outputs.should-skip != '1'
    container:
      image: python:3.12-slim
    steps:
      - name: Install Node.js for checkout action
        run: |
          apt-get update && apt-get install -y curl
          curl -fsSL https://deb.nodesource.com/setup_20.x | bash -
          apt-get install -y nodejs

      - name: Check out code
        uses: actions/checkout@v4

      - name: Install Python dependencies
        run: |
          pip install --no-cache-dir flake8 black mypy pylint

      - name: Check Python syntax
        id: python-syntax-check
        run: |
          find backend -name "*.py" -exec python -m py_compile {} \; 2>&1 | tee /tmp/python-syntax-output.txt || true
        continue-on-error: true

      - name: Run flake8
        id: flake8-check
        run: |
          flake8 backend --max-line-length=100 --ignore=E501,W503,W293,E305,F401,F811,W291,W391,E712,W504,F841,E402,F824,E128,E226,F402,F541,E302,E117,E722 2>&1 | tee /tmp/flake8-output.txt || true
        continue-on-error: true

      - name: Check for Python lint failures
        if: always()
        run: |
          echo "═══════════════════════════════════════════════════════════════"
          echo "📋 PYTHON LINT SUMMARY"
          echo "═══════════════════════════════════════════════════════════════"
          echo ""
          
          FAILED=false
          
          # Python syntax check summary
          echo "## Python Syntax Check Results"
          if [ "x${{ steps.python-syntax-check.outcome }}" = "xfailure" ]; then
            echo "❌ Python syntax check failed"
            FAILED=true
          else
            echo "✅ Python syntax check passed"
          fi
          
          if [ -f /tmp/python-syntax-output.txt ] && [ -s /tmp/python-syntax-output.txt ]; then
            echo ""
            echo "### Syntax Check Output:"
            cat /tmp/python-syntax-output.txt
          fi
          
          echo ""
          echo "---"
          echo ""
          
          # Flake8 summary
          echo "## Flake8 Results"
          if [ "x${{ steps.flake8-check.outcome }}" = "xfailure" ]; then
            echo "❌ Flake8 check failed (errors found)"
            FAILED=true
          else
            echo "✅ Flake8 check passed (warnings may be present)"
          fi
          
          if [ -f /tmp/flake8-output.txt ] && [ -s /tmp/flake8-output.txt ]; then
            echo ""
            echo "### Flake8 Output (errors and warnings):"
            cat /tmp/flake8-output.txt
            
            # Count errors and warnings
            ERROR_COUNT=$(grep -cE "^backend/.*:.*:.* E[0-9]" /tmp/flake8-output.txt 2>/dev/null || echo "0")
            WARNING_COUNT=$(grep -cE "^backend/.*:.*:.* W[0-9]" /tmp/flake8-output.txt 2>/dev/null || echo "0")
            F_COUNT=$(grep -cE "^backend/.*:.*:.* F[0-9]" /tmp/flake8-output.txt 2>/dev/null || echo "0")
            
            echo ""
            echo "### Summary Statistics:"
            echo "- Errors (E*): $ERROR_COUNT"
            echo "- Warnings (W*): $WARNING_COUNT"
            echo "- Pyflakes (F*): $F_COUNT"
          else
            echo ""
            echo "### Flake8 Output:"
            echo "No errors or warnings found."
          fi
          
          echo ""
          echo "═══════════════════════════════════════════════════════════════"
          
          if [ "$FAILED" = "true" ]; then
            echo "❌ One or more Python lint checks failed. Failing job."
            exit 1
          else
            echo "✅ All Python lint checks passed"
          fi

  test-backend:
    needs: skip-ci-check
    runs-on: ubuntu-latest
    if: needs.skip-ci-check.outputs.should-skip != '1'
    container:
      image: python:3.12-slim
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: punimtag_test
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432
      redis:
        image: redis:7-alpine
        options: >-
          --health-cmd "redis-cli ping"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 6379:6379
    env:
      DATABASE_URL: postgresql+psycopg2://postgres:postgres@postgres:5432/punimtag_test
      DATABASE_URL_AUTH: postgresql+psycopg2://postgres:postgres@postgres:5432/punimtag_auth_test
      REDIS_URL: redis://redis:6379/0
    steps:
      - name: Install Node.js for checkout action
        run: |
          apt-get update && apt-get install -y curl
          curl -fsSL https://deb.nodesource.com/setup_20.x | bash -
          apt-get install -y nodejs

      - name: Check out code
        uses: actions/checkout@v4

      - name: Install Python dependencies
        run: |
          apt-get update && apt-get install -y postgresql-client
          pip install --no-cache-dir -r requirements.txt
          pip install --no-cache-dir pytest httpx pytest-cov

      - name: Audit Python dependencies
        run: |
          pip install --no-cache-dir pip-audit
          pip-audit --desc || true
        continue-on-error: true

      - name: Create test databases
        run: |
          export PGPASSWORD=postgres
          psql -h postgres -U postgres -c "CREATE DATABASE punimtag_test;" || true
          psql -h postgres -U postgres -c "CREATE DATABASE punimtag_auth_test;" || true
          echo "✅ Test databases ready"

      - name: Initialize database schemas
        run: |
          export PYTHONPATH=$(pwd)
          echo "🗃️  Initializing main database schema..."
          python -c "from backend.db.models import Base; from backend.db.session import engine; Base.metadata.create_all(bind=engine)"
          echo "✅ Main database schema initialized"
          python << 'EOF'
          # Initialize auth database schema without importing worker (avoids DeepFace/TensorFlow imports)
          from backend.db.session import auth_engine
          from sqlalchemy import text
          
          if auth_engine is None:
              print("⚠️  Auth database not configured, skipping auth schema initialization")
          else:
              try:
                  print("🗃️  Setting up auth database tables...")
                  with auth_engine.connect() as conn:
                      # Create users table
                      conn.execute(text("""
                          CREATE TABLE IF NOT EXISTS users (
                              id SERIAL PRIMARY KEY,
                              email VARCHAR(255) UNIQUE NOT NULL,
                              name VARCHAR(255) NOT NULL,
                              password_hash VARCHAR(255) NOT NULL,
                              is_admin BOOLEAN DEFAULT FALSE,
                              has_write_access BOOLEAN DEFAULT FALSE,
                              email_verified BOOLEAN DEFAULT FALSE,
                              email_confirmation_token VARCHAR(255) UNIQUE,
                              email_confirmation_token_expiry TIMESTAMP,
                              password_reset_token VARCHAR(255) UNIQUE,
                              password_reset_token_expiry TIMESTAMP,
                              is_active BOOLEAN DEFAULT TRUE,
                              created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                              updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                          );
                      """))
                      
                      # Add missing columns if table already exists
                      for col_def in [
                          "ALTER TABLE users ADD COLUMN IF NOT EXISTS is_admin BOOLEAN DEFAULT FALSE",
                          "ALTER TABLE users ADD COLUMN IF NOT EXISTS has_write_access BOOLEAN DEFAULT FALSE",
                          "ALTER TABLE users ADD COLUMN IF NOT EXISTS email_verified BOOLEAN DEFAULT FALSE",
                          "ALTER TABLE users ADD COLUMN IF NOT EXISTS email_confirmation_token VARCHAR(255)",
                          "ALTER TABLE users ADD COLUMN IF NOT EXISTS email_confirmation_token_expiry TIMESTAMP",
                          "ALTER TABLE users ADD COLUMN IF NOT EXISTS password_reset_token VARCHAR(255)",
                          "ALTER TABLE users ADD COLUMN IF NOT EXISTS password_reset_token_expiry TIMESTAMP",
                          "ALTER TABLE users ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE",
                      ]:
                          try:
                              conn.execute(text(col_def))
                          except Exception:
                              pass
                      
                      # Create unique indexes
                      conn.execute(text("""
                          CREATE UNIQUE INDEX IF NOT EXISTS users_email_confirmation_token_key 
                          ON users(email_confirmation_token) 
                          WHERE email_confirmation_token IS NOT NULL;
                      """))
                      conn.execute(text("""
                          CREATE UNIQUE INDEX IF NOT EXISTS users_password_reset_token_key 
                          ON users(password_reset_token) 
                          WHERE password_reset_token IS NOT NULL;
                      """))
                      
                      # Create pending_identifications table
                      conn.execute(text("""
                          CREATE TABLE IF NOT EXISTS pending_identifications (
                              id SERIAL PRIMARY KEY,
                              face_id INTEGER NOT NULL,
                              user_id INTEGER NOT NULL,
                              first_name VARCHAR(255) NOT NULL,
                              last_name VARCHAR(255) NOT NULL,
                              middle_name VARCHAR(255),
                              maiden_name VARCHAR(255),
                              date_of_birth DATE,
                              status VARCHAR(50) DEFAULT 'pending',
                              created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                              updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                              FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                          );
                      """))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_identifications_face_id ON pending_identifications(face_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_identifications_user_id ON pending_identifications(user_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_identifications_status ON pending_identifications(status);"))
                      
                      # Create pending_photos table
                      conn.execute(text("""
                          CREATE TABLE IF NOT EXISTS pending_photos (
                              id SERIAL PRIMARY KEY,
                              user_id INTEGER NOT NULL,
                              filename VARCHAR(255) NOT NULL,
                              original_filename VARCHAR(255) NOT NULL,
                              file_path VARCHAR(512) NOT NULL,
                              file_size INTEGER NOT NULL,
                              mime_type VARCHAR(100) NOT NULL,
                              status VARCHAR(50) DEFAULT 'pending',
                              submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                              reviewed_at TIMESTAMP,
                              reviewed_by INTEGER,
                              rejection_reason TEXT,
                              FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                          );
                      """))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_photos_user_id ON pending_photos(user_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_photos_status ON pending_photos(status);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_photos_submitted_at ON pending_photos(submitted_at);"))
                      
                      # Create inappropriate_photo_reports table
                      conn.execute(text("""
                          CREATE TABLE IF NOT EXISTS inappropriate_photo_reports (
                              id SERIAL PRIMARY KEY,
                              photo_id INTEGER NOT NULL,
                              user_id INTEGER NOT NULL,
                              status VARCHAR(50) DEFAULT 'pending',
                              reported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                              reviewed_at TIMESTAMP,
                              reviewed_by INTEGER,
                              review_notes TEXT,
                              report_comment TEXT,
                              FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                              UNIQUE(photo_id, user_id)
                          );
                      """))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_photo_id ON inappropriate_photo_reports(photo_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_user_id ON inappropriate_photo_reports(user_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_status ON inappropriate_photo_reports(status);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_reported_at ON inappropriate_photo_reports(reported_at);"))
                      
                      # Create pending_linkages table
                      conn.execute(text("""
                          CREATE TABLE IF NOT EXISTS pending_linkages (
                              id SERIAL PRIMARY KEY,
                              photo_id INTEGER NOT NULL,
                              tag_id INTEGER,
                              tag_name VARCHAR(255),
                              user_id INTEGER NOT NULL,
                              status VARCHAR(50) DEFAULT 'pending',
                              notes TEXT,
                              created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                              updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                              FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                          );
                      """))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_linkages_photo_id ON pending_linkages(photo_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_linkages_tag_id ON pending_linkages(tag_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_linkages_user_id ON pending_linkages(user_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_pending_linkages_status ON pending_linkages(status);"))
                      
                      # Create photo_favorites table
                      conn.execute(text("""
                          CREATE TABLE IF NOT EXISTS photo_favorites (
                              id SERIAL PRIMARY KEY,
                              photo_id INTEGER NOT NULL,
                              user_id INTEGER NOT NULL,
                              favorited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                              FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
                              UNIQUE(photo_id, user_id)
                          );
                      """))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_photo_favorites_photo_id ON photo_favorites(photo_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_photo_favorites_user_id ON photo_favorites(user_id);"))
                      conn.execute(text("CREATE INDEX IF NOT EXISTS idx_photo_favorites_favorited_at ON photo_favorites(favorited_at);"))
                      
                      conn.commit()
                  
                  print("✅ Auth database tables created/verified successfully")
              except Exception as e:
                  print(f"⚠️  Failed to create auth database tables: {e}")
          EOF
          echo "✅ Database schemas initialized (main and auth)"

      - name: Run backend tests
        id: backend-tests
        run: |
          export PYTHONPATH=$(pwd)
          export SKIP_DEEPFACE_IN_TESTS=1
          echo "🧪 Running all backend API tests..."
          echo "⚠️  DeepFace/TensorFlow disabled in tests to avoid CPU instruction errors"
          python -m pytest tests/ -v --tb=short --cov=backend --cov-report=term-missing --cov-report=xml --junit-xml=test-results.xml || true
        continue-on-error: true

      - name: Check for test failures
        if: always()
        run: |
          if [ "x${{ steps.backend-tests.outcome }}" = "xfailure" ]; then
            echo "❌ Backend tests failed. Failing job."
            exit 1
          else
            echo "✅ Backend tests passed"
          fi
      
      - name: Test results summary
        if: always()
        run: |
          echo "═══════════════════════════════════════════════════════════════"
          echo "📊 BACKEND TEST RESULTS SUMMARY"
          echo "═══════════════════════════════════════════════════════════════"
          echo ""
          
          # Parse pytest output from the last run
          if [ -f .pytest_cache/v/cache/lastfailed ]; then
            echo "❌ Some tests failed"
            FAILED_COUNT=$(cat .pytest_cache/v/cache/lastfailed | grep -c "test_" || echo "0")
          else
            FAILED_COUNT=0
          fi
          
          # Try to extract test statistics from pytest output
          # Look for the summary line at the end of pytest output
          if [ -f test-results.xml ]; then
            echo "✅ Test results XML file generated"
            
            # Parse JUnit XML if python is available (simplified to avoid YAML parsing issues)
            python3 -c "import xml.etree.ElementTree as ET; tree = ET.parse('test-results.xml') if __import__('os').path.exists('test-results.xml') else None; root = tree.getroot() if tree else None; suites = root.findall('.//testsuite') if root else []; total = sum(int(s.get('tests', 0)) for s in suites); failures = sum(int(s.get('failures', 0)) for s in suites); errors = sum(int(s.get('errors', 0)) for s in suites); skipped = sum(int(s.get('skipped', 0)) for s in suites); time = sum(float(s.get('time', 0)) for s in suites); passed = total - failures - errors - skipped; print(f'\n📈 TEST STATISTICS:\n   Total Tests:    {total}\n   ✅ Passed:      {passed}\n   ❌ Failed:      {failures}\n   ⚠️  Errors:      {errors}\n   ⏭️  Skipped:     {skipped}\n   ⏱️  Duration:    {time:.2f}s\n'); print('✅ ALL TESTS PASSED' if failures == 0 and errors == 0 else f'❌ {failures + errors} TEST(S) FAILED')" || true
          else
            echo "⚠️  Test results XML not found"
            echo "   Run 'pytest tests/ -v' locally to see detailed results."
          fi
          
          echo "═══════════════════════════════════════════════════════════════"
          echo ""
          echo "💡 TIPS:"
          echo "   • To run tests locally: pytest tests/ -v"
          echo "   • To run a specific test: pytest tests/test_api_auth.py::TestLogin::test_login_success -v"
          echo "   • To see coverage: pytest tests/ --cov=backend --cov-report=html"
          echo "   • Check the 'Run backend tests' step above for full pytest output"
          echo ""
          
          # Also write to step summary for Gitea/GitHub Actions compatibility
          if [ -n "$GITHUB_STEP_SUMMARY" ] && [ "$GITHUB_STEP_SUMMARY" != "/dev/stdout" ]; then
            {
              echo "## 📊 Backend Test Results Summary"
              echo ""
              
              if [ -f test-results.xml ]; then
                # Parse test results with a simple Python one-liner to avoid YAML issues
                python3 -c "import xml.etree.ElementTree as ET; t=ET.parse('test-results.xml'); s=t.findall('.//testsuite'); total=sum(int(x.get('tests',0)) for x in s); fails=sum(int(x.get('failures',0)) for x in s); errs=sum(int(x.get('errors',0)) for x in s); skips=sum(int(x.get('skipped',0)) for x in s); time=sum(float(x.get('time',0)) for x in s); passed=total-fails-errs-skips; emoji='✅' if fails==0 and errs==0 else '❌'; status='All tests passed' if fails==0 and errs==0 else f'{fails+errs} test(s) failed'; print(f'### {emoji} {status}\n\n| Metric | Count |\n|--------|-------|\n| Total Tests | {total} |\n| ✅ Passed | {passed} |\n| ❌ Failed | {fails} |\n| ⚠️ Errors | {errs} |\n| ⏭️ Skipped | {skips} |\n| ⏱️ Duration | {time:.2f}s |\n\n### 💡 Tips\n\n- To run tests locally: \`pytest tests/ -v\`\n- Check the Run backend tests step above for full pytest output')" || echo "⚠️ Could not parse test results"
              else
                echo "⚠️ Test results XML not found."
                echo ""
                echo "Check the 'Run backend tests' step above for detailed output."
              fi
            } >> "$GITHUB_STEP_SUMMARY" || true
          fi

  build:
    needs: skip-ci-check
    runs-on: ubuntu-latest
    if: needs.skip-ci-check.outputs.should-skip != '1'
    container:
      image: node:20-bullseye
    steps:
      - name: Check out code
        uses: actions/checkout@v4

      - name: Validate backend (imports and app instantiation)
        id: validate-backend
        continue-on-error: true
        run: |
          # Install Python 3.12 using pyenv (required for modern type hints like str | None)
          # Debian Bullseye doesn't have Python 3.12 in default repos, so we use pyenv
          # Retry logic for transient Debian mirror sync issues
          MAX_RETRIES=3
          RETRY_COUNT=0
          while [ $RETRY_COUNT -lt $MAX_RETRIES ]; do
            if apt-get update && apt-get install -y --fix-missing \
              make build-essential libssl-dev zlib1g-dev \
              libbz2-dev libreadline-dev libsqlite3-dev wget curl llvm \
              libncursesw5-dev xz-utils tk-dev libxml2-dev libxmlsec1-dev \
              libffi-dev liblzma-dev git; then
              echo "✅ Package installation succeeded"
              break
            else
              RETRY_COUNT=$((RETRY_COUNT + 1))
              if [ $RETRY_COUNT -lt $MAX_RETRIES ]; then
                echo "⚠️  Package installation failed (attempt $RETRY_COUNT/$MAX_RETRIES), retrying in 5 seconds..."
                sleep 5
                # Clear apt cache and retry
                apt-get clean
              else
                echo "❌ Package installation failed after $MAX_RETRIES attempts"
                exit 1
              fi
            fi
          done
          
          # Install pyenv
          export PYENV_ROOT="/opt/pyenv"
          export PATH="$PYENV_ROOT/bin:$PATH"
          curl https://pyenv.run | bash
          
          # Install Python 3.12 using pyenv
          eval "$(pyenv init -)"
          pyenv install -v 3.12.7
          pyenv global 3.12.7
          
          # Create virtual environment with Python 3.12
          python3.12 -m venv /tmp/backend-venv
          
          # Use venv's pip and python directly (avoids shell activation issues)
          # Install core dependencies including numpy and pillow (needed for module-level imports)
          # Skip heavy ML dependencies (tensorflow, deepface, opencv) for faster builds
          # Include email-validator for pydantic[email] email validation
          /tmp/backend-venv/bin/pip install --no-cache-dir fastapi uvicorn "pydantic[email]" sqlalchemy psycopg2-binary redis rq python-jose python-multipart python-dotenv bcrypt numpy pillow
          
          # Set environment variables for validation
          export PYTHONPATH=$(pwd)
          export SKIP_DEEPFACE_IN_TESTS=1
          # Use dummy database URLs - we're only validating imports, not connections
          export DATABASE_URL=postgresql+psycopg2://postgres:postgres@localhost:5432/punimtag_test
          export DATABASE_URL_AUTH=postgresql+psycopg2://postgres:postgres@localhost:5432/punimtag_auth_test
          export REDIS_URL=redis://localhost:6379/0
          
          # Validate imports and app instantiation (without starting server or connecting to DB)
          echo "🔍 Validating backend imports and structure..."
          /tmp/backend-venv/bin/python3 << 'EOF'
          import sys
          import os
          sys.path.insert(0, '.')
          
          # Test that core modules can be imported
          try:
              from backend.settings import APP_TITLE, APP_VERSION
              print(f'✅ App settings loaded: {APP_TITLE} v{APP_VERSION}')
          except ImportError as e:
              print(f'❌ Settings import error: {e}')
              sys.exit(1)
          
          # Test that all API routers can be imported (validates import structure)
          try:
              from backend.api import (
                  auth, faces, health, jobs, metrics, people,
                  pending_identifications, pending_linkages, photos,
                  reported_photos, pending_photos, tags, users,
                  auth_users, role_permissions, videos, version
              )
              print('✅ All API routers imported successfully')
          except ImportError as e:
              print(f'❌ API router import error: {e}')
              import traceback
              traceback.print_exc()
              sys.exit(1)
          
          # Test that app factory can be imported
          try:
              from backend.app import create_app
              print('✅ App factory imported successfully')
          except ImportError as e:
              print(f'❌ App factory import error: {e}')
              import traceback
              traceback.print_exc()
              sys.exit(1)
          
          # Note: We don't actually call create_app() here because it would trigger
          # database initialization in the lifespan, which requires a real database.
          # The import validation above is sufficient to catch most build-time errors.
          print('✅ Backend structure validated (imports and dependencies)')
          EOF
          echo "✅ Backend validation complete"

      - name: Install admin-frontend dependencies
        run: |
          cd admin-frontend
          npm ci

      - name: Audit admin-frontend dependencies
        run: |
          cd admin-frontend
          npm audit --audit-level=moderate || true
        continue-on-error: true

      - name: Build admin-frontend
        id: build-admin-frontend
        run: |
          cd admin-frontend
          npm run build
        continue-on-error: true
        env:
          VITE_API_URL: http://localhost:8000

      - name: Install viewer-frontend dependencies
        run: |
          cd viewer-frontend
          npm ci

      - name: Audit viewer-frontend dependencies
        run: |
          cd viewer-frontend
          npm audit --audit-level=moderate || true
        continue-on-error: true

      - name: Generate Prisma Clients
        run: |
          cd viewer-frontend
          npm run prisma:generate:all || true
        continue-on-error: true

      - name: Build viewer-frontend
        id: build-viewer-frontend
        run: |
          cd viewer-frontend
          npm run build
        continue-on-error: true

      - name: Check for build failures
        if: always()
        run: |
          FAILED=false
          if [ "x${{ steps.validate-backend.outcome }}" = "xfailure" ]; then
            echo "❌ Backend validation failed"
            FAILED=true
          fi
          if [ "x${{ steps.build-admin-frontend.outcome }}" = "xfailure" ]; then
            echo "❌ Admin frontend build failed"
            FAILED=true
          fi
          if [ "x${{ steps.build-viewer-frontend.outcome }}" = "xfailure" ]; then
            echo "❌ Viewer frontend build failed"
            FAILED=true
          fi
          if [ "$FAILED" = "true" ]; then
            echo "❌ One or more builds failed. Failing job."
            exit 1
          else
            echo "✅ All builds passed"
          fi
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost:5432/punimtag
          DATABASE_URL_AUTH: postgresql://postgres:postgres@localhost:5432/punimtag_auth
          NEXTAUTH_SECRET: test-secret-key-for-ci
          NEXTAUTH_URL: http://localhost:3001
          RESEND_API_KEY: re_dummy_key_for_ci_build_only
          RESEND_FROM_EMAIL: test@example.com

  secret-scanning:
    needs: skip-ci-check
    if: needs.skip-ci-check.outputs.should-skip != '1'
    runs-on: ubuntu-latest
    container:
      image: zricethezav/gitleaks:latest
    steps:
      - name: Install Node.js for checkout action
        run: |
          apk add --no-cache nodejs npm curl

      - name: Check out code
        uses: actions/checkout@v4
        with:
          fetch-depth: 0

      - name: Scan for secrets
        id: gitleaks-scan
        run: |
          gitleaks detect \
            --source . \
            --no-banner \
            --redact \
            --verbose \
            --report-path gitleaks-report.json \
            --exit-code 0 || true
        continue-on-error: true

      - name: Install jq for report parsing
        run: apk add --no-cache jq

      - name: Display secret scan results
        if: always()
        run: |
          if [ -f gitleaks-report.json ]; then
            echo "## 🔐 Secret Scan Results" >> $GITHUB_STEP_SUMMARY || true
            echo "" >> $GITHUB_STEP_SUMMARY || true
            
            # Count leaks
            LEAK_COUNT=$(jq 'length' gitleaks-report.json 2>/dev/null || echo "0")
            echo "**Total leaks found: $LEAK_COUNT**" >> $GITHUB_STEP_SUMMARY || true
            echo "" >> $GITHUB_STEP_SUMMARY || true
            
            if [ "$LEAK_COUNT" -gt 0 ]; then
              echo "### Leak Summary Table" >> $GITHUB_STEP_SUMMARY || true
              echo "" >> $GITHUB_STEP_SUMMARY || true
              echo "| File | Line | Rule | Entropy | Commit | Author | Date |" >> $GITHUB_STEP_SUMMARY || true
              echo "|------|------|------|---------|--------|--------|------|" >> $GITHUB_STEP_SUMMARY || true
              
              # Extract and display leak details in table format
              jq -r '.[] | "| \(.File) | \(.Line) | \(.RuleID) | \(.Entropy // "N/A") | `\(.Commit[0:8])` | \(.Author // "N/A") | \(.Date // "N/A") |"' gitleaks-report.json >> $GITHUB_STEP_SUMMARY || true
              echo "" >> $GITHUB_STEP_SUMMARY || true
              
              echo "### Detailed Findings" >> $GITHUB_STEP_SUMMARY || true
              echo "" >> $GITHUB_STEP_SUMMARY || true
              
              # Display each finding with full details
              jq -r '.[] | 
                "#### Finding: \(.RuleID) in \(.File):\(.Line)\n" +
                "- **File:** `\(.File)`\n" +
                "- **Line:** \(.Line)\n" +
                "- **Rule ID:** \(.RuleID)\n" +
                "- **Description:** \(.Description // "N/A")\n" +
                "- **Entropy:** \(.Entropy // "N/A")\n" +
                "- **Commit:** `\(.Commit)`\n" +
                "- **Author:** \(.Author // "N/A") (\(.Email // "N/A"))\n" +
                "- **Date:** \(.Date // "N/A")\n" +
                "- **Fingerprint:** `\(.Fingerprint // "N/A")`\n"' gitleaks-report.json >> $GITHUB_STEP_SUMMARY || true
              
              echo "" >> $GITHUB_STEP_SUMMARY || true
              echo "### Full Report (JSON)" >> $GITHUB_STEP_SUMMARY || true
              echo '```json' >> $GITHUB_STEP_SUMMARY || true
              cat gitleaks-report.json >> $GITHUB_STEP_SUMMARY || true
              echo '```' >> $GITHUB_STEP_SUMMARY || true
              
              echo "" >> $GITHUB_STEP_SUMMARY || true
              echo "⚠️ **Action Required:** Review and remove the secrets found above. Secrets should be removed from the codebase and rotated if they were ever exposed." >> $GITHUB_STEP_SUMMARY || true
            else
              echo "✅ No secrets detected!" >> $GITHUB_STEP_SUMMARY || true
            fi
          else
            echo "⚠️  No report file generated" >> $GITHUB_STEP_SUMMARY || true
          fi

      - name: Check for secret scan failures
        if: always()
        run: |
          GITLEAKS_OUTCOME="${{ steps.gitleaks-scan.outcome }}"
          if [ "x$GITLEAKS_OUTCOME" = "xfailure" ] || ([ -f gitleaks-report.json ] && [ "$(jq 'length' gitleaks-report.json 2>/dev/null || echo '0')" != "0" ]); then
            echo "❌ Secret scan found issues. Job marked as failed."
            exit 1
          else
            echo "✅ Secret scan completed successfully."
          fi

  dependency-scan:
    needs: skip-ci-check
    if: needs.skip-ci-check.outputs.should-skip != '1'
    runs-on: ubuntu-latest
    container:
      image: aquasec/trivy:latest
    steps:
      - name: Install Node.js for checkout action
        run: |
          apk add --no-cache nodejs npm curl

      - name: Check out code
        uses: actions/checkout@v4

      - name: Dependency vulnerability scan (Trivy)
        id: trivy-vuln-scan
        run: |
          trivy fs \
            --scanners vuln \
            --severity HIGH,CRITICAL \
            --ignore-unfixed \
            --timeout 10m \
            --skip-dirs .git,node_modules,venv \
            --exit-code 0 \
            --format json \
            --output trivy-vuln-report.json \
            . || true
        continue-on-error: true

      - name: Secret scan (Trivy)
        id: trivy-secret-scan
        run: |
          trivy fs \
            --scanners secret \
            --timeout 10m \
            --skip-dirs .git,node_modules,venv \
            --exit-code 0 \
            . || true
        continue-on-error: true

      - name: Check for scan failures
        if: always()
        run: |
          FAILED=false
          
          # Check for vulnerabilities
          if [ -f trivy-vuln-report.json ]; then
            VULN_COUNT=$(jq '[.Results[]?.Vulnerabilities[]?] | length' trivy-vuln-report.json 2>/dev/null || echo "0")
            if [ "$VULN_COUNT" != "0" ] && [ "$VULN_COUNT" != "null" ]; then
              echo "❌ Trivy found $VULN_COUNT HIGH/CRITICAL vulnerabilities. Job marked as failed."
              FAILED=true
            fi
          fi
          
          # Check for secrets
          TRIVY_OUTCOME="${{ steps.trivy-secret-scan.outcome }}"
          if [ "x$TRIVY_OUTCOME" = "xfailure" ]; then
            echo "❌ Trivy secret scan found issues. Job marked as failed."
            FAILED=true
          fi
          
          if [ "$FAILED" = "true" ]; then
            exit 1
          else
            echo "✅ Dependency scan completed successfully."
          fi

  sast-scan:
    needs: skip-ci-check
    if: needs.skip-ci-check.outputs.should-skip != '1'
    runs-on: ubuntu-latest
    container:
      image: ubuntu:22.04
    steps:
      - name: Install Node.js for checkout action
        run: |
          apt-get update && apt-get install -y curl
          curl -fsSL https://deb.nodesource.com/setup_20.x | bash -
          apt-get install -y nodejs

      - name: Check out code
        uses: actions/checkout@v4

      - name: Install Semgrep
        run: |
          apt-get update && apt-get install -y python3 python3-pip
          pip3 install semgrep

      - name: Run Semgrep scan
        id: semgrep-scan
        run: |
          # Run Semgrep but don't fail on findings (they're reported but not blocking)
          # Most findings are false positives (console.log format strings, safe SQL in setup scripts)
          # Exclude false positive rules: console.log format strings (JS doesn't use format strings)
          # and JWT tokens in test files (expected dummy tokens)
          semgrep --config=auto \
            --exclude-rule=javascript.lang.security.audit.unsafe-formatstring.unsafe-formatstring \
            --exclude-rule=generic.secrets.security.detected-jwt-token.detected-jwt-token \
            || true
        continue-on-error: true

      - name: Check for scan failures
        if: always()
        run: |
          SCAN_OUTCOME="${{ steps.semgrep-scan.outcome }}"
          if [ "x$SCAN_OUTCOME" = "xfailure" ]; then
            echo "❌ Semgrep scan found security issues. Job marked as failed."
            exit 1
          else
            echo "✅ Semgrep scan completed successfully."
          fi

  workflow-summary:
    runs-on: ubuntu-latest
    needs: [lint-and-type-check, python-lint, test-backend, build, secret-scanning, dependency-scan, sast-scan]
    if: always()
    steps:
      - name: Generate workflow summary
        run: |
          echo "═══════════════════════════════════════════════════════════════"
          echo "🔍 CI WORKFLOW SUMMARY"
          echo "═══════════════════════════════════════════════════════════════"
          echo ""
          echo "This gives a plain-English overview of what ran in this pipeline and whether it passed."
          echo ""
          echo "JOB RESULTS:"
          echo "────────────"
          echo ""
          echo "📝 Lint & Type Check:     ${{ needs.lint-and-type-check.result }}"
          echo "   └─ Runs ESLint on the admin UI and TypeScript type-checks the viewer UI"
          echo ""
          echo "🐍 Python Lint:           ${{ needs.python-lint.result }}"
          echo "   └─ Runs Python style and syntax checks over the backend"
          echo ""
          echo "🧪 Backend Tests:         ${{ needs.test-backend.result }}"
          echo "   └─ Runs 'pytest tests/ -v' against the FastAPI backend (with coverage)"
          echo ""
          echo "🏗️  Build:                 ${{ needs.build.result }}"
          echo "   └─ Validates backend imports/structure, builds admin frontend (Vite), and viewer frontend (Next.js)"
          echo ""
          echo "🔐 Secret Scanning:       ${{ needs.secret-scanning.result }}"
          echo "   └─ Uses Gitleaks to look for committed secrets"
          echo ""
          echo "📦 Dependency Scan:       ${{ needs.dependency-scan.result }}"
          echo "   └─ Uses Trivy to scan dependencies for HIGH/CRITICAL vulns"
          echo ""
          echo "🔍 SAST Scan:             ${{ needs.sast-scan.result }}"
          echo "   └─ Uses Semgrep to look for insecure code patterns"
          echo ""
          echo "═══════════════════════════════════════════════════════════════"
          echo "STATUS LEGEND:"
          echo "──────────────"
          echo "  success   = Job finished and all checks/tests passed"
          echo "  failure   = Job ran but one or more checks/tests failed (see that job's log)"
          echo "  cancelled = Job was stopped before finishing"
          echo "  skipped   = Job did not run, usually because CI was skipped for this commit"
          echo ""
          echo "═══════════════════════════════════════════════════════════════"
          echo "📊 HOW TO READ THE BACKEND TEST RESULTS:"
          echo "────────────────────────────────────────"
          echo ""
          echo "• The 'Backend Tests' row above tells you if the test run as a whole passed or failed."
          echo ""
          echo "• To see which specific tests failed or how they ran:"
          echo "  1. Open the 'test-backend' job in this workflow run"
          echo "  2. Look at the 'Run backend tests' step to see the 'pytest -v' output"
          echo "  3. For local debugging, run 'pytest tests/ -v' in your dev environment"
          echo ""
          echo "═══════════════════════════════════════════════════════════════"
          
          # Also write to step summary if available (for GitHub Actions compatibility)
          if [ -n "$GITHUB_STEP_SUMMARY" ] && [ "$GITHUB_STEP_SUMMARY" != "/dev/stdout" ]; then
            {
              echo "## 🔍 CI Workflow Summary"
              echo ""
              echo "This table gives a **plain-English overview** of what ran in this pipeline and whether it passed."
              echo ""
              echo "### Job Results"
              echo ""
              echo "| Job | What it does | Status |"
              echo "|-----|--------------|--------|"
              echo "| 📝 Lint & Type Check | Runs ESLint on the admin UI and TypeScript type-checks the viewer UI | ${{ needs.lint-and-type-check.result }} |"
              echo "| 🐍 Python Lint | Runs Python style and syntax checks over the backend | ${{ needs.python-lint.result }} |"
              echo "| 🧪 Backend Tests | Runs \`pytest tests/ -v\` against the FastAPI backend (with coverage) | ${{ needs.test-backend.result }} |"
              echo "| 🏗️ Build | Validates backend imports/structure, builds admin frontend (Vite), and viewer frontend (Next.js) | ${{ needs.build.result }} |"
              echo "| 🔐 Secret Scanning | Uses Gitleaks to look for committed secrets | ${{ needs.secret-scanning.result }} |"
              echo "| 📦 Dependency Scan | Uses Trivy to scan dependencies for HIGH/CRITICAL vulns | ${{ needs.dependency-scan.result }} |"
              echo "| 🔍 SAST Scan | Uses Semgrep to look for insecure code patterns | ${{ needs.sast-scan.result }} |"
              echo ""
              echo "**Legend for the Status column:**"
              echo "- \`success\`: job finished and all checks/tests passed."
              echo "- \`failure\`: job ran but one or more checks/tests failed (see that job's log)."
              echo "- \`cancelled\`: job was stopped before finishing."
              echo "- \`skipped\`: job did not run, usually because CI was skipped for this commit."
              echo ""
              echo "### 📊 How to read the backend test results"
              echo ""
              echo "- The **Backend Tests** row tells you if the test run as a whole passed or failed."
              echo "- To see which specific tests failed or how they ran:"
              echo "  1. Open the **test-backend** job in this workflow run."
              echo "  2. Look at the **Run backend tests** step to see the \`pytest -v\` output."
              echo "  3. For local debugging, run \`pytest tests/ -v\` in your dev environment."
            } >> "$GITHUB_STEP_SUMMARY" || true
          fi

      - name: Check for job failures
        if: always()
        run: |
          FAILED=false
          if [ "x${{ needs.lint-and-type-check.result }}" = "xfailure" ]; then
            echo "❌ Lint & Type Check job failed"
            FAILED=true
          fi
          if [ "x${{ needs.python-lint.result }}" = "xfailure" ]; then
            echo "❌ Python Lint job failed"
            FAILED=true
          fi
          if [ "x${{ needs.test-backend.result }}" = "xfailure" ]; then
            echo "❌ Backend Tests job failed"
            FAILED=true
          fi
          if [ "x${{ needs.build.result }}" = "xfailure" ]; then
            echo "❌ Build job failed"
            FAILED=true
          fi
          if [ "x${{ needs.secret-scanning.result }}" = "xfailure" ]; then
            echo "❌ Secret Scanning job failed"
            FAILED=true
          fi
          if [ "x${{ needs.dependency-scan.result }}" = "xfailure" ]; then
            echo "❌ Dependency Scan job failed"
            FAILED=true
          fi
          if [ "x${{ needs.sast-scan.result }}" = "xfailure" ]; then
            echo "❌ SAST Scan job failed"
            FAILED=true
          fi
          if [ "$FAILED" = "true" ]; then
            echo "═══════════════════════════════════════════════════════════════"
            echo "❌ WORKFLOW FAILED - One or more jobs failed"
            echo "═══════════════════════════════════════════════════════════════"
            echo ""
            echo "Check the job results above to see which jobs failed."
            exit 1
          else
            echo "═══════════════════════════════════════════════════════════════"
            echo "✅ WORKFLOW SUCCESS - All jobs passed"
            echo "═══════════════════════════════════════════════════════════════"
          fi

