#!/usr/bin/env python3
"""
飞书群聊消息同步到 MySQL (lark_group_message 表)
- Bot身份拉取群消息（增量）
- 非文本媒体上传到腾讯COS
- 记录写入 MySQL vala_test.lark_group_message
"""
import os
import sys
import json
import subprocess
import logging
import re
import pymysql
from datetime import datetime, timezone, timedelta
from pathlib import Path

# ============ 配置 ============

# 飞书群
CHAT_ID = "oc_fabff7672e62a9ced7b326ee4a286c26"
CHAT_NAME = "内容测试问题反馈"

# Bot凭证
LARK_CLI_CONFIG = "/root/.openclaw/credentials/xiaokui"

# 同步状态
LAST_SYNC_FILE = "/root/.openclaw/workspace-xiaokui/data/last_lark_group_sync_time"
WORK_DIR = "/tmp/lark_group_sync_workdir"

# COS
COS_BASE_PATH = "vala_llm/user_feedback"

# MySQL（从环境变量或默认值）
DB_HOST = os.environ.get("LARK_SYNC_DB_HOST", "bj-cdb-8frbdwju.sql.tencentcdb.com")
DB_PORT = int(os.environ.get("LARK_SYNC_DB_PORT", "25413"))
DB_USER = os.environ.get("LARK_SYNC_DB_USER", "chatbot")
DB_PASS = os.environ.get("LARK_SYNC_DB_PASS", "")
DB_NAME = os.environ.get("LARK_SYNC_DB_NAME", "vala_test")

# 时区
TZ = timezone(timedelta(hours=8))

# ============ 配置结束 ============

# 确保 lark-cli 在 PATH 中
_NODE_BIN = "/root/.nvm/versions/node/v24.14.0/bin"
if _NODE_BIN not in os.environ.get("PATH", ""):
    os.environ["PATH"] = _NODE_BIN + ":" + os.environ.get("PATH", "")

logging.basicConfig(level=logging.INFO, format='[%(asctime)s] %(message)s', datefmt='%Y-%m-%d %H:%M:%S')
log = logging.getLogger(__name__)

# 引入COS上传器
sys.path.insert(0, '/root/.openclaw/workspace-xiaokui/skills/tencent-cos-upload/scripts')
from cos_upload import CosUploader


def get_db_connection():
    """获取MySQL连接"""
    return pymysql.connect(
        host=DB_HOST,
        port=DB_PORT,
        user=DB_USER,
        password=DB_PASS,
        database=DB_NAME,
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=False
    )


def lark_cli(*args, cwd=None):
    """调用lark-cli并返回解析后的JSON"""
    env = os.environ.copy()
    env['LARKSUITE_CLI_CONFIG_DIR'] = LARK_CLI_CONFIG
    cmd = ['lark-cli'] + list(args)
    result = subprocess.run(cmd, capture_output=True, text=True, env=env, cwd=cwd or WORK_DIR)
    if result.returncode != 0 and not result.stdout:
        log.error(f"lark-cli error: {result.stderr}")
        return None
    try:
        return json.loads(result.stdout)
    except json.JSONDecodeError:
        log.error(f"lark-cli JSON parse error: {result.stdout[:200]}")
        return None


def fetch_messages(start_iso: str, end_iso: str) -> list:
    """拉取群消息，支持分页"""
    all_messages = []
    page_token = None

    while True:
        args = [
            'im', '+chat-messages-list',
            '--chat-id', CHAT_ID,
            '--start', start_iso,
            '--end', end_iso,
            '--sort', 'asc',
            '--page-size', '50',
            '--as', 'bot'
        ]
        if page_token:
            args.extend(['--page-token', page_token])

        result = lark_cli(*args)
        if not result or not result.get('ok'):
            err = result.get('error', {}).get('message', 'unknown') if result else 'no response'
            log.error(f"拉取消息失败: {err}")
            break

        messages = result.get('data', {}).get('messages', [])
        all_messages.extend(messages)

        page_token = result.get('data', {}).get('page_token')
        has_more = result.get('data', {}).get('has_more', False)
        if not has_more or not page_token:
            break

    return all_messages


