<?php
namespace QuizPress\API\Query;

use QuizPress\SeLicense;

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

class Attempts {


	public static function quiz_attempt_insert( $postarr ) {
		if ( ! is_array( $postarr ) ) {
			return null;
		}

		global $wpdb;
		$guest_id = 0;
		if ( ! is_user_logged_in() && SeLicense::is_pro() ) {
			$token = \QuizPress\Helper::quizpress_get_guest_token();

			if ( $token ) {
				$guest_id = \QuizPress\Helper::get_guest_id_by_token( $token );
			}
		}
		$user_ip = ! \QuizPress\Helper::get_settings( 'is_disable_store_ip_address' ) ? \QuizPress\Helper::get_client_ip_address() : '';

		// Are we updating or creating?
		$attempt_id = ! empty( $postarr['attempt_id'] ) ? $postarr['attempt_id'] : 0;
		$update     = ! empty( $attempt_id );

		$defaults = [
			'quiz_id'                  => '',
			'user_id'                  => get_current_user_id(),
			'guest_id'                 => $guest_id,
			'total_questions'          => '',
			'total_answered_questions' => '',
			'total_marks'              => '',
			'earned_marks'             => '',
			'attempt_info'             => wp_json_encode( [ 'total_correct_answers' => 0 ] ),
			'attempt_status'           => 'pending',
			'attempt_ip'               => $user_ip,
			'attempt_ended_at'         => '',
			'is_manually_reviewed'     => null,
			'manually_reviewed_at'     => null,
		];

		// Only default the start time when creating a new attempt — an update
		// must never overwrite the original attempt_started_at.
		if ( ! $update ) {
			$defaults['attempt_started_at'] = current_time( 'mysql', true );
		}

		$attempt = wp_parse_args( $postarr, $defaults );

		if ( $update ) {
			unset( $attempt['attempt_id'] );
			unset( $attempt['attempt_started_at'] );
			$attempt['attempt_ended_at'] = current_time( 'mysql', true );
		}

		// post insert will be here
		$table_name = $wpdb->prefix . 'quizpress_attempts';
		if ( $update ) {
			$wpdb->update(
				$table_name,
				$attempt,
				[ 'attempt_id' => $attempt_id ],
				[
					'%d',
					'%d',
					'%s',
					'%d',
					'%d',
					'%f',
					'%f',
					'%s',
					'%s',
					'%s',
					'%s',
					'%s',
					'%f',
					'%s',
					'%s',
				],
				[ '%d' ]
			);
			return $attempt_id;
		} else {
			$wpdb->insert(
				$table_name,
				[
					'quiz_id'                  => $attempt['quiz_id'],
					'user_id'                  => $attempt['user_id'],
					'guest_id'                 => $attempt['guest_id'],
					'total_questions'          => $attempt['total_questions'],
					'total_answered_questions' => $attempt['total_answered_questions'],
					'total_marks'              => $attempt['total_marks'],
					'earned_marks'             => $attempt['earned_marks'],
					'attempt_info'             => $attempt['attempt_info'],
					'attempt_status'           => $attempt['attempt_status'],
					'attempt_ip'               => $attempt['attempt_ip'],
					'attempt_started_at'       => $attempt['attempt_started_at'],
					'attempt_ended_at'         => $attempt['attempt_ended_at'],
					'is_manually_reviewed'     => $attempt['is_manually_reviewed'],
					'manually_reviewed_at'     => $attempt['manually_reviewed_at'],
				],
				[
					'%d',
					'%d',
					'%d',
					'%d',
					'%d',
					'%f',
					'%f',
					'%s',
					'%s',
					'%s',
					'%s',
					'%s',
					'%d',
					'%s',
					'%s',
				]
			);
			return $wpdb->insert_id;
		} //end if
		return null;
	}

