-- =====================================================
-- SQL Queries for getCollectionDetails Function
-- Equivalent to CollectionAgentController.getCollectionDetails
-- =====================================================

-- =====================================================
-- STEP 1: Get Agent Data
-- =====================================================
-- Get agent information by ID
-- SET: agent_id = 3, company_id = 15
SELECT 
    id,
    line,
    com_id,
    name,
    mobile_no
FROM collection_agent
WHERE id = 3;

-- =====================================================
-- STEP 2: Get Areas (if not provided in query)
-- =====================================================
-- Get distinct areas for agent's line and company
-- SET: company_id = 15
-- NOTE: Replace :agent_line with actual line value from Step 1 query result
-- Example: If agent's line is 'Line1', use: AND line = 'Line1'
SELECT DISTINCT area
FROM line_entry
WHERE com_id = 15
  AND line = (SELECT line FROM collection_agent WHERE id = 3)  -- Get line from agent
  AND area IS NOT NULL
  AND area != '';

-- =====================================================
-- STEP 3: Get All Loan Entries for Areas
-- =====================================================
-- Get all loan entries matching company and areas
SELECT 
    id,
    loan_id,
    com_id,
    loan_date,
    cust_id,
    cus_name,
    cust_mobile,
    cust_contact,
    aadhaar_no,
    cust_address,
    cust_address2,
    area,
    loan_type,
    loan_amt,
    dues,
    interest,
    interest_amt,
    ledger,
    due_schedule,
    createdOn,
    total_amt,
    per_due_amt,
    next_due_date,
    last_due_date,
    pre_interest,
    status
FROM loan_entry
WHERE com_id = 15
  AND area IN (
      SELECT DISTINCT area 
      FROM line_entry 
      WHERE com_id = 15 
        AND line = (SELECT line FROM collection_agent WHERE id = 3)
        AND area IS NOT NULL 
        AND area != ''
  )  -- Get areas from agent's line
  AND loan_id IS NOT NULL;

-- =====================================================
-- STEP 4: Get All Receipts for Loan IDs
-- =====================================================
-- Get all receipts for the loan IDs (ordered by loan_id, rec_date, created_at)
SELECT 
    id,
    com_id,
    loan_id,
    rec_no,
    rec_date,
    cust_id,
    cust_name,
    cust_mobile,
    num_dues,
    due_amnt,
    paid_amnt,
    paid_dues,
    balance_dues,
    pending_dues,
    next_date,
    remark,
    agent,
    created_at
FROM loan_receipt
WHERE com_id = 15
  AND loan_id IN (
      SELECT loan_id 
      FROM loan_entry 
      WHERE com_id = 15 
        AND area IN (
            SELECT DISTINCT area 
            FROM line_entry 
            WHERE com_id = 15 
              AND line = (SELECT line FROM collection_agent WHERE id = 3)
              AND area IS NOT NULL 
              AND area != ''
        )
        AND loan_id IS NOT NULL
  )  -- Get loan_ids from loan entries
ORDER BY loan_id ASC, rec_date ASC, created_at ASC;

-- =====================================================
-- STEP 5: Get Last Receipt for Each Loan
-- =====================================================
-- Get the most recent receipt for each loan (by rec_date)
SELECT 
    lr1.*
FROM loan_receipt lr1
INNER JOIN (
    SELECT 
        loan_id,
        MAX(rec_date) as max_rec_date
    FROM loan_receipt
    WHERE com_id = 15
      AND loan_id IN (
          SELECT loan_id 
          FROM loan_entry 
          WHERE com_id = 15 
            AND area IN (
                SELECT DISTINCT area 
                FROM line_entry 
                WHERE com_id = 15 
                  AND line = (SELECT line FROM collection_agent WHERE id = 3)
                  AND area IS NOT NULL 
                  AND area != ''
            )
            AND loan_id IS NOT NULL
      )
    GROUP BY loan_id
) lr2 ON lr1.loan_id = lr2.loan_id 
     AND lr1.rec_date = lr2.max_rec_date
WHERE lr1.com_id = 15
ORDER BY lr1.loan_id ASC, lr1.created_at DESC;

