INSTALL httpfs;
LOAD httpfs;

SELECT * FROM extensions WHERE name = 'httpfs';

-- Read JSON directly from GitHub API
CREATE TABLE github_commits AS
SELECT * FROM read_json_auto(
    'https://api.github.com/repos/duckdb/duckdb/commits?per_page=5'
);

SELECT commit->>'sha' AS sha,
       commit->>'message' AS message,
       author->>'name' AS author_name
FROM github_commits
LIMIT 5;

-- Manually parse nested fields
SELECT 
    json_extract(commit, '$.sha') AS commit_sha,
    json_extract(commit, '$.commit.author.name') AS author_name,
    json_extract(commit, '$.commit.author.date') AS commit_date,
    json_extract(commit, '$.commit.stats.total') AS total_changes
FROM github_commits
LIMIT 3;

-- Access private API with Bearer Token
CREATE TABLE private_data AS
SELECT * FROM read_json_auto(
    'https://api.example.com/v1/data',
    type_map='auto',
    file_format='json',
    headers='Authorization: Bearer MY_TOKEN_...'
);

-- Set global HTTP configuration
PRAGMA http_user_agent('DuckDB-DataBot/1.0');

-- Then read normally
SELECT * FROM read_csv_auto(
    'https://data.example.com/export.csv'
);

-- Create target table
CREATE TABLE api_incremental (
    id INTEGER,
    name VARCHAR,
    value DOUBLE,
    fetched_at TIMESTAMP
);

-- Simulate paginated pull (replace with real API in production)
WITH RECURSIVE page_counter(page) AS (
    VALUES (0)
    UNION ALL
    SELECT page + 1 FROM page_counter WHERE page < 4
)
INSERT INTO api_incremental
SELECT 
    page * 100 + row_number() OVER (ORDER BY (SELECT NULL)) AS id,
    'item_' || page * 100 + row_number() OVER (ORDER BY (SELECT NULL)) AS name,
    random() * 1000 AS value,
    current_timestamp AS fetched_at
FROM page_counter, generate_series(1, 100);

SELECT COUNT(*) AS total_records FROM api_incremental;

-- Read remote CSV directly
CREATE TABLE remote_csv AS
SELECT * FROM read_csv_auto(
    'https://raw.githubusercontent.com/duckdb/duckdb/main/extension/httpfs/test/data/sample.csv'
);

-- Quick overview
DESCRIBE remote_csv;

-- Aggregate on remote Parquet without downloading
SELECT 
    region,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_revenue
FROM read_parquet(
    'https://example-data.com/sales/*.parquet'
)
GROUP BY region
ORDER BY total_revenue DESC;

-- Read all Parquet files in a directory
SELECT * FROM read_parquet('https://storage.example.com/daily_reports/2026-07-*.parquet');

-- Merge multiple CSVs
SELECT * FROM read_csv_auto(
    'https://example.com/exports/jan.csv',
    'https://example.com/exports/feb.csv',
    'https://example.com/exports/mar.csv'
);

-- 1. Create staging table with metadata
CREATE TABLE IF NOT EXISTS api_staging (
    record_id VARCHAR PRIMARY KEY,
    data JSON,
    source_url VARCHAR,
    fetched_at TIMESTAMP DEFAULT current_timestamp,
    is_deleted BOOLEAN DEFAULT FALSE
);

-- 2. Check last fetch time
SELECT MAX(fetched_at) AS last_fetch FROM api_staging;

-- 3. Pull only updated data (using If-Modified-Since header)
INSERT INTO api_staging
SELECT * FROM read_json_auto(
    'https://api.example.com/data?since=2026-07-16T00:00:00Z',
    headers='If-Modified-Since: Fri, 16 Jul 2026 00:00:00 GMT'
);

-- 4. Deduplicate: keep latest version
DELETE FROM api_staging
WHERE record_id IN (
    SELECT record_id
    FROM api_staging
    GROUP BY record_id
    HAVING COUNT(*) > 1
)
AND rowid NOT IN (
    SELECT MIN(rowid)
    FROM api_staging
    GROUP BY record_id
);

-- Set maximum HTTP connections
PRAGMA http_max_connections = 10;

-- Set timeout (seconds)
PRAGMA http_timeout = 30;

-- First pull and cache
COPY (
    SELECT * FROM read_json_auto('https://api.example.com/heavy-endpoint')
) TO '/tmp/cache/heavy_endpoint.json';

-- Subsequent reads from local cache
SELECT * FROM read_json_auto('/tmp/cache/heavy_endpoint.json');

-- Use stream mode to avoid memory overflow
SELECT * FROM read_csv_auto(
    'https://example.com/large_export.csv',
    stream=true
);

-- Create orders table
CREATE TABLE orders (
    order_id VARCHAR,
    customer_id VARCHAR,
    product_name VARCHAR,
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    order_date DATE,
    status VARCHAR,
    region VARCHAR
);

-- Ingest from API (simulated)
INSERT INTO orders
SELECT 
    json_extract(item, '$.order_id') AS order_id,
    json_extract(item, '$.customer_id') AS customer_id,
    json_extract(item, '$.product_name') AS product_name,
    cast(json_extract(item, '$.quantity') AS INTEGER) AS quantity,
    cast(json_extract(item, '$.unit_price') AS DECIMAL(10,2)) AS unit_price,
    cast(json_extract(item, '$.order_date') AS DATE) AS order_date,
    json_extract(item, '$.status') AS status,
    json_extract(item, '$.region') AS region
FROM read_json_auto(
    'https://shop.example.com/api/v2/orders?date_from=2026-01-01&limit=10000',
    type_map='auto'
);

-- Analysis: revenue by region
SELECT 
    region,
    COUNT(*) AS order_count,
    SUM(quantity * unit_price) AS revenue,
    AVG(unit_price) AS avg_price
FROM orders
WHERE status = 'completed'
GROUP BY region
ORDER BY revenue DESC;