<?php

session_start();

if (!isset($_SESSION['user_id'])) {

    echo json_encode(['error' => 'Unauthorized']);

    exit;

}



require_once '../config.php';

require_once '../ai_config.php';



// Ensure getSetting function is available

if (!function_exists('getSetting')) {

    function getSetting($key, $default = '') {

        $conn = getDBConnection();

        $result = $conn->query("SELECT setting_value FROM settings WHERE setting_key = '$key'");

        if ($result && $row = $result->fetch_assoc()) {

            return $row['setting_value'];

        }

        return $default;

    }

}



$message = $_POST['message'] ?? '';

$response = [];



// Initialize AI components

$predictor = new AIPaymentPredictor();

$recommendations = new AIRecommendations();



// Process message based on intent

if (strpos(strtolower($message), 'fee') !== false || 

    strpos(strtolower($message), 'payment') !== false ||

    strpos(strtolower($message), 'outstanding') !== false) {

    $response = handleFinancialQueries($message);

    

} elseif (strpos(strtolower($message), 'subject') !== false || 

          strpos(strtolower($message), 'class subject') !== false ||

          strpos(strtolower($message), 'subjects offered') !== false) {

    $response = handleSubjectQueries($message);

    

} elseif (strpos(strtolower($message), 'score') !== false || 

          strpos(strtolower($message), 'grade') !== false ||

          strpos(strtolower($message), 'result') !== false ||

          strpos(strtolower($message), 'academic') !== false) {

    $response = handleAcademicQueries($message);

    

} elseif (strpos(strtolower($message), 'student') !== false || 

          strpos(strtolower($message), 'pupil') !== false ||

          strpos(strtolower($message), 'learner') !== false) {

    $response = handleStudentQueries($message);

    

} elseif (strpos(strtolower($message), 'expense') !== false || 

          strpos(strtolower($message), 'spending') !== false ||

          strpos(strtolower($message), 'cost') !== false) {

    $response = handleExpenseQueries($message);

    

} elseif (strpos(strtolower($message), 'sms') !== false || 

          strpos(strtolower($message), 'notification') !== false ||

          strpos(strtolower($message), 'message') !== false ||

          strpos(strtolower($message), 'alert') !== false) {

    $response = handleCommunicationQueries($message);

    

} elseif (strpos(strtolower($message), 'report') !== false || 

          strpos(strtolower($message), 'report card') !== false) {

    $response = handleReportQueries($message);

    

} elseif (strpos(strtolower($message), 'staff') !== false || 

          strpos(strtolower($message), 'teacher') !== false ||

          strpos(strtolower($message), 'employee') !== false) {

    $response = handleStaffQueries($message);

    

} elseif (strpos(strtolower($message), 'activity') !== false || 

          strpos(strtolower($message), 'log') !== false ||

          strpos(strtolower($message), 'history') !== false) {

    $response = handleActivityQueries($message);

    

} elseif (strpos(strtolower($message), 'help') !== false || 

          strpos(strtolower($message), 'what can you do') !== false) {

    $response = handleHelp();

    

} else {

    // For general queries, use OpenAI/HuggingFace

    $response = handleGeneralQuery($message);

}



echo json_encode($response);



/**

 * Handle financial queries (fees, payments, outstanding)

 */

function handleFinancialQueries($message) {

    $conn = getDBConnection();

    $message = strtolower($message);

    

    // Check if asking about specific class

    if (preg_match('/class (\d+|[a-z]+)/i', $message, $matches)) {

        $className = $matches[0];

        return getFeeStructureByClass($className);

    }

    

    // Check if asking about specific student

    if (preg_match('/student (\d+|[a-z]+)/i', $message, $matches)) {

        $studentQuery = $matches[0];

        return getStudentPaymentInfo($studentQuery);

    }

    

    // Check if asking about outstanding fees

    if (strpos($message, 'outstanding') !== false || strpos($message, 'owing') !== false) {

        return getOutstandingFeesSummary();

    }

    

    // Default: get fee summary

    return getFeeSummary();

}



/**

 * Get fee structure by class

 */

function getFeeStructureByClass($className) {

    $conn = getDBConnection();

    

    $className = str_replace(['class ', 'for ', 'in '], '', strtolower($className));

    $className = ucwords($className);

    

    $query = "SELECT * FROM fee_structures WHERE LOWER(class_name) LIKE ? ORDER BY fee_type";

    $searchTerm = '%' . strtolower($className) . '%';

    $stmt = $conn->prepare($query);

    $stmt->bind_param("s", $searchTerm);

    $stmt->execute();

    $result = $stmt->get_result();

    

    if ($result->num_rows == 0) {

        return [

            'message' => "<div style='text-align: center; padding: 20px;'>

                <span style='font-size: 48px;'>📋</span>

                <h4 style='color: #7f8c8d;'>No fee structures found for {$className}.</h4>

            </div>",

            'actions' => ['View all classes', 'Check another class']

        ];

    }

    

    $fees = [];

    $totalAmount = 0;

    while ($row = $result->fetch_assoc()) {

        $fees[] = $row;

        $totalAmount += $row['amount'];

    }

    

    // Get student count

    $countQuery = "SELECT COUNT(*) as count FROM students WHERE LOWER(class) LIKE ? AND status = 'active'";

    $stmt = $conn->prepare($countQuery);

    $stmt->bind_param("s", $searchTerm);

    $stmt->execute();

    $countResult = $stmt->get_result();

    $studentCount = $countResult->fetch_assoc()['count'];

    

    // Get payment statistics

    $paymentQuery = "SELECT 

                        COUNT(DISTINCT s.id) as students_paid,

                        COALESCE(SUM(p.amount_paid), 0) as total_collected

                     FROM students s

                     LEFT JOIN payments p ON s.id = p.student_id

                     WHERE LOWER(s.class) LIKE ?";

    $stmt = $conn->prepare($paymentQuery);

    $stmt->bind_param("s", $searchTerm);

    $stmt->execute();

    $paymentStats = $stmt->get_result()->fetch_assoc();

    

    $collectionRate = ($totalAmount * max($studentCount, 1)) > 0 ? 

        ($paymentStats['total_collected'] / ($totalAmount * max($studentCount, 1))) * 100 : 0;

    

    // Build HTML response - FIXED VERSION with proper escaping

    $html = formatHtmlHeader("Fee Structure - {$className}", 'h4');

    

    $html .= "<div style='background: #e8f5e9; padding: 10px; border-radius: 4px; margin: 10px 0;'>";

    $html .= "<span style='font-weight: 600;'>Enrollment:</span> {$studentCount} students";

    $html .= "</div>";

    

    $html .= formatHtmlSubHeader("Fee Breakdown");

    

    foreach ($fees as $fee) {

        $dueDate = date('d M Y', strtotime($fee['due_date']));

        $daysLeft = (strtotime($fee['due_date']) - time()) / 86400;

        

        if ($daysLeft < 0) {

            $status = formatHtmlBadge('OVERDUE', 'danger');

        } elseif ($daysLeft <= 7) {

            $status = formatHtmlBadge('Due in ' . round($daysLeft) . ' days', 'warning');

        } else {

            $status = formatHtmlBadge('Due on ' . $dueDate, 'info');

        }

        

        $html .= "

        <div style='background: #f8f9fa; padding: 10px; margin: 8px 0; border-radius: 4px;'>

            <div style='display: flex; justify-content: space-between; align-items: center;'>

                <span style='font-weight: 600;'>{$fee['fee_type']}</span>

                {$status}

            </div>

            <div style='display: flex; justify-content: space-between; margin-top: 5px; color: #7f8c8d;'>

                <span>Amount: <strong>" . formatCurrency($fee['amount']) . "</strong></span>

                <span>Year: {$fee['academic_year']}</span>

            </div>

        </div>";

    }

    

    $html .= formatHtmlDivider();

    $html .= formatHtmlSubHeader("Financial Summary");

    

    $html .= formatHtmlStat("Total Fee Types", (string)count($fees));

    $html .= formatHtmlStat("Total per Student", formatCurrency($totalAmount));

    $html .= formatHtmlStat("Class Total", formatCurrency($totalAmount * $studentCount));

    $html .= formatHtmlStat("Collected So Far", formatCurrency($paymentStats['total_collected']), '#27ae60');

    

    $html .= formatHtmlProgress($collectionRate, "Collection Rate");

    

    $conn->close();

    

    return [

        'message' => $html,

        'actions' => ['Check another class', 'View all fees', 'Record payment']

    ];

}



