<?php
/**
 * Dashboard AJAX Handler
 * 
 * Handles all AJAX requests for the dashboard
 * 
 * @package Spacetree
 * @subpackage Dashboard
 * @since 2.0.0
 */

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

// Include dashboard class
require_once plugin_dir_path( __FILE__ ) . 'class-dashboard.php';

/**
 * Main AJAX router for dashboard requests
 * 
 * Routes different dashboard tab requests to appropriate handlers
 * Uses cached data when available to avoid recalculation
 */
function proqyz_dashboard_ajax_router() {
    // Set timezone from WordPress
    proqyz_set_timezone_from_wp();
    
    // Verify user is logged in
    if ( ! is_user_logged_in() ) {
        wp_send_json_error( array(
            'message' => 'User not logged in',
            'code' => 401
        ) );
    }
    
    $current_user_id = get_current_user_id();
    
    // Verify nonce for security
    if ( ! isset( $_POST['nonce'] ) || ! wp_verify_nonce( $_POST['nonce'], 'proqyz_dashboard_ajax' ) ) {
        wp_send_json_error( array(
            'message' => 'Invalid nonce',
            'code' => 403
        ) );
    }
    
    // Check if cached data is provided (to avoid recalculation)
    $use_cache = false;
    $cached_user = null;
    $cached_courses = null;
    $cached_quizzes = null;
    
    if ( isset( $_POST['cached_user'] ) && ! empty( $_POST['cached_user'] ) ) {
        $cached_user = json_decode( stripslashes( $_POST['cached_user'] ) );
        
        // Validate cached user matches current user (security check)
        if ( $cached_user && isset( $cached_user->user_id ) && $cached_user->user_id == $current_user_id ) {
            $use_cache = true;
            
            // Get cached courses and quizzes
            if ( isset( $_POST['cached_courses'] ) ) {
                $cached_courses = json_decode( stripslashes( $_POST['cached_courses'] ) );
            }
            if ( isset( $_POST['cached_quizzes'] ) ) {
                $cached_quizzes = json_decode( stripslashes( $_POST['cached_quizzes'] ) );
            }
        } else {
            // Session changed - force reload
            wp_send_json_error( array(
                'message' => 'User session has changed, please reload the page',
                'code' => 403,
                'reload' => true
            ) );
        }
    }
    
    // Initialize dashboard (only if not using cache)
    $dashboard = null;
    if ( ! $use_cache ) {
        $dashboard = new ST_Dashboard( $current_user_id );
    }
    
    // Get action
    $action = isset( $_POST['action'] ) ? sanitize_text_field( $_POST['action'] ) : '';
    
    // Parse filters
    $filters = isset( $_POST['filters'] ) ? (object) $_POST['filters'] : (object) array();
    $filters = proqyz_sanitize_filters( $filters );
    
    // Route to appropriate handler
    switch ( $action ) {
        case 'proqyz_ajax_dashboard_overall':
            proqyz_handle_dashboard_overall( $dashboard, $filters, $use_cache, $cached_user, $cached_courses, $cached_quizzes );
            break;
            
        case 'proqyz_ajax_dashboard_listening':
            proqyz_handle_dashboard_category( $dashboard, $filters, 'ielts-listening', $use_cache, $cached_user, $cached_courses );
            break;
            
        case 'proqyz_ajax_dashboard_reading':
            proqyz_handle_dashboard_category( $dashboard, $filters, 'ielts-reading', $use_cache, $cached_user, $cached_courses );
            break;
            
        case 'proqyz_ajax_dashboard_writing':
            proqyz_handle_dashboard_category( $dashboard, $filters, 'ielts-writing', $use_cache, $cached_user, $cached_courses );
            break;
            
        case 'proqyz_ajax_dashboard_speaking':
            proqyz_handle_dashboard_category( $dashboard, $filters, 'ielts-speaking', $use_cache, $cached_user, $cached_courses );
            break;
            
        case 'proqyz_ajax_dashboard_gov_exam':
            proqyz_handle_dashboard_category( $dashboard, $filters, 'gov-exam', $use_cache, $cached_user, $cached_courses );
            break;
            
        case 'proqyz_ajax_dashboard_pdf_quiz':
            proqyz_handle_dashboard_category( $dashboard, $filters, 'proqyz-pdf-quiz', $use_cache, $cached_user, $cached_courses );
            break;
            
        case 'proqyz_ajax_dashboard_abandoned_tests':
            proqyz_handle_dashboard_abandoned_tests( $dashboard, $filters, $use_cache, $cached_user, $cached_courses );
            break;
            
        default:
            wp_send_json_error( array(
                'message' => 'Invalid action',
                'code' => 400
            ) );
    }
}

/**
 * Handle dashboard overall tab
 */
