-- DuckDB script to explore the space fixtures ducklake dataset
-- Usage: duckdb -box < explore_space.sql
--    or: duckdb < explore_space.sql
--    or: duckdb -c ".read explore_space.sql"

-- Install and load required extensions
INSTALL httpfs;
INSTALL ducklake;
LOAD httpfs;
LOAD ducklake;

-- Verify extensions are loaded
SELECT extension_name, loaded, extension_version
FROM duckdb_extensions()
WHERE extension_name IN ('httpfs', 'ducklake') AND loaded;

-- Attach ducklake metadata database from HuggingFace
-- Requires: hf auth login --token "${HF_TOKEN}" --add-to-git-credential
ATTACH 'ducklake:hf://datasets/sciexp/fixtures/lakes/frozen/space.db' AS space;

-- Alternative: attach from local clone (uncomment to use)
-- ATTACH 'ducklake:lakes/frozen/space.db' AS space;

USE space;

-- ducklake metadata is available in `__ducklake_metadata_*` db `ducklake_*` tables
-- SHOW ALL TABLES;

-- Show available tables in the space schema
SHOW TABLES FROM space.main;

-- Describe astronauts table schema
DESCRIBE space.main.astronauts;

-- Statistical summary of astronauts table
SUMMARIZE space.main.astronauts;

-- Find the most experienced astronauts by total space days
SELECT name, nationality, total_space_days, total_flights
FROM astronauts
ORDER BY total_space_days DESC
LIMIT 10;
