<?php

// function cacheAcademicStudentData(
//     $conn_rjllearning_specific, 
//     $institution_id, 
//     $branch_id, 
//     $class_id, 
//     $section_id, 
//     $student_id, 
//     $date_of_admission = null
// ) {
//     try {
//         // Step 1: Get the earliest record of the student
//         $sqlCheck = "
//             SELECT id, branch_id, class_id, section_id, date 
//             FROM cache_academic_students_data 
//             WHERE student_id = ? 
//             ORDER BY date ASC LIMIT 1";
//         $stmtCheck = $conn_rjllearning_specific->prepare($sqlCheck);
//         $stmtCheck->execute([$student_id]);
//         $earliestRecord = $stmtCheck->fetch(PDO::FETCH_ASSOC);

//         if ($earliestRecord) {
//             if ($date_of_admission !== null) {
//                 // If the date_of_admission is earlier than the earliest record's date, update the date
//                 if ($date_of_admission < $earliestRecord['date']) {
//                     $sqlUpdate = "
//                         UPDATE cache_academic_students_data 
//                         SET institution_id = ?, date = ? 
//                         WHERE id = ?";
//                     $stmtUpdate = $conn_rjllearning_specific->prepare($sqlUpdate);
//                     $stmtUpdate->execute([
//                         $institution_id,
//                         $date_of_admission,
//                         $earliestRecord['id']
//                     ]);
//                 } else {
//                     // Perform checks on related tables only if a date_of_admission is provided
//                     $tablesToCheck = [
//                         'student_attendance_daywise' => 'DATE > ?',
//                         'book_issues' => 'due_date > ?',
//                         'apply_leaves' => 'end_date > ?',
//                         'visitors_log' => 'date > ?',
//                         'gate_pass' => 'req_date > ?',
//                         'student_marksheet_data' => 'date_created > ?'
//                     ];

//                     foreach ($tablesToCheck as $table => $condition) {
//                         $sqlCheckTable = "SELECT COUNT(*) as count FROM $table WHERE institution_id = ? AND student_id = ? AND $condition";
//                         $stmtCheckTable = $conn_rjllearning_specific->prepare($sqlCheckTable);
//                         $stmtCheckTable->execute([$institution_id, $student_id, $date_of_admission]);
//                         $row = $stmtCheckTable->fetch();
//                         if ($row && $row['count'] > 0) {
//                             errorreturn("Record exists in $table after the specified date. Kindly choose an appropriate date.");
//                             return;
//                         }
//                     }

//                     // Check the next record's date
//                     $sqlCheckNextRow = "
//                         SELECT date FROM cache_academic_students_data 
//                         WHERE student_id = ? AND date > ? 
//                         ORDER BY date ASC LIMIT 1";
//                     $stmtCheckNextRow = $conn_rjllearning_specific->prepare($sqlCheckNextRow);
//                     $stmtCheckNextRow->execute([$student_id, $date_of_admission]);
//                     $nextRow = $stmtCheckNextRow->fetch(PDO::FETCH_ASSOC);

//                     if ($nextRow && $nextRow['date'] <= $date_of_admission) {
//                         errorreturn("The new date of admission cannot exceed the next record's date.");
//                         return;
//                     }
//                 }
//             }

//             // Update the class, section, and branch for the existing record if they have changed
//             if (
//                 $earliestRecord['class_id'] != $class_id || 
//                 $earliestRecord['section_id'] != $section_id || 
//                 $earliestRecord['branch_id'] != $branch_id
//             ) {
//                 $sqlUpdate = "
//                     UPDATE cache_academic_students_data 
//                     SET institution_id = ?, branch_id = ?, class_id = ?, section_id = ? 
//                     WHERE id = ?";
//                 $stmtUpdate = $conn_rjllearning_specific->prepare($sqlUpdate);
//                 $stmtUpdate->execute([
//                     $institution_id,
//                     $branch_id,
//                     $class_id,
//                     $section_id,
//                     $earliestRecord['id']
//                 ]);
//             }
//         } else {
//             // Step 2: If no record exists, insert a new row
//             $sqlInsert = "
//                 INSERT INTO cache_academic_students_data (institution_id, branch_id, date, class_id, section_id, student_id) 
//                 VALUES (?, ?, ?, ?, ?, ?)";
//             $stmtInsert = $conn_rjllearning_specific->prepare($sqlInsert);
//             $stmtInsert->execute([
//                 $institution_id,
//                 $branch_id,
//                 $date_of_admission ?? date('Y-m-d'), // Use the current date if no date_of_admission is provided
//                 $class_id,
//                 $section_id,
//                 $student_id
//             ]);
//         }
//     } catch (PDOException $e) {
//         // Handle any errors
//         errorreturn("Error processing student ID $student_id: " . $e->getMessage());
//     }
// }