function proqyz_handle_dashboard_overall( $dashboard, $filters, $use_cache = false, $cached_user = null, $cached_courses = null, $cached_quizzes = null ) {
    $recalculated = false;
    
    // Check if cache is valid and has data
    $cache_is_valid = $use_cache && 
                     $cached_courses && 
                     $cached_quizzes && 
                     ( is_array( $cached_courses ) && count( $cached_courses ) > 0 );
    
    // Get pagination parameters
    $per_page = isset( $filters->per_page ) ? absint( $filters->per_page ) : 10;
    $per_page = max( 1, min( $per_page, 100 ) );
    $current_page = isset( $filters->current_page ) ? max( 1, absint( $filters->current_page ) ) : 1;
    
    // Build args for get_dashboard_overall
    $args = array(
        'per_page'     => $per_page,
        'page'         => $current_page,
        'course_id'    => isset( $filters->course ) ? sanitize_text_field( $filters->course ) : 'all',
        'session_type' => isset( $filters->session_type ) ? sanitize_text_field( $filters->session_type ) : 'all',
        'user_id'      => isset( $filters->user ) ? sanitize_text_field( $filters->user ) : 'all',
        'category'     => isset( $filters->category ) ? sanitize_text_field( $filters->category ) : 'all',
    );
    
    if ( $cache_is_valid ) {
        // Use cached data - no recalculation needed
        $courses = $cached_courses;
        $quiz_analytics = $cached_quizzes;
        $widgets = proqyz_generate_widgets_from_cache( $cached_user, $cached_courses, $cached_quizzes );
        
        // Create lightweight instance (skip heavy calculations, only evaluation settings)
        $dashboard = new ST_Dashboard( get_current_user_id(), true );
        
        // Manually set cached data to avoid recalculation
        $dashboard->set_cached_data( $cached_user, $cached_courses, $cached_quizzes );
        
        // Render widgets HTML from cached data
        $widgets_html = $dashboard->render_widgets( $widgets );
        
        // Fetch recent tests with filters (pass pre-rendered widgets)
        $result = $dashboard->get_dashboard_overall( $args, $widgets_html );
        
    } else {
        // Cache is empty or invalid - do full calculation
        if ( ! $dashboard ) {
            $dashboard = new ST_Dashboard( get_current_user_id() );
        }
        
        $result = $dashboard->get_dashboard_overall( $args );
        $courses = $dashboard->get_courses();
        $quiz_analytics = $dashboard->get_quizzes_analytics();
        $widgets = $dashboard->get_widgets_by_role();
        
        $recalculated = true;
    }
    
    wp_send_json_success( array(
        'has_html' => true,
        'html' => array(
            'page'       => $result['html'],
            'pagination' => '',
            'filters'    => ''
        ),
        'has_json' => true,
        'json' => array(
            'widgets'        => $widgets,
            'courses'        => $courses,
            'quiz_analytics' => $quiz_analytics
        ),
        'used_cache'   => $cache_is_valid,
        'recalculated' => $recalculated
    ) );
}

/**
 * Handle category-specific dashboard tabs
 */
function proqyz_handle_dashboard_category( $dashboard, $filters, $category, $use_cache = false, $cached_user = null, $cached_courses = null ) {
    global $wpdb;
    
    $recalculated = false;
    
    // Check if cache is valid and has data
    $cache_is_valid = $use_cache && 
                     $cached_courses && 
                     ( is_array( $cached_courses ) && count( $cached_courses ) > 0 );
    
    // Get pagination parameters
    $per_page = isset( $filters->per_page ) ? absint( $filters->per_page ) : 10;
    $per_page = max( 1, min( $per_page, 100 ) );
    $current_page = isset( $filters->current_page ) ? max( 1, absint( $filters->current_page ) ) : 1;
    
    // Get review filter for writing/speaking
    $review_status = isset( $filters->review ) ? absint( $filters->review ) : null;
    
    // Initialize dashboard (use cache or create new instance)
    if ( $cache_is_valid ) {
        // Create lightweight instance (skip heavy calculations, only evaluation settings)
        $dashboard = new ST_Dashboard( get_current_user_id(), true );
        
        // Manually set cached data to avoid recalculation (including quiz analytics for widgets)
        $dashboard->set_cached_data( $cached_user, $cached_courses, null );
        
        $courses = $cached_courses;
    } else {
        // Full calculation needed
        if ( ! $dashboard ) {
            $dashboard = new ST_Dashboard( get_current_user_id() );
        }
        $courses = $dashboard->get_courses();
        $recalculated = true;
    }
    
    // Get tests for category (always fetches fresh from DB - this is the data we need)
    $result = $dashboard->get_tests_by_category( $category, array(
        'per_page'     => $per_page,
        'page'         => $current_page,
        'review'       => $review_status,
        'course_id'    => isset( $filters->course ) && $filters->course !== 'all' ? $filters->course : null,
        'session_type' => isset( $filters->session_type ) && $filters->session_type !== 'all' ? $filters->session_type : null,
        'user_id'      => isset( $filters->user ) && $filters->user !== 'all' ? absint( $filters->user ) : null
    ) );
    
    // Prepare current filter values to preserve selections
    $current_filters = array(
        'course'       => isset( $filters->course ) ? $filters->course : 'all',
        'session_type' => isset( $filters->session_type ) ? $filters->session_type : 'all',
        'user'         => isset( $filters->user ) ? $filters->user : 'all',
    );
    
    // Generate HTML components
    $table_html = $dashboard->render_tests_table( $result['tests'], $category, $review_status );
    $pagination_html = $dashboard->render_pagination( $result['pagination'] );
    $filters_html = $dashboard->render_filters( $category, $review_status, $current_filters );
    
    // Wrap everything in test-history section
    $complete_html = $dashboard->render_test_history_section( $filters_html, $table_html, $pagination_html, "category-tests" );
    
    wp_send_json_success( array(
        'has_html' => true,
        'html' => array(
            'page' => $complete_html,
            'pagination' => '',
            'filters' => ''
        ),
        'has_json' => true,
        'json' => array(
            'tests' => $result['tests'],
            'pagination' => $result['pagination'],
            'category' => $category,
            'courses' => $courses // Return (possibly updated) courses for frontend cache
        ),
        'used_cache' => $cache_is_valid,
        'recalculated' => $recalculated
    ) );
}

