<?php

/**
 * Class to handle all db operations
 * This class will have CRUD methods for database tables
 *
 * @author Enver Suljic
 */
class DbHandler
{

    private $conn;
    private $prefix;

    function __construct()
    {
        require_once dirname(__FILE__) . '/DbConnect.php';

        // opening db connection
        $db = new DbConnect();
        $this->conn = $db->connect();

        $prefixFile = fopen("../databasePrefix.txt", "r");

        if ($prefixFile == null)
            return false;

        if (!filesize("../databasePrefix.txt") == 0)
            $this->prefix = fread($prefixFile, filesize("../databasePrefix.txt"));

        fclose($prefixFile);
    }

    /* ------------- Index ------------------ */

    public function checkDatabase()
    {
        $prefixFile = fopen("../databasePrefix.txt", "r") or die("Cannot find databasePrefix.txt in root directory!");

        if ($prefixFile == null)
            return false;

        $stmt = $this->conn->query("SHOW TABLES");
        $tables = mysqli_fetch_array($stmt);

        if (!filesize("../databasePrefix.txt") == 0)
            $this->prefix = fread($prefixFile, filesize("../databasePrefix.txt"));

        fclose($prefixFile);

        if (count($tables) < 1)
            return false;
        else if (filesize("../databasePrefix.txt") == 0)
            return true;

        return strpos($tables[0], $this->prefix) !== false;
    }

    public function makeNewDatabase($prefix)
    {
        $sqlQueryFile = fopen("../database.sql", "r") or die("Cannot find database.sql in root directory!");

        $sqlQuery = fread($sqlQueryFile, filesize("../database.sql"));

        fclose($sqlQueryFile);

        $sqlQuery = str_replace("db_", $prefix, $sqlQuery);

        $this->conn->multi_query($sqlQuery) or die("Database error!");

        file_put_contents('../databasePrefix.txt', $prefix);

        $this->prefix = $prefix;
    }

    /* ------------- Main methods ------------------ */

    private function isUserExists($username, $email) {
        $stmt = $this->conn->prepare("SELECT Id FROM " . $this->prefix . "users WHERE Username = ? OR (Email = ? AND Email != NULL)");
        $stmt->bind_param('ss', $username, $email);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    public function checkLogin($username, $password)
    {
        $passwordHash = "";

        // fetching user by username
        $stmt = $this->conn->prepare("SELECT PasswordHash FROM " . $this->prefix . "users WHERE Username = ?");

        $stmt->bind_param("s", $username);

        $stmt->execute();

        $stmt->bind_result($passwordHash);

        $stmt->store_result();

        if ($stmt->num_rows > 0) {
            // Found user with the username
            // Now verify the password

            $stmt->fetch();

            $stmt->close();

            if (PassHash::check_password($passwordHash, $password)) {
                // User password is correct
                return TRUE;

            } else {
                // user password is incorrect
                return FALSE;
            }
        } else {
            $stmt->close();

            // user not existed with the username
            return FALSE;
        }
    }

    public function logSession($userId, $ipAddress, $deviceInfo)
    {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "sessions(UserId, IpAddress, DeviceInfo) VALUES (?, ?, ?)");

        $stmt->bind_param("sss", $userId, $ipAddress, $deviceInfo);

        $stmt->execute();

        $sessionId = $stmt->insert_id;

        $stmt->close();

        return $sessionId;
    }

