movie/app/Models/Comment.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Kalnoy\Nestedset\NodeTrait;
use App\Http\Traits\PagingTrait;
use App\Models\DTO\SearchData;

class Comment extends Model
{
    use NodeTrait;
    use PagingTrait;

    protected $table = 'tb_comment';
    protected $primaryKey = 'id';

    public $keyType = 'int';
    public $incrementing = true;
    public $timestamps = true;

    const CREATED_AT = 'created_at';
    const UPDATED_AT = 'updated_at';
    const DELETED_AT = 'deleted_at';

    protected $guarded = [];

    public function board()
    {
        return $this->belongsTo(Board::class)->withDefault();
    }

    public function post()
    {
        return $this->belongsTo(Post::class)->withDefault();
    }

    public function user()
    {
        return $this->belongsTo(User::class)->withDefault();
    }

    public function commentblame()
    {
        return $this->hasMany(CommentBlame::Class);
    }

    public function commentDeleted()
    {
        return $this->hasMany(CommentDeleted::Class);
    }

    public function commentHistory()
    {
        return $this->hasMany(CommentHistory::Class);
    }

    public function commentLike()
    {
        return $this->hasMany(CommentLike::Class);
    }

    public function commentMeta()
    {
        return $this->hasOne(CommentMeta::Class);
    }

    public function getParentIdName(): string
    {
        return 'parent_id';
    }

    public function getLftName(): string
    {
        return 'lft';
    }

    public function getRgtName(): string
    {
        return 'rgt';
    }

    /**
     * 댓글 조회
     */
    public function data(SearchData $params): object
    {
        $query = $this->query();
        $query->select(
            'tb_comment.*',
            'tb_board.name AS boardName',
            'tb_board.code',
            'tb_post.subject',
            'users.id',
            'users.sid',
            'users.name',
            'users.email'
        );

        if($params->keyword) {
            switch ($params->field) {
                case 'tb_comment.id' :
                case 'tb_comment.post_id' :
                case 'users.id' :
                case 'users.sid' :
                    $query->where($params->field, '=', $params->keyword);
                    break;
                case 'tb_comment.content' :
                case 'users.name' :
                case 'users.email' :
                    $query->where($params->field, 'LIKE', "%{$params->keyword}%");
                    break;
            }
        }

        $query->where('tb_comment.is_delete', '=', 0);

        if($params->startDate || $params->endDate) {
            if($params->startDate) {
                $query->where('tb_comment.created_at', '>=', $params->startDate . ' 00:00:00');
            }
            if($params->endDate) {
                $query->where('tb_comment.created_at', '<=', $params->endDate . ' 23:59:59');
            }
        }

        if($params->boardID) {
            $query->where('tb_comment.board_id', '=', $params->boardID);
        }

        $query->join('tb_board', 'tb_board.id', '=', 'tb_comment.board_id');
        $query->join('tb_post', 'tb_post.id', '=', 'tb_comment.post_id');
        $query->leftJoin('users', 'users.id', '=', 'tb_comment.user_id');
        $query->orderByDesc('tb_comment.id');

        $list = $query->paginate($params->perPage, ['*'], 'page', $params->page);
        $total = $this->count();
        $rows = $list->count();

        return (object)[
            'total' => $total,
            'rows' => $rows,
            'list' => $list
        ];
    }

    /**
     * 댓글 전체 개수
     */
    public function total(int $postID): int
    {
        return $this->where('post_id', $postID)->count();
    }

    /**
     * 댓글 등록
     */
    public function register(array $params): Comment
    {
        return $this->create($params);
    }

    /**
     * 댓글 수정
     */
    public function updater(int $commentID, array $params): Comment
    {
        return $this->updateOrCreate([$this->primaryKey => $commentID], $params);
    }

    /**
     * 답글 등록
     */
    public function reply(int $commentID, array $params): Comment
    {
        $parent = $this->findParent($commentID);
        $node = $parent->children()->create($params);
        $node->depth = ($parent->depth + 1);
        $node->save();

        return $node;
    }

    /**
     * 댓글 조회
     */
    public function get(int $commentID): Comment
    {
        return $this->from('tb_comment AS CMT')->select('CMT.*')
                ->with(['board', 'post', 'user'])->where([
                    ['CMT.id', $commentID],
                    ['CMT.is_delete', 0]
                ])->firstOrNew();
    }

