#!/bin/bash
# analyze-usage - Unified usage analyzer for AI coding assistants
#
# Loads Claude Code, Codex, Cursor, and Pi logs into a persistent DuckDB
# database for analysis via SQL queries.
#
# Usage:
#   analyze-usage              # Auto-detect changes, incremental update, show summary
#   analyze-usage --help       # Detailed help for agents/users
#   analyze-usage --schema     # Show database schema with example queries
#   analyze-usage query "SQL"  # Run a SQL query
#   analyze-usage report        # Generate aggregate-only report JSON
#   analyze-usage update       # Explicit incremental update
#   analyze-usage reload       # Force reload all data

set -euo pipefail
umask 077

VERSION="2.0.0"

# ============================================================================
# Configuration
# ============================================================================

SCRIPT_DIR=$(cd -- "$(dirname "${BASH_SOURCE[0]}")" && pwd)
SKILL_DIR=$(cd -- "$SCRIPT_DIR/.." && pwd)
REFERENCE_SCHEMA_PATH="$SKILL_DIR/references/canonical-agent-schema.duckdb.sql"
REPORT_SCRIPT_PATH="$SCRIPT_DIR/generate-report.py"
INSTALLED_SCHEMA_PATH="${XDG_DATA_HOME:-$HOME/.local/share}/analyze-usage/canonical-agent-schema.duckdb.sql"

DB_PATH="${ANALYZE_USAGE_DB:-$HOME/.local/share/analyze-usage/usage.duckdb}"
DB_DIR=$(dirname "$DB_PATH")

# Source paths
CLAUDE_PROJECTS="${CLAUDE_PROJECTS_DIR:-$HOME/.claude/projects}"

if [ -n "${CURSOR_USER_DIR:-}" ]; then
    CURSOR_BASE="$CURSOR_USER_DIR"
else
    case "$(uname -s)" in
        Darwin)
            CURSOR_BASE="$HOME/Library/Application Support/Cursor/User"
            ;;
        Linux)
            CURSOR_BASE="$HOME/.config/Cursor/User"
            ;;
        *)
            CURSOR_BASE=""
            ;;
    esac
fi

CURSOR_WORKSPACE="${CURSOR_BASE:+$CURSOR_BASE/workspaceStorage}"

CODEX_HOME_DIR="${CODEX_HOME:-$HOME/.codex}"
CODEX_SESSIONS_DIR="$CODEX_HOME_DIR/sessions"
CODEX_ARCHIVED_DIR="$CODEX_HOME_DIR/archived_sessions"
CODEX_SESSION_INDEX_FILE="$CODEX_HOME_DIR/session_index.jsonl"

PI_AGENT_HOME="${PI_AGENT_DIR:-$HOME/.pi/agent}"
PI_SESSIONS_DIR="$PI_AGENT_HOME/sessions"

# ============================================================================
# Prerequisite checks
# ============================================================================

if ! command -v duckdb &> /dev/null; then
    cat >&2 << 'EOF'
Error: DuckDB is required but not installed.

Install with:
  macOS:  brew install duckdb
  Linux:  curl -LO https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
          unzip duckdb_cli-linux-amd64.zip && sudo mv duckdb /usr/local/bin/
EOF
    exit 1
fi

# ============================================================================
# Help Documentation
# ============================================================================

