name: Security Tests

on:
  workflow_call:  # Called by release-gate.yml
  workflow_dispatch:

permissions:
  contents: read
  security-events: write  # Required for uploading SARIF results to Code Scanning

jobs:
  security-tests:
    runs-on: ubuntu-latest
    timeout-minutes: 25

    services:
      postgres:
        image: postgres:13@sha256:1094e2cdc5605e5c7914633bcd93758a9c52ae8c8b2855ddd1c3a8afbe4795d5 # postgres:13
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test_security_db
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
    - name: Harden the runner (Audit all outbound calls)
      uses: step-security/harden-runner@9af89fc71515a100421586dfdb3dc9c984fbf411 # v2.19.4
      with:
        egress-policy: audit

    - uses: actions/checkout@9c091bb21b7c1c1d1991bb908d89e4e9dddfe3e0 # v7.0.0
      with:
        persist-credentials: false

    - name: Set up Python 3.12
      uses: actions/setup-python@ece7cb06caefa5fff74198d8649806c4678c61a1 # v6.3.0
      with:
        python-version: '3.12'

    - name: Cache pip packages
      uses: actions/cache@55cc8345863c7cc4c66a329aec7e433d2d1c52a9 # v6.1.0
      with:
        path: ~/.cache/pip
        key: ${{ runner.os }}-pip-security-${{ hashFiles('**/requirements.txt') }}
        restore-keys: |
          ${{ runner.os }}-pip-security-
          ${{ runner.os }}-pip-

    - name: Set up PDM
      uses: pdm-project/setup-pdm@973541a5febeafcfdadf8a51211435be6ecfd90f # v4.5
      with:
        python-version: '3.12'

    - name: Install system dependencies for SQLCipher
      run: |
        sudo apt-get update
        sudo apt-get install -y libsqlcipher-dev

    - name: Install dependencies
      run: |
        pdm sync -d
        pdm add "bandit[sarif]" safety sqlparse pytest pytest-cov --no-sync
        pdm sync

    - name: Run Bandit security linter
      run: |
        # --exit-zero: report findings without failing (findings go to SARIF/Security tab)
        # If bandit crashes (bad config, missing files), it exits non-zero WITHOUT --exit-zero
        # and won't produce output files — the checks below catch that.
        pdm run bandit -r src/ -f json -o bandit-report.json -lll --exit-zero
        pdm run bandit -r src/ -f sarif -o bandit-results.sarif -lll --exit-zero
        # Crash detection: if bandit crashed, no output files were produced
        if [ ! -f bandit-report.json ] || [ ! -f bandit-results.sarif ]; then
          echo "::error::Bandit crashed — expected output files not produced"
          exit 1
        fi
        echo "Bandit security scan completed"

    - name: Upload Bandit SARIF to Code Scanning
      uses: github/codeql-action/upload-sarif@54f647b7e1bb85c95cddabcd46b0c578ec92bc1a # v4.36.3
      if: always()
      with:
        sarif_file: bandit-results.sarif
        category: bandit

    # Dependency vulnerability scanning is handled by OSV-Scanner
    # (osv-scanner.yml), which reads pdm.lock directly and doesn't
    # fight PDM's resolution overrides the way pip-audit did.

    - name: Run security test suite
      env:
        DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test_security_db
      run: |
        # Run the entire tests/security/ tree as a directory rather than
        # enumerating individual files. The old per-file list only covered
        # ~9 of the 87 test files and silently broke whenever a file was
        # renamed or deleted: a hardcoded reference to the deleted
        # test_input_validation.py (removed in #4243) made pytest exit with
        # code 5 ("no tests collected") and failed this gate (#4411).
        # Running the directory picks up new security tests automatically and
        # never rots when files move.
        pdm run python -m pytest tests/security/ -v --tb=short -n auto

    - name: Check for hardcoded secrets
      run: |
        # Check for potential secrets in code
        grep -r -E "(api[_-]?key|secret[_-]?key|password|token)" src/ --include="*.py" | \
          grep -v -E "(os\.environ|getenv|config\[|placeholder|example|test)" | \
          grep -E "=\s*['\"]" || echo "No hardcoded secrets found"

    - name: Generate security report
      if: always()
      run: |
        echo "Security Test Report"
        echo "==================="
        echo ""
        if [ -f bandit-report.json ]; then
          echo "Bandit Security Issues:"
          python -c "import json; data=json.load(open('bandit-report.json')); print(f'  High: {len([i for i in data.get(\"results\", []) if i[\"issue_severity\"] == \"HIGH\"])}'); print(f'  Medium: {len([i for i in data.get(\"results\", []) if i[\"issue_severity\"] == \"MEDIUM\"])}'); print(f'  Low: {len([i for i in data.get(\"results\", []) if i[\"issue_severity\"] == \"LOW\"])}')" || true
        fi
        echo ""
        echo "Dependency vulnerabilities: covered by OSV-Scanner (osv-scanner.yml)"

    - name: Upload security reports
      if: always()
      uses: actions/upload-artifact@043fb46d1a93c77aae656e7c1c64a875d1fc6a0a # v7.0.1
      with:
        name: security-reports
        path: |
          bandit-report.json
          bandit-results.sarif