<?php
namespace Komma\Blog;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class BlogRepository {

    protected $relatedModel = 'Komma\Kms\Blog\Models\Blog';
    protected $relatedModel2 = 'Komma\Kms\Blog\Models\BlogTranslation';

    /**
     * Get latest $amount blogitems
     *
     * @param $amount
     * @return array
     */
    public function blog($amount){
        return DB::table('blog')
            ->leftJoin('blog_translations', 'blog.id', '=', 'blog_translations.blog_id')
            ->join('images', 'blog.id', '=', 'images.imageble_id')
            ->leftJoin('routes', 'routable_id', '=', 'blog_translations.id')
            ->orderBy('blog.published_at', 'desc')
            ->take($amount)
            ->select('blog.id','blog.published_at','blog_translations.name', 'routes.route', 'blog_translations.intro', 'images.large_image_url')
            ->where('images.imageble_type', $this->relatedModel)
            ->where('routable_type', '=', $this->relatedModel2)
            ->where('blog.published_at', '<', Carbon::tomorrow() )
            ->get();
    }

    /**
     *
     * Get an given $amount of blogitems after $skip
     *
     * @param $amount
     * @param $skip
     * @return array
     */
    public function blogSkip($amount, $skip){
        return DB::table('blog')
            ->leftJoin('blog_translations', 'blog.id', '=', 'blog_translations.blog_id')
            ->join('images', 'blog.id', '=', 'images.imageble_id')
            ->leftJoin('routes', 'routable_id', '=', 'blog_translations.id')
            ->orderBy('blog.published_at', 'desc')
            ->skip($skip)
            ->take($amount)
            ->select('blog.id','blog.published_at','blog_translations.name', 'routes.route', 'blog_translations.intro', 'images.large_image_url')
            ->where('images.imageble_type', $this->relatedModel)
            ->where('routable_type', '=', $this->relatedModel2)
            ->where('blog.published_at', '<', Carbon::tomorrow() )
            ->get();
    }

    /**
     *
     * Get how many Blogitems there are
     *
     * @return array
     */
    public function getAmountOfBlogItems(){
        return DB::table('blog')
            ->where('published_at', '<', Carbon::tomorrow() )
            ->where('lft', '!=', '1')
            ->get();
    }

    /**
     *
     * Get a specific blog article
     *
     * @param $slug
     * @return array
     */
    public function getBlogItem($slug){
        return DB::table('blog')
            ->leftJoin('blog_translations', 'blog.id', '=', 'blog_translations.blog_id')
            ->join('images', 'blog.id', '=', 'images.imageble_id')
            ->orderBy('blog.published_at', 'desc')
            ->select('blog.id','blog.published_at','blog_translations.name', 'blog_translations.intro', 'blog_translations.description', 'images.original_image_url', 'images.large_image_url')
            ->where('images.imageble_type', $this->relatedModel)
            ->where('blog.published_at', '<', Carbon::tomorrow())
            ->where('blog_translations.slug', '=', $slug)
            ->first();
    }

    /**
     *
     * Get all blog articles and their routes
     *
     * @return array
     */
    public function blogRoutes(){
        return DB::table('blog')
            ->leftJoin('blog_translations', 'blog.id', '=', 'blog_translations.blog_id')
            ->leftJoin('routes', 'routable_id', '=', 'blog_translations.id')
            ->orderBy('blog.published_at', 'desc')
            ->select('blog_translations.name', 'routes.route')
            ->where('routable_type', '=', $this->relatedModel2)
            ->get();
    }

    /**
     * Load previous blog items
     *
     * @param $slug
     * @param $amount
     * @return array
     */
    public function getPreviousBlogItems($slug, $amount){
        $slug = DB::table('blog')
            ->leftJoin('blog_translations', 'blog.id', '=', 'blog_translations.blog_id')
            ->select('blog.published_at')
            ->where('blog_translations.slug', '=', $slug)
            ->get();

        $slugId = $slug[0]->published_at;

        return DB::table('blog')
            ->leftJoin('blog_translations', 'blog.id', '=', 'blog_translations.blog_id')
            ->join('images', 'blog.id', '=', 'images.imageble_id')
            ->leftJoin('routes', 'routable_id', '=', 'blog_translations.id')
            ->orderBy('blog.published_at', 'desc')
            ->take($amount)
            ->select('blog.id','blog.published_at','blog_translations.name', 'routes.route', 'blog_translations.intro', 'images.large_image_url')
            ->where('images.imageble_type', $this->relatedModel)
            ->where('routable_type', '=', $this->relatedModel2)
            ->where('blog.published_at', '<', $slugId )
            ->get();
    }

}