<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
use DB ;
class Update extends Model
{
    protected $fillable = [];

    public static function getProjectUpdates($filters)
    {
        $updates = Update::leftjoin('update_comments','updates.id','=','update_comments.update_id')
            ->join('projects','projects.id','=','updates.project_id')
            ->leftjoin('update_comment_mentions','update_comment_mentions.comment_id','=','update_comments.id')
            ->leftjoin('users as commenter_user','commenter_user.id','=','update_comments.commenter_id')
            ->leftjoin('users as updatter_user','updatter_user.id','=','updates.sender_id')
            ->select(
                'updates.id as update_id',
                'updates.update_text',
                'updates.updates_datetime',
                'updates.project_id',
                'update_comments.id as comment_id',
                'update_comments.comment',
                'update_comments.comments_datetime',
                'update_comments.commenter_id',
                "commenter_user.image as commenter_image",
                "commenter_user.name as commenter_name",
                "updatter_user.image as updatter_image",
                "updatter_user.name as updatter_name",
                "projects.project_name")
            ->orderBy('updates.last_comment_datetime','DESC') 
            ->orderBy('update_comments.comments_datetime','ASC') 
            ->groupBy('updates.id')
            ->groupBy('update_comments.id');

            if( isset($filters['projectId'] ))
            {
                $updates = $updates->where('updates.project_id',$filters['projectId'])  ;
            }
            if( isset($filters['mentionUserIds'] ))
            {
                $updates = $updates->whereIn('update_comment_mentions.mention_user_id',
                    $filters['mentionUserIds'] )  ;
            }
            if( isset($filters['notificationUnread'] ))
            {
                $updates = $updates->addSelect("update_comment_mentions.id as update_comment_mentions_id")->where('update_comment_mentions.status_id',
                    $filters['notificationUnread'] )  ;
            }
            $updates = $updates->get();
            return $updates;
    }
}