def download_resource(msg_id: str, file_key: str, res_type: str, filename: str) -> str:
    """下载飞书消息中的资源，返回本地路径"""
    result = lark_cli(
        'im', '+messages-resources-download',
        '--message-id', msg_id,
        '--file-key', file_key,
        '--type', res_type,
        '--output', filename,
        '--as', 'bot',
        cwd=WORK_DIR
    )
    local_path = os.path.join(WORK_DIR, filename)
    if result and result.get('ok') and os.path.exists(local_path) and os.path.getsize(local_path) > 0:
        return local_path
    return None


def extract_file_key(msg_type: str, content: str) -> tuple:
    """从消息内容中提取file_key和资源类型"""
    if msg_type == 'image':
        match = re.search(r'img_[a-zA-Z0-9_-]+', content)
        return (match.group(0), 'image') if match else (None, None)
    elif msg_type in ('media', 'audio'):
        match = re.search(r'file_[a-zA-Z0-9_-]+', content)
        return (match.group(0), 'file') if match else (None, None)
    elif msg_type == 'file':
        try:
            c = json.loads(content)
            return (c.get('file_key'), 'file')
        except Exception:
            match = re.search(r'file_[a-zA-Z0-9_-]+', content)
            return (match.group(0), 'file') if match else (None, None)
    return None, None


def get_media_info(msg_type: str, content: str) -> tuple:
    """返回 (cos子目录, 文件扩展名, content_type)"""
    if msg_type == 'image':
        return 'image', '.png', 'image/png'
    elif msg_type == 'media':
        name_match = re.search(r'name="([^"]*)"', content)
        ext = os.path.splitext(name_match.group(1))[1] if name_match else '.mp4'
        return 'video', ext or '.mp4', 'video/mp4'
    elif msg_type == 'audio':
        return 'audio', '.ogg', 'audio/ogg'
    elif msg_type == 'file':
        try:
            c = json.loads(content)
            ext = os.path.splitext(c.get('file_name', ''))[1]
        except Exception:
            ext = ''
        return 'file', ext or '.bin', 'application/octet-stream'
    return 'other', '', 'application/octet-stream'


def process_message(msg: dict, cos_uploader, date_str: str) -> dict:
    """
    处理单条消息，返回字典用于插入数据库，或 None（跳过）
    """
    msg_id = msg.get('message_id', '')
    sender = msg.get('sender', {})
    sender_id = sender.get('id', '')
    sender_name = sender.get('name', '未知')
    create_time_str = msg.get('create_time', '')
    msg_type = msg.get('msg_type', '')
    content_raw = msg.get('content', '')
    deleted = msg.get('deleted', False)

    if deleted or msg_type == 'system':
        return None

    # 解析消息时间
    try:
        # create_time 可能是毫秒时间戳字符串或ISO格式
        if create_time_str.isdigit():
            ts_ms = int(create_time_str)
            msg_dt = datetime.fromtimestamp(ts_ms / 1000, tz=TZ)
        else:
            msg_dt = datetime.fromisoformat(create_time_str)
            ts_ms = int(msg_dt.timestamp() * 1000)
    except Exception:
        msg_dt = datetime.now(TZ)
        ts_ms = int(msg_dt.timestamp() * 1000)

    record = {
        'chat_id': CHAT_ID,
        'chat_name': CHAT_NAME,
        'sender_id': sender_id,
        'sender_name': sender_name,
        'message_id': msg_id,
        'msg_type': msg_type,
        'content': None,
        'media_url': None,
        'msg_time': msg_dt.strftime('%Y-%m-%d %H:%M:%S'),
        'msg_timestamp': ts_ms,
    }

    # 文本消息
    if msg_type in ('text', 'post'):
        text = re.sub(r'<[^>]*>', '', content_raw).strip()
        record['content'] = text
        return record

    # 表情包
    if msg_type == 'sticker':
        record['content'] = '（表情包）'
        return record

    # 媒体消息
    cos_subdir, ext, content_type = get_media_info(msg_type, content_raw)
    file_key, res_type = extract_file_key(msg_type, content_raw)

    if not file_key:
        record['content'] = f'（无法提取资源key）'
        return record

    short_id = msg_id[-12:]
    if msg_type == 'media':
        name_match = re.search(r'name="([^"]*)"', content_raw)
        orig_ext = os.path.splitext(name_match.group(1))[1] if name_match else ext
        filename = f'{short_id}{orig_ext or ext}'
    elif msg_type == 'file':
        try:
            c = json.loads(content_raw)
            orig_ext = os.path.splitext(c.get('file_name', ''))[1]
        except Exception:
            orig_ext = ext
        filename = f'{short_id}{orig_ext or ext}'
    else:
        filename = f'{short_id}{ext}'

    cos_key = f'{COS_BASE_PATH}/{cos_subdir}/{date_str}/{filename}'

    # 下载
    local_path = download_resource(msg_id, file_key, res_type, filename)
    if not local_path:
        log.warning(f"资源下载失败: {msg_id} ({msg_type})")
        record['content'] = '（下载失败）'
        return record

    # 上传COS
    try:
        url = cos_uploader.upload(local_path, cos_key, content_type)
        log.info(f"COS上传成功: {cos_key}")
        record['media_url'] = url
    except Exception as e:
        log.error(f"COS上传失败: {cos_key} - {e}")
        record['content'] = '（上传失败）'
    finally:
        try:
            os.remove(local_path)
        except Exception:
            pass

    return record


