<?php

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

require_once ASTGDRMSYS_PLUGIN_DIR . 'includes/traits/class-astgdrmsys-exam-label-helper.php';

class ASTGDRMSYS_Bulk_CSV_Generator
{
    use ASTGDRMSYS_Exam_Label_Helper;

    /** @var \wpdb */
    private $wpdb;

    /** @var string */
    private $prefix;

    /** @var array */
    private $notices = array();

    /** @var array */
    private $selected = array(
        'class'      => '',
        'department' => '',
        'exam'       => '',
    );

    /** @var bool */
    private $is_bulk_request = false;

    /**
     * Constructor for the class responsible for generating bulk CSV files.
     *
     * @param wpdb $wpdb WordPress database object.
     *
     * Initializes the class with the provided $wpdb object and sets the database table prefix.
     * Processes POST request data to set selected class, department, and exam values after sanitization.
     * If a POST request is made to generate a bulk CSV (via 'astgdrmsys_generate_bulk_csv'), 
     * it triggers the CSV generation handler.
     */
    public function __construct($wpdb)
    {
        $this->wpdb   = $wpdb;
        $this->prefix = $wpdb->prefix;

        if (! empty($_POST)) {
            $this->selected['class']      = isset($_POST['astgdrmsys_class']) ? sanitize_text_field(wp_unslash($_POST['astgdrmsys_class'])) : '';
            $this->selected['department'] = isset($_POST['astgdrmsys_department']) ? sanitize_text_field(wp_unslash($_POST['astgdrmsys_department'])) : '';
            $this->selected['exam']       = isset($_POST['astgdrmsys_exam']) ? sanitize_text_field(wp_unslash($_POST['astgdrmsys_exam'])) : '';
        }

        if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['astgdrmsys_generate_bulk_csv'])) {
            $this->is_bulk_request = true;
            $this->handle_csv_generation();
        }
    }

    /**
     * Renders the page for generating bulk CSV files.
     *
     * This method outputs the HTML and handles the logic required to display
     * the bulk CSV generation interface in the admin menu.
     *
     * @return void
     */
    private function render_bulk_tab(): void
    {
        // Page Wrapper
        echo "<div class='astgdrmsys-bulk-section'>";

        // Header
        echo "<div class='premium-page-header'>";
        echo "<div class='page-header-text'>";
        echo "<h1>" . esc_html__('Generate CSV for Bulk Upload', 'result-management-system-for-institutions') . "</h1>";
        echo "<p>" . esc_html__('Select the appropriate Class, Department, and Exam Name to download a pre-formatted CSV template for bulk uploads.', 'result-management-system-for-institutions') . "</p>";
        echo "</div></div>";

        // Render Notices
        $this->render_notices();

        // Get Dropdown Data
        $classes       = $this->get_classes();
        $class_id      = absint($this->selected['class']);
        $department_id = absint($this->selected['department']);
        $departments   = $this->get_departments($class_id);
        $exams         = $this->get_exams($class_id, $department_id);

        // Form
        echo "<form method='post' class='astgdrmsys-bulk-csv-form' style='background: #ffffff; padding: 25px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.02); border: 1px solid #e2e8f0; margin-bottom: 30px;'>";
        wp_nonce_field('astgdrmsys_generate_bulk_csv_action', 'astgdrmsys_generate_bulk_csv_nonce');
        echo "<div style='display: flex; flex-wrap: wrap; gap: 20px; align-items: flex-end;'>";

        // CLASS DROPDOWN
        echo "<div class='filter-group' style='flex: 1; min-width: 200px;'>";
        echo "<label style='display: block; font-size: 11px; font-weight: 700; color: #1e293b; text-transform: uppercase; letter-spacing: 0.5px; margin-bottom: 8px;'>Select Class</label>";
        echo "<select class='selectClass premium-input' name='astgdrmsys_class' required style='width: 100%;'>";
        echo "<option value='' selected disabled>Select a Class</option>";
        foreach ($classes as $class) {
            printf(
                '<option value="%1$d" %3$s>%2$s</option>',
                (int) $class['id'],
                esc_html($class['class']),
                $this->get_selected_attr($this->selected['class'], (string) $class['id'])
            );
        }
        echo "</select></div>";

        // DEPARTMENT DROPDOWN
        echo "<div class='filter-group' style='flex: 1; min-width: 200px;'>";
        echo "<label style='display: block; font-size: 11px; font-weight: 700; color: #1e293b; text-transform: uppercase; letter-spacing: 0.5px; margin-bottom: 8px;'>Select Department</label>";
        echo "<select class='selectDepartment premium-input' name='astgdrmsys_department' required style='width: 100%;'>";
        if ($class_id && !empty($departments)) {
            echo "<option value='' disabled>Select Department</option>";
            foreach ($departments as $department) {
                printf(
                    '<option value="%1$d" %3$s>%2$s</option>',
                    (int) $department['id'],
                    esc_html($department['department']),
                    $this->get_selected_attr($this->selected['department'], (string) $department['id'])
                );
            }
        } else {
            echo "<option value='' selected disabled>Select Class First</option>";
        }
        echo "</select></div>";

        // EXAM DROPDOWN
        echo "<div class='filter-group' style='flex: 1; min-width: 200px;'>";
        echo "<label style='display: block; font-size: 11px; font-weight: 700; color: #1e293b; text-transform: uppercase; letter-spacing: 0.5px; margin-bottom: 8px;'>Select Exam</label>";
        echo "<select class='selectExam premium-input' name='astgdrmsys_exam' required style='width: 100%;'>";
        if ($class_id && !empty($exams)) {
            echo "<option value='' disabled>Select Exam</option>";
            foreach ($exams as $exam) {
                $value = $exam['id'] . ',' . $exam['exam_year_id'];
                $label = $exam['exam_name'];
                if (!empty($exam['exam_year'])) {
                    $label .= ' - ' . $exam['exam_year'];
                }
                printf(
                    '<option value="%1$s" %3$s>%2$s</option>',
                    esc_attr($value),
                    esc_html($label),
                    $this->get_selected_attr($this->selected['exam'], $value)
                );
            }
        } else {
            echo "<option value='' selected disabled>Select Class First</option>";
        }
        echo "</select></div>";

        // SUBMIT BUTTON
        echo "<div class='filter-actions' style='margin-top: 0;'>";
        echo "<input class='btn btn-primary hero-btn submit-btn' style='height: 42px; margin-bottom: 0;' type='submit' name='astgdrmsys_generate_bulk_csv' value='Generate CSV'>";
        echo "</div>";

        echo "</div>";
        echo "</form>";
        echo "</div>";
    }

    /**
     * Renders the CSV generator page with bulk and partial tabs.
     *
     * @param ASTGDRMSYS_Partial_CSV_Generator $partial_generator Instance responsible for partial CSV rendering.
     *
     * @return void
     */
    public function render_page(ASTGDRMSYS_Partial_CSV_Generator $partial_generator): void
    {
        $active_tab = $partial_generator->should_be_active() ? 'partial' : 'bulk';
        if ($active_tab !== 'partial' && $this->should_be_active()) {
            $active_tab = 'bulk';
        }

        $bulk_tab_class    = $active_tab === 'bulk' ? 'nav-tab nav-tab-active' : 'nav-tab';
        $partial_tab_class = $active_tab === 'partial' ? 'nav-tab nav-tab-active' : 'nav-tab';

        echo "<div class='container astgdrmsys-generate-csv-wrapper'>";
        echo "<h2 class='nav-tab-wrapper astgdrmsys-tab-nav'>";
        printf(
            "<a href='#' class='%s' data-target='astgdrmsys-bulk-tab'>%s</a>",
            esc_attr($bulk_tab_class),
            esc_html__('Generate CSV for Bulk Upload', 'result-management-system-for-institutions')
        );
        printf(
            "<a href='#' class='%s' data-target='astgdrmsys-partial-tab'>%s</a>",
            esc_attr($partial_tab_class),
            esc_html__('Generate CSV for Partial Update', 'result-management-system-for-institutions')
        );
        echo "</h2>";

        printf(
            "<div id='astgdrmsys-bulk-tab' class='astgdrmsys-tab-content%s'>",
            $active_tab === 'bulk' ? ' is-active' : ''
        );
        $this->render_bulk_tab();
        echo "</div>";

        printf(
            "<div id='astgdrmsys-partial-tab' class='astgdrmsys-tab-content%s'>",
            $active_tab === 'partial' ? ' is-active' : ''
        );
        $partial_generator->render_section();
        echo "</div>";
        echo "</div>";

        echo "<style>
        .astgdrmsys-generate-csv-wrapper{
            background-color: #e7e7e7;
        }
        
        .astgdrmsys-tab-nav {
          display: flex;
          margin-bottom: 1rem;
        }
        
        .astgdrmsys-tab-nav .nav-tab {
          padding: 10px 18px;
          text-decoration: none;
          color: #334155;
          background: #f8fafc;
          border: 1px solid #e2e8f0;
          border-bottom: none;
          margin-right: 4px;
          border-radius: 6px 6px 0 0;
          transition: background 0.2s, color 0.2s;
        }
        
        .astgdrmsys-tab-nav .nav-tab:hover {
          background: #e2e8f0;
          color: #0f172a;
        }

        .astgdrmsys-tab-nav .nav-tab-active {
          background: #2563eb;
          color: #fff;
          border-color: #2563eb;
        }

        /* --- Content Panels --- */
        .astgdrmsys-tab-content {
          display: none;
          border: 1px solid #e2e8f0;
          padding: 20px;
          background: #fff;
          border-radius: 0 6px 6px 6px;
        }
        
        .astgdrmsys-tab-content.is-active {
          display: block;
        }
        </style>";

        ?>
        <script>
        jQuery(function ($) {
            $(document).on('click', '.astgdrmsys-tab-nav .nav-tab', function (event) {
                event.preventDefault();
                var target = $(this).data('target');
                if (!target) {
                    return;
                }
                $('.astgdrmsys-tab-nav .nav-tab').removeClass('nav-tab-active');
                $(this).addClass('nav-tab-active');
                $('.astgdrmsys-tab-content').removeClass('is-active');
                $('#' + target).addClass('is-active');
            });
        });
        </script>
        <?php
    }

    /**
     * Determines whether the bulk tab should remain active.
     */
    public function should_be_active(): bool
    {
        return $this->is_bulk_request || ! empty($this->notices);
    }

    /**
     * Renders admin notices based on the notices stored in the class property.
     *
     * Iterates through the $notices array and displays each notice as a WordPress admin notice.
     * The notice type determines the CSS class: 'notice-success' for success messages,
     * and 'notice-error' for error messages. The message content is properly escaped for output.
     *
     * @return void
     */
    private function render_notices(): void
    {
        foreach ($this->notices as $notice) {
            $class = (isset($notice['type']) && $notice['type'] === 'success') ? 'notice-success' : 'notice-error';
            printf('<div class="notice %1$s"><p>%2$s</p></div>', esc_attr($class), esc_html($notice['message']));
        }
    }

    /**
     * Handles the generation of a bulk CSV file for student results based on selected class, department, and exam.
     *
     * This method performs the following steps:
     * - Verifies the security nonce to prevent CSRF attacks.
     * - Validates that class, department, and exam selections are present and valid.
     * - Retrieves exam details from the database.
     * - Determines the exam year ID if not explicitly provided.
     * - Fetches the list of subjects for the selected class and department.
     * - Retrieves any custom field labels configured in the plugin options.
     * - Builds the CSV structure using the selected parameters.
     * - Streams the generated CSV file to the browser for download.
     *
     * Notices are added and the process is halted if any validation fails.
     *
     * @return void
     */
    private function handle_csv_generation(): void
    {
        if (
            ! isset($_POST['astgdrmsys_generate_bulk_csv_nonce']) ||
            ! wp_verify_nonce(sanitize_text_field(wp_unslash($_POST['astgdrmsys_generate_bulk_csv_nonce'])), 'astgdrmsys_generate_bulk_csv_action')
        ) {
            $this->add_notice(__('Security check failed. Please try again.', 'result-management-system-for-institutions'));
            return;
        }

        $class_id      = absint($this->selected['class']);
        $department_id = absint($this->selected['department']);
        $exam_value    = $this->selected['exam'];

        if (! $class_id) {
            $this->add_notice(__('Please select a class.', 'result-management-system-for-institutions'));
            return;
        }

        if (! $department_id) {
            $this->add_notice(__('Please select a department.', 'result-management-system-for-institutions'));
            return;
        }

        if (empty($exam_value)) {
            $this->add_notice(__('Please select an exam.', 'result-management-system-for-institutions'));
            return;
        }

        $exam_parts   = array_map('intval', explode(',', $exam_value));
        $exam_id      = $exam_parts[0] ?? 0;
        $exam_year_id = $exam_parts[1] ?? 0;

        if (! $exam_id) {
            $this->add_notice(__('The selected exam is invalid.', 'result-management-system-for-institutions'));
            return;
        }

        $exam_table = esc_sql($this->prefix . 'astgdrmsys_exam_name');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $exam = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT id, class_id, department, exam_name, exam_type, exam_year, exam_year_id, first_assessment_display_name, second_assessment_display_name, third_assessment_display_name, total_display_name FROM $exam_table WHERE id = %d",
                $exam_id
            ),
            ARRAY_A
        );

        if (! $exam) {
            $this->add_notice(__('The selected exam could not be found.', 'result-management-system-for-institutions'));
            return;
        }

        if ((int) ($exam['class_id'] ?? 0) !== $class_id) {
            $this->add_notice(__('The selected exam does not belong to the chosen class.', 'result-management-system-for-institutions'));
            return;
        }

        if (! $this->exam_matches_department($exam, $department_id)) {
            $this->add_notice(__('The selected exam does not belong to the chosen department.', 'result-management-system-for-institutions'));
            return;
        }

        if (! $exam_year_id) {
            $exam_year_id = isset($exam['exam_year_id']) ? (int) $exam['exam_year_id'] : 0;
        }

        $subjects = $this->get_subjects($class_id, $department_id);
        if (empty($subjects)) {
            $this->add_notice(__('No subjects were found for the chosen class and department.', 'result-management-system-for-institutions'));
            return;
        }

        $students = $this->get_students_for_exam($class_id, $exam_id, $exam_year_id);
        if (empty($students)) {
            $this->add_notice(__('No students were found for the selected exam.', 'result-management-system-for-institutions'));
            return;
        }

        $custom_fields = get_option('astgdrmsys_custom_field_labels', array());
        if (! is_array($custom_fields)) {
            $custom_fields = array();
        }

        $exam_labels = $this->resolve_exam_mark_labels($exam);

        $csv = $this->build_csv_structure(
            $class_id,
            $department_id,
            $exam_id,
            $exam_year_id,
            $exam,
            $exam_labels,
            $subjects,
            $custom_fields,
            $students
        );

        $filename = sprintf(
            'astgdrmsys-bulk-upload-class-%d-dept-%d-exam-%d.csv',
            $class_id,
            $department_id,
            $exam_id
        );

        $this->stream_csv($filename, $csv['header'], $csv['rows']);
    }

    /**
     * Adds a notice message to the internal notices array.
     *
     * @param string $message The notice message to display.
     * @param string $type    The type of notice (e.g., 'error', 'success'). Default is 'error'.
     *
     * @return void
     */
    private function add_notice(string $message, string $type = 'error'): void
    {
        $this->notices[] = array(
            'message' => $message,
            'type'    => $type,
        );
    }

    /**
     * Builds the structure for a CSV file header and a sample row based on class, department, exam, subjects, and custom fields.
     *
     * @param int   $class_id        The ID of the class.
     * @param int   $department_id   The ID of the department.
     * @param int   $exam_id         The ID of the exam.
     * @param int   $exam_year_id    The ID of the exam year.
     * @param array $exam            The exam details, including 'exam_type'.
     * @param array $exam_labels     The resolved display names for the exam assessments.
     * @param array $subjects        An array of subjects, each containing at least a 'subcode'.
     * @param array $custom_fields   An array of custom field labels to include in the CSV.
     *
     * @return array Returns an associative array with two keys:
     *               - 'header': The array of CSV column headers.
     *               - 'rows':   The student rows for the selected exam.
     */
    private function build_csv_structure(int $class_id, int $department_id, int $exam_id, int $exam_year_id, array $exam, array $exam_labels, array $subjects, array $custom_fields, array $students): array
    {
        $header = array(
            'Roll Number',
            'Registration Number',
            'Name',
            'Gender',
            'Father Name',
            'Mother Name',
            'Remark',
            'Class ID',
            'Department ID',
            'Section ID',
            'Exam Name ID',
            'Exam Year ID',
            'IsAbsent',
            'Student Image',
            'Fourth Subject Code',
            'Not Taken',
        );

        $custom_fields = array_values(array_filter($custom_fields));
        foreach ($custom_fields as $index => $label) {
            $position = $index + 1;
            $header[] = sprintf('Custom Field %d Name', $position);
            $header[] = sprintf('Custom Field %d Value', $position);
        }

        $exam_type         = isset($exam['exam_type']) ? $exam['exam_type'] : 'single';
        $single_label      = $exam_labels['single_mark_display_name'] ?? 'Mark';
        $practical_label   = $exam_labels['practical_mark_display_name'] ?? 'Practical';
        $class_mark_label  = $exam_labels['class_mark_display_name'] ?? 'Class';
        $final_mark_label  = $exam_labels['final_mark_display_name'] ?? 'Final';

        foreach ($subjects as $subject) {
            $subcode = isset($subject['subcode']) ? $subject['subcode'] : '';
            if ($exam_type === 'triple') {
                $header[] = sprintf('Subject %s Code', $subcode);
                $header[] = sprintf('Subject %s %s', $subcode, $practical_label);
                $header[] = sprintf('Subject %s %s', $subcode, $class_mark_label);
                $header[] = sprintf('Subject %s %s', $subcode, $final_mark_label);
            } elseif ($exam_type === 'composite') {
                $header[] = sprintf('Subject %s Code', $subcode);
                $header[] = sprintf('Subject %s %s', $subcode, $class_mark_label);
                $header[] = sprintf('Subject %s %s', $subcode, $final_mark_label);
            } else {
                $header[] = sprintf('Subject %s Code', $subcode);
                $header[] = sprintf('Subject %s %s', $subcode, $single_label);
            }
        }

        $rows = array();
        foreach ($students as $student) {
            $rows[] = $this->build_student_csv_row($student, $class_id, $department_id, $exam_id, $exam_year_id, $exam_type, $subjects, $custom_fields);
        }

        return array(
            'header' => $header,
            'rows'   => $rows,
        );
    }

    private function build_student_csv_row(array $student, int $class_id, int $department_id, int $exam_id, int $exam_year_id, string $exam_type, array $subjects, array $custom_fields): array
    {
        $marks = $this->normalize_marks($student['marks_data'] ?? array());
        $custom_field_values = $this->get_student_custom_field_values($student['custom_fields'] ?? '');

        $row = array(
            (string) ($student['sno'] ?? ''),
            (string) ($student['regno'] ?? ''),
            (string) ($student['name'] ?? ''),
            (string) ($student['gender'] ?? ''),
            (string) ($student['fname'] ?? ''),
            (string) ($student['mname'] ?? ''),
            (string) ($student['remark'] ?? ''),
            (string) $class_id,
            (string) ($student['department'] ?? $department_id),
            (string) ($student['section'] ?? ''),
            (string) $exam_id,
            (string) $exam_year_id,
            isset($student['is_absent']) ? (string) (int) $student['is_absent'] : '0',
            (string) ($student['student_image'] ?? ''),
            isset($marks['fourth_subject']) ? (string) $marks['fourth_subject'] : '',
            implode(',', $this->get_not_taken_subjects($marks)),
        );

        foreach ($custom_fields as $label) {
            $label = (string) $label;
            $row[] = $label;
            $row[] = $custom_field_values[strtolower($label)] ?? '';
        }

        foreach ($subjects as $subject) {
            $subcode = isset($subject['subcode']) ? (string) $subject['subcode'] : '';
            $subject_marks = $marks[$subcode] ?? array();

            if (! is_array($subject_marks)) {
                $subject_marks = array('final' => $subject_marks);
            }

            if ($exam_type === 'triple') {
                $row[] = $subcode;
                $row[] = isset($subject_marks['practical']) ? (string) $subject_marks['practical'] : '';
                $row[] = isset($subject_marks['class']) ? (string) $subject_marks['class'] : '';
                $row[] = isset($subject_marks['final']) ? (string) $subject_marks['final'] : '';
            } elseif ($exam_type === 'composite') {
                $row[] = $subcode;
                $row[] = isset($subject_marks['class']) ? (string) $subject_marks['class'] : '';
                $row[] = isset($subject_marks['final']) ? (string) $subject_marks['final'] : '';
            } else {
                $row[] = $subcode;
                $row[] = isset($subject_marks['final']) ? (string) $subject_marks['final'] : '';
            }
        }

        return $row;
    }

    private function normalize_marks($marks): array
    {
        if (is_string($marks)) {
            $marks = maybe_unserialize($marks);
        }

        if (isset($marks[0]) && is_array($marks[0])) {
            $marks = $marks[0];
        }

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

    private function get_student_custom_field_values($custom_fields): array
    {
        if (! is_string($custom_fields) || trim($custom_fields) === '') {
            return array();
        }

        $decoded = json_decode($custom_fields, true);
        if (! is_array($decoded)) {
            return array();
        }

        $values = array();
        foreach ($decoded as $field) {
            if (! is_array($field) || empty($field['name'])) {
                continue;
            }

            $values[strtolower((string) $field['name'])] = isset($field['value']) ? (string) $field['value'] : '';
        }

        return $values;
    }

    private function get_not_taken_subjects(array $marks): array
    {
        $subjects = array();
        foreach ($marks as $subject_code => $subject_marks) {
            if ('fourth_subject' === $subject_code || ! is_array($subject_marks)) {
                continue;
            }

            if (! empty($subject_marks['not_taken'])) {
                $subjects[] = (string) $subject_code;
            }
        }

        return $subjects;
    }

    /**
     * Streams a CSV file to the browser for download.
     *
     * This method sends appropriate headers to prompt the user to download a CSV file,
     * writes the provided header and rows to the output stream, and terminates the script.
     *
     * @param string $filename The name of the CSV file to be downloaded.
     * @param array $header An array of column headers for the CSV file.
     * @param array $rows An array of rows, where each row is an array of column values.
     *
     * @return void
     */
    private function stream_csv(string $filename, array $header, array $rows): void
    {
        nocache_headers();
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . sanitize_file_name($filename) . '"');
        header('Pragma: no-cache');
        header('Expires: 0');

        if (ob_get_length()) {
            ob_end_clean();
        }

        $output = fopen('php://output', 'w');
        fputcsv($output, $header);
        foreach ($rows as $row_data) {
            fputcsv($output, $row_data);
        }
        fclose($output);
        exit;
    }

    /**
     * Returns the 'selected' attribute for HTML <option> elements if the current value matches the given value.
     *
     * This method checks if the WordPress 'selected' function exists and uses it to generate the attribute.
     * If not, it performs a manual comparison and returns the appropriate attribute string.
     *
     * @param mixed $current The current value to compare.
     * @param mixed $value   The value to check against the current value.
     * @return string        The 'selected' attribute if values match, otherwise an empty string.
     */
    private function get_selected_attr($current, $value): string
    {
        if (function_exists('selected')) {
            return selected($current, $value, false);
        }

        return ((string) $current === (string) $value) ? ' selected="selected"' : '';
    }

    /**
     * Retrieves a list of classes from the database.
     *
     * Queries the database table for classes, returning an array of class IDs and names,
     * ordered alphabetically by class name.
     *
     * @return array List of classes, each as an associative array with 'id' and 'class' keys.
     */
    private function get_classes(): array
    {
        $table = esc_sql($this->prefix . 'astgdrmsys_class');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $this->wpdb->get_results("SELECT id, class FROM $table ORDER BY class ASC", ARRAY_A);
    }

    /**
     * Retrieves a list of departments for a given class ID.
     *
     * Queries the database for departments associated with the specified class,
     * ordered alphabetically by department name.
     *
     * @param int $class_id The ID of the class to retrieve departments for.
     * @return array An array of departments, each as an associative array with 'id' and 'department' keys.
     */
    private function get_departments(int $class_id): array
    {
        $table = esc_sql($this->prefix . 'astgdrmsys_department');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $this->wpdb->get_results(
            "SELECT id, department FROM $table ORDER BY department ASC",
            ARRAY_A
        );
    }

    /**
     * Retrieves a list of exams for a given class and optionally a department.
     *
     * If a department ID is provided and the corresponding department name is found,
     * the exams are filtered by both class and department. Otherwise, exams are filtered
     * by class only. Results are ordered by exam year (descending) and exam name (ascending).
     *
     * @param int $class_id       The ID of the class to retrieve exams for.
     * @param int $department_id  (Optional) The ID of the department to filter exams by.
     * @return array              An array of exams, each containing 'id', 'exam_name', 'exam_year', and 'exam_year_id'.
     */
    private function get_exams(int $class_id, int $department_id): array
    {
        if (! $class_id) {
            return array();
        }

        $table = esc_sql($this->prefix . 'astgdrmsys_exam_name');

        if ($department_id) {
            $department_name = $this->get_department_name($department_id);
            if (! empty($department_name)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
                return $this->wpdb->get_results(
                    $this->wpdb->prepare(
                        "SELECT id, exam_name, exam_year, exam_year_id FROM $table WHERE class_id = %d AND (department = %s OR department = %s) ORDER BY exam_year DESC, exam_name ASC",
                        $class_id,
                        (string) $department_id,
                        $department_name
                    ),
                    ARRAY_A
                );
            }
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT id, exam_name, exam_year, exam_year_id FROM $table WHERE class_id = %d ORDER BY exam_year DESC, exam_name ASC",
                $class_id
            ),
            ARRAY_A
        );
    }

    /**
     * Retrieves the name of a department based on its ID.
     *
     * Queries the database for the department name corresponding to the provided department ID.
     * Returns the department name as a string if found, or null if not found or if the ID is invalid.
     *
     * @param int $department_id The ID of the department to retrieve.
     * @return string|null The name of the department, or null if not found or invalid ID.
     */
    private function get_department_name(int $department_id): ?string
    {
        if (! $department_id) {
            return null;
        }

        $table = esc_sql($this->prefix . 'astgdrmsys_department');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $name = $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT department FROM $table WHERE id = %d",
                $department_id
            )
        );

        return $name ? (string) $name : null;
    }

    private function exam_matches_department(array $exam, int $department_id): bool
    {
        $exam_department = isset($exam['department']) ? (string) $exam['department'] : '';
        if ($exam_department === (string) $department_id) {
            return true;
        }

        $department_name = $this->get_department_name($department_id);
        return $department_name !== null && $exam_department === $department_name;
    }

    /**
     * Retrieves a list of subjects for a given class and department.
     *
     * Queries the database for subjects that match the specified class ID and department ID,
     * returning an array of subject codes and names ordered by subject code in ascending order.
     *
     * @param int $class_id      The ID of the class to filter subjects by.
     * @param int $department_id The ID of the department to filter subjects by.
     * @return array             An array of associative arrays, each containing 'subcode' and 'subname' keys.
     */
    private function get_subjects(int $class_id, int $department_id): array
    {
        $table = esc_sql($this->prefix . 'astgdrmsys_subject');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT subcode, subname FROM $table WHERE class = %d AND department = %d ORDER BY subcode ASC",
                $class_id,
                $department_id
            ),
            ARRAY_A
        );
    }

    private function get_students_for_exam(int $class_id, int $exam_id, int $exam_year_id): array
    {
        $result_table = esc_sql($this->prefix . 'astgdrmsys_student_result');
        $marks_table  = esc_sql($this->prefix . 'astgdrmsys_mark');

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT r.*, m.marks AS marks_data
                FROM {$result_table} r
                LEFT JOIN {$marks_table} m ON m.sid = r.sid
                WHERE r.class = %d AND r.exam_name = %d AND r.exam_year = %d
                ORDER BY CAST(r.sno AS UNSIGNED), r.sno ASC, r.name ASC",
                $class_id,
                $exam_id,
                $exam_year_id
            ),
            ARRAY_A
        );
    }
}


