<?php


function getActiveAndInactiveStudentCounts($conn_rjllearning_specific, $institution_id, $branch_id)
{
  $response = [
    "student_count" => [
      "active_students" => 0,
      "inactive_students" => 0,
    ],
    "faculty_count" => [
      "active_faculty" => 0,
      "inactive_faculty" => 0,
    ]
  ];

  $sql = "SELECT status, COUNT(*) AS count FROM students WHERE can_activate = 1";

  $params = [];

  // Add conditions for institution_id and branch_id if provided
  if ($institution_id !== 'all') {
    $sql .= " AND institution_id = ?";
    $params[] = $institution_id;
  }

  if ($branch_id !== 'all') {
    $sql .= " AND branch_id = ?";
    $params[] = $branch_id;
  }

  $sql .= " GROUP BY status";

  $stmt = $conn_rjllearning_specific->prepare($sql);
  $stmt->execute($params);
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

  foreach ($result as $row) {
    if ($row['status'] == 1) {
      $response['student_count']['active_students'] = $row['count'];
    } elseif ($row['status'] == 0) {
      $response['student_count']['inactive_students'] = $row['count'];
    }
  }

  $sql = "SELECT status, COUNT(*) AS count FROM faculty_info WHERE role IN ('teacher','principal','other')";

  $params = [];

  // Add conditions for institution_id and branch_id if provided
  if ($institution_id !== 'all') {
    $sql .= " AND institution_id = ?";
    $params[] = $institution_id;
  }

  if ($branch_id !== 'all') {
    $sql .= " AND branch_id = ?";
    $params[] = $branch_id;
  }

  //   $sql .= " ORDER BY userid asc";
  $sql .= " GROUP BY status";
  $stmt = $conn_rjllearning_specific->prepare($sql);
  $stmt->execute($params);
  $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

  foreach ($result as $row) {
    if ($row['status'] == 1) {
      $response['faculty_count']['active_faculty'] = $row['count'];
    } elseif ($row['status'] == 0) {
      $response['faculty_count']['inactive_faculty'] = $row['count'];
    }
  }

  return $response;
}

function getRegistered_studentCounts($start_date, $end_date, $institution_id, $branch_id, $conn_rjllearning_specific)
{
  $response = [
    "active_registration" => 0,
    "cancelled_registration" => 0,
    "admitted_registration" => 0
  ];

  // Fetch student counts
  $student_sql = "
        SELECT status, COUNT(*) as count FROM student_registration WHERE date_added BETWEEN ? AND ?";

  if ($institution_id !== 'all') {
    $student_sql .= " AND institution_id = ?";
  }
  if ($branch_id !== 'all') {
    $student_sql .= " AND branch_id = ?";
  }

  $student_sql .= " GROUP BY status";

  $params = [$start_date, $end_date];

  if ($institution_id !== 'all') {
    $params[] = $institution_id;
  }
  if ($branch_id !== 'all') {
    $params[] = $branch_id;
  }

  $stmt = $conn_rjllearning_specific->prepare($student_sql);
  $stmt->execute($params);
  $student_results = $stmt->fetchAll(PDO::FETCH_ASSOC);

  foreach ($student_results as $row) {
    if ($row['status'] == 1) {
      $response['active_registration'] = $row['count'];
    } elseif ($row['status'] == 0) {
      $response['cancelled_registration'] = $row['count'];
    } elseif ($row['status'] == 2) {
      $response['admitted_registration'] = $row['count'];
    }
  }

  return $response;
}

