#!/usr/bin/env bash
# Real demo for P008 "Why PostgreSQL Ignores Your Index" (8-12 min beginner video).
# Runs PostgreSQL 18 in a throwaway container and saves every command + its real
# output to demo_output.json; the terminal scenes show only this captured output.
# Every number in video.json must match a value here. Re-run = new numbers = update the script.
set -euo pipefail
cd "$(dirname "$0")"
name=ce-pg-p008
docker rm -f $name >/dev/null 2>&1 || true
docker run -d --name $name -e POSTGRES_PASSWORD=demo postgres:18 \
  -c shared_preload_libraries=auto_explain >/dev/null
trap 'docker rm -f $name >/dev/null 2>&1 || true' EXIT
until docker exec $name pg_isready -U postgres -q; do sleep 1; done
sleep 2

python3 - "$name" <<'PY'
import json, subprocess, sys
name = sys.argv[1]
def psql(*sqls):
    """One psql session; several statements run in order (shared session settings)."""
    args = ["docker", "exec", name, "psql", "-U", "postgres", "-X", "-q", "-P", "pager=off"]
    for s in sqls:
        args += ["-c", s]
    out = subprocess.run(args, capture_output=True, text=True)
    return (out.stdout + out.stderr).rstrip()

steps = {}
def step(key, cmd, *extra_before, show=None):
    out = psql(*extra_before, cmd)
    steps[key] = {"cmd": show or cmd, "out": out}

# --- the users table: 1M rows, an index on email, a skewed plan column
psql("""
CREATE TABLE users (id bigserial PRIMARY KEY, email text NOT NULL, plan text NOT NULL, created_at timestamptz NOT NULL);
INSERT INTO users (email, plan, created_at)
  SELECT 'User' || g || '@Example.com',
         CASE WHEN random() < 0.95 THEN 'free' ELSE 'enterprise' END,
         now() - (g || ' minutes')::interval
  FROM generate_series(1, 1000000) g;
CREATE INDEX users_email_idx ON users (email);
CREATE INDEX users_plan_idx ON users (plan);
ANALYZE users;
""")
step("count", "SELECT count(*) FROM users;")
step("indexes", "SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users' ORDER BY indexname;")
step("plain", "EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'User424242@Example.com';")
q_lower = "EXPLAIN ANALYZE SELECT * FROM users WHERE lower(email) = 'user424242@example.com';"
step("explain_only", "EXPLAIN SELECT * FROM users WHERE lower(email) = 'user424242@example.com';")
step("slow", q_lower)

# Reason 1: a function on the column -> expression index
step("fix", "CREATE INDEX users_email_lower_idx ON users (lower(email));")
steps["fix"]["out"] = steps["fix"]["out"] or "CREATE INDEX"
step("analyze", "ANALYZE users;"); steps["analyze"]["out"] = steps["analyze"]["out"] or "ANALYZE"
step("fast", q_lower)

# Reason 2: too many matching rows (the planner is right)
step("broad", "EXPLAIN SELECT * FROM users WHERE plan = 'free';")
step("narrow", "EXPLAIN SELECT * FROM users WHERE plan = 'enterprise';")
step("plan_counts", "SELECT plan, count(*) FROM users GROUP BY plan ORDER BY 2 DESC;")

# Reason 3: mismatched types
step("mismatch", "EXPLAIN SELECT * FROM users WHERE id = 42.0;")
step("match", "EXPLAIN SELECT * FROM users WHERE id = 42;")

# Reason 4: a leading wildcard -> trigram index
q_wild = "EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%424242%';"
psql(q_wild)                      # warm-up run: measure warm caches, like a real app
step("wildcard", q_wild)
step("trgm_ext", "CREATE EXTENSION pg_trgm;"); steps["trgm_ext"]["out"] = steps["trgm_ext"]["out"] or "CREATE EXTENSION"
step("trgm_idx", "CREATE INDEX users_email_trgm_idx ON users USING gin (email gin_trgm_ops);")
steps["trgm_idx"]["out"] = steps["trgm_idx"]["out"] or "CREATE INDEX"
psql("ANALYZE users;")
psql(q_wild)                      # warm-up: first touch reads the new index from disk
step("wildcard_fast", q_wild)

# Reason 5: stale statistics (autovacuum off on this table so the stats stay stale for the demo)
psql("""
CREATE TABLE orders (id bigserial PRIMARY KEY, status text NOT NULL, total numeric NOT NULL)
  WITH (autovacuum_enabled = off);
INSERT INTO orders (status, total) SELECT 'pending', (random() * 100)::numeric(10,2) FROM generate_series(1, 500000);
CREATE INDEX orders_status_idx ON orders (status);
ANALYZE orders;
""")
step("ship", "UPDATE orders SET status = 'shipped' WHERE id > 10;")
q_pending = "EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';"
step("stale", q_pending)
step("restat", "VACUUM ANALYZE orders;"); steps["restat"]["out"] = steps["restat"]["out"] or "VACUUM"
step("fresh", q_pending)

# Reason 6: tiny tables
psql("""
CREATE TABLE countries (code text PRIMARY KEY, name text NOT NULL);
INSERT INTO countries VALUES ('US','United States'),('DE','Germany'),('FR','France'),('JP','Japan'),
  ('BR','Brazil'),('IN','India'),('CA','Canada'),('AU','Australia'),('MX','Mexico'),('ES','Spain'),
  ('IT','Italy'),('NL','Netherlands'),('SE','Sweden'),('KR','South Korea'),('NG','Nigeria');
ANALYZE countries;
""")
step("tiny", "EXPLAIN SELECT * FROM countries WHERE code = 'DE';")
step("tiny_size", "SELECT pg_size_pretty(pg_relation_size('countries')) AS table_size;")

# Prevention
step("rollback", "SELECT count(*) FROM users WHERE id < 10;",
     "BEGIN", "EXPLAIN ANALYZE DELETE FROM users WHERE id < 10", "ROLLBACK",
     show="BEGIN;\n=# EXPLAIN ANALYZE DELETE FROM users WHERE id < 10;\n=# ROLLBACK;\n=# SELECT count(*) FROM users WHERE id < 10;")
step("unused", "SELECT indexrelname AS index, idx_scan AS scans FROM pg_stat_user_indexes WHERE relname = 'users' ORDER BY idx_scan;")
psql("SET auto_explain.log_min_duration = '50ms'", "SELECT * FROM users WHERE lower(email) LIKE 'user42424%';")
logs = subprocess.run(["docker", "logs", "--tail", "40", name], capture_output=True, text=True)
plan_lines = [l for l in (logs.stdout + logs.stderr).splitlines() if "duration:" in l or "Scan" in l or "Filter" in l or "Query Text" in l]
steps["auto_explain"] = {"cmd": "SET auto_explain.log_min_duration = '50ms';  -- then any slow query", "out": "\n".join(plan_lines[-6:])}
step("version", "SELECT version();")
json.dump(steps, open("demo_output.json", "w"), indent=1)
for k, v in steps.items():
    print(f"== {k}\n$ {v['cmd']}\n{v['out']}\n")
PY