-- ============================================================================
-- NESTED_STATEMENTS Feature - Comprehensive Test Suite
-- ============================================================================
-- This script tests the NESTED_STATEMENTS feature and demonstrates
-- how nesting levels reflect the actual PostgreSQL executor call stack.
--
-- KEY INSIGHT: Nesting levels depend on HOW a function is called:
--   PERFORM func() / SELECT func() INTO var → creates new executor level
--   result := func()                        → same executor level as caller
-- ============================================================================

\echo '============================================================================'
\echo 'NESTED_STATEMENTS Feature - Comprehensive Test Suite'
\echo '============================================================================'
\echo ''
\echo 'This test suite demonstrates:'
\echo '  1. Validation (NESTED_STATEMENTS requires ANALYZE)'
\echo '  2. Simple PL/pgSQL function (all level 1)'
\echo '  3. PERFORM pattern (creates deeper nesting levels)'
\echo '  4. Expression assignment pattern (stays at same level)'
\echo '  5. Comparison: PERFORM vs expression assignment'
\echo '  6. SQL function nesting (true SQL nesting)'
\echo '  7. Three-level chain with PERFORM'
\echo '  8. Recursive function (increasing levels)'
\echo '  9. Exception handling blocks'
\echo '  10. No nested statements (plain query)'
\echo '  11. Trigger-fired nested statements'
\echo '  12. Combined with VERBOSE and BUFFERS options'
\echo '  13. Statement numbering = completion order (triggers demo)'
\echo '  14. BEGIN/ROLLBACK safety pattern'
\echo '  15. Error during EXPLAIN does not crash (Bug 1 fix)'
\echo '  16. Nested EXPLAIN does not crash (Bug 2 fix)'
\echo '  17. Memory context cleanup (Bug 3 fix)'
\echo '  18. Memory context does not grow across repeated calls'
\echo '  19. Stress test - 50 nested statements'
\echo '  20. Execution Time per nested statement (SUMMARY default)'
\echo '  21. Execution Time hidden with SUMMARY OFF'
\echo '  22. Structured output - JSON format'
\echo '  23. Structured output - XML format'
\echo '  24. Structured output - YAML format'
\echo ''-- ============================================================================
-- Setup: Create shared test table
-- ============================================================================
DROP TABLE IF EXISTS products CASCADE;
CREATE TABLE products (id INT, name TEXT, price NUMERIC, category TEXT);
INSERT INTO products VALUES
    (1, 'Laptop', 999, 'Electronics'),
    (2, 'Mouse', 29, 'Electronics'),
    (3, 'Book', 19, 'Books');

-- ============================================================================
-- TEST 1: Validation
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 1: Validation - NESTED_STATEMENTS requires ANALYZE'
\echo '============================================================================'
\echo ''
\echo 'Expected: ERROR message'
\echo ''

EXPLAIN (NESTED_STATEMENTS) SELECT 1;

\echo ''

-- ============================================================================
-- TEST 2: Simple PL/pgSQL Function (All Level 1)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 2: Simple PL/pgSQL Function - All Statements at Level 1'
\echo '============================================================================'
\echo ''
\echo 'Purpose: SQL statements in a single function all execute at level 1'
\echo '         because they run sequentially in the same executor context.'
\echo ''

CREATE OR REPLACE FUNCTION simple_function() RETURNS INTEGER AS $$
DECLARE cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO cnt FROM products;
    INSERT INTO products VALUES (10, 'Temp', 1, 'Temp');
    UPDATE products SET price = price + 1 WHERE id = 1;
    DELETE FROM products WHERE id = 10;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

\echo 'Expected: All 4 statements at level 1'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT simple_function();

\echo ''

-- ============================================================================
-- TEST 3: PERFORM Pattern - Creates Deeper Nesting
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 3: PERFORM Pattern - Creates Deeper Nesting Levels'
\echo '============================================================================'
\echo ''
\echo 'Purpose: PERFORM func() creates a new executor call (SELECT func()),'
\echo '         so statements inside the called function run at a deeper level.'
\echo ''
\echo 'How it works internally:'
\echo '  PERFORM func() → executes "SELECT func()" → ExecutorRun increments level'
\echo '  → func() body runs its SQL at the elevated level'
\echo ''

