from loans.models import *
from loans.serializers import LoansRepaymentViewSerializer, LoanPaymentTransactionSerializer
from django.db import connection, connections
from django.core.cache import cache
from datetime import datetime
import threading

# Thread-local storage for connection reuse
_thread_locals = threading.local()

def invalidate_loan_savings_cache(organisation_id=None):
    """Invalidate loan savings report cache when loans are updated"""
    try:
        if organisation_id:
            # Clear specific organisation cache patterns
            cache_keys = []
            if hasattr(cache, '_cache'):
                cache_keys = [key for key in cache._cache.keys() if 'loan_savings_' in str(key) and str(organisation_id) in str(key)]
            for key in cache_keys:
                cache.delete(key)
        else:
            # Clear all loan savings cache
            cache_keys = []
            if hasattr(cache, '_cache'):
                cache_keys = [key for key in cache._cache.keys() if 'loan_savings_' in str(key) or 'filter_loan_savings_' in str(key)]
            for key in cache_keys:
                cache.delete(key)
    except:
        # Fallback: clear all cache
        cache.clear()

def get_prepared_statement(query_type, params):
    """Get or create prepared statement for common queries"""
    if not hasattr(_thread_locals, 'prepared_statements'):
        _thread_locals.prepared_statements = {}
    
    if query_type not in _thread_locals.prepared_statements:
        with connections['default'].cursor() as cursor:
            if query_type == 'loan_repayment':
                cursor.execute(f"PREPARE loan_repayment_stmt AS SELECT * FROM loan_repayment_func($1) WHERE $2;")
            elif query_type == 'loan_repayment_range':
                cursor.execute(f"PREPARE loan_repayment_range_stmt AS SELECT * FROM loan_repayment_func($1, $2) WHERE $3;")
        _thread_locals.prepared_statements[query_type] = True
    
    return query_type

def fetch_loan_savings_bulk(base_loan_filter, extra_sql_filters, as_at, extra_report_filter, group_by_fields):
    """
    Fetch all loans in ONE query and return them grouped by the specified fields.
    Pre-fetches loan dues and recovery amounts in bulk to avoid N+1 queries in the serializer.
    group_by_fields: list of column names to group by, e.g. ['loan_officer_id', 'customer_type_id']
    """
    report_type = extra_report_filter.get('report_type', None)
    outstanding_principal_bal = extra_report_filter.get('outstanding_principal_bal', 0)

    sql = json_to_sql_where(base_loan_filter)

    # Single query: loan data + waivers + dues + recovery in one shot
    waiver_query = f"""
        SELECT lr.*,
               (lr.total_principal_expected - lr.princ_paid) as adjusted_princ_bal,
               (lr.total_interest_expected - lr.int_paid - COALESCE(iw.total_interest_waived, 0)) as adjusted_int_bal,
               (lr.total_penalty - lr.penalty_paid - COALESCE(pw.total_penalty_waived, 0)) as adjusted_penalty_bal,
               COALESCE(iw.total_interest_waived, 0) as current_interest_waived,
               COALESCE(pw.total_penalty_waived, 0) as current_penalty_waived
        FROM loan_repayment_func('{as_at}') lr
        LEFT JOIN (
            SELECT loan_application_id, SUM(amount) as total_interest_waived
            FROM loan_interest_waivered
            WHERE date_added::date <= '{as_at}'
            GROUP BY loan_application_id
        ) iw ON lr.id = iw.loan_application_id
        LEFT JOIN (
            SELECT loan_application_id, SUM(amount) as total_penalty_waived
            FROM loan_penalty_waivered
            WHERE date_added::date <= '{as_at}'
            GROUP BY loan_application_id
        ) pw ON lr.id = pw.loan_application_id
        WHERE {sql} {extra_sql_filters}
              AND (
                  lr.status != 'cleared_off'
                  OR (
                      (lr.total_principal_expected - lr.princ_paid) > 0.01
                      OR (lr.total_interest_expected - lr.int_paid - COALESCE(iw.total_interest_waived, 0)) > 0.01
                      OR (lr.total_penalty - lr.penalty_paid - COALESCE(pw.total_penalty_waived, 0)) > 0.01
                  )
              )
    """

    loans = LoansRepaymentView.objects.raw(waiver_query)
    loans_list = list(loans)

    if not loans_list:
        return {}

    loan_ids = [loan.id for loan in loans_list]

    # Pre-fetch loan dues in bulk (replaces loan_schedules_dues N+1)
    bulk_dues = _bulk_loan_dues(loan_ids, as_at)

    # Pre-fetch recovery amounts in bulk (replaces per-loan LoanMainTransactions query)
    from django.db.models import Sum as DSum
    recovery_map = dict(
        LoanMainTransactions.objects.filter(
            loan_application_id__in=loan_ids,
            transaction_type='LoanRecovery'
        ).values('loan_application_id').annotate(total=DSum('amount')).values_list('loan_application_id', 'total')
    )

    for loan in loans_list:
        if hasattr(loan, 'adjusted_int_bal'):
            loan.int_bal = loan.adjusted_int_bal
            loan.total_bal = (loan.total_principal_expected - loan.princ_paid) + loan.adjusted_int_bal
        if hasattr(loan, 'current_interest_waived'):
            loan.interest_waivered = loan.current_interest_waived
        if hasattr(loan, 'current_penalty_waived'):
            loan.penalty_waivered = loan.current_penalty_waived

    serializer = LoansRepaymentViewSerializer(
        loans_list,
        many=True,
        context={
            'type': report_type,
            'as_at': as_at,
            'start_date': None,
            'outstanding_principal_bal': outstanding_principal_bal,
            'bulk_dues': bulk_dues,
            'bulk_recovery': recovery_map,
        }
    )
    serialized = serializer.data

    # Group by the requested fields
    grouped = {}
    for loan_data in serialized:
        key = tuple(loan_data.get(f) for f in group_by_fields)
        grouped.setdefault(key, []).append(loan_data)

    return grouped


