"""
groups/manager.py - 群聊管理器
==============================
标准群聊实现，包含:
  - 群的创建、编辑、解散
  - 成员管理（邀请、移除、角色）
  - 群消息发送与路由（广播到所有成员agent）
  - 群消息历史持久化（SQLite）
  - 群主/管理员权限控制
"""
from __future__ import annotations

import json
import time
import uuid
import sqlite3
import logging
import asyncio
import threading
from dataclasses import dataclass, field, asdict
from typing import Optional, List, Dict, Any
from pathlib import Path

logger = logging.getLogger("myagent.groups")


# =========================================================================
# 数据模型
# =========================================================================

@dataclass
class GroupMember:
    """群成员"""
    agent_path: str            # Agent 的数字 aid，如 "1", "2", "3"
    role: str = "member"       # owner | admin | member
    joined_at: float = 0.0
    nickname: str = ""         # 群内昵称（空=使用agent默认名）
    muted: bool = False        # 是否禁言

    def to_dict(self) -> dict:
        return {
            "agent_path": self.agent_path,
            "role": self.role,
            "joined_at": self.joined_at,
            "nickname": self.nickname,
            "muted": self.muted,
        }

    @classmethod
    def from_dict(cls, data: dict) -> "GroupMember":
        return cls(
            agent_path=data.get("agent_path", ""),
            role=data.get("role", "member"),
            joined_at=data.get("joined_at", 0.0),
            nickname=data.get("nickname", ""),
            muted=data.get("muted", False),
        )


@dataclass
class GroupMessage:
    """群消息"""
    id: str = ""
    group_id: str = ""
    sender: str = ""            # "user" 或 agent 的数字 aid
    sender_name: str = ""       # 发送者显示名
    sender_avatar: str = ""     # 发送者头像emoji
    content: str = ""
    msg_type: str = "text"      # text | system
    timestamp: float = 0.0
    agent_path: str = ""        # 消息关联的 agent 的数字 aid

    def to_dict(self) -> dict:
        return {
            "id": self.id,
            "group_id": self.group_id,
            "sender": self.sender,
            "sender_name": self.sender_name,
            "sender_avatar": self.sender_avatar,
            "content": self.content,
            "msg_type": self.msg_type,
            "timestamp": self.timestamp,
            "agent_path": self.agent_path,
        }


@dataclass
class Group:
    """群"""
    id: str = ""
    name: str = ""
    description: str = ""
    avatar_emoji: str = "👥"
    avatar_color: str = "#6366f1"
    owner: str = ""             # 创建者 agent_path
    created_at: float = 0.0
    updated_at: float = 0.0
    max_members: int = 50
    members: List[GroupMember] = field(default_factory=list)

    # 运行时字段（不序列化）
    _member_map: Dict[str, GroupMember] = field(default_factory=dict, repr=False)

    def __post_init__(self):
        if not self.id:
            self.id = uuid.uuid4().hex[:12]
        if self.created_at == 0.0:
            self.created_at = time.time()
        self.updated_at = self.created_at
        self._rebuild_member_map()

    def _rebuild_member_map(self):
        self._member_map = {m.agent_path: m for m in self.members}

    def get_member(self, agent_path: str) -> Optional[GroupMember]:
        return self._member_map.get(agent_path)

    def add_member(self, member: GroupMember) -> bool:
        if member.agent_path in self._member_map:
            return False
        if member.joined_at == 0.0:
            member.joined_at = time.time()
        self.members.append(member)
        self._member_map[member.agent_path] = member
        self.updated_at = time.time()
        return True

    def remove_member(self, agent_path: str) -> bool:
        if agent_path not in self._member_map:
            return False
        self.members = [m for m in self.members if m.agent_path != agent_path]
        del self._member_map[agent_path]
        self.updated_at = time.time()
        return True

    def member_count(self) -> int:
        return len(self.members)

    def to_dict(self, include_members: bool = True) -> dict:
        d = {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "avatar_emoji": self.avatar_emoji,
            "avatar_color": self.avatar_color,
            "owner": self.owner,
            "created_at": self.created_at,
            "updated_at": self.updated_at,
            "max_members": self.max_members,
            "member_count": len(self.members),
        }
        if include_members:
            d["members"] = [m.to_dict() for m in self.members]
        return d

    def to_persist_dict(self) -> dict:
        """序列化为持久化格式"""
        return {
            "id": self.id,
            "name": self.name,
            "description": self.description,
            "avatar_emoji": self.avatar_emoji,
            "avatar_color": self.avatar_color,
            "owner": self.owner,
            "created_at": self.created_at,
            "updated_at": self.updated_at,
            "max_members": self.max_members,
            "members": [m.to_dict() for m in self.members],
        }

    @classmethod
    def from_dict(cls, data: dict) -> "Group":
        g = cls(
            id=data.get("id", ""),
            name=data.get("name", ""),
            description=data.get("description", ""),
            avatar_emoji=data.get("avatar_emoji", "👥"),
            avatar_color=data.get("avatar_color", "#6366f1"),
            owner=data.get("owner", ""),
            created_at=data.get("created_at", 0.0),
            updated_at=data.get("updated_at", 0.0),
            max_members=data.get("max_members", 50),
        )
        for m_data in data.get("members", []):
            g.members.append(GroupMember.from_dict(m_data))
        g._rebuild_member_map()
        return g


