<?php
namespace AcademyProGroupPlus\Db\Models;

use AcademyProGroupPlus\Db\Models\Model;
use stdClass;
use AcademyProGroupPlus\Roles\{
	TeamMember,
	TeamOrganizer
};
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * @class Group
 */
final class Team extends Model {


	/** @var string $table */
	protected string $table = 'teams';

	public function organizers(
		int $group_id,
		int $team_id,

		int $per_page = 20,
		int $current_page = 1,
		?string $order_by = null,
		string $order_direction = 'DESC'
	) : array {
		global $wpdb;
		// $query = "SELECT u.ID, u.user_login, u.user_email
		// FROM {$wpdb->prefix}users u
		// INNER JOIN {$this->prefix}team_organizers tor
		// ON u.ID = tor.organizer_id
		// where tor.group_id = %d AND tor.team_id = %d";
		// return $wpdb->get_results($wpdb->prepare($query, $group_id, $team_id), ARRAY_A) ?? [];

		return Group::ins()->get_advance_ct(
			'u.ID, u.user_login, u.user_email ', // select rows
			'u.ID', // select count for pagination
			"{$wpdb->users} as u", // main table alias
			"INNER JOIN {$this->prefix}team_organizers tor
					ON u.ID = tor.organizer_id",  // join clause
			[
				'tor.group_id = %d',
				'tor.team_id = %d',
			], // where condition
			[
				$group_id,
				$team_id
			],   // where condition args
			'',   // after where, before order & limit query
			$per_page,
			$current_page,
			$order_by,
			$order_direction
		);
	}
	public function organizer_assigned( int $group_id, int $team_id, int $organizer_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_organizers';
		$count_query = "SELECT COUNT(*) FROM {$table} 
			WHERE group_id = %d AND team_id = %d AND  organizer_id = %d";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
		return intval($wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$count_query,
				$group_id,
				$team_id,
				$organizer_id
			)
		)) > 0;
	}
	public function add_organizer( int $group_id, int $team_id, int $organizer_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_organizers';

		$user_data = get_user_by( 'id', $organizer_id );
		if (
			! $this->organizer_assigned( $group_id, $team_id, $organizer_id ) &&
			( false !== $user_data )
		) {
			$user_data->add_role( TeamOrganizer::ROLE_SLUG );
			$data = [
				'group_id'     => $group_id,
				'team_id'      => $team_id,
				'organizer_id' => $organizer_id,
				'user_id'      => get_current_user_id(),
			];

			do_action( 'academy_pro_group_plus/api/before_add_organizer_to_team', $group_id, $team_id, $organizer_id );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->insert(
				$table,
				$data,
				[ '%d', '%d', '%d', '%d' ]
			);

			do_action( 'academy_pro_group_plus/api/after_add_organizer_to_team', $group_id, $team_id, $organizer_id );

			return false === $wpdb->insert_id ? false : true;
		}//end if
		return false;
	}
	public function remove_organizer( int $group_id, int $team_id, int $organizer_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_organizers';

		if (
			$this->organizer_assigned( $group_id, $team_id, $organizer_id )
		) {
			$where = [
				'group_id'     => $group_id,
				'team_id'      => $team_id,
				'organizer_id' => $organizer_id,
			];

			do_action( 'academy_pro_group_plus/api/before_remove_organizer_from_team', $group_id, $team_id, $organizer_id );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$result = $wpdb->delete(
				$table,
				$where,
				[ '%d', '%d', '%d' ]
			);

			do_action( 'academy_pro_group_plus/api/after_remove_organizer_from_team', $group_id, $team_id, $organizer_id );

			return is_int( $result ) ? true : false;
		}
		return false;
	}




	public function members(
		int $group_id,
		int $team_id,

		int $per_page = 20,
		int $current_page = 1,
		?string $order_by = null,
		string $order_direction = 'DESC',
		?string $search = null
	) : array {
		global $wpdb;
		$where = [
			'tmr.group_id = %d',
			'tmr.team_id = %d',
		];

		$args = [
			$group_id,
			$team_id
		];

		if ( ! empty( $search ) ) {
			$where[] = 'u.user_email LIKE %s';
			$args[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		return Group::ins()->get_advance_ct(
			'u.ID, u.user_login, u.user_email', // select rows
			'u.ID', // select count for pagination
			"{$wpdb->users} as u", // main table alias
			"INNER JOIN {$this->prefix}team_members tmr
					ON u.ID = tmr.member_id",  // join clause
			$where, // where condition
			$args,   // where condition args
			'',   // after where, before order & limit query
			$per_page,
			$current_page,
			$order_by,
			$order_direction
		);
	}

	public function members_count( int $group_id, int $team_id ) : int {
		global $wpdb;
		$query = "SELECT COUNT(tmr.id) 
					FROM {$this->prefix}team_members tmr
					where tmr.group_id = %d AND tmr.team_id = %d";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
		return intval( $wpdb->get_var( $wpdb->prepare( $query, $group_id, $team_id ) ) ?? 0 );
	}

	public function member_assigned( int $group_id, int $team_id, int $member_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_members';
		$count_query = "SELECT COUNT(*) FROM {$table} 
			WHERE group_id = %d AND team_id = %d AND  member_id = %d";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
		return intval($wpdb->get_var(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$count_query,
				$group_id,
				$team_id,
				$member_id
			)
		)) > 0;
	}

	public function is_seats_available( int $group_id, int $assigned = 0 ) : void {
		global $wpdb;
		$table = $this->prefix . 'team_members';
		array_map(
			function( ?array $res ) use ( $assigned ): array {
				if ( absint( $res['used'] ?? 0 ) >= absint( $res['total'] ?? 0 ) ) {
					wp_send_json_error( [
						'message' => sprintf(
							__( 'Seat is not available. %1$s assigned.', 'academy-pro' ),
							0 === $assigned ? 'No member ' : $assigned
						)
					], 422 );
				}
				return $res;
			},
			$wpdb->get_results(
				$wpdb->prepare(
					"SELECT 
						tc.group_id,
						tc.course_id,
						COUNT(tm.member_id) AS used,
    					( SELECT total_seats FROM {$this->prefix}group_courses WHERE course_id = tc.course_id ) as total
					FROM 
						{$this->prefix}team_courses tc
					LEFT JOIN 
						{$this->prefix}team_members tm ON tc.group_id = tm.group_id AND tc.team_id = tm.team_id
					WHERE 
						tc.group_id = 3
					GROUP BY 
						tc.course_id, tc.group_id;",
					$group_id
				),
				ARRAY_A
			) ?? []
		);
	}

	public function add_member( int $group_id, int $team_id, int $member_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_members';

		$user_data = get_user_by( 'id', $member_id );
		if (
			! $this->member_assigned( $group_id, $team_id, $member_id ) &&
			( false !== $user_data )
		) {
			$user_data->add_role( TeamMember::ROLE_SLUG );

			$data = [
				'group_id'     => $group_id,
				'team_id'      => $team_id,
				'member_id'    => $member_id,
				'user_id'      => get_current_user_id(),
			];

			do_action( 'academy_pro_group_plus/api/before_add_member_to_team', $group_id, $team_id, $member_id );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			$wpdb->insert(
				$table,
				$data,
				[ '%d', '%d', '%d', '%d' ]
			);

			do_action( 'academy_pro_group_plus/api/after_add_member_to_team', $group_id, $team_id, $member_id );
			return false === $wpdb->insert_id ? false : true;
		}//end if
		return false;
	}

	public function remove_member( int $group_id, int $team_id, int $member_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_members';

		if (
			$this->member_assigned( $group_id, $team_id, $member_id )
		) {
			$where = [
				'group_id'     => $group_id,
				'team_id'      => $team_id,
				'member_id'    => $member_id,
			];
			do_action( 'academy_pro_group_plus/api/before_remove_member_from_team', $group_id, $team_id, $member_id );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$result = $wpdb->delete(
				$table,
				$where,
				[ '%d', '%d', '%d' ]
			);
			do_action( 'academy_pro_group_plus/api/after_remove_member_from_team', $group_id, $team_id, $member_id );

			return is_int( $result ) ? true : false;
		}
		return false;
	}


	public function courses(
		int $group_id,
		int $team_id,

		int $per_page = 20,
		int $current_page = 1,
		?string $order_by = null,
		string $order_direction = 'DESC'
	) : array {
		global $wpdb;
		// $query = "SELECT p.ID, p.post_title
		// FROM {$wpdb->prefix}posts p
		// INNER JOIN {$this->prefix}team_courses tcr
		// ON p.ID = tcr.course_id
		// where tcr.group_id = %d AND tcr.team_id = %d";
		// return $wpdb->get_results($wpdb->prepare($query, $group_id, $team_id), ARRAY_A) ?? [];

		return Group::ins()->get_advance_ct(
			"p.ID, 
			p.post_title, 
			(
				SELECT meta_value 
					FROM {$wpdb->postmeta} 
						WHERE 
							post_id = p.ID AND meta_key = 'academy_course_type'
			) AS type", // select rows
			'p.ID', // select count for pagination
			"{$wpdb->posts} as p", // main table alias
			"INNER JOIN {$this->prefix}team_courses tcr
					ON p.ID = tcr.course_id",  // join clause
			[
				'tcr.group_id = %d',
				'tcr.team_id = %d',
			], // where condition
			[
				$group_id,
				$team_id
			],   // where condition args
			'',   // after where, before order & limit query
			$per_page,
			$current_page,
			$order_by,
			$order_direction
		);
	}

	public function course_assigned( int $group_id, int $team_id, int $course_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_courses';
		$count_query = "SELECT COUNT(*) FROM {$table} 
			WHERE group_id = %d AND  course_id = %d AND  team_id = %d";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
		return intval( $wpdb->get_var( $wpdb->prepare( $count_query, $group_id, $course_id, $team_id ) ) ) > 0;
	}
	public function add_course( int $group_id, int $team_id, int $course_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_courses';

		if (
			! $this->course_assigned( $group_id, $team_id, $course_id ) &&
			( get_post_type( $course_id ) == 'academy_courses' ) &&
			Group::ins()->course_assigned( $group_id, $course_id )
		) {
			$data = [
				'group_id'    => $group_id,
				'course_id'   => $course_id,
				'team_id'     => $team_id,
				'user_id'     => get_current_user_id(),
			];

			do_action( 'academy_pro_group_plus/api/before_add_course_to_team', $group_id, $team_id, $course_id );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
			$wpdb->insert(
				$table,
				$data,
				[ '%d', '%d', '%d', '%d' ]
			);

			do_action( 'academy_pro_group_plus/api/after_add_course_to_team', $group_id, $team_id, $course_id );

			return false === $wpdb->insert_id ? false : true;
		}//end if
		return false;
	}

	public function remove_course( int $group_id, int $team_id, int $course_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'team_courses';

		if (
			$this->course_assigned( $group_id, $team_id, $course_id )
		) {
			$where = [
				'group_id'  => $group_id,
				'course_id' => $course_id,
				'team_id'   => $team_id,
			];

			do_action( 'academy_pro_group_plus/api/before_remove_course_from_team', $group_id, $team_id, $course_id );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$result = $wpdb->delete(
				$table,
				$where,
				[ '%d', '%d', '%d' ]
			);

			do_action( 'academy_pro_group_plus/api/after_remove_course_from_team', $group_id, $team_id, $course_id );

			return is_int( $result ) ? true : false;
		}
		return false;
	}


	public function validate_organizers( int $group_id, ?int $team_id, int ...$user_ids ) : array {
		global $wpdb;
		if ( is_null( $team_id ) ) {
			$query = "SELECT utr.organizer_id  
						FROM
							{$this->prefix}team_organizers utr
								WHERE 
									utr.group_id = %d AND 
									utr.organizer_id IN (" . implode( ', ', array_fill( 0, count( $user_ids ), '%d' ) ) . ')';
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
			return $wpdb->get_col(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$wpdb->prepare( $query, $group_id, ...$user_ids )
			) ?? [];
		}

		$query = "SELECT utr.organizer_id  
		FROM
			{$this->prefix}team_organizers utr
				WHERE 
					utr.group_id = %d AND 
					utr.team_id  = %d AND 
					utr.organizer_id IN (" . implode( ', ', array_fill( 0, count( $user_ids ), '%d' ) ) . ')';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
		return $wpdb->get_col(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
			$wpdb->prepare( $query, $group_id, $team_id, ...$user_ids )
		) ?? [];
	}
	public function validate_members( int $group_id, ?int $team_id, int ...$user_ids ) : array {
		global $wpdb;

		if ( is_null( $team_id ) ) {
			$query = "SELECT utr.member_id  
			FROM
				{$this->prefix}team_members utr
					WHERE 
						utr.group_id = %d AND 
						utr.member_id IN (" . implode( ', ', array_fill( 0, count( $user_ids ), '%d' ) ) . ')';

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
			return $wpdb->get_col(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$wpdb->prepare( $query, $group_id, ...$user_ids )
			) ?? [];
		}

		$query = "SELECT utr.member_id  
					FROM
						{$this->prefix}team_members utr
							WHERE 
								utr.group_id = %d AND 
								utr.team_id  = %d AND 
								utr.member_id IN (" . implode( ', ', array_fill( 0, count( $user_ids ), '%d' ) ) . ')';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
		return $wpdb->get_col(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
			$wpdb->prepare( $query, $group_id, $team_id, ...$user_ids )
		) ?? [];
	}


	public function selectable_organizers(
		int $group_id,
		?string $email = null,
		int $current_page = 1,
		int $per_page = 20
	) : array {
		global $wpdb;
		$query1 = "SELECT u.ID, u.user_login, u.user_email as email
					FROM {$wpdb->prefix}users u
					INNER JOIN {$this->prefix}team_organizers ugr
						ON u.ID = ugr.organizer_id 
					where ugr.group_id = %d";
		$query2 = "SELECT u.ID, u.user_login, u.user_email as email 
					FROM {$wpdb->prefix}users u
					INNER JOIN {$this->prefix}team_members tmr
						ON u.ID = tmr.member_id
					where tmr.group_id = %d";

		$union = implode(
			' UNION ',
			[
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$wpdb->prepare( $query1, $group_id ),
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$wpdb->prepare( $query2, $group_id )
			]
		);
		$query3 = 'SELECT * FROM (' . $union . ') AS users ';
		$count  = 'SELECT COUNT(*) FROM (' . $union . ') AS users ';

		$args = [];
		if ( ! empty( $email ) ) {
			$query3 .= ' WHERE users.email LIKE %s';
			$count  .= ' WHERE users.email LIKE %s';
			$args[] = "%{$email}%";

		}

		$total = intval(
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
			$wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				( count( $args ) > 0 ? $wpdb->prepare( $count, ...$args ) : $count )
			) ?? 0
		);

		$current_page = absint( $current_page );
		$per_page = absint( $per_page );
		if ( $current_page < 1 || $current_page > $total ) {
			$current_page = 1;
		}
		if ( $per_page < 1 ) {
			$per_page = 20;
		}
		$offset = ( $current_page - 1 ) * $per_page;

		$query3 .= ' LIMIT %d OFFSET %d';
		$args[] = $per_page;
		$args[] = $offset;

		return [
			'current_page' => $current_page,
			'total_pages'  => ( $per_page > 0 && $total > 0 ) ? ceil( $total / $per_page ) : 1,
			'per_page'     => $per_page,
			'total'        => $total,
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
			'data'         => $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				( count( $args ) > 0 ? $wpdb->prepare( $query3, ...$args ) : $query3 ),
				ARRAY_A
			) ?? []
		];

	}


	public function is_organizer( int $group_id, int $team_id ) : bool {
		global $wpdb;
		$query = "SELECT COUNT(id)  
					FROM
						{$this->prefix}team_organizers
							WHERE organizer_id = %d AND group_id = %d AND team_id = %d";

		return ( current_user_can( 'manage_academy_team' ) && boolval(
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
			$wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$wpdb->prepare( $query, get_current_user_id(), $group_id, $team_id )
			) ?? false
		) ) || current_user_can( 'manage_options' );
	}

	public function is_member( int $group_id, int $team_id ) : bool {
		global $wpdb;
		$query = "SELECT COUNT(id)  
					FROM
						{$this->prefix}team_members
							WHERE member_id = %d AND group_id = %d AND team_id = %d";

		return ( current_user_can( 'academy_team_member' ) && boolval(
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
			$wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared 
				$wpdb->prepare( $query, get_current_user_id(), $group_id, $team_id )
			) ?? false
		) ) || current_user_can( 'manage_options' );
	}
}
