# Sarpy and Council Bluffs serve a rolling 12-month window; records that age out
# of those feeds exist nowhere else. This job is the only thing keeping them, so
# it refuses to publish an archive smaller than the one it started with.
name: daily pull

on:
  schedule:
    # 06:00 America/Chicago in summer, 05:00 in winter. Offset from the hour
    # because GitHub drops on-the-hour scheduled runs under load.
    - cron: "17 11 * * *"
  workflow_dispatch:
    inputs:
      bootstrap:
        description: "Start a new archive instead of restoring the published one"
        type: boolean
        default: false
      full:
        description: "Pull each feed in full rather than the last 30 days"
        type: boolean
        default: false

permissions:
  contents: write

concurrency:
  group: archive
  cancel-in-progress: false

env:
  TAG: archive
  DB: raw_data/metro.db
  GH_TOKEN: ${{ github.token }}

jobs:
  pull:
    runs-on: ubuntu-latest
    timeout-minutes: 30

    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-python@v5
        with:
          python-version: "3.13"
          cache: pip
          cache-dependency-path: requirements-ingest.txt

      - run: pip install -r requirements-ingest.txt

      - name: Restore archive
        run: |
          if gh release download "$TAG" --pattern metro.db.gz --dir .; then
            gunzip -c metro.db.gz > "$DB"
            rm metro.db.gz
            echo "restored $(du -h "$DB" | cut -f1)"
          elif [ "${{ inputs.bootstrap }}" = "true" ]; then
            echo "no published archive; starting a new one"
          else
            echo "::error::No metro.db.gz on release '$TAG'. Anything that has" \
                 "already aged out of the Sarpy and Council Bluffs feeds cannot" \
                 "be recovered. Re-run with bootstrap only if that is intended."
            exit 1
          fi

      - name: Count rows before
        run: |
          : > before.txt
          if [ -f "$DB" ]; then
            sqlite3 -noheader -separator ' ' "$DB" \
              "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source" \
              > before.txt
          fi
          cat before.txt

      - name: Pull feeds
        run: |
          if [ "${{ inputs.full }}" = "true" ] || [ "${{ inputs.bootstrap }}" = "true" ]; then
            python ingest.py --full
          else
            python ingest.py
          fi

      - name: Check nothing was lost
        run: |
          sqlite3 -noheader -separator ' ' "$DB" \
            "SELECT source, COUNT(*) FROM incidents GROUP BY source ORDER BY source" \
            > after.txt
          cat after.txt
          test -s after.txt || { echo "::error::archive is empty"; exit 1; }
          # Keyed on FILENAME, not NR == FNR: before.txt is empty on a bootstrap
          # run, and awk never resets FNR for a zero-length file.
          awk -v first=before.txt '
               FILENAME == first { was[$1] = $2; next }
               { now[$1] = $2 }
               END {
                 for (s in was)
                   if (now[s] + 0 < was[s] + 0) {
                     printf "::error::%s lost rows: %d -> %d\n", s, was[s], now[s]
                     bad = 1
                   }
                 exit bad
               }' before.txt after.txt

      - name: Publish archive
        run: |
          sqlite3 "$DB" "VACUUM;"
          gzip -c "$DB" > metro.db.gz
          gh release view "$TAG" >/dev/null 2>&1 \
            || gh release create "$TAG" --title "Incident archive" --notes "building"
          gh release upload "$TAG" metro.db.gz --clobber
          {
            echo "SQLite archive of Omaha metro police incident feeds, rebuilt daily."
            echo "Sarpy County and Council Bluffs publish a rolling 12-month window,"
            echo "so this holds records their own feeds no longer serve."
            echo
            echo "Updated $(date -u '+%Y-%m-%d %H:%M UTC'). Schema: schema.sql."
            echo
            echo '```'
            sqlite3 -header -column "$DB" \
              "SELECT agency, COUNT(*) AS rows, SUM(is_stop) AS stops,
                      MIN(occurred_at) AS earliest, MAX(occurred_at) AS latest
               FROM incidents GROUP BY agency ORDER BY rows DESC"
            echo '```'
          } > notes.md
          gh release edit "$TAG" --notes-file notes.md

      # Runs after the upload on purpose: a feed that stopped updating should
      # raise the alarm without also blocking the archive from being published.
      - name: Check the feeds are still moving
        run: |
          sqlite3 -noheader "$DB" \
            "SELECT source || ' ' || MAX(occurred_at) FROM incidents
             WHERE source <> 'opd_csv' GROUP BY source
             HAVING MAX(occurred_at) < datetime('now', '-7 days')" > stale.txt
          if [ -s stale.txt ]; then
            while read -r line; do echo "::error::feed is stale: $line"; done < stale.txt
            exit 1
          fi
          echo "all feeds current"

      - name: Summary
        if: always()
        run: |
          {
            echo "| source | before | after |"
            echo "|---|---|---|"
            awk -v first=before.txt '
                 FILENAME == first { was[$1] = $2; next }
                 { printf "| %s | %s | %s |\n", $1, ($1 in was ? was[$1] : 0), $2 }' \
                before.txt after.txt
          } >> "$GITHUB_STEP_SUMMARY"