"""Import/Export - CSV file handling."""
from flask import Blueprint, render_template, request, jsonify, send_file, abort
import sqlite3
import csv
import io
import os
from datetime import datetime
import sys
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
from flask import session
from core import get_db, login_required, roles_required, current_user, DEMO_MODE, audit_event, logger
from services import importer

bp = Blueprint('import_export', __name__)


@bp.route('/import')
@roles_required('admin')
def index():
    """Import page."""
    return render_template('import.html')


@bp.route('/api/import/loans', methods=['POST'])
@roles_required('admin')
def import_loans():
    """Validate then upsert loans from a CSV extract (pipeline in services/importer.py).

    ``mode=strict`` (default) rejects the whole file if any row is invalid;
    ``mode=partial`` imports valid rows and reports the rest. Every run is
    recorded in ``import_batches`` with the file's SHA-256.
    """
    upload = request.files.get('file')
    if upload is None or not upload.filename:
        return jsonify({'error': 'No file uploaded'}), 400
    if not upload.filename.lower().endswith('.csv'):
        return jsonify({'error': 'File must be CSV'}), 400
    mode = request.form.get('mode', 'strict')
    if mode not in ('strict', 'partial'):
        return jsonify({'error': 'mode must be strict or partial'}), 400
    conn = get_db()
    try:
        result = importer.run_import(conn, upload.read(), upload.filename, mode, session.get('user_id'), audit_event)
        conn.commit()
    except Exception:
        conn.rollback()
        logger.exception('Loan import failed')
        return jsonify({'error': 'Import failed unexpectedly; nothing was saved. Details are in the server log.'}), 500
    finally:
        conn.close()
    rejected = result.pop('rejected')
    return jsonify(result), (422 if rejected else 200)


@bp.route('/api/import/brokers', methods=['POST'])
@roles_required('admin')
def import_brokers():
    """Import broker companies from CSV."""
    if 'file' not in request.files:
        return jsonify({'error': 'No file uploaded'}), 400
    
    file = request.files['file']
    
    try:
        text = file.read().decode('utf-8')
        reader = csv.DictReader(io.StringIO(text))
        
        conn = get_db()
        cursor = conn.cursor()
        
        imported = skipped = 0

        for row in reader:
            name = row.get('Name', '').strip()
            if not name or name.startswith('.'):
                continue
            
            try:
                cursor.execute("""
                    INSERT INTO broker_companies (name, nmls_id, address, city, state, zip_code, phone)
                    VALUES (?, ?, ?, ?, ?, ?, ?)
                    ON CONFLICT(name) DO UPDATE SET nmls_id=excluded.nmls_id, address=excluded.address,
                        city=excluded.city, state=excluded.state, zip_code=excluded.zip_code, phone=excluded.phone
                """, (
                    name,
                    row.get('NmlsIdentifier', '').strip() or None,
                    row.get('Address', '').strip() or None,
                    row.get('City', '').strip() or None,
                    row.get('State', '').strip() or None,
                    row.get('Zip', '').strip() or None,
                    row.get('Phone', '').strip() or None
                ))
                imported += 1
            except sqlite3.Error:
                skipped += 1

        audit_event('data.imported', 'import', 'brokers', f'rows={imported};skipped={skipped}', conn)
        conn.commit()
        conn.close()
        
        return jsonify({'imported': imported, 'skipped': skipped})

    except Exception:
        logger.exception('Broker import failed')
        return jsonify({'error': 'Broker import failed; details are in the server log.'}), 500


@bp.route('/api/export/loans')
@login_required
def export_loans():
    """Export loans to CSV."""
    # Detailed loan exports can contain borrower identifiers; viewers get only
    # aggregated reports. Demo exports remain safe because all names are fake.
    if not DEMO_MODE and current_user()['role'] not in ('analyst', 'admin'):
        abort(403)
    conn = get_db()
    
    status_filter = request.args.get('status', '')
    query = "SELECT * FROM loans WHERE 1=1"
    
    if status_filter:
        query += " AND loan_status = ?"
        
    rows = conn.execute(query, [status_filter] if status_filter else []).fetchall()
    conn.close()
    
    # Generate CSV
    output = io.StringIO()
    fieldnames = ['loan_number', 'borrower_last', 'borrower_first', 'loan_program',
                 'loan_channel', 'loan_status', 'loan_amount', 'note_rate', 
                 'broker_company', 'registered_date', 'funded_date']
    
    writer = csv.DictWriter(output, fieldnames=fieldnames)
    writer.writeheader()
    
    for row in rows:
        writer.writerow({k: _csv_safe(row[k]) for k in fieldnames})
    
    output.seek(0)
    
    return send_file(
        io.BytesIO(output.getvalue().encode()),
        mimetype='text/csv',
        as_attachment=True,
        download_name=f'loans_export_{datetime.now().strftime("%Y%m%d")}.csv'
    )


def _csv_safe(value):
    """Prevent formula interpretation when exported CSV is opened in Excel."""
    if value is None:
        return ''
    text = str(value)
    if text.lstrip().startswith(('=', '+', '-', '@')) and not isinstance(value, (int, float)):
        return "'" + text
    return value


def _parse_money(value):
    """Parse money string to float."""
    if not value:
        return None
    try:
        clean = str(value).replace('$', '').replace(',', '').strip()
        if clean.startswith('('):
            # Negative in parentheses
            clean = -float(clean[1:-1])
        else:
            clean = float(clean)
        return clean
    except (ValueError, IndexError):
        return None


def _parse_float(value):
    """Parse string to float."""
    if not value:
        return None
    try:
        return float(str(value).strip())
    except ValueError:
        return None


def _parse_int(value):
    """Parse string to int."""
    if not value:
        return None
    try:
        return int(float(str(value).strip()))
    except ValueError:
        return None


def _parse_date(value):
    """Parse date string to YYYY-MM-DD format."""
    if not value:
        return None
    
    value = str(value).strip()
    
    # Try various formats
    for fmt in ['%m/%d/%Y', '%Y-%m-%d', '%m/%d/%y']:
        try:
            dt = datetime.strptime(value, fmt)
            return dt.strftime('%Y-%m-%d')
        except ValueError:
            continue
    
    return value  # Return as-is if no format matches


@bp.route('/api/import/batches')
@roles_required('admin')
def import_batches():
    """Most recent import runs for provenance review."""
    conn = get_db()
    rows = conn.execute('''SELECT b.id, b.kind, b.filename, b.file_sha256, b.mode, b.rows_read, b.rows_inserted,
                                  b.rows_updated, b.rows_rejected, b.status, b.created_at,
                                  COALESCE(u.username, 'system') actor
                           FROM import_batches b LEFT JOIN users u ON u.id = b.actor_user_id
                           ORDER BY b.id DESC LIMIT 25''').fetchall()
    conn.close()
    return jsonify([dict(r) for r in rows])
