<?php
namespace Misuzu\Comments;

use InvalidArgumentException;
use RuntimeException;
use Index\Data\DbStatementCache;
use Index\Data\IDbConnection;
use Index\Data\IDbResult;
use Misuzu\Pagination;
use Misuzu\Users\UserInfo;

class Comments {
    private IDbConnection $dbConn;
    private DbStatementCache $cache;

    public function __construct(IDbConnection $dbConn) {
        $this->dbConn = $dbConn;
        $this->cache = new DbStatementCache($dbConn);
    }

    public function countCategories(
        UserInfo|string|null $owner = null
    ): int {
        if($owner instanceof UserInfo)
            $owner = $owner->getId();

        $hasOwner = $owner !== null;

        $query = 'SELECT COUNT(*) FROM msz_comments_categories';
        if($hasOwner)
            $query .= ' WHERE owner_id = ?';

        $stmt = $this->cache->get($query);
        $stmt->addParameter(1, $owner);
        $stmt->execute();

        $count = 0;
        $result = $stmt->getResult();

        if($result->next())
            $count = $result->getInteger(0);

        return $count;
    }

    public function getCategories(
        UserInfo|string|null $owner = null,
        ?Pagination $pagination = null
    ): iterable {
        if($owner instanceof UserInfo)
            $owner = $owner->getId();

        $hasOwner = $owner !== null;
        $hasPagination = $pagination !== null;

        $query = 'SELECT category_id, category_name, owner_id, UNIX_TIMESTAMP(category_created), UNIX_TIMESTAMP(category_locked), (SELECT COUNT(*) FROM msz_comments_posts AS cp WHERE cp.category_id = cc.category_id AND comment_deleted IS NULL) AS `category_comments` FROM msz_comments_categories AS cc';
        if($hasOwner)
            $query .= ' WHERE owner_id = ?';
        $query .= ' ORDER BY category_id ASC'; // should order by date but no index on
        if($hasPagination)
            $query .= ' LIMIT ? RANGE ?';

        $stmt = $this->cache->get($query);

        $args = 0;
        if($hasOwner)
            $stmt->addParameter(++$args, $owner);
        if($hasPagination) {
            $stmt->addParameter(++$args, $pagination->getRange());
            $stmt->addParameter(++$args, $pagination->getOffset());
        }

        $stmt->execute();

        return $stmt->getResult()->getIterator(CommentsCategoryInfo::fromResult(...));
    }

    public function getCategory(
        ?string $categoryId = null,
        ?string $name = null,
        CommentsPostInfo|string|null $postInfo = null
    ): CommentsCategoryInfo {
        $hasCategoryId = $categoryId !== null;
        $hasName = $name !== null;
        $hasPostInfo = $postInfo !== null;

        if(!$hasCategoryId && !$hasName && !$hasPostInfo)
            throw new InvalidArgumentException('At least one of the arguments must be set.');
        // there has got to be a better way to do this
        if(($hasCategoryId && ($hasName || $hasPostInfo)) || ($hasName && ($hasCategoryId || $hasPostInfo)) || ($hasPostInfo && ($hasCategoryId || $hasName)))
            throw new InvalidArgumentException('Only one of the arguments may be specified.');

        $query = 'SELECT category_id, category_name, owner_id, UNIX_TIMESTAMP(category_created), UNIX_TIMESTAMP(category_locked), (SELECT COUNT(*) FROM msz_comments_posts AS cp WHERE cp.category_id = cc.category_id AND comment_deleted IS NULL) AS category_comments FROM msz_comments_categories AS cc';
        $value = null;
        if($hasCategoryId) {
            $query .= ' WHERE category_id = ?';
            $value = $categoryId;
        }
        if($hasName) {
            $query .= ' WHERE category_name = ?';
            $value = $name;
        }
        if($hasPostInfo) {
            if($postInfo instanceof CommentsPostInfo) {
                $query .= ' WHERE category_id = ?';
                $value = $postInfo->getCategoryId();
            } else {
                $query .= ' WHERE category_id = (SELECT category_id FROM msz_comments_posts WHERE comment_id = ?)';
                $value = $postInfo;
            }
        }

        $stmt = $this->cache->get($query);
        $stmt->addParameter(1, $value);
        $stmt->execute();
        $result = $stmt->getResult();

        if(!$result->next())
            throw new RuntimeException('Comments category not found.');

        return CommentsCategoryInfo::fromResult($result);
    }