CREATE OR REPLACE FUNCTION inner_perform() RETURNS void AS $$
BEGIN
    UPDATE products SET price = price + 1 WHERE id = 2;
    INSERT INTO products VALUES (20, 'Inner', 50, 'Test');
    DELETE FROM products WHERE id = 20;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION outer_perform() RETURNS void AS $$
DECLARE cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO cnt FROM products;  -- level 1
    PERFORM inner_perform();        -- creates new executor → inner runs at level 2
    UPDATE products SET price = price - 1 WHERE id = 2;  -- level 1
END;
$$ LANGUAGE plpgsql;

\echo 'Expected:'
\echo '  Statement #1 (level 1): SELECT COUNT in outer_perform'
\echo '  Statement #2 (level 2): UPDATE in inner_perform'
\echo '  Statement #3 (level 2): INSERT in inner_perform'
\echo '  Statement #4 (level 2): DELETE in inner_perform'
\echo '  Statement #5 (level 1): SELECT inner_perform() [the PERFORM call]'
\echo '  Statement #6 (level 1): UPDATE in outer_perform'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT outer_perform();

\echo ''

-- ============================================================================
-- TEST 4: Expression Assignment - Same Level
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 4: Expression Assignment (result := func()) - Same Level'
\echo '============================================================================'
\echo ''
\echo 'Purpose: result := func() evaluates the function as an expression via'
\echo '         ExecEvalFunc WITHOUT creating a new executor call. Statements'
\echo '         inside the called function run at the SAME level as the caller.'
\echo ''
\echo 'How it works internally:'
\echo '  result := func() → ExecEvalFunc(func) → NO new ExecutorRun'
\echo '  → func() body runs its SQL at the SAME level as caller'
\echo ''

CREATE OR REPLACE FUNCTION inner_expr() RETURNS NUMERIC AS $$
DECLARE total NUMERIC;
BEGIN
    SELECT SUM(price) INTO total FROM products;
    UPDATE products SET price = price + 1 WHERE id = 3;
    RETURN total;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION outer_expr() RETURNS TEXT AS $$
DECLARE
    result NUMERIC;
    cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO cnt FROM products;   -- level 1
    result := inner_expr();                   -- expression eval, inner runs at level 1 too!
    DELETE FROM products WHERE price > 9999;  -- level 1
    RETURN 'Total: ' || result;
END;
$$ LANGUAGE plpgsql;

\echo 'Expected: ALL statements at level 1 (no deeper nesting)'
\echo '  Statement #1 (level 1): SELECT COUNT in outer_expr'
\echo '  Statement #2 (level 1): SELECT SUM in inner_expr  ← same level!'
\echo '  Statement #3 (level 1): UPDATE in inner_expr      ← same level!'
\echo '  Statement #4 (level 1): DELETE in outer_expr'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT outer_expr();

\echo ''

-- ============================================================================
-- TEST 5: Side-by-Side Comparison
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 5: PERFORM vs Expression Assignment - Side by Side'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Same inner function called two different ways to show the'
\echo '         nesting level difference.'
\echo ''

CREATE OR REPLACE FUNCTION shared_inner() RETURNS INTEGER AS $$
DECLARE cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO cnt FROM products WHERE category = 'Electronics';
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

-- Calls via PERFORM (creates new executor level)
CREATE OR REPLACE FUNCTION caller_perform() RETURNS void AS $$
BEGIN
    PERFORM shared_inner();  -- new executor → inner at level 2
END;
$$ LANGUAGE plpgsql;

-- Calls via expression assignment (same executor level)
CREATE OR REPLACE FUNCTION caller_assign() RETURNS INTEGER AS $$
DECLARE result INTEGER;
BEGIN
    result := shared_inner();  -- expression eval → inner at level 1
    RETURN result;
END;
$$ LANGUAGE plpgsql;

\echo '--- Via PERFORM (expect level 2 for inner SELECT): ---'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT caller_perform();

\echo ''
\echo '--- Via expression assignment (expect level 1 for inner SELECT): ---'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT caller_assign();

