"""Read-only external database connector and canonical dashboard contract.

Connection credentials come only from environment variables. This adapter
supports SQLAlchemy's SQLite, PostgreSQL and SQL Server dialects (drivers are
installed separately). No remote data is written to the application's SQLite DB.
"""
import os
import re
from collections import defaultdict
from datetime import date, datetime, timedelta

from sqlalchemy import create_engine, text
from sqlalchemy.engine import make_url
from demo_data import shift_month

SCHEMA_COLUMNS = (
    'loan_number', 'loan_status', 'loan_channel', 'loan_program',
    'loan_amount', 'note_rate', 'registered_date', 'funded_date', 'gross_profit',
    'broker_company', 'rate_locked_date'
)
ACTIVE_STAGES = ['Loan Open', 'Underwriting', 'Approved', 'Clear to Close', 'Docs Out']
CHANNELS = ['Wholesale', 'Correspondent', 'Retail']


class ExternalConnectorError(Exception):
    """Controlled error for an unavailable or incompatible external source."""


def external_metadata():
    from core import DEMO_MODE
    url = os.environ.get('MORTGAGE_EXTERNAL_URL', '').strip() if not DEMO_MODE else ''
    view = os.environ.get('MORTGAGE_EXTERNAL_VIEW', 'mortgage_dashboard_source')
    try:
        dialect = make_url(url).get_backend_name() if url else 'not configured'
    except Exception:
        dialect = 'invalid'
    return {'configured': bool(url), 'database_type': dialect, 'view': view}


def get_connector():
    metadata = external_metadata()
    if not metadata['configured']:
        raise ExternalConnectorError('External data source is not configured.')
    url = os.environ.get('MORTGAGE_EXTERNAL_URL', '')
    try:
        scheme = make_url(url).get_backend_name()
        if scheme not in ('sqlite', 'postgresql', 'mssql'):
            raise ExternalConnectorError('Supported database types are SQLite, PostgreSQL and SQL Server.')
        view = metadata['view']
        if not re.fullmatch(r'[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)?', view):
            raise ExternalConnectorError('Invalid view identifier.')
        return create_engine(url, pool_pre_ping=True, pool_size=5), view
    except ExternalConnectorError:
        raise
    except Exception as exc:
        raise ExternalConnectorError('Invalid external connection configuration.') from exc


def check_connection():
    engine, view = get_connector()
    try:
        with engine.connect() as conn:
            conn.execute(text('SELECT 1')).scalar_one()
            # This checks column compatibility and visibility using a zero-row result.
            conn.execute(text(f"SELECT {', '.join(SCHEMA_COLUMNS)} FROM {view} WHERE 1=0"))
        return {'connected': True, 'view': view, 'database_type': engine.dialect.name}
    except Exception as exc:
        raise ExternalConnectorError('Connection or source-view check failed. Verify the read-only account and expected columns.') from exc
    finally:
        engine.dispose()


def fetch_canonical_rows():
    engine, view = get_connector()
    try:
        statement = text(f"SELECT {', '.join(SCHEMA_COLUMNS)} FROM {view}")
        with engine.connect().execution_options(stream_results=True) as conn:
            result = conn.execute(statement)
            rows = result.mappings().fetchmany(50001)
        if len(rows) > 50000:
            raise ExternalConnectorError('Source contains over 50,000 rows. Add filtered/aggregated views before connecting.')
        return [dict(row) for row in rows]
    except ExternalConnectorError:
        raise
    except Exception as exc:
        raise ExternalConnectorError('External data source could not be read. Check connection and schema mapping.') from exc
    finally:
        engine.dispose()


def as_date(value):
    if isinstance(value, datetime):
        return value.date()
    if isinstance(value, date):
        return value
    if not value:
        return None
    try:
        return date.fromisoformat(str(value)[:10])
    except ValueError:
        return None


def to_number(value):
    try:
        return float(value or 0)
    except (TypeError, ValueError):
        return 0.0