/**
 * Sanitize filter inputs
 */
function proqyz_sanitize_filters( $filters ) {
    $sanitized = new stdClass();
    
    if ( isset( $filters->per_page ) ) {
        $sanitized->per_page = absint( $filters->per_page );
    }
    
    if ( isset( $filters->current_page ) ) {
        $sanitized->current_page = absint( $filters->current_page );
    }
    
    if ( isset( $filters->category ) ) {
        $sanitized->category = sanitize_text_field( $filters->category );
    }
    
    if ( isset( $filters->review ) ) {
        $sanitized->review = absint( $filters->review );
    }
    
    if ( isset( $filters->course ) ) {
        $sanitized->course = sanitize_text_field( $filters->course );
    }
    
    if ( isset( $filters->session_type ) ) {
        $sanitized->session_type = sanitize_text_field( $filters->session_type );
    }
    
    if ( isset( $filters->user ) ) {
        $sanitized->user = sanitize_text_field( $filters->user );
    }
    
    return $sanitized;
}

/**
 * Generate widgets from cached data without recalculation
 * 
 * @param object $cached_user Cached user data
 * @param array $cached_courses Cached courses
 * @param object $cached_quizzes Cached quiz analytics
 * @return array Widget objects
 */
function proqyz_generate_widgets_from_cache( $cached_user, $cached_courses, $cached_quizzes ) {
    global $ST_OPTION_st_proqyz__IELTS_LMS, $ST_OPTION_st_proqyz__GOV_LMS, $ST_OPTION_st_proqyz__PDF_QUIZ_LMS;
    
    $enable_ielts_lms = get_option( $ST_OPTION_st_proqyz__IELTS_LMS, 0 );
    $enable_gov_lms = get_option( $ST_OPTION_st_proqyz__GOV_LMS, 0 );
    $enable_proqyz_pdfquiz = get_option( $ST_OPTION_st_proqyz__PDF_QUIZ_LMS, 0 );
    
    $widgets = array();
    
    // Calculate total tests from cached data
    $total_tests_taken = 0;
    if ( $enable_ielts_lms == 1 && isset( $cached_quizzes->ielts_listenings ) ) {
        $total_tests_taken += (int) $cached_quizzes->ielts_listenings;
        $total_tests_taken += (int) $cached_quizzes->ielts_readings;
        $total_tests_taken += (int) $cached_quizzes->ielts_writings;
        $total_tests_taken += (int) $cached_quizzes->ielts_speakings;
    }
    
    if ( $enable_gov_lms == 1 && isset( $cached_quizzes->competitive_exams ) ) {
        $total_tests_taken += (int) $cached_quizzes->competitive_exams;
    }
    
    if ( $enable_proqyz_pdfquiz == 1 && isset( $cached_quizzes->proqyz_pdf_quiz ) ) {
        $total_tests_taken += (int) $cached_quizzes->proqyz_pdf_quiz;
    }
    
    // Widget: Total Tests
    $widgets[] = (object) array(
        'id' => 'total-tests',
        'icon' => 'ioticon-document',
        'name' => 'Total Tests Taken',
        'value' => $total_tests_taken,
        'type' => 'number'
    );
    
    // Role-specific widgets
    $role = isset( $cached_user->role ) ? $cached_user->role : 'student';
    
    if ( $role === 'admin' || $role === 'teacher' ) {
        // Courses widget
        $widgets[] = (object) array(
            'id' => 'courses',
            'icon' => 'ioticon-book-open',
            'name' => 'Courses',
            'value' => count( $cached_courses ),
            'type' => 'number'
        );
        
        // IELTS widgets
        if ( $enable_ielts_lms == 1 ) {
            $writings_unreviewed = isset( $cached_quizzes->ielts_writings_unreviewed ) ? (int) $cached_quizzes->ielts_writings_unreviewed : 0;
            $speakings_unreviewed = isset( $cached_quizzes->ielts_speakings_unreviewed ) ? (int) $cached_quizzes->ielts_speakings_unreviewed : 0;
            
            $widgets[] = (object) array(
                'id' => 'writings-review',
                'icon' => 'ioticon-edit-3',
                'name' => 'Writings Under Review',
                'value' => $writings_unreviewed,
                'type' => 'number',
                'alert' => $writings_unreviewed > 0
            );
            
            $widgets[] = (object) array(
                'id' => 'speakings-review',
                'icon' => 'ioticon-announcement-speaker',
                'name' => 'Speakings Under Review',
                'value' => $speakings_unreviewed,
                'type' => 'number',
                'alert' => $speakings_unreviewed > 0
            );
        }
        
        // Students widget
        $students_count = isset( $cached_user->students_count ) ? (int) $cached_user->students_count : 0;
        $widgets[] = (object) array(
            'id' => 'total-students',
            'icon' => 'ioticon-student',
            'name' => 'Total Students',
            'value' => $students_count,
            'type' => 'number'
        );
    } elseif ( $role === 'student' ) {
        // Courses widget for students
        $widgets[] = (object) array(
            'id' => 'courses',
            'icon' => 'ioticon-book-open',
            'name' => 'Courses',
            'value' => count( $cached_courses ),
            'type' => 'number'
        );
    }
    
    return $widgets;
}

