"""Dashboard - Main overview with KPIs and charts."""
from flask import Blueprint, render_template, jsonify
import sqlite3
from datetime import datetime, timedelta, date
import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
from core import get_db, login_required, __version__
from demo_data import shift_month

bp = Blueprint('dashboard', __name__)

ACTIVE_STAGES = ['Loan Open', 'Underwriting', 'Approved', 'Clear to Close', 'Docs Out']
CHANNELS = ['Wholesale', 'Correspondent', 'Retail']


def dashboard_overview(period='12m', channel='all', source='local'):
    """One consistent, filterable snapshot powering the public showcase."""
    if source == 'external':
        from services.external import external_overview
        return external_overview(period, channel)
    months = {'3m': 3, '6m': 6, '12m': 12}.get(period, 12)
    period = f'{months}m'
    channel = channel if channel in CHANNELS else 'all'
    today = date.today()
    month_start = today.replace(day=1)
    start = shift_month(month_start, -(months - 1))
    channel_sql = ' AND loan_channel = ?' if channel != 'all' else ''
    params = [channel] if channel != 'all' else []
    marks = ','.join('?' for _ in ACTIVE_STAGES)
    conn = get_db()

    active_sql = f'loan_status IN ({marks}){channel_sql}'
    active_params = ACTIVE_STAGES + params
    active = conn.execute(f"""
        SELECT COUNT(*) loans, COALESCE(SUM(loan_amount), 0) volume,
               COALESCE(SUM(loan_amount * note_rate), 0) weighted_rate,
               SUM(CASE WHEN registered_date < ? THEN 1 ELSE 0 END) aged
        FROM loans WHERE {active_sql}
    """, [(today - timedelta(days=30)).isoformat()] + active_params).fetchone()

    funded_where = f'funded_date BETWEEN ? AND ?{channel_sql}'
    funded_params = [month_start.isoformat(), today.isoformat()] + params
    funded = conn.execute(f"""
        SELECT COUNT(*) loans, COALESCE(SUM(loan_amount),0) volume
        FROM loans WHERE {funded_where}
    """, funded_params).fetchone()

    ytd_profit = conn.execute(f"""
        SELECT COALESCE(SUM(gross_profit),0) FROM loans
        WHERE funded_date BETWEEN ? AND ?{channel_sql}
    """, [date(today.year, 1, 1).isoformat(), today.isoformat()] + params).fetchone()[0]

    volume_rows = conn.execute(f"""
        SELECT substr(funded_date,1,7) month, COUNT(*) loans,
               COALESCE(SUM(loan_amount),0) volume
        FROM loans WHERE funded_date BETWEEN ? AND ?{channel_sql}
        GROUP BY month ORDER BY month
    """, [start.isoformat(), today.isoformat()] + params).fetchall()
    by_month = {r['month']: dict(r) for r in volume_rows}
    monthly = []
    for offset in range(months - 1, -1, -1):
        month = shift_month(month_start, -offset)
        key = month.strftime('%Y-%m')
        row = by_month.get(key, {})
        monthly.append({'month': key, 'label': month.strftime('%b'),
                        'volume': row.get('volume', 0), 'loans': row.get('loans', 0)})

    stage_rows = conn.execute(f"""
        SELECT loan_status status, COUNT(*) loans, COALESCE(SUM(loan_amount),0) volume
        FROM loans WHERE {active_sql} GROUP BY loan_status
    """, active_params).fetchall()
    stage_map = {r['status']: dict(r) for r in stage_rows}
    pipeline = [{'status': s, 'loans': stage_map.get(s, {}).get('loans', 0),
                 'volume': stage_map.get(s, {}).get('volume', 0)} for s in ACTIVE_STAGES]

    # Mix intentionally shows the complete market so channel filters have context.
    channel_rows = conn.execute("""
        SELECT loan_channel channel, COALESCE(SUM(loan_amount),0) volume, COUNT(*) loans
        FROM loans WHERE funded_date BETWEEN ? AND ?
        GROUP BY loan_channel
    """, (start.isoformat(), today.isoformat())).fetchall()
    mix_map = {r['channel']: dict(r) for r in channel_rows}
    mix = [{'channel': c, 'volume': mix_map.get(c, {}).get('volume', 0),
            'loans': mix_map.get(c, {}).get('loans', 0)} for c in CHANNELS]

    broker_rows = conn.execute(f"""
        SELECT broker_company name, COUNT(*) loans, SUM(loan_amount) volume
        FROM loans WHERE funded_date BETWEEN ? AND ?{channel_sql}
        AND broker_company IS NOT NULL GROUP BY broker_company
        ORDER BY volume DESC LIMIT 4
    """, [start.isoformat(), today.isoformat()] + params).fetchall()

    locks = conn.execute(f"""
        SELECT id, loan_number, broker_company, loan_status, loan_amount, loan_program,
               date(rate_locked_date, '+45 days') expires
        FROM loans WHERE {active_sql}
        AND rate_locked_date IS NOT NULL
        AND date(rate_locked_date, '+45 days') BETWEEN ? AND ?
        ORDER BY expires, loan_amount DESC LIMIT 5
    """, active_params + [today.isoformat(), (today + timedelta(days=7)).isoformat()]).fetchall()
    lock_total = conn.execute(f"""
        SELECT COUNT(*) FROM loans WHERE {active_sql} AND rate_locked_date IS NOT NULL
        AND date(rate_locked_date, '+45 days') BETWEEN ? AND ?
    """, active_params + [today.isoformat(), (today + timedelta(days=7)).isoformat()]).fetchone()[0]
    recent = conn.execute(f"""
        SELECT id, loan_number, loan_program, loan_status, loan_channel,
               loan_amount, broker_company, registered_date
        FROM loans WHERE {active_sql}
        ORDER BY registered_date DESC, id DESC LIMIT 6
    """, active_params).fetchall()

    conn.close()
    return {
        'period': period, 'channel': channel, 'source': 'local',
        'as_of': f"{today.strftime('%B')} {today.day}, {today.year}",
        'metrics': {
            'active_loans': active['loans'], 'active_volume': active['volume'],
            'funded_loans': funded['loans'], 'funded_volume': funded['volume'],
            'gross_profit_ytd': ytd_profit, 'expiring_locks': lock_total,
            'aged_loans': active['aged'] or 0,
            'weighted_rate': (active['weighted_rate'] / active['volume']) if active['volume'] else 0,
        },
        'monthly': monthly, 'pipeline': pipeline, 'channel_mix': mix,
        'brokers': [dict(row) for row in broker_rows],
        'lock_watch': [{**dict(row), 'days': (date.fromisoformat(row['expires']) - today).days} for row in locks],
        'recent': [dict(row) for row in recent]
    }