\echo ''
\echo 'Notice: Same function, different nesting levels depending on call pattern.'
\echo ''

-- ============================================================================
-- TEST 6: SQL Function Nesting (True SQL Nesting)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 6: SQL Functions - True SQL Execution Nesting'
\echo '============================================================================'
\echo ''
\echo 'Purpose: SQL functions execute DURING the parent query, creating true'
\echo '         SQL nesting where inner functions complete before outer.'
\echo ''

DROP TABLE IF EXISTS t1, t2, t3 CASCADE;
CREATE TABLE t1 (id INT, data TEXT);
CREATE TABLE t2 (id INT, data TEXT);
CREATE TABLE t3 (id INT, data TEXT);
INSERT INTO t1 VALUES (1, 'A');
INSERT INTO t2 VALUES (1, 'B');
INSERT INTO t3 VALUES (1, 'C');

CREATE OR REPLACE FUNCTION sql_level3() RETURNS TEXT AS $$
    SELECT data FROM t3 WHERE id = 1;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION sql_level2() RETURNS TEXT AS $$
    SELECT data || '+' || sql_level3() FROM t2 WHERE id = 1;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION plpgsql_sql_caller() RETURNS TEXT AS $$
DECLARE result TEXT;
BEGIN
    SELECT data || '+' || sql_level2() INTO result FROM t1 WHERE id = 1;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

\echo 'Expected: Deepest-first execution order (3→2→1)'
\echo '  Statement #1 (level 3): SELECT from t3 (sql_level3)'
\echo '  Statement #2 (level 2): SELECT from t2 (sql_level2)'
\echo '  Statement #3 (level 1): SELECT from t1 (plpgsql_sql_caller)'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT plpgsql_sql_caller();

\echo ''

-- ============================================================================
-- TEST 7: Three-Level Chain with PERFORM
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 7: Three-Level PL/pgSQL Chain (via PERFORM)'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Each PERFORM adds one executor level'
\echo ''

CREATE OR REPLACE FUNCTION chain_level3() RETURNS void AS $$
BEGIN
    PERFORM COUNT(*) FROM products WHERE category = 'Books';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION chain_level2() RETURNS void AS $$
BEGIN
    PERFORM COUNT(*) FROM products WHERE id = 1;
    PERFORM chain_level3();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION chain_level1() RETURNS void AS $$
BEGIN
    PERFORM COUNT(*) FROM products;
    PERFORM chain_level2();
END;
$$ LANGUAGE plpgsql;

\echo 'Expected:'
\echo '  Statement #1 (level 1): SELECT COUNT(*) FROM products'
\echo '  Statement #2 (level 2): SELECT COUNT(*) WHERE id = 1'
\echo '  Statement #3 (level 3): SELECT COUNT(*) WHERE category = Books'
\echo '  Statement #4 (level 2): SELECT chain_level3()'
\echo '  Statement #5 (level 1): SELECT chain_level2()'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT chain_level1();

\echo ''

-- ============================================================================
-- TEST 8: Recursive Function
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 8: Recursive Function - Increasing Nesting Levels'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Recursive PERFORM calls increase the nesting level each time'
\echo ''

DROP TABLE IF EXISTS counter_log;
CREATE TABLE counter_log (depth INT, val INT);

CREATE OR REPLACE FUNCTION recursive_func(n INT) RETURNS VOID AS $$
BEGIN
    INSERT INTO counter_log VALUES (n, n * 10);
    IF n > 1 THEN
        PERFORM recursive_func(n - 1);
    END IF;
END;
$$ LANGUAGE plpgsql;

\echo 'Expected:'
\echo '  Statement #1 (level 1): INSERT with n=3'
\echo '  Statement #2 (level 2): INSERT with n=2'
\echo '  Statement #3 (level 3): INSERT with n=1'
\echo '  + PERFORM calls at levels 2 and 1'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT recursive_func(3);

\echo ''

-- ============================================================================
-- TEST 9: Exception Handling
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 9: Exception Handling - Statements in BEGIN/EXCEPTION Blocks'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Verify statements in exception handlers are captured'
\echo ''

