<?php

/**
 * Student database management.
 *
 * @package ResultManagementSystem
 */

if (! defined('ABSPATH')) {
	exit;
}

global $wpdb;

class ASTGDRMSYS_Student_Database_Manager
{
	private $wpdb;
	private $prefix;
	private $students_table;
	private $enrollments_table;
	private $student_result_table;
	private $marks_table;
	private $limit = 20;

	public function __construct($wpdb)
	{
		$this->wpdb              = $wpdb;
		$this->prefix            = $wpdb->prefix;
		$this->students_table       = $this->prefix . 'astgdrmsys_students';
		$this->enrollments_table    = $this->prefix . 'astgdrmsys_student_enrollments';
		$this->student_result_table = $this->prefix . 'astgdrmsys_student_result';
		$this->marks_table          = $this->prefix . 'astgdrmsys_mark';
	}

	public function display(): void
	{
		$this->handle_actions();
		$this->render_messages();

		$active_tab = isset($_GET['tab']) ? sanitize_key(wp_unslash($_GET['tab'])) : 'students';
		if (! in_array($active_tab, array('students', 'add', 'import', 'promote'), true)) {
			$active_tab = 'students';
		}

		echo '<div class="wrap astgdrmsys-premium-wrap astgdrmsys-student-database">';
		echo '<div class="premium-page-header">';
		echo '<div class="page-header-text">';
		echo '<h1>' . esc_html__('Student Database', 'result-management-system-for-institutions') . '</h1>';
		echo '<p>' . esc_html__('Manage student enrollments, promotions, and imports.', 'result-management-system-for-institutions') . '</p>';
		echo '</div>';
		echo '<div class="page-header-actions">';
		echo '<a href="' . esc_url(admin_url('admin.php?page=astgdrmsys-student-database&tab=add')) . '" class="hero-btn primary-btn"><i class="dashicons dashicons-plus"></i> ' . esc_html__('Add Student', 'result-management-system-for-institutions') . '</a>';
		echo '</div>';
		echo '</div>';
		$this->render_tabs($active_tab);

		if ($active_tab === 'add') {
			$this->render_student_form();
		} elseif ($active_tab === 'import') {
			$this->render_import_export();
		} elseif ($active_tab === 'promote') {
			$this->render_promotion();
		} else {
			$this->render_students_list();
		}

		echo '</div>';
	}

