<?php
include('includes/session.php');
include('includes/dbconnection.php');
include('includes/flash.php');
require_once 'vendor/autoload.php';
require_once __DIR__ . '/includes/session_term_helper.php';
require_once __DIR__ . '/helpers/term_allocation_helpers.php';

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
use Dotenv\Dotenv;

require 'phpmailer/src/Exception.php';
require 'phpmailer/src/PHPMailer.php';
require 'phpmailer/src/SMTP.php';

$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();

$school_name = $_ENV['SCHOOL_NAME'];
$url         = $_ENV['BASE_URL'] ?? '';

// ── Validate required GET params ──────────────────────────────────────────────
if (!isset($_GET['reference'], $_GET['studentno'], $_GET['email'], $_GET['studentName'], $_GET['stdid'])) {
    echo "Invalid request. Missing parameters.";
    exit;
}

$reference      = $_GET['reference'];
$studentno      = $_GET['studentno'];
$email          = $_GET['email'];
$studentName    = $_GET['studentName'];
$stdid          = $_GET['stdid'];
$tabSessionID   = $_GET['tab_id'] ?? '';
$term           = $_GET['term'];
$session        = $_GET['session'];
$discountUsed   = $_GET['discount_used']   ?? 'No';
$discountAmount = floatval($_GET['discount_amount']  ?? 0);
$discountPercent= floatval($_GET['discount_percent'] ?? 0);
$feeType        = $_GET['fee_type']  ?? 'Unknown';
$amountPaid     = floatval($_GET['amount'] ?? 0);
$paymentMethod  = "Wallet";

// ── Duplicate-safe upsert into `payments` table ───────────────────────────────
$checkResult = mysqli_query($con, "SELECT id FROM payments WHERE reference = '" . mysqli_real_escape_string($con, $reference) . "' LIMIT 1");
if (mysqli_num_rows($checkResult) > 0) {
    $paymentQuery = "UPDATE payments SET
        student_id = '$stdid', studentno = '" . mysqli_real_escape_string($con, $studentno) . "',
        studentName = '" . mysqli_real_escape_string($con, $studentName) . "',
        email = '" . mysqli_real_escape_string($con, $email) . "',
        amount = $amountPaid, status = 'approved',
        fee_type = '" . mysqli_real_escape_string($con, $feeType) . "',
        payment_date = NOW(),
        discount_used = '" . mysqli_real_escape_string($con, $discountUsed) . "',
        discount_amount = $discountAmount, discount_percent = $discountPercent,
        payment_method = '$paymentMethod',
        term_id = '" . mysqli_real_escape_string($con, $term) . "',
        session_id = '" . mysqli_real_escape_string($con, $session) . "'
        WHERE reference = '" . mysqli_real_escape_string($con, $reference) . "'";
} else {
    $paymentQuery = "INSERT INTO payments
        (student_id, studentno, studentName, email, reference, amount, status, fee_type,
         payment_date, discount_used, discount_amount, discount_percent, payment_method, term_id, session_id)
        VALUES
        ('$stdid', '" . mysqli_real_escape_string($con, $studentno) . "',
         '" . mysqli_real_escape_string($con, $studentName) . "',
         '" . mysqli_real_escape_string($con, $email) . "',
         '" . mysqli_real_escape_string($con, $reference) . "',
         $amountPaid, 'approved',
         '" . mysqli_real_escape_string($con, $feeType) . "',
         NOW(),
         '" . mysqli_real_escape_string($con, $discountUsed) . "',
         $discountAmount, $discountPercent, '$paymentMethod',
         '" . mysqli_real_escape_string($con, $term) . "',
         '" . mysqli_real_escape_string($con, $session) . "')";
}
mysqli_query($con, $paymentQuery);

// ── Fetch student record ───────────────────────────────────────────────────────
$sql  = "SELECT * FROM students WHERE studentno = ?";
$stmt = $con->prepare($sql);
$stmt->bind_param("s", $studentno);
$stmt->execute();
$studentRow = $stmt->get_result()->fetch_assoc();
$stmt->close();

if (!$studentRow) {
    echo "<script>alert('Student record not found.');window.location.href='oldstdboard.php?tab_id=$tabSessionID';</script>";
    exit;
}

