<?php
    class Blog {
        protected $db;
        protected $utils;

        public $post_id;
        public $status;
        public $title;
        public $short_description;
        public $description;
        public $category;
        public $link;

        public $total_posts;
        public $posts = array();

        public function __construct(Database $db, Utils $utils){
            $this->db = $db;
            $this->utils = $utils;
        }

        public function getPost($post_id){
            $post = array();
            $stmt = $this->db->query("SELECT b.id, b.status, b.title, short_description, b.description, b.image, b.category, c.title AS 'category_name', author, viewed, date_added FROM {$this->db->prefix}blog_posts b LEFT JOIN {$this->db->prefix}blog_categories c ON c.id=b.category WHERE b.id=:id LIMIT 1", array(':id'=>$post_id));
            if($stmt->num_rows > 0){
                $post = array(
                    'id'                     => $stmt->row['id'],
                    'status'                 => $stmt->row['status'],
                    'title'                  => $stmt->row['title'],
                    'short_description'      => $stmt->row['short_description'],
                    'description'            => $stmt->row['description'],
                    'image'                  => $stmt->row['image'],
                    'category'               => $stmt->row['category'],
                    'category_name'          => $stmt->row['category_name'],
                    'author'                 => $stmt->row['author'],
                    'viewed'                 => $stmt->row['viewed'],
                    'date_added'             => $stmt->row['date_added']
                );
            }
            return $post;
        }

        function getPostComments(){
            // TODO
        }

        public function getPosts($data = array()) {
            $posts = array();
            $query = "SELECT b.id, b.status, b.title, short_description, b.description, b.image, c.title AS 'category', viewed, date_added, u.url AS 'link' FROM {$this->db->prefix}blog_posts b LEFT JOIN {$this->db->prefix}blog_categories c ON c.id=b.category LEFT JOIN {$this->db->prefix}urls u ON b.id=u.element WHERE url_type='blog'";
            
            if(isset($data['category']) && $data['category'] != 0){
                $query .= " AND category =".$this->db->escape($data['category']);
            }


            if(isset($data['keyword'])){
                $keywords = explode(' ', $data['keyword']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "((b.title LIKE '%$word%') OR (search_keywords LIKE '%$word%'))";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }
            
            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY b.id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $status = $row['status'];
                    if($status == 1) {
                        $status = '<span class="badge bg-success">Aktif</span>';
                    }else if($status == 0) {
                        $status = '<span class="badge bg-danger">Pasif</span>';
                    }else {
                        $status = 'Bilinmiyor';
                    }
                    $item = array(
                        'id'                     => $row['id'],
                        'status'                 => $row['status'],
                        'status_text'            => $status,
                        'title'                  => $row['title'],
                        'short_description'      => $row['short_description'],
                        'description'            => $row['description'],
                        'image'                  => $row['image'],
                        'category'               => $row['category'],
                        'viewed'                 => $row['viewed'],
                        'date_added'             => $row['date_added'],
                        'url'                    => $row['link'],
                        'comment_count'          => 0
                    );
                    array_push($posts, $item);
                }
            }
            return $posts;
        }

        public function getTotalPosts($data = array()){
            $query = "SELECT COUNT(b.id) AS 'total' FROM {$this->db->prefix}blog_posts b LEFT JOIN {$this->db->prefix}blog_categories c ON c.id=b.category WHERE 1";
            
            if(isset($data['category']) && $data['category'] != 0){
                $query .= " AND category =".$this->db->escape($data['category']);
            }

            if(isset($data['keyword'])){
                $keywords = explode(' ', $data['keyword']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "((b.title LIKE '%$word%') OR (search_keywords LIKE '%$word%'))";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0){
                $this->total_posts      = $stmt->row['total'];
            }else{
                $this->total_posts      = 0;
            }

            return $this->total_posts;
        }

        public function getCategories($data = array()) {
            $categories = array();
            $query = "SELECT c.id, c.status, c.title, u.url AS 'link' FROM {$this->db->prefix}blog_categories c LEFT JOIN {$this->db->prefix}urls u ON c.id=u.element WHERE url_type='blog-category'";
            

            if(isset($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "c.title LIKE '%$word%'";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }
            
            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY c.id";
            }
    
            if(isset($data['order'])){
                $query .= " ".$data['order'];
            }else{
                $query .= " DESC";
            }
    
            if (isset($data['offset']) || isset($data['limit'])) {
                if($data['offset'] < 0){
                    $data['offset'] = 0;
                }
    
                if($data['limit'] < 1){
                    $data['limit'] = 24;
                }
    
                $query .= " LIMIT " . (int)$data['offset'] . "," . (int)$data['limit'];
            }

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $status = $row['status'];
                    if($status == 1) {
                        $status = '<span class="badge bg-success">Aktif</span>';
                    }else if($status == 0) {
                        $status = '<span class="badge bg-danger">Pasif</span>';
                    }else {
                        $status = 'Bilinmiyor';
                    }
                    $item = array(
                        'id'                     => $row['id'],
                        'status'                 => $row['status'],
                        'status_text'            => $status,
                        'title'                  => $row['title'],
                        'url'                    => $row['link']
                    );
                    array_push($categories, $item);
                }
            }

            return $categories;
        }

        public function getTotalCategories($data = array()){
            $query = "SELECT COUNT(c.id) AS 'total' FROM {$this->db->prefix}blog_categories c WHERE 1";
            
            if(isset($data['search_keywords'])){
                $keywords = explode(' ', $data['search_keywords']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = "title LIKE '%$word%'";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }
            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0){
                $this->total_posts      = $stmt->row['total'];
            }else{
                $this->total_posts      = 0;
            }

            return $this->total_posts;
        }

        public function getCategory($id) {
            $category = array();
            $query = "SELECT c.id, c.status, c.title, c.description, u.url AS 'link' FROM {$this->db->prefix}blog_categories c LEFT JOIN {$this->db->prefix}urls u ON c.id=u.element WHERE url_type='blog-category' AND c.id=:id LIMIT 1";

            $stmt = $this->db->query($query, array(':id'=> $id));
            if($stmt->num_rows > 0) {
                $category = array(
                    'id'                     => $stmt->row['id'],
                    'status'                 => $stmt->row['status'],
                    'title'                  => $stmt->row['title'],
                    'description'            => $stmt->row['description'],
                    'url'                    => $stmt->row['link']
                );
            }

            return $category;
        }

        public function increaseHit($post_id) {
            $this->db->query("UPDATE {$this->db->prefix}blog_posts SET viewed=viewed+1 WHERE id=:id", array(':id'=>$post_id));
        }

        public function deletePost($id) {
            $this->db->query("DELETE FROM `{$this->db->prefix}blog_posts` WHERE id=:id", array(':id'=>$id));
            $this->db->query("DELETE FROM `{$this->db->prefix}urls` WHERE element=:id AND url_type='blog'", array(':id'=>$id));
        }

        public function deleteCategory($id) {
            $this->db->query("DELETE FROM `{$this->db->prefix}blog_categories` WHERE id=:id", array(':id'=>$id));
            $this->db->query("DELETE FROM `{$this->db->prefix}urls` WHERE element=:id AND url_type='blog-category'", array(':id'=>$id));
        }
    }