<?php
require_once './includes/config.php';
require_once './includes/sms_helper.php';
//require __DIR__ . '/includes/phone_helper.php';

class STKPushPaymentProcessor {
    private $pdo;
    private $logger;
    private $transactionTimeout = 30; // seconds

    public function __construct($pdo, $logger = null) {
        $this->pdo = $pdo;
        $this->logger = $logger;
        
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $this->pdo->setAttribute(PDO::ATTR_TIMEOUT, $this->transactionTimeout);
        
        $this->log("STK Push processor initialized");
    }

    // ========== MAIN PROCESSING METHOD ==========
    public function processSTKPush($data) {
        $this->log("Starting STK Push processing", ['data' => $data]);
        
        try {
            $this->validateSTKPushData($data);
            
            // Check for duplicate first to avoid unnecessary processing
            if ($this->isDuplicateTransaction($data['CheckoutRequestID'])) {
                $this->log("Duplicate STK Push transaction detected", ['checkout_request_id' => $data['CheckoutRequestID']]);
                return ['ResultCode' => 0, 'ResultDesc' => 'Duplicate transaction ignored'];
            }

            $processingResult = $this->processSTKTransaction($data);
            return $processingResult;

        } catch (Exception $e) {
            $this->log("STK Push processing error", [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString(),
                'data' => $data
            ]);
            
            return ['ResultCode' => 1, 'ResultDesc' => $e->getMessage()];
        }
    }

    private function processSTKTransaction($data) {
        $localTransaction = !$this->pdo->inTransaction();
        $memberAccount = null;
        $notificationData = null;
        $transactionId = null;

        try {
            if ($localTransaction) {
                $this->pdo->beginTransaction();
                $this->log("STK Transaction started");
            }

            // Parse account reference from callback metadata
            $accountReference = $this->extractAccountReference($data);
            $accountParts = $this->parseAccountReference($accountReference);
            $this->log("Account reference parsed", ['parts' => $accountParts]);
            
            list($personalNumber, $accountTypeCode, $subAccountCode) = $accountParts;
            $memberAccount = $this->getMemberAccountWithLock($personalNumber, $accountTypeCode, $subAccountCode);
            
            if (!$memberAccount) {
                throw new Exception("Member account not found for reference: {$accountReference}");
            }

            $isLoanAccount = $this->isLoanAccount($memberAccount['account_type_id']);
            $loanId = $isLoanAccount ? $this->getLoanIdForAccount($memberAccount['member_id'], $memberAccount['account_type_id']) : null;

            $amount = $this->parseAndValidateAmount($data['Amount']);
            $currency = $this->validateCurrency($data['Currency'] ?? 'KES');
            $receiptNumber = $this->validateReceiptNumber($data['MpesaReceiptNumber'] ?? $data['CheckoutRequestID']);

            // Record STK transaction
            $transactionData = [
                'transaction_id' => $data['CheckoutRequestID'],
                'receipt_number' => $receiptNumber,
                'amount' => $amount,
                'phone' => $data['PhoneNumber'],
                'account_reference' => $accountReference,
                'transaction_time' => $data['TransactionDate'] ?? date('YmdHis'),
                'short_code' => $data['BusinessShortCode'] ?? MPESA_SHORTCODE,
                'member_id' => $memberAccount['member_id'],
                'account_type_id' => $memberAccount['account_type_id'],
                'sub_account_code' => $subAccountCode,
                'raw_data' => json_encode($data),
                'result_code' => 0,
                'result_desc' => 'STK Processing started',
                'loan_id' => $loanId,
                'payment_method' => 'stk_push'
            ];
            
            $transactionId = $this->recordTransaction($transactionData);

            // Process accounting
            $this->processAccounting(
                $transactionId,
                $memberAccount,
                $amount,
                $currency,
                $receiptNumber,
                $data['TransactionDate'] ?? date('Y-m-d H:i:s'),
                $isLoanAccount,
                $loanId
            );

            if ($localTransaction) {
                $this->pdo->commit();
                $this->log("STK Transaction committed successfully");
            }

            // Prepare notification data
            $notificationData = [
                'member_id' => $memberAccount['member_id'],
                'amount' => $amount,
                'currency' => $currency,
                'receipt_number' => $receiptNumber,
                'account_name' => $memberAccount['account_name'],
                'account_number' => $memberAccount['account_number'],
                'account_type_id' => $memberAccount['account_type_id'],
                'sub_account_code' => $subAccountCode,
                'is_loan_account' => $isLoanAccount
            ];

            return ['ResultCode' => 0, 'ResultDesc' => 'STK Push payment processed successfully'];

        } catch (Exception $e) {
            if ($localTransaction && $this->pdo->inTransaction()) {
                try {
                    $this->pdo->rollBack();
                    $this->log("STK Transaction rolled back due to error");
                } catch (PDOException $rollbackEx) {
                    $this->log("Rollback failed", ['error' => $rollbackEx->getMessage()]);
                }
            }
            
            if ($transactionId) {
                $this->updateTransactionResult($transactionId, [
                    'ResultCode' => 1,
                    'ResultDesc' => $e->getMessage()
                ]);
            }
            
            throw $e; // Re-throw for outer catch
        } finally {
            // Send notifications after transaction processing
            if ($notificationData && $memberAccount) {
                $this->sendNotificationsInBackground($notificationData);
            }
        }
    }

    // ========== STK-SPECIFIC METHODS ==========
    private function validateSTKPushData($data) {
        $this->log("Validating STK Push data");
        $requiredFields = ['CheckoutRequestID', 'ResultCode', 'Amount', 'PhoneNumber'];
        $missingFields = [];
        
        foreach ($requiredFields as $field) {
            if (empty($data[$field])) {
                $missingFields[] = $field;
            }
        }
        
        if (!empty($missingFields)) {
            throw new Exception("Missing required STK Push fields: " . implode(', ', $missingFields));
        }
        
        // Check if STK push was successful
        if ($data['ResultCode'] != 0) {
            throw new Exception("STK Push failed with error: " . ($data['ResultDesc'] ?? 'Unknown error'));
        }
        
        $this->log("STK Push data validation passed");
    }

