<?php

namespace App\Models;

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

class CommentHistory extends Model
{
    protected $table = 'tb_comment_history';
    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 data(SearchData $params): object
    {
        $query = $this->query();
        $query->select(
            'tb_board.name AS boardName',
            'tb_comment_history.id',
            'tb_comment_history.board_id',
            'tb_comment_history.before_content',
            'tb_comment_history.after_content',
            'tb_comment_history.ip_address',
            'tb_comment_history.user_agent',
            'tb_comment_history.created_at',
            'user_1.sid AS sid_1',
            'user_1.name AS name_1',
            'user_2.sid AS sid_2',
            'user_2.name AS name_2'
        );

        if($params->keyword) {
            switch ($params->field) {
                case 'tb_comment_history.before_content' :
                case 'tb_comment_history.after_content' :
                case 'tb_comment_history.ip_address' :
                case 'user_1.name' :
                case 'user_1.email' :
                case 'user_2.name' :
                case 'user_2.email' :
                    $query->where($params->field, 'LIKE', "%{$params->keyword}%");
                    break;
                case 'user_1.id' :
                case 'user_1.sid' :
                case 'user_2.id' :
                case 'user_2.sid' :
                    $query->where($params->field, '=', $params->keyword);
                    break;
            }
        }

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

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

        $query->join('tb_board', 'tb_board.id', '=', 'tb_comment_history.board_id');
        $query->join('tb_post', 'tb_post.id', '=', 'tb_comment_history.post_id');
        $query->join('tb_comment', 'tb_comment.id', '=', 'tb_comment_history.comment_id');
        $query->leftJoin('users AS user_1', 'user_1.id', '=', 'tb_comment_history.user_id'); // 수정
        $query->leftJoin('users AS user_2', 'user_2.id', '=', 'tb_comment.user_id'); // 작성
        $query->orderByDesc('tb_comment_history.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 register(Comment $comment, string $memo): int
    {
        return $this->insert([
            'board_id' => $comment->board_id,
            'post_id' => $comment->post_id,
            'comment_id' => $comment->id,
            'user_id' => $comment->user_id,
            'before_content' => $comment->content,
            'after_content' => null,
            'memo' => $memo,
            'ip_address' => IP_ADDRESS,
            'user_agent' => USER_AGENT,
            'created_at' => now()
        ]);
    }

    /**
     * 댓글 수정/삭제 등 기록
     */
    public function updater(Comment $comment, string $memo): bool
    {
        $sql = '
            INSERT INTO tb_comment_history (board_id, post_id, comment_id, user_id, before_content, after_content, memo, ip_address, user_agent, created_at)
            SELECT board_id, post_id, id, :user_id, :content, content, :memo, :ip_address, :user_agent, NOW()
            FROM tb_comment WHERE id = :id;
        ';

        return DB::insert($sql, [
            'id' => $comment->id,
            'user_id' => $comment->user_id,
            'content' => $comment->content,
            'memo' =>  $memo,
            'ip_address' => IP_ADDRESS,
            'user_agent' => USER_AGENT
        ]);
    }
}