<?php


namespace Komma\Projects;

use Illuminate\Support\Facades\DB;


class ProjectRepository
{
    /**
     * Load a list of all project
     * or when $home == 'home', all projects that have an position_home > 0
     *
     * @param string $home
     * @return mixed
     */
    public function getProjects($home = '')
    {
        $projects = \DB::table('projects')
            ->leftJoin(
                'project_translations',
                'project_translations.project_id', '=', 'projects.id')
            ->leftJoin('routes', function ($join)
            {
                $join->on('project_translations.id', '=', 'routes.routable_id')
                    ->where('routes.routable_type', '=', 'Komma\Kms\Projects\Models\ProjectTranslation');
            })
            ->where('showInWork', '=', 1)
            ->whereNotNull('project_translations.name')
            ->select('projects.id', 'project_translations.name', 'project_translations.sub_title', 'project_translations.position_home', 'routes.route');

        if($home == 'home')
        {
            $projects->where('position_home', '!=', 0)
                ->orderBy('position_home', 'asc')->take(12);
        }
        else
        {
            $projects->orderBy('projects.lft', 'asc')->take(40);
        }
        $myProjects = $projects->get();

        $projectsWithImages = [];

        foreach ($myProjects as $project)
        {
            $projectImages = \DB::table('images')
                ->where('imageble_id', '=', $project->id)
                ->select('sort_order', 'small_image_url');
            if($home == 'home')
            {
                $projectImages = $projectImages->orderBy('sort_order', 'asc');
            }
            else
            {
                $projectImages = $projectImages->orderBy('sort_order', 'desc');
            }
            $projectImages = $projectImages->first();
            $project->small_image_url = $projectImages->small_image_url;
            $projectsWithImages[] = $project;
        }

        return $projectsWithImages;
    }

    /**
     *
     * Get an specific project
     *
     * @param $id
     * @return mixed
     */
    public function getProject($id)
    {
        $project = \DB::table('projects')
            ->leftJoin(
                'project_translations',
                'project_translations.project_id', '=', 'projects.id')
            ->leftJoin('routes', function ($join)
            {
                $join->on('project_translations.id', '=', 'routes.routable_id')
                    ->where('routes.routable_type', '=', 'Komma\Kms\Projects\Models\ProjectTranslation');
            })
            ->whereNotNull('project_translations.name')
            ->select('project_translations.*', 'projects.date')
            ->orderBy('projects.lft', 'asc')
            ->where('project_translations.project_id', '=', $id)
            ->first();


        $categories = \DB::table('category_translations')
            ->whereNotNull('category_translations.name')
            ->select('category_translations.name')
            ->whereIn('category_translations.category_id', json_decode($project->category))
            ->get();


        $project->category = $categories;

        return $project;
    }

    /**
     * Get the previous and next project from giving id
     *
     * @param $id
     * @return mixed
     */
    public function projectMenu($id)
    {
        //Determine the previous en next project
        $arrayProjects = \DB::table('projects')
            ->select('lft', 'id')
            ->where('lft', '!=', 1)
            ->orderBy('lft', 'asc')
            ->get();

        $projectArrayLocation = 0;
        $index = 0;
        foreach ($arrayProjects as $thisProject)
        {
            if($thisProject->id == $id)
            {
                $projectArrayLocation = $index;
                break;
            }
            $index++;
        }

        //write Exception for clicking the last of first project
        if(($projectArrayLocation != 0) && ($projectArrayLocation != (sizeof($arrayProjects) - 1)))
        {
            $previousNext = [$arrayProjects[($projectArrayLocation - 1)]->id, $arrayProjects[($projectArrayLocation + 1)]->id];
        }
        else
        {
            if($projectArrayLocation == 0)
            {
                $previousNext = [$arrayProjects[(sizeof($arrayProjects) - 1)]->id, $arrayProjects[($projectArrayLocation + 1)]->id];
            }
            else
            {
                $previousNext = [$arrayProjects[($projectArrayLocation - 1)]->id, $arrayProjects[0]->id];
            }
        }

        //load Previous en next project from DB
        $tempProjects = \DB::table('projects')
            ->leftJoin(
                'project_translations',
                'project_translations.project_id', '=', 'projects.id')
            ->leftJoin('routes', function ($join)
            {
                $join->on('project_translations.id', '=', 'routes.routable_id')
                    ->where('routes.routable_type', '=', 'Komma\Kms\Projects\Models\ProjectTranslation');
            })
            ->whereNotNull('project_translations.name')
            ->select('project_translations.name', 'projects.lft', 'projects.id', 'routes.route')
            ->whereIn('projects.id', $previousNext)
            ->get();
        $projects = [];

        foreach ($tempProjects as $project)
        {
            if($project->id == $previousNext[0])
            {
                $projects['previous'] = $project;
            }
            else
            {
                $projects['next'] = $project;
            }
        }

        return $projects;
    }
}