function cacheAcademicStudentData(
    $conn_rjllearning_specific, 
    $institution_id, 
    $branch_id, 
    $class_id, 
    $section_id, 
    $student_id, 
    $date_of_admission = null
) {
    try {
        // Step 1: Get the earliest record of the student
        $sqlCheck = "
            SELECT id, branch_id, class_id, section_id, date 
            FROM cache_academic_students_data 
            WHERE student_id = ? 
            ORDER BY date ASC LIMIT 1";
        $stmtCheck = $conn_rjllearning_specific->prepare($sqlCheck);
        $stmtCheck->execute([$student_id]);
        $earliestRecord = $stmtCheck->fetch(PDO::FETCH_ASSOC);

        if ($earliestRecord) {
            if ($date_of_admission !== null) {
                // Check if the date_of_admission is the same as the existing record
                if ($earliestRecord['date'] == $date_of_admission) {
                    // Update the record if class, section, or branch is different
                    if (
                        $earliestRecord['class_id'] != $class_id || 
                        $earliestRecord['section_id'] != $section_id || 
                        $earliestRecord['branch_id'] != $branch_id
                    ) {
                        $sqlUpdate = "
                            UPDATE cache_academic_students_data 
                            SET institution_id = ?, branch_id = ?, class_id = ?, section_id = ? 
                            WHERE id = ?";
                        $stmtUpdate = $conn_rjllearning_specific->prepare($sqlUpdate);
                        $stmtUpdate->execute([
                            $institution_id,
                            $branch_id,
                            $class_id,
                            $section_id,
                            $earliestRecord['id']
                        ]);
                    }
                } else {
                    // Insert a new row if date_of_admission is different and class/section/branch is different
                    if (
                        $earliestRecord['class_id'] != $class_id || 
                        $earliestRecord['section_id'] != $section_id || 
                        $earliestRecord['branch_id'] != $branch_id
                    ) {
                        $sqlInsert = "
                            INSERT INTO cache_academic_students_data (institution_id, branch_id, date, class_id, section_id, student_id) 
                            VALUES (?, ?, ?, ?, ?, ?)";
                        $stmtInsert = $conn_rjllearning_specific->prepare($sqlInsert);
                        $stmtInsert->execute([
                            $institution_id,
                            $branch_id,
                            $date_of_admission,
                            $class_id,
                            $section_id,
                            $student_id
                        ]);
                    }
                }
            } else {
                // No date_of_admission provided: only update class/section/branch if they differ
                if (
                    $earliestRecord['class_id'] != $class_id || 
                    $earliestRecord['section_id'] != $section_id || 
                    $earliestRecord['branch_id'] != $branch_id
                ) {
                    $sqlUpdate = "
                        UPDATE cache_academic_students_data 
                        SET institution_id = ?, branch_id = ?, class_id = ?, section_id = ? 
                        WHERE id = ?";
                    $stmtUpdate = $conn_rjllearning_specific->prepare($sqlUpdate);
                    $stmtUpdate->execute([
                        $institution_id,
                        $branch_id,
                        $class_id,
                        $section_id,
                        $earliestRecord['id']
                    ]);
                }
            }
        } else {
            // Step 2: If no record exists, insert a new row
            $sqlInsert = "
                INSERT INTO cache_academic_students_data (institution_id, branch_id, date, class_id, section_id, student_id) 
                VALUES (?, ?, ?, ?, ?, ?)";
            $stmtInsert = $conn_rjllearning_specific->prepare($sqlInsert);
            $stmtInsert->execute([
                $institution_id,
                $branch_id,
                $date_of_admission ?? date('Y-m-d'), // Use current date if no date_of_admission is provided
                $class_id,
                $section_id,
                $student_id
            ]);
        }
    } catch (PDOException $e) {
        // Handle any errors
        errorreturn("Error processing student ID $student_id: " . $e->getMessage());
    }
}