/**
 * Set timezone from WordPress settings
 */
function proqyz_set_timezone_from_wp() {
    $timezone_string = get_option( 'timezone_string' );
    
    if ( ! $timezone_string ) {
        $gmt_offset = get_option( 'gmt_offset' );
        $timezone_string = timezone_name_from_abbr( '', $gmt_offset * 3600, false );
    }
    
    if ( $timezone_string ) {
        date_default_timezone_set( $timezone_string );
    } else {
        date_default_timezone_set( 'UTC' );
    }
}

/**
 * Handle delete result AJAX request
 */
function proqyz_dashboard_ajax_delete_result() {
    // Verify user is logged in
    if ( ! is_user_logged_in() ) {
        wp_send_json_error( array(
            'message' => 'User not logged in',
            'code' => 401
        ) );
    }
    
    // Verify nonce
    if ( ! isset( $_POST['nonce'] ) || ! wp_verify_nonce( $_POST['nonce'], 'proqyz_dashboard_ajax' ) ) {
        wp_send_json_error( array(
            'message' => 'Invalid nonce',
            'code' => 403
        ) );
    }
    
    // Get and validate result ID
    if ( ! isset( $_POST['id'] ) || empty( $_POST['id'] ) ) {
        wp_send_json_error( array(
            'message' => 'Result ID is required',
            'code' => 400
        ) );
    }
    
    global $wpdb,
    $table_proqyz_v3_progress_submissions,
	$table_proqyz_v3_progress_submissions_meta, 
	$table_proqyz_v3_progress_meta_submissions,
	$table_proqyz_v3_submissions_review,
	$table_proqyz_v3_submissions_review_meta,
	$table_proqyz_v3_submissions_versioning,	
	$table_proqyz_v3_progress_submissions_user_response,
	$table_proqyz_v3_progress_submissions_session,
	$table_proqyz_v3_progress_submissions_score,
	$table_proqyz_v3_submissions_evaluation_meta;
    
    $result_id = absint( $_POST['id'] );
    # [core] submissions table
    $submissions_table = $table_proqyz_v3_progress_submissions;
    # [core] submissions meta table
    $meta_table = $table_proqyz_v3_progress_submissions_meta;
    # [core] speaking progress meta submissions table
    $progress_meta_submissions_table = $table_proqyz_v3_progress_meta_submissions;
    # [core] score table
    $score_table = $table_proqyz_v3_progress_submissions_score;
    # [core] user_response table
    $user_response_table = $table_proqyz_v3_progress_submissions_user_response;
    # [core] session table
    $session_table = $table_proqyz_v3_progress_submissions_session;
    # [core] evaluation meta table
    $evaluation_meta_table = $table_proqyz_v3_submissions_evaluation_meta;
    # [core] review table
    $review_table = $table_proqyz_v3_submissions_review;
    # [core] review meta table
    $review_meta_table = $table_proqyz_v3_submissions_review_meta;
    # [core] versioning table
    $versioning_table = $table_proqyz_v3_submissions_versioning;



    
    // Get submission to check permissions
    $submission = $wpdb->get_row( $wpdb->prepare(
        "SELECT * FROM {$submissions_table} WHERE ID = %d AND status = 1",
        $result_id
    ), ARRAY_A );
    
    if ( ! $submission ) {
        wp_send_json_error( array(
            'message' => 'Result not found',
            'code' => 404
        ) );
    }
    
    // Check permissions (user can only delete their own results unless admin/teacher)
    $current_user = wp_get_current_user();
    $current_user_id = get_current_user_id();
    $is_admin_or_teacher = in_array( 'administrator', $current_user->roles ) || 
                           in_array( 'teacher', $current_user->roles ) || 
                           in_array( 'instructor', $current_user->roles ) || 
                           in_array( 'trainer', $current_user->roles );


    if ( $submission['user_id'] != $current_user_id) {
        if( !$is_admin_or_teacher ) {
            wp_send_json_error( array(
                'message' => 'Permission denied',
                'code' => 403
            ) );
        }
    }
    // STEP 3-5: Delete associated files for speaking tests (using migrated_from_id)
    if ( $submission['quiz_category'] === 'ielts-speaking' ) {
        // Use quiz_progress_meta table for speaking audio files (permanent table)
        $speaking_meta_records = $wpdb->get_results( $wpdb->prepare(
            "SELECT `json` FROM {$progress_meta_submissions_table} 
            WHERE session_id = %d",
            $result_id
        ) );
        
        if ( ! empty( $speaking_meta_records ) ) {
            // STEP 4: Find and safe remove file from uploads directory
            foreach ( $speaking_meta_records as $meta ) {
                $json_data = @unserialize( $meta->json );
                if ( is_array( $json_data ) || is_object( $json_data ) ) {
                    $json_data = (object) $json_data;
                    
                    // Check if type is 'cpanel' (audio file uploaded)
                    if ( isset( $json_data->type ) && $json_data->type === 'cpanel' ) {
                        if ( isset( $json_data->location ) && isset( $json_data->filename ) ) {
                            $file_path = ABSPATH . $json_data->location . '/' . $json_data->filename;
                            
                            // Silently delete file (no error if doesn't exist)
                            if ( file_exists( $file_path ) ) {
                                @unlink( $file_path ); // Silent fail - don't block deletion
                            }
                        }
                    }
                }
            }
            // STEP 5: Remove progress_meta records after files are removed
            $wpdb->delete( $progress_meta_submissions_table, array( 'session_id' => $result_id ) );
        }
    }

    // Delete reviews (for writing/speaking evaluated tests)
    $review_ids = $wpdb->get_col( $wpdb->prepare(
        "SELECT ID FROM {$review_table} WHERE result_id = %d",
        $result_id
    ) );
    
    if ( ! empty( $review_ids ) ) {
        foreach ( $review_ids as $review_id ) {
            $wpdb->delete( $review_meta_table, array( 'review_id' => $review_id ) );
        }
        $wpdb->delete( $review_table, array( 'result_id' => $result_id ) );
    }

    # STEP: get quiz_version_id from the submissions meta table
    $quiz_version_id = $wpdb->get_var( $wpdb->prepare(
        "SELECT meta_value FROM {$meta_table} 
        WHERE submission_id = %d AND meta_key = 'quiz_version_id' LIMIT 1",
        $result_id
    ) );
    if( $quiz_version_id ) {
        # decrement the usage_count by 1 for the quiz_version_id
        //$wpdb->update( $versioning_table, array( 'usage_count' => 'GREATEST(usage_count - 1, 0)', 'updated_at' => time() ), array( 'version_id' => $quiz_version_id ) );
        $current_count = $wpdb->get_var($wpdb->prepare(
            "SELECT usage_count FROM {$versioning_table} WHERE version_id = %d",
            $quiz_version_id
        ));
        
        // Calculate new value in PHP
        $new_count = max((int)$current_count - 1, 0);
        
        // Update both values using $wpdb->update()
        $wpdb->update(
            $versioning_table,
            array(
                'usage_count' => $new_count,
                'updated_at' => time()
            ),
            array('version_id' => $quiz_version_id),
            array('%d', '%d'),
            array('%d')
        );
    
    }

    # STEP : delete the user_response table
    $wpdb->delete( $user_response_table, array( 'submission_id' => $result_id ) );

    # STEP : delete the session table
    $wpdb->delete( $session_table, array( 'submission_id' => $result_id ) );

    # STEP : delete the score table
    $wpdb->delete( $score_table, array( 'submission_id' => $result_id ) );

    # STEP : delete the evaluation meta table
    $wpdb->delete( $evaluation_meta_table, array( 'session_id' => $result_id ) );
    
    // STEP 6: Delete submission_meta entries
    $wpdb->delete( $meta_table, array( 'submission_id' => $result_id ) );
    
    // STEP 7: Delete submission (only if previous steps succeeded or failed gracefully)
    $deleted = $wpdb->delete( $submissions_table, array( 'ID' => $result_id ) );
    
    // Send response
    if ( $deleted ) {
        wp_send_json_success( array(
            'message' => 'Result deleted successfully',
            'deleted_id' => $result_id
        ) );
    } else {
        wp_send_json_error( array(
            'message' => 'Failed to delete result',
            'code' => 500
        ) );
    }
}