	public static function update_quiz_attempt_by_manual_review( $postarr ) {
		if ( ! is_array( $postarr ) ) {
			return null;
		}

		global $wpdb;
		$defaults = [
			'quiz_id'                  => '',
			'user_id'                  => '',
			'guest_id'                 => '',
			'total_questions'          => '',
			'total_answered_questions' => '',
			'total_marks'              => '',
			'earned_marks'             => '',
			'attempt_info'             => '',
			'attempt_status'           => '',
			'attempt_ip'               => '',
			'attempt_started_at'       => '',
			'attempt_ended_at'         => '',
			'is_manually_reviewed'     => 1,
			'manually_reviewed_at'     => current_time( 'mysql' ),
		];

		$attempt = wp_parse_args( $postarr, $defaults );

		$attempt_id = $attempt['attempt_id'];
		unset( $attempt['attempt_id'] );

		// post insert will be here
		$table_name = $wpdb->prefix . 'quizpress_attempts';

		$wpdb->update(
			$table_name,
			$attempt,
			[ 'attempt_id' => $attempt_id ],
			[
				'%d',
				'%d',
				'%d',
				'%d',
				'%d',
				'%f',
				'%f',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%d',
				'%s',
			],
			[ '%d' ]
		);
		return $attempt_id;
	}

	public static function has_attempt_quiz( $quiz_id, $user_id ) {
		 global $wpdb;
		return $wpdb->get_var( $wpdb->prepare( "SELECT attempt_id FROM {$wpdb->prefix}quizpress_attempts AND quiz_id=%d AND user_id=%d LIMIT 1", $quiz_id, $user_id ) );
	}
	public static function get_quiz_attempts( $args ) {
		 global $wpdb;

		$defaults = [
			'attempt_status' => 'any',
			'per_page'       => 10,
			'offset'         => 0,
			'quiz_id'        => 0,
			'user_id'        => 0,
			'search'         => '',
		];

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

		// If quiz_id provided, delegate to another method.
		if ( ! empty( $args['quiz_id'] ) ) {
			return self::get_quiz_attempt_details_by_quiz_id( $args );
		}

		$table_attempts = $wpdb->prefix . 'quizpress_attempts';
		$table_answers  = $wpdb->prefix . 'quizpress_attempt_answers';
		$table_posts    = $wpdb->posts;

		// Base query.
		$query = $wpdb->prepare(
			"SELECT
				attempt.attempt_id,
				attempt.quiz_id,
				attempt.user_id,
				attempt.total_questions,
				attempt.total_answered_questions,
				attempt.total_marks,
				attempt.earned_marks,
				attempt.attempt_info,
				attempt.attempt_status,
				attempt.attempt_ip,
				attempt.attempt_started_at,
				attempt.attempt_ended_at,
				(
					SELECT COUNT(answer.attempt_answer_id)
					FROM {$table_answers} AS answer
					WHERE answer.attempt_id = attempt.attempt_id
					AND answer.is_correct = 1
				) AS total_correct_answer
			FROM {$table_attempts} AS attempt
			INNER JOIN {$table_posts} AS post ON post.ID = attempt.quiz_id"// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);

		if ( ! empty( $args['user_id'] ) ) {
			$query .= $wpdb->prepare( ' AND attempt.user_id = %d', $args['user_id'] );
		}
		// Add optional search filter.
		if ( ! empty( $args['search'] ) ) {
			$search  = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$query  .= $wpdb->prepare( ' AND post.post_title LIKE %s', $search );
		}

		// Add optional attempt_status filter.
		if ( 'any' !== $args['attempt_status'] ) {
			$query .= $wpdb->prepare( ' AND attempt.attempt_status = %s', $args['attempt_status'] );
		}
		// Add pagination.
		$query .= $wpdb->prepare( ' ORDER BY attempt.attempt_started_at DESC LIMIT %d, %d', $args['offset'], $args['per_page'] );

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

	public static function get_quiz_attempt_details_by_quiz_id( $args ) {
		global $wpdb;
		$defaults = [
			'per_page' => 10,
			'offset'   => 0,
			'quiz_id'  => 0,
			'user_id'  => get_current_user_id(),
		];
		$args = wp_parse_args( $args, $defaults );
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT attempt_id, quiz_id, user_id, total_questions, total_answered_questions, total_marks, earned_marks, attempt_info, attempt_status, attempt_ip, attempt_started_at, attempt_ended_at,
					(select COUNT(attempt_answer_id) from {$wpdb->prefix}quizpress_attempt_answers as attempt_answers where attempt_answers.attempt_id = {$wpdb->prefix}quizpress_attempts.attempt_id AND attempt_answers.is_correct=1) as total_correct_answer
				FROM {$wpdb->prefix}quizpress_attempts WHERE quiz_id=%d AND user_id=%d ORDER BY attempt_started_at DESC LIMIT %d, %d;",
				$args['quiz_id'],
				$args['user_id'],
				$args['offset'],
				$args['per_page']
			),
			OBJECT
		);
	}

