<?php

/**
 * Register all actions and filters for the plugin
 *
 * @link       -
 * @since      1.0.0
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 */

/**
 * Register all actions and filters for the plugin.
 *
 * Maintain a list of all hooks that are registered throughout
 * the plugin, and register them with the WordPress API. Call the
 * run function to execute the list of actions and filters.
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 * @author     Markus Domin, Helge Nissen <->
 */
class Trainingssystem_Plugin_Database_Trainingseiten_Daoimple {



	public static $dbprefix=TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;
	/**
	 * Initialize the collections used to maintain the actions and filters.
	 *
	 * @since    1.0.0
	 */
	public function __construct() {

		$this->iniDB();
	}

	private function iniDB(){

		global $wpdb;
		$table_name = $wpdb->prefix . self::$dbprefix;
		$charset_collate = $wpdb->get_charset_collate();
        $tableversionsstr =[
			"CREATE TABLE IF NOT EXISTS  $table_name(
				lektion_id BIGINT(20) NOT NULL,
				seiten_id BIGINT(20) NOT NULL,
				seiten_index INT NOT NULL,
				traning_id BIGINT(20) NOT NULL,
				PRIMARY KEY (lektion_id, seiten_id, traning_id)
		)$charset_collate;",
		"ALTER TABLE $table_name CHANGE traning_id training_id BIGINT(20) NOT NULL;"
        ];
		Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix,$table_name, $tableversionsstr);
		



	}

	 /**
     * database updating beim plugin update
     *
     * @since    1.0.0
     */
	public function updating(){

	}

	/**
	 * [getLektionenSeiten description]
	 * @param  [type] $trainingsID [description]
	 * @param  [type] $lektionID   [description]
	 * @return [type]              [description]
	 */
	public function getLektionenSeiten($trainingsID, $lektionID)
	{
			global $wpdb;
			$tablename = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;
			$sql = $wpdb->prepare("SELECT * FROM $tablename where training_id = %d AND lektion_id = %d ORDER BY seiten_index", $trainingsID, $lektionID);
			$result = $wpdb->get_results($sql);
			//var_dump($result);
			$lektionarr = array();
			$i=0;
			foreach ($result as $key => $value) {
					$post = get_post($value->seiten_id);
					$url = add_query_arg(array('idt1' => $trainingsID, 'idt2' => $lektionID), get_permalink($value->seiten_id));//esc_url();//get_permalink( $id );
									$imageurl = null;
					if (has_post_thumbnail($post->ID)) {
							$imageurl = get_the_post_thumbnail_url($post->ID);//, 'full' );
					}
					$lektionarr[] = new Trainingssystem_Plugin_Database_Trainingseite(array(
						"id"=>$value->seiten_id,
						"index"=>$i,
						"title"=>$post->post_title,
						"excerpt"=>$post->post_excerpt,
						"url"=>$url,
						"imageurl"=>$imageurl,
						"trainingid"=>$trainingsID,
						"lektionid"=>$lektionID
					));
				$i++;
			}

			return $lektionarr;
	}

	

	/**
	 * Get a Trainingsseite Object with Page-Index
	 * 
	 * @param Integer $trainingId
	 * @param Integer $ektionId
	 * @param Integer $pageId
	 * 
	 * @return Trainingssystem_Plugin_Database_Trainingseite
	 */
	public function getTrainingsseite($trainingid, $lektionid, $pageid){
		global $wpdb;
		$tablenametrainingsseiten = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;

		$sql = $wpdb->prepare("SELECT seiten.seiten_index 
					FROM $tablenametrainingsseiten as seiten 
					WHERE seiten.training_id= %d 
					AND seiten.lektion_id =%d 
					AND seiten.seiten_id = %d",
					$trainingid, $lektionid, $pageid);
		$pageindex = $wpdb->get_var($sql);
		
		if(!is_null($pageindex)) {
			return new Trainingssystem_Plugin_Database_Trainingseite([
				"id" => $pageid,
				"trainingid" => $trainingid, 
				"lektionid" => $lektionid,
				"index" => intval($pageindex),
			]);
		} else {
			return null;
		}
	}

	/**
	 * Prüfen ob eine Lektion oder das Training abgeschlussen wurden
	 * 
	 * @param Trainingssystem_Plugin_Database_Trainingseite $nowpage the page the User is currently on
	 */
	public function checkfin($nowpage){
		global $wpdb;
		$tablename = $wpdb->prefix . TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;
		$training_user_db = $wpdb->prefix . TRAININGSSYSTEM_PLUGIN_DB_TRAINING_USER;

		$user_training_query = $wpdb->prepare("SELECT * FROM $training_user_db WHERE lektion_id = %d AND training_id = %d AND user_id = %d LIMIT 1", $nowpage->getLektionid(), $nowpage->getTrainingid(), get_current_user_id());
		$user_training = $wpdb->get_row($user_training_query);

		$sql = $wpdb->prepare("SELECT * FROM $tablename WHERE lektion_id = %d AND training_id = %d ORDER BY seiten_index DESC LIMIT 1", $nowpage->getLektionid(), $nowpage->getTrainingid());
		$lastdbindex = $wpdb->get_row($sql);
		//check ob das Lektion vollständig beendet wurde
		if($lastdbindex->seiten_id == $nowpage->getId() && $user_training->lektion_fin_date == null){

			$wpdb->update(
				$training_user_db,
				array(
					'lektion_fin_date' => current_time( 'mysql' ),
				),
				array(
					'lektion_id' => $nowpage->getLektionid(),
					'training_id'=> $nowpage->getTrainingid(),
					'user_id' => get_current_user_id(),
				)
			);

			do_action(TRAININGSSYSTEM_PLUGIN_ACTION_LEKTION_FIN, $nowpage, get_current_user_id());

			$trainingTitle = get_the_title($nowpage->getTrainingid());
			$lektionTitle = get_the_title($nowpage->getLektionid());

			$trainingPost = get_post($nowpage->getTrainingid());
			$saveUserEvent = true;
			if(!empty($trainingPost->disableUserEvents) && $trainingPost->disableUserEvents == "1") {
				$saveUserEvent = false;
			}

			// User Event
			if($saveUserEvent) {
				$title = "Training „" . $trainingTitle . "“: Lektion „" . $lektionTitle. "“ abgeschlossen";
				$subtitle = "";
				// Params: $title, $subtitle, $userid, $postid, $url, $type, $level, $date (= null)
				Trainingssystem_Plugin_Database::getInstance()->UserEvent->insertUserEvent($title, $subtitle, get_current_user_id(), $nowpage->getLektionId(), add_query_arg("idt1", $nowpage->getTrainingid(), get_permalink($nowpage->getTrainingid())), "training", "important");
			}
			//check ob das Training vollständig beendet wurde
			$temptrain = Trainingssystem_Plugin_Database::getInstance()->TrainingDao->getTrainingByUser($nowpage->getTrainingid(),get_current_user_id());
			if($temptrain->getFin() == 100){
				do_action(TRAININGSSYSTEM_PLUGIN_ACTION_TRAINING_FIN, $nowpage,$temptrain, get_current_user_id());

				if($saveUserEvent) {
					// User Event
					$title = "Training „" . $trainingTitle . "“ abgeschlossen";
					$subtitle = "";
					// Params: $title, $subtitle, $userid, $postid, $url, $type, $level, $date (= null)
					Trainingssystem_Plugin_Database::getInstance()->UserEvent->insertUserEvent($title, $subtitle, get_current_user_id(), $nowpage->getTrainingid(), add_query_arg("idt1", $nowpage->getTrainingid(), get_permalink($nowpage->getTrainingid())), "training", "important");
				}
			}
		}
	}

	function getAll(){
		$query = new WP_Query( array( 'post_type' => 'seiten','posts_per_page'=>-1,'orderby'=> 'title', 'order'=> 'ASC' ) );
		$posts = $query->posts;
		$seitenarr = array();
		foreach ($posts as $key => $value) {
			$post = get_post($value->ID);
			$seitenarr[]= new Trainingssystem_Plugin_Database_Trainingseite(array(
												"id"=>$value->ID,
												"title"=>$post->post_title,
												"excerpt"=>$post->post_excerpt
											));
		}
		return $seitenarr;
	}

	/*
     * Get the starting dates for each 'lektion' for all $trainings and $userids in one query for the DataExport Module
     * 
     * @param Integer-Array Training-IDs
     * @param Integer-Array User-IDs
     * 
     * @return Array
     *          -> User-ID
     *              |-> Training-ID
     *                  |-> Lektion-ID
	 *						|-> entry
    */
	public function getSystemstatisticStartedLektion($trainings, $userids){

		global $wpdb;
		$table_name_trainings = $wpdb->prefix . self::$dbprefix;
		$table_name_userlogs = $wpdb->prefix . "md_trainingsmgr_user_logs";

		$sql1 = "SELECT training_id, lektion_id
				 FROM $table_name_trainings
				 WHERE training_id IN (" . implode(",", $trainings) . ");
		"; 
		$result1 = $wpdb->get_results($sql1);

		$trainingTemp = array();

		if($result1){
			foreach($result1 as $row){

				if(!isset($trainingTemp[$row->training_id])){
					$trainingTemp[$row->training_id] = array();
				}
				if(!isset($trainingTemp[$row->training_id][$row->lektion_id])){
					$trainingTemp[$row->training_id][$row->lektion_id] = array();
				}

				$trainingTemp[$row->training_id][$row->lektion_id]["startdate"] = null;
			}
		}

		$sql2 = "SELECT ul.userid, ts.training_id, ts.lektion_id, MIN(ul.date_time) AS startdate
				 FROM $table_name_trainings ts 
				 INNER JOIN $table_name_userlogs ul ON ts.seiten_id = ul.pageid
				 WHERE ts.seiten_index = 0 AND ts.training_id IN (" . implode(",", $trainings) . ") AND ul.userid IN (" . implode(",", $userids) . ") 
				 GROUP BY ul.userid, ts.lektion_id, ts.lektion_id
		";

		$result2 = $wpdb->get_results($sql2);

		$ret = array();

		foreach($userids as $user){
			$ret[$user] = $trainingTemp;
		}

		if($result2){
			foreach($result2 as $row2){
				$ret[$row2->userid][$row2->training_id][$row2->lektion_id]["startdate"] = $row2->startdate;
			}
		}

		return $ret;
	}

    public function getSeitenTitle() {
        global $wpdb;

        $sql = "SELECT ID, post_title FROM $wpdb->posts WHERE post_type = 'seiten' AND post_status = 'publish'";
        $data = $wpdb->get_results($sql, OBJECT);
        $result = [];

        foreach ($data as $row){

            $result[$row->ID] = $row->post_title;
        }
        
        return $result;
    }

	public function getAllTrainingseiten(){

		global $wpdb;

		$table_name = $wpdb->prefix . self::$dbprefix;

		$sql = "SELECT * FROM $table_name ts INNER JOIN $wpdb->posts p ON ts.seiten_id = p.ID";

		$data = $wpdb->get_results($sql, OBJECT);
		$result = [];

		foreach($data as $row){
			$url = add_query_arg(array('idt1' => $row->training_id, 'idt2' => $row->lektion_id), get_permalink($row->seiten_id));
			$result[]= new Trainingssystem_Plugin_Database_Trainingseite(array(
				"title" => $row->post_title,
				"id" => $row->seiten_id,
				"url" => $url,
				"index" => $row->seiten_index,
				"trainingid" => $row->training_id,
				"lektionid" => $row->lektion_id
			));
		}

		return $result;
	}

	public function getTrainingBySeite($seitenid) {
        global $wpdb;

        $tablename = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;
        $sql = $wpdb->prepare("SELECT DISTINCT training_id FROM $tablename WHERE seiten_id = %d", $seitenid);
        $data = $wpdb->get_results($sql, OBJECT);

        $result = [];

        foreach ($data as $row){

            $result[] = $row->training_id;
        }
        
        return $result;
    }

	/**
	 * Return the page IDs of a lektion
	 * 
	 * @param Integer Training-ID
	 * @param Integer Lektion-ID
	 * 
	 * @return Array Integers
	 */
	public function getSeitenIdsByLektion($trainingid, $lektionid) {

		global $wpdb;

		$tablename = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;

		$sql = $wpdb->prepare("SELECT seiten_id FROM $tablename where training_id = %d AND lektion_id = %d ORDER BY seiten_index", $trainingid, $lektionid);
		$result = $wpdb->get_results($sql);

		$ret = [];

		foreach($result as $r) {
			$ret[] = $r->seiten_id;
		}
		return $ret;
	}

	/**
	 * Return the page IDs of a training
	 * 
	 * @param Integer Training-ID
	 * 
	 * @return Array Integers
	 */
	public function getSeitenIdsByTraining($trainingid) {

		global $wpdb;

		$tablename = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;

		$sql = $wpdb->prepare("SELECT seiten_id FROM $tablename where training_id = %d ORDER BY seiten_index", $trainingid);
		$result = $wpdb->get_results($sql);

		$ret = [];

		foreach($result as $r) {
			$ret[] = $r->seiten_id;
		}
		return $ret;
	}

	public function getAllLektionIDs() 
	{
		global $wpdb;

		$tablename = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;

		$sql = "SELECT DISTINCT lektion_id FROM $tablename;";

		$result = $wpdb->get_results($sql);

		$ret = [];

		foreach($result as $r) {
			$ret[] = $r->lektion_id;
		}
		return $ret;
	}

	public function getAllSeitenIDs() 
	{
		global $wpdb;

		$tablename = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;

		$sql = "SELECT DISTINCT seiten_id FROM $tablename;";

		$result = $wpdb->get_results($sql);

		$ret = [];

		foreach($result as $r) {
			$ret[] = $r->seiten_id;
		}
		return $ret;
	}

	public function getAllUnusedSeitenIDs()
	{
		$allSeiten = $this->getAll();
        $usedLSeitenIDs = $this->getAllSeitenIDs();

        $ret = [];

        foreach($allSeiten as $seite) {
            if(!in_array($seite->getId(), $usedLSeitenIDs)) {
                $ret[] = $seite;
            }
        }

        return $ret;
	}

    public function getMultipleUsedSeitenIds() {
        global $wpdb;
        
        $ret = [];

        $sql = "SELECT seiten_id FROM " . $wpdb->prefix . self::$dbprefix . " group by seiten_id having count(*) > 1;";
        $data = $wpdb->get_results($sql);

        foreach($data as $row) {
            $ret[] = $row->seiten_id;
        }

        return $ret;
    }
}
