<?php

namespace GemBoards\API;

use GemBoards\Classes\Exception\GemboardsInvalidArgumentException;
use GemBoards\Collections\BoardCollection;
use GemBoards\Entities\Board;
use GemBoards\entities\BoardVisit;
use GemBoards\Utils\Helper;
use GemBoardsPro\Collections\SprintCollection;
use WP_Error;
use WP_REST_Controller;
use WP_REST_Request;
use WP_REST_Server;

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

class Boards extends WP_REST_Controller {

	public static function init() {
		$self            = new self();
		$self->namespace = 'gemboards/v1';
		$self->rest_base = 'boards';
		add_action( 'rest_api_init', [ $self, 'register_routes' ] );
	}

	public function register_routes() {
		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base,
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_items' ],
				'permission_callback' => function () {
					if ( ! current_user_can( 'read' ) ) {
						return new WP_Error(
							'rest_forbidden_context',
							esc_html__( 'Sorry, you are not allowed to access this API.', 'gemboards' ),
							array( 'status' => rest_authorization_required_code() )
						);
					}

					return true;
				},
				'args'                => [
					'status' => [
						'type'        => 'string',
						'required'    => false,
						'default'     => 'active',
						'description' => __( 'Board status', 'gemboards' ),
					],
				],
			],
		);

		register_rest_route(
			$this->namespace,
			'/favourite-boards',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_favourite_boards' ],
				'permission_callback' => function () {
					if ( ! current_user_can( 'read' ) ) {
						return new WP_Error(
							'rest_forbidden_context',
							esc_html__( 'Sorry, you are not allowed to access this API.', 'gemboards' ),
							array( 'status' => rest_authorization_required_code() )
						);
					}

					return true;
				},
				'args'                => [],
			],
		);

		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/(?P<id>\d+)',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_item' ],
				'permission_callback' => function ( WP_REST_Request $request ) {
					return current_user_can( 'view_projects', [
						'board_id' => $request->get_param( 'id' ),
					] );
				},
				'args'                => [
					'id'           => [
						'type'        => 'integer',
						'required'    => true,
						'description' => __( 'Board ID', 'gemboards' ),
					],
					'created_by'   => [
						'type'        => 'string', // can be coma sperated multiple ids.
						'required'    => false,
						'description' => __( 'User ID', 'gemboards' ),
					],
					'search'       => [
						'type'        => 'string',
						'required'    => false,
						'default'     => '',
						'description' => __( 'Search query.', 'gemboards' ),
					],
					'assignee_ids' => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'card assignee filter', 'gemboards' ),
					],
					'priority'     => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'Priority', 'gemboards' ),
					],
					'labels'       => [
						'type'        => 'labels',
						'required'    => false,
						'description' => __( 'Labels', 'gemboards' ),
					],
					'per_page'     => [
						'type'        => 'integer',
						'required'    => false,
						'default'     => 30,
						'description' => __( 'Cards per page', 'gemboards' ),
					],
					'page'         => [
						'type'        => 'integer',
						'required'    => false,
						'default'     => 1,
						'description' => __( 'Page number', 'gemboards' ),
					],
				],
			],
		);

		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/(?P<id>\d+)/columns/(?P<column_id>\d+)',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_column_cards' ],
				'permission_callback' => function ( WP_REST_Request $request ): bool {
					return current_user_can( 'read' ) && current_user_can( 'view_projects', [
						'board_id' => $request->get_param( 'id' ),
					] );
				},
				'args'                => [
					'id'           => [
						'type'        => 'integer',
						'required'    => true,
						'description' => __( 'Board ID', 'gemboards' ),
					],
					'column_id'    => [
						'type'        => 'integer',
						'required'    => true,
						'description' => __( 'Board Column ID', 'gemboards' ),
					],
					'search'       => [
						'type'        => 'string',
						'required'    => false,
						'default'     => '',
						'description' => __( 'Search query.', 'gemboards' ),
					],
					'assignee_ids' => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'card assignee filter', 'gemboards' ),
					],
					'priority'     => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'Priority', 'gemboards' ),
					],
					'labels'       => [
						'type'        => 'labels',
						'required'    => false,
						'description' => __( 'Labels', 'gemboards' ),
					],
					'per_page'     => [
						'type'        => 'integer',
						'required'    => false,
						'default'     => 30,
						'description' => __( 'Cards per page', 'gemboards' ),
					],
					'page'         => [
						'type'        => 'integer',
						'required'    => false,
						'default'     => 1,
						'description' => __( 'Page number', 'gemboards' ),
					],
				],
			],
		);

		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/(?P<id>\d+)/archived-cards',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_archived_cards' ],
				'permission_callback' => function ( WP_REST_Request $request ): bool {
					return current_user_can( 'read' ) && current_user_can( 'manage_tasks', [
						'board_id' => $request->get_param( 'id' ),
					] );
				},
				'args'                => [
					'id'     => [
						'type'        => 'integer',
						'required'    => true,
						'description' => __( 'Board ID', 'gemboards' ),
					],
					'search' => [
						'type'        => 'string',
						'required'    => false,
						'default'     => '',
						'description' => __( 'Search query.', 'gemboards' ),
					],
				],
			],
		);

		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/(?P<id>\d+)/backlog-cards',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_backlog_cards' ],
				'permission_callback' => function ( WP_REST_Request $request ): bool {
					return current_user_can( 'read' ) && current_user_can( 'view_projects', [
						'board_id' => $request->get_param( 'id' ),
					] );
				},
				'args'                => [
					'id'           => [
						'type'        => 'integer',
						'required'    => true,
						'description' => __( 'Board ID', 'gemboards' ),
					],
					'per_page'     => [
						'type'        => 'integer',
						'required'    => false,
						'default'     => 30,
						'description' => __( 'Cards per page', 'gemboards' ),
					],
					'page'         => [
						'type'        => 'integer',
						'required'    => false,
						'default'     => 1,
						'description' => __( 'Page number', 'gemboards' ),
					],
					'search'       => [
						'type'        => 'string',
						'required'    => false,
						'default'     => '',
						'description' => __( 'Search query.', 'gemboards' ),
					],
					'assignee_ids' => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'card assignee filter', 'gemboards' ),
					],
					'priority'     => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'Priority', 'gemboards' ),
					],
					'labels'       => [
						'type'        => 'labels',
						'required'    => false,
						'description' => __( 'Labels', 'gemboards' ),
					],
					'created_by'   => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'User ID', 'gemboards' ),
					],
				],
			],
		);

		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/(?P<id>\d+)/sprints/(?P<sprint_id>\d+)/cards',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_sprint_cards' ],
				'permission_callback' => function ( WP_REST_Request $request ): bool {
					return current_user_can( 'read' ) && current_user_can( 'view_projects', [
						'board_id' => $request->get_param( 'id' ),
					] );
				},
				'args'                => [
					'id'           => [
						'type'        => 'integer',
						'required'    => true,
						'description' => __( 'Board ID', 'gemboards' ),
					],
					'sprint_id'    => [
						'type'        => 'integer',
						'required'    => true,
						'description' => __( 'Sprint ID', 'gemboards' ),
					],
					'per_page'     => [
						'type'        => 'integer',
						'required'    => false,
						'default'     => 30,
						'description' => __( 'Cards per page', 'gemboards' ),
					],
					'page'         => [
						'type'        => 'integer',
						'required'    => false,
						'default'     => 1,
						'description' => __( 'Page number', 'gemboards' ),
					],
					'search'       => [
						'type'        => 'string',
						'required'    => false,
						'default'     => '',
						'description' => __( 'Search query.', 'gemboards' ),
					],
					'assignee_ids' => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'card assignee filter', 'gemboards' ),
					],
					'priority'     => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'Priority', 'gemboards' ),
					],
					'labels'       => [
						'type'        => 'labels',
						'required'    => false,
						'description' => __( 'Labels', 'gemboards' ),
					],
					'created_by'   => [
						'type'        => 'string',
						'required'    => false,
						'description' => __( 'User ID', 'gemboards' ),
					],
				],
			],
		);
	}

	// phpcs:disable Generic.CodeAnalysis.UnusedFunctionParameter.FoundInExtendedClass
	public function get_items( $request ) {
		$boards = BoardCollection::get_boards_for_user(get_current_user_id(), $request->get_param( 'status' ));

		$items = [];
		foreach ( $boards as $board ) {
			$items[] = $this->get_board_array( $board );
		}

		return rest_ensure_response( $items );
	} //phpcs:enable

	public function get_favourite_boards() {
		$user_id      = get_current_user_id();
		$cache_group  = 'gemboards_favourite_boards';
		$last_changed = wp_cache_get_last_changed( $cache_group );
		$cache_key    = "get_favourite_boards_$last_changed:$user_id";
		$has_cache    = wp_cache_get( $cache_key, $cache_group );
		if ( false !== $has_cache ) {
			return rest_ensure_response( $has_cache );
		}

		$user_id = get_current_user_id();
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
		$favourite_boards = $wpdb->get_results(
			$wpdb->prepare("SELECT
					fb.id AS favourite_id,
					b.id AS board_id,
					b.project_id,
					b.folder_id,
					b.board_type AS board_type,
					b.title,
					b.icon_color AS color,
					b.created_at
				FROM
					{$wpdb->prefix}gemboards_favourite_boards fb
					INNER JOIN {$wpdb->prefix}gemboards_boards b ON b.id = fb.board_id
					INNER JOIN {$wpdb->prefix}gemboards_roles r ON r.user_id = %d
						AND r.object_id = b.project_id
						AND r.object_type = 'project'
				WHERE
					fb.user_id = %d", $user_id, $user_id), ARRAY_A
		); // phpcs:enable
		$favourite_boards = array_map( fn ( $item ) => array_merge($item, [
			'favourite_id' => (int) $item['favourite_id'],
			'board_id'     => (int) $item['board_id'],
			'project_id'   => (int) $item['project_id'],
			'folder_id'    => (int) $item['folder_id'],
		]), $favourite_boards);

		// check permissions on favourite boards
		$favourite_board_ids           = array_column( $favourite_boards, 'board_id' );
		$favourite_board_ids_formatter = implode( ',', array_fill( 0, count( $favourite_board_ids ), '%d' ) );

		wp_cache_set($cache_key, $favourite_boards, $cache_group);

		return rest_ensure_response($favourite_boards);
	}

	public function get_item( $request ) {
		$board_id = (int) $request->get_param( 'id' );
		$board    = Helper::get_board( $board_id );
		if ( ! $board ) {
			return new WP_Error( 'not_found', __( 'Board not found', 'gemboards' ), [ 'status' => 404 ] );
		}

		$this->update_recently_open_boards( $board_id );

		$cache_key   = md5( wp_json_encode( [
			'board_id'     => $board_id,
			'search'       => $request->get_param( 'search' ),
			'assignee_ids' => $request->get_param( 'assignee_ids' ),
			'per_page'     => 30,
			'page'         => 1,
		] ) );
		$cache_group = 'gemboards_single_board_api';

		$columns = $board->get_columns();

		$response           = $this->get_board_array( $board );
		$labels             = Helper::get_all_labels();
		$response['labels'] = array_values( $labels );

		$priorities = [];
		foreach (
			get_terms( [
				'taxonomy'   => GEMBOARDS_PLUGIN_SLUG . '_priority',
				'hide_empty' => false,
			] ) as $term
		) {
			$priorities[ $term->term_id ] = [
				'id'   => $term->term_id,
				'name' => $term->name,
				'slug' => $term->slug,
			];
		}

		$response['columns'] = [];
		foreach ( $columns as $column ) {
			$response['columns'][ $column->get_id() ] = [
				'id'                => $column->get_id(),
				'sort_order'        => $column->get_sort_order(),
				'board_id'          => $board_id,
				'title'             => $column->get_title(),
				'color'             => $column->get_color(),
				'total_cards_count' => 0,
				'has_more'          => false,
				'cards'             => [],
			];
		}

		$cards_cache_key = "card_objects_$cache_key";
		$card_objects    = wp_cache_get( $cards_cache_key, $cache_group );

		$columns_cards_count_cache_key = "columns_cards_count_$cache_key";
		$columns_cards_count_list      = wp_cache_get( $columns_cards_count_cache_key, $cache_group );

		global $wpdb;
		if ( ! is_array( $card_objects ) || false === $columns_cards_count_list ) {
			$card_query = $this->get_card_query( $request );

			$board_type = $board->get_board_type();

			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.NotPrepared -- We're preparing the query above, and here we're just replacing parts of the SQL query with some static values.
			$columns_cards_count_list = $wpdb->get_results( str_replace( 'card.*, post.post_title, post.post_content', 'column_id, COUNT(*) AS cards', $card_query . ( 'scrum' === $board_type ? ' AND sprint_id != 0' : '' ) . ' GROUP BY column_id' ) );
			// phpcs:enable
			wp_cache_set( $columns_cards_count_cache_key, $columns_cards_count_list, $cache_group );

			$card_query .= ' ORDER BY sort_order';

			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- we've prepared the $card_query above, and here we're just executing the query.
			$card_objects = $wpdb->get_results( $card_query );
			// phpcs:enable
			wp_cache_set( $cards_cache_key, $card_objects, $cache_group );
		}

		foreach ( ( $columns_cards_count_list ?? [] ) as $columns_cards_count_item ) {
			$column_id = absint( $columns_cards_count_item->column_id );
			if ( 0 === $column_id ) {
				$response['backlog_cards_count'] = (int) $columns_cards_count_item->cards;
				continue;
			}
			$response['columns'][ (int) $columns_cards_count_item->column_id ]['total_cards_count'] = (int) $columns_cards_count_item->cards;
		}

		$all_card_data = [];
		$all_card_ids  = [];
		$all_task_ids  = [];
		$task_card_map = [];
		foreach ( $card_objects as $card_object ) {
			$card_id                   = absint( $card_object->id );
			$task_id                   = absint( $card_object->task_id );
			$all_card_ids[]            = $card_id;
			$all_task_ids[]            = $task_id;
			$task_card_map[ $task_id ] = $card_id;
			$all_card_data[ $card_id ] = [
				'id'                => $card_id,
				'column_id'         => absint( $card_object->column_id ),
				'board_id'          => absint( $card_object->board_id ),
				'task_id'           => $task_id,
				'sprint_id'         => absint( $card_object->sprint_id ),
				'title'             => $card_object->post_title,
				'sort_order'        => absint( $card_object->sort_order ),
				'due_date'          => ! empty( $card_object->due_date ) ? get_date_from_gmt( $card_object->due_date, 'Y-m-d' ) : null,
				'assignees'         => [],
				'priority'          => null,
				'labels'            => [],
				'story_point'       => absint( $card_object->story_point ),
				'checklists_count'  => [
					'items_count'      => 0,
					'is_checked_count' => 0,
				],
				'attachments_count' => 0,
				'comments_count'    => 0,
				'created_by'        => [
					'display_name' => $card_object->display_name,
					'username'     => $card_object->user_login,
					'avatar'       => get_avatar_url( $card_object->user_email ),
					'id'           => absint( $card_object->post_author ),
				],
			];
		}

		$all_card_ids_formatter = implode( ',', array_fill( 0, count( $all_card_ids ), '%d' ) );
		$all_task_ids_formatter = implode( ',', array_fill( 0, count( $all_task_ids ), '%d' ) );
		if ( ! empty( $all_task_ids ) ) {
			$card_ids_cache_key = md5( wp_json_encode( $all_card_ids ) );
			$terms              = wp_get_object_terms(
				$all_task_ids,
				[ 'gemboards_label', 'gemboards_priority' ],
				[
					'fields' => 'all_with_object_id',
				]
			);
			foreach ( $terms as $term ) {
				if ( 'gemboards_label' === $term->taxonomy ) {
					$all_card_data[ $task_card_map[ absint( $term->object_id ) ] ]['labels'][] = $labels[ $term->term_id ];
				} else {
					$all_card_data[ $task_card_map[ absint( $term->object_id ) ] ]['priority'] = $priorities[ $term->term_id ];
				}
			}

			$assignees_cache_key = "card_assignees_$card_ids_cache_key";
			$assignees           = wp_cache_get( $assignees_cache_key, $cache_group );
			if ( false === $assignees ) {
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- There is no unfinished prepared here.
				$assignees = $wpdb->get_results(
					$wpdb->prepare( "
						SELECT
							assignee.*,
							user.display_name,
							user.user_email
						FROM
							{$wpdb->prefix}gemboards_card_assignees assignee
							INNER JOIN $wpdb->users user ON user.ID = assignee.user_id
						WHERE
							assignee.card_id IN ($all_card_ids_formatter)
					", ...$all_card_ids )
				);
				// phpcs:enable
				wp_cache_set( $assignees_cache_key, $assignees, $cache_group );
			}

			foreach ( $assignees as $assignee ) {
				$all_card_data[ (int) $assignee->card_id ]['assignees'][] = [
					'id'     => (int) $assignee->user_id,
					'name'   => $assignee->display_name,
					'avatar' => get_avatar_url( $assignee->user_email, [ 'size' => 96 ] ),
				];
			}
		}

		$active_sprint_ids = [];
		if ( 'scrum' === $board->get_board_type() && Helper::has_pro() ) {
			try {
				$active_sprint_ids = ( new SprintCollection( [
					'per_page' => - 1,
					'fields'   => 'ids',
					'where'    => [
						[
							'key'   => 'board_id',
							'value' => $board_id,
						],
						[
							'key'   => 'is_active',
							'value' => 1,
						],
					],
				] ) )->get_results();
			} catch ( GemboardsInvalidArgumentException $e ) {
				Helper::error_log( $e->getMessage() );
			}
		}

		if ( ! empty($all_card_ids) && Helper::has_pro() ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
			$all_card_id_counts = $wpdb->get_results(
				$wpdb->prepare( "SELECT
						card_id,
						COUNT(*) AS total,
						SUM( IF(is_checked = 1, 1, 0) ) AS completed
					FROM
						{$wpdb->prefix}gemboards_pro_task_checklists
					WHERE
						card_id IN ($all_card_ids_formatter)
					GROUP BY card_id", ...$all_card_ids)
			);
			// phpcs:enable
			foreach ( $all_card_id_counts as $all_card_id_count ) {
				$all_card_data[ absint($all_card_id_count->card_id) ]['checklists_count']['items_count']      = absint($all_card_id_count->total);
				$all_card_data[ absint($all_card_id_count->card_id) ]['checklists_count']['is_checked_count'] = absint($all_card_id_count->completed);
			}
		}

		if ( ! empty($all_task_ids) ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
			$all_task_id_counts = $wpdb->get_results(
				$wpdb->prepare( "SELECT
						task_id,
						COUNT(*) AS total
					FROM
						{$wpdb->prefix}gemboards_task_attachments
					WHERE
						task_id IN ($all_task_ids_formatter)
					GROUP BY task_id", ...$all_task_ids)
			);
			foreach ( $all_task_id_counts as $all_task_id_count ) {
				$all_card_data[ $task_card_map[ absint($all_task_id_count->task_id) ] ]['attachments_count'] = absint($all_task_id_count->total);
			}

			$task_comments_counts = $wpdb->get_results(
				$wpdb->prepare("SELECT
					comment_post_ID AS task_id,
					COUNT(*) AS total
				FROM
					$wpdb->comments
				WHERE
					comment_type = 'task_comment' AND comment_post_ID IN ($all_task_ids_formatter)
				GROUP BY comment_post_ID", ...$all_task_ids)
			);
			// phpcs:enable
			foreach ( $task_comments_counts as $task_comment_counts_item ) {
				$all_card_data[ $task_card_map[ absint($task_comment_counts_item->task_id) ] ]['comments_count'] = absint($task_comment_counts_item->total);
			}
		}

		foreach ( $all_card_data as $all_card_datum ) {
			if ( 0 === $all_card_datum['column_id'] ) {
				continue;
			}
			if ( 'scrum' === $board->get_board_type() && ( 0 === $all_card_datum['sprint_id'] || ! in_array( $all_card_datum['sprint_id'], $active_sprint_ids, true ) ) ) {
				$response['columns'][ $all_card_datum['column_id'] ]['total_cards_count']--;
				continue;
			}
			$response['columns'][ $all_card_datum['column_id'] ]['cards'][] = $all_card_datum;
		}

		foreach ( $response['columns'] as $col_id => $col_data ) {
			if ( $col_data['total_cards_count'] > 30 ) {
				$response['columns'][ $col_id ]['has_more'] = true;
				$response['columns'][ $col_id ]['cards']    = array_slice( $col_data['cards'], 0, 30 );
			}
		}

		if ( 'scrum' === $board->get_board_type() ) {
			$backlog_cards = [];
			foreach ( $all_card_data as $all_card_datum ) {
				if ( 0 === $all_card_datum['sprint_id'] ) {
					$backlog_cards[] = $all_card_datum;
				}
			}

			$backlog_total                   = count( $backlog_cards );
			$response['backlog_total_count'] = $backlog_total;
			$response['backlog_has_more']    = $backlog_total > 30;
			$response['backlog_cards']       = array_slice( $backlog_cards, 0, 30 );
			$sprint_cache_key          = "board_sprints_$board_id";
			$sprint_cache_group        = 'gemboards_pro_sprints';
			$sprints                   = wp_cache_get( $sprint_cache_key, $sprint_cache_group );
			if ( false === $sprints ) {
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
				$sprints = $wpdb->get_results(
					$wpdb->prepare( "
					SELECT * FROM {$wpdb->prefix}gemboards_pro_sprints WHERE is_completed = 0 AND board_id = %d
				", $board_id )
				);
				// phpcs:enable
				wp_cache_set( $sprint_cache_key, $sprints, $sprint_cache_group );
			}

			$sprints_data = [];
			foreach ( $sprints as $sprint ) {
				$sprints_data[ (int) $sprint->id ] = [
					'id'           => (int) $sprint->id,
					'board_id'     => $board_id,
					'title'        => $sprint->title,
					'goal'         => $sprint->goal,
					'start_date'   => ! empty( $sprint->start_date ) ? get_date_from_gmt( $sprint->start_date ) : null,
					'end_date'     => ! empty( $sprint->end_date ) ? get_date_from_gmt( $sprint->end_date ) : null,
					'is_active'    => (bool) $sprint->is_active,
					'is_completed' => (bool) $sprint->is_completed,
					'has_more'     => false,
					'total_count'  => 0,
					'cards'        => [],
				];
			}
			foreach ( $all_card_data as $all_card_datum ) {
				if ( 0 === $all_card_datum['sprint_id'] ) {
					continue;
				}
				$sprints_data[ $all_card_datum['sprint_id'] ]['cards'][] = $all_card_datum;
			}

			foreach ( $sprints_data as $sid => $sprint_data ) {
				$sprint_total                          = count( $sprint_data['cards'] );
				$sprints_data[ $sid ]['total_count']   = $sprint_total;
				$sprints_data[ $sid ]['has_more']      = $sprint_total > 30;
				$sprints_data[ $sid ]['cards']         = array_slice( $sprint_data['cards'], 0, 30 );
			}

			$response['sprints'] = array_values( $sprints_data );
		}

		$response['columns'] = array_values( $response['columns'] );
		wp_cache_set( $cache_key, $response, $cache_group );

		return rest_ensure_response( $response );
	}

	private function update_recently_open_boards( int $board_id ) {
		$user_id = get_current_user_id();

		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Clearing cache through entity class.
		$wpdb->delete(
			$wpdb->prefix . 'gemboards_board_visits',
			[
				'board_id' => $board_id,
				'user_id'  => $user_id,
			],
			[ '%d', '%d' ]
		);
		// phpcs:enable
		( new BoardVisit() )->clear_cache();

		$board_visit = new BoardVisit();
		$board_visit->set_board_id( $board_id );
		$board_visit->set_user_id( $user_id );
		$board_visit->save();
	}

	public function get_column_cards( $request ) {
		$board_id = (int) $request->get_param( 'id' );
		$board    = Helper::get_board( $board_id );
		if ( ! $board ) {
			return new WP_Error( 'not_found', __( 'Board not found', 'gemboards' ), [ 'status' => 404 ] );
		}

		$column_id = (int) $request->get_param( 'column_id' );
		$column    = Helper::get_column( $column_id );
		if ( ! $column ) {
			return new WP_Error( 'not_found', __( 'Column not found', 'gemboards' ), [ 'status' => 404 ] );
		}

		[ 'per_page' => $per_page, 'page' => $page ] = $this->get_pagination_args( $request );

		$cache_key = md5( wp_json_encode( [
			'board_id'     => $board_id,
			'column_id'    => $column_id,
			'search'       => $request->get_param( 'search' ),
			'assignee_ids' => $request->get_param( 'assignee_ids' ),
			'per_page'     => $per_page,
			'page'         => $page,
		] ) );
		$cache_group = 'gemboards_single_board_api';

		$response = [
			'id'          => $column->get_id(),
			'sort_order'  => $column->get_sort_order(),
			'board_id'    => $board_id,
			'title'       => $column->get_title(),
			'color'       => $column->get_color(),
			'cards'       => [],
			'has_more'    => false,
			'total_count' => 0,
			'page'        => $page,
			'per_page'    => $per_page,
		];

		$cards_cache_key = "card_objects_$cache_key";
		$cached          = wp_cache_get( $cards_cache_key, $cache_group );

		global $wpdb;
		if ( false === $cached ) {
			$extra_where = $wpdb->prepare( ' AND card.column_id = %d', $column->get_id() );

			if ( 'scrum' === $board->get_board_type() && Helper::has_pro() ) {
				$active_sprint_ids = [];
				try {
					$active_sprint_ids = ( new SprintCollection( [
						'per_page' => -1,
						'fields'   => 'ids',
						'where'    => [
							[ 'key' => 'board_id', 'value' => $board_id ],
							[ 'key' => 'is_active', 'value' => 1 ],
						],
					] ) )->get_results();
				} catch ( GemboardsInvalidArgumentException $e ) {
					Helper::error_log( $e->getMessage() );
				}
				if ( ! empty( $active_sprint_ids ) ) {
					$sprint_ids_formatter = implode( ',', array_fill( 0, count( $active_sprint_ids ), '%d' ) );
					// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
					$extra_where .= $wpdb->prepare( " AND card.sprint_id IN ($sprint_ids_formatter)", ...$active_sprint_ids );
					// phpcs:enable
				} else {
					$extra_where .= ' AND 1 = 0';
				}
			}

			$paginated = $this->get_paginated_card_query( $request, $per_page, $page, $extra_where );
			$cached    = [
				'objects'     => $paginated['results'],
				'total_count' => $paginated['total_count'],
				'has_more'    => $paginated['has_more'],
			];
			wp_cache_set( $cards_cache_key, $cached, $cache_group );
		}

		$response['total_count'] = $cached['total_count'];
		$response['has_more']    = $cached['has_more'];
		$response['cards']       = array_values( $this->build_card_data_from_objects( $cached['objects'], $cache_group ) );

		wp_cache_set( $cache_key, $response, $cache_group );

		return rest_ensure_response( $response );
	}

	public function get_archived_cards( $request ) {
		$board_id = (int) $request->get_param( 'id' );
		$board    = Helper::get_board( $board_id );
		if ( ! $board ) {
			return new WP_Error( 'not_found', __( 'Board not found', 'gemboards' ), [ 'status' => 404 ] );
		}

		$cache_key   = md5( wp_json_encode( [
			'board_id' => $board_id,
			'archived' => true,
			'search'   => $request->get_param( 'search' ),
		] ) );
		$cache_group = 'gemboards_single_board_api';

		$response = [];

		$cards_cache_key = "card_objects_$cache_key";
		$card_objects    = wp_cache_get( $cards_cache_key, $cache_group );

		global $wpdb;
		if ( ! is_array( $card_objects ) ) {
			$card_query = $this->get_card_query($request);
			$card_query = str_replace( 'card.archived = 0', 'card.archived = 1', $card_query );
			$card_query .= ' ORDER BY id';
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared -- `$card_query` is already prepared above.
			$card_objects = $wpdb->get_results( $card_query );
			// phpcs:enable
			wp_cache_set( $cards_cache_key, $card_objects, $cache_group );
		}

		$all_card_data = [];
		$all_card_ids  = [];
		$all_task_ids  = [];
		foreach ( $card_objects as $card_object ) {
			$card_id                   = absint( $card_object->id );
			$task_id                   = absint( $card_object->task_id );
			$all_card_ids[]            = $card_id;
			$all_task_ids[]            = $task_id;
			$all_card_data[ $card_id ] = [
				'id'         => $card_id,
				'board_id'   => absint( $card_object->board_id ),
				'task_id'    => $task_id,
				'sprint_id'  => absint( $card_object->sprint_id ),
				'title'      => $card_object->post_title,
				'sort_order' => absint( $card_object->sort_order ),
				'due_date'   => ! empty( $card_object->due_date ) ? get_date_from_gmt( $card_object->due_date, 'Y-m-d' ) : null,
				'assignees'  => [],
			];
		}

		if ( ! empty( $all_task_ids ) ) {
			$card_ids_cache_key     = md5( wp_json_encode( $all_card_ids ) );
			$all_card_ids_formatter = implode( ',', array_fill( 0, count( $all_card_ids ), '%d' ) );
			$assignees_cache_key    = "card_assignees_$card_ids_cache_key";
			$assignees              = wp_cache_get( $assignees_cache_key, $cache_group );
			if ( false === $assignees ) {
				$assignees = $wpdb->get_results(
					$wpdb->prepare( "
						SELECT
							assignee.*,
							user.display_name,
							user.user_email
						FROM
							{$wpdb->prefix}gemboards_card_assignees assignee
							INNER JOIN $wpdb->users user ON user.ID = assignee.user_id
						WHERE
							assignee.card_id IN ($all_card_ids_formatter)
					", ...$all_card_ids )
				);
				wp_cache_set( $assignees_cache_key, $assignees, $cache_group );
			}

			foreach ( $assignees as $assignee ) {
				$all_card_data[ (int) $assignee->card_id ]['assignees'][] = [
					'id'     => (int) $assignee->user_id,
					'name'   => $assignee->display_name,
					'avatar' => get_avatar_url( $assignee->user_email, [ 'size' => 96 ] ),
				];
			}
		}

		$response['cards'] = array_values( $all_card_data );

		wp_cache_set( $cache_key, $response, $cache_group );

		return rest_ensure_response( $response );
	}

	public function get_backlog_cards( WP_REST_Request $request ) {
		$board_id = (int) $request->get_param( 'id' );
		$board    = Helper::get_board( $board_id );
		if ( ! $board ) {
			return new WP_Error( 'not_found', __( 'Board not found', 'gemboards' ), [ 'status' => 404 ] );
		}
		if ( 'scrum' !== $board->get_board_type() ) {
			return new WP_Error( 'not_found', __( 'Board not found', 'gemboards' ), [ 'status' => 404 ] );
		}

		[ 'per_page' => $per_page, 'page' => $page ] = $this->get_pagination_args( $request );

		$cache_key = md5( wp_json_encode( [
			'board_id'     => $board_id,
			'search'       => $request->get_param( 'search' ),
			'assignee_ids' => $request->get_param( 'assignee_ids' ),
			'per_page'     => $per_page,
			'page'         => $page,
			'type'         => 'backlog',
		] ) );
		$cache_group     = 'gemboards_single_board_api';
		$cards_cache_key = "backlog_cards_$cache_key";
		$cached          = wp_cache_get( $cards_cache_key, $cache_group );

		if ( false === $cached ) {
			$paginated = $this->get_paginated_card_query( $request, $per_page, $page, ' AND card.sprint_id = 0' );
			$cached    = [
				'objects'     => $paginated['results'],
				'total_count' => $paginated['total_count'],
				'has_more'    => $paginated['has_more'],
			];
			wp_cache_set( $cards_cache_key, $cached, $cache_group );
		}

		return rest_ensure_response( [
			'cards'       => array_values( $this->build_card_data_from_objects( $cached['objects'], $cache_group ) ),
			'has_more'    => $cached['has_more'],
			'total_count' => $cached['total_count'],
			'page'        => $page,
			'per_page'    => $per_page,
		] );
	}

	public function get_sprint_cards( WP_REST_Request $request ) {
		$board_id  = (int) $request->get_param( 'id' );
		$sprint_id = (int) $request->get_param( 'sprint_id' );

		$board = Helper::get_board( $board_id );
		if ( ! $board ) {
			return new WP_Error( 'not_found', __( 'Board not found', 'gemboards' ), [ 'status' => 404 ] );
		}

		if ( ! Helper::has_pro() ) {
			return new WP_Error( 'not_found', __( 'Sprint not found', 'gemboards' ), [ 'status' => 404 ] );
		}

		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$sprint = $wpdb->get_row( $wpdb->prepare(
			"SELECT id FROM {$wpdb->prefix}gemboards_pro_sprints WHERE id = %d AND board_id = %d",
			$sprint_id,
			$board_id
		) );
		// phpcs:enable
		if ( ! $sprint ) {
			return new WP_Error( 'not_found', __( 'Sprint not found', 'gemboards' ), [ 'status' => 404 ] );
		}

		[ 'per_page' => $per_page, 'page' => $page ] = $this->get_pagination_args( $request );

		$cache_key = md5( wp_json_encode( [
			'board_id'     => $board_id,
			'sprint_id'    => $sprint_id,
			'search'       => $request->get_param( 'search' ),
			'assignee_ids' => $request->get_param( 'assignee_ids' ),
			'per_page'     => $per_page,
			'page'         => $page,
			'type'         => 'sprint',
		] ) );
		$cache_group     = 'gemboards_single_board_api';
		$cards_cache_key = "sprint_cards_$cache_key";
		$cached          = wp_cache_get( $cards_cache_key, $cache_group );

		if ( false === $cached ) {
			// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
			$extra_where = $wpdb->prepare( ' AND card.sprint_id = %d', $sprint_id );
			// phpcs:enable
			$paginated = $this->get_paginated_card_query( $request, $per_page, $page, $extra_where );
			$cached    = [
				'objects'     => $paginated['results'],
				'total_count' => $paginated['total_count'],
				'has_more'    => $paginated['has_more'],
			];
			wp_cache_set( $cards_cache_key, $cached, $cache_group );
		}

		return rest_ensure_response( [
			'cards'       => array_values( $this->build_card_data_from_objects( $cached['objects'], $cache_group ) ),
			'has_more'    => $cached['has_more'],
			'total_count' => $cached['total_count'],
			'page'        => $page,
			'per_page'    => $per_page,
		] );
	}

	private function get_card_query( WP_REST_Request $request ): string {
		global $wpdb;
		$board_id   = absint( $request->get_param( 'id' ) );
		$card_query = $wpdb->prepare( "
			SELECT card.*, post.post_title, post.post_content, post.post_author, user.display_name, user.user_email, user.user_login
				FROM {$wpdb->prefix}gemboards_cards card
				    INNER JOIN $wpdb->posts post ON card.task_id = post.ID
					LEFT JOIN $wpdb->users user ON user.ID = post.post_author
				WHERE card.archived = 0 AND post.post_parent = 0 AND card.board_id = %d", $board_id );

		$search = $request->get_param( 'search' );
		if ( ! empty( $search ) ) {
			$card_query .= $wpdb->prepare( ' AND (post.post_title LIKE %s OR post.post_content LIKE %s)', "%$search%", "%$search%" );
		}

		$priority = $request->get_param( 'priority' );
		if ( ! empty( $priority ) ) {
			$priority_slugs = explode( ',', $priority );
			$priority_slugs = array_map('trim', $priority_slugs);
			if ( ! empty( $priority_slugs ) ) {
				$priority_formatter = implode( ',', array_fill( 0, count( $priority_slugs ), '%s' ) );
				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- We're using dynamic numbers of format specifiers here.'
				$card_query .= $wpdb->prepare( " AND EXISTS (
						SELECT
							1
						FROM
							$wpdb->term_relationships tr
							INNER JOIN $wpdb->term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
							INNER JOIN $wpdb->terms t ON t.term_id = tt.term_id
						WHERE
							tr.object_id = post.ID
							AND tt.taxonomy = 'gemboards_priority'
							AND t.slug IN ( $priority_formatter )
					)", ...$priority_slugs );
				// phpcs:enable
			}
		}

		$labels = $request->get_param( 'labels' );
		if ( ! empty( $labels ) ) {
			$label_slugs = explode( ',', $labels );
			$label_slugs = array_map('trim', $label_slugs);
			if ( ! empty( $label_slugs ) ) {
				$labels_formatter = implode( ',', array_fill( 0, count( $label_slugs ), '%s' ) );
				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- We're using dynamic numbers of format specifiers here.'
				$card_query .= $wpdb->prepare( " AND EXISTS (
						SELECT
							1
						FROM
							$wpdb->term_relationships tr
							INNER JOIN $wpdb->term_taxonomy tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
							INNER JOIN $wpdb->terms t ON t.term_id = tt.term_id
						WHERE
							tr.object_id = post.ID
							AND tt.taxonomy = 'gemboards_label'
							AND t.slug IN ( $labels_formatter )
					)", ...$label_slugs );
				// phpcs:enable
			}
		}

		$created_by = $request->get_param( 'created_by' );
		if ( ! empty( $created_by ) ) {
			$created_by = explode( ',', $created_by );
			$created_by = array_map('trim', $created_by);
			if ( ! empty( $created_by ) ) {
				$created_by           = array_map( 'absint', $created_by );
				$created_by_formatter = implode( ',', array_fill( 0, count( $created_by ), '%d' ) );
				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- We're using dynamic numbers of format specifiers here.
				$card_query .= $wpdb->prepare( " AND post.post_author in ( $created_by_formatter )", ...$created_by );
				// phpcs:enable
			}
		}

		$assignee_ids     = [];
		$has_not_assigned = false;
		if ( ! empty( $request->get_param( 'assignee_ids' ) ) ) {
			$assignee_ids     = explode( ',', $request->get_param( 'assignee_ids' ) );
			$has_not_assigned = in_array( 'not-assigned', $assignee_ids, true );
			$assignee_ids     = array_map( 'absint', $assignee_ids );
			$assignee_ids     = array_filter( $assignee_ids );
		}

		if ( empty( $assignee_ids ) && $has_not_assigned ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- We're using dynamic numbers of format specifiers here.
			$card_query .= " AND (
					NOT EXISTS (
						SELECT
							1
						FROM
							{$wpdb->prefix}gemboards_card_assignees AS ca2
						WHERE
							ca2.card_id = card.id
						LIMIT 1
					)
				)";
			// phpcs:enable
		} elseif ( ! empty( $assignee_ids ) ) {
			$assignee_ids_formatter = implode( ',', array_fill( 0, count( $assignee_ids ), '%d' ) );
			if ( $has_not_assigned ) {
				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- We're using dynamic numbers of format specifiers here.
				$card_query .= $wpdb->prepare( " AND (
					EXISTS (
						SELECT
							1
						FROM
							{$wpdb->prefix}gemboards_card_assignees AS ca
						WHERE
							ca.card_id = card.id
							AND ca.user_id IN ($assignee_ids_formatter)
						LIMIT 1
					)
					OR NOT EXISTS (
						SELECT
							1
						FROM
							{$wpdb->prefix}gemboards_card_assignees AS ca2
						WHERE
							ca2.card_id = card.id
						LIMIT 1
					)
				)", ...$assignee_ids );
				// phpcs:enable
			} else {
				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- We're using dynamic numbers of format specifiers here.
				$card_query .= $wpdb->prepare( " AND (
					EXISTS (
						SELECT
							1
						FROM
							{$wpdb->prefix}gemboards_card_assignees AS ca
						WHERE
							ca.card_id = card.id
							AND ca.user_id IN ($assignee_ids_formatter)
						LIMIT 1
					)
				)", ...$assignee_ids );
				// phpcs:enable
			}
		}

		return $card_query;
	}

	private function get_paginated_card_query( WP_REST_Request $request, int $per_page, int $page, string $extra_where = '' ): array {
		global $wpdb;
		$base      = $this->get_card_query( $request ) . $extra_where;
		$count_sql = str_replace(
			'card.*, post.post_title, post.post_content, post.post_author, user.display_name, user.user_email, user.user_login',
			'COUNT(*)',
			$base
		);
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared
		$total   = (int) $wpdb->get_var( $count_sql );
		$offset  = ( $page - 1 ) * $per_page;
		$results = $wpdb->get_results( $base . ' ORDER BY sort_order LIMIT ' . $offset . ', ' . $per_page );
		// phpcs:enable
		return [
			'results'     => $results,
			'total_count' => $total,
			'has_more'    => ( $offset + $per_page ) < $total,
		];
	}

	private function get_pagination_args( WP_REST_Request $request ): array {
		$per_page = (int) ( $request->get_param( 'per_page' ) ?? 30 );
		$page     = (int) ( $request->get_param( 'page' ) ?? 1 );
		$per_page = max( 1, min( 100, $per_page ) );
		$page     = max( 1, $page );
		return [ 'per_page' => $per_page, 'page' => $page ];
	}

	private function build_card_data_from_objects( array $card_objects, string $cache_group ): array {
		global $wpdb;

		$priorities    = Helper::get_all_priorities();
		$labels        = Helper::get_all_labels();
		$all_card_data = [];
		$all_card_ids  = [];
		$all_task_ids  = [];
		$task_card_map = [];

		foreach ( $card_objects as $card_object ) {
			$card_id                   = absint( $card_object->id );
			$task_id                   = absint( $card_object->task_id );
			$all_card_ids[]            = $card_id;
			$all_task_ids[]            = $task_id;
			$task_card_map[ $task_id ] = $card_id;
			$all_card_data[ $card_id ] = [
				'id'                => $card_id,
				'column_id'         => absint( $card_object->column_id ),
				'board_id'          => absint( $card_object->board_id ),
				'task_id'           => $task_id,
				'sprint_id'         => absint( $card_object->sprint_id ),
				'title'             => $card_object->post_title,
				'sort_order'        => absint( $card_object->sort_order ),
				'due_date'          => ! empty( $card_object->due_date ) ? get_date_from_gmt( $card_object->due_date, 'Y-m-d' ) : null,
				'assignees'         => [],
				'priority'          => null,
				'labels'            => [],
				'story_point'       => absint( $card_object->story_point ),
				'checklists_count'  => [
					'items_count'      => 0,
					'is_checked_count' => 0,
				],
				'attachments_count' => 0,
				'comments_count'    => 0,
				'created_by'        => [
					'display_name' => $card_object->display_name,
					'username'     => $card_object->user_login,
					'avatar'       => get_avatar_url( $card_object->user_email ),
					'id'           => absint( $card_object->post_author ),
				],
			];
		}

		if ( empty( $all_task_ids ) ) {
			return $all_card_data;
		}

		$all_card_ids_formatter = implode( ',', array_fill( 0, count( $all_card_ids ), '%d' ) );
		$all_task_ids_formatter = implode( ',', array_fill( 0, count( $all_task_ids ), '%d' ) );
		$card_ids_cache_key     = md5( wp_json_encode( $all_card_ids ) );

		$terms = wp_get_object_terms(
			$all_task_ids,
			[ 'gemboards_label', 'gemboards_priority' ],
			[ 'fields' => 'all_with_object_id' ]
		);
		foreach ( $terms as $term ) {
			if ( 'gemboards_label' === $term->taxonomy ) {
				$all_card_data[ $task_card_map[ absint( $term->object_id ) ] ]['labels'][] = $labels[ $term->term_id ];
			} else {
				$all_card_data[ $task_card_map[ absint( $term->object_id ) ] ]['priority'] = $priorities[ $term->term_id ];
			}
		}

		$assignees_cache_key = "card_assignees_$card_ids_cache_key";
		$assignees           = wp_cache_get( $assignees_cache_key, $cache_group );
		if ( false === $assignees ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
			$assignees = $wpdb->get_results(
				$wpdb->prepare( "
					SELECT
						assignee.*,
						user.display_name,
						user.user_email
					FROM
						{$wpdb->prefix}gemboards_card_assignees assignee
						INNER JOIN $wpdb->users user ON user.ID = assignee.user_id
					WHERE
						assignee.card_id IN ($all_card_ids_formatter)
				", ...$all_card_ids )
			);
			// phpcs:enable
			wp_cache_set( $assignees_cache_key, $assignees, $cache_group );
		}
		foreach ( $assignees as $assignee ) {
			$all_card_data[ (int) $assignee->card_id ]['assignees'][] = [
				'id'     => (int) $assignee->user_id,
				'name'   => $assignee->display_name,
				'avatar' => get_avatar_url( $assignee->user_email, [ 'size' => 96 ] ),
			];
		}

		if ( Helper::has_pro() ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
			$checklist_counts = $wpdb->get_results(
				$wpdb->prepare( "SELECT
						card_id,
						COUNT(*) AS total,
						SUM( IF(is_checked = 1, 1, 0) ) AS completed
					FROM
						{$wpdb->prefix}gemboards_pro_task_checklists
					WHERE
						card_id IN ($all_card_ids_formatter)
					GROUP BY card_id", ...$all_card_ids )
			);
			// phpcs:enable
			foreach ( $checklist_counts as $cc ) {
				$all_card_data[ absint( $cc->card_id ) ]['checklists_count']['items_count']      = absint( $cc->total );
				$all_card_data[ absint( $cc->card_id ) ]['checklists_count']['is_checked_count'] = absint( $cc->completed );
			}
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		$attachment_counts = $wpdb->get_results(
			$wpdb->prepare( "SELECT
					task_id,
					COUNT(*) AS total
				FROM
					{$wpdb->prefix}gemboards_task_attachments
				WHERE
					task_id IN ($all_task_ids_formatter)
				GROUP BY task_id", ...$all_task_ids )
		);
		foreach ( $attachment_counts as $ac ) {
			$all_card_data[ $task_card_map[ absint( $ac->task_id ) ] ]['attachments_count'] = absint( $ac->total );
		}

		$comment_counts = $wpdb->get_results(
			$wpdb->prepare( "SELECT
				comment_post_ID AS task_id,
				COUNT(*) AS total
			FROM
				$wpdb->comments
			WHERE
				comment_type = 'task_comment' AND comment_post_ID IN ($all_task_ids_formatter)
			GROUP BY comment_post_ID", ...$all_task_ids )
		);
		// phpcs:enable
		foreach ( $comment_counts as $cc ) {
			$all_card_data[ $task_card_map[ absint( $cc->task_id ) ] ]['comments_count'] = absint( $cc->total );
		}

		return $all_card_data;
	}

	private function get_board_array( Board $board ): array {
		$data = $board->get_data();
		unset( $data['settings'] );

		return array_merge( $data, [
			'id'                  => $board->get_id(),
			'completed_column_id' => $board->get_completed_column_id(),
			'created_at'          => $board->get_date_created_at() ? $board->get_date_created_at()->format( 'Y-m-d H:i:s' ) : null,
			'updated_at'          => $board->get_date_updated_at() ? $board->get_date_updated_at()->format( 'Y-m-d H:i:s' ) : null,
		] );
	}
}
