<?php

namespace App\Packages\v4\Capsule\Services;

use App\Packages\Capsule\Models\Capsule;
use App\Packages\User\Models\User;
use App\Packages\v4\User\Notifications\TypesNotification;
use DB;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Collection;
use Illuminate\Database\Query\JoinClause;

/**
 * Class CapsuleProfileService
 * @package App\Packages\Capsule\Services\v4
 */
class CapsuleProfileService
{
    const LIMIT = 50;

    /**
     * @param User $user
     *
     * @return Builder
     */
    public function basicProfileCapsules(User $user, $off_private = false)
    {
        $loggedUser = user();
        $query = DB::table('table_capsule')
            ->select([
                'table_capsule.id',
                'table_capsule.uuid',
                'table_capsule.is_private',
                'table_capsule.caption',
                'table_capsule.is_complete',
                'table_capsule.blocked',
                'table_capsule.user_id',
                'table_capsule.count_comments',
                'table_capsule.count_likes',
                'table_capsule.count_subscribers',
                'table_capsule.open_at',
                'table_capsule.created_at',
                'table_capsule.status',

                'preview.width as previewWidth',
                'preview.height as previewHeight',
                'preview.aws_key as previewAwsKey',

                'video.aws_key as videoAwsKey',
                'teaser.aws_key as teaserAwsKey',

                'thumbnail.width as thumbnailWidth',
                'thumbnail.height as thumbnailHeight',
                'thumbnail.aws_key as thumbnailAwsKey',

                'user.uuid as userUuid',
                'user.nickname as userName',
                'avatar.aws_key as avatarAwsKey',
                'original_avatar.aws_key as originalAvatarAwsKey',

                'like.user_id as likedByMe',
                'subscriber.user_id as subscribedByMe',
                'tagged.user_id as taggedInMe',
                'open.user_id as openedByMe',
                'open.created_at as openedAt'
            ])

            ->leftJoin('table_capsule_preview as preview', 'preview.id', '=', 'table_capsule.preview_id')
            ->leftJoin('table_capsule_preview as thumbnail', 'thumbnail.id', '=', 'table_capsule.thumbnail_id')

            ->leftJoin('table_capsule_video as video', 'video.id', '=', 'table_capsule.video_id')
            ->leftJoin('table_capsule_teaser as teaser', 'teaser.id', '=', 'table_capsule.teaser_id')

            ->leftJoin('table_user as user', 'user.id', '=', 'table_capsule.user_id')
            ->leftJoin('table_user_avatar as avatar', 'avatar.id', '=', 'user.avatar_id')
            ->leftJoin('table_user_avatar as original_avatar', 'original_avatar.id', '=', 'user.original_avatar_id')

            ->leftJoin('table_capsule_like as like', function(JoinClause $join) use ($user) {
                $join->on('like.capsule_id', '=', 'table_capsule.id')->on('like.user_id', '=', DB::raw($user->id));
            })

            ->leftJoin('table_capsule_subscriber as subscriber', function(JoinClause $join) use ($user) {
                $join->on('subscriber.capsule_id', '=', 'table_capsule.user_id')->on('subscriber.user_id', '=', DB::raw($user->id));
            })

            ->leftJoin('table_capsule_tagged_user as tagged', function(JoinClause $join) use ($user) {
                $join->on('tagged.capsule_id', '=', 'table_capsule.id')->on('tagged.user_id', '=', DB::raw($user->id));
            })

            ->leftJoin('table_capsule_open as open', function(JoinClause $join) use ($loggedUser) {
                $join
                    ->on('open.capsule_id', '=', 'table_capsule.id')
                    ->on('open.user_id', '=', DB::raw($loggedUser->id));
            })

            ->where(function ($query) use ($user) {
                /** @var Builder $query */
                $query->where('table_capsule.user_id', $user->id)
                    ->orWhere('tagged.user_id', $user->id);
            })

            ->where('is_complete', '=', true)

            // Check for blocked capsule
            ->where(function ($query) use ($loggedUser) {
                /** @var Builder $query */
                $query->where('table_capsule.blocked', '=', 'f')
                    ->orWhere(function ($q) use ($loggedUser) {
                        /** @var Builder $q */
                        $blocked = TypesNotification::BLOCKED;
                        $q->where('table_capsule.blocked', '=', 't')
                            ->where('table_capsule.user_id', '=', $loggedUser->id)
                            ->whereRaw("(SELECT block_notification.is_seen 
                            FROM table_user_notification as block_notification 
                            WHERE block_notification.capsule_id = table_capsule.id AND block_notification.type = {$blocked}) = false");
                    });
            })

            ->whereNull('user.deleted_at')
            ->whereNull('table_capsule.deleted_at')

            ->leftJoin('table_capsule_report as reported', function(JoinClause $join) use ($loggedUser) {
                $join
                    ->on('reported.capsule_id', '=', 'table_capsule.id')
                    ->on('reported.by_user_id', '=', DB::raw($loggedUser->id));
            })
            ->whereNull('reported.id')

            ->distinct();

        if ($loggedUser->id != $user->id) {
            $query->leftJoin('table_capsule_tagged_user as alsoTagged', function(JoinClause $join) use ($user, $loggedUser) {

                $join
                    ->on('alsoTagged.capsule_id', '=', 'table_capsule.id')
                    ->on(function(JoinClause $join) use ($user, $loggedUser) {
                        $join->on(function(JoinClause $join) use ($user, $loggedUser) {
                            $join
                                ->on('alsoTagged.by_user_id', '=', DB::raw($loggedUser->id))
                                ->on('alsoTagged.user_id', '=', DB::raw($user->id));
                        })
                        ->orOn('alsoTagged.user_id', '=', DB::raw($loggedUser->id));
                    });

            });

            $query->where(function(Builder $query) {
                $query
                    ->whereNotNull('alsoTagged.id')
                    ->orWhere('table_capsule.is_private', '=', false);
            });


            /** Private profile */
            if ( ! $off_private ) {
                $query->leftJoin(DB::raw("(SELECT * FROM table_follower_relation WHERE status = true) as follower"),
                    function (JoinClause $join) use ($loggedUser) {
                        $join->on('follower.user_id', '=', 'table_capsule.user_id')
                            ->on('follower.follower_id', '=', DB::raw($loggedUser->id));
                    });

                $query->where(function (Builder $query) {
                    $query->where("user.is_private", false)
                        ->orWhere(function (Builder $q) {
                            $q->where('user.is_private', true)
                                ->whereNotNull('follower.id');
                        });
                });
            }

        }

        return $query;
    }