function getTotalFuelConsumption($start_date, $end_date, $institution_id, $branch_id, $conn_rjllearning_specific)
{

  // Initialize response structure
  $response = [
    "total_topup" => 0,
    "total_meter_reading" => 0
  ];

  try {
    // Check if it is a single date scenario
    if ($start_date === $end_date) {
      // Single date logic
      $sql = "
                SELECT SUM(topup) AS total_topup, 
                       SUM(CAST(SUBSTRING_INDEX(meter_reading, ' ', 1) AS UNSIGNED)) AS total_meter_reading
                FROM fuel_expense 
                WHERE date = ?
            ";
      $params = [$start_date];
    } else {

      $sql = "
                SELECT SUM(topup) AS total_topup, 
                       SUM(meter_reading) AS total_meter_reading
                FROM fuel_expense 
                WHERE date BETWEEN ? AND ?
            ";
      $params = [$start_date, $end_date];
    }

    if ($institution_id !== 'all') {
      $sql .= " AND institution_id = ?";
      $params[] = $institution_id;
    }

    if ($branch_id !== 'all') {
      $sql .= " AND branch_id = ?";
      $params[] = $branch_id;
    }

    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute($params);

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

    $response["total_topup"] = $result["total_topup"] ?? 0;
    $response["total_meter_reading"] = $result["total_meter_reading"] ?? 0;
  } catch (Exception $e) {
    $response["error"] = $e->getMessage();
  }

  return $response;
}


function fetchExpiredDocumentCounts($institution_id, $branch_id, $conn_rjllearning_specific)
{
  $response = [
    "insurance_documents" => 0,
    "permit_documents" => 0,
    "passing_documents" => 0,
    "rc_documents" => 0,
    "roadtax_documents" => 0
  ];

  // Query to fetch document expiry dates
  $sql = "SELECT insured_valid_upto, permit_valid_upto, passing_valid_upto, registration_valid_upto, road_tax_valid_upto
            FROM buses WHERE status = 1";

  $params = [];

  // Add filters for institution_id and branch_id
  if ($institution_id !== 'all') {
    $sql .= " AND institution_id = ?";
    $params[] = $institution_id;
  }

  if ($branch_id !== 'all') {
    $sql .= " AND branch_id = ?";
    $params[] = $branch_id;
  }

  $stmt = $conn_rjllearning_specific->prepare($sql);
  $stmt->execute($params);

  // Fetch all rows
  $documents_results = $stmt->fetchAll(PDO::FETCH_ASSOC);

  $current_date = new DateTime();

  // Iterate through each row to check for expired documents
  foreach ($documents_results as $row) {
    if (!empty($row["insured_valid_upto"])) {
      $expiry_date = new DateTime($row["insured_valid_upto"]);
      if ($expiry_date < $current_date) {
        $response["insurance_documents"]++;
      }
    }

    if (!empty($row["permit_valid_upto"])) {
      $expiry_date = new DateTime($row["permit_valid_upto"]);
      if ($expiry_date < $current_date) {
        $response["permit_documents"]++;
      }
    }

    if (!empty($row["passing_valid_upto"])) {
      $expiry_date = new DateTime($row["passing_valid_upto"]);
      if ($expiry_date < $current_date) {
        $response["passing_documents"]++;
      }
    }

    if (!empty($row["registration_valid_upto"])) {
      $expiry_date = new DateTime($row["registration_valid_upto"]);
      if ($expiry_date < $current_date) {
        $response["rc_documents"]++;
      }
    }

    if (!empty($row["road_tax_valid_upto"])) {
      $expiry_date = new DateTime($row["road_tax_valid_upto"]);
      if ($expiry_date < $current_date) {
        $response["roadtax_documents"]++;
      }
    }
  }

  return $response;
}

