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

// Function to check user role and return JSON error if unauthorized
function check_api_access($required_role_code) {
    if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true || !isset($_SESSION["school_id"])) {
        echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
        exit;
    }

    if ($_SESSION["role"] !== $required_role_code) {
        echo json_encode(['success' => false, 'message' => 'Insufficient permissions']);
        exit;
    }
}

// Check for Admin role
check_api_access(ROLE_ADMIN);

// Get current user and school details from session
$current_user_school_id = $_SESSION["school_id"];

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

// Get action from request
$action = $_GET['action'] ?? '';

// Handle different API actions
switch ($action) {
    case 'get_class_subjects':
        $class_id = intval($_GET['class_id'] ?? 0);
        
        if ($class_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid class ID']);
            exit;
        }

        $sql = "SELECT s.subject_id, s.subject_name, s.subject_code, s.subject_category 
                FROM class_subjects cs 
                JOIN subjects s ON cs.subject_id = s.subject_id 
                JOIN classes c ON cs.class_id = c.class_id
                WHERE cs.class_id = ? AND c.school_id = ?
                ORDER BY s.subject_category, s.subject_name";
        
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("ii", $class_id, $current_user_school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            $subjects = [];
            while ($row = $result->fetch_assoc()) {
                $subjects[] = $row;
            }
            $stmt->close();
            echo json_encode(['success' => true, 'subjects' => $subjects]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error']);
        }
        break;

    case 'get_class_streams':
        $class_id = intval($_GET['class_id'] ?? 0);
        
        if ($class_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid class ID']);
            exit;
        }

        $sql = "SELECT s.stream_id, s.stream_name 
                FROM streams s 
                JOIN classes c ON s.class_id = c.class_id
                WHERE s.class_id = ? AND c.school_id = ?
                ORDER BY s.stream_name";
        
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("ii", $class_id, $current_user_school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            $streams = [];
            while ($row = $result->fetch_assoc()) {
                $streams[] = $row;
            }
            $stmt->close();
            echo json_encode(['success' => true, 'streams' => $streams]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error']);
        }
        break;

    case 'get_subject_teachers':
        $class_id = intval($_GET['class_id'] ?? 0);
        $stream_id = intval($_GET['stream_id'] ?? 0);
        
        if ($class_id <= 0 || $stream_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid parameters']);
            exit;
        }

        // Get current academic year and term
        $current_academic_year = date('Y');
        $current_term = "Term 1";

        $sql_academic_term = "SELECT academic_year, term_name FROM academic_years_terms WHERE school_id = ? ORDER BY id DESC LIMIT 1";
        if ($stmt_academic_term = $conn->prepare($sql_academic_term)) {
            $stmt_academic_term->bind_param("i", $current_user_school_id);
            $stmt_academic_term->execute();
            $stmt_academic_term->bind_result($fetched_year, $fetched_term);
            if ($stmt_academic_term->fetch()) {
                $current_academic_year = $fetched_year;
                $current_term = $fetched_term;
            }
            $stmt_academic_term->close();
        }

        $sql = "SELECT cst.subject_id, cst.teacher_user_id, cst.assignment_slot 
                FROM class_subject_teachers cst
                JOIN classes c ON cst.class_id = c.class_id
                WHERE cst.class_id = ? AND cst.stream_id = ? 
                AND cst.academic_year = ? AND cst.term = ? 
                AND c.school_id = ?
                ORDER BY cst.subject_id, cst.assignment_slot";
        
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("iissi", $class_id, $stream_id, $current_academic_year, $current_term, $current_user_school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            $assignments = [];
            while ($row = $result->fetch_assoc()) {
                $assignments[] = $row;
            }
            $stmt->close();
            echo json_encode(['success' => true, 'assignments' => $assignments]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error']);
        }
        break;

    case 'get_stream_subject_student_count':
        $class_id = intval($_GET['class_id'] ?? 0);
        $stream_id = intval($_GET['stream_id'] ?? 0);
        $subject_id = intval($_GET['subject_id'] ?? 0);
        
        if ($class_id <= 0 || $stream_id <= 0 || $subject_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid parameters']);
            exit;
        }

        // Get current academic year and term
        $current_academic_year = date('Y');
        $current_term = "Term 1";

        $sql_academic_term = "SELECT academic_year, term_name FROM academic_years_terms WHERE school_id = ? ORDER BY id DESC LIMIT 1";
        if ($stmt_academic_term = $conn->prepare($sql_academic_term)) {
            $stmt_academic_term->bind_param("i", $current_user_school_id);
            $stmt_academic_term->execute();
            $stmt_academic_term->bind_result($fetched_year, $fetched_term);
            if ($stmt_academic_term->fetch()) {
                $current_academic_year = $fetched_year;
                $current_term = $fetched_term;
            }
            $stmt_academic_term->close();
        }

        // First, get the class name and stream name
        $sql_class_stream = "SELECT c.class_name, s.stream_name 
                             FROM classes c 
                             JOIN streams s ON c.class_id = s.class_id 
                             WHERE c.class_id = ? AND s.stream_id = ? AND c.school_id = ?";
        
        $class_name = '';
        $stream_name = '';
        
        if ($stmt = $conn->prepare($sql_class_stream)) {
            $stmt->bind_param("iii", $class_id, $stream_id, $current_user_school_id);
            $stmt->execute();
            $stmt->bind_result($class_name, $stream_name);
            $stmt->fetch();
            $stmt->close();
        }

        // Check if subject is compulsory
        $sql_subject = "SELECT subject_category FROM subjects WHERE subject_id = ? AND school_id = ?";
        $subject_category = 'Optional';
        
        if ($stmt = $conn->prepare($sql_subject)) {
            $stmt->bind_param("ii", $subject_id, $current_user_school_id);
            $stmt->execute();
            $stmt->bind_result($subject_category);
            $stmt->fetch();
            $stmt->close();
        }

        if ($subject_category === 'Compulsory') {
            // For compulsory subjects, count all students in the stream from users table
            $sql = "SELECT COUNT(*) as count 
                    FROM users u
                    WHERE u.role = 3 
                    AND u.class_id = ? 
                    AND u.stream_id = ? 
                    AND u.school_id = ?";
            
            if ($stmt = $conn->prepare($sql)) {
                $stmt->bind_param("iii", $class_id, $stream_id, $current_user_school_id);
                $stmt->execute();
                $stmt->bind_result($count);
                $stmt->fetch();
                $stmt->close();
                echo json_encode(['success' => true, 'count' => $count]);
            } else {
                echo json_encode(['success' => false, 'message' => 'Database error']);
            }
        } else {
            // For optional/elective subjects, count students who chose this subject
            $sql = "SELECT COUNT(*) as count 
                    FROM student_subject_choices 
                    WHERE subject_id = ? AND class_id = ? AND stream_id = ? 
                    AND academic_year = ? AND term = ? AND school_id = ?";
            
            if ($stmt = $conn->prepare($sql)) {
                $stmt->bind_param("iiissi", $subject_id, $class_id, $stream_id, $current_academic_year, $current_term, $current_user_school_id);
                $stmt->execute();
                $stmt->bind_result($count);
                $stmt->fetch();
                $stmt->close();
                echo json_encode(['success' => true, 'count' => $count]);
            } else {
                echo json_encode(['success' => false, 'message' => 'Database error']);
            }
        }
        break;

    case 'get_stream_students':
        $class_id = intval($_GET['class_id'] ?? 0);
        $stream_id = intval($_GET['stream_id'] ?? 0);
        
        if ($class_id <= 0 || $stream_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid parameters']);
            exit;
        }

        // Get students from users table where role = 3 (student)
        // Users table has: username (full name), ADM, class_id, stream_id
        $sql = "SELECT u.ADM, u.username, u.phone_number
                FROM users u
                WHERE u.role = 3 
                AND u.class_id = ? 
                AND u.stream_id = ? 
                AND u.school_id = ?
                ORDER BY u.username";
        
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("iii", $class_id, $stream_id, $current_user_school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            $students = [];
            while ($row = $result->fetch_assoc()) {
                // Adapt to expected format: split username into surname and other_names
                $name_parts = explode(' ', $row['username'], 2);
                $students[] = [
                    'ADM' => $row['ADM'],
                    'surname' => $name_parts[0] ?? '',
                    'other_names' => $name_parts[1] ?? '',
                    'gender' => 'N/A' // Gender not in users table, set default
                ];
            }
            $stmt->close();
            echo json_encode(['success' => true, 'students' => $students]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error']);
        }
        break;

    case 'get_student_subject_choices':
        $student_adm = $_GET['student_adm'] ?? '';
        
        if (empty($student_adm)) {
            echo json_encode(['success' => false, 'message' => 'Invalid student ADM']);
            exit;
        }

        // Get current academic year and term
        $current_academic_year = date('Y');
        $current_term = "Term 1";

        $sql_academic_term = "SELECT academic_year, term_name FROM academic_years_terms WHERE school_id = ? ORDER BY id DESC LIMIT 1";
        if ($stmt_academic_term = $conn->prepare($sql_academic_term)) {
            $stmt_academic_term->bind_param("i", $current_user_school_id);
            $stmt_academic_term->execute();
            $stmt_academic_term->bind_result($fetched_year, $fetched_term);
            if ($stmt_academic_term->fetch()) {
                $current_academic_year = $fetched_year;
                $current_term = $fetched_term;
            }
            $stmt_academic_term->close();
        }

        $sql = "SELECT ssc.subject_id, s.subject_name, s.subject_code, s.subject_category
                FROM student_subject_choices ssc
                JOIN subjects s ON ssc.subject_id = s.subject_id
                WHERE ssc.student_ADM = ? AND ssc.academic_year = ? AND ssc.term = ? AND ssc.school_id = ?
                ORDER BY s.subject_name";
        
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("sssi", $student_adm, $current_academic_year, $current_term, $current_user_school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            $choices = [];
            while ($row = $result->fetch_assoc()) {
                $choices[] = $row;
            }
            $stmt->close();
            echo json_encode(['success' => true, 'choices' => $choices]);
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error']);
        }
        break;

    case 'save_student_subject_choice':
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            echo json_encode(['success' => false, 'message' => 'Invalid request method']);
            exit;
        }

        $input = json_decode(file_get_contents('php://input'), true);
        
        $student_adm = $input['student_adm'] ?? '';
        $subject_id = intval($input['subject_id'] ?? 0);
        $class_id = intval($input['class_id'] ?? 0);
        $stream_id = intval($input['stream_id'] ?? 0);
        
        if (empty($student_adm) || $subject_id <= 0 || $class_id <= 0 || $stream_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid parameters']);
            exit;
        }

        // Get current academic year and term
        $current_academic_year = date('Y');
        $current_term = "Term 1";

        $sql_academic_term = "SELECT academic_year, term_name FROM academic_years_terms WHERE school_id = ? ORDER BY id DESC LIMIT 1";
        if ($stmt_academic_term = $conn->prepare($sql_academic_term)) {
            $stmt_academic_term->bind_param("i", $current_user_school_id);
            $stmt_academic_term->execute();
            $stmt_academic_term->bind_result($fetched_year, $fetched_term);
            if ($stmt_academic_term->fetch()) {
                $current_academic_year = $fetched_year;
                $current_term = $fetched_term;
            }
            $stmt_academic_term->close();
        }

        // Check if the subject is optional or elective (not compulsory)
        $sql_subject = "SELECT subject_category FROM subjects WHERE subject_id = ? AND school_id = ?";
        $subject_category = '';
        
        if ($stmt = $conn->prepare($sql_subject)) {
            $stmt->bind_param("ii", $subject_id, $current_user_school_id);
            $stmt->execute();
            $stmt->bind_result($subject_category);
            $stmt->fetch();
            $stmt->close();
        }

        if ($subject_category === 'Compulsory') {
            echo json_encode(['success' => false, 'message' => 'Cannot manually assign compulsory subjects']);
            exit;
        }

        // Check how many optional subjects the student has already chosen
        $sql_count = "SELECT COUNT(*) as count FROM student_subject_choices 
                      WHERE student_ADM = ? AND academic_year = ? AND term = ? AND school_id = ?";
        
        $current_count = 0;
        if ($stmt = $conn->prepare($sql_count)) {
            $stmt->bind_param("sssi", $student_adm, $current_academic_year, $current_term, $current_user_school_id);
            $stmt->execute();
            $stmt->bind_result($current_count);
            $stmt->fetch();
            $stmt->close();
        }

        if ($current_count >= 2) {
            echo json_encode(['success' => false, 'message' => 'Student has already selected maximum (2) optional subjects']);
            exit;
        }

        // Insert the subject choice
        $sql = "INSERT INTO student_subject_choices (student_ADM, subject_id, class_id, stream_id, school_id, academic_year, term) 
                VALUES (?, ?, ?, ?, ?, ?, ?)";
        
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("siiisss", $student_adm, $subject_id, $class_id, $stream_id, $current_user_school_id, $current_academic_year, $current_term);
            
            if ($stmt->execute()) {
                $stmt->close();
                echo json_encode(['success' => true, 'message' => 'Subject assigned successfully']);
            } else {
                if ($conn->errno == 1062) {
                    echo json_encode(['success' => false, 'message' => 'Student has already selected this subject']);
                } else {
                    echo json_encode(['success' => false, 'message' => 'Database error: ' . $stmt->error]);
                }
                $stmt->close();
            }
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error']);
        }
        break;

    case 'remove_student_subject_choice':
        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            echo json_encode(['success' => false, 'message' => 'Invalid request method']);
            exit;
        }

        $input = json_decode(file_get_contents('php://input'), true);
        
        $student_adm = $input['student_adm'] ?? '';
        $subject_id = intval($input['subject_id'] ?? 0);
        
        if (empty($student_adm) || $subject_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Invalid parameters']);
            exit;
        }

        // Get current academic year and term
        $current_academic_year = date('Y');
        $current_term = "Term 1";

        $sql_academic_term = "SELECT academic_year, term_name FROM academic_years_terms WHERE school_id = ? ORDER BY id DESC LIMIT 1";
        if ($stmt_academic_term = $conn->prepare($sql_academic_term)) {
            $stmt_academic_term->bind_param("i", $current_user_school_id);
            $stmt_academic_term->execute();
            $stmt_academic_term->bind_result($fetched_year, $fetched_term);
            if ($stmt_academic_term->fetch()) {
                $current_academic_year = $fetched_year;
                $current_term = $fetched_term;
            }
            $stmt_academic_term->close();
        }

        $sql = "DELETE FROM student_subject_choices 
                WHERE student_ADM = ? AND subject_id = ? AND academic_year = ? AND term = ? AND school_id = ?";
        
        if ($stmt = $conn->prepare($sql)) {
            $stmt->bind_param("sissi", $student_adm, $subject_id, $current_academic_year, $current_term, $current_user_school_id);
            
            if ($stmt->execute()) {
                if ($stmt->affected_rows > 0) {
                    echo json_encode(['success' => true, 'message' => 'Subject removed successfully']);
                } else {
                    echo json_encode(['success' => false, 'message' => 'Subject choice not found']);
                }
                $stmt->close();
            } else {
                echo json_encode(['success' => false, 'message' => 'Database error: ' . $stmt->error]);
                $stmt->close();
            }
        } else {
            echo json_encode(['success' => false, 'message' => 'Database error']);
        }
        break;

    default:
        echo json_encode(['success' => false, 'message' => 'Invalid action']);
        break;
}

$conn->close();
?>
