#!/bin/sh
#
# Data Directory — set up this machine (or an AI agent) to query the published datasets directly.
#
#   curl -fsSL https://data.bunnytech.app/skill.sh | sh
#
# What it does, in order:
#   1. checks for the DuckDB CLI (offers to install it — never without asking)
#   2. writes ./datadirectory/ with an attach.sql, a SKILL.md per dataset, and examples.sql
#   3. connects to each published DuckLake catalog and introspects its schema
#   4. tells you what to run next
#
# It touches nothing outside its own directory: no shell profile, no PATH, no sudo.
# Re-running it is safe — every generated file is rewritten from scratch.
#
# Generated from the live manifests at https://data.bunnytech.app. Datasets: 2.

set -eu

SITE_URL='https://data.bunnytech.app'
INDEX_URL='https://data-directory.fsn1.your-objectstorage.com/public/index.json'
DATADIRECTORY_HOME="${DATADIRECTORY_HOME:-$PWD/datadirectory}"
DUCKDB_INSTALL_URL='https://install.duckdb.org'

# id|alias|catalog-url, one line per published dataset.
DATASETS='itunes-charts|itunes_charts|https://data-directory.fsn1.your-objectstorage.com/catalog/itunes-charts/catalog.ducklake
open-meteo-daily|open_meteo_daily|https://data-directory.fsn1.your-objectstorage.com/catalog/open-meteo-daily/catalog.ducklake'

# ---------------------------------------------------------------------------
# Output helpers. Colour only when stdout is a terminal that wants it.
# ---------------------------------------------------------------------------

if [ -t 1 ] && [ -z "${NO_COLOR:-}" ]; then
  BOLD=$(printf '\033[1m'); DIM=$(printf '\033[2m')
  BLUE=$(printf '\033[34m'); GREEN=$(printf '\033[32m'); RESET=$(printf '\033[0m')
else
  BOLD=''; DIM=''; BLUE=''; GREEN=''; RESET=''
fi

step() { printf '%s==>%s %s%s%s\n' "$BLUE" "$RESET" "$BOLD" "$1" "$RESET"; }
info() { printf '    %s\n' "$1"; }
note() { printf '    %s%s%s\n' "$DIM" "$1" "$RESET"; }
ok() { printf '    %s+%s %s\n' "$GREEN" "$RESET" "$1"; }
die() { printf '\n%sError:%s %s\n' "$BOLD" "$RESET" "$1" >&2; exit 1; }

printf '\n%sData Directory%s — query open datasets with DuckDB\n' "$BOLD" "$RESET"
printf '%s%s%s\n\n' "$DIM" "$SITE_URL" "$RESET"

# ---------------------------------------------------------------------------
# 1. DuckDB
# ---------------------------------------------------------------------------

step 'Checking for the DuckDB CLI'

install_duckdb() {
  if command -v brew >/dev/null 2>&1; then
    info 'Installing with Homebrew...'
    brew install duckdb </dev/null
  else
    info 'Installing with the official installer...'
    curl -fsSL "$DUCKDB_INSTALL_URL" | sh
  fi
}

if command -v duckdb >/dev/null 2>&1; then
  ok "duckdb $(duckdb --version 2>/dev/null </dev/null | head -n 1)"
else
  info 'DuckDB is not on your PATH. It is the only thing this needs.'
  info ''
  info "  official installer:  curl -fsSL $DUCKDB_INSTALL_URL | sh"
  info '  Homebrew (macOS):    brew install duckdb'
  info ''
  # Consent, always. A script piped into a shell must never install software on its own — and
  # when there is no terminal to ask, it stops rather than guessing on the reader's behalf.
  if [ -t 0 ]; then
    printf '    Install DuckDB now? [y/N] '
    read -r reply
    case "$reply" in
      [yY]*) install_duckdb ;;
      *) die 'DuckDB is required. Install it with one of the lines above, then re-run this.' ;;
    esac
  else
    die 'DuckDB is required, and there is no terminal to ask for consent. Install it, then re-run.'
  fi
  command -v duckdb >/dev/null 2>&1 ||
    die 'DuckDB still is not on the PATH. Open a new shell and re-run this.'
  ok "duckdb $(duckdb --version 2>/dev/null </dev/null | head -n 1)"
fi

# ---------------------------------------------------------------------------
# 2. Files
# ---------------------------------------------------------------------------

step "Writing $DATADIRECTORY_HOME"

mkdir -p "$DATADIRECTORY_HOME"

cat > "$DATADIRECTORY_HOME/attach.sql" <<'DATADIRECTORY_ATTACH_SQL'
-- Generated by skill.sh. Re-run the installer to refresh.
-- Source: https://data.bunnytech.app

INSTALL httpfs; LOAD httpfs;
INSTALL ducklake; LOAD ducklake;

-- Apple App Store charts
ATTACH 'https://data-directory.fsn1.your-objectstorage.com/catalog/itunes-charts/catalog.ducklake' AS itunes_charts (TYPE ducklake, READ_ONLY);

-- Daily city weather forecasts
ATTACH 'https://data-directory.fsn1.your-objectstorage.com/catalog/open-meteo-daily/catalog.ducklake' AS open_meteo_daily (TYPE ducklake, READ_ONLY);
DATADIRECTORY_ATTACH_SQL
ok 'attach.sql'

cat > "$DATADIRECTORY_HOME/examples.sql" <<'DATADIRECTORY_EXAMPLES_SQL'
-- Generated by skill.sh — worked examples straight from each dataset manifest.
-- Run with:  duckdb -init attach.sql < examples.sql

-- ========================================================================
-- Apple App Store charts (itunes-charts)
-- Tables: itunes_charts.rankings, itunes_charts.apps
-- ========================================================================

