<?php
require_once 'config/database.php';

echo "Fixing missing payment for transaction CJM6I74RX...\n\n";

// Get the specific transaction
$stmt = $pdo->prepare("
    SELECT bt.*, s.first_name, s.last_name, s.student_id as student_code, ba.bank_name, ba.branch_name
    FROM bank_transactions bt
    JOIN students s ON bt.student_id = s.id
    JOIN bank_accounts ba ON bt.bank_account_id = ba.id
    WHERE bt.transaction_id = ? AND bt.status = 'verified'
");
$stmt->execute(['CJM6I74RX']);
$transaction = $stmt->fetch();

if ($transaction) {
    echo "Transaction found. Creating fee payment record...\n";
    
    try {
        // Begin transaction
        $pdo->beginTransaction();
        
        // Get due fees for this student
        $stmt = $pdo->prepare("
            SELECT id, amount, paid_amount 
            FROM fees 
            WHERE student_id = ? AND status IN ('pending', 'partial', 'overdue') 
            ORDER BY due_date ASC
        ");
        $stmt->execute([$transaction['student_id']]);
        $due_fees = $stmt->fetchAll();
        
        echo "Found " . count($due_fees) . " due fees for this student.\n";
        
        $remaining_amount = (float)$transaction['amount'];
        $updated_fees = [];
        
        foreach ($due_fees as $fee) {
            if ($remaining_amount <= 0) break;
            
            $due_amount = $fee['amount'] - $fee['paid_amount'];
            $payment_amount = min($remaining_amount, $due_amount);
            
            echo "Applying payment of $payment_amount to fee ID " . $fee['id'] . " (due: $due_amount)\n";
            
            $new_paid_amount = $fee['paid_amount'] + $payment_amount;
            $new_status = ($new_paid_amount >= $fee['amount']) ? 'paid' : 'partial';
            
            $stmt = $pdo->prepare("
                UPDATE fees 
                SET paid_amount = ?, status = ?, paid_date = IF(paid_date IS NULL, CURDATE(), paid_date)
                WHERE id = ?
            ");
            $stmt->execute([$new_paid_amount, $new_status, $fee['id']]);
            
            // Insert payment record for this specific fee
            $ins = $pdo->prepare("
                INSERT INTO fee_payments (fee_id, amount, payment_date, payment_method, transaction_id, notes, created_by) 
                VALUES (?, ?, NOW(), ?, ?, ?, ?)
            ");
            $ins->execute([
                $fee['id'], 
                $payment_amount, 
                'bank', 
                $transaction['transaction_id'], 
                'Bank payment to ' . $transaction['bank_name'] . ' - ' . $transaction['branch_name'] . '. Verified by admin. Notes: ' . $transaction['notes'], 
                $transaction['verified_by']
            ]);
            
            $remaining_amount -= $payment_amount;
            $updated_fees[] = [
                'fee_id' => $fee['id'],
                'paid_amount' => $payment_amount
            ];
        }
        
        $pdo->commit();
        
        echo "Successfully created fee payment records!\n";
        echo "Remaining unapplied amount: $remaining_amount\n";
        
    } catch (Exception $e) {
        $pdo->rollBack();
        echo "Error: " . $e->getMessage() . "\n";
    }
} else {
    echo "Verified transaction not found!\n";
}

echo "\nDone.\n";
?>