<?php
session_start();
require_once '../config/database.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized']);
    exit();
}

// Get parameters
$class_id = $_GET['class_id'] ?? '';
$session = $_GET['session'] ?? '';
$search_text = $_GET['search_text'] ?? '';
$search_by = $_GET['search_by'] ?? 'name';

try {
    // Build SQL query
    $sql = "
        SELECT s.id, s.student_id, s.first_name, s.last_name, s.roll_number, 
               s.phone, s.guardian_phone as parent_phone, s.session,
               c.name as class_name, c.section as class_section
        FROM students s
        JOIN classes c ON s.class_id = c.id
        WHERE s.status = 'active'
    ";
    $params = [];
    
    // Add filters
    if (!empty($class_id)) {
        $sql .= " AND s.class_id = ?";
        $params[] = $class_id;
    }
    
    if (!empty($session)) {
        $sql .= " AND s.session = ?";
        $params[] = $session;
    }
    
    if (!empty($search_text)) {
        switch ($search_by) {
            case 'name':
                $sql .= " AND (s.first_name LIKE ? OR s.last_name LIKE ? OR CONCAT(s.first_name, ' ', s.last_name) LIKE ?)";
                $params[] = "%$search_text%";
                $params[] = "%$search_text%";
                $params[] = "%$search_text%";
                break;
            case 'roll':
                $sql .= " AND s.roll_number LIKE ?";
                $params[] = "%$search_text%";
                break;
            case 'student_id':
                $sql .= " AND s.student_id LIKE ?";
                $params[] = "%$search_text%";
                break;
            case 'phone':
                $sql .= " AND (s.phone LIKE ? OR s.guardian_phone LIKE ?)";
                $params[] = "%$search_text%";
                $params[] = "%$search_text%";
                break;
        }
    }
    
    $sql .= " ORDER BY s.class_id, s.roll_number LIMIT 100";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Add additional info for each student (optional, skip if error)
    foreach ($students as &$student) {
        try {
            // Get fee count
            $fee_stmt = $pdo->prepare("SELECT COUNT(*) FROM fees WHERE student_id = ?");
            $fee_stmt->execute([$student['id']]);
            $student['fee_count'] = $fee_stmt->fetchColumn() ?: 0;
            
            // Get total due amount
            $due_stmt = $pdo->prepare("SELECT SUM(amount - paid_amount) FROM fees WHERE student_id = ? AND status != 'paid'");
            $due_stmt->execute([$student['id']]);
            $student['total_due'] = $due_stmt->fetchColumn() ?: 0;
        } catch (Exception $e) {
            // Skip additional info if error
            $student['fee_count'] = 0;
            $student['total_due'] = 0;
        }
    }
    
    // Return JSON response
    header('Content-Type: application/json');
    echo json_encode($students);
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>