movie/app/Models/PostLink.php

<?php

namespace App\Models;

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

class PostLink extends Model
{
    protected $table = 'tb_post_link';
    protected $primaryKey = 'id';

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

    const CREATED_AT = 'created_at';
    const UPDATED_AT = null;

    protected $guarded = [];

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

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

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

    /**
     * 게시글 URL 링크 조회
     */
    public function data(SearchData $params): object
    {
        $query = $this->query();
        $query->select(
            'tb_board.name AS boardName',
            'tb_board.code',
            'tb_post_link.*',
            'tb_post.subject',
            'users.sid',
            'users.name'
        );

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

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

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

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

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

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

    /**
     * 게시글 URL 정보 조회
     */
    public function info(int $linkID): PostLink
    {
        return $this->findOrNew($linkID);
    }

    /**
     * 게시글 URL 입력
     */
    public function register(Post $post, ?array $links): void
    {
        if ($links) {
            foreach ($links as $url) {
                $url = trim($url);
                if ($url) {
                    $this->insert([
                        'board_id' => $post->board_id,
                        'post_id' => $post->id,
                        'url' => $url,
                        'hit' => 0,
                        'ip_address' => IP_ADDRESS,
                        'user_agent' => USER_AGENT,
                        'created_at' => now()
                    ]);
                }
            }

            $post->updateLinkRows($post->id);
        }
    }

    /**
     * 게시글 URL 수정
     */
    public function updater(Post $post, ?array $links): void
    {
        if ($links) {
            foreach ($links as $linkID => $url) {
                $url = trim($url);
                if ($url) {
                    DB::statement("
                        REPLACE INTO tb_post_link
                        SET id = ?, board_id = ?, post_id = ?, url = ?, hit = ?, ip_address = ?, user_agent = ?, created_at = NOW();
                    ", [
                        $linkID,
                        $post->board_id,
                        $post->id,
                        $url,
                        0,
                        IP_ADDRESS,
                        USER_AGENT
                    ]);
                } else {
                    $this->where([
                        ['id', $linkID],
                        ['board_id', $post->board_id],
                        ['post_id', $post->id]
                    ])->delete();
                }
            }

            $post->updateLinkRows($post->id);
        }
    }

    /**
     * 게시글 URL 삭제
     */
    public function remove(int $postLinkID): bool
    {
        return DB::transaction(function() use ($postLinkID) {

            $postLink = $this->info($postLinkID);

            // URL 존재 확인
            if(!$postLink->exists) {
                return false;
            }

            // Link 기록 삭제
            $postLink->postLinkClickLog->delete();

            // 게시글 Link 수 갱신
            $postLink->post->updateLinkRows($postLink->post_id);

            // Link 삭제
            $postLink->delete();

            return true;
        });
    }

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