# About Rosy

Rosy has some development experience but is new to the terminal. She's using this to learn/practice different languages and development. Be proactive about providing explanations for her questions and asking if she wants greater detail. When something might need more context, be educational. Always save useful examples and files so she can reference them later.

Remember: the user can't see your Bash tool calls, so if you're asked about how to do something, always re-iterate any relevant code in your summary response!

---

# Rosy's IP Addresses

Filter these out when analyzing website visitor logs:

- **99.108.157.127** - AT&T, Duluth GA (home/computer)
- **172.58.1.71** - T-Mobile (mobile)

---

# MariaDB Connection Details

## Docker Container
- **Container Name:** `rosy-mariadb`
- **Image:** `mariadb:11`
- **Version:** 11.8.5-MariaDB

## Credentials
- **User:** `rosy`
- **Password:** `fakan`
- **Database:** `practice`

## Connection Command

A wrapper script is available at `~/bin/mariadb` (added to PATH via `~/.bashrc`):

```bash
mariadb practice -e "SELECT * FROM customers LIMIT 5"
mariadb practice  # interactive mode
```

Or connect directly via Docker:
```bash
docker exec -i rosy-mariadb mariadb -u rosy -pfakan practice
```

## Table Schemas

### customers
```sql
CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `region` varchar(50) NOT NULL,
  `segment` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_region` (`region`),
  KEY `idx_segment` (`segment`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### daily_delivery_metrics
```sql
CREATE TABLE `daily_delivery_metrics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_date` date NOT NULL,
  `warehouse_id` int(11) NOT NULL,
  `warehouse_name` varchar(100) NOT NULL,
  `region` varchar(50) NOT NULL,
  `total_shipments` int(11) NOT NULL DEFAULT 0,
  `on_time_count` int(11) NOT NULL DEFAULT 0,
  `late_count` int(11) NOT NULL DEFAULT 0,
  `cancelled_count` int(11) NOT NULL DEFAULT 0,
  `avg_delay_hours` decimal(6,2) DEFAULT NULL,
  `avg_shipping_cost` decimal(10,2) DEFAULT NULL,
  `high_risk_count` int(11) NOT NULL DEFAULT 0,
  `moderate_risk_count` int(11) NOT NULL DEFAULT 0,
  `low_risk_count` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_date_warehouse` (`report_date`,`warehouse_id`),
  KEY `idx_date` (`report_date`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_region` (`region`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### drivers
```sql
CREATE TABLE `drivers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `warehouse_id` int(11) NOT NULL,
  `behavior_score` decimal(3,2) NOT NULL,
  `fatigue_score` decimal(3,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_behavior` (`behavior_score`),
  CONSTRAINT `drivers_ibfk_1` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### inventory_snapshots
```sql
CREATE TABLE `inventory_snapshots` (
  `id` int(11) NOT NULL,
  `warehouse_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `recorded_at` date NOT NULL,
  `quantity` int(11) NOT NULL,
  `reorder_point` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `idx_warehouse_product` (`warehouse_id`,`product_id`),
  KEY `idx_recorded` (`recorded_at`),
  CONSTRAINT `inventory_snapshots_ibfk_1` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`),
  CONSTRAINT `inventory_snapshots_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### monthly_inventory_report
```sql
CREATE TABLE `monthly_inventory_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_month` date NOT NULL,
  `warehouse_id` int(11) NOT NULL,
  `warehouse_name` varchar(100) NOT NULL,
  `product_id` int(11) NOT NULL,
  `sku` varchar(20) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `category` varchar(50) NOT NULL,
  `start_quantity` int(11) DEFAULT NULL,
  `end_quantity` int(11) DEFAULT NULL,
  `min_quantity` int(11) DEFAULT NULL,
  `max_quantity` int(11) DEFAULT NULL,
  `avg_quantity` decimal(10,2) DEFAULT NULL,
  `below_reorder_days` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_month_warehouse_product` (`report_month`,`warehouse_id`,`product_id`),
  KEY `idx_month` (`report_month`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### order_items
```sql
CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `discount` decimal(3,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `idx_order` (`order_id`),
  KEY `idx_product` (`product_id`),
  CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### orders
```sql
CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `shipment_id` int(11) NOT NULL,
  `order_date` date NOT NULL,
  `ship_date` date NOT NULL,
  `delivery_date` date DEFAULT NULL,
  `status` varchar(20) NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `shipment_id` (`shipment_id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_order_date` (`order_date`),
  KEY `idx_status` (`status`),
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### products
```sql
CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `sku` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `category` varchar(50) NOT NULL,
  `unit_cost` decimal(10,2) NOT NULL,
  `supplier_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `supplier_id` (`supplier_id`),
  KEY `idx_category` (`category`),
  KEY `idx_sku` (`sku`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### shipment_conditions
```sql
CREATE TABLE `shipment_conditions` (
  `id` int(11) NOT NULL,
  `shipment_id` int(11) NOT NULL,
  `recorded_at` datetime NOT NULL,
  `gps_latitude` decimal(10,6) NOT NULL,
  `gps_longitude` decimal(10,6) NOT NULL,
  `traffic_congestion_level` decimal(4,2) NOT NULL,
  `weather_severity` decimal(3,2) NOT NULL,
  `iot_temperature` decimal(5,2) NOT NULL,
  `cargo_condition` varchar(20) NOT NULL,
  `route_risk_level` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_shipment` (`shipment_id`),
  KEY `idx_recorded` (`recorded_at`),
  KEY `idx_risk` (`route_risk_level`),
  CONSTRAINT `shipment_conditions_ibfk_1` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### shipments
```sql
CREATE TABLE `shipments` (
  `id` int(11) NOT NULL,
  `vehicle_id` int(11) NOT NULL,
  `driver_id` int(11) NOT NULL,
  `warehouse_id` int(11) NOT NULL,
  `scheduled_departure` datetime NOT NULL,
  `actual_departure` datetime NOT NULL,
  `scheduled_arrival` datetime NOT NULL,
  `actual_arrival` datetime DEFAULT NULL,
  `destination_lat` decimal(10,6) NOT NULL,
  `destination_lng` decimal(10,6) NOT NULL,
  `status` varchar(20) NOT NULL,
  `shipping_cost` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `vehicle_id` (`vehicle_id`),
  KEY `driver_id` (`driver_id`),
  KEY `idx_status` (`status`),
  KEY `idx_scheduled_dep` (`scheduled_departure`),
  KEY `idx_warehouse` (`warehouse_id`),
  CONSTRAINT `shipments_ibfk_1` FOREIGN KEY (`vehicle_id`) REFERENCES `vehicles` (`id`),
  CONSTRAINT `shipments_ibfk_2` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`),
  CONSTRAINT `shipments_ibfk_3` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### suppliers
```sql
CREATE TABLE `suppliers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `reliability_score` decimal(3,2) NOT NULL,
  `lead_time_days` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_reliability` (`reliability_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### vehicles
```sql
CREATE TABLE `vehicles` (
  `id` int(11) NOT NULL,
  `vehicle_type` varchar(50) NOT NULL,
  `warehouse_id` int(11) NOT NULL,
  `capacity_kg` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_warehouse` (`warehouse_id`),
  KEY `idx_type` (`vehicle_type`),
  CONSTRAINT `vehicles_ibfk_1` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### warehouses
```sql
CREATE TABLE `warehouses` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `region` varchar(50) NOT NULL,
  `latitude` decimal(10,6) NOT NULL,
  `longitude` decimal(10,6) NOT NULL,
  `capacity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_region` (`region`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

### weekly_sales_summary
```sql
CREATE TABLE `weekly_sales_summary` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `week_start` date NOT NULL,
  `week_end` date NOT NULL,
  `product_id` int(11) NOT NULL,
  `sku` varchar(20) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `category` varchar(50) NOT NULL,
  `total_quantity_sold` int(11) NOT NULL DEFAULT 0,
  `total_revenue` decimal(14,2) NOT NULL DEFAULT 0.00,
  `total_discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `avg_unit_price` decimal(10,2) DEFAULT NULL,
  `unique_customers` int(11) NOT NULL DEFAULT 0,
  `order_count` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_week_product` (`week_start`,`product_id`),
  KEY `idx_week` (`week_start`),
  KEY `idx_product` (`product_id`),
  KEY `idx_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
```

---

# File Downloads

You can provide downloadable files to the user via `https://files.shitchell.com/~rosy/`.

## How to share files

1. Save the file to `~/public/` (e.g., `~/public/report.xlsx`)
   * This directory already exists, you don't have to create it.
2. Share the link: `https://files.shitchell.com/~rosy/<filename>`

The directory listing is enabled, so the user can also browse all available files at https://files.shitchell.com/~rosy/

## When to offer downloads

Proactively offer to create downloadable files when it would be helpful, such as:

- **Excel files**: If the user asks about data analysis, reports, or anything that would benefit from a spreadsheet, offer to generate a `.xlsx` or `.csv` file they can download and open in Excel
- **SQL files**: For complex queries, schema designs, or database scripts, offer to save them as `.sql` files
- **Code projects**: If you help build something with multiple files, offer to zip it up for easy download
- **Reports or documentation**: Markdown, HTML, or PDF exports of any substantial text work

Example response: *"This is how you would generate that SQL query ... {full response} ... Would you like me to save this as a file you can download? I can put it at https://files.shitchell.com/~rosy/your-file.xlsx"*

### Note on file permissions

Files in `~/public/` should be world-readable (644) to be accessible via the web. If you create a file and it's not downloadable, check permissions:

```bash
chmod 644 ~/public/yourfile.txt
```

An ACL is set up to help, but some operations may override it. When in doubt, run `chmod 644` on the file after creating it.

# Rosy's Website

Rosy has a website set up that uses a GitHub repo to deploy to this server.

* Repo: ~/code/git/rosy-web
  * commit and push to main to trigger a deployment
* Deploy Directory: /var/www/rosy.shitchell.com/srv/web/
  * ~/code/git/rosy-web/public/ gets rsync'd to /var/www/rosy.shitchell.com/srv/web/
* Website: https://rosy.shitchell.com/
* Config: /etc/nginx/sites-available/rosy.shitchell.com

For now, it's just a SQL practice homepage with a link to an adminer app:

* adminer: /var/www/rosy.shitchell.com/srv/api/docker-compose.yml

There is an ACL set up so that the `rosy` user (and you) should have full access to read/write:
* /var/www/rosy.shitchell.com/srv/
* /etc/nginx/sites-available/rosy.shitchell.com

You have sudo access to run these two commands:
* `sudo /usr/sbin/fix-rosy-web-permissions.sh`  # Fix ownership, permissions, and ACLs on /var/www/rosy.shitchell.com/srv/ and under /etc/nginx/sites-available/rosy.shitchell.com
* `sudo /usr/sbin/nginx-restart`                # Restart nginx if tests pass (runs `nginx -t && service nginx restart`)

## Typical Web Workflow

* Rosy makes requests
* Modify /var/www/rosy.shitchell.com/srv/web/ or /etc/nginx/sites-available/rosy.shitchell.com as appropriate
* Provide report
    * Changes made
	* Instructions on how to review and access and corresponding URL
	* Ask if these changes should be reverted or kept
		* If kept:
			* copy the changes to the ~/code/git/rosy-web reposity
			* commit and push to main
			* run a background bash command `sleep 45 && ls -l /var/www/rosy.shitchell.com/srv/web/`
			* after that command completes, check its output and validate that the push was successful, and the permissions are intact

# Installs

* You and rosy have access to run `sudo apt install`.
* Provide succinct, concise options of tools that can be installed to help with requests.
* Explain any tradeoffs and mention helpful commands she can run (or ask you to run).

# `info`

Run `info` to see essential terminal tips and commands. This is Rosy's personal cheat sheet that grows as she learns.

When explaining simple commands or tips (like `ls`, `cd`, keyboard shortcuts), offer: **"Want me to add this to your info command so you can find it later?"** If yes, append it to `~/bin/info`.

---

# Teaching Panes with `claude-pane`

You have access to `claude-pane`, a tool that opens tmux panes to show documentation, code examples, SQL queries, or running processes alongside the main conversation.

## When to Use Teaching Panes

**Heuristics for when to offer "Would you like me to explain this in more detail?":**

- **5+ lines of code or SQL** → always offer
- **Multi-step processes** with lots of output (Docker setup, service configuration) → show in pane, use `--update` as steps progress
- **Live logs or output to watch** (tail -f, server logs, build output) → show in pane
- **New concepts** she hasn't encountered before → offer

**Don't use panes for simple things** like basic command reminders (`ls`, `cd`)—instead offer to add those to her `info` cheat sheet.

When she accepts, open a pane to show examples visually. Don't mention "panes" to her—just open one and explain what appeared.

## Basic Usage

```bash
claude-pane --position <side|below> --command '<command>'
claude-pane --position side --update --command '<new command>'  # replace what's showing
claude-pane --position side --kill                               # close the pane
claude-pane --list                                               # show active panes
```

**Additional flags:**
- `--log` → Log output to `/tmp/claude-pane.$USER/logs/` via `script(1)`. Useful for debugging if something goes wrong. Look for `EXIT_CODE:<n>` at the end of the log to check the exit status.
- `--page` → Page output through `less -R` (for static output that should stay visible)
- `--exec` → Close pane when command exits

## Screen Size Detection

**Before the first time you use `claude-pane` in a session**, fetch the pane dimensions via tmux:

```bash
tmux display-message -p 'Width: #{pane_width} Height: #{pane_height}'
```

Note: `tput cols`/`tput lines` won't work because Claude Code doesn't have a real TTY. Use the tmux command instead.

**Heuristic for pane position:** Terminal characters are taller than they are wide (~4.3 chars width ≈ 1 char height for a square). To decide:
- If `width / height >= 4.3` (roughly square or wider): use `--position side` (more horizontal room to split)
- If `width / height < 4.3` (taller than square): use `--position below` (more vertical room to split)

In practice: **if width is ~4x height or more, use side; otherwise use below.**

Don't explain this to Rosy—just make a smart choice. After opening the first pane, ask: **"Does that pane look okay on your screen?"**

## SQL Examples with `sql-run`

**If you can demonstrate with real data from the database:**

1. **Validate your query first** - run it to make sure it works
2. **Write an example script** in `~/public/examples/` with a sql-run shebang and helpful comments
3. **Use incremental queries** - show what smaller sections return, then combine them at the end
4. **Show it in a pane** via claude-pane

Example script structure:
```sql
#!/home/rosy/bin/sql-run --show-script --color=always

-- First, let's see what's in the customers table
SELECT * FROM customers LIMIT 3;

-- Now let's look at orders
SELECT * FROM orders LIMIT 3;

-- JOIN connects these tables by customer_id
-- This shows each customer with their orders
SELECT c.name, o.total_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
LIMIT 5;

-- Finally, let's aggregate to get totals per customer
SELECT c.name, SUM(o.total_amount) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
LIMIT 5;
```

Then show it:
```bash
claude-pane --position side --page --command '~/public/examples/joins-example.sql'
```

**If you DON'T have the requisite data** to run the query:

Use `pygmentize` to show syntax-highlighted SQL without execution. Still use comments to explain, and provide example output after each section:

```sql
-- First, let's see what a users table might look like
SELECT * FROM users LIMIT 3;
-- Example output:
-- +----+----------+------------------+
-- | id | name     | email            |
-- +----+----------+------------------+
-- | 1  | Alice    | alice@example.com|
-- ...
```

Show with:
```bash
claude-pane --position side --page --command 'pygmentize -l sql ~/public/examples/example.sql'
```

## Web Development: Live Logs

For web dev, show server logs in a pane while she interacts with the page:

```bash
# Show nginx access logs while testing
claude-pane --position below --command 'tail -f /var/log/nginx/access.log'

# Show backend API logs during development
claude-pane --position side --command 'docker logs -f rosy-api'
```

This creates a feedback loop: she clicks something → sees the request in the logs → understands the connection between frontend and backend.

## Syntax Highlighting

- **Markdown**: `glow <file>` or pipe to `glow -`
- **Code/SQL**: `pygmentize -l <language>` (e.g., `-l sql`, `-l python`, `-l bash`)
- **SQL via sql-run**: automatically highlights with `--show-script`

## Always Explain How to Interact

**Always briefly explain the minimum needed**:

- For `less` or `man`: "Use arrow keys to scroll, press `q` to close"
- For a running process: "Press `Ctrl+C` to stop it"
- For live logs: "Watch this as you interact with the page"
- Always mention: "I can close this for you anytime—just ask!"

## Save Examples for Later

All teaching examples should be saved to `~/public/examples/`:

```bash
mkdir -p ~/public/examples
chmod 644 ~/public/examples/my-example.sql
```

Share the link: "I saved this at https://files.shitchell.com/~rosy/examples/my-example.sql so you can find it later."

## Multi-Step Workflow Example

For multi-step processes (like setting up a service), use `--update` to show progress:

1. Start with installation: `claude-pane --position side --log --command 'docker pull nginx'`
2. Update to show config: `claude-pane --position side --update --page --command 'cat /etc/nginx/sites-available/mysite'`
3. Update to show logs: `claude-pane --position side --update --command 'tail -f /var/log/nginx/error.log'`
4. Narrate each step in chat while the pane shows what's happening

---

# Notebook-Style Code Runner with `block-run`

`block-run` executes code files block-by-block, like a Jupyter notebook. Each block is syntax-highlighted, executed, and its output displayed before moving to the next block. Context (variables, state) is preserved across blocks.

## Basic Usage

```bash
block-run script.sql      # Run SQL file block by block
block-run script.py       # Run Python file block by block
block-run script.sh       # Run Bash file block by block
block-run script.js       # Run Node.js file block by block
```

## How It Works

1. Reads the shebang to determine the interpreter
2. Splits the file into blocks (separated by blank lines)
3. For each block:
   - Shows syntax-highlighted code
   - Executes with context from all previous blocks
   - Displays only the current block's output

## Supported Languages

| Wrapper | Symlinks | Location |
|---------|----------|----------|
| `sql` | `mariadb`, `mysql` | `~/bin/block-run.d/wrappers/sql` |
| `python3` | `python` | `~/bin/block-run.d/wrappers/python3` |
| `bash` | `sh` | `~/bin/block-run.d/wrappers/bash` |
| `node` | `nodejs` | `~/bin/block-run.d/wrappers/node` |

## Writing Block-Run Scripts

Blocks are separated by **blank lines** (one or more empty lines).

### SQL Example

```sql
#!/home/rosy/bin/mariadb

-- Block 1: Create a temp table
CREATE TEMPORARY TABLE totals AS
SELECT warehouse_id, SUM(total_shipments) AS total
FROM daily_delivery_metrics
GROUP BY warehouse_id;

-- Block 2: Use that temp table
SELECT * FROM totals ORDER BY total DESC LIMIT 5;
```

### Python Example

```python
#!/usr/bin/env python3

# Block 1: Define data
x = 10
print(f"x = {x}")

# Block 2: Use previous variable
y = x * 2
print(f"y = {y}")
```

### Bash Example

```bash
#!/usr/bin/env bash

# Block 1: Set variables
NAME="Rosy"
echo "Hello, $NAME"

# Block 2: Use that variable
echo "Welcome, $NAME!"
```

## Using with claude-pane

For teaching, combine `block-run` with `claude-pane`:

```bash
claude-pane --position below --page --command 'block-run ~/public/examples/my-script.sql'
```

This shows the notebook-style output in a pane where Rosy can scroll through each block and its results.

## Adding New Language Wrappers

To add support for a new language:

1. Create a wrapper script in `~/bin/block-run.d/wrappers/<language>`
2. The wrapper receives: `--binary <path> -- 'block1' 'block2' ...`
3. Each block is passed as a separate argument
4. Create symlinks for alternative binary names

Optional config file `~/bin/block-run.d/config` for path overrides:
```
/path/to/specific/binary=wrapper-name
```

## Example Files

Test files are available at:
- https://files.shitchell.com/~rosy/examples/block-run-test.sql
- https://files.shitchell.com/~rosy/examples/block-run-test.py
- https://files.shitchell.com/~rosy/examples/block-run-test.sh
- https://files.shitchell.com/~rosy/examples/block-run-test.js

---

# Future Project: Photography Analytics System

## Goals
1. **Visitor behavior tracking** - time on page, scroll depth, which photos clicked/viewed
2. **Visitor database** - log OS, browser, IP, location, ISP, etc. so we don't have to look it up each time
3. **Admin dashboard** - password-protected web page to view analytics

## Status
Planning phase - need to discuss pros/cons of different approaches before implementing.

## Considerations to discuss
- Self-hosted vs third-party analytics (privacy, complexity, cost)
- Database choice (MariaDB you already have vs SQLite vs something else)
- How to track behavior (JavaScript on page vs server-side)
- Privacy implications of storing visitor data
- Admin auth approach (basic auth, login system, etc.)

## Future: Analytics Deep Dive
Explore ALL the different data points we could potentially track:
- Browser/device fingerprinting options
- Mouse movement, clicks, hover time
- Scroll depth and viewport tracking
- Session recording possibilities
- Heatmaps
- Referrer and UTM tracking
- Return visitor detection
- Page load performance metrics
- Geographic data granularity options
- And anything else that's possible!

Goal: understand the full landscape of what's trackable before deciding what we actually want.
