<?php
/**
 * WP Courseware DB Modules.
 *
 * @package WPCW
 * @subpackage Database
 * @since 4.1.0
 */
namespace WPCW\Database;

// Exit if accessed directly
defined( 'ABSPATH' ) || exit;

/**
 * Class DB_Students.
 *
 * @since 4.1.0
 */
class DB_Students extends DB {

	/**
	 * @var string Users Table Name.
	 * @since 4.1.0
	 */
	protected $users_table_name;

	/**
	 * Students Database Constructor.
	 *
	 * Intiate the table name, version, and primary key.
	 *
	 * @since 4.1.0
	 */
	public function __construct() {
		global $wpdb;

		// Define Table Name and Primary Key
		$this->table_name  = wpcw()->database->get_table_name( 'user_courses' );
		$this->primary_key = 'user_id';

		// Attach the users table to join
		$this->users_table_name = $wpdb->users;
	}

	/**
	 * Get Students Database Columns.
	 *
	 * @since 4.1.0
	 *
	 * @return array
	 */
	public function get_columns() {
		return array(
			'user_id'                 => '%d',
			'course_id'               => '%d',
			'course_progress'         => '%d',
			'course_final_grade_sent' => '%s',
			'course_enrolment_date'   => '%s',
		);
	}

	/**
	 * Get User Columns.
	 *
	 * @since 4.1.0
	 *
	 * @return array
	 */
	public function get_user_columns() {
		return array(
			'ID'                  => '%d',
			'user_login'          => '%s',
			'user_pass'           => '%s',
			'user_nicename'       => '%s',
			'user_email'          => '%s',
			'user_url'            => '%s',
			'user_registered'     => '%s',
			'user_activation_key' => '%s',
			'user_status'         => '%d',
			'display_name'        => '%s',
		);
	}

	/**
	 * Get Student.
	 *
	 * @since 4.1.0
	 *
	 * @param int $id The user id.
	 *
	 * @return array The student data.
	 */
	public function get( $id ) {
		if ( 0 === absint( $id ) ) {
			return false;
		}

		$students = $this->get_students( array( 'number' => 1, 'user_id' => $id ) );

		if ( empty( $students ) ) {
			return false;
		}

		return current( $students );
	}

