#app.py
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
from backend.routes.user_routes import user_bp
from backend.database.config import get_db, SessionLocal
from sqlalchemy import text, func
from sqlalchemy.orm import Session
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
from openai import OpenAI

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

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

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

ANALYSIS_COST = 9  # Cost in tokens per analysis

# Utility Functions
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 the database first, then YFinance if needed.
    Prioritizes database data and only uses YFinance to update recent data or fetch all data if not present.
    
    Parameters:
        ticker (str): Stock ticker symbol.
        start_date (str or datetime-like): The start date for fetching historical data.
        
    Returns:
        pd.DataFrame or None: DataFrame containing historical stock data or None if failed.
    """
    try:
        # Standardize inputs
        start_date = pd.to_datetime(start_date)
        ticker = ticker.strip().upper()
        today = pd.Timestamp.now().normalize()
        
        logger.info(f"Fetching data for ticker: {ticker} starting from {start_date.date()}")

        # Initialize database session
        db: Session = SessionLocal()
        try:
            # Retrieve sector information
            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:
                gics_sector, gics_sub_industry = sector_info
                logger.debug(f"Sector info for {ticker}: {gics_sector}, {gics_sub_industry}")
            else:
                gics_sector, gics_sub_industry = (None, None)
                logger.warning(f"No sector information found for {ticker}")

            # Retrieve the earliest and latest dates available in the database for the ticker
            date_range_query = text("""
                SELECT MIN(date) AS min_date, MAX(date) AS max_date
                FROM stock_data
                WHERE ticker = :ticker
            """)
            date_range = db.execute(date_range_query, {"ticker": ticker}).first()
            min_date_db, max_date_db = date_range.min_date, date_range.max_date

            if min_date_db and max_date_db:
                logger.info(f"Database has data for {ticker} from {min_date_db.date()} to {max_date_db.date()}")
                
                # Adjust start_date if it's earlier than the earliest date in the database
                adjusted_start_date = max(start_date, pd.to_datetime(min_date_db))
                if adjusted_start_date > start_date:
                    logger.warning(
                        f"Requested start_date {start_date.date()} is earlier than available data. "
                        f"Adjusting start_date to {adjusted_start_date.date()}."
                    )
                else:
                    adjusted_start_date = start_date

                # Fetch data from the 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": adjusted_start_date})
                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']  # Assuming Adjusted Close is same as Close
                    
                    if sector_info:
                        db_data['gics_sector'] = gics_sector
                        db_data['gics_sub_industry'] = gics_sub_industry
                    
                    logger.info(f"Fetched {len(db_data)} records from the database for {ticker}")
                    
                    # Determine if recent data needs to be updated
                    latest_db_date = db_data.index.max()
                    if latest_db_date < today - pd.Timedelta(days=1):
                        # Fetch recent data from YFinance
                        fetch_start = latest_db_date + pd.Timedelta(days=1)
                        logger.info(f"Fetching recent data from YFinance for {ticker} starting from {fetch_start.date()} to {today.date()}")
                        
                        new_data = yf.download(
                            ticker,
                            start=fetch_start,
                            end=today + pd.Timedelta(days=1),  # Inclusive of today
                            progress=False,
                            auto_adjust=False,
                            timeout=30
                        )
                        
                        if not new_data.empty:
                            new_data['Adj Close'] = new_data['Close']  # Assuming Adjusted Close is same as Close
                            if sector_info:
                                new_data['gics_sector'] = gics_sector
                                new_data['gics_sub_industry'] = gics_sub_industry
                            
                            # Save new data to the database
                            save_stock_data_to_db(ticker, new_data)
                            logger.info(f"Fetched and saved {len(new_data)} new records from YFinance for {ticker}")
                            
                            # Append new data to the existing DataFrame
                            db_data = pd.concat([db_data, new_data])
                        else:
                            logger.warning(f"No new data fetched from YFinance for {ticker} since {fetch_start.date()}")
                    
                    return db_data

            # If no data exists in the database, fetch all data from YFinance
            logger.info(f"No data found in the database for {ticker}. Fetching all historical data from YFinance.")
            stock_data = yf.download(
                ticker,
                start=start_date,
                end=today + pd.Timedelta(days=1),  # Inclusive of today
                progress=False,
                auto_adjust=False,
                timeout=30
            )
            
            if not stock_data.empty:
                stock_data['Adj Close'] = stock_data['Close']  # Assuming Adjusted Close is same as Close
                if sector_info:
                    stock_data['gics_sector'] = gics_sector
                    stock_data['gics_sub_industry'] = gics_sub_industry
                
                # Save fetched data to the database
                save_stock_data_to_db(ticker, stock_data)
                logger.info(f"Fetched and saved {len(stock_data)} records from YFinance for {ticker}")
                
                return stock_data
            else:
                logger.warning(f"No data available from YFinance 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 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()
        
        # Debug: Print raw returns for first few rows
        logger.info(f"Sample returns first 5 rows:\n{returns.head()}")
        
        # Calculate Pearson correlation
        pearson_matrix = returns.corr(method='pearson')
        logger.info(f"Full Pearson correlation matrix:\n{pearson_matrix}")
        
        # Get correlation values (excluding diagonal)
        pearson_values = []
        spearman_values = []
        
        # Calculate Spearman correlation
        spearman_matrix = returns.corr(method='spearman')
        
        n = len(pearson_matrix.index)
        for i in range(n):
            for j in range(n):
                if i != j:  # Exclude diagonal (self-correlations)
                    pearson_values.append(pearson_matrix.iloc[i, j])
                    spearman_values.append(spearman_matrix.iloc[i, j])
        
        # Debug: Print correlation statistics
        logger.info(f"Number of correlation pairs: {len(pearson_values)}")
        logger.info(f"Correlation value distribution:")
        logger.info(f"Pearson - Negative correlations: {sum(1 for x in pearson_values if x < 0)}")
        logger.info(f"Pearson - Positive correlations: {sum(1 for x in pearson_values if x > 0)}")
        logger.info(f"Spearman - Negative correlations: {sum(1 for x in spearman_values if x < 0)}")
        logger.info(f"Spearman - Positive correlations: {sum(1 for x in spearman_values if x > 0)}")
        
        # 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)
        rolling_corr = returns.rolling(window=rolling_window).corr()
        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

    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]  # Get data for this portfolio's tickers
            
            # Calculate correlations for this specific portfolio
            correlations = None
            if len(tickers) > 1:  # Only calculate correlations for portfolios with multiple stocks
                correlations = calculate_correlation_matrix(portfolio_data)
                logger.info(f"Calculated correlations for {portfolio_name} with {len(tickers)} stocks")
                if correlations:
                    logger.info(f"Correlation stats for {portfolio_name}:")
                    logger.info(f"Pearson avg: {correlations['pearson']['avg']}")
                    logger.info(f"Spearman avg: {correlations['spearman']['avg']}")

            portfolio_returns = portfolio_data.pct_change().fillna(0)

            # 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
            )

            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 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."""
    # Handle OPTIONS request
    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:
        logger.info("Starting correlation analysis...")
        # Force content type to application/json for POST requests
        if not request.is_json:
            return jsonify({
                'success': False,
                'error': 'Content-Type must be application/json'
            }), 415
            
        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.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

