<?php

class EventService {

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

    public function add($event) {

        $query = "INSERT INTO " . $this->table . 
            " SET title = :title,
                event_url = :event_url, 
                date = :date, 
                time = :time, 
                fee = :fee, 
                description = :description,
                venue = :venue,
                thumbnail = :thumbnail,
                url = :url,
                url_text = :url_text";
        
        $stmt = $this->conn->prepare($query);
        
        $event->title = htmlspecialchars(strip_tags($event->title));
        $event->event_url = htmlspecialchars(strip_tags($event->event_url));
        $event->date = htmlspecialchars(strip_tags($event->date));
        $event->time = htmlspecialchars(strip_tags($event->time));
        $event->fee = htmlspecialchars(strip_tags($event->fee));
        $event->description = htmlspecialchars(strip_tags($event->description));
        $event->venue = htmlspecialchars(strip_tags($event->venue));
        $event->thumbnail = htmlspecialchars(strip_tags($event->thumbnail));
        $event->url = htmlspecialchars(strip_tags($event->url));
        $event->url_text = htmlspecialchars(strip_tags($event->url_text));
        
        $stmt->bindParam(':title', $event->title);
        $stmt->bindParam(':event_url', $event->event_url);
        $stmt->bindParam(':date', $event->date);
        $stmt->bindParam(':time', $event->time);
        $stmt->bindParam(':fee', $event->fee);
        $stmt->bindParam(':description', $event->description);
        $stmt->bindParam(':venue', $event->venue);
        $stmt->bindParam(':thumbnail', $event->thumbnail);
        $stmt->bindParam(':url', $event->url);
        $stmt->bindParam(':url_text', $event->url_text);

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

    public function update($event) {

        $query = "UPDATE " . $this->table . 
            " SET title = :title,
                event_url = :event_url, 
                date = :date,
                time = :time,   
                fee = :fee,
                description = :description,
                venue = :venue,
                thumbnail = :thumbnail,
                url = :url,
                url_text = :url_text
              WHERE id = :id";
        
        $stmt = $this->conn->prepare($query);
        
        $event->title = htmlspecialchars(strip_tags($event->title));
        $event->event_url = htmlspecialchars(strip_tags($event->event_url));
        $event->date = htmlspecialchars(strip_tags($event->date));
        $event->time = htmlspecialchars(strip_tags($event->time));
        $event->fee = htmlspecialchars(strip_tags($event->fee));
        $event->description = htmlspecialchars(strip_tags($event->description));
        $event->venue = htmlspecialchars(strip_tags($event->venue));
        $event->thumbnail = htmlspecialchars(strip_tags($event->thumbnail));
        $event->url = htmlspecialchars(strip_tags($event->url));
        $event->url_text = htmlspecialchars(strip_tags($event->url_text));
        
        $stmt->bindParam(':title', $event->title);
        $stmt->bindParam(':event_url', $event->event_url);
        $stmt->bindParam(':date', $event->date);
        $stmt->bindParam(':time', $event->time);
        $stmt->bindParam(':fee', $event->fee);
        $stmt->bindParam(':description', $event->description);
        $stmt->bindParam(':venue', $event->venue);
        $stmt->bindParam(':thumbnail', $event->thumbnail);
        $stmt->bindParam(':url', $event->url);
        $stmt->bindParam(':url_text', $event->url_text);
        $stmt->bindParam(':id', $event->id);

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

    public function delete($event) {

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

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

    public function list() {

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

        $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 ASC 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 getByEventURL($event_url) {

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

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

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

    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 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 isUnique($title) {

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

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

        $num_of_rows = $stmt->rowCount();

        if ($num_of_rows > 0) {
            return false;
        } else {
            return true;
        }
    }

    public function search($query_string) {

        $query_string = htmlspecialchars(strip_tags($query_string));
        
        $query = "SELECT * FROM " . $this->table . 
            " WHERE LOWER(title) LIKE :query" .
            " OR LOWER(description) LIKE :query" .
            " OR LOWER(venue) 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;
    }
}

?>