<?php


namespace Komma\Pages;


class PageRepository
{

    public function getById($id)
    {
        return \DB::table('pages')
            ->leftJoin('page_translations', 'pages.id', '=', 'page_translations.page_id')

            ->leftJoin('routes', function ($join) {
                $join->on('page_translations.id', '=', 'routes.routable_id')
                    ->where('routes.routable_type', '=', 'Komma\Kms\Pages\Models\PageTranslation');
            })

            ->leftJoin('images', function ($join) {
                $join->on('pages.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Pages\Models\Page');
            })

            ->orderBy('pages.lft', 'asc')
            ->where('pages.id',$id) // exclude root
            ->select(
                'pages.id as id',
                'pages.lft',
                'pages.rgt',
                'pages.code_name as codeName' ,

                'page_translations.name',
                'page_translations.description',

                'images.large_image_url as headerImage',

                'routes.route'
            )
            ->first();
    }

    public function getRouteByPageId($id)
    {
        return \DB::table('page_translations')
            ->leftJoin('routes', function ($join) {
                $join->on('page_translations.id', '=', 'routes.routable_id')
                    ->where('routes.routable_type', '=', 'Komma\Kms\Pages\Models\PageTranslation');
            })
            ->where('page_translations.page_id',$id)
            ->select(
                'routes.route'
            )
            ->first();
    }

    public function getRoot()
    {
        return \DB::table('pages')
            ->where('lft',1)
            ->select('pages.id')
            ->first();
    }

    public function getParents($lft,$rgt)
    {
        return \DB::table('pages')
            ->leftJoin('page_translations', 'pages.id', '=', 'page_translations.page_id')

            ->leftJoin('routes', function ($join) {
                $join->on('page_translations.id', '=', 'routes.routable_id')
                    ->where('routes.routable_type', '=', 'Komma\Kms\Pages\Models\PageTranslation');
            })

            ->leftJoin('images', function ($join) {
                $join->on('pages.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Pages\Models\Page');
            })

            ->orderBy('pages.lft', 'asc')
            ->where('pages.lft','!=','1') // exclude root
            ->where('pages.lft','<=',$lft)
            ->where('pages.rgt','>=',$rgt)
            ->select(
                'pages.id as id',
                'pages.lft',
                'pages.rgt',
                'pages.code_name as codeName' ,

                'page_translations.name',
                'page_translations.meta_title',
                'page_translations.meta_description',
                'images.large_image_url as headerImage',

                'routes.route'
            )
            ->get();
    }

    public function getIds($ids)
    {
        return \DB::table('pages')
            ->leftJoin('page_translations', 'pages.id', '=', 'page_translations.page_id')

            ->leftJoin('images', function ($join) {
                $join->on('pages.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Pages\Models\Page');
            })
            ->leftJoin('routes', function ($join) {
                $join->on('page_translations.id', '=', 'routes.routable_id')
                    ->where('routes.routable_type', '=', 'Komma\Kms\Pages\Models\PageTranslation');
            })

            ->orderBy('pages.lft', 'asc')

            ->whereIn('pages.id',$ids)

            ->where('pages.lft','!=','1') // exclude root
            ->where('pages.show_in_menu','=',1) // only for menu

            ->select(
                'pages.id as id',
                'pages.lft',
                'pages.rgt',
                'pages.code_name as codeName' ,

                'page_translations.name',
                'images.large_image_url as headerImage',

                'routes.route'
            )
            ->get();
    }

    /*
     * Show the nodes immediate sub-nodes, but no further down the tree
     * http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
     */
    public function getImmediateChildren($parentId)
    {
        // Get id's
        $result = \DB::select(\DB::raw(
            'SELECT node.id, (COUNT(parent.id) - (sub_tree.depth + 1)) AS depth
            FROM pages AS node, pages AS parent, pages AS sub_parent,
                (
                    SELECT node.id, (COUNT(parent.id) - 1) AS depth
                    FROM pages AS node, pages AS parent
                    WHERE node.lft BETWEEN parent.lft AND parent.rgt
                            AND node.id = ' . $parentId . '
                    GROUP BY node.id
                    ORDER BY node.lft
                )
                AS sub_tree

            WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
                AND sub_parent.id = sub_tree.id
            GROUP BY node.id
            HAVING DEPTH <= 1'));

        // Fetch id;s from result
        $ids = array_fetch($result, 'id');

        return $this->getIds($ids);
    }

    /**
     * Return header image
     *
     * @param $id
     */
    public function getHeaderImageByPageId($id)
    {
        return \DB::table('pages')

            ->leftJoin('images', function ($join) {
                $join->on('pages.id', '=', 'images.imageble_id')
                    ->where('images.imageble_type', '=', 'Komma\Kms\Pages\Models\Page');
            })

            ->where('pages.id','=',$id) // exclude root
            ->select(
                'images.large_image_url as headerImage'
            )
            ->first();
    }
}