def _bulk_loan_dues(loan_ids, as_at):
    """Compute dues for all loans in two queries instead of 4-6 per loan."""
    from django.db.models import Sum as DSum

    # All due schedules up to as_at
    schedules = LoanRepaymentSchedule.objects.filter(
        loan_application_id__in=loan_ids,
        expected_date__date__lte=as_at,
        status='active',
    ).values('loan_application_id', 'id', 'principal_expected', 'interest_expected')

    schedule_ids = [s['id'] for s in schedules]

    # Payments per schedule
    payments = LoanPayments.objects.filter(
        loan_repayment_schedule_id__in=schedule_ids,
        payment_status='normal',
        payment_date__date__lte=as_at,
    ).values('loan_application_id', 'loan_repayment_schedule_id').annotate(
        total_princ=DSum('princ_paid'),
        total_int=DSum('int_paid'),
        total_penalty=DSum('penalty_paid'),
    )
    payment_map = {(p['loan_application_id'], p['loan_repayment_schedule_id']): p for p in payments}

    # Interest waivers per schedule
    waivers = LoanInterestWaivered.objects.filter(
        loan_repayment_schedule_id__in=schedule_ids,
        date_added__date__lte=as_at,
    ).values('loan_application_id', 'loan_repayment_schedule_id').annotate(
        total_waived=DSum('amount')
    )
    waiver_map = {(w['loan_application_id'], w['loan_repayment_schedule_id']): w['total_waived'] for w in waivers}

    # Penalty totals per loan
    penalties = LoanPenalty.objects.filter(
        loan_application_id__in=loan_ids,
        date_added__date__lte=as_at,
    ).values('loan_application_id').annotate(total=DSum('amount'))
    penalty_map = {p['loan_application_id']: p['total'] or 0 for p in penalties}

    penalty_paid_map = dict(
        LoanPayments.objects.filter(
            loan_application_id__in=loan_ids,
            payment_status='normal',
            payment_date__date__lte=as_at,
        ).values('loan_application_id').annotate(total=DSum('penalty_paid')).values_list('loan_application_id', 'total')
    )

    penalty_waived_map = dict(
        LoanPenaltyWaivered.objects.filter(
            loan_application_id__in=loan_ids,
            date_added__date__lte=as_at,
        ).values('loan_application_id').annotate(total=DSum('amount')).values_list('loan_application_id', 'total')
    )

    dues = {}
    for s in schedules:
        lid = s['loan_application_id']
        sid = s['id']
        pay = payment_map.get((lid, sid), {})
        princ_paid = pay.get('total_princ') or 0
        int_paid = pay.get('total_int') or 0
        waived = waiver_map.get((lid, sid), 0) or 0

        entry = dues.setdefault(lid, {'princ_due': 0, 'interest_due': 0, 'penalty_due': 0, 'total_due': 0})
        entry['princ_due'] += max(s['principal_expected'] - princ_paid, 0)
        entry['interest_due'] += max(s['interest_expected'] - int_paid - waived, 0)

    for lid in loan_ids:
        entry = dues.setdefault(lid, {'princ_due': 0, 'interest_due': 0, 'penalty_due': 0, 'total_due': 0})
        pen_total = (penalty_map.get(lid) or 0)
        pen_paid = (penalty_paid_map.get(lid) or 0)
        pen_waived = (penalty_waived_map.get(lid) or 0)
        entry['penalty_due'] = max(pen_total - pen_paid - pen_waived, 0)
        entry['total_due'] = entry['princ_due'] + entry['interest_due'] + entry['penalty_due']

    return dues


