<?php
session_start();
require_once 'config.php'; // Ensure this path is correct

header('Content-Type: application/json');

$response = [
    'success' => false,
    'message' => 'Invalid request.',
    'data' => []
];

if (isset($_GET['class_id']) && isset($_GET['stream']) && isset($_SESSION['school_id'])) {
    $class_id = (int)$_GET['class_id'];
    $stream_name_from_frontend = trim($_GET['stream']); // This is the stream NAME from JS
    $school_id = (int)$_SESSION['school_id'];

    // Ensure $conn is available
    if (!isset($conn) || !$conn instanceof mysqli) {
        $response['message'] = 'Database connection error.';
        echo json_encode($response);
        exit();
    }

    // Validate class_id belongs to the current school and get class_name
    $class_name = '';
    $sql_validate_class = "SELECT class_name FROM classes WHERE class_id = ? AND school_id = ?";
    if ($stmt_validate = $conn->prepare($sql_validate_class)) {
        $stmt_validate->bind_param("ii", $class_id, $school_id);
        $stmt_validate->execute();
        $stmt_validate->store_result();
        if ($stmt_validate->num_rows === 0) {
            $response['message'] = 'Unauthorized access or class not found.';
            echo json_encode($response);
            $stmt_validate->close();
            $conn->close();
            exit();
        }
        $stmt_validate->bind_result($class_name_db);
        $stmt_validate->fetch();
        $class_name = $class_name_db;
        $stmt_validate->close();
    } else {
        $response['message'] = 'Database error during class validation: ' . $conn->error;
        echo json_encode($response);
        $conn->close();
        exit();
    }

    // --- NEW: Get stream_id from stream_name ---
    $stream_id = null;
    if (!empty($stream_name_from_frontend)) {
        $sql_get_stream_id = "SELECT stream_id FROM streams WHERE class_id = ? AND school_id = ? AND stream_name = ?";
        if ($stmt_stream_id = $conn->prepare($sql_get_stream_id)) {
            $stmt_stream_id->bind_param("iis", $class_id, $school_id, $stream_name_from_frontend);
            $stmt_stream_id->execute();
            $stmt_stream_id->bind_result($fetched_stream_id);
            if ($stmt_stream_id->fetch()) {
                $stream_id = $fetched_stream_id;
            }
            $stmt_stream_id->close();
        } else {
            $response['message'] = 'Database error (prepare get stream ID): ' . $conn->error;
            echo json_encode($response);
            $conn->close();
            exit();
        }
    }

    // If stream name was provided but no ID found, it's an invalid stream for this class/school
    if (!empty($stream_name_from_frontend) && $stream_id === null) {
        $response['message'] = 'Stream not found for this class.';
        echo json_encode($response);
        $conn->close();
        exit();
    }
    // --- END NEW ---

    // Fetch subjects assigned to this class and their assigned teachers for the specific stream
    // IMPORTANT: Changed 'stream' column references to 'stream_id'
    $sql = "SELECT cs.subject_id, s.subject_name,
                    (SELECT COUNT(st.user_id) FROM students st
                     WHERE st.class_id = ? AND st.school_id = ?
                       AND (st.stream_id = ? OR (? IS NULL AND st.stream_id IS NULL)) -- Use stream_id for students, handle NULL stream_id if no stream selected
                    ) AS student_count_in_stream,
                    cst1.teacher_user_id AS teacher1_id, u1.username AS teacher1_name,
                    cst2.teacher_user_id AS teacher2_id, u2.username AS teacher2_name,
                    cst3.teacher_user_id AS teacher3_id, u3.username AS teacher3_name
            FROM class_subjects cs
            JOIN subjects s ON cs.subject_id = s.subject_id
            LEFT JOIN class_subject_teachers cst1 ON cs.class_id = cst1.class_id AND cs.subject_id = cst1.subject_id AND cst1.stream_id = ? AND cst1.assignment_slot = 1
            LEFT JOIN users u1 ON cst1.teacher_user_id = u1.id
            LEFT JOIN class_subject_teachers cst2 ON cs.class_id = cst2.class_id AND cs.subject_id = cst2.subject_id AND cst2.stream_id = ? AND cst2.assignment_slot = 2
            LEFT JOIN users u2 ON cst2.teacher_user_id = u2.id
            LEFT JOIN class_subject_teachers cst3 ON cs.class_id = cst3.class_id AND cs.subject_id = cst3.subject_id AND cst3.stream_id = ? AND cst3.assignment_slot = 3
            LEFT JOIN users u3 ON cst3.teacher_user_id = u3.id
            WHERE cs.class_id = ? AND s.school_id = ?
            ORDER BY s.subject_name ASC";

    if ($stmt = $conn->prepare($sql)) {
        // Bind parameters:
        // 1. $class_id for st.class_id (int)
        // 2. $school_id for st.school_id (int)
        // 3. $stream_id for st.stream_id (int) - For students in stream
        // 4. $stream_id for (st.stream_id IS NULL AND ? IS NULL) (int or NULL) - For students in no stream
        // 5. $stream_id for cst1.stream_id (int or NULL)
        // 6. $stream_id for cst2.stream_id (int or NULL)
        // 7. $stream_id for cst3.stream_id (int or NULL)
        // 8. $class_id for cs.class_id (int)
        // 9. $school_id for s.school_id (int)

        // The 'stream_id' could be null if no stream is selected (e.g., for "General" class sections)
        // In this case, bind_param 'i' might not correctly handle NULL directly.
        // A common workaround is to use 's' for NULLable integers if you're passing null explicitly,
        // but it's cleaner to handle it directly in the SQL using IS NULL.
        // PHP's bind_param 'i' handles null for PDO, but not always for mysqli::bind_param in older versions.
        // To be safe, we pass $stream_id directly.
        $stmt->bind_param("iiiisii", $class_id, $school_id, $stream_id, $stream_id, $stream_id, $stream_id, $stream_id, $class_id, $school_id);

        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $subjects_data = [];
            while ($row = $result->fetch_assoc()) {
                $row['student_count'] = $row['student_count_in_stream'];
                unset($row['student_count_in_stream']);
                $subjects_data[] = $row;
            }
            $response['success'] = true;
            $response['message'] = 'Subjects and assigned teachers fetched successfully.';
            $response['data'] = $subjects_data;
        } else {
            $response['message'] = 'Error fetching subjects and teachers: ' . $stmt->error;
        }
        $stmt->close();
    } else {
        $response['message'] = 'Database error (prepare fetch subjects): ' . $conn->error;
    }

    $conn->close();
}


echo json_encode($response);
?>