#!/usr/bin/env python3
"""
飞书群聊消息完整同步脚本
同步指定时间范围的所有消息到数据库
"""
import os
import sys
import json
import subprocess
import logging
import re
import pymysql
from datetime import datetime, timezone, timedelta

# 配置
CHAT_ID = "oc_fabff7672e62a9ced7b326ee4a286c26"
CHAT_NAME = "内容测试问题反馈"
LARK_CLI_CONFIG = "/root/.openclaw/credentials/xiaokui"
WORK_DIR = "/tmp/feishu_feedback_sync_workdir"

MYSQL_HOST = "bj-cdb-8frbdwju.sql.tencentcdb.com"
MYSQL_PORT = 25413
MYSQL_USER = "chatbot"
MYSQL_PASS = "xhuBx7d@uT2gUVv"
MYSQL_DB = "vala_test"
MYSQL_TABLE = "lark_group_message"

COS_BASE_PATH = "vala_llm/user_feedback"
TZ = timezone(timedelta(hours=8))

# 同步时间范围
START_DATE = "2026-04-10T00:00:00+08:00"
END_DATE = datetime.now(TZ).isoformat()

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

# 获取脚本所在目录的绝对路径
script_dir = os.path.dirname(os.path.abspath(__file__))
cos_upload_path = os.path.join(script_dir, '../skills/tencent-cos-upload/scripts')
sys.path.insert(0, cos_upload_path)
from cos_upload import CosUploader


def lark_cli(*args, cwd=None):
    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_batch(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
        
        data = result.get('data', {})
        messages = data.get('messages', [])
        all_messages.extend(messages)
        
        log.info(f"已拉取 {len(messages)} 条消息，累计 {len(all_messages)} 条")
        
        # 检查是否有下一页
        page_token = data.get('page_token')
        has_more = 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:
    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:
    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 extract_quote_message_id(msg: dict) -> str:
    """从消息中提取引用/回复消息ID
    
    lark-cli 返回格式: reply_to 字段（字符串，直接是被引用的 message_id）
    飞书原始API格式: parent_id 字段
    body.quote 格式: body.quote.message_id（事件推送格式）
    """
    # 优先级1: lark-cli 的 reply_to 字段（字符串类型）
    reply_to = msg.get('reply_to')
    if reply_to and isinstance(reply_to, str):
        return reply_to
    
    # 优先级2: 飞书原始API的 parent_id 字段
    parent_id = msg.get('parent_id')
    if parent_id:
        return parent_id
    
    # 优先级3: body.quote 字段（事件推送格式）
    body = msg.get('body', {})
    if body:
        quote = body.get('quote')
        if quote and isinstance(quote, dict):
            return quote.get('message_id')
    
    return None


def process_message(msg: dict, cos_uploader, date_str: str) -> dict:
    msg_id = msg.get('message_id', '')
    sender_id = msg.get('sender', {}).get('id', '')
    sender_name = msg.get('sender', {}).get('name', '未知')
    create_time = msg.get('create_time', '')
    msg_type = msg.get('msg_type', '')
    content = msg.get('content', '')
    deleted = msg.get('deleted', False)
    parent_id = msg.get('parent_id')
    root_id = msg.get('root_id')

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

    # 提取引用消息ID（兼容 lark-cli reply_to / 原始API parent_id / body.quote）
    quote_message_id = extract_quote_message_id(msg)

    try:
        dt = datetime.fromisoformat(create_time.replace('Z', '+00:00'))
        msg_time = dt.astimezone(TZ).strftime('%Y-%m-%d %H:%M:%S')
        msg_timestamp = int(dt.timestamp() * 1000)
    except Exception:
        msg_time = datetime.now(TZ).strftime('%Y-%m-%d %H:%M:%S')
        msg_timestamp = int(datetime.now(TZ).timestamp() * 1000)

    record = {
        'chat_id': CHAT_ID,
        'chat_name': CHAT_NAME,
        'sender_id': sender_id,
        'sender_name': sender_name,
        'message_id': msg_id,
        'parent_id': parent_id,
        'root_id': root_id,
        'quote_message_id': quote_message_id,
        'msg_type': msg_type,
        'content': None,
        'media_url': None,
        'msg_time': msg_time,
        'msg_timestamp': msg_timestamp
    }

    if msg_type in ('text', 'post'):
        text = re.sub(r'<[^>]*>', '', content).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)
    file_key, res_type = extract_file_key(msg_type, content)

    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)
        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)
            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

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

    return record


def insert_to_mysql(records: list):
    if not records:
        return

    conn = pymysql.connect(
        host=MYSQL_HOST,
        port=MYSQL_PORT,
        user=MYSQL_USER,
        password=MYSQL_PASS,
        database=MYSQL_DB,
        charset='utf8mb4'
    )

    try:
        with conn.cursor() as cursor:
            sql = f"""
                INSERT INTO {MYSQL_TABLE}
                (chat_id, chat_name, sender_id, sender_name, message_id, parent_id, root_id, 
                 quote_message_id, msg_type, content, media_url, msg_time, msg_timestamp)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE
                    sender_name = VALUES(sender_name),
                    parent_id = VALUES(parent_id),
                    root_id = VALUES(root_id),
                    quote_message_id = VALUES(quote_message_id),
                    content = VALUES(content),
                    media_url = VALUES(media_url)
            """
            
            for record in records:
                cursor.execute(sql, (
                    record['chat_id'],
                    record['chat_name'],
                    record['sender_id'],
                    record['sender_name'],
                    record['message_id'],
                    record['parent_id'],
                    record['root_id'],
                    record['quote_message_id'],
                    record['msg_type'],
                    record['content'],
                    record['media_url'],
                    record['msg_time'],
                    record['msg_timestamp']
                ))
            
            conn.commit()
            log.info(f"MySQL写入成功: {len(records)} 条记录")
    except Exception as e:
        log.error(f"MySQL写入失败: {e}")
        conn.rollback()
    finally:
        conn.close()


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

    log.info(f"开始完整同步: {START_DATE} -> {END_DATE}")

    # 拉取所有消息
    messages = fetch_messages_batch(START_DATE, END_DATE)
    if not messages:
        log.info("没有消息，同步结束")
        return

    log.info(f"共拉取 {len(messages)} 条消息，开始处理...")

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

    # 处理消息
    records = []
    for i, msg in enumerate(messages, 1):
        if i % 10 == 0:
            log.info(f"处理进度: {i}/{len(messages)}")
        record = process_message(msg, cos_uploader, date_str)
        if record:
            records.append(record)

    # 写入数据库
    if records:
        insert_to_mysql(records)
        log.info(f"完整同步完成: {len(records)} 条记录")
    else:
        log.info("无有效消息需要写入")


if __name__ == '__main__':
    main()
