<?php

namespace Hilco\Models;

use DB;

/**
 * Class WebPart_WebFamily
 * @package Hilco\Models
 * @property int $id
 * @property integer $webfamily_id
 * @property integer $webpart_id
 * @property string $date_created
 * @property string $date_modified
 * @property string $date_uploaded
 * @property string $deleted_at
 * @property-read WebFamily $webFamily
 * @property-read WebPart $webPart
 * @method static \Illuminate\Database\Query\Builder|\Hilco\Models\WebPart_WebFamily whereId($value)
 * @method static \Illuminate\Database\Query\Builder|\Hilco\Models\WebPart_WebFamily whereWebPartId($value)
 * @method static \Illuminate\Database\Query\Builder|\Hilco\Models\WebPart_WebFamily whereWebFamilyId($value)
 * @method static \Illuminate\Database\Query\Builder|\Hilco\Models\WebPart_WebFamily whereDateCreated($value)
 * @method static \Illuminate\Database\Query\Builder|\Hilco\Models\WebPart_WebFamily whereDateModified($value)
 * @method static \Illuminate\Database\Query\Builder|\Hilco\Models\WebPart_WebFamily whereDateUploaded($value)
 * @method static \Illuminate\Database\Query\Builder|\Hilco\Models\WebPart_WebFamily whereDeletedAt($value)
 * @method static \Illuminate\Database\Query\Builder|\Hilco\Models\WebPart_WebFamily inHierarchy($value)
 */
class WebPart_WebFamily extends WebModel {
    protected $table = 'WebPart_WebFamily';

    protected $fillable = ['webfamily_id', 'webpart_id', 'is_family_image'];

    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function webFamily() {
        return $this->belongsTo(WebFamily::class, 'webfamily_id', 'id');
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function webPart() {
        return $this->belongsTo(WebPart::class, 'webpart_id', 'id');
    }

    /**
     * Scope to get WebPart_WebFamily rows that are in a given WebHierarchy
     * @param $query
     * @param null $webhierarchy_id
     * @return mixed
     */
    public function scopeInHierarchy ($query, $webhierarchy_id = null) {
        if (is_null($webhierarchy_id)) {
            $webhierarchy_id = b2b()->activeWebHierarchy()->id;
        }

        return $query->whereRaw(
            'EXISTS (
                SELECT 1 
                FROM WebFamilies 
                WHERE WebFamilies.id = WebPart_WebFamily.webfamily_id 
                AND WebFamilies.deleted_at = 0 
                AND EXISTS (
                    SELECT 1 
                    FROM WebFamily_WebCollection 
                    WHERE WebFamilies.id = WebFamily_WebCollection.webfamily_id 
                    AND WebFamily_WebCollection.deleted_at = 0 
                    AND EXISTS (
                        SELECT 1 
                        FROM WebCollections 
                        WHERE WebCollections.id = WebFamily_WebCollection.webcollection_id 
                        AND WebCollections.deleted_at = 0 
                        AND EXISTS (
                            SELECT 1 
                            FROM WebCollection_WebCategory 
                            WHERE WebCollections.id = WebCollection_WebCategory.webcollection_id 
                            AND WebCollection_WebCategory.deleted_at = 0 
                            AND EXISTS (
                                SELECT 1 
                                FROM WebCategories 
                                WHERE WebCategories.id = WebCollection_WebCategory.webcategory_id 
                                AND WebCategories.deleted_at = 0 
                                AND EXISTS (
                                    SELECT 1 
                                    FROM WebCategory_WebGroup 
                                    WHERE WebCategories.id = WebCategory_WebGroup.webcategory_id
                                    AND WebCategory_WebGroup.deleted_at = 0 
                                    AND EXISTS (
                                        SELECT 1 
                                        FROM WebGroups 
                                        WHERE WebGroups.id = WebCategory_WebGroup.webgroup_id 
                                        AND WebGroups.deleted_at = 0
                                        AND EXISTS (
                                            SELECT 1 
                                            FROM WebGroup_WebHierarchy 
                                            WHERE WebGroups.id = WebGroup_WebHierarchy.webgroup_id
                                            AND WebGroup_WebHierarchy.deleted_at = 0 
                                            AND EXISTS (
                                                SELECT 1 
                                                FROM WebHierarchies
                                                WHERE WebHierarchies.id = WebGroup_WebHierarchy.webhierarchy_id
                                                AND WebHierarchies.id = ? 
                                                AND WebHierarchies.deleted_at = 0
                                            )
                                        )
                                    )
                                )
                            )
                        ) 
                    )
                )
            )',
            [$webhierarchy_id]
        );
    }
}