<?php
namespace AcademyProAdvancedAnalytics;

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

class Helper {
	public static function get_total_number_of_quizzes() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_var(
			$wpdb->prepare("SELECT COUNT(ID) 
            FROM {$wpdb->posts} 
            WHERE post_type = %s 
            AND post_status = %s", 'academy_quiz', 'publish')
		);
		return (int) $results;
	}

	public static function get_total_number_of_completed_course() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(comment_ID)
			FROM	{$wpdb->comments} 
			WHERE 	comment_agent = %s 
					AND comment_type = %s 
			",
				'academy',
				'course_completed'
			)
		);
		return (int) $results;
	}

	public static function get_earnings_analytics() {
		global $wpdb;
		$from    = gmdate( 'Y-m-d', strtotime( ' - 30 days' ) );
		$to      = gmdate( 'Y-m-d', strtotime( ' + 1 days' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$earnings = $wpdb->get_results(
			$wpdb->prepare("SELECT  SUM(admin_amount) AS total,
			DATE(created_at) AS date_format
			FROM	{$wpdb->prefix}academy_earnings
			WHERE 	order_status = %s
			AND (created_at BETWEEN %s AND %s )
			GROUP BY date_format
			ORDER BY created_at ASC;", 'completed', $from, $to)
		);
		$analytics = new \Academy\Classes\Analytics();
		$earnings = $analytics->format_query_results_to_chart_data( $from, $to, $earnings );
		return $earnings;
	}

	public static function get_refunds_analytics() {
		global $wpdb;
		$from    = gmdate( 'Y-m-d', strtotime( ' - 30 days' ) );
		$to      = gmdate( 'Y-m-d', strtotime( ' + 1 days' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$refunds = $wpdb->get_results(
			$wpdb->prepare("SELECT  SUM(wc_order_details.total_sales) AS total,
			DATE(wc_order_details.date_created) AS date_format
			FROM {$wpdb->posts} AS post
					INNER JOIN {$wpdb->postmeta} as postmeta ON postmeta.post_id = post.ID
					INNER JOIN {$wpdb->prefix}wc_order_product_lookup AS wc_order ON wc_order.product_id = postmeta.meta_value
					INNER JOIN {$wpdb->prefix}wc_order_stats AS wc_order_details ON wc_order_details.order_id = wc_order.order_id
			WHERE postmeta.meta_key = %s
			AND post.post_type = %s
			AND post.post_status = %s
			AND wc_order_details.status = %s
			AND (wc_order_details.date_created BETWEEN %s AND %s )
			GROUP BY date_format
			ORDER BY wc_order_details.date_created ASC;", 'academy_course_product_id', 'academy_courses', 'publish', 'wc-refunded', $from, $to)
		);
		$analytics = new \Academy\Classes\Analytics();
		$refunds = $analytics->format_query_results_to_chart_data( $from, $to, $refunds );
		return $refunds;
	}

	public static function get_discounts_analytics() {
		global $wpdb;
		$from    = gmdate( 'Y-m-d', strtotime( ' - 30 days' ) );
		$to      = gmdate( 'Y-m-d', strtotime( ' + 1 days' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$discounts = $wpdb->get_results(
			$wpdb->prepare("SELECT  SUM(wc_order.coupon_amount) AS total,
			DATE(wc_order_details.date_created) AS date_format
			FROM {$wpdb->posts} AS post
					INNER JOIN {$wpdb->postmeta} as postmeta ON postmeta.post_id = post.ID
					INNER JOIN {$wpdb->prefix}wc_order_product_lookup AS wc_order ON wc_order.product_id = postmeta.meta_value
					INNER JOIN {$wpdb->prefix}wc_order_stats AS wc_order_details ON wc_order_details.order_id = wc_order.order_id
			WHERE postmeta.meta_key = %s
			AND post.post_type = %s
			AND post.post_status = %s
			AND wc_order_details.status = %s
			AND (wc_order_details.date_created BETWEEN %s AND %s )
			GROUP BY date_format
			ORDER BY wc_order_details.date_created ASC;", 'academy_course_product_id', 'academy_courses', 'publish', 'wc-completed', $from, $to)
		);
		$analytics = new \Academy\Classes\Analytics();
		$discounts = $analytics->format_query_results_to_chart_data( $from, $to, $discounts );
		return $discounts;
	}

	public static function get_popular_courses() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare("SELECT COUNT(enrolled.ID) AS enrolled_count,
				enrolled.post_parent as parent_course_id,
				course.ID,
				course.post_title,
				course.post_author,
				course.post_date,
				course.post_date_gmt
            FROM {$wpdb->posts} enrolled
				INNER JOIN {$wpdb->posts} course ON enrolled.post_parent = course.ID
            WHERE enrolled.post_type =%s
				AND enrolled.post_status =%s
				AND course.post_type =%s
            GROUP BY parent_course_id ORDER BY enrolled_count DESC LIMIT 0, %d;", 'academy_enrolled', 'completed', 'academy_courses', 10)
		);
		return $results;
	}

	public static function get_recent_enrolled_courses() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare("SELECT MAX(enrolled.post_date) AS enrolled_date,
				enrolled.post_parent as parent_course_id,
				course.ID,
				course.post_title,
				course.post_author,
				course.post_date,
				course.post_date_gmt
            FROM {$wpdb->posts} enrolled
				INNER JOIN {$wpdb->posts} course ON enrolled.post_parent = course.ID
            WHERE enrolled.post_type =%s
				AND enrolled.post_status =%s
				AND course.post_type =%s
            GROUP BY parent_course_id ORDER BY enrolled_date DESC LIMIT 0, %d;", 'academy_enrolled', 'completed', 'academy_courses', 10)
		);
		return $results;
	}

	public static function get_recent_reviews() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$reviews = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT {$wpdb->comments}.comment_ID, 
					{$wpdb->comments}.comment_post_ID, 
					{$wpdb->comments}.comment_author, 
					{$wpdb->comments}.comment_author_email, 
					{$wpdb->comments}.comment_date, 
					{$wpdb->comments}.comment_content, 
					{$wpdb->comments}.user_id, 
					{$wpdb->commentmeta}.meta_value AS rating,
					{$wpdb->users}.display_name 
			
			FROM 	{$wpdb->comments}
					INNER JOIN {$wpdb->commentmeta} 
					ON {$wpdb->comments}.comment_ID = {$wpdb->commentmeta}.comment_id 
					LEFT JOIN {$wpdb->users}
					ON {$wpdb->comments}.user_id = {$wpdb->users}.ID
			WHERE comment_type = 'academy_courses' AND meta_key = 'academy_rating'
			ORDER BY comment_ID DESC
			LIMIT 	%d, %d;
			",
				0,
				10
			)
		);
		return $reviews;
	}

	public static function get_recent_registered_students() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ID, display_name, user_nicename, user_email, user_registered
			FROM 	{$wpdb->users} 
					INNER JOIN {$wpdb->usermeta} 
							ON ( {$wpdb->users}.ID = {$wpdb->usermeta}.user_id )
			WHERE 	{$wpdb->usermeta}.meta_key = %s ORDER BY ID DESC 
			LIMIT 0, %d;",
				'is_academy_student',
				10
			)
		);
		return $results;
	}

	public static function get_recent_registered_instructors() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT ID, display_name, user_nicename, user_email, user_registered
			FROM 	{$wpdb->users} 
					INNER JOIN {$wpdb->usermeta} 
							ON ( {$wpdb->users}.ID = {$wpdb->usermeta}.user_id )
			WHERE 	{$wpdb->usermeta}.meta_key = %s ORDER BY ID DESC 
			LIMIT 0, %d;",
				'is_academy_instructor',
				10
			)
		);
		return $results;
	}

	public static function get_total_courses() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$query = $wpdb->prepare(
			"SELECT *
			FROM {$wpdb->posts}
			WHERE post_type = %s
			AND post_status = %s",
			'academy_courses',
			'publish'
		);

		// phpcs:ignore
		return $wpdb->get_results( $query );
	}

	public static function get_user_ip() {
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash
		if ( isset( $_SERVER['HTTP_CLIENT_IP'] ) && filter_var( $_SERVER['HTTP_CLIENT_IP'], FILTER_VALIDATE_IP ) ) {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
			$ip = $_SERVER['HTTP_CLIENT_IP'];
		} elseif ( isset( $_SERVER['HTTP_X_FORWARDED_FOR'] ) ) {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
			$ipList = explode( ',', $_SERVER['HTTP_X_FORWARDED_FOR'] );
			foreach ( $ipList as $ip ) {
				// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
				$ip = trim( $ip );
				if ( filter_var( $ip, FILTER_VALIDATE_IP ) !== false ) {
					return $ip;
				}
			}
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash
		} elseif ( isset( $_SERVER['HTTP_X_FORWARDED'] ) && filter_var( $_SERVER['HTTP_X_FORWARDED'], FILTER_VALIDATE_IP ) ) {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
			$ip = $_SERVER['HTTP_X_FORWARDED'];
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash
		} elseif ( isset( $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'] ) && filter_var( $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'], FILTER_VALIDATE_IP ) ) {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
			$ip = $_SERVER['HTTP_X_CLUSTER_CLIENT_IP'];
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash
		} elseif ( isset( $_SERVER['HTTP_FORWARDED_FOR'] ) && filter_var( $_SERVER['HTTP_FORWARDED_FOR'], FILTER_VALIDATE_IP ) ) {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
			$ip = $_SERVER['HTTP_FORWARDED_FOR'];
		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash
		} elseif ( isset( $_SERVER['HTTP_FORWARDED'] ) && filter_var( $_SERVER['HTTP_FORWARDED'], FILTER_VALIDATE_IP ) ) {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
			$ip = $_SERVER['HTTP_FORWARDED'];
		} else {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized, WordPress.Security.ValidatedSanitizedInput.InputNotValidated
			$ip = $_SERVER['REMOTE_ADDR'];
		}//end if
		if ( '::1' === $ip ) {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
			$ip = 'localhost';
		}
		return $ip;
	}

	public static function get_browser_name() {

		if ( isset( $_SERVER['HTTP_USER_AGENT_CLIENT_HINT'] ) ) {
			// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
			$userAgentHints = json_decode( $_SERVER['HTTP_USER_AGENT_CLIENT_HINT'], true );
			if ( isset( $userAgentHints['brand'] ) ) {
				return $userAgentHints['brand'];
			}
		}

		// phpcs:ignore WordPress.Security.ValidatedSanitizedInput.MissingUnslash, WordPress.Security.ValidatedSanitizedInput.InputNotSanitized 
		$userAgent = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';

		$browser_name = 'Unknown';

		if ( strpos( $userAgent, 'MSIE' ) !== false || strpos( $userAgent, 'Trident' ) !== false ) {
			$browser_name = 'Internet Explorer';
		} elseif ( strpos( $userAgent, 'Edg' ) !== false ) {
			$browser_name = 'Edge';
		} elseif ( strpos( $userAgent, 'Chrome' ) !== false && strpos( $userAgent, 'Edg' ) === false ) {
			$browser_name = 'Google Chrome';
		} elseif ( strpos( $userAgent, 'Firefox' ) !== false ) {
			$browser_name = 'Mozilla Firefox';
		} elseif ( strpos( $userAgent, 'Safari' ) !== false && strpos( $userAgent, 'Chrome' ) === false ) {
			$browser_name = 'Apple Safari';
		} elseif ( strpos( $userAgent, 'Opera' ) !== false || strpos( $userAgent, 'OPR' ) !== false ) {
			$browser_name = 'Opera';
		}

		return $browser_name;
	}

	public static function get_platform() {
		$u_agent = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';

		$is_win     = is_numeric( strpos( $u_agent, 'Windows' ) );
		$is_android = is_numeric( strpos( $u_agent, 'Android' ) );
		$is_iphone  = is_numeric( strpos( $u_agent, 'Iphone' ) );
		$is_ipad    = is_numeric( strpos( $u_agent, 'Ipad' ) );
		$is_mac     = is_numeric( strpos( $u_agent, 'Mac' ) );
		$is_ios     = $is_iphone || $is_ipad;

		if ( $is_ios ) {
			$platform = 'iOS';
		} elseif ( $is_android ) {
			$platform = 'Android';
		} elseif ( $is_win ) {
			$platform = 'Windows';
		} elseif ( $is_mac ) {
			$platform = 'Mac';
		}

		return $platform;
	}

	public static function get_location( $user_ip = null ) {
		$user_ip = sanitize_text_field( wp_unslash( $user_ip ) );
		$remote_address = isset( $_SERVER['REMOTE_ADDR'] ) ? sanitize_text_field( wp_unslash( $_SERVER['REMOTE_ADDR'] ) ) : '';
		$user_ip = $user_ip ?? ( $remote_address );

		if ( ! filter_var( $user_ip, FILTER_VALIDATE_IP ) ) {
			return false;
		}

		$response = wp_remote_get( "http://ip-api.com/json/{$user_ip}" );

		if ( is_wp_error( $response ) ) {
			return false;
		}

		$data = json_decode( wp_remote_retrieve_body( $response ), true );

		return ( isset( $data['country'] ) && isset( $data['city'] ) && 'success' === $data['status'] ) ? [
			'country' => $data['country'],
			'city'    => $data['city'],
		] : false;
	}

	public static function get_device_type() {
		$u_agent = isset( $_SERVER['HTTP_USER_AGENT'] ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : '';

		$device = 'Laptop';

		$is_mob = is_numeric( strpos( $u_agent, 'Mobile' ) );
		$is_tab = is_numeric( strpos( $u_agent, 'Tablet' ) );

		if ( $is_mob ) {
			$device = 'Mobile';
		} elseif ( $is_tab ) {
			$device = 'Tablet';
		}

		return $device;
	}

	public static function insert_analytics_data_into_table( $data ) {
		global $wpdb;
		$prefix = $wpdb->prefix;
		$table_name = $prefix . ACADEMY_PLUGIN_SLUG . '_course_view_analytics';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->insert(
			$table_name,
			$data,
			array(
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
			)
		);
	}


	public static function get_total_views_by_course_id( $course_id ) {
		global $wpdb;
		$course_id = absint( $course_id );
		if ( ! $course_id ) {
			return 0;
		}
		$table_name = $wpdb->prefix . ACADEMY_PLUGIN_SLUG . '_course_view_analytics';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$query       = $wpdb->prepare(
			"SELECT COUNT(ID) FROM {$table_name} WHERE course_id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$course_id
		);
		$total_views = $wpdb->get_var( $query );// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return absint( $total_views );
	}

	public static function get_course_view_details( $course_id ) {
		global $wpdb;
		$course_id = absint( $course_id );

		if ( ! $course_id ) {
			return 0;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total_views = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}academy_course_view_analytics WHERE course_id = %d",
				$course_id
			)
		);

		return $total_views ?? null;
	}

}
