scripts/refresh_metrics_weekly.py

"""
Weekly financial metrics refresh job.
Updates P/E, dividend yield, 52-week range, and market cap for all stocks.
Run: python scripts/refresh_metrics_weekly.py [--limit 500] [--delay 0.5]
"""

import argparse
import asyncio
import os
import sys
import time
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent))

from dotenv import load_dotenv
load_dotenv()

from app.services.database_service import DatabaseService

try:
    import yfinance as yf
except ImportError:
    print("pip install yfinance")
    sys.exit(1)


def yf_ticker(symbol: str, suffix: str) -> str:
    s = symbol.upper()
    if not suffix:
        return s
    suf = suffix if suffix.startswith(".") else f".{suffix}"
    return s if s.endswith(suf) else f"{s}{suf}"


def extract_metrics(info: dict) -> dict:
    """Extract financial metrics from Yahoo Finance info dict."""
    return {
        'pe_ratio': info.get('trailingPE') or info.get('forwardPE'),
        'dividend_yield': info.get('dividendYield'),
        'fifty_two_week_high': info.get('fiftyTwoWeekHigh'),
        'fifty_two_week_low': info.get('fiftyTwoWeekLow'),
        'market_cap': info.get('marketCap'),
    }


async def main(limit: int, delay: float) -> None:
    turso_url = os.getenv("TURSO_URL")
    turso_token = os.getenv("TURSO_TOKEN")
    if not turso_url or not turso_token:
        print("Set TURSO_URL and TURSO_TOKEN in backend/.env")
        sys.exit(1)

    db = DatabaseService(url=turso_url, token=turso_token)
    await db.initialize()

    # Refresh all stocks, prioritizing those with stale data
    rows = await db._execute("""
        SELECT symbol, country, yfinance_suffix
        FROM stocks
        ORDER BY market_cap DESC NULLS LAST, symbol ASC
        LIMIT ?
    """, [limit])

    print(f"Refreshing metrics for {len(rows)} stocks...")
    updated = 0
    for i, row in enumerate(rows, 1):
        sym = yf_ticker(row["symbol"], row.get("yfinance_suffix") or "")
        try:
            info = yf.Ticker(sym).info or {}
            metrics = extract_metrics(info)
            await db._execute("""
                UPDATE stocks
                SET pe_ratio = COALESCE(?, pe_ratio),
                    dividend_yield = COALESCE(?, dividend_yield),
                    fifty_two_week_high = COALESCE(?, fifty_two_week_high),
                    fifty_two_week_low = COALESCE(?, fifty_two_week_low),
                    market_cap = COALESCE(?, market_cap)
                WHERE symbol = ? AND country = ?
            """, [
                metrics['pe_ratio'], metrics['dividend_yield'],
                metrics['fifty_two_week_high'], metrics['fifty_two_week_low'],
                metrics['market_cap'],
                row['symbol'], row['country'],
            ])
            updated += 1
            if updated % 100 == 0:
                print(f"  [{i}/{len(rows)}] {sym} -> updated ({updated} total)", flush=True)
        except Exception as e:
            print(f"  [{i}/{len(rows)}] {sym} skip: {e}", flush=True)
        time.sleep(delay)

    print(f"Done. Updated {updated} rows.")


if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument("--limit", type=int, default=500)
    parser.add_argument("--delay", type=float, default=0.5)
    args = parser.parse_args()
    asyncio.run(main(args.limit, args.delay))