import os
import json
import logging
import traceback
from datetime import datetime
from flask import Flask, request, jsonify
from flask_cors import CORS
from scipy import stats
import yfinance as yf
import pandas as pd
import numpy as np
import empyrical as ep
import statsmodels.api as sm  # New import for regression
from backend.routes.user_routes import user_bp
from backend.database.config import get_db, SessionLocal
from sqlalchemy import text, func
import threading
import time
from backend.database.utils import (
    get_stock_data_from_db,
    save_stock_data_to_db,
    get_latest_date_for_ticker
)
from werkzeug.security import generate_password_hash, check_password_hash
from backend.models import User, Portfolio, Base
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import joinedload
from sqlalchemy.ext.declarative import declarative_base

app = Flask(__name__)
# Update CORS configuration to explicitly allow required headers
CORS(app, resources={
    r"/api/*": {
        "origins": ["http://localhost:5173"],
        "methods": ["GET", "POST", "DELETE", "OPTIONS"],
        "allow_headers": [
            "Content-Type",
            "Cache-Control",
            "Authorization"
        ]
    }
})

# Register the user blueprint
app.register_blueprint(user_bp, url_prefix='/api/user')

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Constants
PORTFOLIO_DIR = 'portfolios'
CACHE_DIR = 'cache'
if not os.path.exists(PORTFOLIO_DIR):
    os.makedirs(PORTFOLIO_DIR)
if not os.path.exists(CACHE_DIR):
    os.makedirs(CACHE_DIR)

# Add this constant at the top of the file
ANALYSIS_COST = 9  # Cost in tokens per analysis

# Utility Functions
def handle_preflight():
    """Handle CORS preflight requests"""
    response = app.make_default_options_response()
    response.headers['Access-Control-Allow-Methods'] = 'POST, OPTIONS'
    response.headers['Access-Control-Allow-Headers'] = 'Content-Type'
    return response

def get_cache_key(ticker, start_date):
    """Generate a cache key based on ticker and start date."""
    # Convert datetime to string format safe for filenames
    if isinstance(start_date, pd.Timestamp):
        start_date = start_date.strftime('%Y%m%d')
    elif isinstance(start_date, str):
        start_date = pd.to_datetime(start_date).strftime('%Y%m%d')
    return f"{ticker}_{start_date}.json"

def load_from_cache(cache_key):
    """Load data from cache if available."""
    try:
        cache_path = os.path.join(CACHE_DIR, cache_key)
        if os.path.exists(cache_path):
            data = pd.read_json(cache_path)
            # Convert string index back to datetime
            data.index = pd.to_datetime(data.index)
            if 'Adj Close' not in data.columns:
                logger.warning(f"Invalid cache data structure in {cache_key}")
                return None
            logger.info(f"Successfully loaded cache from {cache_path}")
            return data
    except Exception as e:
        logger.error(f"Failed to load cache for {cache_key}: {str(e)}")
    return None

def save_to_cache(cache_key, data):
    """Save data to cache."""
    try:
        cache_path = os.path.join(CACHE_DIR, cache_key)
        # Convert datetime index to string format before saving
        data.index = data.index.strftime('%Y-%m-%d')
        data.to_json(cache_path)
        logger.info(f"Successfully cached data to {cache_path}")
    except Exception as e:
        logger.error(f"Failed to save cache for {cache_key}: {str(e)}")

def clean_for_json(obj):
    """Clean objects to make them JSON serializable."""
    if isinstance(obj, (float, np.float32, np.float64)):
        if np.isnan(obj) or np.isinf(obj):
            return 0.0  # Replace NaN/Inf with 0
        return float(obj)
    elif isinstance(obj, (int, np.int32, np.int64)):
        return int(obj)
    elif isinstance(obj, (bool, np.bool_)):  # Add handling for numpy booleans
        return bool(obj)
    elif isinstance(obj, dict):
        return {k: clean_for_json(v) for k, v in obj.items()}
    elif isinstance(obj, (list, tuple)):
        return [clean_for_json(x) for x in obj]
    elif isinstance(obj, np.ndarray):
        return clean_for_json(obj.tolist())
    elif pd.isna(obj):
        return None  # Replace pandas NA with None
    return obj

def fetch_stock_data(ticker, start_date):
    """
    Fetch historical stock data from database first, then yfinance if needed.
    Also checks for earliest available date.
    """
    try:
        start_date = pd.to_datetime(start_date)
        ticker = ticker.strip().upper()
        today = pd.Timestamp.now().normalize()
        
        # First check yfinance for earliest available date
        try:
            # Use a more recent date for the initial check (e.g., 1970)
            earliest_check = yf.download(
                ticker,
                start='1970-01-01',
                end=today,
                progress=False,
                auto_adjust=False,
                timeout=30
            )
            if earliest_check.empty:
                logger.error(f"No data available from yfinance for {ticker}")
                return None
                
            earliest_available = earliest_check.index[0]
            logger.info(f"Earliest available date for {ticker}: {earliest_available}")
            
            # If requested start date is before earliest available
            if start_date < earliest_available:
                logger.warning(f"Adjusting start date from {start_date} to {earliest_available} for {ticker}")
                start_date = earliest_available
                
        except Exception as e:
            logger.error(f"Error checking earliest date for {ticker}: {str(e)}")
            return None

        # Get sector info and proceed with normal data fetch
        db = SessionLocal()
        try:
            sector_query = text("""
                SELECT gics_sector, gics_sub_industry 
                FROM market_tickers 
                WHERE ticker = :ticker
            """)
            sector_info = db.execute(sector_query, {"ticker": ticker}).first()
            
            # Get data from database
            stock_query = text("""
                SELECT date, open, high, low, close, volume
                FROM stock_data 
                WHERE ticker = :ticker 
                AND date >= :start_date
                ORDER BY date
            """)
            
            result = db.execute(
                stock_query, 
                {"ticker": ticker, "start_date": start_date}
            )
            
            # Convert result to DataFrame
            db_data = pd.DataFrame(result.fetchall(), columns=['Date', 'Open', 'High', 'Low', 'Close', 'Volume'])
            if not db_data.empty:
                db_data.set_index('Date', inplace=True)
                db_data['Adj Close'] = db_data['Close']
                
                if sector_info:
                    db_data['gics_sector'] = sector_info[0]
                    db_data['gics_sub_industry'] = sector_info[1]
                
                latest_db_date = db_data.index.max()
                
                # If data is up to date and covers the entire requested period, return it
                if latest_db_date >= today - pd.Timedelta(days=1) and db_data.index[0] <= start_date:
                    logger.info(f"Using database data for {ticker}")
                    return db_data
                
                # If we need to fetch new data or backfill historical data
                if db_data.index[0] > start_date:
                    # Need to fetch historical data - use the adjusted start_date
                    historical_data = yf.download(
                        ticker,
                        start=start_date,  # Use adjusted start_date
                        end=db_data.index[0],
                        progress=False,
                        auto_adjust=False,
                        timeout=30
                    )
                    if not historical_data.empty:
                        if sector_info:
                            historical_data['gics_sector'] = sector_info[0]
                            historical_data['gics_sub_industry'] = sector_info[1]
                        save_stock_data_to_db(ticker, historical_data)
                        db_data = pd.concat([historical_data, db_data])
                
                if latest_db_date < today - pd.Timedelta(days=1):
                    # Need to fetch recent data
                    new_data = yf.download(
                        ticker,
                        start=latest_db_date + pd.Timedelta(days=1),
                        end=today,
                        progress=False,
                        auto_adjust=False,
                        timeout=30
                    )
                    if not new_data.empty:
                        if sector_info:
                            new_data['gics_sector'] = sector_info[0]
                            new_data['gics_sub_industry'] = sector_info[1]
                        save_stock_data_to_db(ticker, new_data)
                        db_data = pd.concat([db_data, new_data])
                
                return db_data
            
            # If no data in database, fetch all from yfinance
            logger.info(f"Fetching all data for {ticker} from yfinance")
            stock_data = yf.download(
                ticker,
                start=start_date,
                end=today,
                progress=False,
                auto_adjust=False,
                timeout=30
            )
            
            if not stock_data.empty:
                if sector_info:
                    stock_data['gics_sector'] = sector_info[0]
                    stock_data['gics_sub_industry'] = sector_info[1]
                
                save_stock_data_to_db(ticker, stock_data)
                return stock_data
            
            logger.warning(f"No data available for {ticker}")
            return None
            
        finally:
            db.close()

    except Exception as e:
        logger.error(f"Failed to fetch data for {ticker}: {str(e)}", exc_info=True)
        return None

def get_db_connection():
    """Get database connection."""
    import psycopg2
    from backend.database.config import DB_CONFIG
    return psycopg2.connect(**DB_CONFIG)

