<?php

/**
 * Result Management System for Institutions
 *
 * Copyright (C) 2025 ASTGD
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
 */

/**
 * This file is part of the Result Management System for Institutions plugin.
 *
 * @package ASTGDRMSYS\ResultManagement
 * @namespace ASTGDRMSYS\ResultManagement
 */

namespace ASTGDRMSYS\ResultManagement;

/**
 * Ensures the file is being run within the WordPress environment.
 * If accessed directly, the script will exit.
 *
 * @package ResultManagementSystem
 * @subpackage Installation
 */

// Exit if accessed directly
if (! defined('ABSPATH')) {
	exit;
}

/**
 * Class ASTGDRMSYS_DatabaseInstaller
 *
 * This class handles the installation of the database for the Result Management System for Institutions plugin.
 * It includes methods to create necessary tables and populate initial data.
 *
 * @package ResultManagementSystem
 */
class ASTGDRMSYS_DatabaseInstaller
{

	private $wpdb;
	private $prefix;
	private $tables;

	/**
	 * Constructor for the class.
	 *
	 * This method is called when an instance of the class is created.
	 */
	public function __construct()
	{
		global $wpdb;
		$this->wpdb   = $wpdb;
		$this->prefix = $wpdb->prefix;
		$this->setup_tables();
	}