/**
 * Handle abandoned tests tab (admin only)
 */
function proqyz_handle_dashboard_abandoned_tests( $dashboard, $filters, $use_cache = false, $cached_user = null, $cached_courses = null ) {
    global $wpdb;
    
    $recalculated = false;
    
    // Check if cache is valid and has data
    $cache_is_valid = $use_cache && 
                     $cached_courses && 
                     ( is_array( $cached_courses ) && count( $cached_courses ) > 0 );
    
    // Get pagination parameters
    $per_page = isset( $filters->per_page ) ? absint( $filters->per_page ) : 10;
    $per_page = max( 1, min( $per_page, 100 ) );
    $current_page = isset( $filters->current_page ) ? max( 1, absint( $filters->current_page ) ) : 1;
    
    // Initialize dashboard (use cache or create new instance)
    if ( $cache_is_valid ) {
        // Create lightweight instance (skip heavy calculations, only evaluation settings)
        $dashboard = new ST_Dashboard( get_current_user_id(), true );
        
        // Manually set cached data to avoid recalculation (including quiz analytics for widgets)
        $dashboard->set_cached_data( $cached_user, $cached_courses, null );
        
        $courses = $cached_courses;
    } else {
        // Full calculation needed
        if ( ! $dashboard ) {
            $dashboard = new ST_Dashboard( get_current_user_id() );
        }
        $courses = $dashboard->get_courses();
        $recalculated = true;
    }
    
    // Get abandoned tests (always fetches fresh from DB)
    $result = $dashboard->get_abandoned_tests( array(
        'per_page'     => $per_page,
        'page'         => $current_page,
        'course_id'    => isset( $filters->course ) && $filters->course !== 'all' ? $filters->course : null,
        'session_type' => isset( $filters->session_type ) && $filters->session_type !== 'all' ? $filters->session_type : null,
        'category'     => isset( $filters->category ) && $filters->category !== 'all' ? $filters->category : null,
    ) );
    
    // Prepare current filter values to preserve selections
    $current_filters = array(
        'category'     => isset( $filters->category ) ? $filters->category : 'all',
        'course'       => isset( $filters->course ) ? $filters->course : 'all',
        'session_type' => isset( $filters->session_type ) ? $filters->session_type : 'all',
    );
    
    // Generate HTML components
    $table_html = $dashboard->render_abandoned_tests_table( $result['tests'] );
    $pagination_html = $dashboard->render_pagination( $result['pagination'] );
    $filters_html = $dashboard->render_abandoned_tests_filters( $current_filters );
    
    // Wrap everything in test-history section
    $complete_html = $dashboard->render_test_history_section( $filters_html, $table_html, $pagination_html );
    
    wp_send_json_success( array(
        'has_html' => true,
        'html' => array(
            'page' => $complete_html,
            'pagination' => '',
            'filters' => ''
        ),
        'has_json' => true,
        'json' => array(
            'tests' => $result['tests'],
            'pagination' => $result['pagination'],
            'courses' => $courses // Return (possibly updated) courses for frontend cache
        ),
        'used_cache' => $cache_is_valid,
        'recalculated' => $recalculated
    ) );
}