def calculate_volume_metrics(data):
    """Calculate volume-related metrics for the portfolio."""
    try:
        # Calculate basic volume metrics
        volume_data = data['Volume']
        price_data = data['Close']
        returns = data['Close'].pct_change()
        
        # Basic volume statistics
        avg_volume = volume_data.mean()
        median_volume = volume_data.median()
        volume_std = volume_data.std()
        
        # Calculate 20-day moving average volume
        volume_ma20 = volume_data.rolling(window=20).mean()
        
        # Volume trend (comparing recent volume to historical)
        recent_volume = volume_data[-20:].mean()  # Last 20 days
        volume_trend = recent_volume / avg_volume - 1  # Positive means increasing volume
        
        # Volume volatility (coefficient of variation)
        volume_volatility = volume_std / avg_volume
        
        # Calculate price-volume correlation
        price_volume_corr = price_data.corr(volume_data)
        
        # Calculate returns-volume correlation
        returns_volume_corr = returns.corr(volume_data)
        
        # Volume ratio (current volume to moving average)
        volume_ratio = volume_data / volume_ma20
        
        # Calculate up/down volume metrics
        up_days = returns > 0
        down_days = returns < 0
        
        up_volume = volume_data[up_days].mean()
        down_volume = volume_data[down_days].mean()
        up_down_ratio = up_volume / down_volume if down_volume != 0 else float('inf')
        
        # Calculate volume momentum (rate of change)
        volume_momentum_5d = (volume_data / volume_data.shift(5) - 1).mean()
        volume_momentum_20d = (volume_data / volume_data.shift(20) - 1).mean()
        
        # Calculate relative volume metrics
        relative_volume = volume_data / volume_ma20
        high_volume_days = (relative_volume > 1.5).sum()  # Days with 50% above average volume
        low_volume_days = (relative_volume < 0.5).sum()   # Days with 50% below average volume
        
        # Calculate volume-weighted metrics
        vwap = (price_data * volume_data).sum() / volume_data.sum()  # Volume-weighted average price
        typical_volume = volume_data.quantile(0.5)  # Median volume
        
        return {
            'basic_metrics': {
                'average_volume': float(avg_volume),
                'median_volume': float(median_volume),
                'volume_std': float(volume_std),
                'volume_volatility': float(volume_volatility)
            },
            'trend_analysis': {
                'volume_trend': float(volume_trend),
                'volume_momentum_5d': float(volume_momentum_5d),
                'volume_momentum_20d': float(volume_momentum_20d)
            },
            'correlations': {
                'price_volume_correlation': float(price_volume_corr),
                'returns_volume_correlation': float(returns_volume_corr)
            },
            'trading_patterns': {
                'up_down_volume_ratio': float(up_down_ratio),
                'up_volume': float(up_volume),
                'down_volume': float(down_volume)
            },
            'relative_metrics': {
                'high_volume_days': int(high_volume_days),
                'low_volume_days': int(low_volume_days),
                'typical_volume': float(typical_volume),
                'vwap': float(vwap)
            }
        }
    except Exception as e:
        logger.error(f"Error calculating volume metrics: {str(e)}")
        return {
            'basic_metrics': {},
            'trend_analysis': {},
            'correlations': {},
            'trading_patterns': {},
            'relative_metrics': {}
        }

def calculate_portfolio_metrics(returns, benchmark_returns=None, portfolio_data=None):
    """Calculate enhanced portfolio metrics."""
    try:
        # Ensure returns is a pandas Series
        if not isinstance(returns, pd.Series):
            raise ValueError("returns should be a pandas Series representing the portfolio returns.")
        metrics = {}
        logger.info("Starting portfolio metrics calculation...")
        
        # Convert returns to numeric, replacing any non-numeric values with NaN
        returns = pd.to_numeric(returns, errors='coerce').fillna(0)
        
        # Calculate drawdowns first
        drawdown_analysis = calculate_drawdown_periods(returns)
        metrics['drawdowns'] = drawdown_analysis
        
        # Store returns for chart
        metrics['returns'] = returns.tolist()
        metrics['dates'] = returns.index.strftime('%Y-%m-%d').tolist()
        
        # Calculate cumulative returns
        cumulative_returns = (1 + returns).cumprod()
        metrics['cumulative_returns'] = cumulative_returns.tolist()
        
        # Basic portfolio metrics
        initial_value = 10000.0
        metrics['start_balance'] = initial_value
        metrics['end_balance'] = float(initial_value * cumulative_returns.iloc[-1])
        metrics['total_return'] = float((metrics['end_balance'] / initial_value) - 1)
        
        # Time period calculations
        total_days = len(returns)
        total_years = total_days / 252  # Assuming 252 trading days per year
        metrics['total_days'] = total_days
        metrics['total_years'] = total_years
        
        # Risk-free rate (2% annual)
        risk_free_rate = 0.02 / 252  # Daily risk-free rate
        
        try:
            # CAGR calculation
            metrics['cagr'] = float(((1 + metrics['total_return']) ** (1 / total_years)) - 1) if total_years > 0 else 0.0
            
            # Volatility calculations
            daily_std = float(returns.std())
            metrics['daily_volatility'] = daily_std
            metrics['annual_volatility'] = float(daily_std * np.sqrt(252))
            
            # Rolling volatility (30-day window)
            rolling_vol = returns.rolling(window=30).std() * np.sqrt(252)
            metrics['rolling_volatility'] = rolling_vol.tolist()
            
            # Risk metrics
            metrics['sharpe_ratio'] = float(ep.sharpe_ratio(returns, risk_free=risk_free_rate))
            metrics['sortino_ratio'] = float(ep.sortino_ratio(returns, required_return=risk_free_rate))
            metrics['max_drawdown'] = float(ep.max_drawdown(returns))
            
            # Calculate rolling returns properly
            def calculate_rolling_return(window):
                return ((1 + returns).rolling(window=window).apply(
                    lambda x: np.prod(x) - 1, raw=True
                ))
    
            rolling_returns = {
                '1M': calculate_rolling_return(21),    # Monthly
                '3M': calculate_rolling_return(63),    # Quarterly
                '6M': calculate_rolling_return(126),   # Semi-annual
                '1Y': calculate_rolling_return(252)    # Annual
            }
            
            # Get the most recent rolling returns
            metrics['rolling_returns'] = {
                period: float(values.iloc[-1]) if not pd.isna(values.iloc[-1]) else 0.0
                for period, values in rolling_returns.items()
            }
            
            # Value at Risk (VaR) calculations
            returns_array = returns.values
            metrics['var_95'] = float(np.percentile(returns_array, 5))  # 95% VaR
            metrics['var_99'] = float(np.percentile(returns_array, 1))  # 99% VaR
            
            # Conditional VaR (CVaR) calculations
            var_95_mask = returns_array <= metrics['var_95']
            var_99_mask = returns_array <= metrics['var_99']
            
            metrics['cvar_95'] = float(np.mean(returns_array[var_95_mask])) if np.any(var_95_mask) else metrics['var_95']
            metrics['cvar_99'] = float(np.mean(returns_array[var_99_mask])) if np.any(var_99_mask) else metrics['var_99']
            
            # Return distribution metrics
            from scipy import stats
            metrics['skewness'] = float(stats.skew(returns_array))
            metrics['kurtosis'] = float(stats.kurtosis(returns_array, fisher=True))
            
            logger.info(f"Base metrics calculated: CAGR={metrics['cagr']:.2%}, Vol={metrics['annual_volatility']:.2%}")
            logger.info(f"Risk metrics: VaR={metrics['var_95']:.2%}, CVaR={metrics['cvar_95']:.2%}")
            logger.info(f"Distribution metrics: Skew={metrics['skewness']:.2f}, Kurt={metrics['kurtosis']:.2f}")
            
        except Exception as e:
            logger.error(f"Error calculating base metrics: {str(e)}")
            metrics.update({
                'cagr': 0.0,
                'annual_volatility': 0.0,
                'sharpe_ratio': 0.0,
                'sortino_ratio': 0.0,
                'max_drawdown': 0.0,
                'var_95': 0.0,
                'var_99': 0.0,
                'cvar_95': 0.0,
                'cvar_99': 0.0,
                'skewness': 0.0,
                'kurtosis': 0.0,
                'rolling_returns': {
                    '1M': 0.0, '3M': 0.0, '6M': 0.0, '1Y': 0.0
                }
            })
    
        # Benchmark comparison metrics
        if benchmark_returns is not None:
            try:
                # Align returns for benchmark calculations
                common_dates = returns.index.intersection(benchmark_returns.index)
                aligned_returns = returns[common_dates]
                aligned_benchmark = benchmark_returns[common_dates]
                
                # Calculate excess returns
                excess_returns = aligned_returns - aligned_benchmark
                tracking_error = float(np.std(excess_returns) * np.sqrt(252)) if len(excess_returns) > 1 else 0.0
                
                # Regression analysis for beta and alpha
                X = sm.add_constant(aligned_benchmark)
                model = sm.OLS(aligned_returns, X).fit()
                
                # Calculate annualized alpha and beta
                alpha = float(model.params['const'] * 252)
                beta = float(model.params[1])
                r_squared = float(model.rsquared)
                
                # Calculate up/down capture ratios properly
                up_market = aligned_benchmark > 0
                down_market = aligned_benchmark < 0
                
                # Calculate up/down capture using cumulative returns
                up_portfolio = (1 + aligned_returns[up_market]).prod() - 1
                up_benchmark = (1 + aligned_benchmark[up_market]).prod() - 1
                down_portfolio = (1 + aligned_returns[down_market]).prod() - 1
                down_benchmark = (1 + aligned_benchmark[down_market]).prod() - 1
                
                up_capture = float(up_portfolio / up_benchmark if up_benchmark != 0 else 1.0)
                down_capture = float(down_portfolio / down_benchmark if down_benchmark != 0 else 1.0)
                
                # Calculate information ratio and active return
                excess_return_mean = float(excess_returns.mean() * 252)  # Annualized
                information_ratio = float(excess_return_mean / tracking_error) if tracking_error > 0 else 0.0
                active_return = float(excess_return_mean)
                
                # Calculate Treynor ratio
                excess_return_mean = float(np.mean(aligned_returns - risk_free_rate) * 252)  # Annualized
                treynor_ratio = float(excess_return_mean / beta) if beta != 0 else 0.0
                
                metrics.update({
                    'alpha': alpha,
                    'beta': beta,
                    'r_squared': r_squared,
                    'tracking_error': tracking_error,
                    'information_ratio': information_ratio,
                    'active_return': active_return,
                    'up_capture': up_capture,
                    'down_capture': down_capture,
                    'correlation': float(aligned_returns.corr(aligned_benchmark)),
                    'treynor_ratio': treynor_ratio
                })
                
                logger.info(f"Relative metrics calculated: Beta={beta:.2f}, Alpha={alpha:.2%}, "
                          f"Information Ratio={information_ratio:.2f}, Treynor={treynor_ratio:.2f}")
                
            except Exception as e:
                logger.error(f"Error calculating benchmark metrics: {str(e)}")
                metrics.update({
                    'alpha': 0.0,
                    'beta': 1.0,
                    'r_squared': 0.0,
                    'tracking_error': 0.0,
                    'information_ratio': 0.0,
                    'active_return': 0.0,
                    'up_capture': 1.0,
                    'down_capture': 1.0,
                    'correlation': 0.0,
                    'treynor_ratio': 0.0
                })
    
        # Calculate volume metrics
        if portfolio_data is not None:
            try:
                logger.info("Starting volume metrics calculation...")
                logger.info(f"Portfolio data columns: {portfolio_data.columns}")
                
                if isinstance(portfolio_data, pd.DataFrame):
                    # For single asset portfolios
                    if len(portfolio_data.columns) == 1:
                        ticker = portfolio_data.columns[0]
                        logger.info(f"Calculating volume metrics for single asset: {ticker}")
                        # Get volume data from database
                        db = SessionLocal()
                        volume_query = text("""
                            SELECT date, close, volume
                            FROM stock_data
                            WHERE ticker = :ticker
                            ORDER BY date
                        """)
                        result = db.execute(volume_query, {"ticker": ticker})
                        volume_data = pd.DataFrame(result.fetchall(), columns=['date', 'Close', 'Volume'])
                        volume_data.set_index('date', inplace=True)
                        db.close()
                        
                        if not volume_data.empty:
                            metrics['volume_analysis'] = calculate_volume_metrics(volume_data)
                    else:
                        # Multi-asset portfolio
                        volume_metrics = {}
                        for ticker in portfolio_data.columns:
                            logger.info(f"Calculating volume metrics for: {ticker}")
                            # Get volume data from database
                            db = SessionLocal()
                            volume_query = text("""
                                SELECT date, close, volume
                                FROM stock_data
                                WHERE ticker = :ticker
                                ORDER BY date
                            """)
                            result = db.execute(volume_query, {"ticker": ticker})
                            volume_data = pd.DataFrame(result.fetchall(), columns=['date', 'Close', 'Volume'])
                            volume_data.set_index('date', inplace=True)
                            db.close()
                            
                            if not volume_data.empty:
                                volume_metrics[ticker] = calculate_volume_metrics(volume_data)
                        
                        if volume_metrics:
                            logger.info("Aggregating volume metrics for portfolio")
                            portfolio_volume_metrics = {
                                'basic_metrics': {
                                    'average_volume': np.mean([m['basic_metrics']['average_volume'] for m in volume_metrics.values()]),
                                    'volume_volatility': np.mean([m['basic_metrics']['volume_volatility'] for m in volume_metrics.values()])
                                },
                                'trend_analysis': {
                                    'volume_trend': np.mean([m['trend_analysis']['volume_trend'] for m in volume_metrics.values()]),
                                    'volume_momentum_20d': np.mean([m['trend_analysis']['volume_momentum_20d'] for m in volume_metrics.values()])
                                },
                                'trading_patterns': {
                                    'up_down_volume_ratio': np.mean([m['trading_patterns']['up_down_volume_ratio'] for m in volume_metrics.values()])
                                },
                                'relative_metrics': {
                                    'high_volume_days': int(np.mean([m['relative_metrics']['high_volume_days'] for m in volume_metrics.values()])),
                                    'low_volume_days': int(np.mean([m['relative_metrics']['low_volume_days'] for m in volume_metrics.values()])),
                                    'typical_volume': np.mean([m['relative_metrics']['typical_volume'] for m in volume_metrics.values()]),
                                    'vwap': np.mean([m['relative_metrics']['vwap'] for m in volume_metrics.values()])
                                },
                                'individual_assets': volume_metrics
                            }
                            metrics['volume_analysis'] = portfolio_volume_metrics
                
                logger.info("Volume metrics calculation completed")
                logger.info(f"Volume metrics: {metrics.get('volume_analysis', 'No volume metrics calculated')}")
                
            except Exception as e:
                logger.error(f"Error calculating volume metrics: {str(e)}", exc_info=True)
                metrics['volume_analysis'] = {}
        
        # Calculate risk metrics
        try:
            risk_metrics = calculate_risk_metrics(returns)
            metrics['risk_metrics'] = risk_metrics
            logger.info(f"Risk metrics calculated: {json.dumps(risk_metrics['daily_metrics'], indent=2)}")
        except Exception as e:
            logger.error(f"Error calculating risk metrics: {str(e)}")
            metrics['risk_metrics'] = {
                'daily_metrics': {
                    'var_95': 0.0,
                    'var_99': 0.0,
                    'cvar_95': 0.0,
                    'cvar_99': 0.0,
                    'volatility': 0.0
                },
                'annualized_metrics': {
                    'volatility': 0.0,
                    'downside_deviation': 0.0,
                    'sharpe_ratio': 0.0,
                    'sortino_ratio': 0.0
                },
                'drawdown_metrics': {
                    'max_drawdown': 0.0,
                    'current_drawdown': 0.0
                },
                'rolling_volatility': [],
                'distribution_metrics': {
                    'skewness': 0.0,
                    'kurtosis': 0.0,
                    'positive_days': 0,
                    'negative_days': 0,
                    'total_days': 0
                }
            }
        
        return clean_for_json(metrics)
        
    except Exception as e:
        logger.error(f"Error in calculate_portfolio_metrics: {str(e)}", exc_info=True)
        return {}

