-- Install and load
INSTALL keboola FROM community;
LOAD keboola;

-- Attach your Keboola project
ATTACH 'https://connection.keboola.com' AS kbc (
    TYPE keboola,
    TOKEN 'my-storage-api-token'
);

-- Query any Keboola table
SELECT * FROM kbc."in.c-crm".contacts WHERE status = 'active' LIMIT 10;

-- Write results back
INSERT INTO kbc."out.c-analytics".summary
SELECT region, SUM(revenue) AS total
FROM kbc."in.c-sales".orders
GROUP BY region;

ATTACH 'https://connection.keboola.com' AS kbc (
    TYPE keboola,
    TOKEN 'my-storage-api-token'
);

CREATE SECRET my_kbc (
    TYPE keboola,
    TOKEN 'my-storage-api-token',
    URL   'https://connection.keboola.com'
);

ATTACH '' AS kbc (TYPE keboola, SECRET my_kbc);

ATTACH 'https://connection.keboola.com' AS kbc (
    TYPE    keboola,
    TOKEN   'my-storage-api-token',
    SNAPSHOT true
);

-- Tables are now cached in a local DuckDB file; queries never hit the API
SELECT COUNT(*) FROM kbc."in.c-crm".contacts;

ATTACH 'https://connection.keboola.com' AS kbc (
    TYPE      keboola,
    TOKEN     'my-storage-api-token',
    READ_ONLY true
);

ATTACH 'https://connection.keboola.com' AS kbc (
    TYPE   keboola,
    TOKEN  'my-storage-api-token',
    BRANCH 'my-feature-branch'
);

-- Full table scan
SELECT * FROM kbc."in.c-crm".contacts;

-- Column projection (only fetches requested columns)
SELECT id, name, email FROM kbc."in.c-crm".contacts;

-- Filter pushdown (translated to Keboola Query Service where clause)
SELECT * FROM kbc."in.c-crm".contacts
WHERE status = 'active' AND country = 'US';

-- Join Keboola data with a local CSV
SELECT k.customer_id, k.revenue, l.segment
FROM kbc."in.c-sales".orders k
JOIN read_csv('segments.csv') l USING (customer_id);

-- Append rows to an existing Keboola table
INSERT INTO kbc."in.c-crm".contacts (id, name, email)
VALUES ('42', 'Alice', '[email protected]');

-- Bulk-insert from a local query result
INSERT INTO kbc."out.c-analytics".summary
SELECT region, SUM(revenue) FROM kbc."in.c-sales".orders GROUP BY region;

-- Update rows (translates to a load + merge under the hood)
UPDATE kbc."in.c-crm".contacts
SET status = 'churned'
WHERE last_order_date < '2024-01-01';

-- Delete matching rows
DELETE FROM kbc."in.c-crm".contacts WHERE status = 'test';

-- Create a new Keboola bucket (schema)
CREATE SCHEMA kbc."out.c-results";

-- Create a new Keboola table
CREATE TABLE kbc."out.c-results".report (
    id      VARCHAR PRIMARY KEY,
    value   DOUBLE,
    created TIMESTAMP
);

-- Drop a table
DROP TABLE kbc."out.c-results".report;

-- Drop a bucket (schema)
DROP SCHEMA kbc."out.c-results";

-- Reload the catalog from Keboola Storage (picks up tables created outside DuckDB)
SELECT keboola_refresh_catalog('kbc');

-- List all tables visible through an attached database
SELECT * FROM keboola_tables('kbc');

-- Pull all tables in database into snapshot
CALL keboola_pull('kbc');

-- Pull all tables in a schema
CALL keboola_pull('kbc."in.c-crm"');

-- Pull a single table
CALL keboola_pull('kbc."in.c-crm".contacts');

-- Pull with changed_since filter (incremental, only if table has _timestamp)
CALL keboola_pull('kbc."in.c-crm".contacts', changed_since := '2024-01-01T00:00:00Z');

-- Pull with row filter
CALL keboola_pull('kbc."in.c-crm".contacts', filter := 'status = ''active''');

-- Current extension version
SELECT keboola_version();

LOAD 'build/release/extension/keboola/keboola.duckdb_extension';

LOAD '/path/to/keboola.duckdb_extension';