#!/usr/bin/env python3
"""
飞书反馈数据实时导出 Excel 并上传到知识库
- 从 MySQL vala_test.lark_group_message 表导出当天数据
- 生成 Excel 文件
- 通过 Bot 身份上传到飞书知识库指定目录下

定时任务：每 5 分钟执行一次
"""
import os
import sys
import json
import subprocess
import logging
from datetime import datetime, timedelta, timezone
from pathlib import Path

import pymysql
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side

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

# 数据库
MYSQL_HOST = os.environ.get("MYSQL_HOST", "bj-cdb-8frbdwju.sql.tencentcdb.com")
MYSQL_PORT = int(os.environ.get("MYSQL_PORT", "25413"))
MYSQL_USER = os.environ.get("MYSQL_USER", "chatbot")
MYSQL_PASS = os.environ.get("MYSQL_PASS", "")
MYSQL_DB = os.environ.get("MYSQL_DB", "vala_test")

# 飞书知识库
WIKI_SPACE_ID = "7612229802338045122"
WIKI_PARENT_NODE = "FOxgwkVtLiTl3ZkrkIXcT8T2nJg"  # "飞书问题反馈" 节点

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

# 输出目录
OUTPUT_DIR = "/root/.openclaw/workspace-xiaokui/output/daily_feedback"

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

# ============ 日志 ============
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S"
)
logger = logging.getLogger(__name__)


def load_secrets():
    """从 secrets.env 加载密钥"""
    secrets_file = "/root/.openclaw/workspace-xiaokui/secrets.env"
    if os.path.exists(secrets_file):
        with open(secrets_file) as f:
            for line in f:
                line = line.strip()
                if line and not line.startswith("#") and "=" in line:
                    key, val = line.split("=", 1)
                    os.environ.setdefault(key.strip(), val.strip())


def get_db_connection():
    """获取数据库连接"""
    return pymysql.connect(
        host=MYSQL_HOST,
        port=MYSQL_PORT,
        user=MYSQL_USER,
        password=os.environ.get("MYSQL_PASS", MYSQL_PASS),
        database=MYSQL_DB,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor
    )


def query_today_data():
    """查询当天的飞书反馈数据"""
    now = datetime.now(TZ)
    today_start = now.replace(hour=0, minute=0, second=0, microsecond=0)
    today_end = today_start + timedelta(days=1)

    logger.info(f"查询时间范围: {today_start.strftime('%Y-%m-%d %H:%M:%S')} ~ {today_end.strftime('%Y-%m-%d %H:%M:%S')}")

    conn = get_db_connection()
    try:
        with conn.cursor() as cursor:
            sql = """
                SELECT 
                    id, 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, collected_at
                FROM lark_group_message
                WHERE msg_time >= %s AND msg_time < %s
                ORDER BY msg_timestamp ASC
            """
            cursor.execute(sql, (
                today_start.strftime("%Y-%m-%d %H:%M:%S"),
                today_end.strftime("%Y-%m-%d %H:%M:%S")
            ))
            rows = cursor.fetchall()
            logger.info(f"查询到 {len(rows)} 条记录")
            return rows, today_start
    finally:
        conn.close()


