<?php
class Database{
	private $dbc;

	public function __construct($host, $db, $user, $pass){
		$this->dbc = pg_connect("host=$host dbname=$db user=$user password=$pass");
	}

	public function getCustomers($view, $limit, $start){
		$query = "SELECT DISTINCT last_name, first_name, customer_id FROM $view ORDER BY last_name LIMIT $limit OFFSET $start";
		return @pg_query($this->dbc, $query);
	}

	public function getActors($view, $limit, $start){
		$query = "SELECT DISTINCT last_name, first_name, actor_id FROM $view ORDER BY last_name LIMIT $limit OFFSET $start";
		return @pg_query($this->dbc, $query);
	}

	public function getFilms($cat, $fsk, $limit, $start){
		$query = "SELECT f.film_id, f.title, f.description FROM film f, category c WHERE f.category_id = c.category_id AND c.name LIKE '$cat' AND f.rating LIKE '$fsk' ORDER BY title LIMIT $limit OFFSET $start";
		return @pg_query($this->dbc, $query);
	}

	public function getCategories(){
		$query = "SELECT DISTINCT name FROM category";
		return @pg_query($this->dbc, $query);
	}

	public function getFsk(){
		$query = "SELECT DISTINCT rating FROM film";
		return @pg_query($this->dbc, $query);
	}

	public function formularSuche($eingabe, $view, $limit, $start, $sort, $order){
		$query = "";
		if($view === "customer"){
			$query = "SELECT * FROM customer NATURAL JOIN p_address NATURAL JOIN city NATURAL JOIN country";
		}
		if($view === "film"){
			$query = "SELECT * FROM film NATURAL JOIN category";
		}
		$a = array();
		$addExt = False;
		if(count($eingabe > 0)){
			foreach ($eingabe as $key=>$value) {
				if (!empty($value)) {
					$addExt = True;
					if($key === "film_id" || $key === "length" || $key === "customer_id"){
						$a[] = "CAST(".$key." as TEXT) LIKE '".$value."'";

					}else{
						$a[] = $key." ILIKE '%".$value."%'";

					}
				}
			}
		}
		$ext = implode(" AND ", $a);
		if($addExt){
			$query .= " WHERE ";
		}

		if($sort != ""){
			$ext .= " ORDER BY ".$sort." ".$order;
		}
		$query .= $ext." LIMIT $limit OFFSET $start";

		return pg_query($this->dbc, $query);
	}

	public function freiTextSuche($eingabe, $view, $limit, $start, $sort, $order){
		$query = "";
		if($view === "customer"){
			$query = "SELECT * FROM customer NATURAL JOIN p_address NATURAL JOIN city NATURAL JOIN country";
		}
		if($view === "film"){
			$query = "SELECT * FROM film NATURAL JOIN category";
		}

		$res = pg_query($this->dbc, $query);
		$tableArray = pg_fetch_all($res);

		if($eingabe){
			#tabellenkopf ermitteln
			$conds = array();
			foreach($tableArray[0] as $key=>$value){
				$conds[] = "CAST(".$key." as TEXT) ILIKE '%".$eingabe."%'";
			}

				#WHERE-Bedingungs-String bauen
				$ext = implode(" OR ", $conds);
				$query .= " WHERE ".$ext;
		}

				if($sort != ""){
				$query .= " ORDER BY ".$sort." ".$order;
				}

				$query .= " LIMIT $limit OFFSET $start";

				return pg_query($this->dbc, $query);
	}
	/**
	 * EXERCISE 4
	 * login-function, returns 1 if authorization was succesfully, else 0
	 */
	public function checkUser($username){
		$query = "select customer_id, first_name, customer_id from customer where customer_id  = '$username';";
		return pg_query($this->dbc, $query);
	}

	/**
	 * EXERCISE 4
	 * function insert values in rental, used in exercise four
	 */
	public function setRental($inventory_id, $customer_id, $staff_id){
		$query = "INSERT INTO rental VALUES(now(), ".$inventory_id.", ".$customer_id.", null, ".$staff_id.");";
		pg_query($this->dbc, $query);
	}

	/**
	 * EXERCISE 4
	 * function searches all movies
	 */
	public function getAllFilms($limit, $start){
		$query = "SELECT DISTINCT title FROM FILM LIMIT $limit OFFSET $start;";
		return pg_query($this->dbc, $query);
	}

	/**
	 * EXERCISE 4
	 * function searches all available film-copies
	 */
	public function getAvailableFilms(){
		$query = "SELECT DISTINCT f.title FROM film f, rental r, inventory i WHERE r.return_date IS NOT NULL AND r.inventory_id = i.inventory_id AND i.film_id = f.film_id;";
		return pg_query($this->dbc, $query);
	}

	/**
	 * EXERCISE 4
	 * function returns all available inventory_id's
	 */
	public function getInventoryID($filmID){
		$query = "SELECT DISTINCT inventory_id FROM inventory WHERE film_id = $filmID AND inventory_id NOT IN (SELECT inventory_id FROM rental WHERE return_date IS NULL);";
		$id = pg_fetch_array(pg_query($this->dbc, $query));
		return $id[0];
	}

	/**
	 * EXERCISE 4
	 * function returns film_id by title
	 */
	public function getFilmID($name){
		$query ="select film_id from film where title = '$name';";
		$id = pg_fetch_array(pg_query($this->dbc, $query));
		return  $id[0];
	}
	/**
	 * EXERCISE 4
	 */
	public function getNumberOfAvailableCopies($filmID){
		$query = "SELECT count(DISTINCT inventory_id) FROM inventory WHERE film_id = $filmID AND inventory_id NOT IN (SELECT inventory_id FROM rental WHERE return_date IS NULL);";
		$ret = pg_fetch_array(pg_query($this->dbc, $query));
		return $ret[0];
	}

	/**
	 * EXERCISE 4
	 */
	public function checkCommit($invID){
		$query = "SELECT * FROM rental WHERE inventory_id = $invID AND return_date IS NULL FOR UPDATE;";
		$ret = pg_fetch_array(pg_query($query));
		if($ret == null){
			return True;
		}
		return False;
	}
	/**
	 * EXERCISE 4
	 */
	public function checkIfAlreadyBorrowed($filmID, $custID){
		$query = "SELECT * FROM rental NATURAL JOIN inventory WHERE customer_id = $custID AND film_id = $filmID AND return_date IS NULL;";
		$ret = pg_fetch_array(pg_query($query));
		if($ret == null){
			return False;
		}
		return True;
	}

	public function getBorrowedMovies($custID){
		$query = "SELECT title,rent_date, ((date(CURRENT_TIMESTAMP) + interval '1 day')-date(rent_date)) as days FROM rental NATURAL JOIN customer INNER JOIN inventory ON (rental.inventory_id = inventory.inventory_id) NATURAL JOIN film WHERE return_date IS NULL AND customer_id = $custID;";
		return pg_query($this->dbc, $query);
	}