/**
 * handle delete abandoned tests by category
 */
function proqyz_dashboard_get_missing_user_ids( $category ) {
    global $wpdb, $table_proqyz_v3_progress_submissions;

    $submissions_table = $table_proqyz_v3_progress_submissions;
    $user_ids          = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT DISTINCT user_id FROM {$submissions_table} WHERE quiz_category = %s",
            $category
        )
    );

    $deleted_user_ids = array();
    foreach ( $user_ids as $user_id ) {
        $user = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT ID FROM {$wpdb->prefix}users WHERE ID = %d",
                $user_id
            )
        );
        if ( ! $user ) {
            $deleted_user_ids[] = $user_id;
        }
    }

    return $deleted_user_ids;
}

function proqyz_dashboard_get_abandoned_count( $category ) {
    global $wpdb, $table_proqyz_v3_progress_submissions;

    $deleted_user_ids = proqyz_dashboard_get_missing_user_ids( $category );
    if ( empty( $deleted_user_ids ) ) {
        return array(
            'count'            => 0,
            'deleted_user_ids' => $deleted_user_ids,
        );
    }

    $submissions_table = $table_proqyz_v3_progress_submissions;
    $placeholders      = implode( ',', array_fill( 0, count( $deleted_user_ids ), '%d' ) );

    $count = (int) $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(*) FROM {$submissions_table} WHERE quiz_category = %s AND user_id IN ($placeholders)",
            array_merge( array( $category ), $deleted_user_ids )
        )
    );

    return array(
        'count'            => $count,
        'deleted_user_ids' => $deleted_user_ids,
    );
}

function proqyz_handle_dashboard_get_abandoned_counts() {
    // Only privileged roles can view/delete abandoned data
    $current_user = wp_get_current_user();
    $is_admin_or_teacher = in_array( 'administrator', $current_user->roles ) ||
        in_array( 'teacher', $current_user->roles ) ||
        in_array( 'instructor', $current_user->roles ) ||
        in_array( 'trainer', $current_user->roles );

    if ( ! $is_admin_or_teacher ) {
        wp_send_json_error(
            array(
                'message' => 'Permission denied',
                'code'    => 403,
            )
        );
    }

    $categories = array(
        'ielts-reading',
        'ielts-listening',
        'ielts-writing',
        'ielts-speaking',
    );

    $counts = array();
    foreach ( $categories as $category ) {
        $counts[ $category ] = proqyz_dashboard_get_abandoned_count( $category )['count'];
    }

    wp_send_json_success(
        array(
            'counts' => $counts,
            'code'   => 200,
        )
    );
}

