<?php
namespace QuizPress\API\Query;

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

class Questions {

	public static function quiz_question_insert( $postarr ) {
		if ( ! is_array( $postarr ) ) {
			return null;
		}
	
		global $wpdb;
	
		$defaults = array(
			'question_title'          => '',
			'question_name'           => '',
			'question_content'        => '',
			'question_explanation'    => '',
			'question_status'         => 'publish',
			'question_level'          => '',
			'question_type'           => '',
			'answer_type'             => 'plain_text',
			'question_score'          => 0,
			'question_order'          => 0,
			'question_negative_score' => 0,
			'question_settings'       => '',
			'question_created_at'     => current_time( 'mysql' ),
			'question_updated_at'     => current_time( 'mysql' ),
		);
	
		$question_arr = wp_parse_args( $postarr, $defaults );
		$question_arr['question_title'] = html_entity_decode( $question_arr['question_title'] );
	
		$table_name = $wpdb->prefix . 'quizpress_questions';
		$question_ID = 0;
		$update = false;
	
		if ( ! empty( $postarr['question_id'] ) ) {
			$question_ID = (int) $postarr['question_id'];
			$update = true;
			unset( $question_arr['question_id'] );
			$question_arr['question_updated_at'] = current_time( 'mysql' );
		}
	
		$formats = array(
			'%s', // question_title
			'%s', // question_name
			'%s', // question_content
			'%s', // question_explanation
			'%s', // question_status
			'%s', // question_level
			'%s', // question_type
			'%s', // answer_type
			'%f', // question_score
			'%d', // question_order
			'%f', // question_negative_score
			'%s', // question_settings
			'%s', // question_created_at
			'%s', // question_updated_at
		);
	
		if ( $update ) {
			$wpdb->update(
				$table_name,
				$question_arr,
				array( 'question_id' => $question_ID ),
				$formats,
				array( '%d' )
			);
			return $question_ID;
		} else {
			$wpdb->insert(
				$table_name,
				$question_arr,
				$formats
			);
			return $wpdb->insert_id;
		}
	
		return null;
	}


	public static function get_quiz_questions( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'per_page' => 10,
			'offset'   => 0,
			'status'   => 'any',
			'keyword'  => '',
		);

		$args = wp_parse_args( $args, $defaults );

		$per_page = absint( $args['per_page'] );
		$offset   = absint( $args['offset'] );
		$status   = sanitize_text_field( $args['status'] );
		$keyword  = sanitize_text_field( $args['keyword'] );

		$table = "{$wpdb->prefix}quizpress_questions";

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

		if ( 'any' !== $status ) {
			$where[]  = "question_status = %s";
			$params[] = $status;
		}

		if ( ! empty( $keyword ) ) {
			$where[]  = "(question_title LIKE %s OR question_name LIKE %s)";
			$search   = '%' . $wpdb->esc_like( $keyword ) . '%';
			$params[] = $search;
			$params[] = $search;
		}

		$sql = "SELECT * FROM {$table}";

