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

)
VALUES
(

)"
	);
				$topsdash_board["selectList"] = array(
		"subtype" => "sql",
		"sql" => "SELECT
    -- Total loans
    COUNT(*) AS total_loans,
    
    -- Total active loans
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_loans,
    
    -- Total closed loans
    SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) AS closed_loans,
    
    -- Total defaulted loans
    SUM(CASE WHEN status = 'defaulted' THEN 1 ELSE 0 END) AS defaulted_loans,
    
    -- Total loan amount
    SUM(loan_amount) AS total_loan_amount,
    
    -- Total payable (principal + profit)
    SUM(total_payable) AS total_payable_amount,
    
    -- Total profit amount
    SUM(profit_amount) AS total_profit,
    
    -- Total collected so far
    (SELECT IFNULL(SUM(amount),0) FROM collections) AS total_collected,
    
    -- Total remaining balance
    (SUM(total_payable) - (SELECT IFNULL(SUM(amount),0) FROM collections)) AS total_remaining_balance,
    
    -- Number of loans overdue (remaining_days < 0)
    SUM(
        CASE 
            WHEN DATEDIFF(end_date, CURDATE()) < 0 AND (total_payable - IFNULL((SELECT SUM(c.amount) 
                FROM collections c WHERE c.loan_id = loans.loan_id),0)) > 0
            THEN 1 
            ELSE 0 
        END
    ) AS overdue_loans,
    
    -- Total overdue amount
    SUM(
        CASE 
            WHEN DATEDIFF(end_date, CURDATE()) < 0 
            THEN GREATEST(total_payable - IFNULL((SELECT SUM(c.amount) 
                FROM collections c WHERE c.loan_id = loans.loan_id),0),0)
            ELSE 0 
        END
    ) AS overdue_amount,
    
    -- Average loan amount
    AVG(loan_amount) AS avg_loan_amount,
    
    -- Average total payable
    AVG(total_payable) AS avg_total_payable,
    
    -- Average profit
    AVG(profit_amount) AS avg_profit

FROM loans;
"
	);
		$tables_data["Dash board"][".operations"] = &$topsdash_board;
?>