    public function checkCategoryNameExists(string $name): bool {
        $stmt = $this->cache->get('SELECT COUNT(*) FROM msz_comments_categories WHERE category_name = ?');
        $stmt->addParameter(1, $name);
        $stmt->execute();

        $count = 0;
        $result = $stmt->getResult();

        if($result->next())
            $count = $result->getInteger(0);

        return $count > 0;
    }

    public function ensureCategory(string $name, UserInfo|string|null $owner = null): CommentsCategoryInfo {
        if($this->checkCategoryNameExists($name))
            return $this->getCategory(name: $name);
        return $this->createCategory($name, $owner);
    }

    public function createCategory(string $name, UserInfo|string|null $owner = null): CommentsCategoryInfo {
        if($owner instanceof UserInfo)
            $owner = $owner->getId();

        $name = trim($name);
        if(empty($name))
            throw new InvalidArgumentException('$name may not be empty.');

        $stmt = $this->cache->get('INSERT INTO msz_comments_categories (category_name, owner_id) VALUES (?, ?)');
        $stmt->addParameter(1, $name);
        $stmt->addParameter(2, $owner);
        $stmt->execute();

        return $this->getCategory(categoryId: (string)$this->dbConn->getLastInsertId());
    }

    public function deleteCategory(CommentsCategoryInfo|string $category): void {
        if($category instanceof CommentsCategoryInfo)
            $category = $category->getId();

        $stmt = $this->cache->get('DELETE FROM msz_comments_categories WHERE category_id = ?');
        $stmt->addParameter(1, $category);
        $stmt->execute();
    }

    public function updateCategory(
        CommentsCategoryInfo|string $category,
        ?string $name = null,
        bool $updateOwner = false,
        UserInfo|string|null $owner = null
    ): void {
        if($category instanceof CommentsCategoryInfo)
            $category = $category->getId();
        if($owner instanceof UserInfo)
            $owner = $owner->getId();

        if($name !== null) {
            $name = trim($name);
            if(empty($name))
                throw new InvalidArgumentException('$name may not be empty.');
        }

        $stmt = $this->cache->get('UPDATE msz_comments_categories SET category_name = COALESCE(?, category_name), owner_id = IF(?, ?, owner_id) WHERE category_id = ?');
        $stmt->addParameter(1, $name);
        $stmt->addParameter(2, $updateOwner ? 1 : 0);
        $stmt->addParameter(3, $owner ? 1 : 0);
        $stmt->addParameter(4, $category);
        $stmt->execute();
    }

    public function lockCategory(CommentsCategoryInfo|string $category): void {
        if($category instanceof CommentsCategoryInfo)
            $category = $category->getId();

        $stmt = $this->cache->get('UPDATE msz_comments_categories SET category_locked = COALESCE(category_locked, NOW()) WHERE category_id = ?');
        $stmt->addParameter(1, $category);
        $stmt->execute();
    }

    public function unlockCategory(CommentsCategoryInfo|string $category): void {
        if($category instanceof CommentsCategoryInfo)
            $category = $category->getId();

        $stmt = $this->cache->get('UPDATE msz_comments_categories SET category_locked = NULL WHERE category_id = ?');
        $stmt->addParameter(1, $category);
        $stmt->execute();
    }

