<?php

class PostService {

    private $conn;
    private $table = "post";
    
    public function __construct($db) {
        $this->conn = $db;
    }

    public function add($post) {

        $query = "INSERT INTO " . $this->table . 
            " SET title = :title, 
                url = :url, 
                thumbnail = :thumbnail, 
                author = :author,
                content = :content";
        
        $stmt = $this->conn->prepare($query);
        
        
        $post->title = htmlspecialchars(strip_tags($post->title));
        $post->url = htmlspecialchars(strip_tags($post->url));
        $post->thumbnail = htmlspecialchars(strip_tags($post->thumbnail));
        $post->author = htmlspecialchars(strip_tags($post->author));
        
        $stmt->bindParam(':title', $post->title);
        $stmt->bindParam(':url', $post->url);
        $stmt->bindParam(':thumbnail', $post->thumbnail);
        $stmt->bindParam(':author', $post->author);
        $stmt->bindParam(':content', $post->content);

        if($stmt->execute()) {
            return true;
        } else {
            return false;
        }
    }

    public function update($post) {

        $query = "UPDATE " . $this->table . 
            " SET title = :title, 
                url = :url,
                thumbnail = :thumbnail,  
                author = :author,
                content = :content
              WHERE id = :id";
        
        $stmt = $this->conn->prepare($query);
        
        $post->title = htmlspecialchars(strip_tags($post->title));
        $post->url = htmlspecialchars(strip_tags($post->url));
        $post->thumbnail = htmlspecialchars(strip_tags($post->thumbnail));
        $post->author = htmlspecialchars(strip_tags($post->author));
        
        $stmt->bindParam(':title', $post->title);
        $stmt->bindParam(':url', $post->url);
        $stmt->bindParam(':thumbnail', $post->thumbnail);
        $stmt->bindParam(':author', $post->author);
        $stmt->bindParam(':content', $post->content);
        $stmt->bindParam(':id', $post->id);

        if($stmt->execute()) {
            return true;
        } else {
            return false;
        }
    }

    public function delete($post) {

        $query = "DELETE FROM " . $this->table . " WHERE id = :id";

        $stmt = $this->conn->prepare($query);
        
        $stmt->bindParam(':id', $post->id);
        
        if($stmt->execute()) {
            return true;
        } else {
            return false;
        }
    }

    public function list() {

        $query = "SELECT * FROM " . $this->table . " ORDER BY created_at DESC ";

        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        $result_list = array();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {       
            array_push($result_list, $row);
        }
        return $result_list;
    }

    public function listLimitTo($limit) {

        $query = "SELECT * FROM " . $this->table . " ORDER BY created_at DESC LIMIT " . $limit;

        $stmt = $this->conn->prepare($query);
        $stmt->execute();

        $result_list = array();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {       
            array_push($result_list, $row);
        }
        return $result_list;
    }

    public function listByLimitToExcept($id, $limit) {

        $query = "SELECT * FROM " . $this->table . 
            " WHERE NOT id = :id " .
            " ORDER BY created_at DESC LIMIT " . $limit;

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        $result_list = array();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {       
            array_push($result_list, $row);
        }
        return $result_list;
    }

    public function listByAuthor($author) {

        $query = "SELECT * FROM " . $this->table . 
            " WHERE author = :author " .
            " ORDER BY created_at DESC ";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':author', $author);
        $stmt->execute();

        $result_list = array();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {       
            array_push($result_list, $row);
        }
        return $result_list;
    }

    public function listByAuthorLimitTo($author, $limit) {

        $query = "SELECT * FROM " . $this->table . 
            " WHERE author = :author " .
            " LIMIT " . $limit;

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':author', $author);
        $stmt->execute();

        $result_list = array();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {       
            array_push($result_list, $row);
        }
        return $result_list;
    }

    public function get($id) {

        $query = "SELECT * FROM " . $this->table . " WHERE id = :id";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        $stmt->execute();

        return ($stmt->fetch(PDO::FETCH_ASSOC));
    }

    public function getByTitle($title) {

        $query = "SELECT * FROM " . $this->table . " WHERE title = :title";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':title', $title);
        $stmt->execute();

        return ($stmt->fetch(PDO::FETCH_ASSOC));
    }

    public function getByURL($url) {

        $query = "SELECT * FROM " . $this->table . " WHERE url = :url";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':url', $url);
        $stmt->execute();

        return ($stmt->fetch(PDO::FETCH_ASSOC));
    }

    public function isUnique($title) {

        $query = "SELECT * FROM " . $this->table . " WHERE LOWER(title) = :title";
        
        $lower = strtolower($title);
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':title', $lower);
        $stmt->execute();

        $num_of_rows = $stmt->rowCount();
        
        if ($num_of_rows > 0) {
            return true;
        } else {
            return false;
        }
    }

    public function search($query_string) {

        $query_string = htmlspecialchars(strip_tags($query_string));
        
        $query = "SELECT * FROM " . $this->table . 
            " WHERE LOWER(title) LIKE :query" .
            " OR LOWER(content) LIKE :query";

        $stmt = $this->conn->prepare($query);
        $query_string = '%' . strtolower($query_string) . '%';
        $stmt->bindParam(':query', $query_string, PDO::PARAM_STR);
        $stmt->execute();

        $result_list = array();

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {       
            array_push($result_list, $row);
        }
        
        return $result_list;
    }
}

?>