#!/usr/bin/env bash
# run.sh — audit-trigger benchmark: jsonb vs hstore, full row vs diff,
# plus hstore-computed diff stored as jsonb (e: hstore_to_jsonb, f: hstore_to_jsonb_loose).
#
#   docker run -d --name audit-bench -e POSTGRES_PASSWORD=x postgres:17
#   (any PostgreSQL 17 container named audit-bench works; set PSQL below otherwise)
#   ./run.sh            # loads setup.sql, runs everything, writes raw/ and results.csv
#
# Per workload, per run (1..3), per variant (none,a,b,c,d,e,f), in a fresh psql session:
#   attach trigger -> TRUNCATE both audit tables -> VACUUM (whole db) -> BEGIN -> timed statement (\timing)
#   -> audit table size + rows + avg bytes per change (before rollback) -> ROLLBACK.
# Runs are interleaved (run 1 of every variant, then run 2, ...) so drift hits all
# variants alike. Median of 3 is computed at the end.
# TRUNCATE is there because a rolled-back insert leaves dead btree pages that
# VACUUM does not return: without it audit_log stayed at 14 MB with 0 rows
# and every size measurement after the first run included that.
set -euo pipefail
cd "$(dirname "$0")"
PSQL=(docker exec -i audit-bench psql -U postgres -X -q -v ON_ERROR_STOP=1)
mkdir -p raw

"${PSQL[@]}" -Atc "select version()" | tee raw/version.txt
"${PSQL[@]}" < setup.sql > raw/setup.out 2>&1

declare -A WL
WL[i_bulk_credit]="update customers set credit_limit = credit_limit + 1 where id <= 100000;"
WL[ii_single_20k]="do \$\$ begin for i in 1..20000 loop update customers set credit_limit = credit_limit + 1 where id = i; end loop; end \$\$;"
WL[iii_bulk_updated_at]="update customers set updated_at = now() where id <= 100000;"

table_for() { case "$1" in a|b|e|f|none) echo audit_log;; c|d) echo audit_log_h;; esac; }

: > results.csv
echo "workload,variant,run,ms,log_bytes,log_rows,bytes_per_change" >> results.csv
for w in i_bulk_credit ii_single_20k iii_bulk_updated_at; do
  for run in 1 2 3; do
    for v in none a b c d e f; do
      t=$(table_for "$v")
      out="raw/${w}_${v}_run${run}.txt"
      "${PSQL[@]}" > "$out" 2>&1 <<SQL
set app.user_id = 'bench';
select set_audit_variant('$v');
truncate audit_log, audit_log_h;
vacuum;
begin;
\\timing on
${WL[$w]}
\\timing off
\\pset tuples_only on
\\pset format unaligned
select 'RESULT|' || pg_total_relation_size('$t') || '|' || count(*) || '|'
       || coalesce(round(avg(coalesce(pg_column_size(old_row),0) + coalesce(pg_column_size(new_row),0)), 1)::text, '-')
  from $t;
rollback;
SQL
      ms=$(grep -m1 '^Time:' "$out" | awk '{print $2}')
      IFS='|' read -r _ sz rows bpc < <(grep '^RESULT|' "$out")
      echo "$w,$v,$run,$ms,$sz,$rows,$bpc" | tee -a results.csv
    done
  done
done
"${PSQL[@]}" -c "select set_audit_variant('none');" > /dev/null

python3 -I - results.csv <<'PY' | tee summary.txt
import csv, sys, statistics as st
rows = list(csv.DictReader(open(sys.argv[1])))
names = {'none':'no trigger','a':'(a) jsonb full row','b':'(b) jsonb diff',
         'c':'(c) hstore full row','d':'(d) hstore diff',
         'e':'(e) hstore diff -> jsonb','f':'(f) hstore diff -> jsonb (loose)'}
for w in dict.fromkeys(r['workload'] for r in rows):
    print(f"\n## {w}\n")
    print("| variant | median ms | runs (ms) | audit log (bytes, median) | audit rows | bytes per change |")
    print("|---|---|---|---|---|---|")
    for v in ['none','a','b','c','d','e','f']:
        rs = [r for r in rows if r['workload']==w and r['variant']==v]
        ms = [float(r['ms']) for r in rs]
        sz = [int(r['log_bytes']) for r in rs]
        print(f"| {names[v]} | {st.median(ms):.1f} | {' / '.join(f'{x:.1f}' for x in ms)} | "
              f"{int(st.median(sz)):,} | {rs[0]['log_rows']} | {rs[0]['bytes_per_change']} |")
PY
