import duckdb
import pandas as pd

# Create DuckDB database file (persistent storage)
con = duckdb.connect("stock_data.duckdb")

# Financial data table: stores daily updated financial metrics
con.execute("""
CREATE TABLE IF NOT EXISTS financials (
 ticker VARCHAR,
 date DATE,
 revenue BIGINT,
 net_income BIGINT,
 total_assets BIGINT,
 total_debt BIGINT,
 eps REAL,
 pe_ratio REAL
)
""")

# Daily snapshot table: for time series analysis
con.execute("""
CREATE TABLE IF NOT EXISTS daily_snapshots (
 ticker VARCHAR,
 snapshot_date DATE,
 market_cap BIGINT,
 volume BIGINT,
 price REAL
)
""")

# Create indexes to accelerate queries
con.execute("CREATE INDEX IF NOT EXISTS idx_financials_ticker_date ON financials(ticker, date)")
con.execute("CREATE INDEX IF NOT EXISTS idx_snapshots_ticker_date ON daily_snapshots(ticker, snapshot_date)")

print("✅ Data model created")

import numpy as np
from datetime import datetime, timedelta

# Simulate 30 days of financial data
np.random.seed(42)
tickers = ['AAPL', 'MSFT', 'GOOGL', 'AMZN', 'TSLA', 'NVDA', 'META', 'JPM']

data_rows = []
snapshot_rows = []
base_date = datetime(2026, 8, 9)

for i, ticker in enumerate(tickers):
 for day in range(30):
 date = base_date - timedelta(days=day)
 revenue = int(50e9 + np.random.randn() * 5e9 + i * 10e9)
 net_income = int(revenue * 0.25 + np.random.randn() * 2e9)
 eps = round(2.0 + np.random.randn() * 0.5 + i * 0.3, 2)
 pe_ratio = round(20 + np.random.randn() * 8 + i * 2, 2)
 
 data_rows.append((ticker, date, revenue, net_income, 
 int(revenue * 8), int(revenue * 0.4), eps, pe_ratio))
 
 snapshot_rows.append((
 ticker, date,
 int(revenue * 15 + np.random.randn() * 1e9),
 int(50e6 + np.random.randn() * 10e6),
 round(pe_ratio * eps, 2)
 ))

# Bulk insert
con.execute("""
 INSERT INTO financials 
 VALUES (SELECT * FROM (VALUES ?) AS t(ticker, date, revenue, net_income, total_assets, total_debt, eps, pe_ratio))
""", [tuple(row) for row in data_rows])

con.execute("""
 INSERT INTO daily_snapshots 
 VALUES (SELECT * FROM (VALUES ?) AS t(ticker, snapshot_date, market_cap, volume, price))
""", [tuple(row) for row in snapshot_rows])

print(f"✅ Inserted: {len(data_rows)} financial records, {len(snapshot_rows)} snapshot records")

signal_sql = """
WITH ranked_stocks AS (
 SELECT 
 ticker,
 date,
 -- Return on Equity (rolling 12-month)
 AVG(net_income * 4.0 / NULLIF(total_assets, 0)) AS avg_roe,
 -- Revenue growth (last 5 days vs previous 5 days)
 (
 AVG(CASE WHEN date >= date - INTERVAL '5' DAY THEN revenue END) /
 NULLIF(AVG(CASE WHEN date < date - INTERVAL '5' DAY THEN revenue END), 0) - 1
 ) AS revenue_growth,
 -- Debt ratio
 AVG(total_debt * 1.0 / NULLIF(total_assets, 0)) AS debt_ratio,
 -- PE percentile
 PERCENT_RANK() OVER (
 PARTITION BY date ORDER BY pe_ratio
 ) AS pe_percentile,
 -- Composite score
 (
 AVG(avg_roe) * 0.4 
 + COALESCE(revenue_growth, 0) * 0.3 
 + (1 - AVG(debt_ratio)) * 0.2
 + (1 - PERCENT_RANK() OVER (PARTITION BY date ORDER BY pe_ratio)) * 0.1
 ) AS composite_score
 FROM financials
 WHERE date >= date - INTERVAL '30' DAY
 GROUP BY ticker, date
 HAVING avg_roe > 0.10 -- ROE > 10%
 AND debt_ratio < 0.6 -- Debt ratio < 60%
 AND pe_ratio < 50 -- PE < 50
)
SELECT * FROM ranked_stocks
ORDER BY date DESC, composite_score DESC
LIMIT 20
"""

signals = con.execute(signal_sql).fetchdf()
print(signals.to_string())

from fastapi import FastAPI
from pydantic import BaseModel
import duckdb

app = FastAPI(title="DuckDB Stock Screening API")

# Process-local singleton connection (avoid reopening file per request)
_db_path = "stock_data.duckdb"

class SignalRequest(BaseModel):
 date: str
 top_n: int = 10

@app.get("/api/signals")
def get_signals(req: SignalRequest):
 con = duckdb.connect(_db_path)
 result = con.execute(f"""
 WITH ranked_stocks AS (
 SELECT 
 ticker,
 AVG(net_income * 4.0 / NULLIF(total_assets, 0)) AS avg_roe,
 (
 AVG(CASE WHEN date >= '{req.date}' - INTERVAL '5' DAY THEN revenue END) /
 NULLIF(AVG(CASE WHEN date < '{req.date}' - INTERVAL '5' DAY THEN revenue END), 0) - 1
 ) AS revenue_growth,
 AVG(total_debt * 1.0 / NULLIF(total_assets, 0)) AS debt_ratio,
 (
 AVG(avg_roe) * 0.4 
 + COALESCE(revenue_growth, 0) * 0.3 
 + (1 - AVG(debt_ratio)) * 0.2
 + (1 - PERCENT_RANK() OVER (PARTITION BY date ORDER BY pe_ratio)) * 0.1
 ) AS composite_score
 FROM financials
 WHERE date = '{req.date}'
 GROUP BY ticker
 HAVING avg_roe > 0.10 AND debt_ratio < 0.6 AND pe_ratio < 50
 )
 SELECT ticker, avg_roe, revenue_growth, debt_ratio, composite_score
 FROM ranked_stocks
 ORDER BY composite_score DESC
 LIMIT {req.top_n}
 """).fetchdf()
 return result.to_dict(orient='records')

if __name__ == "__main__":
 import uvicorn
 uvicorn.run(app, host="0.0.0.0", port=8000)

# Get today's Top 10 stock screening signals
curl -X POST "http://localhost:8000/api/signals" \
 -H "Content-Type: application/json" \
 -d '{"date": "2026-08-09", "top_n": 10}'

# Response
[
 {"ticker": "NVDA", "avg_roe": 0.324, "revenue_growth": 0.152, "debt_ratio": 0.213, "composite_score": 0.487},
 {"ticker": "META", "avg_roe": 0.288, "revenue_growth": 0.099, "debt_ratio": 0.188, "composite_score": 0.456},
 ...
]