show_help() {
    cat << 'EOF'
analyze-usage - Unified usage analyzer for AI coding assistants

DESCRIPTION
    Loads conversation and tool usage logs from Claude Code, Codex, Cursor, and
    Pi into a persistent DuckDB database. Enables SQL-based analysis of AI
    coding patterns across all four tools.

USAGE
    analyze-usage                     Auto-detect changes, update, show summary
    analyze-usage --help              Show this help message
    analyze-usage --schema            Show database schema with example queries
    analyze-usage --version           Show version
    analyze-usage update              Explicit incremental update
    analyze-usage reload              Force reload all data from source logs
    analyze-usage query "SQL"         Execute a SQL query against the database
    analyze-usage report [OPTIONS]     Generate aggregate-only report JSON
    analyze-usage search "query"      Search conversation content
    analyze-usage shell               Open interactive DuckDB shell

COMMANDS
    (default)   If no database, does a full load. Otherwise auto-detects new
                or changed files and incrementally updates. Shows summary.

    update      Explicit incremental update (same as default auto-detect).

    reload      Drops and recreates all tables from source log files.
                Creates a timestamped backup first.

    query       Execute arbitrary SQL. Results printed to stdout.
                Use --schema to see available tables and columns.

    report      Generate a deterministic aggregate report from the normalized
                ledger. By default it covers the full observed archive.
                Options:
                  --from T      Inclusive ISO-8601 boundary with UTC offset
                  --to T        Exclusive ISO-8601 boundary with UTC offset
                  --output P    Write JSON to a file instead of stdout

    search      Search conversation content (messages table).
                Flags:
                  --thinking    Search reasoning traces instead of content
                  --all         Search both content and thinking
                  --fts         Use BM25 full-text search (covers both)
                  -n N          Limit results (default 10)
                  --user        User messages only
                  --asst        Assistant messages only
                  --repo X      Filter to repository name
                  --since T     Time filter (7d, 4w, or YYYY-MM-DD)

    shell       Opens an interactive DuckDB REPL connected to the database.
                Useful for exploratory analysis.

ENVIRONMENT VARIABLES
    ANALYZE_USAGE_DB      Path to DuckDB database file
                            Default: ~/.local/share/analyze-usage/usage.duckdb

    CLAUDE_PROJECTS_DIR     Path to Claude Code projects directory
                            Default: ~/.claude/projects

    CODEX_HOME              Path to Codex home directory
                            Default: ~/.codex

    CURSOR_USER_DIR         Cursor User directory override
                            Default: platform-specific Cursor/User path

    PI_AGENT_DIR            Path to Pi agent directory
                            Default: ~/.pi/agent

DATA SOURCES
    Claude Code     ~/.claude/projects/*/*.jsonl
                    Contains: tool invocations, messages, token usage,
                    system events, queue operations, PR links

    Codex           ~/.codex/sessions/**/*.jsonl
                    ~/.codex/archived_sessions/*.jsonl
                    ~/.codex/session_index.jsonl
                    Contains: tool calls, user/assistant messages,
                    turn token counts, developer prompts, thread titles

    Cursor          ~/Library/Application Support/Cursor/User/workspaceStorage/*/state.vscdb
                    Contains: prompts, chat history, composer sessions

    Pi              ~/.pi/agent/sessions/*/*.jsonl
                    Contains: user/assistant messages, tool calls, per-message
                    token usage with harness-recorded dollar cost, session
                    titles, model/provider changes

EXAMPLES
    # First run - loads data and shows summary
    analyze-usage

    # See what tables and columns are available
    analyze-usage --schema

    # Count tool usage by type
    analyze-usage query "SELECT tool_name, COUNT(*) FROM claude_tools GROUP BY 1 ORDER BY 2 DESC"

    # Daily usage across all tools
    analyze-usage query "SELECT * FROM daily_summary ORDER BY date DESC LIMIT 14"

    # Generate a fixed-window report with explicit UTC boundaries
    analyze-usage report --from 2026-06-13T17:00:00Z --to 2026-07-12T17:00:00Z --output report.json

    # Search conversation content
    analyze-usage search "memory"

    # BM25-ranked full-text search
    analyze-usage search "memory" --fts

    # Search with filters
    analyze-usage search "refactor" --repo bertram-chat --since 7d --user

    # Turn durations
    analyze-usage query "SELECT * FROM turn_durations ORDER BY duration_ms DESC LIMIT 10"

    # Session overview with summaries
    analyze-usage query "SELECT session_id, repo_name, summary FROM session_overview LIMIT 10"

    # Interactive exploration
    analyze-usage shell

FOR AI AGENTS
    To analyze AI coding usage programmatically:

    1. Run: analyze-usage --schema
       This outputs the complete database schema with column types and descriptions.

    2. Write SQL queries against the tables described in the schema.

    3. Execute with: analyze-usage query "YOUR SQL HERE"

    The database uses standard SQL. DuckDB supports modern SQL features including
    window functions, CTEs, JSON functions, and more.

SEE ALSO
    analyze-usage --schema    Complete schema documentation
    https://duckdb.org/docs/    DuckDB SQL reference
EOF
}

show_schema() {
    cat << 'EOF'
analyze-usage DATABASE SCHEMA
===============================

This document describes all tables in the analyze-usage database.
Use this information to construct SQL queries for analysis.

The database contains two schema layers:
1. Loader-native tables such as `claude_tools` and `messages`
2. Canonical `agent_*` tables provisioned from `references/canonical-agent-schema.duckdb.sql`

DATABASE LOCATION
    Default: ~/.local/share/analyze-usage/usage.duckdb
    Override: ANALYZE_USAGE_DB environment variable

================================================================================
CANONICAL REFERENCE TABLES
================================================================================
The canonical `agent_*` tables follow Rails-style naming conventions:

    id                  Primary key on every table
    {table}_id          Foreign keys reference `id`
    external_*          Provider-native ids
    created_at          Insert timestamp
    updated_at          Loader-managed modification timestamp

These tables are provisioned as an empty reference schema. Current imports are
stored in the harness-specific tables and views below.

Core canonical tables:
    agent_sessions      Normalized session records
    agent_raw_events    Raw imported source events with provenance
    agent_contexts      Model/cwd/provider context changes within sessions
    agent_events        Normalized message/tool/system events
    agent_parts         Structured parts for each event
    agent_tool_calls    Tool invocation records
    agent_tool_results  Tool outputs / errors
    agent_tokens        Token and cost metrics per event

================================================================================
TABLE: claude_tools
================================================================================
Primary table for Claude Code tool invocations. Each row represents one tool
call made by Claude during a coding session.

COLUMNS:
    timestamp       TIMESTAMP   When the tool was invoked (ISO 8601)
    session_id      VARCHAR     Unique session identifier
    project_dir     VARCHAR     Working directory / project path
    interface       VARCHAR     Wrapper/interface when detectable (e.g. conductor, cli)
    tool_name       VARCHAR     Name of tool (see TOOL NAMES below)
    context         VARCHAR     Tool-specific context (command, file path, etc.)
    input_tokens    INTEGER     Reserved legacy column; new tool rows use NULL
    output_tokens   INTEGER     Reserved legacy column; new tool rows use NULL
    repo_name       VARCHAR     Repository name (extracted from path)
    worktree_branch VARCHAR     Branch name if worktree, NULL otherwise
    is_worktree     BOOLEAN     TRUE if path is a git/conductor worktree
    source_file     VARCHAR     JSONL file this record was loaded from

TOOL NAMES (tool_name column):
    Bash            Shell command execution
    Read            File reading
    Write           File creation
    Edit            File modification
    MultiEdit       Multiple file edits
    Glob            File pattern matching
    Grep            Text search
    LS              Directory listing
    Task            Subagent/background task
    Skill           Skill invocation (context = skill name)
    WebSearch       Web search
    WebFetch        URL fetching
    TodoRead        Todo list reading
    TodoWrite       Todo list writing
    NotebookRead    Jupyter notebook reading
    NotebookEdit    Jupyter notebook editing

EXAMPLE QUERIES:

    -- Most used tools
    SELECT tool_name, COUNT(*) as uses
    FROM claude_tools
    GROUP BY tool_name
    ORDER BY uses DESC;

    -- Skill usage breakdown
    SELECT context as skill_name, COUNT(*) as uses
    FROM claude_tools
    WHERE tool_name = 'Skill'
    GROUP BY context
    ORDER BY uses DESC;

================================================================================
TABLE: claude_sessions
================================================================================
Metadata about Claude Code sessions.

COLUMNS:
    session_id      VARCHAR     Unique session identifier
    project_dir     VARCHAR     Working directory
    repo_name       VARCHAR     Repository name
    interface       VARCHAR     Wrapper/interface when detectable
    worktree_branch VARCHAR     Branch name if worktree
    is_worktree     BOOLEAN     TRUE if worktree
    started_at      TIMESTAMP   First activity timestamp
    ended_at        TIMESTAMP   Last activity timestamp
    tool_count      INTEGER     Number of tool invocations
    unique_tools    INTEGER     Number of distinct tools used

================================================================================
TABLE: messages
================================================================================
Conversation content from Claude Code, Codex, Cursor, and Pi. Each row
represents one message turn (user or assistant) with extracted text and
thinking content.

COLUMNS:
    uuid            VARCHAR     Message UUID (from JSONL or generated)
    parent_uuid     VARCHAR     For conversation threading
    session_id      VARCHAR     Session identifier
    role            VARCHAR     'user' or 'assistant'
    harness         VARCHAR     'claude_code', 'codex', 'cursor', or 'pi'
    interface       VARCHAR     Wrapper/interface when detectable
    model           VARCHAR     Model used (NULL for user messages)
    content         VARCHAR     User-facing text (concatenated text blocks)
    thinking        VARCHAR     Reasoning traces (concatenated thinking blocks)
    timestamp       TIMESTAMP   When the message was sent
    project_dir     VARCHAR     Working directory
    git_branch      VARCHAR     Git branch (if available)
    repo_name       VARCHAR     Repository name
    worktree_branch VARCHAR     Branch if worktree
    is_worktree     BOOLEAN     TRUE if worktree path
    is_sidechain    BOOLEAN     TRUE if subagent message
    input_tokens    INTEGER     Input tokens (assistant only)
    output_tokens   INTEGER     Output tokens (assistant only)
    cache_write_tokens INTEGER  Cache write tokens
    cache_read_tokens  INTEGER  Cache read tokens
    tool_use_count  INTEGER     Number of tool_use blocks in this turn
    source_file     VARCHAR     JSONL file this record was loaded from
    search_id       VARCHAR     Analyzer-generated unique key used by the FTS index

================================================================================
TABLE: system_events
================================================================================
System-level events from Claude Code sessions. Includes turn durations,
API errors, stop hook summaries, and compact boundaries.

COLUMNS:
    uuid            VARCHAR     Event UUID (primary key)
    session_id      VARCHAR     Session identifier
    subtype         VARCHAR     Event subtype (see below)
    timestamp       TIMESTAMP   When the event occurred
    project_dir     VARCHAR     Working directory
    git_branch      VARCHAR     Git branch
    duration_ms     INTEGER     Turn duration in milliseconds (turn_duration only)
    version         VARCHAR     Claude Code version
    is_sidechain    BOOLEAN     TRUE if subagent event
    source_file     VARCHAR     JSONL file this record was loaded from
    repo_name       VARCHAR     Repository name

SUBTYPES:
    turn_duration       Response timing data (duration_ms populated)
    stop_hook_summary   Hook execution summary
    api_error           API error event
    compact_boundary    Context compaction marker

================================================================================
TABLE: queue_operations
================================================================================
User inputs queued while the assistant is working.

COLUMNS:
    session_id      VARCHAR     Session identifier
    timestamp       TIMESTAMP   When the operation occurred
    operation       VARCHAR     Operation type (e.g., 'enqueue')
    content         VARCHAR     The queued text
    source_file     VARCHAR     JSONL file this record was loaded from

================================================================================
TABLE: pr_links
================================================================================
Session-to-PR mappings from Claude Code.

COLUMNS:
    session_id      VARCHAR     Session identifier
    timestamp       TIMESTAMP   When the link was created
    pr_number       INTEGER     Pull request number
    pr_url          VARCHAR     Pull request URL
    pr_repository   VARCHAR     Repository identifier
    source_file     VARCHAR     JSONL file this record was loaded from

================================================================================
TABLE: _sessions_index
================================================================================
Session metadata from sessions-index.json files. Contains summaries and
first prompts that are not available in the JSONL logs.

COLUMNS:
    session_id      VARCHAR     Session identifier (primary key)
    project_path    VARCHAR     Project directory path
    first_prompt    VARCHAR     First user prompt in the session
    summary         VARCHAR     AI-generated session summary
    message_count   INTEGER     Number of messages
    git_branch      VARCHAR     Git branch
    created_at      TIMESTAMP   Session creation time
    modified_at     TIMESTAMP   Last modification time

================================================================================
TABLE: _loaded_files
================================================================================
Internal tracking table for incremental loading. Stores file modification
times to detect changes.

COLUMNS:
    file_path       VARCHAR     Absolute file path (primary key)
    mtime_ns        BIGINT      File modification time used as a fast metadata check
    size_bytes      BIGINT      Source file size
    source_kind     VARCHAR     claude, codex, cursor, or unknown
    loaded_at       TIMESTAMP   When the file was last processed

================================================================================
VIEW: turn_durations
================================================================================
Response timing from system events.

COLUMNS:
    session_id, timestamp, duration_ms, repo_name, git_branch

EXAMPLE:
    SELECT * FROM turn_durations ORDER BY duration_ms DESC LIMIT 10;

================================================================================
VIEW: api_errors
================================================================================
API error events from system records.

EXAMPLE:
    SELECT * FROM api_errors ORDER BY timestamp DESC;

================================================================================
VIEW: session_overview
================================================================================
Sessions joined with index metadata (summary, first_prompt).

COLUMNS:
    session_id, repo_name, started_at, ended_at, message_count,
    user_messages, assistant_messages, summary, first_prompt, git_branch,
    total_input_tokens, total_output_tokens

EXAMPLE:
    SELECT session_id, repo_name, summary FROM session_overview
    WHERE summary IS NOT NULL ORDER BY started_at DESC LIMIT 10;

================================================================================
TABLE: cursor_prompts
================================================================================
User prompts sent to Cursor AI.

COLUMNS:
    timestamp       TIMESTAMP   When the prompt was sent
    workspace_id    VARCHAR     Cursor workspace identifier (MD5 hash)
    workspace_path  VARCHAR     Resolved workspace directory path
    prompt_text     VARCHAR     The user's prompt text

================================================================================
TABLE: cursor_workspaces
================================================================================
Cursor workspace metadata.

COLUMNS:
    workspace_id    VARCHAR     Unique workspace identifier (MD5 hash)
    workspace_path  VARCHAR     Directory path (decoded from workspace.json)
    prompt_count    INTEGER     Number of prompts in this workspace
    db_path         VARCHAR     Path to state.vscdb file

================================================================================
TABLE: codex_session_index
================================================================================
Thread titles from Codex session_index.jsonl.

COLUMNS:
    session_id      VARCHAR     Codex session / thread identifier
    thread_name     VARCHAR     Current thread title
    updated_at      TIMESTAMP   Last title update timestamp
    source_file     VARCHAR     Source session_index.jsonl path

================================================================================
TABLE: codex_session_meta
================================================================================
Session-level metadata from Codex session_meta records.

COLUMNS:
    session_id      VARCHAR     Codex session identifier
    started_at      TIMESTAMP   Session start timestamp
    project_dir     VARCHAR     Working directory
    repo_name       VARCHAR     Repository name
    worktree_branch VARCHAR     Branch name if worktree
    is_worktree     BOOLEAN     TRUE if worktree path
    source          VARCHAR     Codex source ('cli', 'vscode', etc.)
    originator      VARCHAR     Codex originator string
    cli_version     VARCHAR     Codex version
    model_provider  VARCHAR     Model provider
    git_branch      VARCHAR     Git branch from session metadata
    git_sha         VARCHAR     Git commit from session metadata
    git_origin_url  VARCHAR     Git remote URL
    source_file     VARCHAR     Source transcript path

================================================================================
TABLE: codex_tools
================================================================================
Tool invocations extracted from Codex response_item records.

COLUMNS:
    timestamp       TIMESTAMP   When the tool was invoked
    session_id      VARCHAR     Codex session identifier
    project_dir     VARCHAR     Working directory
    model           VARCHAR     Latest model seen in turn context for the session
    tool_name       VARCHAR     Tool name (exec_command, apply_patch, etc.)
    context         VARCHAR     Parsed command / path / raw argument preview
    repo_name       VARCHAR     Repository name
    worktree_branch VARCHAR     Branch name if worktree
    is_worktree     BOOLEAN     TRUE if worktree path
    source_file     VARCHAR     Source transcript path

================================================================================
TABLE: codex_token_counts
================================================================================
Per-turn token usage snapshots from Codex token_count events.

COLUMNS:
    timestamp               TIMESTAMP   When the token event was emitted
    session_id              VARCHAR     Codex session identifier
    project_dir             VARCHAR     Working directory
    model                   VARCHAR     Latest model seen for the session
    reasoning_effort        VARCHAR     Latest reasoning effort seen
    input_tokens            BIGINT      Last-turn input tokens
    cached_input_tokens     BIGINT      Last-turn cached input tokens
    output_tokens           BIGINT      Last-turn output tokens
    reasoning_output_tokens BIGINT      Last-turn reasoning tokens
    total_tokens            BIGINT      Last-turn total tokens
    model_context_window    BIGINT      Context window size
    source_file             VARCHAR     Source transcript path

================================================================================
TABLE: codex_developer_messages
================================================================================
Developer-role instruction payloads from Codex transcripts. These are stored
separately so they remain queryable without polluting default conversation
search and message counts.

COLUMNS:
    session_id      VARCHAR     Codex session identifier
    timestamp       TIMESTAMP   When the developer message appeared
    project_dir     VARCHAR     Working directory
    model           VARCHAR     Latest model seen for the session
    content         VARCHAR     Full developer instruction text
    source_file     VARCHAR     Source transcript path

================================================================================
TABLE: pi_session_meta
================================================================================
Session-level metadata from Pi session records.

COLUMNS:
    session_id      VARCHAR     Pi session identifier
    started_at      TIMESTAMP   Session start timestamp
    project_dir     VARCHAR     Working directory (session cwd)
    title           VARCHAR     Session name from session_info events
    version         INTEGER     Pi session log format version
    repo_name       VARCHAR     Repository name
    worktree_branch VARCHAR     Branch name if worktree
    is_worktree     BOOLEAN     TRUE if worktree path
    source_file     VARCHAR     Source transcript path

================================================================================
TABLE: pi_tools
================================================================================
Tool invocations extracted from Pi assistant message toolCall blocks.

COLUMNS:
    timestamp       TIMESTAMP   When the tool was invoked
    session_id      VARCHAR     Pi session identifier
    project_dir     VARCHAR     Working directory
    model           VARCHAR     Model recorded on the assistant message
    tool_name       VARCHAR     Tool name (read, bash, edit, etc.)
    context         VARCHAR     Parsed command / path / raw argument preview
    repo_name       VARCHAR     Repository name
    worktree_branch VARCHAR     Branch name if worktree
    is_worktree     BOOLEAN     TRUE if worktree path
    source_file     VARCHAR     Source transcript path

================================================================================
TABLE: pi_usage
================================================================================
Per-assistant-message token usage and harness-recorded dollar cost. Pi logs
both token counts and the dollars the harness computed at request time, so
cost columns here are recorded values, not API-equivalent estimates.

COLUMNS:
    usage_id                VARCHAR     Stable per-message identifier
    timestamp               TIMESTAMP   Assistant message timestamp
    session_id              VARCHAR     Pi session identifier
    project_dir             VARCHAR     Working directory
    provider                VARCHAR     Provider id ('anthropic', 'openai-codex', ...)
    model                   VARCHAR     Model identifier
    thinking_level          VARCHAR     Latest thinking level seen for the session
    input_tokens            BIGINT      Uncached input tokens
    cached_input_tokens     BIGINT      Cache-read input tokens
    cache_write_tokens      BIGINT      Cache-write input tokens
    output_tokens           BIGINT      Output tokens
    reasoning_tokens        BIGINT      Reasoning subset of output tokens
    total_tokens            BIGINT      Total as recorded by Pi
    input_cost_usd          DOUBLE      Recorded input dollars
    cached_input_cost_usd   DOUBLE      Recorded cache-read dollars
    cache_write_cost_usd    DOUBLE      Recorded cache-write dollars
    output_cost_usd         DOUBLE      Recorded output dollars
    cost_usd                DOUBLE      Recorded total dollars
    stop_reason             VARCHAR     stop / toolUse / error / aborted
    source_file             VARCHAR     Source transcript path

================================================================================
VIEW: daily_summary
================================================================================
Aggregated daily usage across all imported harnesses.

COLUMNS:
    date            DATE        The date
    claude_tools    INTEGER     Number of Claude Code tool invocations
    codex_tools     INTEGER     Number of Codex tool invocations
    cursor_prompts  INTEGER     Number of Cursor prompts
    pi_tools        INTEGER     Number of Pi tool invocations
    total           INTEGER     Combined total

================================================================================
VIEW: tool_summary
================================================================================
Aggregated tool usage statistics for harnesses with tool-level detail.

COLUMNS:
    source          VARCHAR     Harness name ('claude_code', 'codex', or 'pi')
    tool_name       VARCHAR     Name of the tool
    uses            INTEGER     Total invocations
    pct_of_source   DECIMAL     Percentage within that harness
    first_used      TIMESTAMP   Earliest usage
    last_used       TIMESTAMP   Most recent usage

================================================================================
================================================================================
                         UNIFIED VIEWS (Cross-Tool Analysis)
================================================================================
================================================================================

VIEW: interactions          Tool calls for Claude/Codex/Pi and prompts for Cursor
VIEW: daily_by_source       Daily counts separated by tool
VIEW: weekly_summary        Weekly aggregation by source
VIEW: project_activity      Project-level summary across all harnesses
VIEW: repo_activity         Repository-level (aggregates worktrees)
VIEW: category_breakdown    Usage by category (tool names / prompts)
VIEW: session_summary       Unified session metrics
VIEW: peak_hours            Hours with the most recorded activity
VIEW: hourly_activity       Time-series at hourly granularity
VIEW: recent_interactions   Last 100 interactions

================================================================================
                      CONVERSATION VIEWS (Message Search)
================================================================================

VIEW: conversation_search   Messages with content/thinking previews
VIEW: session_messages      Per-session aggregation with topic
VIEW: recent_conversations  Last 50 sessions
VIEW: conversation_pairs    User/assistant turns joined on parent_uuid
VIEW: message_stats         Daily message volume by harness/role

================================================================================
                         COST VIEWS
================================================================================

TABLE: model_pricing        Editable known-model Claude API rates
TABLE: codex_model_pricing  Editable known-model OpenAI API-equivalent rates
VIEW: usage_with_cost       One Claude assistant turn per row with pricing status
VIEW: codex_usage_with_cost One Codex token snapshot per row with pricing status
VIEW: pi_usage_with_cost    One Pi assistant message per row, recorded dollars
VIEW: provider_usage_with_cost Unified Claude/Codex/Pi token, cost, cache rows
VIEW: provider_cost_summary Provider/harness/model token and cost totals
VIEW: cache_efficiency_summary Cache utilization and estimated savings
VIEW: cost_summary          Backward-compatible Claude repo/model totals

provider_usage_with_cost normalizes these token columns:
    uncached_input_tokens   Input charged at the ordinary input rate
    cached_input_tokens     Input served from prompt cache
    cache_write_tokens      Cache population tokens (zero for Codex)
    output_tokens           Output tokens; Codex includes reasoning output
    reasoning_output_tokens Reasoning subset (do not add to output again)

Cost columns separate input/cache-write/cache-read/output components, the
cost_usd total, a cost_without_cache_usd baseline, and cache_savings_usd.
pricing_status distinguishes how dollars were derived: 'priced' rows are
API-equivalent estimates from the editable pricing tables (Claude, Codex);
'native' rows carry dollars recorded by the harness itself (Pi). Codex
subscription and credit purchases are not in local logs. Unknown/internal
models remain NULL-cost with unknown_model status.

================================================================================
USEFUL SQL PATTERNS
================================================================================

-- Time filtering (last 7 days)
WHERE timestamp >= CURRENT_DATE - INTERVAL '7 days'

-- Case-insensitive search
WHERE column ILIKE '%search_term%'

-- Aggregate by hour of day
SELECT EXTRACT(HOUR FROM timestamp) as hour, COUNT(*)
FROM claude_tools GROUP BY hour ORDER BY hour;

For full SQL reference: https://duckdb.org/docs/sql/introduction
EOF
}

# ============================================================================
# Database Operations
# ============================================================================

ensure_db_dir() {
    mkdir -p "$DB_DIR"
    chmod 700 "$DB_DIR" 2>/dev/null || true
}

db_exists() {
    [ -f "$DB_PATH" ]
}

# Check if data needs loading (no tables or tables empty)
needs_load() {
    if ! db_exists; then
        return 0
    fi

    local table_count
    table_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='main';" 2>/dev/null || echo "0")

    [ "$table_count" -eq 0 ]
}

# Backup database before load/reload
backup_db() {
    if db_exists; then
        local backup
        backup="${DB_PATH}.bak.$(date +%Y%m%d_%H%M%S)"
        cp "$DB_PATH" "$backup"
        chmod 600 "$backup" 2>/dev/null || true
        echo "  Backup: $backup"
    fi
}

sql_escape_literal() {
    local value="$1"
    local quote="'"
    local escaped_quote="''"
    value=${value//"$quote"/$escaped_quote}
    printf '%s' "$value"
}

sql_quote_literal() {
    printf "'%s'" "$(sql_escape_literal "$1")"
}

emit_file_metadata() {
    local file_path="$1"
    local mtime_ns size_bytes escaped_path raw_time seconds fraction
    case "$(uname -s)" in
        Darwin|*BSD)
            raw_time=$(stat -f %Fm "$file_path")
            seconds=${raw_time%%.*}
            fraction=${raw_time#*.}000000000
            mtime_ns="${seconds}${fraction:0:9}"
            size_bytes=$(stat -f %z "$file_path")
            ;;
        *)
            seconds=$(stat -c %Y "$file_path")
            raw_time=$(stat -c %y "$file_path")
            fraction=${raw_time#*.}
            fraction=${fraction%% *}000000000
            mtime_ns="${seconds}${fraction:0:9}"
            size_bytes=$(stat -c %s "$file_path")
            ;;
    esac
    escaped_path=${file_path//\"/\"\"}
    printf '"%s",%s,%s\n' "$escaped_path" "$mtime_ns" "$size_bytes"
}

resolve_reference_schema_path() {
    if [ -n "${ANALYZE_USAGE_SCHEMA:-}" ] && [ -f "${ANALYZE_USAGE_SCHEMA}" ]; then
        echo "${ANALYZE_USAGE_SCHEMA}"
        return 0
    fi

    if [ -f "$REFERENCE_SCHEMA_PATH" ]; then
        echo "$REFERENCE_SCHEMA_PATH"
        return 0
    fi

    if [ -f "$INSTALLED_SCHEMA_PATH" ]; then
        echo "$INSTALLED_SCHEMA_PATH"
        return 0
    fi

    return 1
}

ensure_reference_schema() {
    local schema_path
    if ! schema_path=$(resolve_reference_schema_path); then
        cat >&2 <<EOF
Error: canonical schema file not found.

Checked:
  ${ANALYZE_USAGE_SCHEMA:-"(ANALYZE_USAGE_SCHEMA not set)"}
  $REFERENCE_SCHEMA_PATH
  $INSTALLED_SCHEMA_PATH

Install the schema file alongside the script install, for example:
  mkdir -p ~/.local/share/analyze-usage
  cp skills/analyze-usage/references/canonical-agent-schema.duckdb.sql ~/.local/share/analyze-usage/
EOF
        exit 1
    fi

    duckdb "$DB_PATH" < "$schema_path"
}

ensure_cursor_tables() {
    duckdb "$DB_PATH" << 'EOF'
CREATE TABLE IF NOT EXISTS cursor_prompts (
    timestamp TIMESTAMP,
    workspace_id VARCHAR,
    workspace_path VARCHAR,
    prompt_text VARCHAR
);

CREATE TABLE IF NOT EXISTS cursor_workspaces (
    workspace_id VARCHAR,
    workspace_path VARCHAR,
    prompt_count INTEGER,
    db_path VARCHAR
);
EOF
}

reset_cursor_tables() {
    duckdb "$DB_PATH" << 'EOF'
DELETE FROM cursor_prompts;
DELETE FROM cursor_workspaces;
EOF
}

needs_interface_backfill() {
    duckdb -csv -noheader "$DB_PATH" -c "
SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = 'claude_tools' AND column_name = 'interface'
    ) THEN 0
    ELSE 1
END;" 2>/dev/null || echo "0"
}

ensure_current_tables() {
    duckdb "$DB_PATH" << 'ENSURE'
CREATE TABLE IF NOT EXISTS claude_tools (
    timestamp TIMESTAMP,
    session_id VARCHAR,
    project_dir VARCHAR,
    model VARCHAR,
    tool_name VARCHAR,
    context VARCHAR,
    input_tokens INTEGER,
    output_tokens INTEGER,
    cache_write_tokens INTEGER,
    cache_read_tokens INTEGER,
    repo_name VARCHAR,
    worktree_branch VARCHAR,
    is_worktree BOOLEAN,
    interface VARCHAR,
    source_file VARCHAR
);
CREATE TABLE IF NOT EXISTS messages (
    uuid VARCHAR,
    parent_uuid VARCHAR,
    session_id VARCHAR,
    role VARCHAR,
    harness VARCHAR,
    interface VARCHAR,
    model VARCHAR,
    content VARCHAR,
    thinking VARCHAR,
    timestamp TIMESTAMP,
    project_dir VARCHAR,
    git_branch VARCHAR,
    repo_name VARCHAR,
    worktree_branch VARCHAR,
    is_worktree BOOLEAN,
    is_sidechain BOOLEAN,
    input_tokens INTEGER,
    output_tokens INTEGER,
    cache_write_tokens INTEGER,
    cache_read_tokens INTEGER,
    tool_use_count INTEGER,
    source_file VARCHAR,
    search_id VARCHAR
);
CREATE TABLE IF NOT EXISTS system_events (uuid VARCHAR PRIMARY KEY, session_id VARCHAR, subtype VARCHAR, timestamp TIMESTAMP, project_dir VARCHAR, git_branch VARCHAR, duration_ms INTEGER, version VARCHAR, is_sidechain BOOLEAN, source_file VARCHAR, repo_name VARCHAR);
CREATE TABLE IF NOT EXISTS queue_operations (session_id VARCHAR, timestamp TIMESTAMP, operation VARCHAR, content VARCHAR, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS pr_links (session_id VARCHAR, timestamp TIMESTAMP, pr_number INTEGER, pr_url VARCHAR, pr_repository VARCHAR, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS _sessions_index (session_id VARCHAR PRIMARY KEY, project_path VARCHAR, first_prompt VARCHAR, summary VARCHAR, message_count INTEGER, git_branch VARCHAR, created_at TIMESTAMP, modified_at TIMESTAMP);
CREATE TABLE IF NOT EXISTS _loaded_files (
    file_path VARCHAR PRIMARY KEY,
    mtime_ns BIGINT,
    size_bytes BIGINT,
    source_kind VARCHAR,
    loaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS codex_session_index (session_id VARCHAR PRIMARY KEY, thread_name VARCHAR, updated_at TIMESTAMP, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS codex_session_meta (session_id VARCHAR PRIMARY KEY, started_at TIMESTAMP, project_dir VARCHAR, repo_name VARCHAR, worktree_branch VARCHAR, is_worktree BOOLEAN, source VARCHAR, originator VARCHAR, cli_version VARCHAR, model_provider VARCHAR, git_branch VARCHAR, git_sha VARCHAR, git_origin_url VARCHAR, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS codex_tools (timestamp TIMESTAMP, session_id VARCHAR, project_dir VARCHAR, model VARCHAR, tool_name VARCHAR, context VARCHAR, repo_name VARCHAR, worktree_branch VARCHAR, is_worktree BOOLEAN, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS codex_token_counts (timestamp TIMESTAMP, session_id VARCHAR, project_dir VARCHAR, model VARCHAR, reasoning_effort VARCHAR, input_tokens BIGINT, cached_input_tokens BIGINT, output_tokens BIGINT, reasoning_output_tokens BIGINT, total_tokens BIGINT, model_context_window BIGINT, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS codex_developer_messages (session_id VARCHAR, timestamp TIMESTAMP, project_dir VARCHAR, model VARCHAR, content VARCHAR, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS pi_session_meta (session_id VARCHAR PRIMARY KEY, started_at TIMESTAMP, project_dir VARCHAR, title VARCHAR, version INTEGER, repo_name VARCHAR, worktree_branch VARCHAR, is_worktree BOOLEAN, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS pi_tools (timestamp TIMESTAMP, session_id VARCHAR, project_dir VARCHAR, model VARCHAR, tool_name VARCHAR, context VARCHAR, repo_name VARCHAR, worktree_branch VARCHAR, is_worktree BOOLEAN, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS pi_usage (usage_id VARCHAR PRIMARY KEY, timestamp TIMESTAMP, session_id VARCHAR, project_dir VARCHAR, provider VARCHAR, model VARCHAR, thinking_level VARCHAR, input_tokens BIGINT, cached_input_tokens BIGINT, cache_write_tokens BIGINT, output_tokens BIGINT, reasoning_tokens BIGINT, total_tokens BIGINT, input_cost_usd DOUBLE, cached_input_cost_usd DOUBLE, cache_write_cost_usd DOUBLE, output_cost_usd DOUBLE, cost_usd DOUBLE, stop_reason VARCHAR, source_file VARCHAR);

-- Add columns to tables that may predate the current schema.
ALTER TABLE claude_tools ADD COLUMN IF NOT EXISTS source_file VARCHAR;
ALTER TABLE claude_tools ADD COLUMN IF NOT EXISTS interface VARCHAR;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS source_file VARCHAR;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS interface VARCHAR;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS search_id VARCHAR;
ALTER TABLE _loaded_files ADD COLUMN IF NOT EXISTS size_bytes BIGINT;
ALTER TABLE _loaded_files ADD COLUMN IF NOT EXISTS source_kind VARCHAR;
ENSURE
}

# ============================================================================
# Worktree SQL fragment (shared by multiple loaders)
# ============================================================================

# Returns SQL for repo_name extraction given a column name
worktree_repo_sql() {
    local col="${1:-project_dir}"
    cat << EOF
CASE
    WHEN ${col} LIKE '%/conductor/workspaces/%' THEN
        REGEXP_EXTRACT(${col}, '/conductor/workspaces/([^/]+)/', 1)
    WHEN ${col} LIKE '%/.worktrees/%' THEN
        REGEXP_EXTRACT(${col}, '/\.worktrees/([^/]+)/', 1)
    WHEN ${col} LIKE '%/.codex/worktrees/%' THEN
        REGEXP_EXTRACT(${col}, '/\.codex/worktrees/[^/]+/([^/]+)', 1)
    WHEN ${col} LIKE '%/code/%' THEN
        REGEXP_EXTRACT(${col}, '/code/([^/]+)', 1)
    ELSE NULL
END
EOF
}

worktree_branch_sql() {
    local col="${1:-project_dir}"
    cat << EOF
CASE
    WHEN ${col} LIKE '%/conductor/workspaces/%' THEN
        REGEXP_EXTRACT(${col}, '/conductor/workspaces/[^/]+/([^/]+)', 1)
    WHEN ${col} LIKE '%/.worktrees/%' THEN
        REGEXP_EXTRACT(${col}, '/\.worktrees/[^/]+/([^/]+)', 1)
    WHEN ${col} LIKE '%/.codex/worktrees/%' THEN NULL
    ELSE NULL
END
EOF
}

worktree_is_sql() {
    local col="${1:-project_dir}"
    cat << EOF
CASE
    WHEN ${col} LIKE '%/conductor/workspaces/%' THEN TRUE
    WHEN ${col} LIKE '%/.worktrees/%' THEN TRUE
    WHEN ${col} LIKE '%/.codex/worktrees/%' THEN TRUE
    ELSE FALSE
END
EOF
}

interface_sql() {
    local cwd_col="${1:-project_dir}"
    local entrypoint_col="${2:-NULL}"
    cat << EOF
CASE
    WHEN LOWER(COALESCE(${cwd_col}, '')) LIKE '%/conductor/workspaces/%' THEN 'conductor'
    WHEN LOWER(COALESCE(${cwd_col}, '')) LIKE '%/.t3code/%'
      OR LOWER(COALESCE(${cwd_col}, '')) LIKE '%/t3code/workspaces/%' THEN 't3code'
    WHEN LOWER(COALESCE(${cwd_col}, '')) LIKE '%/.warp/%'
      OR LOWER(COALESCE(${cwd_col}, '')) LIKE '%/library/application support/dev.warp.warp-stable/%'
      OR LOWER(COALESCE(${cwd_col}, '')) LIKE '%/library/application support/warp/%' THEN 'warp'
    WHEN LOWER(COALESCE(${cwd_col}, '')) LIKE '%/.ghosty/%'
      OR LOWER(COALESCE(${cwd_col}, '')) LIKE '%/.config/ghostty/%'
      OR LOWER(COALESCE(${cwd_col}, '')) LIKE '%/library/application support/com.mitchellh.ghostty/%' THEN 'ghosty'
    WHEN LOWER(COALESCE(${entrypoint_col}, '')) = 'cli' THEN 'cli'
    ELSE NULL
END
EOF
}

# ============================================================================
# File Change Detection
# ============================================================================

emit_tracked_files() {
    if [ -d "$CLAUDE_PROJECTS" ]; then
        find "$CLAUDE_PROJECTS" -name "*.jsonl" -print0 2>/dev/null | while IFS= read -r -d '' f; do
            emit_file_metadata "$f"
        done
    fi

    if [ -n "$CURSOR_WORKSPACE" ] && [ -d "$CURSOR_WORKSPACE" ]; then
        find "$CURSOR_WORKSPACE" \( -name "state.vscdb" -o -name "workspace.json" \) -print0 2>/dev/null | while IFS= read -r -d '' f; do
            emit_file_metadata "$f"
        done
    fi

    emit_codex_session_files | while IFS= read -r f; do
        emit_file_metadata "$f"
    done

    if [ -f "$CODEX_SESSION_INDEX_FILE" ]; then
        emit_file_metadata "$CODEX_SESSION_INDEX_FILE"
    fi

    if [ -d "$PI_SESSIONS_DIR" ]; then
        find "$PI_SESSIONS_DIR" -name "*.jsonl" -print0 2>/dev/null | while IFS= read -r -d '' f; do
            emit_file_metadata "$f"
        done
    fi
}

emit_codex_session_files() {
    {
        if [ -d "$CODEX_SESSIONS_DIR" ]; then
            find "$CODEX_SESSIONS_DIR" -name "*.jsonl" 2>/dev/null | awk '{print "0\t" $0}'
        fi
        if [ -d "$CODEX_ARCHIVED_DIR" ]; then
            find "$CODEX_ARCHIVED_DIR" -name "*.jsonl" 2>/dev/null | awk '{print "1\t" $0}'
        fi
    } | awk -F'\t' '
        {
            priority = $1
            path = $2
            session_id = path
            sub(/^.*rollout-[0-9T:-]+-/, "", session_id)
            sub(/\.jsonl$/, "", session_id)
            if (!(session_id in best_path) || priority < best_priority[session_id]) {
                best_path[session_id] = path
                best_priority[session_id] = priority
            }
        }
        END {
            for (session_id in best_path) {
                print best_path[session_id]
            }
        }
    ' | sort
}

sql_list_from_paths_file() {
    local paths_file="$1"
    local quoted
    quoted=$(mktemp)

    while IFS= read -r file_path; do
        sql_quote_literal "$file_path"
        printf '\n'
    done < "$paths_file" > "$quoted"

    paste -sd',' "$quoted"
    rm -f "$quoted"
}

stage_current_files() {
    local current_csv
    current_csv=$(mktemp)
    emit_tracked_files > "$current_csv"

    local current_csv_sql claude_prefix cursor_condition codex_sessions_prefix codex_archived_prefix codex_index pi_prefix
    current_csv_sql=$(sql_quote_literal "$current_csv")
    claude_prefix=$(sql_quote_literal "${CLAUDE_PROJECTS%/}/")
    cursor_condition="FALSE"
    if [ -n "$CURSOR_WORKSPACE" ]; then
        cursor_condition="starts_with(file_path, $(sql_quote_literal "${CURSOR_WORKSPACE%/}/"))"
    fi
    codex_sessions_prefix=$(sql_quote_literal "${CODEX_SESSIONS_DIR%/}/")
    codex_archived_prefix=$(sql_quote_literal "${CODEX_ARCHIVED_DIR%/}/")
    codex_index=$(sql_quote_literal "$CODEX_SESSION_INDEX_FILE")
    pi_prefix=$(sql_quote_literal "${PI_SESSIONS_DIR%/}/")

    duckdb "$DB_PATH" << EOF
CREATE OR REPLACE TABLE _current_files (
    file_path VARCHAR PRIMARY KEY,
    mtime_ns BIGINT,
    size_bytes BIGINT,
    source_kind VARCHAR
);
EOF

    if [ -s "$current_csv" ]; then
        duckdb "$DB_PATH" << EOF
INSERT INTO _current_files
SELECT
    file_path,
    mtime_ns,
    size_bytes,
    CASE
        WHEN starts_with(file_path, ${claude_prefix}) THEN 'claude'
        WHEN ${cursor_condition} THEN 'cursor'
        WHEN starts_with(file_path, ${codex_sessions_prefix})
          OR starts_with(file_path, ${codex_archived_prefix})
          OR file_path = ${codex_index} THEN 'codex'
        WHEN starts_with(file_path, ${pi_prefix}) THEN 'pi'
        ELSE 'unknown'
    END AS source_kind
FROM read_csv(${current_csv_sql}, header=false, auto_detect=false,
    columns={
        'file_path': 'VARCHAR',
        'mtime_ns': 'BIGINT',
        'size_bytes': 'BIGINT'
    });
EOF
    fi

    rm -f "$current_csv"
}

refresh_file_changes() {
    stage_current_files
    duckdb "$DB_PATH" << 'EOF'
CREATE OR REPLACE TABLE _pending_file_changes AS
SELECT
    COALESCE(c.file_path, l.file_path) AS file_path,
    c.mtime_ns,
    c.size_bytes,
    COALESCE(c.source_kind, l.source_kind,
        CASE
            WHEN l.file_path LIKE '%/.claude/projects/%' THEN 'claude'
            WHEN l.file_path LIKE '%/Cursor/User/workspaceStorage/%' THEN 'cursor'
            WHEN l.file_path LIKE '%/.codex/%' THEN 'codex'
            WHEN l.file_path LIKE '%/.pi/agent/sessions/%' THEN 'pi'
            ELSE 'unknown'
        END
    ) AS source_kind,
    CASE WHEN c.file_path IS NULL THEN 'deleted' ELSE 'changed' END AS change_kind
FROM _current_files c
FULL OUTER JOIN _loaded_files l ON c.file_path = l.file_path
WHERE c.file_path IS NULL
   OR l.file_path IS NULL
   OR c.mtime_ns IS DISTINCT FROM l.mtime_ns
   OR c.size_bytes IS DISTINCT FROM l.size_bytes;
EOF
}

pending_file_list_sql() {
    local source_kind="$1"
    local change_kind="${2:-changed}"
    local source_sql change_sql
    source_sql=$(sql_quote_literal "$source_kind")
    change_sql=$(sql_quote_literal "$change_kind")
    duckdb -list -noheader "$DB_PATH" -c "
SELECT '[' || COALESCE(
    STRING_AGG(
        CHR(39) || REPLACE(file_path, CHR(39), CHR(39) || CHR(39)) || CHR(39),
        ',' ORDER BY file_path
    ),
    ''
) || ']'
FROM _pending_file_changes
WHERE source_kind = ${source_sql} AND change_kind = ${change_sql};"
}

commit_file_tracking() {
    duckdb "$DB_PATH" << 'EOF'
DELETE FROM _loaded_files;
INSERT INTO _loaded_files (file_path, mtime_ns, size_bytes, source_kind)
SELECT file_path, mtime_ns, size_bytes, source_kind
FROM _current_files;
DROP TABLE _pending_file_changes;
DROP TABLE _current_files;
EOF
}

# ============================================================================
# Claude Code Loaders
# ============================================================================

# Returns SQL expression for a JSON reader's file argument.
# List literals (starting with '[') pass through; glob strings get quoted.
sql_file_arg() {
    local pattern="$1"
    if [[ "$pattern" == \[* ]]; then
        echo "$pattern"
    else
        sql_quote_literal "$pattern"
    fi
}

# Load tool invocations from JSONL files
# Args: glob pattern for files (default: all)
load_claude_data() {
    local file_pattern="${1:-$CLAUDE_PROJECTS/**/*.jsonl}"
    local pattern_sql; pattern_sql=$(sql_file_arg "$file_pattern")

    if [ ! -d "$CLAUDE_PROJECTS" ]; then
        echo "  Claude Code: Not found at $CLAUDE_PROJECTS"
        return
    fi

    echo "  Claude Code: Loading tools..."

    duckdb "$DB_PATH" << EOF
