<?php
ini_set('display_errors', 1);

// Include necessary files
require_once '../../base_path_include.php';
require_once $base_path_to_learning_resources . "includes/connect.php";
require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';
require_once $base_path_to_learning_resources . 'lib/validate_function.php';
require_once $base_path_to_learning . 'user_management/authenticate.php';
require_once $base_path_to_learning . 'admin_api/academic_session/functions/validate_faculty.php';
require_once $base_path_to_learning . 'admin_api/academic_session/functions/functions.php';
require_once $base_path_to_learning . 'functions/common_functions.php';
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';
require_once $base_path_to_learning . "vendor/autoload.php";
require_once $base_path_to_learning_resources . 'lib/bucket_functions/bucket_functions.php';
require_once $base_path_to_learning_resources . 'includes/aws_bucket_config.php';

$postdata = $_POST;

// Validate required fields
if (!isset($postdata['institution_id']) || !checkIfIssetAndNotEmpty($postdata['institution_id']) ||
    !isset($postdata['student_id']) || !checkIfIssetAndNotEmpty($postdata['student_id']) ||
    !isset($postdata['marksheet_id']) || !checkIfIssetAndNotEmpty($postdata['marksheet_id']) ||
    !isset($postdata['api_signature']) || !checkIfIssetAndNotEmpty($postdata['api_signature']) ||
    !isset($postdata['jwt_token']) || !checkIfIssetAndNotEmpty($postdata['jwt_token'])) {
    errorreturn("All * fields are required.");
}

validateApiSignature($postdata['api_signature']);
// Authenticate JWT token
$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);

// Extract user details from JWT token
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;

// Validate user access and get specific database name
$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id, ["superadmin", "admin", "principal", "teacher"], null);
if (!$specific_db_name) {
    errorreturn('Access denied.');
} else {
    $conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
}

$institution_id = $postdata['institution_id'];
$student_id = $postdata['student_id'];
$marksheet_id = $postdata['marksheet_id'];

// Fetch marksheet data
$ExecuteSqlMarksheet = $conn_rjllearning_specific->prepare("SELECT * FROM marksheet WHERE id = ?");
$ExecuteSqlMarksheet->execute([$marksheet_id]);
$rowmarksheet = $ExecuteSqlMarksheet->fetch(PDO::FETCH_ASSOC);

// Check if marksheet exists
if (!$rowmarksheet) {
    errorreturn("Marksheet does not exist.");
} else {
    $examination = $rowmarksheet['examination_name'];
    $templateid = $rowmarksheet['template_id'];
    $academicsession = $rowmarksheet['academic_session'];
    $createdon = $rowmarksheet['time_created'];
    $createdon = $rowmarksheet['date_created'];
    $createdby = $rowmarksheet['added_by'];
}

$ExecuteSqlTemplate = $conn_rjllearning->prepare("SELECT cols, template_name FROM template_info WHERE id = ?");
$ExecuteSqlTemplate->execute([$templateid]);
$ResultTemplate = $ExecuteSqlTemplate->fetch(PDO::FETCH_ASSOC);

if (!$ResultTemplate) {
    errorreturn("Marksheet Template does not exist.");
}

$templateName = $ResultTemplate['template_name'];
$cols = $ResultTemplate['cols'];

// Fetch student data
$ExecuteSqlStudent = $conn_rjllearning_specific->prepare("SELECT name, branch_id, class_id, academic_id, section_id FROM students WHERE id = ?");
$ExecuteSqlStudent->execute([$student_id]);
$rowstudent = $ExecuteSqlStudent->fetch(PDO::FETCH_ASSOC);

if (!$rowstudent) {
    errorreturn("Student does not exist.");
} else {
    $studentname = $rowstudent['name'];
    $branch = $rowstudent['branch_id'];
    $class = $rowstudent['class_id'];
    $academic_id = $rowstudent['academic_id'];
    $section_id = $rowstudent['section_id'];
}

$lock_sql = "SELECT class_id, section_id, student_list
             FROM lock_marksheet_cache
             WHERE institution_id = ?
             AND branch_id = ?
             AND marksheet_id = ?";

$lock_stmt = $conn_rjllearning_specific->prepare($lock_sql);
$lock_stmt->execute([
    $institution_id,
    $branch,
    $marksheet_id
]);

