<?php

class PackageService {

    private $conn;
    private $table = "package";

    public function __construct($db) {
        $this->conn = $db;
    }

    public function add($package) {

        $query = "INSERT INTO " . $this->table . 
            " SET name = :name,
                url = :url,
                thumbnail = :thumbnail,
                description = :description,
                category = :category,
                popular = :popular,
                days = :days,
                nights = :nights,
                location = :location,
                starting_price = :starting_price,
                price_details = :price_details,
                includes = :includes,
                excludes = :excludes,
                altitude = :altitude,
                level = :level,
                group_size = :group_size,
                accommodation = :accommodation,
                season = :season,
                notes = :notes";
        
        $stmt = $this->conn->prepare($query);

        $package->name = htmlspecialchars(strip_tags($package->name));
        $package->url = htmlspecialchars(strip_tags($package->url));
        $package->days = htmlspecialchars(strip_tags($package->days));
        $package->nights = htmlspecialchars(strip_tags($package->nights));
        $package->location = htmlspecialchars(strip_tags($package->location));
        $package->starting_price = htmlspecialchars(strip_tags($package->starting_price));
        $package->altitude = htmlspecialchars(strip_tags($package->altitude));
        $package->level = htmlspecialchars(strip_tags($package->level));
        $package->group_size = htmlspecialchars(strip_tags($package->group_size));
        $package->accommodation = htmlspecialchars(strip_tags($package->accommodation));
        $package->season = htmlspecialchars(strip_tags($package->season));

        $stmt->bindParam(':name', $package->name);
        $stmt->bindParam(':url', $package->url);
        $stmt->bindParam(':thumbnail', $package->thumbnail);
        $stmt->bindParam(':description', $package->description);
        $stmt->bindParam(':category', $package->category);
        $stmt->bindParam(':popular', $package->popular);
        $stmt->bindParam(':days', $package->days);
        $stmt->bindParam(':nights', $package->nights);
        $stmt->bindParam(':location', $package->location);
        $stmt->bindParam(':starting_price', $package->starting_price);
        $stmt->bindParam(':price_details', $package->price_details);
        $stmt->bindParam(':includes', $package->includes);
        $stmt->bindParam(':excludes', $package->excludes);
        $stmt->bindParam(':altitude', $package->altitude);
        $stmt->bindParam(':level', $package->level);
        $stmt->bindParam(':group_size', $package->group_size);
        $stmt->bindParam(':accommodation', $package->accommodation);
        $stmt->bindParam(':season', $package->season);
        $stmt->bindParam(':notes', $package->notes);

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

    public function update($package) {

        $query = "UPDATE " . $this->table . 
            " SET name = :name,
                url = :url,
                thumbnail = :thumbnail,
                description = :description,
                category = :category,
                popular = :popular,
                days = :days,
                nights = :nights,
                location = :location,
                starting_price = :starting_price,
                price_details = :price_details,
                includes = :includes,
                excludes = :excludes,
                altitude = :altitude,
                level = :level,
                group_size = :group_size,
                accommodation = :accommodation,
                season = :season,
                notes = :notes
                WHERE id = :id";
        
        $stmt = $this->conn->prepare($query);

        $package->name = htmlspecialchars(strip_tags($package->name));
        $package->url = htmlspecialchars(strip_tags($package->url));
        $package->days = htmlspecialchars(strip_tags($package->days));
        $package->nights = htmlspecialchars(strip_tags($package->nights));
        $package->location = htmlspecialchars(strip_tags($package->location));
        $package->starting_price = htmlspecialchars(strip_tags($package->starting_price));
        $package->altitude = htmlspecialchars(strip_tags($package->altitude));
        $package->level = htmlspecialchars(strip_tags($package->level));
        $package->group_size = htmlspecialchars(strip_tags($package->group_size));
        $package->accommodation = htmlspecialchars(strip_tags($package->accommodation));
        $package->season = htmlspecialchars(strip_tags($package->season));

        $stmt->bindParam(':name', $package->name);
        $stmt->bindParam(':url', $package->url);
        $stmt->bindParam(':thumbnail', $package->thumbnail);
        $stmt->bindParam(':description', $package->description);
        $stmt->bindParam(':category', $package->category);
        $stmt->bindParam(':popular', $package->popular);
        $stmt->bindParam(':days', $package->days);
        $stmt->bindParam(':nights', $package->nights);
        $stmt->bindParam(':location', $package->location);
        $stmt->bindParam(':starting_price', $package->starting_price);
        $stmt->bindParam(':price_details', $package->price_details);
        $stmt->bindParam(':includes', $package->includes);
        $stmt->bindParam(':excludes', $package->excludes);
        $stmt->bindParam(':altitude', $package->altitude);
        $stmt->bindParam(':level', $package->level);
        $stmt->bindParam(':group_size', $package->group_size);
        $stmt->bindParam(':accommodation', $package->accommodation);
        $stmt->bindParam(':season', $package->season);
        $stmt->bindParam(':notes', $package->notes);
        $stmt->bindParam(':id', $package->id);

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

    public function delete($package) {

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

        $stmt = $this->conn->prepare($query);
        
        $stmt->bindParam(':id', $package->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 listPopularLimitTo($limit) {

        $query = "SELECT * FROM " . $this->table . 
            " WHERE popular = 1 " .
            " 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 listPopular() {

        $query = "SELECT * FROM " . $this->table . 
            " WHERE popular = 1 " .
            " 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 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 listByCategory($category) {

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

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

        $result_list = array();

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

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

        $query = "SELECT * FROM " . $this->table . " WHERE LOWER(name) = :name";
        
        $lower = strtolower($name);
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':name', $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(name) LIKE :query" .
            " OR LOWER(description) LIKE :query" .
            " OR LOWER(location) 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;
    }
}
?>