def insert_records(conn, records: list):
    """批量插入记录到MySQL，使用 INSERT IGNORE 避免重复"""
    if not records:
        return 0

    sql = """
    INSERT IGNORE INTO lark_group_message
        (chat_id, chat_name, sender_id, sender_name, message_id, msg_type, content, media_url, msg_time, msg_timestamp)
    VALUES
        (%(chat_id)s, %(chat_name)s, %(sender_id)s, %(sender_name)s, %(message_id)s,
         %(msg_type)s, %(content)s, %(media_url)s, %(msg_time)s, %(msg_timestamp)s)
    """
    inserted = 0
    with conn.cursor() as cursor:
        for record in records:
            cursor.execute(sql, record)
            inserted += cursor.rowcount
    conn.commit()
    return inserted


def main():
    os.makedirs(WORK_DIR, exist_ok=True)
    os.makedirs(os.path.dirname(LAST_SYNC_FILE), exist_ok=True)

    # 读取上次同步时间
    if os.path.exists(LAST_SYNC_FILE):
        with open(LAST_SYNC_FILE) as f:
            last_sync_iso = f.read().strip()
    else:
        # 首次运行：从 2026-04-01 开始
        last_sync_iso = "2026-04-01T00:00:00+08:00"

    current_iso = datetime.now(TZ).isoformat()
    log.info(f"开始同步: {last_sync_iso} -> {current_iso}")

    # 拉取消息
    messages = fetch_messages(last_sync_iso, current_iso)
    if not messages:
        log.info("没有新消息，同步结束")
        with open(LAST_SYNC_FILE, 'w') as f:
            f.write(current_iso)
        return

    log.info(f"发现 {len(messages)} 条新消息")

    # 初始化COS
    cos_uploader = CosUploader()
    date_str = datetime.now(TZ).strftime('%Y-%m-%d')

    # 处理消息
    records = []
    for msg in messages:
        record = process_message(msg, cos_uploader, date_str)
        if record:
            records.append(record)

    # 写入MySQL
    if records:
        conn = get_db_connection()
        try:
            inserted = insert_records(conn, records)
            log.info(f"同步完成: 处理 {len(records)} 条, 新增 {inserted} 条 (重复跳过 {len(records) - inserted} 条)")
        finally:
            conn.close()
    else:
        log.info("无有效消息需要写入")

    # 更新同步时间
    with open(LAST_SYNC_FILE, 'w') as f:
        f.write(current_iso)


if __name__ == '__main__':
    main()