def external_overview(period='12m', channel='all'):
    """Compute the dashboard response from canonical external view rows.

    The v2.1 adapter intentionally has a 50k-row limit. Production-scale sources
    should push these aggregations into SQL/materialized views, not Python.
    """
    rows = fetch_canonical_rows()
    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))
    active = [r for r in rows if r.get('loan_status') in ACTIVE_STAGES and
              (channel == 'all' or r.get('loan_channel') == channel)]
    funded_period = [r for r in rows if (d := as_date(r.get('funded_date'))) and start <= d <= today]
    funded_filtered = [r for r in funded_period if channel == 'all' or r.get('loan_channel') == channel]
    this_month = [r for r in funded_filtered if as_date(r.get('funded_date')) >= month_start]
    ytd = [r for r in rows if (d := as_date(r.get('funded_date'))) and d.year == today.year and
           d <= today and (channel == 'all' or r.get('loan_channel') == channel)]
    active_volume = sum(to_number(r.get('loan_amount')) for r in active)
    weighted = sum(to_number(r.get('loan_amount')) * to_number(r.get('note_rate')) for r in active)
    monthly = []
    for i in range(months - 1, -1, -1):
        month = shift_month(month_start, -i)
        matching = [r for r in funded_filtered if as_date(r.get('funded_date')).strftime('%Y-%m') == month.strftime('%Y-%m')]
        monthly.append({'month':month.strftime('%Y-%m'), 'label':month.strftime('%b'),
                        'volume':sum(to_number(r.get('loan_amount')) for r in matching), 'loans':len(matching)})
    pipeline = [{'status': stage, 'loans': len(matching := [r for r in active if r.get('loan_status') == stage]),
                 'volume': sum(to_number(r.get('loan_amount')) for r in matching)} for stage in ACTIVE_STAGES]
    mix = [{'channel': ch, 'loans':len(matching := [r for r in funded_period if r.get('loan_channel') == ch]),
            'volume':sum(to_number(r.get('loan_amount')) for r in matching)} for ch in CHANNELS]
    partners = defaultdict(lambda: {'loans':0,'volume':0})
    for r in funded_filtered:
        name = r.get('broker_company')
        if name:
            partners[name]['loans'] += 1
            partners[name]['volume'] += to_number(r.get('loan_amount'))
    brokers = [dict(name=name, **value) for name,value in sorted(partners.items(),key=lambda x:x[1]['volume'],reverse=True)[:4]]
    watch=[]
    for r in active:
        locked=as_date(r.get('rate_locked_date'))
        if locked and today <= (expires := locked+timedelta(days=45)) <= today+timedelta(days=7):
            watch.append({'id':None,'loan_number':r.get('loan_number'),'broker_company':r.get('broker_company'),
                          'loan_status':r.get('loan_status'),'loan_amount':to_number(r.get('loan_amount')),
                          'loan_program':r.get('loan_program'),'expires':expires.isoformat(),'days':(expires-today).days})
    watch.sort(key=lambda r:(r['days'],-r['loan_amount']))
    recent=sorted(active,key=lambda r:as_date(r.get('registered_date')) or date.min,reverse=True)[:6]
    return {
        'period':period,'channel':channel, 'source':'external',
        'as_of':f"{today.strftime('%B')} {today.day}, {today.year}",
        'metrics':{
            'active_loans':len(active),'active_volume':active_volume,'funded_loans':len(this_month),
            'funded_volume':sum(to_number(r.get('loan_amount')) for r in this_month),
            'gross_profit_ytd':sum(to_number(r.get('gross_profit')) for r in ytd),
            'expiring_locks':len(watch),
            'aged_loans':sum(1 for r in active if (d := as_date(r.get('registered_date'))) and (today-d).days > 30),
            'weighted_rate':weighted/active_volume if active_volume else 0,
        },
        'monthly':monthly,'pipeline':pipeline,'channel_mix':mix,'brokers':brokers,
        'lock_watch':watch[:5],
        'recent':[{'id':None,'loan_number':r.get('loan_number'),'loan_program':r.get('loan_program'),
                   'loan_status':r.get('loan_status'),'loan_channel':r.get('loan_channel'),
                   'loan_amount':to_number(r.get('loan_amount')),'broker_company':r.get('broker_company'),
                   'registered_date':str(r.get('registered_date') or '')} for r in recent]
    }