class ASTGDRMSYS_Partial_CSV_Generator
{
    use ASTGDRMSYS_Exam_Label_Helper;

    /** @var \wpdb */
    private $wpdb;

    /** @var string */
    private $prefix;

    /** @var array<int, array<string, string>> */
    private $notices = array();

    /** @var array<string, string> */
    private $selected = array(
        'class'   => '',
        'exam'    => '',
        'subject' => '',
    );

    /** @var bool */
    private $is_partial_request = false;

    public function __construct($wpdb)
    {
        $this->wpdb   = $wpdb;
        $this->prefix = $wpdb->prefix;

        if (! empty($_POST)) {
            $this->selected['class']   = isset($_POST['astgdrmsys_partial_class']) ? sanitize_text_field(wp_unslash($_POST['astgdrmsys_partial_class'])) : '';
            $this->selected['exam']    = isset($_POST['astgdrmsys_partial_exam']) ? sanitize_text_field(wp_unslash($_POST['astgdrmsys_partial_exam'])) : '';
            $this->selected['subject'] = isset($_POST['astgdrmsys_partial_subject']) ? sanitize_text_field(wp_unslash($_POST['astgdrmsys_partial_subject'])) : '';
        }

        if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['astgdrmsys_generate_partial_csv'])) {
            $this->is_partial_request = true;
            $this->handle_csv_generation();
        }
    }

    public function render_section(): void
    {
        $classes = $this->get_classes();
        $class_id = absint($this->selected['class']);

        $exams = $this->get_exams($class_id);
        $selected_exam_value = $this->selected['exam'];
        $exam_parts = $selected_exam_value !== '' ? array_map('intval', explode(',', $selected_exam_value)) : array();
        $selected_exam_id = $exam_parts[0] ?? 0;

        $subjects = $selected_exam_id ? $this->get_subjects_for_exam($selected_exam_id) : array();
        $selected_subject_value = $this->selected['subject'];

        echo "<div class='astgdrmsys-partial-section'>";
        echo "<div class='premium-page-header'>";
        echo "<div class='page-header-text'>";
        echo "<h1>" . esc_html__('Generate CSV for Partial Update', 'result-management-system-for-institutions') . "</h1>";
        echo "<p>" . esc_html__('Select Class, Exam, and Subject to create a CSV template for updating marks of a single subject.', 'result-management-system-for-institutions') . "</p>";
        echo "</div></div>";

        $this->render_notices();

        echo "<form method='post' class='astgdrmsys-partial-form' style='background: #ffffff; padding: 25px; border-radius: 8px; box-shadow: 0 2px 10px rgba(0,0,0,0.02); border: 1px solid #e2e8f0; margin-bottom: 30px;'>";
        wp_nonce_field('astgdrmsys_generate_partial_csv_action', 'astgdrmsys_generate_partial_csv_nonce');
        echo "<div style='display: flex; flex-wrap: wrap; gap: 20px; align-items: flex-end;'>";

        echo "<div class='filter-group' style='flex: 1; min-width: 200px;'>";
        echo "<label style='display: block; font-size: 11px; font-weight: 700; color: #1e293b; text-transform: uppercase; letter-spacing: 0.5px; margin-bottom: 8px;'>" . esc_html__('Select Class', 'result-management-system-for-institutions') . "</label>";
        echo "<select class='astgdrmsys-partial-class premium-input' name='astgdrmsys_partial_class' required style='width: 100%;'>";
        echo "<option value='' selected disabled>" . esc_html__('Select a Class', 'result-management-system-for-institutions') . "</option>";
        foreach ($classes as $class) {
            printf(
                '<option value="%1$d" %3$s>%2$s</option>',
                (int) $class['id'],
                esc_html($class['class']),
                $this->get_selected_attr($this->selected['class'], (string) $class['id'])
            );
        }
        echo "</select></div>";

        echo "<div class='filter-group' style='flex: 1; min-width: 200px;'>";
        echo "<label style='display: block; font-size: 11px; font-weight: 700; color: #1e293b; text-transform: uppercase; letter-spacing: 0.5px; margin-bottom: 8px;'>" . esc_html__('Select Exam', 'result-management-system-for-institutions') . "</label>";
        echo "<select class='astgdrmsys-partial-exam premium-input' name='astgdrmsys_partial_exam' required style='width: 100%;'>";
        if ($class_id && ! empty($exams)) {
            echo "<option value='' disabled>" . esc_html__('Select Exam', 'result-management-system-for-institutions') . "</option>";
            foreach ($exams as $exam) {
                $value = $exam['id'] . ',' . $exam['exam_year_id'];
                $label = $exam['exam_name'];
                if (! empty($exam['exam_year'])) {
                    $label .= ' - ' . $exam['exam_year'];
                }
                printf(
                    '<option value="%1$s" %3$s>%2$s</option>',
                    esc_attr($value),
                    esc_html($label),
                    $this->get_selected_attr($this->selected['exam'], $value)
                );
            }
        } else {
            echo "<option value='' selected disabled>" . esc_html__('Select Class First', 'result-management-system-for-institutions') . "</option>";
        }
        echo "</select></div>";

        echo "<div class='filter-group' style='flex: 1; min-width: 200px;'>";
        echo "<label style='display: block; font-size: 11px; font-weight: 700; color: #1e293b; text-transform: uppercase; letter-spacing: 0.5px; margin-bottom: 8px;'>" . esc_html__('Select Subject', 'result-management-system-for-institutions') . "</label>";
        echo "<select class='astgdrmsys-partial-subject premium-input' name='astgdrmsys_partial_subject' required style='width: 100%;'>";
        if ($selected_exam_id && ! empty($subjects)) {
            echo "<option value='' disabled>" . esc_html__('Select Subject', 'result-management-system-for-institutions') . "</option>";
            foreach ($subjects as $subject) {
                $value = $subject['id'] . '|' . $subject['subcode'];
                $label = $subject['subname'] . ' - ' . $subject['subcode'];
                printf(
                    '<option value="%1$s" %3$s>%2$s</option>',
                    esc_attr($value),
                    esc_html($label),
                    $this->get_selected_attr($selected_subject_value, $value)
                );
            }
        } else {
            $placeholder = $selected_exam_id ? esc_html__('No subjects found for the selected exam', 'result-management-system-for-institutions') : esc_html__('Select Exam First', 'result-management-system-for-institutions');
            echo "<option value='' selected disabled>{$placeholder}</option>";
        }
        echo "</select></div>";

        echo "<div class='filter-actions' style='margin-top: 0;'>";
        echo "<input class='btn btn-primary hero-btn submit-btn' style='height: 42px; margin-bottom: 0;' type='submit' name='astgdrmsys_generate_partial_csv' value='" . esc_attr__('Generate CSV', 'result-management-system-for-institutions') . "'>";
        echo "</div>";

        echo "</div>";
        echo "</form>";
        echo "</div>";
    }

    public function should_be_active(): bool
    {
        return $this->is_partial_request || ! empty($this->notices);
    }

    private function render_notices(): void
    {
        foreach ($this->notices as $notice) {
            $class = (isset($notice['type']) && $notice['type'] === 'success') ? 'notice-success' : 'notice-error';
            printf('<div class="notice %1$s"><p>%2$s</p></div>', esc_attr($class), esc_html($notice['message']));
        }
    }

    private function add_notice(string $message, string $type = 'error'): void
    {
        $this->notices[] = array(
            'message' => $message,
            'type'    => $type,
        );
    }

    private function handle_csv_generation(): void
    {
        if (
            ! isset($_POST['astgdrmsys_generate_partial_csv_nonce']) ||
            ! wp_verify_nonce(sanitize_text_field(wp_unslash($_POST['astgdrmsys_generate_partial_csv_nonce'])), 'astgdrmsys_generate_partial_csv_action')
        ) {
            $this->add_notice(__('Security check failed. Please try again.', 'result-management-system-for-institutions'));
            return;
        }

        $class_id = absint($this->selected['class']);
        if (! $class_id) {
            $this->add_notice(__('Please select a class.', 'result-management-system-for-institutions'));
            return;
        }

        $exam_value = $this->selected['exam'];
        if ($exam_value === '') {
            $this->add_notice(__('Please select an exam.', 'result-management-system-for-institutions'));
            return;
        }

        $exam_parts = array_map('intval', explode(',', $exam_value));
        $exam_id = $exam_parts[0] ?? 0;
        $exam_year_id = $exam_parts[1] ?? 0;

        if (! $exam_id) {
            $this->add_notice(__('The selected exam is invalid.', 'result-management-system-for-institutions'));
            return;
        }

        $subject_value = $this->selected['subject'];
        if ($subject_value === '') {
            $this->add_notice(__('Please select a subject.', 'result-management-system-for-institutions'));
            return;
        }

        $subject_parts = explode('|', $subject_value);
        $subject_id = isset($subject_parts[0]) ? (int) $subject_parts[0] : 0;
        $subject_code = $subject_parts[1] ?? '';
        if (! $subject_id || $subject_code === '') {
            $this->add_notice(__('The selected subject is invalid.', 'result-management-system-for-institutions'));
            return;
        }

        $exam = $this->get_exam_details($exam_id);
        if (! $exam) {
            $this->add_notice(__('The selected exam could not be found.', 'result-management-system-for-institutions'));
            return;
        }

        if ((int) ($exam['class_id'] ?? 0) !== $class_id) {
            $this->add_notice(__('The selected exam does not belong to the chosen class.', 'result-management-system-for-institutions'));
            return;
        }

        if (! $exam_year_id && isset($exam['exam_year_id'])) {
            $exam_year_id = (int) $exam['exam_year_id'];
        }

        $exam_labels = $this->resolve_exam_mark_labels($exam);

        $subjects = $this->get_subjects_for_exam($exam_id);
        $subject_ids = array_column($subjects, 'id');
        if (! in_array($subject_id, array_map('intval', $subject_ids), true)) {
            $this->add_notice(__('The selected subject is not available for the chosen exam.', 'result-management-system-for-institutions'));
            return;
        }

        $subject = $this->get_subject_by_id($subject_id);
        if (! $subject || (string) $subject['subcode'] !== (string) $subject_code) {
            $this->add_notice(__('The selected subject could not be found.', 'result-management-system-for-institutions'));
            return;
        }

        $students = $this->get_students($class_id, $exam_id, $exam_year_id);
        if (empty($students)) {
            $this->add_notice(__('No students were found for the selected exam.', 'result-management-system-for-institutions'));
            return;
        }

        $student_ids = wp_list_pluck($students, 'sid');
        $marks_map = $this->get_marks_for_students($student_ids);

        $csv = $this->build_partial_csv($students, $marks_map, (string) $subject_code, $exam, $exam_labels);
        if (empty($csv['rows'])) {
            $this->add_notice(__('Unable to build the CSV file for the selected subject.', 'result-management-system-for-institutions'));
            return;
        }

        $safe_subject = preg_replace('/[^A-Za-z0-9_-]/', '', (string) $subject_code);
        $filename = sprintf('astgdrmsys-partial-update-class-%d-exam-%d-subject-%s.csv', $class_id, $exam_id, $safe_subject);
        $this->stream_csv($filename, $csv['header'], $csv['rows']);
    }

    private function get_classes(): array
    {
        $table = esc_sql($this->prefix . 'astgdrmsys_class');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $this->wpdb->get_results("SELECT id, class FROM {$table} ORDER BY class ASC", ARRAY_A);
    }

    private function get_exams(int $class_id): array
    {
        if (! $class_id) {
            return array();
        }

        $table = esc_sql($this->prefix . 'astgdrmsys_exam_name');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT id, exam_name, exam_year, exam_year_id, exam_type, department, class_id FROM {$table} WHERE class_id = %d ORDER BY exam_year DESC, exam_name ASC",
                $class_id
            ),
            ARRAY_A
        );
    }

    private function get_exam_details(int $exam_id): ?array
    {
        if (! $exam_id) {
            return null;
        }

        $table = esc_sql($this->prefix . 'astgdrmsys_exam_name');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $exam = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT id, class_id, exam_year_id, exam_type, department, first_assessment_display_name, second_assessment_display_name, third_assessment_display_name, total_display_name FROM {$table} WHERE id = %d",
                $exam_id
            ),
            ARRAY_A
        );

        return $exam ?: null;
    }

    private function get_subjects_for_exam(int $exam_id): array
    {
        $exam = $this->get_exam_details($exam_id);
        if (! $exam) {
            return array();
        }

        $class_id = isset($exam['class_id']) ? (int) $exam['class_id'] : 0;
        $department_value = isset($exam['department']) ? (string) $exam['department'] : '';

        if ($class_id <= 0 || ! function_exists('\ASTGDRMSYS\ResultManagement\astgdrmsys_get_subjects_for_exam')) {
            return array();
        }

        return \ASTGDRMSYS\ResultManagement\astgdrmsys_get_subjects_for_exam($this->wpdb, $class_id, $department_value);
    }

    private function get_subject_by_id(int $subject_id): ?array
    {
        if (! $subject_id) {
            return null;
        }

        $table = esc_sql($this->prefix . 'astgdrmsys_subject');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $subject = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT id, subcode, subname FROM {$table} WHERE id = %d",
                $subject_id
            ),
            ARRAY_A
        );

        return $subject ?: null;
    }

    private function get_students(int $class_id, int $exam_id, int $exam_year_id): array
    {
        $table = esc_sql($this->prefix . 'astgdrmsys_student_result');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT sid, sno, is_absent FROM {$table} WHERE class = %d AND exam_name = %d AND exam_year = %d ORDER BY CAST(sno AS UNSIGNED), sno ASC",
                $class_id,
                $exam_id,
                $exam_year_id
            ),
            ARRAY_A
        );
    }

    private function get_marks_for_students(array $student_ids): array
    {
        if (empty($student_ids)) {
            return array();
        }

        $placeholders = implode(',', array_fill(0, count($student_ids), '%d'));
        $table = esc_sql($this->prefix . 'astgdrmsys_mark');
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery
        $prepared = $this->wpdb->prepare(
            "SELECT sid, marks FROM {$table} WHERE sid IN ({$placeholders})",
            $student_ids
        );
        $rows = $this->wpdb->get_results($prepared, ARRAY_A);

        $marks_map = array();
        foreach ($rows as $row) {
            $data = maybe_unserialize($row['marks']);
            if (! is_array($data)) {
                $data = array();
            }
            $marks_map[(int) $row['sid']] = $data;
        }

        return $marks_map;
    }

    private function build_partial_csv(array $students, array $marks_map, string $subject_code, array $exam, array $exam_labels): array
    {
        $exam_type = isset($exam['exam_type']) ? $exam['exam_type'] : 'single';

        $header = array('Roll Number', 'IsAbsent', 'Subject Code');
        if ($exam_type === 'triple') {
            $header[] = $exam_labels['practical_mark_display_name'] ?? 'Practical Mark';
            $header[] = $exam_labels['class_mark_display_name'] ?? 'Class Mark';
            $header[] = $exam_labels['final_mark_display_name'] ?? 'Final Mark';
        } elseif ($exam_type === 'composite') {
            $header[] = $exam_labels['class_mark_display_name'] ?? 'Class Mark';
            $header[] = $exam_labels['final_mark_display_name'] ?? 'Final Mark';
        } else {
            $header[] = $exam_labels['single_mark_display_name'] ?? 'Subject Mark';
        }

        $rows = array();
        foreach ($students as $student) {
            $sid = (int) $student['sid'];
            $marks = $marks_map[$sid] ?? array();
            if (! is_array($marks)) {
                $marks = array();
            }

            $row = array(
                (string) $student['sno'],
                isset($student['is_absent']) ? (string) $student['is_absent'] : '0',
                (string) $subject_code,
            );

            if ($exam_type === 'triple') {
                $subject_marks = isset($marks[$subject_code]) && is_array($marks[$subject_code]) ? $marks[$subject_code] : array();
                $row[] = isset($subject_marks['practical']) ? (string) $subject_marks['practical'] : '';
                $row[] = isset($subject_marks['class']) ? (string) $subject_marks['class'] : '';
                $row[] = isset($subject_marks['final']) ? (string) $subject_marks['final'] : '';
            } elseif ($exam_type === 'composite') {
                $subject_marks = isset($marks[$subject_code]) && is_array($marks[$subject_code]) ? $marks[$subject_code] : array();
                $row[] = isset($subject_marks['class']) ? (string) $subject_marks['class'] : '';
                $row[] = isset($subject_marks['final']) ? (string) $subject_marks['final'] : '';
            } else {
                $mark = $marks[$subject_code] ?? '';
                if (is_array($mark)) {
                    $mark = '';
                }
                $row[] = $mark !== '' ? (string) $mark : '';
            }

            $rows[] = $row;
        }

        return array(
            'header' => $header,
            'rows'   => $rows,
        );
    }

    private function stream_csv(string $filename, array $header, array $rows): void
    {
        nocache_headers();
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . sanitize_file_name($filename) . '"');
        header('Pragma: no-cache');
        header('Expires: 0');

        if (ob_get_length()) {
            ob_end_clean();
        }

        $output = fopen('php://output', 'w');
        fputcsv($output, $header);
        foreach ($rows as $row_data) {
            fputcsv($output, $row_data);
        }
        fclose($output);
        exit;
    }

    private function get_selected_attr($current, $value): string
    {
        if (function_exists('selected')) {
            return selected($current, $value, false);
        }

        return ((string) $current === (string) $value) ? ' selected="selected"' : '';
    }
}


/**
 * Initializes and renders the Bulk CSV Generator page for the Result Management System plugin.
 *
 * - Instantiates the ASTGDRMSYS_Bulk_CSV_Generator class with the global $wpdb object for database interactions.
 * - Calls the render_page() method to display the bulk CSV generation interface in the WordPress admin.
 *
 * @global wpdb $wpdb WordPress database abstraction object.
 * @see ASTGDRMSYS_Bulk_CSV_Generator
 */
global $wpdb;
$astgdrmsys_bulk_csv_generator    = new ASTGDRMSYS_Bulk_CSV_Generator($wpdb);
$astgdrmsys_partial_csv_generator = new ASTGDRMSYS_Partial_CSV_Generator($wpdb);
$astgdrmsys_bulk_csv_generator->render_page($astgdrmsys_partial_csv_generator);
