<?php
namespace AcademyPro\Classes;

use Academy\Classes\ExportBase;

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

class StudentsExport extends ExportBase {

	public function get_students_for_export() {
		 $student_array = [];
		$empty_students_args = [
			'user_id'           => '',
			'user_email'        => '',
			'user_nicename'     => '',
			'user_registered'   => '',
			'first_name'        => '',
			'last_name'         => '',
			'enrolled_courses'  => '',
			'completed_courses' => '',
			'in_progress'       => '',
		];
		$instructor_id = ! current_user_can( 'manage_options' ) ? get_current_user_id() : 0;
		$students      = $this->get_all_students( $instructor_id );
		if ( count( $students ) ) {
			foreach ( $students as $student ) {
				$completed_courses = $this->get_total_completed_courses_by_student_id( $student->ID );
				$course_taken    = $this->get_total_enrolled_courses( $student->ID );
				$student_array[] = [
					'user_id'           => $student->ID,
					'user_email'        => $student->user_email,
					'user_nicename'     => $student->user_nicename,
					'user_registered'   => $student->user_registered,
					'first_name'        => $student->first_name,
					'last_name'         => $student->last_name,
					'enrolled_courses'  => is_array( $course_taken ) ? implode( ', ', $course_taken ) : $course_taken,
					'completed_courses' => is_array( $completed_courses ) ? implode( ', ', $completed_courses ) : $completed_courses,
					'in_progress'       => is_array( $course_taken ) ? implode( ', ', array_diff( $course_taken, $completed_courses ) ) : '',
				];
			} //end foreach
			return $student_array;
		} //end if

		return [ $empty_students_args ];
	}

	/**
	 * Method Overwrite
	 *
	 * Overwrite export base class method
	 *
	 * @param array          $array
	 * @param resource|false $fp
	 * @return void
	 */
	public function write_nested_csv( $array, $fp ) {
		foreach ( $array as $row ) {
			$flattenRow = $this->flatten_array( $row );
			if ( isset( $flattenRow['user_id'] ) ) {
				$row_header = array_keys( $flattenRow );
				fputcsv( $fp, $row_header );
			}
			fputcsv( $fp, $flattenRow );
			fputcsv( $fp, [] );
		}
	}

	public function get_all_students( $instructor_id = 0 ) {
		global $wpdb;

		$query = "
            SELECT u.ID,
                u.user_nicename,
                u.user_email,
                u.user_registered,
                first_name.meta_value AS first_name,
                last_name.meta_value AS last_name
            FROM {$wpdb->users} AS u
            INNER JOIN {$wpdb->usermeta} AS role_meta
                ON u.ID = role_meta.user_id
            AND role_meta.meta_key = 'is_academy_student'
            LEFT JOIN {$wpdb->usermeta} AS first_name
                ON u.ID = first_name.user_id
            AND first_name.meta_key = 'first_name'
            LEFT JOIN {$wpdb->usermeta} AS last_name
                ON u.ID = last_name.user_id
            AND last_name.meta_key = 'last_name'
        ";

		$params = [];

		if ( ! empty( $instructor_id ) ) {
			$student_ids = \Academy\Helper::get_total_students_by_instructor( $instructor_id );

			if ( ! empty( $student_ids ) && is_array( $student_ids ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $student_ids ), '%d' ) );
				$query .= " WHERE u.ID IN ($placeholders)";
				$params = $student_ids;
			} else {
				return [];
			}
		}

		$query .= ' ORDER BY u.ID ASC';

		if ( ! empty( $params ) ) {
            // phpcs:ignore
            $query = $wpdb->prepare($query, $params);
		}

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

	public static function get_total_completed_courses_by_student_id( $user_id ) {
		global $wpdb;
		$completed = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT p.post_title 
				FROM	{$wpdb->comments} AS c
				INNER JOIN {$wpdb->posts} AS p
				WHERE	c.comment_post_ID = p.ID
				AND c.comment_agent = %s 
				AND c.comment_type = %s 
				AND c.user_id = %d;",
				'academy',
				'course_completed',
				$user_id
			)
		);
		return $completed;
	}

	public function get_total_enrolled_courses( $user_id ) {
		global $wpdb;
		$query = $wpdb->prepare(
			"SELECT parent.post_title 
			FROM {$wpdb->posts} AS parent
			INNER JOIN {$wpdb->posts} AS child
			WHERE child.post_parent = parent.ID
			AND child.post_type = %s
			AND child.post_author = %d
			AND child.post_status = %s",
			'academy_enrolled',
			$user_id,
			'completed'
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$results = $wpdb->get_col( $query );
		return $results;
	}
}