-- Sample rankings
USE itunes_charts;
SELECT * FROM rankings
LIMIT 10;

-- Sample apps
USE itunes_charts;
SELECT * FROM apps
LIMIT 10;

-- ========================================================================
-- Daily city weather forecasts (open-meteo-daily)
-- Tables: open_meteo_daily.daily_weather, open_meteo_daily.cities
-- ========================================================================

-- Today’s forecast for every city
USE open_meteo_daily;
SELECT c.name, w.forecast_date, w.temperature_min, w.temperature_max, w.precipitation_sum
FROM daily_weather w
JOIN cities c USING (city_id)
WHERE w.snapshot_date = (SELECT max(snapshot_date) FROM daily_weather)
  AND w.lead_days = 0
ORDER BY w.temperature_max DESC;

-- How the forecast for one day drifted as it got closer
USE open_meteo_daily;
SELECT w.snapshot_date, w.lead_days, w.temperature_max
FROM daily_weather w
JOIN cities c USING (city_id)
WHERE c.name_lc = ''tokyo''
  AND w.forecast_date = DATE ''2026-08-28''
ORDER BY w.lead_days DESC;
DATADIRECTORY_EXAMPLES_SQL
ok 'examples.sql'

# One SKILL.md per dataset, fetched from the site so the instructions are the live ones.
while IFS='|' read -r id alias catalog; do
  [ -n "$id" ] || continue
  mkdir -p "$DATADIRECTORY_HOME/$id"
  if curl -fsSL "$SITE_URL/datasets/$id/llms.txt" -o "$DATADIRECTORY_HOME/$id/SKILL.md" </dev/null; then
    ok "$id/SKILL.md"
  else
    note "$id/SKILL.md could not be fetched — skipping it."
  fi
done <<DATADIRECTORY_ROWS
$DATASETS
DATADIRECTORY_ROWS

# ---------------------------------------------------------------------------
# 3. Connect and introspect. This is the step that proves the whole thing works.
# ---------------------------------------------------------------------------

step 'Connecting to DuckDB and reading the schemas'

TAB=$(printf '\t')
failures=0

while IFS='|' read -r id alias catalog; do
  [ -n "$id" ] || continue
  schema_file="$DATADIRECTORY_HOME/$id/schema.txt"
  mkdir -p "$DATADIRECTORY_HOME/$id"
  printf '\n    %s%s%s\n' "$BOLD" "$id" "$RESET"

  # Ask DuckDB itself what is in there, rather than echoing the manifest back. A table list means
  # the catalog is reachable, the extension loaded and the reader genuinely connected — which is
  # the only claim worth making here.
  #
  # duckdb_tables() and duckdb_columns() rather than the SHOW ALL TABLES meta-view: that one
  # returns nothing for a freshly attached remote catalog when stdout is a file rather than a
  # terminal, which is exactly how this script runs it. These are ordinary table functions and
  # behave the same either way.
  duckdb -noheader -list -c "
      INSTALL httpfs; LOAD httpfs;
      INSTALL ducklake; LOAD ducklake;
      ATTACH '$catalog' AS $alias (TYPE ducklake, READ_ONLY);
      SELECT t.table_name || '$TAB' || string_agg(c.column_name, ', ' ORDER BY c.column_index)
      FROM duckdb_tables() t
      JOIN duckdb_columns() c
        ON c.database_name = t.database_name
       AND c.schema_name = t.schema_name
       AND c.table_name = t.table_name
      WHERE t.database_name = '$alias'
      GROUP BY t.table_name
      ORDER BY t.table_name;
    " >"$schema_file" 2>&1 </dev/null || true

  # The output is the verdict, not the exit status. `duckdb -c` exits 0 even when a statement
  # fails — and a missing catalog does not necessarily fail at ATTACH either, because the metadata
  # is opened lazily. Both of those show up as "an error in the text" or "no tables at all", so
  # that is what gets checked. Trusting `$?` here reports success for a catalog that is not there.
  if [ -s "$schema_file" ] && ! grep -q 'Error:' "$schema_file"; then
    while IFS="$TAB" read -r table columns; do
      [ -n "$table" ] || continue
      printf '      %s%s%s  %s%s%s\n' "$BOLD" "$table" "$RESET" "$DIM" "$columns" "$RESET"
    done <"$schema_file"
    ok "schema written to $id/schema.txt"
  else
    failures=$((failures + 1))
    if [ -s "$schema_file" ]; then
      note "could not read $catalog"
      note "see $schema_file for what DuckDB said"
    else
      note "attached $catalog, but it exposes no tables"
    fi
  fi
done <<DATADIRECTORY_ROWS
$DATASETS
DATADIRECTORY_ROWS

# ---------------------------------------------------------------------------
# 4. What to do next
# ---------------------------------------------------------------------------

printf '\n'
step 'Ready'
info "Files:      $DATADIRECTORY_HOME"
info "Directory:  $INDEX_URL"
info ''
info 'Open a session with every dataset attached:'
printf '      %sduckdb -init %s/attach.sql%s\n' "$BOLD" "$DATADIRECTORY_HOME" "$RESET"
info ''
info 'Or run the worked examples straight away:'
printf '      %sduckdb -init %s/attach.sql < %s/examples.sql%s\n' \
  "$BOLD" "$DATADIRECTORY_HOME" "$DATADIRECTORY_HOME" "$RESET"
info ''
info 'Point an agent at the per-dataset SKILL.md files, or at:'
info "      $SITE_URL/llms.txt"
printf '\n'

if [ "$failures" -gt 0 ]; then
  die "$failures dataset(s) could not be attached. Everything else above was still written."
fi