function fetchAttendanceStats($conn_rjllearning_specific, $institution_id, $branch_id, $date = null)
{
  try {
    // Current date if no date is provided
    $date = $date ?: date('Y-m-d');

    $response = [
      'date' => $date,
      'attendance_stats' => [
        'faculty' => [
          'present' => 0,
          'absent' => 0,
          'leave' => 0,
          'half_day' => 0,
          'total_marked' => 0,
          'unmarked' => 0,
        ],
        'students' => [
          'present' => 0,
          'absent' => 0,
          'leave' => 0,
          'half_day' => 0,
          'total_marked' => 0,
          'unmarked' => 0,
        ],
      ],
    ];

    // Fetch total students count
    $studentCountQuery = "SELECT COUNT(*) AS total FROM students WHERE can_activate = 1 and date_admission <= ? and (date_left IS NULL or date_left >= ?)";
    $studentParams = [$date, $date];

    if ($institution_id !== 'all') {
      $studentCountQuery .= " AND institution_id = ?";
      $studentParams[] = $institution_id;
    }

    if ($branch_id !== 'all') {
      $studentCountQuery .= " AND branch_id = ?";
      $studentParams[] = $branch_id;
    }

    $studentCountStmt = $conn_rjllearning_specific->prepare($studentCountQuery);
    $studentCountStmt->execute($studentParams);
    $totalStudents = (int)$studentCountStmt->fetchColumn();

    // Fetch total staff count (only teachers)
    $staffCountQuery = "SELECT COUNT(*) AS total FROM faculty_info WHERE role IN ('teacher','principal','other')";
    
    $staffParams = [];

    if ($institution_id !== 'all') {
      $staffCountQuery .= " AND institution_id = ?";
      $staffParams[] = $institution_id;
    }

    if ($branch_id !== 'all') {
      $staffCountQuery .= " AND branch_id = ?";
      $staffParams[] = $branch_id;
    }

    $staffCountStmt = $conn_rjllearning_specific->prepare($staffCountQuery);
    $staffCountStmt->execute($staffParams);
    $totalStaff = (int)$staffCountStmt->fetchColumn();

    // Fetch staff attendance data
    $staffQuery = "SELECT value, COUNT(*) AS total FROM staff_attendance_daywise WHERE date = ?";
    $staffParams = [$date];

    if ($institution_id !== 'all') {
      $staffQuery .= " AND institution_id = ?";
      $staffParams[] = $institution_id;
    }

    if ($branch_id !== 'all') {
      $staffQuery .= " AND branch_id = ?";
      $staffParams[] = $branch_id;
    }

    $staffQuery .= " GROUP BY value";

    $staffStmt = $conn_rjllearning_specific->prepare($staffQuery);
    $staffStmt->execute($staffParams);
    $staffResults = $staffStmt->fetchAll(PDO::FETCH_ASSOC);

    // Populate staff attendance data
    $totalStaffMarked = 0;
    foreach ($staffResults as $row) {
      if ($row['value'] === 'P') {
        $response['attendance_stats']['faculty']['present'] = (int)$row['total'];
      } else if ($row['value'] === 'A') {
        $response['attendance_stats']['faculty']['absent'] = (int)$row['total'];
      } else if ($row['value'] === 'L') {
        $response['attendance_stats']['faculty']['leave'] = (int)$row['total'];
      } else if ($row['value'] === 'H') {
        $response['attendance_stats']['faculty']['half_day'] = (int)$row['total'];
      }
      $totalStaffMarked += (int)$row['total'];
    }
    $response['attendance_stats']['faculty']['total_marked'] = $totalStaffMarked;
    $response['attendance_stats']['faculty']['unmarked'] = $totalStaff - $totalStaffMarked;

    // Fetch student attendance data
    $studentQuery = "SELECT value, COUNT(*) AS total FROM student_attendance_daywise WHERE date = ?";
    $studentParams = [$date];

    if ($institution_id !== 'all') {
      $studentQuery .= " AND institution_id = ?";
      $studentParams[] = $institution_id;
    }

    if ($branch_id !== 'all') {
      $studentQuery .= " AND branch_id = ?";
      $studentParams[] = $branch_id;
    }

    $studentQuery .= " GROUP BY value";

    $studentStmt = $conn_rjllearning_specific->prepare($studentQuery);
    $studentStmt->execute($studentParams);
    $studentResults = $studentStmt->fetchAll(PDO::FETCH_ASSOC);

    // Populate student attendance data
    $totalStudentMarked = 0;
    foreach ($studentResults as $row) {
      if ($row['value'] === 'P') {
        $response['attendance_stats']['students']['present'] = (int)$row['total'];
      } else if ($row['value'] === 'A') {
        $response['attendance_stats']['students']['absent'] = (int)$row['total'];
      } else if ($row['value'] === 'L') {
        $response['attendance_stats']['students']['leave'] = (int)$row['total'];
      } else if ($row['value'] === 'H') {
        $response['attendance_stats']['students']['half_day'] = (int)$row['total'];
      }
      $totalStudentMarked += (int)$row['total'];
    }
    $response['attendance_stats']['students']['total_marked'] = $totalStudentMarked;
    $response['attendance_stats']['students']['unmarked'] = $totalStudents - $totalStudentMarked;

    return $response;
  } catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
    return [];
  }
}