		if ( ! empty( $where ) ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where );
		}

		$sql .= " ORDER BY question_id DESC LIMIT %d OFFSET %d";
		$params[] = $per_page;
		$params[] = $offset;

		return $wpdb->get_results( $wpdb->prepare( $sql, $params ), OBJECT );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}
	
	public static function get_questions_by_search( $keyword ) {
        global $wpdb;
        $table = $wpdb->prefix . 'quizpress_questions';

        if ( empty( $keyword ) ) {
            return [];
        }

        // Prepare keyword for LIKE query
        $like = '%' . $wpdb->esc_like( $keyword ) . '%';

        $query = $wpdb->prepare(
            "SELECT *
             FROM {$table}
             WHERE question_title LIKE %s
                OR question_content LIKE %s
             ORDER BY question_id DESC",
            $like,
            $like
        );

        return $wpdb->get_results( $query );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
	}

    public static function get_quiz_question( $ID ) {
		global $wpdb;
		$question   = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}quizpress_questions WHERE question_id=%d", $ID ), OBJECT );
		return current( $question );
	}

	public static function get_questions_by_ids( $ids = [], $random = false ) {
		global $wpdb;

		if ( empty( $ids ) ) {
			return [];
		}

		$ids = array_map( 'intval', $ids );

		// Prepare placeholders for IN()
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// Choose ordering rule
		if ( $random ) {
			// Preserve random order of $ids (already shuffled before calling)
			$order_by = 'ORDER BY FIELD(question_id, ' . implode( ',', $ids ) . ')';
		} else {
			// Default: respect the stored question_order
			$order_by = 'ORDER BY question_order ASC';
		}

		// Prepare SQL query
		$sql = $wpdb->prepare(
			"SELECT * FROM {$wpdb->prefix}quizpress_questions 
			WHERE question_id IN ($placeholders)
			$order_by", ...$ids// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		$questions = $wpdb->get_results( $sql, OBJECT );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return $questions;
	}

	public static function get_questions_by_quid_id( $quiz_id, $user_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'quizpress_attempts';

		$query = $wpdb->prepare(
			"SELECT	JSON_UNQUOTE(JSON_EXTRACT(attempt_info, '$.question_render_ids')) AS question_render_ids
			FROM {$table}
			WHERE quiz_id = %d AND user_id = %d
		", $quiz_id, $user_id );

		return $wpdb->get_results($query, ARRAY_A);
	}

    public static function update_questions_order_by_quiz_id( $questions ) {
		global $wpdb;
		$table_name = $wpdb->prefix . 'quizpress_questions';
    	$updated_count = 0;

		foreach ( $questions as $question ) {
        	$question_id = isset( $question['question_id'] ) ? intval( $question['question_id'] ) : 0;
        	$new_order   = intval( $question['question_order'] );

        	if ( $question_id > 0 && $new_order >= 0 ) {
        	    $result = $wpdb->update(
        	        $table_name,
        	        array( 'question_order' => $new_order ),
        	        array(
        	            'question_id'      => $question_id,
        	        ),
        	        array( '%d' ), 
        	        array( '%d' )
        	    );

        	    if ( false !== $result ) {
        	        $updated_count++;
        	    }
        	}
    	}	
		return $updated_count;
	}

	public static function is_quiz_correct_answer( $IDs, $question_id = '' ) {
		global $wpdb;
		if ( is_array( $IDs ) ) {
			$correct_answer_count = 0;
			$has_wrong_answer = false;
			foreach ( $IDs as $ID ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$answers   = $wpdb->get_results( $wpdb->prepare( "SELECT is_correct FROM {$wpdb->prefix}quizpress_answers WHERE answer_id=%d", $ID ), OBJECT );
				if ( ! empty( $answers ) && (bool) current( $answers )->is_correct === true ) {
					$correct_answer_count++;
				} else {
					$has_wrong_answer = true;
					break;
				}
			}
			if ( $has_wrong_answer ) {
				return false;
			}

			return $correct_answer_count ? true : false;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$answers   = $wpdb->get_results( $wpdb->prepare( "SELECT is_correct FROM {$wpdb->prefix}quizpress_answers WHERE answer_id=%d", $IDs ), OBJECT );
		return (bool) current( $answers )->is_correct;
	}

	public static function get_quiz_total_correct_answer_by_question_id( $ID ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$answers   = $wpdb->get_results( $wpdb->prepare( "SELECT is_correct FROM {$wpdb->prefix}quizpress_answers WHERE question_id=%d AND is_correct=%d", $ID, 1 ), OBJECT );
		return count( $answers );
	}

    public static function get_total_questions_marks_by_quiz_id( $ID ) {
		global $wpdb;
		$questions_marks = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT sum(question_score) as total_marks FROM {$wpdb->prefix}quizpress_questions WHERE question_id=%d;",
				$ID
			),
			OBJECT
		);
		return (float) current( $questions_marks )->total_marks ?? 0;
	}

	public static function get_total_submitted_question_marks( $question_ids, $attempt_id ) {
		global $wpdb;

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

		$question_ids = array_map( 'intval', $question_ids );

		// Remove invalid/zero question IDs
		$question_ids = array_filter( $question_ids );
		if ( empty( $question_ids ) ) {
			return 0.0;
		}

		$placeholders = implode( ',', array_fill( 0, count( $question_ids ), '%d' ) );

		$params = array_merge( $question_ids, [ intval( $attempt_id ) ] );

		// Prepare SQL
		$sql = $wpdb->prepare(
			"SELECT COALESCE(SUM(question_mark), 0)
			FROM {$wpdb->prefix}quizpress_attempt_answers
			WHERE question_id IN ($placeholders)
			AND attempt_id = %d",
			$params
		);

		return (float) $wpdb->get_var( $sql );
	}

	public static function get_attempt_questions_count( $attempt_id ) {
		global $wpdb;

		if ( empty( $attempt_id ) ) {
			return 0;
		}

		// Prepare SQL
		$sql = $wpdb->prepare(
			"SELECT COUNT(question_id)
			FROM {$wpdb->prefix}quizpress_attempt_answers
			WHERE attempt_id = %d",
			$attempt_id
		);

		return (int) $wpdb->get_var( $sql );
	}

    public static function delete_question( $question_id ) {
		global $wpdb;
		$is_deleted = $wpdb->delete( $wpdb->prefix . 'quizpress_questions', array( 'question_id' => $question_id ), array( '%d' ) );
		$wpdb->delete( $wpdb->prefix . 'quizpress_answers', array( 'question_id' => $question_id ), array( '%d' ) );
		$attempt_id = $wpdb->delete( $wpdb->prefix . 'quizpress_attempt_answers', array( 'question_id' => $question_id ), array( '%d' ) );
		$wpdb->delete( $wpdb->prefix . 'quizpress_attempts', array( 'attempt_id' => $attempt_id ), array( '%d' ) );
		return $is_deleted;
	}

	public static function is_required_manually_reviewed( $question_ids ) {
		global $wpdb;

		// Ensure input is an array.
		if ( ! is_array( $question_ids ) ) {
			$question_ids = array( $question_ids );
		}

		// Sanitize all IDs as integers.
		$question_ids = array_map( 'intval', $question_ids );

		if ( empty( $question_ids ) ) {
			return false;
		}

		// Define question types that require manual review.
		$manual_types = array( 'short_answer', 'paragraph', 'date', 'number' );

		$id_placeholders   = implode( ',', array_fill( 0, count( $question_ids ), '%d' ) );
		$type_placeholders = implode( ',', array_fill( 0, count( $manual_types ), '%s' ) );

		$query = $wpdb->prepare(
			"SELECT COUNT(question_id)
			FROM {$wpdb->prefix}quizpress_questions
			WHERE question_id IN ($id_placeholders)
			AND question_type IN ($type_placeholders)",// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			array_merge( $question_ids, $manual_types )// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		$count = (int) $wpdb->get_var( $query );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		return $count > 0;
	}

    public static function get_question_details_by_question_id( $question_id ) {
		global $wpdb;
		return current($wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}quizpress_questions WHERE question_id = %d LIMIT 1", $question_id )
		));
	}

	public static function get_total_question_answers( $question_id ) {
		global $wpdb;
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT *  FROM {$wpdb->prefix}quizpress_answers WHERE question_id = %d",
				$question_id
			),
			OBJECT
		);
	}
	public static function get_total_questions(): int {
		global $wpdb;

		return (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->prefix}quizpress_questions"
		);
	}

	public static function get_quiz_questions_by_quid_id( $quiz_id ) {
		$questions = get_post_meta( $quiz_id, 'quizpress_quiz_questions', true );
		$question_limit = (int) get_post_meta( $quiz_id, 'quizpress_quiz_max_questions_for_answer', true );
		$question_ids = array_column( (array) $questions, 'id' );

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

		if ( $question_limit ) {
			// If limit is greater than available questions, adjust it
			$limit = min( $question_limit, count( $question_ids ) );
			
			// Randomly pick the keys
			$random_keys = array_rand( $question_ids, $limit );
			
			// array_rand returns a single value if limit == 1, ensure it's an array
			$random_keys = (array) $random_keys;
			
			// Get the selected question IDs
			$question_ids = array_intersect_key( $question_ids, array_flip( $random_keys ) );
		}
		
		return self::get_questions_by_ids( $question_ids );
	}
}