<?php

/**
 * Term Allocation Helper Functions
 * 
 * Centralized allocation logic for school fees payments across:
 * - verify_wallet_payment.php
 * - verify_wallet_paymentb.php
 * - pay_tuition.php
 * 
 * These functions implement the corrected term allocation logic that:
 * 1. Determines if student is NEW or OLD based on academic_session
 * 2. For NEW students: allocates "School Fees (New Student)" first, then regular fees
 * 3. For OLD students: checks for previous session debt first, then current session
 * 4. Allocates payments in strict chronological order (First Term → Second Term → Third Term)
 */

/**
 * Determine if a student is NEW or OLD based on academic_session
 * 
 * NEW Student: academic_session equals current session (admitted this session)
 * OLD Student: academic_session differs from current session (admitted in previous session)
 * 
 * @param mysqli $con Database connection
 * @param int $studentId Student ID
 * @param string $currentSession Current session (e.g., "2025/2026")
 * @return bool true if NEW student, false if OLD student
 */
function isNewStudent($con, $studentId, $currentSession)
{
    $stmt = $con->prepare("SELECT academic_session FROM students WHERE id = ?");
    $stmt->bind_param("i", $studentId);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    if (!$result) {
        return false;
    }
    
    return $result['academic_session'] === $currentSession;
}

/**
 * Get admission term for a NEW student
 * 
 * Queries students.admission_term — the authoritative source set at admission time.
 * This works for both online-registered and manually-added students.
 * 
 * @param mysqli $con Database connection
 * @param int $studentId Student ID
 * @param string $currentSession Current session (kept for signature compatibility)
 * @return string|null Admission term (e.g., "First Term", "Second Term") or null if not set
 */
function getAdmissionTerm($con, $studentId, $currentSession)
{
    $stmt = $con->prepare(
        "SELECT admission_term FROM students WHERE id = ? LIMIT 1"
    );
    $stmt->bind_param("i", $studentId);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    return ($result && !empty($result['admission_term'])) ? $result['admission_term'] : null;
}

/**
 * Check if "School Fees (New Student)" is fully paid for admission term
 * 
 * @param mysqli $con Database connection
 * @param int $studentId Student ID
 * @param string $session Session
 * @param string $admissionTerm Admission term
 * @param string $class Student class
 * @param string $stream Student stream
 * @return bool true if fully paid, false otherwise
 */
function isNewStudentFeeFullyPaid($con, $studentId, $session, $admissionTerm, $class, $stream)
{
    // Get amount paid
    $stmt = $con->prepare(
        "SELECT SUM(amount) as paid FROM payment 
         WHERE student_id = ? AND session = ? AND Term = ? AND pay_for = 'School Fees (New Student)'"
    );
    $stmt->bind_param("iss", $studentId, $session, $admissionTerm);
    $stmt->execute();
    $paidResult = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    $amountPaid = floatval($paidResult['paid'] ?? 0);
    
    // Get fee amount
    $feeAmount = getNewStudentFeeAmount($con, $class, $stream, $session, $admissionTerm);
    
    return $amountPaid >= $feeAmount;
}

/**
 * Get "School Fees (New Student)" amount from fee_records only
 * 
 * Used when checking the admission term for a NEW student to ensure
 * the correct fee name and amount is used (not the Old student fee).
 * Only queries fee_records — no fallback to fees table.
 * 
 * @param mysqli $con Database connection
 * @param string $class Student class
 * @param string $stream Student stream
 * @param string $session Session
 * @param string $term Term
 * @return float Fee amount, or 0 if not found
 */
function getNewStudentFeeAmountFromRecords($con, $class, $stream, $session, $term)
{
    $stmt = $con->prepare(
        "SELECT amount FROM fee_records 
         WHERE class = ? AND stream = ? AND session = ? AND term = ? 
         AND student_type = 'New' AND name = 'School Fees (New Student)' LIMIT 1"
    );
    $stmt->bind_param("ssss", $class, $stream, $session, $term);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    return ($result && $result['amount'] > 0) ? floatval($result['amount']) : 0.0;
}

/**
 * Get "School Fees (New Student)" amount for a term
 * 
 * @param mysqli $con Database connection
 * @param string $class Student class
 * @param string $stream Student stream
 * @param string $session Session
 * @param string $term Term
 * @return float Fee amount
 */
