<?php

/**
 * Functions for UserEvent Database Table
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 * @author     Max Sternitzke
 */
class Trainingssystem_Plugin_Database_User_Event_Daoimple implements Trainingssystem_Plugin_Database_User_Event_Dao {

    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_USER_EVENT;
	private static $userEventSettingsKey = "tspv2_user_events_settings";

    public static $level = ["detailed", "info", "important"];
    public static $types = [
                                "profile", // everything a user does in the profile section: change mail, password, names, phone number, delete data
                                "login", // successfull and failed logins 
                                "logout", // logouts
                                "training", // every training page visited as well as training and lektion finished, date training assignments
                                "registerkey", // register keys redeemed
                                "check", // every completed and saved check
                            ];
    public static $origins = ["frontend"];

    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,
                `title` VARCHAR(256) NOT NULL,
                `subtitle` VARCHAR(512) NULL,
                `user_id` BIGINT(20) NOT NULL,
                `post_id` BIGINT(20) NULL,
                `url` VARCHAR(512) NULL,
                `event_type` VARCHAR(32) NOT NULL,
                `event_level` VARCHAR(32) NOT NULL, 
                `date` timestamp NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (`id`)
              )$charset_collate;",
            "ALTER TABLE $table_name ADD INDEX `" . $table_name . "_userid` (`user_id`) USING BTREE;",
            "ALTER TABLE $table_name ADD INDEX `" . $table_name . "_postid` (`post_id`) USING BTREE;",
            "ALTER TABLE $table_name ADD `origin` VARCHAR(32) NULL AFTER event_level;",
            "UPDATE $table_name SET origin = 'frontend' WHERE 1;",
        ];
        Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix,$table_name, $tableversionsstr);
    }

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

    }

    /**
     * Gets a user's events. Filter and ordering is possible
     * 
     * @param Int User-ID
     * @param Array Level
     * @param Array Types
     * @param Int Limit, -1 for unlimited, > 0 for limit
     * @param String orderby ASC or DESC
     * 
     * @return Array of Trainingssystem_Plugin_Database_User_Event
     */
    public function getUserEvents($userid, $level, $types, $limit, $orderby) { 

        if(count($level) == 0 || count($types) == 0 || !in_array($orderby, array("ASC", "DESC"))) {
            return array();
        }

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

        $sql = "SELECT * FROM $table_name WHERE user_id = %d AND event_level IN ('" . implode("','", $level) . "') AND event_type IN ('" . implode("','", $types) . "') ORDER BY date $orderby";
        if($limit != -1 && $limit > 0) {
            $sql .= " LIMIT $limit";
        }

        $data = $wpdb->get_results($wpdb->prepare($sql, $userid));

        $result = [];

        foreach ($data as $row) {
            $result[] = new Trainingssystem_Plugin_Database_User_Event($row->id, $row->title, $row->subtitle, $row->user_id, $row->post_id, $row->url, $row->event_type, $row->event_level, $row->origin, $row->date);
        }
        
        return $result;
    }

    /**
     * Inserts a new User Event
     * 
     * @param String Title
     * @param String Subtitle
     * @param Int User-ID
     * @param Int Post-ID
     * @param String URL
     * @param String Type
     * @param String Level
     * @param String Quelle - default frontend
     * @param String Date, if null, current date is used
     * 
     * @return Bool success
     */
    public function insertUserEvent($title, $subtitle, $userid, $postid, $url, $type, $level, $origin = 'frontend', $date = null) {

        if(!in_array($level, self::$level) || !in_array($type, self::$types) || !in_array($origin, self::$origins)) {
            return false;
        }

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

        if($date == null) {
            $date = current_time('mysql');
        }
        $sql = $wpdb->insert($table_name,
                                array(
                                    "title" => $title,
                                    "subtitle" => $subtitle,
                                    "user_id" => $userid,
                                    "post_id" => $postid,
                                    "url" => $url,
                                    "event_type" => $type,
                                    "event_level" => $level,
                                    "origin" => $origin,
                                    "date" => $date
                                ),
                                array("%s", "%s", "%d", "%d", "%s", "%s", "%s", "%s", "%s")
                            );
        return $sql !== false;
    }

    /**
     * Deletes all of a user's events from the database table
     * 
     * @param Int User-ID
     */
    public function deleteAllEventsForUser($userid){
        global $wpdb;
        
		$table_name = $wpdb->prefix . self::$dbprefix;
        
		$wpdb->delete( $table_name, array( 'user_id' => $userid ) );
    }

    public function getLoginHistory(){
        global $wpdb;

        $table_user_events = $wpdb->prefix . self::$dbprefix;
        $table_users = $wpdb->users;
        $currentDate = date("Y-m-d");
        $currentMinDate = $currentDate." 00:00:00";

        $sql = $wpdb->prepare("SELECT users.user_login, users.ID, userevents.date 
                                FROM $table_user_events AS userevents
                                INNER JOIN $table_users AS users
                                ON userevents.user_id = users.ID
                                WHERE userevents.event_type = 'login' AND userevents.event_level = 'info' AND userevents.date > %s ORDER BY userevents.date DESC
        ", $currentMinDate);
        $result = $wpdb->get_results($sql, ARRAY_N);

        return $result;
    }

	/**
	 * Returns the settings to be displayed on the settings page
	 * 
	 * @return Array
	 */
	public function getSettings() {

		return get_option( self::$userEventSettingsKey, array());
	}

    /**
     * Saves the settings into the wp options
     * 
     * @param Array
     */
    public function saveSettings($data) {
        return update_option( self::$userEventSettingsKey, $data );
    }

    /**
     * Returns Events for a given User and a single Post - Helper for function underneath
     * 
     * @param Integer User-ID
     * @param Integer Post-ID
     * @param optional Integer Limit - return only latest x events
     * 
     * @return Array of Trainingssystem_Plugin_Database_User_Event Objects
     */
    public function getEventsForUserByPost($userid, $postid, $limit = null) {
        return $this->getEventsForUserByPosts($userid, array($postid), $limit);
    }
    
    /**
     * Returns Events for a given User and a set of Posts
     * 
     * @param Integer User-ID
     * @param Array Post-IDs
     * @param optional Integer Limit - return only latest x events
     * 
     * @return Array of Trainingssystem_Plugin_Database_User_Event Objects
     */
    public function getEventsForUserByPosts($userid, $postids, $limit = null) {
        global $wpdb;

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

        $sql = "SELECT * FROM $table_name WHERE user_id = %d AND post_id IN (" . implode(",", $postids) . ") ORDER BY date DESC";
        if(!is_null($limit) && is_numeric($limit)) {
            $sql .= " LIMIT " . $limit;
        }
        $sql .= ";";

        $data = $wpdb->get_results($wpdb->prepare($sql, $userid));

        $result = [];

        foreach ($data as $row) {
            $result[] = new Trainingssystem_Plugin_Database_User_Event($row->id, $row->title, $row->subtitle, $row->user_id, $row->post_id, $row->url, $row->event_type, $row->event_level, $row->origin, $row->date);
        }
        
        return $result;
    }

    /**
     * Deletes User-Events for a given User.
     * Filtered by level and type
     * 
     * @param Integer User-ID
     * @param Array Types
     * @param Array Levels
     * 
     * @return boolean success
     */
    public function deleteUserEvents($userid, $type, $level) {
        foreach($type as $t) {
            if(!in_array($t, self::$types)) {
                return false;
            }
        }

        foreach($level as $l) {
            if(!in_array($l, self::$level)) {
                return false;
            }
        }

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

        $sql = "DELETE FROM $table_name WHERE user_id = %d AND event_type IN ('" . implode("', '", $type) . "') AND event_level IN ('" . implode("', '", $level) . "');";
        $prepared = $wpdb->prepare($sql, $userid);
        
        return $wpdb->query($prepared) !== false;
    }

    /**
     * Clears the User Event table by remoing old entries
     * 
     * @param String type
     * @param String Level
     * @param Integer days
     * 
     * @return boolean success
     */
    public function deleteUserEventsOlderThan($type, $level, $days) {
        if(!in_array($type, self::$types) || !in_array($level, self::$level) || !is_numeric($days) || $days < 1) {
            return false;
        }

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

        $sql = "DELETE FROM $table_name WHERE event_type = '$type' AND event_level = '$level' AND DATEDIFF(NOW(), date) >= $days;";
        
        return $wpdb->query($sql) !== false;
    }

    /**
     * Deletes all User Events for a post
     * 
     * @param Integer Post-ID
     * 
     * @return boolean success
     */
    public function deleteUserEventsByPost($postid) {
        global $wpdb;
        
		$table_name = $wpdb->prefix . self::$dbprefix;

        return $wpdb->delete( $table_name, array( 'post_id' => $postid ) ) !== false;
    }
}