def generate_excel(rows, date_obj):
    """生成 Excel 文件"""
    os.makedirs(OUTPUT_DIR, exist_ok=True)
    date_str = date_obj.strftime("%Y-%m-%d")
    filename = f"飞书反馈_{date_str}.xlsx"
    filepath = os.path.join(OUTPUT_DIR, filename)

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "飞书反馈数据"

    # 表头
    headers = [
        "ID", "群聊ID", "群聊名称", "发送者ID", "发送者姓名",
        "消息ID", "父消息ID", "根消息ID", "引用消息ID",
        "消息类型", "内容", "媒体URL", "消息时间", "时间戳", "入库时间"
    ]

    # 表头样式
    header_font = Font(bold=True, color="FFFFFF", size=11)
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
    thin_border = Border(
        left=Side(style="thin"),
        right=Side(style="thin"),
        top=Side(style="thin"),
        bottom=Side(style="thin")
    )

    for col, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align
        cell.border = thin_border

    # 数据行
    for row_idx, row_data in enumerate(rows, 2):
        values = [
            row_data.get("id"),
            row_data.get("chat_id"),
            row_data.get("chat_name"),
            row_data.get("sender_id"),
            row_data.get("sender_name"),
            row_data.get("message_id"),
            row_data.get("parent_id"),
            row_data.get("root_id"),
            row_data.get("quote_message_id"),
            row_data.get("msg_type"),
            row_data.get("content"),
            row_data.get("media_url"),
            str(row_data.get("msg_time", "")) if row_data.get("msg_time") else "",
            row_data.get("msg_timestamp"),
            str(row_data.get("collected_at", "")) if row_data.get("collected_at") else "",
        ]
        for col, val in enumerate(values, 1):
            cell = ws.cell(row=row_idx, column=col, value=val)
            cell.border = thin_border
            cell.alignment = Alignment(vertical="center", wrap_text=True)

    # 调整列宽
    col_widths = [6, 30, 18, 30, 12, 30, 30, 30, 30, 10, 50, 50, 20, 15, 20]
    for i, width in enumerate(col_widths, 1):
        ws.column_dimensions[openpyxl.utils.get_column_letter(i)].width = width

    # 冻结首行
    ws.freeze_panes = "A2"

    wb.save(filepath)
    logger.info(f"Excel 已生成: {filepath}")
    return filepath, filename


def get_bot_tenant_token():
    """获取 Bot 的 tenant_access_token"""
    # 从 lark-cli 配置中读取 app_id 和 app_secret
    config_path = os.path.join(LARK_CLI_CONFIG, "config.json")
    if not os.path.exists(config_path):
        # 尝试其他路径
        config_path = os.path.join(LARK_CLI_CONFIG, "credentials.json")

    # 通过 lark-cli 获取 token
    result = subprocess.run(
        ["lark-cli", "auth", "token", "--as", "bot"],
        capture_output=True, text=True,
        env={**os.environ, "LARKSUITE_CLI_CONFIG_DIR": LARK_CLI_CONFIG}
    )
    if result.returncode == 0:
        token = result.stdout.strip()
        if token:
            return token

    raise RuntimeError(f"无法获取 Bot tenant_access_token: {result.stderr}")


def upload_to_drive(filepath, filename):
    """使用 Bot 身份上传文件到飞书云空间"""
    logger.info(f"正在上传文件到飞书云空间...")

    # lark-cli 要求相对路径，需要 cd 到文件所在目录
    file_dir = os.path.dirname(os.path.abspath(filepath))
    file_basename = os.path.basename(filepath)

    result = subprocess.run(
        [
            "lark-cli", "drive", "+upload",
            "--file", file_basename,
            "--name", filename,
            "--as", "bot"
        ],
        capture_output=True, text=True,
        cwd=file_dir,
        env={**os.environ, "LARKSUITE_CLI_CONFIG_DIR": LARK_CLI_CONFIG}
    )

    if result.returncode != 0:
        raise RuntimeError(f"文件上传失败: {result.stdout}\n{result.stderr}")

    # 解析返回的 file_token
    output = result.stdout.strip()
    logger.info(f"上传响应: {output}")

    try:
        data = json.loads(output)
        file_token = data.get("data", {}).get("file_token") or data.get("file_token")
        if file_token:
            logger.info(f"文件上传成功, file_token: {file_token}")
            return file_token
    except json.JSONDecodeError:
        pass

    # 尝试从文本中提取 file_token
    if "file_token" in output:
        import re
        match = re.search(r'"file_token"\s*:\s*"([^"]+)"', output)
        if match:
            return match.group(1)

    raise RuntimeError(f"无法从上传响应中解析 file_token: {output}")