-- =====================================================
-- STEP 6: Get Today's Receipts
-- =====================================================
-- Get all receipts created today for the loan IDs
SELECT 
    id,
    com_id,
    loan_id,
    rec_no,
    rec_date,
    cust_id,
    cust_name,
    cust_mobile,
    num_dues,
    due_amnt,
    paid_amnt,
    paid_dues,
    balance_dues,
    pending_dues,
    next_date,
    remark,
    agent,
    created_at
FROM loan_receipt
WHERE com_id = 15
  AND loan_id IN (
      SELECT loan_id 
      FROM loan_entry 
      WHERE com_id = 15 
        AND area IN (
            SELECT DISTINCT area 
            FROM line_entry 
            WHERE com_id = 15 
              AND line = (SELECT line FROM collection_agent WHERE id = 3)
              AND area IS NOT NULL 
              AND area != ''
        )
        AND loan_id IS NOT NULL
  )
  AND rec_date = CURDATE()  -- or use specific date like '2024-01-15'
ORDER BY loan_id ASC, created_at ASC;

-- =====================================================
-- COMPREHENSIVE QUERY: Combined View with Filtering
-- =====================================================
-- This query combines loan entries with their last receipt and today's receipt info
-- Note: Complex calculations still need to be done in application code

WITH loan_entries AS (
    SELECT 
        le.*
    FROM loan_entry le
    WHERE le.com_id = 15
      AND le.area IN (
          SELECT DISTINCT area 
          FROM line_entry 
          WHERE com_id = 15 
            AND line = (SELECT line FROM collection_agent WHERE id = 3)
            AND area IS NOT NULL 
            AND area != ''
      )
      AND le.loan_id IS NOT NULL
),
last_receipts AS (
    SELECT 
        lr.*,
        ROW_NUMBER() OVER (
            PARTITION BY lr.loan_id 
            ORDER BY lr.rec_date DESC, lr.created_at DESC
        ) as rn
    FROM loan_receipt lr
    WHERE lr.com_id = 15
      AND lr.loan_id IN (SELECT loan_id FROM loan_entries)
),
today_receipts AS (
    SELECT 
        lr.*
    FROM loan_receipt lr
    WHERE lr.com_id = 15
      AND lr.loan_id IN (SELECT loan_id FROM loan_entries)
      AND lr.rec_date = CURDATE()  -- or use specific date like '2024-01-15'
),
filtered_loans AS (
    SELECT 
        le.*,
        lr.id as last_receipt_id,
        lr.rec_no as last_rec_no,
        lr.rec_date as last_rec_date,
        lr.due_amnt as last_due_amnt,
        lr.paid_amnt as last_paid_amnt,
        lr.paid_dues as last_paid_dues,
        lr.pending_dues as last_pending_dues,
        lr.balance_dues as last_balance_dues,
        lr.next_date as last_next_date,
        CASE WHEN tr.loan_id IS NOT NULL THEN 1 ELSE 0 END as has_receipt_today,
        tr.id as today_receipt_id,
        tr.paid_amnt as today_paid_amnt
    FROM loan_entries le
    LEFT JOIN last_receipts lr ON le.loan_id = lr.loan_id AND lr.rn = 1
    LEFT JOIN today_receipts tr ON le.loan_id = tr.loan_id
    WHERE 
        -- Include if due today
        (le.next_due_date = CURDATE())
        -- Include if overdue
        OR (le.next_due_date < CURDATE())
        -- Include if last receipt's next_date is due today or overdue
        OR (lr.next_date IS NOT NULL AND lr.next_date <= CURDATE())
        -- Include if has receipt today
        OR (tr.loan_id IS NOT NULL)
)
SELECT 
    fl.*,
    -- Calculate total collected amount (all receipts up to today)
    COALESCE(SUM(CASE 
        WHEN lr_all.rec_date <= CURDATE() 
        THEN lr_all.paid_amnt 
        ELSE 0 
    END), 0) as total_collected_amount,
    -- Calculate collected today amount
    COALESCE(SUM(CASE 
        WHEN lr_all.rec_date = CURDATE() 
        THEN lr_all.paid_amnt 
        ELSE 0 
    END), 0) as collected_today_amount
FROM filtered_loans fl
LEFT JOIN loan_receipt lr_all ON fl.loan_id = lr_all.loan_id 
    AND lr_all.com_id = 15
    AND lr_all.rec_date <= CURDATE()