CREATE TABLE IF NOT EXISTS claude_tools (
    timestamp TIMESTAMP,
    session_id VARCHAR,
    project_dir VARCHAR,
    model VARCHAR,
    tool_name VARCHAR,
    context VARCHAR,
    input_tokens INTEGER,
    output_tokens INTEGER,
    cache_write_tokens INTEGER,
    cache_read_tokens INTEGER,
    repo_name VARCHAR,
    worktree_branch VARCHAR,
    is_worktree BOOLEAN,
    interface VARCHAR,
    source_file VARCHAR
);

INSERT INTO claude_tools (
    timestamp, session_id, project_dir, model, tool_name, context,
    input_tokens, output_tokens, cache_write_tokens, cache_read_tokens,
    repo_name, worktree_branch, is_worktree, interface, source_file
)
WITH raw_logs AS (
    SELECT json, filename
    FROM read_ndjson_objects(${pattern_sql},
        filename=true,
        ignore_errors=true,
        maximum_object_size=104857600)
),
expanded AS (
    SELECT
        TRY_CAST(json_extract_string(logs.json, '\$.timestamp') AS TIMESTAMP) as timestamp,
        json_extract_string(logs.json, '\$.sessionId') as session_id,
        json_extract_string(logs.json, '\$.cwd') as project_dir,
        json_extract_string(logs.json, '\$.message.model') as model,
        json_extract_string(c.value, '\$.name') as tool_name,
        COALESCE(
            json_extract_string(json_extract(c.value, '\$.input'), '\$.command'),
            json_extract_string(json_extract(c.value, '\$.input'), '\$.file_path'),
            json_extract_string(json_extract(c.value, '\$.input'), '\$.pattern'),
            json_extract_string(json_extract(c.value, '\$.input'), '\$.description'),
            json_extract_string(json_extract(c.value, '\$.input'), '\$.query'),
            json_extract_string(json_extract(c.value, '\$.input'), '\$.url'),
            LEFT(json_extract_string(c.value, '\$.input'), 200)
        ) as context,
        NULL::INTEGER as input_tokens,
        NULL::INTEGER as output_tokens,
        NULL::INTEGER as cache_write_tokens,
        NULL::INTEGER as cache_read_tokens,
        json_extract_string(logs.json, '\$.entrypoint') as entrypoint,
        logs.filename as source_file
    FROM raw_logs logs,
    LATERAL UNNEST(
        CASE
            WHEN json_type(json_extract(logs.json, '\$.message.content')) = 'ARRAY'
            THEN from_json(json_extract(logs.json, '\$.message.content')::VARCHAR, '["JSON"]')
            ELSE ['{}']::JSON[]
        END
    ) as c(value)
    WHERE json_extract_string(logs.json, '\$.type') IN ('user', 'assistant')
    AND json_extract_string(c.value, '\$.type') = 'tool_use'
),
with_worktree AS (
    SELECT *,
        $(interface_sql project_dir entrypoint) as interface_calc,
        $(worktree_repo_sql project_dir) as repo_name_calc,
        $(worktree_branch_sql project_dir) as worktree_branch_calc,
        $(worktree_is_sql project_dir) as is_worktree_calc
    FROM expanded
    WHERE tool_name IS NOT NULL
)
SELECT
    timestamp, session_id, project_dir, model, tool_name, context,
    input_tokens, output_tokens, cache_write_tokens, cache_read_tokens,
    repo_name_calc as repo_name,
    worktree_branch_calc as worktree_branch,
    is_worktree_calc as is_worktree,
    interface_calc as interface,
    source_file
FROM with_worktree;
EOF

    local tool_count
    tool_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM claude_tools;" 2>/dev/null || echo "0")
    echo "  Claude Code: $tool_count tool invocations"
}

# Load conversation messages
load_claude_messages() {
    local file_pattern="${1:-$CLAUDE_PROJECTS/**/*.jsonl}"
    local pattern_sql; pattern_sql=$(sql_file_arg "$file_pattern")

    if [ ! -d "$CLAUDE_PROJECTS" ]; then
        return
    fi

    echo "  Claude Code: Loading messages..."

    duckdb "$DB_PATH" << EOF
CREATE TABLE IF NOT EXISTS messages (
    uuid VARCHAR,
    parent_uuid VARCHAR,
    session_id VARCHAR,
    role VARCHAR,
    harness VARCHAR,
    interface VARCHAR,
    model VARCHAR,
    content VARCHAR,
    thinking VARCHAR,
    timestamp TIMESTAMP,
    project_dir VARCHAR,
    git_branch VARCHAR,
    repo_name VARCHAR,
    worktree_branch VARCHAR,
    is_worktree BOOLEAN,
    is_sidechain BOOLEAN,
    input_tokens INTEGER,
    output_tokens INTEGER,
    cache_write_tokens INTEGER,
    cache_read_tokens INTEGER,
    tool_use_count INTEGER,
    source_file VARCHAR,
    search_id VARCHAR
);

INSERT INTO messages (
    uuid, parent_uuid, session_id, role, harness, interface, model,
    content, thinking, timestamp, project_dir, git_branch, repo_name,
    worktree_branch, is_worktree, is_sidechain, input_tokens, output_tokens,
    cache_write_tokens, cache_read_tokens, tool_use_count, source_file
)
WITH raw_logs AS (
    SELECT json, filename
    FROM read_ndjson_objects(${pattern_sql},
        filename=true,
        ignore_errors=true,
        maximum_object_size=104857600)
),
-- User messages where content is a plain string
user_string AS (
    SELECT
        json_extract_string(logs.json, '\$.uuid') as uuid,
        json_extract_string(logs.json, '\$.parentUuid') as parent_uuid,
        json_extract_string(logs.json, '\$.sessionId') as session_id,
        'user' as role,
        'claude_code' as harness,
        json_extract_string(logs.json, '\$.entrypoint') as entrypoint,
        NULL as model,
        json_extract_string(logs.json, '\$.message.content') as content,
        NULL as thinking,
        TRY_CAST(json_extract_string(logs.json, '\$.timestamp') AS TIMESTAMP) as timestamp,
        json_extract_string(logs.json, '\$.cwd') as project_dir,
        NULL as git_branch,
        TRY_CAST(json_extract_string(logs.json, '\$.isSidechain') AS BOOLEAN) as is_sidechain,
        NULL as input_tokens,
        NULL as output_tokens,
        NULL as cache_write_tokens,
        NULL as cache_read_tokens,
        0 as tool_use_count,
        logs.filename as source_file
    FROM raw_logs logs
    WHERE json_extract_string(logs.json, '\$.type') = 'user'
    AND json_type(json_extract(logs.json, '\$.message.content')) = 'VARCHAR'
    AND LENGTH(json_extract_string(logs.json, '\$.message.content')) > 0
),
-- User messages where content is an array of blocks
user_array AS (
    SELECT
        json_extract_string(logs.json, '\$.uuid') as uuid,
        json_extract_string(logs.json, '\$.parentUuid') as parent_uuid,
        json_extract_string(logs.json, '\$.sessionId') as session_id,
        'user' as role,
        'claude_code' as harness,
        json_extract_string(logs.json, '\$.entrypoint') as entrypoint,
        NULL as model,
        STRING_AGG(
            CASE WHEN json_extract_string(c.value, '\$.type') = 'text'
                 THEN json_extract_string(c.value, '\$.text')
            END, E'\n'
        ) as content,
        NULL as thinking,
        TRY_CAST(json_extract_string(logs.json, '\$.timestamp') AS TIMESTAMP) as timestamp,
        json_extract_string(logs.json, '\$.cwd') as project_dir,
        NULL as git_branch,
        TRY_CAST(json_extract_string(logs.json, '\$.isSidechain') AS BOOLEAN) as is_sidechain,
        NULL as input_tokens,
        NULL as output_tokens,
        NULL as cache_write_tokens,
        NULL as cache_read_tokens,
        0 as tool_use_count,
        logs.filename as source_file
    FROM raw_logs logs,
    LATERAL UNNEST(
        CASE
            WHEN json_type(json_extract(logs.json, '\$.message.content')) = 'ARRAY'
            THEN from_json(json_extract(logs.json, '\$.message.content')::VARCHAR, '["JSON"]')
            ELSE ['{}']::JSON[]
        END
    ) as c(value)
    WHERE json_extract_string(logs.json, '\$.type') = 'user'
    AND json_type(json_extract(logs.json, '\$.message.content')) = 'ARRAY'
    AND EXISTS (
        SELECT 1 FROM UNNEST(
            from_json(json_extract(logs.json, '\$.message.content')::VARCHAR, '["JSON"]')
        ) as chk(v)
        WHERE json_extract_string(chk.v, '\$.type') = 'text'
    )
    GROUP BY uuid, parent_uuid, session_id, timestamp,
             project_dir, is_sidechain, entrypoint, source_file
    HAVING content IS NOT NULL
),
-- Assistant messages
assistant_msgs AS (
    SELECT
        json_extract_string(logs.json, '\$.uuid') as uuid,
        json_extract_string(logs.json, '\$.parentUuid') as parent_uuid,
        json_extract_string(logs.json, '\$.sessionId') as session_id,
        'assistant' as role,
        'claude_code' as harness,
        json_extract_string(logs.json, '\$.entrypoint') as entrypoint,
        json_extract_string(logs.json, '\$.message.model') as model,
        STRING_AGG(
            CASE WHEN json_extract_string(c.value, '\$.type') = 'text'
                 THEN json_extract_string(c.value, '\$.text')
            END, E'\n'
        ) as content,
        STRING_AGG(
            CASE WHEN json_extract_string(c.value, '\$.type') = 'thinking'
                 THEN json_extract_string(c.value, '\$.thinking')
            END, E'\n'
        ) as thinking,
        TRY_CAST(json_extract_string(logs.json, '\$.timestamp') AS TIMESTAMP) as timestamp,
        json_extract_string(logs.json, '\$.cwd') as project_dir,
        NULL as git_branch,
        TRY_CAST(json_extract_string(logs.json, '\$.isSidechain') AS BOOLEAN) as is_sidechain,
        TRY_CAST(json_extract_string(logs.json, '\$.message.usage.input_tokens') AS INTEGER) as input_tokens,
        TRY_CAST(json_extract_string(logs.json, '\$.message.usage.output_tokens') AS INTEGER) as output_tokens,
        COALESCE(
            TRY_CAST(json_extract_string(logs.json, '\$.message.usage.cache_creation_input_tokens') AS INTEGER),
            0
        ) as cache_write_tokens,
        COALESCE(
            TRY_CAST(json_extract_string(logs.json, '\$.message.usage.cache_read_input_tokens') AS INTEGER),
            0
        ) as cache_read_tokens,
        COUNT(CASE WHEN json_extract_string(c.value, '\$.type') = 'tool_use' THEN 1 END) as tool_use_count,
        logs.filename as source_file
    FROM raw_logs logs,
    LATERAL UNNEST(
        CASE
            WHEN json_type(json_extract(logs.json, '\$.message.content')) = 'ARRAY'
            THEN from_json(json_extract(logs.json, '\$.message.content')::VARCHAR, '["JSON"]')
            ELSE ['{}']::JSON[]
        END
    ) as c(value)
    WHERE json_extract_string(logs.json, '\$.type') = 'assistant'
    GROUP BY uuid, parent_uuid, session_id, model,
             timestamp, project_dir, is_sidechain, entrypoint,
             input_tokens, output_tokens, cache_write_tokens, cache_read_tokens,
             json_extract(logs.json, '\$.message.usage'), source_file
),
all_messages AS (
    SELECT * FROM user_string
    UNION ALL
    SELECT * FROM user_array
    UNION ALL
    SELECT * FROM assistant_msgs
),
with_worktree AS (
    SELECT m.*,
        $(interface_sql m.project_dir m.entrypoint) as interface_calc,
        $(worktree_repo_sql m.project_dir) as repo_name_calc,
        $(worktree_branch_sql m.project_dir) as worktree_branch_calc,
        $(worktree_is_sql m.project_dir) as is_worktree_calc
    FROM all_messages m
)
SELECT
    uuid, parent_uuid, session_id, role, harness, interface_calc as interface, model,
    content, thinking, timestamp, project_dir, git_branch,
    repo_name_calc as repo_name,
    worktree_branch_calc as worktree_branch,
    is_worktree_calc as is_worktree,
    is_sidechain,
    input_tokens, output_tokens, cache_write_tokens, cache_read_tokens,
    tool_use_count, source_file
FROM with_worktree
WHERE timestamp IS NOT NULL;
EOF

    local msg_count
    msg_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM messages WHERE harness='claude_code';" 2>/dev/null || echo "0")
    echo "  Claude Code: $msg_count messages"
}