$lock_rows = $lock_stmt->fetchAll(PDO::FETCH_ASSOC);

if (!empty($lock_rows)) {

    foreach ($lock_rows as $lock_row) {

        $student_list = json_decode($lock_row['student_list'], true);

        if (!empty($student_list) && in_array((string)$student_id, $student_list)) {

            # Override class and section from lock snapshot
            $class = $lock_row['class_id'];
            $section_id = $lock_row['section_id'];

            break;
        }
    }
}
$array = array();
for ($i = 1; $i <= $cols; $i++) {
    $array[$i] = 0;
}

if (isset($postdata['1'])) {
    $array[1] = $postdata['1'];
}

$subjectPositions = [2, 8, 14, 20, 26, 32, 38, 44];

foreach ($subjectPositions as $i) {
    // Check if the subject name field is not empty
    if (!empty($postdata[strval($i)])) {
        // If the subject name exists, then store all related data
        $array[strval($i)] = $postdata[strval($i)];
        $array[strval($i + 1)] = $postdata[strval($i + 1)] ?? '';
        $array[strval($i + 2)] = $postdata[strval($i + 2)] ?? '';
        $array[strval($i + 3)] = $postdata[strval($i + 3)] ?? '';
        $array[strval($i + 4)] = $postdata[strval($i + 4)] ?? '';
        $array[strval($i + 5)] = $postdata[strval($i + 5)] ?? '';
    } else {
        $array[strval($i)] = '';
        $array[strval($i + 1)] = '';
        $array[strval($i + 2)] = '';
        $array[strval($i + 3)] = '';
        $array[strval($i + 4)] = '';
        $array[strval($i + 5)] = '';
    }
}

$array[50] = isset($postdata['50']) ? $postdata['50'] : '';
$array[51] = isset($postdata['51']) ? $postdata['51'] : '';
$array[52] = isset($postdata['52']) ? $postdata['52'] : 'na';
$array[53] = isset($postdata['53']) ? $postdata['53'] : '';

$analytics = marksheet_analytics_27($array);

$array[54] = $analytics['total_obtained'] ?? 0;
$array[55] = number_format($analytics['percentage'] ?? 0, 2, '.', '');
$array[56] = $analytics['grade'] ?? 'N/A';

// JSON encode the array
$json_encoded_array = json_encode($array);


