<?php
namespace app\mobile\service;

use think\facade\Db;
use think\facade\Cache;
use think\exception\HttpResponseException;

class ChatService
{
    // 旧身份为 signed INT；新版 auth 身份映射到 unsigned INT 高半区，保留已有板块数据。
    const AUTH_ID_OFFSET = 2147483648;
    private $app;
    public function __construct($app) { self::useChatCharset($app); $this->app = $app; }
    /** 仅当前公告板请求／独立进程覆盖字符集，保留默认连接、数据库与表前缀。 */
    public static function useChatCharset($app) {
        $database=$app->config->get('database'); $name=$database['default'];
        $sqlLog=(bool)$app->config->get('chat.sql_log',false);
        if (($database['connections'][$name]['charset'] ?? '') === 'utf8mb4' && (bool)($database['connections'][$name]['trigger_sql']??true)===$sqlLog) return;
        $database['connections'][$name]['charset']='utf8mb4';
        // 仅公告板连接关闭成功SQL明细；异常仍走现有错误日志，其他业务不受影响。
        $database['connections'][$name]['trigger_sql']=$sqlLog;
        $app->config->set($database,'database');
        Db::setConfig($app->config);
        // 中间件可能已经建立连接；只在进入公告板时替换当前请求的默认连接。
        Db::connect($name,true);
    }
    public static function now() { return (int)floor(microtime(true) * 1000); }
    private function fail($message, $status = 400) { throw new HttpResponseException(json(['code'=>1,'message'=>$message,'data'=>null], $status)); }
    public function redis() { return Cache::store('redis')->handler(); }
    public function key($name) {
        $db = $this->app->config->get('database.connections.'.$this->app->config->get('database.default').'.database');
        $prefix = $this->app->config->get('database.connections.'.$this->app->config->get('database.default').'.prefix');
        return 'oms-chat:'.hash('sha256', $db.':'.$prefix).':'.$name;
    }
    public function rate($name, $limit, $seconds) {
        $count = $this->redis()->eval("local n=redis.call('INCR',KEYS[1]); if n==1 then redis.call('EXPIRE',KEYS[1],ARGV[1]) end; return n", [$this->key('rate:'.$name), $seconds], 1);
        if ($count > $limit) $this->fail('操作过于频繁，请稍后再试', 429);
    }
    private function identityQuery($id) {
        return $id >= self::AUTH_ID_OFFSET ? Db::name('auth')->where('id', $id-self::AUTH_ID_OFFSET) : Db::name('admin_user_mobile')->where('id', $id);
    }
    public function user($id) {
        $native = $id >= self::AUTH_ID_OFFSET;
        $account = $this->identityQuery($id)->find();
        if (!$account) $this->fail('登录已失效，请重新认证', 401);
        if (isset($account['status']) && (int)$account['status'] !== 1) $this->fail('账号已停用，请重新认证', 401);
        if ($native && empty($account['openid'])) $this->fail('微信绑定已重置，请重新认证', 401);
        $isMember = (int)$account['user_type'] === 1;
        $person = $isMember ? Db::name('member_list')->where('eid', $account['username'])->where('status', 1)->find() : Db::name('admin_user')->where('username', $account['username'])->where('status', 1)->find();
        if (!$person) $this->fail('员工账号已离职或停用', 401);
        if ($native && (int)$person['id'] !== (int)($isMember ? $account['user_id'] : $account['admin_id'])) $this->fail('员工身份关联已变更，请重新认证', 401);
        return ['id'=>(int)$id,'name'=>(string)($isMember ? $person['ename'] : $person['truename']),'employeeNo'=>$account['username'],'isAdmin'=>in_array($account['username'], $this->app->config->get('chat.admins', []), true)];
    }
    public function storeSession($token, $data, $ttl) { $this->redis()->setex($this->key('session:'.hash('sha256', $token)), $ttl, json_encode($data)); }
    private function colleagueText($column) { return 'CONVERT('.$column.' USING utf8mb4) COLLATE utf8mb4_unicode_ci'; }
    private function mentionAccounts() {
        return Db::name('auth')->where('status',1)->whereNotNull('openid')->where('openid','<>','');
    }
    private function hallMentionMembers($data) {
        $query=$this->mentionAccounts();
        if(!empty($data['search'])){
            $search='%'.addcslashes($this->text($data,'search',30),'%_\\').'%';
            $query->where(function($q)use($search){$q->whereLike('name',$search)->whereOr('username','like',$search);});
        }
        $cursor=empty($data['cursor'])?0:$this->integer($data,'cursor'); $limit=min(100,max(1,(int)($data['limit']??20)));
        $rows=$query->where('id','>',max(0,$cursor-self::AUTH_ID_OFFSET))->field('id,username,name')->order('id')->limit($limit+1)->select()->toArray();
        $more=count($rows)>$limit;if($more)array_pop($rows);
        return ['members'=>array_map(function($row){return ['id'=>self::AUTH_ID_OFFSET+(int)$row['id'],'name'=>trim((string)$row['name'])!==''?$row['name']:$row['username'],'muted'=>false];},$rows),'nextCursor'=>$more?self::AUTH_ID_OFFSET+(int)end($rows)['id']:null];
    }
    public function authenticate($token) {
        if (!preg_match('/^[a-f0-9]{64}$/', $token)) $this->fail('请重新认证', 401);
        $data = json_decode((string)$this->redis()->get($this->key('session:'.hash('sha256', $token))), true);
        if (!$data || $data['expiresAt'] <= self::now()) $this->fail('聊天认证已过期，请重新进入', 401);
        $account = $this->identityQuery($data['id'])->find();
        if (!$account || empty($account['openid']) || !hash_equals((string)$data['openid'], (string)$account['openid'])) $this->fail('微信绑定已变更，请重新认证', 401);
        if ($data['id'] >= self::AUTH_ID_OFFSET) {
            $source = isset($data['sourceKey']) ? Cache::store('redis')->get($data['sourceKey']) : null;
            if (!$source || (int)$source['id'] !== (int)$account['id'] || (int)$source['version'] !== (int)$account['version'] || $source['expires_at'] <= time()) $this->fail('员工登录已失效，请重新进入公告板', 401);
        }
        return $this->user($data['id']);
    }
    private function text($data, $key, $max, $required = true) {
        $value = $data[$key] ?? '';
        if (!is_string($value)) $this->fail('参数格式不正确');
        $value = trim($value);
        if (($required && $value === '') || mb_strlen($value) > $max) $this->fail($key === 'title' ? '请填写 1～30 字的标题' : '内容为空或超过长度限制');
        return $value;
    }
    private function integer($data, $key, $min = 1) {
        $value = $data[$key] ?? null;
        if (filter_var($value, FILTER_VALIDATE_INT) === false || (int)$value < $min) $this->fail('参数格式不正确');
        return (int)$value;
    }
    private function boolValue($data, $key) {
        if (!isset($data[$key]) || !is_bool($data[$key])) $this->fail('参数格式不正确');
        return $data[$key];
    }
    private function member($roomId, $userId) { return Db::name('chat_member')->where(['room_id'=>$roomId,'user_id'=>$userId])->find(); }
    private function joined($room, $user) {
        $member = $this->member($room['id'], $user['id']);
        if (!$member || $member['status'] !== 'joined') $this->fail('你不是该板块成员', 403);
        return $member;
    }
    private function open($room) { if ($room['status'] !== 'open' || ($room['expires_at'] !== null && $room['expires_at'] <= self::now())) $this->fail('板块已关闭，不能继续操作', 409); }
    private function owner($room, $user) { if ((int)$room['owner_id'] !== $user['id']) $this->fail('仅发起人可进行此操作', 403); }
    private function admin($user) { if (!$user['isAdmin']) $this->fail('无管理员权限', 403); }
    private function log($roomId, $userId, $action, $detail = '') { Db::name('chat_log')->insert(['room_id'=>$roomId,'user_id'=>$userId,'action'=>$action,'detail'=>$detail,'created_at'=>self::now()]); }
    public function notify($roomId) { $this->redis()->zAdd($this->key('events'), self::now(), (string)$roomId); }
    private function message($room, $user, $data) {
        $revision = (int)$room['version'] + 1;
        Db::name('chat_room')->where('id', $room['id'])->update(['version'=>$revision]);
        return Db::name('chat_message')->insertGetId(array_merge(['room_id'=>$room['id'],'sender_id'=>$user['id'],'sender_name'=>$user['name'],'client_id'=>bin2hex(random_bytes(16)),'type'=>'system','content'=>'','image_id'=>null,'reply_id'=>null,'mentions'=>'[]','recalled'=>0,'revision'=>$revision,'created_at'=>self::now()], $data));
    }
    private function systemMessage($room, $content) { return $this->message($room, ['id'=>0,'name'=>'系统'], ['content'=>$content]); }
    private function closeLocked($room, $actor = 0) {
        if ($room['status'] !== 'open') return;
        Db::name('chat_room')->where('id', $room['id'])->update(['status'=>'closed','closed_at'=>self::now()]);
        Db::name('chat_invite')->where('room_id', $room['id'])->delete();
        $this->systemMessage($room, $actor ? '板块已提前关闭，交流记录只读保留 30 天。' : '板块已到期关闭，交流记录只读保留 30 天。');
        $this->log($room['id'], $actor, 'close');
    }
    public function expire($id) {
        $changed = Db::transaction(function () use ($id) {
            $room = Db::name('chat_room')->where('id', $id)->lock(true)->find();
            if (!$room || $room['status'] !== 'open' || $room['expires_at'] === null || $room['expires_at'] > self::now()) return false;
            $this->closeLocked($room); return true;
        });
        if ($changed) $this->notify($id);
        return $changed;
    }
    private function roomRow($id, $lock = false) {
        $query = Db::name('chat_room')->where('id', $id);
        if ($lock) $query->lock(true);
        $room = $query->find();
        if (!$room) $this->fail('板块不存在或历史记录已清理', 404);
        if ($room['status'] === 'open' && $room['expires_at'] !== null && $room['expires_at'] <= self::now()) {
            // 写操作会拒绝到期板块；读操作主动落库关闭，定时任务负责无人访问的板块。
            if (!$lock) { $this->expire($id); return Db::name('chat_room')->find($id); }
        }
        return $room;
    }
    private function presentRoom($room, $user) {
        $member = $this->member($room['id'], $user['id']);
        $name = '系统';
        if ((int)$room['owner_id']) {
            try { $name = $this->user((int)$room['owner_id'])['name']; }
            catch (HttpResponseException $error) { $name = '已停用同事'; }
        }
        $pending=(int)$room['owner_id']===$user['id'] ? Db::name('chat_member')->where(['room_id'=>$room['id'],'status'=>'pending'])->count() : 0;
        return ['id'=>(int)$room['id'],'title'=>(int)$room['id']===1?'公共交流':$room['title'],'category'=>$room['category'],'visibility'=>$room['visibility'],'description'=>$room['description'],'ownerId'=>(int)$room['owner_id'],'ownerName'=>$name,'status'=>$room['status'],'expiresAt'=>$room['expires_at'] === null ? null : (int)$room['expires_at'],'memberCount'=>(int)$room['member_count'],'memberStatus'=>$member['status'] ?? 'none','muted'=>(bool)($member['muted'] ?? false),'pendingCount'=>(int)$pending];
    }
    private function inviteRoom($token) {
        if (!is_string($token) || !preg_match('/^[a-f0-9]{64}$/', $token)) $this->fail('邀请无效', 403);
        $invite = Db::name('chat_invite')->find(hash('sha256', $token));
        if (!$invite || $invite['expires_at'] <= self::now()) $this->fail('邀请已失效，请联系发起人重新邀请', 403);
        return (int)$invite['room_id'];
    }
    private function accessPreview($room, $user, $invite) {
        if ($room['visibility'] === 'public' && $room['status'] === 'open') return;
        $member = $this->member($room['id'], $user['id']);
        if ($member && in_array($member['status'], ['joined','pending'], true)) return;
        if ($room['status'] === 'open' && $invite && $this->inviteRoom($invite) === (int)$room['id'] && (!$member || $member['status'] !== 'removed')) return;
        $this->fail('无权查看该板块', 403);
    }
    private function addMember($room, $user, $status) {
        $member = $this->member($room['id'], $user['id']);
        if ($member && $member['status'] === 'removed') $this->fail('你已被移出该板块', 403);
        if ($member && $member['status'] === 'joined') return;
        if ($status === 'joined' && (int)$room['id'] !== 1 && (int)$room['member_count'] >= 100) $this->fail('板块人数已满', 409);
        $row = ['status'=>$status,'muted'=>0,'attention'=>0,'joined_at'=>self::now()];
        if ($member) Db::name('chat_member')->where(['room_id'=>$room['id'],'user_id'=>$user['id']])->update($row);
        else Db::name('chat_member')->insert(array_merge($row, ['room_id'=>$room['id'],'user_id'=>$user['id']]));
        if ($status === 'joined') Db::name('chat_room')->where('id', $room['id'])->inc('member_count')->update();
    }
    private function imageUrl($imageId, $userId) {
        $ticket = bin2hex(random_bytes(32));
        $openid=(string)$this->identityQuery($userId)->value('openid');
        $this->redis()->setex($this->key('image:'.hash('sha256', $ticket)), 3600, json_encode(['id'=>$imageId,'userId'=>$userId,'openid'=>$openid]));
        return $this->app->config->get('chat.image_url').'?ticket='.$ticket;
    }
    private function presentMessage($row, $user, $replies = null) {
        $reply = $row['reply_id'] ? ($replies !== null ? ($replies[$row['reply_id']] ?? null) : Db::name('chat_message')->where(['id'=>$row['reply_id'],'room_id'=>$row['room_id']])->find()) : null;
        return ['id'=>(int)$row['id'],'sequence'=>(int)$row['id'],'clientId'=>$row['client_id'],'senderId'=>(int)$row['sender_id'],'senderName'=>$row['sender_name'],'type'=>$row['type'],'content'=>$row['recalled'] ? '' : $row['content'],'recalled'=>(bool)$row['recalled'],'createdAt'=>(int)$row['created_at'],'imageUrl'=>!$row['recalled'] && $row['image_id'] ? $this->imageUrl($row['image_id'], $user['id']) : null,'reply'=>$reply ? ['senderName'=>$reply['sender_name'],'summary'=>$reply['recalled'] ? '' : ($reply['type'] === 'image' ? '[图片]' : mb_substr($reply['content'], 0, 80)),'recalled'=>(bool)$reply['recalled']] : null];
    }