# Load system events (turn_duration, api_error, stop_hook_summary, etc.)
load_system_events() {
    local file_pattern="${1:-$CLAUDE_PROJECTS/**/*.jsonl}"
    local pattern_sql; pattern_sql=$(sql_file_arg "$file_pattern")

    if [ ! -d "$CLAUDE_PROJECTS" ]; then
        return
    fi

    echo "  Claude Code: Loading system events..."

    duckdb "$DB_PATH" << EOF
CREATE TABLE IF NOT EXISTS system_events (
    uuid VARCHAR PRIMARY KEY,
    session_id VARCHAR,
    subtype VARCHAR,
    timestamp TIMESTAMP,
    project_dir VARCHAR,
    git_branch VARCHAR,
    duration_ms INTEGER,
    version VARCHAR,
    is_sidechain BOOLEAN,
    source_file VARCHAR,
    repo_name VARCHAR
);

INSERT OR IGNORE INTO system_events (
    uuid, session_id, subtype, timestamp, project_dir, git_branch,
    duration_ms, version, is_sidechain, source_file, repo_name
)
WITH raw_logs AS (
    SELECT json, filename
    FROM read_ndjson_objects(${pattern_sql}, filename=true, ignore_errors=true)
)
SELECT
    COALESCE(
        json_extract_string(logs.json, '\$.uuid'),
        MD5(logs.filename || '|' || CAST(logs.json AS VARCHAR))
    ) as uuid,
    json_extract_string(logs.json, '\$.sessionId') as session_id,
    COALESCE(json_extract_string(logs.json, '\$.subtype'), 'unknown') as subtype,
    TRY_CAST(json_extract_string(logs.json, '\$.timestamp') AS TIMESTAMP) as timestamp,
    json_extract_string(logs.json, '\$.cwd') as project_dir,
    json_extract_string(logs.json, '\$.gitBranch') as git_branch,
    TRY_CAST(json_extract_string(logs.json, '\$.durationMs') AS INTEGER) as duration_ms,
    json_extract_string(logs.json, '\$.version') as version,
    TRY_CAST(json_extract_string(logs.json, '\$.isSidechain') AS BOOLEAN) as is_sidechain,
    logs.filename as source_file,
    $(worktree_repo_sql "json_extract_string(logs.json, '\$.cwd')") as repo_name
FROM raw_logs logs
WHERE json_extract_string(logs.json, '\$.type') = 'system';
EOF

    local event_count
    event_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM system_events;" 2>/dev/null || echo "0")
    echo "  Claude Code: $event_count system events"
}

# Load queue operations (user inputs typed during assistant response)
load_queue_operations() {
    local file_pattern="${1:-$CLAUDE_PROJECTS/**/*.jsonl}"
    local pattern_sql; pattern_sql=$(sql_file_arg "$file_pattern")

    if [ ! -d "$CLAUDE_PROJECTS" ]; then
        return
    fi

    echo "  Claude Code: Loading queue operations..."

    duckdb "$DB_PATH" << EOF
CREATE TABLE IF NOT EXISTS queue_operations (
    session_id VARCHAR,
    timestamp TIMESTAMP,
    operation VARCHAR,
    content VARCHAR,
    source_file VARCHAR
);

INSERT INTO queue_operations (
    session_id, timestamp, operation, content, source_file
)
WITH raw_logs AS (
    SELECT json, filename
    FROM read_ndjson_objects(${pattern_sql}, filename=true, ignore_errors=true)
)
SELECT
    json_extract_string(logs.json, '\$.sessionId') as session_id,
    TRY_CAST(json_extract_string(logs.json, '\$.timestamp') AS TIMESTAMP) as timestamp,
    json_extract_string(logs.json, '\$.operation') as operation,
    COALESCE(
        json_extract_string(logs.json, '\$.content'),
        json_extract(logs.json, '\$.content')::VARCHAR
    ) as content,
    logs.filename as source_file
FROM raw_logs logs
WHERE json_extract_string(logs.json, '\$.type') = 'queue-operation';
EOF

    local queue_count
    queue_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM queue_operations;" 2>/dev/null || echo "0")
    echo "  Claude Code: $queue_count queue operations"
}

# Load PR link records
load_pr_links() {
    local file_pattern="${1:-$CLAUDE_PROJECTS/**/*.jsonl}"
    local pattern_sql; pattern_sql=$(sql_file_arg "$file_pattern")

    if [ ! -d "$CLAUDE_PROJECTS" ]; then
        return
    fi

    echo "  Claude Code: Loading PR links..."

    duckdb "$DB_PATH" << EOF
CREATE TABLE IF NOT EXISTS pr_links (
    session_id VARCHAR,
    timestamp TIMESTAMP,
    pr_number INTEGER,
    pr_url VARCHAR,
    pr_repository VARCHAR,
    source_file VARCHAR
);

INSERT INTO pr_links (
    session_id, timestamp, pr_number, pr_url, pr_repository, source_file
)
WITH raw_logs AS (
    SELECT json, filename
    FROM read_ndjson_objects(${pattern_sql}, filename=true, ignore_errors=true)
)
SELECT
    json_extract_string(logs.json, '\$.sessionId') as session_id,
    TRY_CAST(json_extract_string(logs.json, '\$.timestamp') AS TIMESTAMP) as timestamp,
    TRY_CAST(json_extract_string(logs.json, '\$.prNumber') AS INTEGER) as pr_number,
    json_extract_string(logs.json, '\$.prUrl') as pr_url,
    json_extract_string(logs.json, '\$.prRepository') as pr_repository,
    logs.filename as source_file
FROM raw_logs logs
WHERE json_extract_string(logs.json, '\$.type') = 'pr-link';
EOF

    local pr_count
    pr_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM pr_links;" 2>/dev/null || echo "0")
    echo "  Claude Code: $pr_count PR links"
}

# Load sessions-index.json metadata
load_sessions_index() {
    if [ ! -d "$CLAUDE_PROJECTS" ]; then
        return
    fi

    duckdb "$DB_PATH" << 'EOF'
CREATE TABLE IF NOT EXISTS _sessions_index (
    session_id VARCHAR PRIMARY KEY,
    project_path VARCHAR,
    first_prompt VARCHAR,
    summary VARCHAR,
    message_count INTEGER,
    git_branch VARCHAR,
    created_at TIMESTAMP,
    modified_at TIMESTAMP
);

DELETE FROM _sessions_index;
EOF

    # Find all sessions-index.json files
    local index_files
    index_files=$(find "$CLAUDE_PROJECTS" -name "sessions-index.json" 2>/dev/null)

    if [ -z "$index_files" ]; then
        echo "  Sessions index: No index files found"
        return
    fi

    local index_count
    index_count=$(echo "$index_files" | wc -l | tr -d ' ')
    echo "  Sessions index: Loading from $index_count index files..."

    # Process each index file
    echo "$index_files" | while IFS= read -r idx_file; do
        local project_dir
        project_dir=$(dirname "$idx_file")
        local idx_file_sql project_dir_sql
        idx_file_sql=$(sql_escape_literal "$idx_file")
        project_dir_sql=$(sql_escape_literal "$project_dir")

        duckdb "$DB_PATH" << EOF
INSERT OR IGNORE INTO _sessions_index
WITH raw AS (
    SELECT * FROM read_json('${idx_file_sql}',
        auto_detect=true, ignore_errors=true)
)
SELECT
    COALESCE(json_extract_string(e.value, '\$.sessionId'), json_extract_string(e.value, '\$.id')) as session_id,
    '${project_dir_sql}' as project_path,
    json_extract_string(e.value, '\$.firstPrompt') as first_prompt,
    json_extract_string(e.value, '\$.summary') as summary,
    TRY_CAST(json_extract_string(e.value, '\$.messageCount') AS INTEGER) as message_count,
    json_extract_string(e.value, '\$.gitBranch') as git_branch,
    TRY_CAST(json_extract_string(e.value, '\$.createdAt') AS TIMESTAMP) as created_at,
    TRY_CAST(json_extract_string(e.value, '\$.lastModified') AS TIMESTAMP) as modified_at
FROM raw,
LATERAL UNNEST(
    CASE
        WHEN json_type(raw.entries) = 'ARRAY'
        THEN from_json(raw.entries::VARCHAR, '["JSON"]')
        ELSE ARRAY[]::JSON[]
    END
) as e(value)
WHERE session_id IS NOT NULL;
EOF
    done

    local si_count
    si_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _sessions_index;" 2>/dev/null || echo "0")
    echo "  Sessions index: $si_count entries"
}

# ============================================================================
# Cursor Loaders (unchanged from v1.1)
# ============================================================================

load_cursor_data() {
    ensure_cursor_tables
    reset_cursor_tables

    if [ -z "$CURSOR_WORKSPACE" ] || [ ! -d "$CURSOR_WORKSPACE" ]; then
        echo "  Cursor: Not found"
        return
    fi

    local db_count
    db_count=$(find "$CURSOR_WORKSPACE" -name "state.vscdb" 2>/dev/null | wc -l | tr -d ' ')

    if [ "$db_count" -eq 0 ]; then
        echo "  Cursor: No workspace databases found"
        return
    fi

    echo "  Cursor: Loading from $db_count workspaces..."

    duckdb "$DB_PATH" -c "INSTALL sqlite; LOAD sqlite;" 2>/dev/null

    find "$CURSOR_WORKSPACE" -name "state.vscdb" 2>/dev/null | while read -r db; do
        workspace_id=$(basename "$(dirname "$db")")
        workspace_dir=$(dirname "$db")

        workspace_path="unknown"
        if [ -f "$workspace_dir/workspace.json" ]; then
            workspace_path=$(jq -r '.folder // "unknown"' "$workspace_dir/workspace.json" 2>/dev/null | sed 's|file://||' || echo "unknown")
        fi

        local db_sql workspace_id_sql workspace_path_sql
        db_sql=$(sql_escape_literal "$db")
        workspace_id_sql=$(sql_escape_literal "$workspace_id")
        workspace_path_sql=$(sql_escape_literal "$workspace_path")

        duckdb "$DB_PATH" << EOF
LOAD sqlite;
INSERT INTO cursor_prompts (
    timestamp, workspace_id, workspace_path, prompt_text
)
WITH generation_payloads AS (
    SELECT decode(value) as json_text
    FROM sqlite_scan('${db_sql}', 'ItemTable')
    WHERE key = 'aiService.generations'
),
prompt_payloads AS (
    SELECT decode(value) as json_text
    FROM sqlite_scan('${db_sql}', 'ItemTable')
    WHERE key = 'aiService.prompts'
    AND NOT EXISTS (SELECT 1 FROM generation_payloads)
),
generation_rows AS (
    SELECT
        CAST(
            to_timestamp(CAST(json_extract(p.value, '\$.unixMs') AS BIGINT) / 1000.0)
            AT TIME ZONE 'UTC' AS TIMESTAMP
        ) as timestamp,
        json_extract_string(p.value, '\$.textDescription') as prompt_text
    FROM generation_payloads t,
    LATERAL UNNEST(from_json(t.json_text, '["JSON"]')) as p(value)
),
prompt_rows AS (
    SELECT
        NULL as timestamp,
        json_extract_string(p.value, '\$.text') as prompt_text
    FROM prompt_payloads t,
    LATERAL UNNEST(from_json(t.json_text, '["JSON"]')) as p(value)
)
SELECT
    r.timestamp,
    '${workspace_id_sql}' as workspace_id,
    '${workspace_path_sql}' as workspace_path,
    r.prompt_text
FROM (
    SELECT * FROM generation_rows
    UNION ALL
    SELECT * FROM prompt_rows
) r
WHERE r.prompt_text IS NOT NULL;
EOF

        local prompt_count
        prompt_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM cursor_prompts WHERE workspace_id='${workspace_id_sql}';" 2>/dev/null || echo "0")

        duckdb "$DB_PATH" << EOF
INSERT INTO cursor_workspaces (workspace_id, workspace_path, prompt_count, db_path)
VALUES ('${workspace_id_sql}', '${workspace_path_sql}', $prompt_count, '${db_sql}');
EOF
    done

    local prompt_count
    prompt_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM cursor_prompts;" 2>/dev/null || echo "0")
    echo "  Cursor: $prompt_count prompts"
}

load_cursor_messages() {
    duckdb "$DB_PATH" -c "DELETE FROM messages WHERE harness='cursor';"
    local cursor_count
    cursor_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM cursor_prompts;" 2>/dev/null || echo "0")

    if [ "$cursor_count" -eq 0 ]; then
        return
    fi

    echo "  Cursor: Bridging $cursor_count prompts into messages..."

    duckdb "$DB_PATH" << 'EOF'
INSERT INTO messages (
    uuid, parent_uuid, session_id, role, harness, interface, model,
    content, thinking, timestamp, project_dir, git_branch, repo_name,
    worktree_branch, is_worktree, is_sidechain, input_tokens, output_tokens,
    cache_write_tokens, cache_read_tokens, tool_use_count, source_file
)
SELECT
    MD5(workspace_id || CAST(timestamp AS VARCHAR) || LEFT(prompt_text, 50)) as uuid,
    NULL as parent_uuid,
    workspace_id as session_id,
    'user' as role,
    'cursor' as harness,
    NULL as interface,
    NULL as model,
    prompt_text as content,
    NULL as thinking,
    timestamp,
    workspace_path as project_dir,
    NULL as git_branch,
    REGEXP_EXTRACT(workspace_path, '[^/]+$') as repo_name,
    NULL as worktree_branch,
    FALSE as is_worktree,
    FALSE as is_sidechain,
    NULL as input_tokens,
    NULL as output_tokens,
    NULL as cache_write_tokens,
    NULL as cache_read_tokens,
    0 as tool_use_count,
    NULL as source_file
FROM cursor_prompts
WHERE timestamp IS NOT NULL
AND prompt_text IS NOT NULL;
EOF

    local cursor_msg_count
    cursor_msg_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM messages WHERE harness='cursor';" 2>/dev/null || echo "0")
    echo "  Cursor: $cursor_msg_count messages"
}

# ============================================================================
# Codex Loaders
# ============================================================================

load_codex_session_index() {
    duckdb "$DB_PATH" << 'EOF'
CREATE OR REPLACE TABLE codex_session_index (
    session_id VARCHAR PRIMARY KEY,
    thread_name VARCHAR,
    updated_at TIMESTAMP,
    source_file VARCHAR
);
EOF

    if [ ! -f "$CODEX_SESSION_INDEX_FILE" ]; then
        echo "  Codex session index: Not found"
        return
    fi

    echo "  Codex session index: Loading thread names..."

    duckdb "$DB_PATH" << EOF
INSERT INTO codex_session_index
WITH indexed_rows AS (
    SELECT
        json_extract_string(json, '\$.id') as session_id,
        json_extract_string(json, '\$.thread_name') as thread_name,
        TRY_CAST(json_extract_string(json, '\$.updated_at') AS TIMESTAMP) as updated_at,
        '$CODEX_SESSION_INDEX_FILE' as source_file,
        ROW_NUMBER() OVER (
            PARTITION BY json_extract_string(json, '\$.id')
            ORDER BY TRY_CAST(json_extract_string(json, '\$.updated_at') AS TIMESTAMP) DESC NULLS LAST
        ) as rn
    FROM read_ndjson_objects('$CODEX_SESSION_INDEX_FILE', filename=true)
)
SELECT session_id, thread_name, updated_at, source_file
FROM indexed_rows
WHERE session_id IS NOT NULL
AND rn = 1;
EOF

    local codex_index_count
    codex_index_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_session_index;" 2>/dev/null || echo "0")
    echo "  Codex session index: $codex_index_count entries"
}

load_codex_data() {
    duckdb "$DB_PATH" << 'EOF'
CREATE TABLE IF NOT EXISTS codex_session_meta (
    session_id VARCHAR PRIMARY KEY,
    started_at TIMESTAMP,
    project_dir VARCHAR,
    repo_name VARCHAR,
    worktree_branch VARCHAR,
    is_worktree BOOLEAN,
    source VARCHAR,
    originator VARCHAR,
    cli_version VARCHAR,
    model_provider VARCHAR,
    git_branch VARCHAR,
    git_sha VARCHAR,
    git_origin_url VARCHAR,
    source_file VARCHAR
);

CREATE TABLE IF NOT EXISTS codex_tools (
    timestamp TIMESTAMP,
    session_id VARCHAR,
    project_dir VARCHAR,
    model VARCHAR,
    tool_name VARCHAR,
    context VARCHAR,
    repo_name VARCHAR,
    worktree_branch VARCHAR,
    is_worktree BOOLEAN,
    source_file VARCHAR
);

CREATE TABLE IF NOT EXISTS codex_token_counts (
    timestamp TIMESTAMP,
    session_id VARCHAR,
    project_dir VARCHAR,
    model VARCHAR,
    reasoning_effort VARCHAR,
    input_tokens BIGINT,
    cached_input_tokens BIGINT,
    output_tokens BIGINT,
    reasoning_output_tokens BIGINT,
    total_tokens BIGINT,
    model_context_window BIGINT,
    source_file VARCHAR
);

CREATE TABLE IF NOT EXISTS codex_developer_messages (
    session_id VARCHAR,
    timestamp TIMESTAMP,
    project_dir VARCHAR,
    model VARCHAR,
    content VARCHAR,
    source_file VARCHAR
);
EOF

    local codex_file_count codex_file_list_sql
    if [ "$#" -gt 0 ]; then
        codex_file_list_sql="$1"
        codex_file_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT LEN(${codex_file_list_sql});")
    else
        local codex_files
        codex_files=$(mktemp)
        emit_codex_session_files > "$codex_files"
        codex_file_count=$(wc -l < "$codex_files" | tr -d ' ')
        codex_file_list_sql="[$(sql_list_from_paths_file "$codex_files")]"
        rm -f "$codex_files"
    fi

    if [ "$codex_file_count" -eq 0 ]; then
        echo "  Codex: No session files to load"
        return
    fi

    echo "  Codex: Loading from $codex_file_count session files..."

    duckdb "$DB_PATH" << EOF
CREATE OR REPLACE TEMP VIEW codex_raw AS
SELECT
    TRY_CAST(json_extract_string(json, '\$.timestamp') AS TIMESTAMP) as event_timestamp,
    json,
    filename as source_file,
    MD5(filename || '|' || CAST(json AS VARCHAR)) as raw_event_id,
    REGEXP_EXTRACT(filename, 'rollout-[0-9T:-]+-([^.]+)\\.jsonl\$', 1) as session_id
FROM read_ndjson_objects(${codex_file_list_sql}, filename=true);

CREATE OR REPLACE TEMP VIEW codex_context AS
SELECT
    session_id,
    event_timestamp as timestamp,
    json_extract_string(json, '\$.payload.model') as model,
    COALESCE(
        json_extract_string(json, '\$.payload.reasoning_effort'),
        json_extract_string(json, '\$.payload.effort')
    ) as reasoning_effort,
    json_extract_string(json, '\$.payload.cwd') as project_dir,
    source_file
FROM codex_raw
WHERE json_extract_string(json, '\$.type') = 'turn_context';

CREATE OR REPLACE TEMP TABLE codex_event_context AS
SELECT
    raw.raw_event_id,
    ctx.model,
    ctx.reasoning_effort,
    ctx.project_dir
FROM codex_raw raw
ASOF LEFT JOIN codex_context ctx
    ON raw.session_id = ctx.session_id
    AND raw.event_timestamp >= ctx.timestamp;

INSERT INTO codex_session_meta
WITH session_meta_rows AS (
    SELECT
        session_id,
        COALESCE(
            TRY_CAST(json_extract_string(json, '\$.payload.timestamp') AS TIMESTAMP),
            event_timestamp
        ) as started_at,
        json_extract_string(json, '\$.payload.cwd') as project_dir,
        json_extract_string(json, '\$.payload.source') as source,
        json_extract_string(json, '\$.payload.originator') as originator,
        json_extract_string(json, '\$.payload.cli_version') as cli_version,
        json_extract_string(json, '\$.payload.model_provider') as model_provider,
        json_extract_string(json, '\$.payload.git.branch') as git_branch,
        json_extract_string(json, '\$.payload.git.commit_hash') as git_sha,
        json_extract_string(json, '\$.payload.git.repository_url') as git_origin_url,
        source_file,
        ROW_NUMBER() OVER (
            PARTITION BY session_id
            ORDER BY event_timestamp DESC NULLS LAST, source_file DESC
        ) as rn
    FROM codex_raw
    WHERE json_extract_string(json, '\$.type') = 'session_meta'
)
SELECT
    session_id,
    started_at,
    project_dir,
    COALESCE(
        NULLIF(
            REGEXP_REPLACE(REGEXP_EXTRACT(git_origin_url, '[^/]+\$'), '\\.git\$', ''),
            ''
        ),
        $(worktree_repo_sql project_dir)
    ) as repo_name,
    CASE
        WHEN project_dir LIKE '%/.codex/worktrees/%' THEN git_branch
        ELSE $(worktree_branch_sql project_dir)
    END as worktree_branch,
    $(worktree_is_sql project_dir) as is_worktree,
    source,
    originator,
    cli_version,
    model_provider,
    git_branch,
    git_sha,
    git_origin_url,
    source_file
FROM session_meta_rows
WHERE rn = 1
AND session_id IS NOT NULL;

INSERT INTO codex_tools
WITH tool_rows AS (
    SELECT
        raw.event_timestamp as timestamp,
        raw.session_id,
        json_extract_string(raw.json, '\$.payload.arguments') as arguments_text,
        COALESCE(
            CASE
                WHEN json_valid(json_extract_string(raw.json, '\$.payload.arguments'))
                THEN json_extract_string(json_extract_string(raw.json, '\$.payload.arguments'), '\$.workdir')
            END,
            ec.project_dir,
            sm.project_dir
        ) as project_dir,
        ec.model as model,
        sm.git_branch as git_branch,
        sm.repo_name as session_repo_name,
        json_extract_string(raw.json, '\$.payload.name') as tool_name,
        COALESCE(
            CASE
                WHEN json_valid(json_extract_string(raw.json, '\$.payload.arguments'))
                THEN json_extract_string(json_extract_string(raw.json, '\$.payload.arguments'), '\$.cmd')
            END,
            CASE
                WHEN json_valid(json_extract_string(raw.json, '\$.payload.arguments'))
                THEN json_extract_string(json_extract_string(raw.json, '\$.payload.arguments'), '\$.path')
            END,
            CASE
                WHEN json_valid(json_extract_string(raw.json, '\$.payload.arguments'))
                THEN json_extract_string(json_extract_string(raw.json, '\$.payload.arguments'), '\$.workdir')
            END,
            LEFT(json_extract_string(raw.json, '\$.payload.arguments'), 200),
            LEFT(json_extract_string(raw.json, '\$.payload.input'), 200)
        ) as context,
        raw.source_file
    FROM codex_raw raw
    LEFT JOIN codex_session_meta sm ON raw.session_id = sm.session_id
    LEFT JOIN codex_event_context ec ON raw.raw_event_id = ec.raw_event_id
    WHERE json_extract_string(raw.json, '\$.type') = 'response_item'
    AND json_extract_string(raw.json, '\$.payload.type') IN ('function_call', 'custom_tool_call')
)
SELECT
    timestamp,
    session_id,
    project_dir,
    model,
    tool_name,
    context,
    COALESCE(session_repo_name, $(worktree_repo_sql project_dir)) as repo_name,
    CASE
        WHEN project_dir LIKE '%/.codex/worktrees/%' THEN git_branch
        ELSE $(worktree_branch_sql project_dir)
    END as worktree_branch,
    $(worktree_is_sql project_dir) as is_worktree,
    source_file
FROM tool_rows
WHERE timestamp IS NOT NULL
AND tool_name IS NOT NULL;

INSERT INTO codex_token_counts
SELECT
    raw.event_timestamp as timestamp,
    raw.session_id,
    COALESCE(
        ec.project_dir,
        sm.project_dir
    ) as project_dir,
    ec.model as model,
    ec.reasoning_effort as reasoning_effort,
    TRY_CAST(json_extract(raw.json, '\$.payload.info.last_token_usage.input_tokens') AS BIGINT) as input_tokens,
    TRY_CAST(json_extract(raw.json, '\$.payload.info.last_token_usage.cached_input_tokens') AS BIGINT) as cached_input_tokens,
    TRY_CAST(json_extract(raw.json, '\$.payload.info.last_token_usage.output_tokens') AS BIGINT) as output_tokens,
    TRY_CAST(json_extract(raw.json, '\$.payload.info.last_token_usage.reasoning_output_tokens') AS BIGINT) as reasoning_output_tokens,
    TRY_CAST(json_extract(raw.json, '\$.payload.info.last_token_usage.total_tokens') AS BIGINT) as total_tokens,
    TRY_CAST(json_extract(raw.json, '\$.payload.info.model_context_window') AS BIGINT) as model_context_window,
    raw.source_file
FROM codex_raw raw
LEFT JOIN codex_session_meta sm ON raw.session_id = sm.session_id
LEFT JOIN codex_event_context ec ON raw.raw_event_id = ec.raw_event_id
WHERE json_extract_string(raw.json, '\$.type') = 'event_msg'
AND json_extract_string(raw.json, '\$.payload.type') = 'token_count'
AND raw.event_timestamp IS NOT NULL;

INSERT INTO codex_developer_messages
WITH developer_rows AS (
    SELECT
        raw.raw_event_id,
        raw.session_id,
        COALESCE(raw.event_timestamp, sm.started_at) as timestamp,
        COALESCE(
            ec.project_dir,
            sm.project_dir
        ) as project_dir,
        ec.model as model,
        STRING_AGG(
            CASE
                WHEN json_extract_string(block.value, '\$.text') IS NOT NULL
                THEN json_extract_string(block.value, '\$.text')
            END,
            E'\n'
    ) as content,
        raw.source_file
    FROM codex_raw raw
    LEFT JOIN codex_session_meta sm ON raw.session_id = sm.session_id
    LEFT JOIN codex_event_context ec ON raw.raw_event_id = ec.raw_event_id,
    LATERAL UNNEST(
        from_json(COALESCE(json_extract(raw.json, '\$.payload.content')::VARCHAR, '[]'), '["JSON"]')
    ) as block(value)
    WHERE json_extract_string(raw.json, '\$.type') = 'response_item'
    AND json_extract_string(raw.json, '\$.payload.type') = 'message'
    AND json_extract_string(raw.json, '\$.payload.role') = 'developer'
    GROUP BY
        raw.raw_event_id,
        raw.session_id,
        COALESCE(raw.event_timestamp, sm.started_at),
        COALESCE(ec.project_dir, sm.project_dir),
        ec.model,
        raw.source_file
)
SELECT
    session_id,
    timestamp,
    project_dir,
    model,
    content,
    source_file
FROM developer_rows
WHERE content IS NOT NULL;

DROP TABLE codex_event_context;
DROP VIEW codex_context;
DROP VIEW codex_raw;
EOF

    local codex_tool_count codex_token_count codex_developer_count
    codex_tool_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_tools;" 2>/dev/null || echo "0")
    codex_token_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_token_counts;" 2>/dev/null || echo "0")
    codex_developer_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_developer_messages;" 2>/dev/null || echo "0")

    echo "  Codex: $codex_tool_count tool invocations"
    echo "  Codex: $codex_token_count token snapshots"
    echo "  Codex: $codex_developer_count developer prompts"
}