function fetchTransactions($institution_id, $branch_id, $conn_rjllearning_specific, $start_date, $end_date)
{
  $result = [];
  $overall_totals = [
    'receipt' => 0,
    'expense' => 0,
  ];

  $receipt_categories = [];
  $expense_categories = [];

  function generateDateRanges($start_date, $end_date)
  {
    $date_ranges = [];
    $start_date = $start_date ?? date('Y-m-d');
    $end_date = $end_date ?? date('Y-m-d');
    $current_start_date = new DateTime($start_date);
    $end_date = new DateTime($end_date);

    $date_diff = $current_start_date->diff($end_date)->days;

    if ($date_diff === 0) {
      // Same start and end date: Single day range
      $date_ranges[] = [
        'start' => $current_start_date->format('Y-m-d'),
        'end' => $current_start_date->format('Y-m-d')
      ];
    } elseif ($date_diff <= 7) {
      // Handle day-by-day for dates within 7 days
      while ($current_start_date <= $end_date) {
        $date_ranges[] = [
          'start' => $current_start_date->format('Y-m-d'),
          'end' => $current_start_date->format('Y-m-d')
        ];
        $current_start_date->modify('+1 day');
      }
    } elseif ($date_diff <= 31) {
      // Week-based range for dates within 31 days
      while ($current_start_date <= $end_date) {
        $current_end_date = clone $current_start_date;
        $current_end_date->modify('+6 days');
        if ($current_end_date > $end_date) {
          $current_end_date = $end_date;
        }
        $date_ranges[] = [
          'start' => $current_start_date->format('Y-m-d'),
          'end' => $current_end_date->format('Y-m-d')
        ];
        $current_start_date->modify('+7 days');
      }
    } else {
      // Month-based range
      while ($current_start_date <= $end_date) {
        $current_end_date = clone $current_start_date;
        $current_end_date->modify('last day of this month');
        if ($current_end_date > $end_date) {
          $current_end_date = $end_date;
        }
        $date_ranges[] = [
          'start' => $current_start_date->format('Y-m-d'),
          'end' => $current_end_date->format('Y-m-d')
        ];
        $current_start_date->modify('first day of next month');
      }
    }

    return $date_ranges;
  }

  function formatAmount($amount)
  {
    return number_format($amount, 2);
  }

  $date_ranges = generateDateRanges($start_date, $end_date);

  // Fetching data for receipts and expenses by category
  $sql_category = "SELECT t.transaction_type_group, 
                        SUM(t.amount) as total, 
                        t.payment_method, 
                        g.id as category_id, 
                        g.group_name as category_name 
                    FROM unmarked_transaction t
                    JOIN unmarked_transaction_type_group g ON t.category_id = g.id
                    WHERE t.date >= ? AND t.date <= ?";

  if ($institution_id !== 'all') {
    $sql_category .= " AND t.institution_id = ?";
  }
  if ($branch_id !== 'all') {
    $sql_category .= " AND t.branch_id = ?";
  }
  $sql_category .= " GROUP BY t.transaction_type_group, t.payment_method, g.id";

  $stmt_category = $conn_rjllearning_specific->prepare($sql_category);

  $receipt_category_wise = [];
  $expense_category_wise = [];

  foreach ($date_ranges as $range) {
    $array_params = [
      date('Y-m-d', strtotime($range['start'])),
      date('Y-m-d', strtotime($range['end']))
    ];
    if ($institution_id !== 'all') {
      $array_params[] = $institution_id;
    }
    if ($branch_id !== 'all') {
      $array_params[] = $branch_id;
    }

    $stmt_category->execute($array_params);
    $transactions = $stmt_category->fetchAll();

    $temp_date_data_array = [
      'date' => ($range['start'] === $range['end']) ? $range['start'] : $range['start'] . ' - ' . $range['end'],
      'receipt' => 0,
      'expense' => 0,
    ];

    foreach ($transactions as $transaction) {
      $transaction_type = strtolower($transaction['transaction_type_group']);
      $amount = floatval($transaction['total']);
      $category_id = $transaction['category_id'];
      $category_name = $transaction['category_name'];

      $category_key = $category_id . '-' . $category_name;

      if ($transaction_type === 'receipt') {
        $temp_date_data_array['receipt'] += $amount;
        $overall_totals['receipt'] += $amount;

        if (!isset($receipt_category_wise[$category_key])) {
          $receipt_category_wise[$category_key] = [
            'category_id' => $category_id,
            'category_name' => $category_name,
            'amount' => 0,
          ];
        }
        $receipt_category_wise[$category_key]['amount'] += $amount;
      } elseif ($transaction_type === 'expense') {
        $temp_date_data_array['expense'] += $amount;
        $overall_totals['expense'] += $amount;

        if (!isset($expense_category_wise[$category_key])) {
          $expense_category_wise[$category_key] = [
            'category_id' => $category_id,
            'category_name' => $category_name,
            'amount' => 0,
          ];
        }
        $expense_category_wise[$category_key]['amount'] += $amount;
      }
    }

    // Only include the range if there are non-zero totals
    // if ($temp_date_data_array['receipt'] > 0 || $temp_date_data_array['expense'] > 0) {
    //     $result[] = $temp_date_data_array;
    // }

    if ($temp_date_data_array['receipt'] > 0 || $temp_date_data_array['expense'] > 0) {
      $temp_date_data_array['receipt'] = formatAmount($temp_date_data_array['receipt']);
      $temp_date_data_array['expense'] = formatAmount($temp_date_data_array['expense']);
      $result[] = $temp_date_data_array;
    }
  }

  foreach ($receipt_category_wise as &$category) {
    $category['amount'] = formatAmount($category['amount']);
  }

  foreach ($expense_category_wise as &$category) {
    $category['amount'] = formatAmount($category['amount']);
  }


  $overall_totals['receipt'] = formatAmount($overall_totals['receipt']);
  $overall_totals['expense'] = formatAmount($overall_totals['expense']);

  $receipt_categories = array_values($receipt_category_wise);
  $expense_categories = array_values($expense_category_wise);

  return [
    'data' => $result,
    'receipt_category' => $receipt_categories,
    'expense_category' => $expense_categories,
    'overall_totals' => $overall_totals,
  ];
}

