<?php

namespace GemBoards\API;

use GemBoards\Utils\Helper;
use stdClass;
use WP_Error;
use WP_REST_Request;
use WP_REST_Server;

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

class BoardAnalytics {

	/**
	 * 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(): void {
		$self            = new self();
		$self->namespace = 'gemboards/v1';
		$self->rest_base = 'boards';
		add_action( 'rest_api_init', [ $self, 'register_routes' ] );
	}

	public function register_routes(): void {
		register_rest_route(
			$this->namespace,
			'/' . $this->rest_base . '/(?P<id>\d+)/analytics',
			[
				'methods'             => WP_REST_Server::READABLE,
				'callback'            => [ $this, 'get_analytics' ],
				'permission_callback' => function ( WP_REST_Request $request ) {
					$board_id = (int) $request->get_param( 'id' );

					if ( ! current_user_can( 'view_projects', [ 'board_id' => $board_id ] ) ) {
						return new WP_Error(
							'rest_forbidden_context',
							esc_html__( 'Sorry, you are not allowed to access this board.', 'gemboards' ),
							[ 'status' => rest_authorization_required_code() ]
						);
					}

					return true;
				},
				'args'                => [
					'id' => [
						'type'        => 'integer',
						'required'    => true,
						'description' => __( 'Board ID', 'gemboards' ),
					],
				],
			]
		);
	}

	/**
	 * Return all analytics for a single board.
	 *
	 * @param WP_REST_Request $request
	 *
	 * @return \WP_REST_Response|WP_Error
	 */
	public function get_analytics( WP_REST_Request $request ) {
		$board_id = (int) $request->get_param( 'id' );

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

		$cache_key   = 'board_analytics_' . $board_id;
		$cache_group = 'gemboards_board_analytics';
		$has_cache   = wp_cache_get( $cache_key, $cache_group );
		if ( false !== $has_cache ) {
			return rest_ensure_response( $has_cache );
		}

		$data = [
			'board_type'      => $board->get_board_type(),
			'overview'        => $this->get_overview( $board_id ),
			'status_overview' => $this->get_status_overview( $board_id ),
			'team_workload'   => $this->get_team_workload( $board_id ),
		];

		if ( 'scrum' === $board->get_board_type() ) {
			$data['sprint_stats'] = $this->get_sprint_stats( $board_id );
		}

		$data = apply_filters( 'gemboards/board/analytics', $data, $board );

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

		return rest_ensure_response( $data );
	}

