"""Loans - CRUD operations and listing."""
from flask import Blueprint, render_template, request, jsonify, session
import sqlite3
from datetime import date
import sys
import os
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
from core import get_db, login_required, roles_required, audit_event
from services import loan_rules

bp = Blueprint('loans', __name__)


@bp.route('/loans')
@login_required
def list_loans():
    """List all loans with filters."""
    status_filter = request.args.get('status', '')
    channel_filter = request.args.get('channel', '')
    search = request.args.get('search', '')
    
    conn = get_db()
    
    query = "SELECT * FROM loans WHERE 1=1"
    params = []
    
    if status_filter:
        query += " AND loan_status = ?"
        params.append(status_filter)
    if channel_filter:
        query += " AND loan_channel = ?"
        params.append(channel_filter)
    if search:
        query += " AND (loan_number LIKE ? OR borrower_last LIKE ? OR broker_company LIKE ?)"
        params.extend([f'%{search}%', f'%{search}%', f'%{search}%'])
    
    query += " ORDER BY registered_date DESC LIMIT 100"
    
    loans = conn.execute(query, params).fetchall()
    
    # Get distinct statuses for filter dropdown
    statuses = conn.execute("SELECT DISTINCT loan_status FROM loans WHERE loan_status IS NOT NULL").fetchall()
    channels = conn.execute("SELECT DISTINCT loan_channel FROM loans WHERE loan_channel IS NOT NULL").fetchall()
    
    conn.close()
    
    return render_template('loans.html', 
                          loans=loans, 
                          statuses=[s['loan_status'] for s in statuses],
                          channels=[c['loan_channel'] for c in channels],
                          status_filter=status_filter,
                          channel_filter=channel_filter,
                          search=search)


@bp.route('/api/loans')
@login_required
def api_list_loans():
    """API endpoint to list loans."""
    conn = get_db()
    
    try:
        limit = max(1, min(100, int(request.args.get('limit', 50))))
        offset = max(0, int(request.args.get('offset', 0)))
    except ValueError:
        limit, offset = 50, 0

    loans = conn.execute("SELECT * FROM loans ORDER BY registered_date DESC LIMIT ? OFFSET ?", (limit, offset)).fetchall()
    total = conn.execute("SELECT COUNT(*) FROM loans").fetchone()[0]
    
    conn.close()
    
    return jsonify({
        'loans': [dict(loan) for loan in loans],
        'total': total,
        'limit': limit,
        'offset': offset
    })


@bp.route('/api/loans/<int:loan_id>')
@login_required
def api_get_loan(loan_id):
    """Get a single loan."""
    conn = get_db()
    loan = conn.execute("SELECT * FROM loans WHERE id = ?", (loan_id,)).fetchone()
    
    if not loan:
        conn.close()
        return jsonify({'error': 'Loan not found'}), 404
    
    # Get events for this loan
    events = conn.execute("""
        SELECT event_type, details, created_at FROM loan_events 
        WHERE loan_id = ? ORDER BY created_at DESC LIMIT 20
    """, (loan_id,)).fetchall()
    
    conn.close()
    
    return jsonify({
        'loan': dict(loan),
        'events': [dict(e) for e in events]
    })


def _event(cursor, loan_id, event_type, details):
    cursor.execute("INSERT INTO loan_events (loan_id, event_type, details, actor_user_id) VALUES (?, ?, ?, ?)",
                   (loan_id, event_type, details, session.get('user_id')))


@bp.route('/api/loans', methods=['POST'])
@roles_required('admin', 'analyst')
def api_create_loan():
    """Create a new loan after validating every supplied field."""
    data = request.get_json(silent=True)
    clean, errors = loan_rules.validate(data, require_loan_number=True)
    if errors:
        return jsonify({'error': 'Validation failed', 'details': errors}), 422
    clean.setdefault('loan_status', 'Loan Open')
    clean.setdefault('registered_date', date.today().isoformat())
    full = {field: clean.get(field) for field in loan_rules.FIELDS}
    problems = loan_rules.cross_field_errors(full)
    if problems:
        return jsonify({'error': 'Validation failed', 'details': problems}), 422

    columns = sorted(clean)
    conn = get_db()
    cursor = conn.cursor()
    try:
        cursor.execute(f"INSERT INTO loans ({', '.join(columns)}) VALUES ({', '.join('?' for _ in columns)})",
                       [clean[c] for c in columns])
        loan_id = cursor.lastrowid
        _event(cursor, loan_id, 'created', 'Loan created')
        audit_event('loan.created', 'loan', clean['loan_number'], 'new record', conn)
        conn.commit()
        return jsonify({'id': loan_id, 'message': 'Loan created'}), 201
    except sqlite3.IntegrityError:
        conn.rollback()
        return jsonify({'error': 'A loan with this loan number already exists.'}), 409
    finally:
        conn.close()