/**

 * Get student payment information

 */

function getStudentPaymentInfo($query) {

    $conn = getDBConnection();

    

    $searchTerm = str_replace(['student ', 'for ', 'info '], '', strtolower($query));

    

    $studentQuery = "SELECT * FROM students WHERE 

                     student_id LIKE ? OR 

                     CONCAT(first_name, ' ', last_name) LIKE ? 

                     LIMIT 1";

    $searchParam = '%' . $searchTerm . '%';

    $stmt = $conn->prepare($studentQuery);

    $stmt->bind_param("ss", $searchParam, $searchParam);

    $stmt->execute();

    $result = $stmt->get_result();

    

    if ($result->num_rows == 0) {

        return [

            'message' => "Student not found. Please check the student ID or name.",

            'actions' => ['Search again', 'List all students']

        ];

    }

    

    $student = $result->fetch_assoc();

    

    // Get fee structures

    $feeQuery = "SELECT * FROM fee_structures WHERE class_name = ?";

    $stmt = $conn->prepare($feeQuery);

    $stmt->bind_param("s", $student['class']);

    $stmt->execute();

    $fees = $stmt->get_result();

    

    // Get payment history

    $paymentQuery = "SELECT p.*, fs.fee_type 

                     FROM payments p

                     JOIN fee_structures fs ON p.fee_structure_id = fs.id

                     WHERE p.student_id = ?

                     ORDER BY p.payment_date DESC";

    $stmt = $conn->prepare($paymentQuery);

    $stmt->bind_param("i", $student['id']);

    $stmt->execute();

    $payments = $stmt->get_result();

    

    $message = formatHeader("Student Account Summary");

    $message .= "\n";

    

    $message .= "Student: " . $student['first_name'] . " " . $student['last_name'] . "\n";

    $message .= "ID: " . $student['student_id'] . "\n";

    $message .= "Class: " . $student['class'] . " | Section: " . ($student['section'] ?: '-') . "\n";

    $message .= "Parent: " . ($student['parent_name'] ?: 'Not provided') . "\n";

    $message .= "Contact: " . ($student['phone'] ?: 'Not provided') . "\n\n";

    

    $message .= formatHeader("Fee Structure", '-', 30) . "\n";

    

    $totalExpected = 0;

    while ($fee = $fees->fetch_assoc()) {

        $totalExpected += $fee['amount'];

        $dueDate = date('d M Y', strtotime($fee['due_date']));

        $message .= "â€¢ " . $fee['fee_type'] . ": " . formatCurrency($fee['amount']) . "\n";

        $message .= "  Due: " . $dueDate . "\n\n";

    }

    

    $message .= formatHeader("Payment History", '-', 30) . "\n";

    

    $totalPaid = 0;

    if ($payments->num_rows > 0) {

        while ($payment = $payments->fetch_assoc()) {

            $totalPaid += $payment['amount_paid'];

            $date = date('d M Y', strtotime($payment['payment_date']));

            $message .= "â€¢ " . $payment['fee_type'] . "\n";

            $message .= "  Amount: " . formatCurrency($payment['amount_paid']) . "\n";

            $message .= "  Date: " . $date . " (" . ucfirst($payment['payment_method']) . ")\n";

            if ($payment['transaction_id']) {

                $message .= "  Ref: " . $payment['transaction_id'] . "\n";

            }

            $message .= "\n";

        }

    } else {

        $message .= "No payments recorded\n\n";

    }

    

    $outstanding = $totalExpected - $totalPaid;

    

    $message .= formatHeader("Balance Summary", '-', 30) . "\n";

    $message .= "â€¢ Total Expected: " . formatCurrency($totalExpected) . "\n";

    $message .= "â€¢ Total Paid: " . formatCurrency($totalPaid) . "\n";

    $message .= "â€¢ Outstanding Balance: " . formatCurrency($outstanding);

    

    if ($outstanding > 0) {

        $message .= " (Payment required)";

    } else {

        $message .= " (Fully paid)";

    }

    $message .= "\n";

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['Record payment', 'Send reminder', 'View all students']

    ];

}



/**

 * Get outstanding fees summary

 */

function getOutstandingFeesSummary() {

    $conn = getDBConnection();

    

    $query = "SELECT 

                s.id,

                s.student_id,

                s.first_name,

                s.last_name,

                s.class,

                s.parent_name,

                s.phone,

                COALESCE((SELECT SUM(fs.amount) FROM fee_structures fs WHERE fs.class_name = s.class), 0) as total_expected,

                COALESCE((SELECT SUM(p.amount_paid) FROM payments p WHERE p.student_id = s.id), 0) as total_paid

              FROM students s

              WHERE s.status = 'active'

              HAVING total_expected > total_paid

              ORDER BY (total_expected - total_paid) DESC";

    

    $result = $conn->query($query);

    

    if ($result->num_rows == 0) {

        return [

            'message' => "

            <div style='text-align: center; padding: 30px;'>

                <span style='font-size: 48px;'>âœ…</span>

                <h4 style='color: #27ae60; margin: 15px 0;'>No Outstanding Fees</h4>

                <p style='color: #7f8c8d;'>All accounts are up to date.</p>

            </div>",

            'actions' => ['View all students', 'Check payments']

        ];

    }

    

    $html = formatHtmlHeader("Outstanding Fees Summary", 'h4');

    

    $totalOutstanding = 0;

    $students = [];

    

    while ($row = $result->fetch_assoc()) {

        $outstanding = $row['total_expected'] - $row['total_paid'];

        $totalOutstanding += $outstanding;

        $students[] = [

            'name' => $row['first_name'] . ' ' . $row['last_name'],

            'id' => $row['student_id'],

            'class' => $row['class'],

            'outstanding' => $outstanding,

            'parent' => $row['parent_name'],

            'phone' => $row['phone']

        ];

    }

    

    $html .= "<div style='background: #e74c3c; color: white; padding: 15px; border-radius: 8px; margin: 15px 0;'>";

    $html .= "<div style='font-size: 14px; opacity: 0.9;'>Total Outstanding</div>";

    $html .= "<div style='font-size: 28px; font-weight: bold;'>" . formatCurrency($totalOutstanding) . "</div>";

    $html .= "<div style='font-size: 13px;'>Students with Arrears: " . count($students) . "</div>";

    $html .= "</div>";

    

    $html .= formatHtmlSubHeader("Top 5 Outstanding Balances");

    

    $top5 = array_slice($students, 0, 5);

  foreach ($top5 as $student) {

    $riskLevel = $student['outstanding'] > 1000 ? 'danger' : ($student['outstanding'] > 500 ? 'warning' : 'info');

    

    // Determine border color based on risk level

    if ($riskLevel == 'danger') {

        $borderColor = '#e74c3c';

    } elseif ($riskLevel == 'warning') {

        $borderColor = '#f39c12';

    } else {

        $borderColor = '#3498db';

    }

    

    // Determine badge color

    if ($riskLevel == 'danger') {

        $badgeClass = 'bg-danger';

    } elseif ($riskLevel == 'warning') {

        $badgeClass = 'bg-warning';

    } else {

        $badgeClass = 'bg-info';

    }

    

    $html .= "

    <div style='background: #f8f9fa; padding: 10px; margin: 8px 0; border-radius: 4px; border-left: 4px solid {$borderColor};'>

        <div style='display: flex; justify-content: space-between; align-items: center;'>

            <span><strong>{$student['name']}</strong> ({$student['class']})</span>

            <span class='badge {$badgeClass}'>" . formatCurrency($student['outstanding']) . "</span>

        </div>

        <div style='font-size: 12px; color: #7f8c8d; margin-top: 5px;'>

            ID: {$student['id']} | Parent: {$student['parent']} | Tel: {$student['phone']}

        </div>

    </div>";

}

    

    if (count($students) > 5) {

        $html .= "<p style='text-align: center; color: #7f8c8d;'>... and " . (count($students) - 5) . " more students</p>";

    }

    

    $html .= formatHtmlDivider();

    $html .= formatHtmlSubHeader("Summary");

    $html .= formatHtmlStat("Average Debt", formatCurrency($totalOutstanding / count($students)));

    

    $conn->close();

    

    return [

        'message' => $html,

        'actions' => ['Send reminders', 'View all', 'Generate report']

    ];

}