function fetchGatePassCounts($conn, $institution_id, $branch_id, $from_date = null, $to_date = null) {
    $conditions = [];
    $params = [];

    if ($institution_id !== 'all') {
        $conditions[] = "institution_id = ?";
        $params[] = $institution_id;
    }

    if ($branch_id !== 'all') {
        $conditions[] = "branch_id = ?";
        $params[] = $branch_id;
    }

    if (!empty($from_date)) {
        $conditions[] = "req_date >= ?";
        $params[] = $from_date;
    }

    if (!empty($to_date)) {
        $conditions[] = "req_date <= ?";
        $params[] = $to_date;
    }

    $where = count($conditions) > 0 ? "WHERE " . implode(" AND ", $conditions) : "";

    $sql = "
        SELECT
            SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as pending,
            SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as accepted,
            SUM(CASE WHEN status = -1 THEN 1 ELSE 0 END) as denied
        FROM gate_pass
        $where
    ";

    $stmt = $conn->prepare($sql);
    $stmt->execute($params);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    
    return [
        "pending" => (int)$row['pending'],
        "accepted" => (int)$row['accepted'],
        "denied" => (int)$row['denied']
    ];
}


function fetchMailSmsStats($institution_id, $branch_id, $conn_rjllearning, $current_date)
{
  $end_date = new DateTime($current_date);
  $start_date = clone $end_date;
  $start_date->modify('first day of -5 months'); // Start from the beginning of the month 5 months ago

  $result = [];

  // Function to get date ranges
  function getDateRanges(DateTime $start_date, DateTime $end_date)
  {
    $date_ranges = [];
    $current_start_date = clone $start_date;

    while ($current_start_date <= $end_date) {
      $current_end_date = clone $current_start_date;
      $current_end_date->modify('last day of this month');

      if ($current_end_date > $end_date) {
        $current_end_date = $end_date;
      }

      $date_ranges[] = [
        'start' => $current_start_date->format('Y-m-d'),
        'end' => $current_end_date->format('Y-m-d'),
        'month_year' => $current_start_date->format('F Y')
      ];

      $current_start_date->modify('first day of next month');
    }

    return $date_ranges;
  }

  $date_ranges = getDateRanges($start_date, $end_date);

  $last_six_months_data = [];

  // Loop over the date ranges and fetch the data
  foreach ($date_ranges as $range) {
    $sql = "SELECT SUM(mails_sent) AS total_mails_sent,
                SUM(sms_sent) AS total_sms_sent,
                SUM(notification_sent) AS total_notifications_sent
                FROM mail_sms_counter
                WHERE DATE(last_mail_date) >= ? AND DATE(last_mail_date) <= ?";

    if ($institution_id !== 'all') {
      $sql .= " AND institution_id = ?";
    }
    if ($branch_id !== 'all') {
      $sql .= " AND branch_id = ?";
    }

    $stmt = $conn_rjllearning->prepare($sql);

    $params = [$range['start'], $range['end']];
    if ($institution_id !== 'all') {
      $params[] = $institution_id;
    }
    if ($branch_id !== 'all') {
      $params[] = $branch_id;
    }

    $stmt->execute($params);
    $stats = $stmt->fetch();

    $last_six_months_data[] = [
      'month' => $range['month_year'],
      // 'date' => $range['start'] . ' - ' . $range['end'],
      'total_mails_sent' => $stats['total_mails_sent'] ?? 0,
      'total_sms_sent' => $stats['total_sms_sent'] ?? 0,
      'total_notification_sent' => $stats['total_notifications_sent'] ?? 0
    ];
  }

  // Fetch current day data separately
  $sql_current_day = "SELECT SUM(mails_sent) AS mails_sent,
                        SUM(sms_sent) AS sms_sent,
                        SUM(notification_sent) AS notifications_sent
                        FROM mail_sms_counter
                        WHERE DATE(last_mail_date) = ?";

  $stmt_current_day = $conn_rjllearning->prepare($sql_current_day);
  $stmt_current_day->execute([$current_date]);

  $current_day_stats = $stmt_current_day->fetch();

  $current_day_data = [
    'current_day' => [
      'date' => $current_date,
      'mails' => $current_day_stats['mails_sent'] ?? 0,
      'sms' => $current_day_stats['sms_sent'] ?? 0,
      'notifications' => $current_day_stats['notifications_sent'] ?? 0
    ]
  ];

  // Add the current day data and the last six months data to the result
  $result[] = $current_day_data;
  $result[] = ['last_six_months' => $last_six_months_data];

  return $result;
}

