#!/usr/bin/env python3
"""PlanWatch usage export — reads opencode.db READ-ONLY, prints aggregate JSON.

Usage:
  python3 export_usage.py [path/to/opencode.db] [--days 30]

The db is opened with mode=ro (immutable); nothing is written to it.
Upload the printed JSON at the PlanWatch page, or upload opencode.db directly
if it is small enough for your browser to hold in memory.
"""
import json, os, sqlite3, sys, time
from datetime import datetime, timezone

QUERY = """
SELECT
  json_extract(data,'$.providerID') || '/' || json_extract(data,'$.modelID') AS model,
  SUM(CAST(json_extract(data,'$.tokens.input') AS INTEGER)) AS input_tokens,
  SUM(CAST(json_extract(data,'$.tokens.output') AS INTEGER)) AS output_tokens,
  COUNT(*) AS messages,
  COUNT(DISTINCT session_id) AS sessions,
  MIN(time_created) AS first_ms,
  MAX(time_created) AS last_ms
FROM message
WHERE json_extract(data,'$.role') = 'assistant'
  AND json_extract(data,'$.tokens.input') IS NOT NULL
  AND time_created >= ?
GROUP BY model
"""

def main():
    args = [a for a in sys.argv[1:] if not a.startswith('--')]
    days = 30
    for i, a in enumerate(sys.argv[1:]):
        if a == '--days' and i + 1 < len(sys.argv[1:]):
            days = int(sys.argv[i + 2])
    default = os.path.expandvars(os.path.join(
        os.path.expanduser('~'), '.local', 'share', 'opencode', 'opencode.db'))
    path = args[0] if args else os.environ.get('OPENCODE_DB', default)
    if not os.path.exists(path):
        sys.exit('db not found: %s' % path)

    # read-only: query the max timestamp first through a ro connection
    con = sqlite3.connect('file:%s?mode=ro' % path.replace('?', '%3F'), uri=True)
    cur = con.cursor()
    max_ms = cur.execute(
        "SELECT MAX(time_created) FROM message "
        "WHERE json_extract(data,'$.role')='assistant'").fetchone()[0]
    if not max_ms:
        sys.exit('no assistant messages found')
    cutoff = max_ms - days * 86400 * 1000
    rows = []
    for r in cur.execute(QUERY, (cutoff,)):
        rows.append({
            'model': r[0], 'input_tokens': r[1] or 0, 'output_tokens': r[2] or 0,
            'messages': r[3], 'sessions': r[4],
        })
    first_ms = cur.execute(
        "SELECT MIN(time_created) FROM message "
        "WHERE json_extract(data,'$.role')='assistant' AND time_created >= ?",
        (cutoff,)).fetchone()[0]
    con.close()

    out = {
        'tool': 'planwatch-export', 'version': 1,
        'exportedAt': datetime.now(timezone.utc).isoformat(),
        'dbPath': path,
        'windowDays': round((max_ms - first_ms) / 86400000, 2),
        'rows': sorted(rows, key=lambda r: -r['input_tokens']),
    }
    n_models = len(rows)
    n_msg = sum(r['messages'] for r in rows)
    sys.stderr.write('%d models, %d messages, %.1f days\n'
                     % (n_models, n_msg, out['windowDays']))
    print(json.dumps(out))

if __name__ == '__main__':
    main()