INSTALL lpts FROM community;
LOAD lpts;

-- Set both input and output dialects to DuckDB for a first round trip.
D SET lpts_input_dialect = 'duckdb';
D SET lpts_dialect = 'duckdb';

D CREATE TABLE users (id INTEGER, name VARCHAR, age INTEGER);
D INSERT INTO users VALUES (1, 'Alice', 30), (2, 'Bob', 22), (3, 'Carol', 28);

-- Return the optimized plan as readable CTE SQL.
D PRAGMA lpts('SELECT name FROM users WHERE age > 25');
WITH
t0_scan (t0_name) AS (
    SELECT  "name"
    FROM    memory.main.users
    WHERE   (age>25)
)
SELECT  t0_name AS "name"
FROM    t0_scan;

-- The same output, exposed as a first-class EXPLAIN statement.
D EXPLAIN (FORMAT SQL) SELECT name FROM users WHERE age > 25;
┌─────────────────────────────┐
│┌───────────────────────────┐│
││  Optimized Logical Plan   ││
│└───────────────────────────┘│
└─────────────────────────────┘
WITH
t0_scan (t0_name) AS (
    SELECT  "name"
    FROM    memory.main.users
    WHERE   (age>25)
)
SELECT  t0_name AS "name"
FROM    t0_scan;

-- Turn on transparent round-trip checking. Every top-level SELECT is now
-- rewritten by LPTS and its result compared against the original; a wrong
-- rewrite raises an error. The query otherwise returns its normal rows.
D SET lpts_check = true;
D SELECT name FROM users WHERE age > 25;
┌─────────┐
│  name   │
│ varchar │
├─────────┤
│ Alice   │
│ Carol   │
└─────────┘

PRAGMA lpts('<query>');