<?php

namespace FootyRoom\Queries\Post;

use FootyRoom\Support\AutoMapper;
use Illuminate\Database\Connection;
use Illuminate\Database\Query\Builder;

class ForumPostQueryHandler
{
    /**
     * @var \Illuminate\Database\Connection
     */
    protected $mysql;

    /**
     * Constructor.
     *
     * @param \Illuminate\Database\Connection $mysql
     */
    public function __construct(Connection $mysql)
    {
        $this->mysql = $mysql;
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Post\ForumPostQuery $query
     *
     * @return \FootyRoom\Queries\Post\Post[]
     */
    public function find(ForumPostQuery $query): array
    {
        $builder = $this->bootstrap()

        ->select(array_merge($this->select(), [
            'user.username as username',
            'user.status as userStatus',
            'last_comment_user_id as lastCommentUserId',
            'last.username as lastCommentUsername',
            'last_comment_date as lastCommentDate',
        ]))
        ->join('fr_users as user', 'user.user_id', '=', 'post_author')
        ->leftJoin('fr_users as last', 'last.user_id', '=', 'last_comment_user_id')
        ->offset($query->getOffset())
        ->limit($query->getLimit())
        ->orderBy('last_comment_date', 'desc')
        ->orderBy('object_id', 'desc');

        $this->postStatus($builder, $query);
        $this->category($builder, $query);
        $this->sticky($builder, $query);
        $this->userFilter($builder, $query);
        $this->search($builder, $query);

        $postDtos = $builder->get();

        $posts = [];

        foreach ($postDtos as $post) {
            $posts[] = AutoMapper::map($post, ForumPost::class);
        }

        return $posts;
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Post\OneForumPostQuery $query
     *
     * @return \FootyRoom\Queries\Post\ForumPost|null
     */
    public function findOne(OneForumPostQuery $query)
    {
        $postDto = $this->bootstrap()

        ->select($this->select())
        ->where('ID', '=', $query->getPostId())
        ->first();

        if (!$postDto) {
            return null;
        }

        return AutoMapper::map($postDto, ForumPost::class);
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Post\ForumPostsInPeriodQuery $query
     *
     * @return int
     */
    public function postsInPeriod(ForumPostsInPeriodQuery $query)
    {
        return $this->bootstrap()

        ->select([
            $this->mysql->raw('COUNT(post_author) as count'),
        ])
        ->where(
            'post_date',
            '>',
            $this->mysql->raw("DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$query->getPeriod()} SECOND)")
        )
        ->where('wp_terms.term_group', '=', 100)
        ->where('post_author', '=', $query->getUserId())
        ->value('count');
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Post\ForumPostCountQuery $query
     *
     * @return int
     */
    public function count(ForumPostCountQuery $query)
    {
        $builder = $this->bootstrap();

        $this->postStatus($builder, $query);
        $this->category($builder, $query);
        $this->sticky($builder, $query);
        $this->userFilter($builder, $query);
        $this->search($builder, $query);

        return $builder->count();
    }

    /**
     * SQL for list of columns to select.
     *
     * @return string
     */
    protected function select()
    {
        return [
            'ID as id',
            'post_author as userId',
            'post_date as date',
            'post_title as title',
            'post_name as slug',
            'post_status as status',
            'comment_status as discussionStatus',
            'comment_count as commentCount',
            'view_count as viewCount',
            'sticky',
            'wp_terms.name as categoryName',
            'wp_terms.slug as categorySlug',
        ];
    }

    /**
     * Starts the basic query.
     *
     * @return \Illuminate\Database\Query\Builder
     */
    protected function bootstrap()
    {
        return $this->mysql

        ->table('wp_posts')
        ->join('wp_term_relationships', 'ID', '=', 'object_id')
        ->join('wp_terms', 'term_id', '=', 'term_taxonomy_id');
    }

    /**
     * Filter posts by users and/or relevant to users.
     *
     * @param \Illuminate\Database\Query\Builder $builder
     * @param \FootyRoom\Queries\Post\ForumPostQuery $query
     */
    protected function userFilter(Builder $builder, ForumPostQuery $query)
    {
        if ($userId = (int) $query->getWithUserId()) {
            $builder->whereRaw(
                "ID IN(SELECT REPLACE(discussion_id, 'forumPost:', '') FROM comments WHERE user_id = $userId)"
            );
        }

        if ($query->getByUserId()) {
            $builder->where('post_author', '=', $query->getByUserId());
        }
    }

    /**
     * Category condition.
     *
     * @param \Illuminate\Database\Query\Builder $builder
     * @param \FootyRoom\Queries\Post\ForumPostQuery $query
     */
    protected function category(Builder $builder, ForumPostQuery $query)
    {
        if ($query->getCategorySlug()) {
            $builder->where('wp_terms.slug', '=', $query->getCategorySlug());
        } else {
            $builder->where('wp_terms.term_group', '=', 100);
        }
    }

    /**
     * Post status condition.
     *
     * @param \Illuminate\Database\Query\Builder $builder
     * @param \FootyRoom\Queries\Post\ForumPostQuery $query
     */
    protected function postStatus(Builder $builder, ForumPostQuery $query)
    {
        if ($query->getUnpublished()) {
            $builder->whereIn('post_status', ['publish', 'draft']);
        } else {
            $builder->where('post_status', '=', 'publish');
        }
    }

    /**
     * Search condition.
     *
     * @param \Illuminate\Database\Query\Builder $builder
     * @param \FootyRoom\Queries\Post\ForumPostQuery $query
     */
    protected function search(Builder $builder, ForumPostQuery $query)
    {
        if ($query->getSearch()) {
            $builder->where('post_title', 'LIKE', "%{$query->getSearch()}%");
        }
    }

    /**
     * Sticky posts condition.
     *
     * @param \Illuminate\Database\Query\Builder $builder
     * @param \FootyRoom\Queries\Post\ForumPostQuery $query
     */
    protected function sticky(Builder $builder, ForumPostQuery $query)
    {
        if ($query->getSticky()) {
            $builder->where('sticky', '>=', $query->getStickyLevel());
        } else {
            $builder->where('sticky', '=', 0);
        }
    }
}