    /**
     * 댓글 조회
     */
    public function getList(string $code, int $postID, int $page, int $offset, int $perPage, int $sort): object
    {
        $orderBy = $this->sortQuery($sort);

        $sql = "
            SELECT
                COUNT(CMT.id) AS total
            FROM tb_comment CMT
                JOIN tb_post PST ON PST.id = CMT.post_id
                JOIN tb_board BRD ON BRD.id = PST.board_id
            WHERE TRUE
                AND BRD.code = ?
                AND PST.id = ?
                AND CASE WHEN (CMT.is_delete = 1 AND CMT.reply <= 0 AND CMT.depth <= 0) THEN FALSE ELSE TRUE END
        ";

        $total = DB::selectOne($sql, [$code, $postID])->total;

        $sql = "
            SELECT
                @replyRows := (SELECT (COUNT(id) - 1) FROM tb_comment WHERE lft BETWEEN CMT.lft AND CMT.rgt) AS replyRows,
                @depth := (SELECT (COUNT(*) - 1) FROM tb_comment WHERE CMT.rgt BETWEEN lft AND rgt),
                CMT.*,
                USR.name,
                USR.thumb,
                USR.icon,
                IFNULL(CLIK.`type`, 0) AS likeType
            FROM tb_comment CMT
                JOIN tb_comment PCMT ON CMT.lft BETWEEN PCMT.lft AND PCMT.rgt
                JOIN tb_post PST ON PST.id = CMT.post_id
                JOIN tb_board BRD ON BRD.id = PST.board_id
                LEFT JOIN tb_comment_like CLIK ON CLIK.post_id = CMT.post_id AND CMT.id = CLIK.comment_id
                LEFT JOIN users USR ON USR.id = CMT.user_id
            WHERE TRUE
                AND BRD.code = ?
                AND PST.id = ?
                AND CASE WHEN (CMT.is_delete = 1 AND @replyRows <= 0 AND @depth <= 0) THEN FALSE ELSE TRUE END
            GROUP BY
                CMT.id,
                CLIK.`type`
            ORDER BY
                {$orderBy}
            LIMIT ?, ?;
        ";

        $list = $this->getPaginator(
            DB::select($sql, [$code, $postID, $offset, $perPage]), $total, $perPage, $page
        );

        return (object)[
            'total' => $total,
            'list' => $list
        ];
    }

    /**
     * 회원 댓글 목록 조회
     */
    public function getUserList(int $userID, SearchData $params): object
    {
        $sql = "
            SELECT
                COUNT(CMT.id) AS total
            FROM tb_comment CMT
                JOIN tb_post PST ON PST.id = CMT.post_id
                JOIN tb_board BRD ON BRD.id = PST.board_id
                LEFT JOIN users USR ON USR.id = CMT.user_id
            WHERE TRUE
                AND USR.id = ?
                AND CMT.is_delete = 0;
        ";

        $total = DB::selectOne($sql, [$userID])->total;

        $sql = "
            SELECT
                @replyRows := (SELECT (COUNT(id) - 1) FROM tb_comment WHERE lft BETWEEN CMT.lft AND CMT.rgt) AS replyRows,
                @depth := (SELECT (COUNT(*) - 1) FROM tb_comment WHERE CMT.rgt BETWEEN lft AND rgt),
                CMT.*,
                BRD.code,
                PST.subject, PST.comment_rows, PST.file_rows, PST.image_rows, PST.link_rows,
                USR.name, USR.thumb, USR.icon
            FROM tb_comment CMT
                JOIN tb_comment PCMT ON CMT.lft BETWEEN PCMT.lft AND PCMT.rgt
                JOIN tb_post PST ON PST.id = CMT.post_id
                JOIN tb_board BRD ON BRD.id = CMT.board_id
                LEFT JOIN users USR ON USR.id = CMT.user_id
            WHERE TRUE
                AND USR.id = ?
                AND CMT.is_delete = 0
            GROUP BY
                CMT.id
            ORDER BY
                CMT.lft ASC, CMT.created_at ASC
            LIMIT ?, ?;
        ";

        $list = $this->getPaginator(
            DB::select($sql, [$userID, $params->offset, $params->perPage]), $total, $params->perPage, $params->page
        );

        return (object)[
            'total' => $total,
            'list' => $list
        ];
    }

    /**
     * 정렬 순서
     */
    private function sortQuery(int $sort): string
    {
        switch($sort) {
            default:case 1: // 등록순
                return 'CMT.lft ASC, CMT.created_at ASC';
            case 2: // 최신순
                return 'CMT.rgt DESC';
            case 3: // 공감 순
                return '
                    CASE WHEN (CMT.parent_id IS NULL AND CMT.reply <= 0) THEN CMT.like
                         WHEN TRUE THEN TRUE
                              ELSE NULL
                         END DESC
                    , CMT.lft ASC
                ';
            case 4: // 댓글 순
                return '
                    CASE WHEN (CMT.parent_id IS NULL AND CMT.reply <= 0) THEN CMT.like
                         WHEN TRUE THEN TRUE
                              ELSE NULL
                         END DESC
                    , CMT.lft ASC
                ';
        }
    }

    /**
     * 댓글 조회
     */
    public function findParent(int $commentID)
    {
        return $this->newQuery()->withDepth()->find($commentID);
    }

    /**
     * 댓글 신고 수 증가
     */
    public function increaseBlame(int $commentID): int
    {
        return $this->where('id', $commentID)->increment('blame');
    }