function getNewStudentFeeAmount($con, $class, $stream, $session, $term)
{
    // Try fee_records first
    $stmt = $con->prepare(
        "SELECT amount FROM fee_records 
         WHERE class = ? AND stream = ? AND session = ? AND term = ? 
         AND student_type = 'New' AND name = 'School Fees (New Student)' LIMIT 1"
    );
    $stmt->bind_param("ssss", $class, $stream, $session, $term);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    if ($result && $result['amount'] > 0) {
        return floatval($result['amount']);
    }
    
    // Fall back to fees table
    $stmt = $con->prepare(
        "SELECT amount FROM fees 
         WHERE class = ? AND stream = ? AND student_type = 'New' 
         AND name = 'School Fees (New Student)' LIMIT 1"
    );
    $stmt->bind_param("ss", $class, $stream);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    return floatval($result['amount'] ?? 0);
}

/**
 * Get previous session outstanding payments
 * 
 * Starts from the student's academic_session (admission session) up to — but not
 * including — the current session. This is more accurate than scanning payment
 * records because:
 * 1. Some students are added manually and never have a "School Fees (New Student)" record
 * 2. A student may owe fees for a session even if they have zero payment records for it
 * 
 * Returns the sessions to check plus the student's original admission info so that
 * getUnpaidTermsInSession() can use the correct fee name for the admission term.
 * 
 * @param mysqli $con Database connection
 * @param int $studentId Student ID
 * @param string $currentSession Current session (e.g., "2025/2026")
 * @return array {
 *   sessions: string[],            // sessions from admission session up to (not including) current session
 *   admissionSession: string|null, // session the student was originally admitted in
 *   admissionTerm: string|null     // term the student was originally admitted in
 * }
 */
