easypay-api/includes/PaymentProcessor.php

<?php
/**
 * PaymentProcessor Class
 * 
 * Encapsulates the core payment processing logic.
 * This is the single source of truth for payment operations.
 * Used by both the web UI and the API.
 */

class PaymentProcessor
{
    private $pdo;

    // Constants for payment processing
    private const CREDIT_BANK_ID = '000001373634585148';
    private const DEBIT_BANK_ID = '514297805530965017';
    private const PAYMODE_ID = '000001373901891416';
    private const RECIPIENT_ID = 'SS0011441283890434';
    private const PAYMENT_BY = 'SS0011441283890434';
    private const CREATED_BY = 'SS0011441283890434';
    private const CREATED_AS = 'school';
    private const PAYMENT_STATUS = 'Approved';
    private const PAYMODE_CATEGORY = 'BANK';

    public function __construct($pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Process a payment transaction
     * 
     * @param array $params Payment parameters
     * @return array Result with success status, message, and payment details
     * @throws Exception on validation or processing errors
     */
    public function processPayment(array $params)
    {
        // Extract and validate parameters
        $studentId = $params['student_id'] ?? '';
        $studentCode = $params['student_code'] ?? '';
        $selectedFees = $params['selected_fees'] ?? [];
        $tellerNumber = $params['teller_number'] ?? '';
        $paymentDate = $params['payment_date'] ?? '';
        $amountToUse = floatval($params['amount_to_use'] ?? 0);
        $source = $params['source'] ?? 'web'; // Track payment source (web/api)

        // Validate required fields
        if (
            empty($studentId) || empty($studentCode) || empty($selectedFees) ||
            empty($tellerNumber) || empty($paymentDate) || $amountToUse <= 0
        ) {
            throw new Exception('Missing required fields');
        }

        // Validate selected fees is an array
        if (!is_array($selectedFees) || count($selectedFees) === 0) {
            throw new Exception('No fees selected');
        }

        // Sort fees by academic_session ASC, term_of_session ASC (oldest to newest)
        usort($selectedFees, function ($a, $b) {
            if ($a['academic_session'] != $b['academic_session']) {
                return $a['academic_session'] - $b['academic_session'];
            }
            return $a['term_of_session'] - $b['term_of_session'];
        });

        // Re-fetch bank statement to verify
        $bankStatement = $this->getBankStatement($tellerNumber);

        if (!$bankStatement) {
            throw new Exception('Bank statement not found for teller number: ' . $tellerNumber);
        }

        // Verify unreconciled amount
        if ($amountToUse > $bankStatement['unreconciled_amount']) {
            throw new Exception('Amount exceeds unreconciled amount on teller');
        }

        // Extract teller name and number from description
        $descParts = explode(' ', $bankStatement['description']);
        $tellerNo = array_pop($descParts);
        $tellerName = implode(' ', $descParts);

        // Use the oldest fee's session/term for transaction_id
        $dominantSession = $selectedFees[0]['academic_session'];
        $dominantTerm = $selectedFees[0]['term_of_session'];

        // Generate transaction_id
        $transactionId = $studentId . $dominantSession . $paymentDate;

        // STEP 1: Guard check - prevent duplicate payments on same date
        $this->checkDuplicatePayment($studentId, $paymentDate);

        // STEP 2: Allocate payment across fees (oldest to newest)
        $feeAllocations = $this->allocatePaymentToFees($selectedFees, $amountToUse);

        if (count($feeAllocations) === 0) {
            throw new Exception('No fees could be allocated');
        }

        // Calculate total paid
        $totalPaid = array_sum(array_column($feeAllocations, 'amount'));

        // Generate receipt_no (used across all fee records)
        $receiptNo = $studentCode . $paymentDate;

        // STEP 3: Execute database transaction
        $this->pdo->beginTransaction();

        try {
            // 3a) INSERT into tb_account_school_fee_payments (per fee)
            $this->insertSchoolFeePayments(
                $feeAllocations,
                $studentId,
                $studentCode,
                $transactionId,
                $tellerNo,
                $tellerName,
                $paymentDate
            );

            // 3b) INSERT into tb_account_school_fee_sum_payments (single record)
            $this->insertSumPayment(
                $studentId,
                $totalPaid,
                $paymentDate,
                $transactionId,
                $dominantSession,
                $dominantTerm
            );

            // 3c) INSERT into tb_account_student_payments (per fee)
            $this->insertStudentPayments(
                $feeAllocations,
                $studentId,
                $studentCode,
                $transactionId,
                $paymentDate
            );

            // 3d) INSERT into tb_account_payment_registers (per fee)
            $this->insertPaymentRegisters(
                $feeAllocations,
                $studentId,
                $studentCode,
                $receiptNo,
                $paymentDate,
                $transactionId
            );

            // 3e) UPDATE tb_student_logistics
            $this->updateStudentLogistics($feeAllocations, $studentId);

            // Commit transaction
            $this->pdo->commit();

            // Fetch fee descriptions for display
            $feeDescriptions = $this->getFeeDescriptions($feeAllocations);

            // Prepare payment details for display
            foreach ($feeAllocations as $key => $allocation) {
                $feeAllocations[$key]['description'] = $feeDescriptions[$allocation['fee_id']] ?? 'Unknown Fee';
                $feeAllocations[$key]['total_paid_to_date'] = $allocation['previous_paid'] + $allocation['amount'];
                $feeAllocations[$key]['balance'] = $allocation['amount_billed'] - $feeAllocations[$key]['total_paid_to_date'];
            }

            return [
                'success' => true,
                'message' => 'Payment processed successfully',
                'data' => [
                    'student_id' => $studentId,
                    'student_code' => $studentCode,
                    'payment_date' => $paymentDate,
                    'teller_no' => $tellerNo,
                    'teller_name' => $tellerName,
                    'total_paid' => $totalPaid,
                    'receipt_no' => $receiptNo,
                    'transaction_id' => $transactionId,
                    'allocations' => $feeAllocations,
                    'remaining_unreconciled' => $bankStatement['unreconciled_amount'] - $totalPaid,
                    'source' => $source
                ]
            ];

        } catch (Exception $e) {
            $this->pdo->rollBack();
            throw new Exception('Transaction failed: ' . $e->getMessage());
        }
    }

    /**
     * Get bank statement by teller number
     */
    private function getBankStatement($tellerNumber)
    {
        $sql = "SELECT
                    bs.id,
                    bs.description,
                    bs.amount_paid,
                    bs.payment_date,
                    COALESCE(fp.total_registered_fee, 0.00) AS registered_amount,
                    (bs.amount_paid - COALESCE(fp.total_registered_fee, 0.00)) AS unreconciled_amount
                FROM tb_account_bank_statements bs
                LEFT JOIN (
                    SELECT teller_no, SUM(amount_paid) AS total_registered_fee
                    FROM tb_account_school_fee_payments
                    GROUP BY teller_no
                ) fp ON SUBSTRING_INDEX(bs.description, ' ', -1) = fp.teller_no
                WHERE SUBSTRING_INDEX(bs.description, ' ', -1) = :teller_number
                LIMIT 1";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['teller_number' => $tellerNumber]);
        return $stmt->fetch();
    }

    /**
     * Check for duplicate payment on the same date
     */
    private function checkDuplicatePayment($studentId, $paymentDate)
    {
        $sql = "SELECT COUNT(*) AS cnt
                FROM tb_account_school_fee_sum_payments
                WHERE student_id = :student_id
                  AND payment_date = :payment_date";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            'student_id' => $studentId,
            'payment_date' => $paymentDate
        ]);

