#!/usr/bin/env bash
# Real released libraries write the input, including unpromoted records. No fixture produced by the new writer can prove this upgrade.
. "$(dirname "$0")/lib.bash"
REPO=$(cd "$(dirname "$0")/../.." && pwd)
if ((PG_MAJOR != 18)); then echo '1..0 # SKIP the released libraries build on PostgreSQL 18 only (two library roots; none supports 19 yet)'; exit 0; fi
for ref in v3.0.1 v3.1.0; do
	if ! git -C "$REPO" rev-parse --verify -q "$ref^{commit}" >/dev/null; then
		echo "1..0 # SKIP released library source $ref is unavailable"
		exit 0
	fi
done
newroot=$ROOT
QUERY="SELECT count(*) FILTER (WHERE jsonb_path_exists(items, '\$[*] ? (@.s != \"gift\")')) || ':' ||
 count(*) FILTER (WHERE jsonb_path_exists(items, '\$[*] ? (@.s == null)')) || ':' ||
 count(*) FILTER (WHERE items @> '[{\"s\":null}]') FROM oldcol"
TYPES="SELECT string_agg(coalesce(t, 'missing') || ':' || n, ',' ORDER BY t NULLS FIRST)
 FROM (SELECT jsonb_typeof(attr->'a') t, count(*) n FROM oldcol GROUP BY 1) s"
for ref in v3.0.1 v3.1.0; do
	build_old "$ref"; oldroot=$OLDROOT
	node_init legacy 2 'autovacuum = off