# =========================================================================
# GroupManager
# =========================================================================

class GroupManager:
    """
    群聊管理器。

    负责:
      - 群的 CRUD（持久化到 JSON 文件）
      - 群消息持久化（SQLite）
      - 群消息路由（广播到所有成员 agent）
      - 权限控制（群主/管理员）
    """

    def __init__(self, data_dir: str | Path = ""):
        self._data_dir = Path(data_dir) if data_dir else Path.home() / ".myagent" / "data"
        self._groups: Dict[str, Group] = {}
        self._db_conn: Optional[sqlite3.Connection] = None
        self._db_lock = threading.Lock()  # 线程安全锁，保护 SQLite 操作

    # ==================================================================
    # 初始化
    # ==================================================================

    def initialize(self):
        """初始化群管理器（加载群数据、创建消息数据库）"""
        groups_dir = self._data_dir / "groups"
        groups_dir.mkdir(parents=True, exist_ok=True)

        # 加载群数据
        self._load_groups(groups_dir)

        # 初始化消息数据库
        self._init_message_db()

        logger.info(f"GroupManager 初始化完成: {len(self._groups)} 个群")

    def _load_groups(self, groups_dir: Path):
        """从磁盘加载群数据"""
        if not groups_dir.exists():
            return
        for f in sorted(groups_dir.iterdir()):
            if f.suffix == ".json" and f.stem.startswith("group_"):
                try:
                    data = json.loads(f.read_text(encoding="utf-8"))
                    g = Group.from_dict(data)
                    self._groups[g.id] = g
                except Exception as e:
                    logger.warning(f"加载群文件失败: {f.name}: {e}")

    def _save_group(self, group: Group):
        """保存群数据到磁盘"""
        groups_dir = self._data_dir / "groups"
        groups_dir.mkdir(parents=True, exist_ok=True)
        path = groups_dir / f"group_{group.id}.json"
        path.write_text(
            json.dumps(group.to_persist_dict(), indent=2, ensure_ascii=False),
            encoding="utf-8",
        )

    def _delete_group_file(self, group_id: str):
        """删除群文件"""
        groups_dir = self._data_dir / "groups"
        path = groups_dir / f"group_{group_id}.json"
        if path.exists():
            path.unlink()

    def _init_message_db(self):
        """初始化群消息 SQLite 数据库"""
        db_path = self._data_dir / "groups" / "messages.db"
        logger.info(f"[私聊保存] 初始化消息数据库: {db_path}")
        self._db_conn = sqlite3.connect(str(db_path), check_same_thread=False)
        self._db_conn.row_factory = sqlite3.Row
        logger.info(f"[私聊保存] 数据库连接已创建")
        # [v1.23.81] 群聊 session 映射表（每个群一个纯数字 session ID）
        self._db_conn.execute("""
            CREATE TABLE IF NOT EXISTS group_sessions (
                group_id TEXT PRIMARY KEY,
                session_id TEXT NOT NULL UNIQUE,
                created_at REAL NOT NULL
            )
        """)
        self._db_conn.execute("""
            CREATE TABLE IF NOT EXISTS group_messages (
                id TEXT PRIMARY KEY,
                group_id TEXT NOT NULL,
                sender TEXT NOT NULL,
                sender_name TEXT DEFAULT '',
                sender_avatar TEXT DEFAULT '',
                content TEXT NOT NULL,
                msg_type TEXT DEFAULT 'text',
                timestamp REAL NOT NULL,
                agent_path TEXT DEFAULT ''
            )
        """)
        self._db_conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_group_messages_group_id
            ON group_messages(group_id, timestamp)
        """)
        # [v1.23.37] Agent间私聊记录表（独立于群聊消息，便于查询和管理）
        self._db_conn.execute("""
            CREATE TABLE IF NOT EXISTS agent_chat (
                id TEXT PRIMARY KEY,
                group_id TEXT NOT NULL DEFAULT '',
                from_agent TEXT NOT NULL,
                from_name TEXT DEFAULT '',
                to_agent TEXT NOT NULL,
                to_name TEXT DEFAULT '',
                content TEXT NOT NULL,
                timestamp REAL NOT NULL
            )
        """)
        self._db_conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_agent_chat_group
            ON agent_chat(group_id, timestamp)
        """)
        self._db_conn.execute("""
            CREATE INDEX IF NOT EXISTS idx_agent_chat_pair
            ON agent_chat(from_agent, to_agent, timestamp)
        """)
        self._db_conn.commit()

    def close(self):
        """关闭数据库连接"""
        if self._db_conn:
            self._db_conn.close()
            self._db_conn = None

    # ==================================================================
    # 群 CRUD
    # ==================================================================

    def create_group(
        self,
        name: str,
        owner: str = "default",
        description: str = "",
        avatar_emoji: str = "👥",
        avatar_color: str = "",
        member_paths: Optional[List[str]] = None,
    ) -> Group:
        """
        创建群。

        Args:
            name: 群名称
            owner: 创建者 agent_path（自动成为群主）
            description: 群描述
            avatar_emoji: 群头像 emoji
            avatar_color: 群头像颜色
            member_paths: 额外的成员 agent_path 列表

        Returns:
            创建的 Group 对象
        """
        if not avatar_color:
            colors = ['#6366f1','#ec4899','#f59e0b','#10b981','#3b82f6',
                      '#8b5cf6','#ef4444','#14b8a6','#f97316','#06b6d4']
            avatar_color = colors[hash(name) % len(colors)]

        group = Group(
            name=name,
            description=description,
            avatar_emoji=avatar_emoji,
            avatar_color=avatar_color,
            owner=owner,
        )

        # 添加创建者为群主（仅当 owner 非空时）
        if owner:
            owner_member = GroupMember(agent_path=owner, role="owner")
            group.add_member(owner_member)

        # 添加额外成员
        if member_paths:
            for mp in member_paths:
                if mp and mp != owner:
                    group.add_member(GroupMember(agent_path=mp, role="member"))

        self._groups[group.id] = group
        self._save_group(group)

        # 添加系统消息
        self._add_system_message(group.id, f"群 {name} 创建成功")

        logger.info(f"创建群: {name} (id={group.id}, members={group.member_count()})")
        return group

    def get_group(self, group_id: str) -> Optional[Group]:
        """获取群信息"""
        return self._groups.get(group_id)

    def list_groups(self) -> List[Group]:
        """列出所有群"""
        return sorted(self._groups.values(), key=lambda g: g.updated_at, reverse=True)

    def update_group(
        self,
        group_id: str,
        name: str = "",
        description: str = "",
        avatar_emoji: str = "",
        avatar_color: str = "",
    ) -> Optional[Group]:
        """更新群信息（仅群主/管理员可操作）"""
        group = self._groups.get(group_id)
        if not group:
            return None

        changed = False
        if name and name != group.name:
            old_name = group.name
            group.name = name
            self._add_system_message(group_id, f"群名由 \"{old_name}\" 改为 \"{name}\"")
            changed = True
        if description is not None and description != group.description:
            group.description = description
            changed = True
        if avatar_emoji and avatar_emoji != group.avatar_emoji:
            group.avatar_emoji = avatar_emoji
            changed = True
        if avatar_color and avatar_color != group.avatar_color:
            group.avatar_color = avatar_color
            changed = True

        if changed:
            group.updated_at = time.time()
            self._save_group(group)

        return group

    def delete_group(self, group_id: str, operator: str = "") -> bool:
        """
        解散群（仅群主可操作）。

        Args:
            group_id: 群ID
            operator: 操作者 agent_path

        Returns:
            是否成功
        """
        group = self._groups.get(group_id)
        if not group:
            return False

        # 权限检查
        if operator and operator != group.owner:
            logger.warning(f"非群主 {operator} 尝试解散群 {group_id}")
            return False

        # 删除群消息、session映射和Agent间私聊记录
        if self._db_conn:
            with self._db_lock:
                self._db_conn.execute(
                    "DELETE FROM group_messages WHERE group_id = ?", (group_id,)
                )
                self._db_conn.execute(
                    "DELETE FROM group_sessions WHERE group_id = ?", (group_id,)
                )
                self._db_conn.execute(
                    "DELETE FROM agent_chat WHERE group_id = ?", (group_id,)
                )
                self._db_conn.commit()

        # 删除群文件
        self._delete_group_file(group_id)
        del self._groups[group_id]

        logger.info(f"解散群: {group.name} (id={group_id})")
        return True

    # ==================================================================
    # 成员管理
    # ==================================================================

    def add_member(
        self,
        group_id: str,
        agent_path: str,
        role: str = "member",
        nickname: str = "",
        operator: str = "",
    ) -> Optional[Group]:
        """
        添加群成员。

        Args:
            group_id: 群ID
            agent_path: 要添加的 agent 的数字 aid
            role: 成员角色 (member | admin)
            nickname: 群内昵称
            operator: 操作者 agent_path

        Returns:
            更新后的 Group，失败返回 None
        """
        group = self._groups.get(group_id)
        if not group:
            return None

        # 权限检查：只有群主和管理员可以添加成员
        if operator:
            op_member = group.get_member(operator)
            if not op_member or op_member.role == "member":
                logger.warning(f"普通成员 {operator} 尝试添加成员到群 {group_id}")
                return group  # 权限不足，返回未变更的群

        # 检查人数上限
        if group.member_count() >= group.max_members:
            logger.warning(f"群 {group_id} 已满 ({group.max_members})")
            return None

        member = GroupMember(
            agent_path=agent_path,
            role=role,
            nickname=nickname,
        )
        if not group.add_member(member):
            return group  # 已存在，返回未变更的群

        self._save_group(group)
        self._add_system_message(
            group_id,
            f"{agent_path} 加入了群聊",
            sender=operator or "system",
        )

        logger.info(f"群 {group.name}: {agent_path} 加入 (role={role})")
        return group

    def remove_member(
        self,
        group_id: str,
        agent_path: str,
        operator: str = "",
    ) -> Optional[Group]:
        """
        移除群成员。

        Args:
            group_id: 群ID
            agent_path: 要移除的 agent 的数字 aid
            operator: 操作者 agent_path

        Returns:
            更新后的 Group，失败返回 None
        """
        group = self._groups.get(group_id)
        if not group:
            return None

        # 不能移除群主
        if agent_path == group.owner:
            logger.warning(f"尝试移除群主 {agent_path}")
            return group  # 不能移除群主

        # 权限检查
        if operator:
            op_member = group.get_member(operator)
            if not op_member or op_member.role == "member":
                # 普通成员只能移除自己
                if operator != agent_path:
                    logger.warning(f"普通成员 {operator} 尝试移除 {agent_path}")
                    return group  # 权限不足，返回未变更的群

        target = group.get_member(agent_path)
        target_name = target.nickname if target and target.nickname else agent_path

        if not group.remove_member(agent_path):
            return group  # 不存在，返回未变更的群

        self._save_group(group)
        self._add_system_message(
            group_id,
            f"{target_name} 离开了群聊",
            sender=operator or "system",
        )

        logger.info(f"群 {group.name}: {agent_path} 被移除")
        return group

    def set_member_role(
        self,
        group_id: str,
        agent_path: str,
        role: str,
        operator: str = "",
    ) -> Optional[Group]:
        """
        设置成员角色（仅群主可操作）。

        Args:
            group_id: 群ID
            agent_path: 目标成员 agent_path
            role: 新角色 (admin | member)
            operator: 操作者（必须是群主）
        """
        group = self._groups.get(group_id)
        if not group:
            return None

        # 仅群主可设置角色
        if operator != group.owner:
            return group  # 非群主，返回未变更的群

        # 不能修改群主角色
        if agent_path == group.owner:
            return group  # 不能修改群主

        member = group.get_member(agent_path)
        if not member:
            return group  # 成员不存在

        if member.role == role:
            return group  # 无变化

        old_role = member.role
        member.role = role
        self._save_group(group)
        self._add_system_message(
            group_id,
            f"{agent_path} 的角色从 {old_role} 变更为 {role}",
            sender=operator,
        )

        logger.info(f"群 {group.name}: {agent_path} 角色 {old_role} -> {role}")
        return group

    def set_member_muted(
        self,
        group_id: str,
        agent_path: str,
        muted: bool,
        operator: str = "",
    ) -> Optional[Group]:
        """
        设置成员禁言状态（群主/管理员可操作）。
        """
        group = self._groups.get(group_id)
        if not group:
            return None

        # 权限检查
        if operator:
            op_member = group.get_member(operator)
            if not op_member or op_member.role == "member":
                return group  # 权限不足

        member = group.get_member(agent_path)
        if not member:
            return group  # 成员不存在

        # 不能禁言群主
        if agent_path == group.owner:
            return group  # 不能禁言群主

        member.muted = muted
        self._save_group(group)

        action = "被禁言" if muted else "解除禁言"
        self._add_system_message(
            group_id,
            f"{agent_path} {action}",
            sender=operator,
        )

        return group

    # ==================================================================
    # [v1.23.81] 群聊 Session 管理（纯数字 ID，嵌入 URL 用于导航恢复）
    # ==================================================================

    def get_or_create_group_session(self, group_id: str) -> str:
        """
        获取或创建群聊的 session ID。
        每个群有且仅有一个 session ID（纯数字，以 9 开头，递增）。
        此 session ID 用于前端 URL 参数，确保从后台管理返回时能正确恢复群聊视图。
        """
        if not self._db_conn:
            return ""
        with self._db_lock:
            # 查找已有 session
            row = self._db_conn.execute(
                "SELECT session_id FROM group_sessions WHERE group_id = ?", (group_id,)
            ).fetchone()
            if row:
                return row["session_id"]
            # 生成新的纯数字 session ID（9 开头，13 位数字，时间戳后 8 位 + 随机 4 位）
            import random
            ts_part = str(int(time.time()))[-8:]
            rand_part = str(random.randint(1000, 9999))
            new_sid = "9" + ts_part + rand_part  # 共 13 位纯数字
            # 确保唯一性
            for _ in range(10):
                existing = self._db_conn.execute(
                    "SELECT 1 FROM group_sessions WHERE session_id = ?", (new_sid,)
                ).fetchone()
                if not existing:
                    break
                rand_part = str(random.randint(1000, 9999))
                new_sid = "9" + ts_part + rand_part
            self._db_conn.execute(
                "INSERT INTO group_sessions (group_id, session_id, created_at) VALUES (?, ?, ?)",
                (group_id, new_sid, time.time()),
            )
            self._db_conn.commit()
        logger.info(f"群聊 session 创建: group={group_id}, session={new_sid}")
        return new_sid

    def get_group_id_by_session(self, session_id: str) -> Optional[str]:
        """通过 session ID 查找 group_id（用于前端 URL 恢复）"""
        if not self._db_conn:
            return None
        row = self._db_conn.execute(
            "SELECT group_id FROM group_sessions WHERE session_id = ?", (session_id,)
        ).fetchone()
        return row["group_id"] if row else None

    def get_session_by_group(self, group_id: str) -> Optional[str]:
        """通过 group_id 查找 session_id"""
        if not self._db_conn:
            return None
        row = self._db_conn.execute(
            "SELECT session_id FROM group_sessions WHERE group_id = ?", (group_id,)
        ).fetchone()
        return row["session_id"] if row else None

    # ==================================================================
    # 消息管理
    # ==================================================================

    def _add_system_message(self, group_id: str, content: str, sender: str = "system"):
        """添加系统消息"""
        if not self._db_conn:
            return
        msg_id = uuid.uuid4().hex[:16]
        with self._db_lock:
            self._db_conn.execute(
                "INSERT INTO group_messages (id, group_id, sender, sender_name, sender_avatar, content, msg_type, timestamp) "
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                (msg_id, group_id, sender, "系统", "⚙️", content, "system", time.time()),
            )
            self._db_conn.commit()

    def add_message(self, msg: GroupMessage):
        """添加用户/AI消息到群消息记录"""
        if not self._db_conn:
            return
        if not msg.id:
            msg.id = uuid.uuid4().hex[:16]
        if msg.timestamp == 0.0:
            msg.timestamp = time.time()
        with self._db_lock:
            self._db_conn.execute(
                "INSERT INTO group_messages (id, group_id, sender, sender_name, sender_avatar, content, msg_type, timestamp, agent_path) "
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                (msg.id, msg.group_id, msg.sender, msg.sender_name, msg.sender_avatar,
                 msg.content, msg.msg_type, msg.timestamp, msg.agent_path),
            )
            self._db_conn.commit()

    def get_messages(
        self,
        group_id: str,
        limit: int = 100,
        before: float = 0,
    ) -> List[GroupMessage]:
        """获取群消息历史"""
        if not self._db_conn:
            return []

        with self._db_lock:
            if before > 0:
                rows = self._db_conn.execute(
                    "SELECT * FROM group_messages WHERE group_id = ? AND timestamp < ? "
                    "ORDER BY timestamp DESC LIMIT ?",
                    (group_id, before, limit),
                ).fetchall()
            else:
                rows = self._db_conn.execute(
                    "SELECT * FROM group_messages WHERE group_id = ? "
                    "ORDER BY timestamp DESC LIMIT ?",
                    (group_id, limit),
                ).fetchall()

            messages = []
            for r in reversed(rows):
                messages.append(GroupMessage(
                    id=r["id"],
                    group_id=r["group_id"],
                    sender=r["sender"],
                    sender_name=r["sender_name"],
                    sender_avatar=r["sender_avatar"],
                    content=r["content"],
                    msg_type=r["msg_type"],
                    timestamp=r["timestamp"],
                    agent_path=r["agent_path"],
                ))
        return messages

    def clear_messages(self, group_id: str) -> bool:
        """清空群消息"""
        if not self._db_conn:
            return False
        with self._db_lock:
            self._db_conn.execute(
                "DELETE FROM group_messages WHERE group_id = ?", (group_id,)
            )
            self._db_conn.commit()
        return True

    # ==================================================================
    # [v1.23.37] Agent间私聊记录
    # ==================================================================

    def add_agent_chat(self, group_id: str, from_agent: str, from_name: str,
                       to_agent: str, to_name: str, content: str) -> str:
        """添加一条Agent间私聊记录，返回消息ID"""
        logger.info(f"[私聊保存] add_agent_chat 被调用: group_id={group_id}, from_agent={from_agent}, from_name={from_name}, to_agent={to_agent}, to_name={to_name}, content={content[:50]}...")
        
        if not self._db_conn:
            logger.warning(f"[私聊保存] 数据库连接未初始化，无法保存")
            return ""
        
        msg_id = uuid.uuid4().hex[:16]
        logger.info(f"[私聊保存] 生成的消息ID: {msg_id}")
        
        try:
            with self._db_lock:
                logger.info(f"[私聊保存] 开始执行 INSERT 语句")
                self._db_conn.execute(
                    "INSERT INTO agent_chat (id, group_id, from_agent, from_name, to_agent, to_name, content, timestamp) "
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                    (msg_id, group_id, from_agent, from_name, to_agent, to_name, content, time.time()),
                )
                self._db_conn.commit()
                logger.info(f"[私聊保存] INSERT 成功，已提交事务，msg_id={msg_id}")
        except Exception as e:
            logger.error(f"[私聊保存] INSERT 失败: {e}")
            logger.exception(f"[私聊保存] 异常详情")
            return ""
        
        return msg_id

    def get_agent_chats(self, group_id: str = "", from_agent: str = "",
                        to_agent: str = "", limit: int = 200) -> list:
        """查询Agent间私聊记录，支持按群、发送方、接收方筛选"""
        if not self._db_conn:
            return []
        conditions = []
        params = []
        if group_id:
            conditions.append("group_id = ?")
            params.append(group_id)
        if from_agent:
            conditions.append("(from_agent = ? OR to_agent = ?)")
            params.extend([from_agent, from_agent])
        if to_agent:
            conditions.append("(from_agent = ? OR to_agent = ?)")
            params.extend([to_agent, to_agent])
        where = (" WHERE " + " AND ".join(conditions)) if conditions else ""
        with self._db_lock:
            rows = self._db_conn.execute(
                f"SELECT * FROM agent_chat{where} ORDER BY timestamp DESC LIMIT ?",
                params + [limit],
            ).fetchall()
        return [dict(r) for r in rows]

    def get_agent_chat_pairs(self, group_id: str = "") -> list:
        """获取所有有私聊记录的Agent对（去重），返回 [{from_agent, from_name, to_agent, to_name, count, last_time}]"""
        if not self._db_conn:
            return []
        where = " WHERE group_id = ?" if group_id else ""
        params = [group_id] if group_id else []
        with self._db_lock:
            rows = self._db_conn.execute(
                f"""SELECT from_agent, from_name, to_agent, to_name,
                           COUNT(*) as cnt, MAX(timestamp) as last_ts
                FROM agent_chat{where}
                GROUP BY CASE WHEN from_agent < to_agent THEN from_agent ELSE to_agent END,
                         CASE WHEN from_agent < to_agent THEN to_agent ELSE from_agent END
                ORDER BY last_ts DESC""",
                params,
            ).fetchall()
        return [dict(r) for r in rows]

    def clear_agent_chats(self, group_id: str = "") -> int:
        """清空私聊记录，返回删除数量"""
        if not self._db_conn:
            return 0
        with self._db_lock:
            if group_id:
                cur = self._db_conn.execute("DELETE FROM agent_chat WHERE group_id = ?", (group_id,))
            else:
                cur = self._db_conn.execute("DELETE FROM agent_chat")
            self._db_conn.commit()
        return cur.rowcount

    # ==================================================================
    # 群消息统计
    # ==================================================================

    def get_group_stats(self, group_id: str) -> dict:
        """获取群统计信息"""
        group = self._groups.get(group_id)
        if not group:
            return {}

        msg_count = 0
        if self._db_conn:
            with self._db_lock:
                row = self._db_conn.execute(
                    "SELECT COUNT(*) as cnt FROM group_messages WHERE group_id = ?",
                    (group_id,),
                ).fetchone()
                msg_count = row["cnt"] if row else 0

        return {
            "id": group.id,
            "name": group.name,
            "member_count": group.member_count(),
            "message_count": msg_count,
            "created_at": group.created_at,
            "updated_at": group.updated_at,
        }