@bp.route('/')
@login_required
def index():
    """Main dashboard page."""
    from services.external import external_metadata
    return render_template('dashboard.html', overview=dashboard_overview(),
                           external_available=external_metadata()['configured'], version=__version__)


@bp.route('/api/dashboard/overview')
@login_required
def api_overview():
    from flask import request
    from services.external import ExternalConnectorError
    try:
        return jsonify(dashboard_overview(request.args.get('period', '12m'),
                                          request.args.get('channel', 'all'), request.args.get('source', 'local')))
    except ExternalConnectorError as exc:
        return jsonify(error=str(exc)), 503


@bp.route('/api/dashboard/kpis')
@login_required
def api_kpis():
    """API endpoint for dashboard KPIs."""
    conn = get_db()
    
    kpis = {
        'open_loans': conn.execute("SELECT COUNT(*) FROM loans WHERE loan_status IN ('Loan Open', 'Underwriting', 'Approved')").fetchone()[0],
        'total_volume_open': conn.execute("SELECT COALESCE(SUM(loan_amount), 0) FROM loans WHERE loan_status IN ('Loan Open', 'Underwriting', 'Approved')").fetchone()[0],
        'funded_this_month': conn.execute("""
            SELECT COUNT(*) FROM loans 
            WHERE funded_date IS NOT NULL AND strftime('%Y-%m', funded_date) = strftime('%Y-%m', 'now')
        """).fetchone()[0],
        'volume_funded_month': conn.execute("""
            SELECT COALESCE(SUM(loan_amount), 0) FROM loans 
            WHERE funded_date IS NOT NULL AND strftime('%Y-%m', funded_date) = strftime('%Y-%m', 'now')
        """).fetchone()[0],
        'total_profit': conn.execute("SELECT COALESCE(SUM(gross_profit), 0) FROM loans WHERE gross_profit IS NOT NULL").fetchone()[0],
    }
    
    conn.close()
    return jsonify(kpis)