function proqyz_handle_dashboard_delete_abandoned_tests_by_category() {
    global $wpdb,
    $table_proqyz_v3_progress_submissions,
	$table_proqyz_v3_progress_submissions_meta, 
	$table_proqyz_v3_progress_meta_submissions,
	$table_proqyz_v3_submissions_review,
	$table_proqyz_v3_submissions_review_meta,
	$table_proqyz_v3_submissions_versioning,	
	$table_proqyz_v3_progress_submissions_user_response,
	$table_proqyz_v3_progress_submissions_session,
	$table_proqyz_v3_progress_submissions_score,
	$table_proqyz_v3_submissions_evaluation_meta;
    
    # [core] submissions table
    $submissions_table = $table_proqyz_v3_progress_submissions;
    # [core] submissions meta table
    $meta_table = $table_proqyz_v3_progress_submissions_meta;
    # [core] speaking progress meta submissions table
    $progress_meta_submissions_table = $table_proqyz_v3_progress_meta_submissions;
    # [core] score table
    $score_table = $table_proqyz_v3_progress_submissions_score;
    # [core] user_response table
    $user_response_table = $table_proqyz_v3_progress_submissions_user_response;
    # [core] session table
    $session_table = $table_proqyz_v3_progress_submissions_session;
    # [core] evaluation meta table
    $evaluation_meta_table = $table_proqyz_v3_submissions_evaluation_meta;
    # [core] review table
    $review_table = $table_proqyz_v3_submissions_review;
    # [core] review meta table
    $review_meta_table = $table_proqyz_v3_submissions_review_meta;
    # [core] versioning table
    $versioning_table = $table_proqyz_v3_submissions_versioning;
    
    $category = isset( $_POST['category'] ) ? sanitize_text_field( $_POST['category'] ) : '';
    
    if ( empty( $category ) ) {
        wp_send_json_error( array(
            'message' => 'Category is required',
            'code' => 400
        ) );
    }

    // Check permissions (user can only delete their own results unless admin/teacher)
    $current_user_id = get_current_user_id();
    $current_user = wp_get_current_user();
    $is_admin_or_teacher = in_array( 'administrator', $current_user->roles ) || 
        in_array( 'teacher', $current_user->roles ) || 
        in_array( 'instructor', $current_user->roles ) || 
        in_array( 'trainer', $current_user->roles );
    if ( ! $is_admin_or_teacher ) {
        wp_send_json_error( array(
            'message' => 'Permission denied',
            'code' => 403
        ) );
    }

    // Get abandoned count + deleted user ids
    $abandoned_data = proqyz_dashboard_get_abandoned_count( $category );
    $deleted_user_ids = $abandoned_data['deleted_user_ids'];

    // Bail early if no users are missing (nothing to delete)
    if ( empty( $deleted_user_ids ) || $abandoned_data['count'] === 0 ) {
        wp_send_json_error( array(
            'message' => 'No deleted users found for this category',
            'code'    => 404,
        ) );
    }

    // Build a safe placeholder string for the IN() clause
    $placeholders = implode( ',', array_fill( 0, count( $deleted_user_ids ), '%d' ) );

    // Fetch the first 100 matching results for deletion
    $results = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT ID, user_id, quiz_category FROM {$submissions_table} WHERE quiz_category = %s AND user_id IN ($placeholders) ORDER BY ID ASC LIMIT 100",
            array_merge( array( $category ), $deleted_user_ids )
        )
    );
    $deleted_count = 0;
    $users_found = 0;
    $users_deleted = 0;
    if(count($results) > 0) {
        foreach ( $results as $result ) {
            $user_id = $result->user_id; 
            $result_id = $result->ID;
            $quiz_category = $result->quiz_category;
            // check if user is in `wp_users`
            $user = $wpdb->get_row( $wpdb->prepare(
                "SELECT ID FROM {$wpdb->prefix}users WHERE ID = %d",
                $user_id
            ) );
            if ( ! $user ) {
                $users_deleted++;
              
                // delete result
                // STEP 3-5: Delete associated files for speaking tests (using migrated_from_id)
                if ( $quiz_category === 'ielts-speaking' ) {
                    // Use quiz_progress_meta table for speaking audio files (permanent table)
                    $speaking_meta_records = $wpdb->get_results( $wpdb->prepare(
                        "SELECT `json` FROM {$progress_meta_submissions_table} 
                        WHERE session_id = %d",
                        $result_id
                    ) );
                    
                    if ( ! empty( $speaking_meta_records ) ) {
                        // STEP 4: Find and safe remove file from uploads directory
                        foreach ( $speaking_meta_records as $meta ) {
                            $json_data = @unserialize( $meta->json );
                            if ( is_array( $json_data ) || is_object( $json_data ) ) {
                                $json_data = (object) $json_data;
                                
                                // Check if type is 'cpanel' (audio file uploaded)
                                if ( isset( $json_data->type ) && $json_data->type === 'cpanel' ) {
                                    if ( isset( $json_data->location ) && isset( $json_data->filename ) ) {
                                        $file_path = ABSPATH . $json_data->location . '/' . $json_data->filename;
                                        
                                        // Silently delete file (no error if doesn't exist)
                                        if ( file_exists( $file_path ) ) {
                                            @unlink( $file_path ); // Silent fail - don't block deletion
                                        }
                                    }
                                }
                            }
                        }
                        // STEP 5: Remove progress_meta records after files are removed
                        $wpdb->delete( $progress_meta_submissions_table, array( 'session_id' => $result_id ) );
                    }
                }

                // Delete reviews (for writing/speaking evaluated tests)
                $review_ids = $wpdb->get_col( $wpdb->prepare(
                    "SELECT ID FROM {$review_table} WHERE result_id = %d",
                    $result_id
                ) );
                
                if ( ! empty( $review_ids ) ) {
                    foreach ( $review_ids as $review_id ) {
                        $wpdb->delete( $review_meta_table, array( 'review_id' => $review_id ) );
                    }
                    $wpdb->delete( $review_table, array( 'result_id' => $result_id ) );
                }

                # STEP: get quiz_version_id from the submissions meta table
                $quiz_version_id = $wpdb->get_var( $wpdb->prepare(
                    "SELECT meta_value FROM {$meta_table} 
                    WHERE submission_id = %d AND meta_key = 'quiz_version_id' LIMIT 1",
                    $result_id
                ) );
                if( $quiz_version_id ) {
                    # decrement the usage_count by 1 for the quiz_version_id
                    // $wpdb->update( $versioning_table, array( 'usage_count' => 'GREATEST(usage_count - 1, 0)', 'updated_at' => time() ), array( 'version_id' => $quiz_version_id ) );
                    $current_count = $wpdb->get_var($wpdb->prepare(
                        "SELECT usage_count FROM {$versioning_table} WHERE version_id = %d",
                        $quiz_version_id
                    ));
                    
                    // Calculate new value in PHP
                    $new_count = max((int)$current_count - 1, 0);
                    
                    // Update both values using $wpdb->update()
                    $wpdb->update(
                        $versioning_table,
                        array(
                            'usage_count' => $new_count,
                            'updated_at' => time()
                        ),
                        array('version_id' => $quiz_version_id),
                        array('%d', '%d'),
                        array('%d')
                    );
                }

                # STEP : delete the user_response table
                $wpdb->delete( $user_response_table, array( 'submission_id' => $result_id ) );

                # STEP : delete the session table
                $wpdb->delete( $session_table, array( 'submission_id' => $result_id ) );

                # STEP : delete the score table
                $wpdb->delete( $score_table, array( 'submission_id' => $result_id ) );

                # STEP : delete the evaluation meta table
                $wpdb->delete( $evaluation_meta_table, array( 'session_id' => $result_id ) );
                
                // STEP 6: Delete submission_meta entries
                $wpdb->delete( $meta_table, array( 'submission_id' => $result_id ) );
                
                // STEP 7: Delete submission (only if previous steps succeeded or failed gracefully)
                $deleted = $wpdb->delete( $submissions_table, array( 'ID' => $result_id ) );

                
                // Send response
                if ( $deleted ) {
                    $deleted_count++;
                }
                
            } else {
                $users_found++;
            }
        }
    } else {
        wp_send_json_error( array(
            'message' => 'No results found',
            'code' => 404
        ) );
    }

    // Recalculate remaining records for this category after deletion
    $remaining_after_delete = proqyz_dashboard_get_abandoned_count( $category );

    wp_send_json_success( array(
        'message' => 'Deleted ' . $deleted_count . ' results',
        'code' => 200,
        'users_found' => $users_found,
        'users_deleted' => $users_deleted,
        'deleted_count' => $deleted_count,
        'result_ids' => count($results),
        'remaining_count' => $remaining_after_delete['count'],
    ) );

}

