<?php

namespace App\Packages\Capsule\Services;

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

/**
 * Class CapsuleFeedService
 * @package App\Packages\Capsule\Services
 */
class CapsuleFeedService
{
    const LIMIT = 10;

    /**
     * @param User $user
     *
     * @return Builder
     */
    public function basicFeedQuery(User $user)
    {
        return DB::table('table_capsule')
            ->select([

                DB::raw('(SELECT COUNT(*) FROM table_capsule_comment as commentCount where commentCount.capsule_id = table_capsule.id and commentCount.deleted_at is null) as count_comments'),
                DB::raw('(SELECT COUNT(*) FROM table_capsule_like as likeCount where likeCount.capsule_id = table_capsule.id) as count_likes'),
                DB::raw('(SELECT COUNT(*) FROM table_capsule_subscriber as subscriberCount where subscriberCount.capsule_id = table_capsule.id) as count_subscribers'),

                '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.open_at',
                'table_capsule.created_at',
                'table_capsule.status',

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

                'cropped.width as croppedWidth',
                'cropped.height as croppedHeight',
                'cropped.aws_key as croppedAwsKey',

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

                'like.user_id as likedByMe',
                'subscriber.user_id as subscribedByMe',
                'tagged.user_id as taggedInMe',
                'removed.user_id as removedByMe',
                'open.user_id as openedByMe',
            ])

            ->leftJoin('table_capsule_preview as preview', 'preview.id', '=', 'table_capsule.preview_id')
            ->leftJoin('table_capsule_preview as cropped', 'cropped.id', '=', 'table_capsule.cropped_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_friend_relation as friend', 'friend.friend_id', '=', 'table_capsule.user_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.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 ($user) {
                $join
                    ->on('open.capsule_id', '=', 'table_capsule.id')
                    ->on('open.user_id', '=', DB::raw($user->id));
            })

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

            ->where('table_capsule.is_complete', '=', 't')

            // Check for blocked capsule
            ->where(function ($query) use ($user) {
                /** @var Builder $query */
                $query->where('table_capsule.blocked', '=', 'f')
                    ->orWhere(function ($q) use ($user) {
                        /** @var Builder $q */
                        $blocked = TypesNotification::BLOCKED;
                        $q->where('table_capsule.blocked', '=', 't')
                            ->where('table_capsule.user_id', '=', $user->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_removed as removed', function(JoinClause $join) use ($user) {
                $join
                    ->on('removed.capsule_id', '=', 'table_capsule.id')
                    ->on('removed.user_id', '=', DB::raw($user->id));
            })
            ->whereNull('removed.id')


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

            ->leftJoin('table_capsule_tagged_user as alsoTagged', function(JoinClause $join) use ($user) {

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

            /** @TODO: Can be refactore, (alsoTagged not null or my capsule) or is private = false */
            ->where(function(Builder $query) use ($user) {
                $query

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


            })


            ->distinct();
    }

    /**
     * @param User $user
     * @param null $fromDate
     * @param int $limit
     *
     * @return Capsule[]|Collection
     */
    public function getPastCapsules(User $user, $fromDate = null, $limit = self::LIMIT)
    {
        $query = $this
            ->basicFeedQuery($user)
            ->orderBy('open_at', 'DESC')
            ->limit($limit)
            ->whereNotNull('open.user_id');

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

        return $query->get();
    }

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

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

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


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

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

        return $query->get();
    }

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

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

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


}