    public function getUserByUsername($username)
    {
        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "users WHERE username = ?");
        $stmt->bind_param("s", $username);
        if ($stmt->execute()) {
            $user = array();
            $user = $stmt->get_result()->fetch_assoc();
            $stmt->close();
            return $user;
        } else {
            return NULL;
        }
    }

    public function getUserById($userId)
    {
        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "users WHERE Id = ?");
        $stmt->bind_param("s", $userId);
        if ($stmt->execute()) {
            $user = array();
            $user = $stmt->get_result()->fetch_assoc();
            $stmt->close();
            return $user;
        } else {
            return NULL;
        }
    }

    /*
     * Validating user api key
     * If the api key is there in db, it is a valid key
     * @param String $apiKey user api key
     * @return boolean
     */
    public function isValidApiKey($apiKey) {
        $stmt = $this->conn->prepare("SELECT id from " . $this->prefix . "users WHERE ApiKey = ?");
        $stmt->bind_param("s", $apiKey);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();
        return $num_rows > 0;
    }

    /*
     * Fetching user id by api key
     * @param String $apiKey user api key
     * @return null
     */
    public function getUserId($apiKey) {
        $stmt = $this->conn->prepare("SELECT id from " . $this->prefix . "users WHERE ApiKey = ?");
        $stmt->bind_param("s", $apiKey);
        if ($stmt->execute()) {
            $user_id = -1;
            $stmt->bind_result($user_id);
            $stmt->fetch();
            $stmt->close();
            return $user_id;
        } else {
            return NULL;
        }
    }

    /*
     * Fetching all users that scored something in descending order
     */
    public function getLeaderBoard() {
        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "users WHERE Score > 0 ORDER BY (Score / (QuestionCount * AverageTime)) DESC");

        $users = array();

        $stmt->execute();

        $result = $stmt->get_result();

        while($user = $result->fetch_assoc())
            array_push($users, $user);

        $stmt->close();
        return $users;
    }

    /*
     * Fetching all users
     */
    public function getAllUsers() {
        $stmt = $this->conn->prepare("SELECT *, (NOW() - LastOnlineTimeStamp) AS OnlineAgo FROM " . $this->prefix . "users");

        $users = array();

        $stmt->execute();

        $result = $stmt->get_result();

        while($user = $result->fetch_assoc())
            array_push($users, $user);

        $stmt->close();
        return $users;
    }

    /*
     * Fetching all categories
     */
    public function getCategoriesToBlock($forUserId) {
        $stmt = $this->conn->prepare("SELECT DISTINCT catgs.* FROM " . $this->prefix . "categories AS catgs WHERE catgs.Id NOT IN(SELECT catgsBlocks.CategoryId FROM " . $this->prefix . "categoriesblocks AS catgsBlocks WHERE catgsBlocks.UserId = ?)");

        $stmt->bind_param("s", $forUserId);

        $users = array();

        $stmt->execute();

        $result = $stmt->get_result();

        while($category = $result->fetch_assoc())
            array_push($users, $category);

        $stmt->close();
        return $users;
    }

    /*
     * Fetching all categories
     */
    public function blockCategoryForUser($forUserId, $categoryId) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "categoriesblocks(UserId, CategoryId) values(?, ?)");
        $stmt->bind_param("ss", $forUserId, $categoryId);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Fetching all categories
     */
    public function getCategoriesToUnblock($forUserId) {
        $stmt = $this->conn->prepare("SELECT DISTINCT catgs.* FROM " . $this->prefix . "categories AS catgs WHERE catgs.Id IN(SELECT catgsBlocks.CategoryId FROM " . $this->prefix . "categoriesblocks AS catgsBlocks WHERE catgsBlocks.UserId = ?)");

        $stmt->bind_param("s", $forUserId);

        $users = array();

        $stmt->execute();

        $result = $stmt->get_result();

        while($category = $result->fetch_assoc())
            array_push($users, $category);

        $stmt->close();
        return $users;
    }

    /*
     * Fetching all categories
     */
    public function unblockCategoryForUser($forUserId, $categoryId) {
        $stmt = $this->conn->prepare("DELETE FROM " . $this->prefix . "categoriesblocks WHERE UserId = ? AND CategoryId = ?");
        $stmt->bind_param("ss", $forUserId, $categoryId);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Remove single user
     */
    public function removeUser($userId) {
        $stmt = $this->conn->prepare("DELETE FROM " . $this->prefix . "users WHERE Id = ?");
        $stmt->bind_param("s", $userId);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Fetching all questions
     */
    public function getAllQuestions() {
        $stmt = $this->conn->prepare("SELECT qs.*, catgs.Title FROM " . $this->prefix . "questions AS qs INNER JOIN " . $this->prefix . "categories AS catgs ON qs.CategoryId = catgs.Id");

        $users = array();

        $stmt->execute();

        $result = $stmt->get_result();

        while($category = $result->fetch_assoc())
            array_push($users, $category);

        $stmt->close();
        return $users;
    }

    /*
     * Fetching all categories
     */
    public function getAllCategories() {
        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "categories");

        $users = array();

        $stmt->execute();

        $result = $stmt->get_result();

        while($category = $result->fetch_assoc())
            array_push($users, $category);

        $stmt->close();
        return $users;
    }

    /*
     * Add new category
     */
    public function addCategory($Title) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "categories(Title) VALUES(?)");
        $stmt->bind_param("s", $Title);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Fetching all models
     */
    public function getAllModels() {
        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "models");

        $users = array();

        $stmt->execute();

        $result = $stmt->get_result();

        while($category = $result->fetch_assoc())
            array_push($users, $category);

        $stmt->close();
        return $users;
    }

    /*
     * Add new model
     */
    public function addModel($Title) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "models(Title) VALUES(?)");
        $stmt->bind_param("s", $Title);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Fetching all technologies
     */
    public function getAllTechnologies() {
        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "technologies");

        $users = array();

        $stmt->execute();

        $result = $stmt->get_result();

        while($category = $result->fetch_assoc())
            array_push($users, $category);

        $stmt->close();
        return $users;
    }

    /*
     * Add new technology
     */
    public function addTechnology($Title) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "technologies(Title) VALUES(?)");
        $stmt->bind_param("s", $Title);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Add new question
     */
    public function addQuestion($CategoryId, $ModelId, $TechnologyId, $DifficultyLevel, $QuestionText, $Option1, $Option2, $Option3, $Option4, $RightOption) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "questions(CategoryId, ModelId, TechnologyId, DifficultyLevel, QuestionText, Option1, Option2, Option3, Option4, RightOption) VALUES(?,?,?,?,?,?,?,?,?,?)");
        $stmt->bind_param("ssssssssss", $CategoryId, $ModelId, $TechnologyId, $DifficultyLevel, $QuestionText, $Option1, $Option2, $Option3 == "NULL" ? NULL : $Option3, $Option4 == "NULL" ? NULL : $Option4, $RightOption);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Edit question
     */
    public function editQuestion($Id, $CategoryId, $ModelId, $TechnologyId, $DifficultyLevel, $QuestionText, $Option1, $Option2, $Option3, $Option4, $RightOption) {
        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "questions SET CategoryId = ?, ModelId = ?, TechnologyId = ?, DifficultyLevel = ?, QuestionText = ?, Option1 = ?, Option2 = ?, Option3 = ?, Option4 = ?, RightOption = ? WHERE Id = ?");
        $stmt->bind_param("sssssssssss", $CategoryId, $ModelId, $TechnologyId, $DifficultyLevel, $QuestionText, $Option1, $Option2, $Option3, $Option4, $RightOption, $Id);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Remove question
     */
    public function removeQuestion($Id) {
        $stmt = $this->conn->prepare("DELETE FROM " . $this->prefix . "questions WHERE Id = ?");
        $stmt->bind_param("s", $Id);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Add new user
     * */
    public function addUser($username, $accessLevel, $email, $password, $trackLocation, $saveMessages, $logCalls) {
        require_once 'PassHash.php';
        $response = array();

        // First check if user already existed in db
        if (!$this->isUserExists($username, $email)) {
            // Generating password hash
            $passwordHash = PassHash::hash($password);

            // Generating API key
            $apiKey = generateApiKey();

            // insert query
            $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "users(ApiKey, Email, PasswordHash, Username, AccessLevel, TrackLocation, SaveMessages, LogCalls) values(?, ?, ?, ?, ?, ?, ?, ?)");
            $stmt->bind_param("ssssssss", $apiKey, $email == "NULL" ? NULL : $email, $passwordHash, $username, $accessLevel, $trackLocation, $saveMessages, $logCalls);

            $result = $stmt->execute();

            $stmt->close();

            // Check for successful insertion
            if ($result) {
                // User successfully inserted
                $response["error"] = false;
                $response["message"] = "New user added!";
                $response["code"] = 600;
            } else {
                // Failed to create user
                $response["error"] = true;
                $response["message"] = "Oops! An error occurred while adding new user!";
                $response["code"] = 601;
            }
        } else {
            // User with same email already existed in the db
            $response["error"] = true;
            $response["message"] = "Sorry, this email or username already existed";
            $response["code"] = 602;
        }

        return $response;
    }

    /*
     * Edit user
     * */
    public function editUser($Id, $accessLevel, $email, $password, $maxDifficultyLevel, $trackLocation, $saveMessages, $logCalls) {
        require_once 'PassHash.php';
        $response = array();

        // Generating password hash
        $passwordHash = PassHash::hash($password);

        // insert query
        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "users SET Email = ?, " . ($password == "NotChanged" ? "" : "PasswordHash = ?, ") . "AccessLevel = ?, MaxDifficultyLevel = ?, TrackLocation = ?, SaveMessages = ?, LogCalls = ? WHERE Id = ?");

        if($password == "NotChanged")
            $stmt->bind_param("sssssss", $email == "NULL" ? NULL : $email, $accessLevel, $maxDifficultyLevel, $trackLocation, $saveMessages, $logCalls, $Id);
        else
            $stmt->bind_param("ssssssss", $email == "NULL" ? NULL : $email, $passwordHash, $accessLevel, $maxDifficultyLevel, $trackLocation, $saveMessages, $logCalls, $Id);

        $result = $stmt->execute();

        $stmt->close();

        // Check for successful insertion
        if ($result) {
            // User successfully inserted
            $response["error"] = false;
            $response["message"] = "You are successfully registered";
            $response["code"] = 600;
        } else {
            // Failed to create user
            $response["error"] = true;
            $response["code"] = 601;
        }

        return $response;
    }

    /*
     * Rename category
     */
    public function renameCategory($Id, $NewTitle) {

        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "categories WHERE Title = ?");
        $stmt->bind_param("s", $NewTitle);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();

        $response = array();

        if ($num_rows > 0) {
            $response["error"] = true;
            $response["message"] = "There is such title already!";
        } else {
            $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "categories SET Title = ? WHERE Id = ?");
            $stmt->bind_param("ss", $NewTitle, $Id);
            $stmt->execute();
            $stmt->close();

            $response["error"] = false;
        }

        return $response;
    }

    /*
     * Enable or disable category
     */
    public function enableCategory($Id, $Enabled) {
        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "categories SET Enabled = ? WHERE Id = ?");
        $stmt->bind_param("ss", $Enabled, $Id);
        $stmt->execute();
        $stmt->close();
    }

    /*
     * Remove category
     */
    public function removeCategory($Id) {
        $stmt = $this->conn->prepare("DELETE FROM " . $this->prefix . "categories WHERE Id = ?");
        $stmt->bind_param("s", $Id);

        $stmt->execute();

        $stmt->close();

        $stmt = $this->conn->prepare("DELETE FROM qQuestions WHERE CategoryId = ?");
        $stmt->bind_param("s", $Id);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Rename model
     */
    public function renameModel($Id, $NewTitle) {

        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "models WHERE Title = ?");
        $stmt->bind_param("s", $NewTitle);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();

        $response = array();

        if ($num_rows > 0) {
            $response["error"] = true;
            $response["message"] = "There is such title already!";
        } else {
            $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "models SET Title = ? WHERE Id = ?");
            $stmt->bind_param("ss", $NewTitle, $Id);
            $stmt->execute();
            $stmt->close();

            $response["error"] = false;
        }

        return $response;
    }

    /*
     * Enable or disable model
     */
    public function enableModel($Id, $Enabled) {
        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "models SET Enabled = ? WHERE Id = ?");
        $stmt->bind_param("ss", $Enabled, $Id);
        $stmt->execute();
        $stmt->close();
    }

    /*
     * Remove model
     */
    public function removeModel($Id) {
        $stmt = $this->conn->prepare("DELETE FROM " . $this->prefix . "model WHERE Id = ?");
        $stmt->bind_param("s", $Id);

        $stmt->execute();

        $stmt->close();

        $stmt = $this->conn->prepare("DELETE FROM " . $this->prefix . "questions WHERE ModelId = ?");
        $stmt->bind_param("s", $Id);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Rename technology
     */
    public function renameTechnology($Id, $NewTitle) {

        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "technologies WHERE Title = ?");
        $stmt->bind_param("s", $NewTitle);
        $stmt->execute();
        $stmt->store_result();
        $num_rows = $stmt->num_rows;
        $stmt->close();

        $response = array();

        if ($num_rows > 0) {
            $response["error"] = true;
            $response["message"] = "There is such title already!";
        } else {
            $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "technologies SET Title = ? WHERE Id = ?");
            $stmt->bind_param("ss", $NewTitle, $Id);
            $stmt->execute();
            $stmt->close();

            $response["error"] = false;
        }

        return $response;
    }

    /*
     * Enable or disable technology
     */
    public function enableTechnology($Id, $Enabled) {
        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "technologies SET Enabled = ? WHERE Id = ?");
        $stmt->bind_param("ss", $Enabled, $Id);
        $stmt->execute();
        $stmt->close();
    }

    /*
     * Remove technology
     */
    public function removeTechnology($Id) {
        $stmt = $this->conn->prepare("DELETE FROM " . $this->prefix . "tecnologies WHERE Id = ?");
        $stmt->bind_param("s", $Id);

        $stmt->execute();

        $stmt->close();

        $stmt = $this->conn->prepare("DELETE FROM " . $this->prefix . "questions WHERE TechnologyId = ?");
        $stmt->bind_param("s", $Id);

        $stmt->execute();

        $stmt->close();
    }

    /*
     * Get question available for a user
     */
    public function getQuestion($userId) {
        $stmt = $this->conn->prepare("SELECT ques.* FROM " . $this->prefix . "questions as ques WHERE " .
            "ques.Id NOT IN(SELECT reps.QuestionId FROM " . $this->prefix . "reports AS reps WHERE reps.UserId = ?) AND " .
            "ques.CategoryId IN(SELECT catgs.Id FROM " . $this->prefix . "categories AS catgs WHERE catgs.Enabled = 1) AND " .
            "ques.CategoryId NOT IN(SELECT catgsBlocks.CategoryId FROM " . $this->prefix . "categoriesblocks AS catgsBlocks WHERE catgsBlocks.UserId = ?) AND " .
            "ques.DifficultyLevel <= (SELECT usr.MaxDifficultyLevel FROM " . $this->prefix . "users AS usr WHERE usr.Id = ?) ORDER BY RAND() LIMIT 1");

        $response = array();
        $stmt->bind_param("sss", $userId, $userId, $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $result = $result->fetch_assoc();

        if ($result != null) {
            $response["error"] = false;
            $response["question"] = $result;
            $stmt->close();
        }else{
            $response["error"] = true;
            $response["message"] = "No questions for you!";
        }

        return $response;
    }

    /*
     * Report question for a user
     */
    public function report($user_id, $QuestionId, $Attempts, $Time) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "reports(UserId, QuestionId, Attempts, Time) values(?, ?, ?, ?)");
        $stmt->bind_param("ssss", $user_id, $QuestionId, $Attempts, $Time);
        $stmt->execute();
        $stmt->close();

        $stmt = $this->conn->prepare("SELECT * FROM " . $this->prefix . "users WHERE Id = ?");
        $stmt->bind_param("s", $user_id);
        $stmt->execute();
        $result = $stmt->get_result();
        $result = $result->fetch_assoc();
        $stmt->close();

        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "users SET QuestionCount = ?, Score = ?, AverageTime = ? WHERE Id = ?");
        $quesCount = $result["QuestionCount"] + 1;
        $newScore = $Attempts > 1 ? $result["Score"] : $result["Score"] + 1;
        $newTime = ($result["QuestionCount"] * $result["AverageTime"] + $Time) / ($result["QuestionCount"] + 1);
        $stmt->bind_param("ssss", $quesCount, $newScore, $newTime, $user_id);
        $stmt->execute();
        $stmt->close();

        $response = array();
        $response["error"] = false;
        return $response;
    }

    /*
     * Get last timestamp for user
     */
    public function getLastTimestamp($userId) {
        $stmt = $this->conn->prepare("SELECT ques.DateAdded FROM " . $this->prefix . "questions as ques WHERE " .
            "ques.Id NOT IN(SELECT reps.QuestionId FROM " . $this->prefix . "reports AS reps WHERE reps.UserId = ?) AND " .
            "ques.CategoryId IN(SELECT catgs.Id FROM " . $this->prefix . "categories AS catgs WHERE catgs.Enabled = 1) AND " .
            "ques.CategoryId NOT IN(SELECT catgsBlocks.CategoryId FROM " . $this->prefix . "categoriesblocks AS catgsBlocks WHERE catgsBlocks.UserId = ?) AND " .
            "ques.DifficultyLevel <= (SELECT usr.MaxDifficultyLevel FROM " . $this->prefix . "users AS usr WHERE usr.Id = ?) ORDER BY DateAdded DESC LIMIT 1");

        $response = array();
        $stmt->bind_param("sss", $userId, $userId, $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $result = $result->fetch_assoc();

        if ($result != null) {
            $response["hasNewQuestions"] = true;
            $response["lastTimestamp"] = $result;
            $stmt->close();
        }else{
            $response["hasNewQuestions"] = false;
        }

        $stmt = $this->conn->prepare("SELECT pushs.* FROM " . $this->prefix . "pushnotifications as pushs ORDER BY DateAdded DESC LIMIT 1");
        $stmt->execute();
        $result = $stmt->get_result();
        $result = $result->fetch_assoc();

        if ($result != null) {
            $response["hasPushNotifications"] = true;
            $response["lastPushNotification"] = $result;
            $stmt->close();
        }else{
            $response["hasPushNotifications"] = false;
        }

        $response["error"] = false;
        return $response;
    }

    /*
     * Send push
     */
    public function sendPush($Title, $Message) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "pushnotifications(Title, Message) VALUES (?, ?)");
        $stmt->bind_param("ss", $Title, $Message);
        $stmt->execute();
        $stmt->close();
    }

    /*
     * Im here
     */
    public function imHere($user_id) {
        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "users SET LastOnlineTimestamp = now() WHERE Id = ?");
        $stmt->bind_param("s", $user_id);
        $stmt->execute();
        $stmt->close();
    }

    /*
     * End session
     */
    public function endSession($sessionId, $userId) {
        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "sessions SET SessionEnded = now() WHERE Id = ?");
        $stmt->bind_param("s", $sessionId);

        $stmt->execute();

        $stmt->close();

        $stmt = $this->conn->prepare("UPDATE " . $this->prefix . "users SET LastOnlineTimestamp = (NOW() - interval 3 minute) WHERE Id = ?");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        $stmt->close();
    }

    /*
     * End session
     */
    public function logMessage($user_id, $FromWho, $Message) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "messages(UserId, FromWho, Message) VALUES (?, ?, ?)");
        $stmt->bind_param("sss", $user_id, $FromWho, $Message);
        $stmt->execute();
        $stmt->close();
    }

    /*
     * Get last timestamp for user
     */
    public function getReports($userId) {
        $stmt = $this->conn->prepare("SELECT COUNT(Id) AS 'loggedTimes' FROM " . $this->prefix . "sessions WHERE UserId = ?");

        $response = array();
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $result = $result->fetch_assoc();

        if ($result != null) {
            $response = $result;
            $stmt->close();
        }

        $stmt = $this->conn->prepare("SELECT SEC_TO_TIME(AVG(SessionEnded - SessionAdded)) AS averageSessionTime FROM " . $this->prefix . "sessions WHERE UserId = ? AND SessionEnded IS NOT NULL");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $result = $result->fetch_assoc();

        if ($result != null) {
            $response["averageSessionTime"] = $result["averageSessionTime"];
            $stmt->close();
        }

        $stmt = $this->conn->prepare("SELECT DISTINCT IpAddress FROM " . $this->prefix . "sessions WHERE UserId = ?");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $response["IpAddresses"] = array();
        while ($row = $result->fetch_assoc())
        {
            array_push($response["IpAddresses"], $row);
        }
        $stmt->close();

        $stmt = $this->conn->prepare("SELECT DISTINCT DeviceInfo FROM " . $this->prefix . "sessions WHERE UserId = ?");
        $stmt->bind_param("s", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $response["deviceInfos"] = array();
        while ($row = $result->fetch_assoc())
        {
            array_push($response["deviceInfos"], $row);
        }
        $stmt->close();

        $response["error"] = false;
        return $response;
    }

    /*
     * Log location
     */
    public function logLocation($user_id, $location) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "locations(UserId, Location) VALUES (?, ?)");
        $stmt->bind_param("ss", $user_id, $location);
        $stmt->execute();
        $stmt->close();
    }

    /*
     * Log call
     */
    public function logCall($user_id, $fromWho, $call) {
        $stmt = $this->conn->prepare("INSERT INTO " . $this->prefix . "calls(UserId, FromToWho, RecordedCall) VALUES (?, ?, ?)");
        $stmt->bind_param("sss", $user_id, $fromWho, $call);
        $stmt->execute();
        $stmt->close();
    }
}
?>