GROUP BY 
    fl.id, fl.loan_id, fl.com_id, fl.loan_date, fl.cust_id, fl.cus_name,
    fl.cust_mobile, fl.cust_contact, fl.aadhaar_no, fl.cust_address,
    fl.cust_address2, fl.area, fl.loan_type, fl.loan_amt, fl.dues,
    fl.interest, fl.interest_amt, fl.ledger, fl.due_schedule, fl.createdOn,
    fl.total_amt, fl.per_due_amt, fl.next_due_date, fl.last_due_date,
    fl.pre_interest, fl.status,
    fl.last_receipt_id, fl.last_rec_no, fl.last_rec_date, fl.last_due_amnt,
    fl.last_paid_amnt, fl.last_paid_dues, fl.last_pending_dues,
    fl.last_balance_dues, fl.last_next_date, fl.has_receipt_today,
    fl.today_receipt_id, fl.today_paid_amnt
ORDER BY fl.loan_id;

-- =====================================================
-- SUMMARY QUERY: Get Summary Statistics
-- =====================================================
-- This query calculates summary statistics
-- Note: Some calculations (like total_amount_till_today) require complex date logic
-- that is better handled in application code

WITH filtered_customers AS (
    -- Use the comprehensive query above as a CTE
    -- This is a simplified version for summary
    SELECT 
        le.loan_id,
        le.per_due_amt,
        le.dues,
        le.loan_type,
        le.loan_date,
        le.next_due_date,
        lr.paid_amnt as last_paid_amnt,
        lr.next_date as last_next_date,
        lr.paid_dues as last_paid_dues,
        CASE WHEN tr.loan_id IS NOT NULL THEN 1 ELSE 0 END as has_receipt_today
    FROM loan_entry le
    LEFT JOIN (
        SELECT 
            loan_id,
            rec_date,
            paid_amnt,
            next_date,
            paid_dues,
            ROW_NUMBER() OVER (PARTITION BY loan_id ORDER BY rec_date DESC, created_at DESC) as rn
        FROM loan_receipt
        WHERE com_id = 15
    ) lr ON le.loan_id = lr.loan_id AND lr.rn = 1
    LEFT JOIN (
        SELECT DISTINCT loan_id
        FROM loan_receipt
        WHERE com_id = 15
          AND rec_date = CURDATE()
    ) tr ON le.loan_id = tr.loan_id
    WHERE le.com_id = 15
      AND le.area IN (
          SELECT DISTINCT area 
          FROM line_entry 
          WHERE com_id = 15 
            AND line = (SELECT line FROM collection_agent WHERE id = 3)
            AND area IS NOT NULL 
            AND area != ''
      )
      AND le.loan_id IS NOT NULL
      AND (
          le.next_due_date = CURDATE()
          OR le.next_due_date < CURDATE()
          OR (lr.next_date IS NOT NULL AND lr.next_date <= CURDATE())
          OR tr.loan_id IS NOT NULL
      )
)
SELECT 
    COUNT(*) as total_customers,
    SUM(le.per_due_amt) as total_collection_amount,
    SUM(
        CASE 
            WHEN tr.loan_id IS NOT NULL THEN tr.paid_amnt
            ELSE 0
        END
    ) as collected_today_amount,
    SUM(
        CASE 
            WHEN lr_all.rec_date <= CURDATE() THEN lr_all.paid_amnt
            ELSE 0
        END
    ) as total_collected_amount
FROM filtered_customers fc
LEFT JOIN loan_entry le ON fc.loan_id = le.loan_id
LEFT JOIN loan_receipt lr_all ON fc.loan_id = lr_all.loan_id 
    AND lr_all.com_id = 15
    AND lr_all.rec_date <= CURDATE()
LEFT JOIN loan_receipt tr ON fc.loan_id = tr.loan_id 
    AND tr.com_id = 15
    AND tr.rec_date = CURDATE()
GROUP BY fc.loan_id;

-- =====================================================
-- COMPLETE JSON OUTPUT QUERY
-- =====================================================
-- This query produces the exact same JSON output structure as the Node.js function
-- Returns: { status, message, data: { agent_info, customers[], summary } }
-- Note: Some complex calculations are simplified - for exact match, use Node.js function
-- =====================================================

