-- test/sql/multi_leaf.sql
-- Regression test for multi-leaf posting trees (Phase 4.2).
--
-- Objective: Force a trigram to exceed the single-leaf posting budget
-- (~8 KB) so the build splits it across multiple leaves linked by
-- right_link.  Verify that index scans return the same rows as seq-scans.

-- Load the extension (requires preload).
CREATE EXTENSION IF NOT EXISTS pg_tre;

-- Setup: generate 100K rows where the trigram 'the' appears in every row.
-- At ~8 bytes per TID after sparsemap compression, 100K TIDs = ~800 KB,
-- far exceeding the ~8 KB single-leaf budget.
DROP TABLE IF EXISTS multi_leaf_test CASCADE;
CREATE TABLE multi_leaf_test (id serial PRIMARY KEY, body text);

-- Insert 100K rows with 'the' trigram present in every row.
INSERT INTO multi_leaf_test (body)
SELECT 'The quick brown fox jumps over the lazy dog. Row ' || i
FROM generate_series(1, 100000) AS i;

-- Create pg_tre index (should split 'the' posting across multiple leaves).
-- Suppress the version-varying build-progress NOTICEs (trigram/posting
-- counts) here and for the later REINDEX so the expected file is stable
-- across PG majors.
SET client_min_messages = warning;
CREATE INDEX multi_leaf_idx ON multi_leaf_test USING tre (body);
RESET client_min_messages;

-- Verify index built successfully (no errcode_program_limit_exceeded).
SELECT pg_relation_size('multi_leaf_idx') > 0 AS index_exists;

-- Plan-shape probe: assert the forced scan is what we expect, via a
-- single text token (no exact plan-shape dump, which differs across
-- PG majors).
CREATE FUNCTION ml_uses_index(q text) RETURNS boolean
LANGUAGE plpgsql AS $$
DECLARE r record; txt text;
BEGIN
  FOR r IN EXECUTE 'EXPLAIN (FORMAT JSON) ' || q LOOP
    txt := r."QUERY PLAN"::text;
  END LOOP;
  -- Scan the whole plan tree (robust to Aggregate/Gather wrapping and
  -- parallel plans) for an index or bitmap scan node.
  RETURN txt ILIKE '%Index Scan%' OR txt ILIKE '%Bitmap%Scan%';
END $$;

-- Differential test 1: exact match for 'the' (should return all 100K rows).
-- Compare index scan vs seq-scan.
SET enable_seqscan = off;
SELECT CASE WHEN ml_uses_index($$SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0)$$)
            THEN 'idx_forced_uses_index' ELSE 'idx_forced_seq' END AS plan1;
SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0);

SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT CASE WHEN ml_uses_index($$SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0)$$)
            THEN 'seq_forced_uses_index' ELSE 'seq_forced_seq' END AS plan2;
SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0);

SET enable_indexscan = on;
SET enable_bitmapscan = on;

-- Differential test 2: pattern that matches a subset of rows.
SET enable_seqscan = off;
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('Row 12[0-9][0-9][0-9]', 0);

SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('Row 12[0-9][0-9][0-9]', 0);

SET enable_indexscan = on;
SET enable_bitmapscan = on;

-- Differential test 3: approximate match (k=1).
SET enable_seqscan = off;
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('quik', 1);  -- should match 'quick'

SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('quik', 1);

SET enable_indexscan = on;
SET enable_bitmapscan = on;

-- Differential test 4: non-matching pattern (should return 0 rows).
SET enable_seqscan = off;
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('xyzabc', 0);

SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT COUNT(*) FROM multi_leaf_test
WHERE body %~~ tre_pattern('xyzabc', 0);

-- Reset planner settings.
SET enable_indexscan = on;
SET enable_bitmapscan = on;
SET enable_seqscan = on;

-- Differential equivalence check: index scan and seq-scan must agree on
-- every row.  This catches regressions like the multi-leaf right-link
-- chain bug where index scans silently returned a subset of the heap
-- match-set.  We compare the full id sets, not just COUNT(*), so a
-- spurious-match regression also fails.
SET enable_seqscan = off;
CREATE TEMP TABLE multi_leaf_idx_ids AS
    SELECT id FROM multi_leaf_test
    WHERE body %~~ tre_pattern('the', 0);

SET enable_seqscan = on;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
CREATE TEMP TABLE multi_leaf_seq_ids AS
    SELECT id FROM multi_leaf_test
    WHERE body %~~ tre_pattern('the', 0);

SET enable_indexscan = on;
SET enable_bitmapscan = on;

SELECT
    (SELECT COUNT(*) FROM multi_leaf_idx_ids) AS idx_rows,
    (SELECT COUNT(*) FROM multi_leaf_seq_ids) AS seq_rows,
    (SELECT COUNT(*) FROM multi_leaf_idx_ids) =
        (SELECT COUNT(*) FROM multi_leaf_seq_ids)
    AS counts_agree,
    NOT EXISTS (
        SELECT 1 FROM multi_leaf_idx_ids EXCEPT
        SELECT 1 FROM multi_leaf_seq_ids
    )
    AND NOT EXISTS (
        SELECT 1 FROM multi_leaf_seq_ids EXCEPT
        SELECT 1 FROM multi_leaf_idx_ids
    ) AS row_sets_agree;

DROP TABLE multi_leaf_idx_ids;
DROP TABLE multi_leaf_seq_ids;

-- REINDEX test: drop and rebuild the index to verify multi-leaf rebuild.
-- Suppress the version-varying rebuild NOTICEs too.
SET client_min_messages = warning;
REINDEX INDEX multi_leaf_idx;
RESET client_min_messages;

-- Clean up the plan-shape probe helper.
DROP FUNCTION ml_uses_index(text);

-- Verify rebuilt index still works correctly.
SET enable_seqscan = off;
SELECT COUNT(*) FROM multi_leaf_test WHERE body %~~ tre_pattern('the', 0);
SET enable_seqscan = on;

-- Cleanup.
DROP INDEX multi_leaf_idx;
DROP TABLE multi_leaf_test;