	public static function get_quiz_attempt( $ID ) {
		global $wpdb;
		$attempt = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}quizpress_attempts WHERE attempt_id=%d", $ID ), OBJECT );
		return current( $attempt );
	}

	public static function get_all_quiz_attempts() {
		global $wpdb;
		$attempt = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}quizpress_attempts", OBJECT );
		return $attempt;
	}

	public static function get_quiz_attempt_details( $attempt_id, $user_id ) {
		global $wpdb;

		$where  = 'attempt_answers.attempt_id = %d';
		$params = [ $attempt_id ];

		if ( $user_id > 0 ) {
			$where   .= ' AND attempt_answers.user_id = %d';
			$params[] = $user_id;
		}

		$sql = "SELECT
                attempt_answers.attempt_answer_id,
                attempt_answers.attempt_id,
                attempt_answers.user_id,
                attempt_answers.question_id,
                attempt_answers.quiz_id,
                attempt_answers.is_correct,
                attempt_answers.answer AS given_answer,
                GROUP_CONCAT(DISTINCT quiz_answers.answer_title SEPARATOR ' || ') AS correct_answers,
                GROUP_CONCAT(DISTINCT quiz_answers.answer_content SEPARATOR ' || ') AS answer_content,
                quiz_questions.question_title,
                quiz_questions.question_type,
                quiz_questions.question_explanation,
                quiz_attempts.is_manually_reviewed
            FROM {$wpdb->prefix}quizpress_attempt_answers AS attempt_answers
            LEFT JOIN {$wpdb->prefix}quizpress_questions AS quiz_questions
                ON attempt_answers.question_id = quiz_questions.question_id
            LEFT JOIN {$wpdb->prefix}quizpress_answers AS quiz_answers
                ON attempt_answers.question_id = quiz_answers.question_id
            LEFT JOIN {$wpdb->prefix}quizpress_attempts AS quiz_attempts
                ON attempt_answers.attempt_id = quiz_attempts.attempt_id
            WHERE $where
            GROUP BY attempt_answers.question_id
        ";
		return $wpdb->get_results( $wpdb->prepare( $sql, $params ), OBJECT );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	}


	public static function get_quiz_attempt_details_by_attempt_id( $attempt_id ) {
		global $wpdb;
		return $wpdb->get_results($wpdb->prepare("SELECT
            attempt_answers.attempt_answer_id,
            attempt_answers.attempt_id,
            attempt_answers.quiz_id,
            attempt_answers.user_id,
            attempt_answers.question_id,
			attempt_answers.is_correct,
            attempt_answers.answer_id,
            attempt_answers.answer as given_answer,
            quiz_answers.answer_title as correct_answer,
            quiz_answers.answer_content,
			quiz_answers.is_correct as is_correct_answer,
            quiz_questions.question_title,
            quiz_questions.question_type,
			quiz_attempts.is_manually_reviewed
            FROM {$wpdb->prefix}quizpress_attempt_answers as attempt_answers
            LEFT JOIN {$wpdb->prefix}quizpress_questions as quiz_questions ON attempt_answers.question_id = quiz_questions.question_id
            LEFT JOIN {$wpdb->prefix}quizpress_answers as quiz_answers ON attempt_answers.question_id = quiz_answers.question_id
            LEFT JOIN {$wpdb->prefix}quizpress_attempts as quiz_attempts ON attempt_answers.attempt_id = quiz_attempts.attempt_id
            WHERE attempt_answers.attempt_id=%d", $attempt_id), OBJECT);
	}

	public static function delete_quiz_attempt( $attempt_id ) {
		 global $wpdb;
		$is_delete_attempts        = $wpdb->delete( $wpdb->prefix . 'quizpress_attempts', [ 'attempt_id' => $attempt_id ], [ '%d' ] );
		$is_delete_attempt_answers = $wpdb->delete( $wpdb->prefix . 'quizpress_attempt_answers', [ 'attempt_id' => $attempt_id ], [ '%d' ] );
		return $is_delete_attempts === $is_delete_attempt_answers;
	}

	public static function get_total_number_of_attempts( $user_id = 0 ) {
		global $wpdb;
		$query = "SELECT COUNT(attempt_id)
			FROM {$wpdb->prefix}quizpress_attempts AS qa
			INNER JOIN {$wpdb->prefix}posts AS p ON p.ID = qa.quiz_id
			AND p.post_type = 'quizpress_quiz'
		";
		if ( $user_id ) {
			$query .= $wpdb->prepare( ' AND p.post_author = %d', $user_id );
		}
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->get_var( $query );
	}

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

		return $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(quiz_id) FROM {$wpdb->prefix}quizpress_attempt_answers WHERE attempt_id = %d",
				[
					$attempt_id,
				]
			)
		);
	}

	public static function quiz_attempt_answer_insert( $postarr ) {
		if ( ! is_array( $postarr ) ) {
			return null;
		}

		global $wpdb;
		$defaults = [
			'attempt_answer_id' => '',
			'user_id'           => '',
			'quiz_id'           => '',
			'question_id'       => '',
			'attempt_id'        => '',
			'answer_id'         => '',
			'answer'            => '',
			'question_mark'     => '',
			'achieved_mark'     => '',
			'minus_mark'        => '',
			'is_correct'        => '',
		];

		$attempt = wp_parse_args( $postarr, $defaults );

		$table_name = $wpdb->prefix . 'quizpress_attempt_answers';

		// Are we updating or creating?
		$attempt_answer_id       = 0;
		$exist_attempt_answer_id = (int) self::get_exist_attempt_answer_id( $attempt['quiz_id'], $attempt['user_id'], $attempt['question_id'], $attempt['attempt_id'], $attempt['answer_id'] );

		$update = false;

		if ( ! empty( $postarr['attempt_answer_id'] ) || ! empty( $exist_attempt_answer_id ) ) {
			$attempt_answer_id = $postarr['attempt_answer_id'] ?? $exist_attempt_answer_id;
			$update            = true;
			unset( $attempt['attempt_answer_id'] );
		}

		if ( empty( $postarr['user_id'] ) ) {
			$attempt['user_id'] = get_current_user_id();
		}

		// update attempt answer
		if ( $update ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				$table_name,
				$attempt,
				[ 'attempt_answer_id' => $attempt_answer_id ],
				[
					'%d',
					'%d',
					'%d',
					'%d',
					'%d',
					'%s',
					'%f',
					'%f',
					'%f',
					'%d',
				],
				[ '%d' ]
			);
			return $attempt_answer_id;
		} //end if
		  // insert attempt answer
          // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->insert(
			$table_name,
			[
				'user_id'       => $attempt['user_id'],
				'quiz_id'       => $attempt['quiz_id'],
				'question_id'   => $attempt['question_id'],
				'attempt_id'    => $attempt['attempt_id'],
				'answer_id'     => $attempt['answer_id'],
				'answer'        => $attempt['answer'],
				'question_mark' => $attempt['question_mark'],
				'achieved_mark' => $attempt['achieved_mark'],
				'minus_mark'    => $attempt['minus_mark'],
				'is_correct'    => $attempt['is_correct'],
			],
			[
				'%d',
				'%d',
				'%d',
				'%d',
				'%d',
				'%s',
				'%f',
				'%f',
				'%f',
				'%d',
			]
		);
		return $wpdb->insert_id;
	}
	public static function get_attempt_info( $from = '', $to = '' ) {
		global $wpdb;
		$from = ! empty( $from ) ? $from : gmdate( 'Y-m-d', strtotime( ' - 30 days' ) );
		$to   = ! empty( $to ) ? $to : gmdate( 'Y-m-d', strtotime( ' + 1 day' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$table   = $wpdb->prefix . 'quizpress_attempts';
		$results = $wpdb->get_results(
			$wpdb->prepare("SELECT COUNT(attempt_id) as total, DATE(attempt_ended_at)  as date_format
            FROM {$table}
            WHERE attempt_ended_at BETWEEN %s AND %s
            GROUP BY date_format
            ORDER BY attempt_ended_at ASC;", $from, $to)
		);

		$results = self::quiz_attempt_format_query_results_to_chart_data( $from, $to, $results );

		return $results;
	}

	public static function quiz_attempt_format_query_results_to_chart_data( $from, $to, $data ) {
		$datesPeriod        = self::get_empty_collection_date_by_period( $from, $to );
		$total              = wp_list_pluck( $data, 'total' );
		$queried_date       = wp_list_pluck( $data, 'date_format' );
		$date_wise_enrolled = array_combine( $queried_date, $total );

		$results = array_merge( $datesPeriod, $date_wise_enrolled );
		foreach ( $results as $key => $TotalCount ) {
			unset( $results[ $key ] );
			$format_date           = gmdate( 'Y-m-d', strtotime( $key ) );
			$results[ $format_date ] = intval( $TotalCount );
		}
		return $results;
	}

	public static function get_empty_collection_date_by_period( $begin, $end ) {
		$begin     = new \DateTime( $begin );
		$end       = new \DateTime( $end );
		$interval  = \DateInterval::createFromDateString( '1 day' );
		$period    = new \DatePeriod( $begin, $interval, $end );
		$day_lists = [];
		foreach ( $period as $value ) {
			$day_lists[ $value->format( 'Y-m-d' ) ] = 0;
		}
		return $day_lists;
	}

	public static function get_leader_board_score() {
		global $wpdb;
		$table = $wpdb->prefix . 'quizpress_attempts';

		$query = "
			SELECT
				u.display_name,
				u.user_email,
				SUM(m.earned_marks) AS total_marks,
				COUNT(m.attempt_id) AS total_attempts,
				SUM(CASE WHEN m.attempt_status = 'failed' THEN 1 ELSE 0 END) AS failed_attempts
			FROM {$table} AS m
			INNER JOIN {$wpdb->prefix}users AS u ON m.user_id = u.ID
			GROUP BY u.display_name
			ORDER BY
				total_marks DESC,
				total_attempts ASC,
				failed_attempts ASC
		";

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

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

		// Logged-out guest handling
		if ( ! is_user_logged_in() && \QuizPress\Helper::get_settings( 'is_enable_guest_user_attempt', false ) ) {

			$token = \QuizPress\Helper::quizpress_get_guest_token();

			if ( empty( $token ) ) {
				// No token → no attempts found
				return [];
			}

			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * 
                    FROM {$wpdb->prefix}quizpress_attempts 
                    WHERE quiz_id = %d AND guest_id = %d",
					$quiz_id,
					\QuizPress\Helper::get_guest_id_by_token( $token )
				),
				OBJECT
			);
		}

		// Logged-in user
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * 
                FROM {$wpdb->prefix}quizpress_attempts 
                WHERE quiz_id = %d AND user_id = %d",
				$quiz_id,
				$user_id
			),
			OBJECT
		);
	}

	public static function get_passed_attempt( $quiz_id, $user_id ) {
		global $wpdb;
		return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}quizpress_attempts WHERE quiz_id=%d AND user_id=%d AND attempt_status='%s'", $quiz_id, $user_id, 'passed' ), OBJECT );
	}

	public static function get_exist_attempt_answer_id( $quiz_id, $user_id, $question_id, $attempt_id, $answer_id ) {
		global $wpdb;

		if ( ! $quiz_id || ! $user_id || ! $question_id || ! $attempt_id || ! $answer_id ) {
			return null;
		}

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

		$sql = $wpdb->prepare(
			"SELECT attempt_answer_id
			FROM $table
			WHERE quiz_id = %d
			AND user_id = %d
			AND question_id = %d
			AND attempt_id = %d
			AND answer_id = %d
			LIMIT 1",
			$quiz_id, $user_id, $question_id, $attempt_id, $answer_id
		);

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

	public static function delete_attempts_by_quiz_id( $quiz_id ) {
		 global $wpdb;
		return $wpdb->query(
			$wpdb->prepare(
				"DELETE q, a
				FROM {$wpdb->prefix}quizpress_attempts q
				LEFT JOIN {$wpdb->prefix}quizpress_attempt_answers a ON q.attempt_id = a.attempt_id
				WHERE q.quiz_id = %d",
				$quiz_id
			)
		);
	}

	public static function calculate_attempt_data( $quiz_id, $attempt_id, $user_id ) {
		$args = [
			'total_marks'    => 0,
			'earned_marks'   => 0,
			'attempt_status' => '',
			'attempt_info'   => [],
			'percentage'     => '',
		];
		$attempt = self::get_quiz_attempt( $attempt_id );
		if ( $attempt ) {
			$attempt_info           = json_decode( $attempt->attempt_info, true );
			$total_questions        = count( $attempt_info['render_question_ids'] );
			$args['skip_questions'] = self::get_skipped_question_count( $total_questions, $attempt_id );
			$total_questions_marks  = Questions::get_total_submitted_question_marks( $attempt_info['render_question_ids'], $attempt_id );
			$total_earned_marks     = Answers::get_quiz_attempt_answers_earned_marks( $user_id, $attempt_id );
			$args['total_marks']    = $total_questions_marks;
			$args['earned_marks']   = $total_earned_marks;
			$passing_grade          = (int) get_post_meta( $quiz_id, 'quizpress_quiz_passing_grade', true );
			$earned_percentage      = \QuizPress\Helper::calculate_percentage( $total_questions_marks, $total_earned_marks );
			$args['attempt_status'] = $earned_percentage >= $passing_grade ? 'passed' : 'failed';
			$args['attempt_info']   = $attempt_info;
			$args['percentage']     = $earned_percentage;
		}

		return $args;
	}

	public static function get_skipped_question_count( $total_questions, $attempt_id ) {
		$skip_questions = $total_questions - Questions::get_attempt_questions_count( $attempt_id );
		return $skip_questions;
	}

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

		$results = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT attempt_status
				FROM {$wpdb->prefix}quizpress_attempts
				WHERE quiz_id = %d AND user_id = %d
				ORDER BY attempt_id DESC
				LIMIT 1",
				$quiz_id,
				$user_id
			)
		);

		return $results ? $results : '';
	}

	public static function get_attempt_data_by_quiz_id( int $quiz_id ) : array {

		global $wpdb;

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

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT 
					question_id,
					answer_id,
					answer,
					COUNT(*) AS total
				FROM {$table}
				WHERE quiz_id = %d
				GROUP BY question_id, answer_id, answer
				ORDER BY question_id ASC",
				absint( $quiz_id )
			),
			ARRAY_A
		);

		$data = [];

		foreach ( $rows as $row ) {

			$qid = absint( $row['question_id'] );

			if ( ! isset( $data[ $qid ] ) ) {
				$data[ $qid ] = [];
			}

			$data[ $qid ][ absint( $row['answer_id'] ) ] = [
				'answer'    => sanitize_text_field( $row['answer'] ),
				'count'     => absint( $row['total'] ),
			];
		}

		return $data;
	}

}