@app.route('/api/user/preferences', methods=['GET'])
def get_user_preferences():
    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:
            user = db.query(User).filter(User.id == user_id).first()
            if not user:
                return jsonify({
                    'success': False,
                    'error': 'User not found'
                }), 404

            return jsonify({
                'success': True,
                'preferences': {
                    'dark_mode': user.dark_mode
                }
            })
        finally:
            db.close()

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

@app.route('/api/user/preferences', methods=['PUT'])
def update_user_preferences():
    try:
        data = request.get_json()
        logger.info(f"Received preference update request: {data}")
        
        user_id = data.get('user_id')
        dark_mode = data.get('dark_mode')

        if user_id is None or dark_mode is None:
            logger.error(f"Missing required fields. user_id: {user_id}, dark_mode: {dark_mode}")
            return jsonify({
                'success': False,
                'error': 'Missing required fields'
            }), 400

        db = SessionLocal()
        try:
            user = db.query(User).filter(User.id == user_id).first()
            if not user:
                logger.error(f"User not found with ID: {user_id}")
                return jsonify({
                    'success': False,
                    'error': 'User not found'
                }), 404

            # Log the before state
            logger.info(f"Current dark_mode value for user {user_id}: {user.dark_mode}")
            
            # Update the preference
            user.dark_mode = dark_mode
            db.commit()
            db.refresh(user)
            
            # Log the after state
            logger.info(f"Updated dark_mode value for user {user_id}: {user.dark_mode}")

            return jsonify({
                'success': True,
                'message': 'Preferences updated successfully',
                'preferences': {
                    'dark_mode': user.dark_mode,
                    'previous_value': not user.dark_mode
                }
            })

        except Exception as e:
            db.rollback()
            logger.error(f"Database error in update_user_preferences: {str(e)}", exc_info=True)
            return jsonify({
                'success': False,
                'error': f'Database error: {str(e)}'
            }), 500
        finally:
            db.close()

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

