
<?php 
require 'functions.php'; 
require 'function_group_loans.php'; 
checkLogin(); 
$db_link = connect(); 
getGroupLoanID($db_link); 
$timestamp = time(); 


if (isset($_POST['updatestatus'])) {
    // Sanitize user input
    $loan_principal = $_SESSION['loan_principal'];
    $loan_interest = $_SESSION['loan_interest'];
    $loan_period = $_SESSION['loan_period'];
    $loan_issued = $_SESSION['loan_issued'];
    $loan_fee_receipt = sanitize($db_link, $_POST['loan_fee_receipt']);
    $loan_status = sanitize($db_link, $_POST['loan_status']);
    $loan_dateout = sanitize($db_link, $_POST['loan_dateout']);
    $loan_principalapproved = sanitize($db_link, $_POST['loan_principalapproved']);

    if ($loan_status == 2 AND $loan_issued == 0) {
        // Loan is approved and not yet issued
        if (!empty($loan_dateout)) {
            $loan_dateout = strtotime($loan_dateout);
            $sql_update = "UPDATE group_loans SET 
                loanstatus_id = '$loan_status', 
                loan_issued = 1, 
                loan_dateout = '$loan_dateout', 
                loan_principalapproved = '$loan_principalapproved' 
                WHERE loan_id = '$_SESSION[loan_id]'";
        } else {
            // Handle the case when loan_dateout is empty
            $sql_update = "UPDATE group_loans SET 
                loanstatus_id = '$loan_status', 
                loan_issued = 1, 
                loan_principalapproved = '$loan_principalapproved' 
                WHERE loan_id = '$_SESSION[loan_id]'";
        }
    } elseif ($loan_status == 2 AND $loan_issued == 1) {
        // Loan is already issued, just update loan status
        $sql_update = "UPDATE group_loans SET 
            loanstatus_id = '$loan_status' 
            WHERE loan_id = '$_SESSION[loan_id]'";
    } elseif ($loan_status == 5) {
        // Loan is paid off
        $sql_update = "UPDATE group_loans SET 
            loanstatus_id = '$loan_status' 
            WHERE loan_id = '$_SESSION[loan_id]'";
    } else {
        $sql_update = "UPDATE group_loans SET 
            loanstatus_id = '$loan_status', 
            loan_principalapproved = '$loan_principalapproved' 
            WHERE loan_id = '$_SESSION[loan_id]'";
    }
    $query_update = mysqli_query($db_link, $sql_update);
    checkSQL($db_link, $query_update);

    // Check if installments have already been generated for this loan
    $sql_check_ltrans = "SELECT * FROM group_ltrans WHERE loan_id = '$_SESSION[loan_id]'";
    $query_check_ltrans = mysqli_query($db_link, $sql_check_ltrans);
    if (mysqli_num_rows($query_check_ltrans) == 0 && $loan_status == 2) {
        // Loan is approved and not yet issued, so generate installments
        // Calculate monthly interest
        $monthly_interest = $loan_principalapproved * ($loan_interest / 100);
        // Calculate weekly interest
        $weekly_interest = $monthly_interest / 4;
        // Calculate weekly principal
        $weekly_principal = $loan_principalapproved / ($loan_period * 4);
        // Calculate Due Dates & Due Payments and insert them into LTRANS
        $ltrans_due = strtotime("+1 week", $loan_dateout); // Start with a week after disbursement
        $outstanding_balance = $loan_principalapproved;
        for ($i = 1; $i <= $loan_period * 4; $i++) {
            // Fixed interest per week
            if ($i % 4 == 0 || $i == $loan_period * 4) {
                $interest_due = $weekly_interest;
            } else {
                $interest_due = $weekly_interest;
            }
            // Fixed principal per week
            $principal_due = $weekly_principal;
            // If the principal due exceeds the outstanding balance, set it to the outstanding balance
            if ($principal_due > $outstanding_balance) {
                $principal_due = $outstanding_balance;
            }
                // Insert into LTRANS
                $sql_insert_ltrans = "INSERT INTO group_ltrans (loan_id, ltrans_due, ltrans_principaldue, ltrans_interestdue, user_id) VALUES ('$_SESSION[loan_id]', '$ltrans_due', '$principal_due', '$interest_due', '$_SESSION[log_id]')";
                $query_insert_ltrans = mysqli_query($db_link, $sql_insert_ltrans);
                checkSQL($db_link, $query_insert_ltrans);
                // Update outstanding balance
                $outstanding_balance -= $principal_due;
                // Next due date
                $ltrans_due = strtotime("+1 week", $ltrans_due); // Add 1 week
            }
    } elseif ($loan_status == 2 AND $loan_issued == 1) {
        // Loan is already issued, just update loan status
        $sql_update = "UPDATE group_loans SET loanstatus_id = '$loan_status' WHERE loan_id = $_SESSION[loan_id]";
        $query_update = mysqli_query($db_link, $sql_update);
        checkSQL($db_link, $query_update);
    } else {
        // Update loan status
        $sql_update = "UPDATE group_loans SET loanstatus_id = '$loan_status' WHERE loan_id = $_SESSION[loan_id]";
        $query_update = mysqli_query($db_link, $sql_update);
        checkSQL($db_link, $query_update);
    }

    if ($loan_issued == 0 && $_POST['loan_issued'] == 1) {
        // Loan is being issued for the first time
        $sql_issue = "UPDATE group_loans SET loan_issued = '1', loan_dateout = UNIX_TIMESTAMP(), loan_principalapproved = '$loan_principalapproved' WHERE loan_id = '$_SESSION[loan_id]'";
        $query_issue = mysqli_query($db_link, $sql_issue);
        checkSQL($db_link, $query_issue);
    }

    //Insert Loan Fee into INCOMES
    $loan_fee = $loan_principalapproved / 100 * $_SESSION['fee_loan'];
    $sql_inc_lf = "INSERT INTO incomes (group_id, loan_id, inctype_id, inc_amount, inc_date, inc_receipt, inc_created, user_id) VALUES ('$_SESSION[group_id]', '$_SESSION[loan_id]', '3', '$loan_fee', '$loan_dateout', '$loan_fee_receipt', '$timestamp', '$_SESSION[log_id]')";
    $query_inc_lf = mysqli_query($db_link, $sql_inc_lf);
    checkSQL($db_link, $query_inc_lf);

    // Calculate balance
$sql_balance = "SELECT (COALESCE(SUM(ltrans_principaldue), 0) + COALESCE(SUM(ltrans_interestdue), 0)) - (COALESCE(SUM(ltrans_principaldpaid), 0) + COALESCE(SUM(ltrans_interestdpaid), 0)) AS balance FROM group_ltrans WHERE loan_id = '$_SESSION[loan_id]'";
$query_balance = mysqli_query($db_link, $sql_balance);
$row_balance = mysqli_fetch_assoc($query_balance);
$balance = $row_balance['balance'] ?? 0;

// Update loan status if balance is 0 and loan is approved and issued
if ($balance == 0 && $result_loan['loan_issued'] == 1 && $result_loan['loanstatus_id'] == 2) {
    $sql_status_update = "UPDATE group_loans SET loanstatus_id = '5' WHERE loan_id = '$_SESSION[loan_id]'";
    $query_status_update = mysqli_query($db_link, $sql_status_update);
    checkSQL($db_link, $query_status_update);
}
    header('Location: group_loan.php?loan_id='.$_SESSION['loan_id']);
}
	/** MAKE REPAYMENT Button **/
	if(isset($_POST['repay'])){

		//Sanitize User Input
		$loan_repay_amount = sanitize($db_link, $_POST['loan_repay_amount']);
		$loan_repay_receipt = sanitize($db_link, $_POST['loan_repay_receipt']);
		$loan_repay_date = sanitize($db_link, strtotime($_POST['loan_repay_date']));
		$loan_repay_sav = sanitize($db_link, $_POST['loan_repay_sav']);

		// If the paid amount exceeds the total outstanding balance,
		// the outstanding principal and interest are served
		// and the rest goes to savings.
		if ($loan_repay_amount > $_SESSION['balance']){
			$loan_repay_interest = $_SESSION['i_balance'];
			$loan_repay_principal = $_SESSION['p_balance'];
			$loan_repay_savings = $loan_repay_amount - $loan_repay_interest - $loan_repay_principal;
		}

		// If, however, the paid amount is smaller than the total outstanding balance...
		else {
		    

			// Check if total interest has been paid off.
			if ($_SESSION['i_balance'] <= 0 AND $_SESSION['p_balance'] > 0){
					$loan_repay_interest = 0;
					$loan_repay_principal = $loan_repay_amount;
			}

  			// Check if total principal has been paid off.
			elseif ($_SESSION['i_balance'] > 0 AND $_SESSION['p_balance'] <= 0){
					$loan_repay_interest = $loan_repay_amount;
					$loan_repay_principal = 0;
			}
        
			// Otherwise, if principal AND interest both show an open balance...
			elseif ($_SESSION['i_balance'] > 0 AND $_SESSION['p_balance'] > 0){

				// Check if the paid amount is less than the interest due.
				// In that case, everything goes to interest only.
				if ($loan_repay_amount < $_SESSION['interest_sum']){
					$loan_repay_interest = $loan_repay_amount;
					$loan_repay_principal = 0;
				}

				// If, however, the paid amount exceeds
				// the due interest PLUS the total outstanding balance,
				// the excess money is used on interest.
				elseif ($loan_repay_amount > ($_SESSION['interest_sum'] + $_SESSION['p_balance'])){
					$loan_repay_principal = $_SESSION['p_balance'];
					$loan_repay_interest = $loan_repay_amount - $loan_repay_principal;
				}

				//Otherwise, the paid amount is split between interest and principal.
				// This is probably the most common case!
				else {
					$loan_repay_interest = $_SESSION['interest_sum'];
					$loan_repay_principal = $loan_repay_amount - $loan_repay_interest;
					$loan_repay_principalbal = $_SESSION['loan_principal'] / $_SESSION['loan_period'] - $loan_repay_principal;
					$loan_repay_interestbal  = $_SESSION['interest_sum'] - $loan_repay_interest;
				}
			}
		}
		// Check for smallest LTRANS_ID to determine whether an UPDATE or INSERT is needed
		$sql_ltransid = "SELECT MIN(ltrans_id) FROM group_ltrans WHERE loan_id = $_SESSION[loan_id] AND ltrans_date IS NULL AND ltrans_due IS NOT NULL";
		$query_ltransid = mysqli_query($db_link, $sql_ltransid);
		checkSQL($db_link, $query_ltransid);
		$ltransid_result = mysqli_fetch_assoc($query_ltransid);
		$ltransid = $ltransid_result['MIN(ltrans_id)'];

		if(!isset($ltransid)){
			$sql_insertrepay = "INSERT INTO group_ltrans (loan_id, ltrans_date, ltrans_principal, ltrans_interest, ltrans_receipt, ltrans_created, user_id) VALUES ($_SESSION[loan_id], $loan_repay_date, '$loan_repay_principal', '$loan_repay_interest', '$loan_repay_receipt', $timestamp, '$_SESSION[log_id]')";
			$query_insertrepay = mysqli_query($db_link, $sql_insertrepay);
			checkSQL($db_link, $query_insertrepay);

			// Get LTRANS_ID of latest entry
			$sql_ltransid = "SELECT MAX(ltrans_id) FROM group_ltrans WHERE loan_id = '$_SESSION[loan_id]' AND ltrans_receipt = '$loan_repay_receipt' AND ltrans_created = '$timestamp'";
			$query_ltransid = mysqli_query($db_link, $sql_ltransid);
			checkSQL($db_link, $query_ltransid);
			$ltransid_result = mysqli_fetch_row($query_ltransid);
			$ltransid = $ltransid_result[0];
		}
		else {
			$sql_updaterepay = "UPDATE group_ltrans SET ltrans_date = $loan_repay_date, ltrans_principal = '$loan_repay_principal', ltrans_interest = '$loan_repay_interest', ltrans_receipt = '$loan_repay_receipt', ltrans_created = '$timestamp', user_id = '$_SESSION[log_id]' WHERE ltrans_id = $ltransid";
			$query_updaterepay = mysqli_query($db_link, $sql_updaterepay);
			checkSQL($db_link, $query_updaterepay);
		}

		// If interest is paid, insert the amount into INCOMES
		if($loan_repay_interest > 0){
			$sql_incint = "INSERT INTO incomes (group_id, inctype_id, ltrans_id, inc_amount, inc_date, inc_receipt, inc_created, user_id) VALUES ('$_SESSION[group_id]', '4', '$ltransid', '$loan_repay_interest', '$loan_repay_date', '$loan_repay_receipt', $timestamp, '$_SESSION[log_id]')";
			$query_incint = mysqli_query($db_link, $sql_incint);
			checkSQL($db_link, $query_incint);
		}
         // If principal is paid, insert the amount into INCOMES
if($loan_repay_principal > 0){
    $sql_incprinc = "INSERT INTO incomes (group_id, inctype_id, ltrans_id, inc_amount, inc_date, inc_receipt, inc_created, user_id) VALUES ('$_SESSION[group_id]', '12', '$ltransid', '$loan_repay_principal', '$loan_repay_date', '$loan_repay_receipt', $timestamp, '$_SESSION[log_id]')";
    $query_incprinc = mysqli_query($db_link, $sql_incprinc);
    checkSQL($db_link, $query_incprinc);
}
	// Re-calculate interest payments
		if($_SESSION['set_intcalc'] == "modules/mod_inter_float.php") {
			$loan_balances = getLoanBalance($_SESSION['loan_id']);
			$loan_pBalance = $result_loan['loan_principalapproved'] - $loan_balances['ppaid'];
			updateInterFloat($db_link, $_SESSION['loan_id'], $loan_pBalance, $result_loan['loan_interest']);
		}	


		// Re-load loan.php
		header('Location: group_loan.php?loan_id='.$_SESSION['loan_id']);
	}

	/** CHARGE DEFAULT FINE Button **/
	if(isset($_POST['fine'])){

		// Sanitize user input
		$fine_amount = sanitize($db_link, $_POST['fine_amount']);
		$fine_receipt = sanitize($db_link, $_POST['fine_receipt']);
		$fine_date = strtotime(sanitize($db_link, $_POST['fine_date']));
		if(isset($_POST['fine_sav'])) $fine_sav = sanitize($db_link, $_POST['fine_sav']);
		else $fine_sav = 0;

		// Get LTRANS_ID for chargable transaction
		$sql_ltrans = "SELECT MIN(ltrans_id) FROM group_ltrans WHERE group_ltrans.loan_id = '$_SESSION[loan_id]' AND ltrans_due < '$timestamp' AND ltrans_fined = '0' AND ltrans_date IS NULL";
		$query_ltrans = mysqli_query($db_link, $sql_ltrans);
		checkSQL($db_link, $query_ltrans);
		$ltrans = mysqli_fetch_row($query_ltrans);

		// Flag loan transaction as 'fined'
		$sql_ltrans_fined = "UPDATE group_ltrans SET ltrans_fined = '1', ltrans_created = '$timestamp', user_id = '$_SESSION[log_id]' WHERE ltrans_id = '$ltrans[0]'";
		$query_ltrans_fined = mysqli_query($db_link, $sql_ltrans_fined);
		checkSQL($db_link, $query_ltrans_fined);

		// Insert fine as income in INCOMES
		$sql_fine_inc = "INSERT INTO incomes (group_id, ltrans_id, sav_id, inctype_id, inc_amount, inc_date, inc_receipt, inc_created, user_id) VALUES ('$_SESSION[group_id]', '$ltrans[0]', '$sav_id[0]', '5', '$fine_amount', '$fine_date', '$fine_receipt', $timestamp, '$$_SESSION[log_id]')";
		$query_fine_inc = mysqli_query($db_link, $sql_fine_inc);
		checkSQL($db_link, $query_fine_inc);

		header('Location: group_loan.php?loan_id='.$_SESSION[loan_id]);
	}

	