def filter_loans_balances(loan_filter = None, extra_sql_filters = '', start_date=None, end_date = None, extra_report_filter = None ):
    report_type = extra_report_filter.get('report_type', None)
    outstanding_principal_bal = extra_report_filter.get('outstanding_principal_bal', 0)
    if not end_date:
        return []

    # start = datetime.strptime(end_date, "%Y-%m-%d") 
    # end = start - timedelta(days=1) 
    # end_date = end.strftime('%Y-%m-%d')
    as_at = end_date
    sql = json_to_sql_where(loan_filter)
    if start_date:
        loans = LoansRepaymentView.objects.raw("SELECT * FROM loan_repayment_func('" + as_at + "', '"+ start_date + "') WHERE "+ sql + extra_sql_filters + ";" )
    else:
        loans = LoansRepaymentView.objects.raw("SELECT * FROM loan_repayment_func('" + as_at + "') WHERE "+ sql + extra_sql_filters + ";" )
    
    serializer = LoansRepaymentViewSerializer(loans,
                many=True, 
                context={
                            'type': report_type,
                            "as_at":end_date,
                            "start_date":start_date,
                            "outstanding_principal_bal":outstanding_principal_bal
                        })
    loans_response = serializer.data

    return loans_response

def filter_loan_savings_with_waivers(loan_filter = None, extra_sql_filters = '', start_date=None, end_date = None, extra_report_filter = None ):
    """Enhanced loan savings filter that properly handles waivers for cleared loans"""
    # Create cache key for this specific query
    cache_key = f"filter_loan_savings_waivers_{hash(str(loan_filter))}_{hash(extra_sql_filters)}_{end_date}_{start_date}"
    cached_result = cache.get(cache_key)
    if cached_result:
        return cached_result
    
    report_type = extra_report_filter.get('report_type', None)
    outstanding_principal_bal = extra_report_filter.get('outstanding_principal_bal', 0)
    if not end_date:
        return []

    as_at = end_date
    sql = json_to_sql_where(loan_filter)
    
    # Real-time waiver calculation query
    waiver_aware_query = f"""
        SELECT lr.*, 
               (lr.total_principal_expected - lr.princ_paid) as adjusted_princ_bal,
               (lr.total_interest_expected - lr.int_paid - COALESCE(iw.total_interest_waived, 0)) as adjusted_int_bal,
               (lr.total_penalty - lr.penalty_paid - COALESCE(pw.total_penalty_waived, 0)) as adjusted_penalty_bal,
               COALESCE(iw.total_interest_waived, 0) as current_interest_waived,
               COALESCE(pw.total_penalty_waived, 0) as current_penalty_waived,
               CASE 
                   WHEN lr.status = 'cleared_off' 
                        AND (lr.total_principal_expected - lr.princ_paid) <= 0.01
                        AND (lr.total_interest_expected - lr.int_paid - COALESCE(iw.total_interest_waived, 0)) <= 0.01
                        AND (lr.total_penalty - lr.penalty_paid - COALESCE(pw.total_penalty_waived, 0)) <= 0.01
                   THEN 0
                   ELSE lr.arrear_days
               END as adjusted_arrear_days
        FROM loan_repayment_func('{as_at}') lr
        LEFT JOIN (
            SELECT loan_application_id, SUM(amount) as total_interest_waived
            FROM loan_interest_waivered 
            WHERE date_added::date <= '{as_at}'
            GROUP BY loan_application_id
        ) iw ON lr.id = iw.loan_application_id
        LEFT JOIN (
            SELECT loan_application_id, SUM(amount) as total_penalty_waived
            FROM loan_penalty_waivered 
            WHERE date_added::date <= '{as_at}'
            GROUP BY loan_application_id
        ) pw ON lr.id = pw.loan_application_id
        WHERE {sql} {extra_sql_filters}
              AND (
                  lr.status != 'cleared_off' 
                  OR (
                      (lr.total_principal_expected - lr.princ_paid) > 0.01
                      OR (lr.total_interest_expected - lr.int_paid - COALESCE(iw.total_interest_waived, 0)) > 0.01
                      OR (lr.total_penalty - lr.penalty_paid - COALESCE(pw.total_penalty_waived, 0)) > 0.01
                  )
              )
    """
    
    if start_date:
        waiver_aware_query = f"""
            SELECT lr.*, 
                   (lr.total_principal_expected - lr.princ_paid) as adjusted_princ_bal,
                   (lr.total_interest_expected - lr.int_paid - COALESCE(iw.total_interest_waived, 0)) as adjusted_int_bal,
                   (lr.total_penalty - lr.penalty_paid - COALESCE(pw.total_penalty_waived, 0)) as adjusted_penalty_bal,
                   COALESCE(iw.total_interest_waived, 0) as current_interest_waived,
                   COALESCE(pw.total_penalty_waived, 0) as current_penalty_waived,
                   CASE 
                       WHEN lr.status = 'cleared_off' 
                            AND (lr.total_principal_expected - lr.princ_paid) <= 0.01
                            AND (lr.total_interest_expected - lr.int_paid - COALESCE(iw.total_interest_waived, 0)) <= 0.01
                            AND (lr.total_penalty - lr.penalty_paid - COALESCE(pw.total_penalty_waived, 0)) <= 0.01
                       THEN 0
                       ELSE lr.arrear_days
                   END as adjusted_arrear_days
            FROM loan_repayment_func('{as_at}', '{start_date}') lr
            LEFT JOIN (
                SELECT loan_application_id, SUM(amount) as total_interest_waived
                FROM loan_interest_waivered 
                WHERE date_added::date <= '{as_at}' AND date_added::date >= '{start_date}'
                GROUP BY loan_application_id
            ) iw ON lr.id = iw.loan_application_id
            LEFT JOIN (
                SELECT loan_application_id, SUM(amount) as total_penalty_waived
                FROM loan_penalty_waivered 
                WHERE date_added::date <= '{as_at}' AND date_added::date >= '{start_date}'
                GROUP BY loan_application_id
            ) pw ON lr.id = pw.loan_application_id
            WHERE {sql} {extra_sql_filters}
                  AND (
                      lr.status != 'cleared_off' 
                      OR (
                          (lr.total_principal_expected - lr.princ_paid) > 0.01
                          OR (lr.total_interest_expected - lr.int_paid - COALESCE(iw.total_interest_waived, 0)) > 0.01
                          OR (lr.total_penalty - lr.penalty_paid - COALESCE(pw.total_penalty_waived, 0)) > 0.01
                      )
                  )
        """
    
    loans = LoansRepaymentView.objects.raw(waiver_aware_query)
    
    # Process loans to update interest balance with waiver-adjusted values
    loans_list = list(loans)
    for loan in loans_list:
        if hasattr(loan, 'adjusted_int_bal'):
            # Calculate principal balance
            princ_bal = loan.total_principal_expected - loan.princ_paid
            # Update interest balance with waiver adjustment
            loan.int_bal = loan.adjusted_int_bal
            # Update total balance to reflect waived interest
            loan.total_bal = princ_bal + loan.adjusted_int_bal
        if hasattr(loan, 'current_interest_waived'):
            loan.interest_waivered = loan.current_interest_waived
        if hasattr(loan, 'current_penalty_waived'):
            loan.penalty_waivered = loan.current_penalty_waived
    
    serializer = LoansRepaymentViewSerializer(loans_list, 
                many=True, 
                context={
                            'type': report_type,
                            "as_at":end_date,
                            "start_date":start_date,
                            "outstanding_principal_bal":outstanding_principal_bal
                        })
    loans_response = serializer.data
    
    # Cache for 30 seconds only for real-time updates
    cache.set(cache_key, loans_response, 30)
    return loans_response

