<?php

namespace App\Models;

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

class Board extends Model
{
    use PagingTrait;
    use BoardTrait;

    protected $table = 'tb_board';
    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 boardGroup()
    {
        return $this->belongsTo(BoardGroup::class)->withDefault();
    }

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

    public function boardMeta()
    {
        return $this->hasMany(BoardMeta::class);
    }

    public function boardAdmin()
    {
        return $this->hasMany(BoardAdmin::class);
    }

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

    /**
     * 게시판 조회
     */
    public function data(SearchData $params): object
    {
        $query = $this->query();
        $query->select(
            'tb_board.*',
            DB::raw('(SELECT COUNT(*) FROM tb_post WHERE board_id = tb_board.id) AS postRows'),
            DB::raw('(SELECT COUNT(*) FROM tb_comment WHERE board_id = tb_board.id) AS commentRows')
        );
        if ($params->keyword) {
            switch ($params->field) {
                case 'tb_board.code':
                case 'tb_board.name':
                case 'users.name':
                case 'users.email':
                    $query->where($params->field, 'LIKE', "%{$params->keyword}%");
                    break;
                case 'tb_board.board_id':
                case 'users.id':
                case 'users.sid':
                    $query->where($params->field, '=', $params->keyword);
                    break;
            }
        }

        $query->orderBy('tb_board.sort');

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

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

    /**
     * 게시판 그룹에 조회
     */
    public function findByGroup(int $boardGroupID)
    {
        return static::where('board_group_id', $boardGroupID)->get();
    }

    /**
     * 게시판 주소로 게시판 정보 조회
     */
    public function findByCode(string $code): Board
    {
        return static::where('code', $code)->firstOrNew();
    }

    /**
     * 게시판 설정 등록
     */
    public function register(Request $request, ResponseData $response): ResponseData
    {
        DB::beginTransaction();

        try {

            $posts = $request->all();

            $boardID = $this->insertGetId([
                'board_group_id' => $posts['board_group_id'],
                'code' => $posts['code'],
                'name' => $posts['name'],
                'sort' => $posts['sort'],
                'is_search' => $posts['is_search'],
                'is_display' => $posts['is_display'],
                'created_at' => now()
            ]);

            $boardMetaModel = new boardMeta();

            $boardMetaModel->save($boardID, [
                'pc_header_content' => $posts['pc_header_content'],
                'pc_footer_content' => $posts['pc_footer_content'],
                'mobile_header_content' => $posts['mobile_header_content'],
                'mobile_footer_content' => $posts['mobile_footer_content'],
                'use_inform_modal' => $posts['use_inform_modal'],
                'inform_content' => $posts['inform_content']
            ]);

            $tmpDefaultAdminAttributes = [
                // 목록
                'board_layout_type' => [0, '게시판 종류'],
                'list_sort_type' => [1, '기본 정렬 순'],
                'list_per_page' => [15, 'PC - 목록 수'],
                'list_mobile_per_page' => [15, '모바일 - 목록 수'],
                'list_page_count' => [10, 'PC - 페이지 수'],
                'list_page_mobile_count' => [10, '모바일 - 페이지 수'],
                'always_show_write_button' => [1, '글쓰기 버튼 보이기'],
                'show_list_from_view' => [1, '하단 목록 보이기'],
                'new_icon_hour' => [0, 'NEW 아이콘 - 시간'],
                'hot_icon_hit' => [0, 'HOT 아이콘 - 시간'],
                'hot_icon_day' => [0, 'HOT 아이콘 - 일'],
                'subject_length' => [0, 'PC - 제목'],
                'subject_mobile_length' => [0, 'PC - 모바일'],
                'except_notice' => [0, '공지사항 제외'],
                'except_speaker' => [0, '전체공지 제외'],

                // 열람
                'use_bookmark' => [1, '즐겨찾기 기능'],
                'use_post_like' => [1, '추천 기능'],
                'use_post_dislike' => [1, '비추천 기능'],
                'use_print' => [1, '본문 인쇄 기능'],
                'use_sns' => [1, 'SNS 보내기 기능'],
                'use_prev_next_post' => [1, '이전글, 다음글 버튼'],
                'use_blame' => [1, '신고 기능'],
                'blame_blind_count' => [1, '신고 시 숨김'],
                'show_post_ip' => [1, 'IP 보이기'],
                'content_target_blank' => [1, 'Link 새창'],
                'use_auto_url' => [1, '본문 URL Link 생성'],
                'use_copy_post_url' => [1, '주소 복사 버튼'],
                'use_url_qrcode' => [1, '글 주소 QR 코드'],
                'use_attached_url_qrcode' => [1, '첨부된 Link QR 코드'],
                'need_like_for_download' => [1, '다운로드 제한 (추천 필수)'],
                'need_comment_for_download' => [1, '다운로드 제한 (댓글 필수)'],
                'show_user_thumb_in_post' => [1, '회원 프로필 이미지'],
                'show_user_icon_in_post' => [1, '회원 아이콘 이미지'],
                'use_post_user_regdate' => [1, '회원 가입일'],

                // 작성
                'write_header_content' => [null, '작성폼 상단 내용'],
                'mobile_write_header_content' => [null, '모바일 작성폼 상단 내용'],
                'post_default_subject' => [null, '글쓰기 시 기본 제목'],
                'post_default_content' => [null, '글쓰기 시 기본 내용'],
                'use_post_dhtml' => [1, '본문 에디터 사용'],
                'save_external_image' => [0, '외부 이미지 가져오기'],
                'post_subject_min_length' => [0, '최소 글수 제한 (제목)'],
                'post_subject_max_length' => [0, '최대 글수 제한 (제목)'],
                'post_content_min_length' => [0, '최소 글수 제한 (내용)'],
                'post_content_max_length' => [0, '최대 글수 제한 (내용)'],
                'use_category_required' => [0, '분류 필수 선택'],
                'use_post_secret' => [0, '비밀글 사용'],
                'use_post_secret_selected' => [0, '비밀글 기본 선택'],
                'use_post_tag' => [0, '태그 사용'],
                'link_num' => [0, 'URL Link 개수'],
                'use_upload_file' => [0, '첨부파일 사용'],
                'upload_file_num' => [0, '첨부파일 개수 제한'],
                'upload_file_max_size' => [0, '첨부파일 용량 제한'],
                'upload_only_img_file' => [0, '첨부파일 이미지 허용'],
                'upload_file_extension' => [null, '첨부파일 허용 확장자'],
                'use_only_one_post' => [0, '하루에 한 글만 작성'],
                'use_post_captcha' => [0, 'Captcha 사용'],

                // 댓글
                'use_comment' => [1, '댓글 사용 여부'],
                'comment_per_page' => [20, '댓글 목록 수'],
                'comment_page_count' => [10, '댓글 페이지 수'],
                'use_comment_like' => [1, '댓글 추천 기능'],
                'use_comment_dislike' => [1, '댓글 비추천 기능'],
                'show_user_thumb_in_comment' => [1, '회원 프로필 이미지'],
                'show_user_icon_in_comment' => [1, '회원 아이콘 이미지'],
                'update_order_on_comment' => [1, '댓글 작성시 글 수정 시각 갱신'],
                'comment_default_content' => [null, '댓글 기본 내용'],
                'comment_min_length' => [0, '최소 글수 제한'],
                'comment_max_length' => [0, '최대 글수 제한'],
                'use_comment_secret' => [0, '비밀글 사용'],
                'use_comment_secret_selected' => [0, '비밀글 기본 선택'],
                'show_comment_ip' => [1, 'IP 보이기'],
                'use_comment_blame' => [1, '댓글 신고 기능'],
                'comment_blame_blind_count' => [1, '댓글 신고 시 숨김'],
                'protect_delete_comment' => [0, '댓글 보호 기능 (삭제 시)'],
                'protect_update_comment' => [0, '댓글 보호 기능 (수정 시)'],

                // 일반
                'block_delete' => [0, '관리자만 삭제'],
                'protect_post_day' => [0, '게시글 수정/삭제 금지 기간'],
                'protect_comment_day' => [0, '댓글 수정/삭제 금지 기간'],
                'protect_delete_post' => [0, '게시글 보호 기능 (삭제 시)'],
                'protect_update_post' => [0, '게시글 보호 기능 (수정 시)'],
                'use_category' => [0, '분류 기능'],
                'use_personal' => [0, '1:1 게시판'],
                'use_anonymous' => [0, '익명 게시판'],
                'anonymous_except_admin' => [0, '관리자 익명 제외'],
                'anonymous_name' => ['$NUM', '익명 이름'],
                'use_download_log' => [0, '다운로드 기록'],
                'use_post_history' => [1, '게시물 변경 기록'],
                'use_link_click_log' => [1, 'Link 클릭 기록'],
                'use_comment_history' => [1, '댓글 변경 기록'],

                // 알람
                'send_email_post_super_admin' => [0, '이메일 발송(원글 작성) - 최고 관리자'],
                'send_email_post_writer' => [0, '이메일 발송(원글 작성) - 원글 작성자'],
                'send_email_comment_super_admin' => [0, '이메일 발송(댓글 작성) - 최고 관리자'],
                'send_email_comment_post_writer' => [0, '이메일 발송(댓글 작성) - 원글 작성자'],
                'send_email_comment_comment_writer' => [0, '이메일 발송(댓글) - 댓글 작성자'],
                'send_email_blame_super_admin' => [0, '이메일 발송(원글 신고) - 최고 관리자'],
                'send_email_blame_post_writer' => [0, '이메일 발송(원글 신고) - 원글 작성자'],
                'send_email_comment_blame_super_admin' => [0, '이메일 발송(댓글 신고) - 최고 관리자'],
                'send_email_comment_blame_post_writer' => [0, '이메일 발송(댓글 신고) - 원글 작성자'],
                'send_email_comment_blame_comment_writer' => [0, '이메일 발송(댓글 신고) - 댓글 작성자'],
                'send_telegram_post_super_admin' => [0, '텔레그램 발송 (원글 작성 시)'],
                'send_telegram_comment_super_admin' => [0, '텔레그램 발송 (댓글 작성 시)'],
                'send_telegram_blame_super_admin' => [0, '텔레그램 발송 (원글 신고 발생 시)'],
                'send_telegram_comment_blame_super_admin' => [0, '텔레그램 발송 (댓글 신고 발생 시)'],

                // 권한
                'access_post_list' => [null, '목록'],
                'access_post_view' => [null, '글 열람'],
                'access_post_write' => [null, '글 작성'],
                'access_comment_list' => [null, '댓글 목록'],
                'access_comment_write' => [null, '댓글 작성'],
                'access_file_upload' => [null, '파일 업로드'],
                'access_file_download' => [null, '파일 다운로드']
            ];

            /*
             * 기본 정보 저장
             */
            if($tmpDefaultAdminAttributes) {
                foreach ($tmpDefaultAdminAttributes as $attribute => $row) {
                    $boardMetaModel->save($boardID, [
                        $attribute => $row[0]
                    ]);
                }
            }

            /*
             * 그룹, 전체 적용
             */
            $groupData = []; // 그룹적용
            $allData = []; // 전체적용
            $defaultData = [ // 그룹, 전체 적용 값
                'pc_header_content', 'pc_footer_content',
                'mobile_header_content', 'mobile_footer_content',
                'use_inform_modal', 'inform_content'
            ];

            foreach ($defaultData as $field) {
                if (isset($posts['grp'][$field])) {
                    $groupData[$field] = $posts[$field];
                }
                if (isset($posts['all'][$field])) {
                    $allData[$field] = $posts[$field];
                }
            }

            if ($groupData) {
                $brdGroupData = $this->findByGroup($posts['board_group_id']);
                foreach ($brdGroupData as $bKey => $bVal) {
                    if ($bVal->id === $boardID) {
                        continue;
                    }
                    $boardMetaModel->save($bVal->id, $groupData);
                }
            }
            if ($allData) {
                $brdAllData = $this->all();
                foreach ($brdAllData as $bKey => $bVal) {
                    if ($bVal->id === $boardID) {
                        continue;
                    }
                    $boardMetaModel->save($bVal->id, $allData);
                }
            }

            DB::commit();
        } catch (Exception $e) {
            $response = $response::fromException($e);
            DB::rollBack();
        }

        return $response;
    }

    /**
     * 게시판 설정 수정
     */
    public function updater(int $boardID, Request $request, ResponseData $response): ResponseData
    {
        DB::beginTransaction();

        try {

            $posts = $request->all();

            $this->find($boardID)->update([
                'board_group_id' => $posts['board_group_id'],
                'code' => $posts['code'],
                'name' => $posts['name'],
                'sort' => $posts['sort'],
                'is_search' => $posts['is_search'],
                'is_display' => $posts['is_display'],
                'updated_at' => now()
            ]);

            $boardMetaModel = new boardMeta();

            $boardMetaModel->save($boardID, [
                'pc_header_content' => $posts['pc_header_content'],
                'pc_footer_content' => $posts['pc_footer_content'],
                'mobile_header_content' => $posts['mobile_header_content'],
                'mobile_footer_content' => $posts['mobile_footer_content'],
                'use_inform_modal' => $posts['use_inform_modal'],
                'inform_content' => $posts['inform_content']
            ]);

            /*
             * 그룹, 전체 적용
             */
            $groupData = []; // 그룹적용
            $allData = []; // 전체적용
            $defaultData = [ // 그룹, 전체 적용 값
                'pc_header_content', 'pc_footer_content',
                'mobile_header_content', 'mobile_footer_content',
                'use_inform_modal', 'inform_content'
            ];

            foreach ($defaultData as $field) {
                if (isset($posts['grp'][$field])) {
                    $groupData[$field] = $posts[$field];
                }
                if (isset($posts['all'][$field])) {
                    $allData[$field] = $posts[$field];
                }
            }

            if ($groupData) {
                $brdGroupData = $this->findByGroup($posts['board_group_id']);
                foreach ($brdGroupData as $bKey => $bVal) {
                    if ($bVal->id === $boardID) {
                        continue;
                    }
                    $boardMetaModel->save($bVal->id, $groupData);
                }
            }
            if ($allData) {
                $brdAllData = $this->all();
                foreach ($brdAllData as $bKey => $bVal) {
                    if ($bVal->id === $boardID) {
                        continue;
                    }
                    $boardMetaModel->save($bVal->id, $allData);
                }
            }

            DB::commit();
        } catch (Exception $e) {
            $response = $response::fromException($e);
            DB::rollBack();
        }

        return $response;
    }

    /**
     * 게시판 공지글 조회
     * 공지글은 최신순으로 정렬
     */
    public function getNotices(SearchData $params): object
    {
        $sql = "
            SELECT
                COUNT(*) AS total
            FROM tb_post PST
                JOIN tb_board BRD ON BRD.id = PST.board_id
            WHERE
                BRD.code = ? AND BRD.is_display = 1 AND PST.is_notice = 1 AND PST.is_delete = 0 OR
                PST.id IN (SELECT id FROM tb_post WHERE PST.is_speaker = 1 AND PST.is_delete = 0);
        ";

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

        $sql = "
            SELECT
                BRD.code, BRD.name AS boardName,
                BCA.name AS categoryName,
                PST.id, PST.board_id, PST.board_category_id, PST.user_id, PST.thumbnail, PST.subject, PST.content,
                PST.sid, PST.username, PST.email, PST.comment_rows, PST.is_reply, PST.is_personal, PST.is_secret,
                PST.is_notice, PST.is_speaker, PST.is_html, PST.use_comment, PST.receive_email, PST.hit, PST.like, PST.dislike,
                PST.blame, PST.ip_address, PST.user_agent, PST.device_type, PST.file_rows, PST.image_rows,
                PST.link_rows, PST.tag_rows, PST.is_delete, PST.created_at
            FROM tb_post PST
                INNER JOIN tb_board BRD ON BRD.id = PST.board_id
                LEFT JOIN tb_board_category BCA ON BCA.id = PST.board_category_id
                LEFT JOIN users USR ON USR.id = PST.user_id
            WHERE
                BRD.code = ? AND BRD.is_display = 1 AND PST.is_notice = 1 AND PST.is_delete = 0 OR
                PST.id IN (SELECT id FROM tb_post WHERE PST.is_speaker = 1 AND PST.is_delete = 0)
            ORDER BY
                PST.id DESC,
                PST.created_at DESC;
        ";

        $list = $this->setRawAttributes(DB::select($sql, [$params->code]))->getAttributes();

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

    /**
     * 게시글 목록 조회
     */
    public function getPosts(SearchData $params): object
    {
        // 검색 쿼리 생성
        $where = $this->buildWhereQuery($params);

        // 정렬 기준 생성
        $orderBy = $this->sortQuery($params);

        $sql = sprintf("
            SELECT
                COUNT(*) AS total
            FROM tb_post PST
                JOIN tb_board BRD ON BRD.id = PST.board_id
            WHERE BRD.code = ? AND BRD.is_display = 1 AND PST.is_notice = 0 AND PST.is_speaker = 0 AND PST.is_delete = 0
                %s;
        ", $where);

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

        $sql = sprintf("
            SELECT
                BRD.code, BRD.name AS boardName,
                BCA.name AS categoryName,
                PST.id, PST.board_id, PST.board_category_id, PST.user_id, PST.thumbnail, PST.subject, PST.content,
                PST.sid, PST.username, PST.email, PST.comment_rows, PST.is_reply, PST.is_personal, PST.is_secret,
                PST.is_notice, PST.is_speaker, PST.is_html, PST.use_comment, PST.receive_email, PST.hit, PST.like, PST.dislike,
                PST.blame, PST.ip_address, PST.user_agent, PST.device_type, PST.file_rows, PST.image_rows,
                PST.link_rows, PST.tag_rows, PST.is_delete, PST.created_at
            FROM tb_post PST
                INNER JOIN tb_board BRD ON BRD.id = PST.board_id
                LEFT JOIN tb_board_category BCA ON BCA.id = PST.board_category_id
                LEFT JOIN users USR ON USR.id = PST.user_id
            WHERE BRD.code = ? AND BRD.is_display = 1 AND PST.is_notice = 0 AND PST.is_speaker = 0 AND PST.is_delete = 0
                %s
            ORDER BY
                %s
            LIMIT ?, ?;
        ", $where, $orderBy);

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

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

    /**
     * 게시글 목록 조회
     */
    public function getUserPosts(int $userID, SearchData $params): object
    {
        $sql = "
            SELECT
                COUNT(*) AS total
            FROM tb_post PST
                JOIN tb_board BRD ON BRD.id = PST.board_id
            WHERE
                BRD.is_display = 1 AND PST.is_notice = 0 AND PST.is_speaker = 0 AND PST.is_delete = 0 AND PST.user_id = ? AND PST.is_personal = 0;
        ";

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

        $sql = "
            SELECT
                BRD.code, BRD.name AS boardName,
                BCA.name AS categoryName,
                PST.id, PST.board_id, PST.board_category_id, PST.user_id, PST.thumbnail, PST.subject, PST.content,
                PST.sid, PST.username, PST.email, PST.comment_rows, PST.is_reply, PST.is_personal, PST.is_secret,
                PST.is_notice, PST.is_speaker, PST.is_html, PST.use_comment, PST.receive_email, PST.hit, PST.like, PST.dislike,
                PST.blame, PST.ip_address, PST.user_agent, PST.device_type, PST.file_rows, PST.image_rows,
                PST.link_rows, PST.tag_rows, PST.is_delete, PST.created_at
            FROM tb_post PST
                INNER JOIN tb_board BRD ON BRD.id = PST.board_id
                LEFT JOIN tb_board_category BCA ON BCA.id = PST.board_category_id
                LEFT JOIN users USR ON USR.id = PST.user_id
            WHERE
                BRD.is_display = 1 AND PST.is_notice = 0 AND PST.is_speaker = 0 AND PST.is_delete = 0 AND PST.user_id = ? AND PST.is_personal = 0
            ORDER BY
                PST.created_at DESC, PST.id DESC
            LIMIT ?, ?;
        ";

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

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

    /**
     * 게시판 최근 게시글 조회
     */
    public function getLatest(?string $code, int $page, int $perPage): object
    {
        $sql = "
            SELECT
                COUNT(*) AS total
            FROM
                (SELECT @code := ?) V,
                tb_post PST
                JOIN tb_board BRD ON BRD.id = PST.board_id
            WHERE
                CASE WHEN @code IS NOT NULL THEN BRD.code = @code ELSE (PST.is_notice = 0 AND PST.is_speaker = 0) END
                AND BRD.is_display = 1 AND PST.is_delete = 0;
        ";

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

        $sql = "
            SELECT
                BRD.code, BRD.name AS boardName,
                BCA.name AS categoryName,
                PST.id, PST.board_id, PST.board_category_id, PST.user_id, PST.thumbnail, PST.subject, PST.content,
                PST.sid, PST.username, PST.email, PST.comment_rows, PST.is_reply, PST.is_personal, PST.is_secret,
                PST.is_notice, PST.is_speaker, PST.is_html, PST.use_comment, PST.receive_email, PST.hit, PST.like, PST.dislike,
                PST.blame, PST.ip_address, PST.user_agent, PST.device_type, PST.file_rows, PST.image_rows,
                PST.link_rows, PST.tag_rows, PST.is_delete, PST.created_at
            FROM
                (SELECT @code := ?) V,
                tb_post PST
                INNER JOIN tb_board BRD ON BRD.id = PST.board_id
                LEFT JOIN tb_board_category BCA ON BCA.id = PST.board_category_id
                LEFT JOIN users USR ON USR.id = PST.user_id
            WHERE
                CASE WHEN @code IS NOT NULL THEN BRD.code = @code ELSE (PST.is_notice = 0 AND PST.is_speaker = 0) END
                AND BRD.is_display = 1 AND PST.is_delete = 0
            ORDER BY
                PST.created_at DESC, PST.id DESC
            LIMIT ?, ?;
        ";

        $list = $this->getPaginator(
            DB::select($sql, [$code, $this->getPageOffset($page), $perPage]), $total, $perPage, $page,
            ($code ? route('board.list', $code) : null)
        );

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

    /**
     * 게시판 최근 게시글 조회
     */
    public function getSearch(SearchData $params): object
    {
        // 검색 쿼리 생성
        $where = $this->buildWhereQuery($params);

        // 정렬 기준 생성
        $orderBy = $this->sortQuery($params);

        $sql = sprintf("
            SELECT
                COUNT(*) AS total
            FROM
                tb_post PST
                JOIN tb_board BRD ON BRD.id = PST.board_id
            WHERE TRUE AND BRD.is_display = 1 AND PST.is_notice = 0 AND PST.is_speaker = 0 AND PST.is_delete = 0
                %s;
        ", $where);

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

        $sql = sprintf("
            SELECT
                BRD.code, BRD.name AS boardName,
                BCA.name AS categoryName,
                PST.id, PST.board_id, PST.board_category_id, PST.user_id, PST.thumbnail, PST.subject, PST.content,
                PST.sid, PST.username, PST.email, PST.comment_rows, PST.is_reply, PST.is_personal, PST.is_secret,
                PST.is_notice, PST.is_speaker, PST.is_html, PST.use_comment, PST.receive_email, PST.hit, PST.like, PST.dislike,
                PST.blame, PST.ip_address, PST.user_agent, PST.device_type, PST.file_rows, PST.image_rows,
                PST.link_rows, PST.tag_rows, PST.is_delete, PST.created_at
            FROM
                tb_post PST
                INNER JOIN tb_board BRD ON BRD.id = PST.board_id
                LEFT JOIN tb_board_category BCA ON BCA.id = PST.board_category_id
                LEFT JOIN users USR ON USR.id = PST.user_id
            WHERE TRUE AND BRD.is_display = 1 AND PST.is_notice = 0 AND PST.is_speaker = 0 AND PST.is_delete = 0
                %s
            ORDER BY
                %s
            LIMIT ?, ?;
        ", $where, $orderBy);

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

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

    /**
     * 게시판 검색 쿼리 생성
     */
    private function buildWhereQuery(?SearchData $params): string
    {
        $sql = "";

        if(!$params) {
            return $sql;
        }

        // 분류 지정
        if($params->category) {
            $sql .= sprintf(" AND PST.board_category_id = %d", $params->category);
        }

        // 검색
        if($params->keyword) {
            $sql .= " AND BRD.is_search = 1 ";

            switch ($params->field) {
                case 1 : // 제목 + 내용
                    $sql .= sprintf("AND (PST.subject LIKE '%%%s%%' OR PST.content LIKE '%%%s%%')", $params->keyword, $params->keyword);
                    break;
                case 2 : // 제목
                    $sql .= sprintf("AND PST.subject LIKE '%%%s%%'", $params->keyword);
                    break;
                case 3 : // 글 작성자
                    $sql .= sprintf("AND (PST.username LIKE '%%%s%%' OR PST.nickname LIKE '%%%s%%')", $params->keyword);
                    break;
                case 4 : // 댓글 내용
                    $sql .= sprintf("AND PST.id IN (SELECT post_id FROM tb_comment WHERE content LIKE '%%%s%%')", $params->keyword);
                    break;
                case 5 : // 댓글 작성자
                    $sql .= sprintf("AND PST.id IN (SELECT post_id FROM tb_comment WHERE (username LIKE '%%%s%%' OR nickname LIKE '%%%s%%'))", $params->keyword, $params->keyword);
                    break;
            }
        }

        if($params->code) {
            $boardMeta = $params->boardMeta;

            // 1:1 문의 회원 번호 조회
            if ($boardMeta->item('use_personal', 0)) {
                $sql .= sprintf(" AND PST.user_id = %d", UID);
            }
        }

        return $sql;
    }

    /**
     * 게시판 정렬 방법
     */
    private function sortQuery(?SearchData $params): string
    {
        $sql = 'PST.created_at DESC, PST.id DESC';

        if(!$params) {
            return $sql;
        }

        switch($params->sort) {
            default:case 1: // 날짜순
                return $sql;
            case 2: // 조회수
                return 'PST.hit DESC, PST.id DESC';
            case 3: // 댓글순
                return 'PST.comment_rows DESC, PST.id DESC';
            case 4: // 추천순
                return 'PST.like DESC, PST.id DESC';
        }
    }

    /**
     * 게시글 수 갱신
     */
    public function updatePostRows(int $boardID): int
    {
        $sql = '
            UPDATE tb_board A
            SET A.post_rows = (
                (SELECT COUNT(*) AS total FROM tb_post WHERE board_id = A.id AND is_delete = 0)
            )
            WHERE A.id = ?;
        ';
        return DB::update($sql, [$boardID]);
    }

    /**
     * 댓글 개수 갱신
     */
    public function updateCommentRows(int $boardID): int
    {
        $sql = '
            UPDATE tb_board A
            SET A.comment_rows = (
                SELECT COUNT(*) AS total FROM tb_comment CMT WHERE CMT.board_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, [$boardID]);
    }

    /**
     * 삭제
     */
    public function remove(int $boardID): mixed
    {
        return DB::transaction(function() use($boardID) {
            $board = $this->findOrNew($boardID);

            if($board->exists) {
                self::deleting(function($board)
                {
                    // 게시판 분류 삭제
                    $board->boardCategory()->each(function($category) {
                        $category->delete();
                    });

                    // 메타 삭제
                    $board->boardMeta()->each(function($meta) {
                        $meta->delete();
                    });

                    // 관리자 삭제
                    $board->boardAdmin()->each(function($admin) {
                        $admin->delete();
                    });

                    // 게시글 삭제
                    $board->post()->each(function($post) {
                        $post->delete();
                    });
                });

                $board->delete();
            }

            return true;
        });
    }
}