<?php

namespace FootyRoom\Queries\Comment;

use Exception;
use FootyRoom\Support\AutoMapper;
use Illuminate\Database\Connection;

class CommentQueryHandler
{
    /**
     * @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\Comment\CommentCountQuery $query
     *
     * @return int
     */
    public function count(CommentCountQuery $query)
    {
        $sql = [
            'filter' => $this->filterSql($query),
            'unpublished' => $this->unpublishedSql($query),
            'before' => '',
        ];

        if ($query->getBefore()) {
            $sql['before'] = 'AND id < '.(int) $query->getBefore().' ORDER BY id '.$query->getOrder();
        }

        return (int) $this->mysql->select(
            "SELECT COUNT(*) AS count
             FROM comments
             WHERE discussion_id = :discussionId {$sql['filter']} {$sql['unpublished']} {$sql['before']}",
            [
                'discussionId' => $query->getDiscussionId(),
            ]
        )[0]->count;
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Comment\CommentQuery $query
     *
     * @return \FootyRoom\Queries\Comment\Comment[]
     */
    public function find(CommentQuery $query): array
    {
        if (!$query->getDiscussionId() && !$query->getIds()) {
            throw Exception('Either discussion id or individual comment ids must to be specified');
        }

        $bindings = [
            'offset' => $query->getOffset(),
            'limit' => $query->getLimit(),
        ];

        if ($query->getDiscussionId()) {
            $bindings['discussionId'] = $query->getDiscussionId();
        }

        $sql = [
            'select' => $this->selectSql($query).$this->selectUserInfoSql($query),
            'userInfoJoin' => $this->userInfoJoinSql($query),
            'filter' => $this->filterSql($query),
            'unpublished' => $this->unpublishedSql($query),
            'since' => $this->sinceSql($query, $bindings),
            'meta' => $this->metaSql($query, $bindings),
            'threadedWhere' => $query->getThreaded() ? 'AND parent = "0"' : '',
            'threadedJoin' => $query->getThreaded() ? 'OR c.parent = q.id' : '',
            'whereDiscussion'=> $query->getDiscussionId() ? 'discussion_id = :discussionId' : '',
            'whereIds' => $query->getIds() ? 'id IN('.self::bindParamArray('id', $query->getIds(), $bindings).')' : '',
            'without' => '',
        ];

        if ($query->getThreadOf()) {
            $sql['threadedWhere'] = 'AND id = '.(int) $query->getThreadOf();
            $sql['threadedJoin'] =  'OR (q.parent = c.parent AND q.parent != 0) OR q.parent = c.id OR q.id = c.parent';
        }

        if ($query->getWithout()) {
            $sql['without'] =  'HAVING c.id != '.(int) $query->getWithout();
        }

        $comments = $this->mysql->select(
            "SELECT {$sql['select']}
             FROM (
                 SELECT c.*
                 FROM (
                     SELECT id, parent
                     FROM comments
                     WHERE {$sql['whereDiscussion']} {$sql['whereIds']}
                        {$sql['threadedWhere']} {$sql['filter']} {$sql['since']} {$sql['unpublished']}
                     ORDER BY id {$query->getOrder()} LIMIT :offset, :limit
                 ) AS q
                 JOIN comments AS c
                     ON c.id = q.id {$sql['threadedJoin']} {$sql['filter']} {$sql['unpublished']}
                 {$sql['without']}
             ) as r
             LEFT JOIN wp_comments_extra AS cx
                ON cx.comment_id = r.id
             LEFT JOIN wp_commentmeta AS cm
                ON cm.comment_id = r.id AND (cm.meta_key = 'revision' {$sql['meta']})
             {$sql['userInfoJoin']}
             GROUP BY r.id
             ORDER BY r.id {$query->getOrder()}",
            $bindings
        );

        if ($query->getWithUserInfo()) {
            foreach ($comments as $comment) {
                $comment = $this->mapUserInfo($comment);
            }
        }

        $comments = $this->toModels($comments);

        if ($query->getThreaded()) {
            $comments = $this->threadComments($comments);
        }

        return $comments;
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Comment\OneCommentQuery $query
     *
     * @return \FootyRoom\Queries\Comment\Comment|null
     */
    public function findOne(OneCommentQuery $query)
    {
        $bindings = [
            'commentId' => $query->getCommentId(),
        ];

        $sql = [
            'select' => $this->selectSql().$this->selectUserInfoSql($query),
            'meta' => $this->metaSql($query, $bindings),
            'userInfoJoin' => $this->userInfoJoinSql($query),
        ];

        $comments = $this->mysql->select(
            "SELECT {$sql['select']}
             FROM comments AS r
             {$sql['userInfoJoin']}
             LEFT JOIN wp_comments_extra AS cx ON cx.comment_ID = r.id
             LEFT JOIN wp_commentmeta AS cm
                ON cm.comment_id = r.id AND (cm.meta_key = 'revision' {$sql['meta']})
             WHERE r.id = :commentId
             GROUP BY r.id",
            $bindings
        );

        if ($query->getWithUserInfo()) {
            foreach ($comments as $comment) {
                $comment = $this->mapUserInfo($comment);
            }
        }

        $comments = $this->toModels($comments);

        return isset($comments[0]) ? $comments[0] : null;
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Comment\TopCommentsQuery $query
     *
     * @return \FootyRoom\Queries\Comment\Comment[]
     */
    public function findTop(TopCommentsQuery $query)
    {
        $bindings = [
            'discussionId' => $query->getDiscussionId(),
        ];

        $sql = [
            'select' => $this->selectSql($query).$this->selectUserInfoSql($query),
            'filter' => $this->filterSql($query),
            'userInfoJoin' => $this->userInfoJoinSql($query),
            'meta' => $this->metaSql($query, $bindings),
        ];

        $comments = $this->mysql->select(
            "SELECT {$sql['select']}
             FROM comments AS r
             {$sql['userInfoJoin']}
             LEFT JOIN wp_comments_extra AS cx ON cx.comment_ID = r.id
             LEFT JOIN wp_commentmeta AS cm
                ON cm.comment_id = r.id AND (cm.meta_key = 'revision' {$sql['meta']})
             WHERE r.discussion_id = :discussionId AND r.karma >= 4 {$sql['filter']}
             GROUP BY r.id
             ORDER BY r.karma DESC
             LIMIT 0,3",
            $bindings
        );

        if ($query->getWithUserInfo()) {
            foreach ($comments as $comment) {
                $comment = $this->mapUserInfo($comment);
            }
        }

        return $this->toModels($comments);
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Comment\CommentParentQuery $query
     *
     * @return \FootyRoom\Queries\Comment\Comment|null
     */
    public function findParent(CommentParentQuery $query)
    {
        $bindings = [
            'commentId' => $query->getCommentId(),
        ];

        $sql = [
            'select' => $this->selectSql().$this->selectUserInfoSql($query),
            'userInfoJoin' => $this->userInfoJoinSql($query),
            'meta' => $this->metaSql($query, $bindings),
        ];

        $comments = $this->mysql->select(
            "SELECT {$sql['select']}
             FROM comments AS c
             LEFT JOIN comments AS r ON r.id = c.parent
             {$sql['userInfoJoin']}
             LEFT JOIN wp_comments_extra AS cx ON cx.comment_ID = r.id
             LEFT JOIN wp_commentmeta AS cm
                ON cm.comment_id = r.id AND (cm.meta_key = 'revision' {$sql['meta']})
             WHERE c.id = :commentId
             GROUP BY r.id",
            $bindings
        );
        
        if ($query->getWithUserInfo()) {
            foreach ($comments as $comment) {
                $comment = $this->mapUserInfo($comment);
            }
        }

        $comments = $this->toModels($comments);

        return isset($comments[0]) ? $comments[0] : null;
    }

    /**
     * SQL for list of columns to select.
     *
     * @return string
     */
    protected function selectSql()
    {
        // TODO: find the best way to select column html only for forum comment queries (performance)
        // One way is to always select html only, but for that we need to check/update our data.
        return
            'r.id as id,
             r.datetime as date,
             r.content as content,
             r.html as html,
             r.author as author,
             r.parent as parentId,
             r.status as approved,
             r.user_id as userId,
             r.karma as karma,
             r.discussion_id as discussionId,
             cx.teamname,
             GROUP_CONCAT(cm.meta_key SEPARATOR 0x1D) AS meta_keys,
             GROUP_CONCAT(cm.meta_value SEPARATOR 0x1D) AS meta_values';
    }

    /**
     * SQL for selecting user info columns.
     *
     * @param \FootyRoom\Queries\Comment\WithUserQuery $query
     *
     * @return string
     */
    protected function selectUserInfoSql(WithUserQuery $query)
    {
        $select = '';

        if ($query->getWithUserInfo()) {
            $select .= ', u.count_posts as postCount,
                u.count_comments as commentCount,
                u.clubTeam,
                u.nationalTeam as nationalTeam,
                u.user_role as userRole,
                u.status as userStatus';
        }

        return $select;
    }

    /**
     * SQL for joining user info table.
     *
     * @param \FootyRoom\Queries\Comment\WithUserQuery $query
     *
     * @return string
     */
    protected function userInfoJoinSql(WithUserQuery $query)
    {
        $join = '';

        if ($query->getWithUserInfo()) {
            $join .= 'LEFT JOIN fr_users AS u ON u.user_id = r.user_id';
        }

        return $join;
    }

    /**
     * SQL for filtering comments by users.
     *
     * @param object $query
     *
     * @return string
     */
    protected function filterSql($query)
    {
        $sql = '';

        if ($query->getFilter() === 'users') {
            $sql .= ' AND user_id > 0 ';
        } elseif ($query->getFilter() === 'guests') {
            $sql .= ' AND user_id = 0 ';
        }

        return $sql;
    }

    /**
     * SQL for unpublished comments condition.
     *
     * @param object $query
     *
     * @return string
     */
    protected function unpublishedSql($query)
    {
        $sql = '';

        if ($query->getUnpublished() !== true) {
            $sql .= ' AND status = "1" ';
        }

        return $sql;
    }

    /**
     * SQL for since commentId condition.
     *
     * @param object $query
     * @param array &$bindings
     *
     * @return string
     */
    protected function sinceSql($query, &$bindings)
    {
        $sql = '';

        if ($query->getSinceId() > 0) {
            $sql = ' AND id '.($query->getOrder() === 'asc' ? '>=' : '<=').' :since';

            $bindings['since'] = $query->getSinceId();
        }

        return $sql;
    }

    /**
     * SQL for requesting comment meta.
     *
     * @param object $query
     * @param array &$bindings
     *
     * @return string
     */
    protected function metaSql($query, &$bindings)
    {
        $sql = '';

        if ($query->getMeta()) {
            foreach ($query->getMeta() as $meta) {
                $sql .= " OR cm.meta_key = :meta_$meta ";
                $bindings[":meta_$meta"] = $meta;
            }
        }

        return $sql;
    }

    /**
     * Processes raw comment meta values and translates them into array.
     *
     * @param object $comment
     *
     * @return object
     */
    protected function extractCommentMeta($comment)
    {
        $metaKeys = explode("\x1D", $comment->meta_keys);
        $metaValues = explode("\x1D", $comment->meta_values);

        $comment->meta = [];

        for ($i = 0; $i < count($metaKeys) && $i < count($metaValues); $i++) {
            if ($metaKeys[$i] == '') {
                continue;
            }

            $comment->meta[$metaKeys[$i]][] = $metaValues[$i];
        }

        return $comment;
    }

    /**
     * Mapping user info and translates them into array.
     *
     * @param object $object
     *
     * @return object
     */
    protected function mapUserInfo($object)
    {
        $object->userInfo = AutoMapper::map($object, CommentUserInfo::class);

        return $object;
    }

    /**
     * Maps rows to models.
     *
     * @param array $comments
     *
     * @return \FootyRoom\Queries\Comment\Comment[]
     */
    protected function toModels($comments)
    {
        $commentModels = [];

        foreach ($comments as $comment) {
            $commentModels[] = $this->mapToModel($comment);
        }

        return $commentModels;
    }

    /**
     * Maps row to model.
     *
     * @param object $object
     *
     * @return \FootyRoom\Queries\Comment\Comment
     */
    protected function mapToModel($object)
    {
        $object = $this->extractCommentMeta($object);

        $comment = new \FootyRoom\Queries\Comment\Comment();

        foreach ($object as $property => $value) {
            if ($property === 'replies' && is_array($value)) {
                foreach ($value as $reply) {
                    $comment->replies[] = $this->mapToModel($reply);
                }
            } elseif (property_exists($comment, $property)) {
                $comment->{$property} = $value;
            }
        }

        return $comment;
    }

    /**
     * Given a list of comments it will go through them and put children
     * comments into parents as replies.
     *
     * @param \FootyRoom\Queries\Comment\Comment[] $comments
     *
     * @return \FootyRoom\Queries\Comment\Comment[]
     */
    protected function threadComments($comments)
    {
        $repliesFor = [];

        foreach ($comments as $comment) {
            if ($comment->parentId > 0) {
                $repliesFor[$comment->parentId][] = $comment;
            }
        }

        $threadedComments = [];

        foreach ($comments as $comment) {
            if (isset($repliesFor[$comment->id])) {
                $comment->replies = $repliesFor[$comment->id];
            }

            if ($comment->parentId === 0) {
                $threadedComments[] = $comment;
            }
        }

        return $threadedComments;
    }

    /**
     * Determines whether a recent comment in specified post from specified user with
     * specified content exists.
     *
     * @param \FootyRoom\Queries\Comment\DuplicateCommentQuery $query
     *
     * @return bool
     */
    public function hasRecentDuplicate(DuplicateCommentQuery $query)
    {
        $qb = $this->mysql

        ->table('comments')
        ->select('id')
        ->where('discussion_id', '=', $query->getDiscussionId())
        ->where('status', '!=', 'trash')
        ->where('content', '=', $query->getContent())
        ->where('user_id', '=', $query->getUserId())
        ->where(
            'datetime',
            '>',
            $this->mysql->raw('DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 SECOND)')
        );

        $comment = $qb->first();

        return $comment !== null;
    }

    /**
     * Query handler.
     *
     * @param \FootyRoom\Queries\Comment\CommentsInPeriodQuery $query
     *
     * @return int
     */
    public function commentsInPeriod(CommentsInPeriodQuery $query)
    {
        return $this->mysql

        ->table('comments')
        ->select([
            $this->mysql->raw('COUNT(user_id) as count'),
        ])
        ->where(
            'datetime',
            '>',
            $this->mysql->raw("DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$query->getPeriod()} SECOND)")
        )
        ->where('user_id', '=', $query->getUserId())
        ->value('count');
    }

    /*
     * Adds parameter bindings into assoc array and returns a corresponding
     * query string for those parameters.
     *
     * @param string $prefix
     * @param mixed[] $values
     * @param array $bindArray
     *
     * @return string
     */
    protected static function bindParamArray($prefix, $values, &$bindArray)
    {
        $query = '';

        foreach ($values as $index => $value) {
            $query .= ":$prefix$index,";

            $bindArray[$prefix.$index] = $value;
        }

        return rtrim($query, ',');
    }
}