<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Film extends Model
{
    use HasFactory;

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

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

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

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

    public function activeVotes() {
        return $this->votes()->leftJoin('users', 'votes.user', '=', 'users.id')->leftJoin('settings', function($join) {
            $join->on('users.id', '=', 'settings.user')->where('settings.key', '=', 'disabled');
        })->whereNull('settings.value');
    }

    public function nextfilm() {
        $id = Setting::where('key', 'nextfilm')->first()->value;
        return static::load($id);
    }

    public function isNext() {
        $next = Setting::where('key', 'nextfilm')->first()->value;
        return $next == $this->id;
    }

    public function scopePopular($query) {
        $nextfilm = Setting::where('key', 'nextfilm')->first()->value;
        return $query->addSelect(DB::raw('films.*, COUNT(case when votes.vote IS TRUE then 1 end) as upvotes, COUNT(*) as votes'))
            ->leftJoin('votes', 'votes.film', '=', 'films.id')
            ->leftJoin('users', 'users.id', '=', 'votes.user')
            ->whereNull('seen')->whereNull('rejected')
            ->whereNot('films.id', '=', $nextfilm)
            ->whereNot('users.disabled', '=', 1)
            ->groupBy('films.id')
            ->orderBy('upvotes', 'DESC')
            ->orderBy('suggested', 'ASC')
        ;
    }

    public function scopeSeen($query) {
		return $query->whereNull('rejected')->whereNotNull('seen')->orderBy('seen', 'DESC');
	}

	public function scopeRejected($query) {
		return $query->whereNull('seen')->whereNotNull('rejected')->orderBy('rejected', 'DESC');
	}

	public function scopeSuggested($query) {
		return $query->whereNull('seen')->whereNull('rejected')->orderBy('updated_at', 'DESC');
	}
}