athena-oracle/query.py

#!/usr/bin/env python3
"""
AI Research Oracle — Query & Snapshot CLI.

Query the unified database and produce Claude-ready snapshots.
Supports filtering by source, score, confidence, tag, and date range.

Usage:
    python3 query.py top 10                   # top 10 across all sources
    python3 query.py by-source github 5       # top 5 from GitHub
    python3 query.py by-tag "agent"           # entries tagged with "agent"
    python3 query.py snapshot                 # full snapshot for Claude relay
    python3 query.py search "world model"     # keyword search in titles/summaries
    python3 query.py recent --hours 24        # entries from last 24h
    python3 query.py stats                    # database statistics
"""

import argparse
import json
import os
import sqlite3
import sys
from datetime import datetime, timezone, timedelta
from urllib.parse import quote_plus

sys.path.insert(0, os.path.dirname(__file__))

DB_PATH = os.path.join(os.path.dirname(__file__), "oracle.db")


def get_db():
    """Open database connection."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def format_entry(row: sqlite3.Row, rank: int = 0) -> dict:
    """Format a DB row into a clean dict for output."""
    summary = json.loads(row["summary"]) if row["summary"] else {}
    meta = json.loads(row["raw_metadata"]) if row["raw_metadata"] else {}
    tags = json.loads(row["category_tags"]) if row["category_tags"] else []

    score_type = meta.get("score_type", "?")
    source_detail = ""
    if row["source"] == "github":
        source_detail = f"⭐ {meta.get('stars', '?')} stars"
    elif row["source"] == "arxiv":
        source_detail = f"arXiv:{meta.get('arxiv_id', '?')}"
    elif row["source"] == "reddit":
        source_detail = f"r/{meta.get('subreddit', '?')}"

    return {
        "rank": rank,
        "source": row["source"],
        "title": row["title"],
        "url": row["url"],
        "score": row["signal_score"],
        "score_type": score_type,
        "confidence": summary.get("confidence", "?"),
        "source_detail": source_detail,
        "tags": tags,
        "one_liner": summary.get("one_liner", ""),
        "key_technical_point": summary.get("key_technical_point", ""),
        "potential_use_case": summary.get("potential_use_case", ""),
        "first_seen": row["first_seen"],
    }


def cmd_top(args):
    """Top N entries across all sources (per-source ranking)."""
    conn = get_db()
    cur = conn.cursor()

    # Score filter
    min_score = getattr(args, 'min_score', 0) or 0
    # Confidence filter
    min_confidence = getattr(args, 'min_confidence', None) or None
    # Source filter
    source_filter = getattr(args, 'source', None) or None

    where = []
    params = []
    if min_score > 0:
        where.append("signal_score >= ?")
        params.append(min_score)
    if min_confidence:
        where.append("json_extract(summary,'$.confidence') = ?")
        params.append(min_confidence)
    if source_filter:
        where.append("source = ?")
        params.append(source_filter)

    where_str = (" AND " if where else "") + " AND ".join(where) if where else ""
    limit = args.n or 10

    cur.execute(f"""
        SELECT * FROM entries {where_str}
        ORDER BY signal_score DESC
        LIMIT ?
    """, params + [limit])

    rows = cur.fetchall()
    entries = [format_entry(r, i+1) for i, r in enumerate(rows)]

    print(f"Top {len(entries)} entries{' by score' if not source_filter else f' from {source_filter}'}:\n")
    _print_entries(entries)
    conn.close()


def cmd_by_source(args):
    """Top N from a specific source."""
    conn = get_db()
    cur = conn.cursor()
    cur.execute("""
        SELECT * FROM entries WHERE source = ?
        ORDER BY signal_score DESC
        LIMIT ?
    """, (args.source, args.n or 10))

    rows = cur.fetchall()
    entries = [format_entry(r, i+1) for i, r in enumerate(rows)]

    print(f"Top {len(entries)} from {args.source}:\n")
    _print_entries(entries)
    conn.close()


def cmd_by_tag(args):
    """Entries matching a tag."""
    conn = get_db()
    cur = conn.cursor()
    cur.execute("""
        SELECT * FROM entries
        WHERE json_extract(category_tags,'$') LIKE ?
        ORDER BY signal_score DESC
        LIMIT 20
    """, (f'%"{args.tag}"%',))

    rows = cur.fetchall()
    entries = [format_entry(r, i+1) for i, r in enumerate(rows)]

    print(f"Entries tagged '{args.tag}':\n")
    _print_entries(entries)
    conn.close()


def cmd_search(args):
    """Keyword search in titles and summaries."""
    conn = get_db()
    cur = conn.cursor()
    q = f"%{args.query}%"
    cur.execute("""
        SELECT * FROM entries
        WHERE title LIKE ?
           OR json_extract(summary,'$.one_liner') LIKE ?
           OR json_extract(summary,'$.key_technical_point') LIKE ?
        ORDER BY signal_score DESC
        LIMIT 20
    """, (q, q, q))

    rows = cur.fetchall()
    entries = [format_entry(r, i+1) for i, r in enumerate(rows)]

    print(f"Search results for '{args.query}':\n")
    _print_entries(entries)
    conn.close()


def cmd_recent(args):
    """Entries from the last N hours."""
    hours = args.hours or 24
    cutoff = (datetime.now(timezone.utc) - timedelta(hours=hours)).strftime("%Y-%m-%dT%H:%M:%SZ")
    conn = get_db()
    cur = conn.cursor()
    cur.execute("""
        SELECT * FROM entries WHERE first_seen >= ?
        ORDER BY first_seen DESC
    """, (cutoff,))

    rows = cur.fetchall()
    entries = [format_entry(r, i+1) for i, r in enumerate(rows)]

    print(f"Entries from last {hours}h:\n")
    _print_entries(entries)
    conn.close()


def cmd_snapshot(args):
    """Full snapshot for Claude relay.

    Produces a structured summary of the current DB state,
    formatted for Claude to reason over.
    """
    conn = get_db()
    cur = conn.cursor()

    # DB stats
    cur.execute("SELECT COUNT(*) FROM entries")
    total = cur.fetchone()[0]

    cur.execute("SELECT source, COUNT(*) as cnt, AVG(signal_score) as avg_score, MIN(first_seen) as oldest, MAX(last_updated) as newest FROM entries GROUP BY source")
    source_stats = {r["source"]: dict(r) for r in cur.fetchall()}

    # Security-tagged entries
    cur.execute("""
        SELECT COUNT(*) FROM entries
        WHERE summary IS NOT NULL AND summary != '' AND json_extract(summary,'$.potential_use_case') LIKE '%security%'
    """)
    security_count = cur.fetchone()[0]

    # Top 10 overall
    cur.execute("SELECT * FROM entries WHERE summary IS NOT NULL AND summary != '' ORDER BY signal_score DESC LIMIT 10")
    top_entries = [format_entry(r, i+1) for i, r in enumerate(cur.fetchall())]

    # Confidence distribution
    cur.execute("""
        SELECT json_extract(summary,'$.confidence') as conf, COUNT(*) as cnt
        FROM entries WHERE summary IS NOT NULL AND summary != ''
        GROUP BY conf
    """)
    conf_dist = {r["conf"]: r["cnt"] for r in cur.fetchall()}

    now = datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")

    snapshot = {
        "snapshot_time": now,
        "total_entries": total,
        "sources": source_stats,
        "security_flagged": security_count,
        "confidence_distribution": conf_dist,
        "top_10": top_entries,
    }

    # Output as formatted text for relay
    print("=" * 70)
    print("AI RESEARCH ORACLE — SNAPSHOT")
    print("=" * 70)
    print(f"Time: {now}")
    print(f"Total entries: {total}")
    print()

    print("Source breakdown:")
    for src, stats in source_stats.items():
        print(f"  {src}: {stats['cnt']} entries, avg score {stats['avg_score']:.2f}")
    print()

    if conf_dist:
        print(f"Confidence distribution: {conf_dist}")
        print()

    if security_count:
        print(f"⚠ {security_count} entries flagged as security:dual-use")
        print()

    print("Top 10 by signal score (per-source ranking, NOT cross-source comparable):")
    print("-" * 70)
    for e in top_entries:
        score_label = f"{e['score']:.2f} ({e['score_type']})"
        conf = e["confidence"]
        print(f"\n  [{e['rank']}] {e['source'].upper()} | {score_label} | confidence={conf}")
        print(f"      {e['title']}")
        print(f"      {e['source_detail']}")
        if e["one_liner"]:
            print(f"      → {e['one_liner'][:120]}")
        print(f"      Tags: {', '.join(e['tags'][:4])}")

    print("\n" + "=" * 70)
    print("NOTE: Scores are NOT comparable across sources. GitHub uses")
    print("actual star counts (log-scaled), arXiv/reddit use estimated")
    print("heuristics. Rank within-source, not cross-source.")
    print("=" * 70)

    conn.close()


def cmd_explain(args):
    """Explain why an entry scored the way it did."""
    conn = get_db()
    cur = conn.cursor()

    if args.entry_id.isdigit():
        cur.execute("SELECT * FROM entries WHERE id = ?", (args.entry_id,))
    else:
        q = f"%{args.entry_id}%"
        cur.execute("SELECT * FROM entries WHERE title LIKE ?", (q,))

    row = cur.fetchone()
    if not row:
        print(f"Entry not found: {args.entry_id}")
        conn.close()
        return

    meta = json.loads(row["raw_metadata"]) if row["raw_metadata"] else {}
    summary = json.loads(row["summary"]) if row["summary"] else {}
    tags = json.loads(row["category_tags"]) if row["category_tags"] else []

    print(f"=== Score Explanation ===\n")
    print(f"Title: {row['title']}")
    print(f"Source: {row['source']}")
    print(f"Score: {row['signal_score']:.2f} ({meta.get('score_type', '?')})")
    print(f"Confidence: {summary.get('confidence', '?')}")
    print()

    if row["source"] == "arxiv":
        print(f"Authors: {meta.get('author_count', '?')}")
        print(f"Categories: {', '.join(meta.get('categories', []))}")
        print(f"Published: {meta.get('published', '?')}")
        print(f"Abstract length: {meta.get('abstract_length', '?')} chars")
        print(f"Applied domain: {meta.get('applied_domain', 'none (core AI)')}")
        print()
        print("Scoring (arXiv — relevance-weighted, structural capped):")
        print(f"  - Structural (recency+authors+diversity, capped ~3.5): weak signals")
        print(f"  - AI keyword density in abstract (capped 2.0)")
        print(f"  - AI methodology claim (propose/novel = up to 2.5)")
        print(f"  - Applied-domain is TAGGED, not penalized")
        if meta.get('applied_domain'):
            print(f"  Note: tagged '{meta['applied_domain']}' for filtering — no score penalty")
    elif row["source"] == "github":
        print(f"Stars: {meta.get('stars', '?')}")
        print(f"Language: {meta.get('language', '?')}")
        print()
        print("Scoring (GitHub actual star count, log-scaled)")

    print()
    print(f"Tags: {', '.join(tags)}")
    if summary.get('one_liner'):
        print(f"One-liner: {summary['one_liner'][:120]}")

    conn.close()


def cmd_stats(args):
    """Database statistics."""
    conn = get_db()
    cur = conn.cursor()

    cur.execute("SELECT COUNT(*) FROM entries")
    total = cur.fetchone()[0]

    cur.execute("SELECT source, COUNT(*) as cnt, ROUND(AVG(signal_score),2) as avg_score, MIN(signal_score) as min_score, MAX(signal_score) as max_score FROM entries GROUP BY source")
    rows = cur.fetchall()

    print(f"Database: {DB_PATH}")
    print(f"Total entries: {total}\n")

    print(f"{'Source':<12} {'Count':<8} {'Avg':<8} {'Min':<8} {'Max':<8}")
    print("-" * 44)
    for r in rows:
        print(f"{r['source']:<12} {r['cnt']:<8} {r['avg_score']:<8} {r['min_score']:<8} {r['max_score']:<8}")

    # Summarization status
    cur.execute("SELECT COUNT(*) FROM entries WHERE summary IS NOT NULL")
    summarized = cur.fetchone()[0]
    cur.execute("SELECT COUNT(*) FROM entries WHERE summary IS NULL")
    pending = cur.fetchone()[0]
    print(f"\nSummarization: {summarized} done, {pending} pending")

    # Confidence distribution
    cur.execute("SELECT json_extract(summary,'$.confidence') as c, COUNT(*) as n FROM entries WHERE summary IS NOT NULL AND summary != '' GROUP BY c")
    if cur.fetchall():
        print(f"Confidence: {' | '.join(f'{r[0]}={r[1]}' for r in cur.fetchall())}")

    # Recent run history (failure visibility)
    cur.execute("SELECT run_time, total_fetched, total_stored, sources_ok, sources_failed FROM run_log ORDER BY id DESC LIMIT 5")
    runs = cur.fetchall()
    if runs:
        print(f"\nRecent runs (last {len(runs)}):")
        for r in runs:
            failed = json.loads(r["sources_failed"]) if r["sources_failed"] else []
            status = "✓ all ok" if not failed else f"⚠ partial: {', '.join(failed)}"
            print(f"  {r['run_time']}  fetched={r['total_fetched']} stored={r['total_stored']}  {status}")

    conn.close()


def _print_entries(entries: list[dict]):
    """Pretty-print a list of entries."""
    if not entries:
        print("  (no results)")
        return

    for e in entries:
        score_label = f"{e['score']:.2f} ({e['score_type']})"
        conf = e["confidence"]
        print(f"  [{e['rank']}] {e['source'].upper():6} | {score_label} | confidence={conf}")
        print(f"      {e['title']}")
        if e["source_detail"]:
            print(f"      {e['source_detail']}")
        if e["one_liner"]:
            print(f"      → {e['one_liner'][:120]}")
        # Show applied-domain and security tags prominently
        shown_tags = [t for t in e["tags"] if t.startswith("applied:") or t.startswith("security:")]
        other_tags = [t for t in e["tags"] if not t.startswith("applied:") and not t.startswith("security:")]
        display_tags = shown_tags + other_tags[:4]
        if display_tags:
            print(f"      Tags: {', '.join(display_tags)}")
        print()


def main():
    parser = argparse.ArgumentParser(description="AI Research Oracle — Query")
    sub = parser.add_subparsers(dest="command")

    # top
    p_top = sub.add_parser("top", help="Top N entries")
    p_top.add_argument("n", type=int, nargs="?", default=10)
    p_top.add_argument("--source", default=None)
    p_top.add_argument("--min-score", type=float, default=0)

    # by-source
    p_src = sub.add_parser("by-source", help="Top N from a source")
    p_src.add_argument("source")
    p_src.add_argument("n", type=int, nargs="?", default=10)

    # by-tag
    p_tag = sub.add_parser("by-tag", help="Entries by tag")
    p_tag.add_argument("tag")

    # search
    p_search = sub.add_parser("search", help="Keyword search")
    p_search.add_argument("query")

    # recent
    p_recent = sub.add_parser("recent", help="Recent entries")
    p_recent.add_argument("--hours", type=int, default=24)

    # snapshot
    sub.add_parser("snapshot", help="Full snapshot for Claude")

    # explain
    p_explain = sub.add_parser("explain", help="Explain why an entry scored high")
    p_explain.add_argument("entry_id", help="Entry ID or partial title")

    # stats
    sub.add_parser("stats", help="Database statistics")

    args = parser.parse_args()

    commands = {
        "top": cmd_top,
        "by-source": cmd_by_source,
        "by-tag": cmd_by_tag,
        "search": cmd_search,
        "recent": cmd_recent,
        "snapshot": cmd_snapshot,
        "explain": cmd_explain,
        "stats": cmd_stats,
    }

    cmd = commands.get(args.command)
    if cmd:
        cmd(args)
    else:
        parser.print_help()


if __name__ == "__main__":
    main()