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

$transaction_id = 1; // The pending transaction we found
$admin_notes = "Test verification";
$admin_user_id = 1; // Assuming admin user ID is 1

echo "Simulating verification of transaction ID: " . $transaction_id . "\n";

try {
    // Begin transaction
    $pdo->beginTransaction();
    
    // Get transaction details
    $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.id = ? AND bt.status = 'pending'
    ");
    $stmt->execute([$transaction_id]);
    $transaction = $stmt->fetch();
    
    if (!$transaction) {
        throw new Exception("Transaction not found or already processed");
    }
    
    echo "Transaction details:\n";
    echo "- Student: " . $transaction['first_name'] . " " . $transaction['last_name'] . " (" . $transaction['student_code'] . ")\n";
    echo "- Amount: " . $transaction['amount'] . "\n";
    echo "- Transaction ID: " . $transaction['transaction_id'] . "\n";
    echo "- Bank: " . $transaction['bank_name'] . " - " . $transaction['branch_name'] . "\n";
    
    // Check if this transaction ID has already been verified for any student
    $stmt = $pdo->prepare("
        SELECT bt.id, s.first_name, s.last_name, s.student_id as student_code
        FROM bank_transactions bt
        JOIN students s ON bt.student_id = s.id
        WHERE bt.transaction_id = ? AND bt.status = 'verified'
    ");
    $stmt->execute([$transaction['transaction_id']]);
    $existing_verified = $stmt->fetch();
    
    if ($existing_verified) {
        throw new Exception("This transaction ID has already been verified for student " . 
            $existing_verified['student_code'] . " (" . 
            $existing_verified['first_name'] . " " . 
            $existing_verified['last_name'] . "). " .
            "The same transaction cannot be accepted multiple times.");
    }
    
    // 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 "\nFound " . 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 "\nProcessing fee ID " . $fee['id'] . ":\n";
        echo "- Due amount: " . $due_amount . "\n";
        echo "- Payment amount: " . $payment_amount . "\n";
        echo "- Remaining amount: " . $remaining_amount . "\n";
        
        $new_paid_amount = $fee['paid_amount'] + $payment_amount;
        $new_status = ($new_paid_amount >= $fee['amount']) ? 'paid' : 'partial';
        
        echo "- New paid amount: " . $new_paid_amount . "\n";
        echo "- New status: " . $new_status . "\n";
        
        $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: ' . $admin_notes, 
            $admin_user_id
        ]);
        
        $remaining_amount -= $payment_amount;
        $updated_fees[] = [
            'fee_id' => $fee['id'],
            'paid_amount' => $payment_amount
        ];
    }
    
    // Update the transaction
    $stmt = $pdo->prepare("
        UPDATE bank_transactions 
        SET status = 'verified', verified_at = NOW(), verified_by = ?, notes = ?
        WHERE id = ?
    ");
    $stmt->execute([$admin_user_id, $admin_notes, $transaction_id]);
    
    $pdo->commit();
    
    echo "\nTransaction verified successfully!\n";
    echo "Remaining amount after distribution: " . $remaining_amount . "\n";
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage() . "\n";
}
?>