	/**
	 * Get Students.
	 *
	 * @since 4.1.0
	 *
	 * @param array $args An array of query arguments.
	 * @param bool $count Optional. Return only the total number of results.
	 * @param bool $join_users Default is true. Join the users table.
	 *
	 * @return array Array of students.
	 */
	public function get_students( $args = array(), $count = false ) {
		global $wpdb;

		$defaults = array(
			'number'                => 20,
			'offset'                => 0,
			'user_id'               => 0,
			'course_id'             => 0,
			'course_enrolment_date' => '',
			'name'                  => '',
			'email'                 => '',
			'search'                => '',
			'start_date'            => '',
			'end_date'              => '',
			'date_compare'          => '=',
			'date_column'           => 'course_enrolment_date',
			'order'                 => 'DESC',
			'orderby'               => 'ID',
		);

		$args = wp_parse_args( $args, $defaults );

		if ( $args['number'] < 1 ) {
			$args['number'] = 999999999999;
		}

		$users_table   = $this->users_table_name;
		$courses_table = $this->table_name;

		$subquery       = '';
		$subquery_where = '';
		$query          = '';
		$query_where    = '';
		$orderby        = $args['orderby'];
		$order          = strtoupper( $args['order'] );
		$number         = absint( $args['number'] );
		$offset         = absint( $args['offset'] );
		$wild           = '%';
		$date_compare   = ! empty( $args['date_compare'] ) ? esc_attr( $args['date_compare'] ) : '=';
		$date_column    = ! empty( $args['date_column'] ) ? esc_attr( $args['date_column'] ) : 'date_created';

		if ( ! empty( $args['course_id'] ) ) {
			$subquery_where .= ' AND ';

			if ( is_array( $args['course_id'] ) ) {
				$course_ids     = implode( ',', array_map( 'intval', $args['course_id'] ) );
				$subquery_where .= "uc.course_id IN( {$course_ids} ) ";
			} else {
				$course_ids     = intval( $args['course_id'] );
				$subquery_where .= "uc.course_id = {$course_ids}";
			}
		}

		if ( ! empty( $args['user_id'] ) ) {
			if ( is_array( $args['user_id'] ) ) {
				$students    = implode( ',', array_map( 'intval', $args['user_id'] ) );
				$query_where .= "u.ID IN( {$students} )";
			} else {
				$students    = intval( $args['user_id'] );
				$query_where .= "u.ID = {$students}";
			}

			$query_where .= ' AND ';
		}

		if ( ! empty( $args['name'] ) ) {
			$name_value  = $wild . $wpdb->esc_like( stripslashes( $args['name'] ) ) . $wild;
			$query_where .= $wpdb->prepare( "u.display_name LIKE %s", $name_value );
			$query_where .= 'AND ';
		}

		if ( ! empty( $args['email'] ) ) {
			$email_value = $wild . $wpdb->esc_like( stripslashes( $args['email'] ) ) . $wild;
			$query_where .= $wpdb->prepare( "u.user_email LIKE %s", $email_value );
			$query_where .= 'AND ';
		}

		if ( ! empty( $args['search'] ) ) {
			$search_value = $args['search'];

			if ( is_numeric( $search_value ) ) {
				$search = $wpdb->prepare( "u.ID IN( %s )", $search_value );
			} elseif ( is_string( $search_value ) ) {
				$search_value = $wild . $wpdb->esc_like( stripslashes( $search_value ) ) . $wild;
				$search       = $wpdb->prepare( "u.display_name LIKE %s", $search_value );
				$search       .= $wpdb->prepare( " OR u.user_email LIKE %s", $search_value );
			}

			if ( ! empty( $search ) ) {
				$query_where .= '(' . $search . ')';
				$query_where .= ' AND ';
			}
		}

		if ( ! empty( $args['course_enrolment_date'] ) ) {
			$subquery_where .= ' AND ';

			$course_enrolment_date = esc_attr( $args['course_enrolment_date'] );
			$course_enrolment_date = date( 'Y-m-d H:i:s', strtotime( $course_enrolment_date ) );

			$subquery_where .= "course_enrolment_date {$date_compare} '{$course_enrolment_date}'";
		}

		if ( ! empty( $args['start_date'] ) ) {
			$subquery_where .= ' AND ';

			$start_date = esc_attr( $args['start_date'] );
			$start_date = date( 'Y-m-d H:i:s', $start_date );

			$subquery_where .= "{$date_column} >= '{$start_date}'";
		}

		if ( ! empty( $args['end_date'] ) ) {
			$subquery_where .= ' AND ';

			$end_date = esc_attr( $args['end_date'] );
			$end_date = date( 'Y-m-d H:i:s', $end_date );

			$subquery_where .= "{$date_column} <= '{$end_date}'";
		}

		switch ( $args['orderby'] ) {
			case 'id' :
			case 'user_id':
				$orderby = "u.ID";
				break;

			case 'name' :
				$orderby = "u.display_name";
				break;

			default :
				$orderby = array_key_exists( $args['orderby'], $this->get_user_columns() ) ? "u.{$args['orderby']}" : "u.ID";
				break;
		}

		$subquery = "SELECT 1 FROM {$this->table_name} uc WHERE uc.user_id = u.ID{$subquery_where}";

		if ( $count ) {
			$query = "SELECT COUNT(ID) FROM {$users_table} u 
					  WHERE {$query_where}EXISTS( {$subquery} )";

			return $wpdb->get_var( $query );
		}

		$query = "SELECT * FROM {$users_table} u 
				  WHERE {$query_where}EXISTS( {$subquery} ) 
				  ORDER BY {$orderby} {$order} 
				  LIMIT {$offset}, {$number}";

		return $wpdb->get_results( $query );
	}
}