movie/app/Models/Movie/MovieReview.php

<?php

namespace App\Models\Movie;

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

class MovieReview extends Model
{
    use PagingTrait;

    protected $table = 'tb_movie_review';
    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 = null;

    protected $guarded = [];

    /**
     * 평균 평점 조회
     */
    public function getAvgRate(string $movieCd): int|float|null
    {
        $sql = "SELECT ROUND(AVG(`rate`), 1) AS rate FROM tb_movie_review WHERE movie_cd = ?;";

        return DB::selectOne($sql, [$movieCd])->rate;
    }

    /**
     * 최근 영화 평점 및 후기 목록
     */
    public function latest(string $movieCd, int $userID): object
    {
        $params = new SearchData();
        $params->movieCd = $movieCd;
        $where = $this->buildWhereQuery($params);

        // 개수 조회
        $sql = sprintf("
            SELECT
                COUNT(*) AS total
            FROM tb_movie_review MRV
                JOIN tb_movie MVI ON MVI.movie_cd = MRV.movie_cd
                JOIN users USR ON USR.id = MRV.user_id
            WHERE TRUE %s;
        ", $where);

        $total = DB::selectOne($sql)->total;

        $sql = sprintf("
            SELECT
                MRV.id, MRV.movie_cd, MRV.user_id, MRV.content, MRV.rate, MRV.like, MRV.dislike, MRV.created_at
                , USR.id, USR.sid, USR.name, USR.thumb
            FROM tb_movie_review MRV
                JOIN tb_movie MVI ON MVI.movie_cd = MRV.movie_cd
                JOIN users USR ON USR.id = MRV.user_id
            WHERE TRUE %s
            ORDER BY
                FIELD(user_id, ?) DESC, MRV.created_at DESC, MRV.id DESC
            LIMIT 4;
        ", $where);

        $list = DB::select($sql, [$userID]);

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

    /**
     * 영화 평점 및 후기 목록
     */
    public function list(SearchData $params): object
    {
        $where = $this->buildWhereQuery($params);

        $sql = sprintf("
            SELECT
                COUNT(*) AS total
            FROM tb_movie_review MRV
                JOIN tb_movie MVI ON MVI.movie_cd = MRV.movie_cd
                LEFT JOIN tb_movie_detail MVD ON MVD.movie_cd = MVI.movie_cd
                JOIN users USR ON USR.id = MRV.user_id
            WHERE TRUE %s;
        ", $where);

        $total = DB::selectOne($sql)->total;

        $orderBy = $this->sortQuery($params->sort);

        $sql = sprintf("
            SELECT
                MRV.id, MRV.movie_cd, MRV.user_id, MRV.content, MRV.rate, MRV.like, MRV.dislike, MRV.created_at
                , USR.sid, USR.name, USR.thumb
                , MVI.movie_nm, MVI.movie_nm_en, MVD.thumb_img
            FROM tb_movie_review MRV
                JOIN tb_movie MVI ON MVI.movie_cd = MRV.movie_cd
                LEFT JOIN tb_movie_detail MVD ON MVI.movie_cd = MVD.movie_cd
                JOIN users USR ON USR.id = MRV.user_id
            WHERE TRUE %s
            ORDER BY %s
            LIMIT ?, ?;
        ", $where, $orderBy);

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

        $rows = $list->count();

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

    /**
     * where 조건 생성
     */
    private function buildWhereQuery(SearchData $params): string
    {
        $where = "";

        if($params->movieCd) {
            $where .= sprintf(" AND MRV.movie_cd = '%s'", $params->movieCd);
        }

        return $where;
    }

    /**
     * 평점·후기 정렬 방법
     */
    private function sortQuery(int $sort): string
    {
        switch($sort) {
            default:case 1: // 공감순
            return 'MRV.like DESC, MRV.id DESC';
            case 2: // 최신순
                return 'MRV.created_at DESC, MRV.id DESC';
            case 3: // 평점 높은 순
                return 'MRV.rate DESC, MRV.id DESC';
            case 4: // 평점 낮은 순
                return 'MRV.rate DESC, MRV.id DESC';
        }
    }

    /**
     * 영화 평점 이미 등록했는지
     */
    public function isAlready(string $movieCd, int $userID): bool
    {
        return $this->where([
            ['movie_cd', $movieCd],
            ['user_id', $userID]
        ])->exists();
    }

    /**
     * 내가 등록한 후기 인가?
     */
    public function isExists(int $reviewID, int $userID): bool
    {
        return $this->where([
            ['id', $reviewID],
            ['user_id', $userID]
        ])->exists();
    }

    /**
     * 평점·후기 공감/비공감
     */
    public function setLike(string $movieCd, int $reviewID, int $userID, int $type): mixed
    {
        return DB::transaction(function() use($movieCd, $reviewID, $userID, $type) {
            $typeStr = MAP_LIKE_TYPE[$type];
            $this->where([
                ['movie_cd', $movieCd],
                ['id', $reviewID],
                ['user_id', $userID]
            ])->increment($typeStr);

            (new MovieLike)->insert([
                'movie_cd' => $movieCd,
                'movie_review_id' => $reviewID,
                'user_id' => $userID,
                'type' => $type,
                'ip_address' => IP_ADDRESS,
                'user_agent' => USER_AGENT,
                'created_at' => now()
            ]);

            return $this->where([
                ['movie_cd', $movieCd],
                ['id', $reviewID]
            ])->value($typeStr);
        });
    }

    /**
     * 평점·후기 신고하기
     */
    public function setBlame(string $movieCd, int $reviewID, int $userID, int $type, string $reason): mixed
    {
        return DB::transaction(function() use($movieCd, $reviewID, $userID, $type, $reason) {
            $this->where([
                ['movie_cd', $movieCd],
                ['id', $reviewID],
                ['user_id', $userID]
            ])->increment('blame');

            (new MovieBlame)->insert([
                'movie_cd' => $movieCd,
                'movie_review_id' => $reviewID,
                'user_id' => $userID,
                'type' => $type,
                'reason' => $reason,
                'ip_address' => IP_ADDRESS,
                'user_agent' => USER_AGENT,
                'status' => 0,
                'memo' => null,
                'updated_at' => null,
                'created_at' => now()
            ]);
        });
    }
}