# -*- coding: utf-8 -*-
import loguru
import requests
import sys
from collections import defaultdict
from datetime import datetime, timedelta
import json
import os
from loguru import logger
from concurrent.futures import ThreadPoolExecutor
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
sys.path.append(BASE_DIR)
from aidso_geo.models.doubao_android_data_process import doubao_mobile_process_original_data
from aidso_geo.models.qianwen_android_data_process import qianwen_android_process_original_data
import aidso_geo.utils.bh_utils as bh_utils
import aidso_geo.utils.tos_utils as tos_utils


class ExcelWriter:

    def __init__(self, file_path):
        # 如果没有 .xlsx 后缀，自动补上
        if not file_path.lower().endswith(".xlsx"):
            file_path += ".xlsx"

        self.file_path = file_path

        # 自动创建父目录
        parent_dir = os.path.dirname(self.file_path)
        if parent_dir and not os.path.exists(parent_dir):
            os.makedirs(parent_dir, exist_ok=True)

        self.workbook = Workbook()
        self.worksheet = self.workbook.active
        self.worksheet.title = "Sheet1"

    def write_batch_rows(self, data_list):
        """
        批量写入数据
        :param data_list: list[dict]
        """
        if len(data_list) == 0:
            return

        # 取第一条数据的 key 作为表头
        headers = list(data_list[0].keys())

        # 写表头
        for col_idx, header in enumerate(headers, start=1):
            self.worksheet.cell(row=1, column=col_idx, value=header)

        # 写数据
        for row_idx, row_data in enumerate(data_list, start=2):
            for col_idx, header in enumerate(headers, start=1):
                self.worksheet.cell(row=row_idx, column=col_idx, value=row_data.get(header, ""))

        # 保存文件
        self.workbook.save(self.file_path)

    def close(self):
        if self.workbook:
            self.workbook.close()


def regroup_by_brand(data_list):
    brand_map = defaultdict(list)

    for item in data_list:
        brand_name = (item.get("brand_name") or "").strip()
        req_id = item.get("req_id")
        created_at = item.get("created_at")

        if not brand_name or not req_id:
            continue

        brand_map[brand_name].append({
            "req_id": req_id,
            "created_at": created_at
        })

    result = []
    for brand_name, items in brand_map.items():
        # 按 created_at 排序
        items = sorted(items, key=lambda x: x.get("created_at") or "")
        result.append({
            "brand_name": brand_name,
            "data": items
        })

    return result


plat_form_map = {
    "DP": "deepseek网页版",
    "DB": "豆包网页版",
    "TXYB": "腾讯元宝",
    "TYQW": "通义千问",
    "KIMI": "kimi",
    "WXYY": "文心一言",
    "BDAI": "百度ai",
    "DYAI": "抖音ai",
    "DOUBA": "豆包手机版",
    "DPA": "deepseek手机版",
    "TXYBA": "腾讯元宝手机版",
    "TYQWA": "通义千问手机版",
}


def to_excel(req_data_list, file_name, output_dir):
    if not req_data_list:
        print(f"{file_name} 没有可导出的 req 数据")
        return

    # 1. 构造 req_id -> created_at 映射
    req_time_map = {}
    req_ids = []

    for item in req_data_list:
        req_id = item.get("req_id")
        created_at = item.get("created_at")
        if not req_id:
            continue
        req_ids.append(req_id)
        req_time_map[req_id] = created_at

    if not req_ids:
        print(f"{file_name} 没有有效 req_id")
        return

    # 去重，避免 SQL 重复
    req_ids = list(dict.fromkeys(req_ids))

    # 2. 拼 SQL IN
    req_id_sql = ",".join([f"'{req_id}'" for req_id in req_ids])

    query_sql = f"select * from geo_commit_task where reqId in ({req_id_sql}) and platform in ('DB','DOUBA','TYQW','TXYB')"

    query_list = bh_utils.query_data(query_sql)

    if not query_list:
        print(f"{file_name} 查询不到 geo_commit_task 数据")
        return

    plat_form_map = {
        "DP": "deepseek网页版",
        "DB": "豆包网页版",
        "TXYB": "腾讯元宝",
        "TYQW": "通义千问",
        "KIMI": "kimi",
        "WXYY": "文心一言",
        "BDAI": "百度ai",
        "DYAI": "抖音ai",
        "DOUBA": "豆包手机版",
        "DPA": "deepseek手机版",
    }

    task_list = []
    for i in query_list:
        task_list.append(
            {
                "prompt": i.get("prompt"),
                "content": tos_utils.get_string_from_tos(f"geo/{i.get('taskId')}/{i.get('platform')}/context.txt"),
                "platform": plat_form_map[i.get("platform")],
                "created_at": req_time_map.get(i.get('reqId')),
            }
        )
    excel_writer = ExcelWriter(output_dir)
    excel_writer.write_batch_rows(task_list)
    excel_writer.close()

    print(f"{file_name} 数据已成功写入Excel文件: {output_dir}")


def export_all_brand_excel(data_list, phone, begin):
    """
    按品牌批量导出 Excel
    """
    grouped_data = regroup_by_brand(data_list)

    if not grouped_data:
        print("没有可分组导出的数据")
        return

    for item in grouped_data:
        brand_name = item.get("brand_name")
        req_data_list = item.get("data", [])
        out_date = f"{begin.replace('-', '')}"
        output_dir = f"/Users/yaowentong/Desktop/original_{out_date}/{phone}/{out_date}/{brand_name}_{out_date}_original"
        to_excel(req_data_list, brand_name, output_dir=output_dir)


