# Twice-daily archive pull, ported from the GitHub workflow. 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.
# The archive lives as metro.db.gz on the gitbay release tagged "archive";
# the site deploys to the pages branch.
#
# The build runs in a container on the instance's runner, which holds no
# key of its own inside the container. Publishing goes over SSH as the
# blotter-ci account (write on this repository): its private key arrives
# as the BOT_SSH_KEY build secret and is written into the workspace for
# the build, beside an ssh config every ssh and git call is pointed at
# with -F. GITBAY_SSH, set by the runner, is the instance as this build
# reaches it. Nothing is written outside the workspace, so the job runs
# the same in a container and on a machine that is somebody's own.
jobs:
  daily-pull:
    schedule: "17 11,23 * * *"
    steps:
      - python3 -m venv .venv && .venv/bin/pip install -q -r requirements-ingest.txt
      - |
        set -e
        test -n "$BOT_SSH_KEY" || { echo "ERROR: BOT_SSH_KEY secret is not set"; exit 1; }
        test -n "$GITBAY_SSH" || { echo "ERROR: GITBAY_SSH is not set; the runner is too old"; exit 1; }
        umask 077
        printf '%s\n' "$BOT_SSH_KEY" > "$PWD/.bot_key"
        printf 'IdentityFile %s\nIdentitiesOnly yes\nStrictHostKeyChecking accept-new\nUserKnownHostsFile %s\n' "$PWD/.bot_key" "$PWD/.known_hosts" > "$PWD/.ssh_config"
        ssh -F "$PWD/.ssh_config" "$GITBAY_SSH" whoami
      - sh ci/pull-publish.sh
      - |
        set -e
        export PATH="$PWD/.venv/bin:$PATH" DB=raw_data/metro.db GIT_SSH_COMMAND="ssh -F $PWD/.ssh_config"
        .venv/bin/pip install -q -r requirements.txt
        python build_site.py
        origin=ssh://$GITBAY_SSH/krz/omaha-metro-blotter.git
        cd site && git init -q -b pages
        git -c user.name=ci -c user.email=ci@gitbay.org add -A
        git -c user.name=ci -c user.email=ci@gitbay.org commit -q -m "site $(date -u '+%Y-%m-%d %H:%M')"
        git push -qf "$origin" pages:pages
        echo "site deployed"
      - |
        set -e
        export DB=raw_data/metro.db
        # Alarms last, on purpose: a stale feed should fail the build loudly
        # without having blocked the archive or the site.
        sqlite3 -noheader "$DB" \
          "SELECT source || ' ' || MAX(occurred_at) FROM incidents
           WHERE source IN ('opd', 'sarpy', 'cbpd') 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"
        age=$(sqlite3 -noheader "$DB" "
          SELECT CAST(julianday('now') - julianday(MAX(imported_at)) AS INT)
            FROM alpr_searches" 2>/dev/null || echo "")
        if [ -z "$age" ]; then echo "no Flock export on file yet"; exit 0; fi
        echo "newest Flock export imported $age days ago"
        if [ "$age" -ge 27 ]; then
          echo "ERROR: Flock search audit is $age days old and the portal only keeps 30."
          echo "Download it from https://transparency.flocksafety.com/council-bluffs-ia-pd"
          echo "and commit it to raw_data/flock/ before the window closes."
          exit 1
        elif [ "$age" -ge 21 ]; then
          echo "WARNING: Flock search audit is $age days old; refresh it soon."
        fi