    private function extractAccountReference($data) {
        $this->log("Extracting account reference from STK data");
        
        // Try different possible locations for account reference
        $possibleFields = ['AccountReference', 'BillRefNumber', 'Metadata'];
        
        foreach ($possibleFields as $field) {
            if (!empty($data[$field])) {
                $this->log("Account reference found in field: " . $field);
                return $data[$field];
            }
        }
        
        // If no explicit reference, try to extract from callback metadata
        if (!empty($data['CallbackMetadata'])) {
            foreach ($data['CallbackMetadata']['Item'] as $item) {
                if ($item['Name'] === 'AccountReference' && !empty($item['Value'])) {
                    $this->log("Account reference found in CallbackMetadata");
                    return $item['Value'];
                }
            }
        }
        
        throw new Exception("Account reference not found in STK Push data");
    }

    // ========== DATABASE METHODS ==========
    private function isDuplicateTransaction($checkoutRequestId) {
        $this->log("Checking for duplicate STK transaction", ['checkout_request_id' => $checkoutRequestId]);
        
        $stmt = $this->pdo->prepare("
            SELECT id, status, accounting_completed 
            FROM mpesa_transactions 
            WHERE transaction_id = ? AND payment_method = 'stk_push'
            AND created_at > DATE_SUB(NOW(), INTERVAL 24 HOUR)
            LIMIT 1
        ");
        $stmt->execute([$checkoutRequestId]);
        $transaction = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $isDuplicate = $transaction && $transaction['status'] === 'completed' && $transaction['accounting_completed'];
        $this->log("STK duplicate check result", ['is_duplicate' => $isDuplicate]);
        
        return $isDuplicate;
    }

    private function recordTransaction($data) {
        $this->log("Starting STK transaction recording");
        
        // First verify table structure
        try {
            $stmt = $this->pdo->query("DESCRIBE mpesa_transactions");
            $tableColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
            $this->log("Table structure verified", ['columns' => $tableColumns]);
        } catch (Exception $e) {
            $this->log("Failed to verify table structure", ['error' => $e->getMessage()]);
            throw $e;
        }

        // Define all possible columns with defaults
        $defaultValues = [
            'transaction_id' => null,
            'receipt_number' => null,
            'amount' => 0,
            'phone_number' => $data['phone'] ?? null,
            'account_reference' => null,
            'transaction_time' => date('Y-m-d H:i:s'),
            'business_short_code' => MPESA_SHORTCODE,
            'member_id' => null,
            'account_type_id' => null,
            'sub_account_code' => null,
            'raw_data' => null,
            'result_code' => 0,
            'result_desc' => 'Pending processing',
            'status' => 'pending',
            'accounting_completed' => 0,
            'created_at' => date('Y-m-d H:i:s'),
            'updated_at' => null,
            'processed_at' => null,
            'loan_id' => null,
            'payment_method' => 'stk_push'
        ];

        // Merge with provided data
        $mergedData = array_merge($defaultValues, $data);
        $this->log("Merged STK transaction data", ['merged_data' => $mergedData]);

        // Build dynamic SQL
        $columns = [];
        $placeholders = [];
        $values = [];
        
        foreach ($mergedData as $key => $value) {
            if (in_array($key, $tableColumns)) {
                $columns[] = $key;
                $placeholders[] = '?';
                $values[] = $value;
            }
        }
        
        $sql = "INSERT INTO mpesa_transactions (" . 
               implode(', ', $columns) . ") VALUES (" . 
               implode(', ', $placeholders) . ")";
        
        $this->log("Preparing to execute SQL", [
            'sql' => $sql,
            'values' => $values,
            'columns_count' => count($columns),
            'values_count' => count($values)
        ]);

        try {
            $stmt = $this->pdo->prepare($sql);
            $result = $stmt->execute($values);
            
            if (!$result) {
                $errorInfo = $stmt->errorInfo();
                $this->log("STK insert failed", [
                    'error' => $errorInfo,
                    'sql' => $sql,
                    'values' => $values
                ]);
                throw new Exception("Failed to record STK transaction: " . ($errorInfo[2] ?? 'Unknown error'));
            }
            
            $transactionId = $this->pdo->lastInsertId();
            $this->log("STK transaction recorded successfully", ['id' => $transactionId]);
            return $transactionId;
            
        } catch (PDOException $e) {
            $this->log("STK transaction recording failed", [
                'error' => $e->getMessage(),
                'sql' => $sql,
                'values' => $values,
                'trace' => $e->getTraceAsString()
            ]);
            throw new Exception("STK transaction recording failed: " . $e->getMessage());
        }
    }

    private function updateTransactionResult($transactionId, $result) {
        $this->log("Updating transaction result", [
            'transaction_id' => $transactionId,
            'result' => $result
        ]);
        
        $stmt = $this->pdo->prepare("
            UPDATE mpesa_transactions 
            SET result_code = ?, result_desc = ?,
                status = 'failed', processed_at = NOW()
            WHERE id = ?
        ");
        $stmt->execute([$result['ResultCode'], $result['ResultDesc'], $transactionId]);
        
        $this->log("Transaction result updated");
    }

    // ========== ACCOUNTING METHODS ==========
    private function processAccounting(
        $transactionId,
        $memberAccount,
        $amount,
        $currency,
        $receiptNumber,
        $transactionDate,
        $isLoanAccount = false,
        $loanId = null
    ) {
        $this->log("Starting accounting processing", [
            'transactionId' => $transactionId,
            'isLoanAccount' => $isLoanAccount,
            'loanId' => $loanId
        ]);

        try {
            if (empty($memberAccount['financial_year_id'])) {
                throw new Exception("Missing financial year for account");
            }

            if ($isLoanAccount && $loanId) {
                $this->processLoanRepaymentAccounting(
                    $transactionId,
                    $memberAccount,
                    $amount,
                    $currency,
                    $receiptNumber,
                    $transactionDate,
                    $loanId
                );
            } else {
                $this->processSavingsDepositAccounting(
                    $transactionId,
                    $memberAccount,
                    $amount,
                    $currency,
                    $receiptNumber,
                    $transactionDate
                );
            }

            $this->updateTransactionStatus($transactionId, 'completed', true);
            $this->log("Accounting processing completed successfully");
        } catch (Exception $e) {
            $this->log("Accounting processing failed", [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            throw $e;
        }
    }

    private function processSavingsDepositAccounting(
        $transactionId,
        $memberAccount,
        $amount,
        $currency,
        $receiptNumber,
        $transactionDate
    ) {
        $newRunningBalance = (float)$memberAccount['running_balance'] + $amount;
        $newAvailableBalance = (float)$memberAccount['available_balance'] + $amount;

        // Create journal entries
        $this->createJournalEntries(
            $memberAccount['member_id'],
            $amount,
            $currency,
            $receiptNumber,
            $transactionDate,
            $memberAccount['financial_year_id'],
            $memberAccount['account_type_id'],
            false
        );

        // Create transaction record
        $this->createTransactionRecord(
            $memberAccount['member_id'],
            $memberAccount['id'],
            $memberAccount['account_type_id'],
            $memberAccount['sub_account_code'],
            $amount,
            $memberAccount['running_balance'],
            $newRunningBalance,
            $newAvailableBalance,
            $receiptNumber,
            $transactionDate,
            $memberAccount['financial_year_id']
        );

        // Update account balances
        $this->updateAccountBalances(
            $memberAccount['id'],
            $newRunningBalance,
            $newAvailableBalance
        );
    }

    private function processLoanRepaymentAccounting(
        $transactionId,
        $memberAccount,
        $amount,
        $currency,
        $receiptNumber,
        $transactionDate,
        $loanId
    ) {
        $repaymentResult = $this->processLoanRepayment(
            $loanId,
            $amount,
            $receiptNumber,
            $transactionDate
        );

        // Create transaction records for each payment component
        $this->createLoanRepaymentTransactions(
            $memberAccount['member_id'],
            $loanId,
            $memberAccount['account_type_id'],
            $repaymentResult['principal_paid'],
            $repaymentResult['interest_paid'],
            $repaymentResult['penalty_paid'],
            $receiptNumber,
            $transactionDate,
            $memberAccount['financial_year_id']
        );
    }

    // ========== LOAN REPAYMENT METHODS ==========
    private function processLoanRepayment($loanId, $amount, $receiptNumber, $transactionDate) {
        $this->log("Starting loan repayment processing", [
            'loan_id' => $loanId,
            'amount' => $amount,
            'receipt' => $receiptNumber
        ]);

        try {
            // 1. Get loan details with lock
            $stmt = $this->pdo->prepare("
                SELECT l.*, m.first_name, m.last_name, m.member_number,
                       at.type_code as account_type_code
                FROM loans l 
                JOIN members m ON l.member_id = m.id
                JOIN account_types at ON l.account_type_id = at.id
                WHERE l.id = ? FOR UPDATE
            ");
            $stmt->execute([$loanId]);
            $loan = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$loan) {
                throw new Exception("Loan not found");
            }

            // 2. Get ledger mappings (only principle and interest ledgers needed)
            $stmt = $this->pdo->prepare("
                SELECT principle_ledger, interest_payable_ledger
                FROM account_types_ledger_mapping 
                WHERE account_type_id = ?
            ");
            $stmt->execute([$loan['account_type_id']]);
            $ledgerMapping = $stmt->fetch();
            if (!$ledgerMapping) {
                throw new Exception("Ledger accounts mapping not found");
            }

            // 3. Get financial year
            $stmt = $this->pdo->prepare("SELECT id FROM financial_years WHERE is_active = 1 LIMIT 1");
            $stmt->execute();
            $financialYear = $stmt->fetch();
            if (!$financialYear) {
                throw new Exception("No active financial year found");
            }
            $financialYearId = $financialYear['id'];

            // 4. Get member account balances for this loan
            $stmt = $this->pdo->prepare("
                SELECT sub_account_code, running_balance, available_balance
                FROM member_accounts
                WHERE member_id = ? AND loan_id = ?
                FOR UPDATE
            ");
            $stmt->execute([$loan['member_id'], $loanId]);
            $accountBalances = [];
            while ($row = $stmt->fetch()) {
                $accountBalances[$row['sub_account_code']] = [
                    'running' => $row['running_balance'],
                    'available' => $row['available_balance']
                ];
            }

            // 5. Initialize payment tracking
            $totalPenaltyPaid = 0;
            $totalInterestPaid = 0;
            $totalPrincipalPaid = 0;
            $remainingAmount = $amount;

            // 6. Get installments in chronological order for processing
            $stmt = $this->pdo->prepare("
                SELECT * FROM loan_schedule 
                WHERE loan_id = ? 
                AND (penalty_amount > penalty_paid OR 
                     interest_calculated > interest_paid OR 
                     principal_amount > principal_paid)
                ORDER BY due_date ASC
                FOR UPDATE
            ");
            $stmt->execute([$loanId]);
            $installments = $stmt->fetchAll();

            // 7. Process payments in strict order
            foreach ($installments as $installment) {
                if ($remainingAmount <= 0) break;

                // 7a. Process Penalty first
                $penaltyDue = $installment['penalty_amount'] - $installment['penalty_paid'];
                if ($penaltyDue > 0) {
                    $penaltyPayment = min($remainingAmount, $penaltyDue);
                    $totalPenaltyPaid += $penaltyPayment;
                    $remainingAmount -= $penaltyPayment;
                    
                    $this->updateInstallmentPayment(
                        $installment['id'],
                        $penaltyPayment,
                        'penalty'
                    );
                    
                    if ($remainingAmount <= 0) break;
                }

                // 7b. Process Interest next
                $interestDue = $installment['interest_calculated'] - $installment['interest_paid'];
                if ($interestDue > 0) {
                    $interestPayment = min($remainingAmount, $interestDue);
                    $totalInterestPaid += $interestPayment;
                    $remainingAmount -= $interestPayment;
                    
                    $this->updateInstallmentPayment(
                        $installment['id'],
                        $interestPayment,
                        'interest'
                    );
                    
                    if ($remainingAmount <= 0) break;
                }

                // 7c. Process Principal last
                $principalDue = $installment['principal_amount'] - $installment['principal_paid'];
                if ($principalDue > 0) {
                    $principalPayment = min($remainingAmount, $principalDue);
                    $totalPrincipalPaid += $principalPayment;
                    $remainingAmount -= $principalPayment;
                    
                    $this->updateInstallmentPayment(
                        $installment['id'],
                        $principalPayment,
                        'principal'
                    );
                }
            }

            // 8. Create accounting entries
            $assetAccountId = defined('MPESA_LEDGER_ID') ? MPESA_LEDGER_ID : 204;
            $totalAmount = $totalPrincipalPaid + $totalInterestPaid + $totalPenaltyPaid;

            // Get ledger codes
            $stmt = $this->pdo->prepare("
                SELECT id, ledger_code FROM ledgers 
                WHERE id IN (?, ?, ?)
            ");
            $stmt->execute([
                $assetAccountId,
                $ledgerMapping['principle_ledger'],
                $ledgerMapping['interest_payable_ledger']
            ]);
            $ledgerCodes = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

            // Prepare journal entries
            $journalEntries = [
                [
                    'ledger_id' => $assetAccountId,
                    'ledger_code' => $ledgerCodes[$assetAccountId],
                    'amount' => $totalAmount,
                    'entry_type' => 'dr',
                    'description' => 'Loan repayment receipt'
                ]
            ];

            // Combined principal + penalty to principle ledger
            $combinedPrincipal = $totalPrincipalPaid + $totalPenaltyPaid;
            if ($combinedPrincipal > 0) {
                $journalEntries[] = [
                    'ledger_id' => $ledgerMapping['principle_ledger'],
                    'ledger_code' => $ledgerCodes[$ledgerMapping['principle_ledger']],
                    'amount' => $combinedPrincipal,
                    'entry_type' => 'cr',
                    'description' => 'Principal and penalty repayment'
                ];
            }

            // Interest entry if applicable
            if ($totalInterestPaid > 0) {
                $journalEntries[] = [
                    'ledger_id' => $ledgerMapping['interest_payable_ledger'],
                    'ledger_code' => $ledgerCodes[$ledgerMapping['interest_payable_ledger']],
                    'amount' => $totalInterestPaid,
                    'entry_type' => 'cr',
                    'description' => 'Interest repayment'
                ];
            }

            // Insert journal entries
            $stmt = $this->pdo->prepare("
                INSERT INTO journal_entries (
                    member_id, loan_id, ledger_id, ledger_code, amount, entry_type,
                    description, reference_number, financial_year_id, 
                    transaction_date, payment_method, created_by, created_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'system', NOW())
            ");

            foreach ($journalEntries as $entry) {
                $stmt->execute([
                    $loan['member_id'],
                    $loan['id'],
                    $entry['ledger_id'],
                    $entry['ledger_code'],
                    $entry['amount'],
                    $entry['entry_type'],
                    $entry['description'],
                    $receiptNumber,
                    $financialYearId,
                    $transactionDate,
                    'mpesa'
                ]);
            }

            // Update ledger balances
            foreach ($journalEntries as $entry) {
                $this->updateLedgerBalance(
                    $entry['ledger_id'],
                    $entry['amount'],
                    $entry['entry_type']
                );
            }

            $result = [
                'principal_paid' => $totalPrincipalPaid,
                'interest_paid' => $totalInterestPaid,
                'penalty_paid' => $totalPenaltyPaid,
                'remaining_amount' => $remainingAmount
            ];
            
            $this->log("Loan repayment processing completed", $result);
            return $result;

        } catch (Exception $e) {
            $this->log("Loan repayment processing failed", [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            throw $e;
        }
    }

    private function updateInstallmentPayment($installmentId, $amount, $type) {
        $columnMap = [
            'penalty' => 'penalty_paid',
            'interest' => 'interest_paid',
            'principal' => 'principal_paid'
        ];
        
        $column = $columnMap[$type];
        
        $stmt = $this->pdo->prepare("
            UPDATE loan_schedule 
            SET {$column} = {$column} + ?,
                total_paid = total_paid + ?,
                status = CASE 
                    WHEN (principal_amount <= principal_paid + ?) AND
                         (interest_calculated <= interest_paid + ?) AND
                         (penalty_amount <= penalty_paid + ?)
                    THEN 'Paid'
                    WHEN (principal_paid + ? > 0) OR
                         (interest_paid + ? > 0) OR
                         (penalty_paid + ? > 0)
                    THEN 'Partially Paid'
                    ELSE status
                END
            WHERE id = ?
        ");
        
        // Bind parameters for all placeholders
        $params = array_fill(0, 9, 0);
        $params[0] = $amount; // First column update
        $params[1] = $amount; // total_paid increment
        
        // Set the appropriate parameter based on payment type
        switch ($type) {
            case 'penalty':
                $params[2] = 0; // principal
                $params[3] = 0; // interest
                $params[4] = $amount; // penalty
                $params[5] = 0; // principal
                $params[6] = 0; // interest
                $params[7] = $amount; // penalty
                break;
            case 'interest':
                $params[2] = 0; // principal
                $params[3] = $amount; // interest
                $params[4] = 0; // penalty
                $params[5] = 0; // principal
                $params[6] = $amount; // interest
                $params[7] = 0; // penalty
                break;
            case 'principal':
                $params[2] = $amount; // principal
                $params[3] = 0; // interest
                $params[4] = 0; // penalty
                $params[5] = $amount; // principal
                $params[6] = 0; // interest
                $params[7] = 0; // penalty
                break;
        }
        
        $params[8] = $installmentId; // WHERE clause
        
        $stmt->execute($params);
    }

    private function createLoanRepaymentTransactions(
        $memberId,
        $loanId,
        $accountTypeId,
        $principalPaid,
        $interestPaid,
        $penaltyPaid,
        $receiptNumber,
        $transactionDate,
        $financialYearId
    ) {
        $this->log("Creating loan repayment transaction records", [
            'member_id' => $memberId,
            'loan_id' => $loanId,
            'principal' => $principalPaid,
            'interest' => $interestPaid,
            'penalty' => $penaltyPaid
        ]);

        try {
            // Get account details with proper locking to prevent race conditions
            $stmt = $this->pdo->prepare("
                SELECT ma.id, ma.sub_account_code, ma.running_balance, ma.available_balance
                FROM member_accounts ma
                WHERE ma.member_id = ? AND ma.loan_id = ?
                ORDER BY ma.sub_account_code
                FOR UPDATE
            ");
            $stmt->execute([$memberId, $loanId]);
            $accounts = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            $accountMap = [];
            foreach ($accounts as $account) {
                $accountMap[$account['sub_account_code']] = $account;
            }

            // Create transaction for principal + penalty (sub-account 1)
            if (($principalPaid + $penaltyPaid) > 0 && isset($accountMap[1])) {
                $account = $accountMap[1];
                
                // Use the CURRENT balance as opening balance (BEFORE any updates)
                $openingBalance = (float)$account['running_balance'];
                
                // Calculate new balances by subtracting the TOTAL principal + penalty paid
                $newRunningBalance = $openingBalance - ($principalPaid + $penaltyPaid);
                
                $this->createTransactionRecord(
                    $memberId,
                    $account['id'],
                    $accountTypeId,
                    1, // sub-account code for principal
                    $principalPaid + $penaltyPaid,
                    $openingBalance, // Use the current balance BEFORE transaction as opening balance
                    $newRunningBalance,
                    $newRunningBalance, // For loan accounts, available balance = running balance
                    $receiptNumber,
                    $transactionDate,
                    $financialYearId,
                    $loanId,
                    'Loan Principal and Penalty Repayment'
                );
                
                // Update the account balance in database
                $this->updateAccountBalances(
                    $account['id'],
                    $newRunningBalance,
                    $newRunningBalance
                );
            }

            // Create transaction for interest (sub-account 2)
            if ($interestPaid > 0 && isset($accountMap[2])) {
                $account = $accountMap[2];
                
                // Use the CURRENT balance as opening balance (BEFORE any updates)
                $openingBalance = (float)$account['running_balance'];
                
                // Calculate new balance
                $newRunningBalance = $openingBalance - $interestPaid;
                
                $this->createTransactionRecord(
                    $memberId,
                    $account['id'],
                    $accountTypeId,
                    2, // sub-account code for interest
                    $interestPaid,
                    $openingBalance, // Use the current balance BEFORE transaction as opening balance
                    $newRunningBalance,
                    $newRunningBalance, // For loan accounts, available balance = running balance
                    $receiptNumber,
                    $transactionDate,
                    $financialYearId,
                    $loanId,
                    'Loan Interest Repayment'
                );
                
                // Update the account balance in database
                $this->updateAccountBalances(
                    $account['id'],
                    $newRunningBalance,
                    $newRunningBalance
                );
            }

            $this->log("Loan repayment transaction records created successfully");
            
        } catch (Exception $e) {
            $this->log("Error creating loan repayment transactions", [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            throw $e;
        }
    }

    // ========== TRANSACTION RECORD METHODS ==========
    private function createTransactionRecord(
        $memberId,
        $accountId,
        $accountTypeId,
        $subAccountCode,
        $amount,
        $openingBalance,
        $runningBalance,
        $availableBalance,
        $receiptNumber,
        $transactionDate,
        $financialYearId,
        $loanId = null,
        $customDescription = null
    ) {
        $this->log("Creating transaction record", [
            'member_id' => $memberId,
            'amount' => $amount,
            'receipt' => $receiptNumber
        ]);
        
        // Get account name for description
        $accountName = $this->getAccountName($accountTypeId);
        
        // Generate a unique transaction number with retry logic
        $transactionNumber = $this->generateUniqueTransactionNumber($memberId);
        
        // Use custom description if provided, otherwise generate default
        $description = $customDescription ?: 
            ($loanId ? "Loan Repayment to {$accountName}" : "Deposit to {$accountName}");
        
        $stmt = $this->pdo->prepare("
            INSERT INTO transactions (
                transaction_number, member_id, account_id, account_type_id, 
                sub_account_code, transaction_type, amount, opening_balance,
                running_balance, available_balance, payment_method, description,
                reference_number, transaction_date, transaction_status,
                created_by, created_at, updated_at, financial_year_id, loan_id
            ) VALUES (?, ?, ?, ?, ?, 'cr', ?, ?, ?, ?, 'mpesa', ?, ?, ?, 'Completed', 
                     'system', NOW(), NOW(), ?, ?)
        ");
        
        $stmt->execute([
            $transactionNumber,
            $memberId,
            $accountId,
            $accountTypeId,
            $subAccountCode,
            $amount,
            $openingBalance,
            $runningBalance,
            $availableBalance,
            $description,
            $receiptNumber,
            $transactionDate,
            $financialYearId,
            $loanId
        ]);
        
        $this->log("Transaction record created", ['transaction_number' => $transactionNumber]);
        return $transactionNumber;
    }

    private function generateUniqueTransactionNumber($memberId, $maxAttempts = 5) {
        $attempt = 0;
        
        while ($attempt < $maxAttempts) {
            $attempt++;
            
            // Generate a unique ID with prefix and random component
            $uniqueId = uniqid('', true); // More entropy for better uniqueness
            $randomComponent = bin2hex(random_bytes(4)); // Additional randomness
            
            $memberIdString = str_pad((string)$memberId, 6, '0', STR_PAD_LEFT);
            $datePrefix = date('YmdHis');
            
            $transactionNumber = 'STK' . $datePrefix . '-' . $randomComponent . '-' . $memberIdString;
            
            // Check if this transaction number already exists
            $stmt = $this->pdo->prepare("
                SELECT COUNT(*) as count 
                FROM transactions 
                WHERE transaction_number = ?
            ");
            $stmt->execute([$transactionNumber]);
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if ($result['count'] == 0) {
                return $transactionNumber;
            }
            
            // Wait a short time before retrying
            usleep(1000); // 1 millisecond
        }
        
        // If we've exhausted all attempts, throw an exception
        throw new Exception("Failed to generate unique transaction number after $maxAttempts attempts");
    }

    private function getAccountName($accountTypeId) {
        $stmt = $this->pdo->prepare("
            SELECT account_name FROM account_types WHERE id = ?
        ");
        $stmt->execute([$accountTypeId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result ? $result['account_name'] : 'Unknown Account';
    }

    private function updateTransactionStatus($transactionId, $status, $accountingCompleted = false) {
        $this->log("Updating transaction status", [
            'transaction_id' => $transactionId,
            'status' => $status,
            'accounting_completed' => $accountingCompleted
        ]);
        
        $stmt = $this->pdo->prepare("
            UPDATE mpesa_transactions 
            SET status = ?, accounting_completed = ?, processed_at = NOW()
            WHERE id = ?
        ");
        $stmt->execute([$status, (int)$accountingCompleted, $transactionId]);
        
        $this->log("Transaction status updated");
    }

    // ========== NOTIFICATION METHODS ==========
    private function sendNotificationsInBackground($notificationData) {
        try {
            $this->sendNotifications(
                $notificationData['member_id'],
                $notificationData['amount'],
                $notificationData['currency'],
                $notificationData['receipt_number'],
                $notificationData['account_name'],
                $notificationData['account_number'],
                $notificationData['account_type_id'],
                $notificationData['sub_account_code'],
                $notificationData['is_loan_account']
            );
        } catch (Exception $e) {
            $this->log("STK notification failed but transaction was processed", [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
        }
    }

    private function sendNotifications(
        $memberId,
        $amount,
        $currency,
        $receiptNumber,
        $accountName,
        $accountNumber,
        $accountTypeId,
        $subAccountCode,
        $isLoanAccount = false
    ) {
        $this->log("Preparing to send STK notifications", ['member_id' => $memberId]);
        
        try {
            $companySettings = $this->getCompanySettings();
            $memberData = $this->getMemberData($memberId);

            if ($memberData) {
                $balances = $this->getCurrentBalances($memberId, $accountTypeId, $subAccountCode);
                $runningBalance = $balances['running_balance'];
                $availableBalance = $balances['available_balance'];

                $formattedAmount = number_format($amount, 2);
                $formattedRunningBalance = number_format($runningBalance, 2);
                $formattedAvailableBalance = number_format($availableBalance, 2);
                
                $phoneNumber = !empty($memberData['phone_number']) ? 
                    formatPhoneNumber($memberData['phone_number']) : null;

                $preference = strtolower($memberData['communication_preference'] ?? 'both');
                $sendSms = in_array($preference, ['sms', 'both']) && !empty($phoneNumber);
                $sendEmail = in_array($preference, ['email', 'both']) && !empty($memberData['email_address']);

                $this->log("STK notification preferences", [
                    'preference' => $preference,
                    'send_sms' => $sendSms,
                    'send_email' => $sendEmail
                ]);

                if ($sendSms && $phoneNumber) {
                    $this->log("Preparing STK SMS notification");
                    $smsMessage = $this->createSmsMessage(
                        $memberData['first_name'],
                        $formattedAmount,
                        $currency,
                        $companySettings['company_name'] ?? 'Our Organization',
                        $receiptNumber,
                        $accountName,
                        $formattedRunningBalance,
                        $formattedAvailableBalance,
                        $isLoanAccount
                    );

                    sendSms($this->pdo, $phoneNumber, $smsMessage);
                    $this->log("STK SMS notification sent", ['member_id' => $memberId]);
                }

                if ($sendEmail && !empty($memberData['email_address'])) {
                    $this->log("Preparing STK email notification");
                    $emailService = new EmailService($this->pdo, $this->logger);
                    $emailService->sendPaymentNotification(
                        $memberData['email_address'],
                        $amount,
                        $currency,
                        $receiptNumber,
                        $runningBalance,
                        $availableBalance,
                        [
                            'first_name' => $memberData['first_name'],
                            'last_name' => $memberData['last_name'],
                            'email' => $memberData['email_address'],
                            'phone' => $memberData['phone_number']
                        ],
                        $isLoanAccount
                    );
                    $this->log("STK email notification sent", ['member_id' => $memberId]);
                }
            } else {
                $this->log("Member data not found for STK notifications", ['member_id' => $memberId]);
            }
        } catch (Exception $e) {
            $this->log("STK notification failed", [
                'member_id' => $memberId,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
        }
    }

    private function createSmsMessage(
        $firstName,
        $amount,
        $currency,
        $companyName,
        $receiptNumber,
        $accountName,
        $runningBalance,
        $availableBalance,
        $isLoanAccount = false
    ) {
        $this->log("Creating SMS message", ['is_loan' => $isLoanAccount]);
        
        if ($isLoanAccount) {
            return "Dear $firstName, STK loan repayment of $currency $amount received by $companyName. " .
                   "Receipt: $receiptNumber. Loan Account: $accountName. " .
                   "New Loan Balance: $currency $runningBalance.";
        } else {
            return "Dear $firstName, STK payment of $currency $amount received by $companyName. " .
                   "Receipt: $receiptNumber. Account: $accountName. " .
                   "New Running Balance: $currency $runningBalance. " .
                   "New Available Balance: $currency $availableBalance";
        }
    }

    // ========== UTILITY METHODS ==========
    private function log($message, $context = []) {
        $logData = [
            'timestamp' => date('Y-m-d H:i:s.v'),
            'message' => "STK: " . $message,
            'memory_usage' => memory_get_usage(true),
            'memory_peak' => memory_get_peak_usage(true),
            'context' => $context
        ];
        
        if ($this->logger) {
            $this->logger->log(json_encode($logData));
        } else {
            error_log(json_encode($logData));
        }
    }

    private function getCurrentBalances($memberId, $accountTypeId, $subAccountCode = null) {
        $this->log("Getting current balances", [
            'member_id' => $memberId,
            'account_type' => $accountTypeId,
            'sub_account' => $subAccountCode
        ]);
        
        $query = "
            SELECT running_balance, available_balance 
            FROM member_accounts 
            WHERE member_id = ? AND account_type_id = ?
        ";
        $params = [$memberId, $accountTypeId];
        
        if ($subAccountCode !== null) {
            $query .= " AND sub_account_code = ?";
            $params[] = $subAccountCode;
        }
        
        $query .= " LIMIT 1";
        
        $stmt = $this->pdo->prepare($query);
        $stmt->execute($params);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $balances = $result ? [
            'running_balance' => (float)$result['running_balance'],
            'available_balance' => (float)$result['available_balance']
        ] : ['running_balance' => 0.0, 'available_balance' => 0.0];
        
        $this->log("Current balances retrieved", $balances);
        return $balances;
    }

    private function getCompanySettings() {
        $this->log("Getting company settings");
        
        $stmt = $this->pdo->query("
            SELECT company_name, email, phone_number
            FROM company_settings LIMIT 1
        ");
        $settings = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
        
        $this->log("Company settings retrieved", $settings);
        return $settings;
    }

    private function getMemberData($memberId) {
        $this->log("Getting member data", ['member_id' => $memberId]);
        
        $stmt = $this->pdo->prepare("
            SELECT id, first_name, last_name, email_address, 
                   phone_number, communication_preference
            FROM members WHERE id = ? LIMIT 1
        ");
        $stmt->execute([$memberId]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
        
        $this->log("Member data retrieved", ['data_found' => !!$data]);
        return $data;
    }

    private function getPrincipleLedgerId($accountTypeId) {
        $this->log("Getting principle ledger ID", ['account_type' => $accountTypeId]);
        
        $stmt = $this->pdo->prepare("
            SELECT principle_ledger
            FROM account_types_ledger_mapping 
            WHERE account_type_id = ?
        ");
        $stmt->execute([$accountTypeId]);
        $mapping = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $ledgerId = $mapping ? (int)$mapping['principle_ledger'] : null;
        $this->log("Principle ledger ID retrieved", ['id' => $ledgerId]);
        
        return $ledgerId;
    }

    private function getLedgerDetails($ledgerIds) {
        $this->log("Getting ledger details", ['ledger_ids' => $ledgerIds]);
        
        $in = str_repeat('?,', count($ledgerIds) - 1) . '?';
        $stmt = $this->pdo->prepare("
            SELECT id, ledger_code, balance_type 
            FROM ledgers WHERE id IN ($in)
        ");
        $stmt->execute($ledgerIds);
        
        $details = [];
        while ($ledger = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $details[(int)$ledger['id']] = [
                'code' => $ledger['ledger_code'],
                'balance_type' => $ledger['balance_type']
            ];
        }
        
        $this->log("Ledger details retrieved", ['count' => count($details)]);
        return $details;
    }

    private function createJournalEntries(
        $memberId,
        $amount,
        $currency,
        $receiptNumber,
        $transactionDate,
        $financialYearId,
        $accountTypeId,
        $isLoanAccount = false
    ) {
        $this->log("Creating journal entries", [
            'member_id' => $memberId,
            'amount' => $amount,
            'is_loan_account' => $isLoanAccount
        ]);
        
        try {
            $mpesaLedgerId = defined('MPESA_LEDGER_ID') ? MPESA_LEDGER_ID : 204;
            $principleLedgerId = $this->getPrincipleLedgerId($accountTypeId);
            
            $ledgerDetails = $this->getLedgerDetails([$mpesaLedgerId, $principleLedgerId]);
            
            // Debug ledger information
            $this->log("Ledger details for journal entries", [
                'mpesa_ledger_id' => $mpesaLedgerId,
                'mpesa_balance_type' => $ledgerDetails[$mpesaLedgerId]['balance_type'],
                'principle_ledger_id' => $principleLedgerId,
                'principle_balance_type' => $ledgerDetails[$principleLedgerId]['balance_type']
            ]);
            
            // Check balances before creating entries
            $beforeMpesa = $this->getLedgerBalance($mpesaLedgerId);
            $beforePrinciple = $this->getLedgerBalance($principleLedgerId);
            
            $this->log("Balances before journal entries", [
                'mpesa_balance' => $beforeMpesa,
                'principle_balance' => $beforePrinciple
            ]);
            
            $transactionType = $isLoanAccount ? 'Loan Repayment' : 'Deposit';
            $accountName = $this->getAccountName($accountTypeId);
            
            // M-Pesa ledger: Always DR (receiving cash)
            $debitEntryId = $this->createJournalEntry(
                $memberId,
                $mpesaLedgerId,
                $ledgerDetails[$mpesaLedgerId]['code'],
                $amount,
                $currency,
                'DR',
                $receiptNumber,
                $transactionDate,
                $financialYearId,
                $accountTypeId,
                "M-Pesa {$transactionType} Received for {$accountName}: {$receiptNumber}"
            );
            
            // Principle ledger: Always CR 
            $creditEntryId = $this->createJournalEntry(
                $memberId,
                $principleLedgerId,
                $ledgerDetails[$principleLedgerId]['code'],
                $amount,
                $currency,
                'CR',
                $receiptNumber,
                $transactionDate,
                $financialYearId,
                $accountTypeId,
                "Member {$transactionType} to {$accountName}: {$receiptNumber}"
            );
            
            // Update ledger balances with correct entry types
            $this->updateLedgerBalance($mpesaLedgerId, $amount, 'DR');
            $this->updateLedgerBalance($principleLedgerId, $amount, 'CR');
            
            // Verify balances after update
            $afterMpesa = $this->getLedgerBalance($mpesaLedgerId);
            $afterPrinciple = $this->getLedgerBalance($principleLedgerId);
            
            $this->log("Balance changes after journal entries", [
                'mpesa_ledger' => [
                    'before' => $beforeMpesa,
                    'after' => $afterMpesa,
                    'expected_change' => $amount,
                    'actual_change' => $afterMpesa - $beforeMpesa,
                    'match' => ($afterMpesa - $beforeMpesa) == $amount
                ],
                'principle_ledger' => [
                    'before' => $beforePrinciple,
                    'after' => $afterPrinciple,
                    'expected_change' => -$amount,
                    'actual_change' => $afterPrinciple - $beforePrinciple,
                    'match' => ($afterPrinciple - $beforePrinciple) == -$amount
                ]
            ]);
            
            return [
                'debit_entry' => $debitEntryId,
                'credit_entry' => $creditEntryId,
                'mpesa_ledger_balance' => $afterMpesa,
                'principle_ledger_balance' => $afterPrinciple
            ];
            
        } catch (Exception $e) {
            $this->log("ERROR in createJournalEntries", [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            throw $e;
        }
    }

    private function createJournalEntry(
        $memberId,
        $ledgerId,
        $ledgerCode,
        $amount,
        $currency,
        $entryType,
        $receiptNumber,
        $transactionDate,
        $financialYearId,
        $accountTypeId,
        $description
    ) {
        $this->log("Creating journal entry", [
            'ledger_id' => $ledgerId,
            'amount' => $amount,
            'type' => $entryType
        ]);
        
        $stmt = $this->pdo->prepare("
            INSERT INTO journal_entries (
                member_id, ledger_id, ledger_code, amount, currency,
                entry_type, payment_method, description, reference_number,
                transaction_date, created_by, created_at, financial_year_id,
                account_type_id
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?)
        ");
        
        $stmt->execute([
            $memberId,
            $ledgerId,
            $ledgerCode,
            $amount,
            $currency,
            $entryType,
            'mpesa',
            $description,
            $receiptNumber,
            $transactionDate,
            'system',
            $financialYearId,
            $accountTypeId
        ]);
        
        $entryId = (int)$this->pdo->lastInsertId();
        $this->log("Journal entry created", ['entry_id' => $entryId]);
        
        return $entryId;
    }

    private function getLedgerBalance($ledgerId) {
        $stmt = $this->pdo->prepare("
            SELECT current_balance FROM ledgers WHERE id = ?
        ");
        $stmt->execute([$ledgerId]);
        return (float)$stmt->fetchColumn();
    }

    private function updateLedgerBalance($ledgerId, $amount, $entryType) {
        $this->log("Starting ledger balance update", [
            'ledger_id' => $ledgerId,
            'amount' => $amount,
            'entry_type' => $entryType
        ]);
        
        try {
            // First get the ledger's balance type and current balance
            $stmt = $this->pdo->prepare("
                SELECT id, ledger_code, balance_type, current_balance 
                FROM ledgers 
                WHERE id = ?
            ");
            $stmt->execute([$ledgerId]);
            $ledger = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$ledger) {
                throw new RuntimeException("Ledger not found: $ledgerId");
            }
            
            $balanceType = strtoupper(trim($ledger['balance_type']));
            $currentBalance = (float)$ledger['current_balance'];
            $entryType = strtoupper(trim($entryType));
            
            $this->log("Ledger details before update", [
                'ledger_id' => $ledgerId,
                'ledger_code' => $ledger['ledger_code'],
                'balance_type' => $balanceType,
                'current_balance' => $currentBalance,
                'entry_type' => $entryType,
                'amount' => $amount
            ]);
            
            // Determine the new balance based on accounting rules
            if ($balanceType === 'DEBIT' || $balanceType === 'DR') {
                $newBalance = ($entryType === 'DR') 
                    ? $currentBalance + $amount 
                    : $currentBalance - $amount;
                    
            } elseif ($balanceType === 'CREDIT' || $balanceType === 'CR') {
                $newBalance = ($entryType === 'CR') 
                    ? $currentBalance + $amount 
                    : $currentBalance - $amount;
                    
            } else {
                throw new RuntimeException("Unknown balance type: $balanceType for ledger: $ledgerId");
            }
            
            // Update the ledger balance
            $updateStmt = $this->pdo->prepare("
                UPDATE ledgers 
                SET current_balance = ?,
                    updated_at = NOW()
                WHERE id = ?
            ");
            
            $updateResult = $updateStmt->execute([$newBalance, $ledgerId]);
            $rowsAffected = $updateStmt->rowCount();
            
            if (!$updateResult || $rowsAffected === 0) {
                throw new RuntimeException("Failed to update ledger balance. No rows affected.");
            }
            
            // Verify the update was successful
            $verifyStmt = $this->pdo->prepare("
                SELECT current_balance FROM ledgers WHERE id = ?
            ");
            $verifyStmt->execute([$ledgerId]);
            $verifiedBalance = (float)$verifyStmt->fetchColumn();
            
            $this->log("Ledger balance update completed", [
                'ledger_id' => $ledgerId,
                'old_balance' => $currentBalance,
                'new_balance' => $newBalance,
                'verified_balance' => $verifiedBalance,
                'rows_affected' => $rowsAffected,
                'update_successful' => ($newBalance == $verifiedBalance)
            ]);
            
            return $newBalance;
            
        } catch (Exception $e) {
            $this->log("ERROR in updateLedgerBalance", [
                'ledger_id' => $ledgerId,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            throw $e;
        }
    }

    private function updateAccountBalances($accountId, $runningBalance, $availableBalance) {
        $this->log("Updating account balances", [
            'account_id' => $accountId,
            'running_balance' => $runningBalance,
            'available_balance' => $availableBalance
        ]);
        
        $stmt = $this->pdo->prepare("
            UPDATE member_accounts 
            SET running_balance = ?, 
                available_balance = ?,
                last_transaction_date = NOW() 
            WHERE id = ?
        ");
        $stmt->execute([$runningBalance, $availableBalance, $accountId]);
        
        $this->log("Account balances updated");
    }

    private function parseAccountReference($reference) {
        $this->log("Parsing account reference", ['reference' => $reference]);
        $accountParts = explode('-', $reference);
        if (count($accountParts) !== 3) {
            throw new Exception("Invalid account reference format. Expected PERSONALNO-ACCTYPE-SUBACCOUNT");
        }
        $this->log("Account reference parsed successfully");
        return $accountParts;
    }

    private function getMemberAccountWithLock($personalNumber, $accountTypeCode, $subAccountCode) {
        $this->log("Retrieving member account with lock", [
            'personal_number' => $personalNumber,
            'account_type' => $accountTypeCode,
            'sub_account' => $subAccountCode
        ]);
        
        $stmt = $this->pdo->prepare("
            SELECT 
                ma.id, ma.member_id, ma.account_type_id, ma.sub_account_code,
                ma.running_balance, ma.available_balance, ma.financial_year_id,
                m.first_name, m.last_name, m.email_address, m.phone_number,
                m.communication_preference,
                at.account_name, at.type_code, at.account_type,
                ma.account_number
            FROM member_accounts ma
            JOIN members m ON ma.member_id = m.id
            JOIN account_types at ON ma.account_type_id = at.id
            WHERE m.personal_number = ? 
            AND at.type_code = ?
            AND ma.sub_account_code = ?
            LIMIT 1 FOR UPDATE
        ");
        $stmt->execute([$personalNumber, $accountTypeCode, $subAccountCode]);
        $account = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $this->log("Member account retrieved", ['account_found' => !!$account]);
        return $account;
    }

    private function parseAndValidateAmount($amount): float {
        $this->log("Validating amount", ['input' => $amount]);
        $parsedAmount = (float)$amount;
        if ($parsedAmount <= 0) {
            throw new RuntimeException("Invalid amount: must be positive");
        }
        $this->log("Amount validation passed", ['parsed' => $parsedAmount]);
        return $parsedAmount;
    }

    private function validateCurrency(string $currency): string {
        $this->log("Validating currency", ['input' => $currency]);
        $validCurrencies = ['KES', 'USD', 'EUR'];
        $currency = strtoupper(trim($currency));
        
        if (!in_array($currency, $validCurrencies)) {
            throw new RuntimeException("Invalid currency: $currency");
        }
        
        $this->log("Currency validation passed", ['currency' => $currency]);
        return $currency;
    }

    private function validateReceiptNumber(string $receiptNumber): string {
        $this->log("Validating receipt number", ['input' => $receiptNumber]);
        $receiptNumber = trim($receiptNumber);
        if (empty($receiptNumber)) {
            throw new RuntimeException("Receipt number cannot be empty");
        }
        
        if (!preg_match('/^[A-Z0-9]{6,20}$/', $receiptNumber)) {
            throw new RuntimeException("Invalid receipt number format");
        }
        
        $this->log("Receipt number validation passed");
        return $receiptNumber;
    }

    private function isLoanAccount($accountTypeId) {
        $this->log("Checking if account is loan account", ['account_type_id' => $accountTypeId]);
        $stmt = $this->pdo->prepare("
            SELECT account_type FROM account_types WHERE id = ?
        ");
        $stmt->execute([$accountTypeId]);
        $accountType = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $isLoan = ($accountType && $accountType['account_type'] === 'loan');
        $this->log("Loan account check result", ['is_loan' => $isLoan]);
        
        return $isLoan;
    }

    private function getLoanIdForAccount($memberId, $accountTypeId) {
        $this->log("Getting loan ID for account", [
            'member_id' => $memberId,
            'account_type_id' => $accountTypeId
        ]);
        
        $stmt = $this->pdo->prepare("
            SELECT id FROM loans 
            WHERE member_id = ? AND account_type_id = ?
            AND status IN ('Active', 'Disbursed')
            LIMIT 1
        ");
        $stmt->execute([$memberId, $accountTypeId]);
        $loan = $stmt->fetch(PDO::FETCH_ASSOC);
        
        $loanId = $loan ? $loan['id'] : null;
        $this->log("Loan ID retrieved", ['loan_id' => $loanId]);
        
        return $loanId;
    }
}
?>