import duckdb

# Simulated benchmark data from multiple sources
data = '''
model,gpa,swe_bench,math,multimodal,input_price_per_m,output_price_per_m
"GPT-4.5",63,52,71,78,15,60
"GPT-5.6",78,71,89,85,20,80
"Claude 4",75,68,82,80,10,40
"Gemini 2.5 Pro",70,65,78,82,7,21
"DeepSeek V3",68,60,75,65,3,8
"Llama 3.1 405B",65,58,72,60,0,0
'''

# Load directly into DuckDB
con = duckdb.connect()
df = con.sql(f"""
    SELECT * FROM read_csv_auto(
        $$'{data}'$$,
        header=true
    )
""").fetchdf()

print(df)

-- Value index = average capability score / total cost
SELECT
    model,
    ROUND((gpa + swe_bench + math + multimodal) / 4, 1) AS avg_score,
    input_price_per_m + output_price_per_m AS total_cost_per_m,
    ROUND((gpa + swe_bench + math + multimodal) / 4.0 
          / (input_price_per_m + output_price_per_m), 2) AS value_index
FROM model_evaluations
ORDER BY value_index DESC;

# Automatically fetch latest benchmark data monthly
def update_model_evaluations():
    """Periodically fetch and store new benchmark articles"""
    import requests
    
    # Fetch from Hacker News API
    response = requests.get("https://hn.algolia.com/api/v1/search?query=LLM+benchmark")
    articles = response.json().get("hits", [])[:20]
    
    # Store in DuckDB for trend analysis
    con.execute("""
        CREATE TABLE IF NOT EXISTS benchmark_articles (
            title VARCHAR,
            date DATE,
            url VARCHAR,
            points INT
        )
    """)
    
    for article in articles:
        con.execute("""
            INSERT INTO benchmark_articles VALUES (?, ?, ?, ?)
        """, [
            article.get("title"),
            article.get("created_at"),
            article.get("url"),
            article.get("points", 0)
        ])
    
    print(f"✅ Updated {len(articles)} articles")

-- Assume you exported your API usage logs from OpenAI/Claude/Gemini
CREATE TABLE api_usage (
    date DATE,
    model VARCHAR,
    input_tokens BIGINT,
    output_tokens BIGINT,
    cost_usd DOUBLE
);

-- Monthly spending by model
SELECT
    strftime(date, '%Y-%m') AS month,
    model,
    SUM(cost_usd) AS total_cost,
    SUM(input_tokens + output_tokens) AS total_tokens
FROM api_usage
WHERE date >= '2026-06-01'
GROUP BY month, model
ORDER BY month DESC, total_cost DESC;