@bp.route('/api/loans/<int:loan_id>', methods=['PUT'])
@roles_required('admin', 'analyst')
def api_update_loan(loan_id):
    """Update a loan; field rules and cross-field rules are checked against the merged record."""
    data = request.get_json(silent=True)
    clean, errors = loan_rules.validate(data, allowed=loan_rules.EDITABLE)
    if errors:
        return jsonify({'error': 'Validation failed', 'details': errors}), 422
    if not clean:
        return jsonify({'error': 'No fields to update'}), 400

    conn = get_db()
    try:
        current = conn.execute("SELECT * FROM loans WHERE id = ?", (loan_id,)).fetchone()
        if current is None:
            return jsonify({'error': 'Loan not found'}), 404
        merged = {field: current[field] for field in loan_rules.FIELDS}
        merged.update(clean)
        problems = loan_rules.cross_field_errors(merged)
        if problems:
            return jsonify({'error': 'Validation failed', 'details': problems}), 422
        changed = {k: v for k, v in clean.items() if current[k] != v}
        if not changed:
            return jsonify({'message': 'No changes'})
        assignments = ', '.join(f"{field} = ?" for field in changed)
        cursor = conn.cursor()
        cursor.execute(f"UPDATE loans SET {assignments}, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
                       [*changed.values(), loan_id])
        # Record before/after values for traceability (field names only go to the audit log).
        diff = '; '.join(f"{k}: {current[k]!r} -> {v!r}" for k, v in sorted(changed.items()))
        _event(cursor, loan_id, 'updated', diff[:2000])
        audit_event('loan.updated', 'loan', current['loan_number'], 'fields=' + ','.join(sorted(changed)), conn)
        conn.commit()
        return jsonify({'message': 'Loan updated', 'changed': sorted(changed)})
    finally:
        conn.close()


@bp.route('/api/loans/<int:loan_id>/status', methods=['POST'])
@roles_required('admin', 'analyst')
def api_update_status(loan_id):
    """Change status only; reuses the same rules as a full update."""
    data = request.get_json(silent=True) or {}
    new_status = data.get('status')
    if new_status not in loan_rules.STATUSES:
        return jsonify({'error': 'Unknown loan status'}), 400
    conn = get_db()
    try:
        loan = conn.execute("SELECT * FROM loans WHERE id = ?", (loan_id,)).fetchone()
        if loan is None:
            return jsonify({'error': 'Loan not found'}), 404
        merged = {field: loan[field] for field in loan_rules.FIELDS}
        merged['loan_status'] = new_status
        problems = loan_rules.cross_field_errors(merged)
        if problems:
            return jsonify({'error': 'Validation failed', 'details': problems}), 422
        cursor = conn.cursor()
        cursor.execute("UPDATE loans SET loan_status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
                       (new_status, loan_id))
        _event(cursor, loan_id, 'status_change', f"{loan['loan_status']} -> {new_status}")
        audit_event('loan.status_changed', 'loan', loan['loan_number'], f"{loan['loan_status']}->{new_status}", conn)
        conn.commit()
        return jsonify({'message': 'Status updated'})
    finally:
        conn.close()


@bp.route('/api/loans/<int:loan_id>', methods=['DELETE'])
@roles_required('admin')
def api_delete_loan(loan_id):
    """Delete a loan (administrators only). Loan history cascades; the audit log keeps the record."""
    conn = get_db()
    try:
        loan = conn.execute("SELECT loan_number, loan_status, loan_amount FROM loans WHERE id = ?", (loan_id,)).fetchone()
        if loan is None:
            return jsonify({'error': 'Loan not found'}), 404
        conn.execute("DELETE FROM loans WHERE id = ?", (loan_id,))
        audit_event('loan.deleted', 'loan', loan['loan_number'],
                    f"status={loan['loan_status']};amount={loan['loan_amount']}", conn)
        conn.commit()
        return jsonify({'message': 'Loan deleted'})
    finally:
        conn.close()
