#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import os
import sqlite3
from typing import Any, Dict, List, Optional, Tuple

# 固定参数（按需修改）
FIXED_DB = "/mnt/petrelfs/zhengzhijie/mutiAgent4Fraud/data/simu_db/expierement/different_bad_model/deepseek-r1.db"
FIXED_UID1 = 100
FIXED_UID2 = 101
INCLUDE_EVENTS = True

def _table_exists(cur, table_name: str) -> bool:
    cur.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name=?;",
        (table_name,)
    )
    return cur.fetchone() is not None

def _fetch_all(cur, sql: str, params: Tuple[Any, ...]) -> List[Tuple]:
    try:
        cur.execute(sql, params)
        return cur.fetchall()
    except sqlite3.Error:
        return []

def _normalize_ts(ts: Any) -> Tuple[int, Any]:
    # 将各种 ts 归一为可排序键：(class_tag, value)
    # tag: 0=数字时间, 1=字符串可比较, 2=缺失
    if ts is None:
        return (2, 0)
    # 数字
    if isinstance(ts, (int, float)):
        return (0, float(ts))
    # 字符串
    if isinstance(ts, str):
        # 尝试 float 字符串
        try:
            return (0, float(ts))
        except Exception:
            # 再尝试 ISO（尽量保序即可，不严格要求绝对时间）
            # Python 内置 ISO 解析对多格式不完全覆盖，这里直接按字符串排序兜底
            return (1, ts)
    # 其他类型兜底
    return (1, str(ts))

def _clip(s: Optional[str], limit: int = 160) -> str:
    if s is None:
        return ""
    s = s.strip().replace("\r", " ").replace("\n", " ")
    return s if len(s) <= limit else (s[:limit] + "...")

