<?PHP
/**
 * Establish Database Connection
 */
function connect() {
    // We use the file created in Step 1
    require 'db_connect.php';
    // $conn is created in db_connect.php
    if (!$conn) {
        // If connection fails, redirect to setup or die
        die("Database connection failed.");
    }
    // Set charset
    mysqli_set_charset($conn, 'utf8mb4');
    return $conn;
}

/**
 * Generate unique fingerprint for every user session
 */
function fingerprint(){
    return md5($_SERVER['REMOTE_ADDR'].'jikI/20Y,!'.$_SERVER['HTTP_USER_AGENT']);
}

/**
 * Check if current user is logged in
 */
function checkLogin() {
    // Only start session if not already started
    if (session_status() == PHP_SESSION_NONE) {
        session_start();
    }
    $fingerprint = fingerprint();
    
    if (!isset($_SESSION['log_user']) || !isset($_SESSION['log_fingerprint']) || $_SESSION['log_fingerprint'] != $fingerprint) {
        logout();
    }
    session_regenerate_id();
}

/**
 * Check if current user logged out properly last time
 */
function checkLogout(){
    if (isset($_SESSION['logrec_logout']) && $_SESSION['logrec_logout'] == 0){
        showMessage("You forgot to logout last time. Please remember to log out properly.");
        $_SESSION['logrec_logout'] = 1;
    }
}

/**
 * Check Permission Wrappers
 */
function checkPermissionAdmin() {
    if (!isset($_SESSION['log_admin']) || $_SESSION['log_admin'] !== '1'){
        header('Location: start.php');
        exit();
    }
}

function checkPermissionDelete() {
    if (!isset($_SESSION['log_delete']) || $_SESSION['log_delete'] !== '1'){
        header('Location: start.php');
        exit();
    }
}

function checkPermissionReport() {
    if (!isset($_SESSION['log_report']) || $_SESSION['log_report'] !== '1'){
        header('Location: start.php');
        exit();
    }
}

/**
 * Logout procedure
 */
function logout(){
    $_SESSION = array();

    if (ini_get("session.use_cookies")) {
        $params = session_get_cookie_params();
        setcookie(session_name(), '', time() - 86400, $params["path"], $params["domain"], $params["secure"], $params["httponly"]);
    }

    session_destroy();
    
    // FIXED: Now it redirects back to your login page
    header('Location: login.php'); 
    exit();
}

/**
 * Check if an SQL statement has succeeded
 */
function checkSQL($db_link, $sqlquery){
    if (!$sqlquery) {
        die('SQL-Statement failed: ' . mysqli_error($db_link));
    }
}

/**
 * Pushing system settings into session variables
 */
function getSettings($db_link){
    $sql_settings = "SELECT * FROM settings";
    $query_settings = mysqli_query($db_link, $sql_settings);
    checkSQL($db_link, $query_settings); // Fixed: Removed 3rd argument
    
    while($row_settings = mysqli_fetch_assoc($query_settings)){
        // Simplified switch case logic
        switch ($row_settings['set_short']){
            case "SET_MSB": $_SESSION['set_msb'] = $row_settings['set_value']; break;
            case "SET_MLP": $_SESSION['set_minlp'] = $row_settings['set_value']; break;
            case "SET_XLP": $_SESSION['set_maxlp'] = $row_settings['set_value']; break;
            case "SET_CUR": $_SESSION['set_cur'] = $row_settings['set_value']; break;
            case "SET_AUF": $_SESSION['set_auf'] = $row_settings['set_value']; break;
            case "SET_DEA": $_SESSION['set_deact'] = $row_settings['set_value']; break;
            case "SET_DBL": $_SESSION['set_dashl'] = $row_settings['set_value']; break;
            case "SET_DBR": $_SESSION['set_dashr'] = $row_settings['set_value']; break;
            case "SET_ICL": $_SESSION['set_intcalc'] = $row_settings['set_value']; break;
            case "SET_GUA": $_SESSION['set_maxguar'] = $row_settings['set_value']; break;
            case "SET_MEM": $_SESSION['set_minmemb'] = $row_settings['set_value']; break;
            case "SET_PSR": $_SESSION['set_maxpsr'] = $row_settings['set_value']; break;
            case "SET_CNO": $_SESSION['set_cno'] = $row_settings['set_value']; break;
            case "SET_XL1": $_SESSION['set_xl1'] = $row_settings['set_value']; break;
            case "SET_ENO": $_SESSION['set_eno'] = $row_settings['set_value']; break;
            case "SET_SFX": $_SESSION['set_sfx'] = $row_settings['set_value']; break;
            case "SET_CSI": $_SESSION['set_csi'] = $row_settings['set_value']; break;
            case "SET_F4F": $_SESSION['set_f4f'] = $row_settings['set_value']; break;
        }
    }
}