$deleteExistingData = $conn_rjllearning_specific->prepare("
    DELETE FROM student_marksheet_data
    WHERE student_id = ? AND marksheet_id = ?
");

$deleteSuccessful = $deleteExistingData->execute([$student_id, $marksheet_id]);
if (!$deleteSuccessful) {
    errorreturn("Failed to delete existing data.");
}

try {
    $conn_rjllearning_specific->beginTransaction();
    
    $insert = $conn_rjllearning_specific->prepare("
        INSERT INTO student_marksheet_data (
            institution_id, marksheet_id, student_id, data,
            branch_id, academic_id, class_id, section_id,
            created_by, date_created, time_created, status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    
    $insert->execute([
        $institution_id, $marksheet_id, $student_id, $json_encoded_array,
        $branch, $academic_id, $class, $section_id,
        $username, date("Y-m-d"), date("H:i:s"), '1'
    ]);
    
    $insertId = $conn_rjllearning_specific->lastInsertId();
    
    $update = $conn_rjllearning_specific->prepare("
        UPDATE student_marksheet_data 
        SET analytics = ?
        WHERE id = ?
    ");
    $updateSuccessful = $update->execute([json_encode($analytics), $insertId]);
    
    $conn_rjllearning_specific->commit();
    
    if ($updateSuccessful) {
        successreturn("Data inserted successfully.");
    } else {
        errorreturn("Failed to insert data into marksheet_data.");
    }
    
} catch (PDOException $e) {
    $conn_rjllearning_specific->rollBack();
    errorreturn("Database error: " . $e->getMessage());
}

function marksheet_analytics_27($marksData)
{
    $subjects = [];
    $mainSubjects = []; 
    $totalObtained = 0;
    $totalMax = 0;
    
    $subjectTypeMap = [
        'M' => 'Mandatory',
        'O' => 'Optional', 
        'A' => 'Additional'
    ];
    
    $subjectPositions = [2, 8, 14, 20, 26, 32, 38, 44];
    
    foreach ($subjectPositions as $subjectIndex) {
        $subjectName = $marksData[$subjectIndex] ?? '';
        
        // Skip if subject name is empty
        if (empty($subjectName)) {
            continue;
        }
        
        $subjectTypeAbbr = $marksData[$subjectIndex + 1] ?? '';
        $subjectType = $subjectTypeMap[$subjectTypeAbbr] ?? 'Unknown';
        
        // Get theory marks
        $theoryTotal = (float)($marksData[$subjectIndex + 2] ?? 0);
        $theoryObtained = (float)($marksData[$subjectIndex + 3] ?? 0);
        
        // Get practical marks
        $practicalTotal = (float)($marksData[$subjectIndex + 4] ?? 0);
        $practicalObtained = (float)($marksData[$subjectIndex + 5] ?? 0);
        
        // Calculate subject totals
        $subjectTotalMarks = $theoryTotal + $practicalTotal;
        $subjectObtainedMarks = $theoryObtained + $practicalObtained;
        
        // Calculate subject percentage
        $subjectPercentage = $subjectTotalMarks > 0 ? round(($subjectObtainedMarks / $subjectTotalMarks) * 100, 2) : 0;
        
        // Calculate subject grade
        $subjectGrade = getGrade($subjectObtainedMarks, $subjectTotalMarks);
        
        $subjectData = [
            'name' => $subjectName,
            'type' => $subjectType,
            'type_abbr' => $subjectTypeAbbr,
            'theory_total' => $theoryTotal,
            'theory_obtained' => $theoryObtained,
            'practical_total' => $practicalTotal,
            'practical_obtained' => $practicalObtained,
            'total_marks' => $subjectTotalMarks,
            'obtained_marks' => $subjectObtainedMarks,
            'percentage' => $subjectPercentage,
            'grade' => $subjectGrade
        ];
        
        $subjects[] = $subjectData;
        
        if ($subjectTypeAbbr === 'M' || $subjectTypeAbbr === 'O') {
            $mainSubjects[] = $subjectData;
            $totalObtained += $subjectObtainedMarks;
            $totalMax += $subjectTotalMarks;
        }
    }
    
    $percentage = $totalMax > 0 ? round(($totalObtained / $totalMax) * 100, 2) : 0;

    $grade = getGrade($totalObtained, $totalMax);
    
    $highestSubject = 'N/A';
    $lowestSubject = 'N/A';
    $highestPercentage = 0;
    $lowestPercentage = 100;
    
    foreach ($subjects as $subject) {
        if ($subject['percentage'] > $highestPercentage) {
            $highestPercentage = $subject['percentage'];
            $highestSubject = $subject['name'];
        }
        if ($subject['percentage'] < $lowestPercentage && $subject['percentage'] > 0) {
            $lowestPercentage = $subject['percentage'];
            $lowestSubject = $subject['name'];
        }
    }
    
    $subjectRankings = [];
    foreach ($subjects as $subject) {
        $subjectRankings[] = [
            'subject' => $subject['name'],
            'total' => (int)$subject['obtained_marks'],
            'percentage' => $subject['percentage'],
            'grade' => $subject['grade'],
            'type' => $subject['type']
        ];
    }
    
    usort($subjectRankings, function ($a, $b) {
        return $b['percentage'] <=> $a['percentage'];
    });
    
    return [
        'total_obtained' => (int)$totalObtained,
        'total_max' => (int)$totalMax,
        'percentage' => $percentage,
        'grade' => $grade,
        'highest_marks_obtained_subject' => $highestSubject,
        'lowest_marks_obtained_subject' => $lowestSubject,
        'subject_rankings' => $subjectRankings
    ];
}

function getGrade($obtained, $total)
{
    if ($total <= 0) return 'N/A';
    
    $percentage = ($obtained / $total) * 100;
    
    if ($percentage >= 91) return 'A 1';
    if ($percentage >= 81) return 'A 2';
    if ($percentage >= 71) return 'B 1';
    if ($percentage >= 61) return 'B 2';
    if ($percentage >= 51) return 'C 1';
    if ($percentage >= 41) return 'C 2';
    if ($percentage >= 33) return 'D';
    return 'E';
}