def build_pair_trajectory(
    db_path: str,
    uid1: int,
    uid2: int,
    include_events: bool = True,
) -> Optional[Dict[str, Any]]:
    # 归一化 A/B 身份
    A, B = (uid1, uid2) if uid1 <= uid2 else (uid2, uid1)

    if not os.path.exists(db_path):
        print(f"[错误] 数据库不存在: {db_path}")
        return None

    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    try:
        # 必须存在的表：private_message
        if not _table_exists(cur, "private_message"):
            print("[信息] 该数据库无 private_message 表，跳过。")
            return None

        # 取消息（按 timestamp, rowid 排序；如果 timestamp 异构，后续用 Python 再稳定排序）
        msgs_sql = """
            SELECT rowid, sender_id, receiver_id, content, timestamp
            FROM private_message
            WHERE (sender_id=? AND receiver_id=?) OR (sender_id=? AND receiver_id=?)
        """
        msgs_raw = _fetch_all(cur, msgs_sql, (A, B, B, A))
        msgs = []
        for row in msgs_raw:
            # row: (rowid, sender_id, receiver_id, content, timestamp)
            msgs.append({
                "kind": "msg",
                "rowid": row[0],
                "sender_id": row[1],
                "receiver_id": row[2],
                "content": row[3],
                "ts": row[4],
            })

        if not msgs:
            print(f"[信息] 用户对 ({A}, {B}) 无任何消息记录。")
            return None

        events: List[Dict[str, Any]] = []
        if include_events:
            # 转账
            if _table_exists(cur, "transfer_money"):
                tf_sql = """
                    SELECT rowid, sender_id, receiver_id, amount, reason, timestamp
                    FROM transfer_money
                    WHERE (sender_id IN (?, ?) AND receiver_id IN (?, ?))
                """
                for row in _fetch_all(cur, tf_sql, (A, B, A, B)):
                    s_id, r_id = row[1], row[2]
                    if {s_id, r_id} == {A, B}:
                        events.append({
                            "kind": "transfer",
                            "rowid": row[0],
                            "sender_id": s_id,
                            "receiver_id": r_id,
                            "amount": row[3],
                            "reason": row[4],
                            "ts": row[5],
                        })

            # 点链接
            if _table_exists(cur, "click_link"):
                cl_sql = """
                    SELECT rowid, user_id, sender_id, link_url, timestamp
                    FROM click_link
                    WHERE (user_id IN (?, ?) AND sender_id IN (?, ?))
                """
                for row in _fetch_all(cur, cl_sql, (A, B, A, B)):
                    u_id, s_id = row[1], row[2]
                    if {u_id, s_id} == {A, B}:
                        events.append({
                            "kind": "click",
                            "rowid": row[0],
                            "user_id": u_id,      # 点击者
                            "sender_id": s_id,    # 发送链接者
                            "url": row[3],
                            "ts": row[4],
                        })

            # 提交信息
            if _table_exists(cur, "submit_info"):
                si_sql = """
                    SELECT rowid, user_id, receiver_id, info_type, info_content, timestamp
                    FROM submit_info
                    WHERE (user_id IN (?, ?) AND receiver_id IN (?, ?))
                """
                for row in _fetch_all(cur, si_sql, (A, B, A, B)):
                    u_id, r_id = row[1], row[2]
                    if {u_id, r_id} == {A, B}:
                        events.append({
                            "kind": "submit_info",
                            "rowid": row[0],
                            "user_id": u_id,
                            "receiver_id": r_id,
                            "info_type": row[3],
                            "info_content": row[4],
                            "ts": row[5],
                        })

            # 封禁
            if _table_exists(cur, "banned_private_channel"):
                ban_sql = """
                    SELECT rowid, ban_time
                    FROM banned_private_channel
                    WHERE user_id_1=? AND user_id_2=?
                """
                for row in _fetch_all(cur, ban_sql, (A, B)):
                    events.append({
                        "kind": "ban",
                        "rowid": row[0],
                        "ts": row[1],
                    })

        # 合并并排序（按 ts 归一+rowid 稳定）
        seq = msgs + events
        seq.sort(key=lambda x: (_normalize_ts(x.get("ts")), x.get("rowid", 0)))

        # 渲染为无时间戳的顺序轨迹
        lines: List[str] = []
        idx = 1
        for item in seq:
            k = item["kind"]
            if k == "msg":
                sender = item["sender_id"]
                content = _clip(item.get("content", ""))
                if sender == A:
                    lines.append(f"[{idx}][A->B][MSG] {content}")
                else:
                    lines.append(f"[{idx}][B->A][MSG] {content}")
                idx += 1
            elif k == "transfer":
                sender = item["sender_id"]
                amount = item.get("amount", "")
                reason = _clip(item.get("reason", ""), 160)
                if sender == A:
                    lines.append(f"[{idx}][A][EVENT/TRANSFER] to=B amount={amount} reason=\"{reason}\"")
                else:
                    lines.append(f"[{idx}][B][EVENT/TRANSFER] to=A amount={amount} reason=\"{reason}\"")
                idx += 1
            elif k == "click":
                user_id = item["user_id"]      # 点击者
                sender_id = item["sender_id"]  # 发送链接者
                url = _clip(item.get("url", ""), 200)
                if user_id == A and sender_id == B:
                    lines.append(f"[{idx}][A][EVENT/CLICK_LINK] from=B url={url}")
                elif user_id == B and sender_id == A:
                    lines.append(f"[{idx}][B][EVENT/CLICK_LINK] from=A url={url}")
                idx += 1
            elif k == "submit_info":
                user_id = item["user_id"]
                recv_id = item["receiver_id"]
                info_type = _clip(item.get("info_type", ""), 80)
                info_content = _clip(item.get("info_content", ""), 160)
                if user_id == A and recv_id == B:
                    lines.append(f"[{idx}][A][EVENT/SUBMIT_INFO] to=B type={info_type} info=\"{info_content}\"")
                elif user_id == B and recv_id == A:
                    lines.append(f"[{idx}][B][EVENT/SUBMIT_INFO] to=A type={info_type} info=\"{info_content}\"")
                idx += 1
            elif k == "ban":
                lines.append(f"[{idx}][EVENT/BAN]")
                idx += 1

        depth = sum(1 for x in msgs)  # 仅计消息数
        header = [
            f"[PAIR] A({A}) vs B({B})",
            "[MAP] A=min_id, B=max_id",
            "[TRAJECTORY]",
        ]
        trajectory_text = "\n".join(header) + "\n" + ("\n\n".join(lines) if lines else "")

        return {
            "模型种类": os.path.basename(db_path),
            "对话的深度": depth,
            "对话的内容": trajectory_text,
        }

    finally:
        try:
            cur.close()
        except Exception:
            pass
        try:
            conn.close()
        except Exception:
            pass

def main():
    result = build_pair_trajectory(
        db_path=FIXED_DB,
        uid1=FIXED_UID1,
        uid2=FIXED_UID2,
        include_events=INCLUDE_EVENTS,
    )
    if result is None:
        return

    print(result["对话的内容"])

if __name__ == "__main__":
    main()