-- Create a schemaless collection
CREATE COLLECTION users;

-- Insert some data (standard SQL)
INSERT INTO users (id, name, email, age) VALUES ('u1', 'Alice', '[email protected]', 30);
INSERT INTO users (id, name, email, role) VALUES ('u2', 'Bob', '[email protected]', 'admin');

-- Object literal syntax also works
INSERT INTO users { name: 'Alice', email: '[email protected]', age: 30 };
INSERT INTO users { name: 'Bob', email: '[email protected]', role: 'admin' };

-- Query
SELECT * FROM users WHERE name = 'Alice';
SELECT name, email FROM users WHERE age > 25;

-- Create a strict collection with a defined schema
CREATE COLLECTION orders TYPE DOCUMENT STRICT (
    id TEXT PRIMARY KEY,
    customer_id TEXT,
    total FLOAT,
    status TEXT,
    created_at TIMESTAMP
);

INSERT INTO orders (id, customer_id, total, status)
VALUES ('o1', 'u1', 99.99, 'pending');

SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

-- Create a collection with a vector index
CREATE COLLECTION articles;
CREATE VECTOR INDEX idx_articles_embedding ON articles METRIC cosine DIM 384;

-- Insert documents with embeddings (standard SQL)
INSERT INTO articles (id, title, embedding) VALUES ('a1', 'Intro to AI', ARRAY[0.1, 0.2, 0.3]);
-- Or:
INSERT INTO articles { id: 'a1', title: 'Intro to AI', embedding: [0.1, 0.2, 0.3] };

-- Search by similarity
SEARCH articles USING VECTOR(embedding, ARRAY[0.1, 0.3, ...], 10);

-- Graph is an overlay on document collections, not a separate collection type
CREATE COLLECTION social;

-- Insert nodes
INSERT INTO social (id, name) VALUES ('alice', 'Alice');
INSERT INTO social (id, name) VALUES ('bob', 'Bob');

-- Add edges
-- JSON string form:
GRAPH INSERT EDGE FROM 'alice' TO 'bob' TYPE 'knows' PROPERTIES '{"since": 2020}';
-- Object literal form (equivalent):
GRAPH INSERT EDGE FROM 'alice' TO 'bob' TYPE 'knows' PROPERTIES { since: 2020 };

-- Traverse
GRAPH TRAVERSE FROM 'alice' DEPTH 2;

-- Run an algorithm
GRAPH ALGO PAGERANK ON social DAMPING 0.85 ITERATIONS 20 TOLERANCE 1e-7;

-- Create a KV collection
CREATE COLLECTION sessions TYPE KEY_VALUE (key TEXT PRIMARY KEY);

-- Set a key-value pair (standard SQL)
INSERT INTO sessions (key, value) VALUES ('sess_abc', 'token-abc');
-- Or:
INSERT INTO sessions { key: 'sess_abc', value: 'token-abc' };

-- Get by key
SELECT * FROM sessions WHERE key = 'sess_abc';

-- Plain columnar: general analytics
CREATE COLLECTION web_events TYPE COLUMNAR (
    ts TIMESTAMP,
    user_id UUID,
    page VARCHAR,
    duration_ms INT
);

SELECT page, AVG(duration_ms), COUNT(*)
FROM web_events
WHERE ts > now() - INTERVAL '7 days'
GROUP BY page
ORDER BY COUNT(*) DESC;

-- Timeseries profile: TIME_KEY column drives partition-by-time and block skip
CREATE COLLECTION cpu_metrics TYPE COLUMNAR (
    ts TIMESTAMP TIME_KEY,
    host VARCHAR,
    cpu FLOAT
) WITH profile = 'timeseries', partition_by = '1h';

-- CREATE TIMESERIES is a convenience alias for the timeseries profile
-- CREATE TIMESERIES cpu_metrics;

SELECT time_bucket('5 minutes', ts) AS bucket, host, AVG(cpu)
FROM cpu_metrics
WHERE ts > now() - INTERVAL '1 hour'
GROUP BY bucket, host;

-- Spatial profile: SPATIAL_INDEX column gets an automatic R*-tree
CREATE COLLECTION locations TYPE COLUMNAR (
    geom GEOMETRY SPATIAL_INDEX,
    name VARCHAR
);

SELECT name, ST_Distance(geom, ST_Point(-73.98, 40.75)) AS dist
FROM locations
WHERE ST_DWithin(geom, ST_Point(-73.98, 40.75), 1000)
ORDER BY dist;

-- Fire asynchronously after each insert (default, zero write-latency impact)
CREATE TRIGGER notify_on_order AFTER INSERT ON orders FOR EACH ROW $$
BEGIN
    INSERT INTO notifications (id, user_id, message)
    VALUES (NEW.id || '_notif', NEW.customer_id, 'Order received');
END;
$$;

-- Fire synchronously in the same transaction (ACID, adds trigger latency to writes)
CREATE TRIGGER enforce_balance AFTER UPDATE ON accounts FOR EACH ROW
WITH (EXECUTION = SYNC) $$
BEGIN
    IF NEW.balance < 0 THEN
        RAISE EXCEPTION 'Balance cannot go negative';
    END IF;
END;
$$;

-- SQL expression function (inlined into query plans by the optimizer)
CREATE FUNCTION full_name(first VARCHAR, last VARCHAR) RETURNS VARCHAR
LANGUAGE SQL IMMUTABLE AS $$ first || ' ' || last $$;

-- Use in queries
SELECT full_name(first_name, last_name) AS name FROM users;

-- Procedural function with BEGIN...END body
CREATE FUNCTION tier_label(amount DECIMAL) RETURNS VARCHAR
LANGUAGE SQL STABLE
BEGIN
    IF amount > 1000 THEN
        RETURN 'premium';
    ELSIF amount > 100 THEN
        RETURN 'standard';
    ELSE
        RETURN 'basic';
    END IF;
END;

-- Create a change stream with webhook delivery
CREATE CHANGE STREAM order_changes
ON orders
WITH (
    WEBHOOK_URL = 'https://hooks.example.com/orders',
    WEBHOOK_SECRET = 'whsec_abc123'
);

-- Create a consumer group to track read offsets
CREATE CONSUMER GROUP processors ON order_changes;

-- Commit offsets after processing (batch: all partitions at latest)
COMMIT OFFSETS ON order_changes CONSUMER GROUP processors;

-- Show all streams
SHOW CHANGE STREAMS;

-- Backup all tenant data across all engines (encrypted with AES-256-GCM).
-- Bytes stream over the pgwire COPY framing; the client redirects to disk.
COPY (BACKUP TENANT acme) TO STDOUT;

-- Validate a backup without restoring
COPY tenant_restore(acme) FROM STDIN DRY RUN;

-- Restore
COPY tenant_restore(acme) FROM STDIN;

-- Permanently delete all tenant data (GDPR erasure) — requires CONFIRM
PURGE TENANT acme CONFIRM;

-- Inspect resource usage and limits
SHOW TENANT USAGE FOR acme;
SHOW TENANT QUOTA FOR acme;