<?php
    class Pages {
        protected $db;
        protected $utils;
        public function __construct(Database $db, Utils $utils){
            $this->db = $db;
            $this->utils = $utils;
        }

        public function getPage($page_id){
            $page = array();
            $stmt = $this->db->query("SELECT d.id, d.title, i.type AS 'type', d.status, d.description FROM {$this->db->prefix}information_description d LEFT JOIN {$this->db->prefix}information i ON i.information_id = d.id WHERE d.id=:id LIMIT 1", array(':id'=>$page_id));
            if($stmt->num_rows > 0){
                $page = array(
                    'id'                     => $stmt->row['id'],
                    'status'                 => $stmt->row['status'],
                    'title'                  => $stmt->row['title'],
                    'description'            => $stmt->row['description'],
                    'type'                   => $stmt->row['type']
                );
            }
            return $page;
        }

        public function getPages($data = array()) {
            $posts = array();
            $query = "SELECT d.id, d.title, t.title AS 'type', d.status, (SELECT url FROM {$this->db->prefix}urls WHERE element=d.id AND url_type='information') AS 'link' FROM {$this->db->prefix}information_description d LEFT JOIN {$this->db->prefix}information i ON i.information_id = d.id LEFT JOIN {$this->db->prefix}information_types t ON t.slug = i.type WHERE 1";
            
            if(isset($data['type']) && $data['type'] != 0){
                $query .= " AND t.id =".(int)$data['type'];
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND d.status =".$data['status'];
                }
            }

            if(isset($data['keyword'])){
                $keywords = explode(' ', $data['keyword']);
                $searchTermKeywords = array();
                foreach ($keywords as $word) {
                    $word                 = $this->db->escape($word);
                    $searchTermKeywords[] = " d.title LIKE '%$word%'";
                }
                $query .= ' AND '.implode(' AND ', $searchTermKeywords);
            }
            
            if(isset($data['sort'])){
                $query .= " ORDER BY " . $data['sort'];
            }else{
                $query .= " ORDER BY d.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($row['status'] == 1) {
                        $status = '<span class="badge bg-success">Aktif</span>';
                    }else if($row['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'],
                        'type'                   => $row['type']
                    );
                    array_push($posts, $item);
                }
            }
            return $posts;
        }

        public function getTotalPages($data = array()){
            $query = "SELECT COUNT(d.id) AS 'total' FROM {$this->db->prefix}information_description d LEFT JOIN {$this->db->prefix}information i ON i.information_id = d.id LEFT JOIN {$this->db->prefix}information_types t ON t.slug = i.type WHERE 1";
            
            if(isset($data['type']) && $data['type'] != 0){
                $query .= " AND t.id =".(int)$data['type'];
            }

            if(isset($data['status'])){
                if($data['status'] != ''){
                    $query .= " AND d.status =".$data['status'];
                }
            }

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

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

            return 0;
        }

        public function getTypes() {
            $types = array();
            $query = "SELECT id, status, title, slug FROM {$this->db->prefix}information_types";

            $stmt = $this->db->query($query);
            if($stmt->num_rows > 0) {
                foreach($stmt->rows as $row) {
                    $item = array(
                        'id'                     => $row['id'],
                        'status'                 => $row['status'],
                        'title'                  => $row['title'],
                        'slug'                   => $row['slug']
                    );
                    array_push($types, $item);
                }
            }

            return $types;
        }

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