    /**
     * @param User $user
     * @return int
     */
    public function getProfileCapsulesCount(User $user)
    {
        $query = $this
            ->basicProfileCapsules($user, true);

        return $query->count('table_capsule.id');
    }

    /**
     * @param User $user
     * @param string|null $fromDate
     *
     * @return Capsule[]|Collection
     */
    public function getUserLockedCapsules(User $user, $fromDate = null)
    {
        $query = $this
            ->basicProfileCapsules($user)
            ->orderBy('table_capsule.open_at', 'ASC')
            ->limit(static::LIMIT)
            ->whereNull('open.user_id');

        if ($fromDate) {
            $query->where('table_capsule.open_at', '>', $fromDate);
        }

        return $query->get();
    }

    /**
     * @param User $user
     * @param string|null $fromDate
     *
     * @return Capsule[]|Collection
     */
    public function getUserOpenedCapsules(User $user, $fromDate = null)
    {
        $query = $this
            ->basicProfileCapsules($user)
            ->orderBy('open.created_at', 'DESC')
            ->limit(static::LIMIT)
            ->whereNotNull('open.user_id');

        if ($fromDate) {
            $query->where('open.created_at', '<', $fromDate);
        }

        return $query->get();
    }

    /**
     * @param User $user
     * @param null $fromDate
     *
     * @return int
     */
    public function getUserLockedCapsulesCount(User $user, $fromDate = null)
    {
        $query = $this
            ->basicProfileCapsules($user)
            ->whereNull('open.user_id');

        if ($fromDate) {
            $query->where('table_capsule.open_at', '>', $fromDate);
        }

        return $query->count('table_capsule.id');
    }

    /**
     * @param User $user
     * @param null $fromDate
     *
     * @return int
     */
    public function getUserOpenedCapsulesCount(User $user, $fromDate = null)
    {
        $query = $this
            ->basicProfileCapsules($user)
            ->whereNotNull('open.user_id');

        if ($fromDate) {
            $query->where('open.created_at', '<', $fromDate);
        }

        return $query->count('table_capsule.id');
    }


}