#!/usr/bin/env python3
"""
US Stock Market Analysis Server (SEC EDGAR)
Flask backend for US stock analysis with Claude AI integration
Port: 5051
"""

import os
import sys
import json
import subprocess
import time
import re
import sqlite3
from datetime import datetime, timedelta
from typing import Dict, Any, Optional
from functools import wraps

from flask import Flask, request, jsonify, send_from_directory
from flask_cors import CORS

from scraper_sec import (
    get_all_tickers, get_all_metrics, get_company_submissions,
    ticker_to_cik, save_tickers_to_db, update_company_info, SP500_TOP
)
from us_data_enrichment import (
    enrich_stock, build_claude_prompt, calculate_ratios,
    save_metrics_to_db, save_analysis_to_db
)
from us_error_logger import (
    log_error, log_warning, log_info, log_claude_error,
    get_recent_errors, get_error_stats
)

# Initialize Flask app
app = Flask(__name__, static_folder='us')
CORS(app)

# Configuration
DB_PATH = '/home/mcmarketshost/public_html/us_stock_analysis.db'
VERSION = '1.0.0'
PORT = 5051


def get_db_conn():
    """Get database connection"""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def _safe_float(value):
    """Safely convert to float"""
    if value is None:
        return None
    try:
        f = float(value)
        if f != f:  # NaN check
            return None
        return f
    except (ValueError, TypeError):
        return None


# ============================================================================
# Claude CLI Integration
# ============================================================================

def call_claude_cli(prompt, timeout=120, max_retries=3, ticker=None):
    """Call Claude CLI for analysis"""
    system_prompt = "You are a financial analyst. Analyze the provided SEC financial data and respond with valid JSON only. No markdown, no explanations."
    cmd = ["claude", "-p", "--model", "haiku", "--system-prompt", system_prompt, prompt]

    # Clean environment
    env = os.environ.copy()
    keys_to_remove = [k for k in env.keys()
                      if 'CLAUDE' in k.upper() or 'MCP' in k.upper() or 'ANTHROPIC' in k.upper()]
    for k in keys_to_remove:
        env.pop(k, None)

    for attempt in range(max_retries):
        try:
            result = subprocess.run(
                cmd,
                stdout=subprocess.PIPE,
                stderr=subprocess.PIPE,
                universal_newlines=True,
                timeout=timeout,
                cwd=os.path.expanduser("~"),
                env=env
            )

            response_text = result.stdout.strip()

            if result.returncode != 0:
                error_msg = result.stderr.strip() or 'Unknown error'
                if 'rate' in error_msg.lower() or '429' in error_msg:
                    wait_time = (2 ** attempt) * 5
                    log_warning('claude', 'Rate limited, waiting {}s'.format(wait_time), ticker=ticker)
                    time.sleep(wait_time)
                    continue
                log_claude_error(ticker, error_msg)
                return {'status': 'error', 'error': error_msg}

            # Parse JSON response
            json_data = parse_claude_response(response_text)
            if json_data:
                # Validate required fields
                required = ['rating', 'confidence', 'thesis']
                missing = [f for f in required if f not in json_data]
                if missing:
                    return {'status': 'error', 'error': 'Missing fields: {}'.format(missing)}

                # Validate rating
                valid_ratings = ['STRONG_BUY', 'BUY', 'HOLD', 'SELL', 'STRONG_SELL']
                if json_data['rating'] not in valid_ratings:
                    json_data['rating'] = 'HOLD'

                json_data['confidence'] = int(json_data.get('confidence', 50))
                json_data['status'] = 'ok'
                return json_data

            return {'status': 'error', 'error': 'Could not parse JSON'}

        except subprocess.TimeoutExpired:
            if attempt < max_retries - 1:
                continue
            log_claude_error(ticker, 'Timeout after {}s'.format(timeout))
            return {'status': 'error', 'error': 'Timeout'}

        except Exception as e:
            log_claude_error(ticker, str(e))
            return {'status': 'error', 'error': str(e)}

    return {'status': 'error', 'error': 'Max retries exceeded'}


def parse_claude_response(response_text):
    """Parse JSON from Claude response"""
    # Try code blocks first
    json_block_pattern = r'```(?:json)?\s*(\{[\s\S]*?\})\s*```'
    matches = re.findall(json_block_pattern, response_text)
    for match in matches:
        try:
            return json.loads(match)
        except json.JSONDecodeError:
            continue

    # Try raw JSON
    json_pattern = r'\{[^{}]*"rating"[^{}]*\}'
    matches = re.findall(json_pattern, response_text, re.DOTALL)
    for match in matches:
        try:
            return json.loads(match)
        except json.JSONDecodeError:
            continue

    # Try full response
    try:
        return json.loads(response_text)
    except json.JSONDecodeError:
        pass

    return None