sift.async_maintenance = off'
	use_root legacy "$oldroot"
	textkind=astext
	[[ $ref != v3.0.1 ]] || textkind=text
	node_start legacy || bail "cannot start $ref"
	sql legacy "CREATE EXTENSION pg_sift" "CREATE TABLE oldcol (id int, items jsonb, attr jsonb)" \
		"CREATE INDEX oldcol_idx ON oldcol USING sift_col (id,items,attr) WITH (paths = 'items[]{s text}, attr->a $textkind')" \
		"INSERT INTO oldcol SELECT i, jsonb_build_array(CASE i % 3 WHEN 0 THEN '{}'::jsonb WHEN 1 THEN '{\"s\":null}'::jsonb ELSE '{\"s\":\"gift\"}'::jsonb END),
		 CASE i % 5 WHEN 0 THEN '{}'::jsonb WHEN 1 THEN '{\"a\":null}'::jsonb WHEN 2 THEN '{\"a\":true}'::jsonb WHEN 3 THEN '{\"a\":1}'::jsonb ELSE '{\"a\":\"1\"}'::jsonb END
		 FROM generate_series(1,90) i" "VACUUM ANALYZE oldcol" >/dev/null || bail "$ref setup"
	if [[ $ref == v3.1.0 ]]; then
		sql legacy "CREATE TABLE oldseg AS TABLE oldcol" "CREATE INDEX oldseg_idx ON oldseg USING sift_col (id,items,attr) WITH (paths = 'items[]{s text}, attr->a astext')" \
			"VACUUM ANALYZE oldseg" "CREATE TABLE oldtbl (LIKE oldcol) USING sift_table" \
			"INSERT INTO oldtbl SELECT * FROM oldcol" "VACUUM oldtbl" >/dev/null || bail '3.1 immutable setup'
	fi
	want=$(sql legacy "SET sift.enable_agg = off" "$QUERY")
	types=$(sql legacy "SET sift.enable_agg = off" "$TYPES")
	# The small INSERT stays in the old library's write buffer, never an immutable segment.
	check "$ref left unpromoted records" test "$(sql legacy "SELECT count(*) FROM sift.col_segments('oldcol_idx') WHERE kind = 'immutable'")" == 0
	node_stop legacy
	use_root legacy "$newroot"
	node_start legacy || bail 'cannot start new library'
	sql legacy "ALTER EXTENSION pg_sift UPDATE" >/dev/null || bail 'SQL update'
	if [[ $ref == v3.1.0 ]]; then
		sql legacy "SELECT sift.table_set_paths('oldtbl','items','items[]{s text}')" \
			"SELECT sift.table_set_paths('oldtbl','attr','attr->a astext')" >/dev/null || bail 'declare upgraded paths'
		check 'real 3.1 minor 4 index null predicates equal the heap' test "$(sql legacy "SET seq_page_cost = 10000" "${QUERY//oldcol/oldseg}")" == "$want"
		check 'real 3.1 table groups preserve JSON null answers' test "$(sql legacy "SET seq_page_cost = 10000" "${QUERY//oldcol/oldtbl}")" == "$want"
		sql legacy "VACUUM FULL oldtbl" >/dev/null || bail '3.1 table rewrite'
		# the minor of a table's groups is what this tree writes (7 with the minimum and maximum of the cube, 22 where a key table fits): the groups of 3.1 are rewritten above minor 4
		tmin=$(sql legacy "SELECT min(format) FROM sift.table_segments('oldtbl') WHERE kind = 'columns'")
		check 'VACUUM FULL upgrades 3.1 groups above minor 4' test "$tmin" -gt 4
		check 'rewritten 3.1 groups answer null predicates exactly' test "$(sql legacy "SET seq_page_cost = 10000" "${QUERY//oldcol/oldtbl}")" == "$want"
	fi
	check "$ref write-buffer null predicates equal the heap" test "$(sql legacy "SET seq_page_cost = 10000" "$QUERY")" == "$want"
	check "$ref write-buffer mixed astext kinds equal the heap" test "$(sql legacy "SET seq_page_cost = 10000" "$TYPES")" == "$types"
	sql legacy "SELECT sift.col_promote('oldcol_idx')" >/dev/null || bail 'promotion'
	check "$ref promotion preserves uncertain null kinds" test "$(sql legacy "SET seq_page_cost = 10000" "$QUERY")" == "$want"
	check "$ref promotion preserves uncertain astext kinds" test "$(sql legacy "SET seq_page_cost = 10000" "$TYPES")" == "$types"
	check "$ref promotion does not claim minor 6" test "$(sql legacy "SELECT max(format) < 6 FROM sift.col_segments('oldcol_idx') WHERE kind = 'immutable'")" == t
	# the layout of an index made before 4.2 (no presence leaf), so that 3.1 reads the segments by their minor (the leaf is a layout it refuses before it reads one)
	sql legacy "SET sift.test_col_no_present = on" "REINDEX INDEX oldcol_idx" >/dev/null || bail 'reindex'
	check "$ref REINDEX writes minor 12 or later (the complete kinds, with the sections that are on by default)" test "$(sql legacy "SELECT min(format) >= 12 FROM sift.col_segments('oldcol_idx') WHERE kind = 'immutable'")" == t
	check "$ref rewritten null predicates equal the heap" test "$(sql legacy "SET seq_page_cost = 10000" "$QUERY")" == "$want"
	check "$ref rewritten astext kinds equal the heap" test "$(sql legacy "SET seq_page_cost = 10000" "$TYPES")" == "$types"
	if [[ $ref == v3.1.0 ]]; then
		# the small-set summary of a bigint column (minor 10, opt-in): under the setting 4 the new library writes what 3.1 reads, under the default the 3.1 library refuses it
		SETQ="SELECT count(*) || ':' || sum(id) FROM setlow WHERE k = 2919"
		sql legacy "CREATE TABLE setlow AS SELECT i AS id, (i * 7919 % 5000)::bigint AS k FROM generate_series(1, 3000) i" "CREATE TABLE sethigh AS TABLE setlow" \
			"SET sift.col_format_minor = 4" "CREATE INDEX setlow_idx ON setlow USING sift_col (id, k) WITH (key_columns = 'k')" "RESET sift.col_format_minor" \
			"CREATE INDEX sethigh_idx ON sethigh USING sift_col (id, k) WITH (key_columns = 'k')" "VACUUM ANALYZE setlow" "VACUUM ANALYZE sethigh" >/dev/null || bail 'minor 10 setup'
		setwant=$(sql legacy "SET sift.enable_agg = off" "$SETQ")
		check 'the setting 4 writes no summary (minor 4) and the default one does (minor 12, with the sections that are on by default)' test "$(sql legacy "SELECT (SELECT max(format) FROM sift.col_segments('setlow_idx') WHERE kind = 'immutable') || ':' || (SELECT max(format) FROM sift.col_segments('sethigh_idx') WHERE kind = 'immutable')")" == 4:12
		# the sorted run (minor 13, opt-in): the setting 4 writes no run, the setting 14 writes what 3.1 refuses (from 15 the run also holds its head, minor 18)
		ORDQ="SELECT string_agg(id::text, ',' ORDER BY k, id) FROM (SELECT id, k FROM ordlow ORDER BY k, id LIMIT 50) x"
		sql legacy "CREATE TABLE ordlow AS SELECT i AS id, i * 7919 % 5000 AS k FROM generate_series(1, 3000) i" "CREATE TABLE ordhigh AS TABLE ordlow" \
			"SET sift.col_format_minor = 4" "CREATE INDEX ordlow_idx ON ordlow USING sift_col (id, k) WITH (order_columns = 'k')" "SET sift.col_format_minor = 14" \
			"CREATE INDEX ordhigh_idx ON ordhigh USING sift_col (id, k) WITH (order_columns = 'k')" "RESET sift.col_format_minor" "VACUUM ANALYZE ordlow" "VACUUM ANALYZE ordhigh" >/dev/null || bail 'minor 13 setup'
		ordwant=$(sql legacy "SET sift.enable_agg = off" "$ORDQ")
		check 'the setting 4 writes no run and the default one does' test "$(sql legacy "SELECT (SELECT max(format) FROM sift.col_segments('ordlow_idx') WHERE kind = 'immutable') || ':' || (SELECT max(format) FROM sift.col_segments('ordhigh_idx') WHERE kind = 'immutable')")" == 4:13
		# the key list or bitmap of an int column (minor 15, opt-in): the setting 4 writes none, the default one writes what 3.1 refuses
		KPQ="SELECT count(DISTINCT k), count(*) FROM kplow"
		sql legacy "CREATE TABLE kplow AS SELECT i AS id, i % 200 AS k, i * 7919 % 100003 AS v FROM generate_series(1, 3000) i" "CREATE TABLE kphigh AS TABLE kplow" \
			"SET sift.col_format_minor = 4" "CREATE INDEX kplow_idx ON kplow USING sift_col (id, k, v) WITH (key_columns = 'k')" "RESET sift.col_format_minor" \
			"CREATE INDEX kphigh_idx ON kphigh USING sift_col (id, k, v) WITH (key_columns = 'k')" "VACUUM ANALYZE kplow" "VACUUM ANALYZE kphigh" >/dev/null || bail 'minor 15 setup'
		kpwant=$(sql legacy "SET sift.enable_agg = off" "$KPQ")
		check 'the setting 4 writes no key presence and the default one does' test "$(sql legacy "SELECT (SELECT max(format) FROM sift.col_segments('kplow_idx') WHERE kind = 'immutable') || ':' || (SELECT max(format) FROM sift.col_segments('kphigh_idx') WHERE kind = 'immutable')")" == 4:15
		node_stop legacy
		use_root legacy "$oldroot"
		node_start legacy || bail 'cannot restart 3.1'
		err=$(sql legacy "SET enable_seqscan = off" "SELECT sift.col_verify('oldcol_idx', false)" 2>&1)
		diag "3.1 refusal: $err"
		check '3.1 refuses a minor 12 or later index (complete kinds and the default sections) with a format error and REINDEX hint' test "$(grep -c 'format 1\.[0-9]*\|REINDEX' <<<"$err")" -ge 2
		err=$(sql legacy "SELECT count(*) FROM oldtbl" 2>&1)
		check "3.1 refuses minor $tmin table groups" grep -q "format 1.$tmin" <<<"$err"
		check '3.1 reads an index written with key_columns on a bigint column under the setting 4' test "$(sql legacy "SELECT sift.col_verify('setlow_idx', true)" "SET sift.enable_agg = on" "$SETQ" | tail -n 1)" == "$setwant"
		err=$(sql legacy "SET enable_seqscan = off" "SELECT sift.col_verify('sethigh_idx', false)" 2>&1)
		check '3.1 refuses a minor 12 or later set summary index with a format error and REINDEX hint' test "$(grep -c 'format 1\.[0-9]*\|REINDEX' <<<"$err")" -ge 2
		check '3.1 reads an index written with order_columns under the setting 4' test "$(sql legacy "SELECT sift.col_verify('ordlow_idx', true)" "SET sift.enable_agg = on" "$ORDQ" | tail -n 1)" == "$ordwant"
		err=$(sql legacy "SET enable_seqscan = off" "SELECT sift.col_verify('ordhigh_idx', false)" 2>&1)
		check '3.1 refuses a minor 13 index with a format error and REINDEX hint' test "$(grep -c 'format 1.13\|REINDEX' <<<"$err")" -ge 2
		check '3.1 reads an index written with key_columns on an int column under the setting 4' test "$(sql legacy "SELECT sift.col_verify('kplow_idx', true)" "SET sift.enable_agg = on" "$KPQ" | tail -n 1)" == "$kpwant"
		err=$(sql legacy "SET enable_seqscan = off" "SELECT sift.col_verify('kphigh_idx', false)" 2>&1)
		check '3.1 refuses a minor 15 index with a format error and REINDEX hint' test "$(grep -c 'format 1.15\|REINDEX' <<<"$err")" -ge 2
	fi
	node_stop legacy
	ROOT=$newroot
done
echo "1..$NTEST"
finish