/**

 * Get fee summary

 */

function getFeeSummary() {

    $conn = getDBConnection();

    

    $query = "SELECT 

                class_name,

                COUNT(*) as fee_count,

                SUM(amount) as total_amount,

                MIN(due_date) as earliest_due,

                MAX(due_date) as latest_due

              FROM fee_structures

              GROUP BY class_name

              ORDER BY FIELD(class_name, 

                  'Nursery 1', 'Nursery 2', 

                  'KG 1', 'KG 2',

                  'Class 1', 'Class 2', 'Class 3', 'Class 4', 'Class 5', 'Class 6',

                  'JHS 1', 'JHS 2', 'JHS 3')";

    

    $result = $conn->query($query);

    

    $html = formatHtmlHeader("Fee Structure Summary by Class", 'h4');

    

    $totalFees = 0;

    $grandTotal = 0;

    $classCount = 0;

    

    $classItems = [];

    

    while ($row = $result->fetch_assoc()) {

        $classCount++;

        $totalFees += $row['fee_count'];

        $grandTotal += $row['total_amount'];

        

        $classItems[] = "

        <div style='background: #f8f9fa; padding: 10px; border-radius: 4px;'>

            <div style='font-weight: 600; color: #2c3e50; margin-bottom: 5px;'>{$row['class_name']}</div>

            <div style='font-size: 13px; color: #7f8c8d;'>

                <div>Fee Types: {$row['fee_count']}</div>

                <div>Total: " . formatCurrency($row['total_amount']) . "</div>

                <div>Due: " . formatDateRange($row['earliest_due'], $row['latest_due']) . "</div>

            </div>

        </div>";

    }

    

    $html .= "<div style='display: grid; grid-template-columns: repeat(2, 1fr); gap: 10px; margin: 15px 0;'>";

    $html .= implode('', $classItems);

    $html .= "</div>";

    

    $html .= formatHtmlDivider();

    

    $html .= formatHtmlSubHeader("Overall Summary");

    $html .= formatHtmlStat("Total Classes", $classCount);

    $html .= formatHtmlStat("Total Fee Types", $totalFees);

    $html .= formatHtmlStat("Grand Total All Fees", formatCurrency($grandTotal), '#27ae60');

    

    $conn->close();

    

    return [

        'message' => $html,

        'actions' => ['View by class', 'Check payments', 'Add fee structure']

    ];

}



/**

 * Handle subject queries

 */

function handleSubjectQueries($message) {

    $conn = getDBConnection();

    $message = strtolower($message);

    

    if (preg_match('/class (\d+|[a-z]+)/i', $message, $matches)) {

        $className = str_replace(['class ', 'for ', 'in '], '', strtolower($matches[0]));

        $className = ucwords($className);

        

        $query = "SELECT cs.*, s.subject_name, s.subject_code 

                  FROM class_subjects cs

                  JOIN subjects s ON cs.subject_id = s.id

                  WHERE LOWER(cs.class_name) LIKE ? AND s.active = 'yes'

                  ORDER BY cs.display_order";

        $searchTerm = '%' . strtolower($className) . '%';

        $stmt = $conn->prepare($query);

        $stmt->bind_param("s", $searchTerm);

        $stmt->execute();

        $result = $stmt->get_result();

        

        if ($result->num_rows == 0) {

            return [

                'message' => "<div style='text-align: center; padding: 20px;'>No subjects found for {$className}.</div>",

                'actions' => ['List all classes', 'View all subjects']

            ];

        }

        

        $html = formatHtmlHeader("Subjects Offered - {$className}", 'h4');

        

        $compulsory = [];

        $electives = [];

        

        while ($row = $result->fetch_assoc()) {

            $subject = $row['subject_name'];

            if ($row['subject_code']) {

                $subject .= " ({$row['subject_code']})";

            }

            

            if ($row['is_compulsory'] == 'yes') {

                $compulsory[] = $subject;

            } else {

                $electives[] = $subject;

            }

        }

        

        if (!empty($compulsory)) {

            $html .= formatHtmlSubHeader("Compulsory Subjects");

            $html .= "<ul style='list-style-type: disc; margin: 10px 0 20px 20px;'>";

            foreach ($compulsory as $subject) {

                $html .= "<li style='margin: 5px 0;'>{$subject}</li>";

            }

            $html .= "</ul>";

        }

        

        if (!empty($electives)) {

            $html .= formatHtmlSubHeader("Elective Subjects");

            $html .= "<ul style='list-style-type: circle; margin: 10px 0 20px 20px;'>";

            foreach ($electives as $subject) {

                $html .= "<li style='margin: 5px 0;'>{$subject}</li>";

            }

            $html .= "</ul>";

        }

        

        $html .= formatHtmlDivider();

        $html .= formatHtmlStat("Total Subjects", $result->num_rows, '#27ae60');

        

        $conn->close();

        

        return [

            'message' => $html,

            'actions' => ['Check another class', 'View grade boundaries', 'View scores']

        ];

    }

    

    // List all subjects

    $query = "SELECT s.*, COUNT(cs.id) as class_count 

              FROM subjects s

              LEFT JOIN class_subjects cs ON s.id = cs.subject_id

              WHERE s.active = 'yes'

              GROUP BY s.id

              ORDER BY s.subject_name";

    $result = $conn->query($query);

    

    $html = formatHtmlHeader("All Subjects Overview", 'h4');

    

    $subjectItems = [];

    while ($row = $result->fetch_assoc()) {

        $subjectItems[] = "

        <div style='background: #f8f9fa; padding: 10px; border-radius: 4px;'>

            <div style='font-weight: 600;'>{$row['subject_name']}</div>

            <div style='font-size: 12px; color: #7f8c8d;'>

                Code: {$row['subject_code']}<br>

                Classes: {$row['class_count']}

            </div>

        </div>";

    }

    

    $html .= "<div style='display: grid; grid-template-columns: repeat(2, 1fr); gap: 10px; margin: 15px 0;'>";

    $html .= implode('', $subjectItems);

    $html .= "</div>";

    

    $html .= formatHtmlDivider();

    $html .= formatHtmlStat("Total Active Subjects", $result->num_rows, '#27ae60');

    

    $conn->close();

    

    return [

        'message' => $html,

        'actions' => ['View by class', 'Check scores', 'Grade boundaries']

    ];

}



/**

 * Handle academic queries (scores, grades)

 */

function handleAcademicQueries($message) {

    $conn = getDBConnection();

    $message = strtolower($message);

    

    // Check if asking about grade boundaries

    if (strpos($message, 'grade boundary') !== false || strpos($message, 'grading system') !== false) {

        return getGradeBoundaries($message);

    }

    

    // Check if asking about specific student's scores

    if (preg_match('/student (\d+|[a-z]+)/i', $message, $matches) && 

        (strpos($message, 'score') !== false || strpos($message, 'grade') !== false)) {

        return getStudentScores($matches[0]);

    }

    

    // Check if asking about class performance

    if (preg_match('/class (\d+|[a-z]+)/i', $message, $matches) && 

        (strpos($message, 'performance') !== false || strpos($message, 'average') !== false)) {

        return getClassPerformance($matches[0]);

    }

    

    // Default: show academic summary

    return getAcademicSummary();

}



/**

 * Get grade boundaries

 */