SELECT 
    JSON_OBJECT(
        'status', 'success',
        'message', 'Collection details retrieved successfully',
        'data', JSON_OBJECT(
            'agent_info', JSON_OBJECT(
                'agent_id', ca.id,
                'agent_name', ca.name,
                'agent_mobile', ca.mobile_no,
                'company_id', ca.com_id
            ),
            'customers', IFNULL(
                JSON_ARRAYAGG(
                    JSON_OBJECT(
                        'loan_id', customer_data.loan_id,
                        'cust_id', customer_data.cust_id,
                        'customer_name', customer_data.customer_name,
                        'customer_mobile', customer_data.customer_mobile,
                        'area', customer_data.area,
                        'address', customer_data.address,
                        'address2', customer_data.address2,
                        'next_due_date', customer_data.next_due_date,
                        'per_due_amount', ROUND(customer_data.per_due_amount, 2),
                        'collection_details', JSON_OBJECT(
                            'previous_unpaid_amount', ROUND(customer_data.previous_unpaid_amount, 2),
                            'previous_unpaid_dues', customer_data.previous_unpaid_dues,
                            'today_expected_amount', ROUND(customer_data.today_expected_amount, 2),
                            'future_paid_amount', ROUND(customer_data.future_paid_amount, 2),
                            'partial_future_paid_amount', ROUND(customer_data.partial_future_paid_amount, 2),
                            'total_collection_amount', ROUND(customer_data.total_collection_amount, 2),
                            'total_amount_till_today', ROUND(customer_data.total_amount_till_today, 2),
                            'total_collected_amount', ROUND(customer_data.total_collected_amount, 2),
                            'collected_today_amount', ROUND(customer_data.collected_today_amount, 2),
                            'remaining_amount_till_today', ROUND(customer_data.remaining_amount_till_today, 2),
                            'remaining_dues_till_today', customer_data.remaining_dues_till_today,
                            'pending_dues_count', customer_data.pending_dues_count,
                            'total_pending_amount', ROUND(customer_data.total_pending_amount, 2),
                            'remaining_pending', ROUND(customer_data.remaining_pending, 2),
                            'collection_status', customer_data.collection_status,
                            'payment_type', customer_data.payment_type,
                            'total_dues', customer_data.total_dues,
                            'pending_dues', customer_data.pending_dues,
                            'cumulative_paid_dues', customer_data.cumulative_paid_dues,
                            'per_due_amount', ROUND(customer_data.per_due_amount, 2)
                        ),
                        'loan_entry', JSON_OBJECT(
                            'id', customer_data.loan_entry_id,
                            'loan_date', customer_data.loan_date,
                            'loan_type', customer_data.loan_type,
                            'loan_amt', ROUND(customer_data.loan_amt, 2),
                            'total_amt', ROUND(customer_data.total_amt, 2),
                            'dues', customer_data.dues,
                            'interest', ROUND(customer_data.interest, 2),
                            'interest_amt', ROUND(customer_data.interest_amt, 2),
                            'status', customer_data.status
                        ),
                        'has_receipt', customer_data.has_receipt,
                        'last_receipt', IF(customer_data.last_receipt_id IS NOT NULL,
                            JSON_OBJECT(
                                'id', customer_data.last_receipt_id,
                                'rec_no', customer_data.last_rec_no,
                                'rec_date', customer_data.last_rec_date,
                                'due_amnt', ROUND(customer_data.last_due_amnt, 2),
                                'paid_amnt', ROUND(customer_data.last_paid_amnt, 2),
                                'paid_dues', customer_data.last_paid_dues,
                                'pending_dues', customer_data.last_pending_dues,
                                'balance_dues', customer_data.last_balance_dues,
                                'next_date', customer_data.last_next_date
                            ),
                            NULL
                        )
                    )
                ),
                JSON_ARRAY()
            ),
            'summary', JSON_OBJECT(
                'total_customers', summary_data.total_customers,
                'total_collection_amount', ROUND(summary_data.total_collection_amount, 2),
                'total_amount_till_today', ROUND(summary_data.total_amount_till_today, 2),
                'total_needed_calculated_today', ROUND(summary_data.total_needed_calculated_today, 2),
                'total_collected_amount', ROUND(summary_data.total_collected_amount, 2),
                'collected_today_amount', ROUND(summary_data.collected_today_amount, 2),
                'total_pending_amount', ROUND(summary_data.total_pending_amount, 2),
                'remaining_pending_amount', ROUND(summary_data.remaining_pending_amount, 2),
                'total_previous_unpaid', ROUND(summary_data.total_previous_unpaid, 2),
                'total_future_paid', ROUND(summary_data.total_future_paid, 2),
                'total_partial_future_paid', ROUND(summary_data.total_partial_future_paid, 2),
                'collection_status', JSON_OBJECT(
                    'fully_paid', summary_data.fully_paid_count,
                    'partially_paid', summary_data.partially_paid_count,
                    'unpaid', summary_data.unpaid_count
                ),
                'payment_types', JSON_OBJECT(
                    'previous_unpaid', summary_data.previous_unpaid_count,
                    'future_paid', summary_data.future_paid_count,
                    'partial_future_paid', summary_data.partial_future_paid_count,
                    'full_paid', summary_data.full_paid_count,
                    'none', summary_data.none_count
                ),
                'collection_rate', ROUND(summary_data.collection_rate, 2),
                'today_collection_rate', ROUND(summary_data.today_collection_rate, 2),
                'current_date', CURDATE(),
                'areas', summary_data.areas_json
            )
        )
    ) as result