// Register AJAX actions
add_action( 'wp_ajax_proqyz_ajax_dashboard_overall', 'proqyz_dashboard_ajax_router' );
add_action( 'wp_ajax_proqyz_ajax_dashboard_listening', 'proqyz_dashboard_ajax_router' );
add_action( 'wp_ajax_proqyz_ajax_dashboard_reading', 'proqyz_dashboard_ajax_router' );
add_action( 'wp_ajax_proqyz_ajax_dashboard_writing', 'proqyz_dashboard_ajax_router' );
add_action( 'wp_ajax_proqyz_ajax_dashboard_speaking', 'proqyz_dashboard_ajax_router' );
add_action( 'wp_ajax_proqyz_ajax_dashboard_gov_exam', 'proqyz_dashboard_ajax_router' );
add_action( 'wp_ajax_proqyz_ajax_dashboard_pdf_quiz', 'proqyz_dashboard_ajax_router' );
add_action( 'wp_ajax_proqyz_ajax_dashboard_abandoned_tests', 'proqyz_dashboard_ajax_router' );
add_action( 'wp_ajax_proqyz_ajax_delete_result', 'proqyz_dashboard_ajax_delete_result' );
add_action( 'wp_ajax_proqyz_ajax_delete_abandoned_tests_by_category', 'proqyz_handle_dashboard_delete_abandoned_tests_by_category' );
add_action( 'wp_ajax_proqyz_ajax_get_abandoned_counts', 'proqyz_handle_dashboard_get_abandoned_counts' );