def get_req_id(phone, begin, end):
    url = f"https://openapi.aidso.com/openapi/ywt/geoReqList?phone={phone}&begin={begin} 00:00:00&end={end} 23:59:59"

    payload = {}
    headers = {
        'Authorization': ''
    }

    response = requests.request("GET", url, headers=headers, data=payload, timeout=30)
    return response.json().get('data')


def get_url_top40(user_id, brand_library_id, begin, end, p, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/trends"

    if isinstance(p, str):
        p = [p]
    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f'{begin} 00:00:00',
        "end_time": f'{end} 23:59:59',
        "limit": 40,
        "platform_list": p
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Authorization': '',
        'Content-Type': 'application/json'
    }

    response = requests.request("POST", url, headers=headers, data=payload)
    return response.json().get('data')


def export_base_info(phone, begin, end):
    data_list = get_req_id(phone, begin, end)
    export_all_brand_excel(data_list, phone, begin)


def generate_date_list(start_date: str, end_date: str):
    start = datetime.strptime(start_date, "%Y-%m-%d")
    end = datetime.strptime(end_date, "%Y-%m-%d")

    if start > end:
        return []

    result = []
    current = start

    while current <= end:
        result.append(current.strftime("%Y-%m-%d"))
        current += timedelta(days=1)

    return result


def get_refer_rate_heatmap(brand_library_id, user_id, begin_time, end_time, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/platforms"

    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59"
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Content-Type': 'application/json'
    }

    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", {})

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []
    except ValueError as e:
        print(f"返回结果不是合法 JSON: {e}")
        return []
        # print(response.text)


def get_platforms(brand_library_id, user_id, begin_time, end_time, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/platforms"

    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59"
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Content-Type': 'application/json'
    }

    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", {})

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []
    except ValueError as e:
        print(f"返回结果不是合法 JSON: {e}")
        return []
        # print(response.text)


def get_all_word_rank(brand_library_id, user_id, begin_time, end_time, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/all_word_rank"

    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59",
        "last": True
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Content-Type': 'application/json'
    }

    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", [])

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []
    except ValueError as e:
        print(f"返回结果不是合法 JSON: {e}")
        return []


def get_sentiment_overview(brand_library_id, user_id, begin_time, end_time, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/overview"

    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59"
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Content-Type': 'application/json'
    }

    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", {})

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []

    # response = requests.request("POST", url, headers=headers, data=payload)
    #
    # print(response.text)


def get_platforms_sentiment(brand_library_id, user_id, begin_time, end_time, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/platforms/sentiment"

    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59",
        "last": True
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Content-Type': 'application/json'
    }
    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", {})

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []


def get_site_category(brand_library_id, user_id, begin_time, end_time, p, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/statistics/site_category"
    if isinstance(p, str):
        p = [p]
    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59",
        "platform_list": p,
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Content-Type': 'application/json'
    }

    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", [])

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []


def get_quote_mention_word_cloud(brand_library_id, user_id, begin_time, end_time, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/quote_mention_word_cloud"

    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59"
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Content-Type': 'application/json'
    }

    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", {})

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []


def get_quto_statistics(brand_library_id, user_id, begin_time, end_time, question_list=None):
    url = "https://openapi.aidso.com/openapi/ywt/statistics"

    payload_data = {
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59"
    }
    if question_list is not None:
        payload_data["question_list"] = question_list
    payload = json.dumps(payload_data)
    headers = {
        'Content-Type': 'application/json'
    }

    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", {})

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []


def cha_report(phone_list, begin, end):
    for ph in phone_list:
        for de in generate_date_list(begin, end):
            data_list = get_req_id(ph, de, de)
            out_date = f"{de.replace('-', '')}"

            export_base_info(ph, de, de)
            print(f"ph:{ph}---de{de}开始执行")
            result = []
            seen = set()
            if data_list:
                for i in data_list:
                    item = {
                        "user_id": i.get("user_id"),
                        "brand_library_id": i.get("brand_library_id"),
                        "brand_name": i.get("brand_name")
                    }

                    key = (item["user_id"], item["brand_library_id"], item["brand_name"])
                    if key not in seen:
                        seen.add(key)
                        result.append(item)

                for r in result:
                    # platforms = ["DB", "DOUBA", "TYQW", "TXYB"]
                    platforms = ["DB", "DOUBA", "TYQW", "TXYB", "DP", "DPA", "TXYBA", "TYQWA", "BDAI", "WXYY", "KIMI",
                                 "DYAI"]
                    for p in platforms:
                        t_data = get_url_top40(r.get('user_id'), r.get('brand_library_id'), de, de, p)
                        fields = ["site_name", "log_count", "quote_ratio_percent"]
                        result = [{k: item.get(k) for k in fields} for item in t_data]
                        out_path = f"/Users/yaowentong/Desktop/source_{out_date}/{ph}/{out_date}{p}_source/{r.get('brand_name')}_{out_date}_source"
                        writer = ExcelWriter(out_path)
                        writer.write_batch_rows(result)
                        writer.close()