	/**
	 * Return assigned/unassigned/in-progress/completed card counts for a board.
	 *
	 * @param int $board_id
	 *
	 * @return stdClass
	 */
	private function get_overview( int $board_id ): stdClass {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Implemented caching on upper level.
		$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
					WHERE
						c.board_id = %d
						AND c.archived = 0
					GROUP BY
						c.id
				) AS t",
				$board_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;
	}

	/**
	 * Return card count per column for a board.
	 *
	 * @param int $board_id
	 *
	 * @return array Array of objects with title, slug, color, card_count.
	 */
	private function get_status_overview( int $board_id ): array {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Implemented caching on upper level.
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					col.title,
					col.slug,
					col.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 AND c.archived = 0
				WHERE
					col.board_id = %d
				GROUP BY
					col.id, col.title, col.slug, col.color",
				$board_id
			)
		);
		// phpcs:enable

		return array_map( function ( $row ) {
			$row->card_count = (int) $row->card_count;
			return $row;
		}, $results );
	}

	/**
	 * Return assignees with their work distribution for a board,
	 * including an "Unassigned" entry and a percentage for each row.
	 *
	 * @param int $board_id
	 *
	 * @return array Array of objects with user_id, name, avatar, card_count, completed_count, in_progress_count, percentage.
	 */
	private function get_team_workload( int $board_id ): array {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Implemented caching on upper level.
		$total_cards = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(id) FROM {$wpdb->prefix}gemboards_cards WHERE board_id = %d AND archived = 0",
				$board_id
			)
		);

		$assigned_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					ca.user_id,
					COUNT(DISTINCT ca.card_id) AS card_count,
					SUM(IF(c.is_completed = 1, 1, 0)) AS completed_count,
					SUM(IF(c.is_completed = 0, 1, 0)) AS in_progress_count
				FROM
					{$wpdb->prefix}gemboards_card_assignees ca
					INNER JOIN {$wpdb->prefix}gemboards_cards c ON c.id = ca.card_id
				WHERE
					c.board_id = %d
					AND c.archived = 0
				GROUP BY
					ca.user_id
				ORDER BY
					card_count DESC",
				$board_id
			)
		);

		$unassigned_count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(c.id)
				FROM {$wpdb->prefix}gemboards_cards c
				LEFT JOIN {$wpdb->prefix}gemboards_card_assignees ca ON ca.card_id = c.id
				WHERE c.board_id = %d AND c.archived = 0 AND ca.card_id IS NULL",
				$board_id
			)
		);
		// phpcs:enable

		$items = [];

		if ( $unassigned_count > 0 ) {
			$items[] = [
				'user_id'           => 0,
				'name'              => __( 'Unassigned', 'gemboards' ),
				'avatar'            => '',
				'card_count'        => $unassigned_count,
				'completed_count'   => 0,
				'in_progress_count' => $unassigned_count,
				'percentage'        => $total_cards > 0 ? round( ( $unassigned_count / $total_cards ) * 100, 1 ) : 0,
			];
		}

		foreach ( $assigned_rows as $row ) {
			$card_count = (int) $row->card_count;
			$items[]    = [
				'user_id'           => (int) $row->user_id,
				'name'              => get_the_author_meta( 'display_name', $row->user_id ),
				'avatar'            => get_avatar_url( $row->user_id, [ 'size' => 96 ] ),
				'card_count'        => $card_count,
				'completed_count'   => (int) $row->completed_count,
				'in_progress_count' => (int) $row->in_progress_count,
				'percentage'        => $total_cards > 0 ? round( ( $card_count / $total_cards ) * 100, 1 ) : 0,
			];
		}

		return $items;
	}

	/**
	 * Return sprint story point stats for a scrum board.
	 * Only populated when GemBoards Pro is active.
	 *
	 * @param int $board_id
	 *
	 * @return array
	 */
	private function get_sprint_stats( int $board_id ): array {
		if ( ! class_exists( 'GemBoardsPro\Collections\SprintCollection' ) ) {
			return [];
		}

		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Implemented caching on upper level.
		$last_7_days = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					SUM(card.story_point) AS total,
					SUM(
						IF(sprint.is_active = 1 AND card.is_completed = 0, card.story_point, 0)
					) AS started,
					SUM(
						IF(card.is_completed = 1, card.story_point, 0)
					) AS completed
				FROM
					{$wpdb->prefix}gemboards_pro_sprints sprint
					INNER JOIN {$wpdb->prefix}gemboards_cards card ON card.sprint_id = sprint.id
				WHERE
					sprint.board_id = %d
					AND sprint.created_at >= NOW() - INTERVAL 7 DAY",
				$board_id
			)
		);

		$totals = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					SUM(card.story_point) AS total,
					SUM(
						IF(sprint.is_active = 1 AND card.is_completed = 0, card.story_point, 0)
					) AS started,
					SUM(
						IF(card.is_completed = 1, card.story_point, 0)
					) AS completed
				FROM
					{$wpdb->prefix}gemboards_pro_sprints sprint
					INNER JOIN {$wpdb->prefix}gemboards_cards card ON card.sprint_id = sprint.id
				WHERE
					sprint.board_id = %d",
				$board_id
			)
		);
		// phpcs:enable

		return [
			'last_seven_days' => array_map( 'absint', (array) $last_7_days ),
			'totals'          => array_map( 'absint', (array) $totals ),
		];
	}
}