        $duplicateCheck = $stmt->fetch();
        if ($duplicateCheck['cnt'] > 0) {
            throw new Exception('A payment for this student has already been registered on this date (' . $paymentDate . ')');
        }
    }

    /**
     * Allocate payment amount across fees (oldest to newest)
     */
    private function allocatePaymentToFees($selectedFees, $amountToUse)
    {
        $feeAllocations = [];
        $remainingAmount = $amountToUse;

        foreach ($selectedFees as $fee) {
            if ($remainingAmount <= 0) {
                break;
            }

            $outstandingAmount = floatval($fee['outstanding_amount']);

            if ($remainingAmount >= $outstandingAmount) {
                // Fully settle this fee
                $amountForThisFee = $outstandingAmount;
                $remainingAmount -= $outstandingAmount;
            } else {
                // Partially settle this fee
                $amountForThisFee = $remainingAmount;
                $remainingAmount = 0;
            }

            $feeAllocations[] = [
                'fee_id' => $fee['fee_id'],
                'academic_session' => $fee['academic_session'],
                'term_of_session' => $fee['term_of_session'],
                'amount' => $amountForThisFee,
                'amount_billed' => floatval($fee['amount_billed'] ?? 0),
                'previous_paid' => floatval($fee['amount_paid'] ?? 0)
            ];
        }

        return $feeAllocations;
    }

    /**
     * Insert records into tb_account_school_fee_payments
     */
    private function insertSchoolFeePayments(
        $feeAllocations,
        $studentId,
        $studentCode,
        $transactionId,
        $tellerNo,
        $tellerName,
        $paymentDate
    ) {
        foreach ($feeAllocations as $allocation) {
            $schoolFeePaymentId = $studentCode . $allocation['fee_id'] . $paymentDate;

            $sql = "INSERT INTO tb_account_school_fee_payments (
                        id, fee_id, student_id, transaction_id, amount_paid,
                        teller_no, teller_name, credit_bank_id, debit_bank_id,
                        paymode_id, payment_status, payment_date, recipient_id,
                        academic_session, term_of_session, payment_by, payment_on
                    ) VALUES (
                        :id, :fee_id, :student_id, :transaction_id, :amount_paid,
                        :teller_no, :teller_name, :credit_bank_id, :debit_bank_id,
                        :paymode_id, :payment_status, :payment_date, :recipient_id,
                        :academic_session, :term_of_session, :payment_by, NOW()
                    )";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'id' => $schoolFeePaymentId,
                'fee_id' => $allocation['fee_id'],
                'student_id' => $studentId,
                'transaction_id' => $transactionId,
                'amount_paid' => $allocation['amount'],
                'teller_no' => $tellerNo,
                'teller_name' => $tellerName,
                'credit_bank_id' => self::CREDIT_BANK_ID,
                'debit_bank_id' => self::DEBIT_BANK_ID,
                'paymode_id' => self::PAYMODE_ID,
                'payment_status' => self::PAYMENT_STATUS,
                'payment_date' => $paymentDate,
                'recipient_id' => self::RECIPIENT_ID,
                'academic_session' => $allocation['academic_session'],
                'term_of_session' => $allocation['term_of_session'],
                'payment_by' => self::PAYMENT_BY
            ]);
        }
    }

    /**
     * Insert record into tb_account_school_fee_sum_payments
     */
    private function insertSumPayment(
        $studentId,
        $totalPaid,
        $paymentDate,
        $transactionId,
        $dominantSession,
        $dominantTerm
    ) {
        $sumPaymentsId = $studentId . $dominantSession . $paymentDate;

        $sql = "INSERT INTO tb_account_school_fee_sum_payments (
                    id, student_id, total_paid, paymode_id, payment_date,
                    credit_bank_id, debit_bank_id, transaction_id, status,
                    academic_session, term_of_session, registered_by, registered_on
                ) VALUES (
                    :id, :student_id, :total_paid, :paymode_id, :payment_date,
                    :credit_bank_id, :debit_bank_id, :transaction_id, :status,
                    :academic_session, :term_of_session, :registered_by, NOW()
                )";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            'id' => $sumPaymentsId,
            'student_id' => $studentId,
            'total_paid' => $totalPaid,
            'paymode_id' => self::PAYMODE_ID,
            'payment_date' => $paymentDate,
            'credit_bank_id' => self::CREDIT_BANK_ID,
            'debit_bank_id' => self::DEBIT_BANK_ID,
            'transaction_id' => $transactionId,
            'status' => self::PAYMENT_STATUS,
            'academic_session' => $dominantSession,
            'term_of_session' => $dominantTerm,
            'registered_by' => self::CREATED_BY
        ]);
    }

    /**
     * Insert records into tb_account_student_payments
     */
    private function insertStudentPayments(
        $feeAllocations,
        $studentId,
        $studentCode,
        $transactionId,
        $paymentDate
    ) {
        foreach ($feeAllocations as $allocation) {
            // Get current payment_to_date (sum of all previous payments for this fee/session/term)
            $sql = "SELECT SUM(payment_to_date) AS current_total
                    FROM tb_account_student_payments
                    WHERE student_id = :student_id
                      AND fee_id = :fee_id
                      AND academic_session = :academic_session
                      AND term_of_session = :term_of_session";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'student_id' => $studentId,
                'fee_id' => $allocation['fee_id'],
                'academic_session' => $allocation['academic_session'],
                'term_of_session' => $allocation['term_of_session']
            ]);

            $currentPayment = $stmt->fetch();
            $newPaymentToDate = $allocation['amount'];

            $studentPaymentId = $studentCode . $allocation['fee_id'] . $paymentDate;

            $sql = "INSERT INTO tb_account_student_payments (
                        id, fee_id, student_id, payment_to_date, transaction_id,
                        academic_session, term_of_session, created_by, created_as, created_on
                    ) VALUES (
                        :id, :fee_id, :student_id, :payment_to_date, :transaction_id,
                        :academic_session, :term_of_session, :created_by, :created_as, NOW()
                    )";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'id' => $studentPaymentId,
                'fee_id' => $allocation['fee_id'],
                'student_id' => $studentId,
                'payment_to_date' => $newPaymentToDate,
                'transaction_id' => $transactionId,
                'academic_session' => $allocation['academic_session'],
                'term_of_session' => $allocation['term_of_session'],
                'created_by' => self::CREATED_BY,
                'created_as' => self::CREATED_AS
            ]);
        }
    }

    /**
     * Insert records into tb_account_payment_registers
     */
    private function insertPaymentRegisters(
        $feeAllocations,
        $studentId,
        $studentCode,
        $receiptNo,
        $paymentDate,
        $transactionId
    ) {
        foreach ($feeAllocations as $allocation) {
            $paymentRegisterId = $studentCode . $allocation['fee_id'] . $paymentDate;

            $sql = "INSERT INTO tb_account_payment_registers (
                        id, fee_id, student_id, amount_paid, amount_due,
                        receipt_no, recipient_id, payment_date, paymode_category,
                        transaction_id, academic_session, term_of_session,
                        created_by, created_as, created_on
                    ) VALUES (
                        :id, :fee_id, :student_id, :amount_paid, :amount_due,
                        :receipt_no, :recipient_id, :payment_date, :paymode_category,
                        :transaction_id, :academic_session, :term_of_session,
                        :created_by, :created_as, NOW()
                    )";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'id' => $paymentRegisterId,
                'fee_id' => $allocation['fee_id'],
                'student_id' => $studentId,
                'amount_paid' => $allocation['amount'],
                'amount_due' => $allocation['amount'],
                'receipt_no' => $receiptNo,
                'recipient_id' => self::RECIPIENT_ID,
                'payment_date' => $paymentDate,
                'paymode_category' => self::PAYMODE_CATEGORY,
                'transaction_id' => $transactionId,
                'academic_session' => $allocation['academic_session'],
                'term_of_session' => $allocation['term_of_session'],
                'created_by' => self::CREATED_BY,
                'created_as' => self::CREATED_AS
            ]);
        }
    }

    /**
     * Update tb_student_logistics to reduce outstanding fees
     */
    private function updateStudentLogistics($feeAllocations, $studentId)
    {
        // Group allocations by session/term to update specific records
        $sessionTermTotals = [];
        foreach ($feeAllocations as $allocation) {
            $key = $allocation['academic_session'] . '-' . $allocation['term_of_session'];
            if (!isset($sessionTermTotals[$key])) {
                $sessionTermTotals[$key] = [
                    'academic_session' => $allocation['academic_session'],
                    'term_of_session' => $allocation['term_of_session'],
                    'total' => 0
                ];
            }
            $sessionTermTotals[$key]['total'] += $allocation['amount'];
        }

        // Update each session/term record
        foreach ($sessionTermTotals as $st) {
            $sql = "UPDATE tb_student_logistics
                    SET fees_outstanding = GREATEST(0, fees_outstanding - :total_paid)
                    WHERE student_id = :student_id
                      AND academic_session = :academic_session
                      AND term_of_session = :term_of_session";

            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'total_paid' => $st['total'],
                'student_id' => $studentId,
                'academic_session' => $st['academic_session'],
                'term_of_session' => $st['term_of_session']
            ]);
        }
    }

    /**
     * Get fee descriptions for display
     */
    private function getFeeDescriptions($feeAllocations)
    {
        $feeIds = array_column($feeAllocations, 'fee_id');
        $placeholders = implode(',', array_fill(0, count($feeIds), '?'));

        $sql = "SELECT id, description FROM tb_account_school_fees WHERE id IN ($placeholders)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($feeIds);
        return $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
    }

    /**
     * Validate student exists
     */
    public function validateStudent($studentId)
    {
        $sql = "SELECT id, student_code, 
                       CONCAT(last_name, ' ', first_name, ' ', COALESCE(other_name, '')) AS full_name
                FROM tb_student_registrations
                WHERE id = :student_id AND admission_status = 'Active'";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['student_id' => $studentId]);
        return $stmt->fetch();
    }

    /**
     * Get outstanding fees for a student
     */
    public function getOutstandingFees($studentId)
    {
        $sql = "SELECT
                    ar.fee_id,
                    sf.description AS fee_description,
                    ar.academic_session,
                    ar.term_of_session,
                    ar.actual_value AS amount_billed,
                    COALESCE(SUM(sp.payment_to_date), 0) AS amount_paid,
                    (ar.actual_value - COALESCE(SUM(sp.payment_to_date), 0)) AS outstanding_amount
                FROM tb_account_receivables ar
                INNER JOIN tb_account_school_fees sf ON ar.fee_id = sf.id
                LEFT JOIN tb_account_student_payments sp ON 
                    ar.student_id = sp.student_id AND 
                    ar.fee_id = sp.fee_id AND
                    ar.academic_session = sp.academic_session AND
                    ar.term_of_session = sp.term_of_session
                WHERE ar.student_id = :student_id
                GROUP BY ar.fee_id, ar.academic_session, ar.term_of_session, sf.description, ar.actual_value
                HAVING outstanding_amount > 0
                ORDER BY ar.academic_session ASC, ar.term_of_session ASC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['student_id' => $studentId]);
        return $stmt->fetchAll();
    }

    /**
     * Get total outstanding balance with breakdown
     * 
     * @param string $studentId
     * @return array Total and breakdown
     */
    public function getTotalOutstandingBalance($studentId)
    {
        // Reuse getOutstandingFees as it already provides the breakdown of unpaid items
        $breakdown = $this->getOutstandingFees($studentId);

        $totalOutstanding = 0;
        foreach ($breakdown as $item) {
            $totalOutstanding += floatval($item['outstanding_amount']);
        }

        return [
            'student_id' => $studentId,
            'currency' => 'NGN',
            'total_outstanding' => $totalOutstanding,
            'breakdown' => $breakdown
        ];
    }

    /**
     * Get invoice (fee breakdown) for a specific term
     * Shows all fees billed, paid, and balance
     * 
     * @param string $studentId
     * @param string $session
     * @param string $term
     * @return array List of fees
     */
    public function getTermInvoice($studentId, $session, $term)
    {
        $sql = "SELECT
                    ar.fee_id,
                    sf.description,
                    ar.actual_value AS amount_billed,
                    COALESCE(SUM(sp.payment_to_date), 0) AS amount_paid,
                    (ar.actual_value - COALESCE(SUM(sp.payment_to_date), 0)) AS balance
                FROM tb_account_receivables ar
                INNER JOIN tb_account_school_fees sf ON ar.fee_id = sf.id
                LEFT JOIN tb_account_student_payments sp ON 
                    ar.student_id = sp.student_id AND 
                    ar.fee_id = sp.fee_id AND
                    ar.academic_session = sp.academic_session AND
                    ar.term_of_session = sp.term_of_session
                WHERE ar.student_id = :student_id
                  AND ar.academic_session = :session
                  AND ar.term_of_session = :term
                GROUP BY ar.fee_id, sf.description, ar.actual_value";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            'student_id' => $studentId,
            'session' => $session,
            'term' => $term
        ]);

        return $stmt->fetchAll();
    }

    /**
     * Get payment status summary and transactions for a term
     * 
     * @param string $studentId
     * @param string $session
     * @param string $term
     * @return array Summary and transactions
     */
    public function getTermPaymentStatus($studentId, $session, $term)
    {
        // 1. Get Summary (Billed vs Paid)
        // We can use getTermInvoice to aggregate this
        $invoiceItems = $this->getTermInvoice($studentId, $session, $term);

        $totalBilled = 0;
        $totalPaid = 0;
        $totalBalance = 0;

        foreach ($invoiceItems as $item) {
            $totalBilled += floatval($item['amount_billed']);
            $totalPaid += floatval($item['amount_paid']);
            $totalBalance += floatval($item['balance']);
        }

        // 2. Get Transactions (Payments made for this term)
        // We look at the sum_payments table or specific fee payments
        // Using sum_payments is better for receipts, but it tracks 'dominant' session/term.
        // A better approach for "transactions" might be to look at where the money went.
        // However, usually we want to see "Receipts issued for this term".
        // Let's query tb_account_payment_registers which tracks receipts per fee,
        // or tb_account_school_fee_sum_payments which tracks the main transaction.

        // Let's use tb_account_school_fee_sum_payments for the main "Transactions" list
        // filtering by the session/term assigned to the payment.

        $sql = "SELECT
                    payment_date,
                    total_paid AS amount,
                    transaction_id,
                    id AS payment_ref
                FROM tb_account_school_fee_sum_payments
                WHERE student_id = :student_id
                  AND academic_session = :session
                  AND term_of_session = :term
                ORDER BY payment_date DESC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            'student_id' => $studentId,
            'session' => $session,
            'term' => $term
        ]);
        $transactions = $stmt->fetchAll();

        return [
            'summary' => [
                'total_billed' => $totalBilled,
                'total_paid' => $totalPaid,
                'outstanding' => $totalBalance
            ],
            'transactions' => $transactions
        ];
    }
}