<?php

namespace App\Rpc\baoxian;

use App\Model\AppNews;
use App\Model\AppNewsColumn;
use App\Model\AppNewsSecondColumn;
use App\Rpc\BaseService;
use Hyperf\DbConnection\Db;
use Hyperf\RpcServer\Annotation\RpcService;

#[RpcService(name: "baoxian/news", server: "jsonrpc-http", protocol: "jsonrpc-http")]
class NewsService extends BaseService
{
    /**
     * 查看单个新闻详情
     * @url /news/view
     * @param $id
     * @return array
     */
    public function view($id, $app_id): array
    {
        $query = AppNews::query()->where('id', $id)->where('platform',  $app_id)->where('is_delete', 0)->first()?->toArray();

        if (!$query) {
            return $this->getResponse()->setCode(404)->send();
        }

        $query['created_at'] = date('Y-m-d', $query['created_at']);
        $columnTag = AppNewsColumn::find($query['column_tag']);
        $query['column_tag'] = $columnTag->name ?? '';
        $query['column_tag_url'] = $columnTag->url ?? '';

        if ($query['second_column']) {
            $columnTag = AppNewsSecondColumn::find($query['second_column']);
            $query['second_column_tag'] = $columnTag->name ?? '';
            $query['second_column_tag_url'] = $columnTag->url ?? '';
        }

        // 相关文章
        $query['about'] = AppNews::formatQuery(['created_at'])
            ->where('platform',  $app_id)
            ->where('is_delete', 0)
            ->select(['title', 'id', 'cover', 'created_at', 'description'])
            ->limit(10)
            ->orderBy('id', 'desc')
            ->get()
            ->toArray();

        // 随机推荐
        $query['recommend'] = AppNews::formatQuery(['created_at'])
            ->where('platform',  $app_id)
            ->where('is_delete', 0)
            ->select(['title', 'id', 'cover', 'created_at'])
            ->limit(10)
            ->orderBy(Db::raw('RAND()'))
            ->get()
            ->toArray();

        // 上一篇文章
        $query['prevNews'] = AppNews::query()->where('id', '<', $id)->select(['title', 'id'])->where('platform', $app_id)->where('is_delete', 0)->orderBy('id', 'desc')->first();
        // 下一篇文章
        $query['nextNews'] = AppNews::query()->where('id', '>', $id)->select(['title', 'id'])->where('platform', $app_id)->where('is_delete', 0)->first();

        return $this->getResponse()->setData($query)->setCode(0)->send();
    }

    /**
     * 查看保险栏目所有新闻
     * @url /news/index
     * @param int $tag
     * @param int $limit
     * @param int $page
     * @param array $filter
     * @return array
     */
    public function index(int $id, int $limit = 30, int $page = 1, array $filter = []): array
    {
        $filterParams = [];
        if (isset($filter['column_tag'])) {
            $filterParams[] = "column_tag = {$filter['column_tag']}";
        }

        if (isset($filter['second_column'])) {
            $filterParams[] = "second_column = {$filter['second_column']}";
        }

        $filter = implode(' AND ', $filterParams) ? implode(' AND ', $filterParams) . ' AND ' : '';
        // 使用 DB 和 Query Builder
        // 使用 DB 和 Query Builder 执行窗口函数查询
        // 使用原生 SQL 查询
        $sql = "
            SELECT
              id,
              second_column,
              cover,
              title,
              description,
               platform,
               column_tag
            FROM
              ( SELECT second_column, id, cover, title, description, platform,column_tag, ROW_NUMBER() OVER ( PARTITION BY second_column ORDER BY id DESC ) AS rn FROM app_news ) AS ranked 
            WHERE
              rn <= 30 AND 
              {$filter}
              platform = {$id}
  ";

        $articles = [];
        // 执行原生 SQL 查询
        $query = Db::select($sql);

        $totalSql = "
            SELECT
              count(`id`) as `total`
            FROM
              app_news 
            WHERE
              {$filter}
              platform = {$id}
  ";
        $total = Db::select($totalSql);
        $res = [];
        $cache = [];
        foreach ($query as $item) {
            $tag = $item->second_column;
            if (!isset($cache[$tag])) {
                $cache[$tag] = AppNewsSecondColumn::find($tag)?->toArray();
            }

            if ($cache[$tag]) {
                $res[$tag]['title'] = $cache[$tag]['name'];
                $res[$tag]['desc'] = $cache[$tag]['desc'];
                $res[$tag]['url'] = $cache[$tag]['url'];
                $res[$tag]['item'][] = $item;
            }
        }

        $articles['articles'] = $res;// 随机推荐
        $articles['about'] = AppNews::formatQuery(['created_at'])
            ->where('platform',  $id)
            ->where('is_delete', 0)
            ->select(['title', 'id'])
            ->limit(10)
            ->orderBy(Db::raw('RAND()'))
            ->get()
            ->toArray();
        $total = current($total)?->total ?? 0;
        var_dump('pageCOunt',$total / $limit);
        return $this->getResponse()->setExtra('total', ceil($total / $limit))->setData($articles)->setCode(0)->send();
    }
}