// function get_studentbirthday($conn_rjllearning, $workspace_id, $institution_id, $branch_id, $mindate, $maxdate)
// {
//   $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;
// }



function get_specific_birthday($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $mindate, $maxdate, $workspace_id)
{
    $result = [
        'student_birthdates_info' => [],
        'faculty_birthdates_info' => []
    ];

    $dates_between = getDatesBetween($mindate, $maxdate);
    if (empty($dates_between)) {
        echo "No dates found between $mindate and $maxdate\n";
        die();
    }

    $md_list = array_unique(array_map(function ($d) {
        return date('m-d', strtotime($d));
    }, $dates_between));

    $placeholders = implode(',', array_fill(0, count($md_list), '?'));
    $params = $md_list;

    $where = "can_activate = 1 AND DATE_FORMAT(dob, '%m-%d') IN ($placeholders)";

    if (!empty($institution_id) && $institution_id !== 'all') {
        $where .= " AND institution_id = ?";
        $params[] = $institution_id;
    }

    if (!empty($branch_id) && $branch_id !== 'all') {
        $where .= " AND branch_id = ?";
        $params[] = $branch_id;
    }

    if (!empty($workspace_id) && $workspace_id !== 'all') {
        $where .= " AND workspace_id = ?";
        $params[] = $workspace_id;
    }

    // Fetch users matching the conditions
    $sql = "SELECT id AS user_id, role, dob, branch_id, institution_id FROM users WHERE $where";
    $stmt = $conn_rjllearning->prepare($sql);
    $stmt->execute($params);
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (empty($users)) {
        echo "No users matched birthday condition\n";
        die();
    }

    $user_ids = array_column($users, 'user_id');
    $user_ids_placeholder = implode(',', array_fill(0, count($user_ids), '?'));

    // Fetch students with optional institution filter
    $student_sql = "SELECT id, user_id, name, email, admission_number, dob, class_id, section_id, branch_id 
                    FROM students 
                    WHERE user_id IN ($user_ids_placeholder)";
    $student_params = $user_ids;

    if (!empty($institution_id) && $institution_id !== 'all') {
        $student_sql .= " AND institution_id = ?";
        $student_params[] = $institution_id;
    }

    $student_stmt = $conn_rjllearning_specific->prepare($student_sql);
    $student_stmt->execute($student_params);
    $students = $student_stmt->fetchAll(PDO::FETCH_ASSOC);

    $students_by_user = [];
    foreach ($students as $student) {
        $students_by_user[$student['user_id']] = $student;
    }

    // Fetch faculty with optional institution filter
    $faculty_sql = "SELECT id, userid AS user_id, name, gender, email, role, dob, branch_id 
                    FROM faculty_info 
                    WHERE userid IN ($user_ids_placeholder)";
    $faculty_params = $user_ids;

    if (!empty($institution_id) && $institution_id !== 'all') {
        $faculty_sql .= " AND institution_id = ?";
        $faculty_params[] = $institution_id;
    }

    $faculty_stmt = $conn_rjllearning_specific->prepare($faculty_sql);
    $faculty_stmt->execute($faculty_params);
    $faculties = $faculty_stmt->fetchAll(PDO::FETCH_ASSOC);

    $faculties_by_user = [];
    foreach ($faculties as $faculty) {
        $faculties_by_user[$faculty['user_id']] = $faculty;
    }

    // Combine all info using institution_id from users table
    foreach ($users as $user) {
        $uid = $user['user_id'];
        $role = $user['role'];
        $institution_id_for_user = $user['institution_id'];

        if ($role === 'student' && isset($students_by_user[$uid])) {
            $s = $students_by_user[$uid];

            $result['student_birthdates_info'][] = [
                'workspace_id' => $workspace_id,
                'student_id' => $s['id'],
                'user_id' => $uid,
                'student_name' => $s['name'],
                'email' => $s['email'],
                'admission_number' => $s['admission_number'],
                'date_of_birth' => $s['dob'],

                'branch_info' => getBranchInfo($conn_rjllearning_specific, $institution_id_for_user, $s['branch_id'], null, true),
                'class_info' => getClass($conn_rjllearning_specific, $institution_id_for_user, $s['class_id'], true),
                'section_info' => getSection($conn_rjllearning_specific, $institution_id_for_user, $s['section_id'], true)
            ];
        } elseif (in_array($role, ['teacher', 'principal']) && isset($faculties_by_user[$uid])) {
            $f = $faculties_by_user[$uid];

            $result['faculty_birthdates_info'][] = [
                'workspace_id' => $workspace_id,
                'faculty_id' => $f['id'],
                'user_id' => $uid,
                'faculty_name' => $f['name'],
                'gender' => $f['gender'],
                'email' => $f['email'],
                'facultyrole' => $f['role'],
                'date_of_birth' => $f['dob'],
                'branch_info' => getBranchInfo($conn_rjllearning_specific, $institution_id_for_user, $f['branch_id'], null, true),
            ];
        }
    }

    return $result;
}