DROP TABLE IF EXISTS safe_table;
CREATE TABLE safe_table (id INT PRIMARY KEY, data TEXT);
INSERT INTO safe_table VALUES (1, 'existing');

CREATE OR REPLACE FUNCTION exception_func() RETURNS TEXT AS $$
BEGIN
    UPDATE safe_table SET data = 'updated' WHERE id = 1;
    BEGIN
        INSERT INTO safe_table VALUES (1, 'duplicate');  -- will fail
    EXCEPTION WHEN unique_violation THEN
        UPDATE safe_table SET data = 'recovered' WHERE id = 1;
    END;
    RETURN 'done';
END;
$$ LANGUAGE plpgsql;

\echo 'Expected:'
\echo '  - UPDATE (level 1): initial update'
\echo '  - UPDATE (level 1): recovery in exception handler'
\echo '  - The failed INSERT is rolled back and may not appear'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT exception_func();

\echo ''

-- ============================================================================
-- TEST 10: No Nested Statements
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 10: No Nested Statements - Plain Query'
\echo '============================================================================'
\echo ''
\echo 'Purpose: When no nested statements execute, no "Nested Plans:" section'
\echo ''

\echo 'Expected: Normal EXPLAIN output, no Nested Plans section'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM products WHERE id = 1;

\echo ''

-- ============================================================================
-- TEST 11: Trigger-Fired Nested Statements
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 11: Trigger-Fired Nested Statements'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Triggers fire DURING the triggering statement, creating'
\echo '         deeper nesting. Note: trigger statements may appear BEFORE'
\echo '         their parent (completion order, not start order).'
\echo ''

DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS audit_log CASCADE;
CREATE TABLE orders (id INT PRIMARY KEY, amount NUMERIC, status TEXT);
CREATE TABLE audit_log (order_id INT, action TEXT, logged_at TIMESTAMP DEFAULT now());

CREATE OR REPLACE FUNCTION log_order_change() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (order_id, action)
    VALUES (NEW.id, TG_OP || ': ' || NEW.status);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_audit_trigger
    AFTER INSERT OR UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION log_order_change();

CREATE OR REPLACE FUNCTION process_order(p_id INT) RETURNS VOID AS $$
BEGIN
    INSERT INTO orders VALUES (p_id, 99.99, 'new');
    UPDATE orders SET status = 'processed' WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

\echo 'Expected:'
\echo '  - INSERT INTO audit_log (level 2): trigger from INSERT'
\echo '  - INSERT INTO orders (level 1): the triggering statement'
\echo '  - INSERT INTO audit_log (level 2): trigger from UPDATE'
\echo '  - UPDATE orders (level 1): the triggering statement'
\echo ''
\echo 'Note: Level 2 statements appear before level 1 because triggers'
\echo '      complete (ExecutorEnd) before the parent statement completes.'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT process_order(1);

\echo ''

-- ============================================================================
-- TEST 12: Combined with VERBOSE and BUFFERS
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 12: NESTED_STATEMENTS with VERBOSE and BUFFERS'
\echo '============================================================================'
\echo ''
\echo 'Purpose: VERBOSE and BUFFERS options are inherited by nested plans'
\echo ''

CREATE OR REPLACE FUNCTION verbose_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM products;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

\echo 'Expected: Schema-qualified names (public.products) and Output columns'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, VERBOSE, BUFFERS, TIMING OFF, SUMMARY OFF)
SELECT verbose_func();

\echo ''

-- ============================================================================
-- TEST 13: Statement Numbering = Completion Order
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 13: Statement Numbering Shows Completion Order'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Statement #N reflects when a statement FINISHES (ExecutorEnd),'
\echo '         not when it starts. This is most visible with triggers:'
\echo ''
\echo '  Timeline for INSERT with an AFTER trigger:'
\echo '    1. Parent INSERT starts executing'
\echo '    2. Trigger fires → trigger INSERT starts → trigger INSERT FINISHES → gets #1'
\echo '    3. Parent INSERT FINISHES → gets #2'
\echo ''
\echo '  The trigger statement finishes INSIDE the parent, so it gets a lower number.'
\echo ''