def zhou_report_suggestion(phone, begin, end, brand_name, platform=None):
    req_list = get_req_id(phone, begin, end)

    req_time_map = {}
    req_ids = []
    for item in req_list:
        print(item)
        if item.get('brand_name') == brand_name:
            req_id = item.get("req_id")
            created_at = item.get("created_at")
            req_ids.append(req_id)
            req_time_map[req_id] = created_at
    req_id_sql = ",".join([f"'{req_id}'" for req_id in req_ids])
    if platform:
        platform_list = ",".join([f"'{p}'" for p in platform])
        query_sql = f"select * from geo_commit_task where reqId in ({req_id_sql}) and platform in ({platform_list}) "
    else:
        query_sql = f"select * from geo_commit_task where reqId in ({req_id_sql})"

    query_list = bh_utils.query_data(query_sql)

    result = []
    if query_list:
        for q in query_list:
            r = {
                "prompt": q.get('prompt'),
                "platform": plat_form_map[q.get('platform')],
                "suggestions": tos_utils.get_string_from_tos(
                    f"geo/{q.get('taskId')}/{q.get('platform')}/suggestions.txt"),
                "created_at": req_time_map[q.get('reqId')],
            }
            result.append(r)
        all_file = f"/Users/yaowentong/Desktop/{brand_name}_all.txt"
        with open(all_file, "w", encoding="utf-8") as f:
            for item in result:
                f.write(json.dumps(item, ensure_ascii=False) + "\n\n\n")


def qian_report(phone, begin, end, plan_name, platform=None):
    req_list = get_req_id(phone, begin, end)
    req_ids = []
    req_time_map = {}
    for item in req_list:
        if item.get('plan_name') == plan_name:
            req_id = item.get("req_id")
            created_at = item.get("created_at")
            req_ids.append(req_id)
            req_time_map[req_id] = created_at

    req_id_sql = ",".join([f"'{req_id}'" for req_id in req_ids])
    if platform:
        platform_list = ",".join([f"'{p}'" for p in platform])
        query_sql = f"select * from geo_commit_task where reqId in ({req_id_sql}) and platform in ({platform_list}) "
    else:
        query_sql = f"select * from geo_commit_task where reqId in ({req_id_sql})"
    query_list = bh_utils.query_data(query_sql)

    result = []
    if query_list:
        for q in query_list:
            print( q.get('reqId'))
            r = {
                "thinkingEnabled":q.get('thinkingEnabled'),
                "prompt": q.get('prompt'),
                "reqId": q.get('reqId'),
                "platform": plat_form_map[q.get('platform')],
                "content": tos_utils.get_string_from_tos(f"geo/{q.get('taskId')}/{q.get('platform')}/context.txt"),
                "quote": tos_utils.get_string_from_tos(f"geo/{q.get('taskId')}/{q.get('platform')}/quote.txt"),
                "created_at": q.get('pt')
            }
            result.append(r)
        all_file = f"/Users/yaowentong/Desktop/{plan_name}_all.txt"
        lite_file = f"/Users/yaowentong/Desktop/{plan_name}_lite.txt"
        with open(all_file, "w", encoding="utf-8") as f:
            for item in result:
                f.write(json.dumps(item, ensure_ascii=False) + "\n\n\n")

        remove_keys = {"site_icon", "task_id", "quto_id"}
        data = []
        with open(all_file, "r", encoding="utf-8") as f:
            for line in f:
                line = line.strip()
                if not line:
                    continue

                item = json.loads(line)

                quote = item.get("quote")
                if quote:
                    quote_list = json.loads(quote)  # 把 quote 字符串转成 list

                    for one in quote_list:  # one 是每个 dict
                        if isinstance(one, dict):
                            for k in remove_keys:
                                one.pop(k, None)

                    item["quote"] = json.dumps(quote_list, ensure_ascii=False)
                data.append(item)

        with open(lite_file, "w", encoding="utf-8") as f:
            for item in data:
                f.write(json.dumps(item, ensure_ascii=False) + "\n\n\n")