def calculate_risk_metrics(returns_data, rolling_window=30):
    """Calculate comprehensive risk metrics for portfolio analysis."""
    try:
        returns = returns_data
        
        returns_array = returns.values
        dates = returns.index
    
        # Basic Risk Metrics
        daily_std = np.std(returns_array)
        annualized_vol = daily_std * np.sqrt(252)
        
        # Value at Risk (VaR) calculations
        var_95 = np.percentile(returns_array, 5)  # 95% VaR
        var_99 = np.percentile(returns_array, 1)  # 99% VaR
        
        # Conditional VaR (Expected Shortfall)
        cvar_95 = returns_array[returns_array <= var_95].mean()
        cvar_99 = returns_array[returns_array <= var_99].mean()
    
        # Rolling Volatility
        rolling_vol = returns.rolling(window=rolling_window).std() * np.sqrt(252)
        rolling_vol_data = [
            {
                'date': date.strftime('%Y-%m-%d'),
                'volatility': vol
            }
            for date, vol in zip(dates, rolling_vol)
            if not np.isnan(vol)
        ]
    
        # Downside Risk Metrics
        negative_returns = returns_array[returns_array < 0]
        downside_deviation = np.sqrt(np.mean(negative_returns ** 2)) * np.sqrt(252)
        
        # Maximum Drawdown calculation
        cumulative_returns = (1 + returns).cumprod()
        rolling_max = cumulative_returns.expanding().max()
        drawdowns = (cumulative_returns - rolling_max) / rolling_max
        max_drawdown = drawdowns.min()
        current_drawdown = drawdowns.iloc[-1]
    
        # Risk Ratios
        risk_free_rate = 0.02 / 252  # Daily risk-free rate (2% annual)
        avg_return = np.mean(returns_array)
        sharpe_ratio = (avg_return * 252 - risk_free_rate * 252) / (daily_std * np.sqrt(252))
        sortino_ratio = (avg_return * 252 - risk_free_rate * 252) / (downside_deviation) if downside_deviation != 0 else 0
    
        # Distribution metrics
        skewness = float(stats.skew(returns_array))
        kurtosis = float(stats.kurtosis(returns_array))
        positive_days = int(np.sum(returns_array > 0))
        negative_days = int(np.sum(returns_array < 0))
    
        return {
            'daily_metrics': {
                'var_95': float(var_95),
                'var_99': float(var_99),
                'cvar_95': float(cvar_95),
                'cvar_99': float(cvar_99),
                'volatility': float(daily_std)
            },
            'annualized_metrics': {
                'volatility': float(annualized_vol),
                'downside_deviation': float(downside_deviation),
                'sharpe_ratio': float(sharpe_ratio),
                'sortino_ratio': float(sortino_ratio)
            },
            'drawdown_metrics': {
                'max_drawdown': float(max_drawdown),
                'current_drawdown': float(current_drawdown)
            },
            'rolling_volatility': rolling_vol_data,
            'distribution_metrics': {
                'skewness': skewness,
                'kurtosis': kurtosis,
                'positive_days': positive_days,
                'negative_days': negative_days,
                'total_days': len(returns_array)
            }
        }

    except Exception as e:
        logger.error(f"Error calculating risk metrics: {str(e)}")
        return {
            'daily_metrics': {},
            'annualized_metrics': {},
            'drawdown_metrics': {},
            'rolling_volatility': [],
            'distribution_metrics': {}
        }

