pulse: how the whole thing works

#!/usr/bin/env bash
# Ingest Claude Code daily usage from ccusage into DuckDB.
# Usage: ingest-daily.sh [SINCE_YYYYMMDD]
# Default SINCE = 8 days ago (always keeps the recent week fresh).

set -euo pipefail

# Cron-safe PATH. Node comes from nvm; source nvm.sh so we follow the
# `default` alias instead of pinning a version dir that rots on upgrade.
export NVM_DIR="${HOME}/.nvm"
set +eu
[ -s "$NVM_DIR/nvm.sh" ] && . "$NVM_DIR/nvm.sh" >/dev/null 2>&1
set -eu
export PATH="${HOME}/.local/bin:/usr/local/bin:/usr/bin:/bin:${PATH:-}"

DB="${HOME}/Documents/claude-stats/claude.duckdb"
DUCKDB="${HOME}/.local/bin/duckdb"
TMPDIR="$(mktemp -d)"
trap 'rm -rf "$TMPDIR"' EXIT

SINCE="${1:-$(date -d '8 days ago' +%Y%m%d)}"
CSV="${TMPDIR}/usage.csv"
PROJ_CSV="${TMPDIR}/projects.csv"

# (1) per-(date, model) totals  — feeds daily_usage
# ccusage v19 split agents under subcommands; top-level `daily` now aggregates
# all agents and drops modelBreakdowns. `claude daily` preserves the legacy shape.
npx --yes ccusage@latest claude daily --since "$SINCE" --json \
  | jq -r '
      .daily[] as $d
      | $d.modelBreakdowns[]
      | [
          $d.date,
          .modelName,
          (.inputTokens // 0),
          (.outputTokens // 0),
          (.cacheCreationTokens // 0),
          (.cacheReadTokens // 0),
          (.cost // 0)
        ]
      | @csv
    ' > "$CSV"

# (2) per-(project, date, model) breakdown — feeds project_daily_usage.
# Uses --instances to add the project_path dimension.
npx --yes ccusage@latest claude daily --since "$SINCE" --instances --breakdown --json \
  | jq -r '
      .projects
      | to_entries[] as $p
      | $p.value[] as $d
      | $d.modelBreakdowns[] as $m
      | [
          $p.key,
          $d.date,
          $m.modelName,
          ($m.inputTokens // 0),
          ($m.outputTokens // 0),
          ($m.cacheCreationTokens // 0),
          ($m.cacheReadTokens // 0),
          ($m.cost // 0)
        ]
      | @csv
    ' > "$PROJ_CSV"

ROWS=$(wc -l < "$CSV")
PROJ_ROWS=$(wc -l < "$PROJ_CSV")
if [[ "$ROWS" -eq 0 && "$PROJ_ROWS" -eq 0 ]]; then
  echo "[ingest] no rows from ccusage since $SINCE — nothing to do"
  exit 0
fi

"$DUCKDB" "$DB" <<SQL
CREATE OR REPLACE TEMP TABLE staging AS
SELECT * FROM read_csv('$CSV',
  header=false,
  columns={
    'date': 'DATE',
    'model': 'VARCHAR',
    'input_tokens': 'BIGINT',
    'output_tokens': 'BIGINT',
    'cache_creation_tokens': 'BIGINT',
    'cache_read_tokens': 'BIGINT',
    'cost': 'DOUBLE'
  }
);

INSERT OR REPLACE INTO daily_usage
  (date, model, input_tokens, output_tokens, cache_creation_tokens, cache_read_tokens, cost, ingested_at)
SELECT date, model, input_tokens, output_tokens, cache_creation_tokens, cache_read_tokens, cost, CURRENT_TIMESTAMP
FROM staging;

CREATE OR REPLACE TEMP TABLE staging_proj AS
SELECT * FROM read_csv('$PROJ_CSV',
  header=false,
  columns={
    'project_path': 'VARCHAR',
    'date': 'DATE',
    'model': 'VARCHAR',
    'input_tokens': 'BIGINT',
    'output_tokens': 'BIGINT',
    'cache_creation_tokens': 'BIGINT',
    'cache_read_tokens': 'BIGINT',
    'cost': 'DOUBLE'
  }
);

INSERT OR REPLACE INTO project_daily_usage
  (project_path, date, model, input_tokens, output_tokens, cache_creation_tokens, cache_read_tokens, cost, ingested_at)
SELECT project_path, date, model, input_tokens, output_tokens, cache_creation_tokens, cache_read_tokens, cost, CURRENT_TIMESTAMP
FROM staging_proj;

SELECT
  '[ingest] upserted ' || (SELECT COUNT(*) FROM staging)
  || ' daily rows + ' || (SELECT COUNT(*) FROM staging_proj)
  || ' project-day rows (since $SINCE)' AS msg;
SQL

#!/usr/bin/env python3
"""Ingest Claude Code session data into the local DuckDB.

Source: JSONL files at ~/.claude/projects/*/*.jsonl  (per-conversation detail).

Writes: conversations, conversation_tool_usage, conversation_skill_usage.
Idempotent via INSERT OR REPLACE.
"""

import csv, json, os, shutil, subprocess, sys, tempfile
from collections import Counter
from pathlib import Path

HOME = Path.home()
PROJECTS = HOME / ".claude" / "projects"
DB = HOME / "Documents" / "claude-stats" / "claude.duckdb"
DUCKDB = HOME / ".local" / "bin" / "duckdb"

# Only needs python3 (already running) + duckdb (absolute path above); no node.
os.environ["PATH"] = (
    f"{HOME}/.local/bin:/usr/local/bin:/usr/bin:/bin:" + os.environ.get("PATH", "")
)

def classify_path(path: Path):
    """Return (kind, project_path, parent_session_id) from a JSONL file path."""
    # Main:     .claude/projects/<project>/<uuid>.jsonl
    # Subagent: .claude/projects/<project>/<parent_uuid>/subagents/<agent-id>.jsonl
    parts = path.relative_to(PROJECTS).parts
    project_path = parts[0]
    if len(parts) == 2:
        return "main", project_path, None
    if len(parts) >= 3 and "subagents" in parts:
        parent_uuid = parts[1]
        return "subagent", project_path, parent_uuid
    return "main", project_path, None

def parse_jsonl(path: Path):
    session_id = path.stem
    kind, project_path, parent_sid = classify_path(path)
    started_at = ended_at = cwd = git_branch = summary = None
    n_user = n_assistant = n_tool_calls = 0
    max_ctx = 0
    max_ctx_model = ""
    tools: Counter = Counter()
    skills: Counter = Counter()

    with path.open(errors="replace") as f:
        for line in f:
            line = line.strip()
            if not line:
                continue
            try:
                d = json.loads(line)
            except json.JSONDecodeError:
                continue  # tolerate truncated/in-progress files

            ts = d.get("timestamp")
            if ts:
                if started_at is None or ts < started_at: started_at = ts
                if ended_at  is None or ts > ended_at:  ended_at = ts

            t = d.get("type")
            if t == "assistant":
                n_assistant += 1
                msg = d.get("message", {}) or {}
                usage = msg.get("usage") or {}
                if usage:
                    ctx = ((usage.get("input_tokens") or 0)
                         + (usage.get("cache_creation_input_tokens") or 0)
                         + (usage.get("cache_read_input_tokens") or 0))
                    if ctx > max_ctx:
                        max_ctx = ctx
                        max_ctx_model = msg.get("model") or ""
                for block in (msg.get("content") or []):
                    if isinstance(block, dict) and block.get("type") == "tool_use":
                        name = block.get("name") or "unknown"
                        tools[name] += 1
                        n_tool_calls += 1
                        if name == "Skill":
                            sk = (block.get("input") or {}).get("skill")
                            if sk: skills[sk] += 1

sql = f"""
CREATE OR REPLACE TEMP TABLE staging_conv AS
SELECT * FROM read_csv('{tmpdir}/conversations.csv',
  header=false,
  columns={{
    'session_id': 'VARCHAR', 'project_path': 'VARCHAR', 'cwd': 'VARCHAR',
    'git_branch': 'VARCHAR', 'kind': 'VARCHAR', 'parent_session_id': 'VARCHAR',
    'started_at': 'TIMESTAMP', 'ended_at': 'TIMESTAMP',
    'n_user_msgs': 'INTEGER', 'n_assistant_msgs': 'INTEGER',
    'n_tool_calls': 'INTEGER', 'max_context_tokens': 'BIGINT',
    'model': 'VARCHAR', 'summary': 'VARCHAR'
  }});

INSERT OR REPLACE INTO conversations
  (session_id, project_path, cwd, git_branch, kind, parent_session_id,
   started_at, ended_at, n_user_msgs, n_assistant_msgs, n_tool_calls,
   max_context_tokens, model, summary, ingested_at)
SELECT session_id, project_path, cwd, git_branch, kind, parent_session_id,
       started_at, ended_at, n_user_msgs, n_assistant_msgs, n_tool_calls,
       max_context_tokens, model, summary, CURRENT_TIMESTAMP
FROM staging_conv;
"""
proc = subprocess.run([str(DUCKDB), str(DB)], input=sql, text=True, capture_output=True)

def main():
    if not PROJECTS.exists():
        print("[ingest-sessions] no ~/.claude/projects yet — nothing to ingest")
        return

    tmpdir = Path(tempfile.mkdtemp(prefix="claude-stats-"))
    try:
        conv_rows, tool_rows, skill_rows = [], [], []
        for jsonl in sorted(PROJECTS.rglob("*.jsonl")):
            result = parse_jsonl(jsonl)
            if not result:
                continue
            conv, tools, skills = result
            conv_rows.append(conv)
            for tool_name, count in tools:
                tool_rows.append({"session_id": conv["session_id"], "tool_name": tool_name, "call_count": count})
            for skill_name, count in skills:
                skill_rows.append({"session_id": conv["session_id"], "skill_name": skill_name, "call_count": count})

        write_csv(conv_rows, [...], tmpdir / "conversations.csv")
        write_csv(tool_rows, [...], tmpdir / "tools.csv")
        write_csv(skill_rows, [...], tmpdir / "skills.csv")
        duckdb_load(str(tmpdir))
    finally:
        shutil.rmtree(tmpdir, ignore_errors=True)

CREATE TABLE IF NOT EXISTS daily_usage (
    date DATE NOT NULL,
    model VARCHAR NOT NULL,
    input_tokens BIGINT NOT NULL DEFAULT 0,
    output_tokens BIGINT NOT NULL DEFAULT 0,
    cache_creation_tokens BIGINT NOT NULL DEFAULT 0,
    cache_read_tokens BIGINT NOT NULL DEFAULT 0,
    cost DOUBLE NOT NULL DEFAULT 0.0,
    ingested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (date, model)
);
CREATE INDEX IF NOT EXISTS idx_daily_usage_date ON daily_usage(date);
CREATE INDEX IF NOT EXISTS idx_daily_usage_model ON daily_usage(model);

-- v2: one row per .jsonl file (main conversation OR subagent transcript)
CREATE TABLE IF NOT EXISTS conversations (
    session_id VARCHAR PRIMARY KEY,
    project_path VARCHAR,
    cwd VARCHAR,
    git_branch VARCHAR,
    kind VARCHAR NOT NULL DEFAULT 'main',     -- 'main' or 'subagent'
    parent_session_id VARCHAR,                -- NULL for main, parent UUID for subagent
    started_at TIMESTAMP,
    ended_at TIMESTAMP,
    n_user_msgs INTEGER NOT NULL DEFAULT 0,
    n_assistant_msgs INTEGER NOT NULL DEFAULT 0,
    n_tool_calls INTEGER NOT NULL DEFAULT 0,
    max_context_tokens BIGINT NOT NULL DEFAULT 0,
    model VARCHAR,
    summary VARCHAR,
    ingested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- v2: per-tool call counts per conversation
CREATE TABLE IF NOT EXISTS conversation_tool_usage (
    session_id VARCHAR,
    tool_name VARCHAR,
    call_count INTEGER NOT NULL,
    PRIMARY KEY (session_id, tool_name)
);

-- v3: per-skill call counts (extracted from Skill tool_use blocks)
CREATE TABLE IF NOT EXISTS conversation_skill_usage (
    session_id VARCHAR,
    skill_name VARCHAR,
    call_count INTEGER NOT NULL,
    PRIMARY KEY (session_id, skill_name)
);

-- v4: per-(project, date, model) cost breakdown from
--     `ccusage claude daily --instances --breakdown --json`.
-- Same write semantics as daily_usage: INSERT OR REPLACE on a rolling 8-day window
-- driven by ingest-daily.sh. Rows older than 8 days are immutable. Adds project_path
-- so per-project cost can be windowed AND reconciled against daily_usage to the
-- cent (sum over project_path = daily row).
CREATE TABLE IF NOT EXISTS project_daily_usage (
    project_path VARCHAR NOT NULL,
    date         DATE    NOT NULL,
    model        VARCHAR NOT NULL,
    input_tokens          BIGINT NOT NULL DEFAULT 0,
    output_tokens         BIGINT NOT NULL DEFAULT 0,
    cache_creation_tokens BIGINT NOT NULL DEFAULT 0,
    cache_read_tokens     BIGINT NOT NULL DEFAULT 0,
    cost         DOUBLE  NOT NULL DEFAULT 0.0,
    ingested_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (project_path, date, model)
);

#!/usr/bin/env bash
# Delete rows older than 365 days across all tables. Runs after each ingest.
set -euo pipefail

DB="${HOME}/Documents/claude-stats/claude.duckdb"
DUCKDB="${HOME}/.local/bin/duckdb"

"$DUCKDB" "$DB" <<'SQL'
DELETE FROM daily_usage         WHERE date < (CURRENT_DATE - INTERVAL 365 DAY);
DELETE FROM project_daily_usage WHERE date < (CURRENT_DATE - INTERVAL 365 DAY);

DELETE FROM conversations
WHERE ended_at IS NOT NULL
  AND CAST(ended_at AS DATE) < (CURRENT_DATE - INTERVAL 365 DAY);

-- Orphan tool/skill rows for conversations we just deleted
DELETE FROM conversation_tool_usage
WHERE session_id NOT IN (SELECT session_id FROM conversations);

DELETE FROM conversation_skill_usage
WHERE session_id NOT IN (SELECT session_id FROM conversations);

SELECT '[cleanup] daily_usage=' || (SELECT COUNT(*) FROM daily_usage)
  || ' project_daily=' || (SELECT COUNT(*) FROM project_daily_usage)
  || ' conversations=' || (SELECT COUNT(*) FROM conversations)
  || ' tool_rows=' || (SELECT COUNT(*) FROM conversation_tool_usage)
  || ' skill_rows=' || (SELECT COUNT(*) FROM conversation_skill_usage) AS msg;
SQL

# q() — run a DuckDB SQL query, capture JSON output into a shell var.
# Used everywhere: each chart gets its own q "..." block.
q() {
  "$DUCKDB" -json "$DB" "$1"
}

# Example: cost over time
COST_TS=$(q "
  SELECT
    (date::TIMESTAMP AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Kolkata' AS day,
    SUM(cost) AS cost
  FROM daily_usage
  WHERE date >= CURRENT_DATE - INTERVAL ${DAYS} DAY
  GROUP BY day
  ORDER BY day
")

cat > "$OUT" <<HTML
<!doctype html>
<html><head>
  <script src="https://cdn.plot.ly/plotly-2.35.0.min.js"></script>
  <style>${CSS}</style>
</head><body>
  <div id="chart-cost"></div>
  <div id="chart-tokens"></div>
  <script>
    const DATA = {
      cost_ts: ${COST_TS:-[]},
      tokens_ts: ${TOKENS_TS:-[]},
      top_projects: ${TOP_PROJECTS:-[]},
      // ... a dozen more
    };
    renderCost();
    renderTokens();
  </script>
</body></html>
HTML

    function renderCost() {
      const trace = {
        x: DATA.cost_ts.map(r => r.day),
        y: DATA.cost_ts.map(r => r.cost),
        type: 'bar',
        marker: { color: '#a78bfa' }
      };
      Plotly.newPlot('chart-cost', [trace], {
        paper_bgcolor: '#000',
        plot_bgcolor:  '#000',
        font: { color: '#f5f5f5' },
        margin: { t: 8, r: 8, b: 30, l: 50 }
      }, { displayModeBar: false });
    }

#!/bin/bash
# poll.sh — single battery + screen-state sample, append to DuckDB
set -euo pipefail

DB="$HOME/Documents/battery-stats/battery.duckdb"
DUCKDB="$HOME/.local/bin/duckdb"
BAT="/sys/class/power_supply/BAT0"
AC="/sys/class/power_supply/AC"

[ -d "$BAT" ] || { echo "no BAT0" >&2; exit 0; }

# raw sysfs reads (microunits)
energy_now_uwh=$(cat "$BAT/energy_now")
energy_full_uwh=$(cat "$BAT/energy_full")
energy_full_design_uwh=$(cat "$BAT/energy_full_design")
power_now_uw=$(cat "$BAT/power_now" 2>/dev/null || echo 0)
voltage_uv=$(cat "$BAT/voltage_now")
capacity=$(cat "$BAT/capacity")
cycles=$(cat "$BAT/cycle_count" 2>/dev/null || echo 0)
state=$(cat "$BAT/status" | tr '[:upper:]' '[:lower:]')
on_ac=$(cat "$AC/online" 2>/dev/null || echo 0)

# screen state — Wayland-native
idle_hint="false"
screensaver="false"
if [ -n "${XDG_SESSION_ID:-}" ]; then
    h=$(loginctl show-session "$XDG_SESSION_ID" -p IdleHint --value 2>/dev/null || echo "no")
    [ "$h" = "yes" ] && idle_hint="true"
fi
if command -v gdbus >/dev/null 2>&1; then
    s=$(timeout 2 gdbus call --session \
            --dest org.gnome.ScreenSaver \
            --object-path /org/gnome/ScreenSaver \
            --method org.gnome.ScreenSaver.GetActive 2>/dev/null || echo "(false,)")
    [[ "$s" == *"true"* ]] && screensaver="true"
fi
screen_active="true"
[ "$idle_hint" = "true" ] && screen_active="false"
[ "$screensaver" = "true" ] && screen_active="false"

brightness_pct="NULL"
for bl in /sys/class/backlight/*/; do
    if [ -d "$bl" ]; then
        cur=$(cat "$bl/brightness" 2>/dev/null || echo 0)
        max=$(cat "$bl/max_brightness" 2>/dev/null || echo 1)
        [ "$max" -gt 0 ] && brightness_pct=$(( cur * 100 / max ))
        break
    fi
done

# convert to base units
e_now=$(awk -v v="$energy_now_uwh" 'BEGIN{printf "%.6f", v/1000000}')
e_full=$(awk -v v="$energy_full_uwh" 'BEGIN{printf "%.6f", v/1000000}')
e_full_des=$(awk -v v="$energy_full_design_uwh" 'BEGIN{printf "%.6f", v/1000000}')
p_now=$(awk -v v="$power_now_uw" 'BEGIN{printf "%.6f", v/1000000}')
v_now=$(awk -v v="$voltage_uv" 'BEGIN{printf "%.6f", v/1000000}')

on_ac_bool="false"
[ "$on_ac" = "1" ] && on_ac_bool="true"

ts=$(date -u +"%Y-%m-%d %H:%M:%S")

"$DUCKDB" "$DB" <<SQL
INSERT INTO battery_samples
  (ts, energy_now_wh, energy_full_wh, energy_full_design_wh, power_now_w,
   voltage_v, capacity_pct, cycle_count, state, on_ac,
   screen_active, idle_hint, screensaver_active, brightness_pct)
VALUES
  ('$ts', $e_now, $e_full, $e_full_des, $p_now,
   $v_now, $capacity, $cycles, '$state', $on_ac_bool,
   $screen_active, $idle_hint, $screensaver, $brightness_pct)
ON CONFLICT (ts) DO NOTHING;
SQL

# Refresh derived tables so dashboard reflects every poll.
# Use flock so concurrent runs (e.g. manual + timer) don't collide on the DB.
LOCK="/tmp/battery-stats-aggregate.lock"
flock -n "$LOCK" -c "$HOME/Documents/battery-stats/bin/aggregate-daily.sh >/dev/null 2>&1" || true

-- wipe and rebuild derived tables (cheap; battery_samples is small)
DELETE FROM discharge_sessions;
DELETE FROM daily_battery;

-- step 1: tag each sample with session boundaries via on_ac transitions
WITH ordered AS (
    SELECT
        ts, energy_now_wh, energy_full_wh, energy_full_design_wh,
        capacity_pct, cycle_count, state, on_ac, screen_active,
        LAG(on_ac) OVER (ORDER BY ts) AS prev_on_ac,
        LAG(ts)    OVER (ORDER BY ts) AS prev_ts
    FROM battery_samples
),

boundaries AS (
    SELECT
        *,
        -- new session whenever AC goes from true→false, or first row that is on battery
        CASE
            WHEN on_ac = false AND (prev_on_ac IS NULL OR prev_on_ac = true) THEN 1
            ELSE 0
        END AS new_session,
        -- gap to next sample, used for SOT integration; cap at 600s to handle suspends
        LEAST(
            COALESCE(EXTRACT(EPOCH FROM (LEAD(ts) OVER (ORDER BY ts) - ts)), 300),
            600
        ) AS interval_seconds
    FROM ordered
),

tagged AS (
    SELECT *,
        SUM(new_session) OVER (ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_id
    FROM boundaries
    WHERE on_ac = false  -- only battery samples belong to sessions
)
INSERT INTO discharge_sessions
SELECT
    session_id,
    MIN(ts) AS start_ts, MAX(ts) AS end_ts,
    CAST(SUM(interval_seconds) AS BIGINT) AS duration_seconds,
    MAX(capacity_pct) AS start_pct, MIN(capacity_pct) AS end_pct,
    GREATEST(MAX(energy_now_wh) - MIN(energy_now_wh), 0) AS energy_used_wh,
    CAST(SUM(CASE WHEN screen_active THEN interval_seconds ELSE 0 END) AS BIGINT) AS sot_seconds,
    CAST(SUM(CASE WHEN NOT screen_active THEN interval_seconds ELSE 0 END) AS BIGINT) AS screen_off_seconds,
    -- avg drain across whole session (incl screen off)
    CASE WHEN SUM(interval_seconds) > 0
         THEN GREATEST(MAX(energy_now_wh) - MIN(energy_now_wh), 0) * 3600.0 / SUM(interval_seconds)
         ELSE NULL END AS avg_drain_w,
    -- avg drain ONLY when screen was on (the honest number)
    CASE WHEN SUM(CASE WHEN screen_active THEN interval_seconds ELSE 0 END) > 0
         THEN GREATEST(MAX(energy_now_wh) - MIN(energy_now_wh), 0) * 3600.0
              / SUM(CASE WHEN screen_active THEN interval_seconds ELSE 0 END)
         ELSE NULL END AS avg_drain_w_sot,
    -- extrapolated full-charge runtime at current SOT drain rate
    CASE WHEN SUM(CASE WHEN screen_active THEN interval_seconds ELSE 0 END) > 0
              AND GREATEST(MAX(energy_now_wh) - MIN(energy_now_wh), 0) > 0
         THEN MAX(energy_full_design_wh) /
              (GREATEST(MAX(energy_now_wh) - MIN(energy_now_wh), 0) * 3600.0
               / SUM(CASE WHEN screen_active THEN interval_seconds ELSE 0 END))
         ELSE NULL END AS projected_full_runtime_hours
FROM tagged
GROUP BY session_id
HAVING duration_seconds > 60
   AND (MAX(energy_now_wh) - MIN(energy_now_wh)) > 0.05;  -- skip phantoms

-- step 2: daily roll-up (group by IST date so a 23:00 IST session belongs to today)
INSERT INTO daily_battery
SELECT
    DATE_TRUNC('day', (start_ts AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Kolkata')::DATE AS date,
    CAST(SUM(sot_seconds) / 60 AS INTEGER) AS sot_minutes,
    CAST(SUM(duration_seconds) / 60 AS INTEGER) AS discharge_minutes,
    SUM(energy_used_wh) AS total_discharge_wh,
    CASE WHEN SUM(sot_seconds) > 0
         THEN SUM(energy_used_wh) * 3600.0 / SUM(sot_seconds)
         ELSE NULL END AS avg_drain_w_sot,
    COUNT(*) AS n_sessions,
    NULL AS cycle_count_eod, NULL AS energy_full_wh_eod, NULL AS health_pct
FROM discharge_sessions
GROUP BY date;

-- backfill end-of-day cycle / energy_full from raw samples
UPDATE daily_battery d SET
    cycle_count_eod    = s.cycle_count,
    energy_full_wh_eod = s.energy_full_wh,
    health_pct         = s.energy_full_wh / NULLIF(s.energy_full_design_wh, 0) * 100
FROM (
    SELECT
        DATE_TRUNC('day', (ts AT TIME ZONE 'UTC') AT TIME ZONE 'Asia/Kolkata')::DATE AS date,
        FIRST(cycle_count    ORDER BY ts DESC) AS cycle_count,
        FIRST(energy_full_wh ORDER BY ts DESC) AS energy_full_wh,
        FIRST(energy_full_design_wh ORDER BY ts DESC) AS energy_full_design_wh
    FROM battery_samples
    GROUP BY date
) s
WHERE d.date = s.date;

# UPower names files history-{rate,charge}-{MODEL}-{SERIAL}.dat. We read
# the kernel-reported model from sysfs and glob for matching files.
BATTERY_MODEL="${BATTERY_MODEL:-$(cat /sys/class/power_supply/BAT0/model_name 2>/dev/null | tr -d '[:space:]')}"

if [ -z "$BATTERY_MODEL" ]; then
    echo "could not detect battery model from /sys/class/power_supply/BAT0/model_name" >&2
    echo "set BATTERY_MODEL=<tag> to override (look in $HIST_DIR for history-rate-*.dat)" >&2
    exit 1
fi

RATE_FILE=$(ls "$HIST_DIR"/history-rate-"$BATTERY_MODEL"-*.dat 2>/dev/null | head -1 || true)
CHARGE_FILE=$(ls "$HIST_DIR"/history-charge-"$BATTERY_MODEL"-*.dat 2>/dev/null | head -1 || true)

# convert TSV "epoch  value  state" into a CSV that DuckDB can COPY
awk -F'\t' '$1 ~ /^[0-9]+$/ {
    cmd = "date -u -d @" $1 " +\"%Y-%m-%d %H:%M:%S\""
    cmd | getline ts
    close(cmd)
    printf "\"%s\",%s,\"%s\"\n", ts, $2, $3
}' "$RATE_FILE" > "$WORK/rate.csv"

CREATE TEMP TABLE staging_rate (ts TIMESTAMP, watts DOUBLE, state VARCHAR);
COPY staging_rate FROM '$WORK/rate.csv' (HEADER FALSE);
INSERT INTO upower_rate
SELECT ts, watts, state FROM staging_rate
ON CONFLICT (ts, state) DO NOTHING;

CREATE TEMP TABLE staging_charge (ts TIMESTAMP, percentage DOUBLE, state VARCHAR);
COPY staging_charge FROM '$WORK/charge.csv' (HEADER FALSE);
INSERT INTO upower_charge
SELECT ts, percentage, state FROM staging_charge
ON CONFLICT (ts, state) DO NOTHING;

#!/bin/bash
set -euo pipefail

DB="$HOME/Documents/battery-stats/battery.duckdb"
DUCKDB="$HOME/.local/bin/duckdb"
ASKPASS="${SUDO_ASKPASS:-$HOME/.local/bin/sudo-askpass}"

DURATION="${1:-30}"
shift || true
NOTES="${*:-}"

WORK=$(mktemp -d)
trap 'rm -rf "$WORK"' EXIT
CSV="$WORK/powertop.csv"

echo "running powertop for ${DURATION}s (sudo askpass)..."
SUDO_ASKPASS="$ASKPASS" sudo -A powertop --csv="$CSV" --time="$DURATION" >/dev/null 2>&1

python3 - "$CSV" "$WORK/parsed.csv" <<'PYEOF'
import csv, sys, re
src, dst = sys.argv[1], sys.argv[2]
rows = []
with open(src, newline='', encoding='utf-8', errors='replace') as f:
    in_section = False
    rank = 0
    for line in f:
        stripped = line.strip().strip(';').strip()
        if 'Top 10 Power Consumers' in line or 'Overview of Software Power Consumers' in line:
            in_section = True; rank = 0
            continue
        if in_section:
            if not stripped or stripped.startswith('___') or stripped.startswith('* * *'):
                if rank > 0: in_section = False
                continue
            parts = [p.strip() for p in line.split(';')]
            if len(parts) >= 3 and any(parts):
                desc = max(parts, key=len)
                # try to pull wattage / wakeups
                ...
                rank += 1
                rows.append((rank, desc[:200], parts[0][:100], wk, pw, 'process'))
                if rank >= 20: in_section = False
PYEOF

-- get next run_id
INSERT INTO powertop_runs (run_id, captured_at, duration_seconds, on_ac, notes)
SELECT
    COALESCE((SELECT MAX(run_id) FROM powertop_runs), 0) + 1,
    '$ts'::TIMESTAMP, $DURATION, $on_ac_bool,
    NULLIF('$NOTES', '');

CREATE TEMP TABLE staging (
    rank INTEGER, description VARCHAR, usage VARCHAR,
    wakeups_per_sec DOUBLE, pw_estimate_w DOUBLE, category VARCHAR
);
COPY staging FROM '$WORK/parsed.csv' (HEADER FALSE);

INSERT INTO powertop_top_processes
SELECT
    (SELECT MAX(run_id) FROM powertop_runs) AS run_id,
    rank, description, usage, wakeups_per_sec, pw_estimate_w, category
FROM staging
ON CONFLICT (run_id, rank, description) DO NOTHING;

CREATE TABLE IF NOT EXISTS battery_samples (
    ts                  TIMESTAMP NOT NULL,
    energy_now_wh       DOUBLE NOT NULL,
    energy_full_wh      DOUBLE NOT NULL,
    energy_full_design_wh DOUBLE NOT NULL,
    power_now_w         DOUBLE NOT NULL,        -- positive = drain or charge magnitude
    voltage_v           DOUBLE NOT NULL,
    capacity_pct        INTEGER NOT NULL,
    cycle_count         INTEGER,
    state               VARCHAR NOT NULL,       -- charging | discharging | full | not-charging
    on_ac               BOOLEAN NOT NULL,
    screen_active       BOOLEAN NOT NULL,       -- IdleHint=no AND ScreenSaver inactive
    idle_hint           BOOLEAN NOT NULL,       -- raw IdleHint
    screensaver_active  BOOLEAN NOT NULL,
    brightness_pct      INTEGER,
    PRIMARY KEY (ts)
);

-- discharge sessions (derived nightly): one row per "unplug to plug-in or shutdown"
CREATE TABLE IF NOT EXISTS discharge_sessions (
    session_id          INTEGER PRIMARY KEY,
    start_ts            TIMESTAMP NOT NULL,
    end_ts              TIMESTAMP NOT NULL,
    duration_seconds    BIGINT NOT NULL,
    start_pct           INTEGER NOT NULL,
    end_pct             INTEGER NOT NULL,
    energy_used_wh      DOUBLE NOT NULL,
    sot_seconds         BIGINT NOT NULL,            -- screen-on-time during this session
    screen_off_seconds  BIGINT NOT NULL,
    avg_drain_w         DOUBLE,                     -- across whole session
    avg_drain_w_sot     DOUBLE,                     -- only when screen was on (the honest number)
    projected_full_runtime_hours DOUBLE             -- design / avg_drain_w_sot
);

-- daily roll-up: one row per calendar day
CREATE TABLE IF NOT EXISTS daily_battery (
    date                DATE PRIMARY KEY,
    sot_minutes         INTEGER NOT NULL,
    discharge_minutes   INTEGER NOT NULL,
    total_discharge_wh  DOUBLE NOT NULL,
    avg_drain_w_sot     DOUBLE,
    n_sessions          INTEGER NOT NULL,
    cycle_count_eod     INTEGER,                    -- end-of-day reading
    energy_full_wh_eod  DOUBLE,                     -- for decay tracking
    health_pct          DOUBLE                      -- energy_full / energy_full_design * 100
);