def filter_loan_savings(loan_filter = None, extra_sql_filters = '', start_date=None, end_date = None, extra_report_filter = None ):
    # Create cache key for this specific query
    cache_key = f"filter_loan_savings_{hash(str(loan_filter))}_{hash(extra_sql_filters)}_{end_date}_{start_date}"
    cached_result = cache.get(cache_key)
    if cached_result:
        return cached_result
    
    report_type = extra_report_filter.get('report_type', None)
    outstanding_principal_bal = extra_report_filter.get('outstanding_principal_bal', 0)
    if not end_date:
        return []

    as_at = end_date
    sql = json_to_sql_where(loan_filter)
    
    # Optimize PostgreSQL settings for this query (session-level only)
    with connections['default'].cursor() as cursor:
        cursor.execute("SET work_mem = '64MB';")
        cursor.execute("SET enable_seqscan = off;")
        cursor.execute("SET random_page_cost = 1.1;")
    
    # For loan_savings_report, use the waiver-aware version
    if report_type == 'loan_savings_report':
        return filter_loan_savings_with_waivers(loan_filter, extra_sql_filters, start_date, end_date, extra_report_filter)
    
    if start_date:
        query = "SELECT * FROM loan_repayment_func('" + as_at + "', '"+ start_date + "') WHERE "+ sql + extra_sql_filters + ";"
    else:
        query = "SELECT * FROM loan_repayment_func('" + as_at + "') WHERE "+ sql + extra_sql_filters + ";"
    
    loans = LoansRepaymentView.objects.raw(query)
    
    serializer = LoansRepaymentViewSerializer(loans, 
                many=True, 
                context={
                            'type': report_type,
                            "as_at":end_date,
                            "start_date":start_date,
                            "outstanding_principal_bal":outstanding_principal_bal
                        })
    loans_response = serializer.data
    
    # Cache for 30 seconds only for real-time updates
    cache.set(cache_key, loans_response, 30)
    return loans_response