function getPreviousSessionDebt($con, $studentId, $currentSession)
{
    // Get the student's original admission session and term — authoritative source
    $admissionSession = null;
    $admissionTerm    = null;
    $stmt = $con->prepare(
        "SELECT academic_session, admission_term FROM students WHERE id = ? LIMIT 1"
    );
    $stmt->bind_param("i", $studentId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if ($row) {
        $admissionSession = $row['academic_session'] ?? null;
        $admissionTerm    = $row['admission_term']    ?? null;
    }

    // If no admission session recorded, nothing to check
    if (empty($admissionSession)) {
        return [
            'sessions'         => [],
            'admissionSession' => null,
            'admissionTerm'    => null,
        ];
    }

    // If admission session is the current session, no previous sessions to check
    if ($admissionSession === $currentSession) {
        return [
            'sessions'         => [],
            'admissionSession' => $admissionSession,
            'admissionTerm'    => $admissionTerm,
        ];
    }

    // Build the list of all sessions from admission session up to (not including) current session
    // by querying school_sessions table in chronological order
    $previousSessions = [];
    $stmt = $con->prepare(
        "SELECT session_name FROM school_sessions 
         WHERE session_name >= ? AND session_name < ?
         ORDER BY session_name ASC"
    );
    $stmt->bind_param("ss", $admissionSession, $currentSession);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($r = $result->fetch_assoc()) {
        $previousSessions[] = $r['session_name'];
    }
    $stmt->close();

    return [
        'sessions'         => $previousSessions,
        'admissionSession' => $admissionSession,
        'admissionTerm'    => $admissionTerm,
    ];
}

/**
 * Get unpaid terms in a specific session
 * 
 * Returns array of terms with unpaid amounts in chronological order
 * 
 * @param mysqli $con Database connection
 * @param int $studentId Student ID
 * @param string $session Session
 * @param string $class Student class
 * @param string $stream Student stream
 * @param bool $isFinalYear Whether student is final year (SS 3 or JSS 3)
 * @param string|null $admissionTerm For NEW students: the term they were admitted in (uses New student fee for that term)
 * @return array Array of terms with unpaid amounts
 */
function getUnpaidTermsInSession($con, $studentId, $session, $class, $stream, $isFinalYear = false, $admissionTerm = null)
{
    $unpaidTerms = [];
    
    // Determine which terms to check
    $termsToCheck = $isFinalYear ? ['First Term', 'Second Term'] : ['First Term', 'Second Term', 'Third Term'];
    
    foreach ($termsToCheck as $term) {
        // Get amount paid — matches both 'School Fees' and 'School Fees (New Student)'
        $stmt = $con->prepare(
            "SELECT SUM(amount) as paid FROM payment 
             WHERE student_id = ? AND session = ? AND Term = ? AND pay_for LIKE '%School Fees%'"
        );
        $stmt->bind_param("iss", $studentId, $session, $term);
        $stmt->execute();
        $paidResult = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        $amountPaid = floatval($paidResult['paid'] ?? 0);
        
        // Get fee amount — for the admission term of a NEW student, use the New student fee name
        if ($admissionTerm !== null && $term === $admissionTerm) {
            $feeAmount = getNewStudentFeeAmountFromRecords($con, $class, $stream, $session, $term);
            // Fall back to regular fee if no new student fee record found
            if ($feeAmount <= 0) {
                $feeAmount = getTermFeeAmount($con, $class, $stream, $session, $term, $isFinalYear);
            }
        } else {
            $feeAmount = getTermFeeAmount($con, $class, $stream, $session, $term, $isFinalYear);
        }
        
        // Calculate unpaid
        $unpaidAmount = $feeAmount - $amountPaid;
        
        if ($unpaidAmount > 0) {
            $unpaidTerms[$term] = [
                'term' => $term,
                'fee' => $feeAmount,
                'paid' => $amountPaid,
                'unpaid' => $unpaidAmount
            ];
        }
    }
    
    return $unpaidTerms;
}

/**
 * Get term fee amount
 * 
 * @param mysqli $con Database connection
 * @param string $class Student class
 * @param string $stream Student stream
 * @param string $session Session
 * @param string $term Term
 * @param bool $isFinalYear Whether student is final year
 * @return float Fee amount
 */
function getTermFeeAmount($con, $class, $stream, $session, $term, $isFinalYear = false)
{
    // For final year students, Second Term includes Third Term
    if ($isFinalYear && $term === 'Second Term') {
        $secondTermFee = getRegularTermFee($con, $class, $stream, $session, 'Second Term');
        $thirdTermFee = getRegularTermFee($con, $class, $stream, $session, 'Third Term');
        return $secondTermFee + $thirdTermFee;
    }
    
    // Check for special first-term fees
    if ($term === 'First Term') {
        $specialFee = getSpecialFirstTermFee($con, $class, $stream, $session);
        if ($specialFee > 0) {
            return $specialFee;
        }
    }
    
    return getRegularTermFee($con, $class, $stream, $session, $term);
}

/**
 * Get regular term fee
 * 
 * @param mysqli $con Database connection
 * @param string $class Student class
 * @param string $stream Student stream
 * @param string $session Session
 * @param string $term Term
 * @return float Fee amount
 */
function getRegularTermFee($con, $class, $stream, $session, $term)
{
    // Try fee_records first
    $stmt = $con->prepare(
        "SELECT amount FROM fee_records 
         WHERE class = ? AND stream = ? AND session = ? AND term = ? 
         AND student_type = 'Old' AND name = 'School Fees' LIMIT 1"
    );
    $stmt->bind_param("ssss", $class, $stream, $session, $term);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    if ($result && $result['amount'] > 0) {
        return floatval($result['amount']);
    }
    
    // Fall back to fees table
    $stmt = $con->prepare(
        "SELECT amount FROM fees 
         WHERE class = ? AND stream = ? AND student_type = 'Old' AND name = 'School Fees' LIMIT 1"
    );
    $stmt->bind_param("ss", $class, $stream);
    $stmt->execute();
    $result = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    return floatval($result['amount'] ?? 0);
}

/**
 * Get special first-term fee (e.g., "First Term SS 1 School Fees")
 * 
 * @param mysqli $con Database connection
 * @param string $class Student class
 * @param string $stream Student stream
 * @param string $session Session
 * @return float Fee amount or 0 if not found
 */
function getSpecialFirstTermFee($con, $class, $stream, $session)
{
    $specialNames = [
        'First Term SS 1 School Fees',
        'First Term SS 3 School Fees',
        'First Term JSS 3 School Fees'
    ];
    
    foreach ($specialNames as $name) {
        if (strpos($name, $class) === false) {
            continue;
        }
        
        // Try fee_records
        $stmt = $con->prepare(
            "SELECT amount FROM fee_records 
             WHERE name = ? AND class = ? AND stream = ? AND student_type = 'Old' 
             AND session = ? AND term = 'First Term' ORDER BY id DESC LIMIT 1"
        );
        $stmt->bind_param("ssss", $name, $class, $stream, $session);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        if ($result && $result['amount'] > 0) {
            return floatval($result['amount']);
        }
        
        // Try fees table
        $stmt = $con->prepare(
            "SELECT amount FROM fees 
             WHERE name = ? AND class = ? AND stream = ? AND student_type = 'Old' LIMIT 1"
        );
        $stmt->bind_param("sss", $name, $class, $stream);
        $stmt->execute();
        $result = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        
        if ($result && $result['amount'] > 0) {
            return floatval($result['amount']);
        }
    }
    
    return 0;
}

/**
 * Allocate payment across terms
 * 
 * Core allocation logic that:
 * 1. Determines target session(s) based on student status
 * 2. Prioritizes previous session debt for OLD students
 * 3. Allocates to unpaid terms in chronological order
 * 4. Handles excess (refund for final year, carry-over for others)
 * 
 * @param mysqli $con Database connection
 * @param int $studentId Student ID
 * @param float $paymentAmount Payment amount
 * @param string $currentSession Current session
 * @param string $class Student class
 * @param string $stream Student stream
 * @param string $admissionTerm Admission term (for NEW students)
 * @return array Array of allocations with session, term, and amount
 */
function allocatePaymentAcrossTerms($con, $studentId, $paymentAmount, $currentSession, $class, $stream, $admissionTerm = null)
{
    $allocations = [];
    $remaining = $paymentAmount;
    
    $isNewStudent = isNewStudent($con, $studentId, $currentSession);
    $isFinalYear = (trim($class) === 'SS 3' || trim($class) === 'JSS 3');
    
    // ========== NEW STUDENT LOGIC ==========
    if ($isNewStudent) {
        if (!$admissionTerm) {
            $admissionTerm = getAdmissionTerm($con, $studentId, $currentSession);
        }
        
        if ($admissionTerm) {
            // Check if "School Fees (New Student)" is fully paid
            $isNewStudentFeePaid = isNewStudentFeeFullyPaid($con, $studentId, $currentSession, $admissionTerm, $class, $stream);
            
            if (!$isNewStudentFeePaid) {
                // Allocate to complete "School Fees (New Student)"
                $newStudentFee = getNewStudentFeeAmount($con, $class, $stream, $currentSession, $admissionTerm);
                
                // Get amount already paid
                $stmt = $con->prepare(
                    "SELECT SUM(amount) as paid FROM payment 
                     WHERE student_id = ? AND session = ? AND Term = ? AND pay_for = 'School Fees (New Student)'"
                );
                $stmt->bind_param("iss", $studentId, $currentSession, $admissionTerm);
                $stmt->execute();
                $paidResult = $stmt->get_result()->fetch_assoc();
                $stmt->close();
                
                $amountPaid = floatval($paidResult['paid'] ?? 0);
                $unpaidNewStudentFee = $newStudentFee - $amountPaid;
                
                if ($unpaidNewStudentFee > 0) {
                    $allocate = min($remaining, $unpaidNewStudentFee);
                    $allocations[] = [
                        'session' => $currentSession,
                        'term' => $admissionTerm,
                        'amount' => $allocate,
                        'pay_for' => 'School Fees (New Student)'
                    ];
                    $remaining -= $allocate;
                }
            }
        }
    }
    
    // ========== OLD STUDENT LOGIC - CHECK PREVIOUS SESSION DEBT ==========
    if (!$isNewStudent) {
        $debtInfo         = getPreviousSessionDebt($con, $studentId, $currentSession);
        $previousSessions = $debtInfo['sessions'];
        $origAdmSession   = $debtInfo['admissionSession'];
        $origAdmTerm      = $debtInfo['admissionTerm'];

        foreach ($previousSessions as $prevSession) {
            if ($remaining <= 0) break;

            // If this is the student's original admission session, pass their admission term
            // so getUnpaidTermsInSession() uses "School Fees (New Student)" fee for that term
            $prevAdmTerm = ($prevSession === $origAdmSession) ? $origAdmTerm : null;

            $unpaidTerms = getUnpaidTermsInSession($con, $studentId, $prevSession, $class, $stream, $isFinalYear, $prevAdmTerm);

            foreach (['First Term', 'Second Term', 'Third Term'] as $term) {
                if ($remaining <= 0) break;
                if (!isset($unpaidTerms[$term])) continue;

                $unpaidAmount = $unpaidTerms[$term]['unpaid'];
                $allocate     = min($remaining, $unpaidAmount);

                $allocations[] = [
                    'session' => $prevSession,
                    'term'    => $term,
                    'amount'  => $allocate,
                    'pay_for' => 'School Fees',
                ];
                $remaining -= $allocate;
            }
        }
    }
    
    // ========== CURRENT SESSION ALLOCATION ==========
    if ($remaining > 0) {
        // For NEW students, pass admissionTerm so the correct fee name is used for that term
        $unpaidTerms = getUnpaidTermsInSession($con, $studentId, $currentSession, $class, $stream, $isFinalYear, $isNewStudent ? $admissionTerm : null);
        
        // For NEW students, only allocate from admission term onwards (skip earlier terms)
        $termOrder = ['First Term', 'Second Term', 'Third Term'];
        if ($isNewStudent && $admissionTerm) {
            $admissionIndex = array_search($admissionTerm, $termOrder);
            if ($admissionIndex !== false) {
                $termOrder = array_slice($termOrder, $admissionIndex);
            }
        }
        
        foreach ($termOrder as $term) {
            if ($remaining <= 0) break;
            if (!isset($unpaidTerms[$term])) continue;
            
            $unpaidAmount = $unpaidTerms[$term]['unpaid'];
            $allocate = min($remaining, $unpaidAmount);
            
            $allocations[] = [
                'session' => $currentSession,
                'term' => $term,
                'amount' => $allocate,
                'pay_for' => 'School Fees'
            ];
            $remaining -= $allocate;
        }
    }
    
    // ========== HANDLE EXCESS ==========
    if ($remaining > 0) {
        $excessAllocation = handleExcessPayment($con, $studentId, $remaining, $currentSession, $class, $stream, $isFinalYear);
        if ($excessAllocation) {
            $allocations[] = $excessAllocation;
        }
    }
    
    return $allocations;
}

/**
 * Handle excess payment
 * 
 * For final year students: create refund record
 * For non-final year students: carry to next session
 * 
 * @param mysqli $con Database connection
 * @param int $studentId Student ID
 * @param float $excessAmount Excess amount
 * @param string $currentSession Current session
 * @param string $class Student class
 * @param string $stream Student stream
 * @param bool $isFinalYear Whether student is final year
 * @return array|null Allocation array or null if refund created
 */
function handleExcessPayment($con, $studentId, $excessAmount, $currentSession, $class, $stream, $isFinalYear)
{
    if ($isFinalYear) {
        // Create refund record for final year students
        // This is handled by the calling code
        return null;
    }
    
    // Carry to next session for non-final year students
    $sessionParts = explode('/', $currentSession);
    if (count($sessionParts) === 2) {
        $nextSession = ($sessionParts[0] + 1) . '/' . ($sessionParts[1] + 1);
        
        return [
            'session' => $nextSession,
            'term' => 'First Term',
            'amount' => $excessAmount,
            'pay_for' => 'School Fees'
        ];
    }
    
    return null;
}

/**
 * Check if a payment session is in the future relative to the active school session
 * 
 * @param string $paymentSession The session being paid for
 * @param string $activeSession  The currently active school session
 * @return bool true if paymentSession is ahead of activeSession
 */
function isFutureSession($paymentSession, $activeSession)
{
    return strcmp($paymentSession, $activeSession) > 0;
}

/**
 * Validate future session payment
 * 
 * Rules:
 * 1. If the student's own academic_session matches the payment session, they are a
 *    future-admitted student paying for their own session — always allowed.
 * 2. Otherwise, the student must have no unpaid terms in the currently active session
 *    before they can pay for a future session.
 * 
 * @param mysqli $con Database connection
 * @param int $studentId Student ID
 * @param string $activeSession The currently active school session
 * @param string $class Student class
 * @param string $stream Student stream
 * @param string|null $paymentSession The session being paid for (optional, used for rule 1)
 * @return bool true if allowed, false if current session still has unpaid terms
 */
function validateFutureSessionPayment($con, $studentId, $activeSession, $class, $stream, $paymentSession = null)
{
    // Rule 1: student is admitted into the future session they are paying for
    // — they have no obligations in the current active session
    if ($paymentSession !== null) {
        $stmt = $con->prepare("SELECT academic_session FROM students WHERE id = ? LIMIT 1");
        $stmt->bind_param("i", $studentId);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if ($row && $row['academic_session'] === $paymentSession) {
            // This is their own admission session — allow unconditionally
            return true;
        }
    }

    // Rule 2: existing student paying ahead — must clear current session first
    $isFinalYear = (trim($class) === 'SS 3' || trim($class) === 'JSS 3');
    $admissionTerm = getAdmissionTerm($con, $studentId, $activeSession);
    $unpaidTerms = getUnpaidTermsInSession(
        $con, $studentId, $activeSession, $class, $stream, $isFinalYear, $admissionTerm
    );

    return empty($unpaidTerms);
}