function fetchUnmarkedAttendanceClasses($conn_rjllearning_specific, $institution_id, $branch_id, $date, $limit, $offset) {
    try {
        $date = $date ?: date('Y-m-d');
        $unmarkedClasses = [];

        //get all active class-section combinations with student counts
        $activeClassesQuery = "SELECT s.class_id, s.section_id, s.institution_id, COUNT(s.id) as student_count
                              FROM students s
                              WHERE s.can_activate = 1 
                              AND s.date_admission <= ? 
                              AND (s.date_left IS NULL OR s.date_left >= ?)";

        $params = [$date, $date];

        if ($institution_id !== 'all') {
            $activeClassesQuery .= " AND s.institution_id = ?";
            $params[] = $institution_id;
        }

        if ($branch_id !== 'all') {
            $activeClassesQuery .= " AND s.branch_id = ?";
            $params[] = $branch_id;
        }

        $activeClassesQuery .= " GROUP BY s.class_id, s.section_id, s.institution_id";
        
        if ($limit !== 'all') {
            $activeClassesQuery .= " LIMIT ? OFFSET ?";
            $params[] = (int)$limit;
            $params[] = (int)$offset;
        }

        $activeStmt = $conn_rjllearning_specific->prepare($activeClassesQuery);
        $activeStmt->execute($params);
        $activeClasses = $activeStmt->fetchAll(PDO::FETCH_ASSOC);

        //get class-sections with attendance marked (any value)
        $markedQuery = "SELECT DISTINCT class_id, section_id 
                        FROM student_attendance_daywise 
                        WHERE date = ?";
        $markedParams = [$date];

        if ($institution_id !== 'all') {
            $markedQuery .= " AND institution_id = ?";
            $markedParams[] = $institution_id;
        }

        if ($branch_id !== 'all') {
            $markedQuery .= " AND branch_id = ?";
            $markedParams[] = $branch_id;
        }

        $markedStmt = $conn_rjllearning_specific->prepare($markedQuery);
        $markedStmt->execute($markedParams);
        $markedClasses = $markedStmt->fetchAll(PDO::FETCH_ASSOC);

        //find unmarked classes
        foreach ($activeClasses as $class) {
            $isMarked = false;

            foreach ($markedClasses as $marked) {
                if (
                    $marked['class_id'] == $class['class_id'] &&
                    $marked['section_id'] == $class['section_id']
                ) {
                    $isMarked = true;
                    break;
                }
            }

            if ($class['student_count'] > 0 && !$isMarked) {
                // Use the specific institution_id from the class record when institution_id is 'all'
                $fetch_institution_id = ($institution_id === 'all') ? $class['institution_id'] : $institution_id;

                $classInfo = getClass($conn_rjllearning_specific, $fetch_institution_id, $class['class_id'], true);
                $sectionInfo = getSection($conn_rjllearning_specific, $fetch_institution_id, $class['section_id'], true);

                if ($classInfo && $sectionInfo && !isset($classInfo['error'])) {
                    $unmarkedClasses[] = [
                        'class' => $classInfo,
                        'section' => $sectionInfo,
                        'active_students' => $class['student_count']
                    ];
                }
            }
        }

        return [
            'data' => $unmarkedClasses,
            'count' => count($unmarkedClasses)
        ];

    } catch (PDOException $e) {
        error_log("Attendance check failed: " . $e->getMessage());
        return [
            'success' => false,
            'message' => "Database error",
            'error' => $e->getMessage()
        ];
    }
}
