.load ./sexp

-- Parse and store
CREATE TABLE config (id INTEGER PRIMARY KEY, data BLOB);
INSERT INTO config (data) VALUES (sexp('(server (host "localhost") (port 8080))'));

-- Query with native type returns (like json_extract)
SELECT sexp_get(data, 'host') FROM config;
-- localhost

SELECT sexp_get(data, 'port') + 1000 FROM config;
-- 9080  (integer arithmetic works directly!)

-- Nested access (like json_extract with $.user.name)
INSERT INTO config (data) VALUES (sexp('(app (db (host "db.local") (port 5432))))'));
SELECT sexp_get(data, 'db', 'host') FROM config WHERE id = 2;
-- db.local

-- Access by index
SELECT sexp_nth(sexp('(10 20 30)'), 1);
-- 20  (returns INTEGER, not blob)

-- From current directory
.load ./sexp

-- From install location
.load /usr/local/lib/sqlite3/sexp

-- In application code
SELECT load_extension('/path/to/sexp');

-- Create a table with sexp data
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    timestamp TEXT DEFAULT CURRENT_TIMESTAMP,
    data BLOB
);

-- Insert s-expression data
INSERT INTO events (data) VALUES
    (sexp('(login (user "alice") (ip "192.168.1.1"))')),
    (sexp('(purchase (user "bob") (item "widget") (price 29.99))')),
    (sexp('(logout (user "alice"))'));

-- Direct value extraction - no conversion functions needed!
SELECT sexp_car(sexp('(click user123)'));      -- 'click' (TEXT)
SELECT sexp_nth(sexp('(10 20 30)'), 1);        -- 20 (INTEGER)
SELECT sexp_path(sexp('((a 1.5))'), 0, 1);     -- 1.5 (REAL)

-- Works in expressions
SELECT sexp_nth(sexp('(10 20 30)'), 0) + sexp_nth(sexp('(10 20 30)'), 1);  -- 30

-- String comparison works directly
SELECT * FROM events WHERE sexp_car(data) = 'login';

-- Structure: (tag (key1 value1) (key2 value2) ...)
SELECT sexp_get(sexp('(user (name "alice") (age 30))'), 'name');  -- 'alice'
SELECT sexp_get(sexp('(user (name "alice") (age 30))'), 'age');   -- 30

-- Use in queries
SELECT * FROM events WHERE sexp_get(data, 'user') = 'alice';

-- Arithmetic on extracted values
SELECT * FROM events WHERE sexp_get(data, 'price') > 20.00;

-- NESTED ACCESS: Multiple keys navigate through nested structures
-- Structure: (event (user (name "alice") (type "premium")) (metadata ...))
SELECT sexp_get(data, 'user', 'name');   -- 'alice' (no re-parsing needed!)
SELECT sexp_get(data, 'user', 'type');   -- 'premium'

-- Equivalent to JSONB: json_extract(data, '$.user.name')
-- Three levels deep:
SELECT sexp_get(data, 'metadata', 'product', 'version');

-- Get event type (first element) - returns TEXT directly
SELECT id, sexp_car(data) as event_type FROM events;
-- 1|login
-- 2|purchase
-- 3|logout

-- Filter by event type - direct comparison
SELECT * FROM events WHERE sexp_car(data) = 'login';

-- Extract nested values using sexp_get
SELECT sexp_get(data, 'user') as user FROM events;
-- alice
-- bob
-- alice

-- Or using path-based access
SELECT sexp_path(data, 1, 1) as user FROM events;

-- Find events containing specific data
SELECT * FROM events WHERE sexp_contains(data, sexp('(user "alice")'));

-- Key-based containment (order-independent matching)
SELECT * FROM events 
WHERE sexp_contains_key(data, sexp('(purchase (price 29.99))'));

-- Access list elements (scalars return native types)
SELECT sexp_car(sexp('(a b c)'));              -- 'a' (TEXT)
SELECT sexp_text(sexp_cdr(sexp('(a b c)')));   -- '(b c)' (cdr always returns list)
SELECT sexp_nth(sexp('(a b c)'), 1);           -- 'b' (TEXT)
SELECT sexp_length(sexp('(a b c)'));           -- 3

-- When the element is a list, you get a blob back
SELECT typeof(sexp_nth(sexp('((nested) b c)'), 0));  -- 'blob'
SELECT sexp_text(sexp_nth(sexp('((nested) b c)'), 0));  -- '(nested)'

-- Deep access with sexp_path
SELECT sexp_path(sexp('((a 1) (b 2) (c 3))'), 1, 0);   -- 'b'
SELECT sexp_path(sexp('((a 1) (b 2) (c 3))'), 2, 1);   -- 3

-- These work on sexp blobs (e.g., from stored data or sexp() function)
SELECT sexp_int_value(sexp('42'));           -- 42 (INTEGER)
SELECT sexp_float_value(sexp('3.14'));       -- 3.14 (REAL)
SELECT sexp_string_value(sexp('"hello"'));   -- hello (TEXT)
SELECT sexp_symbol_value(sexp('foo'));       -- foo (TEXT)
SELECT sexp_number_value(sexp('42'));        -- 42 (auto: INTEGER or REAL)

-- Type predicates return 1 (true) or 0 (false)
-- O(1) performance - no full parsing needed
SELECT is_list(sexp('(a b)'));     -- 1
SELECT is_symbol(sexp('foo'));     -- 1
SELECT is_string(sexp('"foo"'));   -- 1
SELECT is_integer(sexp('42'));     -- 1
SELECT is_float(sexp('3.14'));     -- 1
SELECT is_number(sexp('42'));      -- 1
SELECT is_nil(sexp('()'));         -- 1
SELECT is_atom(sexp('foo'));       -- 1

-- Get type name
SELECT sexp_typeof(sexp('42'));      -- integer
SELECT sexp_typeof(sexp('(a b)'));   -- list