function getGradeBoundaries($message) {

    $conn = getDBConnection();

    

    // Check if for specific class

    if (preg_match('/class (\d+|[a-z]+)/i', $message, $matches)) {

        $className = str_replace(['class ', 'for ', 'in '], '', strtolower($matches[0]));

        $className = ucwords($className);

        

        $query = "SELECT * FROM grade_boundaries WHERE LOWER(class_name) LIKE ? ORDER BY min_score DESC";

        $searchTerm = '%' . strtolower($className) . '%';

        $stmt = $conn->prepare($query);

        $stmt->bind_param("s", $searchTerm);

        $stmt->execute();

        $result = $stmt->get_result();

        

        if ($result->num_rows > 0) {

            $message = "Grade Boundaries - {$className}\n";

            $message .= str_repeat("-", 40) . "\n\n";

            

            while ($row = $result->fetch_assoc()) {

                $message .= "Grade {$row['grade']}: {$row['min_score']}% - {$row['max_score']}%\n";

                $message .= "  Remark: {$row['remark']}\n\n";

            }

            

            $conn->close();

            

            return [

                'message' => $message,

                'actions' => ['Check another class', 'View scores', 'View reports']

            ];

        }

    }

    

    // Get all grade boundaries grouped by class

    $query = "SELECT class_name, COUNT(*) as grade_count 

              FROM grade_boundaries 

              GROUP BY class_name 

              ORDER BY class_name";

    $result = $conn->query($query);

    

    $message = "Grading System Overview\n";

    $message .= str_repeat("-", 40) . "\n\n";

    

    while ($row = $result->fetch_assoc()) {

        $message .= "â€¢ {$row['class_name']}: {$row['grade_count']} grade levels\n";

    }

    

    $message .= "\nTo view specific grade boundaries, ask: 'Show grade boundaries for Class 4'";

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View by class', 'Check scores', 'View reports']

    ];

}



/**

 * Get student scores

 */

function getStudentScores($query) {

    $conn = getDBConnection();

    

    $searchTerm = str_replace(['student ', 'for ', 'scores ', 'grades '], '', strtolower($query));

    

    $studentQuery = "SELECT * FROM students WHERE 

                     student_id LIKE ? OR 

                     CONCAT(first_name, ' ', last_name) LIKE ? 

                     LIMIT 1";

    $searchParam = '%' . $searchTerm . '%';

    $stmt = $conn->prepare($studentQuery);

    $stmt->bind_param("ss", $searchParam, $searchParam);

    $stmt->execute();

    $studentResult = $stmt->get_result();

    

    if ($studentResult->num_rows == 0) {

        return [

            'message' => "Student not found. Please provide a valid student ID or name.",

            'actions' => ['Search again', 'List all students']

        ];

    }

    

    $student = $studentResult->fetch_assoc();

    

    $academicYear = getSetting('academic_year', date('Y') . '-' . (date('Y')+1));

    $currentTerm = getCurrentTerm();

    

    $scoreQuery = "SELECT sc.*, s.subject_name, s.subject_code

                   FROM scores sc

                   JOIN subjects s ON sc.subject_id = s.id

                   WHERE sc.student_id = ? AND sc.academic_year = ? AND sc.term = ?

                   ORDER BY s.subject_name";

    $stmt = $conn->prepare($scoreQuery);

    $stmt->bind_param("iss", $student['id'], $academicYear, $currentTerm);

    $stmt->execute();

    $scores = $stmt->get_result();

    

    $message = formatHeader("Academic Report - " . $student['first_name'] . " " . $student['last_name']);

    $message .= "\n";

    

    $message .= "Student ID: " . $student['student_id'] . "\n";

    $message .= "Class: " . $student['class'] . " | Section: " . ($student['section'] ?: '-') . "\n";

    $message .= "Term: " . $currentTerm . " | Academic Year: " . $academicYear . "\n\n";

    

    if ($scores->num_rows == 0) {

        $message .= "No scores recorded for this term.\n";

    } else {

        $message .= formatHeader("Subject Scores", '-', 30) . "\n\n";

        

        $totalScore = 0;

        $subjectCount = 0;

        

        while ($score = $scores->fetch_assoc()) {

            $subjectCount++;

            $totalScore += $score['total_score'];

            

            $message .= $score['subject_name'] . "\n";

            $message .= "  Class Work: " . number_format($score['class_score'], 1) . "%\n";

            $message .= "  Exam Score: " . number_format($score['exam_score'], 1) . "%\n";

            $message .= "  Total: " . number_format($score['total_score'], 1) . "%\n";

            $message .= "  Grade: " . $score['grade'] . " (" . $score['remark'] . ")\n\n";

        }

        

        $average = $totalScore / $subjectCount;

        

        $message .= formatHeader("Summary", '-', 30) . "\n";

        $message .= "â€¢ Average Score: " . number_format($average, 1) . "%\n";

        

        // Get grade for average

        $gradeQuery = "SELECT grade, remark FROM grade_boundaries 

                       WHERE LOWER(class_name) LIKE ? AND ? BETWEEN min_score AND max_score

                       LIMIT 1";

        $searchClass = '%' . strtolower($student['class']) . '%';

        $stmt = $conn->prepare($gradeQuery);

        $stmt->bind_param("sd", $searchClass, $average);

        $stmt->execute();

        $gradeResult = $stmt->get_result();

        

        if ($gradeRow = $gradeResult->fetch_assoc()) {

            $message .= "â€¢ Overall Grade: " . $gradeRow['grade'] . " (" . $gradeRow['remark'] . ")\n";

        }

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View report card', 'Check another student', 'View class performance']

    ];

}



/**

 * Get class performance

 */

function getClassPerformance($query) {

    $conn = getDBConnection();

    

    $className = str_replace(['class ', 'for ', 'performance '], '', strtolower($query));

    $className = ucwords($className);

    

    $academicYear = getSetting('academic_year', date('Y') . '-' . (date('Y')+1));

    $currentTerm = getCurrentTerm();

    

    $query = "SELECT 

                AVG(sc.total_score) as class_average,

                COUNT(DISTINCT sc.student_id) as students_with_scores,

                COUNT(sc.id) as total_subject_scores,

                MIN(sc.total_score) as minimum_score,

                MAX(sc.total_score) as maximum_score

              FROM scores sc

              JOIN students s ON sc.student_id = s.id

              WHERE s.class = ? AND sc.academic_year = ? AND sc.term = ?";

    

    $stmt = $conn->prepare($query);

    $stmt->bind_param("sss", $className, $academicYear, $currentTerm);

    $stmt->execute();

    $result = $stmt->get_result();

    $stats = $result->fetch_assoc();

    

    if (!$stats['students_with_scores']) {

        return [

            'message' => "No performance data found for {$className} in {$currentTerm} term.",

            'actions' => ['Check another class', 'View all classes']

        ];

    }

    

    // Get subject-wise performance

    $subjectQuery = "SELECT 

                        s.subject_name,

                        AVG(sc.total_score) as subject_average,

                        MIN(sc.total_score) as min_score,

                        MAX(sc.total_score) as max_score

                     FROM scores sc

                     JOIN subjects s ON sc.subject_id = s.id

                     JOIN students stu ON sc.student_id = stu.id

                     WHERE stu.class = ? AND sc.academic_year = ? AND sc.term = ?

                     GROUP BY sc.subject_id

                     ORDER BY subject_average DESC";

    

    $stmt = $conn->prepare($subjectQuery);

    $stmt->bind_param("sss", $className, $academicYear, $currentTerm);

    $stmt->execute();

    $subjects = $stmt->get_result();

    

    $message = "Class Performance - {$className}\n";

    $message .= str_repeat("-", 40) . "\n\n";

    $message .= "Term: {$currentTerm} | Academic Year: {$academicYear}\n\n";

    

    $message .= "Overall Statistics:\n";

    $message .= "â€¢ Class Average: " . number_format($stats['class_average'], 1) . "%\n";

    $message .= "â€¢ Students Assessed: {$stats['students_with_scores']}\n";

    $message .= "â€¢ Highest Score: " . number_format($stats['maximum_score'], 1) . "%\n";

    $message .= "â€¢ Lowest Score: " . number_format($stats['minimum_score'], 1) . "%\n\n";

    

    $message .= "Subject Performance:\n";

    while ($subject = $subjects->fetch_assoc()) {

        $message .= "{$subject['subject_name']}:\n";

        $message .= "  Average: " . number_format($subject['subject_average'], 1) . "%\n";

        $message .= "  Range: " . number_format($subject['min_score'], 1) . "% - " . 

                    number_format($subject['max_score'], 1) . "%\n\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View student details', 'Generate report', 'Check another class']

    ];

}