def move_file_to_wiki(file_token, filename):
    """将云空间文件移入知识库指定节点下"""
    logger.info(f"正在将文件移入知识库...")

    import time

    # 使用 move_docs_to_wiki API
    data = {
        "parent_wiki_token": WIKI_PARENT_NODE,
        "obj_type": "file",
        "obj_token": file_token
    }

    result = subprocess.run(
        [
            "lark-cli", "api", "POST",
            f"/open-apis/wiki/v2/spaces/{WIKI_SPACE_ID}/nodes/move_docs_to_wiki",
            "--data", json.dumps(data),
            "--as", "bot"
        ],
        capture_output=True, text=True,
        env={**os.environ, "LARKSUITE_CLI_CONFIG_DIR": LARK_CLI_CONFIG}
    )

    output = result.stdout.strip()
    logger.info(f"move_docs_to_wiki 响应: {output}")

    try:
        resp = json.loads(output)
        if resp.get("code") == 0:
            task_id = resp.get("data", {}).get("task_id", "")
            logger.info(f"移动任务已提交, task_id: {task_id}")

            # 等待异步任务完成
            time.sleep(3)

            # 查询任务结果
            check_result = subprocess.run(
                [
                    "lark-cli", "api", "GET",
                    f"/open-apis/wiki/v2/tasks/{task_id}",
                    "--params", '{"task_type":"move"}',
                    "--as", "bot"
                ],
                capture_output=True, text=True,
                env={**os.environ, "LARKSUITE_CLI_CONFIG_DIR": LARK_CLI_CONFIG}
            )

            check_output = check_result.stdout.strip()
            check_resp = json.loads(check_output)

            if check_resp.get("code") == 0:
                move_results = check_resp.get("data", {}).get("task", {}).get("move_result", [])
                if move_results and move_results[0].get("status") == 0:
                    node_token = move_results[0].get("node", {}).get("node_token", "")
                    wiki_url = f"https://makee-interactive.feishu.cn/wiki/{node_token}"
                    logger.info(f"文件已成功移入知识库: {wiki_url}")
                    return wiki_url
                else:
                    raise RuntimeError(f"移动任务失败: {move_results}")
            else:
                raise RuntimeError(f"查询移动任务失败: {check_output}")
        else:
            raise RuntimeError(f"move_docs_to_wiki 失败: code={resp.get('code')}, msg={resp.get('msg')}")
    except json.JSONDecodeError:
        raise RuntimeError(f"move_docs_to_wiki 响应解析失败: {output}")


def send_notification(success, message):
    """给李若松发送通知"""
    notify_cmd = [
        "/root/.nvm/versions/node/v24.14.0/bin/openclaw",
        "message", "send",
        "--channel", "feishu",
        "--target", "user:ou_088ee79216826be4a24af44f7268f880",
        "--message", message
    ]
    subprocess.run(notify_cmd, capture_output=True, text=True)


def main():
    """主流程"""
    logger.info("=== 飞书反馈数据每日导出开始 ===")

    # 加载环境变量
    load_secrets()

    try:
        # 1. 查询前一天数据
        rows, date_obj = query_today_data()

        # 2. 判断是否有有效数据
        if not rows:
            logger.info("当天尚无数据，跳过导出")
            return

        # 3. 生成 Excel
        filepath, filename = generate_excel(rows, date_obj)

        # 4. 上传到飞书云空间
        file_token = upload_to_drive(filepath, filename)

        # 5. 将文件移入知识库
        wiki_url = move_file_to_wiki(file_token, filename)

        # 6. 发送成功通知
        date_str = date_obj.strftime("%Y-%m-%d")
        send_notification(True,
            f"📊 飞书反馈实时导出成功\n"
            f"日期：{date_str}\n"
            f"记录数：{len(rows)} 条\n"
            f"文件：{wiki_url}"
        )
        logger.info("=== 导出完成 ===")

    except Exception as e:
        logger.error(f"导出失败: {e}", exc_info=True)
        send_notification(False, f"❌ 飞书反馈日报导出失败\n错误：{str(e)[:200]}")
        sys.exit(1)


if __name__ == "__main__":
    main()
