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)
        
        # NEW: Get benchmark from request data, default to 'SPY' if not provided
        benchmark = data.get('benchmark', 'SPY').strip().upper()
        logger.info(f"Using benchmark: {benchmark}")

        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 from all portfolios
        unique_tickers = set()
        for portfolio in portfolios:
            portfolio_stocks = portfolio.get('portfolio', [])
            unique_tickers.update([stock['ticker'].strip().upper() for stock in portfolio_stocks])
        
        # NEW: Add benchmark to unique_tickers to ensure it's fetched
        unique_tickers.add(benchmark)

        logger.info(f"Fetching data for {len(unique_tickers)} unique tickers (including benchmark).")

        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 == benchmark:
                    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 = [k for k, v in earliest_available_dates.items() if v == limiting_date][0]
        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.loc[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
        if benchmark not in all_data.columns:
            error_msg = f'Benchmark data for {benchmark} is not available.'
            update_progress(0, 'failed', error_msg)
            return jsonify({
                'success': False,
                'error': error_msg
            }), 400

        benchmark_data = all_data[benchmark]
        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', f'Unnamed Portfolio {i}')
            portfolio_stocks = portfolio.get('portfolio', [])

            logger.info(f"Processing portfolio: {portfolio_name}")

            # Extract weights and tickers
            weights = np.array([float(stock['weight']) / 100 for stock in portfolio_stocks])
            weights = weights / weights.sum()  # Normalize weights

            tickers = [stock['ticker'].strip().upper() for stock in portfolio_stocks]
            portfolio_data = all_data[tickers]
            portfolio_returns = portfolio_data.pct_change().fillna(0)

            logger.info(f"Portfolio data columns: {portfolio_data.columns}")

            # Calculate weighted portfolio returns
            weighted_returns = (portfolio_returns * weights).sum(axis=1)

            # Calculate metrics with benchmark
            portfolio_metrics = calculate_portfolio_metrics(
                weighted_returns,
                benchmark_returns,
                portfolio_data  # Pass the original data with volume information
            )

            logger.info(f"Volume metrics calculated: {portfolio_metrics.get('volume_analysis', 'No volume metrics found')}")

            # 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({benchmark: 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)

import { Box, Paper, Typography, Table, TableBody, TableCell, TableContainer, TableHead, TableRow, Grid, Tabs, Tab } from '@mui/material'
import { formatMetricValue } from '../../utils/metricsUtils'
import { useState } from 'react'

function CorrelationMatrix({ results, comparisonPortfolios }) {
  const [selectedPortfolio, setSelectedPortfolio] = useState(0)
  const comparison = results?.comparison || {}
  
  // Get all portfolios' data
  const portfolios = [
    {
      name: 'Current Portfolio',
      data: comparison['Current Portfolio'],
      correlations: results?.correlations
    },
    ...(comparisonPortfolios || []).map(p => ({
      name: p.name,
      data: comparison[p.name],
      correlations: p.correlations
    }))
  ].filter(p => p.correlations)

  const currentPortfolio = portfolios[selectedPortfolio]
  if (!currentPortfolio) return null

  // Get stock tickers from the current portfolio
  const stockTickers = currentPortfolio.data?.portfolio?.map(stock => stock.ticker) || []
  const correlations = currentPortfolio.correlations || {}

  // Format correlation value with color
  const formatCorrelation = (value) => {
    if (value === null || value === undefined) return { value: 'N/A', color: 'inherit', bgcolor: 'transparent' }
    
    const formattedValue = value.toFixed(2)
    let color = 'inherit'
    let bgcolor = 'transparent'
    
    // Color scale based on correlation strength
    if (value > 0.7) {
      color = '#fff'
      bgcolor = '#ef5350'  // Strong positive - red
    } else if (value > 0.3) {
      color = '#fff'
      bgcolor = '#ff9800'  // Medium positive - orange
    } else if (value < -0.7) {
      color = '#fff'
      bgcolor = '#4caf50'  // Strong negative - green
    } else if (value < -0.3) {
      color = '#fff'
      bgcolor = '#81c784'  // Medium negative - light green
    }
    
    return { value: formattedValue, color, bgcolor }
  }

  // Prepare correlation statistics
  const getCorrelationStats = (correlationType) => {
    const matrix = correlations[correlationType]?.matrix
    if (!matrix) return null

    const values = []
    Object.values(matrix).forEach(row => {
      Object.values(row).forEach(value => {
        if (value !== 1) values.push(value)  // Exclude self-correlations
      })
    })

    return {
      min: Math.min(...values),
      max: Math.max(...values),
      avg: values.reduce((a, b) => a + b, 0) / values.length
    }
  }

  const pearsonStats = getCorrelationStats('pearson')
  const spearmanStats = getCorrelationStats('spearman')

  return (
    <Box sx={{ display: 'flex', flexDirection: 'column', gap: 4 }}>
      {/* Portfolio selector */}
      {portfolios.length > 1 && (
        <Paper sx={{ mb: 2 }}>
          <Tabs
            value={selectedPortfolio}
            onChange={(e, newValue) => setSelectedPortfolio(newValue)}
            variant="scrollable"
            scrollButtons="auto"
          >
            {portfolios.map((p, index) => (
              <Tab 
                key={p.name}
                label={p.name}
                value={index}
              />
            ))}
          </Tabs>
        </Paper>
      )}

      {/* Correlation Statistics */}
      <Paper elevation={1} sx={{ p: 2 }}>
        <Typography variant="h6" gutterBottom>
          Correlation Statistics
        </Typography>
        <Grid container spacing={3}>
          <Grid item xs={12} md={6}>
            <Typography variant="subtitle1" gutterBottom>
              Pearson Correlation
            </Typography>
            <TableContainer>
              <Table size="small">
                <TableBody>
                  <TableRow>
                    <TableCell>Minimum</TableCell>
                    <TableCell align="right">{formatMetricValue(pearsonStats?.min, 'decimal')}</TableCell>
                  </TableRow>
                  <TableRow>
                    <TableCell>Maximum</TableCell>
                    <TableCell align="right">{formatMetricValue(pearsonStats?.max, 'decimal')}</TableCell>
                  </TableRow>
                  <TableRow>
                    <TableCell>Average</TableCell>
                    <TableCell align="right">{formatMetricValue(pearsonStats?.avg, 'decimal')}</TableCell>
                  </TableRow>
                </TableBody>
              </Table>
            </TableContainer>
          </Grid>
          <Grid item xs={12} md={6}>
            <Typography variant="subtitle1" gutterBottom>
              Spearman Correlation
            </Typography>
            <TableContainer>
              <Table size="small">
                <TableBody>
                  <TableRow>
                    <TableCell>Minimum</TableCell>
                    <TableCell align="right">{formatMetricValue(spearmanStats?.min, 'decimal')}</TableCell>
                  </TableRow>
                  <TableRow>
                    <TableCell>Maximum</TableCell>
                    <TableCell align="right">{formatMetricValue(spearmanStats?.max, 'decimal')}</TableCell>
                  </TableRow>
                  <TableRow>
                    <TableCell>Average</TableCell>
                    <TableCell align="right">{formatMetricValue(spearmanStats?.avg, 'decimal')}</TableCell>
                  </TableRow>
                </TableBody>
              </Table>
            </TableContainer>
          </Grid>
        </Grid>
      </Paper>

      {/* Correlation Legend */}
      <Paper elevation={1} sx={{ p: 2 }}>
        <Typography variant="subtitle2" gutterBottom>
          Correlation Strength Legend
        </Typography>
        <Grid container spacing={2}>
          <Grid item>
            <Box sx={{ display: 'flex', alignItems: 'center', gap: 1 }}>
              <Box sx={{ width: 20, height: 20, bgcolor: '#ef5350' }} />
              <Typography variant="body2">Strong Positive ({`>`} 0.7)</Typography>
            </Box>
          </Grid>
          <Grid item>
            <Box sx={{ display: 'flex', alignItems: 'center', gap: 1 }}>
              <Box sx={{ width: 20, height: 20, bgcolor: '#ff9800' }} />
              <Typography variant="body2">Medium Positive (0.3 to 0.7)</Typography>
            </Box>
          </Grid>
          <Grid item>
            <Box sx={{ display: 'flex', alignItems: 'center', gap: 1 }}>
              <Box sx={{ width: 20, height: 20, bgcolor: '#81c784' }} />
              <Typography variant="body2">Medium Negative (-0.7 to -0.3)</Typography>
            </Box>
          </Grid>
          <Grid item>
            <Box sx={{ display: 'flex', alignItems: 'center', gap: 1 }}>
              <Box sx={{ width: 20, height: 20, bgcolor: '#4caf50' }} />
              <Typography variant="body2">Strong Negative ({`<`} -0.7)</Typography>
            </Box>
          </Grid>
        </Grid>
      </Paper>

      {/* Pearson Correlation Matrix */}
      <Paper elevation={1} sx={{ p: 2 }}>
        <Typography variant="h6" gutterBottom>
          Pearson Correlation Matrix
        </Typography>
        <TableContainer>
          <Table size="small">
            <TableHead>
              <TableRow>
                <TableCell />
                {stockTickers.map(ticker => (
                  <TableCell key={ticker} align="right">{ticker}</TableCell>
                ))}
              </TableRow>
            </TableHead>
            <TableBody>
              {stockTickers.map(row => (
                <TableRow key={row}>
                  <TableCell component="th" scope="row">{row}</TableCell>
                  {stockTickers.map(col => {
                    const correlation = correlations.pearson?.matrix?.[row]?.[col]
                    const { value, color, bgcolor } = formatCorrelation(correlation)
                    return (
                      <TableCell 
                        key={col} 
                        align="right"
                        sx={{ 
                          color,
                          bgcolor,
                          fontWeight: row === col ? 'bold' : 'normal'
                        }}
                      >
                        {value}
                      </TableCell>
                    )
                  })}
                </TableRow>
              ))}
            </TableBody>
          </Table>
        </TableContainer>
      </Paper>

      {/* Spearman Correlation Matrix */}
      <Paper elevation={1} sx={{ p: 2 }}>
        <Typography variant="h6" gutterBottom>
          Spearman Correlation Matrix
        </Typography>
        <TableContainer>
          <Table size="small">
            <TableHead>
              <TableRow>
                <TableCell />
                {stockTickers.map(ticker => (
                  <TableCell key={ticker} align="right">{ticker}</TableCell>
                ))}
              </TableRow>
            </TableHead>
            <TableBody>
              {stockTickers.map(row => (
                <TableRow key={row}>
                  <TableCell component="th" scope="row">{row}</TableCell>
                  {stockTickers.map(col => {
                    const correlation = correlations.spearman?.matrix?.[row]?.[col]
                    const { value, color, bgcolor } = formatCorrelation(correlation)
                    return (
                      <TableCell 
                        key={col} 
                        align="right"
                        sx={{ 
                          color,
                          bgcolor,
                          fontWeight: row === col ? 'bold' : 'normal'
                        }}
                      >
                        {value}
                      </TableCell>
                    )
                  })}
                </TableRow>
              ))}
            </TableBody>
          </Table>
        </TableContainer>
      </Paper>
    </Box>
  )
}

export default CorrelationMatrix

import { Box, Paper, Typography, Grid, Fade, Stack, Tooltip, IconButton, Tabs, Tab } from '@mui/material'
import { formatPercent } from '../../utils'
import HeatMap from './HeatMap'
import InfoOutlinedIcon from '@mui/icons-material/InfoOutlined'
import TimelineIcon from '@mui/icons-material/Timeline'
import TrendingUpIcon from '@mui/icons-material/TrendingUp'
import TrendingDownIcon from '@mui/icons-material/TrendingDown'
import CompareArrowsIcon from '@mui/icons-material/CompareArrows'
import { useState } from 'react'

const CORRELATION_DESCRIPTIONS = {
  'Pearson': 'Measures the linear relationship between assets. Best for detecting linear relationships.',
  'Spearman': 'Measures the monotonic relationship between assets. More robust to outliers and non-linear relationships.',
  'Average': 'The mean correlation across all asset pairs, indicating overall portfolio diversification.',
  'Minimum': 'The lowest correlation between any pair of assets, showing the best diversification opportunity.',
  'Maximum': 'The highest correlation between any pair of assets, showing the strongest relationship.'
}

function CorrelationAnalysis({ correlations, comparisonPortfolios = [], comparison }) {
  const [selectedPortfolio, setSelectedPortfolio] = useState(0)
  
  console.log('Correlations:', correlations)
  console.log('Comparison Portfolios:', comparisonPortfolios)
  console.log('Comparison:', comparison)

  if (!correlations) return null

  const portfolioCorrelations = [
    { 
      name: Object.keys(comparison)[0],
      data: correlations 
    },
    ...(comparisonPortfolios || []).map(p => {
      console.log('Processing portfolio:', p.name, 'correlations:', p.correlations)
      return {
        name: p.name,
        data: p.correlations || null
      }
    })
  ].filter(p => p.data)

  console.log('Portfolio Correlations:', portfolioCorrelations)
  
  const handlePortfolioChange = (event, newValue) => {
    setSelectedPortfolio(newValue)
  }

  const currentCorrelations = portfolioCorrelations[selectedPortfolio]?.data
  if (!currentCorrelations) return null

  const { pearson, spearman } = currentCorrelations

  const renderCorrelationStats = (data, type) => (
    <Box sx={{ mb: { xs: 2, sm: 3 } }}>
      <Box sx={{ 
        display: 'flex', 
        alignItems: 'center', 
        gap: 1, 
        mb: { xs: 1.5, sm: 2 }
      }}>
        <CompareArrowsIcon color="primary" />
        <Typography variant="subtitle1" fontWeight="500" sx={{ fontSize: { xs: '1rem', sm: '1.1rem' } }}>
          Summary Statistics
        </Typography>
        <Tooltip title={CORRELATION_DESCRIPTIONS[type]} arrow>
          <IconButton size="small">
            <InfoOutlinedIcon fontSize="small" />
          </IconButton>
        </Tooltip>
      </Box>

      <Grid container spacing={{ xs: 1.5, sm: 2 }}>
        <Grid item xs={12} sm={4}>
          <Paper 
            variant="outlined" 
            sx={{ 
              p: { xs: 1.5, sm: 2 },
              bgcolor: 'background.default',
              transition: 'transform 0.2s ease',
              '&:hover': { transform: 'translateY(-2px)' }
            }}
          >
            <Stack spacing={1}>
              <Box sx={{ display: 'flex', alignItems: 'center', gap: 1 }}>
                <TimelineIcon color="action" fontSize="small" />
                <Typography variant="caption" color="text.secondary">
                  Average Correlation
                </Typography>
              </Box>
              <Typography variant="h6" color={data.avg < 0 ? 'error.main' : 'success.main'}>
                {formatPercent(data.avg, 1)}
              </Typography>
            </Stack>
          </Paper>
        </Grid>

        <Grid item xs={12} sm={4}>
          <Paper 
            variant="outlined" 
            sx={{ 
              p: { xs: 1.5, sm: 2 },
              bgcolor: 'background.default',
              transition: 'transform 0.2s ease',
              '&:hover': { transform: 'translateY(-2px)' }
            }}
          >
            <Stack spacing={1}>
              <Box sx={{ display: 'flex', alignItems: 'center', gap: 1 }}>
                <TrendingDownIcon color="error" fontSize="small" />
                <Typography variant="caption" color="text.secondary">
                  Minimum Correlation
                </Typography>
              </Box>
              <Typography variant="h6" color="error.main">
                {formatPercent(data.min, 1)}
              </Typography>
            </Stack>
          </Paper>
        </Grid>

        <Grid item xs={12} sm={4}>
          <Paper 
            variant="outlined" 
            sx={{ 
              p: { xs: 1.5, sm: 2 },
              bgcolor: 'background.default',
              transition: 'transform 0.2s ease',
              '&:hover': { transform: 'translateY(-2px)' }
            }}
          >
            <Stack spacing={1}>
              <Box sx={{ display: 'flex', alignItems: 'center', gap: 1 }}>
                <TrendingUpIcon color="success" fontSize="small" />
                <Typography variant="caption" color="text.secondary">
                  Maximum Correlation
                </Typography>
              </Box>
              <Typography variant="h6" color="success.main">
                {formatPercent(data.max, 1)}
              </Typography>
            </Stack>
          </Paper>
        </Grid>
      </Grid>
    </Box>
  )

  return (
    <Box>
      <Box sx={{ mb: { xs: 3, sm: 4 } }}>
        <Box sx={{ 
          display: 'flex', 
          alignItems: 'center', 
          gap: 1, 
          mb: { xs: 1.5, sm: 2 }
        }}>
          <CompareArrowsIcon color="primary" />
          <Typography variant="h5" sx={{ fontSize: { xs: '1.25rem', sm: '1.5rem' } }}>
            Correlation Analysis
          </Typography>
          <Tooltip title="Analysis of asset relationships and diversification" arrow>
            <IconButton size="small">
              <InfoOutlinedIcon fontSize="small" />
            </IconButton>
          </Tooltip>
        </Box>
        <Typography variant="body2" color="text.secondary" sx={{ fontSize: { xs: '0.875rem', sm: '1rem' } }}>
          Analyze how different assets in your portfolio move in relation to each other
        </Typography>
      </Box>

      {portfolioCorrelations.length > 1 && (
        <Paper sx={{ mb: 3 }}>
          <Tabs
            value={selectedPortfolio}
            onChange={handlePortfolioChange}
            variant="scrollable"
            scrollButtons="auto"
            sx={{ px: 2 }}
          >
            {portfolioCorrelations.map((p, index) => (
              <Tab 
                key={p.name}
                label={p.name}
                value={index}
                sx={{
                  textTransform: 'none',
                  minHeight: 48,
                  fontSize: '0.9rem'
                }}
              />
            ))}
          </Tabs>
        </Paper>
      )}

      <Grid container spacing={{ xs: 2, sm: 3 }}>
        {/* Pearson Correlation */}
        <Grid item xs={12} lg={6}>
          <Fade in={true} timeout={500}>
            <Paper sx={{ p: { xs: 2, sm: 3 } }}>
              <Box sx={{ 
                display: 'flex', 
                alignItems: 'center', 
                gap: 1, 
                mb: { xs: 2, sm: 3 }
              }}>
                <TimelineIcon color="primary" />
                <Typography variant="h6" sx={{ fontSize: { xs: '1.1rem', sm: '1.25rem' } }}>
                  {portfolioCorrelations[selectedPortfolio].name} - Pearson Correlation
                </Typography>
                <Tooltip title={CORRELATION_DESCRIPTIONS['Pearson']} arrow>
                  <IconButton size="small">
                    <InfoOutlinedIcon fontSize="small" />
                  </IconButton>
                </Tooltip>
              </Box>
              
              {renderCorrelationStats(pearson, 'Pearson')}
              
              <Box sx={{ mt: { xs: 2, sm: 3 } }}>
                <Typography variant="subtitle2" gutterBottom>
                  Correlation Matrix
                </Typography>
                <Box sx={{ 
                  overflowX: 'auto',
                  '& > div': { minWidth: { xs: 280, sm: 'auto' } }
                }}>
                  <HeatMap 
                    data={pearson.matrix} 
                    formatValue={formatPercent}
                  />
                </Box>
              </Box>
            </Paper>
          </Fade>
        </Grid>

        {/* Spearman Correlation */}
        <Grid item xs={12} lg={6}>
          <Fade in={true} timeout={800}>
            <Paper sx={{ p: 3 }}>
              <Box sx={{ display: 'flex', alignItems: 'center', gap: 1, mb: 3 }}>
                <TimelineIcon color="primary" />
                <Typography variant="h6">
                  {portfolioCorrelations[selectedPortfolio].name} - Spearman Correlation
                </Typography>
                <Tooltip title={CORRELATION_DESCRIPTIONS['Spearman']} arrow>
                  <IconButton size="small">
                    <InfoOutlinedIcon fontSize="small" />
                  </IconButton>
                </Tooltip>
              </Box>
              
              {renderCorrelationStats(spearman, 'Spearman')}
              
              <Box sx={{ mt: 3 }}>
                <Typography variant="subtitle2" gutterBottom>
                  Correlation Matrix
                </Typography>
                <HeatMap 
                  data={spearman.matrix} 
                  formatValue={formatPercent}
                />
              </Box>
            </Paper>
          </Fade>
        </Grid>
      </Grid>

      <Fade in={true} timeout={1000}>
        <Paper sx={{ p: { xs: 2, sm: 3 }, mt: { xs: 2, sm: 3 } }}>
          <Box sx={{ display: 'flex', alignItems: 'center', gap: 1, mb: 3 }}>
            <InfoOutlinedIcon color="primary" />
            <Typography variant="h6">
              Interpretation Guide
            </Typography>
          </Box>
          <Grid container spacing={2}>
            <Grid item xs={12} sm={4}>
              <Paper 
                variant="outlined" 
                sx={{ 
                  p: 2,
                  bgcolor: 'success.light',
                  color: 'success.contrastText',
                  transition: 'transform 0.2s ease',
                  '&:hover': { transform: 'translateY(-2px)' }
                }}
              >
                <Typography variant="subtitle2" gutterBottom>
                  Strong Positive (&gt; 0.7)
                </Typography>
                <Typography variant="body2">
                  Assets tend to move in the same direction, offering less diversification benefit.
                </Typography>
              </Paper>
            </Grid>
            <Grid item xs={12} sm={4}>
              <Paper 
                variant="outlined" 
                sx={{ 
                  p: 2,
                  bgcolor: 'info.light',
                  color: 'info.contrastText',
                  transition: 'transform 0.2s ease',
                  '&:hover': { transform: 'translateY(-2px)' }
                }}
              >
                <Typography variant="subtitle2" gutterBottom>
                  Low Correlation (-0.3 to 0.3)
                </Typography>
                <Typography variant="body2">
                  Assets move independently, providing good diversification benefits.
                </Typography>
              </Paper>
            </Grid>
            <Grid item xs={12} sm={4}>
              <Paper 
                variant="outlined" 
                sx={{ 
                  p: 2,
                  bgcolor: 'error.light',
                  color: 'error.contrastText',
                  transition: 'transform 0.2s ease',
                  '&:hover': { transform: 'translateY(-2px)' }
                }}
              >
                <Typography variant="subtitle2" gutterBottom>
                  Strong Negative (&lt; -0.7)
                </Typography>
                <Typography variant="body2">
                  Assets tend to move in opposite directions, offering strong diversification.
                </Typography>
              </Paper>
            </Grid>
          </Grid>
        </Paper>
      </Fade>
    </Box>
  )
}

export default CorrelationAnalysis

when in Comparison Portfolios, although there is two tab displaying their own correlation, but the two tab are just showing the same data/metrics(only the first portfolio, the second tab should show its portfolio instead of the first one), check the data is pass correctly to the frontend yet or not? or is backend problem
fix it for me, and give me the full code of that part