/**

 * Get academic summary

 */

function getAcademicSummary() {

    $conn = getDBConnection();

    

    $academicYear = getSetting('academic_year', date('Y') . '-' . (date('Y')+1));

    $currentTerm = getCurrentTerm();

    

    $query = "SELECT 

                COUNT(DISTINCT student_id) as students_with_scores,

                COUNT(*) as total_scores,

                AVG(total_score) as overall_average

              FROM scores

              WHERE academic_year = ? AND term = ?";

    

    $stmt = $conn->prepare($query);

    $stmt->bind_param("ss", $academicYear, $currentTerm);

    $stmt->execute();

    $result = $stmt->get_result();

    $stats = $result->fetch_assoc();

    

    $message = "Academic Summary - {$currentTerm} Term, {$academicYear}\n";

    $message .= str_repeat("-", 40) . "\n\n";

    

    if ($stats['students_with_scores'] > 0) {

        $message .= "â€¢ Students Assessed: {$stats['students_with_scores']}\n";

        $message .= "â€¢ Total Scores Recorded: {$stats['total_scores']}\n";

        $message .= "â€¢ Overall Average: " . number_format($stats['overall_average'], 1) . "%\n\n";

    } else {

        $message .= "No academic records found for current term.\n\n";

    }

    

    // Get class-wise subject count

    $classQuery = "SELECT 

                    class_name,

                    COUNT(*) as subject_count

                  FROM class_subjects

                  GROUP BY class_name

                  ORDER BY class_name";

    $classResult = $conn->query($classQuery);

    

    $message .= "Subjects by Class:\n";

    while ($class = $classResult->fetch_assoc()) {

        $message .= "â€¢ {$class['class_name']}: {$class['subject_count']} subjects\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View by class', 'Check scores', 'Grade boundaries']

    ];

}



/**

 * Handle student queries

 */

function handleStudentQueries($message) {

    $conn = getDBConnection();

    $message = strtolower($message);

    

    // Check if asking about specific student

    if (preg_match('/(?:student|pupil)\s+(\d+|[a-z\s]+)/i', $message, $matches)) {

        return getStudentInfo($matches[0]);

    }

    

    // Check if asking about class list

    if (preg_match('/class (\d+|[a-z]+)/i', $message, $matches) && 

        (strpos($message, 'list') !== false || strpos($message, 'students') !== false)) {

        return getClassList($matches[0]);

    }

    

    // Default: get student statistics

    return getStudentStatistics();

}



/**

 * Get student information

 */

function getStudentInfo($query) {

    $conn = getDBConnection();

    

    $searchTerm = str_replace(['student ', 'pupil ', 'info '], '', strtolower($query));

    

    $studentQuery = "SELECT * FROM students WHERE 

                     student_id LIKE ? OR 

                     CONCAT(first_name, ' ', last_name) LIKE ? 

                     LIMIT 1";

    $searchParam = '%' . $searchTerm . '%';

    $stmt = $conn->prepare($studentQuery);

    $stmt->bind_param("ss", $searchParam, $searchParam);

    $stmt->execute();

    $result = $stmt->get_result();

    

    if ($result->num_rows == 0) {

        return [

            'message' => "Student not found. Please check the student ID or name.",

            'actions' => ['Search again', 'List all students']

        ];

    }

    

    $student = $result->fetch_assoc();

    

    $message = "Student Information\n";

    $message .= str_repeat("-", 40) . "\n\n";

    $message .= "Name: {$student['first_name']} {$student['last_name']}\n";

    $message .= "Student ID: {$student['student_id']}\n";

    $message .= "Class: {$student['class']} | Section: {$student['section']}\n";

    $message .= "Status: " . ucfirst($student['status']) . "\n\n";

    

    $message .= "Personal Details:\n";

    $message .= "â€¢ Date of Birth: " . ($student['date_of_birth'] ? date('d M Y', strtotime($student['date_of_birth'])) : 'Not provided') . "\n";

    $message .= "â€¢ Gender: {$student['sex']}\n";

    $message .= "â€¢ Nationality: {$student['nationality']}\n";

    $message .= "â€¢ Religion: {$student['religion']}\n\n";

    

    $message .= "Parent/Guardian Information:\n";

    if ($student['father_name']) {

        $message .= "â€¢ Father: {$student['father_name']} - {$student['father_phone']}\n";

    }

    if ($student['mother_name']) {

        $message .= "â€¢ Mother: {$student['mother_name']} - {$student['mother_phone']}\n";

    }

    if ($student['guardian_relationship']) {

        $message .= "â€¢ Guardian: {$student['guardian_relationship']}\n";

    }

    

    $message .= "\nContact:\n";

    $message .= "â€¢ Phone: {$student['phone']}\n";

    $message .= "â€¢ Email: {$student['email']}\n";

    $message .= "â€¢ Address: {$student['address']}\n";

    

    if ($student['has_handicap'] == 'Yes') {

        $message .= "\nMedical Note: {$student['handicap_description']}\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View payments', 'View scores', 'Edit student']

    ];

}



/**

 * Get class list

 */

function getClassList($query) {

    $conn = getDBConnection();

    

    $className = str_replace(['class ', 'list ', 'students '], '', strtolower($query));

    $className = ucwords($className);

    

    $studentQuery = "SELECT * FROM students WHERE LOWER(class) LIKE ? AND status = 'active' ORDER BY first_name";

    $searchTerm = '%' . strtolower($className) . '%';

    $stmt = $conn->prepare($studentQuery);

    $stmt->bind_param("s", $searchTerm);

    $stmt->execute();

    $result = $stmt->get_result();

    

    if ($result->num_rows == 0) {

        return [

            'message' => "No students found in {$className}.",

            'actions' => ['Check another class', 'View all classes']

        ];

    }

    

    $message = "Student List - {$className}\n";

    $message .= str_repeat("-", 40) . "\n\n";

    $message .= "Total Students: " . $result->num_rows . "\n\n";

    

    $male = 0;

    $female = 0;

    

    while ($student = $result->fetch_assoc()) {

        if ($student['sex'] == 'Male') $male++;

        if ($student['sex'] == 'Female') $female++;

        

        $message .= "â€¢ {$student['first_name']} {$student['last_name']}";

        if ($student['student_id']) {

            $message .= " ({$student['student_id']})";

        }

        $message .= "\n";

    }

    

    $message .= "\nGender Distribution:\n";

    $message .= "â€¢ Male: {$male}\n";

    $message .= "â€¢ Female: {$female}\n";

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View another class', 'Add student', 'Generate report']

    ];

}



/**

 * Get student statistics

 */

function getStudentStatistics() {

    $conn = getDBConnection();

    

    $query = "SELECT 

                COUNT(*) as total_students,

                SUM(CASE WHEN sex = 'Male' THEN 1 ELSE 0 END) as male_count,

                SUM(CASE WHEN sex = 'Female' THEN 1 ELSE 0 END) as female_count,

                SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_count

              FROM students";

    

    $result = $conn->query($query);

    $stats = $result->fetch_assoc();

    

    $classQuery = "SELECT class, COUNT(*) as count FROM students GROUP BY class ORDER BY class";

    $classResult = $conn->query($classQuery);

    

    $message = "Student Statistics\n";

    $message .= str_repeat("-", 40) . "\n\n";

    $message .= "Total Students: {$stats['total_students']}\n";

    $message .= "â€¢ Male: {$stats['male_count']}\n";

    $message .= "â€¢ Female: {$stats['female_count']}\n";

    $message .= "â€¢ Active: {$stats['active_count']}\n\n";

    

    $message .= "Distribution by Class:\n";

    while ($class = $classResult->fetch_assoc()) {

        $message .= "â€¢ {$class['class']}: {$class['count']} students\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View by class', 'Add student', 'View all students']

    ];

}



/**

 * Handle expense queries

 */

