"""Analytics - Advanced reporting and insights."""
from flask import Blueprint, render_template, request, jsonify
import sqlite3
from datetime import datetime, timedelta
import csv
from flask import send_file
import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
from core import get_db, login_required

bp = Blueprint('analytics', __name__)


@bp.route('/analytics')
@login_required
def index():
    """Analytics page."""
    return render_template('analytics.html')


@bp.route('/api/analytics/profitability')
@login_required
def api_profitability():
    """Profitability analysis by month."""
    conn = get_db()
    
    rows = conn.execute("""
        SELECT strftime('%Y-%m', funded_date) as month,
               COUNT(*) as loan_count,
               COALESCE(SUM(loan_amount), 0) as volume,
               COALESCE(SUM(gross_profit), 0) as gross_profit,
               COALESCE(SUM(net_profit), 0) as net_profit
        FROM loans 
        WHERE funded_date IS NOT NULL AND gross_profit IS NOT NULL
        GROUP BY month
        ORDER BY month DESC
        LIMIT 12
    """).fetchall()
    
    conn.close()
    
    return jsonify([dict(r) for r in rows])


@bp.route('/api/analytics/turnaround')
@login_required
def api_turnaround():
    """Average turnaround time by stage."""
    conn = get_db()
    
    # Time from registered to underwriting
    reg_to_uw = conn.execute("""
        SELECT AVG(julianday(underwriting_date) - julianday(registered_date)) as avg_days
        FROM loans 
        WHERE underwriting_date IS NOT NULL AND registered_date IS NOT NULL
    """).fetchone()
    
    # Time from underwriting to approved
    uw_to_approved = conn.execute("""
        SELECT AVG(julianday(approved_date) - julianday(underwriting_date)) as avg_days
        FROM loans 
        WHERE approved_date IS NOT NULL AND underwriting_date IS NOT NULL
    """).fetchone()
    
    # Time from approved to funded
    approved_to_funded = conn.execute("""
        SELECT AVG(julianday(funded_date) - julianday(approved_date)) as avg_days
        FROM loans 
        WHERE funded_date IS NOT NULL AND approved_date IS NOT NULL
    """).fetchone()
    
    # Total time from registered to funded
    total_time = conn.execute("""
        SELECT AVG(julianday(funded_date) - julianday(registered_date)) as avg_days
        FROM loans 
        WHERE funded_date IS NOT NULL AND registered_date IS NOT NULL
    """).fetchone()
    
    conn.close()
    
    return jsonify({
        'registered_to_underwriting': reg_to_uw['avg_days'],
        'underwriting_to_approved': uw_to_approved['avg_days'],
        'approved_to_funded': approved_to_funded['avg_days'],
        'total_registered_to_funded': total_time['avg_days']
    })


@bp.route('/api/analytics/underwriter_performance')
@login_required
def api_underwriter_performance():
    """Underwriter workload and performance."""
    conn = get_db()
    
    rows = conn.execute("""
        SELECT underwriter,
               COUNT(*) as loan_count,
               COALESCE(SUM(loan_amount), 0) as volume,
               AVG(julianday(approved_date) - julianday(underwriting_date)) as avg_uw_days
        FROM loans 
        WHERE underwriter IS NOT NULL AND approved_date IS NOT NULL
        GROUP BY underwriter
        ORDER BY loan_count DESC
    """).fetchall()
    
    conn.close()
    
    return jsonify([dict(r) for r in rows])


@bp.route('/api/analytics/processor_performance')
@login_required
def api_processor_performance():
    """Processor workload and performance."""
    conn = get_db()
    
    rows = conn.execute("""
        SELECT processor,
               COUNT(*) as loan_count,
               COALESCE(SUM(loan_amount), 0) as volume
        FROM loans 
        WHERE processor IS NOT NULL AND funded_date IS NOT NULL
        GROUP BY processor
        ORDER BY loan_count DESC
    """).fetchall()
    
    conn.close()
    
    return jsonify([dict(r) for r in rows])


@bp.route('/api/analytics/rate_lock_performance')
@login_required
def api_rate_lock_performance():
    """Rate lock success rate."""
    conn = get_db()
    
    total_locked = conn.execute("""
        SELECT COUNT(*) FROM loans WHERE rate_locked_date IS NOT NULL
    """).fetchone()[0]
    
    locked_and_funded = conn.execute("""
        SELECT COUNT(*) FROM loans 
        WHERE rate_locked_date IS NOT NULL AND funded_date IS NOT NULL
    """).fetchone()[0]
    
    not_yet_funded = total_locked - locked_and_funded
    
    conn.close()
    
    return jsonify({
        'total_locked': total_locked,
        'locked_and_funded': locked_and_funded,
        'not_yet_funded': not_yet_funded,
        'lock_failures': not_yet_funded,  # legacy API alias; not a verified failure count
        'success_rate': (locked_and_funded / total_locked * 100) if total_locked > 0 else 0
    })


@bp.route('/api/analytics/export')
@login_required
def api_export_report():
    """Export analytics report as CSV."""
    conn = get_db()
    
    # Monthly summary
    rows = conn.execute("""
        SELECT strftime('%Y-%m', funded_date) as month,
               COUNT(*) as loan_count,
               COALESCE(SUM(loan_amount), 0) as volume,
               COALESCE(SUM(gross_profit), 0) as gross_profit
        FROM loans 
        WHERE funded_date IS NOT NULL
        GROUP BY month
        ORDER BY month DESC
    """).fetchall()
    
    conn.close()
    
    # Generate CSV
    import io
    output = io.StringIO()
    writer = csv.writer(output)
    writer.writerow(['Month', 'Loan Count', 'Volume', 'Gross Profit'])
    
    for row in rows:
        writer.writerow([row['month'], row['loan_count'], f"${row['volume']:,.2f}", f"${row['gross_profit']:,.2f}"])
    
    output.seek(0)
    
    return send_file(
        io.BytesIO(output.getvalue().encode()),
        mimetype='text/csv',
        as_attachment=True,
        download_name=f'mortgage_analytics_{datetime.now().strftime("%Y%m%d")}.csv'
    )
