<?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;
use App\Http\Traits\CommonTrait;

class Company extends Model
{
    use CommonTrait;
    use PagingTrait;

    protected $table = 'tb_company';
    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 list(SearchData $params): object
    {
        $where = $this->buildWhereQuery($params);

        $sql = sprintf("
            SELECT
                COUNT(*) AS total
            FROM tb_company CMP
            WHERE TRUE %s;
        ", $where);

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

        $sql = sprintf("
            SELECT
                CMP.company_cd AS companyCd,
                CMP.company_nm AS companyNm,
                CMP.company_nm_en AS companyNmEn,
                CMP.ceo_nm AS ceoNm,
                CMP.company_part_names AS companyPartNames,
                CMP.filmo_names AS filmoNames
            FROM tb_company CMP
            WHERE TRUE %s
            ORDER BY
                CMP.company_nm ASC
            LIMIT ?, ?;
        ", $where);

        $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
        ];
    }

    /**
     * 영화사 상세 조회 (기본정보 + 분류(parts) + 필모그래피)
     */
    public function info(string $companyCd): ?object
    {
        $sql = "
            SELECT
                company_cd AS companyCd,
                company_nm AS companyNm,
                company_nm_en AS companyNmEn,
                ceo_nm AS ceoNm,
                parts
            FROM tb_company
            WHERE company_cd = ?
            LIMIT 1;
        ";

        $row = DB::selectOne($sql, [$companyCd]);

        if (!$row) {
            return null;
        }

        $row->parts = $row->parts ? json_decode($row->parts) : [];

        $filmos = DB::select("
            SELECT
                movie_cd AS movieCd,
                movie_nm AS movieNm,
                company_part_nm AS companyPartNm
            FROM tb_company_filmo
            WHERE company_cd = ?
            ORDER BY id ASC;
        ", [$companyCd]);

        $row->filmos = $filmos;

        return $row;
    }

    /**
     * 영화사 존재 여부 (상세 정보 수집 완료 기준: parts NOT NULL)
     */
    public function isExists(string $companyCd): int
    {
        $sql = "SELECT IF(COUNT(*) > 0, 1, 0) AS isExists FROM tb_company WHERE company_cd = ? AND parts IS NOT NULL;";

        return DB::selectOne($sql, [$companyCd])->isExists;
    }

    private function buildWhereQuery(SearchData $params): string
    {
        $where = "";

        if ($params->keyword) {
            $where .= sprintf(" AND (CMP.company_nm LIKE '%%%s%%' OR CMP.company_nm_en LIKE '%%%s%%') ", $params->keyword, $params->keyword);
        }

        if ($params->ceo) {
            $where .= sprintf(" AND CMP.ceo_nm LIKE '%%%s%%' ", $params->ceo);
        }

        return $where;
    }
}