<?php
namespace AcademyPro\Classes;

use Academy\Classes\ExportBase;
use \Academy\Helper;

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

class InstructorsExport extends ExportBase {

	public function get_instructors_for_export() {
		$instructor_array = [];
		$empty_instructor_args = [
			'user_id'           => '',
			'user_email'        => '',
			'user_nicename'     => '',
			'user_registered'   => '',
			'first_name'        => '',
			'last_name'         => '',
			'total_courses'  => '',
		];

		$instructors      = $this->get_all_instructors();
		if ( count( $instructors ) ) {
			foreach ( $instructors as $instructor ) {
				$total_courses = $this->get_courses_by_instructor_id( $instructor->ID );
				$instructor_array[] = [
					'user_id'           => $instructor->ID,
					'user_email'        => $instructor->user_email,
					'user_nicename'     => $instructor->user_nicename,
					'user_registered'   => $instructor->user_registered,
					'first_name'        => $instructor->first_name,
					'last_name'         => $instructor->last_name,
					'total_courses'     => is_array( $total_courses ) ? implode( ',', $total_courses ) : $total_courses,
				];
			} //end foreach
			return $instructor_array;
		} //end if

		return [ $empty_instructor_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_instructors() {
		global $wpdb;
		$results = $wpdb->get_results( "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_instructor'
            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'
            ORDER BY u.ID ASC"
		);
		return $results ?? [];
	}

	public static function get_courses_by_instructor_id( $instructor_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$course_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT p.post_title
				FROM   {$wpdb->usermeta} AS um
				INNER JOIN {$wpdb->posts} AS p
				WHERE p.ID = um.meta_value
				AND  um.user_id = %d
				AND um.meta_key = %s
			",
				$instructor_id,
				'academy_instructor_course_id'
			)
		);
		if ( count( $course_ids ) ) {
			return $course_ids;
		}
		return false;
	}
}