	/**
	 * Sets up the necessary database tables for the plugin.
	 *
	 * This method is responsible for creating and configuring the required
	 * database tables that the plugin will use to store its data.
	 *
	 * @return void
	 */
	private function setup_tables(): void
	{
		$this->tables = array(
			'student_result' => array(
				'name' => 'astgdrmsys_student_result',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_student_result` (
                    `sid` INT(10) NOT NULL AUTO_INCREMENT,
                    `student_id` INT(10) DEFAULT NULL,
                    `enrollment_id` INT(10) DEFAULT NULL,
                    `sno` VARCHAR(10) NOT NULL,
                    `regno` VARCHAR(30) NOT NULL,
                    `name` VARCHAR(100) NOT NULL,
                    `gender` VARCHAR(10) NOT NULL,
                    `fname` VARCHAR(100) NOT NULL,
                    `mname` VARCHAR(100) NOT NULL,
                    `remark` VARCHAR(50) NOT NULL,
                    `class` VARCHAR(50) NOT NULL,
                    `section` VARCHAR(30) NOT NULL,
                    `department` VARCHAR(30) NOT NULL,
                    `exam_name` VARCHAR(30) NOT NULL,
                    `exam_year` VARCHAR(30) NOT NULL,
                    `is_absent` TINYINT(1) NOT NULL DEFAULT 0,
                    `student_image` VARCHAR(255) DEFAULT NULL,
                    `custom_fields` TEXT NULL DEFAULT NULL,
					`rank` INT DEFAULT NULL,
                    PRIMARY KEY (`sid`),
                    KEY `student_id` (`student_id`),
                    KEY `enrollment_id` (`enrollment_id`),
                    KEY `exam_student` (`exam_name`, `student_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
            ),
			'students' => array(
				'name' => 'astgdrmsys_students',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_students` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `student_uid` VARCHAR(40) NOT NULL,
                    `regno` VARCHAR(30) DEFAULT NULL,
                    `name` VARCHAR(100) NOT NULL,
                    `gender` VARCHAR(10) NOT NULL DEFAULT '',
                    `fname` VARCHAR(100) NOT NULL DEFAULT '',
                    `mname` VARCHAR(100) NOT NULL DEFAULT '',
                    `student_image` VARCHAR(255) DEFAULT NULL,
                    `custom_fields` TEXT NULL DEFAULT NULL,
                    `wp_user_id` BIGINT(20) UNSIGNED DEFAULT NULL,
                    `status` VARCHAR(20) NOT NULL DEFAULT 'active',
                    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `student_uid` (`student_uid`),
                    KEY `regno` (`regno`),
                    KEY `status` (`status`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'student_enrollments' => array(
				'name' => 'astgdrmsys_student_enrollments',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_student_enrollments` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `student_id` INT(10) NOT NULL,
                    `exam_year_id` INT(10) NOT NULL,
                    `class_id` INT(10) NOT NULL,
                    `department_id` INT(10) NOT NULL,
                    `section_id` INT(10) NOT NULL DEFAULT 0,
                    `roll_no` VARCHAR(20) NOT NULL DEFAULT '',
                    `status` VARCHAR(20) NOT NULL DEFAULT 'active',
                    `promoted_from_enrollment_id` INT(10) DEFAULT NULL,
                    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `student_year` (`student_id`, `exam_year_id`),
                    KEY `class_lookup` (`exam_year_id`, `class_id`, `department_id`, `section_id`, `status`),
                    KEY `student_id` (`student_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'school_info'    => array(
				'name' => 'astgdrmsys_school_info',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_school_info` (
                    `sc_id` INT(10) NOT NULL AUTO_INCREMENT,
                    `sc_name` VARCHAR(100) NOT NULL,
					`sc_code` VARCHAR(30) NOT NULL,
                    `vill` VARCHAR(30) NOT NULL,
                    `pin` VARCHAR(10) NOT NULL,
                    `ps` VARCHAR(30) NOT NULL,
                    `dist` VARCHAR(30) NOT NULL,
                    `state` VARCHAR(30) NOT NULL,
                    `phone` VARCHAR(30) NOT NULL,
                    `email` VARCHAR(100) NOT NULL,
                    `website` VARCHAR(100) NOT NULL,
                    `logo` VARCHAR(100) NOT NULL,
                    PRIMARY KEY (`sc_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'general_settings' => [
                'name' => 'astgdrmsys_general_settings',
                'sql' =>  "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_general_settings` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `setting_key` VARCHAR(50) NOT NULL,
                    `setting_value` TEXT NOT NULL,
                    PRIMARY KEY (`id`),
                    UNIQUE KEY `setting_key` (`setting_key`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
            ],
			'view_settings'  => array(
				'name'         => 'astgdrmsys_view_settings',
				'sql'          => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_view_settings` (
            `vid` INT(10) NOT NULL AUTO_INCREMENT,
            `methodfield` VARCHAR(30) NOT NULL,
            `titlefield` VARCHAR(30) NOT NULL,
            `namefield` VARCHAR(50) NOT NULL,
            `genderfield` VARCHAR(30) NOT NULL,
            `fnamefield` VARCHAR(30) NOT NULL,
            `mnamefield` VARCHAR(30) NOT NULL,
            `subjectfield` VARCHAR(50) NOT NULL,
            `classfield` VARCHAR(30) NOT NULL,
            `regnofield` VARCHAR(30) NOT NULL,
            `rollfield` VARCHAR(30) NOT NULL,
            `totalfield` VARCHAR(30) NOT NULL,
            `minimumfield` VARCHAR(30) NOT NULL,
            `obtainedfield` VARCHAR(30) NOT NULL,
            `remarkfield` VARCHAR(30) NOT NULL,
            `percentage` VARCHAR(30) NOT NULL,
            `minimumpercentage` VARCHAR(30) NOT NULL,
            `finalresult` VARCHAR(30) NOT NULL,
            `principalfield` VARCHAR(30) NOT NULL,
            PRIMARY KEY (`vid`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
				'initial_data' => array(
					array(
						'vid'               => 1,
						'methodfield'       => 'Roll Number',
						'titlefield'        => 'PROGRESS REPORT',
						'namefield'         => 'Name of the Student :',
						'genderfield'       => 'Gender :',
						'fnamefield'        => 'Father\'s Name :',
						'mnamefield'        => 'Mother\'s Name :',
						'classfield'        => 'Class :',
						'regnofield'        => 'Registration Number :',
						'subjectfield'      => 'Subject :',
						'rollfield'         => 'Roll No :',
						'totalfield'        => 'Total Marks:',
						'minimumfield'      => 'Min Passing Marks:',
						'obtainedfield'     => 'Obtained Marks:',
						'remarkfield'       => 'Grade :',
						'percentage'        => 'Percentage :',
						'minimumpercentage' => 'Minimum Passing Percentage :',
						'finalresult'       => 'Remark :',
						'principalfield'    => 'Principal :',
					),
					array(
						'vid'               => 2,
						'methodfield'       => 'true',
						'titlefield'        => 'true',
						'namefield'         => 'true',
						'genderfield'       => 'true',
						'fnamefield'        => 'true',
						'mnamefield'        => 'true',
						'classfield'        => 'true',
						'regnofield'        => 'true',
						'subjectfield'      => 'true',
						'rollfield'         => 'true',
						'totalfield'        => 'true',
						'minimumfield'      => 'true',
						'obtainedfield'     => 'true',
						'remarkfield'       => 'true',
						'percentage'        => 'true',
						'minimumpercentage' => 'true',
						'finalresult'       => 'true',
						'principalfield'    => 'true',
					),
					array(
						'vid'               => 3,
						'methodfield'       => 'marksheet',
						'titlefield'        => 'watermark',
						'namefield'         => 'false',
						'genderfield'       => 'false',
						'fnamefield'        => 'false',
						'mnamefield'        => 'false',
						'classfield'        => 'false',
						'regnofield'        => 'false',
						'subjectfield'      => 'false',
						'rollfield'         => 'false',
						'totalfield'        => 'false',
						'minimumfield'      => 'false',
						'obtainedfield'     => 'false',
						'remarkfield'       => 'false',
						'percentage'        => 'false',
						'minimumpercentage' => 'false',
						'finalresult'       => 'false',
						'principalfield'    => 'false',
					),
				),
			),
			'grade_system' => array(
				'name' => 'astgdrmsys_grade_system',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_grade_system` (
					`id` INT(10) NOT NULL AUTO_INCREMENT,
					`min_mark` INT NOT NULL,
					`max_mark` INT NOT NULL,
					`grade` VARCHAR(10) NOT NULL,
					`grade_point` FLOAT NOT NULL,
					`remarks` VARCHAR(255) NOT NULL,
					PRIMARY KEY (`id`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'mark' => array(
				'name' => 'astgdrmsys_mark',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_mark` (
					`id` INT NOT NULL AUTO_INCREMENT,
            		`sid` INT NOT NULL,
		            `marks` LONGTEXT NOT NULL,
		            `remark` VARCHAR(255) NULL,
		            PRIMARY KEY (`id`)
	     		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'retake_marks' => array(
				'name' => 'astgdrmsys_retake_marks',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_retake_marks` (
					`id` INT(10) NOT NULL AUTO_INCREMENT,
					`sid` INT(10) NOT NULL,
					`student_id` INT(10) DEFAULT NULL,
					`exam_id` INT(10) NOT NULL,
					`exam_year_id` INT(10) DEFAULT NULL,
					`subject_code` VARCHAR(50) NOT NULL,
					`retake_mark` DECIMAL(10,2) NOT NULL,
					`retake_date` DATE DEFAULT NULL,
					`note` TEXT NULL DEFAULT NULL,
					`created_by` BIGINT(20) UNSIGNED DEFAULT NULL,
					`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
					`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
					PRIMARY KEY (`id`),
					UNIQUE KEY `student_subject` (`sid`, `subject_code`),
					KEY `exam_lookup` (`exam_id`, `subject_code`),
					KEY `student_id` (`student_id`)
	     		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'subject'        => array(
				'name' => 'astgdrmsys_subject',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_subject` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `subid` INT(10) NOT NULL,
                    `class` VARCHAR(100) NOT NULL,
                    `department` VARCHAR(100) NOT NULL,
                    `subname` VARCHAR(100) NOT NULL,
                    `isFourthSubject` TINYINT(1) NOT NULL DEFAULT 0,
                    `subcode` INT(10) NOT NULL,
                    `minmark` INT(10) NOT NULL,
                    `total` INT(10) NOT NULL,
                    PRIMARY KEY (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'class'          => array(
				'name' => 'astgdrmsys_class',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_class` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `class` VARCHAR(100) NOT NULL,
                    PRIMARY KEY (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'department'     => array(
				'name' => 'astgdrmsys_department',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_department` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `department` VARCHAR(30) NOT NULL,
                    `class` VARCHAR(30) NOT NULL,
                    PRIMARY KEY (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'section'        => array(
				'name' => 'astgdrmsys_section',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_section` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `section` VARCHAR(100) NOT NULL,
                    `department` VARCHAR(30) NOT NULL,
                    `classname` VARCHAR(30) NOT NULL,
                    PRIMARY KEY (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'exam_name'      => array(
				'name' => 'astgdrmsys_exam_name',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_exam_name` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `exam_name` VARCHAR(255) NOT NULL,
                    `exam_type` VARCHAR(50) NOT NULL DEFAULT 'single',
					`exam_category` VARCHAR(50) NOT NULL DEFAULT 'general_exam',					
                    `first_assessment_display_name` VARCHAR(100) DEFAULT NULL,
                    `second_assessment_display_name` VARCHAR(100) DEFAULT NULL,
                    `third_assessment_display_name` VARCHAR(100) DEFAULT NULL,
                    `total_display_name` VARCHAR(100) DEFAULT NULL,
                    `department` VARCHAR(30) NOT NULL,
                    `class` VARCHAR(30) NOT NULL,
                    `class_id` INT(10) NOT NULL,
                    `exam_year` VARCHAR(50) NOT NULL,
                    `exam_year_id` INT(10) NOT NULL,
                    `result_title` VARCHAR(100) NOT NULL,
                    `result_subtitle` VARCHAR(100) NOT NULL,
                    `result_footer_text` VARCHAR(100) NOT NULL,
                    `principal_signature` VARCHAR(255) DEFAULT NULL,
                    `include_qr_code` TINYINT(1) NOT NULL DEFAULT 1,
                    `fail_on_single_subject` TINYINT(1) NOT NULL DEFAULT 0,
                    `office_seal` VARCHAR(255) DEFAULT NULL,
                    `result_publish_status` TINYINT(1) NOT NULL DEFAULT 0,
                    `restrict_non_logged_in` TINYINT(1) NOT NULL DEFAULT 0,
                    `check_only_own_result` TINYINT(1) NOT NULL DEFAULT 0,
                    `subject_full_marks` LONGTEXT DEFAULT NULL,
                    `combined_component_exams` LONGTEXT DEFAULT NULL,
                    `custom_field_name1` VARCHAR(100) DEFAULT NULL,
                    `custom_field_value1` VARCHAR(100) DEFAULT NULL,
                    `custom_field_name2` VARCHAR(100) DEFAULT NULL,
                    `custom_field_value2` VARCHAR(100) DEFAULT NULL,
                    `custom_field_name3` VARCHAR(100) DEFAULT NULL,
					`custom_field_value3` VARCHAR(100) DEFAULT NULL,
                    PRIMARY KEY (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
			'exam_year'      => array(
				'name' => 'astgdrmsys_exam_year',
				'sql'  => "CREATE TABLE IF NOT EXISTS `{$this->prefix}astgdrmsys_exam_year` (
                    `id` INT(10) NOT NULL AUTO_INCREMENT,
                    `exam_year` VARCHAR(30) NOT NULL,
                    PRIMARY KEY (`id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;",
			),
		);
	}

	/**
	 * Handles the installation process for the plugin.
	 *
	 * @return array An array containing the results of the installation process.
	 */
	public function install(): array
	{
		$results = array();
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

                foreach ($this->tables as $table => $definition) {
                        try {
                                dbDelta($definition['sql']);
                                $results[$table] = true;
                        } catch (\Exception $e) {
                                $results[$table] = false;
                                do_action('astgdrmsys_log_error', "ASTDGRMSYS Error creating table {$table}: " . $e->getMessage());
                        }
                }

                $this->ensure_exam_category_defaults();
                $this->ensure_exam_subjects_column();
                $this->ensure_combined_exam_components_column();
                $this->ensure_exam_assessment_display_columns();
                $this->ensure_global_departments();
                $this->ensure_student_database_schema();
                $this->backfill_student_database();
                $this->seed_default_data();
                return $results;
        }

        /**
         * Merges class-scoped duplicate departments into global department rows.
         */
        private function ensure_global_departments(): void
        {
                try {
                        $department_table = esc_sql($this->prefix . 'astgdrmsys_department');
                        $rows = $this->wpdb->get_results("SELECT id, department FROM `{$department_table}` ORDER BY id ASC", ARRAY_A);

                        if (! is_array($rows) || empty($rows)) {
                                return;
                        }

                        $canonical_by_name = array();
                        $canonical_names = array();
                        $duplicate_map = array();

                        foreach ($rows as $row) {
                                $id = isset($row['id']) ? (int) $row['id'] : 0;
                                $name = isset($row['department']) ? trim((string) $row['department']) : '';
                                $normalized = strtolower($name);

                                if ($id <= 0 || $normalized === '') {
                                        continue;
                                }

                                if (! isset($canonical_by_name[$normalized])) {
                                        $canonical_by_name[$normalized] = $id;
                                        $canonical_names[$normalized] = $name;
                                        continue;
                                }

                                $duplicate_map[$id] = (int) $canonical_by_name[$normalized];
                        }

                        foreach ($duplicate_map as $old_id => $new_id) {
                                $this->remap_department_references((int) $old_id, (int) $new_id);
                                $this->wpdb->delete($department_table, array('id' => (int) $old_id), array('%d'));
                        }

                        foreach ($canonical_by_name as $normalized => $canonical_id) {
                                $this->remap_department_name_references((string) ($canonical_names[$normalized] ?? $normalized), (int) $canonical_id);
                        }

                        $this->wpdb->query("UPDATE `{$department_table}` SET `class` = '0'");
                        wp_cache_delete('astgdrmsys_departments');
                        wp_cache_delete('astgdrmsys_class_list');
                } catch (\Exception $e) {
                        do_action('astgdrmsys_log_error', 'ASTGDRMSYS Error ensuring global departments: ' . $e->getMessage());
                }
        }

        /**
         * Repoints known department references from a duplicate id to the canonical id.
         */
        private function remap_department_references(int $old_id, int $new_id): void
        {
                $updates = array(
                        'astgdrmsys_student_result'      => array('department'),
                        'astgdrmsys_student_enrollments' => array('department_id'),
                        'astgdrmsys_section'             => array('department'),
                        'astgdrmsys_subject'             => array('department'),
                        'astgdrmsys_exam_name'           => array('department'),
                );

                foreach ($updates as $table => $columns) {
                        $table_name = esc_sql($this->prefix . $table);

                        foreach ($columns as $column) {
                                $column = esc_sql($column);
                                $this->wpdb->query(
                                        $this->wpdb->prepare(
                                                "UPDATE `{$table_name}` SET `{$column}` = %d WHERE `{$column}` = %d OR `{$column}` = %s",
                                                $new_id,
                                                $old_id,
                                                (string) $old_id
                                        )
                                );
                        }
                }
        }

        /**
         * Converts legacy department-name references to canonical department ids.
         */
        private function remap_department_name_references(string $department_name, int $department_id): void
        {
                $updates = array(
                        'astgdrmsys_student_result' => array('department'),
                        'astgdrmsys_section'        => array('department'),
                        'astgdrmsys_subject'        => array('department'),
                        'astgdrmsys_exam_name'      => array('department'),
                );

                foreach ($updates as $table => $columns) {
                        $table_name = esc_sql($this->prefix . $table);

                        foreach ($columns as $column) {
                                $column = esc_sql($column);
                                $this->wpdb->query(
                                        $this->wpdb->prepare(
                                                "UPDATE `{$table_name}` SET `{$column}` = %d WHERE LOWER(TRIM(`{$column}`)) = LOWER(TRIM(%s))",
                                                $department_id,
                                                $department_name
                                        )
                                );
                        }
                }
        }

        /**
         * Ensures student master/enrollment columns exist for upgraded installs.
         */
        private function ensure_student_database_schema(): void
        {
                try {
                        $result_table = esc_sql($this->prefix . 'astgdrmsys_student_result');

                        $columns = array(
                                'student_id'    => "ALTER TABLE `{$result_table}` ADD COLUMN `student_id` INT(10) DEFAULT NULL AFTER `sid`",
                                'enrollment_id' => "ALTER TABLE `{$result_table}` ADD COLUMN `enrollment_id` INT(10) DEFAULT NULL AFTER `student_id`",
                        );

                        foreach ($columns as $column => $alter) {
                                $column_exists = $this->wpdb->get_var(
                                        $this->wpdb->prepare(
                                                "SHOW COLUMNS FROM `{$result_table}` LIKE %s",
                                                $column
                                        )
                                );

                                if ($column_exists === null) {
                                        $this->wpdb->query($alter);
                                }
                        }

                        $indexes = $this->wpdb->get_results("SHOW INDEX FROM `{$result_table}`", ARRAY_A);
                        $index_names = array();

                        if (is_array($indexes)) {
                                foreach ($indexes as $index) {
                                        if (! empty($index['Key_name'])) {
                                                $index_names[] = $index['Key_name'];
                                        }
                                }
                        }

                        $index_names = array_unique($index_names);
                        $index_map = array(
                                'student_id'    => "ALTER TABLE `{$result_table}` ADD INDEX `student_id` (`student_id`)",
                                'enrollment_id' => "ALTER TABLE `{$result_table}` ADD INDEX `enrollment_id` (`enrollment_id`)",
                                'exam_student'  => "ALTER TABLE `{$result_table}` ADD INDEX `exam_student` (`exam_name`, `student_id`)",
                        );

                        foreach ($index_map as $index_name => $alter) {
                                if (! in_array($index_name, $index_names, true)) {
                                        $this->wpdb->query($alter);
                                }
                        }
                } catch (\Exception $e) {
                        do_action('astgdrmsys_log_error', 'ASTGDRMSYS Error ensuring student database schema: ' . $e->getMessage());
                }
        }

        /**
         * Backfills the reusable student database from legacy result rows.
         */
        private function backfill_student_database(): void
        {
                try {
                        $result_table     = esc_sql($this->prefix . 'astgdrmsys_student_result');
                        $students_table   = esc_sql($this->prefix . 'astgdrmsys_students');
                        $enrollment_table = esc_sql($this->prefix . 'astgdrmsys_student_enrollments');

                        $rows = $this->wpdb->get_results(
                                "SELECT sid, sno, regno, name, gender, fname, mname, class, department, section, exam_year, student_image, custom_fields, student_id, enrollment_id FROM `{$result_table}` ORDER BY sid ASC",
                                ARRAY_A
                        );

                        if (empty($rows)) {
                                return;
                        }

                        $identity_map   = array();
                        $enrollment_map = array();

                        foreach ($rows as $row) {
                                $sid = isset($row['sid']) ? (int) $row['sid'] : 0;
                                if ($sid <= 0) {
                                        continue;
                                }

                                $student_id = isset($row['student_id']) ? (int) $row['student_id'] : 0;
                                if ($student_id <= 0) {
                                        $student_id = $this->resolve_or_create_student_from_result($students_table, $row, $identity_map);
                                }

                                if ($student_id <= 0) {
                                        continue;
                                }

                                $enrollment_id = isset($row['enrollment_id']) ? (int) $row['enrollment_id'] : 0;
                                if ($enrollment_id <= 0) {
                                        $enrollment_id = $this->resolve_or_create_enrollment_from_result($enrollment_table, $row, $student_id, $enrollment_map);
                                }

                                $updates = array();
                                if ((int) ($row['student_id'] ?? 0) <= 0) {
                                        $updates['student_id'] = $student_id;
                                }

                                if ($enrollment_id > 0 && (int) ($row['enrollment_id'] ?? 0) <= 0) {
                                        $updates['enrollment_id'] = $enrollment_id;
                                }

                                if (! empty($updates)) {
                                        $this->wpdb->update(
                                                $this->prefix . 'astgdrmsys_student_result',
                                                $updates,
                                                array('sid' => $sid)
                                        );
                                }
                        }
                } catch (\Exception $e) {
                        do_action('astgdrmsys_log_error', 'ASTGDRMSYS Error backfilling student database: ' . $e->getMessage());
                }
        }

        /**
         * Finds or creates a master student for a legacy result row.
         *
         * @param string              $students_table Sanitized students table.
         * @param array<string,mixed> $row            Result row.
         * @param array<string,int>   $identity_map   In-memory identity cache.
         */
        private function resolve_or_create_student_from_result(string $students_table, array $row, array &$identity_map): int
        {
                $regno = isset($row['regno']) ? trim((string) $row['regno']) : '';
                $name  = isset($row['name']) ? trim((string) $row['name']) : '';
                $fname = isset($row['fname']) ? trim((string) $row['fname']) : '';
                $mname = isset($row['mname']) ? trim((string) $row['mname']) : '';

                if ($name === '' && $regno === '') {
                        return 0;
                }

                $identity_key = $regno !== ''
                        ? 'regno:' . strtolower($regno)
                        : 'name:' . md5(strtolower($name . '|' . $fname . '|' . $mname));

                if (isset($identity_map[$identity_key])) {
                        return (int) $identity_map[$identity_key];
                }

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

                        if ($student_id > 0) {
                                $identity_map[$identity_key] = $student_id;
                                return $student_id;
                        }
                }

                $student_id = (int) $this->wpdb->get_var(
                        $this->wpdb->prepare(
                                "SELECT id FROM `{$students_table}` WHERE name = %s AND fname = %s AND mname = %s ORDER BY id ASC LIMIT 1",
                                $name,
                                $fname,
                                $mname
                        )
                );

                if ($student_id > 0) {
                        $identity_map[$identity_key] = $student_id;
                        return $student_id;
                }

                $inserted = $this->wpdb->insert(
                        $this->prefix . 'astgdrmsys_students',
                        array(
                                'student_uid'   => $this->generate_student_uid(),
                                'regno'         => $regno !== '' ? $regno : null,
                                'name'          => $name,
                                'gender'        => isset($row['gender']) ? sanitize_text_field((string) $row['gender']) : '',
                                'fname'         => $fname,
                                'mname'         => $mname,
                                'student_image' => ! empty($row['student_image']) ? esc_url_raw((string) $row['student_image']) : null,
                                'custom_fields' => isset($row['custom_fields']) ? (string) $row['custom_fields'] : null,
                                'status'        => 'active',
                        )
                );

                if (! $inserted) {
                        return 0;
                }

                $student_id = (int) $this->wpdb->insert_id;
                $identity_map[$identity_key] = $student_id;

                return $student_id;
        }

        /**
         * Finds or creates an enrollment for a legacy result row.
         *
         * @param string              $enrollment_table Sanitized enrollments table.
         * @param array<string,mixed> $row              Result row.
         * @param int                 $student_id       Master student id.
         * @param array<string,int>   $enrollment_map   In-memory enrollment cache.
         */
        private function resolve_or_create_enrollment_from_result(string $enrollment_table, array $row, int $student_id, array &$enrollment_map): int
        {
                $exam_year_id  = isset($row['exam_year']) ? (int) $row['exam_year'] : 0;
                $class_id      = isset($row['class']) ? (int) $row['class'] : 0;
                $department_id = isset($row['department']) ? (int) $row['department'] : 0;
                $section_id    = isset($row['section']) ? (int) $row['section'] : 0;
                $roll_no       = isset($row['sno']) ? sanitize_text_field((string) $row['sno']) : '';

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

                $key = implode(':', array($student_id, $exam_year_id));
                if (isset($enrollment_map[$key])) {
                        return (int) $enrollment_map[$key];
                }

                $enrollment_id = (int) $this->wpdb->get_var(
                        $this->wpdb->prepare(
                                "SELECT id FROM `{$enrollment_table}` WHERE student_id = %d AND exam_year_id = %d ORDER BY id ASC LIMIT 1",
                                $student_id,
                                $exam_year_id
                        )
                );

                if ($enrollment_id > 0) {
                        $enrollment_map[$key] = $enrollment_id;
                        return $enrollment_id;
                }

                $inserted = $this->wpdb->insert(
                        $this->prefix . 'astgdrmsys_student_enrollments',
                        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',
                        )
                );

                if (! $inserted) {
                        return 0;
                }

                $enrollment_id = (int) $this->wpdb->insert_id;
                $enrollment_map[$key] = $enrollment_id;

                return $enrollment_id;
        }

        /**
         * Generates a stable-looking unique student identifier.
         */
        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->prefix}astgdrmsys_students` WHERE student_uid = %s",
                                        $uid
                                )
                        );
                } while ($exists > 0);

                return $uid;
        }

        /**
         * Ensures the exam category column has a default value for existing records.
         *
         * This method backfills the `exam_category` column with the default value
         * for any legacy rows created before the column existed.
         *
         * @return void
         */
        private function ensure_exam_category_defaults(): void
        {
                try {
                        $table = esc_sql($this->prefix . 'astgdrmsys_exam_name');

                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        $column_exists = $this->wpdb->get_var(
                                $this->wpdb->prepare(
                                        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                        "SHOW COLUMNS FROM `{$table}` LIKE %s",
                                        'exam_category'
                                )
                        );

                        if ($column_exists === null) {
                                return;
                        }

                        // Backfill empty exam_category values with the default option.
                        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery
                        $this->wpdb->query(
                                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                "UPDATE `{$table}` SET `exam_category` = 'general_exam' WHERE `exam_category` IS NULL OR `exam_category` = ''"
                        );
                } catch (\Exception $e) {
                        do_action('astgdrmsys_log_error', 'ASTDGRMSYS Error ensuring exam category defaults: ' . $e->getMessage());
                }
        }

        /**
         * Ensures the subject_full_marks column exists and is initialized.
         */
        private function ensure_exam_subjects_column(): void
        {
                try {
                        $table = esc_sql($this->prefix . 'astgdrmsys_exam_name');

                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        $column_exists = $this->wpdb->get_var(
                                $this->wpdb->prepare(
                                        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                        "SHOW COLUMNS FROM `{$table}` LIKE %s",
                                        'subject_full_marks'
                                )
                        );

                        if ($column_exists === null) {
                                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                                $this->wpdb->query(
                                        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                        "ALTER TABLE `{$table}` ADD COLUMN `subject_full_marks` LONGTEXT DEFAULT NULL"
                                );
                        }

                        // Ensure empty values default to an empty serialized array.
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        $this->wpdb->query(
                                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                "UPDATE `{$table}` SET `subject_full_marks` = 'a:0:{}' WHERE `subject_full_marks` IS NULL OR `subject_full_marks` = ''"
                        );
                } catch (\Exception $e) {
                        do_action('astgdrmsys_log_error', 'ASTDGRMSYS Error ensuring exam subject defaults: ' . $e->getMessage());
                }
        }

        /**
         * Ensures the assessment display-name columns exist and are populated with defaults.
         */
        private function ensure_exam_assessment_display_columns(): void
        {
                try {
                        $table = esc_sql($this->prefix . 'astgdrmsys_exam_name');

                        $columns = array(
                                'first_assessment_display_name'  => "ALTER TABLE `{$table}` ADD COLUMN `first_assessment_display_name` VARCHAR(100) DEFAULT NULL",
                                'second_assessment_display_name' => "ALTER TABLE `{$table}` ADD COLUMN `second_assessment_display_name` VARCHAR(100) DEFAULT NULL",
                                'third_assessment_display_name'  => "ALTER TABLE `{$table}` ADD COLUMN `third_assessment_display_name` VARCHAR(100) DEFAULT NULL",
                                'total_display_name'             => "ALTER TABLE `{$table}` ADD COLUMN `total_display_name` VARCHAR(100) DEFAULT NULL",
                        );

                        foreach ($columns as $column => $alter) {
                                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                                $column_exists = $this->wpdb->get_var(
                                        $this->wpdb->prepare(
                                                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                                "SHOW COLUMNS FROM `{$table}` LIKE %s",
                                                $column
                                        )
                                );

                                if ($column_exists === null) {
                                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                                        $this->wpdb->query($alter);
                                }
                        }

                        // Backfill any missing values using global defaults.
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        $exams = $this->wpdb->get_results(
                                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                "SELECT id, exam_type, first_assessment_display_name, second_assessment_display_name, third_assessment_display_name, total_display_name FROM `{$table}`",
                                ARRAY_A
                        );

                        if (! empty($exams)) {
                                foreach ($exams as $exam) {
                                        $resolved = astgdrmsys_determine_assessment_display_names($exam);
                                        $updates  = array();

                                        foreach ($resolved as $key => $value) {
                                                if (! isset($exam[$key]) || trim((string) $exam[$key]) === '') {
                                                        $updates[$key] = $value;
                                                }
                                        }

                                        if (! empty($updates)) {
                                                $this->wpdb->update(
                                                        $this->prefix . 'astgdrmsys_exam_name',
                                                        $updates,
                                                        array('id' => (int) $exam['id'])
                                                );
                                        }
                                }
                        }
                } catch (\Exception $e) {
                        do_action('astgdrmsys_log_error', 'ASTDGRMSYS Error ensuring assessment display columns: ' . $e->getMessage());
                }
        }

        /**
         * Ensures the combined_component_exams column exists for storing combined exam metadata.
         */
        private function ensure_combined_exam_components_column(): void
        {
                try {
                        $table = esc_sql($this->prefix . 'astgdrmsys_exam_name');

                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        $column_exists = $this->wpdb->get_var(
                                $this->wpdb->prepare(
                                        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                        "SHOW COLUMNS FROM `{$table}` LIKE %s",
                                        'combined_component_exams'
                                )
                        );

                        if ($column_exists === null) {
                                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                                $this->wpdb->query(
                                        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                                        "ALTER TABLE `{$table}` ADD COLUMN `combined_component_exams` LONGTEXT DEFAULT NULL"
                                );
                        }
                } catch (\Exception $e) {
                        do_action('astgdrmsys_log_error', 'ASTDGRMSYS Error ensuring combined exam column: ' . $e->getMessage());
                }
        }

	/**
	 * Seeds the default data into the database.
	 *
	 * This function is responsible for populating the database with initial
	 * default data required for the plugin to function correctly.
	 *
	 * @return void
	 */
	private function seed_default_data(): void
	{
		// Seed view settings if empty
		try {
			$sanitized_grade_system_table = esc_sql($this->prefix . 'astgdrmsys_grade_system');
			$existing_grades = $this->wpdb->get_results("SELECT * FROM `{$sanitized_grade_system_table}`");

			// If no grades exist, insert default grades
			if (empty($existing_grades)) {
				$default_grades = array(
					array('min_mark' => 0, 'max_mark' => 32, 'grade' => 'F', 'grade_point' => 0, 'remarks' => 'Fail'),
					array('min_mark' => 33, 'max_mark' => 39, 'grade' => 'D', 'grade_point' => 1, 'remarks' => 'Pass'),
					array('min_mark' => 40, 'max_mark' => 49, 'grade' => 'C', 'grade_point' => 2, 'remarks' => 'Average'),
					array('min_mark' => 50, 'max_mark' => 59, 'grade' => 'B', 'grade_point' => 3, 'remarks' => 'Good'),
					array('min_mark' => 60, 'max_mark' => 69, 'grade' => 'A-', 'grade_point' => 3.5, 'remarks' => 'Very Good'),
					array('min_mark' => 70, 'max_mark' => 79, 'grade' => 'A', 'grade_point' => 4, 'remarks' => 'Excellent'),
					array('min_mark' => 80, 'max_mark' => 100, 'grade' => 'A+', 'grade_point' => 5, 'remarks' => 'Outstanding'),
				);

				foreach ($default_grades as $grade) {
					$this->wpdb->insert(
						"{$this->prefix}astgdrmsys_grade_system",
						$grade
					);
				}
			}

			$sanitized_general_settings_table = esc_sql($this->prefix . 'astgdrmsys_general_settings');
			// Check if general settings exist
			$existing_settings = $this->wpdb->get_results("SELECT * FROM `{$sanitized_general_settings_table}`");
			if (empty($existing_settings)) {
				// Insert default general settings
                                $default_general_settings = array(
                                        array('setting_key' => 'enable_teacher_mark_update', 'setting_value' => '0'),
                                );

				foreach ($default_general_settings as $gen_setting) {
					$this->wpdb->insert(
						"{$this->prefix}astgdrmsys_general_settings",
						$gen_setting
					);
				}
			}

			// Sanitize table name
			$sanitized_view_settings_table = esc_sql($this->prefix . 'astgdrmsys_view_settings');

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
			$existing_views = $this->wpdb->get_results("SELECT * FROM `{$sanitized_view_settings_table}`");

			if (empty($existing_views)) {
				$default_view_settings = array(
					array(
						'vid'               => 1,
						'methodfield'       => 'Roll Number',
						'titlefield'        => 'PROGRESS REPORT',
						'namefield'         => 'Name of the Student :',
						'genderfield'       => 'Gender :',
						'fnamefield'        => 'Father\'s Name :',
						'mnamefield'        => 'Mother\'s Name :',
						'classfield'        => 'Class :',
						'regnofield'        => 'Registration Number :',
						'subjectfield'      => 'Subject :',
						'rollfield'         => 'Roll No :',
						'totalfield'        => 'Total Marks:',
						'minimumfield'      => 'Min Passing Marks:',
						'obtainedfield'     => 'Obtained Marks:',
						'remarkfield'       => 'Grade :',
						'percentage'        => 'Percentage :',
						'minimumpercentage' => 'Minimum Passing Percentage :',
						'finalresult'       => 'Remark :',
						'principalfield'    => 'Principal :',
					),
					array(
						'vid'               => 2,
						'methodfield'       => 'true',
						'titlefield'        => 'true',
						'namefield'         => 'true',
						'genderfield'       => 'true',
						'fnamefield'        => 'true',
						'mnamefield'        => 'true',
						'classfield'        => 'true',
						'regnofield'        => 'true',
						'subjectfield'      => 'true',
						'rollfield'         => 'true',
						'totalfield'        => 'true',
						'minimumfield'      => 'true',
						'obtainedfield'     => 'true',
						'remarkfield'       => 'true',
						'percentage'        => 'true',
						'minimumpercentage' => 'true',
						'finalresult'       => 'true',
						'principalfield'    => 'true',
					),
					array(
						'vid'               => 3,
						'methodfield'       => 'marksheet',
						'titlefield'        => 'watermark',
						'namefield'         => 'false',
						'genderfield'       => 'false',
						'fnamefield'        => 'false',
						'mnamefield'        => 'false',
						'classfield'        => 'false',
						'regnofield'        => 'false',
						'subjectfield'      => 'false',
						'rollfield'         => 'false',
						'totalfield'        => 'false',
						'minimumfield'      => 'false',
						'obtainedfield'     => 'false',
						'remarkfield'       => 'false',
						'percentage'        => 'false',
						'minimumpercentage' => 'false',
						'finalresult'       => 'false',
						'principalfield'    => 'false',
					),
				);

				foreach ($default_view_settings as $setting) {
					$inserted = $this->wpdb->insert(
						"{$this->prefix}astgdrmsys_view_settings",
						$setting
					);

					if ($inserted === false) {
						throw new \Exception($this->wpdb->last_error);
					}
				}
			}
		} catch (\Exception $e) {
			do_action('astgdrmsys_log_error', 'ASTGDRMSYS Error seeding default data: ' . $e->getMessage());
		}
	}
}

/**
 * Creates the upload directory for the plugin.
 *
 * This function is responsible for creating the necessary upload directory
 * within the WordPress content directory for storing plugin-related files.
 *
 * @return void
 */
function create_upload_directory(): void
{
	try {
		$upload_dir = wp_upload_dir();
		$result_dir = $upload_dir['basedir'] . '/astgdrmsys-results';

		if (! file_exists($result_dir)) {
			if (! wp_mkdir_p($result_dir)) {
				throw new \Exception('Failed to create result directory');
			}

			// Create security files
			$files = array(
				'index.php' => "<?php\n// Silence is golden.",
				'.htaccess' => "Options -Indexes\nDeny from all",
			);

			$chmod_file = defined('FS_CHMOD_FILE') ? FS_CHMOD_FILE : 0644;

			if (! function_exists('WP_Filesystem')) {
				require_once ABSPATH . 'wp-admin/includes/file.php';
			}

			WP_Filesystem();
			global $wp_filesystem;

			foreach ($files as $file => $content) {
				$file_path = trailingslashit($result_dir) . $file;

				if ( ! $wp_filesystem || ! @$wp_filesystem->put_contents($file_path, $content, $chmod_file) ) {
					if (false === @file_put_contents($file_path, $content)) {
						throw new \Exception("Failed to create {$file}");
					}
					@chmod($file_path, $chmod_file);
				}
			}
		}
	} catch (\Exception $e) {
		do_action('astgdrmsys_log_error', 'RMS Directory Creation Error: ' . $e->getMessage());
		throw $e;
	}
}

/**
 * Installs the plugin.
 *
 * This function is responsible for setting up the necessary environment
 * and configurations required for the plugin to function correctly.
 *
 * @return void
 */
function astgdrmsys_run_installation_routine(): void
{
	try {
		// Create upload directory
		create_upload_directory();

		// Install database
		$installer = new ASTGDRMSYS_DatabaseInstaller();
		$results   = $installer->install();

		// Trigger custom logging for debugging
		if (WP_DEBUG && WP_DEBUG_LOG) {
			do_action('astgdrmsys_debug_log', 'RMS Plugin Installation Results: ' . wp_json_encode($results));
		}

		// Store installation version
		update_option('astgdrmsys_db_version', ASTGDRMSYS_VERSION);
	} catch (\Exception $e) {
		if (WP_DEBUG && WP_DEBUG_LOG) {
			do_action('astgdrmsys_debug_log', 'RMS Plugin Installation Error: ' . $e->getMessage());
		}
		wp_die('Error during plugin installation. Please check error logs.');
	}
}