def zhou_report(phone, begin, end, brand_name):
    req_list = get_req_id(phone, begin, end)
    req_time_map = {}
    req_ids = []
    brand_id = 0
    user_id = 0
    for item in req_list:
        if item.get('brand_name') == brand_name:
            req_id = item.get("req_id")
            user_id = item.get("user_id")
            brand_id = item.get("brand_library_id")
            created_at = item.get("created_at")
            req_ids.append(req_id)
            req_time_map[req_id] = created_at

    result = []
    platform_data = get_platforms(brand_id, user_id, begin, end)
    refer_rate_heatmap = get_refer_rate_heatmap(brand_id, user_id, begin, end)
    print('---')
    print('---')
    print(refer_rate_heatmap)
    print('---')
    print('---')
    geo_refer_rank_overview_vos = platform_data.get('geo_refer_rank_overview_vos')
    brand_overview = platform_data.get('brand_overview')
    for item in geo_refer_rank_overview_vos:
        result.append({
            "平台": plat_form_map.get(item.get("platform"), item.get("platform")),
            "对话次数": item.get("ai_count"),
            "提及率": item.get("refer_rate"),
            "Top1提及率": item.get("top1_refer_rate"),
            "Top3提及率": item.get("top3_refer_rate"),
            "Top10提及率": item.get("top10_refer_rate"),
            "平均提及排名": item.get("avg_rank"),
            "提及好感度": item.get("sentiment_score"),
        })

    brand_overview_result = []
    brand_overview_result.append({
        '对话次数': brand_overview.get('ai_count'),
        '提及对话次数': brand_overview.get('valid_rank_count'),
        '提及率': brand_overview.get('refer_rate'),
        'top1提及率': brand_overview.get('top1_refer_rate'),
        'top3提及率': brand_overview.get('top3_refer_rate'),
        'top10提及率': brand_overview.get('top10_refer_rate'),
        '品牌提及次数': brand_overview.get('mention_count'),
        '平均提及排名': brand_overview.get('avg_rank'),
        '品牌提及好感度': brand_overview.get('sentiment_score'),
        '品牌得分': brand_overview.get('brand_score')
    })
    # 概览数据
    # 分平台数据

    all_word = get_all_word_rank(brand_id, user_id, begin, end)
    all_word_result = []
    for all in all_word:
        all_word_result.append({
            "品牌提及次数": all.get('ref_count'),
            "品牌名称": all.get("word"),
            "品牌得分": all.get("score"),
            "品牌提及率": all.get("rate"),
            "平均提及排名": all.get("avg_rank")
        })
    # 全部品牌词数据

    overview_result = []
    sentiment_overview = get_sentiment_overview(brand_id, user_id, begin, end)

    overview_result.append(
        {
            "提及品牌AI对话次数": sentiment_overview.get('sentiment_count'),
            "品牌提及好感度": sentiment_overview.get('sentiment_score'),
            "正面情感": sentiment_overview.get('positive_count'),
            "中性情感": sentiment_overview.get('neutral_count'),
            "负面情感": sentiment_overview.get('negative_count'),
            "全部引用文章": sentiment_overview.get('article_count'),
            "有品牌内容引用文章": sentiment_overview.get('has_brand_count'),
            # todo 负面引用文章
            "负面引用文章": sentiment_overview.get('negative_count')
        }
    )
    platform_sentiment_result = []
    platform_sentiment = get_platforms_sentiment(brand_id, user_id, begin, end)
    for pla in platform_sentiment:
        platform_sentiment_result.append({
            "平台": plat_form_map.get(pla.get("platform"), pla.get("platform")),
            "正面": round(pla.get('positive_count') / pla.get('sentiment_count'), 2),
            "中性": round(pla.get('neutral_count') / pla.get('sentiment_count'), 2),
            "负面": round(pla.get('negative_count') / pla.get('sentiment_count'), 2),
            "提及品牌次数": pla.get('brand_words_count'),
            "引用来源": pla.get('source_count'),
        })

    platform_list = ["DB", "DP", "TYQW", "TXYB", "BDAI", "DOUBA", "DPA", "DYAI", "KIMI", "WXYY"]
    url_top_40 = get_url_top40(user_id, brand_id, begin, end, platform_list)
    url_top_40_result = []
    for url in url_top_40:
        url_top_40_result.append({
            "网站名称": url.get('site_name'),
            "引用次数": url.get('log_count'),
            "引用占比": url.get('quote_ratio_percent'),
        })

    site_category = get_site_category(brand_id, user_id, begin, end, platform_list)
    site_category_result = []
    for site in site_category:
        site_category_result.append({
            "站点类型": site.get('category'),
            "站点数量": site.get('site_count'),
            "引用文章数": site.get('resource_count'),
            "引用次数": site.get('total_log_count'),
            "引用率": site.get('quote_rate'),
        })

    quote_mention_word_cloud = get_quote_mention_word_cloud(brand_id, user_id, begin, end)
    positive_top = quote_mention_word_cloud.get('positive_top')
    negative_top = quote_mention_word_cloud.get('negative_top')
    positive_top_result = []
    for positive in positive_top:
        positive_top_result.append({
            "关键词": positive.get('word'),
            "出现次数": positive.get('count'),
        })

    negative_top_result = []
    for negative in negative_top:
        negative_top_result.append({
            "关键词": negative.get('word'),
            "出现次数": negative.get('count'),
        })
    quto_statistics_result = []
    quto_statistics = get_quto_statistics(brand_id, user_id, begin, end)
    quto_statistics_result.append({
        "引用文章数": quto_statistics.get('resource_count'),
        "引用次数": quto_statistics.get('total_log_count'),
        "引用网站": quto_statistics.get('site_count'),
    })
    return {
        "brand_overview_result": brand_overview_result,
        "result": result,
        "all_word_result": all_word_result,
        "overview_result": overview_result,
        "platform_sentiment_result": platform_sentiment_result,
        "url_top_40_result": url_top_40_result,
        "site_category_result": site_category_result,
        "positive_top_result": positive_top_result,
        "negative_top_result": negative_top_result,
        "quto_statistics_result": quto_statistics_result
    }


def cha_report_video(videoId):
    url = f"http://172.16.1.37:8874/getVideoDetail2?videoId={videoId}"

    payload = {}
    headers = {}
    response = requests.request("GET", url, headers=headers, data=payload)
    try:
        return response.json().get('data')[0]
    except Exception as e:
        return None


def cha_report_user(uid):
    url = f"http://172.16.1.37:8873/getUserInfoUid?uid={uid}"

    payload = {}
    headers = {}

    response = requests.request("GET", url, headers=headers, data=payload)
    return response.json().get('data')


def get_detail(keyword, monday, sunday):
    url = f"http://172.16.1.37:8873/SuanShuGetRelationWord?keyword={keyword}&monday={monday}&sunday={sunday}"

    payload = {}
    headers = {}

    response = requests.request("GET", url, headers=headers, data=payload)
    try:
        response_json = response.json()

        if response_json.get('code') == 200:
            return response_json.get('data')
    except Exception as e:
        print(e)


def get_portrait(keyword, monday, sunday):
    url = f"http://172.16.1.37:8873/SuanShuGetPortrait?keyword={keyword}&monday={monday}&sunday={sunday}"

    payload = {}
    headers = {}

    response = requests.request("GET", url, headers=headers, data=payload)
    try:
        response_json = response.json()

        if response_json.get('code') == 200:
            return response_json.get('data')
    except Exception as e:
        print(e)


