#!/usr/bin/env bash

set -eu -o pipefail

# Configuration
TEST_DURATION=${TEST_DURATION:-180}
PRELOAD=100000
CLIENTS=10

WORKDIR="${WORKDIR:-$(pwd)}"
RESULTS_DIR="$WORKDIR/cf-5556-results-$(date +%Y%m%d_%H%M%S)"
mkdir -p "$RESULTS_DIR"

cd "$WORKDIR"
BASELINE_COMMIT=$(git rev-parse --short=11 origin/master)

PATCHES=($(ls -1 v??-*.patch 2>/dev/null | sort))
if [ ${#PATCHES[@]} -eq 0 ]; then
    echo "Error: No v??-*.patch files found in $WORKDIR"
    exit 1
fi

PATCHED_BRANCH="cf-5556-test-patched"
git branch -D "$PATCHED_BRANCH" 2>/dev/null || true
git checkout -q origin/master
git checkout -q -b "$PATCHED_BRANCH"

for patch in "${PATCHES[@]}"; do
    git am "$patch" > /dev/null 2>&1 || {
	echo "Error: Failed to apply $patch"
	git am --abort 2>/dev/null || true
	exit 1
    }
done

PATCHED_COMMIT=$(git rev-parse --short=11 HEAD)

echo "Test duration: ${TEST_DURATION} seconds per test"
echo "Baseline: $BASELINE_COMMIT (origin/master)"
echo "Patched:  $PATCHED_COMMIT (origin/master + ${#PATCHES[@]} patches)"
echo "Results:  $RESULTS_DIR"
echo ""

# Test definitions: name, init_sql, bench_sql
declare -a TESTS=(
    "JSONB BTREE expression index"
    "CREATE TABLE t (id int PRIMARY KEY, data jsonb);
     CREATE INDEX idx ON t((data->>'category'));
     INSERT INTO t SELECT i, jsonb_build_object('category', 'A', 'value', 0) FROM generate_series(1, $PRELOAD) i;"
    "UPDATE t SET data = jsonb_set(data, '{value}', to_jsonb((random() * 1000000)::int))
     WHERE id = floor(random() * ($PRELOAD / $CLIENTS))::int * $CLIENTS + :client_id;"

    "JSONB GIN expression index"
    "CREATE TABLE t (id int PRIMARY KEY, data jsonb);
     CREATE INDEX idx ON t USING gin((data->'tags'));
     INSERT INTO t SELECT i, jsonb_build_object('tags', jsonb_build_array('postgres', 'sql'), 'counter', 0) FROM generate_series(1, $PRELOAD) i;"
    "UPDATE t SET data = jsonb_set(data, '{counter}', to_jsonb((random() * 1000000)::int))
     WHERE id = floor(random() * ($PRELOAD / $CLIENTS))::int * $CLIENTS + :client_id;"

    "Partial index"
    "CREATE TABLE t (id int PRIMARY KEY, status text, value int);
     CREATE INDEX idx ON t(value) WHERE status = 'active';
     INSERT INTO t SELECT i, 'inactive', i FROM generate_series(1, $PRELOAD) i;"
    "UPDATE t SET value = value + 1 WHERE status = 'inactive'
     AND id = floor(random() * ($PRELOAD / $CLIENTS))::int * $CLIENTS + :client_id;"

    "3 expression indexes"
    "CREATE TABLE t (id int PRIMARY KEY, data jsonb);
     CREATE INDEX idx1 ON t((data->>'field1'));
     CREATE INDEX idx2 ON t((data->>'field2'));
     CREATE INDEX idx3 ON t((data->>'field3'));
     INSERT INTO t SELECT i, jsonb_build_object('field1', 'A', 'field2', 'B', 'field3', 'C', 'counter', 0) FROM generate_series(1, $PRELOAD) i;"
    "UPDATE t SET data = jsonb_set(data, '{counter}', to_jsonb((random() * 1000000)::int))
     WHERE id = floor(random() * ($PRELOAD / $CLIENTS))::int * $CLIENTS + :client_id;"

    "6 expression indexes"
    "CREATE TABLE t (id int PRIMARY KEY, data jsonb) WITH (fillfactor = 70);
     CREATE INDEX idx1 ON t((data->>'f1'));
     CREATE INDEX idx2 ON t((data->>'f2'));
     CREATE INDEX idx3 ON t((data->>'f3'));
     CREATE INDEX idx4 ON t((data->>'f4'));
     CREATE INDEX idx5 ON t((data->>'f5'));
     CREATE INDEX idx6 ON t((data->>'f6'));
     INSERT INTO t SELECT i, jsonb_build_object('f1','A','f2','B','f3','C','f4','D','f5','E','f6','F','counter',0) FROM generate_series(1, $PRELOAD) i;"
    "UPDATE t SET data = jsonb_set(data, '{counter}', to_jsonb((random() * 1000000)::int))
     WHERE id = floor(random() * ($PRELOAD / $CLIENTS))::int * $CLIENTS + :client_id;"

    "Array expression index"
    "CREATE TABLE t (id int PRIMARY KEY, arr int[]);
     CREATE INDEX idx ON t((arr[5]));
     INSERT INTO t SELECT i, ARRAY[i, i+1, i+2, i+3, i+4, i+5, i+6] FROM generate_series(1, $PRELOAD) i;"
    "UPDATE t SET arr[3] = (random() * 1000000)::int
     WHERE id = floor(random() * ($PRELOAD / $CLIENTS))::int * $CLIENTS + :client_id;"

    "UPDATE 100 rows"
    "CREATE TABLE t (id int PRIMARY KEY, data jsonb);
     CREATE INDEX idx ON t((data->>'category'));
     INSERT INTO t SELECT i, jsonb_build_object('category', 'A', 'value', 0) FROM generate_series(1, $PRELOAD) i;"
    "\set start_id random(1, $PRELOAD - 100)
UPDATE t SET data = jsonb_set(data, '{value}', to_jsonb((random() * 1000000)::int)) WHERE id BETWEEN :start_id AND :start_id + 99;"

    "Control: indexed field changes"
    "CREATE TABLE t (id int PRIMARY KEY, data jsonb);
     CREATE INDEX idx ON t((data->>'status'));
     INSERT INTO t SELECT i, jsonb_build_object('status', 'active', 'counter', 0) FROM generate_series(1, $PRELOAD) i;"
    "UPDATE t SET data = jsonb_set(data, '{status}', to_jsonb(CASE WHEN random() < 0.5 THEN 'active' ELSE 'inactive' END))
     WHERE id = floor(random() * ($PRELOAD / $CLIENTS))::int * $CLIENTS + :client_id;"

    "Control: expensive expression"
    "CREATE TABLE t (id int PRIMARY KEY, data jsonb);
     CREATE INDEX idx ON t((md5(data::text)));
     INSERT INTO t SELECT i, jsonb_build_object('value', 0) FROM generate_series(1, $PRELOAD) i;"
    "UPDATE t SET data = jsonb_set(data, '{value}', to_jsonb((random() * 1000000)::int))
     WHERE id = floor(random() * ($PRELOAD / $CLIENTS))::int * $CLIENTS + :client_id;"

    "Induced TU_Updated: est ~10% contention"
    "CREATE TABLE t (id int PRIMARY KEY, amt INT);
     CREATE INDEX idx ON t(amt);
     INSERT INTO t SELECT i, 0 FROM generate_series(1, 2000) i;"
    "UPDATE t SET amt = (random() * 1000)::int WHERE id = floor(random() * 100 + 1)::int;"
)

run_test() {
    local version=$1
    local commit=$2
    local commit_short=$(git rev-parse --short=11 "$commit")
    local build_dir="$WORKDIR/build-$commit_short"
    local install_dir="$WORKDIR/install-$commit_short"
    local datadir="/tmp/pgdata_$version"

    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
    echo "Testing: $version ($commit_short)"
    echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"

    git checkout -q "$commit"

    if [ -d "$install_dir" ] && [ -f "$install_dir/bin/postgres" ]; then
	echo "Using existing build in $install_dir ✓"
    else
	echo -n "Building... "
	meson -C "$build_dir" clean > /dev/null 2>&1 || true
	meson setup --reconfigure --buildtype=debugoptimized -Dreadline=disabled --prefix="$install_dir" "$build_dir" "$WORKDIR" > "$RESULTS_DIR/${version}_build.log" 2>&1
	meson compile -C "$build_dir" >> "$RESULTS_DIR/${version}_build.log" 2>&1
	meson install -C "$build_dir" >> "$RESULTS_DIR/${version}_build.log" 2>&1
	echo "✓"
    fi

    echo -n "Starting server... "
    "$install_dir/bin/pg_ctl" -D "$datadir" stop > /dev/null 2>&1 || true
    pkill -9 postgres > /dev/null 2>&1 || true
    rm -rf "$datadir"
    "$install_dir/bin/initdb" -D "$datadir" > /dev/null 2>&1
    echo "autovacuum = off" >> "$datadir/postgresql.conf"
    "$install_dir/bin/pg_ctl" -D "$datadir" -l "$RESULTS_DIR/${version}_server.log" start > /dev/null 2>&1
    sleep 2
    echo "✓"

    # Run tests
    for ((i=0; i<${#TESTS[@]}; i+=3)); do
	local test_num=$((i/3 + 1))
	local test_name="${TESTS[$i]}"
	local init_sql="${TESTS[$i+1]}"
	local bench_sql="${TESTS[$i+2]}"

	echo -n "  Test $test_num ($test_name)... "

	"$install_dir/bin/createdb" "test$test_num" > /dev/null 2>&1 || true
	"$install_dir/bin/psql" -q -A -t -X "test$test_num" -c "$init_sql" > /dev/null 2>&1
	"$install_dir/bin/psql" -q -A -t -X "test$test_num" -c "VACUUM ANALYZE t;" > /dev/null 2>&1

	# Multi-client test
	echo "$bench_sql" | "$install_dir/bin/pgbench" -n -c $CLIENTS -j 4 -T $TEST_DURATION -f - "test$test_num" \
	    > "$RESULTS_DIR/${version}_test${test_num}_bench.txt" 2>&1

	# Stats
	"$install_dir/bin/psql" -q -A -t -X "test$test_num" > "$RESULTS_DIR/${version}_test${test_num}_stats.txt" 2>&1 <<EOF
SELECT '$test_name' as test_name;
WITH table_stats AS (
  SELECT
    CASE
      WHEN EXISTS (SELECT 1 FROM pg_inherits WHERE inhparent = 't'::regclass)
      THEN (SELECT sum(pg_stat_get_tuples_updated(inhrelid)) FROM pg_inherits WHERE inhparent = 't'::regclass)
      ELSE pg_stat_get_tuples_updated('t'::regclass)
    END as updates,
    CASE
      WHEN EXISTS (SELECT 1 FROM pg_inherits WHERE inhparent = 't'::regclass)
      THEN (SELECT sum(pg_stat_get_tuples_hot_updated(inhrelid)) FROM pg_inherits WHERE inhparent = 't'::regclass)
      ELSE pg_stat_get_tuples_hot_updated('t'::regclass)
    END as hot
)
SELECT updates, hot,
       round(100.0 * hot / NULLIF(updates, 0), 1) as hot_pct
FROM table_stats;
SELECT pg_relation_size('t') as heap_after,
       (SELECT sum(pg_relation_size(indexrelid)) FROM pg_index WHERE indrelid = 't'::regclass) as index_after;
EOF

	echo "✓"
    done

    echo -n "Stopping server... "
    "$install_dir/bin/pg_ctl" -D "$datadir" stop > /dev/null 2>&1
    pkill -9 postgres > /dev/null 2>&1 || true
    echo "✓"
    echo ""
}

run_test "baseline" "$BASELINE_COMMIT"
run_test "patched" "$PATCHED_COMMIT"

# Generate report
echo "╔════════════════════════════════════════════════════════════════╗"
echo "║                      RESULTS SUMMARY                           ║"
echo "╚════════════════════════════════════════════════════════════════╝"
echo ""

test_names=()
for ((i=0; i<${#TESTS[@]}; i+=3)); do
    test_names+=("${TESTS[$i]}")
done

echo "HOT Update Percentages:"
echo "────────────────────────────────────────────────────────────────"
printf "%-35s %10s %10s %10s\n" "Test" "Baseline" "Patched" "Δ"
echo "────────────────────────────────────────────────────────────────"

for i in $(seq 1 ${#test_names[@]}); do
    baseline_hot=$(grep -v "Time:" "$RESULTS_DIR/baseline_test${i}_stats.txt" 2>/dev/null | grep "|" | head -1 | cut -d'|' -f3)
    patched_hot=$(grep -v "Time:" "$RESULTS_DIR/patched_test${i}_stats.txt" 2>/dev/null | grep "|" | head -1 | cut -d'|' -f3)

    if [ -n "$baseline_hot" ] && [ -n "$patched_hot" ]; then
	delta=$(echo "$patched_hot - $baseline_hot" | bc)
	printf "%-35s %9s%% %9s%% %+9s%%\n" "${test_names[$((i-1))]}" "$baseline_hot" "$patched_hot" "$delta"
    fi
done

echo ""
echo "Throughput (10 clients):"
echo "────────────────────────────────────────────────────────────────"
printf "%-35s %12s %12s %10s\n" "Test" "Baseline" "Patched" "Δ"
echo "────────────────────────────────────────────────────────────────"

for i in $(seq 1 ${#test_names[@]}); do
    baseline_tps=$(grep "tps = " "$RESULTS_DIR/baseline_test${i}_bench.txt" 2>/dev/null | awk '{print $3}')
    patched_tps=$(grep "tps = " "$RESULTS_DIR/patched_test${i}_bench.txt" 2>/dev/null | awk '{print $3}')

    if [ -n "$baseline_tps" ] && [ -n "$patched_tps" ]; then
	delta=$(echo "scale=1; 100 * ($patched_tps - $baseline_tps) / $baseline_tps" | bc)
	printf "%-35s %12.1f %12.1f %+9s%%\n" "${test_names[$((i-1))]}" "$baseline_tps" "$patched_tps" "$delta"
    fi
done

echo ""
echo "Index Bloat After Updates (MB):"
echo "────────────────────────────────────────────────────────────────"
printf "%-35s %10s %10s %10s\n" "Test" "Baseline" "Patched" "Reduction"
echo "────────────────────────────────────────────────────────────────"

for i in $(seq 1 ${#test_names[@]}); do
    baseline_idx=$(grep -v "Time:" "$RESULTS_DIR/baseline_test${i}_stats.txt" 2>/dev/null | grep "|" | tail -1 | cut -d'|' -f2)
    patched_idx=$(grep -v "Time:" "$RESULTS_DIR/patched_test${i}_stats.txt" 2>/dev/null | grep "|" | tail -1 | cut -d'|' -f2)

    if [ -n "$baseline_idx" ] && [ -n "$patched_idx" ] && [ "$baseline_idx" != "0" ]; then
	baseline_mb=$(echo "scale=1; $baseline_idx / 1048576" | bc)
	patched_mb=$(echo "scale=1; $patched_idx / 1048576" | bc)
	reduction=$(echo "scale=1; 100 * ($baseline_idx - $patched_idx) / $baseline_idx" | bc)
	printf "%-35s %9.1f %9.1f %9s%%\n" "${test_names[$((i-1))]}" "$baseline_mb" "$patched_mb" "$reduction"
    fi
done

echo ""
echo "────────────────────────────────────────────────────────────────"
echo "Full results: $RESULTS_DIR"
echo "────────────────────────────────────────────────────────────────"

git checkout -q -