"""Reporting and audit exports. Independent of dashboard presentation."""
import csv
import hashlib
import io
import json
import os
import sys
from datetime import datetime, timedelta, timezone
from flask import Blueprint, render_template, request, jsonify, send_file
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
from core import get_db, login_required, roles_required, __version__
from web.dashboard import dashboard_overview

bp = Blueprint('reporting', __name__)


def safe_value(value):
    if value is None:
        return ''
    value = str(value)
    return "'" + value if value.lstrip().startswith(('=', '+', '-', '@')) else value


def export_csv(filename, fields, rows):
    output = io.StringIO(newline='')
    writer = csv.DictWriter(output, fieldnames=fields)
    writer.writeheader()
    for row in rows:
        writer.writerow({key: safe_value(row.get(key, '')) for key in fields})
    return send_file(io.BytesIO(output.getvalue().encode('utf-8-sig')),
                     mimetype='text/csv; charset=utf-8', as_attachment=True, download_name=filename)


@bp.route('/reports')
@login_required
def index():
    return render_template('reporting.html')


@bp.route('/api/reports/dashboard.csv')
@login_required
def dashboard_csv():
    from services.external import ExternalConnectorError
    try:
        snapshot = 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
    rows = [{'section': 'KPI', 'name': name, 'period': 'Current', 'value': value}
            for name, value in snapshot['metrics'].items()]
    rows += [{'section': 'Funding history', 'name': 'Funded volume', 'period': row['month'], 'value': row['volume']}
             for row in snapshot['monthly']]
    rows += [{'section': 'Pipeline stages', 'name': row['status'], 'period': 'Current', 'value': row['volume']}
             for row in snapshot['pipeline']]
    return export_csv('mortgage_banker_portal_dashboard_snapshot.csv', ['section', 'name', 'period', 'value'], rows)


@bp.route('/api/reports/portfolio.csv')
@login_required
def analyst_csv():
    conn = get_db()
    rows = conn.execute('''SELECT COALESCE(loan_status,'Unknown') status,
               COALESCE(loan_channel,'Unknown') channel, COUNT(*) loan_count,
               COALESCE(SUM(loan_amount),0) principal_volume
               FROM loans GROUP BY loan_status, loan_channel ORDER BY loan_status, loan_channel''').fetchall()
    conn.close()
    return export_csv('mortgage_banker_portal_portfolio_by_stage.csv', ['status', 'channel', 'loan_count', 'principal_volume'],
                      [dict(row) for row in rows])


@bp.route('/audit')
@roles_required('admin')
def audit_center():
    return render_template('audit.html')


def configuration_rows():
    """Export a reproducible sanitized configuration snapshot; never credentials."""
    from core import DEMO_MODE
    from services.external import external_metadata
    conn = get_db()
    settings = [dict(row) for row in conn.execute('SELECT key, value FROM settings ORDER BY key')]
    conn.close()
    rows = [
        {'category': 'Application', 'key': 'version', 'value': __version__},
        {'category': 'Application', 'key': 'demo_mode', 'value': DEMO_MODE},
        {'category': 'Source', 'key': 'active_default', 'value': 'local'},
    ]
    metadata = external_metadata()
    rows += [
        {'category': 'External source', 'key': 'configured', 'value': metadata['configured']},
        {'category': 'External source', 'key': 'database_type', 'value': metadata['database_type']},
        {'category': 'External source', 'key': 'view', 'value': metadata['view']},
    ]
    for item in settings:
        key = item['key']
        sensitive = any(part in key.lower() for part in ('password', 'secret', 'token', 'credential', 'api_key', 'access_key'))
        rows.append({'category': 'User settings', 'key': key,
                     'value': '[REDACTED]' if sensitive else item['value']})
    canonical = json.dumps(rows, sort_keys=True, separators=(',', ':'))
    digest = hashlib.sha256(canonical.encode('utf-8')).hexdigest()
    rows.insert(0, {'category': 'Snapshot', 'key': 'sha256', 'value': digest})
    return rows


@bp.route('/api/audit/export/config.csv')
@roles_required('admin')
def config_export():
    stamp = datetime.now(timezone.utc).strftime('%Y%m%d_%H%M%SZ')
    return export_csv(f'mortgage_banker_portal_config_{stamp}.csv', ['category', 'key', 'value'], configuration_rows())


@bp.route('/api/audit/events')
@roles_required('admin')
def audit_events():
    try:
        limit = min(500, max(1, int(request.args.get('limit', 100))))
    except ValueError:
        limit = 100
    conn = get_db()
    rows = conn.execute('''SELECT ae.id, ae.created_at, COALESCE(u.username,'system') actor,
                      ae.category, ae.action, ae.subject, ae.details
                      FROM audit_events ae LEFT JOIN users u ON u.id=ae.actor_user_id
                      ORDER BY ae.id DESC LIMIT ?''', (limit,)).fetchall()
    conn.close()
    return jsonify([dict(row) for row in rows])


@bp.route('/api/audit/export/events.csv')
@roles_required('admin')
def events_export():
    try:
        days = min(365, max(1, int(request.args.get('days', 90))))
    except ValueError:
        days = 90
    cutoff = (datetime.now(timezone.utc) - timedelta(days=days)).strftime('%Y-%m-%d %H:%M:%S')
    conn = get_db()
    rows = conn.execute('''SELECT ae.id, ae.created_at, COALESCE(u.username,'system') actor,
                      ae.category, ae.action, ae.subject, ae.details
                      FROM audit_events ae LEFT JOIN users u ON u.id=ae.actor_user_id
                      WHERE ae.created_at >= ? ORDER BY ae.id DESC LIMIT 10000''', (cutoff,)).fetchall()
    conn.close()
    return export_csv('mortgage_banker_portal_audit_events.csv', ['id', 'created_at', 'actor', 'category', 'action', 'subject', 'details'],
                      [dict(row) for row in rows])