    public function countPosts(
        CommentsCategoryInfo|string|null $categoryInfo = null,
        CommentsPostInfo|string|null $parentInfo = null,
        UserInfo|string|null $userInfo = null,
        ?bool $replies = null,
        ?bool $deleted = null
    ): int {
        if($categoryInfo instanceof CommentsCategoryInfo)
            $categoryInfo = $categoryInfo->getId();
        if($parentInfo instanceof CommentsPostInfo)
            $parentInfo = $parentInfo->getId();

        $hasCategoryInfo = $categoryInfo !== null;
        $hasParentInfo = $parentInfo !== null;
        $hasUserInfo = $userInfo !== null;
        $hasReplies = $replies !== null;
        $hasDeleted = $deleted !== null;

        $args = 0;
        $query = 'SELECT COUNT(*) FROM msz_comments_posts';

        if($hasParentInfo) {
            ++$args;
            $query .= ' WHERE comment_reply_to = ?';
        }
        if($hasCategoryInfo)
            $query .= sprintf(' %s category_id = ?', ++$args > 1 ? 'AND' : 'WHERE');
        if($hasReplies)
            $query .= sprintf(' %s comment_reply_to %s NULL', ++$args > 1 ? 'AND' : 'WHERE', $replies ? 'IS NOT' : 'IS');
        if($hasDeleted)
            $query .= sprintf(' %s comment_deleted %s NULL', ++$args > 1 ? 'AND' : 'WHERE', $deleted ? 'IS NOT' : 'IS');
        if($hasUserInfo)
            $query .= sprintf(' %s user_id = ?', ++$args > 1 ? 'AND' : 'WHERE');

        $args = 0;
        $stmt = $this->cache->get($query);
        if($hasParentInfo)
            $stmt->addParameter(++$args, $parentInfo);
        elseif($hasCategoryInfo)
            $stmt->addParameter(++$args, $categoryInfo);
        if($hasUserInfo)
            $stmt->addParameter(++$args, $userInfo instanceof UserInfo ? $userInfo->getId() : $userInfo);
        $stmt->execute();

        $result = $stmt->getResult();
        $count = 0;

        if($result->next())
            $count = $result->getInteger(0);

        return $count;
    }

    public function getPosts(
        CommentsCategoryInfo|string|null $categoryInfo = null,
        CommentsPostInfo|string|null $parentInfo = null,
        UserInfo|string|null $userInfo = null,
        ?bool $replies = null,
        ?bool $deleted = null,
        bool $includeRepliesCount = false,
        bool $includeVotesCount = false
    ): iterable {
        if($categoryInfo instanceof CommentsCategoryInfo)
            $categoryInfo = $categoryInfo->getId();
        if($parentInfo instanceof CommentsPostInfo)
            $parentInfo = $parentInfo->getId();

        $hasCategoryInfo = $categoryInfo !== null;
        $hasParentInfo = $parentInfo !== null;
        $hasUserInfo = $userInfo !== null;
        $hasReplies = $replies !== null;
        $hasDeleted = $deleted !== null;

        $args = 0;
        $query = 'SELECT comment_id, category_id, user_id, comment_reply_to, comment_text, UNIX_TIMESTAMP(comment_created), UNIX_TIMESTAMP(comment_pinned), UNIX_TIMESTAMP(comment_edited), UNIX_TIMESTAMP(comment_deleted)';
        if($includeRepliesCount)
            $query .= ', (SELECT COUNT(*) FROM msz_comments_posts AS ccr WHERE ccr.comment_reply_to = cpp.comment_id AND comment_deleted IS NULL) AS `comment_replies`';
        if($includeVotesCount) {
            $query .= ', (SELECT COUNT(*) FROM msz_comments_votes AS cvc WHERE cvc.comment_id = cpp.comment_id) AS `comment_votes_total`';
            $query .= ', (SELECT COUNT(*) FROM msz_comments_votes AS cvc WHERE cvc.comment_id = cpp.comment_id AND comment_vote > 0) AS `comment_votes_positive`';
            $query .= ', (SELECT COUNT(*) FROM msz_comments_votes AS cvc WHERE cvc.comment_id = cpp.comment_id AND comment_vote < 0) AS `comment_votes_negative`';
        }
        $query .= ' FROM msz_comments_posts AS cpp';

        if($hasParentInfo) {
            ++$args;
            $query .= ' WHERE comment_reply_to = ?';
        }
        if($hasCategoryInfo)
            $query .= sprintf(' %s category_id = ?', ++$args > 1 ? 'AND' : 'WHERE');
        if($hasReplies)
            $query .= sprintf(' %s comment_reply_to %s NULL', ++$args > 1 ? 'AND' : 'WHERE', $replies ? 'IS NOT' : 'IS');
        if($hasDeleted)
            $query .= sprintf(' %s comment_deleted %s NULL', ++$args > 1 ? 'AND' : 'WHERE', $deleted ? 'IS NOT' : 'IS');
        if($hasUserInfo)
            $query .= sprintf(' %s user_id = ?', ++$args > 1 ? 'AND' : 'WHERE');

        // this should really not be implicit like this
        if($hasParentInfo)
            $query .= ' ORDER BY comment_deleted ASC, comment_pinned DESC, comment_created ASC';
        elseif($hasCategoryInfo)
            $query .= ' ORDER BY comment_deleted ASC, comment_pinned DESC, comment_created DESC';
        else
            $query .= ' ORDER BY comment_created DESC';

        $args = 0;
        $stmt = $this->cache->get($query);
        if($hasParentInfo)
            $stmt->addParameter(++$args, $parentInfo);
        elseif($hasCategoryInfo)
            $stmt->addParameter(++$args, $categoryInfo);
        if($hasUserInfo)
            $stmt->addParameter(++$args, $userInfo instanceof UserInfo ? $userInfo->getId() : $userInfo);
        $stmt->execute();

        return $stmt->getResult()->getIterator(fn($result) => CommentsPostInfo::fromResult($result, $includeRepliesCount, $includeVotesCount));
    }

