<?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 RMS Result Management System plugin.
 *
 * It ensures that the file is being accessed through WordPress by checking if the ABSPATH constant is defined.
 * If ABSPATH is not defined, the script will exit to prevent unauthorized access.
 */
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// Include WordPress database access
global $wpdb;

/**
 * Class ASTGDRMSYS_CSV_Update
 *
 * This class handles the updating of marks from a CSV file.
 */
class ASTGDRMSYS_CSV_Update {

	private $wpdb;
	private $prefix;

	/**
	 * Constructor for the class.
	 *
	 * @param wpdb $wpdb WordPress database access abstraction object.
	 */
	public function __construct( $wpdb ) {
		$this->wpdb   = $wpdb;
		$this->prefix = $wpdb->prefix;

		// Check if 'REQUEST_METHOD' is set before proceeding
		if ( isset( $_SERVER['REQUEST_METHOD'] ) && $_SERVER['REQUEST_METHOD'] === 'POST' ) {
			// Verify the nonce before processing the form
			if ( isset( $_POST['submit-update-csv'] ) && isset( $_POST['astgdrmsys_csv_upload_nonce'] ) && wp_verify_nonce( sanitize_text_field( wp_unslash( $_POST['astgdrmsys_csv_upload_nonce'] ) ), 'astgdrmsys_csv_upload_action' ) ) {
				$this->handleCsvUpload();
			} else {
				// Handle nonce failure or missing nonce
				wp_die( esc_html__( 'Security check failed: Nonce is missing or invalid.', 'result-management-system-for-institutions' ) );
			}
		}
	}


	/**
	 * Displays the form for updating marks via CSV.
	 *
	 * This function renders the form that allows users to upload a CSV file
	 * for updating marks in the system.
	 *
	 * @return void
	 */
	public function displayForm() {
		global $wpdb; ?>
		<div class='container astgdrmsys-premium-wrap'>
			<div>
				<div class="notice notice-info astgdrmsys-update-notice">
					<h2>📘 Update Student's Marks</h2>
					<p>
						<strong>Instructions:</strong><br>
						1️⃣ Select the correct <strong>Class</strong> and <strong>Exam Name</strong>.<br>
						2️⃣ Go to 
						<a class="custom-display-url" href="<?php echo admin_url('admin.php?page=astgdrmsys-generate-bulk-csv'); ?>">
							<strong>Generate CSV</strong>
						</a> → <strong>Generate CSV for Partial Update</strong> to download the template.<br>
						3️⃣ Enter marks in the CSV file carefully (don’t change header names).<br>
						4️⃣ Upload the completed CSV file below to update student records.
					</p>
				</div>
			</div><br>
			<form method='POST' action='' id="csvupdateForm" enctype="multipart/form-data" class="premium-filter-form">
				<?php wp_nonce_field( 'astgdrmsys_csv_upload_action', 'astgdrmsys_csv_upload_nonce' ); ?>
				<input type="hidden" name="page" value="astgdrmsys-update-marks-csv">
				
				<div class="filter-group">
					<label>Select Class :</label>
					<select class="selectClass premium-input" name="csv-class" required>
						<option value="" selected disabled>Select a Class</option>
						<?php
						$cache_key = 'astgdrmsys_class_list';
						$classes   = wp_cache_get( $cache_key );

						if ( $classes === false ) {
							$table_name = esc_sql( $this->prefix . 'astgdrmsys_class' );

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

							if ( ! empty( $classes ) ) {
								wp_cache_set( $cache_key, $classes );
							}
						}

						foreach ( $classes as $class ) {
							?>
							<option value="<?php echo esc_attr( $class['id'] ); ?>">
								<?php echo esc_html( $class['class'] ); ?>
							</option>
							<?php
						}
						?>
					</select>
				</div>
				
				<div class="filter-group">
					<label>Select Exam Name:</label>
					<select class="selectExam premium-input" name="csv-examname" required>
						<option value="" selected disabled>Select Class First</option>
					</select>
				</div>
				
				<div class="filter-group">
					<label>Choose CSV File:</label>
					<div class="premium-input" style="height: 42px; display: flex; align-items: center; border: 1px solid #e2e8f0; padding: 0 10px; border-radius: 4px;">
						<input type="file" name="update-csv-file" id="csvUpdateFile" accept=".csv" required style="border:none; padding:0; height: auto;">
					</div>
				</div>
				
				<div class="filter-actions" style="margin-bottom: 5px;">
					<input class="hero-btn primary-btn" style="border:none;" type="submit" name="submit-update-csv" value="Update Mark" id="csvUpdateImport">
				</div>
			</form>
		</div> <?php
	}