load_codex_messages() {
    duckdb "$DB_PATH" << 'EOF'
CREATE TABLE IF NOT EXISTS messages (
    uuid VARCHAR,
    parent_uuid VARCHAR,
    session_id VARCHAR,
    role VARCHAR,
    harness VARCHAR,
    interface VARCHAR,
    model VARCHAR,
    content VARCHAR,
    thinking VARCHAR,
    timestamp TIMESTAMP,
    project_dir VARCHAR,
    git_branch VARCHAR,
    repo_name VARCHAR,
    worktree_branch VARCHAR,
    is_worktree BOOLEAN,
    is_sidechain BOOLEAN,
    input_tokens INTEGER,
    output_tokens INTEGER,
    cache_write_tokens INTEGER,
    cache_read_tokens INTEGER,
    tool_use_count INTEGER,
    source_file VARCHAR,
    search_id VARCHAR
);
EOF

    local codex_file_count codex_file_list_sql
    if [ "$#" -gt 0 ]; then
        codex_file_list_sql="$1"
        codex_file_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT LEN(${codex_file_list_sql});")
    else
        local codex_files
        codex_files=$(mktemp)
        emit_codex_session_files > "$codex_files"
        codex_file_count=$(wc -l < "$codex_files" | tr -d ' ')
        codex_file_list_sql="[$(sql_list_from_paths_file "$codex_files")]"
        rm -f "$codex_files"
    fi

    if [ "$codex_file_count" -eq 0 ]; then
        return
    fi

    echo "  Codex: Bridging user and assistant messages..."

    duckdb "$DB_PATH" << EOF
CREATE OR REPLACE TEMP VIEW codex_raw AS
SELECT
    TRY_CAST(json_extract_string(json, '\$.timestamp') AS TIMESTAMP) as event_timestamp,
    json,
    filename as source_file,
    MD5(filename || '|' || CAST(json AS VARCHAR)) as raw_event_id,
    REGEXP_EXTRACT(filename, 'rollout-[0-9T:-]+-([^.]+)\\.jsonl\$', 1) as session_id
FROM read_ndjson_objects(${codex_file_list_sql}, filename=true);

CREATE OR REPLACE TEMP VIEW codex_context AS
SELECT
    session_id,
    event_timestamp as timestamp,
    json_extract_string(json, '\$.payload.model') as model,
    json_extract_string(json, '\$.payload.cwd') as project_dir
FROM codex_raw
WHERE json_extract_string(json, '\$.type') = 'turn_context';

CREATE OR REPLACE TEMP TABLE codex_event_context AS
SELECT
    raw.raw_event_id,
    ctx.model,
    ctx.project_dir
FROM codex_raw raw
ASOF LEFT JOIN codex_context ctx
    ON raw.session_id = ctx.session_id
    AND raw.event_timestamp >= ctx.timestamp;

INSERT INTO messages (
    uuid, parent_uuid, session_id, role, harness, interface, model,
    content, thinking, timestamp, project_dir, git_branch, repo_name,
    worktree_branch, is_worktree, is_sidechain, input_tokens, output_tokens,
    cache_write_tokens, cache_read_tokens, tool_use_count, source_file
)
WITH codex_message_rows AS (
    SELECT
        raw.raw_event_id,
        raw.session_id,
        json_extract_string(raw.json, '\$.payload.role') as role,
        COALESCE(raw.event_timestamp, sm.started_at) as timestamp,
        COALESCE(
            ec.project_dir,
            sm.project_dir
        ) as project_dir,
        ec.model as model,
        sm.git_branch as git_branch,
        sm.repo_name as session_repo_name,
        STRING_AGG(
            CASE
                WHEN json_extract_string(block.value, '\$.text') IS NOT NULL
                THEN json_extract_string(block.value, '\$.text')
            END,
            E'\n'
    ) as content,
        raw.source_file
    FROM codex_raw raw
    LEFT JOIN codex_session_meta sm ON raw.session_id = sm.session_id
    LEFT JOIN codex_event_context ec ON raw.raw_event_id = ec.raw_event_id,
    LATERAL UNNEST(
        from_json(COALESCE(json_extract(raw.json, '\$.payload.content')::VARCHAR, '[]'), '["JSON"]')
    ) as block(value)
    WHERE json_extract_string(raw.json, '\$.type') = 'response_item'
    AND json_extract_string(raw.json, '\$.payload.type') = 'message'
    AND json_extract_string(raw.json, '\$.payload.role') IN ('user', 'assistant')
    GROUP BY
        raw.raw_event_id,
        raw.session_id,
        role,
        COALESCE(raw.event_timestamp, sm.started_at),
        COALESCE(ec.project_dir, sm.project_dir),
        ec.model,
        sm.git_branch,
        sm.repo_name,
        raw.source_file
)
SELECT
    raw_event_id as uuid,
    NULL as parent_uuid,
    session_id,
    role,
    'codex' as harness,
    NULL as interface,
    model,
    content,
    NULL as thinking,
    timestamp,
    project_dir,
    git_branch,
    COALESCE(session_repo_name, $(worktree_repo_sql project_dir)) as repo_name,
    CASE
        WHEN project_dir LIKE '%/.codex/worktrees/%' THEN git_branch
        ELSE $(worktree_branch_sql project_dir)
    END as worktree_branch,
    $(worktree_is_sql project_dir) as is_worktree,
    FALSE as is_sidechain,
    NULL as input_tokens,
    NULL as output_tokens,
    NULL as cache_write_tokens,
    NULL as cache_read_tokens,
    0 as tool_use_count,
    source_file
FROM codex_message_rows
WHERE content IS NOT NULL
AND timestamp IS NOT NULL;

DROP TABLE codex_event_context;
DROP VIEW codex_context;
DROP VIEW codex_raw;
EOF

    local codex_msg_count
    codex_msg_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM messages WHERE harness='codex';" 2>/dev/null || echo "0")
    echo "  Codex: $codex_msg_count searchable messages"
}

# ============================================================================
# Pi Data Loading
# ============================================================================

emit_pi_session_files() {
    if [ -d "$PI_SESSIONS_DIR" ]; then
        find "$PI_SESSIONS_DIR" -name "*.jsonl" 2>/dev/null | sort
    fi
}

pi_file_list_arg() {
    # Resolves the optional SQL file-list argument shared by the Pi loaders.
    # Sets PI_FILE_COUNT and PI_FILE_LIST_SQL.
    if [ "$#" -gt 0 ]; then
        PI_FILE_LIST_SQL="$1"
        PI_FILE_COUNT=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT LEN(${PI_FILE_LIST_SQL});")
    else
        local pi_files
        pi_files=$(mktemp)
        emit_pi_session_files > "$pi_files"
        PI_FILE_COUNT=$(wc -l < "$pi_files" | tr -d ' ')
        PI_FILE_LIST_SQL="[$(sql_list_from_paths_file "$pi_files")]"
        rm -f "$pi_files"
    fi
}

load_pi_data() {
    duckdb "$DB_PATH" << 'EOF'
CREATE TABLE IF NOT EXISTS pi_session_meta (session_id VARCHAR PRIMARY KEY, started_at TIMESTAMP, project_dir VARCHAR, title VARCHAR, version INTEGER, repo_name VARCHAR, worktree_branch VARCHAR, is_worktree BOOLEAN, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS pi_tools (timestamp TIMESTAMP, session_id VARCHAR, project_dir VARCHAR, model VARCHAR, tool_name VARCHAR, context VARCHAR, repo_name VARCHAR, worktree_branch VARCHAR, is_worktree BOOLEAN, source_file VARCHAR);
CREATE TABLE IF NOT EXISTS pi_usage (usage_id VARCHAR PRIMARY KEY, timestamp TIMESTAMP, session_id VARCHAR, project_dir VARCHAR, provider VARCHAR, model VARCHAR, thinking_level VARCHAR, input_tokens BIGINT, cached_input_tokens BIGINT, cache_write_tokens BIGINT, output_tokens BIGINT, reasoning_tokens BIGINT, total_tokens BIGINT, input_cost_usd DOUBLE, cached_input_cost_usd DOUBLE, cache_write_cost_usd DOUBLE, output_cost_usd DOUBLE, cost_usd DOUBLE, stop_reason VARCHAR, source_file VARCHAR);
EOF

    local PI_FILE_COUNT PI_FILE_LIST_SQL
    pi_file_list_arg "$@"

    if [ "$PI_FILE_COUNT" -eq 0 ]; then
        echo "  Pi: No session files to load"
        return
    fi

    echo "  Pi: Loading from $PI_FILE_COUNT session files..."

    duckdb "$DB_PATH" << EOF
CREATE OR REPLACE TEMP VIEW pi_raw AS
SELECT
    TRY_CAST(json_extract_string(json, '\$.timestamp') AS TIMESTAMP) as event_timestamp,
    json,
    filename as source_file,
    MD5(filename || '|' || COALESCE(json_extract_string(json, '\$.id'), CAST(json AS VARCHAR))) as raw_event_id,
    json_extract_string(json, '\$.type') as event_type
FROM read_ndjson_objects(${PI_FILE_LIST_SQL}, filename=true);

CREATE OR REPLACE TEMP TABLE pi_file_sessions AS
SELECT
    source_file,
    FIRST(json_extract_string(json, '\$.id') ORDER BY event_timestamp) as session_id,
    FIRST(json_extract_string(json, '\$.cwd') ORDER BY event_timestamp) as project_dir,
    FIRST(TRY_CAST(json_extract_string(json, '\$.version') AS INTEGER) ORDER BY event_timestamp) as version,
    MIN(event_timestamp) as started_at
FROM pi_raw
WHERE event_type = 'session'
GROUP BY source_file;

CREATE OR REPLACE TEMP VIEW pi_thinking AS
SELECT
    source_file,
    event_timestamp as timestamp,
    json_extract_string(json, '\$.thinkingLevel') as thinking_level
FROM pi_raw
WHERE event_type = 'thinking_level_change';

CREATE OR REPLACE TEMP TABLE pi_event_thinking AS
SELECT
    raw.raw_event_id,
    t.thinking_level
FROM pi_raw raw
ASOF LEFT JOIN pi_thinking t
    ON raw.source_file = t.source_file
    AND raw.event_timestamp >= t.timestamp;

INSERT OR REPLACE INTO pi_session_meta
WITH titled AS (
    SELECT
        source_file,
        LAST(json_extract_string(json, '\$.name') ORDER BY event_timestamp) as title
    FROM pi_raw
    WHERE event_type = 'session_info'
    GROUP BY source_file
)
SELECT
    fs.session_id,
    fs.started_at,
    fs.project_dir,
    t.title,
    fs.version,
    $(worktree_repo_sql fs.project_dir) as repo_name,
    $(worktree_branch_sql fs.project_dir) as worktree_branch,
    $(worktree_is_sql fs.project_dir) as is_worktree,
    fs.source_file
FROM pi_file_sessions fs
LEFT JOIN titled t USING (source_file)
WHERE fs.session_id IS NOT NULL;

INSERT INTO pi_tools
WITH tool_rows AS (
    SELECT
        raw.event_timestamp as timestamp,
        fs.session_id,
        fs.project_dir,
        json_extract_string(raw.json, '\$.message.model') as model,
        json_extract_string(block.value, '\$.name') as tool_name,
        COALESCE(
            json_extract_string(block.value, '\$.arguments.command'),
            json_extract_string(block.value, '\$.arguments.cmd'),
            json_extract_string(block.value, '\$.arguments.path'),
            json_extract_string(block.value, '\$.arguments.file_path'),
            json_extract_string(block.value, '\$.arguments.query'),
            json_extract_string(block.value, '\$.arguments.url'),
            LEFT(json_extract(block.value, '\$.arguments')::VARCHAR, 200)
        ) as context,
        raw.source_file
    FROM pi_raw raw
    JOIN pi_file_sessions fs USING (source_file),
    LATERAL UNNEST(
        from_json(COALESCE(json_extract(raw.json, '\$.message.content')::VARCHAR, '[]'), '["JSON"]')
    ) as block(value)
    WHERE raw.event_type = 'message'
    AND json_extract_string(raw.json, '\$.message.role') = 'assistant'
    AND json_extract_string(block.value, '\$.type') = 'toolCall'
)
SELECT
    timestamp,
    session_id,
    project_dir,
    model,
    tool_name,
    context,
    $(worktree_repo_sql project_dir) as repo_name,
    $(worktree_branch_sql project_dir) as worktree_branch,
    $(worktree_is_sql project_dir) as is_worktree,
    source_file
FROM tool_rows
WHERE tool_name IS NOT NULL
AND timestamp IS NOT NULL;

INSERT OR REPLACE INTO pi_usage
SELECT
    raw.raw_event_id as usage_id,
    raw.event_timestamp as timestamp,
    fs.session_id,
    fs.project_dir,
    json_extract_string(raw.json, '\$.message.provider') as provider,
    json_extract_string(raw.json, '\$.message.model') as model,
    et.thinking_level,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.input') AS BIGINT) as input_tokens,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cacheRead') AS BIGINT) as cached_input_tokens,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cacheWrite') AS BIGINT) as cache_write_tokens,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.output') AS BIGINT) as output_tokens,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.reasoning') AS BIGINT) as reasoning_tokens,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.totalTokens') AS BIGINT) as total_tokens,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cost.input') AS DOUBLE) as input_cost_usd,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cost.cacheRead') AS DOUBLE) as cached_input_cost_usd,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cost.cacheWrite') AS DOUBLE) as cache_write_cost_usd,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cost.output') AS DOUBLE) as output_cost_usd,
    TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cost.total') AS DOUBLE) as cost_usd,
    json_extract_string(raw.json, '\$.message.stopReason') as stop_reason,
    raw.source_file
FROM pi_raw raw
JOIN pi_file_sessions fs USING (source_file)
LEFT JOIN pi_event_thinking et ON raw.raw_event_id = et.raw_event_id
WHERE raw.event_type = 'message'
AND json_extract_string(raw.json, '\$.message.role') = 'assistant'
AND json_extract(raw.json, '\$.message.usage') IS NOT NULL
AND raw.event_timestamp IS NOT NULL;

DROP TABLE pi_event_thinking;
DROP VIEW pi_thinking;
EOF

    local pi_tool_count pi_usage_count
    pi_tool_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM pi_tools;" 2>/dev/null || echo "0")
    pi_usage_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM pi_usage;" 2>/dev/null || echo "0")
    echo "  Pi: $pi_tool_count tool calls, $pi_usage_count usage rows"
}

