<?php
session_start();

// Set a test user ID to simulate a logged-in student
// Uncomment one of these lines to test with a specific user:
// $_SESSION['user_id'] = 1;  // Admin user - should fail
$_SESSION['user_id'] = 16992;  // Student with payments - should work
$_SESSION['user_role'] = 'student';

require_once 'config/database.php';

echo "Debugging my_fees.php issue...\n";

// Check session
echo "Session user_id: " . ($_SESSION['user_id'] ?? 'NOT SET') . "\n";
echo "Session user_role: " . ($_SESSION['user_role'] ?? 'NOT SET') . "\n";

// Get student from user
$stmt = $pdo->prepare("SELECT s.*, c.name as class_name, c.section FROM students s LEFT JOIN classes c ON s.class_id=c.id WHERE s.user_id=? LIMIT 1");
$stmt->execute([$_SESSION['user_id']]);
$student = $stmt->fetch();

if (!$student) { 
    echo "ERROR: Student profile not found for user_id: " . ($_SESSION['user_id'] ?? 'NOT SET') . "\n";
    
    // Let's check what users exist
    $stmt = $pdo->query("SELECT id, username, role FROM users WHERE role='student' LIMIT 5");
    $users = $stmt->fetchAll();
    echo "Sample student users:\n";
    foreach ($users as $user) {
        echo "- ID: " . $user['id'] . ", Username: " . $user['username'] . ", Role: " . $user['role'] . "\n";
    }
    
    exit;
} else {
    echo "Student found:\n";
    echo "- DB ID: " . $student['id'] . "\n";
    echo "- Student ID: " . $student['student_id'] . "\n";
    echo "- Name: " . $student['first_name'] . " " . $student['last_name'] . "\n";
    echo "- Class: " . ($student['class_name'] ?? 'N/A') . " - " . ($student['section'] ?? 'N/A') . "\n";
}

// Check if student has scholarship
$has_scholarship = ($student['scholarship'] === 'yes');
echo "Has scholarship: " . ($has_scholarship ? 'Yes' : 'No') . "\n";

// Load all fees and payments summary
$stmt = $pdo->prepare("SELECT f.*, (f.amount - f.paid_amount) as due_amount FROM fees f WHERE f.student_id=? ORDER BY f.due_date ASC, f.created_at ASC");
$stmt->execute([$student['id']]);
$fees = $stmt->fetchAll();

echo "Number of fees found: " . count($fees) . "\n";

$total_payable = 0; $total_paid = 0; $total_due = 0;
foreach ($fees as $f) {
  $total_payable += (float)$f['amount'];
  $total_paid += (float)$f['paid_amount'];
  $total_due += (float)($f['amount'] - $f['paid_amount']);
}

echo "Total payable: " . $total_payable . "\n";
echo "Total paid: " . $total_paid . "\n";
echo "Total due: " . $total_due . "\n";

// If student has scholarship, set total_due to 0
if ($has_scholarship) {
    $total_due = 0;
    echo "Student has scholarship, setting due to 0\n";
}

// Payment history (transactions) - exclude rejected bKash transactions
$payments = [];
try {
    echo "Querying payment history for student ID: " . $student['id'] . "\n";
    
    $stmt = $pdo->prepare(
        "SELECT 
            fp.id,
            fp.amount,
            fp.payment_method,
            fp.transaction_id,
            COALESCE(fp.payment_date, fp.created_at) AS pay_date,
            f.fee_type,
            f.month
         FROM fee_payments fp
         JOIN fees f ON f.id = fp.fee_id
         LEFT JOIN bkash_transactions bt ON fp.transaction_id = bt.transaction_id COLLATE utf8mb4_unicode_ci
         WHERE f.student_id = ?
         AND (bt.status != 'rejected' OR bt.status IS NULL OR fp.payment_method != 'bkash')
         ORDER BY pay_date DESC, fp.id DESC"
    );
    $stmt->execute([$student['id']]);
    $payments = $stmt->fetchAll();
    
    echo "Number of payments found: " . count($payments) . "\n";
    
    if (count($payments) > 0) {
        echo "Payments:\n";
        foreach ($payments as $p) {
            echo "- Date: " . $p['pay_date'] . ", Amount: " . $p['amount'] . ", Method: " . $p['payment_method'] . ", Fee: " . $p['fee_type'] . " " . $p['month'] . ", Txn: " . $p['transaction_id'] . "\n";
        }
    }
} catch (Exception $e) {
    echo "ERROR querying payments with bkash join: " . $e->getMessage() . "\n";
    
    // If there's a collation error, try without the bkash join
    try {
        echo "Trying fallback query without bkash join...\n";
        $stmt = $pdo->prepare(
            "SELECT 
                fp.id,
                fp.amount,
                fp.payment_method,
                fp.transaction_id,
                COALESCE(fp.payment_date, fp.created_at) AS pay_date,
                f.fee_type,
                f.month
             FROM fee_payments fp
             JOIN fees f ON f.id = fp.fee_id
             WHERE f.student_id = ?
             ORDER BY pay_date DESC, fp.id DESC"
        );
        $stmt->execute([$student['id']]);
        $payments = $stmt->fetchAll();
        
        echo "Fallback query successful. Number of payments found: " . count($payments) . "\n";
        
        if (count($payments) > 0) {
            echo "Payments:\n";
            foreach ($payments as $p) {
                echo "- Date: " . $p['pay_date'] . ", Amount: " . $p['amount'] . ", Method: " . $p['payment_method'] . ", Fee: " . $p['fee_type'] . " " . $p['month'] . ", Txn: " . $p['transaction_id'] . "\n";
            }
        }
    } catch (Exception $e2) {
        echo "ERROR with fallback query: " . $e2->getMessage() . "\n";
        $payments = [];
    }
}

echo "Script completed.\n";
?>