@app.route('/api/user/preferences/debug/<int:user_id>', methods=['GET'])
def debug_user_preferences(user_id):
    db = SessionLocal()
    try:
        user = db.query(User).filter(User.id == user_id).first()
        if not user:
            return jsonify({
                'success': False,
                'error': 'User not found'
            }), 404

        return jsonify({
            'success': True,
            'user_id': user.id,
            'dark_mode': user.dark_mode,
            'dark_mode_type': str(type(user.dark_mode))
        })
    finally:
        db.close()

# Add your configuration section
API_CONFIG = {
    'XAI_API_KEY': '<TRUFFLEHOG_REDACTED_XAI>',
    'XAI_BASE_URL': 'https://api.x.ai/v1'
}

# Initialize OpenAI client with configuration
client = OpenAI(
    api_key=API_CONFIG['XAI_API_KEY'],
    base_url=API_CONFIG['XAI_BASE_URL']
)

@app.route('/api/analyze', methods=['POST'])
def analyze_data():
    try:
        data = request.json
        analysis_data = data.get('analysisData', {})
        
        analyses = {}
        
        # Process each tab's analysis sequentially
        for tab_name, tab_data in analysis_data.items():
            try:
                # Extract only relevant metrics based on tab
                filtered_data = {}
                if tab_name == 'Overview':
                    filtered_data = {
                        'total_return': tab_data.get('metrics', {}).get('total_return'),
                        'cagr': tab_data.get('metrics', {}).get('cagr'),
                        'sharpe_ratio': tab_data.get('metrics', {}).get('sharpe_ratio'),
                        'max_drawdown': tab_data.get('metrics', {}).get('max_drawdown'),
                        'annual_volatility': tab_data.get('metrics', {}).get('annual_volatility')
                    }
                # ... other tab filters remain the same ...

                # Create prompt based on tab type
                prompts = {
                    'Overview': "Analyze these key portfolio metrics and provide insights about overall performance:",
                    'Correlation Analysis': "Analyze these correlation matrices and provide insights about portfolio diversification:",
                    'Volume Analysis': "Analyze these volume metrics and provide insights about trading patterns:",
                    'Rebalancing Analysis': "Analyze these rebalancing results and suggest optimal rebalancing strategy:",
                    'Drawdown Analysis': "Analyze these drawdown metrics and provide risk management insights:",
                    'Risk Analysis': "Analyze these risk metrics and provide comprehensive risk assessment:",
                    'Attribution Analysis': "Analyze these attribution metrics and explain performance drivers:"
                }

                completion = client.chat.completions.create(
                    model="grok-beta",
                    messages=[
                        {
                            "role": "system",
                            "content": "You are a financial analysis expert. Provide clear, concise insights in a structured format."
                        },
                        {
                            "role": "user",
                            "content": f"{prompts.get(tab_name, 'Analyze this data:')} {json.dumps(filtered_data, indent=2)}"
                        }
                    ],
                    temperature=0.3,
                    max_tokens=1000
                )

                analyses[tab_name] = completion.choices[0].message.content

            except Exception as e:
                logger.error(f"Error analyzing {tab_name}: {str(e)}")
                analyses[tab_name] = f"Analysis failed for {tab_name}"

        return jsonify({
            "success": True,
            "analyses": analyses
        })

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

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

add log in the app.py to see its returing correctly from the llm reponse, also I feels like @app.route('/api/analyze', methods=['POST'])
def analyze_data():
and 
# API Routes
@app.route('/api/compare', methods=['POST'])
and
@app.route('/api/backtest', methods=['POST'])
def backtest():
is very redundant in the code? can you help enhance it and return full updated code to me, make sure dont interrupt with the current features