function handleExpenseQueries($message) {

    $conn = getDBConnection();

    

    // Get expense summary

    $query = "SELECT 

                COUNT(*) as total_expenses,

                SUM(amount) as total_amount,

                MIN(expense_date) as earliest,

                MAX(expense_date) as latest

              FROM expenses";

    

    $result = $conn->query($query);

    $stats = $result->fetch_assoc();

    

    $categoryQuery = "SELECT category, COUNT(*) as count, SUM(amount) as total 

                      FROM expenses 

                      GROUP BY category 

                      ORDER BY total DESC";

    $categoryResult = $conn->query($categoryQuery);

    

    $message = "Expense Summary\n";

    $message .= str_repeat("-", 40) . "\n\n";

    $message .= "Total Expenses: " . formatCurrency($stats['total_amount']) . "\n";

    $message .= "Number of Transactions: {$stats['total_expenses']}\n";

    $message .= "Date Range: " . date('d M Y', strtotime($stats['earliest'])) . 

                " - " . date('d M Y', strtotime($stats['latest'])) . "\n\n";

    

    $message .= "Expenses by Category:\n";

    while ($category = $categoryResult->fetch_assoc()) {

        $percentage = ($category['total'] / $stats['total_amount']) * 100;

        $message .= "â€¢ {$category['category']}: " . formatCurrency($category['total']) . 

                    " ({$category['count']} transactions, " . number_format($percentage, 1) . "%)\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['Add expense', 'View details', 'Generate report']

    ];

}



/**

 * Handle communication queries (SMS, notifications)

 */

function handleCommunicationQueries($message) {

    $conn = getDBConnection();

    

    $query = "SELECT 

                COUNT(*) as total_sms,

                SUM(CASE WHEN status = 'Success' THEN 1 ELSE 0 END) as successful,

                SUM(CASE WHEN status = 'Failed' THEN 1 ELSE 0 END) as failed,

                MAX(created_at) as last_sent

              FROM sms_logs";

    

    $result = $conn->query($query);

    $stats = $result->fetch_assoc();

    

    $notifyQuery = "SELECT 

                        type,

                        COUNT(*) as count,

                        SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent_count

                    FROM notifications

                    GROUP BY type";

    $notifyResult = $conn->query($notifyQuery);

    

    $message = "Communication Summary\n";

    $message .= str_repeat("-", 40) . "\n\n";

    

    $message .= "SMS Statistics:\n";

    $message .= "â€¢ Total Sent: {$stats['total_sms']}\n";

    $message .= "â€¢ Successful: {$stats['successful']}\n";

    $message .= "â€¢ Failed: {$stats['failed']}\n";

    $message .= "â€¢ Success Rate: " . number_format(($stats['successful'] / max($stats['total_sms'], 1)) * 100, 1) . "%\n";

    $message .= "â€¢ Last Message: " . ($stats['last_sent'] ? date('d M Y H:i', strtotime($stats['last_sent'])) : 'Never') . "\n\n";

    

    $message .= "Notifications:\n";

    while ($notify = $notifyResult->fetch_assoc()) {

        $message .= "â€¢ " . ucfirst($notify['type']) . ": {$notify['sent_count']} sent\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['Send SMS', 'View logs', 'Check failed messages']

    ];

}



/**

 * Handle report queries

 */

function handleReportQueries($message) {

    $conn = getDBConnection();

    

    // Check if asking about specific student's report

    if (preg_match('/student (\d+|[a-z]+)/i', $message, $matches)) {

        return getStudentReportInfo($matches[0]);

    }

    

    // Get report summary

    $query = "SELECT 

                COUNT(*) as total_reports,

                COUNT(DISTINCT student_id) as students_with_reports,

                MIN(created_at) as earliest,

                MAX(created_at) as latest

              FROM report_details";

    

    $result = $conn->query($query);

    $stats = $result->fetch_assoc();

    

    $termQuery = "SELECT term, COUNT(*) as count FROM report_details GROUP BY term";

    $termResult = $conn->query($termQuery);

    

    $message = "Report Cards Summary\n";

    $message .= str_repeat("-", 40) . "\n\n";

    $message .= "Total Reports Generated: {$stats['total_reports']}\n";

    $message .= "Students with Reports: {$stats['students_with_reports']}\n\n";

    

    $message .= "Reports by Term:\n";

    while ($term = $termResult->fetch_assoc()) {

        $message .= "â€¢ Term {$term['term']}: {$term['count']} reports\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['Generate report', 'View student report', 'Print reports']

    ];

}



/**

 * Get student report information

 */

function getStudentReportInfo($query) {

    $conn = getDBConnection();

    

    $searchTerm = str_replace(['student ', 'for '], '', strtolower($query));

    

    // Find student

    $studentQuery = "SELECT * FROM students WHERE 

                     student_id LIKE ? OR 

                     CONCAT(first_name, ' ', last_name) LIKE ? 

                     LIMIT 1";

    $searchParam = '%' . $searchTerm . '%';

    $stmt = $conn->prepare($studentQuery);

    $stmt->bind_param("ss", $searchParam, $searchParam);

    $stmt->execute();

    $studentResult = $stmt->get_result();

    

    if ($studentResult->num_rows == 0) {

        return [

            'message' => "Student not found.",

            'actions' => ['Search again', 'Generate new report']

        ];

    }

    

    $student = $studentResult->fetch_assoc();

    

    // Get report details

    $reportQuery = "SELECT * FROM report_details 

                    WHERE student_id = ? 

                    ORDER BY created_at DESC 

                    LIMIT 1";

    $stmt = $conn->prepare($reportQuery);

    $stmt->bind_param("i", $student['id']);

    $stmt->execute();

    $reportResult = $stmt->get_result();

    

    $message = "Report Card - {$student['first_name']} {$student['last_name']}\n";

    $message .= str_repeat("-", 40) . "\n\n";

    

    if ($report = $reportResult->fetch_assoc()) {

        $message .= "Term: {$report['term']} | Year: {$report['academic_year']}\n";

        $message .= "Attendance: {$report['attendance_out_of']} days\n";

        

        if ($report['promoted_to']) {

            $message .= "Promoted to: {$report['promoted_to']}\n";

        }

        

        $message .= "\nTeacher's Remarks:\n";

        $message .= "Conduct: {$report['conduct']}\n";

        $message .= "Interest: {$report['interest']}\n";

        $message .= "Class Teacher: {$report['teacher_remark']}\n\n";

        

        $message .= "School Calendar:\n";

        $message .= "Closing Date: {$report['closing_date']}\n";

        $message .= "Reopening Date: {$report['reopening_date']}\n";

    } else {

        $message .= "No report card found for this student.\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['Generate report', 'View scores', 'Print report']

    ];

}



/**

 * Handle staff queries

 */

function handleStaffQueries($message) {

    $conn = getDBConnection();

    

    $query = "SELECT COUNT(*) as total_staff FROM staff WHERE status = 'active'";

    $result = $conn->query($query);

    $count = $result->fetch_assoc()['total_staff'];

    

    $message = "Staff Information\n";

    $message .= str_repeat("-", 40) . "\n\n";

    $message .= "Total Active Staff: {$count}\n\n";

    $message .= "To view detailed staff information, please use the Staff Management section.\n";

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View staff list', 'Add staff', 'Manage staff']

    ];

}



/**

 * Handle activity queries

 */

function handleActivityQueries($message) {

    $conn = getDBConnection();

    

    $query = "SELECT 

                COUNT(*) as total_activities,

                COUNT(DISTINCT user_id) as active_users,

                MAX(created_at) as last_activity

              FROM user_activities";

    

    $result = $conn->query($query);

    $stats = $result->fetch_assoc();

    

    $recentQuery = "SELECT ua.*, u.username 

                    FROM user_activities ua

                    JOIN users u ON ua.user_id = u.id

                    ORDER BY ua.created_at DESC

                    LIMIT 5";

    $recentResult = $conn->query($recentQuery);

    

    $message = "System Activity Summary\n";

    $message .= str_repeat("-", 40) . "\n\n";

    $message .= "Total Activities Logged: {$stats['total_activities']}\n";

    $message .= "Active Users: {$stats['active_users']}\n";

    $message .= "Last Activity: " . ($stats['last_activity'] ? date('d M Y H:i', strtotime($stats['last_activity'])) : 'Never') . "\n\n";

    

    $message .= "Recent Activities:\n";

    while ($activity = $recentResult->fetch_assoc()) {

        $time = date('d M H:i', strtotime($activity['created_at']));

        $message .= "â€¢ [{$time}] {$activity['username']}: {$activity['action']}\n";

    }

    

    $conn->close();

    

    return [

        'message' => $message,

        'actions' => ['View all logs', 'User activity', 'System audit']

    ];

}



