scripts/check_db_counts.py

import asyncio
import sys
import os
from pathlib import Path
sys.path.insert(0, str(Path(__file__).parent.parent))
from app.services.database_service import DatabaseService
from dotenv import load_dotenv

load_dotenv()

async def main():
    db = DatabaseService(url=os.getenv("TURSO_URL"), token=os.getenv("TURSO_TOKEN"))
    await db.initialize()
    
    total_stocks = await db.get_stock_count()
    active_stocks = await db.get_active_stock_count()
    
    try:
        res = await db._execute("SELECT COUNT(*) as count FROM forecasts")
        forecasts_count = res[0]["count"] if res else 0
    except Exception as e:
        print(f"Error querying forecasts count: {e}")
        forecasts_count = 0
        
    try:
        res_by_country = await db._execute("""
            SELECT s.country, COUNT(DISTINCT f.symbol) as forecasted_stocks, COUNT(*) as total_forecast_rows
            FROM forecasts f
            JOIN (
                SELECT DISTINCT 
                    CASE WHEN symbol LIKE 'NZX:%' THEN SUBSTR(symbol, 6) ELSE symbol END || COALESCE(yfinance_suffix, '') as yf_symbol,
                    country
                FROM stocks
            ) s ON s.yf_symbol = f.symbol
            GROUP BY s.country
        """)
    except Exception as e:
        print(f"Error querying forecasts by country: {e}")
        res_by_country = []
        
    print("=" * 50)
    print("DATABASE COUNTS SUMMARY")
    print("=" * 50)
    print(f"Total Stocks:   {total_stocks}")
    print(f"Active Stocks:  {active_stocks}")
    print(f"Total Forecast Rows in DB: {forecasts_count}")
    print("-" * 50)
    print("Forecasted Stocks & Rows by Country:")
    for row in res_by_country:
        print(f"  {row.get('country', 'unknown').upper()}: {row.get('forecasted_stocks', 0)} stocks ({row.get('total_forecast_rows', 0)} rows)")
    print("=" * 50)
    
    await db.close()

if __name__ == "__main__":
    asyncio.run(main())