from django.db import connection
from questbanker_api.utils import get_current_user

def get_green_finance_summary(request):
    """
    Returns aggregated statistics for green finance loans:
    - Total number of loans
    - Total loan volume
    - Breakdown by indicator (Adaptation, Mitigation, Biodiversity Conservation)
      including both loan counts and total volumes.
    - NEW: 'breakdown_by_volume' for frontend use
    """

    organisation_id = get_current_user(request, "organisation_id", None)

    query = """
        SELECT 
           COUNT(DISTINCT l.id) AS total_loans,
           COALESCE(SUM(l.loan_amount), 0) AS total_volume,

           -- Adaptation
           COALESCE(SUM(CASE WHEN c.name = 'Adaptation' THEN 1 ELSE 0 END), 0) AS adaptation_count,
           COALESCE(SUM(CASE WHEN c.name = 'Adaptation' THEN l.loan_amount ELSE 0 END), 0) AS adaptation_volume,

           -- Mitigation
           COALESCE(SUM(CASE WHEN c.name = 'Mitigation' THEN 1 ELSE 0 END), 0) AS mitigation_count,
           COALESCE(SUM(CASE WHEN c.name = 'Mitigation' THEN l.loan_amount ELSE 0 END), 0) AS mitigation_volume,

           -- Biodiversity Conservation
           COALESCE(SUM(CASE WHEN c.name = 'Biodiversity Conservation' THEN 1 ELSE 0 END), 0) AS biodiversity_count,
           COALESCE(SUM(CASE WHEN c.name = 'Biodiversity Conservation' THEN l.loan_amount ELSE 0 END), 0) AS biodiversity_volume

        FROM loan_applications l
        JOIN classification_item_loans cil ON cil.loan_id = l.id
        JOIN classification_items ci ON ci.id = cil.classification_item_id
        JOIN classifications c ON c.id = ci.classification_id
        WHERE l.organisation_id = %s;
    """

    with connection.cursor() as cursor:
        cursor.execute(query, [organisation_id])
        row = cursor.fetchone()

    # Build the response
    response = {
        "total_loans": row[0],
        "total_volume": float(row[1]),
        "adaptation_count": row[2],
        "mitigation_count": row[4],
        "biodiversity_conservation_count": row[6],
        # ✅ New: Volume breakdown array for frontend use
        "breakdown_by_volume": [
            {
                "indicator": "Adaptation",
                "count": row[2],
                "volume": float(row[3]),
            },
            {
                "indicator": "Mitigation",
                "count": row[4],
                "volume": float(row[5]),
            },
            {
                "indicator": "Biodiversity Conservation",
                "count": row[6],
                "volume": float(row[7]),
            },
        ],
    }

    return response