def calculate_correlation_matrix(portfolio_data):
    """Calculate correlation matrices for the portfolio."""
    try:
        # Calculate returns
        returns = portfolio_data.pct_change().dropna()
        
        # Calculate Pearson correlation
        pearson_matrix = returns.corr(method='pearson')
        pearson_values = pearson_matrix.values[np.triu_indices_from(pearson_matrix.values, k=1)]
        
        # Calculate Spearman correlation
        spearman_matrix = returns.corr(method='spearman')
        spearman_values = spearman_matrix.values[np.triu_indices_from(spearman_matrix.values, k=1)]
        
        # Convert matrices to dictionaries
        pearson_dict = {ticker: pearson_matrix[ticker].to_dict() for ticker in pearson_matrix.index}
        spearman_dict = {ticker: spearman_matrix[ticker].to_dict() for ticker in spearman_matrix.index}
        
        # Calculate rolling correlations
        rolling_window = min(252, len(returns) // 2)  # Use 1 year or half the data length
        if rolling_window < 2:
            rolling_window = 2  # Minimum window size
        
        rolling_corr = returns.rolling(window=rolling_window).corr()
        
        # Get the most recent rolling correlations
        latest_rolling = rolling_corr.xs(returns.index[-1], level=0)
        rolling_dict = {ticker: latest_rolling[ticker].to_dict() for ticker in latest_rolling.index}
        
        return {
            'pearson': {
                'matrix': pearson_dict,
                'avg': float(np.mean(pearson_values)),
                'min': float(np.min(pearson_values)),
                'max': float(np.max(pearson_values))
            },
            'spearman': {
                'matrix': spearman_dict,
                'avg': float(np.mean(spearman_values)),
                'min': float(np.min(spearman_values)),
                'max': float(np.max(spearman_values))
            },
            'rolling': {
                'matrix': rolling_dict,
                'window': rolling_window
            }
        }
    except Exception as e:
        logger.error(f"Error in calculate_correlation_matrix: {str(e)}", exc_info=True)
        return None

def calculate_drawdown_periods(returns):
    """
    Identify drawdown periods in the portfolio.
    """
    try:
        # Calculate cumulative returns and running maximum
        initial_value = 10000  # Start with $10,000
        cumulative = initial_value * (1 + returns).cumprod()
        running_max = cumulative.expanding().max()
        drawdown = (cumulative - running_max) / running_max
        
        # Prepare chart data
        chart_data = {
            'dates': returns.index.strftime('%Y-%m-%d').tolist(),
            'drawdowns': drawdown.tolist(),
            'cumulative': cumulative.tolist(),
            'peaks': running_max.tolist()
        }

        # Track all drawdowns
        significant_drawdowns = []
        
        # Initialize tracking variables
        in_drawdown = False
        peak_value = cumulative.iloc[0]
        peak_date = returns.index[0]
        trough_value = peak_value
        trough_date = peak_date
        
        # Track drawdown periods
        for i in range(1, len(cumulative)):
            current_value = cumulative.iloc[i]
            current_date = returns.index[i]
            
            # New peak
            if current_value > peak_value and not in_drawdown:
                peak_value = current_value
                peak_date = current_date
            
            # Calculate current drawdown from peak
            current_drawdown = (current_value - peak_value) / peak_value
            
            # Start or deepen drawdown
            if current_drawdown < -0.05:  # Only track drawdowns deeper than 5%
                if not in_drawdown:
                    in_drawdown = True
                    trough_value = current_value
                    trough_date = current_date
                elif current_value < trough_value:
                    trough_value = current_value
                    trough_date = current_date
            
            # Check for recovery (back to peak or new high)
            elif in_drawdown and current_value >= peak_value * 0.95:  # Consider recovered when within 5% of peak
                depth = (trough_value - peak_value) / peak_value
                length_days = (trough_date - peak_date).days
                recovery_days = (current_date - trough_date).days
                
                drawdown_info = {
                    'start': peak_date.strftime('%Y-%m-%d'),
                    'end': trough_date.strftime('%Y-%m-%d'),
                    'recovery': current_date.strftime('%Y-%m-%d'),
                    'depth': float(depth),
                    'length_days': int(length_days),
                    'recovery_time_days': int(recovery_days),
                    'peak_value': float(peak_value),
                    'trough_value': float(trough_value),
                    'recovery_value': float(current_value),
                    'is_active': False
                }
                significant_drawdowns.append(drawdown_info)
                
                # Reset for next drawdown
                in_drawdown = False
                peak_value = current_value
                peak_date = current_date
        
        # Handle ongoing drawdown
        if in_drawdown:
            current_value = cumulative.iloc[-1]
            current_date = returns.index[-1]
            depth = (trough_value - peak_value) / peak_value
            
            drawdown_info = {
                'start': peak_date.strftime('%Y-%m-%d'),
                'end': trough_date.strftime('%Y-%m-%d'),
                'recovery': None,
                'depth': float(depth),
                'length_days': int((current_date - peak_date).days),
                'recovery_time_days': None,
                'peak_value': float(peak_value),
                'trough_value': float(trough_value),
                'is_active': True
            }
            significant_drawdowns.append(drawdown_info)
        
        # Sort drawdowns by depth (most severe first)
        significant_drawdowns.sort(key=lambda x: x['depth'])
        worst_drawdowns = significant_drawdowns[:10]  # Take 10 worst drawdowns
        
        # Calculate average metrics from all drawdowns
        avg_depth = np.mean([abs(d['depth']) for d in significant_drawdowns]) if significant_drawdowns else 0
        recovered_drawdowns = [d for d in significant_drawdowns if d['recovery_time_days'] is not None]
        avg_recovery = np.mean([d['recovery_time_days'] for d in recovered_drawdowns]) if recovered_drawdowns else 0
        
        return {
            'periods': worst_drawdowns,
            'chart_data': chart_data,
            'max_drawdown': float(drawdown.min()) if len(drawdown) > 0 else 0.0,
            'avg_depth': float(avg_depth),
            'avg_recovery': float(round(avg_recovery, 2)),  # Ensure float with 2 decimal places
            'total_events': len(significant_drawdowns)
        }

    except Exception as e:
        logger.error(f"Error in calculate_drawdown_periods: {str(e)}", exc_info=True)
        return {
            'periods': [],
            'chart_data': {
                'dates': [],
                'drawdowns': [],
                'cumulative': [],
                'peaks': []
            },
            'max_drawdown': 0.0,
            'avg_depth': 0.0,
            'avg_recovery': 0.0,
            'total_events': 0
        }

def calculate_rebalancing_impact(portfolio_data, weights, transaction_cost):
    """Calculate the impact of different rebalancing strategies."""
    try:
        logger.info("Starting rebalancing impact analysis...")
        
        # If it's a single-asset portfolio (like SPY), return same values for all strategies
        if len(weights) == 1:
            logger.info("Single-asset portfolio detected - no rebalancing needed")
            returns = portfolio_data.pct_change().fillna(0)
            metrics = calculate_portfolio_metrics(returns.iloc[:, 0])  # Use first column
            
            # Create base metrics for single-asset portfolio
            base_metrics = {
                'portfolio_values': (10000 * (1 + returns.iloc[:, 0]).cumprod()).tolist(),
                'final_value': float(10000 * (1 + returns.iloc[:, 0]).cumprod().iloc[-1]),
                'total_return': float((1 + returns.iloc[:, 0]).cumprod().iloc[-1] - 1),
                'rebalance_count': 0,
                'total_costs': 0.0,
                'cost_percentage': 0.0,
                'avg_turnover': 0.0,
                'avg_tracking_error': 0.0,
                'weight_drift': {
                    'mean': 0.0,
                    'max': 0.0,
                    'current': 0.0,
                    'history': [0.0] * len(returns)
                },
                'current_weights': [1.0],
                'weight_history': [[1.0]] * len(returns)
            }
            
            # Update with calculated metrics
            metrics.update(base_metrics)
            
            # Return same metrics for all strategies
            return {
                strategy: clean_for_json(metrics)
                for strategy in ['Buy and Hold', 'Monthly', 'Quarterly', 'Semi-Annual', 'Annual']
            }
        
        # For multi-asset portfolios, proceed with rebalancing calculation
        # Ensure weights are numpy array and sum to 1
        weights = np.array(weights)
        if not np.isclose(weights.sum(), 1.0):
            weights = weights / weights.sum()
            
        # Calculate returns and ensure no NaN values
        returns = portfolio_data.pct_change().fillna(0)
        
        # Define rebalancing strategies with specific day offsets
        strategies = {
            'Buy and Hold': None,  # No rebalancing
            'Monthly': pd.DateOffset(months=1),
            'Quarterly': pd.DateOffset(months=3),
            'Semi-Annual': pd.DateOffset(months=6),
            'Annual': pd.DateOffset(years=1)
        }
        
        results = {}
        initial_value = 10000.0  # Start with $10,000
        
        for strategy_name, rebalance_period in strategies.items():
            try:
                # Initialize portfolio tracking
                portfolio_value = initial_value
                current_weights = weights.copy()
                current_holdings = portfolio_value * current_weights
                total_costs = 0
                rebalance_count = 0
                portfolio_values = [initial_value]  # Start with initial value
                daily_returns = [0]  # Start with 0 return
                turnover = []
                tracking_error = []
                current_weights_history = [weights.tolist()]  # Start with initial weights
                weight_drift_history = [0]  # Start with 0 drift
                
                last_rebalance_date = returns.index[0]
                
                # Track daily portfolio changes
                for date in returns.index[1:]:  # Skip first day since we already initialized
                    # Update holdings based on daily returns
                    daily_return_vector = returns.loc[date].values
                    current_holdings *= (1 + daily_return_vector)
                    portfolio_value = current_holdings.sum()
                    
                    # Calculate current weights
                    current_weights = current_holdings / portfolio_value
                    
                    # Calculate weight drift
                    weight_drift = np.sqrt(np.sum((current_weights - weights) ** 2))
                    weight_drift_history.append(float(weight_drift))
                    
                    # Check if rebalancing is needed
                    should_rebalance = False
                    if strategy_name != 'Buy and Hold':
                        # Check if enough time has passed according to strategy
                        if rebalance_period and date > last_rebalance_date + rebalance_period:
                            # Only rebalance if drift exceeds threshold
                            if weight_drift > 0.01:  # 1% threshold
                                should_rebalance = True
                    
                    if should_rebalance:
                        # Calculate trades needed
                        target_holdings = portfolio_value * weights
                        trades = target_holdings - current_holdings
                        
                        # Calculate transaction costs
                        trade_costs = abs(trades).sum() * transaction_cost
                        
                        # Apply transaction costs
                        portfolio_value -= trade_costs
                        total_costs += trade_costs
                        
                        # Update holdings and record rebalancing
                        current_holdings = portfolio_value * weights
                        last_rebalance_date = date
                        rebalance_count += 1
                        
                        # Calculate turnover
                        turnover.append(abs(trades).sum() / (2 * portfolio_value))
                    
                    # Calculate daily return
                    daily_return = (portfolio_value / portfolio_values[-1]) - 1
                    
                    # Record daily tracking data
                    portfolio_values.append(portfolio_value)
                    daily_returns.append(daily_return)
                    current_weights_history.append(current_weights.tolist())
                    tracking_error.append(float(np.std(current_weights - weights)))
                
                # Calculate strategy metrics
                daily_returns_series = pd.Series(daily_returns, index=returns.index)
                metrics = calculate_portfolio_metrics(daily_returns_series)
                
                # Add rebalancing-specific metrics
                metrics.update({
                    'portfolio_values': portfolio_values,
                    'final_value': float(portfolio_values[-1]),
                    'total_return': float((portfolio_values[-1] / initial_value) - 1),
                    'rebalance_count': int(rebalance_count),
                    'total_costs': float(total_costs),
                    'cost_percentage': float(total_costs / portfolio_values[-1]),
                    'avg_turnover': float(np.mean(turnover) if turnover else 0),
                    'avg_tracking_error': float(np.mean(tracking_error)),
                    'weight_drift': {
                        'mean': float(np.mean(weight_drift_history)),
                        'max': float(np.max(weight_drift_history)),
                        'current': float(weight_drift_history[-1]),
                        'history': [float(x) for x in weight_drift_history]
                    },
                    'current_weights': current_weights.tolist(),
                    'weight_history': current_weights_history
                })
                
                results[strategy_name] = clean_for_json(metrics)
                
            except Exception as e:
                logger.error(f"Error calculating {strategy_name} strategy: {str(e)}")
                results[strategy_name] = {
                    'error': str(e),
                    'final_value': initial_value,
                    'total_return': 0.0,
                    'rebalance_count': 0,
                    'total_costs': 0.0
                }
        
        return results
            
    except Exception as e:
        logger.error(f"Error in calculate_rebalancing_impact: {str(e)}")
        return {}

def calculate_attribution(portfolio_data, weights, benchmark_data):
    """Calculate enhanced performance attribution metrics."""
    try:
        logger.info("Starting attribution analysis...")
        
        # Debug: Log initial shapes
        logger.info(f"Initial shapes - Portfolio: {portfolio_data.shape}, Benchmark: {benchmark_data.shape}")
        
        # Calculate returns and align dates
        portfolio_returns = portfolio_data.pct_change().fillna(0)
        benchmark_returns = benchmark_data.pct_change().fillna(0)
        
        # Debug: Log returns shapes before alignment
        logger.info(f"Returns shapes before alignment - Portfolio: {portfolio_returns.shape}, Benchmark: {benchmark_returns.shape}")
        logger.info(f"Portfolio dates range: {portfolio_returns.index[0]} to {portfolio_returns.index[-1]}")
        logger.info(f"Benchmark dates range: {benchmark_returns.index[0]} to {benchmark_returns.index[-1]}")
        
        # Align dates between portfolio and benchmark
        common_dates = portfolio_returns.index.intersection(benchmark_returns.index)
        logger.info(f"Number of common dates: {len(common_dates)}")
        
        portfolio_returns = portfolio_returns.loc[common_dates]
        benchmark_returns = benchmark_returns.loc[common_dates]
        
        # Debug: Log shapes after alignment
        logger.info(f"Returns shapes after alignment - Portfolio: {portfolio_returns.shape}, Benchmark: {benchmark_returns.shape}")
        
        # Calculate weighted portfolio returns
        weights = np.array(weights)
        weights = weights / weights.sum()  # Normalize weights
        weighted_returns = (portfolio_returns * weights).sum(axis=1)
        
        # Debug: Log weighted returns shape
        logger.info(f"Weighted returns shape: {weighted_returns.shape}")
        
        # Get sector data from market_tickers table
        sector_data = {}
        db = SessionLocal()
        
        for ticker in portfolio_data.columns:
            sector_query = text("""
                SELECT gics_sector, gics_sub_industry 
                FROM market_tickers 
                WHERE ticker = :ticker
            """)
            sector_info = db.execute(sector_query, {"ticker": ticker}).first()
            
            if sector_info:
                sector = sector_info[0]
                if sector not in sector_data:
                    sector_data[sector] = {
                        'holdings': [],
                        'weight': 0,
                        'return': 0,
                        'contribution': 0
                    }
                sector_data[sector]['holdings'].append(ticker)
                sector_data[sector]['weight'] += weights[list(portfolio_data.columns).index(ticker)]
        
        db.close()

        # Calculate sector returns and contributions
        for sector in sector_data:
            sector_tickers = sector_data[sector]['holdings']
            sector_weights = np.array([weights[list(portfolio_data.columns).index(t)] for t in sector_tickers])
            sector_weights = sector_weights / sector_weights.sum()  # Normalize weights within sector
            
            sector_returns = portfolio_returns[sector_tickers]
            sector_data[sector]['return'] = (sector_returns * sector_weights).sum(axis=1).mean() * 252  # Annualized
            sector_data[sector]['contribution'] = sector_data[sector]['return'] * sector_data[sector]['weight']

        # Factor Attribution Calculations
        excess_returns = weighted_returns - benchmark_returns.iloc[:, 0]  # Get first column for benchmark
        
        # Convert Series to numpy arrays and ensure they're 1D
        weighted_returns_array = weighted_returns.values.reshape(-1)
        benchmark_returns_array = benchmark_returns.values.reshape(-1)  # Flatten to 1D
        excess_returns_array = excess_returns.values.reshape(-1)
        
        # Debug: Log array shapes before covariance
        logger.info(f"Array shapes before covariance:")
        logger.info(f"Weighted returns: {weighted_returns_array.shape}")
        logger.info(f"Benchmark returns: {benchmark_returns_array.shape}")
        
        # Calculate correlation and beta directly
        correlation = np.corrcoef(weighted_returns_array, benchmark_returns_array)[0,1]
        beta = correlation * (np.std(weighted_returns_array) / np.std(benchmark_returns_array))
        
        # Debug: Log correlation and beta
        logger.info(f"Correlation: {correlation}")
        logger.info(f"Beta: {beta}")
        
        # Calculate returns and contributions
        market_return = np.mean(benchmark_returns_array) * 252  # Annualized
        market_contribution = beta * market_return
        
        # Selection (Alpha) Factor
        alpha = (np.mean(weighted_returns_array) - beta * np.mean(benchmark_returns_array)) * 252
        selection_contribution = alpha
        
        # Interaction Factor
        total_return = np.mean(weighted_returns_array) * 252
        interaction_contribution = total_return - (market_contribution + selection_contribution)
        
        # Performance Metrics
        tracking_error = np.std(excess_returns_array) * np.sqrt(252)
        excess_return_mean = np.mean(excess_returns_array)
        std_excess = np.std(excess_returns_array)
        information_ratio = (excess_return_mean / std_excess) * np.sqrt(252) if not np.isclose(std_excess, 0) else 0
        
        # Calculate R-squared using correlation
        r_squared = correlation ** 2
        # Debug: Log final metrics
        logger.info(f"Final metrics calculated:")
        logger.info(f"Market contribution: {market_contribution}")
        logger.info(f"Selection contribution: {selection_contribution}")
        logger.info(f"Information ratio: {information_ratio}")
        
        attribution_results = {
            'factor': {
                'market_contribution': float(market_contribution),
                'selection_contribution': float(selection_contribution),
                'interaction_contribution': float(interaction_contribution),
                'beta': float(beta),
                'alpha': float(alpha),
                'r_squared': float(correlation ** 2)
            },
            'sector': sector_data,
            'performance': {
                'total_return': float(total_return),
                'benchmark_return': float(market_return),
                'excess_return': float(total_return - market_return),
                'tracking_error': float(tracking_error),
                'information_ratio': float(information_ratio)
            }
        }
        
        logger.info("Attribution analysis completed successfully")
        return attribution_results
            
    except Exception as e:
        logger.error(f"Error in calculate_attribution: {str(e)}", exc_info=True)
        return {
            'factor': {
                'market_contribution': 0.0,
                'selection_contribution': 0.0,
                'interaction_contribution': 0.0,
                'beta': 1.0,
                'alpha': 0.0,
                'r_squared': 0.0
            },
            'sector': {},
            'performance': {
                'total_return': 0.0,
                'benchmark_return': 0.0,
                'excess_return': 0.0,
                'tracking_error': 0.0,
                'information_ratio': 0.0
            }
        }

# API Routes
@app.route('/api/compare', methods=['POST'])
def compare_portfolios():
    global analysis_progress
    
    if request.method == 'OPTIONS':
        return handle_preflight()

    db = SessionLocal()
    tokens_remaining = None  # Store token count before closing session
    
    try:
        logger.info("Starting portfolio comparison...")
        analysis_progress['progress'] = 0
        analysis_progress['status'] = 'processing'
        analysis_progress['error'] = None

        data = request.get_json()
        logger.info(f"Received request data: {json.dumps(data, indent=2)}")
        
        user_id = data.get('user_id')
        portfolios = data.get('portfolios', [])
        start_date = data.get('startDate')
        transaction_cost = min(float(data.get('transactionCost', 0.001)), 0.01)

        if not portfolios:
            update_progress(0, 'failed', 'No portfolios provided for comparison')
            return jsonify({
                'success': False,
                'error': 'No portfolios provided for comparison'
            }), 400

        # Check user and tokens if user_id is provided
        if user_id:
            try:
                user = db.query(User).filter(User.id == user_id).first()
                if not user:
                    return jsonify({
                        'success': False,
                        'error': 'User not found'
                    }), 404

                # Check if user has enough tokens
                if user.tokens < ANALYSIS_COST:
                    return jsonify({
                        'success': False,
                        'error': f'Insufficient tokens. Analysis requires {ANALYSIS_COST} tokens.'
                    }), 403

                # Update user stats and store token count
                user.analysis_count += 1
                user.tokens -= ANALYSIS_COST
                tokens_remaining = user.tokens  # Store token count
                db.commit()

            except Exception as e:
                db.rollback()
                logger.error(f"Database error: {str(e)}")
                return jsonify({
                    'success': False,
                    'error': 'Database error occurred'
                }), 500
            finally:
                db.close()  # Close session after user update

        # Initialize results dictionary
        comparison_results = {}
        
        start_date = pd.to_datetime(start_date)
        all_data = pd.DataFrame()
        
        # Collect all unique tickers
        unique_tickers = set()
        for portfolio in portfolios:
            portfolio_stocks = portfolio.get('portfolio', [])
            unique_tickers.update([stock['ticker'] for stock in portfolio_stocks])
        logger.info(f"Fetching data for {len(unique_tickers)} unique tickers...")
        
        update_progress(10)
        # Fetch data for all tickers and track earliest dates
        earliest_available_dates = {}
        for i, ticker in enumerate(unique_tickers, 1):
            stock_data = fetch_stock_data(ticker, start_date)
            if stock_data is not None and not stock_data.empty:
                earliest_available_dates[ticker] = stock_data.index[0]
                all_data[ticker] = stock_data['Adj Close']
            else:
                error_msg = f'Failed to fetch data for {ticker}'
                if ticker == 'SPY':
                    error_msg += ' (benchmark)'
                update_progress(0, 'failed', error_msg)
                return jsonify({
                    'success': False,
                    'error': error_msg
                }), 400
            progress = 10 + (30 * i / len(unique_tickers))
            update_progress(int(progress))

        # Find the limiting date (latest among earliest dates)
        limiting_date = max(earliest_available_dates.values())
        limiting_ticker = max(earliest_available_dates, key=earliest_available_dates.get)
        logger.info(f"Final analysis start date adjusted to {limiting_date} (limited by {limiting_ticker})")

        # Filter all data to start from the limiting date
        all_data = all_data[limiting_date:]
        logger.info(f"Final analysis period: {all_data.index[0]} to {all_data.index[-1]}")

        # Align all data to common dates and handle missing values
        all_data = all_data.dropna()
        if len(all_data) < 252:
            update_progress(0, 'failed', 'Insufficient historical data')
            return jsonify({
                'success': False,
                'error': 'Insufficient historical data. Need at least 252 data points.'
            }), 400

        update_progress(50)

        # Extract benchmark data
        benchmark_data = all_data['SPY']
        benchmark_returns = benchmark_data.pct_change().fillna(0)

        # Calculate metrics for each portfolio
        total_portfolios = len(portfolios)
        for i, portfolio in enumerate(portfolios, 1):
            portfolio_name = portfolio.get('name', 'Unnamed Portfolio')
            portfolio_stocks = portfolio.get('portfolio', [])
            
            # Add logging
            logger.info(f"Processing portfolio: {portfolio_name}")
            
            weights = np.array([float(stock['weight']) / 100 for stock in portfolio_stocks])
            weights = weights / weights.sum()
            
            tickers = [stock['ticker'] for stock in portfolio_stocks]
            portfolio_data = all_data[tickers]
            portfolio_returns = portfolio_data.pct_change().fillna(0)
            weighted_returns = (portfolio_returns * weights).sum(axis=1)
            # Add logging for volume data
            logger.info(f"Portfolio data columns: {portfolio_data.columns}")
            if 'Volume' in portfolio_data.columns:
                logger.info(f"Volume data sample: {portfolio_data['Volume'].head()}")
            else:
                logger.info("No Volume column found in portfolio data")
            
            # Calculate metrics with benchmark
            portfolio_metrics = calculate_portfolio_metrics(
                weighted_returns,  # Updated to pass weighted_returns
                benchmark_returns,
                portfolio_data  # Pass the original data with volume information
            )
            
            # Add logging for volume metrics
            if 'volume_analysis' in portfolio_metrics:
                logger.info(f"Volume metrics calculated: {portfolio_metrics['volume_analysis']}")
            else:
                logger.info("No volume metrics found in portfolio metrics")
            
            # Add historical performance data
            initial_value = 10000
            portfolio_values = initial_value * (1 + weighted_returns).cumprod()
            portfolio_metrics['historical_performance'] = {
                'dates': weighted_returns.index.strftime('%Y-%m-%d').tolist(),
                'values': portfolio_values.tolist()
            }
            
            # Calculate correlation matrices for portfolios with multiple stocks
            correlations = calculate_correlation_matrix(portfolio_data) if len(tickers) > 1 else None
            
            # Calculate rebalancing impact
            rebalancing_results = calculate_rebalancing_impact(
                portfolio_data, 
                weights,
                transaction_cost
            )

            # Calculate attribution
            attribution_results = calculate_attribution(
                portfolio_data,
                weights,
                pd.DataFrame({'Close': benchmark_data})
            )
            
            comparison_results[portfolio_name] = {
                'metrics': portfolio_metrics,
                'rebalancing': rebalancing_results,
                'portfolio': portfolio_stocks,
                'attribution': attribution_results,
                'correlations': correlations
            }

            # Update progress (50-90%)
            progress = 50 + (40 * i / total_portfolios)
            update_progress(int(progress))

        # Final progress update
        update_progress(100, 'completed')

        # Clean the entire response object before returning
        response_data = {
            'success': True,
            'comparison_results': clean_for_json(comparison_results),
            'data_quality': clean_for_json({
                'num_data_points': len(all_data),
                'date_range': {
                    'start': all_data.index[0].strftime('%Y-%m-%d'),
                    'end': all_data.index[-1].strftime('%Y-%m-%d')
                }
            })
        }

        if tokens_remaining is not None:
            response_data['tokens_remaining'] = tokens_remaining

        return jsonify(response_data)

    except Exception as e:
        logger.error(f"Error in compare_portfolios: {str(e)}", exc_info=True)
        analysis_progress['status'] = 'failed'
        analysis_progress['error'] = str(e)
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

@app.route('/api/backtest', methods=['POST'])
def backtest():
    """
    Backtest the portfolio against historical data.
    Expects JSON payload with:
        - portfolio: list of stocks with 'ticker' and 'weight'
        - startDate: backtest start date in 'YYYY-MM-DD'
    """
    try:
        logger.info("Starting backtest...")
        data = request.get_json()
        logger.info(f"Received backtest data: {json.dumps(data, indent=2)}")
        
        portfolio = data.get('portfolio', [])
        start_date = data.get('startDate')
        
        if not portfolio:
            return jsonify({
                'success': False,
                'error': 'No portfolio provided for backtest'
            }), 400

        # Validate portfolio
        is_valid, error_msg = validate_portfolio(portfolio)
        if not is_valid:
            return jsonify({
                'success': False,
                'error': error_msg
            }), 400

        start_date = pd.to_datetime(start_date)
        portfolio_data = pd.DataFrame()

        # Fetch data for portfolio stocks
        logger.info("Fetching portfolio data...")
        for stock in portfolio:
            ticker = stock['ticker']
            stock_data = fetch_stock_data(ticker, start_date)
            if stock_data is not None and not stock_data.empty:
                portfolio_data[ticker] = stock_data['Adj Close']
                logger.info(f"Data fetched for {ticker}, shape: {stock_data.shape}")
            else:
                return jsonify({
                    'success': False,
                    'error': f'No data available for {ticker}'
                }), 400

        # Fetch or define benchmark
        benchmark = 'SPY'  # Default benchmark
        benchmark_data = fetch_stock_data(benchmark, start_date)
        if benchmark_data is None or benchmark_data.empty:
            return jsonify({
                'success': False,
                'error': f'No data available for benchmark {benchmark}'
            }), 400

        # Calculate metrics
        portfolio_returns = portfolio_data.pct_change().dropna()
        benchmark_returns = benchmark_data['Adj Close'].pct_change().dropna().reindex(portfolio_returns.index)
        benchmark_returns = benchmark_returns.fillna(0)

        # Compute weighted returns
        portfolio_weights = np.array([float(stock['weight']) for stock in portfolio])
        portfolio_weights = portfolio_weights / portfolio_weights.sum()
        weighted_returns = (portfolio_returns * portfolio_weights).sum(axis=1)

        portfolio_metrics = calculate_portfolio_metrics(weighted_returns, benchmark_returns)
        benchmark_metrics = calculate_portfolio_metrics(benchmark_returns)

        # Calculate cumulative portfolio and benchmark values
        initial_investment = portfolio_metrics.get('start_balance', 10000.0)
        portfolio_cumulative = (1 + weighted_returns).cumprod() * initial_investment
        benchmark_cumulative = (1 + benchmark_returns).cumprod() * initial_investment

        # Calculate drawdowns
        portfolio_drawdowns = calculate_drawdown_periods(weighted_returns)
        benchmark_drawdowns = calculate_drawdown_periods(benchmark_returns)

        # Prepare response with data quality information
        common_dates = weighted_returns.index
        response_data = {
            'success': True,
            'portfolio_metrics': clean_for_json(portfolio_metrics),
            'benchmark_metrics': clean_for_json(benchmark_metrics),
            'historical_performance': {
                'dates': common_dates.strftime('%Y-%m-%d').tolist(),
                'portfolio_values': portfolio_cumulative.tolist(),
                'benchmark_values': benchmark_cumulative.tolist(),
                'initial_investment': initial_investment
            },
            'drawdowns': {
                'portfolio_drawdowns': portfolio_drawdowns,
                'benchmark_drawdowns': benchmark_drawdowns
            },
            'data_quality': {
                'num_data_points': len(common_dates),
                'date_range': {
                    'start': common_dates[0].strftime('%Y-%m-%d'),
                    'end': common_dates[-1].strftime('%Y-%m-%d')
                },
                'missing_values': False,
                'aligned_data': True
            }
        }

        logger.info("Backtest completed successfully")
        return jsonify(response_data)

    except Exception as e:
        logger.error(f"Error in backtest: {str(e)}", exc_info=True)
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

@app.route('/api/correlation', methods=['POST', 'OPTIONS'])
def get_correlation():
    """Calculate and return correlation matrices for the portfolio."""
    if request.method == 'OPTIONS':
        return handle_preflight()

    try:
        logger.info("Starting correlation analysis...")
        data = request.get_json()
        logger.info(f"Received correlation data: {json.dumps(data, indent=2)}")
        
        portfolio = data.get('portfolio', [])
        start_date = data.get('startDate')
        
        if not portfolio or len(portfolio) < 2:
            return jsonify({
                'success': False,
                'error': 'Portfolio must contain at least two stocks for correlation analysis'
            }), 400

        # Validate portfolio
        is_valid, error_msg = validate_portfolio(portfolio)
        if not is_valid:
            return jsonify({
                'success': False,
                'error': f"Invalid portfolio: {error_msg}"
            }), 400

        # Fetch data for portfolio stocks
        portfolio_tickers = [stock['ticker'] for stock in portfolio]
        portfolio_data = pd.DataFrame()
        adjusted_start_date = pd.to_datetime(start_date)

        logger.info("Fetching portfolio data for correlation analysis...")
        for ticker in portfolio_tickers:
            stock_data = fetch_stock_data(ticker, adjusted_start_date)
            if stock_data is not None and not stock_data.empty:
                portfolio_data[ticker] = stock_data['Adj Close']
                logger.info(f"Data fetched for {ticker}, shape: {stock_data.shape}")
            else:
                return jsonify({
                    'success': False,
                    'error': f'No data available for {ticker}'
                }), 400

        # Calculate correlation matrices
        correlations = calculate_correlation_matrix(portfolio_data)

        return jsonify({
            'success': True,
            'correlations': correlations,
            'date_range': {
                'start': portfolio_data.index[0].strftime('%Y-%m-%d'),
                'end': portfolio_data.index[-1].strftime('%Y-%m-%d')
            }
        })

    except Exception as e:
        logger.error(f"Error in get_correlation: {str(e)}", exc_info=True)
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

@app.route('/api/attribution', methods=['POST', 'OPTIONS'])
def get_attribution():
    """Get portfolio attribution analysis."""
    if request.method == 'OPTIONS':
        response = app.make_default_options_response()
        response.headers['Access-Control-Allow-Methods'] = 'POST'
        response.headers['Access-Control-Allow-Headers'] = 'Content-Type'
        return response

    try:
        data = request.get_json()
        portfolio = data.get('portfolio', [])
        start_date = data.get('startDate')
        benchmark = data.get('benchmark', 'SPY')
        
        if not portfolio:
            return jsonify({
                'success': False,
                'error': 'No portfolio provided'
            }), 400

        # Validate portfolio
        is_valid, error_msg = validate_portfolio(portfolio)
        if not is_valid:
            return jsonify({
                'success': False,
                'error': f"Invalid portfolio: {error_msg}"
            }), 400

        start_date = pd.to_datetime(start_date)
        portfolio_tickers = [stock['ticker'] for stock in portfolio]
        portfolio_weights = np.array([float(stock['weight']) for stock in portfolio])

        # Fetch data for portfolio stocks
        logger.info("Fetching portfolio data for attribution analysis...")
        portfolio_data = pd.DataFrame()
        for ticker in portfolio_tickers:
            stock_data = fetch_stock_data(ticker, start_date)
            if stock_data is not None and not stock_data.empty:
                portfolio_data[ticker] = stock_data['Adj Close']
                logger.info(f"Data fetched for {ticker}, shape: {stock_data.shape}")
            else:
                return jsonify({
                    'success': False,
                    'error': f'No data available for {ticker}'
                }), 400

        # Fetch benchmark data
        logger.info(f"Fetching benchmark data for {benchmark}")
        benchmark_data = fetch_stock_data(benchmark, start_date)
        if benchmark_data is None or benchmark_data.empty:
            return jsonify({
                'success': False,
                'error': f'No data available for benchmark {benchmark}'
            }), 400

        # Calculate attribution
        attribution_results = calculate_attribution(portfolio_data, portfolio_weights, benchmark_data)
        if attribution_results is None:
            return jsonify({
                'success': False,
                'error': 'Failed to calculate attribution'
            }), 500

        return jsonify({
            'success': True,
            'attribution': attribution_results
        })

    except Exception as e:
        logger.error(f"Error in get_attribution: {str(e)}", exc_info=True)
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

def validate_portfolio(portfolio):
    """Validate portfolio structure and weights."""
    try:
        if not portfolio or not isinstance(portfolio, list):
            return False, "Invalid portfolio format"

        # Validate each position
        for position in portfolio:
            if not isinstance(position, dict):
                return False, "Invalid position format"
            if 'ticker' not in position or 'weight' not in position:
                return False, "Missing ticker or weight"
            
            # Convert weight to float if it's a string
            if isinstance(position['weight'], str):
                position['weight'] = float(position['weight'])

        # Calculate total weight
        total_weight = sum(float(position['weight']) for position in portfolio)
        
        # Check if weights are in percentage format (0-100) or decimal format (0-1)
        is_percentage = total_weight > 1.1  # Assume percentage if sum > 1.1
        
        if is_percentage:
            # Convert to decimal if in percentage format
            for position in portfolio:
                position['weight'] = float(position['weight']) / 100
        
        # Validate total weight is close to 1.0 after normalization
        total_weight = sum(float(position['weight']) for position in portfolio)
        if not np.isclose(total_weight, 1.0, rtol=1e-3):
            # Normalize weights to sum to 1.0
            for position in portfolio:
                position['weight'] = float(position['weight']) / total_weight

        return True, ""
        
    except Exception as e:
        return False, f"Validation error: {str(e)}"

# Portfolio management routes
@app.route('/api/portfolio/save', methods=['POST'])
def save_portfolio():
    """Save a portfolio to database"""
    try:
        data = request.get_json()
        if not data:
            return jsonify({
                'success': False,
                'error': 'No data provided'
            }), 400

        name = data.get('name')
        portfolio_data = data.get('portfolio', [])
        user_id = data.get('user_id')

        if not name or not portfolio_data or not user_id:
            return jsonify({
                'success': False,
                'error': 'Missing required fields'
            }), 400

        db = SessionLocal()
        try:
            # Create new portfolio with explicit timestamp
            current_time = func.now()
            new_portfolio = Portfolio(
                user_id=user_id,
                name=name,
                data=json.dumps(portfolio_data),
                created_at=current_time
            )
            db.add(new_portfolio)
            db.commit()
            db.refresh(new_portfolio)

            # Ensure we have a valid timestamp
            created_at = new_portfolio.created_at or datetime.now()

            return jsonify({
                'success': True,
                'message': 'Portfolio saved successfully',
                'portfolio': {
                    'id': new_portfolio.id,
                    'name': new_portfolio.name,
                    'portfolio': json.loads(new_portfolio.data),
                    'created_at': created_at.isoformat()
                }
            })
        except Exception as e:
            db.rollback()
            raise e
        finally:
            db.close()

    except Exception as e:
        logger.error(f"Error in save_portfolio: {str(e)}")
        return jsonify({
            'success': False,
            'error': str(e)
        }), 500

@app.route('/api/portfolio/load', methods=['GET'])
def load_portfolios():
    """Load all portfolios for a user"""
    try:
        user_id = request.args.get('user_id')
        if not user_id:
            return jsonify({
                'success': False,
                'error': 'User ID is required'
            }), 400

        db = SessionLocal()
        try:
            # Check if user exists
            user = db.query(User).filter(User.id == user_id).first()
            if not user:
                return jsonify({
                    'success': False,
                    'error': 'User not found'
                }), 404

            # Query portfolios
            portfolios = db.query(Portfolio).filter(
                Portfolio.user_id == user_id
            ).order_by(Portfolio.created_at.desc()).all()

            # Always return success with empty list if no portfolios
            return jsonify({
                'success': True,
                'portfolios': [{
                    'id': p.id,
                    'name': p.name,
                    'portfolio': json.loads(p.data),
                    'created_at': p.created_at.isoformat() if p.created_at else None
                } for p in portfolios] if portfolios else []
            })

        except Exception as db_error:
            logger.error(f"Database error in load_portfolios: {str(db_error)}")
            return jsonify({
                'success': False,
                'error': 'An error occurred while loading portfolios'
            }), 500
        finally:
            db.close()

    except Exception as e:
        logger.error(f"Error in load_portfolios: {str(e)}")
        return jsonify({
            'success': False,
            'error': 'An unexpected error occurred'
        }), 500

@app.route('/api/portfolio/delete/<int:portfolio_id>', methods=['DELETE'])
def delete_portfolio(portfolio_id):
    """Delete a portfolio"""
    try:
        user_id = request.args.get('user_id')
        if not user_id:
            return jsonify({
                'success': False,
                'error': 'User ID is required'
            }), 400

        db = SessionLocal()
        try:
            # Find and delete the portfolio
            portfolio = db.query(Portfolio).filter(
                Portfolio.id == portfolio_id,
                Portfolio.user_id == user_id
            ).first()

            if not portfolio:
                return jsonify({
                    'success': False,
                    'error': 'Portfolio not found'
                }), 404

            db.delete(portfolio)
            db.commit()

            # Return updated portfolio list
            portfolios = db.query(Portfolio).filter(
                Portfolio.user_id == user_id
            ).order_by(Portfolio.created_at.desc()).all()

            return jsonify({
                'success': True,
                'message': 'Portfolio deleted successfully',
                'portfolios': [{
                    'id': p.id,
                    'name': p.name,
                    'portfolio': json.loads(p.data),
                    'created_at': p.created_at.isoformat() if p.created_at else None
                } for p in portfolios]
            })
        except Exception as e:
            db.rollback()
            logger.error(f"Database error in delete_portfolio: {str(e)}")
            return jsonify({
                'success': False,
                'error': 'An error occurred while deleting the portfolio'
            }), 500
        finally:
            db.close()

    except Exception as e:
        logger.error(f"Error in delete_portfolio: {str(e)}")
        return jsonify({
            'success': False,
            'error': 'An unexpected error occurred'
        }), 500

@app.after_request
def after_request(response):
    """Add CORS headers to all responses"""
    response.headers.add('Access-Control-Allow-Origin', 'http://localhost:5173')
    response.headers.add('Access-Control-Allow-Headers', 'Content-Type,Cache-Control,Authorization')
    response.headers.add('Access-Control-Allow-Methods', 'GET,PUT,POST,DELETE,OPTIONS')
    return response

@app.route('/api/health', methods=['HEAD', 'GET'])
def health_check():
    """Simple health check endpoint"""
    return '', 200

# Add this global variable to track progress
analysis_progress = {
    'progress': 0,
    'status': 'idle',  # 'idle', 'processing', 'completed', 'failed'
    'error': None
}

# Add this function to update progress
def update_progress(value, status='processing', error=None):
    global analysis_progress
    analysis_progress['progress'] = value
    analysis_progress['status'] = status
    analysis_progress['error'] = error

# Add this new endpoint
@app.route('/api/progress', methods=['GET'])
def get_progress():
    """Get the current analysis progress."""
    global analysis_progress
    return jsonify(analysis_progress)

@app.route('/api/register', methods=['POST'])
def register():
    data = request.get_json()
    username = data.get('username')
    email = data.get('email')
    password = data.get('password')

    if not username or not email or not password:
        return jsonify({'success': False, 'error': 'All fields are required'}), 400

    hashed_password = generate_password_hash(password)

    db = SessionLocal()
    try:
        new_user = User(
            username=username,
            email=email,
            password_hash=hashed_password,
            created_at=func.now()
        )
        db.add(new_user)
        db.commit()
        
        # Refresh to get the created_at timestamp
        db.refresh(new_user)
        
        created_at_str = new_user.created_at.isoformat() if new_user.created_at else datetime.now().isoformat()
        
        user_data = {
            'id': new_user.id,
            'username': new_user.username,
            'email': new_user.email,
            'created_at': created_at_str
        }
        
        return jsonify({
            'success': True,
            'message': 'User registered successfully',
            'user': user_data
        }), 201
    except IntegrityError as e:
        db.rollback()
        error_message = str(e.orig)
        if 'users_username_key' in error_message:
            return jsonify({
                'success': False,
                'error': f'Username "{username}" is already taken. Please choose another username.'
            }), 409
        elif 'users_email_key' in error_message:
            return jsonify({
                'success': False,
                'error': f'Email "{email}" is already registered. Please use a different email address.'
            }), 409
        else:
            logger.error(f"Registration error: {str(e)}")
            return jsonify({
                'success': False,
                'error': 'An error occurred during registration. Please try again.'
            }), 500
    except Exception as e:
        db.rollback()
        logger.error(f"Registration error: {str(e)}")
        return jsonify({
            'success': False,
            'error': 'An error occurred during registration. Please try again.'
        }), 500
    finally:
        db.close()

@app.route('/api/login', methods=['POST'])
def login():
    data = request.get_json()
    login_id = data.get('login')  # This will be either email or username
    password = data.get('password')

    if not login_id or not password:
        return jsonify({'success': False, 'error': 'Login ID and password are required'}), 400

    db = SessionLocal()
    try:
        # Query user by either email or username
        user = db.query(User).filter(
            (User.email == login_id) | (User.username == login_id)
        ).first()
        
        if user and check_password_hash(user.password_hash, password):
            # Create response with user data (excluding password_hash)
            user_data = {
                'id': user.id,
                'username': user.username,
                'email': user.email,
                'created_at': user.created_at.isoformat()
            }
            return jsonify({
                'success': True,
                'message': 'Login successful',
                'user': user_data
            }), 200
        else:
            return jsonify({
                'success': False,
                'error': 'Invalid credentials'
            }), 401
    except Exception as e:
        return jsonify({'success': False, 'error': str(e)}), 500
    finally:
        db.close()

@app.route('/api/user/stats', methods=['GET'])
def get_user_stats():
    db = SessionLocal()
    try:
        # Get user ID from query parameter
        user_id = request.args.get('user_id')
        if not user_id:
            return jsonify({
                'success': False,
                'error': 'User ID is required'
            }), 400

        # Get user
        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return jsonify({
                'success': False,
                'error': 'User not found'
            }), 404

        try:
            # Get portfolio count
            portfolio_count = db.query(Portfolio).filter(Portfolio.user_id == user.id).count()
        except Exception as e:
            logger.error(f"Error counting portfolios: {str(e)}")
            portfolio_count = 0

        stats = {
            'portfolioCount': portfolio_count,
            'tokens': user.tokens,  # Use the actual token value from the database
            'analysisCount': user.analysis_count
        }

        return jsonify({
            'success': True,
            'stats': stats
        })
        
    except Exception as e:
        logger.error(f"Error getting user stats: {str(e)}")
        return jsonify({
            'success': False,
            'error': 'Failed to retrieve user stats'
        }), 500
    finally:
        db.close()

@app.route('/api/portfolio/edit/<int:portfolio_id>', methods=['PUT'])
def edit_portfolio(portfolio_id):
    """Edit a portfolio"""
    try:
        data = request.get_json()
        user_id = data.get('user_id')
        name = data.get('name')
        portfolio_data = data.get('portfolio', [])

        if not user_id or not name or not portfolio_data:
            return jsonify({
                'success': False,
                'error': 'Missing required fields'
            }), 400

        db = SessionLocal()
        try:
            # Find the portfolio
            portfolio = db.query(Portfolio).filter(
                Portfolio.id == portfolio_id,
                Portfolio.user_id == user_id
            ).first()

            if not portfolio:
                return jsonify({
                    'success': False,
                    'error': 'Portfolio not found'
                }), 404

            # Update portfolio
            portfolio.name = name
            portfolio.data = json.dumps(portfolio_data)
            db.commit()
            db.refresh(portfolio)

            return jsonify({
                'success': True,
                'message': 'Portfolio updated successfully',
                'portfolio': {
                    'id': portfolio.id,
                    'name': portfolio.name,
                    'portfolio': json.loads(portfolio.data),
                    'created_at': portfolio.created_at.isoformat() if portfolio.created_at else None
                }
            })
        except Exception as e:
            db.rollback()
            logger.error(f"Database error in edit_portfolio: {str(e)}")
            return jsonify({
                'success': False,
                'error': 'An error occurred while updating the portfolio'
            }), 500
        finally:
            db.close()

    except Exception as e:
        logger.error(f"Error in edit_portfolio: {str(e)}")
        return jsonify({
            'success': False,
            'error': 'An unexpected error occurred'
        }), 500

if __name__ == '__main__':
    app.run(debug=True)


ERROR:__main__:Error in compare_portfolios: 'SPY'
Traceback (most recent call last):
  File "C:\Users\calvi\Downloads\top\venv\lib\site-packages\pandas\core\indexes\base.py", line 3802, in get_loc
    return self._engine.get_loc(casted_key)
  File "pandas\_libs\index.pyx", line 138, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\index.pyx", line 165, in pandas._libs.index.IndexEngine.get_loc
  File "pandas\_libs\hashtable_class_helper.pxi", line 5745, in pandas._libs.hashtable.PyObjectHashTable.get_item
  File "pandas\_libs\hashtable_class_helper.pxi", line 5753, in pandas._libs.hashtable.PyObjectHashTable.get_item
KeyError: 'SPY'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\calvi\Downloads\top\backend\app.py", line 1402, in compare_portfolios
    benchmark_data = all_data['SPY']
  File "C:\Users\calvi\Downloads\top\venv\lib\site-packages\pandas\core\frame.py", line 3807, in __getitem__
    indexer = self.columns.get_loc(key)
  File "C:\Users\calvi\Downloads\top\venv\lib\site-packages\pandas\core\indexes\base.py", line 3804, in get_loc
    raise KeyError(key) from err
KeyError: 'SPY'
INFO:werkzeug:127.0.0.1 - - [14/Nov/2024 00:49:28] "[35m[1mPOST /api/compare HTTP/1.1[0m" 500

fix it and better dont use hard code spy
return me full fixed code of that part