<?php
/**
 * Functions for the user log Table
 *
 * @author     Markus Domin, Helge Nissen; further adjustments by Tim Mallwitz, Max Sternitzke
 */
class Trainingssystem_Plugin_Database_Userlogs_Daoimple
{
    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_USER_LOGS;

    /**
     * Initialize the collections used to maintain the actions and filters.
     *
     * @since    1.0.0
     */
    public function __construct()
    {
        $this->iniDB();
    }

    /**
     * Initialize the collections used to maintain the actions and filters.
     *
     * @since    1.0.0
     */
    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 INT(11) NOT NULL AUTO_INCREMENT ,
                userid INT (11) NOT NULL ,
                browser_name VARCHAR(50) NOT NULL ,
                browser_version VARCHAR (100) NOT NULL,
                device_type  VARCHAR (100) NOT NULL,
                engine_name  VARCHAR (100) NOT NULL,
                os_name  VARCHAR (100) NOT NULL,
                os_version  VARCHAR (100)NOT NULL,
                device_model  VARCHAR (100) NOT NULL,
                device_manufaktur  VARCHAR (100)NOT NULL,
                pageid  BIGINT(20) NOT NULL,
                uri  VARCHAR(100) NOT NULL,
                date_time DATETIME NOT NULL,
                referred   VARCHAR(100) NOT NULL,
                ip  VARCHAR(100) NOT NULL,
                duration BIGINT(20) NOT NULL,
                PRIMARY KEY (id)
                )$charset_collate;",
            "UPDATE $table_name SET ip = '' WHERE 1=1;",
            "ALTER TABLE $table_name ADD INDEX `" . $table_name . "_userid` (`userid`) USING BTREE;",
            "ALTER TABLE $table_name ADD INDEX `" . $table_name . "_pageid` (`pageid`) USING BTREE;",
        ];
        Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix, $table_name, $tableversionsstr);
    }

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

    }
    /**
     * insert user data in database
     *
     * @since    1.0.0
     */
    public function save(Trainingssystem_Plugin_Database_Userlogs $userdata)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        $sql = $wpdb->insert(
            $table_name,
            array(
                'browser_name'      => $userdata->getBrowserName(),
                'date_time'         => current_time('mysql'),
                'userid'            => get_current_user_id(),
                'browser_version'   => $userdata->getBrowserVersion(),
                'device_type'       => $userdata->getDeviceType(),
                'engine_name'       => $userdata->getEngineName(),
                'os_name'           => $userdata->getOsName(),
                'os_version'        => $userdata->getOsVersion(),
                'device_model'      => $userdata->getDeviceModel(),
                'device_manufaktur' => $userdata->getDeviceManufaktur(),
                'pageid'            => $userdata->getPageid(),
                'uri'               => $userdata->getUri(),
                'referred'          => $userdata->getReferred(),
                'ip'                => $userdata->getIp(),
                'duration'          => $userdata->getDuration(),
            ),
            array(
                '%s',
                '%s',
                '%d',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%d',
                '%s',
                '%s',
                '%s',
                '%d',
            )
        );
        if ($sql === 1) {
            return $wpdb->insert_id;
        } else {
            return null;
        }
    }

    public function updateTime($logid, $userid, $time)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        return $wpdb->update(
            $table_name,
            array("duration" => $time),
            array("userid" => $userid, "id" => $logid),
            array("%d"),
            array("%d", "%d")
        ) !== false;
    }

    public function getUserLog($logid, $userid)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        $data       = $wpdb->get_results($wpdb->prepare("SELECT * FROM $table_name WHERE id = %d and userid = %d LIMIT 1", array($logid, $userid)), OBJECT);
        $result     = null;
        foreach ($data as $row) {
            $result = new Trainingssystem_Plugin_Database_Userlogs($row->id, $row->browser_name, $row->browser_version, $row->device_type, $row->engine_name, 
                                                                    $row->os_name, $row->os_version, $row->device_model, $row->device_manufaktur, $row->pageid,
                                                                    $row->uri, $row->date_time, $row->referred, $row->ip, $row->userid, $row->duration);
        }
        return $result;
    }

    public function countHour($userid = null)
    {
        return $this->getColumnEntries("date_time", false, $userid, true);
    }

    public function sumHour($userid = null)
    {
        return $this->getColumnEntries("date_time", true, $userid, true);
    }

    public function countWeekday($userid = null)
    {
        return $this->getColumnEntries("date_time", false, $userid, false, true);
    }

    public function sumWeekday($userid = null)
    {
        return $this->getColumnEntries("date_time", true, $userid, false, true);
    }

    public function countPageView($userid = null)
    {
        return $this->getColumnEntries("pageid", false, $userid, false, false, true);
    }
    public function sumPageView($userid = null)
    {
        return $this->getColumnEntries("pageid", true, $userid, false, false, true);
    }

    public function countBrowser($userid = null)
    {
        return $this->getColumnEntries("browser_name", false, $userid, false, false, false);
    }

    public function countDevice($userid = null)
    {
        return $this->getColumnEntries("device_type", false, $userid, false, false, false);
    }

    // Funktionen, um die Statistiken/Daten für vom Benutzer gewählte Zeiträume zu generieren
    public function countHourInterval($userid = null, $startDate = null, $endDate = null)
    {
        return $this->getColumnEntriesInterval("date_time", false, $userid, true, false, false, $startDate, $endDate);
    }

    public function sumHourInterval($userid = null, $startDate = null, $endDate = null)
    {
        return $this->getColumnEntriesInterval("date_time", true, $userid, true, false, false, $startDate, $endDate);
    }

    public function countWeekdayInterval($userid = null, $startDate = null, $endDate = null)
    {
        return $this->getColumnEntriesInterval("date_time", false, $userid, false, true, false, $startDate, $endDate);
    }

    public function sumWeekdayInterval($userid = null, $startDate = null, $endDate = null)
    {
        return $this->getColumnEntriesInterval("date_time", true, $userid, false, true, false, $startDate, $endDate);
    }

    public function countPageViewInterval($userid = null, $startDate = null, $endDate = null)
    {
        return $this->getColumnEntriesInterval("pageid", false, $userid, false, false, true, $startDate, $endDate);
    }

    public function sumPageViewInterval($userid = null, $startDate = null, $endDate = null)
    {
        return $this->getColumnEntriesInterval("pageid", true, $userid, false, false, true, $startDate, $endDate);
    }

    public function countBrowserInterval($userid = null, $startDate = null, $endDate = null)
    {
        return $this->getColumnEntriesInterval("browser_name", false, $userid, false, false, false, $startDate, $endDate);
    }

    public function countDeviceInterval($user_id = null, $startDate = null, $endDate = null)
    {
        return $this->getColumnEntriesInterval("device_type", false, $user_id, false, false, false, $startDate, $endDate);
    }

    private function getColumnEntries($colname, $sum = false, $userid = null, $hour = false, $weekday = false, $pageview = false)
    {
        global $wpdb;

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

        //$sqlstraction = "$colname as label, COUNT(*) AS anzahl FROM $table_name where duration = 0";
        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        $sqlstraction = "$colname as label, COUNT(*) AS anzahl FROM $table_name where duration > 0";
        if ($sum) {
            $sqlstraction = "$colname as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        if ($hour && $sum) {
            $sqlstraction = "HOUR($colname) as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        //if ($hour && !$sum ) $sqlstraction= "HOUR($colname) as label, COUNT(*) as anzahl  FROM $table_name where duration = 0";
        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($hour && !$sum) {
            $sqlstraction = "HOUR($colname) as label, COUNT(*) as anzahl  FROM $table_name where duration > 0";
        }

        if ($weekday && $sum) {
            $sqlstraction = "WEEKDAY($colname) as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        //if ($weekday && !$sum ) $sqlstraction= "WEEKDAY($colname) as label, COUNT(*) as anzahl  FROM $table_name where duration = 0";
        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($weekday && !$sum) {
            $sqlstraction = "WEEKDAY($colname) as label, COUNT(*) as anzahl  FROM $table_name where duration > 0";
        }

        if ($pageview && $sum) {
            $sqlstraction = "posts.post_title as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name left join $wpdb->posts as posts on posts.ID = pageid where duration != 0";
        }

        //if($pageview && !$sum) $sqlstraction = "posts.post_title as label, COUNT(*) as anzahl  FROM $table_name left join $wpdb->posts as posts on posts.ID = pageid where duration = 0";
        // // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($pageview && !$sum) {
            $sqlstraction = "posts.post_title as label, COUNT(*) as anzahl  FROM $table_name left join $wpdb->posts as posts on posts.ID = pageid where duration > 0";
        }

        $sqlpre = "SELECT $sqlstraction GROUP BY $colname";
        if ($userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY $colname", $userid);
        }

        if ($hour && $userid == null) {
            $sqlpre = "SELECT $sqlstraction GROUP BY HOUR($colname)";
        }

        if ($hour && $userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY HOUR($colname)", $userid);
        }

        if ($weekday && $userid == null) {
            $sqlpre = "SELECT $sqlstraction GROUP BY WEEKDAY($colname)";
        }

        if ($weekday && $userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY WEEKDAY($colname)", $userid);
        }

        $data = $wpdb->get_results($sqlpre, OBJECT);

        $label  = array();
        $values = array();
        foreach ($data as $value) {
            $label[]  = $value->label;
            $values[] = $value->anzahl;

        }
        $result = ["labels" => $label, "values" => $values];
        return $result;

    }

    public function resetUserLogs()
    {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        //print_r($table_name);

        $wpdb->query(
            "DELETE FROM $table_name"
        );

    }

    // Funktionen, um den Wertebereich der beiden Säulendiagramme "Benutzeraktivität (gemessen in Seitenaufrufe/Seite)" und "Benutzeraktivität (in Sekunden/Seite)" einzugrenzen
    public function countPageViewRangeOfValues($user_id = null, $startDate = null, $endDate = null, $startValue = null, $endValue = null)
    {
        return $this->getColoumnEntriesRangeOfValues("pageid", false, $user_id, false, false, true, $startDate, $endDate, $startValue, $endValue);
    }

    public function sumPageViewRangeOfValues($user_id = null, $startDate = null, $endDate = null, $startValue = null, $endValue = null)
    {
        return $this->getColoumnEntriesRangeOfValues("pageid", true, $user_id, false, false, true, $startDate, $endDate, $startValue, $endValue);
    }

    // Daten/Statistiken für vom Benutzer ausgewählte Zeiträume und Wertebereiche
    private function getColoumnEntriesRangeOfValues($colname, $sum = false, $userid = null, $hour = false, $weekday = false, $pageview = false, $startDate = null, $endDate = null, $startValue = null, $endValue = null)
    {
        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;

        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        $sqlstraction = "$colname as label, COUNT(*) AS anzahl FROM $table_name where duration > 0";
        if ($sum) {
            $sqlstraction = "$colname as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        if ($hour && $sum) {
            $sqlstraction = "HOUR($colname) as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($hour && !$sum) {
            $sqlstraction = "HOUR($colname) as label, COUNT(*) as anzahl  FROM $table_name where duration > 0";
        }

        if ($weekday && $sum) {
            $sqlstraction = "WEEKDAY($colname) as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($weekday && !$sum) {
            $sqlstraction = "WEEKDAY($colname) as label, COUNT(*) as anzahl  FROM $table_name where duration > 0";
        }

        if ($pageview && $sum) {
            $sqlstraction = "posts.post_title as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name left join $wpdb->posts as posts on posts.ID = pageid where duration != 0";
        }

        // // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($pageview && !$sum) {
            $sqlstraction = "posts.post_title as label, COUNT(*) as anzahl  FROM $table_name left join $wpdb->posts as posts on posts.ID = pageid where duration > 0";
        }

        // Zeiträume einfügen
        if ($startDate !== null && $endDate === null) {
            $sqlstraction = $wpdb->prepare("$sqlstraction and date_time >= '%s'", $startDate);
        } else if ($startDate === null && $endDate !== null) {
            $sqlstraction = $wpdb->prepare("$sqlstraction and date_time <= '%s'", $endDate);
        } else if ($startDate !== null && $endDate !== null) {
            $sqlstraction = $wpdb->prepare("$sqlstraction and date_time >= '%s' and date_time <= '%s'", $startDate, $endDate);
        }

        $sqlpre = "SELECT $sqlstraction GROUP BY $colname";
        if ($userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY $colname", $userid);
        }

        if ($hour && $userid == null) {
            $sqlpre = "SELECT $sqlstraction GROUP BY HOUR($colname)";
        }

        if ($hour && $userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY HOUR($colname)", $userid);
        }

        if ($weekday && $userid == null) {
            $sqlpre = "SELECT $sqlstraction GROUP BY WEEKDAY($colname)";
        }

        if ($weekday && $userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY WEEKDAY($colname)", $userid);
        }

        // Gewählten Wertebereich einfügen
        if ($startValue !== null && $endValue === null) {
            $sqlpre = $wpdb->prepare("$sqlpre HAVING anzahl >= %d", $startValue);
        } else if ($startValue === null && $endValue !== null) {
            $sqlpre = $wpdb->prepare("$sqlpre HAVING anzahl <= %d", $endValue);
        } else if ($startValue !== null && $endValue !== null) {
            $sqlpre = $wpdb->prepare("$sqlpre HAVING anzahl >= %d AND anzahl <= %d", $startValue, $endValue);
        }

        $data = $wpdb->get_results($sqlpre, OBJECT);

        $label  = array();
        $values = array();
        foreach ($data as $value) {
            $label[]  = $value->label;
            $values[] = $value->anzahl;

        }
        $result = ["labels" => $label, "values" => $values];
        return $result;

    }

    // Daten/Statistiken für vom Benutzer ausgewählte Zeiträume
    private function getColumnEntriesInterval($colname, $sum = false, $userid = null, $hour = false, $weekday = false, $pageview = false, $startDate = null, $endDate = null)
    {
        global $wpdb;

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

        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        $sqlstraction = "$colname as label, COUNT(*) AS anzahl FROM $table_name where duration > 0";
        if ($sum) {
            $sqlstraction = "$colname as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        if ($hour && $sum) {
            $sqlstraction = "HOUR($colname) as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($hour && !$sum) {
            $sqlstraction = "HOUR($colname) as label, COUNT(*) as anzahl  FROM $table_name where duration > 0";
        }

        if ($weekday && $sum) {
            $sqlstraction = "WEEKDAY($colname) as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name where duration != 0";
        }

        // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($weekday && !$sum) {
            $sqlstraction = "WEEKDAY($colname) as label, COUNT(*) as anzahl  FROM $table_name where duration > 0";
        }

        if ($pageview && $sum) {
            $sqlstraction = "posts.post_title as label, ROUND((SUM(duration)/1000),0) as anzahl  FROM $table_name left join $wpdb->posts as posts on posts.ID = pageid where duration != 0";
        }

        // // Alle Daten ohne Chrome, da duration aktuell hier nicht/nicht korrekt gemessen wird
        if ($pageview && !$sum) {
            $sqlstraction = "posts.post_title as label, COUNT(*) as anzahl  FROM $table_name left join $wpdb->posts as posts on posts.ID = pageid where duration > 0";
        }

        // Zeiträume einfügen
        if ($startDate !== null && $endDate === null) {
            $sqlstraction = $wpdb->prepare("$sqlstraction and date_time >= '%s'", $startDate);
        } else if ($startDate === null && $endDate !== null) {
            $sqlstraction = $wpdb->prepare("$sqlstraction and date_time <= '%s'", $endDate);
        } else if ($startDate !== null && $endDate !== null) {
            $sqlstraction = $wpdb->prepare("$sqlstraction and date_time >= '%s' and date_time <= '%s'", $startDate, $endDate);
        }

        $sqlpre = "SELECT $sqlstraction GROUP BY $colname";
        if ($userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY $colname", $userid);
        }

        if ($hour && $userid == null) {
            $sqlpre = "SELECT $sqlstraction GROUP BY HOUR($colname)";
        }

        if ($hour && $userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY HOUR($colname)", $userid);
        }

        if ($weekday && $userid == null) {
            $sqlpre = "SELECT $sqlstraction GROUP BY WEEKDAY($colname)";
        }

        if ($weekday && $userid != null) {
            $sqlpre = $wpdb->prepare("SELECT $sqlstraction and userid = %d GROUP BY WEEKDAY($colname)", $userid);
        }

        $data = $wpdb->get_results($sqlpre, OBJECT);

        $label  = array();
        $values = array();
        foreach ($data as $value) {
            $label[]  = $value->label;
            $values[] = $value->anzahl;

        }
        $result = ["labels" => $label, "values" => $values];
        return $result;
    }

    public function getLastActivityBeforeLastLogin($userid)
    {

        global $wpdb;
        $table_userlogs  = $wpdb->prefix . $this::$dbprefix;
        $table_lastlogin = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->LastLoginDao::$dbprefix;

        $sql = $wpdb->prepare("SELECT MAX(ul.date_time) AS lastActivity
                                FROM $table_userlogs ul INNER JOIN $table_lastlogin ll ON ul.userid = ll.user_id
                                WHERE ll.user_id = %d AND ll.lastlogin > ul.date_time", $userid);

        $result       = $wpdb->get_row($sql);
        $lastActivity = $result->lastActivity;

        return $lastActivity;
    }

    public function deleteUserInput($user_id)
    {
        global $wpdb;
        $datatable = $wpdb->prefix . $this::$dbprefix;
        $wpdb->delete($datatable, array('userid' => $user_id));
    }

}