/**
 * Pushing fee settings into session variables
 */
function getFees($db_link){
    $sql_fees = "SELECT * FROM fees";
    $query_fees = mysqli_query($db_link, $sql_fees);
    checkSQL($db_link, $query_fees); // Fixed: Removed 3rd argument

    while ($row_fees = mysqli_fetch_assoc($query_fees)){
        switch ($row_fees['fee_short']){
            case "FEE_ENT": $_SESSION['fee_entry'] = $row_fees['fee_value']; break;
            case "FEE_WDL": $_SESSION['fee_withdraw'] = $row_fees['fee_value']; break;
            case "FEE_STS": $_SESSION['fee_stationary'] = $row_fees['fee_value']; break;
            case "FEE_ASB": $_SESSION['fee_subscr'] = $row_fees['fee_value']; break;
            case "FEE_LOF": $_SESSION['fee_loan'] = $row_fees['fee_value']; break;
            case "FEE_LAP": $_SESSION['fee_loanappl'] = $row_fees['fee_value']; break;
            case "FEE_LDF": $_SESSION['fee_loanfine'] = $row_fees['fee_value']; break;
            case "FEE_LIR": $_SESSION['fee_loaninterestrate'] = $row_fees['fee_value']; break;
            case "FEE_INS": $_SESSION['fee_loaninsurance'] = $row_fees['fee_value']; break;
            case "FEE_XL1": 
                $_SESSION['fee_xl1_name'] = $row_fees['fee_name'];
                $_SESSION['fee_xl1'] = $row_fees['fee_value']; 
                break;
        }
    }
}

/**
 * Pushing current share value into a session variable
 */
function getShareValue($db_link){
    $sql_shareval = "SELECT shareval_value FROM shareval WHERE shareval_id IN (SELECT MAX(shareval_id) FROM shareval)";
    $query_shareval = mysqli_query($db_link, $sql_shareval);
    checkSQL($db_link, $query_shareval); // Fixed: Removed 3rd argument
    $result_shareval = mysqli_fetch_assoc($query_shareval);
    $_SESSION['share_value'] = $result_shareval['shareval_value'];
}

/**
 * Sanitize and secure user input
 */
function sanitize($db_link, $var) {
    // Fixed: Removed get_magic_quotes_gpc check (Deprecated in PHP 7.4, Removed in 8.0)
    $var = trim($var); // Added trim for extra safety
    $var = strip_tags($var);
    $var = mysqli_real_escape_string($db_link, $var);
    return $var;
}

function convertDays($days){
    return $days * 86400;
}

function convertMonths($months){
    return $months * 2635200; // Seconds for 30.5 days
}

function getCustID($db_link){
    if (isset($_GET['cust'])) $_SESSION['cust_id'] = sanitize($db_link, $_GET['cust']);
    else header('Location: start.php');
}

function getLoanID($db_link){
    if (isset($_GET['lid'])) $_SESSION['loan_id'] = sanitize($db_link, $_GET['lid']);
    else header('Location: customer.php?cust='.$_SESSION['cust_id']);
}