$loan_id = $_GET['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);


// Select details of current loan from LOANS, LOANSTATUS, CUSTOMER
$sql_group_loan = "SELECT * FROM group_loans 
                   WHERE group_loans.loan_id = $_SESSION[loan_id]";
$query_group_loan = mysqli_query($db_link, $sql_group_loan);
checkSQL($db_link, $query_group_loan);
$result_group_loan = mysqli_fetch_assoc($query_group_loan);

// Select Instalments from LTRANS
	$sql_duedates = "SELECT * FROM group_ltrans LEFT JOIN user ON group_ltrans.user_id = user.user_id WHERE loan_id = $_SESSION[loan_id] ORDER BY ltrans_id";
	$query_duedates = mysqli_query($db_link, $sql_duedates);
	checkSQL($db_link, $query_duedates);
	
	//Prepare array data export
	$ltrans_exp_date = date("Y-m-d",time());
	$_SESSION['ltrans_export'] = array();
	$_SESSION['ltrans_exp_title'] = $_SESSION['group_id'].'_loan_'.$ltrans_exp_date;
?>

<!DOCTYPE html>
<html>
   <head>
    <?php includeHead('Loan Details',0) ?>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
    <script src="functions_validate.js"></script>
    <script src="function_randCheck.js"></script>
</head> 
<body>
    <!-- MENU -->
    <?php includeMenu(3); ?>
    <div id="menu_main">
        <a href="group.php?cust=<?php echo $_SESSION['group_id'] ?>">groups</a>
        <a href="loans_search.php">Search</a>
        <a href="loans_act.php">Active Loans</a>
        <a href="loans_pend.php">Pending Loans</a>
        <a href="loans_securities.php">Loan Securities</a>
    </div>

<!-- LEFT SIDE: Loan Details -->
<div class="content_left">
    <?php 
    $group_name_query = "SELECT group_name FROM `group` WHERE group_id = '$result_group_loan[group_id]'";
    $group_name_result = mysqli_query($db_link, $group_name_query);
    checkSQL($db_link, $group_name_result);
    $group_name_row = mysqli_fetch_assoc($group_name_result);
    $group_name = $group_name_row['group_name'];
    ?>
    <p class="heading_narrow class="left"><?php echo $group_name ?> - Loan No. <?php echo $result_group_loan['loan_no'] ?></p>
   <p class="heading">Group Members</p> 
    <form name="loaninfo" action="group_loan.php" method="post" onSubmit="return firstIssue(this)">
        <table id="tb_fields">
            <colgroup>
                <col width="15%">
                <col width="35%">
                <col width="15%">
                <col width="35%">
            </colgroup>
            <?php 
            // Assuming you have a table for group members
            $sql_group_members = "SELECT member_name, member_contact FROM group_member WHERE group_id = '$result_group_loan[group_id]'";
            $query_group_members = mysqli_query($db_link, $sql_group_members); 
            checkSQL($db_link, $query_group_members); 
            $member_count = 1;
            while ($row_group_member = mysqli_fetch_assoc($query_group_members)) {
                if ($member_count <= 5) {
                    echo '<tr>
                            <td>Member '.$member_count.' Name:</td>
                            <td><input type="text" name="member_'.$member_count.'_name" disabled="disabled" value="'.$row_group_member['member_name'].'" /></td>
                            <td>Contact:</td>
                            <td><input type="text" name="member_'.$member_count.'_contact" disabled="disabled" value="'.$row_group_member['member_contact'].'" /></td>
                        </tr>';
                    $member_count++;
                }
            }
            // Fill in empty member fields if less than 5 members
            while ($member_count <= 5) {
                echo '<tr>
                        <td>Member '.$member_count.' Name:</td>
                        <td><input type="text" name="member_'.$member_count.'_name" disabled="disabled" value="" /></td>
                        <td>Contact:</td>
                        <td><input type="text" name="member_'.$member_count.'_contact" disabled="disabled" value="" /></td>
                    </tr>';
                $member_count++;
            }
            ?>
            <tr>
                 <td>LC1 Name:</td>
                <td><input type="text" name="group_name" disabled="disabled" value="<?php echo $result_group_loan['lc1_name']?>" /></td>
                <td>Contact:</td>
                <td><input type="text" name="group_contact" disabled="disabled" value="<?php echo $result_group_loan['lc1_contact']?>" /></td>
                
            </tr>
           <td>Purpose:</td>
						<td><input type="text" name="loan_purpose" disabled="disabled" value="<?PHP echo $result_loan['loan_purpose']?>" /></td>
					</tr>
					<tr>
						<td>Principal applied:</td>
						<td><input type="text" name="loan_principal" disabled="disabled" value="<?PHP echo number_format($result_loan['loan_principal']).' '.$_SESSION['set_cur'] ?>" /></td>
						<td>Interest Rate:</td>
						<td><input type="text" name="loan_interest" disabled="disabled" value="<?PHP echo $result_loan['loan_interest'].'% per Month'?>" /></td>
					</tr>
					<tr>
						<td>No of Installements:</td>
						<td><input type="text" name="loan_period" disabled="disabled" value="<?PHP echo $result_loan['loan_period']?>" /></td>
						
					</tr>
				<?php 
$total_repay = $result_loan['loan_principal'] + ($result_loan['loan_principal'] * $result_loan['loan_interest'] / 100 * $result_loan['loan_period']);
$total_installment = $result_loan['loan_period'];
?>

<tr>
    <td>Total Repay:</td>
    <td><input type="text" name="loan_repaytotal" disabled="disabled" value="<?PHP echo number_format($total_repay).' '.$_SESSION['set_cur'] ?>"/></td>
    <td>Total Installment:</td>
   <td><input type="text" name="loan_installment" disabled="disabled" value="<?PHP echo number_format($result_loan['loan_repaytotal'] / $result_loan['loan_period']).' '.$_SESSION['set_cur'] ?>"/></td>
</tr>
<tr>
<td>LC1 Letter</td>
<td>
				<?php if (!empty($result_loan['letter_path'])) { ?>
  <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#letterModal"><i class="fa fa-eye"></i></button>

  <div class="modal fade" id="letterModal" tabindex="-1" role="dialog" aria-labelledby="letterModalLabel" aria-hidden="true">
    <div class="modal-dialog modal-lg" role="document">
      <div class="modal-content">
        <div class="modal-header">
          <h5 class="modal-title" id="letterModalLabel">Letter</h5>
          <button type="button" class="close" data-dismiss="modal" aria-label="Close">
            <span aria-hidden="true">&times;</span>
          </button>
        </div>
        <div class="modal-body">
          <embed src="<?php echo $result_loan['letter_path']; ?>" type="application/pdf" width="100%" height="500">
        </div>
        <div class="modal-footer">
          <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
        </div>
      </div>
    </div>
  </div>
<?php } ?>
	
	
						
						</td>
					</tr>
			
					<tr>
						<td>Application Date:</td>
						<td>
							<input type="text" value="<?PHP echo date("d.m.Y", $result_loan['loan_date']) ?>" disabled="disabled" />
						</td>
					<td>Principal approved:</td>
						<td>
						<?PHP
						if($result_loan['loan_issued'] == 1)
							echo '<input type="text" disabled="disabled" value="'.number_format($result_loan['loan_principalapproved']).' '.$_SESSION['set_cur'].'" />';
						else
							echo '<input type="number" name="loan_principalapproved" placeholder="Approved Principal Amount" value="'.$result_loan['loan_principal'].'" />';
						?>
						</td>
					</tr>
   <tr>
    <td>Disbursed on:</td>
    <td>
        <input type="date" id="datepicker" name="loan_dateout" 
        <?PHP if($result_loan['loan_issued'] == 1) { 
            echo ' disabled="disabled"'; 
            echo ' value="'.date("Y-m-d", $result_loan['loan_dateout']).'" '; 
        } ?> />
    </td>
    <td>Status:</td>
    <td>
        <select name="loan_status" id="loan_status" size="1">
            <?PHP //Select Loanstatus from LOANSTATUS
            $sql_loanstatus = "SELECT * FROM loanstatus";
            $query_loanstatus = mysqli_query($db_link, $sql_loanstatus);
            while ($row_status = mysqli_fetch_assoc($query_loanstatus)){
                echo '<option value="'.$row_status['loanstatus_id'].'"';
                if ($row_status['loanstatus_id'] == $result_loan['loanstatus_id']) echo ' selected="selected" ';
                echo '>'.$row_status['loanstatus_status'].'</option>';
            } ?>
        </select>
    </td>
</tr>

					<tr>
						<td colspan=4 style="text-align:center">
							<input type="hidden" name="loan_issued" id="loan_issued" value="<?PHP echo $result_loan['loan_issued']?>" />
							<input type="hidden" name="loan_fee_receipt" id="loan_fee_receipt" value="" />
							<input type="submit" name="updatestatus" value="Update" />
						</td>
					</tr>
				</table>
				<?PHP
				//Pass relevant data to SESSION
				$_SESSION['loan_principal'] = $result_loan['loan_principalapproved'];
				$_SESSION['loan_interest'] = $result_loan['loan_interest'];
				$_SESSION['loan_period'] = $result_loan['loan_period'];
				$_SESSION['loan_fee'] = $result_loan['loan_fee'];
				$_SESSION['loan_issued'] = $result_loan['loan_issued'];
				?>
			</form>
		</div>

		<!-- RIGHT SIDE: Payment Transactions -->
<div class="content_right">
  <table id="tb_table">
    <tr>
      <form class="export" action="ltrans_export.php" method="post">
        <th class="title" colspan="15">Loan Payment Transactions
          <!-- Export Button -->
          <input type="submit" name="export_rep" value="Export" />
        </th>
      </form>
    </tr>
    <tr>
      <th>Date due</th>
      <th>Date paid</th>
      <th>Princ. due</th>
      <th>Princ. paid</th>
      <th>Princ. bal</th>
      <th>Inter. due</th>
      <th>Inter. paid</th>
      <th>Inter. bal</th>
      <th>princ & inter due</th>
      <th>princ & inter paid</th>
      <th>princ & inter bal</th>
      <th>mode </th>
      <th>Fined</th>
      <th>Updated</th>
      <th>Delete</th>
    </tr>
    <?PHP
    $p_due = 0;
    $p_paid = 0;
    $p_bal = 0;
    $i_due = 0;
    $i_paid = 0;
    $i_bal = 0;
    $loan_default = 0;
    $int_sum_set = 0;

    // Initialize variables to keep track of the balance
    $cumulative_principal_balance = 0;
    $cumulative_interest_balance = 0;

    while ($row_duedates = mysqli_fetch_assoc($query_duedates)) {
      echo '<tr>';

      // Date due
      if ($row_duedates['ltrans_due'] === NULL) {
        echo '<td></td>';
      } elseif ($row_duedates['ltrans_due'] < $timestamp AND $row_duedates['ltrans_principaldue'] > $row_duedates['ltrans_principal'] AND $row_duedates['ltrans_fined'] == 0) {
        echo '<td class="warn">' . date("d.m.Y", $row_duedates['ltrans_due']) . '</td>';
      } else {
        echo '<td>' . date("d.m.Y", $row_duedates['ltrans_due']) . '</td>';
      }

      // Date paid
      if ($row_duedates['ltrans_date'] === NULL) {
        echo '<td></td>';
      } else {
        echo '<td>' . date("d.m.Y", $row_duedates['ltrans_date']) . '</td>';
      }

      // Principal due
      if ($row_duedates['ltrans_principaldue'] === NULL) {
        echo '<td></td>';
      } else {
        echo '<td>' . number_format($row_duedates['ltrans_principaldue']) . '</td>';
      }

      // Principal paid
      if ($row_duedates['ltrans_principal'] === NULL) {
        echo '<td></td>';
      } else {
        echo '<td>' . number_format($row_duedates['ltrans_principal']) . '</td>';
      }

      // Calculate principal balance
      $cumulative_principal_balance += ($row_duedates['ltrans_principaldue'] - $row_duedates['ltrans_principal']);
      echo '<td>' . number_format($cumulative_principal_balance) . '</td>';

      // Interest due
      if ($row_duedates['ltrans_interestdue'] === NULL) {
        echo '<td></td>';
      } else {
        echo '<td>' . number_format($row_duedates['ltrans_interestdue']) . '</td>';
      }

      // Interes paid
      if ($row_duedates['ltrans_interest'] === NULL) {
        echo '<td></td>';
      } else {
        echo '<td>' . number_format($row_duedates['ltrans_interest']) . '</td>';
      }

      // Calculate interest balanc
      $cumulative_interest_balance += ($row_duedates['ltrans_interestdue'] - $row_duedates['ltrans_interest']);
      echo '<td>' . number_format($cumulative_interest_balance) . '</td>';

      // Princ & inter due
      echo '<td>' . number_format($row_duedates['ltrans_principaldue'] + $row_duedates['ltrans_interestdue']) . '</td>';

      // Princ & inte paid
      echo '<td>' . number_format($row_duedates['ltrans_principal'] + $row_duedates['ltrans_interest']) . '</td>';

      // Prin & inter ba
      echo '<td>' . number_format($cumulative_principal_balance + $cumulative_interest_balance) . '</td>';

      echo '<td>' . $row_duedates['ltrans_receipt'] . '</td>';

    if ($row_duedates['ltrans_fined'] == 1) {
        $sql_fine_amount = "SELECT inc_amount FROM incomes WHERE ltrans_id = '".$row_duedates['ltrans_id']."' AND inctype_id = '5'";
        $query_fine_amount = mysqli_query($db_link, $sql_fine_amount);
        checkSQL($db_link, $query_fine_amount);
        if (mysqli_num_rows($query_fine_amount) > 0) {
            $fine_amount_row = mysqli_fetch_assoc($query_fine_amount);
            echo '<td>'.number_format($fine_amount_row['inc_amount']).'</td>';
        } else {
            echo '<td>No fine income found</td>';
        }
    } else {
        echo '<td></td>';
    }

echo '<td>'.$row_duedates['user_name'].'</td> <td>';
if ($_SESSION['log_delete'] == 1) echo '<a href="group_ltrans_del.php?lt_id='.$row_duedates['ltrans_id'].'" onClick="return randCheck();"><i class="fa fa-remove fa-lg"></i></a>';
echo '</td> </tr>';
$p_due = $p_due + $row_duedates['ltrans_principaldue'];
$p_paid = $p_paid + $row_duedates['ltrans_principal'];
$p_bal = ($p_due - $p_paid)+ $row_duedates['ltrans_principalbal'];
$i_due = $i_due + $row_duedates['ltrans_interestdue'];
$i_paid = $i_paid + $row_duedates['ltrans_interest'];
$i_bal = ($i_due - $i_paid )+ $row_duedates['ltrans_interestbal'];
if ($row_duedates['ltrans_date'] === NULL && $int_sum_set == 0){
    $_SESSION['interest_sum'] = $row_duedates['ltrans_interestdue'];
    $int_sum_set = 1;
} elseif ($int_sum_set == 0) $_SESSION['interest_sum'] = 0;
if ($row_duedates['ltrans_date'] == NULL && $row_duedates['ltrans_due'] != NULL && $row_duedates['ltrans_due'] < $timestamp && $row_duedates['ltrans_fined'] == 0) $loan_default++;
if ($row_duedates['ltrans_fined'] == 0) $exp_fined='No'; else $exp_fined='Yes';
//Prepare data for export to Excel file
array_push($_SESSION['ltrans_export'], array("Date due" => date("d.m.Y",$row_duedates['ltrans_due']), "Date paid" => date("d.m.Y",$row_duedates['ltrans_date']), "Principial due" => $row_duedates['ltrans_principaldue'], "Principal paid" => $row_duedates['ltrans_principal'], "Principal bal" => $row_duedates['ltrans_principalbal'],"Interest due" => $row_duedates['ltrans_interestdue'], "Interest paid" => $row_duedates['ltrans_interest'],"Interest bal" => $row_duedates['ltrans_interestbal'], "Receipt" => $row_duedates['ltrans_receipt'], "Fined" => $exp_fined));
} //Pass relevant data to SESSION
$_SESSION['p_due'] = $p_due;
$_SESSION['p_paid'] = $p_paid;
$_SESSION['i_due'] = $i_due;
$_SESSION['i_paid'] = $i_paid;
$_SESSION['p_balance'] = $p_due - $p_paid;
$_SESSION['i_balance'] = $i_due - $i_paid;
$_SESSION['balance'] = $p_due - $p_paid + $i_due - $i_paid;
if (isset($_SESSION['interest_sum']) AND $_SESSION['interest_sum'] == 0) $_SESSION['interest_sum'] = $_SESSION['i_balance'];
?>
<tr class="balance"> 
    <td>Total:</td> 
    <td></td> 
    <td><?PHP echo number_format($_SESSION['loan_principal']); ?></td> 
    <td><?PHP echo number_format($p_paid); ?></td> 
    <td><?PHP echo number_format($p_bal); ?></td> 
    <td><?PHP echo number_format($i_due); ?></td> 
    <td><?PHP echo number_format($i_paid); ?></td> 
    <td><?PHP echo number_format($i_bal); ?></td> 
    <td colspan="10"></td> 
</tr>
<tr> 
    <td>Remaining:</td> 
    <td></td> 
    <td><?PHP echo number_format($_SESSION['loan_principal'] - $p_paid); ?></td> 
    <td></td> 
    <td><?PHP echo number_format($i_due - $i_paid); ?></td> 
    <td colspan="10"></td> 
</tr>
</table>
<!-- MAKE REPAYMENT Frm -->
<?PHP if ($result_loan['loanstatus_id'] == 2 && $_SESSION['balance'] > 0) echo ' 
<form name="loan_repay" action="group_loan.php" method="post" onSubmit="return validate(this)">
    <table id="tb_fields" style="width:75%">
        <tr>
            <td>Date Paid:</td>
            <td><input type="text" id="datepicker2" name="loan_repay_date" value="'.date("d.m.Y", $timestamp).'" placeholder="DD.MM.YYYY" /></td>
            <td>Amount Paid:</td>
            <td><input type="number" name="loan_repay_amount" class="defaultnumber" placeholder="'.$_SESSION['set_cur'].'" /></td>
        </tr>
        <tr>
            <td>Mode of payment:</td>
            <td><input type="text" name="loan_repay_receipt" class="defaultnumber" placeholder="payment mode" /></td>
            <td></td>
        </tr>
        <tr>
            <td class="center" colspan=4>
                <input type="submit" name="repay" value="Make Repayment" />
            </td>
        </tr>
    </table>
</form>';
	?>
			<?php
if (isset($_POST['update_comments'])) {
    $loan_recovery_comments = sanitize($db_link, $_POST['loan_recovery_comments']);
    $loan_id = $_SESSION['loan_id']; // Make sure loan_id is available in session

    $sql_update_comments = "UPDATE group_loans SET loan_recovery_comments = '$loan_recovery_comments' WHERE loan_id = $loan_id";
    $query_update_comments = mysqli_query($db_link, $sql_update_comments);
    checkSQL($db_link, $query_update_comments);

    // Redirect back to the particular loan page
    header("Location: group_loan.php?loan_id=$loan_id&status=success"); // Pass status param
    exit();
}

// Check for success status
if (isset($_GET['status']) && $_GET['status'] == 'success') {
    echo "<p style='color:green;'>Comment updated successfully!</p>";
}
?>

<form name="update_comments_form" action="" method="post">
  <tr>
    <td>Recovery Comments:</td>
    <td colspan="3">
      <textarea style="width:100%" name="loan_recovery_comments"><?PHP echo $result_loan['loan_recovery_comments']; ?></textarea>
      <input type="submit" name="update_comments" value="Update Comments">
    </td>
  </tr>
</form>
<!-- CHARGE DEFAULT FINE Form -->
			<form name="default_fine" method="post" action="group_loan.php" onSubmit="return validateFine(this)">
				<?PHP
				if ($result_loan['loanstatus_id'] == 2 && $loan_default != 0)
					echo '<table id="tb_fields" style="width:75%; background:#a7dbd8">
									<tr>
										<td>Date:</td>
										<td>
											<input type="text" id="datepicker3" name="fine_date" value="'.date("d.m.Y", $timestamp).'" placeholder="DD.MM.YYYY" />
										</td>
										<td>Amount fined:</td>
										<td>
											<input type="number" name="fine_amount" class="defaultnumber" placeholder="'.$_SESSION['set_cur'].'" value="'.$_SESSION['fee_loanfine'].'" />
										</td>
									</tr>
									<tr>
										<td>Receipt No:</td>
										<td>
											<input type="number" name="fine_receipt" class="defaultnumber" placeholder="for Default Fine" />
										</td>
										<td></td>
										
									</tr>
									<tr>
										<td colspan=4 class="center">
											<input type="submit" name="fine" value="Charge Default Fine" />
										</td>
									</tr>
								</table>';
				?>
			</form>
		</div>
	</body>
</html>