    public function getPost(
        string $postId,
        bool $includeRepliesCount = false,
        bool $includeVotesCount = false
    ): CommentsPostInfo {
        $query = 'SELECT comment_id, category_id, user_id, comment_reply_to, comment_text, UNIX_TIMESTAMP(comment_created), UNIX_TIMESTAMP(comment_pinned), UNIX_TIMESTAMP(comment_edited), UNIX_TIMESTAMP(comment_deleted)';
        if($includeRepliesCount)
            $query .= ', (SELECT COUNT(*) FROM msz_comments_posts AS ccr WHERE ccr.comment_reply_to = cpp.comment_id AND comment_deleted IS NULL) AS `comment_replies`';
        if($includeVotesCount) {
            $query .= ', (SELECT COUNT(*) FROM msz_comments_votes AS cvc WHERE cvc.comment_id = cpp.comment_id) AS `comment_votes_total`';
            $query .= ', (SELECT COUNT(*) FROM msz_comments_votes AS cvc WHERE cvc.comment_id = cpp.comment_id AND comment_vote > 0) AS `comment_votes_positive`';
            $query .= ', (SELECT COUNT(*) FROM msz_comments_votes AS cvc WHERE cvc.comment_id = cpp.comment_id AND comment_vote < 0) AS `comment_votes_negative`';
        }
        $query .= ' FROM msz_comments_posts AS cpp WHERE comment_id = ?';

        $stmt = $this->cache->get($query);
        $stmt->addParameter(1, $postId);
        $stmt->execute();

        $result = $stmt->getResult();
        if(!$result->next())
            throw new RuntimeException('No comment with that ID exists.');

        return CommentsPostInfo::fromResult($result, $includeRepliesCount, $includeVotesCount);
    }

    public function createPost(
        CommentsCategoryInfo|string|null $category,
        CommentsPostInfo|string|null $parent,
        UserInfo|string|null $user,
        string $body,
        bool $pin = false
    ): CommentsPostInfo {
        if($category instanceof CommentsCategoryInfo)
            $category = $category->getId();
        if($parent instanceof CommentsPostInfo) {
            if($category === null)
                $category = $parent->getCategoryId();
            elseif($category !== $parent->getCategoryId())
                throw new InvalidArgumentException('$parent belongs to a different category than where this post is attempted to be created.');
            $parent = $parent->getId();
        }
        if($category === null)
            throw new InvalidArgumentException('$category is null; at least a $category or $parent must be specified.');
        if($user instanceof UserInfo)
            $user = $user->getId();
        if(empty(trim($body)))
            throw new InvalidArgumentException('$body may not be empty.');

        $stmt = $this->cache->get('INSERT INTO msz_comments_posts (category_id, user_id, comment_reply_to, comment_text, comment_pinned) VALUES (?, ?, ?, ?, IF(?, NOW(), NULL))');
        $stmt->addParameter(1, $category);
        $stmt->addParameter(2, $user);
        $stmt->addParameter(3, $parent);
        $stmt->addParameter(4, $body);
        $stmt->addParameter(5, $pin ? 1 : 0);
        $stmt->execute();

        return $this->getPost((string)$this->dbConn->getLastInsertId());
    }

    public function deletePost(CommentsPostInfo|string $infoOrId): void {
        if($infoOrId instanceof CommentsPostInfo)
            $infoOrId = $infoOrId->getId();

        $stmt = $this->cache->get('UPDATE msz_comments_posts SET comment_deleted = COALESCE(comment_deleted, NOW()) WHERE comment_id = ?');
        $stmt->addParameter(1, $infoOrId);
        $stmt->execute();
    }

