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

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_resources . 'includes/specific_db_config.php';

$postdata = $_POST;

if (
    empty($postdata['institution_id']) ||
    empty($postdata['student_id']) ||
    empty($postdata['jwt_token']) ||
    empty($postdata['api_signature']) ||
    !isset($postdata['marks'])
) {
    errorreturn("All required fields are missing.");
}

validateApiSignature($postdata['api_signature']);

$jwt = authenticate_jwt($postdata['jwt_token']);

$user_id      = $jwt->data->id;
$username     = $jwt->data->username;
$workspace_id = $jwt->data->workspace_id;
$role         = $jwt->data->role;

$specific_db = validateUserAccess(
    $conn_rjllearning,
    $user_id,
    $role,
    $workspace_id,
    ["superadmin", "admin", "principal", "teacher"],
    null
);

if (!$specific_db) {
    errorreturn("Access denied.");
}

$conn_rjllearning_specific = dynamicDbConnection($specific_db);

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

function safeFloat($v)
{
    return is_numeric($v) ? (float)$v : 0;
}

function getGrade($obtained, $total)
{
    if ($total <= 0) return '';

    $percentage = ($obtained / $total) * 100;

    if ($percentage >= 91) return 'A1';
    if ($percentage >= 81) return 'A2';
    if ($percentage >= 71) return 'B1';
    if ($percentage >= 61) return 'B2';
    if ($percentage >= 51) return 'C1';
    if ($percentage >= 41) return 'C2';
    if ($percentage >= 33) return 'D';
    return 'E';
}

$ExecuteSqlMarksheet = $conn_rjllearning_specific->prepare("SELECT * FROM marksheet WHERE id = ?");
$ExecuteSqlMarksheet->execute([$marksheet_id]);

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

// Check if marksheet exists
if (!$rowmarksheet) {
    errorreturn("Marksheet does not exist.");
} else {
    // If it exists, fetch the required data
    $examination = $rowmarksheet['examination_name'];
    $templateid = $rowmarksheet['template_id'];
    $academicsession = $rowmarksheet['academic_session'];
    $createdon = $rowmarksheet['time_created'];
    $createdon = $rowmarksheet['date_created']; // Ensure proper date formatting
    $createdby = $rowmarksheet['added_by'];

    // Now you can use the fetched data as needed
}


/* ================== CHECK LOCK TABLE ================== */

$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 table
            $class      = $lock_row['class_id'];
            $section_id = $lock_row['section_id'];

            break;
        }
    }
}


$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.");
}

// If the template exists, extract the template name and other relevant data
$templateName = $ResultTemplate['template_name'];
$cols = $ResultTemplate['cols'];

// $cols = (int)$template['cols'];

// $data = array_fill(0, $cols, null);
$data = [];
for ($i = 1; $i <= $cols; $i++) {
    $data[$i] = null;
}

$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'];
}

// foreach ($postdata['marks'] as $i => $val) {
//     $data[$i] = is_numeric($val) ? safeFloat($val) : trim($val);
// }

foreach ($postdata['marks'] as $i => $val) {
    $idx = $i + 1; // keep same index
    $data[$idx] = is_numeric($val) ? safeFloat($val) : trim($val);
}

$rules = [
    // English
    5  => ['sum_of' => [1, 2, 3, 4]],

    // Hindi
    10 => ['sum_of' => [6, 7, 8, 9]],

    // Punjabi
    15 => ['sum_of' => [11, 12, 13, 14]],

    // Maths
    20 => ['sum_of' => [16, 17, 18, 19]],

    // EVS
    25 => ['sum_of' => [21, 22, 23, 24]],

    // Grand Total
    28 => ['sum_of' => [5, 10, 15, 20, 25]],

    // Percentage
    29 => ['percentage_of' => [[28], 500]],
];

foreach ($rules as $target => $rule) {

    if (isset($rule['sum_of'])) {
        $sum = 0;
        foreach ($rule['sum_of'] as $src) {
            $sum += safeFloat($data[$src] ?? 0);
        }
        $data[$target] = $sum;
    }

    if (isset($rule['percentage_of'])) {
        [$indices, $total] = $rule['percentage_of'];

        $obtained = 0;
        foreach ($indices as $idx) {
            $obtained += safeFloat($data[$idx] ?? 0);
        }

        $data[$target] = ($total > 0)
            ? round(($obtained / $total) * 100, 2)
            : 0;
    }
}

$data[30] = getGrade($data[28], 500);

$conn_rjllearning_specific->prepare("
    DELETE FROM student_marksheet_data
    WHERE institution_id=? AND student_id=? AND marksheet_id=?
")->execute([$institution_id, $student_id, $marksheet_id]);

// $json = json_encode(array_values($data), JSON_UNESCAPED_UNICODE);
$json = json_encode($data, JSON_UNESCAPED_UNICODE);
try {
    $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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
");

    // Execute the query with the provided parameters
    $insertSuccessful = $insert->execute([
        $institution_id,
        $marksheet_id,
        $student_id,
        $json,
        $branch,
        $academic_id,
        $class,
        $section_id,
        $username, // Added by the current user
        date("Y-m-d"), // Date created
        date("H:i:s"), // Time created
        '1' // Default status to 1 if not provided
    ]);

    $insertId = $conn_rjllearning_specific->lastInsertId();

    $analytics = marksheet_analytics_simple($json);

    $update = $conn_rjllearning_specific->prepare("
        UPDATE student_marksheet_data 
        SET analytics = ?
        WHERE id = ?
    ");
    $updateSuccessful = $update->execute([json_encode($analytics), $insertId]);

    if ($insertSuccessful) {
        successreturn("Data inserted successfully.");
    } else {
        errorreturn("Failed to insert data into marksheet_data.");
    }
} catch (PDOException $e) {
    errorreturn("Database error: " . $e->getMessage());
}

function marksheet_analytics_simple($json)
{
    $data = json_decode($json, true);

    $subjects = [
        'English' => 5,
        'Hindi' => 10,
        'Punjabi' => 15,
        'Maths' => 20,
        'EVS' => 25
    ];

    $subjectRankings = [];

    foreach ($subjects as $name => $index) {
        $marks = (float)($data[$index] ?? 0);

        $subjectRankings[] = [
            'subject' => $name,
            'marks' => $marks,
            // 'percentage' => round(($marks / 100) * 100, 2),
            'percentage' => round($marks, 2),
            'grade' => getGrade($marks, 100)
        ];
    }

    usort($subjectRankings, fn($a, $b) => $b['marks'] <=> $a['marks']);

    return [
        'total' => $data[28] ?? 0,
        'percentage' => $data[29] ?? 0,
        'grade' => $data[30] ?? '',
        'highest_subject' => $subjectRankings[0]['subject'] ?? null,
        'lowest_subject' => end($subjectRankings)['subject'] ?? null,
        'subject_rankings' => $subjectRankings
    ];
}
