--
-- planner.sql - Phase 6 planner cost estimation tests (version-robust)
--
-- The planner makes a cost-based choice between index and seq scan
-- depending on pattern selectivity.  The exact plan shape and cost
-- differ across PG majors and selectivity-estimate changes, so we do
-- NOT assert plan shape here (planner_auto.sql / selectivity.sql cover
-- the index-selection behavior via FORMAT JSON probes).  This test
-- asserts the contract that actually matters: whichever scan the
-- planner picks, it returns the correct rows.
--
SET client_min_messages = warning;
CREATE EXTENSION IF NOT EXISTS pg_tre;

-- Test fixture: small table to keep tests fast
CREATE TABLE planner_test (id int, body text);
INSERT INTO planner_test
    SELECT i, 'row_' || i || ' test pattern selective unique'
    FROM generate_series(1, 100) AS i;

-- Add some rows with specific patterns
INSERT INTO planner_test VALUES
    (101, 'approximate match pattern here'),
    (102, 'another approximate example'),
    (103, 'test case for selectivity');

-- Create index
CREATE INDEX idx_planner_test ON planner_test USING tre (body);

-- Force ANALYZE to update statistics
ANALYZE planner_test;

-- Index result must equal seq-scan result for every pattern, regardless
-- of which scan the cost model chooses.
CREATE FUNCTION planner_agree(pat text, k int) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE seq_n bigint; idx_n bigint;
BEGIN
  SET LOCAL enable_indexscan=off; SET LOCAL enable_bitmapscan=off; SET LOCAL enable_seqscan=on;
  EXECUTE format('SELECT count(*) FROM planner_test WHERE tre_amatch(body, %L, %s)', pat, k) INTO seq_n;
  SET LOCAL enable_seqscan=off; SET LOCAL enable_indexscan=on; SET LOCAL enable_bitmapscan=on;
  EXECUTE format('SELECT count(*) FROM planner_test WHERE body %%~~ tre_pattern(%L, %s)', pat, k) INTO idx_n;
  IF seq_n = idx_n THEN RETURN 'agree'; END IF;
  RETURN format('disagree seq=%s idx=%s', seq_n, idx_n);
END $$;

SELECT planner_agree('approximate', 0);
SELECT planner_agree('row_42', 0);
SELECT planner_agree('e', 0);
SELECT planner_agree('approximate', 1);

-- Sanity check on the raw counts (version-independent).
SELECT COUNT(*) FROM planner_test WHERE body %~~ tre_pattern('approximate', 0);
SELECT COUNT(*) FROM planner_test WHERE body %~~ tre_pattern('row_42', 0);

-- Cleanup
DROP FUNCTION planner_agree(text, int);
DROP TABLE planner_test;