llm-intelligence/scripts/report_state_tracking_test.sh

#!/usr/bin/env bash

set -euo pipefail

ROOT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
cd "$ROOT_DIR"

while IFS= read -r kv; do export "$kv"; done < <("$ROOT_DIR/scripts/load_project_env.sh" ".env.local")
while IFS= read -r kv; do key="${kv%%=*}"; [[ -n "$key" && -n "${!key:-}" ]] && continue; export "$kv"; done < <("$ROOT_DIR/scripts/load_project_env.sh" ".env")
source scripts/report_utils.sh
source scripts/report_utils.sh

if [[ -z "${DATABASE_URL:-}" ]]; then
    echo "DATABASE_URL is required"
    exit 1
fi

TEST_DATE="2099-01-01"
cleanup() {
    psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "DELETE FROM report_runs WHERE report_date = DATE '$TEST_DATE'; DELETE FROM daily_report WHERE report_date = DATE '$TEST_DATE';" >/dev/null
}
trap cleanup EXIT
cleanup

track_report_state "$DATABASE_URL" "$TEST_DATE" generated 123 'official summary' 'reports/daily/daily_report_2099-01-01.md' '' scheduled cron true >/dev/null


OFFICIAL_ROW="$(psql "$DATABASE_URL" -Atqc "SELECT status || '|' || run_kind || '|' || trigger_source || '|' || is_official_daily::text FROM daily_report WHERE report_date = DATE '$TEST_DATE';")"
[[ "$OFFICIAL_ROW" == "generated|scheduled|cron|true" ]]

OFFICIAL_RUN_COUNT="$(psql "$DATABASE_URL" -Atqc "SELECT count(*) FROM report_runs WHERE report_date = DATE '$TEST_DATE';")"
[[ "$OFFICIAL_RUN_COUNT" == "1" ]]

track_report_state "$DATABASE_URL" "$TEST_DATE" failed '' '' "$(report_ad_hoc_markdown_path "$TEST_DATE" manual pipeline)" 'manual failed' manual pipeline false >/dev/null

MANUAL_ROW="$(psql "$DATABASE_URL" -Atqc "SELECT status || '|' || run_kind || '|' || trigger_source || '|' || is_official_daily::text FROM daily_report WHERE report_date = DATE '$TEST_DATE';")"
[[ "$MANUAL_ROW" == "generated|scheduled|cron|true" ]]

RUN_ROWS="$(psql "$DATABASE_URL" -Atqc "SELECT string_agg(status || '|' || run_kind || '|' || trigger_source || '|' || is_official_daily::text, E'\n' ORDER BY id) FROM report_runs WHERE report_date = DATE '$TEST_DATE';")"
EXPECTED_ROWS=$'generated|scheduled|cron|true\nfailed|manual|pipeline|false'
[[ "$RUN_ROWS" == "$EXPECTED_ROWS" ]]

echo "report_state_tracking_test: PASS"