FROM collection_agent ca
CROSS JOIN (
    -- Get all areas for agent's line
    SELECT DISTINCT area
    FROM line_entry
    WHERE com_id = 15
      AND line = (SELECT line FROM collection_agent WHERE id = 3)
      AND area IS NOT NULL
      AND area != ''
) areas_list
CROSS JOIN (
    -- Customer data with calculations
    WITH loan_entries AS (
        SELECT le.*
        FROM loan_entry le
        WHERE le.com_id = 15
          AND le.area IN (
              SELECT DISTINCT area 
              FROM line_entry 
              WHERE com_id = 15 
                AND line = (SELECT line FROM collection_agent WHERE id = 3)
                AND area IS NOT NULL 
                AND area != ''
          )
          AND le.loan_id IS NOT NULL
    ),
    last_receipts AS (
        SELECT 
            lr.*,
            ROW_NUMBER() OVER (
                PARTITION BY lr.loan_id 
                ORDER BY lr.rec_date DESC, lr.created_at DESC
            ) as rn
        FROM loan_receipt lr
        WHERE lr.com_id = 15
          AND lr.loan_id IN (SELECT loan_id FROM loan_entries)
    ),
    today_receipts AS (
        SELECT 
            loan_id,
            SUM(paid_amnt) as today_paid_total
        FROM loan_receipt
        WHERE com_id = 15
          AND rec_date = CURDATE()
          AND loan_id IN (SELECT loan_id FROM loan_entries)
        GROUP BY loan_id
    ),
    all_receipts_summary AS (
        SELECT 
            loan_id,
            SUM(CASE WHEN rec_date <= CURDATE() THEN paid_amnt ELSE 0 END) as total_collected,
            SUM(CASE WHEN rec_date = CURDATE() THEN paid_amnt ELSE 0 END) as collected_today
        FROM loan_receipt
        WHERE com_id = 15
          AND loan_id IN (SELECT loan_id FROM loan_entries)
        GROUP BY loan_id
    ),
    filtered_loans AS (
        SELECT 
            le.*,
            lr.id as last_receipt_id,
            lr.rec_no as last_rec_no,
            lr.rec_date as last_rec_date,
            lr.due_amnt as last_due_amnt,
            lr.paid_amnt as last_paid_amnt,
            lr.paid_dues as last_paid_dues,
            lr.pending_dues as last_pending_dues,
            lr.balance_dues as last_balance_dues,
            lr.next_date as last_next_date,
            COALESCE(tr.today_paid_total, 0) as collected_today_amount,
            COALESCE(ars.total_collected, 0) as total_collected_amount,
            COALESCE(ars.collected_today, 0) as collected_today_from_summary,
            CASE 
                WHEN le.next_due_date = CURDATE() THEN 1
                WHEN le.next_due_date < CURDATE() THEN 1
                WHEN lr.next_date IS NOT NULL AND lr.next_date <= CURDATE() THEN 1
                WHEN tr.loan_id IS NOT NULL THEN 1
                ELSE 0
            END as should_include
        FROM loan_entries le
        LEFT JOIN last_receipts lr ON le.loan_id = lr.loan_id AND lr.rn = 1
        LEFT JOIN today_receipts tr ON le.loan_id = tr.loan_id
        LEFT JOIN all_receipts_summary ars ON le.loan_id = ars.loan_id
        WHERE 
            (le.next_due_date = CURDATE())
            OR (le.next_due_date < CURDATE())
            OR (lr.next_date IS NOT NULL AND lr.next_date <= CURDATE())
            OR (tr.loan_id IS NOT NULL)
    )
    SELECT 
        fl.loan_id,
        fl.cust_id,
        fl.cus_name as customer_name,
        fl.cust_mobile as customer_mobile,
        fl.area,
        fl.cust_address as address,
        fl.cust_address2 as address2,
        COALESCE(fl.last_next_date, fl.next_due_date) as next_due_date,
        COALESCE(fl.per_due_amt, 0) as per_due_amount,
        -- Simplified calculations (exact calculations require complex date logic)
        0 as previous_unpaid_amount,
        0 as previous_unpaid_dues,
        CASE 
            WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() THEN COALESCE(fl.per_due_amt, 0)
            ELSE 0
        END as today_expected_amount,
        0 as future_paid_amount,
        0 as partial_future_paid_amount,
        CASE 
            WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() THEN COALESCE(fl.per_due_amt, 0)
            ELSE 0
        END as total_collection_amount,
        -- Approximate total_amount_till_today (simplified)
        COALESCE(fl.per_due_amt * GREATEST(1, DATEDIFF(CURDATE(), fl.loan_date) / 
            CASE fl.loan_type
                WHEN 'Weekly' THEN 7
                WHEN 'Monthly' THEN 30
                ELSE 1
            END), 0) as total_amount_till_today,
        fl.total_collected_amount,
        fl.collected_today_amount,
        GREATEST(0, COALESCE(fl.per_due_amt * GREATEST(1, DATEDIFF(CURDATE(), fl.loan_date) / 
            CASE fl.loan_type
                WHEN 'Weekly' THEN 7
                WHEN 'Monthly' THEN 30
                ELSE 1
            END), 0) - fl.total_collected_amount) as remaining_amount_till_today,
        0 as remaining_dues_till_today,
        COALESCE(fl.last_pending_dues, fl.dues) as pending_dues_count,
        GREATEST(0, CASE 
            WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() THEN COALESCE(fl.per_due_amt, 0)
            ELSE 0
        END - fl.collected_today_amount) as total_pending_amount,
        GREATEST(0, CASE 
            WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() THEN COALESCE(fl.per_due_amt, 0)
            ELSE 0
        END - fl.collected_today_amount) as remaining_pending,
        CASE 
            WHEN fl.collected_today_amount >= COALESCE(fl.per_due_amt, 0) THEN 'FULLY_PAID'
            WHEN fl.collected_today_amount > 0 THEN 'PARTIALLY_PAID'
            ELSE 'UNPAID'
        END as collection_status,
        CASE 
            WHEN fl.collected_today_amount >= COALESCE(fl.per_due_amt, 0) THEN 'FULL_PAID'
            WHEN fl.collected_today_amount > 0 THEN 'PARTIAL_FUTURE_PAID'
            ELSE 'NONE'
        END as payment_type,
        COALESCE(fl.dues, 0) as total_dues,
        COALESCE(fl.last_pending_dues, fl.dues) as pending_dues,
        COALESCE(fl.last_paid_dues, 0) as cumulative_paid_dues,
        fl.id as loan_entry_id,
        fl.loan_date,
        fl.loan_type,
        COALESCE(fl.loan_amt, 0) as loan_amt,
        COALESCE(fl.total_amt, 0) as total_amt,
        fl.dues,
        COALESCE(fl.interest, 0) as interest,
        COALESCE(fl.interest_amt, 0) as interest_amt,
        fl.status,
        CASE WHEN fl.last_receipt_id IS NOT NULL THEN 1 ELSE 0 END as has_receipt,
        fl.last_receipt_id,
        fl.last_rec_no,
        fl.last_rec_date,
        COALESCE(fl.last_due_amnt, 0) as last_due_amnt,
        COALESCE(fl.last_paid_amnt, 0) as last_paid_amnt,
        COALESCE(fl.last_paid_dues, 0) as last_paid_dues,
        COALESCE(fl.last_pending_dues, 0) as last_pending_dues,
        COALESCE(fl.last_balance_dues, 0) as last_balance_dues,
        fl.last_next_date
    FROM filtered_loans fl
) customer_data
CROSS JOIN (
    -- Summary statistics
    WITH loan_entries AS (
        SELECT le.*
        FROM loan_entry le
        WHERE le.com_id = 15
          AND le.area IN (
              SELECT DISTINCT area 
              FROM line_entry 
              WHERE com_id = 15 
                AND line = (SELECT line FROM collection_agent WHERE id = 3)
                AND area IS NOT NULL 
                AND area != ''
          )
          AND le.loan_id IS NOT NULL
    ),
    last_receipts AS (
        SELECT 
            lr.*,
            ROW_NUMBER() OVER (
                PARTITION BY lr.loan_id 
                ORDER BY lr.rec_date DESC, lr.created_at DESC
            ) as rn
        FROM loan_receipt lr
        WHERE lr.com_id = 15
          AND lr.loan_id IN (SELECT loan_id FROM loan_entries)
    ),
    today_receipts AS (
        SELECT loan_id, SUM(paid_amnt) as today_paid
        FROM loan_receipt
        WHERE com_id = 15 AND rec_date = CURDATE()
          AND loan_id IN (SELECT loan_id FROM loan_entries)
        GROUP BY loan_id
    ),
    all_receipts_summary AS (
        SELECT 
            loan_id,
            SUM(CASE WHEN rec_date <= CURDATE() THEN paid_amnt ELSE 0 END) as total_collected,
            SUM(CASE WHEN rec_date = CURDATE() THEN paid_amnt ELSE 0 END) as collected_today
        FROM loan_receipt
        WHERE com_id = 15
          AND loan_id IN (SELECT loan_id FROM loan_entries)
        GROUP BY loan_id
    ),
    filtered_loans AS (
        SELECT 
            le.*,
            lr.next_date as last_next_date,
            COALESCE(tr.today_paid, 0) as collected_today,
            COALESCE(ars.total_collected, 0) as total_collected,
            COALESCE(ars.collected_today, 0) as collected_today_sum
        FROM loan_entries le
        LEFT JOIN last_receipts lr ON le.loan_id = lr.loan_id AND lr.rn = 1
        LEFT JOIN today_receipts tr ON le.loan_id = tr.loan_id
        LEFT JOIN all_receipts_summary ars ON le.loan_id = ars.loan_id
        WHERE 
            (le.next_due_date = CURDATE())
            OR (le.next_due_date < CURDATE())
            OR (lr.next_date IS NOT NULL AND lr.next_date <= CURDATE())
            OR (tr.loan_id IS NOT NULL)
    )
    SELECT 
        COUNT(*) as total_customers,
        SUM(CASE 
            WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() 
            THEN COALESCE(fl.per_due_amt, 0) ELSE 0 
        END) as total_collection_amount,
        SUM(COALESCE(fl.per_due_amt * GREATEST(1, DATEDIFF(CURDATE(), fl.loan_date) / 
            CASE fl.loan_type
                WHEN 'Weekly' THEN 7
                WHEN 'Monthly' THEN 30
                ELSE 1
            END), 0)) as total_amount_till_today,
        SUM(COALESCE(fl.per_due_amt * GREATEST(1, DATEDIFF(CURDATE(), fl.loan_date) / 
            CASE fl.loan_type
                WHEN 'Weekly' THEN 7
                WHEN 'Monthly' THEN 30
                ELSE 1
            END), 0) + fl.collected_today) as total_needed_calculated_today,
        SUM(fl.total_collected) as total_collected_amount,
        SUM(fl.collected_today) as collected_today_amount,
        SUM(GREATEST(0, CASE 
            WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() 
            THEN COALESCE(fl.per_due_amt, 0) ELSE 0 
        END - fl.collected_today)) as total_pending_amount,
        SUM(GREATEST(0, CASE 
            WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() 
            THEN COALESCE(fl.per_due_amt, 0) ELSE 0 
        END - fl.collected_today)) as remaining_pending_amount,
        0 as total_previous_unpaid,
        0 as total_future_paid,
        0 as total_partial_future_paid,
        SUM(CASE WHEN fl.collected_today >= COALESCE(fl.per_due_amt, 0) THEN 1 ELSE 0 END) as fully_paid_count,
        SUM(CASE WHEN fl.collected_today > 0 AND fl.collected_today < COALESCE(fl.per_due_amt, 0) THEN 1 ELSE 0 END) as partially_paid_count,
        SUM(CASE WHEN fl.collected_today = 0 THEN 1 ELSE 0 END) as unpaid_count,
        0 as previous_unpaid_count,
        0 as future_paid_count,
        SUM(CASE WHEN fl.collected_today > 0 AND fl.collected_today < COALESCE(fl.per_due_amt, 0) THEN 1 ELSE 0 END) as partial_future_paid_count,
        SUM(CASE WHEN fl.collected_today >= COALESCE(fl.per_due_amt, 0) THEN 1 ELSE 0 END) as full_paid_count,
        COUNT(*) - SUM(CASE WHEN fl.collected_today >= COALESCE(fl.per_due_amt, 0) THEN 1 ELSE 0 END) - 
        SUM(CASE WHEN fl.collected_today > 0 AND fl.collected_today < COALESCE(fl.per_due_amt, 0) THEN 1 ELSE 0 END) as none_count,
        CASE 
            WHEN COUNT(*) > 0 
            THEN (SUM(CASE WHEN fl.collected_today >= COALESCE(fl.per_due_amt, 0) THEN 1 ELSE 0 END) / COUNT(*)) * 100
            ELSE 0
        END as collection_rate,
        CASE 
            WHEN SUM(CASE 
                WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() 
                THEN COALESCE(fl.per_due_amt, 0) ELSE 0 
            END) > 0
            THEN (SUM(fl.collected_today) / SUM(CASE 
                WHEN fl.next_due_date = CURDATE() OR fl.last_next_date = CURDATE() 
                THEN COALESCE(fl.per_due_amt, 0) ELSE 0 
            END)) * 100
            ELSE 0
        END as today_collection_rate,
        (
            SELECT JSON_ARRAYAGG(area)
            FROM (
                SELECT DISTINCT area
                FROM line_entry
                WHERE com_id = 15
                  AND line = (SELECT line FROM collection_agent WHERE id = 3)
                  AND area IS NOT NULL
                  AND area != ''
            ) areas_sub
        ) as areas_json
    FROM filtered_loans fl
) summary_data
WHERE ca.id = 3
GROUP BY ca.id, ca.name, ca.mobile_no, ca.com_id, summary_data.total_customers,
    summary_data.total_collection_amount, summary_data.total_amount_till_today,
    summary_data.total_needed_calculated_today, summary_data.total_collected_amount,
    summary_data.collected_today_amount, summary_data.total_pending_amount,
    summary_data.remaining_pending_amount, summary_data.total_previous_unpaid,
    summary_data.total_future_paid, summary_data.total_partial_future_paid,
    summary_data.fully_paid_count, summary_data.partially_paid_count,
    summary_data.unpaid_count, summary_data.previous_unpaid_count,
    summary_data.future_paid_count, summary_data.partial_future_paid_count,
    summary_data.full_paid_count, summary_data.none_count,
    summary_data.collection_rate, summary_data.today_collection_rate,
    summary_data.areas_json;

-- =====================================================
-- CONFIGURED VALUES:
-- =====================================================
-- agent_id = 3
-- company_id = 15
-- agent_line = Retrieved automatically from collection_agent table
-- areas = Retrieved automatically from line_entry table based on agent's line
-- loan_ids = Retrieved automatically from loan_entry table
-- CURDATE() = Current date (replace with specific date like '2024-01-15' if needed)
-- 
-- NOTE: All queries are now configured with agent_id = 3 and company_id = 15
-- The agent's line and areas are automatically retrieved from the database
-- 
-- IMPORTANT: The JSON output query above produces the same structure as Node.js
-- but some complex calculations (like weekly loan date logic) are simplified.
-- For exact calculations matching the Node.js function, use the Node.js API.
-- =====================================================