load_pi_messages() {
    local PI_FILE_COUNT PI_FILE_LIST_SQL
    pi_file_list_arg "$@"

    if [ "$PI_FILE_COUNT" -eq 0 ]; then
        return
    fi

    echo "  Pi: Bridging user and assistant messages..."

    duckdb "$DB_PATH" << EOF
CREATE OR REPLACE TEMP VIEW pi_raw AS
SELECT
    TRY_CAST(json_extract_string(json, '\$.timestamp') AS TIMESTAMP) as event_timestamp,
    json,
    filename as source_file,
    MD5(filename || '|' || COALESCE(json_extract_string(json, '\$.id'), CAST(json AS VARCHAR))) as raw_event_id,
    json_extract_string(json, '\$.type') as event_type
FROM read_ndjson_objects(${PI_FILE_LIST_SQL}, filename=true);

CREATE OR REPLACE TEMP TABLE pi_file_sessions AS
SELECT
    source_file,
    FIRST(json_extract_string(json, '\$.id') ORDER BY event_timestamp) as session_id,
    FIRST(json_extract_string(json, '\$.cwd') ORDER BY event_timestamp) as project_dir
FROM pi_raw
WHERE event_type = 'session'
GROUP BY source_file;

INSERT INTO messages (
    uuid, parent_uuid, session_id, role, harness, interface, model,
    content, thinking, timestamp, project_dir, git_branch, repo_name,
    worktree_branch, is_worktree, is_sidechain, input_tokens, output_tokens,
    cache_write_tokens, cache_read_tokens, tool_use_count, source_file
)
WITH pi_message_rows AS (
    SELECT
        raw.raw_event_id,
        CASE
            WHEN json_extract_string(raw.json, '\$.parentId') IS NULL THEN NULL
            ELSE MD5(raw.source_file || '|' || json_extract_string(raw.json, '\$.parentId'))
        END as parent_uuid,
        fs.session_id,
        json_extract_string(raw.json, '\$.message.role') as role,
        raw.event_timestamp as timestamp,
        fs.project_dir,
        json_extract_string(raw.json, '\$.message.model') as model,
        STRING_AGG(
            CASE WHEN json_extract_string(block.value, '\$.type') = 'text'
                 THEN json_extract_string(block.value, '\$.text')
            END, E'\n'
        ) as content,
        STRING_AGG(
            CASE WHEN json_extract_string(block.value, '\$.type') = 'thinking'
                 THEN json_extract_string(block.value, '\$.thinking')
            END, E'\n'
        ) as thinking,
        COUNT(CASE WHEN json_extract_string(block.value, '\$.type') = 'toolCall' THEN 1 END) as tool_use_count,
        TRY_CAST(json_extract_string(raw.json, '\$.message.usage.input') AS INTEGER) as input_tokens,
        TRY_CAST(json_extract_string(raw.json, '\$.message.usage.output') AS INTEGER) as output_tokens,
        TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cacheWrite') AS INTEGER) as cache_write_tokens,
        TRY_CAST(json_extract_string(raw.json, '\$.message.usage.cacheRead') AS INTEGER) as cache_read_tokens,
        raw.source_file
    FROM pi_raw raw
    JOIN pi_file_sessions fs USING (source_file),
    LATERAL UNNEST(
        CASE
            WHEN json_array_length(json_extract(raw.json, '\$.message.content')) > 0
            THEN from_json(json_extract(raw.json, '\$.message.content')::VARCHAR, '["JSON"]')
            ELSE ['{}']::JSON[]
        END
    ) as block(value)
    WHERE raw.event_type = 'message'
    AND json_extract_string(raw.json, '\$.message.role') IN ('user', 'assistant')
    GROUP BY
        raw.raw_event_id,
        parent_uuid,
        raw.source_file,
        fs.session_id,
        role,
        raw.event_timestamp,
        fs.project_dir,
        model,
        input_tokens,
        output_tokens,
        cache_write_tokens,
        cache_read_tokens
)
SELECT
    raw_event_id as uuid,
    parent_uuid,
    session_id,
    role,
    'pi' as harness,
    NULL as interface,
    model,
    content,
    thinking,
    timestamp,
    project_dir,
    NULL as git_branch,
    $(worktree_repo_sql project_dir) as repo_name,
    $(worktree_branch_sql project_dir) as worktree_branch,
    $(worktree_is_sql project_dir) as is_worktree,
    FALSE as is_sidechain,
    input_tokens,
    output_tokens,
    cache_write_tokens,
    cache_read_tokens,
    tool_use_count,
    source_file
FROM pi_message_rows
WHERE timestamp IS NOT NULL
AND (role = 'assistant' OR content IS NOT NULL);

DROP TABLE pi_file_sessions;
DROP VIEW pi_raw;
EOF

    local pi_msg_count
    pi_msg_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM messages WHERE harness='pi';" 2>/dev/null || echo "0")
    echo "  Pi: $pi_msg_count searchable messages"
}

# ============================================================================
# Views
# ============================================================================

create_views() {
    duckdb "$DB_PATH" << 'EOF'
-- ============================================================================
-- CLAUDE SESSIONS (messages establish sessions; tools provide tool counts)
-- ============================================================================
CREATE OR REPLACE TABLE claude_sessions AS
WITH message_activity AS (
    SELECT
        session_id,
        FIRST(project_dir ORDER BY timestamp) AS project_dir,
        FIRST(repo_name ORDER BY timestamp) AS repo_name,
        FIRST(interface ORDER BY timestamp) AS interface,
        FIRST(worktree_branch ORDER BY timestamp) AS worktree_branch,
        BOOL_OR(is_worktree) AS is_worktree,
        MIN(timestamp) AS started_at,
        MAX(timestamp) AS ended_at
    FROM messages
    WHERE harness = 'claude_code' AND session_id IS NOT NULL
    GROUP BY session_id
),
tool_stats AS (
    SELECT
        session_id,
        COUNT(*) AS tool_count,
        COUNT(DISTINCT tool_name) AS unique_tools
    FROM claude_tools
    WHERE session_id IS NOT NULL
    GROUP BY session_id
)
SELECT
    m.session_id,
    m.project_dir,
    m.repo_name,
    m.interface,
    m.worktree_branch,
    m.is_worktree,
    m.started_at,
    m.ended_at,
    COALESCE(t.tool_count, 0) AS tool_count,
    COALESCE(t.unique_tools, 0) AS unique_tools
FROM message_activity m
LEFT JOIN tool_stats t USING (session_id);

-- ============================================================================
-- CODEX SESSIONS (derived from session metadata + activity)
-- ============================================================================
CREATE OR REPLACE TABLE codex_sessions AS
WITH activity AS (
    SELECT
        session_id,
        MIN(timestamp) as started_at,
        MAX(timestamp) as ended_at
    FROM (
        SELECT session_id, timestamp FROM codex_tools
        UNION ALL
        SELECT session_id, timestamp FROM messages WHERE harness = 'codex'
        UNION ALL
        SELECT session_id, timestamp FROM codex_token_counts
    )
    WHERE session_id IS NOT NULL
    GROUP BY session_id
),
tool_stats AS (
    SELECT
        session_id,
        COUNT(*) as tool_count,
        COUNT(DISTINCT tool_name) as unique_tools
    FROM codex_tools
    GROUP BY session_id
)
SELECT
    COALESCE(sm.session_id, activity.session_id, idx.session_id) as session_id,
    sm.project_dir,
    sm.repo_name,
    sm.worktree_branch,
    sm.is_worktree,
    COALESCE(activity.started_at, sm.started_at) as started_at,
    activity.ended_at,
    idx.thread_name,
    sm.source,
    sm.originator,
    sm.cli_version,
    sm.model_provider,
    sm.git_branch,
    sm.git_sha,
    sm.git_origin_url,
    COALESCE(tool_stats.tool_count, 0) as tool_count,
    COALESCE(tool_stats.unique_tools, 0) as unique_tools
FROM codex_session_meta sm
FULL OUTER JOIN activity ON sm.session_id = activity.session_id
FULL OUTER JOIN codex_session_index idx
    ON COALESCE(sm.session_id, activity.session_id) = idx.session_id
LEFT JOIN tool_stats
    ON COALESCE(sm.session_id, activity.session_id, idx.session_id) = tool_stats.session_id;

-- ============================================================================
-- PI SESSIONS (derived from session metadata + activity)
-- ============================================================================
CREATE OR REPLACE TABLE pi_sessions AS
WITH activity AS (
    SELECT
        session_id,
        MIN(timestamp) as started_at,
        MAX(timestamp) as ended_at
    FROM (
        SELECT session_id, timestamp FROM pi_tools
        UNION ALL
        SELECT session_id, timestamp FROM messages WHERE harness = 'pi'
        UNION ALL
        SELECT session_id, timestamp FROM pi_usage
    )
    WHERE session_id IS NOT NULL
    GROUP BY session_id
),
tool_stats AS (
    SELECT
        session_id,
        COUNT(*) as tool_count,
        COUNT(DISTINCT tool_name) as unique_tools
    FROM pi_tools
    GROUP BY session_id
)
SELECT
    COALESCE(sm.session_id, activity.session_id) as session_id,
    sm.project_dir,
    sm.repo_name,
    sm.worktree_branch,
    sm.is_worktree,
    sm.title,
    sm.version,
    COALESCE(activity.started_at, sm.started_at) as started_at,
    activity.ended_at,
    COALESCE(tool_stats.tool_count, 0) as tool_count,
    COALESCE(tool_stats.unique_tools, 0) as unique_tools
FROM pi_session_meta sm
FULL OUTER JOIN activity ON sm.session_id = activity.session_id
LEFT JOIN tool_stats
    ON COALESCE(sm.session_id, activity.session_id) = tool_stats.session_id;

-- ============================================================================
-- TURN DURATIONS VIEW
-- ============================================================================
CREATE OR REPLACE VIEW turn_durations AS
SELECT session_id, timestamp, duration_ms, repo_name, git_branch
FROM system_events WHERE subtype = 'turn_duration' AND duration_ms IS NOT NULL;

-- ============================================================================
-- API ERRORS VIEW
-- ============================================================================
CREATE OR REPLACE VIEW api_errors AS
SELECT * FROM system_events WHERE subtype = 'api_error';

-- ============================================================================
-- UNIFIED INTERACTIONS VIEW
-- ============================================================================
CREATE OR REPLACE VIEW interactions AS
SELECT
    TRY_CAST(timestamp AS TIMESTAMP) as timestamp,
    TRY_CAST(timestamp AS TIMESTAMP) AT TIME ZONE 'UTC'
        AT TIME ZONE current_setting('TimeZone') as local_timestamp,
    'claude_code' as source,
    interface,
    session_id as session_id,
    project_dir as project,
    REGEXP_EXTRACT(project_dir, '[^/]+$') as project_name,
    repo_name,
    worktree_branch,
    is_worktree,
    model,
    'tool_use' as interaction_type,
    tool_name as category,
    context as detail,
    input_tokens,
    output_tokens,
    cache_write_tokens,
    cache_read_tokens,
    input_tokens + output_tokens as total_tokens
FROM claude_tools
WHERE timestamp IS NOT NULL

UNION ALL

SELECT
    TRY_CAST(timestamp AS TIMESTAMP) as timestamp,
    TRY_CAST(timestamp AS TIMESTAMP) AT TIME ZONE 'UTC'
        AT TIME ZONE current_setting('TimeZone') as local_timestamp,
    'codex' as source,
    NULL as interface,
    session_id as session_id,
    project_dir as project,
    REGEXP_EXTRACT(project_dir, '[^/]+$') as project_name,
    repo_name,
    worktree_branch,
    is_worktree,
    model,
    'tool_use' as interaction_type,
    tool_name as category,
    context as detail,
    NULL as input_tokens,
    NULL as output_tokens,
    NULL as cache_write_tokens,
    NULL as cache_read_tokens,
    NULL as total_tokens
FROM codex_tools
WHERE timestamp IS NOT NULL

UNION ALL

SELECT
    TRY_CAST(timestamp AS TIMESTAMP) as timestamp,
    TRY_CAST(timestamp AS TIMESTAMP) AT TIME ZONE 'UTC'
        AT TIME ZONE current_setting('TimeZone') as local_timestamp,
    'cursor' as source,
    NULL as interface,
    workspace_id as session_id,
    workspace_path as project,
    REGEXP_EXTRACT(workspace_path, '[^/]+$') as project_name,
    REGEXP_EXTRACT(workspace_path, '[^/]+$') as repo_name,
    NULL as worktree_branch,
    FALSE as is_worktree,
    NULL as model,
    'prompt' as interaction_type,
    'Prompt' as category,
    LEFT(prompt_text, 200) as detail,
    NULL as input_tokens,
    NULL as output_tokens,
    NULL as cache_write_tokens,
    NULL as cache_read_tokens,
    NULL as total_tokens
FROM cursor_prompts
WHERE timestamp IS NOT NULL

UNION ALL

-- Pi tool rows carry no token columns: message-level usage lives in pi_usage
-- and would double-count if repeated on each toolCall block.
SELECT
    TRY_CAST(timestamp AS TIMESTAMP) as timestamp,
    TRY_CAST(timestamp AS TIMESTAMP) AT TIME ZONE 'UTC'
        AT TIME ZONE current_setting('TimeZone') as local_timestamp,
    'pi' as source,
    NULL as interface,
    session_id as session_id,
    project_dir as project,
    REGEXP_EXTRACT(project_dir, '[^/]+$') as project_name,
    repo_name,
    worktree_branch,
    is_worktree,
    model,
    'tool_use' as interaction_type,
    tool_name as category,
    context as detail,
    NULL as input_tokens,
    NULL as output_tokens,
    NULL as cache_write_tokens,
    NULL as cache_read_tokens,
    NULL as total_tokens
FROM pi_tools
WHERE timestamp IS NOT NULL;

-- ============================================================================
-- HOURLY ACTIVITY VIEW
-- ============================================================================
CREATE OR REPLACE VIEW hourly_activity AS
SELECT
    DATE_TRUNC('hour', local_timestamp) as hour,
    source,
    interface,
    COUNT(*) as interactions,
    COUNT(DISTINCT session_id) as sessions
FROM interactions
GROUP BY hour, source, interface;

-- ============================================================================
-- PROJECT ACTIVITY VIEW
-- ============================================================================
CREATE OR REPLACE VIEW project_activity AS
SELECT
    project_name,
    project,
    repo_name,
    interface,
    worktree_branch,
    is_worktree,
    source,
    COUNT(*) as interactions,
    COUNT(DISTINCT session_id) as sessions,
    MIN(timestamp) as first_activity,
    MAX(timestamp) as last_activity,
    COUNT(DISTINCT DATE_TRUNC('day', timestamp)) as active_days
FROM interactions
WHERE project_name IS NOT NULL AND project_name != 'unknown'
GROUP BY project_name, project, repo_name, interface, worktree_branch, is_worktree, source;

-- ============================================================================
-- REPO ACTIVITY VIEW
-- ============================================================================
CREATE OR REPLACE VIEW repo_activity AS
SELECT
    repo_name,
    source,
    interface,
    COUNT(*) as interactions,
    COUNT(DISTINCT session_id) as sessions,
    COUNT(DISTINCT worktree_branch) as worktrees,
    MIN(timestamp) as first_activity,
    MAX(timestamp) as last_activity,
    COUNT(DISTINCT DATE_TRUNC('day', timestamp)) as active_days
FROM interactions
WHERE repo_name IS NOT NULL
GROUP BY repo_name, source, interface;

-- ============================================================================
-- CATEGORY BREAKDOWN VIEW
-- ============================================================================
CREATE OR REPLACE VIEW category_breakdown AS
SELECT
    source,
    interface,
    category,
    COUNT(*) as uses,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY source, interface), 2) as pct_of_source,
    MIN(timestamp) as first_used,
    MAX(timestamp) as last_used
FROM interactions
GROUP BY source, interface, category
ORDER BY source, interface, uses DESC;

-- ============================================================================
-- DAILY ACTIVITY BY SOURCE VIEW
-- ============================================================================
CREATE OR REPLACE VIEW daily_by_source AS
SELECT
    DATE_TRUNC('day', local_timestamp)::DATE as date,
    source,
    interface,
    COUNT(*) as interactions,
    COUNT(DISTINCT session_id) as sessions,
    COUNT(DISTINCT project_name) as projects
FROM interactions
GROUP BY date, source, interface;

-- ============================================================================
-- WEEKLY SUMMARY VIEW
-- ============================================================================
CREATE OR REPLACE VIEW weekly_summary AS
SELECT
    DATE_TRUNC('week', local_timestamp)::DATE as week_start,
    source,
    interface,
    COUNT(*) as interactions,
    COUNT(DISTINCT session_id) as sessions,
    COUNT(DISTINCT project_name) as projects,
    COUNT(DISTINCT DATE_TRUNC('day', timestamp)) as active_days
FROM interactions
GROUP BY week_start, source, interface;

-- ============================================================================
-- SESSION SUMMARY VIEW
-- ============================================================================
CREATE OR REPLACE VIEW session_summary AS
SELECT
    session_id,
    source,
    interface,
    project_name,
    project,
    repo_name,
    worktree_branch,
    is_worktree,
    MIN(timestamp) as started_at,
    MAX(timestamp) as ended_at,
    EXTRACT(EPOCH FROM (MAX(timestamp) - MIN(timestamp))) / 60 as duration_minutes,
    COUNT(*) as interactions,
    COUNT(DISTINCT category) as unique_categories
FROM interactions
GROUP BY session_id, source, interface, project_name, project, repo_name, worktree_branch, is_worktree;

-- ============================================================================
-- PEAK HOURS VIEW
-- ============================================================================
CREATE OR REPLACE VIEW peak_hours AS
SELECT
    EXTRACT(HOUR FROM local_timestamp)::INTEGER as hour_of_day,
    source,
    interface,
    COUNT(*) as interactions,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY source, interface), 2) as pct
FROM interactions
GROUP BY hour_of_day, source, interface
ORDER BY hour_of_day, source, interface;

-- ============================================================================
-- RECENT INTERACTIONS VIEW
-- ============================================================================
CREATE OR REPLACE VIEW recent_interactions AS
SELECT
    timestamp,
    local_timestamp,
    source,
    interface,
    category,
    repo_name,
    worktree_branch,
    project_name,
    detail
FROM interactions
ORDER BY timestamp DESC
LIMIT 100;

-- ============================================================================
-- DAILY SUMMARY VIEW
-- ============================================================================
CREATE OR REPLACE VIEW daily_summary AS
SELECT
    DATE_TRUNC('day', local_timestamp)::DATE as date,
    COUNT(*) FILTER (WHERE source='claude_code') as claude_tools,
    COUNT(*) FILTER (WHERE source='codex') as codex_tools,
    COUNT(*) FILTER (WHERE source='cursor') as cursor_prompts,
    COUNT(*) FILTER (WHERE source='pi') as pi_tools,
    COUNT(*) as total
FROM interactions
GROUP BY date;

-- ============================================================================
-- TOOL SUMMARY VIEW
-- ============================================================================
CREATE OR REPLACE VIEW tool_summary AS
SELECT
    source,
    tool_name,
    COUNT(*) as uses,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY source), 2) as pct_of_source,
    MIN(timestamp) as first_used,
    MAX(timestamp) as last_used
FROM (
    SELECT 'claude_code' as source, tool_name, timestamp FROM claude_tools
    UNION ALL
    SELECT 'codex' as source, tool_name, timestamp FROM codex_tools
    UNION ALL
    SELECT 'pi' as source, tool_name, timestamp FROM pi_tools
) combined_tools
GROUP BY source, tool_name
ORDER BY source, uses DESC;

-- ============================================================================
-- MODEL PRICING TABLE
-- ============================================================================
CREATE TABLE IF NOT EXISTS model_pricing (
    model VARCHAR PRIMARY KEY,
    input_rate DECIMAL(10,4),
    output_rate DECIMAL(10,4),
    cache_write_rate DECIMAL(10,4),
    cache_read_rate DECIMAL(10,4)
);

INSERT OR IGNORE INTO model_pricing VALUES
    ('claude-fable-5', 10.0, 50.0, 12.50, 1.00),
    ('claude-opus-4-5-20251101', 5.0, 25.0, 6.25, 0.50),
    ('claude-sonnet-4-5-20250929', 3.0, 15.0, 3.75, 0.30),
    ('claude-haiku-4-5-20251001', 1.0, 5.0, 1.25, 0.10),
    ('claude-opus-4-6', 5.0, 25.0, 6.25, 0.50),
    ('claude-opus-4-7', 5.0, 25.0, 6.25, 0.50),
    ('claude-opus-4-8', 5.0, 25.0, 6.25, 0.50),
    ('claude-sonnet-4-6', 3.0, 15.0, 3.75, 0.30),
    ('claude-sonnet-5', 2.0, 10.0, 2.50, 0.20);

-- Migrate only the obsolete built-in defaults; preserve user-edited rates.
UPDATE model_pricing
SET input_rate=5.0, output_rate=25.0, cache_write_rate=6.25, cache_read_rate=0.50
WHERE model='claude-opus-4-5-20251101'
  AND input_rate=15.0 AND output_rate=75.0
  AND cache_write_rate=18.75 AND cache_read_rate=1.50;
