import os, sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
import pandas as pd
from database import get_pg_engine
import os
os.environ["DATABASE_URL"] = "sqlite:///portfolio_db.sqlite3"

engine = get_pg_engine()
tickers = ['AAPL', 'MSFT', 'GOOGL', 'AMZN', 'NVDA', 'META', 'TSLA', 'JPM', 'GS', 'XOM', 'CVX', 'TLT', 'IEF', 'SHY', 'LQD', 'HYG', 'GLD', 'SLV', 'USO', 'SPY', 'QQQ', 'DIA', 'ES=F', 'NQ=F', 'GC=F', 'CL=F']
all_rets = {}
for t in tickers:
    df = pd.read_sql("SELECT date, close_price FROM daily_prices WHERE ticker=? ORDER BY date", engine, params=(t,))
    df['date'] = pd.to_datetime(df['date'])
    df = df.set_index('date')
    all_rets[t] = df['close_price'].pct_change()
    print(f"{t}: {len(all_rets[t].dropna())}")

comb = pd.DataFrame(all_rets).dropna()
print("Combined dropna shape:", comb.shape)