function getGroupLoanID($db_link){
    if (isset($_GET['loan_id']) && !empty($_GET['loan_id'])){
        $loan_id = sanitize($db_link, $_GET['loan_id']);
        $sql_check_loan_id = "SELECT loan_id FROM group_loans WHERE loan_id = '$loan_id'";
        $query_check_loan_id = mysqli_query($db_link, $sql_check_loan_id);
        if (mysqli_num_rows($query_check_loan_id) > 0) {
            $_SESSION['loan_id'] = $loan_id;
        } else {
            header('Location: group.php');
            exit;
        }
    } elseif (isset($_SESSION['loan_id']) && !empty($_SESSION['loan_id'])){
        $sql_check_loan_id = "SELECT loan_id FROM group_loans WHERE loan_id = '{$_SESSION['loan_id']}'";
        $query_check_loan_id = mysqli_query($db_link, $sql_check_loan_id);
        if (mysqli_num_rows($query_check_loan_id) == 0) {
            header('Location: group.php');
            exit;
        }
    } else {
        header('Location: group.php');
        exit;
    }
}

function getGroupID($db_link){
    if (isset($_GET['group_id'])) 
        $_SESSION['group_id'] = sanitize($db_link, $_GET['group_id']); 
    else 
        header('Location: start.php'); 
}

/**
 * Generate HTML Header Section
 */
function includeHead($title, $endFlag = 1) {
    echo '<head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8">
        <meta name="robots" content="noindex, nofollow">
        <title>'.$title.'</title>
        <link rel="shortcut icon" href="ico/favicon.ico" type="image/x-icon">
        <link rel="stylesheet" href="css/mangoo.css" />
        <link rel="stylesheet" href="ico/font-awesome/css/font-awesome.min.css">
        <link rel="stylesheet" href="jquery/jquery-ui-1.11.4/jquery-ui.min.css">
        <script src="jquery/jquery-2.2.1.min.js"></script>
        <script src="jquery/jquery-ui-1.11.4/jquery-ui.min.js"></script>
        <script>
            $(function() {
                $("#datepicker, #datepicker2, #datepicker3").datepicker({
                    showOtherMonths: true,
                    selectOtherMonths: true,
                    dateFormat: \'dd.mm.yy\',
                    changeMonth: true,
                    changeYear: true
                });
            });
        </script>
        ';
    if ($endFlag == 1) echo '</head>';
}

/**
 * Generate Menu bar
 */
function includeMenu($tab_no){
    // Ensure session variables are set to avoid Undefined Index warnings
    $log_user = isset($_SESSION['log_user']) ? $_SESSION['log_user'] : 'User';
    $log_report = isset($_SESSION['log_report']) ? $_SESSION['log_report'] : 0;
    $log_admin = isset($_SESSION['log_admin']) ? $_SESSION['log_admin'] : 0;

    echo '
    <div id="menu_header">
        <img src="ico/Capture1.png" style="margin : 1em 0 0 .75em;"/>
        <div id="menu_logout">
            <ul>
                <li>'.$log_user.'
                    <ul>
                    <li><a href="logout.php"><i class="fa fa-sign-out fa-fw"></i> Logout</a></li>
                    </ul>
                </li>
            </ul>
        </div>
    </div>';

    echo '
    <div id="menu_tabs">
        <ul>
            <li'. ($tab_no == 1 ? ' id="tab_selected"' : '') .'><a href="start.php"><i class="fa fa-tachometer fa-fw"></i> Dashboard</a></li>
            <li'. ($tab_no == 2 ? ' id="tab_selected"' : '') .'><a href="cust_search.php"><i class="fa fa-group fa-fw"></i> Customers</a></li>
            <li'. ($tab_no == 8 ? ' id="tab_selected"' : '') .'><a href="group_new.php"><i class="fa fa-users fa-fw"></i> Group</a></li>
            <li'. ($tab_no == 3 ? ' id="tab_selected"' : '') .'><a href="loans_search.php"><i class="fa fa-percent fa-fw"></i> Loans</a></li>
            <li'. ($tab_no == 4 ? ' id="tab_selected"' : '') .'><a href="finance.php"><i class="fa fa-calculator fa-fw"></i> Finance</a></li>
            <li'. ($tab_no == 7 ? ' id="tab_selected"' : '') .'><a href="empl_curr.php"><i class="fa fa-male fa-fw"></i> Employees</a></li>';
            
            if ($log_report == 1){
                echo '<li'. ($tab_no == 5 ? ' id="tab_selected"' : '') .'><a href="reports.php"><i class="fa fa-line-chart fa-fw"></i> Reports</a></li>';
            }
            if ($log_admin == 1){
                echo '<li'. ($tab_no == 6 ? ' id="tab_selected"' : '') .'><a href="set_basic.php"><i class="fa fa-wrench fa-fw"></i> Settings</a></li>';
            }
    echo '</ul>
    </div>';
}