def filter_loans_repayment(loan_filter = None, extra_sql_filters = '', start_date=None, end_date = None, extra_report_filter = None ):
    loans_response = []

    if loan_filter:

        filters = loan_filter.copy()

        filters['payment_date__date__gte'] = start_date
        filters['payment_date__date__lte'] = end_date
        filters['transaction_status'] = 'normal'

        loans_payments = LoanPaymentTransaction.objects.filter(
            **filters
        ).order_by('loan_application')

        serializer = LoanPaymentTransactionSerializer(loans_payments, many=True)
        loans_response = serializer.data

    return loans_response
        
def get_loan_balances(end_date, extra_filter, start_date=None):
    as_at = end_date
    sql = "SELECT sum(total_principal_expected) as total_principal_expected, sum(princ_paid) as total_princ_paid  FROM loan_repayment_func('" + as_at + "') WHERE status='disbursed' AND is_deleted=False AND " + extra_filter + ";"
    if start_date:
        start_date = start_date
        sql = "SELECT sum(total_principal_expected) as total_principal_expected, sum(princ_paid) as total_princ_paid  FROM loan_repayment_func('" + as_at + "', '" + start_date + "') WHERE status='disbursed' AND is_deleted=False AND " + extra_filter + ";"

    cursor = connection.cursor()
    cursor.execute(sql)
    row = cursor.fetchone()
    return { "princ_bal": row[0] - row[1] } if (row[0] is not None and row[1] is not None) else { "princ_bal": 0 }


