<?php
// for student and faculty birthdays
function get_student_birthday($conn_rjllearning, $workspace_id = null, $institution_id =null, $branch_id =null, $mindate =null, $maxdate =null)
{
  $array_return = [
    'student_birthdates_info' => [],
    'faculty_birthdates_info' => []
  ];

  $array_dates = getDatesBetween($mindate, $maxdate);

  // Prepare the SQL query
  $sql_session = "SELECT * FROM users WHERE can_activate = 1 and (";
  $array_params = [];

  // foreach ($array_dates as $temp) {
  //     $exploded_date = explode("-", $temp);
  //     $sql_session .= "DATE_FORMAT(dob, '%m-%d') = ? OR";
  //     $array_params[] = $temp[1] . '-' . $temp[2]; // Use date
  // }

  foreach ($array_dates as $temp) {
    $exploded_date = explode("-", $temp);
    $sql_session .= "DATE_FORMAT(dob, '%m-%d') = ? OR ";
    $array_params[] = $exploded_date[1] . '-' . $exploded_date[2]; // Use month-day format
  }

  // Remove the trailing "OR" from the last condition
  $sql_session = rtrim($sql_session, " OR ") . ")";

  // Add institution_id condition if present
  if (!empty($institution_id) && $institution_id !== 'all') {
    $sql_session .= " AND institution_id = ?";
    $array_params[] = $institution_id;
  }
  if ($workspace_id != null) {
    $sql_session .= " AND workspace_id = ?";
    $array_params[] = $workspace_id;
  }

  // Prepare and execute the query
  $stmt = $conn_rjllearning->prepare($sql_session);

  $stmt->execute($array_params);
  $session_rows = $stmt->fetchAll();

  foreach ($session_rows as $rows) {


    $user_id = $rows['id'];
    $workspace_id = $rows['workspace_id'];

    // Query to get db_name for the workspace
    $sql = "SELECT db_name FROM workspace_database_info WHERE workspace_id = ? and status =1";
    $array_params = [$workspace_id];
    $stmt = $conn_rjllearning->prepare($sql);
    $stmt->execute($array_params);
    $section_rows = $stmt->fetchAll();

    $db_name = null;

    if (count($section_rows) > 0) {
      $db_name = $section_rows[0]['db_name']; // Get the db_name
    }


    $conn_rjllearning_specific = dynamicDbConnection($db_name);

    // Query to get student info from the specific database
    if ($conn_rjllearning_specific !== null) {

      if ($rows['role'] == 'student') {

        $sql_student = "SELECT * FROM students WHERE user_id = ? AND institution_id = ?";
        $array_params = [$user_id, $rows['institution_id']];
        // Assuming we are connecting to the student's database
        $stmt_student = $conn_rjllearning_specific->prepare($sql_student);
        $stmt_student->execute($array_params);
        $student_rows = $stmt_student->fetchAll();
        // print_r($student_rows);
        if (empty($student_rows)) {
          continue; // Skip if no student data found
        }
        $student_row = $student_rows[0]; // Get the student row
        // print_r($student_row);
        $section_id = $student_row['section_id'];

        // Initialize section info
        $section = [];

        if ($section_id == 'all') {
          $section = ['section_id' => $section_id, 'section_name' => ucwords($section_id)];
        } else {
          $sql = "SELECT * FROM section_info WHERE institution_id = ? AND id = ?";
          $array_params = [$student_row['institution_id'], $section_id];
          $stmt = $conn_rjllearning_specific->prepare($sql);
          $stmt->execute($array_params);
          $section_rows = $stmt->fetchAll();

          if (!empty($section_rows)) {
            $section = ['section_id' => $section_rows[0]['id'], 'section_name' => $section_rows[0]['section_name']];
          }
        }
        // echo $student_row['class_id'];
        // Get institution, class, and branch info
        $institution_data = getInstitution($conn_rjllearning, $student_row['institution_id']);
        $class_info = getClass($conn_rjllearning_specific, $student_row['institution_id'], $student_row['class_id']);
        $branch_info = getBranchInfo($conn_rjllearning_specific, $student_row['institution_id'], $student_row['branch_id']);

        // Add to the return array under 'student_birthdates_info'
        $array_return['student_birthdates_info'][] = [
          'workspace_id' => $workspace_id,
          'student_id' => $student_row['id'],
          'user_id' => $student_row['user_id'],
          'student_name' => $student_row['name'],
          'email' => $student_row['email'],
          'admission_number' => $student_row['admission_number'],
          'date_of_birth' => $student_row['dob'],
          'institution_data' => $institution_data,
          'branch_id' => $branch_info,
          'class_info' => $class_info,
          'section_info' => $section
        ];
      } elseif ($rows['role'] == 'teacher' or $rows['role'] == 'principal') {
        $sql_faculty = "SELECT * FROM faculty_info WHERE userid = ? AND institution_id = ?";
        $array_params = [$user_id, $rows['institution_id']];
        $stmt_faculty = $conn_rjllearning_specific->prepare($sql_faculty);
        $stmt_faculty->execute($array_params);
        $faculty_rows = $stmt_faculty->fetchAll();
        if (empty($faculty_rows)) {
          continue; // Skip if no faculty data found
        }

        $faculty_row = $faculty_rows[0];

        $faculty_institution_data = getInstitution($conn_rjllearning, $faculty_row['institution_id']);
        $faculty_branch_info = getBranchInfo($conn_rjllearning_specific, $faculty_row['institution_id'], $faculty_row['branch_id']);

        $array_return['faculty_birthdates_info'][] = [
          'workspace_id' => $workspace_id,
          'faculty_id' => $faculty_row['id'],
          'user_id' => $faculty_row['userid'],
          'faculty_name' => $faculty_row['name'],
          'gender' => $faculty_row['gender'],
          'email' => $faculty_row['email'],
          'facultyrole' => $faculty_row['role'],
          'date_of_birth' => $faculty_row['dob'],
          'institution_data' => $faculty_institution_data,
          'branch_info' => $faculty_branch_info
        ];
      }
    }
  }

  // Return the data in the desired format
  return $array_return;
}