/**

 * Handle general queries with OpenAI/HuggingFace

 */

function handleGeneralQuery($message) {

    $context = getSystemContext();

    

    // Try OpenAI first if API key is available

    if (defined('AI_OPENAI_API_KEY') && !empty(AI_OPENAI_API_KEY)) {

        $aiResponse = queryOpenAI($message, $context);

    } 

    // Fallback to HuggingFace

    elseif (defined('AI_HUGGINGFACE_API_KEY') && !empty(AI_HUGGINGFACE_API_KEY)) {

        $aiResponse = queryHuggingFace($message, $context);

    } 

    // If no APIs, use rule-based fallback

    else {

        $aiResponse = getRuleBasedResponse($message);

    }

    

    return [

        'message' => $aiResponse,

        'actions' => getSuggestedActions($message)

    ];

}



/**

 * Query OpenAI API

 */

function queryOpenAI($message, $context) {

    $api_key = AI_OPENAI_API_KEY;

    

    $data = [

        'model' => 'gpt-3.5-turbo',

        'messages' => [

            [

                'role' => 'system',

                'content' => "You are a professional school administration assistant for {$context['school_name']}. " .

                            "You help with fee management, academic records, student information, and reports. " .

                            "Provide clear, concise, and professional responses without emojis. " .

                            "Current date: " . date('Y-m-d') . ". " .

                            "Academic year: {$context['academic_year']}. " .

                            "Total students: {$context['total_students']}. " .

                            "For specific data queries, guide users to use commands like 'Show fee structure for Class 4' or 'Check student scores'."

            ],

            [

                'role' => 'user',

                'content' => $message

            ]

        ],

        'temperature' => 0.7,

        'max_tokens' => 300

    ];

    

    $ch = curl_init('https://api.openai.com/v1/chat/completions');

    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    curl_setopt($ch, CURLOPT_POST, true);

    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));

    curl_setopt($ch, CURLOPT_HTTPHEADER, [

        'Content-Type: application/json',

        'Authorization: Bearer ' . $api_key

    ]);

    

    $response = curl_exec($ch);

    $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);

    curl_close($ch);

    

    if ($http_code == 200) {

        $result = json_decode($response, true);

        return $result['choices'][0]['message']['content'] ?? 

               "I apologize, but I'm unable to process your request at the moment.";

    }

    

    error_log("OpenAI API error: HTTP $http_code");

    return getRuleBasedResponse($message);

}



/**

 * Query HuggingFace API

 */

function queryHuggingFace($message, $context) {

    $api_key = AI_HUGGINGFACE_API_KEY;

    

    $data = [

        'inputs' => "As a school admin assistant for {$context['school_name']}, respond professionally to: {$message}",

        'parameters' => [

            'max_length' => 200,

            'temperature' => 0.7

        ]

    ];

    

    $ch = curl_init('https://api-inference.huggingface.co/models/gpt2');

    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

    curl_setopt($ch, CURLOPT_POST, true);

    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));

    curl_setopt($ch, CURLOPT_HTTPHEADER, [

        'Content-Type: application/json',

        'Authorization: Bearer ' . $api_key

    ]);

    

    $response = curl_exec($ch);

    $http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);

    curl_close($ch);

    

    if ($http_code == 200) {

        $result = json_decode($response, true);

        if (is_array($result) && isset($result[0]['generated_text'])) {

            return $result[0]['generated_text'];

        }

    }

    

    error_log("HuggingFace API error: HTTP $http_code");

    return getRuleBasedResponse($message);

}



/**

 * Get system context for AI

 */

