<?php

namespace GemBoards\API;

use GemBoards\Classes\Exception\GemboardsInvalidArgumentException;
use GemBoards\Collections\BoardCollection;
use GemBoards\Collections\BoardVisitCollection;
use GemBoards\entities\BoardVisit;
use GemBoards\Utils\Helper;
use stdClass;
use WP_Error;
use WP_REST_Server;

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

class Dashboard {

	/**
	 * The namespace of this controller's route.
	 *
	 * @var string
	 */
	protected string $namespace;

	/**
	 * The base of this controller's route.
	 *
	 * @var string
	 */
	protected string $rest_base;

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

	public function register_routes() {
		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/stats',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_stats' ],
				'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' ),
							[ 'status' => rest_authorization_required_code() ]
						);
					}

					return true;
				},
				'args'                => [],
			],
		);
		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/overview',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_overview' ],
				'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 . '/status-overview',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_status_overview' ],
				'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'                => [],
				'schema'              => [
					'$schema' => 'http://json-schema.org/draft-04/schema#',
					'title'   => __( 'Dashboard Status Overview', 'gemboards' ),
					'type'    => 'array',
					'items'   => [
						'type'       => 'object',
						'properties' => [
							'title'      => 'string',
							'slug'       => 'string',
							'card_count' => 'integer',
						],
					],
				],
			],
		);
		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/current-week-tasks',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_current_week_tasks' ],
				'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 . '/assigned-tasks',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_assigned_tasks' ],
				'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 . '/recently-open',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_recently_open' ],
				'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'                => [],
				'schema'              => [
					'$schema' => 'http://json-schema.org/draft-04/schema#',
					'title'   => __( 'Dashboard Status Overview', 'gemboards' ),
					'type'    => 'array',
					'items'   => [
						'type'       => 'object',
						'properties' => [
							'title'      => 'string',
							'slug'       => 'string',
							'card_count' => 'integer',
						],
					],
				],
			],
		);
		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/bookmarks',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_bookmarks' ],
				'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'                => [],
			],
		);
	}

	public function get_stats() {
		$cache_key   = 'dashboard_stats_' . get_current_user_id();
		$cache_group = 'gemboards_dashboard_stats';
		$has_cache   = wp_cache_get( $cache_key, $cache_group );
		if ( $has_cache ) {
			return rest_ensure_response( $has_cache );
		}

		$data = apply_filters( 'gemboards/dashboard/user_stats', [
			'overview'        => $this->get_overview_data(),
			'status_overview' => $this->get_status_overview_data(),
		] );
		wp_cache_set( $cache_key, $data, $cache_group );

		return rest_ensure_response( $data );
	}

	private function get_overview_data() {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Implemented caching on upper level.
		if ( current_user_can( 'manage_gemboards' ) ) {
			$result = $wpdb->get_row(
				"SELECT
						  SUM(IF(cnt > 0, 1, 0)) AS assigned_count,
						  SUM(IF(cnt = 0, 1, 0)) AS unassigned_count,
						  SUM(IF(t.is_completed = 0, 1, 0)) AS in_progress_count,
						  SUM(IF(t.is_completed = 1, 1, 0)) AS completed_count
						FROM (
						  SELECT c.is_completed, COUNT(ca.card_id) AS cnt
						  FROM {$wpdb->prefix}gemboards_cards c
						  LEFT JOIN {$wpdb->prefix}gemboards_card_assignees ca
						    ON c.id = ca.card_id
						  WHERE c.updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
						  GROUP BY c.id
						) AS t;"
			);
		} else {
			$result = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT
						SUM(IF(cnt > 0, 1, 0)) AS assigned_count,
						SUM(IF(cnt = 0, 1, 0)) AS unassigned_count,
						SUM(IF(t.is_completed = 0, 1, 0)) AS in_progress_count,
						SUM(IF(t.is_completed = 1, 1, 0)) AS completed_count
					FROM
						(
							SELECT
								c.is_completed,
								COUNT(ca.card_id) AS cnt
							FROM
								{$wpdb->prefix}gemboards_cards c
								LEFT JOIN {$wpdb->prefix}gemboards_card_assignees ca ON c.id = ca.card_id
								INNER JOIN {$wpdb->prefix}gemboards_boards board ON board.project_id = c.board_id
								INNER JOIN {$wpdb->prefix}gemboards_roles rl ON rl.object_type = 'project'
								AND rl.object_id = board.project_id
								AND rl.user_id = %d
							WHERE
								c.updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
							GROUP BY
								c.id
						) AS t", get_current_user_id()
				)
			);
		}
		// phpcs:enable

		if ( ! $result instanceof stdClass ) {
			return ( (object) [
				'assigned_count'    => 0,
				'unassigned_count'  => 0,
				'in_progress_count' => 0,
				'completed_count'   => 0,
			] );
		}

		$result->assigned_count    = (int) $result->assigned_count;
		$result->unassigned_count  = (int) $result->unassigned_count;
		$result->in_progress_count = (int) $result->in_progress_count;
		$result->completed_count   = (int) $result->completed_count;

		return $result;
	}

	private function get_status_overview_data() {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Implemented caching on upper level.
		if ( current_user_can( 'manage_gemboards' ) ) {
			return $wpdb->get_results( "SELECT
				col.title,
				col.slug,
				( SELECT col2.color FROM {$wpdb->prefix}gemboards_columns col2
					WHERE col2.slug = col.slug LIMIT 1
				) AS color,
				COUNT(c.id) AS card_count
			FROM
				{$wpdb->prefix}gemboards_columns col
				INNER JOIN {$wpdb->prefix}gemboards_cards c ON c.column_id = col.id
				GROUP BY col.title, col.slug" );
		} else {
			return $wpdb->get_results(
				$wpdb->prepare( "SELECT
						col.title,
						col.slug,
						( SELECT col2.color FROM {$wpdb->prefix}gemboards_columns col2
							WHERE col2.slug = col.slug LIMIT 1
						) AS color,
						COUNT(c.id) AS card_count
					FROM
						{$wpdb->prefix}gemboards_columns AS col
						LEFT JOIN {$wpdb->prefix}gemboards_cards AS c ON c.column_id = col.id
					WHERE
						col.board_id IN (
							SELECT
								board.id
							FROM
								{$wpdb->prefix}gemboards_boards AS board
								INNER JOIN {$wpdb->prefix}gemboards_roles AS role ON role.object_id = board.project_id
								AND role.object_type = 'project'
								AND role.user_id = %d
						)
					GROUP BY
						col.title, col.slug", get_current_user_id() )
			);
		}
		// phpcs:enable
	}

	public function get_overview() {
		wp_send_json( $this->get_overview_data() );
	}

	public function get_status_overview() {
		return $this->get_status_overview_data();
	}

	public function get_current_week_tasks() {
		$weekend = get_weekstartend( current_time( 'mysql' ) );

		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
							p.ID as task_id,
							p.post_title,
							c.due_date,
							c.id as card_id,
							c.board_id as board_id,
							c.column_id as column_id,
							c.sort_order as sort_order,
								col.title AS column_title,
								col.color AS column_color
						FROM
							$wpdb->posts p
							INNER JOIN {$wpdb->prefix}gemboards_cards c ON c.task_id = p.ID
							INNER JOIN {$wpdb->prefix}gemboards_boards b ON c.board_id = b.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'
							LEFT JOIN {$wpdb->prefix}gemboards_columns col ON col.id = c.column_id
						WHERE
							p.post_type = 'gemboards_task'
							AND p.post_date >= %s ORDER BY p.ID DESC LIMIT 5", get_current_user_id(), gmdate( 'Y-m-d H:i:s', $weekend['start'] )
			)
		);
		// phpcs:enable

		$card_data = [];
		foreach ( $results as $result ) {
			$card    = Helper::get_card( $result->card_id );
			$task_id = $card->get_task_id();
			$task    = $card->get_task();
			if ( empty( $task ) ) {
				continue;
			}

			$task_description = wp_kses_post($task->post_content);
			$priority         = get_the_terms( $task_id, GEMBOARDS_PLUGIN_SLUG . '_priority' ) ?: [];
			$labels           = get_the_terms( $task_id, GEMBOARDS_PLUGIN_SLUG . '_label' ) ?: [];

			$priority_data = $this->normalize_terms( $priority );
			$priority_data = reset( $priority_data ) ?? null;

			$labels_data = array_map( function ( $term ) {
				return [
					'id'         => (int) $term->term_id,
					'name'       => $term->name,
					'bg_color'   => get_term_meta( $term->term_id, 'bg_color', true ) ?: '#cccccc',
					'text_color' => get_term_meta( $term->term_id, 'text_color', true ) ?: '#000000',
				];
			}, $labels );

			$attachments = array_map( function ( $attachment ) {
				return [
					'id'        => $attachment->get_id(),
					'file_url'  => $attachment->get_file_url(),
					'file_name' => $attachment->get_file_name(),
					'file_type' => $attachment->get_file_type(),
					'file_size' => $attachment->get_file_size(),
					'date'      => $attachment->get_date_created_at(),
				];
			}, $card->get_attachments() );

			$assignee_list = array_map( function ( $assignee ) {
				return [
					'id'     => $assignee->get_user_id(),
					'name'   => get_the_author_meta( 'display_name', $assignee->get_user_id() ),
					'avatar' => get_avatar_url( $assignee->get_user_id(), [ 'size' => 96 ] ),
				];
			}, $card->get_assignees() );

			$checklist = array_map( function ( $checklist ) {
				return [
					'id'         => $checklist->get_id(),
					'label'      => $checklist->get_label(),
					'is_checked' => $checklist->is_checked(),
					'sort_order' => $checklist->get_sort_order(),
				];
			}, $card->get_checklists() );

			$comment_data = array_map( function ( $comment ) {
				return [
					'id'        => (int) $comment->comment_ID,
					'content'   => $comment->comment_content,
					'date_time' => get_comment_date( 'Y-m-d H:i', $comment ),
					'author'    => $comment->comment_author,
					'avatar'    => get_avatar_url( $comment->user_id, [ 'size' => 96 ] ),
				];
			}, Helper::get_card_comments( $task_id ) );

			$activity_data = array_map( function ( $comment ) {
				return [
					'id'        => (int) $comment->comment_ID,
					'content'   => $comment->comment_content,
					'date_time' => get_comment_date( 'Y-m-d H:i', $comment ),
					'author'    => $comment->comment_author,
					'avatar'    => get_avatar_url( $comment->user_id, [ 'size' => 96 ] ),
				];
			}, Helper::get_card_activities( $task_id ) );

			$card_data[] = [
				'id'                   => $card->get_id(),
				'column_id'            => $card->get_column_id(),
				'board_id'             => $card->get_board_id(),
				'task_id'              => $card->get_task_id(),
				'title'                => $task->post_title,
				'description_rendered' => get_post_meta( $task_id, 'task_rendered', true ),
				'stage'                => $result->column_title,
				'stage_color'          => $result->column_color,
				'description'          => $task_description,
				'sort_order'           => $card->get_sort_order(),
				'due_date'             => $card->get_due_date() ? $card->get_due_date()->format( 'Y-m-d' ) : '',
				'assignee'             => $assignee_list,
				'comments'             => $comment_data,
				'activity'             => $activity_data,
				'priority'             => $priority_data,
				'labels'               => $labels_data,
				'attachments'          => $attachments,
				'checklist'            => $checklist,
			];
		}

		return rest_ensure_response( $card_data );
	}

	private function normalize_terms( $terms ): array {
		return array_map( function ( $term ) {
			return [
				'id'   => (int) $term->term_id,
				'name' => $term->name,
				'slug' => $term->slug,
			];
		}, $terms );
	}

	public function get_assigned_tasks() {
		global $wpdb;

		$current_user_id = get_current_user_id();

		if ( ! $current_user_id ) {
			wp_send_json( [] );
		}


		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
                p.ID as task_id,
                p.post_title,
                c.due_date,
                c.id as card_id,
                c.board_id as board_id,
                c.column_id as column_id,
                c.sort_order as sort_order,
                p.post_date as task_created_date,
                col.title AS column_title,
                col.color AS column_color
            FROM
                $wpdb->posts p
                INNER JOIN {$wpdb->prefix}gemboards_cards c ON c.task_id = p.ID
                INNER JOIN {$wpdb->prefix}gemboards_card_assignees a ON a.card_id = c.id
            	LEFT JOIN {$wpdb->prefix}gemboards_columns col ON col.id = c.column_id
            WHERE
                p.post_type = 'gemboards_task'
                AND a.user_id = %d
            ORDER BY 
                task_created_date DESC, 
                p.ID DESC",
				$current_user_id
			)
		);
		// phpcs:enable

		$assigned_data = [];
		foreach ( $results as $result ) {
			$card = Helper::get_card( $result->card_id );
			if ( ! $card ) {
				continue;
			}

			$task_id = $card->get_task_id();
			$task    = $card->get_task();
			if ( empty( $task ) ) {
				continue;
			}

			$task_description = wp_kses_post( $task->post_content );
			$priority         = get_the_terms( $task_id, GEMBOARDS_PLUGIN_SLUG . '_priority' ) ?: [];
			$labels           = get_the_terms( $task_id, GEMBOARDS_PLUGIN_SLUG . '_label' ) ?: [];

			$priority_data = $this->normalize_terms( $priority );
			$priority_data = reset( $priority_data ) ?? null;

			$labels_data = array_map( function ( $term ) {
				return [
					'id'         => (int) $term->term_id,
					'name'       => $term->name,
					'bg_color'   => get_term_meta( $term->term_id, 'bg_color', true ) ?: '#cccccc',
					'text_color' => get_term_meta( $term->term_id, 'text_color', true ) ?: '#000000',
				];
			}, $labels );

			$attachments = array_map( function ( $attachment ) {
				return [
					'id'        => $attachment->get_id(),
					'file_url'  => $attachment->get_file_url(),
					'file_name' => $attachment->get_file_name(),
					'file_type' => $attachment->get_file_type(),
					'file_size' => $attachment->get_file_size(),
					'date'      => $attachment->get_date_created_at(),
				];
			}, $card->get_attachments() );

			$assignee_list = array_map( function ( $assignee ) {
				return [
					'id'     => $assignee->get_user_id(),
					'name'   => get_the_author_meta( 'display_name', $assignee->get_user_id() ),
					'avatar' => get_avatar_url( $assignee->get_user_id(), [ 'size' => 96 ] ),
				];
			}, $card->get_assignees() );

			$checklist = array_map( function ( $checklist ) {
				return [
					'id'         => $checklist->get_id(),
					'label'      => $checklist->get_label(),
					'is_checked' => $checklist->is_checked(),
					'sort_order' => $checklist->get_sort_order(),
				];
			}, $card->get_checklists() );

			$comment_data = array_map( function ( $comment ) {
				return [
					'id'        => (int) $comment->comment_ID,
					'content'   => $comment->comment_content,
					'date_time' => get_comment_date( 'Y-m-d H:i', $comment ),
					'author'    => $comment->comment_author,
					'avatar'    => get_avatar_url( $comment->user_id, [ 'size' => 96 ] ),
				];
			}, Helper::get_card_comments( $task_id ) );

			$activity_data = array_map( function ( $comment ) {
				return [
					'id'        => (int) $comment->comment_ID,
					'content'   => $comment->comment_content,
					'date_time' => get_comment_date( 'Y-m-d H:i', $comment ),
					'author'    => $comment->comment_author,
					'avatar'    => get_avatar_url( $comment->user_id, [ 'size' => 96 ] ),
				];
			}, Helper::get_card_activities( $task_id ) );

			$assigned_data[] = [
				'id'                   => $card->get_id(),
				'column_id'            => $card->get_column_id(),
				'board_id'             => $card->get_board_id(),
				'task_id'              => $card->get_task_id(),
				'title'                => $task->post_title,
				'description_rendered' => get_post_meta( $task_id, 'task_rendered', true ),
				'description'          => $task_description,
				'sort_order'           => $card->get_sort_order(),
				'due_date'             => $card->get_due_date() ? $card->get_due_date()->format( 'Y-m-d' ) : '',
				'column'               => [
					'title' => $result->column_title,
					'color' => $result->column_color,
				],
				'assignee'             => $assignee_list,
				'comments'             => $comment_data,
				'activity'             => $activity_data,
				'priority'             => $priority_data,
				'labels'               => $labels_data,
				'attachments'          => $attachments,
				'checklist'            => $checklist,
			];
		}

		wp_send_json( $assigned_data );
	}

	public function get_recently_open() {
		$user_id = get_current_user_id();

		try {
			/** @var BoardVisit[] $board_visits */
			$board_visits = ( new BoardVisitCollection([
				'per_page' => 5,
				'where'    => [
					[
						'key'   => 'user_id',
						'value' => $user_id,
					],
				],
				'order'    => 'DESC',
			]) )->get_results();
			$board_ids    = array_map(fn( $board_visit) => $board_visit->get_board_id(), $board_visits);

			if ( ! empty( $board_ids ) ) {
				$board_ids_formatter = implode( ',', array_fill( 0, count( $board_ids ), '%d' ) );

				$last_changed_boards = wp_cache_get_last_changed('gemboards_boards');
				$last_changed_roles  = wp_cache_get_last_changed('gemboards_roles');
				$cache_key           = "recently_opened_boards_{$last_changed_boards}_$last_changed_roles";
				$has_board_ids       = wp_cache_get( $cache_key, 'gemboards_boards' );

				if ( false === $has_board_ids ) {
					global $wpdb;
					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$board_ids = $wpdb->get_col( $wpdb->prepare("
				SELECT
					board.id
				FROM
					{$wpdb->prefix}gemboards_boards board
					INNER JOIN {$wpdb->prefix}gemboards_roles r ON r.user_id = %d
					AND r.object_id = board.project_id
					AND r.object_type = 'project'
				WHERE
					board.id IN ($board_ids_formatter)", get_current_user_id(), ...$board_ids) );
					// phpcs:enable
					$board_ids = array_map('intval', $board_ids);
					$board_ids = array_unique($board_ids);
					wp_cache_set( $cache_key, $board_ids, 'gemboards_boards' );
				} else {
					$board_ids = $has_board_ids;
				}
			}

			if ( empty( $board_ids ) ) {
				$boards = [];
			} else {
				$boards = new BoardCollection( [
					'per_page' => - 1,
					'where'    => [
						[
							'key'     => 'id',
							'value'   => $board_ids,
							'compare' => 'IN',
						],
					],
				] );
			}
		} catch ( GemboardsInvalidArgumentException $e ) {
			$boards = [];
			Helper::error_log( $e->getMessage() );
		}

		wp_send_json( array_map( fn( $board ) => array_merge($board->get_data(), [
			'id' => $board->get_id(),
		]), $boards->get_results() ) );
	}

	public function get_bookmarks() {
		$user_id      = get_current_user_id();
		$cache_group  = Helper::BOOKMARKS_TABLE_NAME;
		$last_changed = wp_cache_get_last_changed( $cache_group ) . ':' . wp_cache_get_last_changed( 'gemboards_projects' );
		$cache_key    = "bookmarks_for_user_$user_id:$last_changed";
		$has_cache    = wp_cache_get( $cache_key, $cache_group );
		if ( $has_cache ) {
			return rest_ensure_response( $has_cache );
		}

		global $wpdb;
		$table_name = $wpdb->prefix . Helper::BOOKMARKS_TABLE_NAME;
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is hardcoded.
		$results = $wpdb->get_results(
			$wpdb->prepare( "SELECT
					bookmark.*,
					card.board_id AS card_board_id,
					COALESCE(board.title, task.post_title) AS title,
					task.ID AS task_id
				FROM
					$table_name bookmark
					INNER JOIN {$wpdb->prefix}gemboards_projects project ON project.id = bookmark.project_id
					INNER JOIN {$wpdb->prefix}gemboards_roles role ON role.object_id = bookmark.project_id
						AND role.object_type = 'project' AND role.user_id = %d
					LEFT JOIN {$wpdb->prefix}gemboards_boards board ON board.id = bookmark.target_object_id
						AND bookmark.target_object_type = 'board'
					LEFT JOIN {$wpdb->prefix}gemboards_cards card ON card.id = bookmark.target_object_id
						AND bookmark.target_object_type = 'card'
					LEFT JOIN $wpdb->posts task ON task.ID = card.task_id AND bookmark.target_object_type = 'card'
				WHERE bookmark.user_id = %d
				ORDER BY bookmark.id DESC", $user_id, $user_id ) );
		// phpcs:enable
		$results = array_map( fn( $bookmark ) => array_merge( (array) $bookmark, [
			'id'               => absint( $bookmark->id ),
			'project_id'       => absint( $bookmark->project_id ),
			'user_id'          => absint( $bookmark->user_id ),
			'target_object_id' => absint( $bookmark->target_object_id ),
			'card_board_id'    => absint( $bookmark->card_board_id ),
			'task_id'          => absint( $bookmark->task_id ),
		]), $results);
		wp_cache_set( $cache_key, $results, $cache_group );

		return rest_ensure_response( $results );
	}

}