def json_to_sql_where(json_filter):
    if not json_filter:
        return "1=1"
    
    conditions = []
    
    for key, value in json_filter.items():
        if isinstance(value, (str, int, float)):
            if isinstance(value, str):
                conditions.append(f"{key} = '{value}'")
            else:
                conditions.append(f"{key} = {value}")
        elif isinstance(value, list) and value:  # Check if list is not empty
            if all(isinstance(item, (int, float)) or str(item).isdigit() for item in value):
                conditions.append(f"{key} IN ({','.join(map(str, value))})")
            else:
                quoted_values = "','".join(str(item) for item in value)
                conditions.append(f"{key} IN ('{quoted_values}')")
    
    return " AND ".join(conditions) if conditions else "1=1"

# Revamp new  
def convert_json_to_sql_where(json_filter):
    conditions = []

    for key, value in json_filter.items():
        # Handle single values (str, int, float)
        if isinstance(value, (str, int, float)):
            if isinstance(value, str) and not str(value).isdigit():
                conditions.append(f"{key} = ''{value}''")
            else:
                conditions.append(f"{key} = {value}")

        # Handle lists — skip empty lists entirely (no condition = no filter = all rows)
        elif isinstance(value, list) and len(value) > 0:
            if all(isinstance(item, (int, float)) or str(item).isdigit() for item in value):
                conditions.append(f"{key} IN ({','.join(map(str, value))})")
            else:
                quoted_values = ','.join(f"''{str(item)}''" for item in value)
                conditions.append(f"{key} IN ({quoted_values})")

    where_clause = " AND ".join(conditions)
    return where_clause


def get_loans_portfolio(organisation_id, filters, as_at):
    with connection.cursor() as cursor:
        query = f"""
                    WITH loan_data AS (
                        SELECT
                            princ_expected - princ_paid AS princ_bal
                        FROM
                            public.get_loan_tracking_data({organisation_id}, '{as_at}', '{filters}')
                    )
                    SELECT
                        SUM(princ_bal) AS total_princ_bal
                    FROM
                        loan_data;
                """
        cursor.execute(query)
        row = cursor.fetchone()
        return row[0] if row[0] else 0

def get_loans_total_due_amount(organisation_id, filters, start_date, as_at):
    with connection.cursor() as cursor:
        query = f"""
                    WITH loan_data AS (
                        SELECT
                            princ_due + int_due + sch_princ_expected + sch_int_expected AS total_due
                        FROM
                            public.get_loan_dues_repayment_data({organisation_id}, '{start_date}', '{as_at}', '{filters}')
                    )
                    SELECT
                        SUM(total_due) AS total_amount_due
                    FROM
                        loan_data;
                """
        cursor.execute(query)
        row = cursor.fetchone()
        return row[0] if row[0] else 0


def validate_date(date_str):
    try:
        # Parse date from the string
        date = datetime.strptime(date_str, '%Y-%m-%d').date()
        # Date is valid
        return True

    except (ValueError, TypeError):
        # Invalid date format or None
        return False