    /**
     * 댓글 신고 수 증감
     */
    public function decreaseBlame(int $commentID): int
    {
        return $this->where('id', $commentID)->where('blame', '>', '0')->decrement('blame', 1);
    }

    /**
     * 댓글 좋아요 수 증가
     */
    public function increaseLike(int $commentID): int
    {
        return $this->where('id', $commentID)->increment('like');
    }

    /**
     * 댓글 좋아요 수 증감
     */
    public function decreaseLike(int $commentID): int
    {
        return $this->where('id', $commentID)->where('like', '>', '0')->decrement('like', 1);
    }

    /**
     * 댓글 싫어요 수 증가
     */
    public function increaseDisLike(int $commentID): int
    {
        return $this->where('id', $commentID)->increment('dislike');
    }

    /**
     * 댓글 싫어요 수 증감
     */
    public function decreaseDisLike(int $commentID): int
    {
        return $this->where('id', $commentID)->where('dislike', '>', '0')->decrement('dislike');
    }

    /**
     * 댓글 수 증가
     */
    public function increaseReply(int $commentID): int
    {
        return $this->where('id', $commentID)->increment('reply');
    }

    /**
     * 댓글 수 증감
     */
    public function decreaseReply(int $commentID): int
    {
        return $this->where('id', $commentID)->where('reply', '>', '0')->decrement('reply');
    }

    /**
     * 댓글 개수 갱신
     */
    public function updateCommentRows(int $commentID): int
    {
        $sql = "
            UPDATE tb_comment A, (
                SELECT
                    (COUNT(*) - 1) AS total
                FROM tb_comment CMT
                JOIN tb_comment PCMT ON CMT.lft BETWEEN PCMT.lft AND PCMT.rgt
                WHERE PCMT.id = :id
            ) B
            SET A.reply = B.total
            WHERE A.id = :id;
        ";

        return DB::update($sql, [
            'id' => $commentID
        ]);
    }

    /**
     * 댓글의 현재 페이지 번호
     */
    public function findPageNumber(int $commentID, int $perPage = DEFAULT_LIST_PER_PAGE): int
    {
        $sql = "
            SELECT
                D.cnt,
                COALESCE(CEIL((D.cnt - 1) / :perPage), 1) AS page
            FROM (
                SELECT
                    *, @num := (@num + 1) AS cnt
                FROM
                    tb_comment,
                    (SELECT @num := 0) AS N1,
                    (SELECT
                        @boardID := board_id, @postID := post_id, @commentID := id
                        FROM tb_comment WHERE id = :commentID
                    ) AS N2
                WHERE TRUE
                AND board_id = @boardID
                AND post_id = @postID
                ORDER BY lft
            ) D
            WHERE D.id = @commentID;
        ";

        return DB::selectOne($sql, [
            'commentID' => $commentID,
            'perPage' => $perPage
        ])->page;
    }

    /**
     * 댓글 존재 확인
     */
    public function isExists(int $commentID): int
    {
        $sql = "
            SELECT IF(COUNT(*) > 0, 1, 0) AS isExists
            FROM tb_comment CMT JOIN tb_post PST ON PST.id = CMT.post_id
            WHERE CMT.id = ? AND CMT.is_delete = 0;
        ";
        return DB::selectOne($sql, [$commentID])->isExists;
    }

    /**
     * 내가 작성한 댓글인지 확인
     */
    public function getChildRows(int $commentID): int
    {
        $sql = "
            SELECT (COUNT(CMT.comment_id) - 1) AS `rows` FROM tb_comment CMT
            JOIN tb_comment PCMT ON CMT.lft BETWEEN PCMT.lft AND PCMT.rgt
            WHERE PCMT.id = ?
        ";
        return DB::selectOne($sql, [$commentID])->rows;
    }

    /**
     * 추가 댓글 번호
     */
    public function lastedKey(): int
    {
        return intval(DB::selectOne(
            "SELECT AUTO_INCREMENT AS id FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'tb_comment';"
        )->id);
    }

    /**
     * 댓글 삭제
     */
    public function remove(Comment $comment, ?User $user): mixed
    {
        return DB::transaction(function() use ($comment, $user)
        {
            // 댓글 삭제 여부 승인
            $this->updater($comment->id, [
                'is_delete' => 1,
                'deleted_at' => now()
            ]);

            // 댓글 삭제 정보 추가
            (new CommentDeleted)->register([
                'board_id' => $comment->board_id,
                'post_id' => $comment->post_id,
                'comment_id' => $comment->id,
                'user_id' => $user?->id,
                'ip_address' => IP_ADDRESS,
                'user_agent' => USER_AGENT,
                'created_at' => now()
            ]);

            // 게시판 댓글 수 갱신
            (new Board)->updateCommentRows($comment->board_id);

            // 게시판 댓글 수 갱신
            (new Post)->updateCommentRows($comment->post_id);

            // 부모 댓글 수 갱신
            $this->updateCommentRows($comment->id);

            return true;
        });
    }
}