# ============================================================================
# API Endpoints
# ============================================================================

@app.route('/api/health', methods=['GET'])
def health_check():
    """Health check endpoint"""
    try:
        conn = get_db_conn()
        cursor = conn.cursor()
        cursor.execute('SELECT COUNT(*) FROM us_stock_master')
        stock_count = cursor.fetchone()[0]
        cursor.execute('SELECT COUNT(*) FROM us_stock_analyses')
        analysis_count = cursor.fetchone()[0]
        conn.close()

        return jsonify({
            'status': 'ok',
            'version': VERSION,
            'database': 'connected',
            'stocks': stock_count,
            'analyses': analysis_count,
            'timestamp': datetime.now().isoformat()
        })
    except Exception as e:
        return jsonify({
            'status': 'error',
            'error': str(e)
        }), 500


@app.route('/api/stats', methods=['GET'])
def get_stats():
    """Get database statistics"""
    try:
        conn = get_db_conn()
        cursor = conn.cursor()

        # Total stocks
        cursor.execute('SELECT COUNT(*) FROM us_stock_master')
        total_stocks = cursor.fetchone()[0]

        # Analyzed stocks
        cursor.execute('SELECT COUNT(DISTINCT ticker) FROM us_stock_analyses')
        analyzed_stocks = cursor.fetchone()[0]

        # Rating breakdown
        cursor.execute('''
            SELECT rating, COUNT(*) as count
            FROM (
                SELECT ticker, rating,
                       ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY timestamp DESC) as rn
                FROM us_stock_analyses
            ) WHERE rn = 1
            GROUP BY rating
        ''')
        ratings = {row['rating']: row['count'] for row in cursor.fetchall()}

        # Recent analyses (last 24h)
        yesterday = (datetime.now() - timedelta(hours=24)).isoformat()
        cursor.execute('SELECT COUNT(*) FROM us_stock_analyses WHERE timestamp > ?', (yesterday,))
        recent_analyses = cursor.fetchone()[0]

        # Queue status
        cursor.execute("SELECT COUNT(*) FROM us_processing_queue WHERE status = 'pending'")
        queue_pending = cursor.fetchone()[0]

        conn.close()

        return jsonify({
            'status': 'ok',
            'total_stocks': total_stocks,
            'analyzed_stocks': analyzed_stocks,
            'ratings': ratings,
            'recent_analyses_24h': recent_analyses,
            'queue_pending': queue_pending,
            'timestamp': datetime.now().isoformat()
        })
    except Exception as e:
        log_error('server', str(e), exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/stocks', methods=['GET'])
def get_stocks():
    """Get list of stocks with filters"""
    try:
        page = int(request.args.get('page', 1))
        per_page = int(request.args.get('per_page', 50))
        exchange = request.args.get('exchange')
        rating = request.args.get('rating')
        search = request.args.get('search', '').upper()

        offset = (page - 1) * per_page

        conn = get_db_conn()
        cursor = conn.cursor()

        # Build query
        query = '''
            SELECT m.ticker, m.company_name, m.exchange, m.sic_description,
                   a.rating, a.confidence, a.thesis, a.timestamp as analysis_date,
                   c.gross_margin, c.operating_margin, c.net_margin, c.roe, c.roa,
                   c.current_ratio, c.debt_to_equity, c.revenue_growth, c.eps_growth,
                   cs.gpt_rating, cs.gpt_confidence, cs.combined_rating, cs.combined_confidence, cs.agreement_level
            FROM us_stock_master m
            LEFT JOIN (
                SELECT ticker, rating, confidence, thesis, timestamp,
                       ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY timestamp DESC) as rn
                FROM us_stock_analyses
            ) a ON m.ticker = a.ticker AND a.rn = 1
            LEFT JOIN us_calculated_metrics c ON m.ticker = c.ticker
            LEFT JOIN us_combined_scores cs ON m.ticker = cs.ticker
            WHERE m.is_active = 1
        '''
        params = []

        if search:
            query += ' AND (m.ticker LIKE ? OR m.company_name LIKE ?)'
            params.extend(['%{}%'.format(search), '%{}%'.format(search)])

        if exchange:
            query += ' AND m.exchange = ?'
            params.append(exchange)

        if rating:
            query += ' AND a.rating = ?'
            params.append(rating)

        # Count total - use same query structure with JOINs for accurate count
        count_query = '''
            SELECT COUNT(*) FROM (
                SELECT m.ticker
                FROM us_stock_master m
                LEFT JOIN (
                    SELECT ticker, rating,
                           ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY timestamp DESC) as rn
                    FROM us_stock_analyses
                ) a ON m.ticker = a.ticker AND a.rn = 1
                WHERE m.is_active = 1
        '''
        count_params = []
        if search:
            count_query += ' AND (m.ticker LIKE ? OR m.company_name LIKE ?)'
            count_params.extend(['%{}%'.format(search), '%{}%'.format(search)])
        if exchange:
            count_query += ' AND m.exchange = ?'
            count_params.append(exchange)
        if rating:
            count_query += ' AND a.rating = ?'
            count_params.append(rating)
        count_query += ')'
        cursor.execute(count_query, count_params)
        total = cursor.fetchone()[0]

        # Add ordering and pagination
        query += ' ORDER BY m.priority ASC, m.ticker ASC LIMIT ? OFFSET ?'
        params.extend([per_page, offset])

        cursor.execute(query, params)
        rows = cursor.fetchall()
        conn.close()

        stocks = []
        for row in rows:
            stocks.append({
                'ticker': row['ticker'],
                'company_name': row['company_name'],
                'exchange': row['exchange'],
                'sector': row['sic_description'],
                'rating': row['rating'],
                'confidence': row['confidence'],
                'thesis': row['thesis'],
                'analysis_date': row['analysis_date'],
                'gpt_rating': row['gpt_rating'],
                'gpt_confidence': row['gpt_confidence'],
                'combined_rating': row['combined_rating'],
                'combined_confidence': row['combined_confidence'],
                'agreement_level': row['agreement_level'],
                'metrics': {
                    'gross_margin': row['gross_margin'],
                    'operating_margin': row['operating_margin'],
                    'net_margin': row['net_margin'],
                    'roe': row['roe'],
                    'roa': row['roa'],
                    'current_ratio': row['current_ratio'],
                    'debt_to_equity': row['debt_to_equity'],
                    'revenue_growth': row['revenue_growth'],
                    'eps_growth': row['eps_growth'],
                }
            })

        return jsonify({
            'status': 'ok',
            'stocks': stocks,
            'total': total,
            'page': page,
            'per_page': per_page,
            'total_pages': (total + per_page - 1) // per_page
        })

    except Exception as e:
        log_error('server', str(e), exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/stock/<ticker>', methods=['GET'])
def get_stock(ticker):
    """Get single stock details"""
    try:
        ticker = ticker.upper()
        conn = get_db_conn()
        cursor = conn.cursor()

        # Get stock master info
        cursor.execute('SELECT * FROM us_stock_master WHERE ticker = ?', (ticker,))
        stock = cursor.fetchone()

        if not stock:
            return jsonify({'status': 'not_found', 'error': 'Stock not found'}), 404

        # Get latest analysis
        cursor.execute('''
            SELECT * FROM us_stock_analyses
            WHERE ticker = ?
            ORDER BY timestamp DESC LIMIT 1
        ''', (ticker,))
        analysis = cursor.fetchone()

        # Get calculated metrics
        cursor.execute('''
            SELECT * FROM us_calculated_metrics
            WHERE ticker = ?
            ORDER BY created_at DESC LIMIT 1
        ''', (ticker,))
        metrics = cursor.fetchone()

        # Get recent filings
        cursor.execute('''
            SELECT * FROM us_filing_tracker
            WHERE ticker = ?
            ORDER BY filing_date DESC LIMIT 10
        ''', (ticker,))
        filings = cursor.fetchall()

        conn.close()

        result = {
            'status': 'ok',
            'stock': dict(stock),
            'analysis': dict(analysis) if analysis else None,
            'metrics': dict(metrics) if metrics else None,
            'recent_filings': [dict(f) for f in filings]
        }

        # Parse JSON fields in analysis
        if result['analysis']:
            for field in ['strengths', 'risks', 'key_metrics_to_watch', 'metrics_snapshot']:
                if result['analysis'].get(field):
                    try:
                        result['analysis'][field] = json.loads(result['analysis'][field])
                    except:
                        pass

        return jsonify(result)

    except Exception as e:
        log_error('server', str(e), ticker=ticker, exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/analysis/<ticker>', methods=['GET'])
def get_analysis(ticker):
    """Get latest analysis for a stock"""
    try:
        ticker = ticker.upper()
        conn = get_db_conn()
        cursor = conn.cursor()

        cursor.execute('''
            SELECT * FROM us_stock_analyses
            WHERE ticker = ?
            ORDER BY timestamp DESC LIMIT 1
        ''', (ticker,))
        analysis = cursor.fetchone()
        conn.close()

        if not analysis:
            return jsonify({'status': 'not_found', 'error': 'No analysis found'}), 404

        result = dict(analysis)

        # Parse JSON fields
        for field in ['strengths', 'risks', 'key_metrics_to_watch', 'metrics_snapshot']:
            if result.get(field):
                try:
                    result[field] = json.loads(result[field])
                except:
                    pass

        result['status'] = 'ok'
        return jsonify(result)

    except Exception as e:
        log_error('server', str(e), ticker=ticker, exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/refresh-universe', methods=['POST'])
def refresh_universe():
    """Refresh stock universe from SEC"""
    try:
        log_info('server', 'Starting universe refresh')

        # Get all tickers from SEC
        result = get_all_tickers()
        if result['status'] != 'ok':
            return jsonify(result), 500

        tickers = result['tickers']

        # Save to database
        count = save_tickers_to_db(tickers)

        # Initialize processing queue for new stocks
        conn = get_db_conn()
        cursor = conn.cursor()

        # R4: skip preferred shares (e.g. CMS-PB, GAB-PG) at queue insertion time.
        # Preferred shares share the issuer's CIK and financials with the common
        # stock - any analysis would be a mislabeled duplicate of the common's.
        # R1 (investment company) and R2 (no 10-K/10-Q) are checked at enrich time
        # because they need the SEC submissions data which we don't have yet here.
        preferred_re = re.compile(r'-P[A-Z]?$')
        skipped_preferred = 0

        # Add S&P 500 stocks with priority 1
        for ticker in SP500_TOP:
            if ticker in tickers:
                if preferred_re.search(ticker):
                    skipped_preferred += 1
                    continue
                cursor.execute('''
                    INSERT OR IGNORE INTO us_processing_queue (ticker, cik, priority, status)
                    VALUES (?, ?, 1, 'pending')
                ''', (ticker, tickers[ticker]['cik']))

        # Add other stocks with priority 100
        for ticker, info in tickers.items():
            if ticker not in SP500_TOP:
                if preferred_re.search(ticker):
                    skipped_preferred += 1
                    continue
                cursor.execute('''
                    INSERT OR IGNORE INTO us_processing_queue (ticker, cik, priority, status)
                    VALUES (?, ?, 100, 'pending')
                ''', (ticker, info['cik']))

        conn.commit()
        log_info('server', 'refresh_universe: skipped {} preferred shares (R4)'.format(skipped_preferred))

        cursor.execute("SELECT COUNT(*) FROM us_processing_queue WHERE status = 'pending'")
        queue_count = cursor.fetchone()[0]

        conn.close()

        log_info('server', 'Universe refresh complete: {} stocks, {} in queue'.format(count, queue_count))

        return jsonify({
            'status': 'ok',
            'stocks_updated': count,
            'queue_pending': queue_count
        })

    except Exception as e:
        log_error('server', str(e), exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/run-analysis/<ticker>', methods=['POST'])
def run_analysis(ticker):
    """Run analysis for a single stock"""
    try:
        ticker = ticker.upper()
        start_time = time.time()

        log_info('server', 'Starting analysis for {}'.format(ticker), ticker=ticker)

        # Enrich stock data
        enriched = enrich_stock(ticker)
        if enriched['status'] != 'ok':
            return jsonify(enriched), 400

        # Save metrics
        save_metrics_to_db(ticker, enriched['ratios'], enriched.get('growth', {}))

        # Build prompt
        prompt = build_claude_prompt(enriched)
        if not prompt:
            return jsonify({'status': 'error', 'error': 'Failed to build prompt'}), 400

        # Call Claude
        analysis = call_claude_cli(prompt, ticker=ticker)
        if analysis['status'] != 'ok':
            return jsonify(analysis), 400

        # Calculate processing time
        processing_time_ms = int((time.time() - start_time) * 1000)

        # Save analysis
        save_analysis_to_db(
            ticker,
            enriched['cik'],
            analysis,
            enriched['ratios'],
            processing_time_ms
        )

        # Update queue status
        conn = get_db_conn()
        cursor = conn.cursor()
        cursor.execute('''
            UPDATE us_processing_queue
            SET status = 'completed', last_attempt = ?, attempt_count = attempt_count + 1
            WHERE ticker = ?
        ''', (datetime.now().isoformat(), ticker))
        conn.commit()
        conn.close()

        return jsonify({
            'status': 'ok',
            'ticker': ticker,
            'rating': analysis.get('rating'),
            'confidence': analysis.get('confidence'),
            'thesis': analysis.get('thesis'),
            'processing_time_ms': processing_time_ms
        })

    except Exception as e:
        log_error('server', str(e), ticker=ticker, exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/errors', methods=['GET'])
def get_errors():
    """Get recent errors"""
    try:
        limit = int(request.args.get('limit', 100))
        hours = int(request.args.get('hours', 24))

        errors = get_recent_errors(limit=limit, hours=hours)
        stats = get_error_stats(hours=hours)

        return jsonify({
            'status': 'ok',
            'errors': errors,
            'stats': stats
        })

    except Exception as e:
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/queue', methods=['GET'])
def get_queue():
    """Get processing queue status"""
    try:
        conn = get_db_conn()
        cursor = conn.cursor()

        # Get queue summary
        cursor.execute('''
            SELECT status, COUNT(*) as count
            FROM us_processing_queue
            GROUP BY status
        ''')
        summary = {row['status']: row['count'] for row in cursor.fetchall()}

        # Get next items in queue
        cursor.execute('''
            SELECT ticker, cik, priority, status, last_attempt, attempt_count
            FROM us_processing_queue
            WHERE status = 'pending'
            ORDER BY priority ASC, ticker ASC
            LIMIT 20
        ''')
        pending = [dict(row) for row in cursor.fetchall()]

        # Get recently processed
        cursor.execute('''
            SELECT ticker, cik, status, last_attempt
            FROM us_processing_queue
            WHERE status IN ('completed', 'failed')
            ORDER BY last_attempt DESC
            LIMIT 10
        ''')
        recent = [dict(row) for row in cursor.fetchall()]

        conn.close()

        return jsonify({
            'status': 'ok',
            'summary': summary,
            'pending': pending,
            'recent': recent
        })

    except Exception as e:
        return jsonify({'status': 'error', 'error': str(e)}), 500


# ============================================================================
# Stock Page API Endpoints (for marketshost.com/ai-score/us/)
# ============================================================================

@app.route('/api/stock/<ticker>/full', methods=['GET'])
def get_stock_full(ticker):
    """Get complete stock data for stock page display"""
    try:
        ticker = ticker.upper()
        conn = get_db_conn()
        cursor = conn.cursor()

        # Get stock master info with all company details
        cursor.execute('SELECT * FROM us_stock_master WHERE ticker = ?', (ticker,))
        stock = cursor.fetchone()

        if not stock:
            return jsonify({'status': 'not_found', 'error': 'Stock not found'}), 404

        stock_data = dict(stock)

        # Get latest analysis
        cursor.execute('''
            SELECT * FROM us_stock_analyses
            WHERE ticker = ?
            ORDER BY timestamp DESC LIMIT 1
        ''', (ticker,))
        analysis = cursor.fetchone()

        # Get calculated metrics
        cursor.execute('''
            SELECT * FROM us_calculated_metrics
            WHERE ticker = ?
            ORDER BY created_at DESC LIMIT 1
        ''', (ticker,))
        metrics = cursor.fetchone()

        # Get analysis history (for rating trend)
        cursor.execute('''
            SELECT id, timestamp, rating, confidence, data_freshness
            FROM us_stock_analyses
            WHERE ticker = ?
            ORDER BY timestamp DESC LIMIT 10
        ''', (ticker,))
        history = cursor.fetchall()

        # Get recent filings
        cursor.execute('''
            SELECT form_type, filing_date, accession_number, primary_document
            FROM us_filing_tracker
            WHERE ticker = ?
            ORDER BY filing_date DESC LIMIT 10
        ''', (ticker,))
        filings = cursor.fetchall()

        # Get GPT analysis
        cursor.execute('''
            SELECT * FROM us_gpt_analyses
            WHERE ticker = ?
            ORDER BY timestamp DESC LIMIT 1
        ''', (ticker,))
        gpt_analysis = cursor.fetchone()

        # Get combined scores
        cursor.execute('''
            SELECT * FROM us_combined_scores
            WHERE ticker = ?
            LIMIT 1
        ''', (ticker,))
        combined_scores = cursor.fetchone()

        # Get next upcoming earnings (if any). Wrapped in try so a missing
        # us_earnings_calendar table doesn't break the whole endpoint.
        next_earnings = None
        try:
            import pytz
            today_iso = datetime.now(pytz.timezone('America/New_York')).date().isoformat()
            cursor.execute('''
                SELECT earnings_date, earnings_time, eps_estimate, eps_prior,
                       num_estimates, fiscal_quarter
                FROM us_earnings_calendar
                WHERE ticker = ? AND earnings_date >= ?
                ORDER BY earnings_date ASC
                LIMIT 1
            ''', (ticker, today_iso))
            row = cursor.fetchone()
            if row:
                next_earnings = dict(row)
        except sqlite3.OperationalError:
            pass

        conn.close()

        # Build response
        result = {
            'status': 'ok',
            'ticker': ticker,
            'company': {
                'name': stock_data.get('company_name'),
                'cik': stock_data.get('cik'),
                'sic_code': stock_data.get('sic_code'),
                'sic_description': stock_data.get('sic_description'),
                'exchange': stock_data.get('exchange'),
                'state': stock_data.get('state_of_incorporation'),
                'fiscal_year_end': stock_data.get('fiscal_year_end'),
                'category': stock_data.get('category'),
                'entity_type': stock_data.get('entity_type'),
                'website': stock_data.get('website'),
            },
            'analysis': None,
            'gpt_analysis': None,
            'combined_scores': None,
            'metrics': None,
            'history': [],
            'filings': [],
            'next_earnings': next_earnings
        }

        # Parse analysis
        if analysis:
            analysis_data = dict(analysis)
            for field in ['strengths', 'risks', 'key_metrics_to_watch', 'metrics_snapshot']:
                if analysis_data.get(field):
                    try:
                        analysis_data[field] = json.loads(analysis_data[field])
                    except:
                        pass
            result['analysis'] = analysis_data

        # Parse metrics
        if metrics:
            result['metrics'] = dict(metrics)

        # Parse history
        if history:
            result['history'] = [dict(h) for h in history]

        # Parse filings
        if filings:
            result['filings'] = [dict(f) for f in filings]

        # Parse GPT analysis
        if gpt_analysis:
            gpt_data = dict(gpt_analysis)
            for field in ['positives', 'negatives', 'metrics_snapshot', 'key_metrics_to_watch']:
                if gpt_data.get(field):
                    try:
                        gpt_data[field] = json.loads(gpt_data[field])
                    except:
                        pass
            result['gpt_analysis'] = gpt_data

        # Parse combined scores
        if combined_scores:
            result['combined_scores'] = dict(combined_scores)

        return jsonify(result)

    except Exception as e:
        log_error('server', str(e), ticker=ticker, exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/stock/<ticker>/history', methods=['GET'])
def get_stock_history(ticker):
    """Get analysis history for a stock (for showing old vs new)"""
    try:
        ticker = ticker.upper()
        limit = int(request.args.get('limit', 20))

        conn = get_db_conn()
        cursor = conn.cursor()

        # Check stock exists
        cursor.execute('SELECT ticker FROM us_stock_master WHERE ticker = ?', (ticker,))
        if not cursor.fetchone():
            return jsonify({'status': 'not_found', 'error': 'Stock not found'}), 404

        # Get analysis history with full details
        cursor.execute('''
            SELECT id, timestamp, rating, confidence, thesis,
                   strengths, risks, key_metrics_to_watch,
                   data_freshness, data_completeness, metrics_snapshot,
                   model_used, processing_time_ms
            FROM us_stock_analyses
            WHERE ticker = ?
            ORDER BY timestamp DESC
            LIMIT ?
        ''', (ticker, limit))
        analyses = cursor.fetchall()
        conn.close()

        history = []
        for row in analyses:
            item = dict(row)
            # Parse JSON fields
            for field in ['strengths', 'risks', 'key_metrics_to_watch', 'metrics_snapshot']:
                if item.get(field):
                    try:
                        item[field] = json.loads(item[field])
                    except:
                        pass
            history.append(item)

        # Calculate changes between analyses
        if len(history) >= 2:
            latest = history[0]
            previous = history[1]

            # Rating change
            rating_map = {'STRONG_SELL': 1, 'SELL': 2, 'HOLD': 3, 'BUY': 4, 'STRONG_BUY': 5}
            latest_score = rating_map.get(latest.get('rating'), 3)
            prev_score = rating_map.get(previous.get('rating'), 3)

            latest['rating_change'] = latest_score - prev_score
            latest['confidence_change'] = (latest.get('confidence') or 0) - (previous.get('confidence') or 0)

        return jsonify({
            'status': 'ok',
            'ticker': ticker,
            'count': len(history),
            'history': history
        })

    except Exception as e:
        log_error('server', str(e), ticker=ticker, exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/stocks/analyzed', methods=['GET'])
def get_analyzed_stocks():
    """Get list of all analyzed stocks for sitemap/listing.

    Returns metrics_snapshot (parsed from us_stock_analyses) and growth fields
    (from us_calculated_metrics) per stock so frontend list pages can filter on
    fundamentals (roe, total_assets, debt_to_equity, dividends_paid, etc.).

    Query params:
        page (int)        - page number, default 1
        per_page (int)    - items per page, default 100, max 5000
        rating (str)      - filter by single rating (e.g. STRONG_BUY)
        ratings (str)     - comma-separated list of ratings (e.g. STRONG_BUY,BUY)
    """
    try:
        page = int(request.args.get('page', 1))
        per_page = min(int(request.args.get('per_page', 100)), 5000)
        rating = request.args.get('rating')
        ratings_csv = request.args.get('ratings')

        offset = (page - 1) * per_page

        conn = get_db_conn()
        cursor = conn.cursor()

        # Build query for analyzed stocks only.
        # We pull metrics_snapshot from us_stock_analyses (JSON blob with
        # roe, total_assets, dividends_paid, etc.) and growth fields from
        # us_calculated_metrics so list pages can filter on fundamentals.
        select_cols = (
            'm.ticker, m.company_name, m.exchange, m.sic_description, '
            'a.rating, a.confidence, a.timestamp as analysis_date, '
            'a.metrics_snapshot, '
            'cm.revenue_growth, cm.net_income_growth, cm.eps_growth, '
            'c.gpt_rating, c.gpt_confidence, c.combined_rating, '
            'c.combined_confidence, c.agreement_level'
        )
        base_query = '''
            FROM us_stock_master m
            INNER JOIN (
                SELECT ticker, rating, confidence, timestamp, metrics_snapshot,
                       ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY timestamp DESC) as rn
                FROM us_stock_analyses
            ) a ON m.ticker = a.ticker AND a.rn = 1
            LEFT JOIN us_combined_scores c ON m.ticker = c.ticker
            LEFT JOIN (
                SELECT ticker, revenue_growth, net_income_growth, eps_growth,
                       ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY period DESC) as rn
                FROM us_calculated_metrics
            ) cm ON m.ticker = cm.ticker AND cm.rn = 1
            WHERE m.is_active = 1
        '''
        params = []

        # Rating filter: prefer ratings (multi) over rating (single) if both given
        if ratings_csv:
            rating_list = [r.strip() for r in ratings_csv.split(',') if r.strip()]
            if rating_list:
                placeholders = ','.join(['?'] * len(rating_list))
                base_query += ' AND a.rating IN (' + placeholders + ')'
                params.extend(rating_list)
        elif rating:
            base_query += ' AND a.rating = ?'
            params.append(rating)

        # Count total
        cursor.execute('SELECT COUNT(*) ' + base_query, params)
        total = cursor.fetchone()[0]

        # Fetch page
        page_query = ('SELECT ' + select_cols + ' ' + base_query +
                      ' ORDER BY a.timestamp DESC LIMIT ? OFFSET ?')
        cursor.execute(page_query, params + [per_page, offset])
        rows = cursor.fetchall()
        conn.close()

        stocks = []
        for row in rows:
            # Parse metrics_snapshot JSON blob (may be empty/null for old rows)
            metrics_snapshot = {}
            ms_raw = row['metrics_snapshot']
            if ms_raw:
                try:
                    metrics_snapshot = json.loads(ms_raw)
                except (ValueError, TypeError):
                    metrics_snapshot = {}

            # Merge growth fields (from us_calculated_metrics) into the snapshot
            # so list filters can read everything from one nested object.
            if row['revenue_growth'] is not None:
                metrics_snapshot['revenue_growth'] = row['revenue_growth']
            if row['net_income_growth'] is not None:
                metrics_snapshot['net_income_growth'] = row['net_income_growth']
            if row['eps_growth'] is not None:
                metrics_snapshot['eps_growth'] = row['eps_growth']

            stock_data = {
                'ticker': row['ticker'],
                'company_name': row['company_name'],
                'exchange': row['exchange'],
                'sector': row['sic_description'],
                'rating': row['rating'],
                'confidence': row['confidence'],
                'analysis_date': row['analysis_date'],
                'gpt_rating': row['gpt_rating'],
                'gpt_confidence': row['gpt_confidence'],
                'combined_rating': row['combined_rating'],
                'combined_confidence': row['combined_confidence'],
                'agreement_level': row['agreement_level'],
                'metrics_snapshot': metrics_snapshot,
            }
            stocks.append(stock_data)

        return jsonify({
            'status': 'ok',
            'stocks': stocks,
            'total': total,
            'page': page,
            'per_page': per_page,
            'total_pages': (total + per_page - 1) // per_page
        })

    except Exception as e:
        log_error('server', str(e), exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


@app.route('/api/earnings/upcoming', methods=['GET'])
def get_upcoming_earnings():
    """Earnings calendar with AI ratings joined in.

    Query params:
        range (str)  - 'today' | 'tomorrow' | 'this-week' | 'next-week' | 'days'
                       Default 'this-week' (today through next Sunday)
        days  (int)  - used when range='days'; 1..30, default 7
    """
    try:
        import pytz
        range_param = (request.args.get('range') or 'this-week').lower()
        today = datetime.now(pytz.timezone('America/New_York')).date()  # match NASDAQ Eastern

        if range_param == 'today':
            start = end = today
        elif range_param == 'tomorrow':
            start = end = today + timedelta(days=1)
        elif range_param == 'this-week':
            # today through coming Sunday
            start = today
            end = today + timedelta(days=(6 - today.weekday()))
        elif range_param == 'next-week':
            # next Monday through next Sunday
            start = today + timedelta(days=(7 - today.weekday()))
            end = start + timedelta(days=6)
        elif range_param == 'days':
            n = max(1, min(int(request.args.get('days', 7)), 30))
            start = today
            end = today + timedelta(days=n - 1)
        else:
            return jsonify({'status': 'error', 'error': 'Invalid range'}), 400

        conn = get_db_conn()
        cursor = conn.cursor()
        cursor.execute('''
            SELECT
                e.ticker, e.earnings_date, e.company_name, e.earnings_time,
                e.eps_estimate, e.eps_prior, e.num_estimates,
                e.fiscal_quarter, e.market_cap,
                m.exchange, m.sic_description AS sector,
                a.rating, a.confidence, a.timestamp AS analysis_date,
                c.gpt_rating, c.combined_rating, c.combined_confidence,
                c.agreement_level
            FROM us_earnings_calendar e
            LEFT JOIN us_stock_master m ON m.ticker = e.ticker
            LEFT JOIN (
                SELECT ticker, rating, confidence, timestamp,
                       ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY timestamp DESC) AS rn
                FROM us_stock_analyses
            ) a ON a.ticker = e.ticker AND a.rn = 1
            LEFT JOIN us_combined_scores c ON c.ticker = e.ticker
            WHERE e.earnings_date BETWEEN ? AND ?
            ORDER BY e.earnings_date ASC,
                     CASE e.earnings_time WHEN 'BMO' THEN 1 WHEN 'TNS' THEN 2 WHEN 'AMC' THEN 3 ELSE 4 END,
                     COALESCE(e.market_cap, 0) DESC
        ''', (start.isoformat(), end.isoformat()))

        rows = []
        for r in cursor.fetchall():
            rows.append({
                'ticker': r['ticker'],
                'earnings_date': r['earnings_date'],
                'company_name': r['company_name'],
                'earnings_time': r['earnings_time'],
                'eps_estimate': r['eps_estimate'],
                'eps_prior': r['eps_prior'],
                'num_estimates': r['num_estimates'],
                'fiscal_quarter': r['fiscal_quarter'],
                'market_cap': r['market_cap'],
                'exchange': r['exchange'],
                'sector': r['sector'],
                'rating': r['combined_rating'] or r['rating'],
                'confidence': r['combined_confidence'] or r['confidence'],
                'claude_rating': r['rating'],
                'gpt_rating': r['gpt_rating'],
                'agreement_level': r['agreement_level'],
                'analysis_date': r['analysis_date'],
            })

        # Last cron run (max last_updated as a freshness signal)
        cursor.execute('SELECT MAX(last_updated) FROM us_earnings_calendar')
        last_updated = cursor.fetchone()[0]

        conn.close()
        return jsonify({
            'status': 'ok',
            'range': range_param,
            'start_date': start.isoformat(),
            'end_date': end.isoformat(),
            'count': len(rows),
            'last_updated': last_updated,
            'earnings': rows,
        })
    except Exception as e:
        log_error('server', str(e), exception=e)
        return jsonify({'status': 'error', 'error': str(e)}), 500


# Serve frontend
@app.route('/us/')
@app.route('/us/<path:path>')
def serve_frontend(path='index.html'):
    """Serve frontend files"""
    return send_from_directory('us', path)


# ============================================================================
# Main
# ============================================================================

if __name__ == '__main__':
    log_info('server', 'Starting US Stock Analysis Server on port {}'.format(PORT))
    print("US Stock Analysis Server")
    print("=" * 50)
    print("Port: {}".format(PORT))
    print("Database: {}".format(DB_PATH))
    print("=" * 50)

    app.run(host='0.0.0.0', port=PORT, debug=False, threaded=True)