<?php

/**
 * Functions for Formdata Database Table
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 * @author     Max Sternitzke
 */
class Trainingssystem_Plugin_Database_Formdata_Daoimple implements Trainingssystem_Plugin_Database_Formdata_Dao {

    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_FORM_DATA;
    public static $dbprefix_fields = TRAININGSSYSTEM_PLUGIN_DB_FORM_FIELDS;

    public function __construct(){
        $this->iniDB();
    }

    public function iniDB(){
        global $wpdb;

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

        $charset_collate = $wpdb->get_charset_collate();

        $tableversionsstr =[
            "CREATE TABLE IF NOT EXISTS $table_name (
                `ID` bigint(20) NOT NULL AUTO_INCREMENT,
                `field_ID` bigint(20) NOT NULL,
                `user_ID` bigint(20) NOT NULL,
                `data` text NOT NULL,
                `save_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                `form_group` int(11) NOT NULL DEFAULT 0,
                PRIMARY KEY (`ID`)
              )$charset_collate;"
        ];
        Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix,$table_name, $tableversionsstr);
    }

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

    }

    /**
     * Returns a FormData Object according to its ID
     * 
     * @param Int ID of FormData
     *
     * @return FormObject or null
     */
    public function getFormData($formdataid) {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $row = $wpdb->get_row($wpdb->prepare( "SELECT * FROM $table_name WHERE ID = %d", $formdataid), OBJECT);

        if($row != null) {
            return new Trainingssystem_Plugin_Database_Formdata($row->ID, $row->field_ID, $row->user_ID, $row->data, $row->save_date, $row->form_group);
        }
        return null;
    }

    /**
     * Returns the amount of formgroups a user has for a specific form
     * 
     * @param int form ID
     * @param int user ID 
     * 
     * @return int Amount of form groups
     */
    public function getFormgroupCount($formid, $userid) {
        global $wpdb;
        $table_name_fields = $wpdb->prefix . self::$dbprefix_fields;
        $table_name_data = $wpdb->prefix . self::$dbprefix;

        $row = $wpdb->get_row($wpdb->prepare( "SELECT MAX(data.form_group) as count FROM $table_name_fields as fields INNER JOIN $table_name_data as data ON fields.ID = data.field_ID WHERE fields.form_ID = %d AND data.user_ID = %d", $formid, $userid), OBJECT);
        
        if($row != null) {
            return $row->count;
        }
        return 0;
    }

    /**
     * Returns the latest data of a form field for a specific user and formgroup
     * 
     * @param int field ID
     * @param int user ID
     * @param int form group
     * 
     * @return Trainingssystem_Plugin_Database_Formdata if present therwise null
     */
    public function getFormfieldData($fieldid, $userid, $formgroup) {

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

        $row = $wpdb->get_row($wpdb->prepare( "SELECT * FROM $table_name WHERE field_ID = %d AND user_ID = %d AND form_group = %d ORDER BY save_date DESC LIMIT 1", $fieldid, $userid, $formgroup), OBJECT);
        
        if($row != null) {
            return new Trainingssystem_Plugin_Database_Formdata($row->ID, $row->field_ID, $row->user_ID, $row->data, $row->save_date, $row->form_group);
        }
        return null;
    }

    /**
     * Returns all data of a form field for a specific user and formgroup
     * 
     * @param int field ID
     * @param int user ID
     * @param int form group
     * 
     * @return Trainingssystem_Plugin_Database_Formdata if present therwise null
     */
    public function getAllFormfieldData($fieldid, $userid, $formgroup = null) {

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

        if(!is_null($formgroup) && is_int($formgroup)) {
            $data = $wpdb->get_results($wpdb->prepare( "SELECT * FROM $table_name WHERE field_ID = %d AND user_ID = %d AND form_group = %d ORDER BY save_date DESC", $fieldid, $userid, $formgroup), OBJECT);
        } else {
            $data = $wpdb->get_results($wpdb->prepare( "SELECT * FROM $table_name WHERE field_ID = %d AND user_ID = %d ORDER BY save_date DESC", $fieldid, $userid), OBJECT);
        }
        
        $result = [];

        foreach ($data as $row){

            $result[] = new Trainingssystem_Plugin_Database_Formdata($row->ID, $row->field_ID, $row->user_ID, $row->data, $row->save_date, $row->form_group);
        }

        return $result;
    }

    /**
     * Returns the data for the mediagrid shortcode
     * 
     * @param int Array or null user-ID
     * @param Array Field-IDs to include
     * @param int limits the number of records to be returned, default null = unlimited 
     * 
     * @return Array
     */
    public function getMediaGridData($userid, Array $includefields, $limit = null) {
        global $wpdb;

        $table_name = $wpdb->prefix . self::$dbprefix;
        
        $limitstr = "";
        if($limit != null) {
            $limitstr = " LIMIT " . sizeof($includefields) * $limit;
        }

        if($userid == null) {
            $data = $wpdb->get_results("SELECT * FROM $table_name WHERE field_ID IN (" . implode(',', $includefields) . ") ORDER BY save_date DESC, ID DESC" . $limitstr, OBJECT);
        } else if(is_array($userid)) {
            $data = $wpdb->get_results("SELECT * FROM $table_name WHERE field_ID IN (" . implode(',', $includefields) . ") AND user_ID IN (" . implode(',', $userid) . ") ORDER BY save_date DESC, ID DESC" . $limitstr, OBJECT);
        } else {
            $data = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name WHERE field_ID IN (" . implode(',', $includefields) . ") AND user_ID = %d ORDER BY save_date DESC, ID DESC" . $limitstr, $userid ), OBJECT);
        }

        $result = [];

        foreach ($data as $row)
        {
            if(!isset($result[$row->save_date . '-' . $row->user_ID])) {
                $result[$row->save_date . '-' . $row->user_ID] = array();
            }
            $result[$row->save_date . '-' . $row->user_ID][$row->field_ID] = new Trainingssystem_Plugin_Database_Formdata($row->ID, $row->field_ID, $row->user_ID, trim($row->data), $row->save_date, $row->form_group);
        }

        return $result;
    }

    /**
     * Return all FormData for a given project name 
     * 
     * @param int User-ID
     * @param String Project Name
     * @param int ID of project field
     * 
     * @return Array
     */
    public function getMediaGridDataByProjectname($userid, $projectname, $projectfieldid, $uploadfieldid) {
        global $wpdb;
        
        $table_name = $wpdb->prefix . self::$dbprefix;

        if(trim($projectname) != "") { // only one project
            $data = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name WHERE user_ID = %d AND save_date IN 
                                                        (SELECT save_date FROM $table_name WHERE user_ID = %d AND data = %s AND field_ID = %d)", $userid, $userid, $projectname, $projectfieldid), OBJECT);
        } else { // all my files
            $data = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name WHERE user_ID = %d AND save_date IN 
                                                        (SELECT save_date FROM $table_name WHERE user_ID = %d AND field_ID = %d)", $userid, $userid, $uploadfieldid), OBJECT);
        }
        $result = [];

        foreach ($data as $row)
        {
            $result[] = new Trainingssystem_Plugin_Database_Formdata($row->ID, $row->field_ID, $row->user_ID, trim($row->data), $row->save_date, $row->form_group);
        }

        return $result;
    }

    /**
     * Returns the data of a form field for a group of users
     * 
     * @param int field ID
     * @param Array user IDs
     * 
     * @return Array of Trainingssystem_Plugin_Database_Formdata or empty array if no data is present
     */
    public function getFormfieldDataMultiUser($fieldid, $userids) {
        global $wpdb;
        
        if(empty($userids)) {
            return array();
        }

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

        $data = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name WHERE field_ID = %d AND user_ID IN (" . implode(',', $userids) . ")", $fieldid ), OBJECT);
        $result = [];

        foreach ($data as $row){

            $result[] = new Trainingssystem_Plugin_Database_Formdata($row->ID, $row->field_ID, $row->user_ID, $row->data, $row->save_date, $row->form_group);
        }

        return $result;
    }

    /**
     * Deletes all form data for a user and a specific form
     * 
     * @param int form ID
     * @param int user ID
     * 
     * @return boolean success true/false
     */
    public function deleteFormDataForUser($formid, $userid) {
        global $wpdb;
        $table_name_fields = $wpdb->prefix . self::$dbprefix_fields;
        $table_name_data = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->prepare("DELETE $table_name_data FROM $table_name_data
                                JOIN $table_name_fields ON $table_name_fields.ID = $table_name_data.field_ID 
                                WHERE $table_name_fields.form_ID = %d AND $table_name_data.user_ID = %d", $formid, $userid);

        return $wpdb->query($sql) !== false;
    }

    /**
     * Inserts a new formdata dataset
     * 
     * @param int field ID
     * @param int user ID
     * @param String data
     * @param int formgroup
     * 
     * @return boolean success
     */
    public function insertFormDataForUser($fieldid, $userid, $data, $formgroup, $time = null) {
        global $wpdb;
        $table_name_data = $wpdb->prefix . self::$dbprefix;

        if($time == null) {
            $time = current_time('mysql');
        }
        $sql = $wpdb->insert($table_name_data,
                                array(
                                    "field_ID" => $fieldid,
                                    "user_ID" => $userid,
                                    "data" => $data,
                                    "form_group" => $formgroup,
                                    "save_date" => $time,
                                ),
                                array("%d", "%d", "%s", "%d", "%s")
                            );
        return $sql !== false;
    }

    /**
     * Deletes all form data connected to a specific form id. Usually called when form is deleted
     * 
     * @param form ID
     * 
     * @return boolean success
     */
    public function deleteFormdataByFormId($formid) {
        global $wpdb;
        $table_name_fields = $wpdb->prefix . self::$dbprefix_fields;
        $table_name_data = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->prepare("DELETE $table_name_data FROM $table_name_data
                                JOIN $table_name_fields ON $table_name_fields.ID = $table_name_data.field_ID 
                                WHERE $table_name_fields.form_ID = %d", $formid);

        return $wpdb->query($sql) !== false;
    }

    /**
     * Deletes all form data connected to a specific field id. Usually called when field is deleted
     * 
     * @param field ID
     * 
     * @return boolean success
     */
    public function deleteFormdataByFieldId($fieldid) {
        global $wpdb;
        $table_name_data = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->delete($table_name_data,
                                array(
                                    "field_ID" => $fieldid
                                ),
                                array(
                                    "%d"
                                )
                            );

        return $sql !== false;
    }

    /**
     * Deletes all formdata for a specific user and savedate
     * 
     * @param Int User-ID
     * @param Timestamp 
     * 
     * @return boolean success
     */
    public function deleteSavepoint($userid, $timestamp) {
        global $wpdb;
        $table_name_data = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->delete($table_name_data,
                                array(
                                    "user_ID" => $userid,
                                    "save_date" => $timestamp
                                ),
                                array(
                                    "%d", "%s"
                                )
                            );

        return $sql !== false;
    }

    /**
     * Deletes a formdata set by its unique ID
     * 
     * @param int FormData ID
     * 
     * @return boolean success/failure
     */
    public function deleteFormData($formdataid) {
        global $wpdb;
        $table_name_data = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->delete($table_name_data,
                                array(
                                    "ID" => $formdataid
                                ),
                                array(
                                    "%d"
                                )
                            );

        return $sql !== false;
    }

    private function removeAllUploadFiles($userid) {
        global $wpdb;
        $table_name_data = $wpdb->prefix . self::$dbprefix;
        $table_name_fields = $wpdb->prefix . self::$dbprefix_fields;

        $result = $wpdb->get_results($wpdb->prepare("SELECT data FROM $table_name_data d INNER JOIN $table_name_fields f ON d.field_ID = f.ID WHERE d.user_ID = %d AND f.type = 'upload'", $userid));

        if($result) {
            foreach($result as $file) {
                wp_delete_attachment($file->data, true);
            }
        }
    }

    /**
     * Get data for all $tsformids and $userids in one query for the DataExport Module
     * 
     * @param Integer-Array TS Form IDs
     * @param Integer-Array User-IDs
     * @param Boolean Index 4th level by form-group (default) or by savedate -> form-group
     * 
     * @return Array
     *          -> User-ID
     *              |-> TS Form ID
     *                  |-> Field ID
     *                      |-> entry 1
     *                      |-> entry 2 etc.
     */
    public function getDataExportData($tsformids, $userids, $indexdate = false) {
        global $wpdb;
        $table_name_data = $wpdb->prefix . self::$dbprefix;
        $table_name_fields = $wpdb->prefix . self::$dbprefix_fields;

        $sql = "SELECT f.form_ID, d.field_ID, d.user_ID, d.data, d.form_group, d.save_date
                FROM $table_name_fields f
                INNER JOIN $table_name_data d ON d.field_ID = f.ID 
                WHERE user_ID IN (" . implode(", ", $userids) . ") 
                    AND form_ID IN (" . implode(",", $tsformids) . ")
                ORDER BY form_ID, field_ID, form_group, save_date ASC";
        $result = $wpdb->get_results($sql);

        $ret = array();
        if($result) {
            foreach($result as $row) {

                if(!isset($ret[$row->user_ID])) {
                    $ret[$row->user_ID] = array();
                }

                if(!isset($ret[$row->user_ID][$row->form_ID])) {
                    $ret[$row->user_ID][$row->form_ID] = array();
                }

                if(!isset($ret[$row->user_ID][$row->form_ID][$row->field_ID])) {
                    $ret[$row->user_ID][$row->form_ID][$row->field_ID] = array();
                }

                if($indexdate) {
                    $ret[$row->user_ID][$row->form_ID][$row->field_ID][$row->save_date][$row->form_group] = $row->data;
                } else {
                    $ret[$row->user_ID][$row->form_ID][$row->field_ID][$row->form_group] = $row->data;
                }
            }
        }
        return $ret;
    }

    function getAllFormdataDates($startDate = null, $endDate = null){
        global $wpdb;
        $table_name = $wpdb->prefix.self::$dbprefix;
        $data = array();
        if(is_null($startDate) && is_null($endDate)) {
            $data = $wpdb->get_results("SELECT save_date FROM " . $table_name . " ORDER BY save_date DESC",ARRAY_N);
        } elseif(!is_null($startDate) && is_null($endDate)) {
            $data = $wpdb->get_results("SELECT save_date FROM " . $table_name . " WHERE save_date >= '" . $startDate . "' ORDER BY save_date DESC",ARRAY_N);
        } elseif(is_null($startDate) && !is_null($endDate)) {
            $data = $wpdb->get_results("SELECT save_date FROM " . $table_name . " WHERE save_date <= '" . $endDate . "' ORDER BY save_date DESC",ARRAY_N);
        } elseif(!is_null($startDate) && !is_null($endDate)) {
            $data = $wpdb->get_results("SELECT save_date FROM " . $table_name . " WHERE save_date BETWEEN '" . $startDate . "' AND '" . $endDate . "' ORDER BY save_date DESC",ARRAY_N);
        }
        return $data;
    }
    
    public function checkNewContent($userIds, $tsFormIds, $pageId){

		global $wpdb;

		$table_user_logs = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->UserLogs::$dbprefix;
		$table_form_data = $wpdb->prefix . self::$dbprefix;
		$table_form_fields = $wpdb->prefix . self::$dbprefix_fields;

		$table_user_meta = $wpdb->usermeta;
		$current_user_id = get_current_user_id();
		$newContent = array();

		if(isset($userIds) && !empty($userIds) && isset($tsFormIds) && !empty($tsFormIds) && isset($pageId) && !empty($pageId)){
			$sql1 = $wpdb->prepare("SELECT us.* FROM $table_user_logs us 
									INNER JOIN (SELECT pageid, MAX(date_time) AS maxdate FROM $table_user_logs WHERE userid = %d GROUP BY pageid) md ON us.pageid = md.pageid AND date_time = maxdate 
									WHERE us.pageid = %d", $current_user_id, $pageId);


			$results = $wpdb->get_row($sql1);

			foreach($tsFormIds as $tsFormId){
				if(isset($results->date_time)){
					$sql2 =$wpdb->prepare("SELECT fData.* FROM $table_form_data fData 
											INNER JOIN $table_form_fields fFields ON fData.field_ID = fFields.ID
											INNER JOIN $table_user_meta uMeta ON fData.user_ID = uMeta.user_id
											WHERE uMeta.meta_key = 'forward_content' AND fFields.form_ID = %d AND uMeta.meta_value LIKE '%$tsFormId%' AND save_date > '%s' AND fData.user_ID IN (". implode(',', $userIds) .")", $tsFormId, $results->date_time);
					$formData = $wpdb->get_results($sql2);

					foreach($formData as $fData){
						$newContent[] = $fData;
					}
				}
				else{
					$sql2 =$wpdb->prepare("SELECT fData.* FROM $table_form_data fData 
											INNER JOIN $table_form_fields fFields ON fData.field_ID = fFields.ID
											INNER JOIN $table_user_meta uMeta ON fData.user_ID = uMeta.user_id
											WHERE uMeta.meta_key = 'forward_content' AND fFields.form_ID = %d AND uMeta.meta_value LIKE '%$tsFormId%' AND fData.user_ID IN (". implode(',', $userIds) .")", $tsFormId);
					$formData = $wpdb->get_results($sql2);

					foreach($formData as $fData){
						$newContent[] = $fData;
					}
				}
			}
		}

		return $newContent;
	}

    public function checkNewSurveyResults($userIds, $tsFormIds, $pageId){

		global $wpdb;

		$table_user_logs = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->UserLogs::$dbprefix;
		$table_form_data = $wpdb->prefix . self::$dbprefix;
		$table_form_fields = $wpdb->prefix . self::$dbprefix_fields;

		$current_user_id = get_current_user_id();
		$newSurveyResults = array();

		if(isset($userIds) && !empty($userIds) && isset($tsFormIds) && !empty($tsFormIds) && isset($pageId) && !empty($pageId)){
			$sql1 = $wpdb->prepare("SELECT us.* FROM $table_user_logs us 
									INNER JOIN (SELECT pageid, MAX(date_time) AS maxdate FROM $table_user_logs WHERE userid = %d GROUP BY pageid) md ON us.pageid = md.pageid AND date_time = maxdate 
									WHERE us.pageid = %d", $current_user_id, $pageId);


			$results = $wpdb->get_row($sql1);

			foreach($tsFormIds as $tsFormId){
				if(isset($results->date_time)){
					$sql2 =$wpdb->prepare("SELECT fData.* FROM $table_form_data fData 
											INNER JOIN $table_form_fields fFields ON fData.field_ID = fFields.ID
											WHERE fFields.form_ID = %d AND save_date > '%s' AND fData.user_ID IN (". implode(',', $userIds) .")", $tsFormId,$results->date_time);
					$formData = $wpdb->get_results($sql2);

					foreach($formData as $fData){
						$newSurveyResults[] = $fData;
					}
				}
				else{
					$sql2 =$wpdb->prepare("SELECT fData.* FROM $table_form_data fData 
											INNER JOIN $table_form_fields fFields ON fData.field_ID = fFields.ID
											WHERE fFields.form_ID = %d AND fData.user_ID IN (". implode(',', $userIds) .")", $tsFormId);
					$formData = $wpdb->get_results($sql2);

					foreach($formData as $fData){
						$newSurveyResults[] = $fData;
					}
				}
			}
		}

		return $newSurveyResults;
	}

    public function deleteAllFormDataForUser($userid){
        global $wpdb;

        $this->removeAllUploadFiles($userid);
        
		$table_name_data = $wpdb->prefix . self::$dbprefix;
        
		$wpdb->delete( $table_name_data, array( 'user_ID' => $userid ) );
    }
}