function getSystemContext() {

    $conn = getDBConnection();

    

    $school_name = getSetting('school_name', 'Our School');

    $academic_year = getSetting('academic_year', date('Y') . '-' . (date('Y')+1));

    

    $stats = $conn->query("SELECT 

        (SELECT COUNT(*) FROM students WHERE status = 'active') as total_students,

        (SELECT COUNT(*) FROM staff WHERE status = 'active') as total_staff,

        (SELECT COUNT(*) FROM subjects WHERE active = 'yes') as total_subjects,

        (SELECT COUNT(*) FROM fee_structures) as total_fees

    ")->fetch_assoc();

    

    $conn->close();

    

    return [

        'school_name' => $school_name,

        'academic_year' => $academic_year,

        'total_students' => $stats['total_students'] ?? 0,

        'total_staff' => $stats['total_staff'] ?? 0,

        'total_subjects' => $stats['total_subjects'] ?? 0,

        'total_fees' => $stats['total_fees'] ?? 0

    ];

}



/**

 * Get current term (simplified - you may want to store this in settings)

 */

function getCurrentTerm() {

    $month = date('n');

    if ($month >= 9 && $month <= 12) return '1';

    if ($month >= 1 && $month <= 4) return '2';

    return '3';

}



/**

 * Rule-based fallback responses

 */

function getRuleBasedResponse($message) {

    $message = strtolower($message);

    

    if (strpos($message, 'hello') !== false || strpos($message, 'hi') !== false) {

        return "Hello. I am your school administration assistant. How may I help you today? You can ask about fees, students, academic records, or reports.";

    }

    

    if (strpos($message, 'help') !== false) {

        return "I can assist with the following:\nâ€¢ Fee structures and payments\nâ€¢ Student information\nâ€¢ Academic scores and grades\nâ€¢ Class subjects\nâ€¢ Expense tracking\nâ€¢ Report cards\n\nTry asking specific questions like 'Show fee structure for Class 4' or 'Check student scores for John Doe'.";

    }

    

    if (strpos($message, 'thank') !== false) {

        return "You are welcome. Please let me know if you need any further assistance.";

    }

    

    return "I understand you have a question. For specific information, please try one of these commands:\nâ€¢ 'Show fee structure for [class]'\nâ€¢ 'Check student [name/ID]'\nâ€¢ 'View scores for [student]'\nâ€¢ 'List subjects for [class]'\nâ€¢ 'Outstanding fees summary'";

}



/**

 * Get suggested actions based on query

 */

function getSuggestedActions($message) {

    $message = strtolower($message);

    

    if (strpos($message, 'fee') !== false) {

        return ['Check fee structure', 'View payments', 'Outstanding fees', 'Record payment'];

    } elseif (strpos($message, 'student') !== false) {

        return ['Search student', 'View class list', 'Add student', 'Check scores'];

    } elseif (strpos($message, 'subject') !== false) {

        return ['View by class', 'All subjects', 'Grade boundaries', 'Check scores'];

    } elseif (strpos($message, 'score') !== false || strpos($message, 'grade') !== false) {

        return ['Check student scores', 'Class performance', 'Grade boundaries', 'Generate report'];

    } elseif (strpos($message, 'expense') !== false) {

        return ['View expenses', 'Add expense', 'Expense report', 'Category summary'];

    } else {

        return ['Fee summary', 'Student stats', 'Academic summary', 'Help'];

    }

}



/**

 * Handle help command

 */

function handleHelp() {

    $html = formatHtmlHeader("School Administration Assistant - Help Guide", 'h3');

    

    $html .= "<div style='background: linear-gradient(135deg, #667eea, #764ba2); color: white; padding: 15px; border-radius: 8px; margin: 15px 0;'>";

    $html .= "<span style='font-size: 20px;'>ðŸ¤–</span> I can help you manage fees, students, academics, and more!";

    $html .= "</div>";

    

    $commands = [

        'Financial' => [

            ['command' => 'Show fee structure for Class 4', 'example' => 'Show fee structure for Class 4'],

            ['command' => 'Check student STU001 payments', 'example' => 'Check student STU001 payments'],

            ['command' => 'Outstanding fees summary', 'example' => 'Outstanding fees summary'],

            ['command' => 'Expense summary', 'example' => 'Expense summary']

        ],

        'Academic' => [

            ['command' => 'List subjects for Class 3', 'example' => 'List subjects for Class 3'],

            ['command' => 'Check scores for John Doe', 'example' => 'Check scores for John Doe'],

            ['command' => 'Grade boundaries for Class 4', 'example' => 'Grade boundaries for Class 4'],

            ['command' => 'Class 5 performance', 'example' => 'Class 5 performance']

        ],

        'Student' => [

            ['command' => 'Student info STU001', 'example' => 'Student info STU001'],

            ['command' => 'Class 4 student list', 'example' => 'Class 4 student list'],

            ['command' => 'Student statistics', 'example' => 'Student statistics']

        ],

        'Reports' => [

            ['command' => 'Report card for STU001', 'example' => 'Report card for STU001'],

            ['command' => 'Activity log', 'example' => 'Activity log'],

            ['command' => 'SMS history', 'example' => 'SMS history']

        ]

    ];

    

    foreach ($commands as $category => $cmds) {

        $html .= formatHtmlSubHeader($category);

        $html .= "<ul style='list-style-type: none; padding: 0; margin: 10px 0;'>";

        foreach ($cmds as $cmd) {

            $html .= "

            <li style='margin: 8px 0; padding: 8px; background: #f8f9fa; border-radius: 4px;'>

                <span style='font-weight: 600; color: #2c3e50;'>{$cmd['command']}</span>

                <br>

                <span style='font-size: 12px; color: #7f8c8d;'>Example: '{$cmd['example']}'</span>

            </li>";

        }

        $html .= "</ul>";

    }

    

    $html .= formatHtmlDivider();

    $html .= "

    <div style='background: #f8f9fa; padding: 12px; border-radius: 8px; text-align: center;'>

        <span style='color: #7f8c8d;'>Simply type your question naturally, and I'll help you find the information!</span>

    </div>";

    

    return [

        'message' => $html,

        'actions' => ['Check fees', 'Find student', 'Academic summary', 'Financial summary']

    ];

}



/**

 * Format a section header with consistent styling

 */

function formatHeader($title, $char = '=', $length = 40) {

    return $title . "\n" . str_repeat($char, $length) . "\n";

}



/**

 * Format a key-value pair with consistent spacing

 */

function formatKeyValue($key, $value, $width = 15) {

    return "â€¢ " . str_pad($key . ":", $width) . " " . $value;

}



/**

 * Format a bullet point with proper indentation

 */

function formatBullet($text, $indent = 0) {

    $prefix = $indent > 0 ? str_repeat("  ", $indent) . "â€¢ " : "â€¢ ";

    return $prefix . $text;

}



/**

 * Format currency consistently

 */

// function formatCurrency($amount) {

//     return 'â‚µ' . number_format($amount, 2);

// }



/**

 * Format a date range

 */

function formatDateRange($start, $end) {

    return date('d M', strtotime($start)) . " - " . date('d M Y', strtotime($end));

}



/**

 * HTML Formatting Functions for Beautiful Chat Responses

 */



function formatHtmlHeader($title, $level = 'h4') {

    $colors = [

        'h3' => '#2c3e50',

        'h4' => '#34495e',

        'h5' => '#7f8c8d'

    ];

    $color = $colors[$level] ?? '#34495e';

    return "<{$level} style='color: {$color}; margin: 15px 0 10px 0; border-bottom: 2px solid #3498db; padding-bottom: 5px;'>{$title}</{$level}>";

}



function formatHtmlSubHeader($title) {

    return "<h5 style='color: #7f8c8d; margin: 10px 0 5px 0; font-weight: 600;'>{$title}</h5>";

}



function formatHtmlStat($label, $value, $color = '#2c3e50') {

    return "

    <div style='display: flex; justify-content: space-between; margin: 5px 0; padding: 5px 0; border-bottom: 1px dashed #ecf0f1;'>

        <span style='color: #7f8c8d;'>{$label}:</span>

        <span style='color: {$color}; font-weight: 600;'>{$value}</span>

    </div>";

}



function formatHtmlBulletList($items, $type = 'disc') {

    if (empty($items)) return '';

    $listStyle = $type === 'disc' ? 'list-style-type: disc;' : 'list-style-type: decimal;';

    $html = "<ul style='{$listStyle} margin: 10px 0 10px 20px; padding: 0;'>";

    foreach ($items as $item) {

        $html .= "<li style='margin: 5px 0; color: #2c3e50;'>{$item}</li>";

    }

    $html .= "</ul>";

    return $html;

}



function formatHtmlCard($title, $content, $color = '#3498db') {

    return "

    <div style='background: #f8f9fa; border-left: 4px solid {$color}; padding: 12px; margin: 10px 0; border-radius: 4px;'>

        <div style='font-weight: 600; color: {$color}; margin-bottom: 8px;'>{$title}</div>

        <div style='color: #2c3e50;'>{$content}</div>

    </div>";

}



function formatHtmlGrid($items, $columns = 2) {

    $html = "<div style='display: grid; grid-template-columns: repeat({$columns}, 1fr); gap: 10px; margin: 10px 0;'>";

    foreach ($items as $item) {

        $html .= "<div style='background: #f8f9fa; padding: 10px; border-radius: 4px; text-align: center;'>{$item}</div>";

    }

    $html .= "</div>";

    return $html;

}



function formatHtmlBadge($text, $type = 'info') {

    $colors = [

        'success' => ['#27ae60', '#d4edda'],

        'warning' => ['#f39c12', '#fff3cd'],

        'danger' => ['#e74c3c', '#f8d7da'],

        'info' => ['#3498db', '#d1ecf1']

    ];

    list($textColor, $bgColor) = $colors[$type] ?? $colors['info'];

    

    return "<span style='background: {$bgColor}; color: {$textColor}; padding: 3px 10px; border-radius: 20px; font-size: 12px; font-weight: 600; display: inline-block;'>{$text}</span>";

}



function formatHtmlProgress($percent, $label = '') {

    $color = $percent >= 70 ? '#27ae60' : ($percent >= 40 ? '#f39c12' : '#e74c3c');

    return "

    <div style='margin: 10px 0;'>

        " . ($label ? "<div style='display: flex; justify-content: space-between; margin-bottom: 5px;'><span>{$label}</span><span>{$percent}%</span></div>" : "") . "

        <div style='background: #ecf0f1; height: 20px; border-radius: 10px; overflow: hidden;'>

            <div style='background: {$color}; width: {$percent}%; height: 100%; text-align: center; color: white; font-size: 11px; line-height: 20px;'>{$percent}%</div>

        </div>

    </div>";

}



function formatHtmlTable($headers, $rows) {

    $html = "<table style='width: 100%; border-collapse: collapse; margin: 10px 0; font-size: 13px;'>";

    

    // Headers

    $html .= "<thead><tr style='background: #3498db; color: white;'>";

    foreach ($headers as $header) {

        $html .= "<th style='padding: 8px; text-align: left;'>{$header}</th>";

    }

    $html .= "</tr></thead>";

    

    // Rows

    $html .= "<tbody>";

    foreach ($rows as $row) {

        $html .= "<tr style='border-bottom: 1px solid #ecf0f1;'>";

        foreach ($row as $cell) {

            $html .= "<td style='padding: 8px;'>{$cell}</td>";

        }

        $html .= "</tr>";

    }

    $html .= "</tbody></table>";

    

    return $html;

}



function formatHtmlDivider() {

    return "<hr style='border: 0; border-top: 1px solid #ecf0f1; margin: 15px 0;'>";

}



function formatHtmlSection($title, $content) {

    return "

    <div style='margin: 15px 0;'>

        <div style='font-weight: 600; color: #2c3e50; margin-bottom: 8px;'>{$title}</div>

        <div style='padding-left: 10px; border-left: 3px solid #3498db;'>{$content}</div>

    </div>";

}

?>