def get_full_weeks(start_time: str, end_time: str):
    start_date = datetime.strptime(start_time, "%Y%m%d").date()
    end_date = datetime.strptime(end_time, "%Y%m%d").date()

    if start_date > end_date:
        return []

    # 找到 >= start_date 的第一个周一
    days_to_monday = (7 - start_date.weekday()) % 7
    first_monday = start_date + timedelta(days=days_to_monday)

    result = []
    current_monday = first_monday

    while True:
        current_sunday = current_monday + timedelta(days=6)

        # 周日必须 <= end_date，才算完整包含
        if current_sunday > end_date:
            break

        result.append((
            current_monday.strftime("%Y%m%d"),
            current_sunday.strftime("%Y%m%d")
        ))

        current_monday += timedelta(days=7)

    return result


def suanshu_export(start, end, keyword_list, file_name):
    # start = "20250602"
    # end = "20250630"
    # keyword_list = ['旅游', '美食', '景点', '亲子游', '周边游', '避暑', '自驾游', '民宿', '海边', '古镇']

    data_list = get_full_weeks(start, end)

    for keyword in keyword_list:
        result = []
        for da in data_list:
            detail_result = get_detail(keyword, da[0], da[1])
            result.append({
                "type": "detail",
                "keyword": keyword,
                "start": da[0],
                "end": da[1],
                "result": detail_result
            })
        portrait_result = get_portrait(keyword, start, end)
        result.append({
            "type": "portrait",
            "keyword": keyword,
            "start": start,
            "end": end,
            "result": portrait_result
        })
        result_file = f"{file_name}/{keyword}.txt"
        with open(result_file, "w", encoding="utf-8") as f:
            for item in result:
                f.write(json.dumps(item, ensure_ascii=False) + "\n\n\n")


def dao_report(phone, begin, end, brand_name, platform=None):
    req_list = get_req_id(phone, begin, end)

    req_time_map = {}
    req_ids = []

    for item in req_list:
        if item.get('brand_name') == brand_name:
            req_id = item.get("req_id")
            created_at = item.get("created_at")
            req_ids.append(req_id)
            req_time_map[req_id] = created_at

    req_id_sql = ",".join([f"'{req_id}'" for req_id in req_ids])

    if platform:
        platform_list = ",".join([f"'{p}'" for p in platform])
        query_sql = f"select * from geo_commit_task where reqId in ({req_id_sql}) and platform in ({platform_list}) "
    else:
        query_sql = f"select * from geo_commit_task where reqId in ({req_id_sql})"
    query_list = bh_utils.query_data(query_sql)

    result = []
    if query_list:
        for q in query_list:
            r = {
                "prompt": q.get('prompt'),
                "platform": plat_form_map[q.get('platform')],
                "search_keyword": tos_utils.get_string_from_tos(
                    f"geo/{q.get('taskId')}/{q.get('platform')}/search_keyword.txt"),
                "created_at": req_time_map[q.get('reqId')],
            }
            result.append(r)
        all_file = f"/Users/yaowentong/Desktop/{brand_name}_dao_report.txt"
        with open(all_file, "w", encoding="utf-8") as f:
            for item in result:
                f.write(json.dumps(item, ensure_ascii=False) + "\n\n\n")


def get_platforms_q(brand_library_id, user_id, begin_time, end_time, question_list):
    url = "https://openapi.aidso.com/openapi/ywt/platforms"

    payload = json.dumps({
        "brand_library_id": brand_library_id,
        "user_id": user_id,
        "begin_time": f"{begin_time} 00:00:00",
        "end_time": f"{end_time} 23:59:59",
        "question_list": question_list
    })
    print(question_list)
    headers = {
        'Content-Type': 'application/json'
    }

    try:
        response = requests.post(
            url,
            headers=headers,
            data=payload,
            timeout=15
        )
        response.raise_for_status()
        result = response.json()

        if result.get("code") == 200:
            return result.get("data", {})

        return []

    except requests.RequestException as e:
        print(f"请求失败: {e}")
        return []
    except ValueError as e:
        print(f"返回结果不是合法 JSON: {e}")
        return []
        # print(response.text)


def txt_to_excel_with_type(txt_file, excel_file, req_type_map):
    """
    从 txt 读取数据，根据 reqId 补充 type 字段，写入 Excel。

    req_type_map 示例：
    {
        "reqId1": "普通任务",
        "reqId2": "搜索任务"
    }
    """

    data_list = read_txt_json_to_list(txt_file)

    wb = Workbook()
    ws = wb.active
    ws.title = "数据"

    headers = [
        "reqId",
        "type",
        "prompt",
        "platform",
        "content",
        "quote",
        "created_at"
    ]

    ws.append(headers)

    # 表头样式
    header_fill = PatternFill("solid", fgColor="D9EAF7")

    for col_idx, header in enumerate(headers, start=1):
        cell = ws.cell(row=1, column=col_idx)
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center", vertical="center")

    # 写入数据
    for item in data_list:
        req_id = item.get("reqId") or item.get("req_id") or ""

        req_type = req_type_map.get(req_id, "")

        ws.append([
            limit_excel_text(req_id),
            limit_excel_text(req_type),
            limit_excel_text(item.get("prompt", "")),
            limit_excel_text(item.get("platform", "")),
            limit_excel_text(item.get("content", "")),
            limit_excel_text(item.get("quote", "")),
            limit_excel_text(item.get("created_at", "")),
        ])

    # 设置列宽
    column_widths = {
        "A": 42,  # reqId
        "B": 18,  # type
        "C": 40,  # prompt
        "D": 18,  # platform
        "E": 80,  # content
        "F": 80,  # quote
        "G": 22,  # created_at
    }

    for col, width in column_widths.items():
        ws.column_dimensions[col].width = width

    # 自动换行 + 顶部对齐
    for row in ws.iter_rows(min_row=2):
        for cell in row:
            cell.alignment = Alignment(
                vertical="top",
                wrap_text=True
            )

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

    # 开启筛选
    ws.auto_filter.ref = ws.dimensions

    # 创建目录
    dir_name = os.path.dirname(excel_file)
    if dir_name:
        os.makedirs(dir_name, exist_ok=True)

    wb.save(excel_file)

    print(f"读取 txt：{txt_file}")
    print(f"写入 Excel：{excel_file}")
    print(f"总条数：{len(data_list)}")

    return excel_file