$id           = $studentRow['id'];
$section      = $studentRow['section'];
$class        = $studentRow['class'];
$stream       = $studentRow['stream'];
$demoteStatus = $studentRow['demote_status'] ?? 0;
$school_type  = $stream;

// ── Duplicate payment guard ────────────────────────────────────────────────────
$dupCheck = mysqli_query($con, "SELECT id FROM payment WHERE studentno = '" . mysqli_real_escape_string($con, $studentno) . "' AND pay_id = '" . mysqli_real_escape_string($con, $reference) . "' LIMIT 1");
if (mysqli_num_rows($dupCheck) > 0) {
    echo "<script>alert('Payment already approved.');window.location.href='oldstdboard.php?tab_id=$tabSessionID';</script>";
    exit;
}

// ── Determine payment type and run appropriate approval logic ─────────────────
$isSchoolFees  = (stripos($feeType, 'School Fees') !== false);
$isLessonFees  = (stripos($feeType, 'Lesson') !== false || stripos($feeType, 'ACEC') !== false);

$allInserted   = false;
$emailBody     = '';

// ── SCHOOL FEES APPROVAL ───────────────────────────────────────────────────────
if ($isSchoolFees) {
    $isFinalYear = (trim($class) === 'SS 3' || trim($class) === 'JSS 3');

    // ── Future session guard ───────────────────────────────────────────────────
    $stResult          = resolveCurrentSessionTerm($con);
    $activeSessionName = $stResult['session_name'];

    if (isFutureSession($session, $activeSessionName)) {
        if (!validateFutureSessionPayment($con, $id, $activeSessionName, $class, $stream, $session)) {
            flash_alert('Future session payment rejected: you still have unpaid fees in the current session (' . $activeSessionName . '). Please clear those first.');
            header("Location: oldstdboard.php?tab_id=$tabSessionID");
            exit;
        }
    }

    // Determine if student is NEW or OLD
    $isNew = isNewStudent($con, $id, $session);
    $admissionTerm = null;
    
    if ($isNew) {
        // For NEW students, get admission term
        $admissionTerm = getAdmissionTerm($con, $id, $session);
        if (!$admissionTerm) {
            $admissionTerm = 'First Term'; // Default if not found
        }
    }
    
    // Use helper function to allocate payment across terms
    $allocations = allocatePaymentAcrossTerms($con, $id, $amountPaid, $session, $class, $stream, $admissionTerm);
    
    // Insert payment records for each allocation
    $allInserted = true;
    $excessAmount = 0;
    
    foreach ($allocations as $allocation) {
        $allocSession = $allocation['session'];
        $allocTerm = $allocation['term'];
        $allocAmount = $allocation['amount'];
        $payFor = $allocation['pay_for'];
        
        // Check if this is an excess/carry-over allocation to next session
        if ($allocSession !== $session) {
            // This is excess payment being carried to next session
            $excessAmount = $allocAmount;
            
            // Insert into payment table for next session
            $term_pay_id = $reference . '_next_' . strtolower(str_replace(' ', '_', $allocTerm));
            $q = mysqli_query($con, "INSERT INTO payment (studentno, studentName, Term, session, class, school_type, payment_type, pay_id, amount, pay_for, student_id, method, section, discount_used, discount_amount, discount_percent)
                VALUES ('" . mysqli_real_escape_string($con, $studentno) . "', '" . mysqli_real_escape_string($con, $studentName) . "', '$allocTerm', '" . mysqli_real_escape_string($con, $allocSession) . "', '" . mysqli_real_escape_string($con, $class) . "', '" . mysqli_real_escape_string($con, $school_type) . "', 'Full Payment', '$term_pay_id', '$allocAmount', '$payFor', '$id', 'Wallet', '" . mysqli_real_escape_string($con, $section) . "', 'No', 0, 0)");
            if (!$q) { $allInserted = false; break; }
        } else {
            // This is a regular allocation to current session
            $term_pay_id = $reference . '_' . str_replace(' ', '_', strtolower($allocTerm));
            $q = mysqli_query($con, "INSERT INTO payment (studentno, studentName, Term, session, class, school_type, payment_type, pay_id, amount, pay_for, student_id, method, section, discount_used, discount_amount, discount_percent)
                VALUES ('" . mysqli_real_escape_string($con, $studentno) . "', '" . mysqli_real_escape_string($con, $studentName) . "', '$allocTerm', '" . mysqli_real_escape_string($con, $allocSession) . "', '" . mysqli_real_escape_string($con, $class) . "', '" . mysqli_real_escape_string($con, $school_type) . "', 'Full Payment', '$term_pay_id', '$allocAmount', '$payFor', '$id', 'Wallet', '" . mysqli_real_escape_string($con, $section) . "', '" . mysqli_real_escape_string($con, $discountUsed) . "', $discountAmount, $discountPercent)");
            if (!$q) { $allInserted = false; break; }
        }
    }
    
    // Handle excess for final year students (create refund record)
    if ($allInserted && $isFinalYear && $excessAmount > 0) {
        mysqli_query($con, "INSERT INTO refund (student_id, studentno, studentName, class, section, session, term, amount, pay_id, method, school_type, payment_type, status, reason)
            VALUES ('$id', '" . mysqli_real_escape_string($con, $studentno) . "', '" . mysqli_real_escape_string($con, $studentName) . "', '" . mysqli_real_escape_string($con, $class) . "', '" . mysqli_real_escape_string($con, $section) . "', '" . mysqli_real_escape_string($con, $session) . "', 'Second Term', '$excessAmount', '" . mysqli_real_escape_string($con, $reference) . "', 'Wallet', '" . mysqli_real_escape_string($con, $school_type) . "', 'Full Payment', 'pending', 'Excess payment from Final Year student (Both terms fully paid)')");
    }

    $emailBody = "
        <p>We are pleased to inform you that your School Fees payment of NGN $amountPaid has been confirmed successfully.</p>
        <p><strong>Session:</strong> $session</p>
        <p><strong>Class:</strong> $class</p>
        <p><strong>Payment Method:</strong> Wallet</p>" .
        ($discountUsed === 'Yes' ? "<p><strong>Discount Applied:</strong> NGN $discountAmount ($discountPercent%)</p>" : "");

// ── LESSON / ACEC FEES APPROVAL ────────────────────────────────────────────────
} elseif ($isLessonFees) {
    $q = mysqli_query($con, "INSERT INTO payment (studentno, studentName, Term, session, class, school_type, payment_type, pay_id, amount, pay_for, student_id, method, section)
        VALUES ('" . mysqli_real_escape_string($con, $studentno) . "', '" . mysqli_real_escape_string($con, $studentName) . "', '" . mysqli_real_escape_string($con, $term) . "', '" . mysqli_real_escape_string($con, $session) . "', '" . mysqli_real_escape_string($con, $class) . "', '" . mysqli_real_escape_string($con, $school_type) . "', 'Full Payment', '" . mysqli_real_escape_string($con, $reference) . "', $amountPaid, '" . mysqli_real_escape_string($con, $feeType) . "', '$id', 'Wallet', '" . mysqli_real_escape_string($con, $section) . "')");
    $allInserted = (bool)$q;

    $emailBody = "
        <p>We are pleased to inform you that your Lesson Fees payment of NGN $amountPaid has been confirmed successfully.</p>
        <p><strong>Term:</strong> $term &nbsp; <strong>Session:</strong> $session</p>
        <p><strong>Class:</strong> $class &nbsp; <strong>Payment Method:</strong> Wallet</p>";

// ── OTHER FEES APPROVAL ────────────────────────────────────────────────────────
} else {
    $boardingFeeNames = [
        'JSS 1-3 FULL (ONE TERM) BOARDING FEES (NEW STUDENT)',
        'JSS 1-3 FULL (2 TERMS) BOARDING FEES (NEW STUDENT)',
        'JSS 1-3 FULL SESSION BOARDING FEES (NEW STUDENT)',
        'SS 1-3 FULL (ONE TERM) BOARDING FEES (NEW STUDENT)',
        'SS 1-3 FULL (2 TERMS) BOARDING FEES (NEW STUDENT)',
        'SS 1-3 FULL SESSION BOARDING FEES (NEW STUDENT)',
    ];

    $q = mysqli_query($con, "INSERT INTO payment (studentno, studentName, Term, session, class, school_type, payment_type, pay_id, amount, pay_for, student_id, method, section)
        VALUES ('" . mysqli_real_escape_string($con, $studentno) . "', '" . mysqli_real_escape_string($con, $studentName) . "', '" . mysqli_real_escape_string($con, $term) . "', '" . mysqli_real_escape_string($con, $session) . "', '" . mysqli_real_escape_string($con, $class) . "', '" . mysqli_real_escape_string($con, $school_type) . "', 'Full Payment', '" . mysqli_real_escape_string($con, $reference) . "', $amountPaid, '" . mysqli_real_escape_string($con, $feeType) . "', '$id', 'Wallet', '" . mysqli_real_escape_string($con, $section) . "')");
    $allInserted = (bool)$q;

    $emailBody = "
        <p>We are pleased to inform you that your payment of NGN $amountPaid for <strong>" . htmlspecialchars($feeType) . "</strong> has been confirmed successfully.</p>
        <p><strong>Term:</strong> $term &nbsp; <strong>Session:</strong> $session</p>
        <p><strong>Class:</strong> $class &nbsp; <strong>Payment Method:</strong> Wallet</p>";

    if ($allInserted && in_array(trim($feeType), $boardingFeeNames)) {
        $chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
        $token = '';
        for ($i = 0; $i < 10; $i++) $token .= $chars[random_int(0, strlen($chars) - 1)];
        mysqli_query($con, "INSERT INTO registration_tokens (studentno, student_name, token_for, class, term, session, school_type, email, token, token_status)
            VALUES ('" . mysqli_real_escape_string($con, $studentno) . "', '" . mysqli_real_escape_string($con, $studentName) . "', '" . mysqli_real_escape_string($con, $feeType) . "', '" . mysqli_real_escape_string($con, $class) . "', '" . mysqli_real_escape_string($con, $term) . "', '" . mysqli_real_escape_string($con, $session) . "', '" . mysqli_real_escape_string($con, $school_type) . "', '" . mysqli_real_escape_string($con, $email) . "', '$token', 'pending')");
        $registrationLink = "$url/boarding_registration.php";
        $emailBody .= "
        <p><strong>Registration Token:</strong> <span style='font-size:18px;color:red;'>$token</span></p>
        <p>This token is required to complete your boarding registration. <a href='$registrationLink'>Complete Boarding Registration</a></p>";
    }
}

// ── Send confirmation email to student ────────────────────────────────────────
if ($allInserted) {
    try {
        $mail = new PHPMailer(true);
        $mail->isSMTP();
        $mail->Host       = $_ENV['SMTP_HOST'];
        $mail->SMTPAuth   = true;
        $mail->Username   = $_ENV['SMTP_NO_REPLY'] ?? $_ENV['SMTP_USERNAME'];
        $mail->Password   = $_ENV['SMTP_NO_REPLY_PASSWORD'] ?? $_ENV['SMTP_PASSWORD'];
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS;
        $mail->Port       = $_ENV['SMTP_PORT'];
        $mail->Timeout    = 10;
        $mail->setFrom($_ENV['SMTP_NO_REPLY'] ?? $_ENV['SMTP_USERNAME'], $school_name);
        $mail->addAddress($email, $studentName);
        $mail->isHTML(true);
        $mail->Subject = $school_name . ' – Payment Approved';
        $mail->Body    = "
            <html><body>
            <div style='max-width:600px;margin:auto;font-family:Arial,sans-serif;background:#f4f4f4;padding:20px;'>
                <h1 style='color:#fff;text-align:center;padding:10px 0;background:#4CAF50;'>Payment Approved</h1>
                <p>Dear $studentName,</p>
                $emailBody
                <p>Please log in to your student portal to view and download your receipt.</p>
                <p>Thank you!</p>
                <hr><center><p>$school_name &copy; " . date('Y') . ". All Rights Reserved.</p></center>
            </div>
            </body></html>";
        $mail->send();
    } catch (Exception $e) {
        error_log("Wallet payment email failed for $studentno: " . $e->getMessage());
    }

    flash_alert('Payment approved successfully! Your wallet payment has been confirmed.');
    header("Location: oldstdboard.php?tab_id=$tabSessionID");
    exit;
} else {
    flash_alert("Payment was processed but could not be fully recorded. Please contact support with reference: $reference");
    header("Location: oldstdboard.php?tab_id=$tabSessionID");
    exit;
}