@bp.route('/api/dashboard/pipeline')
@login_required
def api_pipeline():
    """Loan pipeline by status."""
    conn = get_db()
    rows = conn.execute("""
        SELECT loan_status, COUNT(*) as count, COALESCE(SUM(loan_amount), 0) as volume
        FROM loans 
        WHERE loan_status IN ('Loan Open', 'Underwriting', 'Approved', 'Clear to Close', 'Docs Out')
        GROUP BY loan_status
    """).fetchall()
    
    pipeline = []
    for row in rows:
        pipeline.append({
            'status': row['loan_status'],
            'count': row['count'],
            'volume': row['volume']
        })
    
    conn.close()
    return jsonify(pipeline)


@bp.route('/api/dashboard/monthly_volume')
@login_required
def api_monthly_volume():
    """Monthly funding volume for last 12 months."""
    conn = get_db()
    rows = conn.execute("""
        SELECT strftime('%Y-%m', funded_date) as month, 
               COUNT(*) as count, 
               COALESCE(SUM(loan_amount), 0) as volume
        FROM loans 
        WHERE funded_date IS NOT NULL
        GROUP BY month
        ORDER BY month DESC
        LIMIT 12
    """).fetchall()
    
    monthly = []
    for row in rows:
        monthly.append({
            'month': row['month'],
            'count': row['count'],
            'volume': row['volume']
        })
    
    conn.close()
    return jsonify(monthly)


@bp.route('/api/dashboard/channel_breakdown')
@login_required
def api_channel_breakdown():
    """Volume by channel (retail vs wholesale)."""
    conn = get_db()
    rows = conn.execute("""
        SELECT loan_channel, COUNT(*) as count, COALESCE(SUM(loan_amount), 0) as volume
        FROM loans 
        WHERE funded_date IS NOT NULL
        GROUP BY loan_channel
    """).fetchall()
    
    channels = []
    for row in rows:
        channels.append({
            'channel': row['loan_channel'] or 'Unknown',
            'count': row['count'],
            'volume': row['volume']
        })
    
    conn.close()
    return jsonify(channels)


@bp.route('/api/dashboard/program_mix')
@login_required
def api_program_mix():
    """Volume by loan program."""
    conn = get_db()
    rows = conn.execute("""
        SELECT loan_program, COUNT(*) as count, COALESCE(SUM(loan_amount), 0) as volume
        FROM loans 
        WHERE funded_date IS NOT NULL AND loan_program IS NOT NULL
        GROUP BY loan_program
        ORDER BY volume DESC
        LIMIT 10
    """).fetchall()
    
    programs = []
    for row in rows:
        programs.append({
            'program': row['loan_program'],
            'count': row['count'],
            'volume': row['volume']
        })
    
    conn.close()
    return jsonify(programs)


@bp.route('/api/dashboard/top_brokers')
@login_required
def api_top_brokers():
    """Top brokers by volume."""
    conn = get_db()
    rows = conn.execute("""
        SELECT broker_company, COUNT(*) as count, COALESCE(SUM(loan_amount), 0) as volume
        FROM loans 
        WHERE funded_date IS NOT NULL AND broker_company IS NOT NULL
        GROUP BY broker_company
        ORDER BY volume DESC
        LIMIT 10
    """).fetchall()
    
    brokers = []
    for row in rows:
        brokers.append({
            'broker': row['broker_company'],
            'count': row['count'],
            'volume': row['volume']
        })
    
    conn.close()
    return jsonify(brokers)


@bp.route('/api/dashboard/aging')
@login_required
def api_aging():
    """Loan aging report."""
    conn = get_db()
    rows = conn.execute("""
        SELECT 
            CASE 
                WHEN julianday('now') - julianday(registered_date) < 7 THEN '0-6 days'
                WHEN julianday('now') - julianday(registered_date) < 14 THEN '7-13 days'
                WHEN julianday('now') - julianday(registered_date) < 30 THEN '14-29 days'
                WHEN julianday('now') - julianday(registered_date) < 60 THEN '30-59 days'
                ELSE '60+ days'
            END as age_bucket,
            COUNT(*) as count,
            COALESCE(SUM(loan_amount), 0) as volume
        FROM loans 
        WHERE loan_status IN ('Loan Open', 'Underwriting', 'Approved')
        GROUP BY age_bucket
    """).fetchall()
    
    aging = []
    for row in rows:
        aging.append({
            'bucket': row['age_bucket'],
            'count': row['count'],
            'volume': row['volume']
        })
    
    conn.close()
    return jsonify(aging)
