-- Ready-to-use query with GROUP BY fix
-- Set variables first, then run the query
-- Example:
-- SET @branch_code = 'nuevaecija';
-- SET @as_of_date = '2025-11-14';

SET @branch_code = 'nuevaecija';
SET @as_of_date = '2025-11-14';

SELECT 
    lt.accountno,
    lt.BRANCHCODE,
    MIN(lt.collector) AS collector
FROM 
    lms_jk_kprime.loan_transactions lt
WHERE 
    lt.status <> 'Cancelled'
    AND (@branch_code IS NULL OR @branch_code = '' OR lt.BRANCHCODE = @branch_code)
    AND (
        COALESCE((
            SELECT 
                SUM(
                    CASE
                        WHEN ldd.due_date <= @as_of_date
                          AND ldd.due_date <= COALESCE(
                              (SELECT MAX(lt2.maturity) 
                               FROM lms_jk_kprime.loan_transactions lt2 
                               WHERE lt2.accountno = lt.accountno 
                                 AND lt2.branchcode = lt.branchcode 
                                 AND lt2.status <> 'Cancelled'), 
                              '9999-12-31'
                          )
                          AND (
                              EXISTS (
                                  SELECT 1 
                                  FROM lms_jk_kprime.loan_transactions lt3 
                                  WHERE lt3.accountno = lt.accountno 
                                    AND lt3.branchcode = lt.branchcode
                                    AND lt3.status <> 'Cancelled'
                                    AND (
                                        (LOWER(COALESCE(lt3.operational_due, '')) = 'monday'    AND DAYOFWEEK(ldd.due_date) = 2) OR
                                        (LOWER(COALESCE(lt3.operational_due, '')) = 'tuesday'   AND DAYOFWEEK(ldd.due_date) = 3) OR
                                        (LOWER(COALESCE(lt3.operational_due, '')) = 'wednesday' AND DAYOFWEEK(ldd.due_date) = 4) OR
                                        (LOWER(COALESCE(lt3.operational_due, '')) = 'thursday'  AND DAYOFWEEK(ldd.due_date) = 5) OR
                                        (LOWER(COALESCE(lt3.operational_due, '')) = 'friday'    AND DAYOFWEEK(ldd.due_date) = 6) OR
                                        (LOWER(COALESCE(lt3.operational_due, '')) = 'saturday'  AND DAYOFWEEK(ldd.due_date) = 7) OR
                                        (LOWER(COALESCE(lt3.operational_due, '')) = 'sunday'    AND DAYOFWEEK(ldd.due_date) = 1)
                                    )
                              )
                          )
                        THEN (
                            SELECT COALESCE(MAX(lt4.weekly_bill), 0) + COALESCE(MAX(lt4.weekly_savings), 0)
                            FROM lms_jk_kprime.loan_transactions lt4
                            WHERE lt4.accountno = lt.accountno 
                              AND lt4.branchcode = lt.branchcode
                              AND lt4.status <> 'Cancelled'
                        )
                        ELSE 0
                    END
                )
            FROM lms_jk_kprime.loan_due_dates ldd
            WHERE ldd.acctno = lt.accountno
              AND ldd.branchcode = lt.branchcode
        ), 0) > COALESCE((
            SELECT 
                SUM(p.AMOUNT)
            FROM lms_jk_kprime.loan_payments p
            WHERE p.accountno = lt.accountno
              AND p.amount > 0
              AND p.referenceno <> ''
              AND p.docstatus <> 'Cancelled'
              AND p.dateposted <= @as_of_date
        ), 0)
    )
GROUP BY 
    lt.accountno, 
    lt.BRANCHCODE
LIMIT 0, 1000;