    public function perform($action, $user, $data) {
        $this->rate('request:'.$user['id'], 240, 60);
        if ($action === 'attention') {
            $attention=Db::name('chat_member')->where('user_id',$user['id'])->where('attention',1)->whereIn('status',['joined','pending'])->count()>0;
            $attention=$attention||Db::name('chat_room')->where(['owner_id'=>$user['id'],'status'=>'open'])->whereBetween('expires_at',[self::now(),self::now()+3600000])->count()>0;
            return ['attention'=>$attention];
        }
        if ($action === 'hall') {
            $room = Db::transaction(function () use ($user) { $room = $this->roomRow(1, true); $this->addMember($room, $user, 'joined'); return $this->roomRow(1, true); });
            $attention = Db::name('chat_member')->where('user_id', $user['id'])->where('attention', 1)->whereIn('status', ['joined','pending'])->count() > 0;
            $attention = $attention || Db::name('chat_room')->where('owner_id', $user['id'])->where('status', 'open')->whereBetween('expires_at', [self::now(), self::now()+3600000])->count() > 0;
            return ['room'=>$this->presentRoom($room, $user),'attention'=>$attention,'canModerate'=>$user['isAdmin']];
        }
        if ($action === 'rooms') {
            $limit = min(50, max(1, (int)($data['limit'] ?? 20)));
            $query = Db::name('chat_room')->where('id', '<>', 1);
            if (($data['scope'] ?? '') === 'mine') {
                $ids = Db::name('chat_member')->where('user_id', $user['id'])->whereIn('status', ['joined','pending'])->column('room_id');
                $query->whereIn('id', $ids ?: [0]);
                if (($data['filter'] ?? '') === 'owned') $query->where('owner_id', $user['id']);
                if (($data['filter'] ?? '') === 'closed') $query->where(function ($q) { $q->where('status','closed')->whereOr('expires_at','<=',self::now()); });
                else $query->where('status','open')->where('expires_at','>',self::now());
            } else $query->where(['visibility'=>'public','status'=>'open'])->where('expires_at','>',self::now());
            if (!empty($data['category'])) $query->where('category', $this->text($data, 'category', 16));
            if (!empty($data['search'])) $query->whereLike('title', '%'.addcslashes($this->text($data, 'search', 30), '%_\\').'%');
            if (!empty($data['cursor'])) $query->where('id','<',$this->integer($data,'cursor'));
            $rows = $query->order('id desc')->limit($limit+1)->select()->toArray();
            $more = count($rows) > $limit; if ($more) array_pop($rows);
            return ['rooms'=>array_map(function ($room) use ($user) { return $this->presentRoom($room,$user); }, $rows),'nextCursor'=>$more ? (int)end($rows)['id'] : null];
        }
        if ($action === 'create') {
            $title = $this->text($data,'title',30); $description = $this->text($data,'description',300,false);
            $client = $this->text($data,'clientId',80); $hours = $this->integer($data,'durationHours');
            if (!in_array($hours,[2,24,72,168],true) || !in_array($data['category'] ?? '',['二手','出游','答疑','拼车','兴趣','其他'],true) || !in_array($data['visibility'] ?? '',['public','private'],true)) $this->fail('创建参数不正确');
            $room = Db::transaction(function () use ($user,$data,$title,$description,$client,$hours) {
                // 同一个员工的建房请求串行，严格执行活跃板块数量限制。
                $this->identityQuery($user['id'])->lock(true)->find();
                $existing = Db::name('chat_room')->where(['owner_id'=>$user['id'],'client_id'=>$client])->find();
                if ($existing) return $existing;
                if (Db::name('chat_room')->where(['owner_id'=>$user['id'],'status'=>'open'])->where('expires_at','>',self::now())->count() >= 3) $this->fail('最多同时创建 3 个活跃板块',409);
                $id = Db::name('chat_room')->insertGetId(['title'=>$title,'category'=>$data['category'],'visibility'=>$data['visibility'],'description'=>$description,'owner_id'=>$user['id'],'client_id'=>$client,'status'=>'open','expires_at'=>self::now()+$hours*3600000,'created_at'=>self::now()]);
                $room = $this->roomRow($id,true); $this->addMember($room,$user,'joined'); $this->log($id,$user['id'],'create');
                return $this->roomRow($id,true);
            });
            return ['room'=>$this->presentRoom($room,$user)];
        }
        if ($action === 'adminReports') {
            $this->admin($user);
            $reports=Db::name('chat_report')->where('status','pending')->order('id desc')->limit(100)->select()->toArray();
            foreach($reports as &$report){
                $message=Db::name('chat_message')->find($report['message_id']);$reportedRoom=Db::name('chat_room')->find($report['room_id']);
                $report['roomTitle']=$reportedRoom['title']??'已清理板块';
                $report['senderName']=$message['sender_name']??'已清理消息';
                $report['content']=$message ? ($message['recalled']?'消息已撤回':($message['type']==='image'?'[图片]':$message['content'])) : '消息已清理';
                $this->log($report['room_id'],$user['id'],'report-review',(string)$report['id']);
            }unset($report);
            return ['reports'=>$reports];
        }
        $id = !empty($data['roomId']) ? $this->integer($data,'roomId') : $this->inviteRoom($data['invite'] ?? '');
        $room = $this->roomRow($id);
        if ($action === 'room') { $this->accessPreview($room,$user,$data['invite'] ?? null); return ['room'=>$this->presentRoom($room,$user)]; }
        if ($action === 'join') {
            Db::transaction(function () use ($id,$user,$data) {
                $room = $this->roomRow($id,true); $this->open($room);
                if ($room['visibility'] === 'private' && $this->inviteRoom($data['invite'] ?? '') !== $id) $this->fail('邀请不属于此板块',403);
                $this->addMember($room,$user,$room['visibility'] === 'private' ? 'pending' : 'joined');
                if ($room['visibility'] === 'private') Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$room['owner_id']])->update(['attention'=>1]);
            });
            $this->notify($id); return ['room'=>$this->presentRoom($this->roomRow($id),$user)];
        }
        if ($action === 'resolveReport') {
            $this->admin($user);
            $reportId = $this->integer($data,'reportId');
            Db::transaction(function () use ($id,$user,$data,$reportId) {
                $room = $this->roomRow($id,true);
                $report = Db::name('chat_report')->where(['id'=>$reportId,'room_id'=>$id])->find();
                if (!$report) $this->fail('举报不存在',404);
                if (($data['resolution'] ?? '') === 'close') { if ($id === 1) $this->fail('公共交流不能关闭'); $this->closeLocked($room,$user['id']); }
                elseif (($data['resolution'] ?? '') === 'mute') {
                    $message=Db::name('chat_message')->find($report['message_id']);
                    if(!$message || !(int)$message['sender_id'])$this->fail('无法对该消息进行禁言');
                    Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$message['sender_id']])->update(['muted'=>1]);
                }
                elseif (($data['resolution'] ?? '') === 'recall') {
                    $revision=(int)$room['version']+1;
                    Db::name('chat_message')->where(['id'=>$report['message_id'],'room_id'=>$id])->update(['recalled'=>1,'revision'=>$revision]);
                    $replies=Db::name('chat_message')->where(['room_id'=>$id,'reply_id'=>$report['message_id']])->column('id');
                    foreach($replies as $reply){$revision++;Db::name('chat_message')->where('id',$reply)->update(['revision'=>$revision]);}
                    Db::name('chat_room')->where('id',$id)->update(['version'=>$revision]);
                }
                elseif (($data['resolution'] ?? '') !== 'dismiss') $this->fail('处理方式无效');
                Db::name('chat_report')->where('id',$reportId)->update(['status'=>'resolved']); $this->log($id,$user['id'],'resolve-report',(string)$reportId);
            });
            $this->notify($id); return [];
        }
        $member = $this->joined($room,$user);
        if ($action === 'messages') {
            $limit = min(200,max(1,(int)($data['limit'] ?? 40))); $version = (int)$room['version'];
            $before = !empty($data['before']); $incremental = !$before && isset($data['cursor']);
            $position=$before?$this->integer($data,'before'):($incremental?$this->integer($data,'cursor',0):0);
            // 权限已实时核对；无新版本时不查询消息表。
            if ($incremental && $position >= $version) return ['messages'=>[],'cursor'=>$version,'olderCursor'=>null,'hasMore'=>false,'hasUpdates'=>false,'room'=>$this->presentRoom($room,$user)];
            // 缓存原始行，图片签名按收件人生成；撤回/新消息改变版本后使用新键。
            $cacheKey=$this->key('page:'.$id.':'.$version.':'.($before?'before':($incremental?'after':'latest')).':'.$position.':'.$limit);
            $cached=json_decode((string)$this->redis()->get($cacheKey),true);
            if (is_array($cached) && isset($cached['rows'],$cached['replies'])) { $rows=$cached['rows'];$replies=$cached['replies']; }
            else {
                $query=Db::name('chat_message')->where('room_id',$id)->where('revision','<=',$version);
                if($before)$query->where('id','<',$position);elseif($incremental)$query->where('revision','>',$position);
                $rows=$query->order($incremental?'revision asc':'id desc')->limit($limit+1)->select()->toArray();
                $replyIds=array_values(array_unique(array_filter(array_column($rows,'reply_id'))));
                $replies=$replyIds?Db::name('chat_message')->where('room_id',$id)->whereIn('id',$replyIds)->column('*','id'):[];
                $this->redis()->setex($cacheKey,30,json_encode(['rows'=>$rows,'replies'=>$replies]));
            }
            $more = count($rows)>$limit; if ($more) array_pop($rows);
            $cursor = $incremental && $more ? (int)end($rows)['revision'] : $version;
            $older = $rows ? (int)min(array_column($rows,'id')) : null;
            return ['messages'=>array_map(function ($row) use ($user,$replies) { return $this->presentMessage($row,$user,$replies); },$rows),'cursor'=>$cursor,'olderCursor'=>$older,'hasMore'=>!$incremental && $more,'hasUpdates'=>$incremental && $more,'room'=>$this->presentRoom($room,$user)];
        }
        if ($action === 'mentionMembers' && $id===1) return $this->hallMentionMembers($data);
        if ($action === 'members' || $action === 'mentionMembers') {
            $query = Db::name('chat_member')->alias('cm')->where(['cm.room_id'=>$id,'cm.status'=>'joined'])->field('cm.*');
            if (!empty($data['search'])) {
                $search='%'.addcslashes($this->text($data,'search',30),'%_\\').'%';
                $username='COALESCE('.$this->colleagueText('na.username').','.$this->colleagueText('a.username').')';
                $query->leftJoin('admin_user_mobile a','a.id=cm.user_id')->leftJoin('auth na','na.id=CASE WHEN cm.user_id>='.self::AUTH_ID_OFFSET.' THEN cm.user_id-'.self::AUTH_ID_OFFSET.' ELSE 0 END')->leftJoin('member_list m',$this->colleagueText('m.eid').'='.$username.' AND COALESCE(na.user_type,a.user_type)=1')->leftJoin('admin_user u',$this->colleagueText('u.username').'='.$username.' AND COALESCE(na.user_type,a.user_type)>1');
                $query->where(function($q)use($search){$q->whereLike('m.ename',$search)->whereOr('u.truename','like',$search);});
            }
            if (!empty($data['cursor'])) $query->where('cm.user_id','>',$this->integer($data,'cursor'));
            $limit=min(100,max(1,(int)($data['limit']??50)));
            $rows=$query->order('cm.user_id')->limit($limit+1)->select()->toArray(); $more=count($rows)>$limit; if($more)array_pop($rows);
            $present=function($row) { try { $person=$this->user($row['user_id']); return ['id'=>$person['id'],'name'=>$person['name'],'muted'=>(bool)$row['muted']]; } catch(HttpResponseException $error) { return ['id'=>(int)$row['user_id'],'name'=>'已停用同事','muted'=>(bool)$row['muted']]; } };
            $pending=(int)$room['owner_id']===$user['id'] ? Db::name('chat_member')->where(['room_id'=>$id,'status'=>'pending'])->limit(100)->select()->toArray() : [];
            return ['members'=>array_map($present,$rows),'requests'=>array_map($present,$pending),'nextCursor'=>$more ? (int)end($rows)['user_id'] : null];
        }
        if ($action === 'read') {
            $sequence=$this->integer($data,'sequence',0);
            Db::transaction(function()use($id,$user,$sequence,$room){
                // 已读只锁该成员行，大厅多人确认已读时不争抢板块锁。
                $member=Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$user['id']])->lock(true)->find();
                if(!$member||$member['status']!=='joined')$this->fail('你不是该板块成员',403);
                $last=(int)Db::name('chat_message')->where('room_id',$id)->max('id');$sequence=min($sequence,$last);
                if($sequence<(int)$member['last_read'])return;
                $pending=(int)$room['owner_id']===$user['id'] && Db::name('chat_member')->where(['room_id'=>$id,'status'=>'pending'])->count()>0;
                $attention=$pending || (int)$member['last_mention']>$sequence;
                Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$user['id']])->update(['last_read'=>$sequence,'attention'=>$attention?1:0]);
            });return [];
        }
        if ($action === 'report') {
            $this->rate('report:'.$user['id'],10,60); $messageId=$this->integer($data,'messageId'); $reason=$this->text($data,'reason',300);
            if (!Db::name('chat_message')->where(['id'=>$messageId,'room_id'=>$id])->find()) $this->fail('消息不存在',404);
            if (!Db::name('chat_report')->where(['user_id'=>$user['id'],'message_id'=>$messageId])->find()) Db::name('chat_report')->insert(['room_id'=>$id,'message_id'=>$messageId,'user_id'=>$user['id'],'reason'=>$reason,'created_at'=>self::now()]);
            return [];
        }
        if ($action === 'connect') {
            $url=$this->app->config->get('chat.socket_url','');
            if (!preg_match('#^wss://#',$url)) $this->fail('公告板实时服务尚未配置',503);
            $ticket=bin2hex(random_bytes(32));
            $this->redis()->setex($this->key('socket:'.hash('sha256',$ticket)),30,json_encode(['roomId'=>$id,'userId'=>$user['id']]));
            return ['url'=>$url.(strpos($url,'?')===false?'?':'&').'ticket='.$ticket];
        }
        if ($action === 'image') return $this->upload($room,$user,$data['file']??null);
        $result=Db::transaction(function () use ($action,$id,$user,$data) {
            $room=$this->roomRow($id,true); $member=$this->joined($room,$user);
            if ($action === 'send') {
                $client=$this->text($data,'clientId',80);
                $existing=Db::name('chat_message')->where(['room_id'=>$id,'sender_id'=>$user['id'],'client_id'=>$client])->find();
                if ($existing) return ['message'=>$this->presentMessage($existing,$user)];
                $this->open($room); if($member['muted'])$this->fail('你已被发起人禁言',403);
                $this->rate('send:'.$user['id'],30,60);
                $type=$data['type']??''; if(!in_array($type,['text','image'],true))$this->fail('消息类型不正确');
                $content=$type==='text' ? $this->text($data,'content',1000) : '';
                $imageId=$type==='image' ? $this->text($data,'imageId',64) : null;
                if($imageId && !Db::name('chat_image')->where(['id'=>$imageId,'room_id'=>$id,'user_id'=>$user['id']])->find())$this->fail('图片不存在或不属于此板块',403);
                $replyId=empty($data['replyId']) ? null : $this->integer($data,'replyId');
                if($replyId && !Db::name('chat_message')->where(['id'=>$replyId,'room_id'=>$id,'recalled'=>0])->find())$this->fail('引用的消息已撤回或不存在');
                $mentions=$data['mentionIds']??[]; if(!is_array($mentions)||count($mentions)>20)$this->fail('提及成员过多');
                $valid=[]; foreach($mentions as $mention){
                    $target=$this->integer(['id'=>$mention],'id');
                    if($id===1 && ($target<self::AUTH_ID_OFFSET || !$this->mentionAccounts()->where('id',$target-self::AUTH_ID_OFFSET)->find()))$this->fail('提及的同事已停用或尚未认证',403);
                    if($id===1 && !Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$target,'status'=>'joined'])->find()){
                        $this->addMember($room,$this->user($target),'joined');
                    }
                    if(Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$target,'status'=>'joined'])->find())$valid[]=$target;
                }
                $messageId=$this->message($room,$user,['client_id'=>$client,'type'=>$type,'content'=>$content,'image_id'=>$imageId,'reply_id'=>$replyId,'mentions'=>json_encode(array_unique($valid))]);
                if($valid)Db::name('chat_member')->where('room_id',$id)->whereIn('user_id',$valid)->update(['attention'=>1,'last_mention'=>$messageId]);
                return ['message'=>$this->presentMessage(Db::name('chat_message')->find($messageId),$user)];
            }
            $this->open($room);
            if($action==='recall'){
                $messageId=$this->integer($data,'messageId'); $message=Db::name('chat_message')->where(['id'=>$messageId,'room_id'=>$id])->find();
                if(!$message || (int)$message['sender_id']!==$user['id'] || $message['type']==='system')$this->fail('只能撤回自己的消息',403);
                if($message['recalled'])return [];
                if(self::now()-(int)$message['created_at']>=120000)$this->fail('只能撤回 2 分钟内的消息',409);
                $revision=(int)$room['version']+1; Db::name('chat_room')->where('id',$id)->update(['version'=>$revision]);
                Db::name('chat_message')->where('id',$messageId)->update(['recalled'=>1,'revision'=>$revision]);
                // 引用消息也递增 revision，使已显示的引用同步撤回状态。
                $replies=Db::name('chat_message')->where(['room_id'=>$id,'reply_id'=>$messageId])->column('id');
                foreach($replies as $reply){$revision++;Db::name('chat_message')->where('id',$reply)->update(['revision'=>$revision]);}
                Db::name('chat_room')->where('id',$id)->update(['version'=>$revision]);return [];
            }
            if($action==='leave'){
                if($id===1 || (int)$room['owner_id']===$user['id'])$this->fail('公共交流或发起人不能退出');
                Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$user['id']])->update(['status'=>'left']);Db::name('chat_room')->where('id',$id)->dec('member_count')->update();return [];
            }
            $this->owner($room,$user); if($id===1)$this->fail('公共交流不支持此操作');
            if($action==='extend'){
                $hours=$this->integer($data,'hours');if(!in_array($hours,[24,72,168],true))$this->fail('延期时长无效');
                $expires=(int)$room['expires_at']+$hours*3600000;Db::name('chat_room')->where('id',$id)->update(['expires_at'=>$expires,'reminder_stage'=>0]);
                $this->systemMessage($room,'发起人已将板块延期至 '.date('m/d H:i',(int)($expires/1000)));$this->log($id,$user['id'],'extend',(string)$expires);
            }elseif($action==='close')$this->closeLocked($room,$user['id']);
            elseif($action==='update'){ $description=$this->text($data,'description',300,false);Db::name('chat_room')->where('id',$id)->update(['description'=>$description]);$this->log($id,$user['id'],'update'); }
            elseif($action==='invite'){
                $token=bin2hex(random_bytes(32));Db::name('chat_invite')->insert(['token_hash'=>hash('sha256',$token),'room_id'=>$id,'owner_id'=>$user['id'],'expires_at'=>min((int)$room['expires_at'],self::now()+86400000)]);return ['token'=>$token];
            }elseif(in_array($action,['approve','mute','remove'],true)){
                $target=$this->integer($data,'memberId');if($target===$user['id'])$this->fail('不能对发起人进行此操作');
                $targetMember=$this->member($id,$target);if(!$targetMember)$this->fail('成员不存在',404);
                if($action==='approve'){
                    if($targetMember['status']!=='pending')$this->fail('申请已处理',409);
                    $accepted=$this->boolValue($data,'accepted');
                    if($accepted){$this->user($target);$this->addMember($room,['id'=>$target],'joined');Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$target])->update(['attention'=>1]);}
                    else Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$target])->update(['status'=>'rejected']);
                    $hasPending=Db::name('chat_member')->where(['room_id'=>$id,'status'=>'pending'])->count()>0;
                    $ownerMember=$this->member($id,$user['id']);
                    Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$user['id']])->update(['attention'=>$hasPending || (int)$ownerMember['last_mention']>(int)$ownerMember['last_read']?1:0]);
                }else{
                    if($targetMember['status']!=='joined')$this->fail('该同事不是板块成员');
                    if($action==='mute')Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$target])->update(['muted'=>$this->boolValue($data,'muted')?1:0]);
                    else {Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$target])->update(['status'=>'removed']);Db::name('chat_room')->where('id',$id)->dec('member_count')->update();}
                }
                $this->log($id,$user['id'],$action,(string)$target);
            }else $this->fail('不支持的操作');
            return [];
        });
        $this->notify($id);return $result;
    }

    private function upload($room,$user,$file) {
        $this->open($room);$member=$this->joined($room,$user);if($member['muted'])$this->fail('你已被禁言',403);
        $this->rate('image:'.$user['id'],10,60);
        if(!$file || $file->getSize()>5*1024*1024)$this->fail('请选择不超过 5MB 的图片');
        $info=@getimagesize($file->getPathname());
        if(!$info || !in_array($info[2],[IMAGETYPE_JPEG,IMAGETYPE_PNG,IMAGETYPE_GIF],true) || $info[0]*$info[1]>25000000)$this->fail('仅支持 JPG、PNG、GIF 图片，图片尺寸过大时请压缩');
        $extension=[IMAGETYPE_JPEG=>'jpg',IMAGETYPE_PNG=>'png',IMAGETYPE_GIF=>'gif'][$info[2]];
        $id=bin2hex(random_bytes(32));$root=$this->app->config->get('chat.image_root');
        if(!is_dir($root)&&!mkdir($root,0700,true))$this->fail('图片存储不可用',503);
        $path=$id.'.'.$extension;
        if(!move_uploaded_file($file->getPathname(),$root.'/'.$path))$this->fail('图片保存失败',503);
        try{Db::name('chat_image')->insert(['id'=>$id,'room_id'=>$room['id'],'user_id'=>$user['id'],'path'=>$path,'created_at'=>self::now()]);}
        catch(\Throwable $error){@unlink($root.'/'.$path);throw $error;}
        return ['imageId'=>$id];
    }
    public function imageContent($ticket) {
        if(!preg_match('/^[a-f0-9]{64}$/',$ticket))$this->fail('图片授权已失效',403);
        $data=json_decode((string)$this->redis()->get($this->key('image:'.hash('sha256',$ticket))),true);
        if(!$data)$this->fail('图片授权已失效，请重新进入板块',403);
        $user=$this->user($data['userId']);$image=Db::name('chat_image')->find($data['id']);
        $openid=(string)$this->identityQuery($data['userId'])->value('openid');
        if(empty($data['openid']) || !hash_equals($data['openid'],$openid))$this->fail('图片授权已失效，请重新进入板块',403);
        if(!$image)$this->fail('图片不存在',404);
        $this->joined($this->roomRow($image['room_id']),$user);
        if(Db::name('chat_message')->where('image_id',$image['id'])->where('recalled',1)->find())$this->fail('图片消息已撤回',403);
        if(!preg_match('/^[a-f0-9]{64}\.(jpg|png|gif)$/',$image['path']))$this->fail('图片路径无效',404);
        $path=$this->app->config->get('chat.image_root').'/'.$image['path'];if(!is_file($path))$this->fail('图片已清理',404);
        return ['path'=>$path,'mime'=>mime_content_type($path)];
    }
    public function maintain($cleanup = true) {
        $ids=Db::name('chat_room')->where('status','open')->whereNotNull('expires_at')->where('expires_at','<=',self::now())->limit(200)->column('id');
        foreach($ids as $id)$this->expire($id);
        $soon=Db::name('chat_room')->where(['status'=>'open','reminder_stage'=>0])->whereBetween('expires_at',[self::now()+1,self::now()+3600000])->limit(100)->column('id');
        $tenMinutes=Db::name('chat_room')->where(['status'=>'open','reminder_stage'=>1])->whereBetween('expires_at',[self::now()+1,self::now()+600000])->limit(100)->column('id');
        $soon=array_merge($soon,$tenMinutes);
        foreach($soon as$id){
            $changed=Db::transaction(function()use($id){
                $room=$this->roomRow($id,true);if($room['status']!=='open'||$room['expires_at']<=self::now()||$room['expires_at']>self::now()+3600000)return false;
                $stage=$room['expires_at']-self::now()<=600000?2:1;
                if($stage<=(int)$room['reminder_stage'])return false;
                Db::name('chat_room')->where('id',$id)->update(['reminder_stage'=>$stage]);
                $this->systemMessage($room,$stage===2?'板块将在 10 分钟内关闭，发起人可在详情中延期。':'板块将在 1 小时内关闭，发起人可在详情中延期。');
                Db::name('chat_member')->where(['room_id'=>$id,'user_id'=>$room['owner_id']])->update(['attention'=>1]);
                return true;
            });if($changed)$this->notify($id);
        }
        if (!$cleanup) return ['closed'=>count($ids),'purged'=>0];
        $old=Db::name('chat_room')->where('status','closed')->where('closed_at','<',self::now()-30*86400000)->limit(100)->column('id');
        foreach($old as $id){
            $paths=Db::name('chat_image')->where('room_id',$id)->column('path');
            Db::transaction(function()use($id){foreach(['chat_message','chat_member','chat_invite','chat_image','chat_report','chat_log']as$table)Db::name($table)->where('room_id',$id)->delete();Db::name('chat_room')->where('id',$id)->delete();});
            foreach($paths as $path)if(preg_match('/^[a-f0-9]{64}\.(jpg|png|gif)$/',$path))@unlink($this->app->config->get('chat.image_root').'/'.$path);
        }
        Db::name('chat_invite')->where('expires_at','<=',self::now())->delete();
        $this->redis()->zRemRangeByScore($this->key('events'),'-inf',self::now()-3600000);
        return ['closed'=>count($ids),'purged'=>count($old)];
    }
}