def read_txt_json_to_list(txt_file):
    """
    读取 txt 文件中的 JSON 数据。

    兼容格式：
    1. 每条 JSON 一行
    2. 每条 JSON 后面多个空行
    """
    result = []

    with open(txt_file, "r", encoding="utf-8") as f:
        content = f.read()

    # 先按行读取，更稳。因为你原来是一条 json + 多个换行。
    for line in content.splitlines():
        line = line.strip()
        if not line:
            continue

        try:
            item = json.loads(line)
            result.append(item)
        except Exception as e:
            print(f"JSON 解析失败，跳过：{e}")
            print(f"异常内容：{line[:300]}")

    return result


def limit_excel_text(text, max_len=32000):
    """
    Excel 单个单元格最大字符数约 32767，这里做保护。
    """
    if text is None:
        return ""
    return str(text)[:max_len]





def txt_to_excel(txt_file, excel_file):
    data_list = []

    # TXT 每个非空行是一条 JSON 数据
    with open(txt_file, "r", encoding="utf-8") as f:
        for line_number, line in enumerate(f, start=1):
            line = line.strip()

            if not line:
                continue

            try:
                item = json.loads(line)

                if isinstance(item, dict):
                    data_list.append(item)
                else:
                    print(f"跳过第 {line_number} 行：内容不是 JSON 对象")

            except json.JSONDecodeError as e:
                print(f"第 {line_number} 行解析失败：{e}")

    if not data_list:
        print("TXT 中没有可写入的数据")
        return

    columns = [
        "thinkingEnabled",
        "prompt",
        "reqId",
        "platform",
        "content",
        "quote",
        "created_at"
    ]

    wb = Workbook()
    ws = wb.active
    ws.title = "数据"

    # 表头样式
    header_font = Font(bold=True, color="FFFFFF")
    header_fill = PatternFill(
        fill_type="solid",
        fgColor="4F81BD"
    )
    header_alignment = Alignment(
        horizontal="center",
        vertical="center"
    )

    # 写入表头
    for column_index, column_name in enumerate(columns, start=1):
        cell = ws.cell(
            row=1,
            column=column_index,
            value=column_name
        )
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_alignment

    # 写入数据
    for row_index, item in enumerate(data_list, start=2):
        for column_index, column_name in enumerate(columns, start=1):
            value = item.get(column_name, "")

            # list/dict 先转换成 JSON 字符串
            if isinstance(value, (list, dict)):
                value = json.dumps(value, ensure_ascii=False)

            value = limit_excel_text(value)

            cell = ws.cell(
                row=row_index,
                column=column_index,
                value=value
            )

            cell.alignment = Alignment(
                vertical="top",
                wrap_text=True
            )

    # 设置列宽
    column_widths = {
        "A": 18,  # thinkingEnabled
        "B": 40,  # prompt
        "C": 40,  # reqId
        "D": 20,  # platform
        "E": 100, # content
        "F": 100, # quote
        "G": 22   # created_at
    }

    for column_letter, width in column_widths.items():
        ws.column_dimensions[column_letter].width = width

    # 冻结表头
    ws.freeze_panes = "A2"

    # 开启筛选
    ws.auto_filter.ref = ws.dimensions

    wb.save(excel_file)

    print(f"转换完成，共写入 {len(data_list)} 条数据")
    print(f"Excel 文件：{excel_file}")


