from django.db import connection
from rest_framework.response import Response
from questbanker_api.utils import get_current_user


def utilization_report(request):
    organisation_id = get_current_user(request, 'organisation_id', None) 
    branch_id       = get_current_user(request, 'organisation_branch_id', None)
    start = request.data.get('start')
    end = request.data.get('end')

    query = f"""
    WITH loan_payments_summary AS (
        SELECT 
            loan_application_id,
            COALESCE(SUM(int_paid::numeric), 0.00) AS total_int_paid,
            COALESCE(SUM(princ_paid::numeric), 0.00) AS total_princ_paid,
            COALESCE(SUM(penalty_paid::numeric), 0.00) AS total_penalty_paid
        FROM loan_payments
        WHERE payment_status <> 'reversed'
        GROUP BY loan_application_id
    ),
    loan_interest_waivered_summary AS (
        SELECT 
            loan_application_id,
            COALESCE(SUM(amount), 0) AS total_interest_waivered
        FROM loan_interest_waivered
        GROUP BY loan_application_id
    ),
    loan_penalty_waivered_summary AS (
        SELECT 
            loan_application_id,
            COALESCE(SUM(amount), 0) AS total_penalty_waivered
        FROM loan_penalty_waivered
        GROUP BY loan_application_id
    ),
    loan_penalty_summary AS (
        SELECT 
            loan_application_id,
            COALESCE(SUM(amount), 0) AS total_penalty
        FROM loan_penalty
        GROUP BY loan_application_id
    ),
    loan_expected_payments AS (
        SELECT 
            loan_application_id,
            COALESCE(SUM(total_payment), 0) AS total_expected_payment
        FROM loan_repayments_schedule
        WHERE expected_date <=
            CASE 
                WHEN '2024-11-30'::date > NOW() THEN NOW() 
                ELSE '2024-11-30'::date 
            END
        GROUP BY loan_application_id
    )
    SELECT 
        L.id, L.loan_amount, L.date_added, L.reason, 
        C.name AS customer_name, C.gender, C.member_number, C.old_member_number, 
        A.region, L.int_rate, L.loan_period, L.period_type, 
        D.loan_disbursement_date, D.heading AS disbursement_desc, 
        B.name AS branch_name,
        L.status, D.loan_amount AS disbursed_amt,
        (SELECT expected_date 
         FROM loan_repayments_schedule 
         WHERE loan_application_id = L.id 
         ORDER BY id DESC 
         LIMIT 1) AS maturity_date,
        O.name AS organisation_name, S.name AS sector_name,
        --
        (
                COALESCE(EP.total_expected_payment, 0) - 
                (COALESCE(LP.total_int_paid, 0) + COALESCE(LP.total_princ_paid, 0) + COALESCE(LP.total_penalty_paid, 0))
            ) AS amount_in_arrears,
            CASE 
                WHEN 
                    COALESCE(EP.total_expected_payment, 0) - 
                    (COALESCE(LP.total_int_paid, 0) + COALESCE(LP.total_princ_paid, 0) + COALESCE(LP.total_penalty_paid, 0)) > 0 
                THEN GREATEST(
                    DATE_PART('day', 
                        CASE 
                            WHEN '2024-11-30'::date > NOW() THEN NOW() 
                            ELSE '2024-11-30'::date 
                        END - 
                        (SELECT MAX(expected_date) 
                        FROM loan_repayments_schedule 
                        WHERE loan_application_id = L.id)
                    ), 
                    0
                )
                ELSE 0
            END AS days_in_arrears,
        
        --
        (
            COALESCE(SUM(LS.total_payment), 0) - 
            COALESCE(IW.total_interest_waivered, 0) - 
            COALESCE(PW.total_penalty_waivered, 0) - 
            (COALESCE(LP.total_int_paid, 0) + COALESCE(LP.total_princ_paid, 0) + COALESCE(LP.total_penalty_paid, 0)) +
            COALESCE(PS.total_penalty, 0)
        ) AS loan_balance,
        CASE 
            WHEN L.period_type = 'd' THEN ROUND((L.loan_period / 30.0), 1) || 'm'
            WHEN L.period_type = 'w' THEN ROUND((L.loan_period / 4.345), 2) || 'm'
            WHEN L.period_type = 'y' THEN (L.loan_period * 12) || 'm'
            ELSE L.loan_period || ' Months'
        END AS duration_in_months
    FROM loan_applications L
    INNER JOIN customer C ON C.id = L.customer_id
    INNER JOIN loan_application_disbursement D ON D.loan_application_id = L.id
    INNER JOIN organisation_branch B ON B.id = L.organisation_branch_id 
    INNER JOIN organisation O ON O.id = B.branch_organisation_id
    LEFT JOIN customer_address A ON A.customer_id = C.id
    LEFT JOIN loan_sectors S ON S.id = L.loan_sector_id
    LEFT JOIN loan_repayments_schedule LS ON LS.loan_application_id = L.id
    LEFT JOIN loan_payments_summary LP ON LP.loan_application_id = L.id
    LEFT JOIN loan_interest_waivered_summary IW ON IW.loan_application_id = L.id
    LEFT JOIN loan_penalty_waivered_summary PW ON PW.loan_application_id = L.id
    LEFT JOIN loan_penalty_summary PS ON PS.loan_application_id = L.id
    LEFT JOIN loan_expected_payments EP ON EP.loan_application_id = L.id
    WHERE O.id = '{organisation_id}'
    AND D.loan_disbursement_date BETWEEN '{start}' AND '{end}'
    GROUP BY 
        L.id, C.name, C.gender, C.member_number, C.old_member_number, A.region, L.int_rate, 
        L.loan_period, L.period_type, D.loan_disbursement_date, D.heading, L.status, 
        D.loan_amount, O.name, S.name, IW.total_interest_waivered, PW.total_penalty_waivered, 
        LP.total_int_paid, LP.total_princ_paid, LP.total_penalty_paid, PS.total_penalty, B.name,
        EP.total_expected_payment 
    ORDER BY D.loan_disbursement_date DESC;
    """
    with connection.cursor() as cursor:
        cursor.execute(query)
        columns = [col[0] for col in cursor.description]
        results = [dict(zip(columns, row)) for row in cursor.fetchall()]
    
    # Return the results as JSON
    return Response({"results": results, "count":len(results)})