#!/usr/bin/env bash
set -euo pipefail

repo_root="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
examples_dir="${1:-$repo_root/examples}"

rm -rf "$examples_dir"
mkdir -p "$examples_dir"

cat <<'BOOTSTRAP_INVENTORY_CSV' > "$examples_dir/inventory.csv"
product,category,price,stock,active
Keyboard,electronics,12.5,8,true
Cable,electronics,5.0,24,true
Notebook,office,3.2,50,false
Pen,office,1.5,80,true
BOOTSTRAP_INVENTORY_CSV

# Bootstrap the XLSX input directly from CSV to keep this script self-contained.
cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.csv" \
  --sql "SELECT product, category, price, stock, active FROM table" \
  --output "$examples_dir/inventory.xlsx" \
  --format xlsx

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xlsx" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products.txt"

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xlsx" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products.csv" \
  --format csv

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xlsx" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products.jsonl" \
  --format jsonl

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xlsx" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products.md" \
  --format markdown

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xlsx" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products.xml" \
  --format xml

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xlsx" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products.feather" \
  --format feather

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xlsx" \
  --sql "SELECT category, SUM(stock) AS total_stock FROM table GROUP BY category ORDER BY total_stock DESC" \
  --output "$examples_dir/stock-by-category.xlsx" \
  --format xlsx

cat <<'INVENTORY_XML' > "$examples_dir/inventory.xml"
<?xml version="1.0" encoding="UTF-8"?>
<datasets>
  <Inventory>
    <row>
      <product>Keyboard</product>
      <category>electronics</category>
      <price>12.5</price>
      <stock>8</stock>
      <active>true</active>
    </row>
    <row>
      <product>Cable</product>
      <category>electronics</category>
      <price>5.0</price>
      <stock>24</stock>
      <active>true</active>
    </row>
    <row>
      <product>Notebook</product>
      <category>office</category>
      <price>3.2</price>
      <stock>50</stock>
      <active>false</active>
    </row>
    <row>
      <product>Pen</product>
      <category>office</category>
      <price>1.5</price>
      <stock>80</stock>
      <active>true</active>
    </row>
  </Inventory>
  <Archive>
    <row>
      <product>Legacy Mouse</product>
      <category>electronics</category>
      <price>2.0</price>
      <stock>2</stock>
      <active>false</active>
    </row>
  </Archive>
</datasets>
INVENTORY_XML

cat <<'INVENTORY_CSV' > "$examples_dir/inventory.csv"
product,category,price,stock,active
Keyboard,electronics,12.5,8,true
Cable,electronics,5.0,24,true
Notebook,office,3.2,50,false
Pen,office,1.5,80,true
INVENTORY_CSV

cat <<'SNAPSHOT_BEFORE_CSV' > "$examples_dir/snapshot-before.csv"
id,product,category,price,stock,updated_at
1,Keyboard,electronics,12.5,8,2025-01-01T08:00:00Z
2,Cable,electronics,5.0,24,2025-01-01T08:00:00Z
3,Notebook,office,3.2,50,2025-01-01T08:00:00Z
SNAPSHOT_BEFORE_CSV

cat <<'SNAPSHOT_AFTER_CSV' > "$examples_dir/snapshot-after.csv"
id,product,category,price,stock,updated_at
1,Keyboard,electronics,12.5,8,2025-02-01T08:00:00Z
2,Cable,electronics,5.0,20,2025-02-01T08:00:00Z
4,Pen,office,1.5,80,2025-02-01T08:00:00Z
SNAPSHOT_AFTER_CSV

cat <<'INVENTORY_JSONL' > "$examples_dir/inventory.jsonl"
{"product":"Keyboard","category":"electronics","price":12.5,"stock":8,"active":true}
{"product":"Cable","category":"electronics","price":5.0,"stock":24,"active":true}
{"product":"Notebook","category":"office","price":3.2,"stock":50,"active":false}
{"product":"Pen","category":"office","price":1.5,"stock":80,"active":true}
INVENTORY_JSONL