DROP TABLE IF EXISTS demo_orders CASCADE;
DROP TABLE IF EXISTS demo_log CASCADE;
CREATE TABLE demo_orders (id INT, item TEXT);
CREATE TABLE demo_log (msg TEXT);

CREATE OR REPLACE FUNCTION demo_log_trigger() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO demo_log VALUES ('order placed: ' || NEW.item);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER demo_after_insert
    AFTER INSERT ON demo_orders
    FOR EACH ROW EXECUTE FUNCTION demo_log_trigger();

CREATE OR REPLACE FUNCTION place_order() RETURNS void AS $$
BEGIN
    INSERT INTO demo_orders VALUES (1, 'Widget');   -- has trigger
    INSERT INTO demo_orders VALUES (2, 'Gadget');   -- has trigger
END;
$$ LANGUAGE plpgsql;

\echo 'Expected numbering (completion order):'
\echo '  #1 (level 2): INSERT INTO demo_log  ← trigger finishes first'
\echo '  #2 (level 1): INSERT INTO demo_orders (Widget) ← parent finishes second'
\echo '  #3 (level 2): INSERT INTO demo_log  ← second trigger finishes'
\echo '  #4 (level 1): INSERT INTO demo_orders (Gadget) ← second parent finishes'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT place_order();

\echo ''
\echo 'Key takeaway: Lower statement number = finished earlier.'
\echo 'Triggers finish inside their parent, so they always get lower numbers.'
\echo ''

-- ============================================================================
-- TEST 14: BEGIN/ROLLBACK Safety Pattern
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 14: BEGIN/ROLLBACK - Safe Analysis of Data-Modifying Functions'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Demonstrate the recommended pattern for safely analyzing'
\echo '         functions that modify data without persisting changes.'
\echo ''
\echo 'Pattern:'
\echo '  BEGIN;'
\echo '  EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT my_function();'
\echo '  ROLLBACK;'
\echo ''
\echo 'The function executes (so we get real plans with actual rows),'
\echo 'but ROLLBACK undoes all changes.'
\echo ''

DROP TABLE IF EXISTS safe_orders CASCADE;
CREATE TABLE safe_orders (id INT, item TEXT, status TEXT);
INSERT INTO safe_orders VALUES (1, 'Widget', 'pending'), (2, 'Gadget', 'pending');

CREATE OR REPLACE FUNCTION process_all_orders() RETURNS INT AS $$
DECLARE processed INT := 0;
BEGIN
    UPDATE safe_orders SET status = 'processing' WHERE status = 'pending';
    INSERT INTO safe_orders VALUES (3, 'Bonus', 'new');
    DELETE FROM safe_orders WHERE item = 'Gadget';
    GET DIAGNOSTICS processed = ROW_COUNT;
    RETURN processed;
END;
$$ LANGUAGE plpgsql;

\echo '--- Before: ---'
SELECT * FROM safe_orders;

\echo ''
\echo '--- EXPLAIN inside BEGIN/ROLLBACK: ---'

BEGIN;
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT process_all_orders();
ROLLBACK;

\echo ''
\echo '--- After ROLLBACK (data unchanged): ---'
SELECT * FROM safe_orders;

\echo ''
\echo 'Result: We got full execution plans with actual row counts,'
\echo '        but the data is unchanged after ROLLBACK.'
\echo ''

-- ============================================================================
-- TEST 15: Error During EXPLAIN Does Not Crash (Bug 1 Fix)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 15: Error During EXPLAIN Does Not Crash Server'
\echo '============================================================================'
\echo ''
\echo 'Purpose: If EXPLAIN errors (e.g., division by zero), hooks must be'
\echo '         cleaned up so subsequent queries do not crash the backend.'
\echo ''

CREATE OR REPLACE FUNCTION divz_plpgsql(x int) RETURNS int LANGUAGE plpgsql AS $$
DECLARE r int;
BEGIN
  SELECT 1/x INTO r;
  RETURN r;
END;
$$;

-- This errors - hooks must be cleaned up via PG_FINALLY
EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT divz_plpgsql(0);