UPDATE model_pricing
SET input_rate=1.0, output_rate=5.0, cache_write_rate=1.25, cache_read_rate=0.10
WHERE model='claude-haiku-4-5-20251001'
  AND input_rate=0.80 AND output_rate=4.0
  AND cache_write_rate=1.00 AND cache_read_rate=0.08;

-- Codex logs contain OpenAI token snapshots, but subscription and purchased-
-- credit billing are not present. These rates therefore estimate the
-- equivalent Standard API cost; unknown/internal models remain unpriced.
CREATE TABLE IF NOT EXISTS codex_model_pricing (
    model VARCHAR PRIMARY KEY,
    input_rate DECIMAL(10,4),
    cached_input_rate DECIMAL(10,4),
    output_rate DECIMAL(10,4),
    long_context_threshold BIGINT,
    long_context_input_multiplier DECIMAL(5,2),
    long_context_output_multiplier DECIMAL(5,2),
    verified_on DATE,
    source_url VARCHAR
);

INSERT OR IGNORE INTO codex_model_pricing VALUES
    ('gpt-5.5', 5.0, 0.50, 30.0, 272000, 2.0, 1.5,
     DATE '2026-07-11', 'https://developers.openai.com/api/docs/models/gpt-5.5'),
    ('gpt-5.6', 5.0, 0.50, 30.0, 272000, 2.0, 1.5,
     DATE '2026-07-11', 'https://developers.openai.com/api/docs/models/gpt-5.6-sol'),
    ('gpt-5.6-sol', 5.0, 0.50, 30.0, 272000, 2.0, 1.5,
     DATE '2026-07-11', 'https://developers.openai.com/api/docs/models/gpt-5.6-sol'),
    ('gpt-5.6-terra', 2.5, 0.25, 15.0, 272000, 2.0, 1.5,
     DATE '2026-07-11', 'https://developers.openai.com/api/docs/models/gpt-5.6-terra'),
    ('gpt-5.6-luna', 1.0, 0.10, 6.0, 272000, 2.0, 1.5,
     DATE '2026-07-11', 'https://developers.openai.com/api/docs/models/gpt-5.6-luna'),
    ('gpt-5.4', 2.5, 0.25, 15.0, 272000, 2.0, 1.5,
     DATE '2026-07-11', 'https://developers.openai.com/api/docs/models/gpt-5.4');

-- ============================================================================
-- USAGE WITH COST VIEW
-- ============================================================================
CREATE OR REPLACE VIEW usage_with_cost AS
WITH assistant_turns AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY COALESCE(uuid, search_id)
            ORDER BY timestamp, source_file
        ) AS usage_rank
    FROM messages
    WHERE harness = 'claude_code'
      AND role = 'assistant'
      AND model IS NOT NULL
)
SELECT
    t.uuid AS usage_id,
    t.timestamp,
    t.session_id,
    t.project_dir,
    t.repo_name,
    t.interface,
    t.worktree_branch,
    t.is_worktree,
    t.model,
    t.input_tokens,
    t.output_tokens,
    t.cache_write_tokens,
    t.cache_read_tokens,
    p.input_rate,
    p.output_rate,
    p.cache_write_rate,
    p.cache_read_rate,
    CASE WHEN p.model IS NULL THEN 'unknown_model' ELSE 'priced' END AS pricing_status,
    CASE
        WHEN p.model IS NULL THEN NULL
        ELSE ROUND((
            COALESCE(t.input_tokens, 0) * p.input_rate +
            COALESCE(t.output_tokens, 0) * p.output_rate +
            COALESCE(t.cache_write_tokens, 0) * p.cache_write_rate +
            COALESCE(t.cache_read_tokens, 0) * p.cache_read_rate
        ) / 1000000.0, 6)
    END AS cost_usd
FROM assistant_turns t
LEFT JOIN model_pricing p ON t.model = p.model
WHERE t.usage_rank = 1;

-- ============================================================================
-- CODEX USAGE WITH API-EQUIVALENT COST VIEW
-- ============================================================================
CREATE OR REPLACE VIEW codex_usage_with_cost AS
WITH token_rows AS (
    SELECT
        MD5(
            COALESCE(t.session_id, '') || '|' ||
            COALESCE(CAST(t.timestamp AS VARCHAR), '') || '|' ||
            COALESCE(t.source_file, '')
        ) AS usage_id,
        t.timestamp,
        t.session_id,
        t.project_dir,
        s.repo_name,
        t.model,
        t.reasoning_effort,
        GREATEST(COALESCE(t.input_tokens, 0) - COALESCE(t.cached_input_tokens, 0), 0)
            AS uncached_input_tokens,
        COALESCE(t.cached_input_tokens, 0) AS cached_input_tokens,
        COALESCE(t.output_tokens, 0) AS output_tokens,
        COALESCE(t.reasoning_output_tokens, 0) AS reasoning_output_tokens,
        t.model_context_window,
        p.input_rate,
        p.cached_input_rate,
        p.output_rate,
        p.long_context_threshold,
        p.long_context_input_multiplier,
        p.long_context_output_multiplier,
        p.verified_on,
        p.source_url,
        p.model IS NOT NULL AS is_priced,
        CASE
            WHEN p.long_context_threshold IS NULL THEN FALSE
            ELSE COALESCE(t.input_tokens, 0) > p.long_context_threshold
        END AS is_long_context
    FROM codex_token_counts t
    LEFT JOIN codex_sessions s USING (session_id)
    LEFT JOIN codex_model_pricing p ON t.model = p.model
), rated AS (
    SELECT *,
        CASE WHEN is_long_context THEN long_context_input_multiplier ELSE 1.0 END
            AS input_multiplier,
        CASE WHEN is_long_context THEN long_context_output_multiplier ELSE 1.0 END
            AS output_multiplier
    FROM token_rows
)
SELECT
    usage_id,
    timestamp,
    session_id,
    project_dir,
    repo_name,
    model,
    reasoning_effort,
    uncached_input_tokens,
    cached_input_tokens,
    output_tokens,
    reasoning_output_tokens,
    model_context_window,
    input_rate,
    cached_input_rate,
    output_rate,
    is_long_context,
    verified_on,
    source_url AS pricing_source_url,
    CASE WHEN is_priced THEN 'priced' ELSE 'unknown_model' END AS pricing_status,
    CASE WHEN NOT is_priced THEN NULL ELSE ROUND(
        uncached_input_tokens * input_rate * input_multiplier / 1000000.0, 6
    ) END AS input_cost_usd,
    CASE WHEN NOT is_priced THEN NULL ELSE ROUND(
        cached_input_tokens * cached_input_rate * input_multiplier / 1000000.0, 6
    ) END AS cached_input_cost_usd,
    CASE WHEN NOT is_priced THEN NULL ELSE ROUND(
        output_tokens * output_rate * output_multiplier / 1000000.0, 6
    ) END AS output_cost_usd,
    CASE WHEN NOT is_priced THEN NULL ELSE ROUND((
        uncached_input_tokens * input_rate * input_multiplier +
        cached_input_tokens * cached_input_rate * input_multiplier +
        output_tokens * output_rate * output_multiplier
    ) / 1000000.0, 6) END AS cost_usd,
    CASE WHEN NOT is_priced THEN NULL ELSE ROUND((
        (uncached_input_tokens + cached_input_tokens) * input_rate * input_multiplier +
        output_tokens * output_rate * output_multiplier
    ) / 1000000.0, 6) END AS cost_without_cache_usd,
    CASE WHEN NOT is_priced THEN NULL ELSE ROUND(
        cached_input_tokens * (input_rate - cached_input_rate) * input_multiplier /
        1000000.0, 6
    ) END AS cache_savings_usd
FROM rated;

-- ============================================================================
-- PI USAGE WITH HARNESS-RECORDED COST VIEW
-- ============================================================================
-- Pi logs dollars per assistant message at request time, so this view exposes
-- recorded values ('native' pricing_status) rather than API-equivalent
-- estimates from a local pricing table.
CREATE OR REPLACE VIEW pi_usage_with_cost AS
SELECT
    u.usage_id,
    u.timestamp,
    u.session_id,
    u.project_dir,
    s.repo_name,
    u.provider,
    u.model,
    u.thinking_level,
    COALESCE(u.input_tokens, 0) AS uncached_input_tokens,
    COALESCE(u.cached_input_tokens, 0) AS cached_input_tokens,
    COALESCE(u.cache_write_tokens, 0) AS cache_write_tokens,
    COALESCE(u.output_tokens, 0) AS output_tokens,
    u.reasoning_tokens,
    u.total_tokens,
    u.stop_reason,
    CASE WHEN u.cost_usd IS NULL THEN 'unknown_model' ELSE 'native' END AS pricing_status,
    u.input_cost_usd,
    u.cached_input_cost_usd,
    u.cache_write_cost_usd,
    u.output_cost_usd,
    u.cost_usd
FROM pi_usage u
LEFT JOIN pi_sessions s USING (session_id);

-- ============================================================================
-- UNIFIED PROVIDER COST AND CACHE VIEWS
-- ============================================================================
CREATE OR REPLACE VIEW provider_usage_with_cost AS
SELECT
    usage_id,
    timestamp,
    session_id,
    'anthropic' AS provider,
    'claude_code' AS harness,
    project_dir,
    repo_name,
    model,
    COALESCE(input_tokens, 0)::BIGINT AS uncached_input_tokens,
    COALESCE(cache_read_tokens, 0)::BIGINT AS cached_input_tokens,
    COALESCE(cache_write_tokens, 0)::BIGINT AS cache_write_tokens,
    COALESCE(output_tokens, 0)::BIGINT AS output_tokens,
    NULL::BIGINT AS reasoning_output_tokens,
    input_rate,
    cache_read_rate AS cached_input_rate,
    cache_write_rate,
    output_rate,
    FALSE AS is_long_context,
    pricing_status,
    CASE WHEN pricing_status <> 'priced' THEN NULL ELSE ROUND(
        COALESCE(input_tokens, 0) * input_rate / 1000000.0, 6
    ) END AS input_cost_usd,
    CASE WHEN pricing_status <> 'priced' THEN NULL ELSE ROUND(
        COALESCE(cache_read_tokens, 0) * cache_read_rate / 1000000.0, 6
    ) END AS cached_input_cost_usd,
    CASE WHEN pricing_status <> 'priced' THEN NULL ELSE ROUND(
        COALESCE(cache_write_tokens, 0) * cache_write_rate / 1000000.0, 6
    ) END AS cache_write_cost_usd,
    CASE WHEN pricing_status <> 'priced' THEN NULL ELSE ROUND(
        COALESCE(output_tokens, 0) * output_rate / 1000000.0, 6
    ) END AS output_cost_usd,
    cost_usd,
    CASE WHEN pricing_status <> 'priced' THEN NULL ELSE ROUND((
        (COALESCE(input_tokens, 0) + COALESCE(cache_read_tokens, 0) +
         COALESCE(cache_write_tokens, 0)) * input_rate +
        COALESCE(output_tokens, 0) * output_rate
    ) / 1000000.0, 6) END AS cost_without_cache_usd,
    CASE WHEN pricing_status <> 'priced' THEN NULL ELSE ROUND((
        COALESCE(cache_read_tokens, 0) * (input_rate - cache_read_rate) -
        COALESCE(cache_write_tokens, 0) * (cache_write_rate - input_rate)
    ) / 1000000.0, 6) END AS cache_savings_usd
FROM usage_with_cost
UNION ALL
SELECT
    usage_id,
    timestamp,
    session_id,
    'openai' AS provider,
    'codex' AS harness,
    project_dir,
    repo_name,
    model,
    uncached_input_tokens,
    cached_input_tokens,
    0::BIGINT AS cache_write_tokens,
    output_tokens,
    reasoning_output_tokens,
    input_rate,
    cached_input_rate,
    NULL::DECIMAL(10,4) AS cache_write_rate,
    output_rate,
    is_long_context,
    pricing_status,
    input_cost_usd,
    cached_input_cost_usd,
    0.0::DOUBLE AS cache_write_cost_usd,
    output_cost_usd,
    cost_usd,
    cost_without_cache_usd,
    cache_savings_usd
FROM codex_usage_with_cost
UNION ALL
SELECT
    usage_id,
    timestamp,
    session_id,
    provider,
    'pi' AS harness,
    project_dir,
    repo_name,
    model,
    uncached_input_tokens,
    cached_input_tokens,
    cache_write_tokens,
    output_tokens,
    reasoning_tokens AS reasoning_output_tokens,
    NULL::DECIMAL(10,4) AS input_rate,
    NULL::DECIMAL(10,4) AS cached_input_rate,
    NULL::DECIMAL(10,4) AS cache_write_rate,
    NULL::DECIMAL(10,4) AS output_rate,
    FALSE AS is_long_context,
    pricing_status,
    input_cost_usd,
    cached_input_cost_usd,
    cache_write_cost_usd,
    output_cost_usd,
    cost_usd,
    NULL::DOUBLE AS cost_without_cache_usd,
    NULL::DOUBLE AS cache_savings_usd
FROM pi_usage_with_cost;

CREATE OR REPLACE VIEW provider_cost_summary AS
SELECT
    provider,
    harness,
    model,
    pricing_status,
    COUNT(*) AS usage_rows,
    COUNT(DISTINCT session_id) AS sessions,
    SUM(uncached_input_tokens) AS uncached_input_tokens,
    SUM(cached_input_tokens) AS cached_input_tokens,
    SUM(cache_write_tokens) AS cache_write_tokens,
    SUM(output_tokens) AS output_tokens,
    SUM(reasoning_output_tokens) AS reasoning_output_tokens,
    SUM(is_long_context::INTEGER) AS long_context_rows,
    ROUND(SUM(input_cost_usd), 2) AS input_cost_usd,
    ROUND(SUM(cached_input_cost_usd), 2) AS cached_input_cost_usd,
    ROUND(SUM(cache_write_cost_usd), 2) AS cache_write_cost_usd,
    ROUND(SUM(output_cost_usd), 2) AS output_cost_usd,
    ROUND(SUM(cost_usd), 2) AS cost_usd,
    ROUND(SUM(cost_without_cache_usd), 2) AS cost_without_cache_usd,
    ROUND(SUM(cache_savings_usd), 2) AS cache_savings_usd
FROM provider_usage_with_cost
GROUP BY provider, harness, model, pricing_status;

CREATE OR REPLACE VIEW cache_efficiency_summary AS
SELECT
    provider,
    harness,
    model,
    pricing_status,
    COUNT(*) AS usage_rows,
    SUM(uncached_input_tokens + cached_input_tokens + cache_write_tokens)
        AS total_input_tokens,
    SUM(cached_input_tokens) AS cached_input_tokens,
    ROUND(
        100.0 * SUM(cached_input_tokens) /
        NULLIF(SUM(uncached_input_tokens + cached_input_tokens + cache_write_tokens), 0),
        2
    ) AS cache_utilization_pct,
    ROUND(SUM(cost_usd), 2) AS cost_usd,
    ROUND(SUM(cost_without_cache_usd), 2) AS cost_without_cache_usd,
    ROUND(SUM(cache_savings_usd), 2) AS cache_savings_usd,
    ROUND(
        100.0 * SUM(cache_savings_usd) / NULLIF(SUM(cost_without_cache_usd), 0),
        2
    ) AS cost_reduction_pct
FROM provider_usage_with_cost
GROUP BY provider, harness, model, pricing_status;

-- ============================================================================
-- COST SUMMARY VIEW
-- ============================================================================
CREATE OR REPLACE VIEW cost_summary AS
SELECT
    repo_name,
    model,
    COUNT(*) as assistant_turns,
    ROUND(SUM(input_tokens) / 1000000.0, 3) as input_M,
    ROUND(SUM(output_tokens) / 1000000.0, 3) as output_M,
    ROUND(SUM(cache_write_tokens) / 1000000.0, 3) as cache_write_M,
    ROUND(SUM(cache_read_tokens) / 1000000.0, 3) as cache_read_M,
    ROUND(SUM(cost_usd), 2) as cost_usd
FROM usage_with_cost
GROUP BY repo_name, model;

-- ============================================================================
-- CONVERSATION SEARCH VIEW
-- ============================================================================
CREATE OR REPLACE VIEW conversation_search AS
SELECT
    uuid, parent_uuid, session_id, role, harness, interface, model,
    LEFT(content, 500) as content_preview,
    LEFT(thinking, 500) as thinking_preview,
    content, thinking,
    timestamp, project_dir, git_branch,
    repo_name, worktree_branch, is_worktree, is_sidechain,
    input_tokens, output_tokens, cache_write_tokens, cache_read_tokens,
    tool_use_count
FROM messages
WHERE content IS NOT NULL OR thinking IS NOT NULL;

-- ============================================================================
-- SESSION MESSAGES VIEW
-- ============================================================================
CREATE OR REPLACE VIEW session_messages AS
SELECT
    session_id,
    harness,
    interface,
    repo_name,
    project_dir,
    MIN(timestamp) as started_at,
    MAX(timestamp) as ended_at,
    COUNT(*) as message_count,
    COUNT(CASE WHEN role = 'user' THEN 1 END) as user_messages,
    COUNT(CASE WHEN role = 'assistant' THEN 1 END) as assistant_messages,
    SUM(COALESCE(input_tokens, 0)) as total_input_tokens,
    SUM(COALESCE(output_tokens, 0)) as total_output_tokens,
    FIRST(CASE WHEN role = 'user' AND content IS NOT NULL
          THEN LEFT(content, 200) END ORDER BY timestamp) as topic
FROM messages
GROUP BY session_id, harness, interface, repo_name, project_dir;

-- ============================================================================
-- SESSION OVERVIEW VIEW
-- ============================================================================
CREATE OR REPLACE VIEW session_overview AS
SELECT
    sm.session_id, sm.repo_name, sm.interface, sm.started_at, sm.ended_at,
    sm.message_count, sm.user_messages, sm.assistant_messages,
    COALESCE(si.summary, csi.thread_name, ps.title) as summary,
    si.first_prompt,
    COALESCE(si.git_branch, cs.git_branch) as git_branch,
    sm.total_input_tokens, sm.total_output_tokens
FROM session_messages sm
LEFT JOIN _sessions_index si
    ON sm.harness = 'claude_code'
    AND sm.session_id = si.session_id
LEFT JOIN codex_session_index csi
    ON sm.harness = 'codex'
    AND sm.session_id = csi.session_id
LEFT JOIN codex_sessions cs
    ON sm.harness = 'codex'
    AND sm.session_id = cs.session_id
LEFT JOIN pi_sessions ps
    ON sm.harness = 'pi'
    AND sm.session_id = ps.session_id;

-- ============================================================================
-- RECENT CONVERSATIONS VIEW
-- ============================================================================
CREATE OR REPLACE VIEW recent_conversations AS
SELECT * FROM session_messages
ORDER BY started_at DESC
LIMIT 50;

-- ============================================================================
-- CONVERSATION PAIRS VIEW
-- ============================================================================
CREATE OR REPLACE VIEW conversation_pairs AS
SELECT
    u.uuid as user_uuid,
    a.uuid as assistant_uuid,
    u.session_id,
    u.repo_name,
    COALESCE(a.interface, u.interface) as interface,
    u.timestamp as user_timestamp,
    a.timestamp as assistant_timestamp,
    u.content as user_content,
    a.content as assistant_content,
    a.thinking as assistant_thinking,
    a.model,
    a.input_tokens,
    a.output_tokens,
    a.tool_use_count
FROM messages u
JOIN messages a ON a.parent_uuid = u.uuid
WHERE u.role = 'user' AND a.role = 'assistant';

-- ============================================================================
-- MESSAGE STATS VIEW
-- ============================================================================
CREATE OR REPLACE VIEW message_stats AS
SELECT
    DATE_TRUNC(
        'day',
        TRY_CAST(timestamp AS TIMESTAMP) AT TIME ZONE 'UTC'
            AT TIME ZONE current_setting('TimeZone')
    )::DATE as date,
    harness,
    interface,
    role,
    COUNT(*) as message_count,
    SUM(COALESCE(input_tokens, 0)) as input_tokens,
    SUM(COALESCE(output_tokens, 0)) as output_tokens
FROM messages
WHERE timestamp IS NOT NULL
GROUP BY date, harness, interface, role;

-- Metadata table
CREATE TABLE IF NOT EXISTS _metadata (
    key VARCHAR PRIMARY KEY,
    value VARCHAR,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT OR REPLACE INTO _metadata (key, value, updated_at)
VALUES ('last_load', CURRENT_TIMESTAMP::VARCHAR, CURRENT_TIMESTAMP);
EOF
}

