<?php
namespace AcademyProGroupPlus\Db\Models;

use AcademyProGroupPlus\Db\Models\Model;
use stdClass;
use AcademyProGroupPlus\Roles\{
	GroupOrganizer,
	TeamOrganizer
};

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

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


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

	protected array $columns = [ 'name', 'created_at', 'updated_at' ];

	public function teams(
		int $id,
		array $conditions = [],
		array $args = [],
		int $per_page = 20,
		int $current_page = 1,
		?string $order_by = null,
		string $order_direction = 'DESC'
	) : array {
		$conditions[] = 'group_id = %d';
		$args[]       = $id;
		return Team::ins()->get( $conditions, $args, $per_page, $current_page, $order_by, $order_direction );
	}

	public function organizers(
		int $group_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
		// FROM {$wpdb->prefix}users u
		// INNER JOIN {$this->prefix}group_organizers ugr
		// ON u.ID = ugr.organizer_id
		// where ugr.group_id = %d";
		// return $wpdb->get_results($wpdb->prepare($query, $group_id), ARRAY_A) ?? [];

		return self::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}group_organizers ugr
					ON u.ID = ugr.organizer_id",  // join clause
			[ 'ugr.group_id = %d' ], // where condition
			[ $group_id ],   // where condition args
			'',   // after where, before order & limit query
			$per_page,
			$current_page,
			$order_by,
			$order_direction
		);
	}

	public function courses(
		int $group_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, gcr.total_seats
		// FROM {$wpdb->prefix}posts p
		// INNER JOIN {$this->prefix}group_courses gcr
		// ON p.ID = gcr.course_id
		// where gcr.group_id = %d";

		// return $wpdb->get_results($wpdb->prepare($query, $group_id), ARRAY_A) ?? [];

		return self::ins()->get_advance_ct(
			"p.ID, 
			p.post_title,
			p.post_status, 
			gcr.total_seats, 
			gcr.req_seats,
			(
				SELECT COUNT(tmr.id) 
					FROM {$this->prefix}team_members tmr
					INNER JOIN {$this->prefix}team_courses tcr
						ON tcr.group_id = tmr.group_id
						WHERE 
							tcr.group_id = gcr.group_id AND 
							tcr.course_id = p.ID
			) AS seat_used,
			(
				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}group_courses gcr
					ON p.ID = gcr.course_id",  // join clause
			[ 'gcr.group_id = %d' ], // where condition
			[ $group_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 $course_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'group_courses';
		$count_query = "SELECT COUNT(*) FROM {$table} 
			WHERE group_id = %d AND  course_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 ) ) ) > 0;
	}

	public function course_seats( int $group_id, int $course_id ) : int {
		global $wpdb;
		$table = $this->prefix . 'group_courses';
		$count_query = "SELECT total_seats FROM {$table} 
			WHERE group_id = %d AND  course_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 ) ?? 0 ) );
	}



	public function get_available_seats_by_course( int $group_id, int $course_id ) : int {
		global $wpdb;
		$table = $this->prefix . 'group_courses';
		$count_query = "SELECT total_seats FROM {$table} 
			WHERE group_id = %d AND  course_id = %d";

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

		$team_members_q = "SELECT COUNT(tmr.id) 
					FROM {$this->prefix}team_members tmr
					INNER JOIN {$this->prefix}team_courses tcr
						ON tmr.team_id = tcr.team_id
					WHERE tmr.group_id = %d AND  tcr.course_id = %d";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$used = intval( $wpdb->get_var( $wpdb->prepare( $team_members_q, $group_id, $course_id ) ) ?? 0 );

		if ( $total > $used ) {
			return $total - $used;
		}
		return 0;
	}

	public function add_course( int $group_id, int $course_id, int $total_seats, bool $req = false ) : bool {
		global $wpdb;
		$table = $this->prefix . 'group_courses';

		if (
			! $this->course_assigned( $group_id, $course_id ) &&
			( get_post_type( $course_id ) == 'academy_courses' ) // course exists
		) {
			$data = [
				'group_id'    => $group_id,
				'course_id'   => $course_id,
				'user_id'     => get_current_user_id(),
			];

			if ( $req ) {
				$data['req_seats'] = $total_seats;
			} else {
				$data['total_seats'] = $total_seats;
			}

			do_action( 'academy_pro_group_plus/api/before_add_courses_to_group', $data );
			// 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_courses_to_group', $data );

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



	public function update_course( int $group_id, int $course_id, int $total_seats, bool $req = false ) : bool {
		global $wpdb;
		$table = $this->prefix . 'group_courses';

		if (
			$this->course_assigned( $group_id, $course_id ) &&
			( get_post_type( $course_id ) == 'academy_courses' ) // course exists
		) {
			$data = [];

			if ( $req ) {
				$data['req_seats'] = $total_seats;
			} else {
				$data['total_seats'] = $total_seats;
			}

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

			return is_int( $result ) ? true : false;
		}//end if
		return $this->add_course( $group_id, $course_id, $total_seats, $req );
	}

	public function remove_course( int $group_id, int $course_id ) : bool {
		global $wpdb;

		$table = $this->prefix . 'group_courses';
		$table_team = $this->prefix . 'team_courses';

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

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

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

	public function organizer_assigned( int $group_id, int $organizer_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'group_organizers';
		$count_query = "SELECT COUNT(*) FROM {$table} 
			WHERE group_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( $count_query, $group_id, $organizer_id ) ) ) > 0;
	}

	public function add_organizer( int $group_id, int $organizer_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'group_organizers';

		$user_data = get_user_by( 'id', $organizer_id );

		if (
			! $this->organizer_assigned( $group_id, $organizer_id ) &&
			( false !== $user_data )
		) {

			$user_data->add_role( GroupOrganizer::ROLE_SLUG );

			$data = [
				'group_id'     => $group_id,
				'organizer_id' => $organizer_id,
				'user_id'      => get_current_user_id(),
			];
			do_action( 'academy_pro_group_plus/api/before_add_group_organizer', $data );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->insert(
				$table,
				$data,
				[ '%d', '%d', '%d' ]
			);
			do_action( 'academy_pro_group_plus/api/after_add_group_organizer', $data );

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



	public function remove_organizer( int $group_id, int $organizer_id ) : bool {
		global $wpdb;
		$table = $this->prefix . 'group_organizers';
		if (
			$this->organizer_assigned( $group_id, $organizer_id )
		) {
			$where = [
				'group_id'     => $group_id,
				'organizer_id' => $organizer_id,
			];

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

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

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

		return ( current_user_can( 'manage_academy_group' ) && 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 )
			) ?? false
		) ) || current_user_can( 'manage_options' );
	}

	public function validate_organizers( int $group_id, int ...$user_ids ) : array {
		global $wpdb;
		$query = "SELECT ugr.organizer_id  
					FROM
						{$this->prefix}group_organizers ugr
							WHERE ugr.group_id = %d AND 
							ugr.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 )
		) ?? [];
	}
	public static function get_user_info( $user_id ) {
		$user = get_userdata( $user_id );

		if ( ! $user ) {
			return null; // or return an empty array if you prefer []
		}

		return [
			'id'    => $user->ID,
			'email' => $user->user_email,
			'name'  => $user->display_name,
		];
	}

	public function is_group_organizer( int $user_id, int $group_id ) : bool {
		$join = 'INNER JOIN ' . static::add_prefix( 'group_organizers' ) . ' ugr
					ON g.id = ugr.group_id';
		$conditions = [];
		$args = [];

		$conditions[] = 'ugr.organizer_id = %d';
		$args[]       = $user_id;

		$conditions[] = 'ugr.group_id = %d';
		$args[]       = $group_id;
		return ! empty( static::ins()->get_advance(
			'g.*', // select rows
			'g.id', // select count for pagination
			'g', // main table alias
			$join,  // join clause
			$conditions, // where condition
			$args,   // where condition args
			'',   // after where, before order & limit query
			1
		) );
	}
}
