<?php
$topsloan_details = array();
		$topsloan_details["insert"] = array(
		"subtype" => "sql",
		"sql" => "INSERT INTO `Costomer View` (

)
VALUES
(

)"
	);
				$topsloan_details["selectList"] = array(
		"subtype" => "sql",
		"sql" => "SELECT
    t.loan_id,
    t.customer_id,
    t.horno,
    t.guarantor_1,
    t.guarantor_2,

    /* Name */
    CONCAT(
        t.full_name, ' / ',
        t.nic, ' / ',
        t.phone, ' / ',
        t.address
    ) AS name,

    /* Details with short Sinhala labels */
    CONCAT(
        \"ණය: \", t.loan_amount,\", \", \"\\n\",
         \"සේවා: \", t.profit_amount,\", \", \"\\n\",
        \"මුළු ගෙවීම: \", t.total_payable,\", \", \"\\n\",
        \"වාරිකය: \", t.installment_amount,\", \", \"\\n\",
        \"කාලය: \", t.payment_interval_days,\", \", \"\\n\",
        \"අරම්භය: \", t.start_date,\", \", \"\\n\",
        \"අවසානය: \", t.end_date,\", \", \"\\n\",
        \"තත්ත්වය: \", t.status,\", \", \"\\n\",
         \"තව දින: \", t.remaining_days,\", \", \"\\n\",
        \"තව වාරික: \", t.remaining_installments,\", \", \"\\n\",
        \"ගෙවූ මුදල: \", t.total_collected,\", \", \"\\n\",
        \"අදට ගෙවිය යුතු: \", t.should_pay,\", \", \"\\n\",
        \"හිඟ: \", t.arrears_amount, \"\\n\"
    ) AS details, t.arrears_amount,
(t.remaining_balance + t.profit_amount) as \"Recovery Amount\" ,
    t.remaining_balance,
    t.Old_Loan

FROM (
    SELECT
        l.loan_id,
        l.customer_id,
        l.horno,
        l.guarantor_1,
        l.guarantor_2,

        /* Customer fields */
        cus.full_name AS full_name,
        cus.nic AS nic,
        cus.phone AS phone,
        cus.address AS address,

        l.loan_amount,
        l.total_payable,
        l.installment_amount,
        l.payment_interval_days,
        l.start_date,
        l.end_date,
        l.status,
        l.Old_Loan,
        l.profit_amount,

        IFNULL(SUM(c.amount), 0) AS total_collected,

        /* Calculations – capped to total_payable */
        LEAST(
            (FLOOR(DATEDIFF(CURDATE(), l.start_date) / l.payment_interval_days) + 1)
            * l.installment_amount,
            l.total_payable
        ) AS should_pay,

        (l.total_payable - IFNULL(SUM(c.amount), 0)) AS remaining_balance,

        GREATEST(
            LEAST(
                (FLOOR(DATEDIFF(CURDATE(), l.start_date) / l.payment_interval_days) + 1)
                * l.installment_amount,
                l.total_payable
            ) - IFNULL(SUM(c.amount), 0),
            0
        ) AS arrears_amount,

        DATEDIFF(l.end_date, CURDATE()) AS remaining_days,

        GREATEST(
            ROUND((l.total_payable / l.installment_amount)
                - (IFNULL(SUM(c.amount), 0) / l.installment_amount), 0),
            0
        ) AS remaining_installments

    FROM loans l
    LEFT JOIN customers cus ON l.customer_id = cus.customer_id
    LEFT JOIN collections c ON l.loan_id = c.loan_id
    WHERE l.status = 'active' OR l.status = 'Recovered'
    GROUP BY l.loan_id
) AS t

ORDER BY t.loan_id DESC;"
	);
		$tables_data["Loan Details"][".operations"] = &$topsloan_details;
?>