<?php

function getMarksheetTemplate($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $class_id, $section_id, $student_id) {
    // Step 1: Fetch marksheet template settings for the institution
    $SqlTemplate = "SELECT * FROM marksheet_info WHERE institution_id = ? AND status = ?";
    $StmtTemplate = $conn_rjllearning_specific->prepare($SqlTemplate);
    $StmtTemplate->execute([$institution_id, 1]);
    $result = $StmtTemplate->fetchAll();

    if (count($result) < 1) {
        errorreturn("Template not found. Contact admin.");
    }

    $marksheet_id = $result[0]['marksheet_id'];

    // Step 2: Fetch student marksheet template data for the selected template
    $SqlStudentTemplate = "SELECT * FROM student_marksheet_data WHERE institution_id = ? AND marksheet_id = ? AND status = ?";
    $array_params = [$institution_id, $marksheet_id, 1];

    // Handle class and section filters
    if ($class_id != 'all') {
        $SqlStudentTemplate .= " AND class_id = ?";
        $array_params[] = $class_id;
    }
    if ($section_id != 'all') {
        $SqlStudentTemplate .= " AND section_id = ?";
        $array_params[] = $section_id;
    }

    $StmtStudentTemplate = $conn_rjllearning_specific->prepare($SqlStudentTemplate);
    $StmtStudentTemplate->execute($array_params);
    $StudentResult = $StmtStudentTemplate->fetchAll();

    if (count($StudentResult) < 1) {
        errorreturn("Template not found for this class or section. Contact admin.");
    }

    // Step 3: Fetch students based on the given criteria (institution, branch, class, section, student)
    $SqlStudents = "SELECT * FROM students WHERE institution_id = ? AND branch_id = ? AND status = ?";
    $array_params = [$institution_id, $branch_id, 1];

    if ($class_id != 'all') {
        $SqlStudents .= " AND class_id = ?";
        $array_params[] = $class_id;
    }
    if ($section_id != 'all') {
        $SqlStudents .= " AND section_id = ?";
        $array_params[] = $section_id;
    }
    if ($student_id != 'all') {
        $SqlStudents .= " AND user_id = ?";
        $array_params[] = $student_id;
    }

    $StmtStudents = $conn_rjllearning_specific->prepare($SqlStudents);
    $StmtStudents->execute($array_params);
    $StudentsResult = $StmtStudents->fetchAll(PDO::FETCH_ASSOC);

    $student_data_array = [];

    // Step 4: Process each student and filter the data according to the template
    foreach ($StudentsResult as $student_rows) {
        $filtered_student_data = [];

        foreach ($student_rows as $key => $value) {
            // Handle specific fields like 'student_profile_image', 'class_id', 'section_id'
            if ($key === 'student_profile_image') {
                if (!empty($student_rows['student_profile_image'])) {
                    $data_attachment = json_decode($student_rows['student_profile_image'], true);
                    $filePath = $data_attachment['filepath'];
                    $filtered_student_data['student_profile_image'] = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                }
            } elseif ($key === 'class_id') {
                $filtered_student_data['class'] = getClass($conn_rjllearning_specific, $institution_id, $student_rows['class_id']);
            } elseif ($key === 'section_id') {
                $filtered_student_data['section'] = getSection($conn_rjllearning_specific, $institution_id, $student_rows['section_id']);
            } elseif ($key === 'address_line_1') {
                // Convert the address to valid UTF-8
                $address = mb_convert_encoding($student_rows['address_line_1'], 'UTF-8', 'UTF-8');
                $address = str_replace("?", " ", $address);
                $filtered_student_data['address_line_1'] = $address;
            } else {
                $filtered_student_data[$key] = $value;
            }
        }

        // Add filtered data to the result array if not empty
        if (!empty($filtered_student_data)) {
            $student_data_array[] = $filtered_student_data;
        }
    }

    return $student_data_array;
}

function getStudentMarksheet_Details($conn_rjllearning_specific, $student_id, $class_id, $section_id, $institution_id, $branch_id)
{
    try {
        $sql = "
            SELECT 
                smd.id,
                smd.institution_id,
                smd.branch_id,
                smd.academic_id,
                smd.student_id,
                smd.class_id,
                smd.section_id,
                smd.data AS marks_data,
                s.name AS student_name,
                s.father_name,
                s.mother_name,
                s.admission_number
            FROM 
                student_marksheet_data smd
            INNER JOIN 
                students s ON smd.student_id = s.id 
            WHERE 
                smd.student_id = ? 
                AND smd.class_id = ? 
                AND smd.section_id = ? 
                AND smd.institution_id = ? 
                AND smd.branch_id = ?;
        ";

        // Prepare and execute the statement
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$student_id, $class_id, $section_id, $institution_id, $branch_id]);

        // Fetch the result
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        // If no result found
        if (!$result) {
            return "No data found for the given student and criteria.";
        }

        // Decode the marks data (assuming it's stored as JSON in the 'data' column)
        $result['marks_data'] = json_decode($result['marks_data'], true);

        // Return the fetched result
        return $result;
    } catch (PDOException $e) {
        return "Error: " . $e->getMessage();
    }
}


// Example usage:

