<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use DB;

class Page extends Model
{
    protected $primaryKey = 'page_id';

    public function group() {
        return $this->belongsTo("App\PageGroup", 'page_gr_id', 'page_gr_id');
    }

    public function translations() {
        return $this->hasMany("App\PageTranslation", 'page_id', 'page_id');
    }

    public function translation($lang) {
        return $this->hasOne("App\PageTranslation", 'page_id', 'page_id')->where('lang', '=', $lang)->first();
    }

    public function ctranslation() {
        return $this->hasOne("App\PageTranslation", 'page_id', 'page_id')->where('lang', '=', app()->getLocale());
    }

    public function route($lang) {
        return $this->hasOne("App\Route", 'object_id', 'page_id')->where('object', '=', 'Page')->where('lang', '=', $lang)->first();
    }

    // Frontend methods

    public static function getInformationPages()
    {

        $locale = app()->getLocale();

        $data = collect(DB::select('SELECT pt.`page_name`, r.slug 
                                    FROM `page_translations` pt 
                                    INNER JOIN `pages` p ON pt.`page_id` = p.`page_id`
                                    INNER JOIN `routes` r ON p.`page_id` = r.`object_id` AND r.`object` = "Page"  AND r.`lang` = :lang
                                    WHERE p.`page_gr_id` = 1 AND pt.`lang` = :lang', [':lang' => $locale]
        ));

        return $data;
    }

    public static function getPagesForMenu(){

        try{

            $locale = app()->getLocale();

            $data = collect(DB::select('SELECT pt.`page_name`, r.`slug` 
                                    FROM `page_translations` pt 
                                    INNER JOIN `pages` p ON pt.`page_id` = p.`page_id`
                                    INNER JOIN `routes` r ON p.`page_id` = r.`object_id` AND r.`object` = "Page"  AND r.`lang` = :lang
                                    WHERE p.`page_in_nav` = 1 AND pt.`lang` = :lang', [':lang' => $locale]
            ));

            return $data;

        } catch (\Illuminate\Database\QueryException $e) {
            // something went wrong with the transaction, rollback
            DB::rollback();
            handle_error($e);
        } catch (\Exception $e) {
            // something went wrong elsewhere
            DB::rollback();
            handle_error($e);
        }
    }
}