cat <<'INVENTORY_JSON' > "$examples_dir/inventory.json"
{
  "Inventory": [
    {
      "product": "Keyboard",
      "category": "electronics",
      "price": 12.5,
      "stock": 8,
      "active": true
    },
    {
      "product": "Cable",
      "category": "electronics",
      "price": 5.0,
      "stock": 24,
      "active": true
    },
    {
      "product": "Notebook",
      "category": "office",
      "price": 3.2,
      "stock": 50,
      "active": false
    },
    {
      "product": "Pen",
      "category": "office",
      "price": 1.5,
      "stock": 80,
      "active": true
    }
  ],
  "Archive": [
    {
      "product": "Legacy Mouse",
      "category": "electronics",
      "price": 2.0,
      "stock": 2,
      "active": false
    }
  ]
}
INVENTORY_JSON

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.csv" \
  --sql "SELECT product, category, price, stock, active FROM table ORDER BY product" \
  --output "$examples_dir/inventory.feather" \
  --format feather

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.csv" \
  --sql "SELECT product, category, price, stock, active FROM table ORDER BY product" \
  --output "$examples_dir/inventory.html" \
  --format html

cat <<'INVENTORY_MD' > "$examples_dir/inventory.md"
# Inventory Data

| product | category | price | stock | active |
| --- | --- | ---: | ---: | :---: |
| Keyboard | electronics | 12.5 | 8 | true |
| Cable | electronics | 5.0 | 24 | true |
| Notebook | office | 3.2 | 50 | false |
| Pen | office | 1.5 | 80 | true |

## Archive

| product | category | price | stock | active |
| --- | --- | ---: | ---: | :---: |
| Legacy Mouse | electronics | 2.0 | 2 | false |
INVENTORY_MD

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xml:Inventory" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products-from-xml.csv" \
  --format csv

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.csv" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products-from-csv.csv" \
  --format csv

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.jsonl" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products-from-jsonl.csv" \
  --format csv

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.json:Inventory" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products-from-json.csv" \
  --format csv

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.md" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products-from-markdown.csv" \
  --format csv

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.html" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products-from-html.csv" \
  --format csv

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.feather" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products-from-feather.csv" \
  --format csv

cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  query \
  --input "$examples_dir/inventory.xlsx" \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output "$examples_dir/active-products.html" \
  --format html

if cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  diff \
  --key id \
  --ignore-columns updated_at \
  "$examples_dir/snapshot-before.csv" \
  "$examples_dir/snapshot-after.csv" \
  --output "$examples_dir/diff-products.txt" \
  --format text; then
  :
else
  status=$?
  if [[ "$status" -ne 1 ]]; then
    exit "$status"
  fi
fi

if cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  diff \
  --key id \
  --ignore-columns updated_at \
  --show all \
  --side-by-side \
  "$examples_dir/snapshot-before.csv" \
  "$examples_dir/snapshot-after.csv" \
  --output "$examples_dir/diff-products-side-by-side.md" \
  --format markdown; then
  :
else
  status=$?
  if [[ "$status" -ne 1 ]]; then
    exit "$status"
  fi
fi

if cargo run --manifest-path "$repo_root/Cargo.toml" --bin qf -- \
  diff \
  --schema-only \
  "$examples_dir/snapshot-before.csv" \
  "$examples_dir/snapshot-after.csv" \
  --output "$examples_dir/diff-schema.json" \
  --format json; then
  :
else
  status=$?
  if [[ "$status" -ne 1 ]]; then
    exit "$status"
  fi
fi

cat <<'COMMANDS' > "$examples_dir/commands.sh"
#!/usr/bin/env bash
set -euo pipefail

cargo run -- \
  query \
  --input ./examples/inventory.xlsx \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products.txt

cargo run -- \
  query \
  --input ./examples/inventory.xlsx \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products.csv \
  --format csv

cargo run -- \
  query \
  --input ./examples/inventory.xlsx \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products.jsonl \
  --format jsonl

cargo run -- \
  query \
  --input ./examples/inventory.xlsx \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products.md \
  --format markdown

cargo run -- \
  query \
  --input ./examples/inventory.xlsx \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products.xml \
  --format xml

cargo run -- \
  query \
  --input ./examples/inventory.xlsx \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products.feather \
  --format feather

cargo run -- \
  query \
  --input ./examples/inventory.xlsx \
  --sql "SELECT category, SUM(stock) AS total_stock FROM table GROUP BY category ORDER BY total_stock DESC" \
  --output ./examples/stock-by-category.xlsx \
  --format xlsx

cargo run -- \
  query \
  --input ./examples/inventory.xml:Inventory \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products-from-xml.csv \
  --format csv

cargo run -- \
  query \
  --input ./examples/inventory.csv \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products-from-csv.csv \
  --format csv

cargo run -- \
  query \
  --input ./examples/inventory.jsonl \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products-from-jsonl.csv \
  --format csv

cargo run -- \
  query \
  --input ./examples/inventory.json:Inventory \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products-from-json.csv \
  --format csv

