<?php
namespace AcademyProGroupPlus;

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

use stdClass;

class Helper {

	protected string $group_table;
	protected string $gcr_table;
	protected string $tcr_table;
	protected string $tmr_table;
	protected string $gor_table;
	protected string $tor_table;

	public function __construct() {
		$this->gcr_table   = static::merge_prefix_at_table( 'group_courses' );
		$this->tmr_table   = static::merge_prefix_at_table( 'team_members' );
		$this->tcr_table   = static::merge_prefix_at_table( 'team_courses' );
		$this->group_table = static::merge_prefix_at_table( 'groups' );
		$this->gor_table   = static::merge_prefix_at_table( 'group_organizers' );
		$this->tor_table   = static::merge_prefix_at_table( 'team_organizers' );
	}

	public static function get_enrolled_courses_ids_by_user( array $ids, int $user_id ) : array {
		global $wpdb;
		$table = static::merge_prefix_at_table( 'team_courses' );
		$join_table = static::merge_prefix_at_table( 'team_members' );
		$query = "SELECT course_id 
					FROM {$table} tc
						INNER JOIN {$join_table} tmr
							ON tmr.team_id = tc.team_id
					where tmr.member_id = %d";
		return array_merge( $wpdb->get_col( $wpdb->prepare( $query, $user_id ) ), $ids ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
	}

	public function current_user_entity_exists( int $id, string $table ) : bool {
		global $wpdb;
		$organizer_id = get_current_user_id();
		$col = 'group_organizers' === $table ? 'group_id' : 'team_id';
		$table = static::merge_prefix_at_table( $table );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return boolval( $wpdb->get_var( $wpdb->prepare(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			"SELECT COUNT(group_id) FROM {$table} WHERE organizer_id = %d AND {$col} = %d",
			$organizer_id,
			$id
		) ) ?? 0 > 0 );
	}

	public function is_team_member_enrolled( int $course_id, int $member_id ) : ?stdClass {
		global $wpdb;
		$query = "SELECT g.id as ID, 
                         g.name as post_title, 
                         tmr.member_id as post_author, 
                         'completed' as enrolled_status, 
                         g.updated_at as post_date,
                         g.updated_at as post_date_gmt
					FROM {$this->tmr_table}  tmr
                    INNER JOIN {$this->tcr_table}  tcr 
                        ON tmr.team_id = tcr.team_id
                    INNER JOIN {$this->group_table}  g
                        ON g.id = tcr.group_id
					        WHERE tcr.course_id = %d AND  tmr.member_id = %d";

		$data = $wpdb->get_row( $wpdb->prepare( $query, $course_id, $member_id ) ) ?? false;// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared

		if ( $data ) {
			return $data;
		}

		return null;
	}

	public function total_seat_count_by_course( int $course_id ) : int {
		global $wpdb;
		$query = "SELECT SUM(total_seats) 
					FROM {$this->gcr_table}
					where course_id = %d";

		return intval( $wpdb->get_var( $wpdb->prepare( $query, $course_id ) ) ?? 0 );// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared 
	}

	protected static function merge_prefix_at_table( string $table ) : string {
		return $GLOBALS['wpdb']->prefix . ACADEMY_PLUGIN_SLUG . '_' . $table;
	}

	public static function ins(): self {
		// singleton is needed to enhance performance, because this method is need to be call several times
		static $instance = null;
		if ( is_null( $instance ) ) {
			$instance = new self();
		}
		return $instance;
	}

	public static function is_organizer( $user_id, $course_id ) {
		global $wpdb;
		$table = '';
		$inner_table = '';

		if ( current_user_can( 'academy_group_organizer' ) ) {
			$table = $wpdb->prefix . 'academy_group_organizers';
			$inner_table = $wpdb->prefix . 'academy_group_courses';
		} elseif ( current_user_can( 'academy_team_organizer' ) ) {
			$table = $wpdb->prefix . 'academy_team_organizers';
			$inner_table = $wpdb->prefix . 'academy_team_courses';
		} else {
			return '';
		}

		$results = $wpdb->get_var( $wpdb->prepare(
			"SELECT co.course_id
			FROM {$table} AS org
			INNER JOIN {$inner_table} AS co
				ON org.group_id = co.group_id
			WHERE org.organizer_id = %d AND co.course_id = %d",
			$user_id, $course_id
		));
		return $results;
	}

	public static function get_seats( $user_id, $course_id ) {
		global $wpdb;

		$table = $wpdb->prefix . 'academy_group_organizers';
		$inner_table = $wpdb->prefix . 'academy_group_courses';

		$results = $wpdb->get_var( $wpdb->prepare(
			"SELECT co.total_seats
			FROM {$table} AS org
			INNER JOIN {$inner_table} AS co
				ON org.group_id = co.group_id
			WHERE org.organizer_id = %d AND co.course_id = %d",
			$user_id, $course_id
		));

		return intval( $results );
	}

	public static function get_group_orders_by_user_id( $user_id ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$orders = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					orders.id AS ID,
					orders.customer_id AS post_author,
					orders.date_created_gmt AS post_date_gmt,
					orders.date_created_gmt AS post_date,
					orders.status AS post_status,
					orders.date_updated_gmt AS post_modified_gmt,
					orders.total_amount AS amount,
					orders.currency AS currency,
					meta.post_id AS course_id
				FROM {$wpdb->prefix}storeengine_orders AS orders
				INNER JOIN {$wpdb->prefix}postmeta AS meta
				ON orders.id = meta.meta_value
					AND meta.meta_key = 'is_academy_group_order_id'
				WHERE orders.customer_id = %d",
				$user_id
			)
		);
		return $orders;
	}
}