	private function handle_actions(): void
	{
		if (isset($_POST['astgdrmsys_student_db_export'])) {
			$this->verify_nonce('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
			$this->export_students();
		}

		if (isset($_POST['astgdrmsys_student_db_save'])) {
			$this->verify_nonce('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
			$this->save_student();
		}

		if (isset($_POST['astgdrmsys_student_db_import'])) {
			$this->verify_nonce('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
			$this->import_students();
		}

		if (isset($_POST['astgdrmsys_student_db_promote'])) {
			$this->verify_nonce('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
			$this->promote_students();
		}

		if (isset($_POST['astgdrmsys_student_db_delete'])) {
			$this->verify_nonce('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
			$this->delete_student();
		}

		if (isset($_POST['astgdrmsys_student_db_delete_all'])) {
			$this->verify_nonce('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
			$this->delete_all_students();
		}

		if (isset($_GET['student_action'], $_GET['student_id'], $_GET['_wpnonce'])) {
			$action = sanitize_key(wp_unslash($_GET['student_action']));
			$id     = absint(wp_unslash($_GET['student_id']));

			if ($id > 0 && wp_verify_nonce(sanitize_text_field(wp_unslash($_GET['_wpnonce'])), 'astgdrmsys_student_status_' . $id)) {
				if ($action === 'deactivate') {
					$this->wpdb->update($this->students_table, array('status' => 'inactive', 'updated_at' => current_time('mysql')), array('id' => $id));
					wp_safe_redirect(add_query_arg(array('page' => 'astgdrmsys-student-database', 'message' => 'deactivated'), admin_url('admin.php')));
					exit;
				}

				if ($action === 'reactivate') {
					$this->wpdb->update($this->students_table, array('status' => 'active', 'updated_at' => current_time('mysql')), array('id' => $id));
					wp_safe_redirect(add_query_arg(array('page' => 'astgdrmsys-student-database', 'message' => 'reactivated'), admin_url('admin.php')));
					exit;
				}
			}
		}
	}

	private function verify_nonce(string $action, string $field): void
	{
		if (! isset($_POST[$field]) || ! wp_verify_nonce(sanitize_text_field(wp_unslash($_POST[$field])), $action)) {
			wp_die(esc_html__('Security check failed. Please refresh and try again.', 'result-management-system-for-institutions'));
		}
	}

	private function render_messages(): void
	{
		if (empty($_GET['message'])) {
			return;
		}

		$message_key = sanitize_key(wp_unslash($_GET['message']));
		$messages = array(
			'saved'       => __('Student saved successfully.', 'result-management-system-for-institutions'),
			'imported'    => __('Student CSV imported successfully.', 'result-management-system-for-institutions'),
			'promoted'    => __('Selected students promoted successfully.', 'result-management-system-for-institutions'),
			'deactivated' => __('Student deactivated successfully.', 'result-management-system-for-institutions'),
			'reactivated' => __('Student reactivated successfully.', 'result-management-system-for-institutions'),
			'deleted'     => __('Student record deleted successfully.', 'result-management-system-for-institutions'),
			'all_deleted' => __('All student records deleted successfully.', 'result-management-system-for-institutions'),
			'confirm_failed' => __('Delete cancelled. Type confirm to delete student records.', 'result-management-system-for-institutions'),
			'delete_failed' => __('Student record delete failed. Please try again.', 'result-management-system-for-institutions'),
			'none'        => __('No students were selected.', 'result-management-system-for-institutions'),
		);

		if (! isset($messages[$message_key])) {
			return;
		}

		$notice_class = in_array($message_key, array('confirm_failed', 'delete_failed', 'none'), true) ? 'notice-warning' : 'notice-success';
		echo '<div class="notice ' . esc_attr($notice_class) . ' is-dismissible"><p>' . esc_html($messages[$message_key]) . '</p></div>';
	}

	private function render_tabs(string $active_tab): void
	{
		$tabs = array(
			'students' => __('Students', 'result-management-system-for-institutions'),
			'add'      => __('Add / Edit Student', 'result-management-system-for-institutions'),
			'import'   => __('Import / Export', 'result-management-system-for-institutions'),
			'promote'  => __('Promote Students', 'result-management-system-for-institutions'),
		);

		echo '<nav class="premium-tabs">';
		foreach ($tabs as $tab => $label) {
			$url   = add_query_arg(array('page' => 'astgdrmsys-student-database', 'tab' => $tab), admin_url('admin.php'));
			$class = $active_tab === $tab ? ' active' : '';
			echo '<a class="' . esc_attr($class) . '" href="' . esc_url($url) . '">' . esc_html($label) . '</a>';
		}
		echo '</nav>';
	}

	private function render_students_list(): void
	{
		$page   = isset($_GET['paginate']) ? max(1, absint(wp_unslash($_GET['paginate']))) : 1;
		$offset = ($page - 1) * $this->limit;
		$search = isset($_GET['student_search']) ? sanitize_text_field(wp_unslash($_GET['student_search'])) : '';
		$status = isset($_GET['student_status']) ? sanitize_key(wp_unslash($_GET['student_status'])) : '';
		$class_id      = isset($_GET['student_class_id']) ? absint(wp_unslash($_GET['student_class_id'])) : 0;
		$department_id = isset($_GET['student_department_id']) ? absint(wp_unslash($_GET['student_department_id'])) : 0;
		$group_id      = isset($_GET['student_group_id']) ? absint(wp_unslash($_GET['student_group_id'])) : 0;

		$where  = array('1=1');
		$params = array();

		if ($search !== '') {
			$where[] = '(s.name LIKE %s OR s.regno LIKE %s OR s.student_uid LIKE %s)';
			$like = '%' . $this->wpdb->esc_like($search) . '%';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		if (in_array($status, array('active', 'inactive'), true)) {
			$where[]  = 's.status = %s';
			$params[] = $status;
		}

		if ($class_id > 0) {
			$where[]  = 'e.class_id = %d';
			$params[] = $class_id;
		}

		if ($department_id > 0) {
			$where[]  = 'e.department_id = %d';
			$params[] = $department_id;
		}

		if ($group_id > 0) {
			$where[]  = 'e.section_id = %d';
			$params[] = $group_id;
		}

		$latest_enrollment_join = "LEFT JOIN {$this->enrollments_table} e ON e.id = (
				SELECT e2.id
				FROM {$this->enrollments_table} e2
				WHERE e2.student_id = s.id AND e2.status = 'active'
				ORDER BY e2.exam_year_id DESC, e2.id DESC
				LIMIT 1
			)";
		$where_sql = 'WHERE ' . implode(' AND ', $where);
		$count_sql = "SELECT COUNT(*) FROM {$this->students_table} s
			{$latest_enrollment_join}
			{$where_sql}";
		$list_sql  = "SELECT s.*, e.exam_year_id, e.class_id, e.department_id, e.section_id, e.roll_no
			FROM {$this->students_table} s
			{$latest_enrollment_join}
			{$where_sql}
			ORDER BY s.name ASC
			LIMIT %d, %d";

		$count_query = ! empty($params) ? $this->wpdb->prepare($count_sql, $params) : $count_sql;
		$total       = (int) $this->wpdb->get_var($count_query);

		$list_params = array_merge($params, array($offset, $this->limit));
		$students    = $this->wpdb->get_results($this->wpdb->prepare($list_sql, $list_params), ARRAY_A);

		echo '<form method="get" class="astgdrmsys-filter-form premium-filter-form">';
		echo '<input type="hidden" name="page" value="astgdrmsys-student-database">';
		echo '<input type="hidden" name="tab" value="students">';
		echo '<div class="filter-group"><label>Search</label>';
		echo '<input type="search" class="premium-input" name="student_search" value="' . esc_attr($search) . '" placeholder="' . esc_attr__('Search name, reg no, UID', 'result-management-system-for-institutions') . '"></div>';
		echo '<div class="filter-group"><label>Status</label>';
		echo '<select name="student_status" class="premium-input">';
		echo '<option value="">' . esc_html__('All Statuses', 'result-management-system-for-institutions') . '</option>';
		echo '<option value="active" ' . selected($status, 'active', false) . '>' . esc_html__('Active', 'result-management-system-for-institutions') . '</option>';
		echo '<option value="inactive" ' . selected($status, 'inactive', false) . '>' . esc_html__('Inactive', 'result-management-system-for-institutions') . '</option>';
		echo '</select></div>';
		$this->render_filter_select('student_class_id', $class_id, $this->get_class_options(), __('Class', 'result-management-system-for-institutions'), true);
		$this->render_filter_select('student_department_id', $department_id, $this->get_department_options(), __('Department', 'result-management-system-for-institutions'), true);
		$this->render_filter_select('student_group_id', $group_id, $this->get_section_options(), __('Group', 'result-management-system-for-institutions'), true);
		echo '<div class="filter-actions">';
		submit_button(__('Filter', 'result-management-system-for-institutions'), 'secondary', '', false, array('class' => 'hero-btn primary-btn', 'style' => 'margin:0;'));
		$reset_url = add_query_arg(array('page' => 'astgdrmsys-student-database', 'tab' => 'students'), admin_url('admin.php'));
		echo '<a class="hero-btn secondary-btn" style="background:#e2e8f0;color:#334155;border:none;margin:0;" href="' . esc_url($reset_url) . '">' . esc_html__('Reset', 'result-management-system-for-institutions') . '</a>';
		echo '</div>';
		echo '</form>';

		if ($total > 0) {
			echo '<form method="post" style="margin:12px 0;" onsubmit="return astgdrmsysStudentDeleteConfirm(this, \'' . esc_js(__('Type confirm to delete all student records.', 'result-management-system-for-institutions')) . '\');">';
			wp_nonce_field('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
			echo '<input type="hidden" name="astgdrmsys_delete_confirm" value="">';
			submit_button(__('Delete All Student Records', 'result-management-system-for-institutions'), 'delete', 'astgdrmsys_student_db_delete_all', false);
			echo '</form>';
		}

		echo '<table class="styled-table">';
		echo '<thead><tr>';
		echo '<th>' . esc_html__('UID', 'result-management-system-for-institutions') . '</th>';
		echo '<th>' . esc_html__('Name', 'result-management-system-for-institutions') . '</th>';
		echo '<th>' . esc_html__('Registration', 'result-management-system-for-institutions') . '</th>';
		echo '<th>' . esc_html__('Current Class', 'result-management-system-for-institutions') . '</th>';
		echo '<th>' . esc_html__('Roll', 'result-management-system-for-institutions') . '</th>';
		echo '<th>' . esc_html__('Status', 'result-management-system-for-institutions') . '</th>';
		echo '<th>' . esc_html__('Actions', 'result-management-system-for-institutions') . '</th>';
		echo '</tr></thead><tbody>';

		if (empty($students)) {
			echo '<tr><td colspan="7">' . esc_html__('No students found.', 'result-management-system-for-institutions') . '</td></tr>';
		}

		foreach ($students as $student) {
			$edit_url = add_query_arg(array('page' => 'astgdrmsys-student-database', 'tab' => 'add', 'student_id' => (int) $student['id']), admin_url('admin.php'));
			$status_action = $student['status'] === 'active' ? 'deactivate' : 'reactivate';
			$status_label  = $student['status'] === 'active' ? __('Deactivate', 'result-management-system-for-institutions') : __('Reactivate', 'result-management-system-for-institutions');
			$status_url    = wp_nonce_url(
				add_query_arg(array('page' => 'astgdrmsys-student-database', 'student_action' => $status_action, 'student_id' => (int) $student['id']), admin_url('admin.php')),
				'astgdrmsys_student_status_' . (int) $student['id']
			);

			echo '<tr>';
			echo '<td>' . esc_html($student['student_uid']) . '</td>';
			echo '<td><strong>' . esc_html($student['name']) . '</strong><br><span class="description">' . esc_html($student['fname']) . '</span></td>';
			echo '<td>' . esc_html($student['regno']) . '</td>';
			echo '<td>' . esc_html($this->format_enrollment_label($student)) . '</td>';
			echo '<td>' . esc_html($student['roll_no']) . '</td>';
			echo '<td>' . esc_html(ucfirst((string) $student['status'])) . '</td>';
			echo '<td><a class="button button-small" href="' . esc_url($edit_url) . '">' . esc_html__('Edit', 'result-management-system-for-institutions') . '</a> ';
			echo '<a class="button button-small" href="' . esc_url($status_url) . '">' . esc_html($status_label) . '</a> ';
			echo '<form method="post" style="display:inline;" onsubmit="return astgdrmsysStudentDeleteConfirm(this, \'' . esc_js(sprintf(__('Type confirm to delete %s.', 'result-management-system-for-institutions'), (string) $student['name'])) . '\');">';
			wp_nonce_field('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
			echo '<input type="hidden" name="student_id" value="' . esc_attr((int) $student['id']) . '">';
			echo '<input type="hidden" name="astgdrmsys_delete_confirm" value="">';
			submit_button(__('Delete', 'result-management-system-for-institutions'), 'small delete', 'astgdrmsys_student_db_delete', false);
			echo '</form></td>';
			echo '</tr>';
		}

		echo '</tbody></table>';
		echo '<script>
function astgdrmsysStudentDeleteConfirm(form, message) {
	var response = window.prompt(message);
	if (response === null) {
		return false;
	}
	response = response.trim().toLowerCase();
	if (response !== "confirm") {
		window.alert("' . esc_js(__('Delete cancelled. You must type confirm.', 'result-management-system-for-institutions')) . '");
		return false;
	}
	var field = form.querySelector("input[name=\"astgdrmsys_delete_confirm\"]");
	if (field) {
		field.value = response;
	}
	return true;
}
</script>';
		$this->render_pagination($page, $total, array(
			'tab'                   => 'students',
			'student_search'        => $search,
			'student_status'        => $status,
			'student_class_id'      => $class_id,
			'student_department_id' => $department_id,
			'student_group_id'      => $group_id,
		));
	}

	private function render_student_form(): void
	{
		$student_id = isset($_GET['student_id']) ? absint(wp_unslash($_GET['student_id'])) : 0;
		$student = $student_id > 0 ? $this->get_student($student_id) : array();
		$enrollment = $student_id > 0 ? $this->get_latest_enrollment($student_id) : array();

		echo '<div class="astgdrmsys-premium-wrap" style="padding-bottom:0; min-height:auto;">';
		echo '<div class="premium-page-header"><div class="page-header-text"><h1>' . esc_html($student_id > 0 ? __('Edit Student', 'result-management-system-for-institutions') : __('Add Student', 'result-management-system-for-institutions')) . '</h1></div></div>';
		echo '<form method="post" style="margin-top:20px;">';
		wp_nonce_field('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
		echo '<input type="hidden" name="student_id" value="' . esc_attr($student_id) . '">';
		echo '<table class="form-table"><tbody>';
		$this->render_input_row(__('Registration No', 'result-management-system-for-institutions'), 'regno', $student['regno'] ?? '');
		$this->render_input_row(__('Name', 'result-management-system-for-institutions'), 'name', $student['name'] ?? '', true);
		$this->render_select_row(__('Gender', 'result-management-system-for-institutions'), 'gender', $student['gender'] ?? '', array('MALE' => 'MALE', 'FEMALE' => 'FEMALE', 'OTHERS' => 'OTHERS'));
		$this->render_input_row(__('Father\'s Name', 'result-management-system-for-institutions'), 'fname', $student['fname'] ?? '');
		$this->render_input_row(__('Mother\'s Name', 'result-management-system-for-institutions'), 'mname', $student['mname'] ?? '');
		$this->render_input_row(__('Student Image URL', 'result-management-system-for-institutions'), 'student_image', $student['student_image'] ?? '');
		$this->render_select_row(__('Status', 'result-management-system-for-institutions'), 'status', $student['status'] ?? 'active', array('active' => __('Active', 'result-management-system-for-institutions'), 'inactive' => __('Inactive', 'result-management-system-for-institutions')));
		echo '<tr><th colspan="2"><h3>' . esc_html__('Current Enrollment', 'result-management-system-for-institutions') . '</h3></th></tr>';
		$this->render_select_row(__('Exam Year', 'result-management-system-for-institutions'), 'exam_year_id', $enrollment['exam_year_id'] ?? '', $this->get_exam_year_options());
		$this->render_select_row(__('Class', 'result-management-system-for-institutions'), 'class_id', $enrollment['class_id'] ?? '', $this->get_class_options());
		$this->render_select_row(__('Department', 'result-management-system-for-institutions'), 'department_id', $enrollment['department_id'] ?? '', $this->get_department_options());
		$this->render_section_select_row((int) ($enrollment['section_id'] ?? 0));
		$this->render_input_row(__('Roll Number', 'result-management-system-for-institutions'), 'roll_no', $enrollment['roll_no'] ?? '');
		echo '<tr><th><label for="custom_fields">' . esc_html__('Custom Fields', 'result-management-system-for-institutions') . '</label></th><td><textarea name="custom_fields" id="custom_fields" rows="6" class="large-text" placeholder="' . esc_attr__("Birthdate: 2010-04-15\nPermanent Address: 12 School Road\nZip Code: 1207", 'result-management-system-for-institutions') . '">' . esc_textarea($this->format_custom_fields_for_edit($student['custom_fields'] ?? '')) . '</textarea><p class="description">' . esc_html__('Use one custom field per line in “Field Name: Field Value” format. Examples: Birthdate, Permanent Address, Zip Code, Blood Group. These values are copied into future exam assignments, while old published results keep their saved snapshot.', 'result-management-system-for-institutions') . '</p></td></tr>';
		echo '</tbody></table>';
		submit_button(__('Save Student', 'result-management-system-for-institutions'), 'primary', 'astgdrmsys_student_db_save', true, array('class' => 'hero-btn primary-btn', 'style' => 'border:none;'));
		echo '</form></div>';
	}

	private function render_import_export(): void
	{
		echo '<div class="astgdrmsys-premium-wrap" style="padding-bottom:0; min-height:auto;">';
		echo '<div class="premium-page-header"><div class="page-header-text"><h1>' . esc_html__('Import Students', 'result-management-system-for-institutions') . '</h1></div></div>';
		echo '<p style="margin-top:15px;">' . esc_html__('CSV headers: regno,name,gender,fname,mname,class_id,department_id,section_id,exam_year_id,roll_no,status,student_image,custom_fields', 'result-management-system-for-institutions') . '</p>';
		echo '<p class="description" style="margin-bottom:15px;">' . esc_html__('For custom fields, either add one custom_fields column using “Birthdate: 2010-04-15; Permanent Address: 12 School Road; Zip Code: 1207”, or add separate columns such as birthdate, permanent_address, zip_code, blood_group. Any non-standard CSV column with a value is saved as a custom field.', 'result-management-system-for-institutions') . '</p>';
		echo '<form method="post" enctype="multipart/form-data" style="margin-bottom:30px;">';
		wp_nonce_field('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
		echo '<input type="file" name="student_csv" accept=".csv" required style="margin-right:10px;"> ';
		submit_button(__('Import CSV', 'result-management-system-for-institutions'), 'primary', 'astgdrmsys_student_db_import', false, array('class' => 'hero-btn primary-btn', 'style' => 'border:none;'));
		echo '</form>';

		echo '<div class="premium-page-header" style="margin-top:40px;"><div class="page-header-text"><h1>' . esc_html__('Export Students', 'result-management-system-for-institutions') . '</h1></div></div>';
		echo '<form method="post" style="margin-top:15px;">';
		wp_nonce_field('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
		submit_button(__('Export CSV', 'result-management-system-for-institutions'), 'secondary', 'astgdrmsys_student_db_export', false, array('class' => 'hero-btn secondary-btn', 'style' => 'border:none;'));
		echo '</form></div>';
	}

	private function render_promotion(): void
	{
		$source_exam_year = isset($_GET['source_exam_year_id']) ? absint(wp_unslash($_GET['source_exam_year_id'])) : 0;
		$source_class     = isset($_GET['source_class_id']) ? absint(wp_unslash($_GET['source_class_id'])) : 0;
		$source_dept      = isset($_GET['source_department_id']) ? absint(wp_unslash($_GET['source_department_id'])) : 0;
		$source_section   = isset($_GET['source_section_id']) ? absint(wp_unslash($_GET['source_section_id'])) : 0;

		echo '<div class="astgdrmsys-premium-wrap" style="padding-bottom:0; min-height:auto;">';
		echo '<div class="premium-page-header"><div class="page-header-text"><h1>' . esc_html__('Promote Students', 'result-management-system-for-institutions') . '</h1></div></div>';
		echo '<form method="get" class="astgdrmsys-filter-form premium-filter-form" style="margin-top:20px;">';
		echo '<input type="hidden" name="page" value="astgdrmsys-student-database"><input type="hidden" name="tab" value="promote">';
		$this->render_filter_select('source_exam_year_id', $source_exam_year, $this->get_exam_year_options(), __('Source Year', 'result-management-system-for-institutions'));
		$this->render_filter_select('source_class_id', $source_class, $this->get_class_options(), __('Source Class', 'result-management-system-for-institutions'));
		$this->render_filter_select('source_department_id', $source_dept, $this->get_department_options(), __('Source Department', 'result-management-system-for-institutions'));
		$this->render_filter_select('source_section_id', $source_section, $this->get_section_options(), __('Source Section', 'result-management-system-for-institutions'), true);
		echo '<div class="filter-actions">';
		submit_button(__('Load Students', 'result-management-system-for-institutions'), 'secondary', '', false, array('class' => 'hero-btn primary-btn', 'style' => 'border:none; margin:0;'));
		echo '</div></form>';

		if ($source_exam_year <= 0 || $source_class <= 0 || $source_dept <= 0) {
			echo '<p style="margin-top:15px;">' . esc_html__('Select a source year, class, and department to load students for promotion.', 'result-management-system-for-institutions') . '</p></div>';
			return;
		}

		$students = $this->get_enrollments_for_promotion($source_exam_year, $source_class, $source_dept, $source_section);

		echo '<form method="post" style="margin-top:30px;">';
		wp_nonce_field('astgdrmsys_student_db_action', 'astgdrmsys_student_db_nonce');
		echo '<div class="premium-page-header"><div class="page-header-text"><h1>' . esc_html__('Target Placement', 'result-management-system-for-institutions') . '</h1></div></div>';
		echo '<table class="form-table"><tbody>';
		$this->render_select_row(__('Target Exam Year', 'result-management-system-for-institutions'), 'target_exam_year_id', '', $this->get_exam_year_options());
		$this->render_select_row(__('Target Class', 'result-management-system-for-institutions'), 'target_class_id', '', $this->get_class_options());
		$this->render_select_row(__('Target Department', 'result-management-system-for-institutions'), 'target_department_id', '', $this->get_department_options());
		$this->render_select_row(__('Target Section', 'result-management-system-for-institutions'), 'target_section_id', '', $this->get_section_options(), false);
		echo '</tbody></table>';

		echo '<table class="styled-table"><thead><tr><th><input type="checkbox" onclick="jQuery(\'.astgdrmsys-promote-check\').prop(\'checked\', this.checked);"></th><th>' . esc_html__('Name', 'result-management-system-for-institutions') . '</th><th>' . esc_html__('Reg No', 'result-management-system-for-institutions') . '</th><th>' . esc_html__('Current Roll', 'result-management-system-for-institutions') . '</th><th>' . esc_html__('New Roll', 'result-management-system-for-institutions') . '</th></tr></thead><tbody>';

		if (empty($students)) {
			echo '<tr><td colspan="5">' . esc_html__('No active students found for this source placement.', 'result-management-system-for-institutions') . '</td></tr>';
		}

		foreach ($students as $student) {
			$id = (int) $student['enrollment_id'];
			echo '<tr>';
			echo '<td><input class="astgdrmsys-promote-check" type="checkbox" name="promote_enrollment_ids[]" value="' . esc_attr($id) . '"></td>';
			echo '<td>' . esc_html($student['name']) . '</td>';
			echo '<td>' . esc_html($student['regno']) . '</td>';
			echo '<td>' . esc_html($student['roll_no']) . '</td>';
			echo '<td><input type="text" name="new_roll[' . esc_attr($id) . ']" value="' . esc_attr($student['roll_no']) . '"></td>';
			echo '</tr>';
		}

		echo '</tbody></table>';
		submit_button(__('Promote Selected Students', 'result-management-system-for-institutions'), 'primary', 'astgdrmsys_student_db_promote', true, array('class' => 'hero-btn primary-btn', 'style' => 'border:none;'));
		echo '</form></div>';
	}

	private function save_student(): void
	{
		$student_id = isset($_POST['student_id']) ? absint(wp_unslash($_POST['student_id'])) : 0;
		$name       = isset($_POST['name']) ? sanitize_text_field(wp_unslash($_POST['name'])) : '';

		if ($name === '') {
			wp_die(esc_html__('Student name is required.', 'result-management-system-for-institutions'));
		}

		$data = array(
			'regno'         => isset($_POST['regno']) && trim((string) $_POST['regno']) !== '' ? sanitize_text_field(wp_unslash($_POST['regno'])) : null,
			'name'          => $name,
			'gender'        => isset($_POST['gender']) ? sanitize_text_field(wp_unslash($_POST['gender'])) : '',
			'fname'         => isset($_POST['fname']) ? sanitize_text_field(wp_unslash($_POST['fname'])) : '',
			'mname'         => isset($_POST['mname']) ? sanitize_text_field(wp_unslash($_POST['mname'])) : '',
			'student_image' => isset($_POST['student_image']) && trim((string) $_POST['student_image']) !== '' ? esc_url_raw(wp_unslash($_POST['student_image'])) : null,
			'custom_fields' => isset($_POST['custom_fields']) ? $this->normalize_custom_fields_text(wp_unslash($_POST['custom_fields'])) : null,
			'status'        => isset($_POST['status']) && $_POST['status'] === 'inactive' ? 'inactive' : 'active',
			'updated_at'    => current_time('mysql'),
		);

		if ($student_id > 0) {
			$this->wpdb->update($this->students_table, $data, array('id' => $student_id));
		} else {
			$data['student_uid'] = $this->generate_student_uid();
			$data['created_at']  = current_time('mysql');
			$this->wpdb->insert($this->students_table, $data);
			$student_id = (int) $this->wpdb->insert_id;
		}

		$this->upsert_enrollment_from_request($student_id);

		wp_safe_redirect(add_query_arg(array('page' => 'astgdrmsys-student-database', 'message' => 'saved'), admin_url('admin.php')));
		exit;
	}

	private function delete_student(): void
	{
		if (! $this->is_delete_confirmed()) {
			$this->redirect_students_list('confirm_failed');
		}

		$student_id = isset($_POST['student_id']) ? absint(wp_unslash($_POST['student_id'])) : 0;
		if ($student_id <= 0) {
			$this->redirect_students_list('delete_failed');
		}

		$deleted = $this->delete_students_by_ids(array($student_id));
		$this->redirect_students_list($deleted ? 'deleted' : 'delete_failed');
	}

	private function delete_all_students(): void
	{
		if (! $this->is_delete_confirmed()) {
			$this->redirect_students_list('confirm_failed');
		}

		$deleted = $this->delete_all_student_records();
		$this->redirect_students_list($deleted ? 'all_deleted' : 'delete_failed');
	}

	private function is_delete_confirmed(): bool
	{
		$confirmation = isset($_POST['astgdrmsys_delete_confirm']) ? sanitize_text_field(wp_unslash($_POST['astgdrmsys_delete_confirm'])) : '';
		return strtolower(trim($confirmation)) === 'confirm';
	}

	private function redirect_students_list(string $message): void
	{
		wp_safe_redirect(add_query_arg(array('page' => 'astgdrmsys-student-database', 'tab' => 'students', 'message' => $message), admin_url('admin.php')));
		exit;
	}

	private function delete_students_by_ids(array $student_ids): bool
	{
		$student_ids = array_values(array_filter(array_map('absint', $student_ids)));
		if (empty($student_ids)) {
			return false;
		}

		$result_ids = $this->get_result_ids_for_students($student_ids);
		$this->wpdb->query('START TRANSACTION');

		if (! empty($result_ids) && ! $this->delete_marks_for_result_ids($result_ids)) {
			$this->wpdb->query('ROLLBACK');
			return false;
		}

		if (! $this->delete_result_rows_for_students($student_ids)) {
			$this->wpdb->query('ROLLBACK');
			return false;
		}

		if ($this->delete_from_table_for_students($this->enrollments_table, $student_ids) === false) {
			$this->wpdb->query('ROLLBACK');
			return false;
		}

		if ($this->delete_from_table_for_students($this->students_table, $student_ids, 'id') === false) {
			$this->wpdb->query('ROLLBACK');
			return false;
		}

		$this->wpdb->query('COMMIT');
		return true;
	}

	private function delete_all_student_records(): bool
	{
		$this->wpdb->query('START TRANSACTION');

		if ($this->wpdb->query("DELETE m FROM {$this->marks_table} m INNER JOIN {$this->student_result_table} r ON r.sid = m.sid") === false) {
			$this->wpdb->query('ROLLBACK');
			return false;
		}

		foreach (array($this->student_result_table, $this->enrollments_table, $this->students_table) as $table) {
			if ($this->wpdb->query("DELETE FROM {$table}") === false) {
				$this->wpdb->query('ROLLBACK');
				return false;
			}
		}

		$this->wpdb->query('COMMIT');
		return true;
	}

	private function get_result_ids_for_students(array $student_ids): array
	{
		$placeholders = implode(',', array_fill(0, count($student_ids), '%d'));
		$sql = "SELECT sid FROM {$this->student_result_table} WHERE student_id IN ({$placeholders})";
		$result_ids = $this->wpdb->get_col($this->wpdb->prepare($sql, $student_ids));

		return array_values(array_filter(array_map('absint', is_array($result_ids) ? $result_ids : array())));
	}

	private function delete_marks_for_result_ids(array $result_ids): bool
	{
		$placeholders = implode(',', array_fill(0, count($result_ids), '%d'));
		$sql = "DELETE FROM {$this->marks_table} WHERE sid IN ({$placeholders})";

		return $this->wpdb->query($this->wpdb->prepare($sql, $result_ids)) !== false;
	}

	private function delete_result_rows_for_students(array $student_ids): bool
	{
		$placeholders = implode(',', array_fill(0, count($student_ids), '%d'));
		$sql = "DELETE FROM {$this->student_result_table} WHERE student_id IN ({$placeholders})";

		return $this->wpdb->query($this->wpdb->prepare($sql, $student_ids)) !== false;
	}

	private function delete_from_table_for_students(string $table, array $student_ids, string $column = 'student_id')
	{
		$column = esc_sql($column);
		$placeholders = implode(',', array_fill(0, count($student_ids), '%d'));
		$sql = "DELETE FROM {$table} WHERE {$column} IN ({$placeholders})";

		return $this->wpdb->query($this->wpdb->prepare($sql, $student_ids));
	}

	private function upsert_enrollment_from_request(int $student_id): void
	{
		$exam_year_id  = isset($_POST['exam_year_id']) ? absint(wp_unslash($_POST['exam_year_id'])) : 0;
		$class_id      = isset($_POST['class_id']) ? absint(wp_unslash($_POST['class_id'])) : 0;
		$department_id = isset($_POST['department_id']) ? absint(wp_unslash($_POST['department_id'])) : 0;
		$section_id    = isset($_POST['section_id']) ? absint(wp_unslash($_POST['section_id'])) : 0;
		$roll_no       = isset($_POST['roll_no']) ? sanitize_text_field(wp_unslash($_POST['roll_no'])) : '';

		if ($student_id <= 0 || $exam_year_id <= 0 || $class_id <= 0 || $department_id <= 0) {
			return;
		}

		$existing_id = (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->enrollments_table} WHERE student_id = %d AND exam_year_id = %d LIMIT 1",
				$student_id,
				$exam_year_id
			)
		);

		$data = array(
			'student_id'    => $student_id,
			'exam_year_id'  => $exam_year_id,
			'class_id'      => $class_id,
			'department_id' => $department_id,
			'section_id'    => $section_id,
			'roll_no'       => $roll_no,
			'status'        => 'active',
			'updated_at'    => current_time('mysql'),
		);

		if ($existing_id > 0) {
			$this->wpdb->update($this->enrollments_table, $data, array('id' => $existing_id));
		} else {
			$data['created_at'] = current_time('mysql');
			$this->wpdb->insert($this->enrollments_table, $data);
		}
	}

	private function import_students(): void
	{
		if (empty($_FILES['student_csv']['tmp_name']) || ! is_uploaded_file($_FILES['student_csv']['tmp_name'])) {
			wp_die(esc_html__('Please upload a valid CSV file.', 'result-management-system-for-institutions'));
		}

		$handle = fopen($_FILES['student_csv']['tmp_name'], 'r');
		if ($handle === false) {
			wp_die(esc_html__('Unable to open uploaded CSV file.', 'result-management-system-for-institutions'));
		}

		$headers = fgetcsv($handle);
		if ($headers === false) {
			fclose($handle);
			wp_die(esc_html__('The CSV file is empty.', 'result-management-system-for-institutions'));
		}

		$headers = array_map(static function ($header) {
			return strtolower(trim((string) $header));
		}, $headers);

		while (($row = fgetcsv($handle)) !== false) {
			$data = array_combine($headers, $row);
			if (! is_array($data) || empty(array_filter($data))) {
				continue;
			}

			$name = isset($data['name']) ? sanitize_text_field($data['name']) : '';
			if ($name === '') {
				continue;
			}

			$regno = isset($data['regno']) ? sanitize_text_field($data['regno']) : '';
			$student_id = 0;

			if ($regno !== '') {
				$student_id = (int) $this->wpdb->get_var($this->wpdb->prepare("SELECT id FROM {$this->students_table} WHERE regno = %s LIMIT 1", $regno));
			}

			$student_data = array(
				'regno'         => $regno !== '' ? $regno : null,
				'name'          => $name,
				'gender'        => isset($data['gender']) ? sanitize_text_field($data['gender']) : '',
				'fname'         => isset($data['fname']) ? sanitize_text_field($data['fname']) : '',
				'mname'         => isset($data['mname']) ? sanitize_text_field($data['mname']) : '',
				'student_image' => ! empty($data['student_image']) ? esc_url_raw($data['student_image']) : null,
				'custom_fields' => $this->build_custom_fields_from_csv_row($data),
				'status'        => isset($data['status']) && $data['status'] === 'inactive' ? 'inactive' : 'active',
				'updated_at'    => current_time('mysql'),
			);

			if ($student_id > 0) {
				$this->wpdb->update($this->students_table, $student_data, array('id' => $student_id));
			} else {
				$student_data['student_uid'] = $this->generate_student_uid();
				$student_data['created_at'] = current_time('mysql');
				$this->wpdb->insert($this->students_table, $student_data);
				$student_id = (int) $this->wpdb->insert_id;
			}

			$this->upsert_enrollment(
				$student_id,
				isset($data['exam_year_id']) ? (int) $data['exam_year_id'] : 0,
				isset($data['class_id']) ? (int) $data['class_id'] : 0,
				isset($data['department_id']) ? (int) $data['department_id'] : 0,
				isset($data['section_id']) ? (int) $data['section_id'] : 0,
				isset($data['roll_no']) ? sanitize_text_field($data['roll_no']) : ''
			);
		}

		fclose($handle);
		wp_safe_redirect(add_query_arg(array('page' => 'astgdrmsys-student-database', 'tab' => 'import', 'message' => 'imported'), admin_url('admin.php')));
		exit;
	}

	private function export_students(): void
	{
		if (headers_sent()) {
			wp_die(esc_html__('Cannot export CSV because headers have already been sent.', 'result-management-system-for-institutions'));
		}

		if (ob_get_level() > 0) {
			while (ob_get_level() > 0) {
				ob_end_clean();
			}
		}

		nocache_headers();
		header('Content-Type: text/csv; charset=utf-8');
		header('Content-Disposition: attachment; filename=astgdrmsys-student-database.csv');

		$output = fopen('php://output', 'w');
		fputcsv($output, array('student_uid', 'regno', 'name', 'gender', 'fname', 'mname', 'class_id', 'department_id', 'section_id', 'exam_year_id', 'roll_no', 'status', 'student_image', 'custom_fields'));

		$rows = $this->wpdb->get_results(
			"SELECT s.student_uid, s.regno, s.name, s.gender, s.fname, s.mname, e.class_id, e.department_id, e.section_id, e.exam_year_id, e.roll_no, s.status, s.student_image, s.custom_fields
			FROM {$this->students_table} s
			LEFT JOIN {$this->enrollments_table} e ON e.id = (
				SELECT e2.id
				FROM {$this->enrollments_table} e2
				WHERE e2.student_id = s.id AND e2.status = 'active'
				ORDER BY e2.exam_year_id DESC, e2.id DESC
				LIMIT 1
			)
			ORDER BY s.name ASC",
			ARRAY_A
		);

		foreach ($rows as $row) {
			fputcsv($output, $row);
		}

		fclose($output);
		exit;
	}

	/**
	 * Converts saved custom fields into the admin-friendly one-field-per-line format.
	 */
	private function format_custom_fields_for_edit($value): string
	{
		if (! is_string($value) || trim($value) === '') {
			return '';
		}

		$decoded = json_decode($value, true);
		if (is_array($decoded)) {
			$lines = array();
			foreach ($decoded as $item) {
				if (is_array($item) && isset($item['name'], $item['value'])) {
					$lines[] = sanitize_text_field((string) $item['name']) . ': ' . sanitize_text_field((string) $item['value']);
				}
			}

			if (! empty($lines)) {
				return implode("\n", $lines);
			}
		}

		return $value;
	}

	/**
	 * Normalizes textarea custom fields to the JSON shape used by result snapshots.
	 */
	private function normalize_custom_fields_text($value): ?string
	{
		$value = is_string($value) ? trim($value) : '';
		if ($value === '') {
			return null;
		}

		$decoded = json_decode($value, true);
		if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
			return wp_json_encode($decoded);
		}

		$fields = array();
		$lines = preg_split('/\r\n|\r|\n|;/', $value);
		foreach ($lines as $line) {
			$line = trim((string) $line);
			if ($line === '') {
				continue;
			}

			$parts = explode(':', $line, 2);
			if (count($parts) !== 2) {
				continue;
			}

			$name = sanitize_text_field(trim($parts[0]));
			$field_value = sanitize_text_field(trim($parts[1]));

			if ($name !== '' && $field_value !== '') {
				$fields[] = array(
					'name'  => $name,
					'value' => $field_value,
				);
			}
		}

		return ! empty($fields) ? wp_json_encode($fields) : null;
	}

	/**
	 * Builds custom fields from either a custom_fields CSV column or extra custom columns.
	 *
	 * @param array<string, mixed> $data CSV row keyed by normalized header.
	 */
	private function build_custom_fields_from_csv_row(array $data): ?string
	{
		$core_headers = array(
			'student_uid',
			'regno',
			'name',
			'gender',
			'fname',
			'mname',
			'class_id',
			'department_id',
			'section_id',
			'exam_year_id',
			'roll_no',
			'status',
			'student_image',
			'custom_fields',
		);

		$fields = array();

		if (! empty($data['custom_fields'])) {
			$normalized = $this->normalize_custom_fields_text((string) $data['custom_fields']);
			$decoded = $normalized ? json_decode($normalized, true) : array();
			if (is_array($decoded)) {
				foreach ($decoded as $item) {
					if (is_array($item) && isset($item['name'], $item['value'])) {
						$fields[] = array(
							'name'  => sanitize_text_field((string) $item['name']),
							'value' => sanitize_text_field((string) $item['value']),
						);
					}
				}
			}
		}

		foreach ($data as $header => $value) {
			$header = strtolower(trim((string) $header));
			$value = trim((string) $value);

			if ($value === '' || in_array($header, $core_headers, true)) {
				continue;
			}

			$fields[] = array(
				'name'  => $this->humanize_custom_field_header($header),
				'value' => sanitize_text_field($value),
			);
		}

		return ! empty($fields) ? wp_json_encode($fields) : null;
	}

	/**
	 * Converts CSV headers like permanent_address into Permanent Address.
	 */
	private function humanize_custom_field_header(string $header): string
	{
		$header = str_replace(array('-', '_'), ' ', $header);
		$header = preg_replace('/\s+/', ' ', $header);

		return ucwords(trim((string) $header));
	}

	private function promote_students(): void
	{
		$ids = isset($_POST['promote_enrollment_ids']) && is_array($_POST['promote_enrollment_ids'])
			? array_map('absint', wp_unslash($_POST['promote_enrollment_ids']))
			: array();

		$ids = array_values(array_filter($ids));
		if (empty($ids)) {
			wp_safe_redirect(add_query_arg(array('page' => 'astgdrmsys-student-database', 'tab' => 'promote', 'message' => 'none'), admin_url('admin.php')));
			exit;
		}

		$target_exam_year  = isset($_POST['target_exam_year_id']) ? absint(wp_unslash($_POST['target_exam_year_id'])) : 0;
		$target_class      = isset($_POST['target_class_id']) ? absint(wp_unslash($_POST['target_class_id'])) : 0;
		$target_department = isset($_POST['target_department_id']) ? absint(wp_unslash($_POST['target_department_id'])) : 0;
		$target_section    = isset($_POST['target_section_id']) ? absint(wp_unslash($_POST['target_section_id'])) : 0;
		$new_rolls         = isset($_POST['new_roll']) && is_array($_POST['new_roll']) ? wp_unslash($_POST['new_roll']) : array();

		if ($target_exam_year <= 0 || $target_class <= 0 || $target_department <= 0) {
			wp_die(esc_html__('Target year, class, and department are required.', 'result-management-system-for-institutions'));
		}

		foreach ($ids as $enrollment_id) {
			$source = $this->wpdb->get_row(
				$this->wpdb->prepare("SELECT * FROM {$this->enrollments_table} WHERE id = %d", $enrollment_id),
				ARRAY_A
			);

			if (! $source) {
				continue;
			}

			$roll_no = isset($new_rolls[$enrollment_id]) ? sanitize_text_field($new_rolls[$enrollment_id]) : (string) $source['roll_no'];
			$this->upsert_enrollment((int) $source['student_id'], $target_exam_year, $target_class, $target_department, $target_section, $roll_no, $enrollment_id);
		}

		wp_safe_redirect(add_query_arg(array('page' => 'astgdrmsys-student-database', 'tab' => 'promote', 'message' => 'promoted'), admin_url('admin.php')));
		exit;
	}

	private function upsert_enrollment(int $student_id, int $exam_year_id, int $class_id, int $department_id, int $section_id, string $roll_no, int $promoted_from = 0): int
	{
		if ($student_id <= 0 || $exam_year_id <= 0 || $class_id <= 0 || $department_id <= 0) {
			return 0;
		}

		$existing_id = (int) $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT id FROM {$this->enrollments_table} WHERE student_id = %d AND exam_year_id = %d LIMIT 1",
				$student_id,
				$exam_year_id
			)
		);

		$data = array(
			'student_id'     => $student_id,
			'exam_year_id'   => $exam_year_id,
			'class_id'       => $class_id,
			'department_id'  => $department_id,
			'section_id'     => $section_id,
			'roll_no'        => $roll_no,
			'status'         => 'active',
			'updated_at'     => current_time('mysql'),
		);

		if ($promoted_from > 0) {
			$data['promoted_from_enrollment_id'] = $promoted_from;
		}

		if ($existing_id > 0) {
			$this->wpdb->update($this->enrollments_table, $data, array('id' => $existing_id));
			return $existing_id;
		}

		$data['created_at'] = current_time('mysql');
		$this->wpdb->insert($this->enrollments_table, $data);
		return (int) $this->wpdb->insert_id;
	}

	private function get_student(int $student_id): array
	{
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare("SELECT * FROM {$this->students_table} WHERE id = %d", $student_id),
			ARRAY_A
		);

		return is_array($row) ? $row : array();
	}

	private function get_latest_enrollment(int $student_id): array
	{
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare("SELECT * FROM {$this->enrollments_table} WHERE student_id = %d ORDER BY exam_year_id DESC, id DESC LIMIT 1", $student_id),
			ARRAY_A
		);

		return is_array($row) ? $row : array();
	}

	private function get_enrollments_for_promotion(int $exam_year_id, int $class_id, int $department_id, int $section_id): array
	{
		$where = 'e.exam_year_id = %d AND e.class_id = %d AND e.department_id = %d AND e.status = %s AND s.status = %s';
		$params = array($exam_year_id, $class_id, $department_id, 'active', 'active');

		if ($section_id > 0) {
			$where .= ' AND e.section_id = %d';
			$params[] = $section_id;
		}

		$sql = "SELECT e.id AS enrollment_id, e.roll_no, s.name, s.regno
			FROM {$this->enrollments_table} e
			INNER JOIN {$this->students_table} s ON s.id = e.student_id
			WHERE {$where}
			ORDER BY CAST(e.roll_no AS UNSIGNED), s.name ASC";

		$rows = $this->wpdb->get_results($this->wpdb->prepare($sql, $params), ARRAY_A);
		return is_array($rows) ? $rows : array();
	}

	private function render_input_row(string $label, string $name, $value, bool $required = false): void
	{
		echo '<tr><th><label for="' . esc_attr($name) . '">' . esc_html($label) . '</label></th><td><input type="text" class="regular-text premium-input" id="' . esc_attr($name) . '" name="' . esc_attr($name) . '" value="' . esc_attr((string) $value) . '"' . ($required ? ' required' : '') . '></td></tr>';
	}

	private function render_select_row(string $label, string $name, $selected, array $options, bool $required = true): void
	{
		echo '<tr><th><label for="' . esc_attr($name) . '">' . esc_html($label) . '</label></th><td><select class="premium-input" id="' . esc_attr($name) . '" name="' . esc_attr($name) . '"' . ($required ? ' required' : '') . '>';
		echo '<option value="">' . esc_html__('Select', 'result-management-system-for-institutions') . '</option>';
		foreach ($options as $value => $option_label) {
			echo '<option value="' . esc_attr((string) $value) . '" ' . selected((string) $selected, (string) $value, false) . '>' . esc_html($option_label) . '</option>';
		}
		echo '</select></td></tr>';
	}

	private function render_section_select_row(int $selected): void
	{
		$sections = $this->get_section_option_rows();
		$classes  = $this->get_class_options();

		echo '<tr><th><label for="section_id">' . esc_html__('Section', 'result-management-system-for-institutions') . '</label></th><td><select class="premium-input" id="section_id" name="section_id">';
		echo '<option value="">' . esc_html__('Select class and department first', 'result-management-system-for-institutions') . '</option>';

		foreach ($sections as $section) {
			$class_name = isset($classes[(int) $section['class_id']]) ? $classes[(int) $section['class_id']] : '';
			echo '<option value="' . esc_attr((string) $section['id']) . '" data-department-id="' . esc_attr((string) $section['department_id']) . '" data-class-id="' . esc_attr((string) $section['class_id']) . '" ' . selected($selected, (int) $section['id'], false) . '>' . esc_html($section['section'] . ($class_name !== '' ? ' - ' . $class_name : '')) . '</option>';
		}

		echo '</select><p class="description">' . esc_html__('Only sections matching the selected class and department are shown.', 'result-management-system-for-institutions') . '</p>';
		echo '<script>
(function() {
	var classSelect = document.getElementById("class_id");
	var departmentSelect = document.getElementById("department_id");
	var sectionSelect = document.getElementById("section_id");

	if (!classSelect || !departmentSelect || !sectionSelect) {
		return;
	}

	function filterSections() {
		var selectedClass = classSelect.value;
		var selectedDepartment = departmentSelect.value;
		var hasSelection = selectedClass !== "" && selectedDepartment !== "";
		var hasVisibleSelected = false;

		Array.prototype.forEach.call(sectionSelect.options, function(option, index) {
			if (index === 0) {
				option.hidden = false;
				option.disabled = false;
				return;
			}

			var matches = hasSelection && option.getAttribute("data-class-id") === selectedClass && option.getAttribute("data-department-id") === selectedDepartment;
			option.hidden = !matches;
			option.disabled = !matches;

			if (matches && option.selected) {
				hasVisibleSelected = true;
			}
		});

		if (!hasVisibleSelected) {
			sectionSelect.value = "";
		}
	}

	classSelect.addEventListener("change", filterSections);
	departmentSelect.addEventListener("change", filterSections);
	filterSections();
})();
</script></td></tr>';
	}

	private function render_filter_select(string $name, int $selected, array $options, string $label, bool $allow_empty = false): void
	{
		echo '<div class="filter-group"><label>' . esc_html($label) . '</label>';
		echo '<select name="' . esc_attr($name) . '" class="premium-input">';
		echo '<option value="">' . esc_html($allow_empty ? __('All', 'result-management-system-for-institutions') : __('Select', 'result-management-system-for-institutions')) . '</option>';
		foreach ($options as $value => $option_label) {
			echo '<option value="' . esc_attr((string) $value) . '" ' . selected($selected, (int) $value, false) . '>' . esc_html($option_label) . '</option>';
		}
		echo '</select></div>';
	}

	private function render_pagination(int $page, int $total, array $args): void
	{
		$total_pages = max(1, (int) ceil($total / $this->limit));
		if ($total_pages <= 1) {
			return;
		}

		echo '<p class="tablenav-pages">';
		for ($i = 1; $i <= $total_pages; $i++) {
			$url = add_query_arg(array_merge(array('page' => 'astgdrmsys-student-database', 'paginate' => $i), $args), admin_url('admin.php'));
			if ($i === $page) {
				echo '<span class="button button-primary" style="margin-right:4px;">' . esc_html((string) $i) . '</span>';
			} else {
				echo '<a class="button" style="margin-right:4px;" href="' . esc_url($url) . '">' . esc_html((string) $i) . '</a>';
			}
		}
		echo '</p>';
	}

	private function get_exam_year_options(): array
	{
		return $this->get_options_from_table('astgdrmsys_exam_year', 'exam_year');
	}

	private function get_class_options(): array
	{
		return $this->get_options_from_table('astgdrmsys_class', 'class');
	}

	private function get_department_options(): array
	{
		return $this->get_options_from_table('astgdrmsys_department', 'department');
	}

	private function get_section_options(): array
	{
		$options = array();
		foreach ($this->get_section_option_rows() as $section) {
			$options[(int) $section['id']] = (string) $section['section'];
		}

		return $options;
	}

	private function get_section_option_rows(): array
	{
		$section_table    = esc_sql($this->prefix . 'astgdrmsys_section');
		$department_table = esc_sql($this->prefix . 'astgdrmsys_department');
		$class_table      = esc_sql($this->prefix . 'astgdrmsys_class');

		$rows = $this->wpdb->get_results(
			"SELECT s.id, s.section, CAST(s.department AS UNSIGNED) AS department_id, c.id AS class_id
			FROM `{$section_table}` s
			INNER JOIN `{$department_table}` d ON CAST(s.department AS UNSIGNED) = d.id
			INNER JOIN `{$class_table}` c ON s.classname = c.class
			ORDER BY s.section ASC, s.id ASC",
			ARRAY_A
		);

		return is_array($rows) ? $rows : array();
	}

	private function get_options_from_table(string $table, string $column): array
	{
		$table_name = esc_sql($this->prefix . $table);
		$column     = esc_sql($column);
		$rows       = $this->wpdb->get_results("SELECT id, {$column} AS label FROM `{$table_name}` ORDER BY {$column} ASC", ARRAY_A);
		$options    = array();

		if (is_array($rows)) {
			foreach ($rows as $row) {
				$options[(int) $row['id']] = (string) $row['label'];
			}
		}

		return $options;
	}

	private function format_enrollment_label(array $student): string
	{
		if (empty($student['class_id'])) {
			return '';
		}

		$classes     = $this->get_class_options();
		$departments = $this->get_department_options();
		$sections    = $this->get_section_options();
		$years       = $this->get_exam_year_options();

		$parts = array(
			$classes[(int) $student['class_id']] ?? '',
			$departments[(int) $student['department_id']] ?? '',
			! empty($student['section_id']) ? ($sections[(int) $student['section_id']] ?? '') : '',
			$years[(int) $student['exam_year_id']] ?? '',
		);

		return implode(' / ', array_filter($parts));
	}

	private function generate_student_uid(): string
	{
		do {
			$uid = 'STD-' . strtoupper(wp_generate_password(12, false, false));
			$exists = (int) $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->students_table} WHERE student_uid = %s",
					$uid
				)
			);
		} while ($exists > 0);

		return $uid;
	}
}

$astgdrmsys_student_database_manager = new ASTGDRMSYS_Student_Database_Manager($wpdb);
$astgdrmsys_student_database_manager->display();
