<?php
    defined('BASEPATH') OR exit('No direct script access allowed');

    require "vendor/autoload.php";

    use PHPHtmlParser\Dom;
    use PHPHtmlParser\Dom\HtmlNode;
    use PHPHtmlParser\Dom\TextNode;

    class BlogModel extends CI_Model
    {

        function __construct()
        {
            parent::__construct();
            $this->load->model('UserModel', '', TRUE);
        }

        function transfer() {
            $data = $this->db->query('SELECT * FROM kingofdog.blog_posts')->result_array();
            foreach ($data as $item) {
                extract($item);
//                $this->db->query('INSERT INTO kingofdog_new.blog_post_versions (postID, lang, active, title, description, content, contentWordsCount, authorID, changes, edited) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)', [
//                    $postID, $language, $isActive, "", "", $content, $wordCount, $contentAuthorID, $versionMessage, $contentDate
//                ]);
//                $this->db->query('UPDATE kingofdog_new.blog_post_versions SET title = ?, description = ? WHERE postID = ? AND lang = ?', [
//                    $postTitle, $postDesc, $postID, $language
//                ]);
//                $this->db->query('UPDATE kingofdog_new.blog_post_versions SET url = ? WHERE postID = ?', [$postUrl, $postID]);
            }
        }

        private function getPostIDByUrl($url) {
            $data = $this->db->query('SELECT postID FROM blog_post_versions WHERE url = ? AND lang = ? AND active ORDER BY edited DESC LIMIT 1', [$url, $_SESSION['site_lang']])->result_array();
            return !empty($data) ? $data[0]['postID'] : NULL;
        }

        function getAllPosts($search, $amount, $offset = 0)
        {
            $offset *= $amount;
            if ($search !== '') {
                $search = strtolower($search);
                $posts = $this->db->query('
                  SELECT
                    p.*
                  FROM blog_post p
                  WHERE ((SELECT LOWER(title) RLIKE ? OR LOWER(description) RLIKE ? OR LOWER(content) RLIKE ? FROM blog_post_versions WHERE postID = p.ID AND lang = ?)
                           OR (SELECT LOWER(displayname) RLIKE ? FROM blog_categories c WHERE c.lang = ? AND c.categoryID IN (SELECT categoryID FROM blog_post_categories pc WHERE pc.postID = p.ID)))
                        AND state = 1
                  ORDER BY initialRelease DESC
                  LIMIT ? OFFSET ?', [$search, $search, $search, $_SESSION['site_lang'], $search, $_SESSION['site_lang'], $amount, $offset])->result_array();
            } else {
                $posts = $this->db->query('
                  SELECT *
                  FROM blog_post
                  WHERE state = 1 
                  ORDER BY initialRelease DESC
                  LIMIT ? OFFSET ?', [$amount, $offset])->result_array();
            }
            $posts = $this->mergePostTranslation($posts);
            $posts = $this->mergePostCategories($posts);
            $posts = $this->mergePostStats($posts);
            $posts = $this->mergePostAuthorData($posts);
            return $posts;
        }

        public function mergePostTranslation($posts, $content = FALSE, $language = NULL, $indexID = 'ID')
        {
            $language = ($language == NULL ? $_SESSION['site_lang'] : $language);
            foreach ($posts as $i => $post) {
                $translationData = $this->getPostTranslation($post[$indexID], $content, $language);
                if(isset($translationData['error']) && $translationData['error']) {
                    unset($posts[$i]);
                    continue;
                }
                $posts[$i] += $translationData;
            }
            return $posts;
        }

        private function mergePostCategories($posts, $language = NULL)
        {
            $language = $language == NULL ? $_SESSION['site_lang'] : $language;
            foreach ($posts as $i => $post) {
                $categories = $this->getPostCategories($post['ID'], $language);
                $posts[$i]['categories'] = $categories;
            }
            return $posts;
        }

        private function getPostCategories($postID, $language)
        {
            $data = $this->db->query('SELECT ID, name, displayname FROM blog_categories WHERE categoryID IN (SELECT categoryID FROM blog_post_categories WHERE postID = ?) AND lang = ?', [$postID, $language])->result_array();
            return $data;
        }

        private function mergePostStats($posts, $language = NULL)
        {
            $language = $language == NULL ? $_SESSION['site_lang'] : $language;
            foreach ($posts as $i => $post) {
                $stats = $this->getPostStats($post['ID'], $language);
                $posts[$i] += $stats;
            }
            return $posts;
        }

        private function mergePostState($posts) {
            $states = [
                1 => "Veröffentlicht",
                2 => "Entwurf",
                3 => "Geplant",
                4 => "Im Papierkorb",
            ];
            foreach ($posts as $i => $post) {
                $posts[$i]['stateName'] = $states[$post['state']];
            }
            return $posts;
        }

        private function getPostStats($postID, $language)
        {
            $comments = $this->db->query('SELECT COUNT(*) count FROM blog_post_comments WHERE postID = ?', [$postID])->result_array()[0]['count'];
            $likes = $this->db->query('SELECT COUNT(*) count FROM blog_post_likes WHERE postID = ?', [$postID])->result_array()[0]['count'];
            $words = $this->db->query('SELECT contentWordsCount count FROM blog_post_versions WHERE postID = ? AND active AND lang = ?', [$postID, $language])->result_array();

            $words = empty($words) ? 0 : $words[0]['count'];

            return [
                'commentCount' => $comments,
                'likeCount' => $likes,
                'wordCount' => $words,
            ];
        }

        private function mergePostAuthorData($posts)
        {
            foreach ($posts as $i => $post) {
                $authorData = $this->getAuthorData($post['authorID']);
                $posts[$i]['author'] = $authorData;
            }
            return $posts;
        }

        public function getAuthorData($authorID)
        {
            $author = $this->db->query('SELECT u.ID, u.username, u.displayname, u.rank, s.profilePicture, s.headerImage, s.about FROM users u LEFT JOIN user_settings s ON u.ID = s.ID WHERE u.ID = ?', [$authorID])->result_array();

            $author = $this->UserModel->setDefaultImages($author);

            if (empty($author)) {
                return null;
            }
            return $author[0];
        }

        public function getPostPageCount($search, $postsPerPage)
        {
            if ($search !== '') {
                $search = strtolower($search);
                $data = $this->db->query('
                  SELECT COUNT(*) pageCount
                  FROM blog_post p
                  WHERE ((SELECT LOWER(title) RLIKE ? OR LOWER(description) RLIKE ? OR LOWER(content) RLIKE ? FROM blog_post_versions v WHERE v.postID = p.ID AND v.lang = ? AND active)
                           OR (SELECT LOWER(displayname) RLIKE ? FROM blog_categories c WHERE c.lang = ? AND c.categoryID IN (SELECT categoryID FROM blog_post_categories pc WHERE pc.postID = p.ID)))
                  AND state = 1', [$search, $search, $_SESSION['site_lang'], $search, $_SESSION['site_lang']])->result_array();
            } else {
                $data = $this->db->query('SELECT COUNT(*) pageCount FROM blog_post')->result_array();
            }
            return ($data[0]['pageCount']) / $postsPerPage;
        }

        function getCategoryPosts($category, $amount, $offset = 0)
        {
            $category = $this->db->query('SELECT ID FROM blog_categories WHERE name = ? AND lang = ?', [$category, $_SESSION['site_lang']])->result_array();

            if(empty($category)) {
                return NULL;
            }

            $offset *= $amount;
            $posts = $this->db->query('SELECT * FROM blog_post p WHERE ID IN (SELECT postID FROM blog_post_categories pc WHERE pc.categoryID = ?) LIMIT ? OFFSET ?', [$category[0]['ID'], $amount, $offset])->result_array();
            $posts = $this->mergePostTranslation($posts);
            $posts = $this->mergePostCategories($posts);
            $posts = $this->mergePostStats($posts);
            $posts = $this->mergePostAuthorData($posts);
            return $posts;
        }

        public function getCategoryPostsByID($categories, $amount = 3, $postID = NULL)
        {
            function getIDs($val) {
                return $val['ID'];
            }

            if(empty($categories)) {
                return NULL;
            }

            $categories = array_map('getIDs', $categories);

            $posts = $this->db->query('SELECT * FROM blog_post WHERE ID IN (SELECT postID FROM blog_post_categories WHERE categoryID IN ?) AND ID != ? AND state = 1 ORDER BY initialRelease DESC LIMIT ?', [$categories, $postID, $amount])->result_array();
            $posts = $this->mergePostTranslation($posts);
            return $posts;
        }

        public function getTagPosts($tag, $amount, $offset = 0)
        {
            $offset *= $amount;
            $tag = strtolower($tag);
            $tagID = $this->db->query('SELECT ID FROM blog_tags WHERE name = ? AND lang = ?', [$tag, $_SESSION['site_lang']])->result_array();

            if(empty($tagID)) {
                return;
            }

            $posts = $this->db->query('SELECT * FROM blog_post p WHERE state = 1 AND ID IN (SELECT postID FROM blog_post_tags WHERE tagID = ?) ORDER BY initialRelease DESC LIMIT ? OFFSET ?', [$tagID[0]['ID'], $amount, $offset])->result_array();
            $posts = $this->mergePostTranslation($posts);
            $posts = $this->mergePostCategories($posts);
            $posts = $this->mergePostStats($posts);
            $posts = $this->mergePostAuthorData($posts);
            return $posts;
        }

        function getCategoryIDAfterInsert($name, $display_name)
        {
            if (!$this->db->simple_query('INSERT INTO blog_categories (name, displayname) VALUES (?, ?)', [$name, $display_name])) {
                $return = $this->db->query('SELECT ID FROM blog_categories WHERE name = ? AND displayname = ? LIMIT 1', [$name, $display_name])->result_array()[0];
                $return = $return['ID'];
            } else {
                $return = $this->db->query('SELECT LAST_INSERT_ID() ID')->result_array()[0]['ID'];
            }
            return $return;
        }

        function incrementViews($id)
        {
            $this->db->query('INSERT IGNORE INTO blog_post_stats (ID) VALUES (?)', [$id]);

            $this->db->query('UPDATE blog_post_stats SET views = views + 1 WHERE ID = ?', [$id]);
            $this->db->cache_delete('admin', 'blog');
        }

        public function getCategories()
        {
            $this->db->cache_off();
            $categories = $this->db->query('SELECT * FROM blog_categories')->result_array();
            $this->db->cache_on();
            return $categories;
        }

        public function getPost($postUrl)
        {
            $content = $this->db->query('
              SELECT p.* 
              FROM blog_post p
              WHERE (SELECT postID FROM blog_post_versions v WHERE v.postID = p.ID AND (v.lang = ? OR v.lang = "de") AND active AND url = ? ORDER BY edited DESC LIMIT 1) = p.ID AND state = 1
              GROUP BY ID', [$_SESSION['site_lang'], $postUrl])->result_array(); //TODO: language integration

            $content = $this->mergePostTranslation($content, true);
            $content = $this->mergePostStats($content);
            $content = $this->mergePostCategories($content);
            $content = $this->mergePostAuthorData($content);
            return $content[0];
        }

        public function getPostDataByID($postID)
        {
            $data = $this->db->query('
              SELECT *
              FROM blog_post
              WHERE ID = ?', [$postID])->result_array();

            $data = $this->mergePostCategories($data);

            return $data;
        }

        public function getRandomPosts($postID)
        {
            $posts = $this->db->query('SELECT * FROM blog_post WHERE ID <> ? AND state = 1 ORDER BY RAND() LIMIT 3', [$postID])->result_array();
            $posts = $this->mergePostTranslation($posts);
            $posts = $this->mergePostAuthorData($posts);
            return $posts;
        }

        public function getPostTranslation($postID, $showContent, $language)
        {
            $data = $this->db->query('SELECT * FROM blog_post_versions WHERE postID = ? AND active ORDER BY lang ASC', [$postID])->result_array();

            if (empty($data)) {
                return ['error' => true, 'title' => 'Nicht vorhandener Post', 'description' => 'Aus unbekannten Gründen ist dieser Blog-Post nicht vorhanden. Bitte versuche es später erneut oder kontaktiere das Support-Team'];
            }

            $url = $data[0]['url'];
            $title = $data[0]['title'];
            $description = $data[0]['description'];
            $content = $data[0]['content'];
            foreach ($data as $row) {
                if ($row['lang'] == $language) {
                    $url = $row['url'];
                    $title = $row['title'];
                    $description = $row['description'];
                    $content = $row['content'];
                    break;
                }
            }

            $return = [
                'url' => $url,
                'title' => $title,
                'description' => $description,
            ];
            if($showContent) {
                $return['content'] = $content;
            }

            return $return;
        }

        public function getPostTranslationByID($postID, $versionID, $lang) {
            $data = $this->db->query('SELECT * FROM blog_post_versions WHERE postID = ? AND ID = ? AND lang = ?', [$postID, $versionID, $lang])->result_array();
            return $data;
        }

        public function getPostTranslationByHashID($versionID) {
            $data = $this->db->query('SELECT * FROM blog_post_versions WHERE MD5(ID) = ?', [$versionID])->result_array();
            return !empty($data) ? $data[0] : NULL;
        }

        public function getComments($postID)
        {
            $comments = $this->db->query('SELECT * FROM blog_post_comments WHERE postID = ? ORDER BY date DESC', [$postID])->result_array();
            return $comments;
        }

        public function getCommentsByUrl($postUrl)
        {
            $ID = $this->getPostIDByUrl($postUrl);
            if($ID !== NULL) {
                return $this->getComments($ID);
            } else {
                return NULL;
            }
        }

        public function getComment($commentID) {
            $comment = $this->db->query('SELECT * FROM blog_post_comments WHERE ID = ?', [$commentID])->result_array();
            return !empty($comment) ? $comment[0] : NULL;
        }

        public function isCommentIDValid($commentID) {
            $comment = $this->db->query('SELECT ID FROM blog_post_comments WHERE ID = ?', [$commentID])->result_array();
            return !empty($comment);
        }

        public function addCommentByUrl($postUrl, $userID, $comment, $replyTo)
        {
            $postID = $this->getPostIDByUrl($postUrl);

            if($postID == NULL) {
                return null;
            }

            $this->addComment($postID, $userID, $comment, $replyTo);

            return $this->db->query('SELECT * FROM blog_post_comments WHERE postID = ? AND userID = ? ORDER BY ID DESC LIMIT 1', [$postID, $userID])->result_array()[0];
        }

        public function addComment($postID, $userID, $comment, $replyTo)
        {
            $this->db->query('INSERT INTO blog_post_comments (postID, userID, comment, replyToID) VALUES (?, ?, ?, ?)', [$postID, $userID, $comment, $replyTo]);

            $this->db->cache_delete('user', 'getComments');
            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'comment');
            $this->db->cache_delete('blog', 'getComments');
            $this->db->cache_delete('blog', 'getReportModal');
            $this->db->cache_delete('blog', 'reportModal');
            $this->db->cache_delete('blog', 'getDeleteModal');
            $this->db->cache_delete('blog', 'deleteComment');
            $this->db->cache_delete('admin', 'blog');
        }

        public function reportComment($commentID, $reason, $reasonText) {
            $this->db->query('INSERT INTO blog_post_comments_reports (commentID, reason, reasonText) VALUES (?, ?, ?)', [$commentID, $reason, $reasonText]);

            $this->db->cache_delete('admin', 'blog');
        }

        public function deleteComment($userID, $commentID) {
            $this->db->query('DELETE FROM blog_post_comments WHERE replyToID = ?', [$commentID]);
            $this->db->query('DELETE FROM blog_post_comments WHERE userID = ? AND ID = ?', [$userID, $commentID]);

            $this->db->cache_delete('user', 'getComments');
            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'comment');
            $this->db->cache_delete('blog', 'getComments');
            $this->db->cache_delete('blog', 'getReportModal');
            $this->db->cache_delete('blog', 'reportModal');
            $this->db->cache_delete('blog', 'getDeleteModal');
            $this->db->cache_delete('blog', 'deleteComment');
            $this->db->cache_delete('admin', 'blog');
        }

        public function getAllTags()
        {
            return $this->db->query('SELECT * FROM blog_tags')->result_array();
        }

        public function getTags($postID)
        {
            $tags = $this->db->query('SELECT t.* FROM blog_tags t WHERE ID IN (SELECT tagID FROM blog_post_tags WHERE postID = ?)', [$postID])->result_array();
            return $tags;
        }

        public function mergeTagInfo($tags)
        {
            foreach ($tags as $i => $tag) {
                $data = $this->db->query('SELECT count(*) countUsed, SUM(postViews) totalViews FROM blog_posts WHERE postID = (SELECT postID FROM blog_post_tags WHERE tag_id = ? AND postID = postID)', [$tag['ID']])->result_array();
                $data = $data[0];
                $tags[$i]['countUsed'] = $data['countUsed'];
                $tags[$i]['totalViews'] = $data['totalViews'] != '' ? $data['totalViews'] : 0;
            }
            return $tags;
        }

        public function hasAlreadyLiked($postID, $userID)
        {
            $getLikes = $this->db->query('SELECT * FROM blog_post_likes WHERE postID = ? AND userID = ?', [$postID, $userID])->result_array();
            if (empty($getLikes)) {
                return false;
            } else {
                return true;
            }
        }

        public function addLike($postID, $userID)
        {
            $this->db->query('INSERT INTO blog_post_likes (postID, userID) VALUES (?, ?)', [$postID, $userID]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'like');
            $this->db->cache_delete('admin', 'blog');

            return $this->db->query('SELECT count(*) likeCount FROM blog_post_likes WHERE postID = ?', [$postID])->result_array()[0];
        }

        public function removeLike($postID, $userID)
        {
            $this->db->query('DELETE FROM blog_post_likes WHERE postID = ? AND userID = ?', [$postID, $userID]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'like');
            $this->db->cache_delete('admin', 'blog');

            return $this->db->query('SELECT count(*) likeCount FROM blog_post_likes WHERE postID = ?', [$postID])->result_array()[0];
        }

        public function getPostList($onlyTrash)
        {
            if($onlyTrash) {
                $posts = $this->db->query('SELECT p.*, s.views
                  FROM blog_trash p LEFT JOIN blog_post_stats s ON s.ID = p.ID')->result_array();
            } else {
                $posts = $this->db->query('SELECT p.*, s.views
                  FROM blog_post p LEFT JOIN blog_post_stats s ON s.ID = p.ID')->result_array();
            }

            $posts = $this->mergePostTranslation($posts);
            $posts = $this->mergePostCategories($posts);
            $posts = $this->mergePostStats($posts);
            $posts = $this->mergePostAuthorData($posts);
            $posts = $this->mergePostState($posts);
            return $posts;
        }

        public function getAllPostVersions($postID, $lang)
        {
            $content = $this->db->query('SELECT *, MD5(ID) hashID FROM blog_post_versions WHERE postID = ? AND lang = ? ORDER BY edited DESC', [$postID, $lang])->result_array();
            return $content;
        }

        public function createTagIfNotExists($tag)
        {
            $this->db->cache_off();
            $tagData = $this->db->query('SELECT ID FROM blog_tags WHERE name = ?', [strtolower($tag)])->result_array();
            if (empty($tagData)) {
                $this->db->query('INSERT INTO blog_tags (name, displayname) VALUES (?, ?)', [strtolower($tag), $tag]);
                $tagData = $this->db->query('SELECT ID FROM blog_tags WHERE name = ?', [strtolower($tag)])->result_array();
            }
            $this->db->cache_on();
            $tagID = $tagData[0]['ID'];
            return $tagID;
        }

        public function addPostTagByID($postID, $tagID)
        {
            $this->db->query('INSERT INTO blog_post_tags (postID, tagID) VALUES (?, ?) ON DUPLICATE KEY UPDATE postID = postID', [$postID, $tagID]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'like');
            $this->db->cache_delete('admin', 'blog');
        }

        public function deleteAllPostTags($postID)
        {
            $this->db->query('DELETE FROM blog_post_tags WHERE postID = ?', [$postID]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'tag');
            $this->db->cache_delete('admin', 'blog');
        }

        public function addPostCategoryByID($postID, $categoryID)
        {
            $this->db->query('INSERT INTO blog_post_categories (postID, categoryID) VALUES (?, ?) ON DUPLICATE KEY UPDATE postID = postID', [$postID, $categoryID]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'like');
            $this->db->cache_delete('admin', 'blog');
        }

        public function deleteAllPostCategories($postID)
        {
            $this->db->query('DELETE FROM blog_post_categories WHERE postID = ?', [$postID]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'category');
            $this->db->cache_delete('admin', 'blog');
        }
        // TODO: Update
        public function prepareContentForRelease($content)
        {
            $dom = new Dom;
            $dom->load($content);
            $images = $dom->find('img');
            foreach ($images as $image) {
                $image_src = $image->getAttribute('src');
                $image_caption = $image->getAttribute('alt');
                $image->removeAllAttributes();
                $image->setAttribute('class', 'img-fluid');
                $image->setAttribute('src', $image_src);
                $image->setAttribute('alt', $image_caption);

                $container = new HtmlNode('div');
                $container->setAttribute('class', 'img');

                $caption_node = new HtmlNode('p');
                $caption = new TextNode($image_caption);
                $caption_node->addChild($caption);

                $parent = $image->getParent();
                $parent->addChild($container);
                $container->addChild($image);
                $container->addChild($caption_node);
            }
            $iframes = $dom->find('iframe');
            foreach ($iframes as $iframe) {
                $container = new HtmlNode('div');
                $container->setAttribute('class', 'responsive-video');
                $parent = $iframe->getParent();
                $parent->addChild($container);
                $container->addChild($iframe);
            }
            return $dom;
        }

        public function getFirstImage($content)
        {
            $dom = new Dom;
            $dom->load($content);
            $images = $dom->find('img');
            if ($images[0] == null)
                return '';
            $imageSrc = $images[0]->getAttribute('src');
            return $imageSrc;
        }

        public function deletePost($id)
        {
            $this->db->query('INSERT INTO blog_trash SELECT * FROM blog_post WHERE ID = ?', [$id]);
            $this->db->query('DELETE FROM blog_post WHERE ID = ?', [$id]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'like');
            $this->db->cache_delete('blog', 'comment');
            $this->db->cache_delete('blog', 'getComments');
            $this->db->cache_delete('admin', 'blog');
        }

        public function restorePost($id)
        {
            $this->db->query('INSERT INTO blog_post SELECT * FROM blog_trash WHERE ID = ?', [$id]);
            $this->db->query('DELETE FROM blog_trash WHERE ID = ?', [$id]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'like');
            $this->db->cache_delete('blog', 'comment');
            $this->db->cache_delete('blog', 'getComments');
            $this->db->cache_delete('admin', 'blog');
        }

        public function deletePostFinally($id)
        {
            $data = $this->db->query('SELECT ID FROM blog_trash WHERE ID = ?', [$id])->result_array();

            if(empty($data)) {
                return;
            }

            $this->db->query('DELETE FROM blog_post_categories WHERE postID = ?', [$id]);
            $this->db->query('DELETE FROM blog_post_comments WHERE postID = ?', [$id]);
            $this->db->query('DELETE FROM blog_post_likes WHERE postID = ?', [$id]);
            $this->db->query('DELETE FROM blog_post_stats WHERE ID = ?', [$id]);
            $this->db->query('DELETE FROM blog_post_tags WHERE postID = ?', [$id]);
            $this->db->query('DELETE FROM blog_post_versions WHERE postID = ?', [$id]);
            $this->db->query('DELETE FROM blog_trash WHERE ID = ? LIMIT 1', [$id]);

            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'like');
            $this->db->cache_delete('blog', 'comment');
            $this->db->cache_delete('blog', 'getComments');
            $this->db->cache_delete('admin', 'blog');
        }

        public function getPrevPost($initialRelease)
        {
            $posts = $this->db->query('SELECT ID, image FROM blog_post WHERE initialRelease < ? AND state = 1 ORDER BY initialRelease DESC LIMIT 1', [$initialRelease])->result_array();
            $posts = $this->mergePostTranslation($posts);
            return $posts;
        }

        public function getNextPost($initialRelease)
        {
            $posts = $this->db->query('SELECT ID, image FROM blog_post WHERE initialRelease > ? AND state = 1 ORDER BY initialRelease ASC LIMIT 1', [$initialRelease])->result_array();
            $posts = $this->mergePostTranslation($posts);
            return $posts;
        }

        public function getMostRecentPosts($count)
        {
            $posts = $this->db->query('SELECT ID, image, initialRelease FROM blog_post WHERE state = 1 ORDER BY initialRelease DESC LIMIT ?', [$count])->result_array();
            $posts = $this->mergePostTranslation($posts);
            return $posts;
        }

        public function getReadingTime($wordCount)
        {
            $wordsPerSecond = 3;
            $duration = $wordCount / $wordsPerSecond;
            $duration /= 60;
            $duration = round($duration);
            return $duration;
        }

        public function createNewPostDraft($authorID)
        {
            $this->db->query('INSERT INTO blog_post (authorID, state) VALUE (?, 2)', [$authorID]);

            $this->db->cache_delete('admin', 'blog');
            $data = $this->db->query('SELECT ID FROM blog_post WHERE state = 2 ORDER BY ID DESC LIMIT 1')->result_array();

            return intval($data[0]['postID']);
        }

        public function createNewTranslationDraft($postID, $authorID, $lang) {
            $this->db->query('INSERT INTO blog_post_versions (postID, authorID, active, lang) VALUES (?, ?, 0, ?)', [$postID, $authorID, $lang]);
            $this->db->cache_delete('admin', 'blog');

            $data = $this->db->query('SELECT ID FROM blog_post_versions WHERE postID = ? ORDER BY ID DESC LIMIT 1', [$postID])->result_array();

            return intval($data[0]['ID']);
        }

        public function updatePostDraft($postID, $initialRelease, $postImage)
        {
            $this->db->query('UPDATE blog_post SET initialRelease = ?, image = ? WHERE ID = ?', [$initialRelease, $postImage, $postID]);
            $this->db->cache_delete('admin', 'blog');
        }

        public function updateTranslationDraft($versionID, $url, $title, $description, $content, $lang)
        {
            $wordCount = $this->countWords($content);
            $this->db->query('UPDATE blog_post_versions SET lang = ?, url = ?, title = ?, description = ?, content = ?, contentWordsCount = ?, edited = NOW() WHERE ID = ?', [$lang, $url, $title, $description, $content, $wordCount, $versionID]);
            $this->db->cache_delete('admin', 'blog');
        }

        private function countWords($text)
        {
            $text = preg_replace("/<[a-zA-Z0-9\/ .,:;\-_+!?&%=\"]+>/", '', $text);
            return str_word_count($text);
        }

        public function publishPostDraft($postID)
        {
            $this->db->query('UPDATE blog_post SET initialRelease = NOW(), lastEdit = NOW(), state = 1 WHERE ID = ?', [$postID]);
            $this->db->query('INSERT INTO blog_post_stats (ID) VALUES (?)', [$postID]);
            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'post');
            $this->db->cache_delete('blog', 'tag');
            $this->db->cache_delete('blog', 'category');
            $this->db->cache_delete('blog', 'search');
            $this->db->cache_delete('admin', 'blog');
        }

        public function publishTranslationDraft($postID, $versionID, $authorID, $lang)
        {
            $this->db->query('UPDATE blog_post_versions SET active = FALSE WHERE postID = ? AND lang = ?', [$postID, $lang]);
            $this->db->query('UPDATE blog_post_versions SET active = TRUE, edited = NOW(), authorID = ? WHERE postID = ? AND ID = ?', [$authorID, $postID, $versionID]);
        }

        public function getPostVersionIDs($postID) {
            $data = $this->db->query('SELECT ID, lang FROM blog_post_versions WHERE postID = ? GROUP BY lang ORDER BY active DESC, edited DESC, ID DESC', [$postID])->result_array();
            return $data;
        }

        public function getPostVersions($postID) {
            function getIDs($val) {
                return $val['ID'];
            }

            $versionIDs = $this->getPostVersionIDs($postID);
            $versionIDs = array_map("getIDs", $versionIDs);
            $data = $this->db->query('SELECT * FROM blog_post_versions WHERE ID IN ?', [$versionIDs])->result_array();

            return $data;
        }

        public function postIDExisting($postID)
        {
            $data = $this->db->query('SELECT ID FROM blog_post WHERE ID = ?', [$postID])->result_array();
            return !empty($data);
        }

        public function versionIDExisting($postID, $versionID)
        {
            $data = $this->db->query('SELECT ID FROM blog_post_versions WHERE postID = ? AND ID = ?', [$postID, $versionID])->result_array();
            return !empty($data);
        }

        public function postUrlExisting($postUrl)
        {
            $data = $this->db->query('SELECT url FROM blog_post_versions WHERE url = ?', [$postUrl])->result_array();
            return !empty($data);
        }

        public function createCategory($name, $displayname, $lang, $categoryID = null) {
            $sameName = $this->db->query('SELECT categoryID FROM blog_categories WHERE name = ? AND displayname = ? AND lang = ?', [$name, $displayname, $lang])->result_array();
            if(!empty($sameName)) {
                return $sameName[0]['categoryID'];
            }

            if($categoryID == null) {
                $highestCategoryID = $this->db->query('SELECT categoryID FROM blog_categories ORDER BY categoryID DESC LIMIT 1')->result_array();
                $categoryID = !empty($highestCategoryID) ? $highestCategoryID[0]['categoryID'] + 1 : 1;
            }
            $this->db->query('INSERT INTO blog_categories (categoryID, lang, name, displayname) VALUES (?, ?, ?, ?)', [$categoryID, $lang, $name, $displayname]);
            $this->db->cache_delete('admin', 'blog');
            $this->db->cache_delete('blog', 'index');
            $this->db->cache_delete('blog', 'category');
            $this->db->cache_delete('blog', 'tag');
            $this->db->cache_delete('blog', 'search');
            $this->db->cache_delete('blog', 'post');

            return $categoryID;
        }
    }