	/**
	 * Handles the CSV file upload process.
	 *
	 * This function processes the uploaded CSV file, validates its contents,
	 * and updates the marks accordingly in the system.
	 *
	 * @return void
	 */
	private function handleCsvUpload() {
		global $wpdb;
		// Nonce verification
		if ( ! isset( $_POST['astgdrmsys_csv_upload_nonce'] ) || ! wp_verify_nonce( sanitize_text_field( wp_unslash( $_POST['astgdrmsys_csv_upload_nonce'] ) ), 'astgdrmsys_csv_upload_action' ) ) {
			wp_die( esc_html__( 'Security check failed. Please reload the page and try again.', 'result-management-system-for-institutions' ) );
		}

		// Validate form data
		$classname                 = isset( $_POST['csv-class'] ) ? sanitize_text_field( wp_unslash( $_POST['csv-class'] ) ) : 0;
		$examnamewithyear          = isset( $_POST['csv-examname'] ) ? sanitize_text_field( wp_unslash( $_POST['csv-examname'] ) ) : '';
		list($examname, $examyear) = explode( ',', $examnamewithyear );

		// Get the exam type before the loop
		$exam_table = $this->prefix . 'astgdrmsys_exam_name';
		$exam_type = $this->wpdb->get_var(
			$this->wpdb->prepare("SELECT exam_type FROM $exam_table WHERE id = %d", $examname)
		);
		
		// Default fallback
		if (empty($exam_type)) {
			$exam_type = 'single';
		}

		if ( ! empty( $_FILES['update-csv-file']['name'] ) ) {
			// phpcs:disable WordPress.WP.AlternativeFunctions.file_system_operations_fopen
			if (
				isset( $_FILES['update-csv-file'] ) &&
				isset( $_FILES['update-csv-file']['tmp_name'] ) &&
				// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized
				is_uploaded_file( $_FILES['update-csv-file']['tmp_name'] )
			) {
				// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized, WordPress.WP.AlternativeFunctions.file_system_operations_fopen
				$imported_csv_file = fopen( $_FILES['update-csv-file']['tmp_name'], 'r' );
			}

			$cache_key = 'student_results_' . md5( $classname . $examname . $examyear );
			$results   = wp_cache_get( $cache_key );

			if ( $results === false ) {
				$sanitized_table = esc_sql( $this->prefix . 'astgdrmsys_student_result' );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$results = $wpdb->get_results(
					$wpdb->prepare(
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
						"SELECT sno, sid FROM `{$sanitized_table}` WHERE class = %d AND exam_name = %d AND exam_year = %d",
						$classname,
						$examname,
						$examyear
					),
					ARRAY_A
				);

				wp_cache_set( $cache_key, $results );
			}

			if ( empty( $results ) ) {
				echo "<div class='custom-alert'>No results found.</div>";
				// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
				fclose( $imported_csv_file );
				return;
			}

			$sno_to_sid_map = array_column( $results, 'sid', 'sno' ); // map of sno => sid
			$row_count      = 0; // Initialize row counter
			$update_success = false; // Flag to track successful updates
			

			// Process CSV data
			while ( $data = fgetcsv( $imported_csv_file ) ) {
				++$row_count;
				if ( $row_count === 1 ) {
					continue; // Skip the header row
				}

				$sno_from_csv = $data[0] ?? null; // Get the Roll Number from the first column

				if ( isset($sno_to_sid_map[$sno_from_csv]) ) {
					$sid = $sno_to_sid_map[$sno_from_csv];
					
					// Call for CSV processing
					$update_result = $this->updateMarksFromCsvRow( $sid, $data, $exam_type );
	
					if ( $update_result ) {
						$update_success = true;
					}
				}
			}

			// phpcs:disable WordPress.WP.AlternativeFunctions.file_system_operations_fclose
			fclose( $imported_csv_file ); // Close CSV after reading

			// Success message
			if ( $update_success ) {
				echo "<div id='message_div' class='alert alert-success custom-message'>
							<div class='message-content'>
								<center>Marks Updated Successfully.</center>
							</div>
							<button class='close-message' aria-label='Close'>&times;</button>
						</div>
					";
			} else {
				echo "<div id='message_div' class='alert alert-warning'>
							<div class='message-content'>
								<center>No marks were updated.</center>
							</div>
							<button class='close-message' aria-label='Close'>&times;</button>
						</div>
					";
			}
		}
	}

