-- How many times the hook runs PostgreSQL's planner for a statement. A statement is planned once; the hook plans it more than once only to choose between two
-- forms of it (a shared CTE and its inline plan, the lateral sub-selects as written and pulled up), and the choice is kept for the statement without its constants
-- (sift.agg_verdict_cache), so that the next statement of that shape plans the form that won, once. The function sift._test_agg_plan_passes counts the runs of the planner.
CREATE EXTENSION pg_sift;
CREATE EXTENSION pg_sift_test;
SET client_min_messages = warning;
SET max_parallel_workers_per_gather = 0;
CREATE TABLE pp_site (id int PRIMARY KEY, name text);
INSERT INTO pp_site SELECT g, 'n' || g FROM generate_series(1, 2000) g;
CREATE TABLE pp_event (id int PRIMARY KEY, site_id int NOT NULL, ts date NOT NULL, kind int NOT NULL, doc jsonb) WITH (autovacuum_enabled = false);
INSERT INTO pp_event SELECT g, 1 + g % 2000, date '2025-01-01' + g % 360, g % 7, jsonb_build_object('a', g % 3, 'b', g % 5) FROM generate_series(1, 30000) g;
CREATE INDEX pp_event_col ON pp_event USING sift_col (id, site_id, ts, kind, doc) WITH (paths = 'doc.* float8');
VACUUM (FREEZE, ANALYZE) pp_event;
ANALYZE pp_site;
RESET client_min_messages;

CREATE FUNCTION pp_passes(q text) RETURNS bigint LANGUAGE plpgsql AS $f$
DECLARE l text;
BEGIN
	PERFORM sift._test_agg_plan_passes(true);
	FOR l IN EXECUTE 'EXPLAIN (COSTS OFF) ' || q LOOP NULL; END LOOP;
	RETURN sift._test_agg_plan_passes(true);
END $f$;

CREATE FUNCTION pp_plan(q text) RETURNS text LANGUAGE plpgsql AS $f$
DECLARE l text; t text := '';
BEGIN
	FOR l IN EXECUTE 'EXPLAIN (COSTS ON) ' || q LOOP t := t || l || E'\n'; END LOOP;
	RETURN t;
END $f$;

-- a statement with lateral sub-selects that can be pulled up, and no aggregate to push down
CREATE FUNCTION pp_list(ids text) RETURNS text LANGUAGE sql AS $f$ SELECT format($q$
 SELECT a.id, s.key, s.name FROM pp_event a INNER JOIN LATERAL (SELECT r.key, m.name FROM jsonb_each(a.doc) r LEFT JOIN pp_site m ON m.id = a.site_id) s ON true
 WHERE a.site_id IN (%s) ORDER BY a.id LIMIT 10 $q$, ids) $f$;

-- the first statement of a backend is planned again once the library is loaded
SELECT pp_passes('SELECT 1') >= 1 AS warmed;

-- a statement of nothing to choose between is planned once: a point lookup, an aggregate, a CTE read twice of a table with no column index
SELECT pp_passes('SELECT * FROM pp_event WHERE id = 5') AS point,
	pp_passes('SELECT kind, count(*) FROM pp_event GROUP BY kind') AS aggregate,
	pp_passes('WITH a AS (SELECT id, name FROM pp_site WHERE id < 100) SELECT count(*) FROM (SELECT * FROM a UNION ALL SELECT * FROM a) b') AS cte_of_other_table;

-- the statement that plans two forms does it once, and its next ones (other constants) plan the form that won, once; the seventh keeps clear of the plan in full that every eighth hit makes
SELECT pp_passes(pp_list('1,2,3')) <= 2 AS first_at_most_two;
SELECT bool_and(pp_passes(pp_list(format('%s,%s,%s', 3 * n + 1, 3 * n + 2, 3 * n + 3))) = 1) AS then_one_each, count(*) AS statements FROM generate_series(1, 6) n;

-- the plan the verdict names is the plan in full (the same plan, costs and all, for the same constants)
SELECT pp_plan(pp_list('10,11,12')) = pp_plan(pp_list('10,11,12')) AS same_plan_again;

-- with the cache off every statement is planned in full, and its plan is the same
SET sift.agg_verdict_cache = off;
SELECT pp_passes(pp_list('1,2,3')) <= 2 AS full_at_most_two;
SELECT pp_plan(pp_list('20,21,22')) AS plan_in_full \gset
RESET sift.agg_verdict_cache;
SELECT pp_plan(pp_list('20,21,22')) = :'plan_in_full' AS same_as_in_full;

-- the rows are those of the normal plan
\ir test/agg_helpers.sql
SELECT runq(pp_list('1,2,3,40,41'), true) = runq(pp_list('1,2,3,40,41'), false) AS same_rows;

DROP FUNCTION pp_passes, pp_plan, pp_list, runq, pushed, runs_in_scan, outcome(text, bool);
DROP TABLE pp_event, pp_site;
DROP EXTENSION pg_sift_test;
DROP EXTENSION pg_sift;