cargo run -- \
  query \
  --input ./examples/inventory.md \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products-from-markdown.csv \
  --format csv

cargo run -- \
  query \
  --input ./examples/inventory.html \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products-from-html.csv \
  --format csv

cargo run -- \
  query \
  --input ./examples/inventory.feather \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products-from-feather.csv \
  --format csv

cargo run -- \
  query \
  --input ./examples/inventory.xlsx \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output ./examples/active-products.html \
  --format html

cargo run -- \
  query \
  --input @clipboard.csv \
  --sql "SELECT product, price FROM table WHERE active = 1 ORDER BY price DESC" \
  --output @clipboard.json \
  --format json

cargo run -- \
  diff \
  --key id \
  --ignore-columns updated_at \
  ./examples/snapshot-before.csv \
  ./examples/snapshot-after.csv \
  --output ./examples/diff-products.txt \
  --format text

cargo run -- \
  diff \
  --key id \
  --ignore-columns updated_at \
  --show all \
  --side-by-side \
  ./examples/snapshot-before.csv \
  ./examples/snapshot-after.csv \
  --output ./examples/diff-products-side-by-side.md \
  --format markdown

cargo run -- \
  diff \
  --schema-only \
  ./examples/snapshot-before.csv \
  ./examples/snapshot-after.csv \
  --output ./examples/diff-schema.json \
  --format json
COMMANDS

cat <<'README' > "$examples_dir/README.md"
# Examples

Contenuto generato da `scripts/regenerate_examples.sh`.

## File

- `inventory.xlsx`: workbook di input con il foglio predefinito generato automaticamente.
- `inventory.xml`: input XML con i tag `Inventory` e `Archive`.
- `inventory.csv`: input CSV con gli stessi campi dell'inventory principale.
- `inventory.json`: input JSON con chiavi top-level `Inventory` e `Archive`.
- `inventory.jsonl`: input JSONL con un oggetto per riga.
- `inventory.html`: input HTML con una tabella equivalente agli altri esempi di inventory.
- `inventory.md`: input Markdown con più tabelle; di default viene usata la prima.
- `inventory.feather`: input Feather equivalente agli altri dataset tabellari.
- `snapshot-before.csv` / `snapshot-after.csv`: snapshot CSV da confrontare con `qf diff`.
- `active-products.txt`: output testuale di una query sui prodotti attivi.
- `active-products.csv`: output CSV della stessa query sui prodotti attivi.
- `active-products.jsonl`: output JSONL della stessa query sui prodotti attivi.
- `active-products.md`: output Markdown della stessa query sui prodotti attivi.
- `active-products.html`: output HTML della stessa query sui prodotti attivi.
- `active-products.xml`: output XML della stessa query sui prodotti attivi.
- `active-products.feather`: output Feather della stessa query sui prodotti attivi.
- `active-products-from-xml.csv`: output CSV ottenuto interrogando `inventory.xml:Inventory`.
- `active-products-from-csv.csv`: output CSV ottenuto interrogando `inventory.csv`.
- `active-products-from-json.csv`: output CSV ottenuto interrogando `inventory.json:Inventory`.
- `active-products-from-jsonl.csv`: output CSV ottenuto interrogando `inventory.jsonl`.
- `active-products-from-html.csv`: output CSV ottenuto interrogando `inventory.html`.
- `active-products-from-markdown.csv`: output CSV ottenuto interrogando la prima tabella di `inventory.md`.
- `active-products-from-feather.csv`: output CSV ottenuto interrogando `inventory.feather`.
- `stock-by-category.xlsx`: output XLSX aggregato per categoria.
- `diff-products.txt`: diff tabellare (added/removed/changed) con confronto per chiave.
- `diff-products-side-by-side.md`: diff completo con colonne `_left_*` / `_right_*`.
- `diff-schema.json`: confronto schema-only tra i due snapshot.
- `commands.sh`: esempi di comandi da eseguire dalla root del repository.

## Note

- Gli esempi CSV e JSONL riflettono il percorso ottimizzato per file grandi: caricamento incrementale e inserimenti SQLite a batch.
- Per gli esempi clipboard usa pseudo-percorsi come `@clipboard.csv` e `@clipboard.json`; i dati reali non vengono salvati nella cartella `examples/`.

## Rigenerazione locale

```bash
./scripts/regenerate_examples.sh
```

## Rigenerazione via GitHub Actions

Esegui manualmente il workflow `Regenerate examples` dalla tab **Actions**. Il job allega la cartella `examples/` come artifact.
README

chmod +x "$examples_dir/commands.sh"