def qian_report2(req_ids,plan_name):
    # req_list = get_req_id(phone, begin, end)
    # req_ids = []
    # req_time_map = {}
    # for item in req_list:
    #     if item.get('plan_name') == plan_name:
    #         req_id = item.get("req_id")
    #         created_at = item.get("created_at")
    #         req_ids.append(req_id)
    #         req_time_map[req_id] = created_at

    req_id_sql = ",".join([f"'{req_id}'" for req_id in req_ids])
    query_sql = f"select * from geo_commit_task where reqId in ({req_id_sql})"
    query_list = bh_utils.query_data(query_sql)

    result = []
    if query_list:
        for q in query_list:
            print( q.get('reqId'))
            r = {
                "thinkingEnabled":q.get('thinkingEnabled'),
                "prompt": q.get('prompt'),
                "reqId": q.get('reqId'),
                "platform": plat_form_map[q.get('platform')],
                "content": tos_utils.get_string_from_tos(f"geo/{q.get('taskId')}/{q.get('platform')}/context.txt"),
                "quote": tos_utils.get_string_from_tos(f"geo/{q.get('taskId')}/{q.get('platform')}/quote.txt"),
                "created_at": q.get('pt')
            }
            result.append(r)
        all_file = f"/Users/yaowentong/Desktop/{plan_name}_all.txt"
        lite_file = f"/Users/yaowentong/Desktop/{plan_name}_lite.txt"
        with open(all_file, "w", encoding="utf-8") as f:
            for item in result:
                f.write(json.dumps(item, ensure_ascii=False) + "\n\n\n")

        remove_keys = {"site_icon", "task_id", "quto_id"}
        data = []
        with open(all_file, "r", encoding="utf-8") as f:
            for line in f:
                line = line.strip()
                if not line:
                    continue

                item = json.loads(line)

                quote = item.get("quote")
                if quote:
                    quote_list = json.loads(quote)  # 把 quote 字符串转成 list

                    for one in quote_list:  # one 是每个 dict
                        if isinstance(one, dict):
                            for k in remove_keys:
                                one.pop(k, None)

                    item["quote"] = json.dumps(quote_list, ensure_ascii=False)
                data.append(item)

        with open(lite_file, "w", encoding="utf-8") as f:
            for item in data:
                f.write(json.dumps(item, ensure_ascii=False) + "\n\n\n")