\echo 'After error - next query (should not crash):'
SELECT 1 AS post_error_test;
\echo 'After error - EXPLAIN NESTED_STATEMENTS again:'
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT 1;
\echo 'TEST 15: Server survived the error — hooks cleaned up correctly'
\echo ''

-- ============================================================================
-- TEST 16: Nested EXPLAIN Does Not Crash (Bug 2 Fix)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 16: Nested EXPLAIN Does Not Crash Server'
\echo '============================================================================'
\echo ''
\echo 'Purpose: A function that internally runs EXPLAIN (NESTED_STATEMENTS)'
\echo '         should not corrupt the outer EXPLAIN state (reentrancy guard).'
\echo ''

CREATE OR REPLACE FUNCTION f_nested_explain() RETURNS void LANGUAGE plpgsql AS $$
DECLARE r record;
BEGIN
  FOR r IN EXECUTE 'EXPLAIN (ANALYZE, NESTED_STATEMENTS) SELECT 1'
  LOOP NULL; END LOOP;
END;
$$;

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT f_nested_explain();
\echo 'TEST 16: Server survived nested EXPLAIN — reentrancy guard works'
\echo ''

-- ============================================================================
-- TEST 17: Memory Context Cleanup (Bug 3 Fix)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 17: Memory Context Properly Freed After EXPLAIN'
\echo '============================================================================'
\echo ''
\echo 'Purpose: The dedicated memory context for nested plans should not'
\echo '         persist after EXPLAIN completes (no memory leak).'
\echo ''

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT 1;
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT 1;
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT 1;

\echo 'Memory contexts named "Nested EXPLAIN plans" (should be 0 rows):'
SELECT name FROM pg_backend_memory_contexts WHERE name = 'Nested EXPLAIN plans';
\echo 'TEST 17: 0 rows above = memory context freed after EXPLAIN'
\echo ''

-- ============================================================================
-- TEST 18: Memory Context Does Not Grow Across Repeated Calls
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 18: Memory Context Does Not Grow Across Repeated Calls'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Run EXPLAIN NESTED_STATEMENTS 20 times and verify no memory'
\echo '         context persists or accumulates between calls.'
\echo ''

CREATE TABLE IF NOT EXISTS mem_test (id INT, val TEXT);
INSERT INTO mem_test VALUES (1, 'a'), (2, 'b');
CREATE OR REPLACE FUNCTION mem_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM mem_test;
    UPDATE mem_test SET val = val || 'x' WHERE id = 1;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

-- Run 20 times
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT mem_func();

\echo 'After 20 runs - "Nested EXPLAIN plans" contexts (should be 0 rows):'
SELECT name FROM pg_backend_memory_contexts WHERE name = 'Nested EXPLAIN plans';
\echo 'TEST 18: 0 rows above = no memory accumulation after 20 runs'
\echo ''

-- ============================================================================
-- TEST 19: Stress Test - 50 Nested Statements
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 19: Stress Test - Function With 50 Nested Statements'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Verify the feature handles many nested statements without'
\echo '         crashing or corrupting memory.'
\echo ''

DROP TABLE IF EXISTS stress_table;
CREATE TABLE stress_table (id INT, val TEXT);
INSERT INTO stress_table SELECT g, 'row_' || g FROM generate_series(1, 100) g;

CREATE OR REPLACE FUNCTION stress_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 1;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 2;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 3;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 4;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 5;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 6;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 7;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 8;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 9;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 10;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 11;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 12;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 13;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 14;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 15;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 16;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 17;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 18;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 19;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 20;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 21;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 22;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 23;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 24;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 25;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 26;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 27;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 28;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 29;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 30;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 31;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 32;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 33;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 34;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 35;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 36;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 37;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 38;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 39;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 40;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 41;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 42;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 43;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 44;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 45;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 46;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 47;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 48;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 49;
    SELECT COUNT(*) INTO cnt FROM stress_table WHERE id = 50;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT stress_func();

\echo ''
\echo 'Server healthy after stress:'
SELECT 1 AS server_healthy;
\echo 'No memory leak after stress:'
SELECT name FROM pg_backend_memory_contexts WHERE name = 'Nested EXPLAIN plans';
\echo 'TEST 19: 50 nested statements captured, server healthy, no leak'
\echo ''