    public function nukePost(CommentsPostInfo|string $infoOrId): void {
        if($infoOrId instanceof CommentsPostInfo)
            $infoOrId = $infoOrId->getId();

        $stmt = $this->cache->get('DELETE FROM msz_comments_posts WHERE comment_id = ?');
        $stmt->addParameter(1, $infoOrId);
        $stmt->execute();
    }

    public function restorePost(CommentsPostInfo|string $infoOrId): void {
        if($infoOrId instanceof CommentsPostInfo)
            $infoOrId = $infoOrId->getId();

        $stmt = $this->cache->get('UPDATE msz_comments_posts SET comment_deleted = NULL WHERE comment_id = ?');
        $stmt->addParameter(1, $infoOrId);
        $stmt->execute();
    }

    public function editPost(CommentsPostInfo|string $infoOrId, string $body): void {
        if($infoOrId instanceof CommentsPostInfo)
            $infoOrId = $infoOrId->getId();

        if(empty(trim($body)))
            throw new InvalidArgumentException('$body may not be empty.');

        $stmt = $this->cache->get('UPDATE msz_comments_posts SET comment_text = ?, comment_edited = NOW() WHERE comment_id = ?');
        $stmt->addParameter(1, $body);
        $stmt->addParameter(2, $infoOrId);
        $stmt->execute();
    }

    public function pinPost(CommentsPostInfo|string $infoOrId): void {
        if($infoOrId instanceof CommentsPostInfo)
            $infoOrId = $infoOrId->getId();

        $stmt = $this->cache->get('UPDATE msz_comments_posts SET comment_pinned = COALESCE(comment_pinned, NOW()) WHERE comment_id = ?');
        $stmt->addParameter(1, $infoOrId);
        $stmt->execute();
    }

    public function unpinPost(CommentsPostInfo|string $infoOrId): void {
        if($infoOrId instanceof CommentsPostInfo)
            $infoOrId = $infoOrId->getId();

        $stmt = $this->cache->get('UPDATE msz_comments_posts SET comment_pinned = NULL WHERE comment_id = ?');
        $stmt->addParameter(1, $infoOrId);
        $stmt->execute();
    }

    public function getPostVote(
        CommentsPostInfo|string $post,
        UserInfo|string|null $user
    ): CommentsPostVoteInfo {
        if($post instanceof CommentsPostInfo)
            $post = $post->getId();
        if($user instanceof UserInfo)
            $user = $user->getId();

        // SUM() here makes it so a result row is always returned, albeit with just NULLs
        $stmt = $this->cache->get('SELECT comment_id, user_id, SUM(comment_vote) FROM msz_comments_votes WHERE comment_id = ? AND user_id = ?');
        $stmt->addParameter(1, $post);
        $stmt->addParameter(2, $user);
        $stmt->execute();

        $result = $stmt->getResult();
        if(!$result->next())
            throw new RuntimeException('Failed to fetch vote info.');

        return new CommentsPostVoteInfo($result);
    }

    public function addPostVote(
        CommentsPostInfo|string $post,
        UserInfo|string $user,
        int $weight
    ): void {
        if($weight === 0)
            return;
        if($post instanceof CommentsPostInfo)
            $post = $post->getId();
        if($user instanceof UserInfo)
            $user = $user->getId();

        $stmt = $this->cache->get('REPLACE INTO msz_comments_votes (comment_id, user_id, comment_vote) VALUES (?, ?, ?)');
        $stmt->addParameter(1, $post);
        $stmt->addParameter(2, $user);
        $stmt->addParameter(3, $weight);
        $stmt->execute();
    }

    public function addPostPositiveVote(CommentsPostInfo|string $post, UserInfo|string $user): void {
        $this->addPostVote($post, $user, 1);
    }

    public function addPostNegativeVote(CommentsPostInfo|string $post, UserInfo|string $user): void {
        $this->addPostVote($post, $user, -1);
    }

    public function removePostVote(
        CommentsPostInfo|string $post,
        UserInfo|string $user
    ): void {
        if($post instanceof CommentsPostInfo)
            $post = $post->getId();
        if($user instanceof UserInfo)
            $user = $user->getId();

        $stmt = $this->cache->get('DELETE FROM msz_comments_votes WHERE comment_id = ? AND user_id = ?');
        $stmt->addParameter(1, $post);
        $stmt->addParameter(2, $user);
        $stmt->execute();
    }
}