movie/app/Models/Post.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use CyrildeWit\EloquentViewable\InteractsWithViews;
use CyrildeWit\EloquentViewable\Contracts\Viewable;
use App\Models\DTO\SearchData;

class Post extends Model implements Viewable
{
    use InteractsWithViews;

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

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

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

    protected $guarded = [];

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

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

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

    public function postBlame()
    {
        return $this->hasMany(PostBlame::class);
    }

    public function postBookmark()
    {
        return $this->hasMany(PostBookmark::class);
    }

    public function postDeleted()
    {
        return $this->hasMany(PostDeleted::class);
    }

    public function postFile()
    {
        return $this->hasMany(PostFile::class);
    }

    public function postFileDownLog()
    {
        return $this->hasMany(PostFileDownLog::class);
    }

    public function postHistory()
    {
        return $this->hasMany(PostHistory::class);
    }

    public function postLike()
    {
        return $this->hasMany(PostLike::class);
    }

    public function postLink()
    {
        return $this->hasMany(PostLink::class);
    }

    public function postLinkClickLog()
    {
        return $this->hasMany(PostLinkClickLog::class);
    }

    public function postMeta()
    {
        return $this->hasMany(PostMeta::class);
    }

    public function postTag()
    {
        return $this->hasMany(PostTag::class);
    }

    public function comments()
    {
        return $this->hasMany(Comment::Class);
    }

    /**
     * 게시물 조회
     */
    public function data(SearchData $params)
    {
        $query = $this->query();
        $query->select(
            'tb_board.name AS boardName',
            'tb_board.code',
            'tb_post.*',
            'users.sid',
            'users.name'
        );

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

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

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

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

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

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

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

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

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

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

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

            return true;
        });
    }

    /**
     * 게시글 조회
     */
    public function get(int $postID): Post
    {
        return $this->from('tb_post AS PST')->select('PST.*')
            ->selectRaw(
                '(SELECT id FROM tb_post WHERE id < PST.id AND board_id = PST.board_id AND is_delete = 0 ORDER BY id DESC LIMIT 1) AS beforePostID'
            )->selectRaw(
                '(SELECT id FROM tb_post WHERE id > PST.id AND board_id = PST.board_id AND is_delete = 0 ORDER BY id ASC LIMIT 1) AS nextPostID'
            )->with(['board', 'user'])->where([
                ['PST.id', $postID],
                ['PST.is_delete', 0]
            ])->firstOrNew();
    }

    /**
     * 댓글 갯수 갱신
     */
    public function updateCommentRows(int $postID): int
    {
        $sql = "
            UPDATE tb_post A
            SET A.comment_rows =
            (
                SELECT COUNT(*) AS total FROM tb_comment CMT WHERE CMT.post_id = A.id AND
                CASE WHEN
                (
                    CMT.is_delete = 1
                    AND ((SELECT (COUNT(*) - 1) FROM tb_comment WHERE lft BETWEEN CMT.lft AND CMT.rgt) <= 0)
                    AND ((SELECT (COUNT(*) - 1) FROM tb_comment WHERE CMT.rgt BETWEEN lft AND rgt) <= 0)
                ) THEN FALSE ELSE TRUE END
            )
            WHERE A.id = ?;
        ";
        return DB::update($sql, [$postID]);
    }

    /**
     * 파일 첨부 갯수 갱신
     */
    public function updateFileRows(int $postID): int
    {
        $sql = "
            UPDATE tb_post A
            SET A.file_rows = (SELECT COUNT(*) AS total FROM tb_post_file WHERE post_id = A.id)
            WHERE A.id = ?;
        ";
        return DB::update($sql, [$postID]);
    }

    /**
     * 이미지 파일 갯수 갱신
     */
    public function updateImageRows(int $postID): int
    {
        $sql = "
            UPDATE tb_post A
            SET A.image_rows = ((SELECT COUNT(*) AS total FROM tb_post_file WHERE post_id = A.id AND is_image = 1) + A.image_rows)
            WHERE A.id = ?;
        ";
        return DB::update($sql, [$postID]);
    }

    /**
     * 게시글 태그 갯수 갱신
     */
    public function updateTagRows(int $postID): int
    {
        $sql = "
            UPDATE tb_post A
            SET A.tag_rows = (SELECT COUNT(*) AS total FROM tb_post_tag WHERE post_id = A.id)
            WHERE A.id = ?;
        ";
        return DB::update($sql, [$postID]);
    }

    /**
     * 게시글 링크 갯수 갱신
     */
    public function updateLinkRows(int $postID): int
    {
        $sql = "
            UPDATE tb_post A
            SET A.link_rows = (SELECT COUNT(*) AS total FROM tb_post_link WHERE post_id = A.id)
            WHERE A.id = ?;
        ";
        return DB::update($sql, [$postID]);
    }

    /**
     * 게시글 조회 수 증가
     */
    public function increaseHit(int $postID): int
    {
        return $this->where('id', $postID)->increment('hit');
    }

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

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

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

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

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

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

    /**
     * 게시글 존재 여부
     */
    public function isExists(int $postID)
    {
        $sql = "
            SELECT IF(COUNT(*) > 0, 1, 0) AS isExists
            FROM tb_post PST JOIN tb_board BRD ON BRD.id = PST.board_id
            WHERE PST.id = ? AND PST.is_delete = 0;
        ";
        return DB::selectOne($sql, [$postID])->isExists;
    }

    /**
     * 게시글의 현재 페이지 번호
     */
    public function findPageNumber(int $postID, int $perPage = DEFAULT_LIST_PER_PAGE): int
    {
        $sql = "
             SELECT ROUND(COUNT(*) / :perPage) + 1 AS page
             FROM
				tb_post,
				(SELECT @postID := id, @boardID := board_id
                    FROM tb_post WHERE id = :postID
                ) AS N
             WHERE id > @postID AND board_id = @boardID AND is_delete = 0;
        ";

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

    /**
     * 추가 게시글 번호
     */
    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_post';"
        )->id);
    }

    /**
     * 게시글 조회수 증가
     */
    public function addHit(Post $post): bool
    {
        return views($post)->cooldown(now()->addYear())->record();
    }
}