-- ============================================================================
-- TEST 20: Execution Time Per Nested Statement (SUMMARY default)
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 20: Execution Time Per Nested Statement'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Each nested statement shows its Execution Time when SUMMARY'
\echo '         is enabled (default with ANALYZE). Uses query_instr->total.'
\echo ''

DROP TABLE IF EXISTS et_test;
CREATE TABLE et_test (id INT, val TEXT);
INSERT INTO et_test VALUES (1, 'hello'), (2, 'world');

CREATE OR REPLACE FUNCTION et_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM et_test;
    UPDATE et_test SET val = 'updated' WHERE id = 1;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

\echo 'Expected: Each nested statement shows "Execution Time: X.XXX ms"'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF) SELECT et_func();

\echo ''

-- ============================================================================
-- TEST 21: Execution Time Hidden with SUMMARY OFF
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 21: Execution Time Hidden with SUMMARY OFF'
\echo '============================================================================'
\echo ''
\echo 'Purpose: When SUMMARY OFF is specified, nested statements should NOT'
\echo '         show Execution Time (consistent with main query behavior).'
\echo ''

\echo 'Expected: No "Execution Time" lines in nested plans'
\echo ''
EXPLAIN (ANALYZE, NESTED_STATEMENTS, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT et_func();

DROP FUNCTION et_func();
DROP TABLE et_test;
\echo ''

-- ============================================================================
-- TEST 22: Structured Output - JSON Format
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 22: Structured Output - JSON Format'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Nested plans are output as proper structured JSON with'
\echo '         Node Type, Plans array, costs, timing, and execution time.'
\echo ''

DROP TABLE IF EXISTS json_t;
CREATE TABLE json_t (id INT, val TEXT);
INSERT INTO json_t VALUES (1, 'test'), (2, 'data');
CREATE OR REPLACE FUNCTION json_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM json_t;
    UPDATE json_t SET val = 'updated' WHERE id = 1;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

EXPLAIN (ANALYZE, NESTED_STATEMENTS, FORMAT JSON) SELECT json_func();

DROP FUNCTION json_func();
DROP TABLE json_t;
\echo ''

-- ============================================================================
-- TEST 23: Structured Output - XML Format
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 23: Structured Output - XML Format'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Nested plans are output as proper XML with Node-Type,'
\echo '         Plans elements, costs, timing, and execution time.'
\echo ''

DROP TABLE IF EXISTS xml_t;
CREATE TABLE xml_t (id INT, val TEXT);
INSERT INTO xml_t VALUES (1, 'test'), (2, 'data');
CREATE OR REPLACE FUNCTION xml_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM xml_t;
    UPDATE xml_t SET val = 'updated' WHERE id = 1;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

EXPLAIN (ANALYZE, NESTED_STATEMENTS, FORMAT XML) SELECT xml_func();

DROP FUNCTION xml_func();
DROP TABLE xml_t;
\echo ''

-- ============================================================================
-- TEST 24: Structured Output - YAML Format
-- ============================================================================
\echo '============================================================================'
\echo 'TEST 24: Structured Output - YAML Format'
\echo '============================================================================'
\echo ''
\echo 'Purpose: Nested plans are output as proper YAML with Node Type,'
\echo '         Plans list, costs, timing, and execution time.'
\echo ''

DROP TABLE IF EXISTS yaml_t;
CREATE TABLE yaml_t (id INT, val TEXT);
INSERT INTO yaml_t VALUES (1, 'test'), (2, 'data');
CREATE OR REPLACE FUNCTION yaml_func() RETURNS INT AS $$
DECLARE cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM yaml_t;
    UPDATE yaml_t SET val = 'updated' WHERE id = 1;
    RETURN cnt;
END;
$$ LANGUAGE plpgsql;

EXPLAIN (ANALYZE, NESTED_STATEMENTS, FORMAT YAML) SELECT yaml_func();

DROP FUNCTION yaml_func();
DROP TABLE yaml_t;
\echo ''

-- ============================================================================
-- Cleanup
-- ============================================================================
DROP FUNCTION IF EXISTS simple_function();
DROP FUNCTION IF EXISTS inner_perform();
DROP FUNCTION IF EXISTS outer_perform();
DROP FUNCTION IF EXISTS inner_expr();
DROP FUNCTION IF EXISTS outer_expr();
DROP FUNCTION IF EXISTS shared_inner();
DROP FUNCTION IF EXISTS caller_perform();
DROP FUNCTION IF EXISTS caller_assign();
DROP FUNCTION IF EXISTS sql_level3();
DROP FUNCTION IF EXISTS sql_level2();
DROP FUNCTION IF EXISTS plpgsql_sql_caller();
DROP FUNCTION IF EXISTS chain_level1();
DROP FUNCTION IF EXISTS chain_level2();
DROP FUNCTION IF EXISTS chain_level3();
DROP FUNCTION IF EXISTS recursive_func(INT);
DROP FUNCTION IF EXISTS exception_func();
DROP FUNCTION IF EXISTS process_order(INT);
DROP TRIGGER IF EXISTS order_audit_trigger ON orders;
DROP FUNCTION IF EXISTS log_order_change();
DROP FUNCTION IF EXISTS verbose_func();
DROP FUNCTION IF EXISTS place_order();
DROP TRIGGER IF EXISTS demo_after_insert ON demo_orders;
DROP FUNCTION IF EXISTS demo_log_trigger();
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS t1, t2, t3 CASCADE;
DROP TABLE IF EXISTS counter_log;
DROP TABLE IF EXISTS safe_table;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS audit_log;
DROP TABLE IF EXISTS demo_orders CASCADE;
DROP TABLE IF EXISTS demo_log;
DROP TABLE IF EXISTS safe_orders CASCADE;
DROP FUNCTION IF EXISTS process_all_orders();
DROP FUNCTION IF EXISTS divz_plpgsql(int);
DROP FUNCTION IF EXISTS f_nested_explain();
DROP FUNCTION IF EXISTS mem_func();
DROP FUNCTION IF EXISTS stress_func();
DROP TABLE IF EXISTS mem_test;
DROP TABLE IF EXISTS stress_table;

\echo '============================================================================'
\echo 'Test Suite Complete'
\echo '============================================================================'
\echo ''
\echo 'KEY FINDING: Nesting levels reflect executor call stack depth.'
\echo ''
\echo '  PERFORM func() / SELECT func() INTO var:'
\echo '    → Creates new ExecutorRun → deeper nesting level'
\echo ''
\echo '  result := func():'
\echo '    → ExecEvalFunc (no new ExecutorRun) → SAME nesting level'
\echo ''
\echo 'Tests Covered:'
\echo '  1.  Validation (requires ANALYZE)'
\echo '  2.  Simple PL/pgSQL (all level 1)'
\echo '  3.  PERFORM pattern (creates deeper levels)'
\echo '  4.  Expression assignment (stays at same level)'
\echo '  5.  Side-by-side comparison of both patterns'
\echo '  6.  SQL function nesting (true SQL nesting)'
\echo '  7.  Three-level chain with PERFORM'
\echo '  8.  Recursive function (increasing levels)'
\echo '  9.  Exception handling blocks'
\echo '  10. No nested statements (plain query)'
\echo '  11. Trigger-fired nested statements'
\echo '  12. Combined with VERBOSE and BUFFERS'
\echo '  13. Statement numbering = completion order (triggers demo)'
\echo '  14. BEGIN/ROLLBACK safety pattern'
\echo '  15. Error during EXPLAIN does not crash (Bug 1 fix)'
\echo '  16. Nested EXPLAIN does not crash (Bug 2 fix)'
\echo '  17. Memory context cleanup (Bug 3 fix)'
\echo '  18. Memory context does not grow across repeated calls'
\echo '  19. Stress test - 50 nested statements'
\echo '  20. Execution Time per nested statement (SUMMARY default)'
\echo '  21. Execution Time hidden with SUMMARY OFF'
\echo '  22. Structured output - JSON format'
\echo '  23. Structured output - XML format'
\echo '  24. Structured output - YAML format'
\echo '============================================================================'