<?php

class Film extends Eloquent {
	protected $table = "films";

	public function comments() {
		return $this->hasMany('Comment', 'film');
	}

	public function votes() {
		return $this->hasMany('Vote', 'film');
	}

	public function besitzer() {
		return $this->belongsTo('User', 'user');
	}

	public function scopeZuletztGesehen($query) {
		return $query->whereNotNull('gesehen')->orderBy('gesehen', 'DESC')->take(5);
	}

	public function scopeNeuesteVorschlage($query) {
		return $query->whereNull('gesehen')->orderBy('updated_at', 'DESC')->take(5);
	}

    public function scopeMeistgewunschteVorschlage($query) {
        $nextfilm = Dumbo::find(1)->film;
        return $query
            ->addSelect(DB::raw('film_films.*, COUNT(case when film_votes.stimme IS TRUE then 1 end) as upvotes'))
            ->leftJoin('votes', 'votes.film', '=', 'films.id')
            ->whereNull('gesehen')
            ->where('films.id', "!=", is_object($nextfilm) ? $nextfilm->id : 0)
            ->groupBy('films.id')
            ->orderBy('upvotes', 'DESC')
            ->orderBy('vorgeschlagen', 'ASC')
            ->take(5);
    }

	public function getVotes() {
        return Vote::where('film', '=', $this->id)->count();
    }

    public function getUpvotes() {
        return Vote::where('film', '=', $this->id)->whereRaw('stimme IS TRUE')->count();
    }

    public function getBewertung() {
        if(!is_null($this->gesehen)) {
            $count = Comment::where('film', $this->id)->where('bewertung', '>', 0)->count();
            return $count > 0 ? Comment::where('film', $this->id)->where('bewertung', '>', 0)->sum('bewertung') / $count : 0;

        } else return (0);

    }

}