function showMessage($text) {
    echo '<script language=javascript>
                    alert(\''.$text.'\')
                </script>';
}

function getSavingsBalance($db_link, $cust_id){
    $sql_savbal = "SELECT savbal_balance FROM savbalance WHERE cust_id = $cust_id";
    $query_savbal = mysqli_query($db_link, $sql_savbal);
    checkSQL($db_link, $query_savbal);

    $savbal = mysqli_fetch_assoc($query_savbal);
    return $savbal['savbal_balance'];
}

function getSavingsFixed($db_link, $cust_id){
    $sql_savfixed = "SELECT savbal_fixed FROM savbalance WHERE cust_id = $cust_id";
    $query_savfixed = mysqli_query($db_link, $sql_savfixed);
    checkSQL($db_link, $query_savfixed);

    $savfixed = mysqli_fetch_assoc($query_savfixed);
    return $savfixed['savbal_fixed'];
}

function updateSavingsBalance($db_link, $cust_id){
    $timestamp = time();
    $sql_savbal_upd = "UPDATE savbalance SET savbal_balance = (SELECT SUM(sav_amount) FROM savings WHERE cust_id = $cust_id), savbal_fixed = (SELECT SUM(sav_amount) FROM savings WHERE cust_id = $cust_id and sav_fixed > $timestamp) WHERE cust_id = $cust_id";
    $query_savbal_upd = mysqli_query($db_link, $sql_savbal_upd);
    checkSQL($db_link, $query_savbal_upd); // Fixed: Removed 3rd argument
}

function updateSavingsBalanceAll($db_link){
    $sql_savbal_upd_all = "UPDATE savbalance SET savbalance.savbal_balance = (SELECT SUM(savings.sav_amount) FROM savings WHERE savings.cust_id = savbalance.cust_id)";
    $query_savbal_upd_all = mysqli_query($db_link, $sql_savbal_upd_all);
    checkSQL($db_link, $query_savbal_upd_all); // Fixed: Removed 3rd argument
}

function getLoanBalance($db_link, $loan_id){
    //Select Loan Balance from LTRANS
    $sql_balances = "SELECT ltrans_principaldue, ltrans_interestdue, ltrans_principal, ltrans_interest FROM ltrans WHERE ltrans.loan_id = '$loan_id'";
    $query_balances = mysqli_query($db_link, $sql_balances);
    checkSQL($db_link, $query_balances); // Fixed: Removed 3rd argument

    //Calculate outstanding balances
    $loan_balances = array("pdue" => 0, "idue" => 0, "ppaid" => 0, "ipaid" => 0, "balance" => 0);
    
    while ($row_balances = mysqli_fetch_assoc($query_balances)){
        $loan_balances['pdue'] += $row_balances['ltrans_principaldue'];
        $loan_balances['idue'] += $row_balances['ltrans_interestdue'];
        $loan_balances['ppaid'] += $row_balances['ltrans_principal'];
        $loan_balances['ipaid'] += $row_balances['ltrans_interest'];
    }
    $loan_balances['balance'] = ($loan_balances['pdue'] + $loan_balances['idue']) - ($loan_balances['ppaid'] + $loan_balances['ipaid']);

    return $loan_balances;
}

function getShareBalance($db_link, $cust_id){
    $sql_sharebal = "SELECT share_amount, share_value FROM shares WHERE cust_id = $cust_id";
    $query_sharebal = mysqli_query($db_link, $sql_sharebal);
    checkSQL($db_link, $query_sharebal); // Fixed: Removed 3rd argument
    $sharebal = array("amount" => "0", "value" => "0");
    while($row_sharebal = mysqli_fetch_assoc($query_sharebal)){
        $sharebal['amount'] += $row_sharebal['share_amount'];
        $sharebal['value'] += $row_sharebal['share_value'];
    }
    return $sharebal;
}

function getCustomer($db_link, $custID){
    $sql_cust = "SELECT * FROM customer LEFT JOIN custsex ON customer.custsex_id = custsex.custsex_id LEFT JOIN custmarried ON customer.custmarried_id = custmarried.custmarried_id LEFT JOIN custsick ON customer.custsick_id = custsick.custsick_id LEFT JOIN user ON customer.user_id = user.user_id WHERE cust_id = '$custID'";
    $query_cust = mysqli_query($db_link, $sql_cust);
    checkSQL($db_link, $query_cust); // Fixed: Removed 3rd argument
    $result_cust = mysqli_fetch_assoc($query_cust);
    return $result_cust;
}

function getCustOther($db_link){
    $sql_custother = "SELECT * FROM customer LEFT JOIN custsex ON custsex.custsex_id = customer.custsex_id WHERE cust_id NOT IN (0, {$_SESSION['cust_id']}) ORDER BY cust_id";
    $query_custother = mysqli_query($db_link, $sql_custother);
    checkSQL($db_link, $query_custother); // Fixed: Removed 3rd argument
    return $query_custother;
}

function getCustAct($db_link){
    $sql_custact = "SELECT * FROM customer LEFT JOIN custsex ON custsex.custsex_id = customer.custsex_id WHERE cust_id != 0 AND cust_active = 1 ORDER BY cust_id";
    $query_custact = mysqli_query($db_link, $sql_custact);
    checkSQL($db_link, $query_custact); // Fixed: Removed 3rd argument
    return $query_custact;
}

function getCustInact($db_link){
    $sql_custinact = "SELECT * FROM customer LEFT JOIN custsex ON custsex.custsex_id = customer.custsex_id WHERE cust_id != 0 AND cust_active != 1 ORDER BY cust_id";
    $query_custinact = mysqli_query($db_link, $sql_custinact);
    checkSQL($db_link, $query_custinact); // Fixed: Removed 3rd argument
    return $query_custinact;
}

function getGroupAct($db_link){
    // Fixed: `group` is a reserved word in MySQL. Added backticks.
    $sql_groupact = "SELECT * FROM `group` WHERE group_status = 'active' ORDER BY group_id";
    $query_groupact = mysqli_query($db_link, $sql_groupact);
    checkSQL($db_link, $query_groupact);
    return $query_groupact;
}

function getGroup($db_link, $group_id) {
    // Fixed: `group` is a reserved word in MySQL. Added backticks.
    $sql_get_group = "SELECT * FROM `group` WHERE group_id = '$group_id'";
    $query_get_group = mysqli_query($db_link, $sql_get_group);
    checkSQL($db_link, $query_get_group);
    return mysqli_fetch_assoc($query_get_group);
}

function generateGroupLoanSchedule($db_link, $loan_id, $loan_period, $loan_amount, $interest_rate){
    $pDue = round($loan_amount / $loan_period, -3);
    $difference_principal = $loan_amount - ($pDue * $loan_period);

    $ltrans_pDue = $pDue;
    $ltrans_pRemain = $loan_amount;
    $i = 1;
    $ltrans_due = time() + convertDays(31);
    
    while ($i <= $loan_period){
        $ltrans_iDue = ($ltrans_pRemain / 100 * $interest_rate);

        if ($i == 1) $ltrans_pDue = $ltrans_pDue + $difference_principal;

        $sql_insert_ltrans = "INSERT INTO ltrans (loan_id, ltrans_due, ltrans_principaldue, ltrans_interestdue, user_id) VALUES ('$loan_id', '$ltrans_due', '$ltrans_pDue', '$ltrans_iDue', '$_SESSION[log_id]')";
        $query_insert_ltrans = mysqli_query($db_link, $sql_insert_ltrans);
        checkSQL($db_link, $query_insert_ltrans);

        $ltrans_pRemain = $ltrans_pRemain - $ltrans_pDue;

        if ($i == 1) $ltrans_pDue = $pDue;

        $ltrans_due = $ltrans_due + convertDays(31);
        $i++;
    }
}

function getCustAll($db_link){
    $sql_custall = "SELECT * FROM customer LEFT JOIN custsex ON custsex.custsex_id = customer.custsex_id WHERE cust_id !=0";
    $query_custall = mysqli_query($db_link, $sql_custall);
    checkSQL($db_link, $query_custall); // Fixed: Removed 3rd argument
    return $query_custall;
}

function getCustOverdue($db_link){
    $last_subscr = time() - convertDays(365);
    $sql_custoverdue = "SELECT * FROM customer WHERE cust_active = 1 AND cust_lastsub < $last_subscr ORDER BY cust_lastsub, cust_id";
    $query_custoverdue = mysqli_query($db_link, $sql_custoverdue);
    checkSQL($db_link, $query_custoverdue); // Fixed: Removed 3rd argument
    return $query_custoverdue;
}

function buildCustNo($db_link){
    $sql_maxID = "SELECT MAX(cust_id) AS maxid FROM customer";
    $query_maxID = mysqli_query($db_link, $sql_maxID);
    checkSQL($db_link, $query_maxID);
    $result_maxID = mysqli_fetch_array($query_maxID);

    $cnParts = explode("%", $_SESSION['set_cno']);
    $cnCount = count($cnParts);

    $i = 0;
    $custNo = "";
    for ($i = 1; $i < $cnCount; $i++) {
        switch($cnParts[$i]){
            case "N": $custNo .= ($result_maxID['maxid'] + 1); break;
            case "Y": $custNo .= date("Y",time()); break;
            case "M": $custNo .= date("m",time()); break;
            case "D": $custNo .= date("d",time()); break;
            default:  $custNo .= $cnParts[$i];
        }
    }
    return $custNo;
}

function getEmployee($db_link, $empl_id){
    $sql_empl = "SELECT * FROM employee LEFT JOIN user ON employee.empl_id = user.empl_id WHERE employee.empl_id = $empl_id";
    $query_empl = mysqli_query($db_link, $sql_empl);
    checkSQL($db_link, $query_empl); // Fixed: Removed 3rd argument
    $result_empl = mysqli_fetch_assoc($query_empl);
    return $result_empl;
}

function getEmplCurr($db_link){
    $timestamp = time();
    $sql_emplcurr = "SELECT * FROM employee LEFT JOIN emplsex ON employee.emplsex_id = emplsex.emplsex_id LEFT JOIN emplmarried ON employee.emplmarried_id = emplmarried.emplmarried_id WHERE empl_id != 0 AND (empl_out > $timestamp OR empl_out IS NULL) ORDER BY empl_id";
    $query_emplcurr = mysqli_query($db_link, $sql_emplcurr);
    checkSQL($db_link, $query_emplcurr); // Fixed: Removed 3rd argument
    return $query_emplcurr;
}

function getEmplPast($db_link){
    $timestamp = time();
    $sql_emplpast = "SELECT * FROM employee LEFT JOIN emplsex ON employee.emplsex_id = emplsex.emplsex_id LEFT JOIN emplmarried ON employee.emplmarried_id = emplmarried.emplmarried_id WHERE empl_id != 0 AND empl_out < $timestamp ORDER BY empl_id";
    $query_emplpast = mysqli_query($db_link, $sql_emplpast);
    checkSQL($db_link, $query_emplpast); // Fixed: Removed 3rd argument
    return $query_emplpast;
}

function buildEmplNo($db_link){
    $sql_maxID = "SELECT MAX(empl_id) AS maxid FROM employee";
    $query_maxID = mysqli_query($db_link, $sql_maxID);
    checkSQL($db_link, $query_maxID); // Fixed: Removed 3rd argument
    $result_maxID = mysqli_fetch_array($query_maxID);

    $enParts = explode("%", $_SESSION['set_eno']);
    $enCount = count($enParts);

    $i = 0;
    $emplNo = "";
    for ($i = 1; $i < $enCount; $i++) {
        switch($enParts[$i]){
            case "N": $emplNo .= ($result_maxID['maxid'] + 1); break;
            case "Y": $emplNo .= date("Y",time()); break;
            case "M": $emplNo .= date("m",time()); break;
            case "D": $emplNo .= date("d",time()); break;
            default:  $emplNo .= $enParts[$i];
        }
    }
    return $emplNo;
}

function getLtransOverdue($db_link){
    $timestamp = time();
    $sql_overdue = "SELECT * FROM ltrans LEFT JOIN loans ON ltrans.loan_id = loans.loan_id LEFT JOIN customer ON loans.cust_id = customer.cust_id WHERE ltrans_due <= $timestamp AND ltrans_date IS NULL AND loanstatus_id = 2 ORDER BY ltrans_due";
    $query_overdue = mysqli_query($db_link, $sql_overdue);
    checkSQL($db_link, $query_overdue); // Fixed: Removed 3rd argument
    return $query_overdue;
}

function getGroupLoanDetails($db_link, $loan_id){
    $sql_loan = "SELECT * FROM group_loans WHERE loan_id = $loan_id";
    $query_loan = mysqli_query($db_link, $sql_loan);
    checkSQL($db_link, $query_loan);
    $result_loan = mysqli_fetch_assoc($query_loan);
    return $result_loan;
}

function getGroupLoanBalance($db_link, $loan_id){
    $sql_balances = "SELECT ltrans_principaldue, ltrans_interestdue, ltrans_principal, ltrans_interest 
                     FROM group_ltrans 
                     WHERE group_ltrans.loan_id = ?";
    $stmt = mysqli_prepare($db_link, $sql_balances);
    mysqli_stmt_bind_param($stmt, "i", $loan_id);
    mysqli_stmt_execute($stmt);
    $query_balances = mysqli_stmt_get_result($stmt);

    if (!$query_balances) {
        return array("pdue" => 0, "idue" => 0, "ppaid" => 0, "ipaid" => 0, "balance" => 0, "error" => "Failed to retrieve loan balance");
    }

    $loan_balances = array("pdue" => 0, "idue" => 0, "ppaid" => 0, "ipaid" => 0, "balance" => 0);
    while ($row_balances = mysqli_fetch_assoc($query_balances)){
        $loan_balances['pdue'] += $row_balances['ltrans_principaldue'];
        $loan_balances['idue'] += $row_balances['ltrans_interestdue'];
        $loan_balances['ppaid'] += $row_balances['ltrans_principal'];
        $loan_balances['ipaid'] += $row_balances['ltrans_interest'];
    }
    $loan_balances['balance'] = ($loan_balances['pdue'] + $loan_balances['idue']) - ($loan_balances['ppaid'] + $loan_balances['ipaid']);
    return $loan_balances;
}

function getLoanSecurities($db_link, $loan_id){
    $sql_secur = "SELECT * FROM securities WHERE loan_id = $loan_id";
    $query_secur = mysqli_query($db_link, $sql_secur);
    checkSQL($db_link, $query_secur);
    $securities = array();
    while ($row_secur = mysqli_fetch_assoc($query_secur)) $securities[] = $row_secur;
    return $securities;
}

function getGroupLoanSecurities($db_link, $loan_id){
    $sql_secur = "SELECT * FROM securities WHERE loan_id = $loan_id AND group_id IS NOT NULL";
    $query_secur = mysqli_query($db_link, $sql_secur);
    checkSQL($db_link, $query_secur);
    $securities = array();
    while ($row_secur = mysqli_fetch_assoc($query_secur)) 
        $securities[] = $row_secur;
    return $securities;
}

function getSecurity($db_link, $sec_id){
    $sql_sec = "SELECT * FROM securities LEFT JOIN loans ON securities.loan_id = loans.loan_id LEFT JOIN customer ON securities.cust_id = customer.cust_id WHERE sec_id = $sec_id";
    $query_sec = mysqli_query($db_link, $sql_sec);
    checkSQL($db_link, $query_sec);
    $result_sec = mysqli_fetch_assoc($query_sec);
    return $result_sec;
}

function getgroupSecurity($db_link, $sec_id){
    // Fixed: `group` is a reserved word
    $sql_sec = "SELECT * FROM securities LEFT JOIN group_loans ON securities.loan_id = group_loans.loan_id LEFT JOIN `group` ON securities.group_id = `group`.cust_id WHERE sec_id = $sec_id";
    $query_sec = mysqli_query($db_link, $sql_sec);
    checkSQL($db_link, $query_sec);
    $result_sec = mysqli_fetch_assoc($query_sec);
    return $result_sec;
}
?>