# Create FTS index on messages for full-text search
create_fts_index() {
    echo "  Creating FTS index..."
    if ! duckdb "$DB_PATH" << 'EOF'
INSTALL fts; LOAD fts;
DROP SCHEMA IF EXISTS fts_main_messages_fts CASCADE;
DROP TABLE IF EXISTS messages_fts;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS search_id VARCHAR;
UPDATE messages
SET search_id = MD5(
    CAST(rowid AS VARCHAR) || '|' ||
    COALESCE(harness, '') || '|' ||
    COALESCE(source_file, '') || '|' ||
    COALESCE(uuid, '')
)
WHERE search_id IS NULL;
PRAGMA create_fts_index('messages', 'search_id', 'content', 'thinking',
    stemmer='porter', stopwords='english', overwrite=1);
EOF
    then
        echo "  Warning: FTS index unavailable; literal search remains available." >&2
    fi
}

# ============================================================================
# Loaded Files Tracking
# ============================================================================

populate_loaded_files() {
    echo "  Tracking loaded files..."
    stage_current_files
    duckdb "$DB_PATH" << 'EOF'
DELETE FROM _loaded_files;
INSERT INTO _loaded_files (file_path, mtime_ns, size_bytes, source_kind)
SELECT file_path, mtime_ns, size_bytes, source_kind
FROM _current_files;
DROP TABLE _current_files;
EOF

    local file_count
    file_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _loaded_files;" 2>/dev/null || echo "0")
    echo "  Tracking $file_count files"
}

# ============================================================================
# Full Load / Reload
# ============================================================================

build_all_data() {
    echo "Loading AI coding usage data..."
    echo ""
    ensure_reference_schema
    ensure_cursor_tables
    ensure_current_tables
    load_claude_data
    load_claude_messages
    load_system_events
    load_queue_operations
    load_pr_links
    load_sessions_index
    load_cursor_data
    load_cursor_messages
    load_codex_session_index
    load_codex_data
    load_codex_messages
    load_pi_data
    load_pi_messages
    create_views
    create_fts_index
    populate_loaded_files
}

load_all_data() {
    ensure_db_dir
    backup_db

    local original_db="$DB_PATH"
    local working_db
    working_db=$(mktemp "$DB_DIR/.analyze-usage-reload.XXXXXX")
    rm -f "$working_db"
    DB_PATH="$working_db"

    set +e
    ( set -e; build_all_data )
    local status=$?
    set -e
    DB_PATH="$original_db"

    if [ "$status" -ne 0 ]; then
        rm -f "$working_db"
        echo "Reload failed; the existing database was left unchanged." >&2
        return "$status"
    fi

    chmod 600 "$working_db" 2>/dev/null || true
    mv "$working_db" "$original_db"

    echo ""
    echo "Data loaded to: $original_db"
}

# ============================================================================
# Incremental Load
# ============================================================================

incremental_load() {
    ensure_db_dir
    ensure_reference_schema
    ensure_cursor_tables

    local needs_backfill
    needs_backfill=$(needs_interface_backfill)
    ensure_current_tables
    refresh_file_changes

    if [ "$needs_backfill" -eq 1 ]; then
        echo "  Schema change detected: rebuilding Claude interface metadata from all session logs..."
        duckdb "$DB_PATH" << 'EOF'
DELETE FROM _pending_file_changes WHERE source_kind = 'claude';
INSERT INTO _pending_file_changes
SELECT file_path, mtime_ns, size_bytes, source_kind, 'changed'
FROM _current_files
WHERE source_kind = 'claude';
EOF
    fi

    local count
    count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _pending_file_changes;")

    if [ "$count" -eq 0 ]; then
        # Derived schema can change even when source logs do not. Recreate
        # pricing tables and views so an analyzer upgrade is applied on the
        # first `update` instead of waiting for unrelated log activity.
        create_views
        duckdb "$DB_PATH" -c "DROP TABLE _pending_file_changes; DROP TABLE _current_files;"
        echo "Data is current. No new or changed files."
        return 0
    fi

    echo "Updating $count changed or deleted files..."
    echo ""

    local claude_affected_count claude_changed_count
    claude_affected_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _pending_file_changes WHERE source_kind='claude';")
    claude_changed_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _pending_file_changes WHERE source_kind='claude' AND change_kind='changed';")

    if [ "$claude_affected_count" -gt 0 ]; then
        duckdb "$DB_PATH" << 'EOF'
DELETE FROM claude_tools WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='claude'
);
DELETE FROM messages WHERE harness='claude_code' AND source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='claude'
);
DELETE FROM system_events WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='claude'
);
DELETE FROM queue_operations WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='claude'
);
DELETE FROM pr_links WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='claude'
);
EOF
    fi

    if [ "$claude_changed_count" -gt 0 ]; then
        local file_list_sql
        file_list_sql=$(pending_file_list_sql claude changed)

        load_claude_data "$file_list_sql"
        load_claude_messages "$file_list_sql"
        load_system_events "$file_list_sql"
        load_queue_operations "$file_list_sql"
        load_pr_links "$file_list_sql"
    fi

    # Always reload sessions-index (small, not file-tracked)
    load_sessions_index

    local cursor_affected_count codex_affected_count codex_changed_count
    cursor_affected_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _pending_file_changes WHERE source_kind='cursor';")
    codex_affected_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _pending_file_changes WHERE source_kind='codex';")
    codex_changed_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _pending_file_changes WHERE source_kind='codex' AND change_kind='changed' AND file_path LIKE '%.jsonl';")

    if [ "$cursor_affected_count" -gt 0 ]; then
        load_cursor_data
        load_cursor_messages
    fi

    if [ "$codex_affected_count" -gt 0 ]; then
        load_codex_session_index
        duckdb "$DB_PATH" << 'EOF'
DELETE FROM codex_session_meta WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='codex'
);
DELETE FROM codex_tools WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='codex'
);
DELETE FROM codex_token_counts WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='codex'
);
DELETE FROM codex_developer_messages WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='codex'
);
DELETE FROM messages WHERE harness='codex' AND source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='codex'
);
EOF
        if [ "$codex_changed_count" -gt 0 ]; then
            local codex_file_list_sql
            codex_file_list_sql=$(pending_file_list_sql codex changed)
            load_codex_data "$codex_file_list_sql"
            load_codex_messages "$codex_file_list_sql"
        fi
    fi

    local pi_affected_count pi_changed_count
    pi_affected_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _pending_file_changes WHERE source_kind='pi';")
    pi_changed_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _pending_file_changes WHERE source_kind='pi' AND change_kind='changed';")

    if [ "$pi_affected_count" -gt 0 ]; then
        duckdb "$DB_PATH" << 'EOF'
DELETE FROM pi_session_meta WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='pi'
);
DELETE FROM pi_tools WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='pi'
);
DELETE FROM pi_usage WHERE source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='pi'
);
DELETE FROM messages WHERE harness='pi' AND source_file IN (
    SELECT file_path FROM _pending_file_changes WHERE source_kind='pi'
);
EOF
        if [ "$pi_changed_count" -gt 0 ]; then
            local pi_file_list_sql
            pi_file_list_sql=$(pending_file_list_sql pi changed)
            load_pi_data "$pi_file_list_sql"
            load_pi_messages "$pi_file_list_sql"
        fi
    fi

    # Recreate views and FTS
    create_views
    create_fts_index

    commit_file_tracking

    echo ""
    echo "Incremental update complete."
    return 0
}

atomic_incremental_load() {
    ensure_db_dir
    local original_db="$DB_PATH"
    local working_db
    working_db=$(mktemp "$DB_DIR/.analyze-usage-update.XXXXXX")
    cp "$original_db" "$working_db"
    DB_PATH="$working_db"

    set +e
    ( set -e; incremental_load )
    local status=$?
    set -e
    DB_PATH="$original_db"

    if [ "$status" -ne 0 ]; then
        rm -f "$working_db"
        echo "Update failed; the existing database was left unchanged." >&2
        return "$status"
    fi

    chmod 600 "$working_db" 2>/dev/null || true
    mv "$working_db" "$original_db"
}

# ============================================================================
# Query Execution
# ============================================================================

run_query() {
    local sql="$1"

    if ! db_exists; then
        echo "Database not found. Run 'analyze-usage' first to load data." >&2
        exit 1
    fi

    duckdb "$DB_PATH" -c "$sql"
}

open_shell() {
    if ! db_exists; then
        echo "Database not found. Run 'analyze-usage' first to load data." >&2
        exit 1
    fi

    echo "Opening DuckDB shell. Type '.help' for commands, '.quit' to exit."
    echo "Database: $DB_PATH"
    echo ""
    duckdb "$DB_PATH"
}

# ============================================================================
# Search
# ============================================================================

run_search() {
    if ! db_exists; then
        echo "Database not found. Run 'analyze-usage' first to load data." >&2
        exit 1
    fi

    local query=""
    local search_field="content"
    local use_fts=false
    local limit=10
    local role_filter=""
    local repo_filter=""
    local since_filter=""

    while [ $# -gt 0 ]; do
        case "$1" in
            --thinking)
                search_field="thinking"
                shift
                ;;
            --all)
                search_field="all"
                shift
                ;;
            --fts)
                use_fts=true
                shift
                ;;
            -n)
                shift
                limit="${1:-10}"
                shift
                ;;
            --user)
                role_filter="user"
                shift
                ;;
            --asst|--assistant)
                role_filter="assistant"
                shift
                ;;
            --repo)
                shift
                repo_filter="${1:-}"
                shift
                ;;
            --since)
                shift
                local since_val="${1:-7d}"
                shift
                case "$since_val" in
                    *d) if [[ ! "$since_val" =~ ^[0-9]+d$ ]]; then
                            echo "Invalid --since value: $since_val" >&2
                            exit 1
                        fi
                        since_filter="TRY_CAST(timestamp AS TIMESTAMP) >= CURRENT_DATE - INTERVAL '${since_val%d} days'"
                        ;;
                    *w) if [[ ! "$since_val" =~ ^[0-9]+w$ ]]; then
                            echo "Invalid --since value: $since_val" >&2
                            exit 1
                        fi
                        since_filter="TRY_CAST(timestamp AS TIMESTAMP) >= CURRENT_DATE - INTERVAL '${since_val%w} weeks'"
                        ;;
                    ????-??-??) if [[ ! "$since_val" =~ ^[0-9]{4}-[0-9]{2}-[0-9]{2}$ ]]; then
                            echo "Invalid --since value: $since_val" >&2
                            exit 1
                        fi
                        since_filter="TRY_CAST(timestamp AS TIMESTAMP) >= '${since_val}'"
                        ;;
                    *)
                        echo "Invalid --since value: $since_val" >&2
                        exit 1
                        ;;
                esac
                ;;
            -*)
                echo "Unknown flag: $1" >&2
                echo "Usage: analyze-usage search \"query\" [--thinking|--all|--fts] [-n N] [--user|--asst] [--repo X] [--since 7d]" >&2
                exit 1
                ;;
            *)
                query="$1"
                shift
                ;;
        esac
    done

    if [ -z "$query" ]; then
        echo "Usage: analyze-usage search \"query\" [--thinking|--all|--fts] [-n N] [--user|--asst] [--repo X] [--since 7d]" >&2
        exit 1
    fi

    if [[ ! "$limit" =~ ^[1-9][0-9]*$ ]]; then
        echo "Invalid result limit: $limit" >&2
        exit 1
    fi

    local escaped_query escaped_repo
    escaped_query=$(sql_escape_literal "$query")
    escaped_repo=$(sql_escape_literal "$repo_filter")

    local where_parts=()

    if [ "$use_fts" = true ]; then
        if ! duckdb "$DB_PATH" -c "LOAD fts;" >/dev/null 2>&1; then
            echo "FTS extension is unavailable. Run once with network access or use literal search." >&2
            exit 1
        fi
        local fts_sql="SELECT m.uuid, m.session_id, m.role, m.harness, m.model,
    LEFT(m.content, 300) as content_preview,
    LEFT(m.thinking, 300) as thinking_preview,
    m.timestamp, m.repo_name, m.project_dir,
    fts.score
FROM messages m
JOIN (
    SELECT search_id, fts_main_messages.match_bm25(search_id, '${escaped_query}') as score
    FROM messages
    WHERE score IS NOT NULL
) fts ON m.search_id = fts.search_id"

        if [ -n "$role_filter" ]; then
            where_parts+=("m.role = '${role_filter}'")
        fi
        if [ -n "$repo_filter" ]; then
            where_parts+=("m.repo_name ILIKE '%${escaped_repo}%'")
        fi
        if [ -n "$since_filter" ]; then
            local m_since="${since_filter/timestamp/m.timestamp}"
            where_parts+=("${m_since}")
        fi

        if [ ${#where_parts[@]} -gt 0 ]; then
            local where_clause=""
            for part in "${where_parts[@]}"; do
                if [ -z "$where_clause" ]; then
                    where_clause="WHERE ${part}"
                else
                    where_clause="${where_clause} AND ${part}"
                fi
            done
            fts_sql="${fts_sql}
${where_clause}"
        fi

        fts_sql="${fts_sql}
ORDER BY fts.score DESC
LIMIT ${limit};"

        duckdb "$DB_PATH" -c "$fts_sql"
    else
        case "$search_field" in
            content)
                where_parts+=("content ILIKE '%${escaped_query}%'")
                ;;
            thinking)
                where_parts+=("thinking ILIKE '%${escaped_query}%'")
                ;;
            all)
                where_parts+=("(content ILIKE '%${escaped_query}%' OR thinking ILIKE '%${escaped_query}%')")
                ;;
        esac

        if [ -n "$role_filter" ]; then
            where_parts+=("role = '${role_filter}'")
        fi
        if [ -n "$repo_filter" ]; then
            where_parts+=("repo_name ILIKE '%${escaped_repo}%'")
        fi
        if [ -n "$since_filter" ]; then
            where_parts+=("${since_filter}")
        fi

        local where_clause=""
        for part in "${where_parts[@]}"; do
            if [ -z "$where_clause" ]; then
                where_clause="WHERE ${part}"
            else
                where_clause="${where_clause} AND ${part}"
            fi
        done

        duckdb "$DB_PATH" -c "SELECT uuid, session_id, role, harness, model,
    LEFT(content, 300) as content_preview,
    LEFT(thinking, 300) as thinking_preview,
    timestamp, repo_name, project_dir
FROM messages
${where_clause}
ORDER BY timestamp DESC
LIMIT ${limit};"
    fi
}

# ============================================================================
# Summary Output
# ============================================================================

show_summary() {
    if ! db_exists; then
        echo "No data loaded yet."
        return
    fi

    cat << 'EOF'
╔═══════════════════════════════════════════════════════════════════════════════╗
║                         AI Coding Usage Summary                               ║
╚═══════════════════════════════════════════════════════════════════════════════╝
EOF

    echo ""

    local claude_tools claude_sessions claude_messages
    claude_tools=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM claude_tools;" 2>/dev/null || echo "0")
    claude_sessions=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM claude_sessions;" 2>/dev/null || echo "0")
    claude_messages=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM messages WHERE harness='claude_code' AND (content IS NOT NULL OR thinking IS NOT NULL);" 2>/dev/null || echo "0")

    local system_events queue_ops pr_links_count sessions_index_count
    system_events=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM system_events;" 2>/dev/null || echo "0")
    queue_ops=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM queue_operations;" 2>/dev/null || echo "0")
    pr_links_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM pr_links;" 2>/dev/null || echo "0")
    sessions_index_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _sessions_index;" 2>/dev/null || echo "0")

    echo "Claude Code"
    echo "  Tool invocations: $claude_tools"
    echo "  Sessions: $claude_sessions"
    echo "  Searchable messages: $claude_messages"
    echo "  System events: $system_events"
    echo "  Queued inputs: $queue_ops"
    echo "  PR links: $pr_links_count"
    echo "  Session summaries: $sessions_index_count"
    echo ""

    local codex_tools codex_sessions codex_messages codex_token_counts codex_developer_messages codex_thread_names
    codex_tools=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_tools;" 2>/dev/null || echo "0")
    codex_sessions=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_sessions;" 2>/dev/null || echo "0")
    codex_messages=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM messages WHERE harness='codex' AND content IS NOT NULL;" 2>/dev/null || echo "0")
    codex_token_counts=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_token_counts;" 2>/dev/null || echo "0")
    codex_developer_messages=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_developer_messages;" 2>/dev/null || echo "0")
    codex_thread_names=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM codex_session_index;" 2>/dev/null || echo "0")

    echo "Codex"
    echo "  Tool invocations: $codex_tools"
    echo "  Sessions: $codex_sessions"
    echo "  Searchable messages: $codex_messages"
    echo "  Token snapshots: $codex_token_counts"
    echo "  Developer prompts: $codex_developer_messages"
    echo "  Thread titles: $codex_thread_names"
    echo ""

    local cursor_prompts cursor_workspaces cursor_messages
    cursor_prompts=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM cursor_prompts;" 2>/dev/null || echo "0")
    cursor_workspaces=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM cursor_workspaces WHERE prompt_count > 0;" 2>/dev/null || echo "0")
    cursor_messages=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM messages WHERE harness='cursor' AND content IS NOT NULL;" 2>/dev/null || echo "0")

    echo "Cursor"
    echo "  Prompts: $cursor_prompts"
    echo "  Active workspaces: $cursor_workspaces"
    echo "  Searchable messages: $cursor_messages"
    echo ""

    local pi_tools_count pi_sessions_count pi_messages_count pi_usage_rows pi_cost_total
    pi_tools_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM pi_tools;" 2>/dev/null || echo "0")
    pi_sessions_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM pi_sessions;" 2>/dev/null || echo "0")
    pi_messages_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM messages WHERE harness='pi' AND (content IS NOT NULL OR thinking IS NOT NULL);" 2>/dev/null || echo "0")
    pi_usage_rows=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM pi_usage;" 2>/dev/null || echo "0")
    pi_cost_total=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COALESCE(ROUND(SUM(cost_usd), 2), 0) FROM pi_usage;" 2>/dev/null || echo "0")

    echo "Pi"
    echo "  Tool invocations: $pi_tools_count"
    echo "  Sessions: $pi_sessions_count"
    echo "  Searchable messages: $pi_messages_count"
    echo "  Usage rows: $pi_usage_rows"
    echo "  Recorded cost (USD): $pi_cost_total"
    echo ""

    local file_count
    file_count=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT COUNT(*) FROM _loaded_files;" 2>/dev/null || echo "0")

    local last_load
    last_load=$(duckdb -csv -noheader "$DB_PATH" -c "SELECT value FROM _metadata WHERE key='last_load';" 2>/dev/null || echo "unknown")
    echo "Files tracked: $file_count"
    echo "Database: $DB_PATH"
    echo "Last loaded: $last_load"
    echo ""

    echo "───────────────────────────────────────────────────────────────────────────────"
    echo ""
    echo "Quick start:"
    echo "  analyze-usage --schema           Show tables and example queries"
    echo "  analyze-usage query \"SQL\"        Run a SQL query"
    echo "  analyze-usage report              Generate aggregate report JSON"
    echo "  analyze-usage search \"query\"     Search conversation content"
    echo "  analyze-usage update             Incremental update"
    echo "  analyze-usage shell              Interactive SQL shell"
    echo "  analyze-usage reload             Full reload from source logs"
    echo ""
    echo "Examples:"
    echo "  analyze-usage query \"SELECT * FROM turn_durations ORDER BY duration_ms DESC LIMIT 5\""
    echo "  analyze-usage query \"SELECT session_id, summary FROM session_overview WHERE summary IS NOT NULL LIMIT 5\""
    echo "  analyze-usage query \"SELECT * FROM cost_summary ORDER BY cost_usd DESC\""
}

# ============================================================================
# Main
# ============================================================================

case "${1:-}" in
    --help|-h|help)
        show_help
        ;;

    --schema|schema)
        show_schema
        ;;

    --version|-v)
        echo "analyze-usage version $VERSION"
        ;;

    reload)
        load_all_data
        echo ""
        show_summary
        ;;

    update)
        if needs_load; then
            load_all_data
        else
            atomic_incremental_load
        fi
        echo ""
        show_summary
        ;;

    query|q)
        if [ -z "${2:-}" ]; then
            echo "Usage: analyze-usage query \"SQL QUERY\"" >&2
            exit 1
        fi
        run_query "$2"
        ;;

    report)
        if [ ! -x "$REPORT_SCRIPT_PATH" ]; then
            echo "Report generator not found or not executable: $REPORT_SCRIPT_PATH" >&2
            exit 1
        fi
        shift
        "$REPORT_SCRIPT_PATH" --db "$DB_PATH" "$@"
        ;;

    search|s)
        shift
        run_search "$@"
        ;;

    shell|repl)
        open_shell
        ;;

    "")
        # Default: full load if needed, otherwise incremental, then summary
        if needs_load; then
            load_all_data
            echo ""
        else
            atomic_incremental_load
            echo ""
        fi
        show_summary
        ;;

    *)
        echo "Unknown command: $1" >&2
        echo "Run 'analyze-usage --help' for usage" >&2
        exit 1
        ;;
esac