	/**
	 * Updates the marks for a given student ID with the provided data.
	 *
	 * @param int   $sid The student ID whose marks need to be updated.
	 * @param array $data An associative array containing the new marks data.
	 *
	 * @return void
	 */
	private function updateMarksFromCsvRow( int $sid, array $data, string $exam_type ): bool
	{
		// First column (index 0) is roll number, second column (index 1) is IsAbsent
	
		// Update student's absent status
		$is_absent = isset($data[1]) ? intval($data[1]) : 0;
		$this->wpdb->update(
			"{$this->prefix}astgdrmsys_student_result",
			['is_absent' => $is_absent],
			['sid' => $sid],
			['%d'],
			['%d']
		);
	
		// Fetch the student's existing full marks record
		$marks_row = $this->wpdb->get_row(
			$this->wpdb->prepare(
					"SELECT * FROM `{$this->prefix}astgdrmsys_mark` WHERE sid = %d",
					$sid
			),
			ARRAY_A
		);
	
		// If the student has no marks record at all, we can't update anything.
		if (empty($marks_row)) {
			return false;
		}
	
		$unserializedMarksData = maybe_unserialize($marks_row['marks']);
		if (!is_array($unserializedMarksData)) {
			$unserializedMarksData = []; // Start fresh if data is corrupted
		}
	
		// The CSV structure for marks is:
		// sno, is_absent, subject1_code, subject1_mark1, subject1_mark2, ..., subject2_code, ...
		// So, we loop through the data starting from the third column (index 2).
		$total_columns = count($data);
	
		if ($exam_type === 'triple') {
			// We read 4 columns at a time: code, practical, class, final
			for ($i = 2; $i + 3 < $total_columns; $i += 4) {
				$subcode   = sanitize_text_field($data[$i]);
				$practical = intval($data[$i + 1]);
				$class     = intval($data[$i + 2]);
				$final     = intval($data[$i + 3]);
				if (!empty($subcode)) {
					$unserializedMarksData[$subcode] = ['practical' => $practical, 'class' => $class, 'final' => $final];
				}
			}
		} elseif ($exam_type === 'composite') {
			// We read 3 columns at a time: code, class, final
			for ($i = 2; $i + 2 < $total_columns; $i += 3) {
				$subcode = sanitize_text_field($data[$i]);
				$class   = intval($data[$i + 1]);
				$final   = intval($data[$i + 2]);
				if (!empty($subcode)) {
					$unserializedMarksData[$subcode] = ['class' => $class, 'final' => $final];
				}
			}
		} else { // 'single'
			// We read 2 columns at a time: code, mark
			for ($i = 2; $i + 1 < $total_columns; $i += 2) {
				$subcode = sanitize_text_field($data[$i]);
				$mark    = intval($data[$i + 1]);
				if (!empty($subcode)) {
					$unserializedMarksData[$subcode] = $mark;
				}
			}
		}
	
		// Serialize the updated marks and save back to the database
		$updatedMarks = serialize($unserializedMarksData);
		$update_result = $this->wpdb->update(
			"{$this->prefix}astgdrmsys_mark",
			['marks' => $updatedMarks],
			['id' => $marks_row['id']],
			['%s'],
			['%d']
		);
	
		return $update_result !== false;
	}

}

/**
 * Creates a new instance of the ASTGDRMSYS_CSV_Update class and displays the form.
 *
 * This script initializes the ASTGDRMSYS_CSV_Update class with the global $wpdb object
 * and calls the displayForm method to render the form for updating marks via CSV.
 *
 * @package ResultManagementSystem
 * @since 1.0.0
 */
$csvUpdate = new ASTGDRMSYS_CSV_Update( $wpdb );
$csvUpdate->displayForm();
?>