# =========================
# 使用示例
# =========================
if __name__ == "__main__":
    req_list  = ['e49ee7f8-7e71-47bd-9d10-10f062647697','a2695d7a-e475-40b8-b5ab-e4123f642d76','a1560a90-23a3-468c-a691-3ae66929bcfb','2078e9cd-6788-4987-b4b9-c2d3a04e8a1e','fccdcef7-91d4-41d8-a436-adcb73700130','b7f841ac-2eed-4c3e-8d53-65aa12315a49','47d57e4a-5588-4f40-8650-ef27eaf6da70','9ddc6d73-876d-4fda-b803-38aec72548e6','9dd7df07-3fec-4c76-86ec-07c2f9b456ab','3517e57f-9416-4226-9e11-6b7afb9d35e4','24cb447a-21b8-41ea-82a0-ebcd62e0f41d','f340eb27-ddaa-4e06-af19-5d99e341af0d','63fa1930-67b8-48ca-9093-c5493d281fb8','e860be96-5a76-49d9-b463-664710e07d76','765d7c2c-a790-41d4-ba7d-68bc637d433c','f2ac7a93-ccb6-403c-9f67-9baa1c7ac694','a5650d00-f5cd-4614-96c8-c5470ff8aa3a','9a21345b-c2b8-4971-909c-3cc064f37946','1e294b97-ffd2-4f2b-8a09-19712d27b2d8','15592422-4138-4edc-985f-39f277819108','b35ea5da-0e06-444a-bbb1-b767afa1cb09','435ea67b-4419-4f8f-bcaa-8d2792ebb4a6','145b7a20-1d41-43f9-add0-5a5fd2493ff2','c29be6d6-9616-4ce7-bdcb-4f072c63d57a','d9733d4a-5852-4d50-bf74-6cffb79cd252','7d0c25f9-ecd1-4e42-9d14-8174069d609e','828f5e2b-e9a6-4d71-ab42-71df9e8947e7','01956eeb-fa19-4639-9bfe-131953ad7c14','f4e0296f-607d-44a8-a116-f9f5b5acfe08','29600e8b-c197-4473-96b5-c3d631ab9abe','439fc7b4-11f9-48ad-b6a3-6c08fdb0653f','294ad0f6-817b-4ab5-b896-de9cdffc9e12','4133491f-0d41-4c09-a1f9-3d1fd3142801','167c1b01-bd2f-47ca-829a-66714070b034','38fe3c6b-779c-40b8-9632-578a2e56841e','3a2438c2-cdd5-469e-b05c-3a8c074ed914','8f1368fd-eafe-406f-8a6c-92c543e7ca80','3f287fa3-80e9-45b8-b67d-8b62ab42ba1f','7f777d9d-2ab6-4b16-9c80-273c58f4fcdd','a8172551-d28e-40fe-bb62-fdb9dfb3994c','c68d1a5a-aee2-489a-b721-2c8f0fb21751','9da2b561-89f6-41fc-bf1e-46e1c8c1d73f','a005cba6-3d6f-42d1-a41d-3a49b9c50da6','24a2c763-9c68-49e1-8f6d-07fbd3a03829','2df5e59f-45e8-4d27-b826-e83db7588e5e','4b6bcbaf-e4d9-4404-a3f3-de0880cd6acf','afe77b23-b04b-40b3-bd01-8c313d8ac8f0','bc865bab-6320-44c5-8f7d-b26802209ab7','9f0a9e74-c5c0-4d89-8d0a-9bbbdac2f1c8','04ed4c8a-92d1-4bca-a5fd-3172f46a09a6','0bdc1cf0-f734-430e-bf8c-7075ae27f0f3','6bfc60d4-5796-4cb2-91a9-ba6b85cf424c','feb0d838-303f-4cb2-8f81-a53e76c4fd2f','ce865c09-b29c-4d12-b8eb-d02cce9c9672','9601342d-aef3-4bda-ad18-bdeb0d4c52b8','72742f91-7e4e-4854-be1a-8f5895511451','51c53925-5ff0-4915-b329-1cbad89f5619','da854174-55e3-46b0-865b-1c18f894f908','5a8133f2-31e6-4735-a559-eb0cd0169248','ac6c17cc-e9f8-4c60-8590-b866140bf70a','9c7f3153-cc8a-43be-a719-fa6a320110c7','1bd034ff-dc1b-4a20-b98f-9bd29c68b68b','1331fb78-deae-4171-9cc6-1abcce00b2d4','7ca71831-6828-43cf-9019-b068b5962f33','bd169869-011d-4dcb-a5bc-a20abe473da4','a0be46ac-2d80-4b3b-909d-b194c1caf91e','98b594c1-f10c-4d6c-a4e6-be4a8bdb1964','63dae6aa-4082-419e-a344-4289ba5c9901','dd67abc4-3421-44e7-8b20-f58c7c68f067','5e6b759a-6ad9-4e98-aadb-499422f231da','75c905f3-f1a8-46a7-9d9b-cbd6cd08f42a','dfeb515a-78b2-4aed-a9d7-c3bc1a514f8d','b565eb6a-e66f-45b2-9cfd-ff557817e86a','97373e1a-b118-46a1-8e13-b29ea932806d','910fd1a7-c745-4da1-9a7d-30ef513e15b4','d8e65bc2-5825-4fa3-a120-102f7af0e039','69e735c1-8ce9-41dd-8e10-a3127af2406a','3a7a5550-aabe-41f7-ad33-d8b2a7c28948','2bdc094a-bd0e-4878-8ad1-b4640e68cfac','c5186a50-4c44-402a-861c-ce53e26a306b','6af52a28-7aaa-4352-9d88-90790878602e','0e906b34-a898-4a2b-ace5-564f2dbda170','77f0b68b-9e8b-4a9a-ac17-6a920dce9ee1','1fec9e2b-ddb5-48db-befe-e3a644067e62','fa1e2e0a-7dbf-4cef-a5bf-dbfcbf4f701e','2c8a6e4b-d4ce-4291-808f-abe028404652','484e4381-4c9f-4099-95c6-1b7ef52c6711','21460360-0e13-4a5d-9d1a-f15593c3bab9','3463d72b-9ee5-4191-bf4d-dc1731a59816','f501b913-9b61-4f44-86a1-6971a94c08a1','54bd3f65-bb0d-480c-a1d9-7c337c633c31','bd8cca4e-5bff-424f-8675-3c32ffa15066','d58a7960-2261-4ed1-a437-8748ab2f0307','caea36b7-230c-4930-986f-ada0cc28b2d2','515c9a58-2eb3-4836-ada5-f1e2b220fc26','cfed2f60-f233-4b8a-9f94-774b2785dba9','078c0464-f917-4df6-bf52-583bf9139ce3','84afcfc3-bbb1-4bfd-b3f7-4b795f6beecc','c32e234f-e2c7-41bd-b1cc-c8e7bff9daab','f0700778-3a3b-4950-9486-f2de7b5bca2f','35f1bc35-a98d-42a4-9982-0eecd24bb69b','6a9fb614-9b40-488a-979a-f94bd65e974d','042690b2-62a1-4f2d-b519-eacef8fc9ed7','dad0bf58-ead9-4866-b4b0-ec74236851d6','a923c37e-670f-45c0-b378-08bd033fd09a','3bad0631-cb94-4ca9-aeca-1e1b8066d6a9','4c11caf6-5647-4e81-af6e-c34ee6dd90d5','4ad5002e-2f8e-42e2-8889-385206b7c5d5','06543bb4-fb7b-4bdb-950b-5c303171b413','21d337d8-3b2e-43b9-8c58-b5fb327c0224','5cf52bdb-6bf7-4e2a-b2f5-d83a42109a01','32a2cad5-686d-443c-a824-af4411f6b513','496bb514-7b13-444a-b1b7-e47fa6d07323','679ea973-2df5-4e2a-b9c7-63518ce4d3f7','63d0a699-9ca6-4859-9d3e-aa01cacc62dd','a953a6ff-7812-43a2-af3a-9b3d51fe0bc2','9958488b-6751-4e2a-8d5a-7d0f39490536','e74a9f1a-109d-4249-8cc1-cb9d528aa8ba','fd1cfbd4-611e-4190-a55c-29f468d02da4','d9e55f46-dc9e-4f99-a86a-4ba86a5133c8','278156a2-457e-4d80-8d36-0be1b29e1d5d','00e65cb3-3ecb-40d2-92e9-4a15ee78c4f7','328edc10-ad06-44c4-b00b-7db6d11ccd7a','9ba47b9a-4810-48f4-8ca6-8699109b35a7','f962ee62-7fb9-4f7e-bd1c-e3b50a42e544','5ba63557-6b78-4c3e-8120-d42a2db0cbf9','ae3cfc92-53be-4de3-a457-002bff636960','1c31b295-8339-4fcc-83ae-72c2d6b192cc','a0abb2b8-54f9-4636-8caa-963c89398af5','4f117c0a-309b-4601-9d4b-e600f20e2d90','a8c46fcb-865c-4328-9709-0ca7ad444861','bc882877-4a97-40c5-95e1-5585e3c6c756','ec297cce-9aa5-4471-92fd-3a8b010f0fbe','b339eafd-483f-42c8-8369-6e633d73a7e3','c11d9b31-24ca-4822-9cf0-053477145fe0','cf974b31-ca64-47a5-b6cb-08b23148fc10','1747f918-94fb-43c0-b34f-06be3ec62199','1652fba3-4fc0-4368-8180-fd55705aa6db','5dfafcf0-7add-4483-81f9-190bd30fd8c3','8ec80c71-5169-4be1-bb01-4e36e7ad2cf0']
    qian_report2(req_list,'海尔麦浪冰箱')