# coding: utf-8
from datetime import date, datetime, timedelta
from typing import Literal

from . import clickhouse, get_file_content, get_splitted_sql


def generate_wealth_campaign_report(p_report_date: date) -> dict[str, str]:
    sql_wealth = get_file_content("sql/ads/wealth_report/wealth_campaign_wealth.sql")
    sql_operation = get_file_content(
        "sql/ads/wealth_report/wealth_campaign_operation.sql"
    )

    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    (
        new_open_wealth_num,
        inc_wealth_market_value_hkd,
        all_open_wealth_num,
        all_wealth_market_value_hkd,
    ) = ads_hook.get_first(sql_wealth.format(p_report_date=p_report_date.isoformat()))
    inc_wealth_market_value_hkd = float(inc_wealth_market_value_hkd)
    all_wealth_market_value_hkd = float(all_wealth_market_value_hkd)

    (
        ths_new_reg_num,
        ths_ytd_reg_num,
        ths_new_open_num,
        ths_ytd_open_num,
        ths_new_first_deposit_num,
        ths_ytd_first_deposit_num,
    ) = ads_hook.get_first(
        sql_operation.format(p_report_date=p_report_date.isoformat(), p_category="THS")
    )

    (
        uc_new_reg_num,
        uc_ytd_reg_num,
        uc_new_open_num,
        uc_ytd_open_num,
        uc_new_first_deposit_num,
        uc_ytd_first_deposit_num,
    ) = ads_hook.get_first(
        sql_operation.format(p_report_date=p_report_date.isoformat(), p_category="UC")
    )

    (
        ry_new_reg_num,
        ry_ytd_reg_num,
        ry_new_open_num,
        ry_ytd_open_num,
        ry_new_first_deposit_num,
        ry_ytd_first_deposit_num,
    ) = ads_hook.get_first(
        sql_operation.format(p_report_date=p_report_date.isoformat(), p_category="RY")
    )

    message_title = f"战役播报 {p_report_date.isoformat()}"
    message_content = f"""### {message_title}
> 1. 财富商城：当日新增开户用户{new_open_wealth_num}，当日净流{"入" if inc_wealth_market_value_hkd >= 0 else "出"}AUM {abs(inc_wealth_market_value_hkd) / 10000:.2f}万港币(数据T+1更新)；累计开户{all_open_wealth_num}，累计AUM {all_wealth_market_value_hkd / 100000000:.2f}亿港币
> 2. 同花顺：当日新增注册用户{ths_new_reg_num}，完成开户{ths_new_open_num}例，首次入金用户{ths_new_first_deposit_num}例（新增有效户）；{p_report_date.year}年累计注册{ths_ytd_reg_num}，{p_report_date.year}年累计开户{ths_ytd_open_num}，{p_report_date.year}年累计有效户{ths_ytd_first_deposit_num}
> 3. 自研App：当日新增注册用户{uc_new_reg_num}，完成开户{uc_new_open_num}例，首次入金用户{uc_new_first_deposit_num}例（新增有效户）；{p_report_date.year}年累计注册{uc_ytd_reg_num}，{p_report_date.year}年累计开户{uc_ytd_open_num}，{p_report_date.year}年累计有效户{uc_ytd_first_deposit_num}
> 4. 睿银：当日新增注册用户{ry_new_reg_num}，完成开户{ry_new_open_num}例，首次入金用户{ry_new_first_deposit_num}例（新增有效户）；{p_report_date.year}年累计注册{ry_ytd_reg_num}，{p_report_date.year}年累计开户{ry_ytd_open_num}，{p_report_date.year}年累计有效户{ry_ytd_first_deposit_num}"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_hnw_deposit_revenue(p_report_date: date) -> str:
    # 去年结束日
    pre_year_end_date = date(year=p_report_date.year, month=1, day=1) - timedelta(
        days=1
    )

    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    query_sql = get_file_content(
        "sql/ads/operation_explore/operation_hnw_deposit_revenue.sql"
    ).format(
        p_cur_date=p_report_date.isoformat(),
        p_pre_year_end_date=pre_year_end_date.isoformat(),
    )
    (
        hnw_counts,
        hnw_revenue_hkd,
        hnw_deposit_hkd,
        hnw_aum_hkd,
        hnw_revenue_usd,
        hnw_deposit_usd,
        hnw_aum_usd,
    ) = ads_hook.get_first(query_sql)

    message = f"""截止到{p_report_date},本年度累计新增高客数 {hnw_counts}
    本年度新增高客,在本年度产生的创收 {float(hnw_revenue_hkd):,.2f}(HKD), {float(hnw_revenue_usd):,.2f}(USD)
    本年度新增高客,在本年度入金 {float(hnw_deposit_hkd):,.2f}(HKD), {float(hnw_deposit_usd):,.2f}(USD)
    本年度新增高客,在当前aum {float(hnw_aum_hkd):,.2f}(HKD), {float(hnw_aum_usd):,.2f}(USD)"""

    return message


def generate_invest_consult_dingtalk_stats(p_stats_date: date) -> dict[str, str]:
    sql_query = get_file_content(
        "sql/ads/wealth_report/invest_consult_dingtalk_stats.sql"
    )
    sql_query = sql_query.format(p_stats_date=p_stats_date.isoformat())

    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    (
        total_num,
        total_asset_hkd,
        proprietary_business_num,
        proprietary_business_asset_hkd,
        staff_num,
        staff_asset_hkd,
        client_num,
        client_asset_hkd,
        client_holding_commission_hkd,
        client_trade_commission_hkd,
    ) = ads_hook.get_first(sql_query)

    float_format = "{:,.2f}"
    total_asset_hkd = float_format.format(total_asset_hkd)
    proprietary_business_asset_hkd = float_format.format(proprietary_business_asset_hkd)
    staff_asset_hkd = float_format.format(staff_asset_hkd)
    client_asset_hkd = float_format.format(client_asset_hkd)
    client_holding_commission_hkd = float_format.format(client_holding_commission_hkd)
    client_trade_commission_hkd = float_format.format(client_trade_commission_hkd)

    message_title = (
        f"好易投客户、AUM及相关创收数据 统计日期：{p_stats_date.strftime('%Y.%m.%d')}"
    )
    message_content = f"""### {message_title}
* 总户数：{total_num}
* 总AUM：{total_asset_hkd} HKD
<br><br>
    * 自营户數：{proprietary_business_num}
    * 自营户AUM：{proprietary_business_asset_hkd} HKD
<br><br>
    * 内部员工戶數：{staff_num}
    * 内部员工AUM：{staff_asset_hkd} HKD
<br><br>
    * 客户數：{client_num}
    * 客户AUM：{client_asset_hkd} HKD
<br><br>
* 客户管理费（今年以来）：{client_holding_commission_hkd} HKD
* 客户交易佣金（今年以来）：{client_trade_commission_hkd} HKD
"""
    return {
        "title": message_title,
        "text": message_content,
    }


def generate_ae_open_report(ae_code: str, p_stats_date: date) -> dict[str, str]:
    sql_query = get_file_content("sql/ads/wealth_report/ae_open_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    ae_staff_name, open_num_ytd, open_num_mtd, open_num_last = ads_hook.get_first(
        sql_query.format(
            p_stats_date=p_stats_date.isoformat(),
            p_ae_code=ae_code,
            query_type="tmp_summary",
        )
    )

    last_open_detail = []
    for (
            client_name,
            channel_name,
            account_no,
            correspondence_phone,
    ) in ads_hook.get_records(
        sql_query.format(
            p_stats_date=p_stats_date.isoformat(),
            p_ae_code=ae_code,
            query_type="tmp_last_open",
        )
    ):
        correspondence_phone = correspondence_phone.replace("*", "\\*")
        last_open_detail.append(f"客户姓名：{client_name}    ")
        last_open_detail.append(f"渠道名称：{channel_name}    ")
        last_open_detail.append(f"客户证券号码：{account_no}    ")
        last_open_detail.append(f"客户手机号码：{correspondence_phone}    ")
        last_open_detail.append("<br>")

    if not last_open_detail:
        last_open_detail_content = "无"
    else:
        last_open_detail_content = "\n".join(last_open_detail[:-1])

    message_title = f"{ae_code}开户日报"
    message_content = f"""### {message_title}
#### 【开户数据】
统计日期：{p_stats_date.strftime("%Y.%m.%d")}    
AECODE：{ae_code}    
服务人员：{ae_staff_name}    
今年累计客户：{open_num_ytd}    
本月新增客户：{open_num_mtd}    
今日新增客户：{open_num_last}
<br>
#### 【客户明细】
{last_open_detail_content}
"""
    return {
        "title": message_title,
        "text": message_content,
    }


def generate_public_fund_daily_report(
        fund_type: Literal["no_mmf", "mmf"], curr_end_date: date
) -> dict[str, str]:
    prev_end_date = curr_end_date - timedelta(days=7)
    prev2_end_date = curr_end_date - timedelta(days=14)

    sql_query = get_file_content("sql/ads/wealth_report/public_fund_daily_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    if fund_type == "no_mmf":
        result_statement = "select * from tmp_no_mmf_summary"
        (
            curr_individual_buy_num_acc,
            curr_corporate_buy_num_acc,
            curr_individual_sell_staff_acc,
            curr_corporate_sell_staff_acc,
            curr_individual_buy_num_ytd,
            curr_corporate_buy_num_ytd,
            curr_individual_sell_staff_ytd,
            curr_corporate_sell_staff_ytd,
            curr_gross_amount_ytd,
            curr_sub_red_fee_ytd,
            curr_manage_fee_ytd,
            curr_end_value,
            curr_buy_gross_amount_wtd,
            curr_sell_gross_amount_wtd,
        ) = ads_hook.get_first(
            sql_query.format(
                p_end_date=curr_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        (
            prev_individual_buy_num_acc,
            prev_corporate_buy_num_acc,
            prev_individual_sell_staff_acc,
            prev_corporate_sell_staff_acc,
            prev_individual_buy_num_ytd,
            prev_corporate_buy_num_ytd,
            prev_individual_sell_staff_ytd,
            prev_corporate_sell_staff_ytd,
            prev_gross_amount_ytd,
            prev_sub_red_fee_ytd,
            prev_manage_fee_ytd,
            prev_end_value,
            prev_buy_gross_amount_wtd,
            prev_sell_gross_amount_wtd,
        ) = ads_hook.get_first(
            sql_query.format(
                p_end_date=prev_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        (
            prev2_individual_buy_num_acc,
            prev2_corporate_buy_num_acc,
            prev2_individual_sell_staff_acc,
            prev2_corporate_sell_staff_acc,
            prev2_individual_buy_num_ytd,
            prev2_corporate_buy_num_ytd,
            prev2_individual_sell_staff_ytd,
            prev2_corporate_sell_staff_ytd,
            prev2_gross_amount_ytd,
            prev2_sub_red_fee_ytd,
            prev2_manage_fee_ytd,
            prev2_end_value,
            prev2_buy_gross_amount_wtd,
            prev2_sell_gross_amount_wtd,
        ) = ads_hook.get_first(
            sql_query.format(
                p_end_date=prev2_end_date.isoformat(),
                result_statement=result_statement,
            )
        )

        result_statement = "select * from tmp_no_mmf_trade"
        no_mmf_trade = ads_hook.get_records(
            sql_query.format(
                p_end_date=curr_end_date.isoformat(),
                result_statement=result_statement,
            )
        )

        result_statement = "select * from tmp_no_mmf_trade_by_department order by gross_amount_ytd desc"
        curr_no_mmf_trade_by_department = ads_hook.get_records(
            sql_query.format(
                p_end_date=curr_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        prev_no_mmf_trade_by_department = ads_hook.get_records(
            sql_query.format(
                p_end_date=prev_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        prev_no_mmf_trade_by_department = {
            department_name: gross_amount_ytd
            for department_name, gross_amount_ytd in prev_no_mmf_trade_by_department
        }

        result_statement = (
            "select * from tmp_no_mmf_trade_by_staff order by gross_amount_ytd desc"
        )
        curr_no_mmf_trade_by_staff = ads_hook.get_records(
            sql_query.format(
                p_end_date=curr_end_date.isoformat(),
                result_statement=result_statement,
            )
        )

        result_statement = (
            "select * from tmp_no_mmf_trade_by_security order by gross_amount_ytd desc"
        )
        curr_no_mmf_trade_by_security = ads_hook.get_records(
            sql_query.format(
                p_end_date=curr_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        prev_no_mmf_trade_by_security = ads_hook.get_records(
            sql_query.format(
                p_end_date=prev_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        prev_no_mmf_trade_by_security = {
            security_name: gross_amount_ytd
            for security_name, gross_amount_ytd in prev_no_mmf_trade_by_security
        }

        individual_buy_num_acc_change_rate = (
            f"{(curr_individual_buy_num_acc - prev_individual_buy_num_acc) / prev_individual_buy_num_acc:.2%}"
            if prev_individual_buy_num_acc
            else "-"
        )
        corporate_buy_num_acc_change_rate = (
            f"{(curr_corporate_buy_num_acc - prev_corporate_buy_num_acc) / prev_corporate_buy_num_acc:.2%}"
            if prev_corporate_buy_num_acc
            else "-"
        )
        individual_buy_num_ytd_change_rate = (
            f"{(curr_individual_buy_num_ytd - prev_individual_buy_num_ytd) / prev_individual_buy_num_ytd:.2%}"
            if prev_individual_buy_num_ytd
            else "-"
        )
        corporate_buy_num_ytd_change_rate = (
            f"{(curr_corporate_buy_num_ytd - prev_corporate_buy_num_ytd) / prev_corporate_buy_num_ytd:.2%}"
            if prev_corporate_buy_num_ytd
            else "-"
        )

        individual_sell_staff_acc_change_rate = (
            f"{(curr_individual_sell_staff_acc - prev_individual_sell_staff_acc) / prev_individual_sell_staff_acc:.2%}"
            if prev_individual_sell_staff_acc
            else "-"
        )
        corporate_sell_staff_acc_change_rate = (
            f"{(curr_corporate_sell_staff_acc - prev_corporate_sell_staff_acc) / prev_corporate_sell_staff_acc:.2%}"
            if prev_corporate_sell_staff_acc
            else "-"
        )
        individual_sell_staff_ytd_change_rate = (
            f"{(curr_individual_sell_staff_ytd - prev_individual_sell_staff_ytd) / prev_individual_sell_staff_ytd:.2%}"
            if prev_individual_sell_staff_ytd
            else "-"
        )
        corporate_sell_staff_ytd_change_rate = (
            f"{(curr_corporate_sell_staff_ytd - prev_corporate_sell_staff_ytd) / prev_corporate_sell_staff_ytd:.2%}"
            if prev_corporate_sell_staff_ytd
            else "-"
        )

        gross_amount_ytd_change_rate = (
            f"{(curr_gross_amount_ytd - prev_gross_amount_ytd) / prev_gross_amount_ytd:.2%}"
            if prev_gross_amount_ytd
            else "-"
        )
        sub_red_fee_ytd_change_rate = (
            f"{(curr_sub_red_fee_ytd - prev_sub_red_fee_ytd) / prev_sub_red_fee_ytd:.2%}"
            if prev_sub_red_fee_ytd
            else "-"
        )
        manage_fee_ytd_change_rate = (
            f"{(curr_manage_fee_ytd - prev_manage_fee_ytd) / prev_manage_fee_ytd:.2%}"
            if prev_manage_fee_ytd
            else "-"
        )

        curr_end_value_increase = (
                                      float(curr_end_value) if curr_end_value is not None else 0.0
                                  ) - (float(prev_end_value) if prev_end_value is not None else 0.0)
        prev_end_value_increase = (
                                      float(prev_end_value) if prev_end_value is not None else 0.0
                                  ) - (float(prev2_end_value) if prev2_end_value is not None else 0.0)
        end_value_increase_change_rate = (
            f"{(curr_end_value_increase - prev_end_value_increase) / prev_end_value_increase:.2%}"
            if prev_end_value_increase
            else "-"
        )

        buy_gross_amount_wtd_change_rate = (
            f"{(curr_buy_gross_amount_wtd - prev_buy_gross_amount_wtd) / prev_buy_gross_amount_wtd:.2%}"
            if prev_buy_gross_amount_wtd
            else "-"
        )
        sell_gross_amount_wtd_change_rate = (
            f"{(curr_sell_gross_amount_wtd - prev_sell_gross_amount_wtd) / prev_sell_gross_amount_wtd:.2%}"
            if prev_sell_gross_amount_wtd
            else "-"
        )

        no_mmf_trade_detail = []
        for staff_name, gross_amount_hkd, security_name in no_mmf_trade:
            no_mmf_trade_detail.append(f"ꔷ 销售姓名：{staff_name}    ")
            no_mmf_trade_detail.append(f"ꔷ 销售金额：{gross_amount_hkd:.2f}HKD    ")
            no_mmf_trade_detail.append(f"ꔷ 基金名称：{security_name}    ")
            no_mmf_trade_detail.append("<br>")
        no_mmf_trade_detail = (
            "\n".join(no_mmf_trade_detail) if no_mmf_trade_detail else ""
        )

        top_trade_by_department = []
        for idx, (department_name, gross_amount_ytd) in enumerate(
                curr_no_mmf_trade_by_department, start=1
        ):
            prev_gross_amount_ytd = prev_no_mmf_trade_by_department.get(
                department_name, 0.0
            )
            top_trade_by_department.append(
                f"ꔷ {department_name}：{float(gross_amount_ytd) / 1e4:.2f} 万（{(float(gross_amount_ytd) - float(prev_gross_amount_ytd)) / 1e4:.2f}万）    "
            )
            if idx >= 5:
                break
        top_trade_by_department = (
            "\n".join(top_trade_by_department) if top_trade_by_department else "无    "
        )

        top_trade_by_staff = []
        for idx, (staff_name, gross_amount_ytd) in enumerate(
                curr_no_mmf_trade_by_staff, start=1
        ):
            top_trade_by_staff.append(
                f"ꔷ {staff_name}：{float(gross_amount_ytd) / 1e4:.2f} 万    "
            )
            if idx >= 5:
                break
        top_trade_by_staff = (
            "\n".join(top_trade_by_staff) if top_trade_by_staff else "无    "
        )

        top_trade_by_security = []
        for idx, (security_name, gross_amount_ytd) in enumerate(
                curr_no_mmf_trade_by_security, start=1
        ):
            prev_gross_amount_ytd = prev_no_mmf_trade_by_security.get(
                security_name, 0.0
            )
            top_trade_by_security.append(
                f"ꔷ {security_name}：{float(gross_amount_ytd) / 1e4:.2f} 万（{(float(gross_amount_ytd) - float(prev_gross_amount_ytd)) / 1e4:.2f}万）    "
            )
            if idx >= 5:
                break
        top_trade_by_security = (
            "\n".join(top_trade_by_security) if top_trade_by_security else "无    "
        )

        message_title = "非货基金日报"
        message_content = f"""### {message_title}
统计日期：截止至{curr_end_date.year}年{curr_end_date.month}月{curr_end_date.day}日    
金额单位：港币
<br>
### 整体数据(个人 / 企业)
ꔷ 总认购客户数：{curr_individual_buy_num_acc}（{individual_buy_num_acc_change_rate}） / {curr_corporate_buy_num_acc}（{corporate_buy_num_acc_change_rate}）    
ꔷ 今年认购客户数：{curr_individual_buy_num_ytd}（{individual_buy_num_ytd_change_rate}） / {curr_corporate_buy_num_ytd}（{corporate_buy_num_ytd_change_rate}）    
ꔷ 总开单RM数：{curr_individual_sell_staff_acc}（{individual_sell_staff_acc_change_rate}） / {curr_corporate_sell_staff_acc}（{corporate_sell_staff_acc_change_rate}）    
ꔷ 今年开单RM数：{curr_individual_sell_staff_ytd}（{individual_sell_staff_ytd_change_rate}） / {curr_corporate_sell_staff_ytd}（{corporate_sell_staff_ytd_change_rate}）    
ꔷ 今年销量：{float(curr_gross_amount_ytd) / 1e4:.2f} 万HKD（{gross_amount_ytd_change_rate}）    
ꔷ 今年申赎费：{float(curr_sub_red_fee_ytd) / 1e4:.2f} 万HKD（{sub_red_fee_ytd_change_rate}）    
ꔷ 今年管理费：{float(curr_manage_fee_ytd) / 1e4:.2f} 万HKD（{manage_fee_ytd_change_rate}）    
ꔷ 本周新增AUM：{curr_end_value_increase / 1e4:.2f} 万HKD（{end_value_increase_change_rate}）    
ꔷ 本周新增认购：{float(curr_buy_gross_amount_wtd) / 1e4:.2f} 万HKD（{buy_gross_amount_wtd_change_rate}）    
ꔷ 本周新增赎回：{float(curr_sell_gross_amount_wtd) / 1e4:.2f} 万HKD（{sell_gross_amount_wtd_change_rate}）    
\*\*括号内为同比上周新增
<br>
### 开单明细
ꔷ 今日开单人数：{len(no_mmf_trade)}    
<br>
{no_mmf_trade_detail}
### 今年最佳团队销量TOP5
{top_trade_by_department}
\*\*括号内为同比上周新增AUM
<br>
### 今年最佳个人销量TOP5
{top_trade_by_staff}
<br>
### 今年非货基金销量TOP5
{top_trade_by_security}
\*\*括号内为同比上周新增销量
"""
    else:
        result_statement = "select * from tmp_mmf_summary"
        (
            curr_individual_buy_num_acc,
            curr_corporate_buy_num_acc,
            curr_individual_sell_staff_acc,
            curr_corporate_sell_staff_acc,
            curr_individual_buy_num_ytd,
            curr_corporate_buy_num_ytd,
            curr_individual_sell_staff_ytd,
            curr_corporate_sell_staff_ytd,
            curr_start_value,
            curr_end_value,
            curr_avg_value,
            curr_manage_fee_ytd,
        ) = ads_hook.get_first(
            sql_query.format(
                p_end_date=curr_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        (
            prev_individual_buy_num_acc,
            prev_corporate_buy_num_acc,
            prev_individual_sell_staff_acc,
            prev_corporate_sell_staff_acc,
            prev_individual_buy_num_ytd,
            prev_corporate_buy_num_ytd,
            prev_individual_sell_staff_ytd,
            prev_corporate_sell_staff_ytd,
            prev_start_value,
            prev_end_value,
            prev_avg_value,
            prev_manage_fee_ytd,
        ) = ads_hook.get_first(
            sql_query.format(
                p_end_date=prev_end_date.isoformat(),
                result_statement=result_statement,
            )
        )

        result_statement = (
            "select * from tmp_mmf_value_by_department order by avg_value desc"
        )
        curr_value_by_department = ads_hook.get_records(
            sql_query.format(
                p_end_date=curr_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        prev_value_by_department = ads_hook.get_records(
            sql_query.format(
                p_end_date=prev_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        prev_value_by_department = {
            department_name: avg_value
            for department_name, avg_value in prev_value_by_department
        }

        result_statement = (
            "select * from tmp_mmf_value_by_staff order by avg_value desc"
        )
        curr_value_by_staff = ads_hook.get_records(
            sql_query.format(
                p_end_date=curr_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        prev_value_by_staff = ads_hook.get_records(
            sql_query.format(
                p_end_date=prev_end_date.isoformat(),
                result_statement=result_statement,
            )
        )
        prev_value_by_staff = {
            staff_name: avg_value for staff_name, avg_value in prev_value_by_staff
        }

        individual_buy_num_acc_change_rate = (
            f"{(curr_individual_buy_num_acc - prev_individual_buy_num_acc) / prev_individual_buy_num_acc:.2%}"
            if prev_individual_buy_num_acc
            else "-"
        )
        corporate_buy_num_acc_change_rate = (
            f"{(curr_corporate_buy_num_acc - prev_corporate_buy_num_acc) / prev_corporate_buy_num_acc:.2%}"
            if prev_corporate_buy_num_acc
            else "-"
        )
        individual_buy_num_ytd_change_rate = (
            f"{(curr_individual_buy_num_ytd - prev_individual_buy_num_ytd) / prev_individual_buy_num_ytd:.2%}"
            if prev_individual_buy_num_ytd
            else "-"
        )
        corporate_buy_num_ytd_change_rate = (
            f"{(curr_corporate_buy_num_ytd - prev_corporate_buy_num_ytd) / prev_corporate_buy_num_ytd:.2%}"
            if prev_corporate_buy_num_ytd
            else "-"
        )

        individual_sell_staff_acc_change_rate = (
            f"{(curr_individual_sell_staff_acc - prev_individual_sell_staff_acc) / prev_individual_sell_staff_acc:.2%}"
            if prev_individual_sell_staff_acc
            else "-"
        )
        corporate_sell_staff_acc_change_rate = (
            f"{(curr_corporate_sell_staff_acc - prev_corporate_sell_staff_acc) / prev_corporate_sell_staff_acc:.2%}"
            if prev_corporate_sell_staff_acc
            else "-"
        )
        individual_sell_staff_ytd_change_rate = (
            f"{(curr_individual_sell_staff_ytd - prev_individual_sell_staff_ytd) / prev_individual_sell_staff_ytd:.2%}"
            if prev_individual_sell_staff_ytd
            else "-"
        )
        corporate_sell_staff_ytd_change_rate = (
            f"{(curr_corporate_sell_staff_ytd - prev_corporate_sell_staff_ytd) / prev_corporate_sell_staff_ytd:.2%}"
            if prev_corporate_sell_staff_ytd
            else "-"
        )

        top_value_by_department = []
        for idx, (department_name, avg_value) in enumerate(
                curr_value_by_department, start=1
        ):
            prev_avg_value = prev_value_by_department.get(department_name, 0.0)
            top_value_by_department.append(
                f"ꔷ {department_name}：{float(avg_value) / 1e8:.2f} 亿（{(float(avg_value) - float(prev_avg_value)) / 1e8:.2f}亿）    "
            )
            if idx >= 5:
                break
        top_value_by_department = (
            "\n".join(top_value_by_department) if top_value_by_department else "无    "
        )

        top_value_by_staff = []
        for idx, (staff_name, avg_value) in enumerate(curr_value_by_staff, start=1):
            prev_avg_value = prev_value_by_staff.get(staff_name, 0.0)
            top_value_by_staff.append(
                f"ꔷ {staff_name}：{float(avg_value) / 1e4:.2f} 万（{(float(avg_value) - float(prev_avg_value)) / 1e4:.2f}万）    "
            )
            if idx >= 5:
                break
        top_value_by_staff = (
            "\n".join(top_value_by_staff) if top_value_by_staff else "无    "
        )

        message_title = "货币基金日报"
        message_content = f"""### {message_title}
统计日期：截止至{curr_end_date.year}年{curr_end_date.month}月{curr_end_date.day}日    
金额单位：港币
<br>
### 整体数据(个人 / 企业)
ꔷ 总认购客户数：{curr_individual_buy_num_acc}（{individual_buy_num_acc_change_rate}） / {curr_corporate_buy_num_acc}（{corporate_buy_num_acc_change_rate}）    
ꔷ 今年认购客户数：{curr_individual_buy_num_ytd}（{individual_buy_num_ytd_change_rate}） / {curr_corporate_buy_num_ytd}（{corporate_buy_num_ytd_change_rate}）    
ꔷ 总开单RM数：{curr_individual_sell_staff_acc}（{individual_sell_staff_acc_change_rate}） / {curr_corporate_sell_staff_acc}（{corporate_sell_staff_acc_change_rate}）    
ꔷ 今年开单RM数：{curr_individual_sell_staff_ytd}（{individual_sell_staff_ytd_change_rate}） / {curr_corporate_sell_staff_ytd}（{corporate_sell_staff_ytd_change_rate}）    
ꔷ 去年底AUM：{float(curr_start_value) / 1e8:.2f} 亿    
ꔷ 当前AUM：{float(curr_end_value) / 1e8:.2f} 亿    
ꔷ 今年日均AUM：{float(curr_avg_value) / 1e8:.2f} 亿    
ꔷ 今年管理费：{float(curr_manage_fee_ytd) / 1e4:.2f} 万    
\*\*括号内为同比上周新增
<br>
### 今年日均AUM团队TOP5
{top_value_by_department}
\*\*括号内为同比上周新增AUM
<br>
### 今年日均AUM个人TOP5
{top_value_by_staff}
\*\*括号内为同比上周新增AUM
"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_bond_daily_report(curr_end_date: date) -> dict[str, str]:
    prev_end_date = curr_end_date - timedelta(days=7)

    sql_query = get_file_content("sql/ads/wealth_report/bond_daily_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    result_statement = "select * from tmp_summary"
    (
        curr_total_holding_value,
        curr_total_commission,
        curr_total_trade_amount,
        curr_total_all_trade_client,
        curr_total_non_pi_trade_client,
        curr_total_pi_trade_client,
        curr_individual_trade_staff,
        curr_corporate_trade_staff,
        curr_non_pi_trade_staff,
        curr_pi_trade_staff,
        curr_total_commission_wtd,
        curr_total_trade_amount_wtd,
    ) = ads_hook.get_first(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            result_statement=result_statement,
        )
    )
    (
        prev_total_holding_value,
        prev_total_commission,
        prev_total_trade_amount,
        prev_total_all_trade_client,
        prev_total_non_pi_trade_client,
        prev_total_pi_trade_client,
        prev_individual_trade_staff,
        prev_corporate_trade_staff,
        prev_non_pi_trade_staff,
        prev_pi_trade_staff,
        prev_total_commission_wtd,
        prev_total_trade_amount_wtd,
    ) = ads_hook.get_first(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            result_statement=result_statement,
        )
    )

    result_statement = (
        "select * from tmp_commission_by_department order by commission desc"
    )
    curr_commission_by_department = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            result_statement=result_statement,
        )
    )
    prev_commission_by_department = ads_hook.get_records(
        sql_query.format(
            p_end_date=prev_end_date.isoformat(),
            result_statement=result_statement,
        )
    )
    prev_commission_by_department = {
        department_name: commission
        for department_name, commission in prev_commission_by_department
    }

    result_statement = (
        "select * from tmp_commission_by_staff_wtd order by commission desc"
    )
    curr_commission_by_staff_wtd = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            result_statement=result_statement,
        )
    )

    result_statement = "select * from tmp_commission_by_staff order by commission desc"
    curr_commission_by_staff = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            result_statement=result_statement,
        )
    )

    result_statement = (
        "select * from tmp_holding_client_by_department order by holding_client desc"
    )
    curr_holding_client_by_department = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            result_statement=result_statement,
        )
    )

    result_statement = (
        "select * from tmp_holding_client_by_staff order by holding_client desc"
    )
    curr_holding_client_by_staff = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            result_statement=result_statement,
        )
    )

    individual_trade_staff_change_rate = (
        f"{(curr_individual_trade_staff - prev_individual_trade_staff) / prev_individual_trade_staff:.2%}"
        if prev_individual_trade_staff
        else "-"
    )
    corporate_trade_staff_change_rate = (
        f"{(curr_corporate_trade_staff - prev_corporate_trade_staff) / prev_corporate_trade_staff:.2%}"
        if prev_corporate_trade_staff
        else "-"
    )

    top_commission_by_department = []
    for idx, (department_name, commission) in enumerate(
            curr_commission_by_department, start=1
    ):
        prev_commission = prev_commission_by_department.get(department_name, 0.0)
        top_commission_by_department.append(
            f"ꔷ {department_name}：{float(commission) / 1e4:.2f} 万（{(float(commission) - float(prev_commission)) / 1e4:.2f}万）    "
        )
        if idx >= 5:
            break
    top_commission_by_department = (
        "\n".join(top_commission_by_department)
        if top_commission_by_department
        else "无    "
    )

    commission_by_staff_wtd = []
    for staff_name, commission in curr_commission_by_staff_wtd:
        commission_by_staff_wtd.append(
            f"ꔷ {staff_name}：{float(commission) / 1e4:.2f} 万    "
        )
    commission_by_staff_wtd = (
        "\n".join(commission_by_staff_wtd) if commission_by_staff_wtd else "无    "
    )

    top_commission_by_staff = []
    for idx, (staff_name, commission) in enumerate(curr_commission_by_staff, start=1):
        top_commission_by_staff.append(
            f"ꔷ {staff_name}：{float(commission) / 1e4:.2f} 万    "
        )
        if idx >= 5:
            break
    top_commission_by_staff = (
        "\n".join(top_commission_by_staff) if top_commission_by_staff else "无    "
    )

    top_holding_client_by_department = []
    for idx, (department_name, holding_client) in enumerate(
            curr_holding_client_by_department, start=1
    ):
        top_holding_client_by_department.append(
            f"ꔷ {department_name}：{holding_client}    "
        )
        if idx >= 5:
            break
    top_holding_client_by_department = (
        "\n".join(top_holding_client_by_department)
        if top_holding_client_by_department
        else "无    "
    )

    top_holding_client_by_staff = []
    for idx, (staff_name, holding_client) in enumerate(
            curr_holding_client_by_staff, start=1
    ):
        top_holding_client_by_staff.append(f"ꔷ {staff_name}：{holding_client}    ")
        if idx >= 5:
            break
    top_holding_client_by_staff = (
        "\n".join(top_holding_client_by_staff)
        if top_holding_client_by_staff
        else "无    "
    )

    message_title = "债券交易日报（国债 + PI债券）"
    message_content = f"""### {message_title}
统计日期：截止至{curr_end_date.year}年{curr_end_date.month}月{curr_end_date.day}日    
金额单位：港币
<br>
#### 整体数据(个人 / 企业)
ꔷ 当前债券 AUM：{float(curr_total_holding_value) / 1e8:.2f} 亿    
ꔷ 今年债券交易佣金：{float(curr_total_commission) / 1e4:.2f} 万    
ꔷ 今年债券交易额：{float(curr_total_trade_amount) / 1e4:.2f} 万    
ꔷ 今年债券交易客户数：{curr_total_all_trade_client}（国债 {curr_total_non_pi_trade_client} 个，PI 债券 {curr_total_pi_trade_client} 个）    
ꔷ 总开单债券交易销售人数：{curr_individual_trade_staff}（{individual_trade_staff_change_rate}） / {curr_corporate_trade_staff}（{corporate_trade_staff_change_rate}）（国债 {curr_non_pi_trade_staff} 个，PI 债 {curr_pi_trade_staff} 个）    
ꔷ 本周新增债券交易佣金：{float(curr_total_commission_wtd) / 1e4:.2f} 万    
ꔷ 本周新增债券交易额：{float(curr_total_trade_amount_wtd) / 1e4:.2f} 万
<br>
#### 本周债券下单销售及佣金
{commission_by_staff_wtd}
<br>
#### 今年债券交易佣金团队TOP5
{top_commission_by_department}
<br>
#### 今年债券交易佣金个人TOP5
{top_commission_by_staff}
<br>
#### 当前债券持仓客户数团队TOP5
{top_holding_client_by_department}
<br>
#### 当前债券持仓客户数个人 TOP5
{top_holding_client_by_staff}
"""
    return {
        "title": message_title,
        "text": message_content,
    }


def generate_fosun_group_executive_trade(p_stats_date: date) -> str:
    sql_query = get_file_content("sql/ads/compliance/fosun_group_executive_trade.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    result_statement = (
        "select * from tmp_stock_trade order by trade_date,client_id,account_no,ref_no"
    )
    stock_trade = ads_hook.get_records(
        sql_query.format(
            p_stats_date=p_stats_date.isoformat(),
            result_statement=result_statement,
        )
    )

    if not stock_trade:
        stock_trade_content = "无\n"
    else:
        stock_trade_content = []

        current_account = ""
        current_account_trade = []
        for (
                trade_date,
                ref_no,
                client_id,
                client_name,
                account_no,
                security_code,
                security_name,
                trade_type,
                quantity,
        ) in stock_trade:
            if isinstance(trade_date, datetime):
                trade_date = trade_date.date()
            if current_account != account_no:
                if current_account != "":
                    stock_trade_content.extend(current_account_trade)
                    stock_trade_content.append("\n")

                current_account = account_no
                current_account_trade.clear()

                current_account_trade.append(f"客户名称：{client_name} {account_no}")
                current_account_trade.append(f"成交日期：{trade_date.isoformat()}")
                current_account_trade.append("成交明细：")

            current_account_trade.append(
                f"ꔷ {security_code} {security_name}：{trade_type} {quantity:.2f}"
            )
        if current_account_trade:
            stock_trade_content.extend(current_account_trade)
            stock_trade_content.append("\n")

        stock_trade_content = "\n".join(stock_trade_content)

    result_statement = "select * from tmp_wealth_apply order by apply_date,client_id,account_no,src,ref_no"
    wealth_apply = ads_hook.get_records(
        sql_query.format(
            p_stats_date=p_stats_date.isoformat(),
            result_statement=result_statement,
        )
    )

    if not wealth_apply:
        wealth_apply_content = "无"
    else:
        wealth_apply_content = []

        current_account = ""
        current_account_apply = []
        for (
                apply_date,
                src,
                ref_no,
                client_id,
                client_name,
                account_no,
                biz_type,
                isin_code,
                security_name,
                trade_type,
                currency_code,
                apply_amount,
        ) in wealth_apply:
            if isinstance(apply_date, datetime):
                apply_date = apply_date.date()
            if current_account != account_no:
                if current_account != "":
                    wealth_apply_content.extend(current_account_apply)
                    wealth_apply_content.append("\n")

                current_account = account_no
                current_account_apply.clear()

                current_account_apply.append(f"客户名称：{client_name} {account_no}")
                current_account_apply.append(f"提交日期：{apply_date.isoformat()}")
                current_account_apply.append("提交明细：")

            current_account_apply.append(
                f"ꔷ {biz_type} {isin_code}：{trade_type} {apply_amount:.2f} {currency_code}"
            )
        if current_account_apply:
            wealth_apply_content.extend(current_account_apply)
            wealth_apply_content.append("\n")

        wealth_apply_content = "\n".join(wealth_apply_content)

    message = f"""【股票成交监控】
{stock_trade_content}
【财富产品交易监控】
{wealth_apply_content}"""

    return message



def generate_online_operation_report(
        p_curr_end_date: date
) -> dict[str, str]:
    sql_query_stats = get_file_content(
        "sql/ads/operation_explore/online_operation_report_stats.sql"
    )
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads",execution_timeout=300)

    # 当前周期
    p_curr_ytd_begin_date = date(p_curr_end_date.year, 1, 1)
    (
        curr_ytd_open_num,
        curr_ytd_valid_num_end,
        curr_ytd_asset_end,
        curr_ytd_loss_num,
        curr_ytd_recall_num,
        curr_ytd_active_num,
        curr_ytd_active_num_xcf_rate,
        curr_ytd_active_duration,
        curr_ytd_avg_active_duration,
        curr_ytd_deposit_num,
        curr_ytd_first_deposit_num,
        curr_ytd_first_deposit_valid_num,
        curr_ytd_total_revenue,
        curr_ytd_revenue_hk,
        curr_ytd_revenue_us,
        curr_ytd_revenue_us_option,
        curr_ytd_revenue_interest,
		curr_ytd_revenue_other,
        curr_ytd_broker_arpu,
        curr_ytd_register_num,
        curr_ytd_trade_all_num,
        curr_ytd_trade_us_num,
        curr_ytd_trade_hk_num,
        curr_ytd_trade_us_gross_amount,
        curr_ytd_trade_us_quantity,
        curr_ytd_trade_us_option_num,
        curr_ytd_trade_hk_gross_amount,
        curr_ytd_trade_us_night_num,
        curr_ytd_trade_us_night_gross_amount,
        curr_ytd_trade_us_night_quantity,
        curr_ytd_revenue_us_night,
    ) = ads_hook.get_first(
        sql_query_stats.format(
            p_begin_date=p_curr_ytd_begin_date.isoformat(),
            p_end_date=p_curr_end_date.isoformat(),
            p_is_prev_us_day=0,
        )
    )

    p_curr_mtd_begin_date = date(p_curr_end_date.year, p_curr_end_date.month, 1)
    (
        curr_mtd_open_num,
        curr_mtd_valid_num_end,
        curr_mtd_asset_end,
        curr_mtd_loss_num,
        curr_mtd_recall_num,
        curr_mtd_active_num,
        curr_mtd_active_num_xcf_rate,
        curr_mtd_active_duration,
        curr_mtd_avg_active_duration,
        curr_mtd_deposit_num,
        curr_mtd_first_deposit_num,
        curr_mtd_first_deposit_valid_num,
        curr_mtd_total_revenue,
        curr_mtd_revenue_hk,
        curr_mtd_revenue_us,
        curr_mtd_revenue_us_option,
        curr_mtd_revenue_interest,
		curr_mtd_revenue_other,
        curr_mtd_broker_arpu,
        curr_mtd_register_num,
        curr_mtd_trade_all_num,
        curr_mtd_trade_us_num,
        curr_mtd_trade_hk_num,
        curr_mtd_trade_us_gross_amount,
        curr_mtd_trade_us_quantity,
        curr_mtd_trade_us_option_num,
        curr_mtd_trade_hk_gross_amount,
        curr_mtd_trade_us_night_num,
        curr_mtd_trade_us_night_gross_amount,
        curr_mtd_trade_us_night_quantity,
        curr_mtd_revenue_us_night,
    ) = ads_hook.get_first(
        sql_query_stats.format(
            p_begin_date=p_curr_mtd_begin_date.isoformat(),
            p_end_date=p_curr_end_date.isoformat(),
            p_is_prev_us_day=0,
        )
    )
    p_curr_wtd_begin_date = p_curr_end_date - timedelta(days=p_curr_end_date.weekday())
    (
        curr_wtd_open_num,
        curr_wtd_valid_num_end,
        curr_wtd_asset_end,
        curr_wtd_loss_num,
        curr_wtd_recall_num,
        curr_wtd_active_num,
        curr_wtd_active_num_xcf_rate,
        curr_wtd_active_duration,
        curr_wtd_avg_active_duration,
        curr_wtd_deposit_num,
        curr_wtd_first_deposit_num,
        curr_wtd_first_deposit_valid_num,
        curr_wtd_total_revenue,
        curr_wtd_revenue_hk,
        curr_wtd_revenue_us,
        curr_wtd_revenue_us_option,
        curr_wtd_revenue_interest,
		curr_wtd_revenue_other,
        curr_wtd_broker_arpu,
        curr_wtd_register_num,
        curr_wtd_trade_all_num,
        curr_wtd_trade_us_num,
        curr_wtd_trade_hk_num,
        curr_wtd_trade_us_gross_amount,
        curr_wtd_trade_us_quantity,
        curr_wtd_trade_us_option_num,
        curr_wtd_trade_hk_gross_amount,
        curr_wtd_trade_us_night_num,
        curr_wtd_trade_us_night_gross_amount,
        curr_wtd_trade_us_night_quantity,
        curr_wtd_revenue_us_night,
    ) = ads_hook.get_first(
        sql_query_stats.format(
            p_begin_date=p_curr_wtd_begin_date.isoformat(),
            p_end_date=p_curr_end_date.isoformat(),
            p_is_prev_us_day=0,
        )
    )

    (
        curr_day_open_num,
        curr_day_valid_num_end,
        curr_day_asset_end,
        curr_day_loss_num,
        curr_day_recall_num,
        curr_day_active_num,
        curr_day_active_num_xcf_rate,
        curr_day_active_duration,
        curr_day_avg_active_duration,
        curr_day_deposit_num,
        curr_day_first_deposit_num,
        curr_day_first_deposit_valid_num,
        curr_day_total_revenue,
        curr_day_revenue_hk,
        curr_day_revenue_us,
        curr_day_revenue_us_option,
        curr_day_revenue_interest,
		curr_day_revenue_other,
        curr_day_broker_arpu,
        curr_day_register_num,
        curr_day_trade_all_num,
        curr_day_trade_us_num,
        curr_day_trade_hk_num,
        curr_day_trade_us_gross_amount,
        curr_day_trade_us_quantity,
        curr_day_trade_us_option_num,
        curr_day_trade_hk_gross_amount,
        curr_day_trade_us_night_num,
        curr_day_trade_us_night_gross_amount,
        curr_day_trade_us_night_quantity,
        curr_day_revenue_us_night,
    ) = ads_hook.get_first(
        sql_query_stats.format(
            p_begin_date=p_curr_end_date.isoformat(),
            p_end_date=p_curr_end_date.isoformat(),
            p_is_prev_us_day=0,
        )
    )

    # 前年度
    p_prev_ytd_end_date = date(
        p_curr_end_date.year - 1,
        p_curr_end_date.month,
        p_curr_end_date.day - 1 if p_curr_end_date.day == 29 else p_curr_end_date.day,
    )
    p_prev_ytd_begin_date = date(p_prev_ytd_end_date.year, 1, 1)


    (
        prev_ytd_open_num,
        prev_ytd_valid_num_end,
        prev_ytd_asset_end,
        prev_ytd_loss_num,
        prev_ytd_recall_num,
        prev_ytd_active_num,
        prev_ytd_active_num_xcf_rate,
        prev_ytd_active_duration,
        prev_ytd_avg_active_duration,
        prev_ytd_deposit_num,
        prev_ytd_first_deposit_num,
        prev_ytd_first_deposit_valid_num,
        prev_ytd_total_revenue,
        prev_ytd_revenue_hk,
        prev_ytd_revenue_us,
        prev_ytd_revenue_us_option,
        prev_ytd_revenue_interest,
		prev_ytd_revenue_other,
        prev_ytd_broker_arpu,
        prev_ytd_register_num,
        prev_ytd_trade_all_num,
        prev_ytd_trade_us_num,
        prev_ytd_trade_hk_num,
        prev_ytd_trade_us_gross_amount,
        prev_ytd_trade_us_quantity,
        prev_ytd_trade_us_option_num,
        prev_ytd_trade_hk_gross_amount,
        prev_ytd_trade_us_night_num,
        prev_ytd_trade_us_night_gross_amount,
        prev_ytd_trade_us_night_quantity,
        prev_ytd_revenue_us_night,
    ) = ads_hook.get_first(
        sql_query_stats.format(
            p_begin_date=p_prev_ytd_begin_date.isoformat(),
            p_end_date=p_prev_ytd_end_date.isoformat(),
            p_is_prev_us_day=0,
        )
    )



    # 前月度
    p_prev_mtd_end_date = p_curr_mtd_begin_date - timedelta(days=1)
    if p_prev_mtd_end_date.day > p_curr_end_date.day:
        p_prev_mtd_end_date = date(
            p_prev_mtd_end_date.year, p_prev_mtd_end_date.month, p_curr_end_date.day
        )
    p_prev_mtd_begin_date = date(p_prev_mtd_end_date.year, p_prev_mtd_end_date.month, 1)

    (
        prev_mtd_open_num,
        prev_mtd_valid_num_end,
        prev_mtd_asset_end,
        prev_mtd_loss_num,
        prev_mtd_recall_num,
        prev_mtd_active_num,
        prev_mtd_active_num_xcf_rate,
        prev_mtd_active_duration,
        prev_mtd_avg_active_duration,
        prev_mtd_deposit_num,
        prev_mtd_first_deposit_num,
        prev_mtd_first_deposit_valid_num,
        prev_mtd_total_revenue,
        prev_mtd_revenue_hk,
        prev_mtd_revenue_us,
        prev_mtd_revenue_us_option,
        prev_mtd_revenue_interest,
		prev_mtd_revenue_other,
        prev_mtd_broker_arpu,
        prev_mtd_register_num,
        prev_mtd_trade_all_num,
        prev_mtd_trade_us_num,
        prev_mtd_trade_hk_num,
        prev_mtd_trade_us_gross_amount,
        prev_mtd_trade_us_quantity,
        prev_mtd_trade_us_option_num,
        prev_mtd_trade_hk_gross_amount,
        prev_mtd_trade_us_night_num,
        prev_mtd_trade_us_night_gross_amount,
        prev_mtd_trade_us_night_quantity,
        prev_mtd_revenue_us_night,
    ) = ads_hook.get_first(
        sql_query_stats.format(
            p_begin_date=p_prev_mtd_begin_date.isoformat(),
            p_end_date=p_prev_mtd_end_date.isoformat(),
            p_is_prev_us_day=0,
        )
    )

    # 前周度
    p_prev_wtd_end_date = p_curr_end_date - timedelta(days=7)
    p_prev_wtd_begin_date = p_curr_wtd_begin_date - timedelta(days=7)


    (
        prev_wtd_open_num,
        prev_wtd_valid_num_end,
        prev_wtd_asset_end,
        prev_wtd_loss_num,
        prev_wtd_recall_num,
        prev_wtd_active_num,
        prev_wtd_active_num_xcf_rate,
        prev_wtd_active_duration,
        prev_wtd_avg_active_duration,
        prev_wtd_deposit_num,
        prev_wtd_first_deposit_num,
        prev_wtd_first_deposit_valid_num,
        prev_wtd_total_revenue,
        prev_wtd_revenue_hk,
        prev_wtd_revenue_us,
        prev_wtd_revenue_us_option,
        prev_wtd_revenue_interest,
		prev_wtd_revenue_other,
        prev_wtd_broker_arpu,
        prev_wtd_register_num,
        prev_wtd_trade_all_num,
        prev_wtd_trade_us_num,
        prev_wtd_trade_hk_num,
        prev_wtd_trade_us_gross_amount,
        prev_wtd_trade_us_quantity,
        prev_wtd_trade_us_option_num,
        prev_wtd_trade_hk_gross_amount,
        prev_wtd_trade_us_night_num,
        prev_wtd_trade_us_night_gross_amount,
        prev_wtd_trade_us_night_quantity,
        prev_wtd_revenue_us_night,
    ) = ads_hook.get_first(
        sql_query_stats.format(
            p_begin_date=p_prev_wtd_begin_date.isoformat(),
            p_end_date=p_prev_wtd_end_date.isoformat(),
            p_is_prev_us_day=0,
        )
    )

    # 前日
    p_prev_day_date = p_curr_end_date - timedelta(days=1)

    (
        prev_day_open_num,
        prev_day_valid_num_end,
        prev_day_asset_end,
        prev_day_loss_num,
        prev_day_recall_num,
        prev_day_active_num,
        prev_day_active_num_xcf_rate,
        prev_day_active_duration,
        prev_day_avg_active_duration,
        prev_day_deposit_num,
        prev_day_first_deposit_num,
        prev_day_first_deposit_valid_num,
        prev_day_total_revenue,
        prev_day_revenue_hk,
        prev_day_revenue_us,
        prev_day_revenue_us_option,
        prev_day_revenue_interest,
		prev_day_revenue_other,
        prev_day_broker_arpu,
        prev_day_register_num,
        prev_day_trade_all_num,
        prev_day_trade_us_num,
        prev_day_trade_hk_num,
        prev_day_trade_us_gross_amount,
        prev_day_trade_us_quantity,
        prev_day_trade_us_option_num,
        prev_day_trade_hk_gross_amount,
        prev_day_trade_us_night_num,
        prev_day_trade_us_night_gross_amount,
        prev_day_trade_us_night_quantity,
        prev_day_revenue_us_night,
    ) = ads_hook.get_first(
        sql_query_stats.format(
            p_begin_date=p_prev_day_date.isoformat(),
            p_end_date=p_prev_day_date.isoformat(),
            p_is_prev_us_day=1,
        )
    )

    # 钉钉消息
    def format_change_rate(curr_value, prev_value):
        return f"{(curr_value - prev_value) / prev_value:.2%}" if prev_value else "-"

    def format_duration(seconds):
        return (
            f"{int(seconds) // 60:d}分{int(seconds) % 60:d}秒"
            if seconds >= 60
            else f"{seconds:,.2f}秒"
        )

    def format_amount(amount):
        """
        统一格式化金额显示
        千以内：直接展示具体数字
        千万以内：以万为单位
        过亿：以亿为单位
        支持负数
        """
        abs_amount = abs(amount)
        sign = "-" if amount < 0 else ""
        
        if abs_amount < 10000:
            return f"{sign}{abs_amount:,.0f}"
        elif abs_amount < 100000000:  # 亿以内
            return f"{sign}{abs_amount / 10000:,.2f}万"
        else:  # 过亿
            return f"{sign}{abs_amount / 100000000:,.2f}亿"
    

    def format_total_ratio(part_value, total_value):
        """
        格式化比率显示
        如果总数为0，返回"-"
        否则返回百分比格式
        """
        return f"{part_value / total_value:.2%}" if total_value else "-"

    # 计算各时间段的交易人数占比
    curr_ytd_trade_us_num_rate = format_total_ratio(curr_ytd_trade_us_num, curr_ytd_trade_all_num)
    curr_ytd_trade_hk_num_rate = format_total_ratio(curr_ytd_trade_hk_num, curr_ytd_trade_all_num)
    curr_mtd_trade_us_num_rate = format_total_ratio(curr_mtd_trade_us_num, curr_mtd_trade_all_num)
    curr_mtd_trade_hk_num_rate = format_total_ratio(curr_mtd_trade_hk_num, curr_mtd_trade_all_num)
    curr_wtd_trade_us_num_rate = format_total_ratio(curr_wtd_trade_us_num, curr_wtd_trade_all_num)
    curr_wtd_trade_hk_num_rate = format_total_ratio(curr_wtd_trade_hk_num, curr_wtd_trade_all_num)
    curr_day_trade_us_num_rate = format_total_ratio(curr_day_trade_us_num, curr_day_trade_all_num)
    curr_day_trade_hk_num_rate = format_total_ratio(curr_day_trade_hk_num, curr_day_trade_all_num)

    message_title = "互财数据播报"
    message_content = f"""## {message_title}
统计日期：截止至{p_curr_end_date.year}年{p_curr_end_date.month}月{p_curr_end_date.day}日    
金额单位：港币    
<br>
### 整体数据
ꔷ 累计有效户：{format_amount(curr_ytd_valid_num_end)}（{format_change_rate(curr_ytd_valid_num_end, prev_ytd_valid_num_end)}）    
ꔷ AUM：{format_amount(curr_ytd_asset_end)}（{format_change_rate(curr_ytd_asset_end, prev_ytd_asset_end)}）    
<br>
### 一、年度数据（YTD）
ꔷ 总活跃：{format_amount(curr_ytd_active_num)}（{format_change_rate(curr_ytd_active_num, prev_ytd_active_num)}）    
ꔷ 其中使用星财富APP比例：{curr_ytd_active_num_xcf_rate:.2%}    
ꔷ 注册人数：{format_amount(curr_ytd_register_num)}（{format_change_rate(curr_ytd_register_num, prev_ytd_register_num)}）    
ꔷ 开户人数：{format_amount(curr_ytd_open_num)}（{format_change_rate(curr_ytd_open_num, prev_ytd_open_num)}）    
ꔷ 新增首入金：{format_amount(curr_ytd_first_deposit_num)}（{format_change_rate(curr_ytd_first_deposit_num, prev_ytd_first_deposit_num)}）    
ꔷ 入金人数：{format_amount(curr_ytd_deposit_num)}（{format_change_rate(curr_ytd_deposit_num, prev_ytd_deposit_num)}）    
ꔷ 交易人数：{format_amount(curr_ytd_trade_all_num)}（{format_change_rate(curr_ytd_trade_all_num, prev_ytd_trade_all_num)}），夜盘：{format_amount(curr_ytd_trade_us_night_num)} （{format_change_rate(curr_ytd_trade_us_night_num, prev_ytd_trade_us_night_num)}）     
ꔷ 美股交易人数及占比：{format_amount(curr_ytd_trade_us_num)}（{curr_ytd_trade_us_num_rate}）    
ꔷ 港股交易人数及占比：{format_amount(curr_ytd_trade_hk_num)}（{curr_ytd_trade_hk_num_rate}）    
ꔷ 新增有效户：{curr_ytd_first_deposit_valid_num}（{format_change_rate(curr_ytd_first_deposit_valid_num, prev_ytd_first_deposit_valid_num)}）    
ꔷ 新增流失户：{format_amount(curr_ytd_loss_num)}（{format_change_rate(curr_ytd_loss_num, prev_ytd_loss_num)}）    
ꔷ 累计创收：{format_amount(curr_ytd_total_revenue)}（{format_change_rate(curr_ytd_total_revenue, prev_ytd_total_revenue)}）    
ꔷ 港股交易额：{format_amount(curr_ytd_trade_hk_gross_amount)}（{format_change_rate(curr_ytd_trade_hk_gross_amount, prev_ytd_trade_hk_gross_amount)}）
ꔷ 港股创收：{format_amount(curr_ytd_revenue_hk)}（{format_change_rate(curr_ytd_revenue_hk, prev_ytd_revenue_hk)}）
ꔷ 美股交易额：{format_amount(curr_ytd_trade_us_gross_amount)}（{format_change_rate(curr_ytd_trade_us_gross_amount, prev_ytd_trade_us_gross_amount)}），夜盘：{format_amount(curr_ytd_trade_us_night_gross_amount)}（{format_change_rate(curr_ytd_trade_us_night_gross_amount, prev_ytd_trade_us_night_gross_amount)}）
ꔷ 美股成交数：{format_amount(curr_ytd_trade_us_quantity)}（{format_change_rate(curr_ytd_trade_us_quantity, prev_ytd_trade_us_quantity)}），夜盘：{format_amount(curr_ytd_trade_us_night_quantity)}（ {format_change_rate(curr_ytd_trade_us_night_quantity, prev_ytd_trade_us_night_quantity)}）
ꔷ 美股创收：{format_amount(curr_ytd_revenue_us)}（{format_change_rate(curr_ytd_revenue_us, prev_ytd_revenue_us)}），夜盘：{format_amount(curr_ytd_revenue_us_night)} （{format_change_rate(curr_ytd_revenue_us_night, prev_ytd_revenue_us_night)}）
ꔷ 期权交易张数：{format_amount(curr_ytd_trade_us_option_num)}（{format_change_rate(curr_ytd_trade_us_option_num, prev_ytd_trade_us_option_num)}）
ꔷ 期权创收：{format_amount(curr_ytd_revenue_us_option)}（{format_change_rate(curr_ytd_revenue_us_option, prev_ytd_revenue_us_option)}）
ꔷ 融资利息创收：{format_amount(curr_ytd_revenue_interest)}（{format_change_rate(curr_ytd_revenue_interest, prev_ytd_revenue_interest)}）
ꔷ 其他财富创收：{format_amount(curr_ytd_revenue_other)}（{format_change_rate(curr_ytd_revenue_other, prev_ytd_revenue_other)}）
ꔷ 召回用户：{format_amount(curr_ytd_recall_num)}（{format_change_rate(curr_ytd_recall_num, prev_ytd_recall_num)}）    
ꔷ 用户人均日在线时长：{format_duration(curr_ytd_avg_active_duration)}（{format_change_rate(curr_ytd_avg_active_duration, prev_ytd_avg_active_duration)}）     
\*\*括号内为同比去年新增    
<br>
### 二、月度数据（MTD）
ꔷ 总活跃：{format_amount(curr_mtd_active_num)}（{format_change_rate(curr_mtd_active_num, prev_mtd_active_num)}）    
ꔷ 其中使用星财富APP比例：{curr_mtd_active_num_xcf_rate:.2%}    
ꔷ 注册人数：{format_amount(curr_mtd_register_num)}（{format_change_rate(curr_mtd_register_num, prev_mtd_register_num)}）    
ꔷ 开户人数：{format_amount(curr_mtd_open_num)}（{format_change_rate(curr_mtd_open_num, prev_mtd_open_num)}）    
ꔷ 新增首入金：{format_amount(curr_mtd_first_deposit_num)}（{format_change_rate(curr_mtd_first_deposit_num, prev_mtd_first_deposit_num)}）    
ꔷ 入金人数：{format_amount(curr_mtd_deposit_num)}（{format_change_rate(curr_mtd_deposit_num, prev_mtd_deposit_num)}）    
ꔷ 交易人数：{format_amount(curr_mtd_trade_all_num)}（{format_change_rate(curr_mtd_trade_all_num, prev_mtd_trade_all_num)}），夜盘：{format_amount(curr_mtd_trade_us_night_num)} （{format_change_rate(curr_mtd_trade_us_night_num, prev_mtd_trade_us_night_num)}）     
ꔷ 美股交易人数及占比：{format_amount(curr_mtd_trade_us_num)}（{curr_mtd_trade_us_num_rate}）    
ꔷ 港股交易人数及占比：{format_amount(curr_mtd_trade_hk_num)}（{curr_mtd_trade_hk_num_rate}）    
ꔷ 新增有效户：{curr_mtd_first_deposit_valid_num}（{format_change_rate(curr_mtd_first_deposit_valid_num, prev_mtd_first_deposit_valid_num)}）    
ꔷ 新增流失户：{format_amount(curr_mtd_loss_num)}（{format_change_rate(curr_mtd_loss_num, prev_mtd_loss_num)}）    
ꔷ 累计创收：{format_amount(curr_mtd_total_revenue)}（{format_change_rate(curr_mtd_total_revenue, prev_mtd_total_revenue)}）    
ꔷ 港股交易额：{format_amount(curr_mtd_trade_hk_gross_amount)}（{format_change_rate(curr_mtd_trade_hk_gross_amount, prev_mtd_trade_hk_gross_amount)}）
ꔷ 港股创收：{format_amount(curr_mtd_revenue_hk)}（{format_change_rate(curr_mtd_revenue_hk, prev_mtd_revenue_hk)}）
ꔷ 美股交易额：{format_amount(curr_mtd_trade_us_gross_amount)}（{format_change_rate(curr_mtd_trade_us_gross_amount, prev_mtd_trade_us_gross_amount)}），夜盘：{format_amount(curr_mtd_trade_us_night_gross_amount)}（{format_change_rate(curr_mtd_trade_us_night_gross_amount, prev_mtd_trade_us_night_gross_amount)}）
ꔷ 美股成交数：{format_amount(curr_mtd_trade_us_quantity)}（{format_change_rate(curr_mtd_trade_us_quantity, prev_mtd_trade_us_quantity)}），夜盘：{format_amount(curr_mtd_trade_us_night_quantity)}（{format_change_rate(curr_mtd_trade_us_night_quantity, prev_mtd_trade_us_night_quantity)}）
ꔷ 美股创收：{format_amount(curr_mtd_revenue_us)}（{format_change_rate(curr_mtd_revenue_us, prev_mtd_revenue_us)}），夜盘：{format_amount(curr_mtd_revenue_us_night)} （{format_change_rate(curr_mtd_revenue_us_night, prev_mtd_revenue_us_night)}）
ꔷ 期权交易张数：{format_amount(curr_mtd_trade_us_option_num)}（{format_change_rate(curr_mtd_trade_us_option_num, prev_mtd_trade_us_option_num)}）
ꔷ 期权创收：{format_amount(curr_mtd_revenue_us_option)}（{format_change_rate(curr_mtd_revenue_us_option, prev_mtd_revenue_us_option)}）
ꔷ 融资利息创收：{format_amount(curr_mtd_revenue_interest)}（{format_change_rate(curr_mtd_revenue_interest, prev_mtd_revenue_interest)}）
ꔷ 其他财富创收：{format_amount(curr_mtd_revenue_other)}（{format_change_rate(curr_mtd_revenue_other, prev_mtd_revenue_other)}）
ꔷ 召回用户：{format_amount(curr_mtd_recall_num)}（{format_change_rate(curr_mtd_recall_num, prev_mtd_recall_num)}）    
ꔷ 用户人均日在线时长：{format_duration(curr_mtd_avg_active_duration)}（{format_change_rate(curr_mtd_avg_active_duration, prev_mtd_avg_active_duration)}）        
\*\*括号内为环比上月新增    
<br>
### 三、本周数据（WTD）
ꔷ 总活跃：{format_amount(curr_wtd_active_num)}（{format_change_rate(curr_wtd_active_num, prev_wtd_active_num)}）    
ꔷ 其中使用星财富APP比例：{curr_wtd_active_num_xcf_rate:.2%}    
ꔷ 注册人数：{format_amount(curr_wtd_register_num)}（{format_change_rate(curr_wtd_register_num, prev_wtd_register_num)}）    
ꔷ 开户人数：{format_amount(curr_wtd_open_num)}（{format_change_rate(curr_wtd_open_num, prev_wtd_open_num)}）    
ꔷ 新增首入金：{format_amount(curr_wtd_first_deposit_num)}（{format_change_rate(curr_wtd_first_deposit_num, prev_wtd_first_deposit_num)}）    
ꔷ 入金人数：{format_amount(curr_wtd_deposit_num)}（{format_change_rate(curr_wtd_deposit_num, prev_wtd_deposit_num)}）    
ꔷ 交易人数：{format_amount(curr_wtd_trade_all_num)}（{format_change_rate(curr_wtd_trade_all_num, prev_wtd_trade_all_num)}），夜盘：{format_amount(curr_wtd_trade_us_night_num)} （{format_change_rate(curr_wtd_trade_us_night_num, prev_wtd_trade_us_night_num)}）     
ꔷ 美股交易人数及占比：{format_amount(curr_wtd_trade_us_num)}（{curr_wtd_trade_us_num_rate}）    
ꔷ 港股交易人数及占比：{format_amount(curr_wtd_trade_hk_num)}（{curr_wtd_trade_hk_num_rate}）    
ꔷ 新增有效户：{curr_wtd_first_deposit_valid_num}（{format_change_rate(curr_wtd_first_deposit_valid_num, prev_wtd_first_deposit_valid_num)}）    
ꔷ 新增流失户：{format_amount(curr_wtd_loss_num)}（{format_change_rate(curr_wtd_loss_num, prev_wtd_loss_num)}）    
ꔷ 累计创收：{format_amount(curr_wtd_total_revenue)}（{format_change_rate(curr_wtd_total_revenue, prev_wtd_total_revenue)}）    
ꔷ 港股交易额：{format_amount(curr_wtd_trade_hk_gross_amount)}（{format_change_rate(curr_wtd_trade_hk_gross_amount, prev_wtd_trade_hk_gross_amount)}）
ꔷ 港股创收：{format_amount(curr_wtd_revenue_hk)}（{format_change_rate(curr_wtd_revenue_hk, prev_wtd_revenue_hk)}）
ꔷ 美股交易额：{format_amount(curr_wtd_trade_us_gross_amount)}（{format_change_rate(curr_wtd_trade_us_gross_amount, prev_wtd_trade_us_gross_amount)}），夜盘：{format_amount(curr_wtd_trade_us_night_gross_amount)}（{format_change_rate(curr_wtd_trade_us_night_gross_amount, prev_wtd_trade_us_night_gross_amount)}）
ꔷ 美股成交数：{format_amount(curr_wtd_trade_us_quantity)}（{format_change_rate(curr_wtd_trade_us_quantity, prev_wtd_trade_us_quantity)}），夜盘：{format_amount(curr_wtd_trade_us_night_quantity)}（ {format_change_rate(curr_wtd_trade_us_night_quantity, prev_wtd_trade_us_night_quantity)}）
ꔷ 美股创收：{format_amount(curr_wtd_revenue_us)}（{format_change_rate(curr_wtd_revenue_us, prev_wtd_revenue_us)}），夜盘：{format_amount(curr_wtd_revenue_us_night)} （{format_change_rate(curr_wtd_revenue_us_night, prev_wtd_revenue_us_night)}）
ꔷ 期权交易张数：{format_amount(curr_wtd_trade_us_option_num)}（{format_change_rate(curr_wtd_trade_us_option_num, prev_wtd_trade_us_option_num)}）
ꔷ 期权创收：{format_amount(curr_wtd_revenue_us_option)}（{format_change_rate(curr_wtd_revenue_us_option, prev_wtd_revenue_us_option)}）
ꔷ 融资利息创收：{format_amount(curr_wtd_revenue_interest)}（{format_change_rate(curr_wtd_revenue_interest, prev_wtd_revenue_interest)}）
ꔷ 其他财富创收：{format_amount(curr_wtd_revenue_other)}（{format_change_rate(curr_wtd_revenue_other, prev_wtd_revenue_other)}）
ꔷ 召回用户：{format_amount(curr_wtd_recall_num)}（{format_change_rate(curr_wtd_recall_num, prev_wtd_recall_num)}）    
ꔷ 用户人均日在线时长：{format_duration(curr_wtd_avg_active_duration)}（{format_change_rate(curr_wtd_avg_active_duration, prev_wtd_avg_active_duration)}）     
\*\*括号内为环比上周新增    
<br>
### 四、今日数据
ꔷ 总活跃：{format_amount(curr_day_active_num)}（{format_change_rate(curr_day_active_num, prev_day_active_num)}）    
ꔷ 其中使用星财富APP比例：{curr_day_active_num_xcf_rate:.2%}    
ꔷ 注册人数：{format_amount(curr_day_register_num)}（{format_change_rate(curr_day_register_num, prev_day_register_num)}）    
ꔷ 开户人数：{format_amount(curr_day_open_num)}（{format_change_rate(curr_day_open_num, prev_day_open_num)}）    
ꔷ 新增首入金：{format_amount(curr_day_first_deposit_num)}（{format_change_rate(curr_day_first_deposit_num, prev_day_first_deposit_num)}）    
ꔷ 入金人数：{format_amount(curr_day_deposit_num)}（{format_change_rate(curr_day_deposit_num, prev_day_deposit_num)}）    
ꔷ 交易人数：{format_amount(curr_day_trade_all_num)}（{format_change_rate(curr_day_trade_all_num, prev_day_trade_all_num)}），夜盘：{format_amount(curr_day_trade_us_night_num)} （{format_change_rate(curr_day_trade_us_night_num, prev_day_trade_us_night_num)}）     
ꔷ 美股交易人数及占比：{format_amount(curr_day_trade_us_num)}（{curr_day_trade_us_num_rate}）    
ꔷ 港股交易人数及占比：{format_amount(curr_day_trade_hk_num)}（{curr_day_trade_hk_num_rate}）    
ꔷ 新增有效户：{curr_day_first_deposit_valid_num}（{format_change_rate(curr_day_first_deposit_valid_num, prev_day_first_deposit_valid_num)}）    
ꔷ 新增流失户：{format_amount(curr_day_loss_num)}（{format_change_rate(curr_day_loss_num, prev_day_loss_num)}）    
ꔷ 累计创收：{format_amount(curr_day_total_revenue)}（{format_change_rate(curr_day_total_revenue, prev_day_total_revenue)}）    
ꔷ 港股交易额：{format_amount(curr_day_trade_hk_gross_amount)}（{format_change_rate(curr_day_trade_hk_gross_amount, prev_day_trade_hk_gross_amount)}）
ꔷ 港股创收：{format_amount(curr_day_revenue_hk)}（{format_change_rate(curr_day_revenue_hk, prev_day_revenue_hk)}）
ꔷ 美股交易额：{format_amount(curr_day_trade_us_gross_amount)}（{format_change_rate(curr_day_trade_us_gross_amount, prev_day_trade_us_gross_amount)}），夜盘：{format_amount(curr_day_trade_us_night_gross_amount)}（{format_change_rate(curr_day_trade_us_night_gross_amount, prev_day_trade_us_night_gross_amount)}）
ꔷ 美股成交数：{format_amount(curr_day_trade_us_quantity)}（{format_change_rate(curr_day_trade_us_quantity, prev_day_trade_us_quantity)}），夜盘：{format_amount(curr_day_trade_us_night_quantity)}（ {format_change_rate(curr_day_trade_us_night_quantity, prev_day_trade_us_night_quantity)}）
ꔷ 美股创收：{format_amount(curr_day_revenue_us)}（{format_change_rate(curr_day_revenue_us, prev_day_revenue_us)}），夜盘：{format_amount(curr_day_revenue_us_night)} （{format_change_rate(curr_day_revenue_us_night, prev_day_revenue_us_night)}）
ꔷ 期权交易张数：{format_amount(curr_day_trade_us_option_num)}（{format_change_rate(curr_day_trade_us_option_num, prev_day_trade_us_option_num)}）
ꔷ 期权创收：{format_amount(curr_day_revenue_us_option)}（{format_change_rate(curr_day_revenue_us_option, prev_day_revenue_us_option)}）
ꔷ 融资利息创收：{format_amount(curr_day_revenue_interest)}（{format_change_rate(curr_day_revenue_interest, prev_day_revenue_interest)}）
ꔷ 其他财富创收：{format_amount(curr_day_revenue_other)}（{format_change_rate(curr_day_revenue_other, prev_day_revenue_other)}）
ꔷ 召回用户：{format_amount(curr_day_recall_num)}（{format_change_rate(curr_day_recall_num, prev_day_recall_num)}）    
ꔷ 用户人均日在线时长：{format_duration(curr_day_avg_active_duration)}（{format_change_rate(curr_day_avg_active_duration, prev_day_avg_active_duration)}）  
\*\*括号内为环比昨天新增"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_esop_ding_report(p_end_date: date) -> str:
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")
    sql_query = get_file_content("sql/ads/esop/esop_ding_report.sql")

    (
        num_individual,
        num_corporation,
        commission_individual_ytd,
        commission_corporation_ytd,
        revenue_individual_ytd,
        revenue_corporation_ytd,
        commission_individual_mtd,
        commission_corporation_mtd,
        revenue_individual_mtd,
        revenue_corporation_mtd,
        commission_individual_day,
        commission_corporation_day,
        revenue_individual_day,
        revenue_corporation_day,
        asset_individual,
        asset_corporation,
        trade_times_individual_mtd,
        trade_times_corporation_mtd,
        trade_amount_individual_mtd,
        trade_amount_corporation_mtd,
        trade_times_individual_day,
        trade_times_corporation_day,
        trade_amount_individual_day,
        trade_amount_corporation_day,
    ) = ads_hook.get_first(sql_query.format(p_end_date=p_end_date.isoformat()))

    message = f"""ESOP 團隊
统计日期：截止至{p_end_date.year}年{p_end_date.month}月{p_end_date.day}日
金额单位：港币

整体数据(个人 / 企业)
当前累計淨收入: {revenue_individual_ytd:,.2f} / {revenue_corporation_ytd:,.2f}
当前 AUM：{asset_individual:,.2f} / {asset_corporation:,.2f}
当前交易佣金：{commission_individual_ytd:,.2f} / {commission_corporation_ytd:,.2f}
当前客戶總數: {num_individual:,d} / {num_corporation:,d}

當月累計交易数据(个人 / 企业)
當月累計交易單數: {trade_times_individual_mtd:,d} / {trade_times_corporation_mtd:,d}
當月累計交易成交總金額: {trade_amount_individual_mtd:,.2f} / {trade_amount_corporation_mtd:,.2f}
當月累計佣金收入: {commission_individual_mtd:,.2f} / {commission_corporation_mtd:,.2f}
當月累計淨收入: {revenue_individual_mtd:,.2f} / {revenue_corporation_mtd:,.2f}

每日交易数据(个人 / 企业) [日期: {p_end_date.year}年{p_end_date.month}月{p_end_date.day}日(昨天)]
昨日交易單數: {trade_times_individual_day:,d} / {trade_times_corporation_day:,d}
昨日交易成交總金額: {trade_amount_individual_day:,.2f} / {trade_amount_corporation_day:,.2f}
昨日佣金收入: {commission_individual_day:,.2f} / {commission_corporation_day:,.2f}
昨日淨收入: {revenue_individual_day:,.2f} / {revenue_corporation_day:,.2f}"""

    return message


def generate_bond_h009k_daily_report(p_end_date: date) -> dict[str, str]:
    sql_query = get_file_content("sql/ads/wealth_report/bond_h009k_daily_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    (
        total_client,
        total_asset,
        total_revenue,
        bond_revenue,
        all_trade_client,
        non_pi_trade_client,
        pi_trade_client,
    ) = ads_hook.get_first(sql_query.format(p_end_date=p_end_date.isoformat()))

    message_title = "债券交易日报（久期 H009K）"
    message_content = f"""### {message_title}
统计日期：截止至{p_end_date.year}年{p_end_date.month}月{p_end_date.day}日    
金额单位：港币
<br>
#### 整体数据（久期 H009K）
ꔷ 当前客户AUM：{float(total_asset) / 1e4:.2f} 万    
ꔷ 当前客户总数：{total_client:d}    
ꔷ 当前客户交易总佣金：{float(total_revenue) / 1e4:.2f} 万
<br>
#### 分项数据（久期 H009K）
ꔷ 债券交易客户数：{all_trade_client:d}    
ꔷ 国债客户数：{non_pi_trade_client:d}    
ꔷ PI 债券客户数：{pi_trade_client:d}    
ꔷ 债券交易总佣金：{float(bond_revenue) / 1e4:.2f} 万"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_internal_note_daily_report(curr_end_date: date) -> dict[str, str]:
    prev_end_date = date(
        curr_end_date.year - 1,
        curr_end_date.month,
        28
        if curr_end_date.month == 2 and curr_end_date.day == 29
        else curr_end_date.day,
    )

    sql_query = get_file_content("sql/ads/wealth_report/note_daily_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    result_statement = "select * from tmp_summary"
    (
        curr_all_holding_value,
        curr_all_commission_ytd,
        curr_all_trade_client_ytd,
        curr_individual_trade_staff_ytd,
        curr_corporate_trade_staff_ytd,
    ) = ads_hook.get_first(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            p_note_type=1,
            result_statement=result_statement,
        )
    )
    (
        prev_all_holding_value,
        prev_all_commission_ytd,
        prev_all_trade_client_ytd,
        prev_individual_trade_staff_ytd,
        prev_corporate_trade_staff_ytd,
    ) = ads_hook.get_first(
        sql_query.format(
            p_end_date=prev_end_date.isoformat(),
            p_note_type=1,
            result_statement=result_statement,
        )
    )

    result_statement = "select * from tmp_trade_by_department"
    curr_trade_by_department = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            p_note_type=1,
            result_statement=result_statement,
        )
    )
    prev_trade_by_department = ads_hook.get_records(
        sql_query.format(
            p_end_date=prev_end_date.isoformat(),
            p_note_type=1,
            result_statement=result_statement,
        )
    )

    result_statement = "select * from tmp_trade_by_staff"
    curr_trade_by_staff = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            p_note_type=1,
            result_statement=result_statement,
        )
    )

    curr_commission_by_department = [
        (department_name, commission)
        for department_name, commission, _, _ in curr_trade_by_department
        if commission != 0
    ]
    curr_commission_by_department.sort(key=lambda x: x[1], reverse=True)
    prev_commission_by_department = {
        department_name: float(commission)
        for department_name, commission, _, _ in prev_trade_by_department
    }

    top_commission_by_department = []
    for idx, (department_name, commission) in enumerate(
            curr_commission_by_department, start=1
    ):
        prev_commission = prev_commission_by_department.get(department_name, 0.0)
        top_commission_by_department.append(
            f"ꔷ {department_name}：{float(commission) / 1e4:.2f} 万（{(float(commission) - prev_commission) / 1e4:.2f}万）    "
        )
        if idx >= 5:
            break
    top_commission_by_department = (
        "\n".join(top_commission_by_department)
        if top_commission_by_department
        else "无    "
    )

    curr_commission_by_staff = [
        (staff_name, commission)
        for staff_name, commission, _, _ in curr_trade_by_staff
        if commission != 0
    ]
    curr_commission_by_staff.sort(key=lambda x: x[1], reverse=True)
    curr_commission_by_staff = curr_commission_by_staff[:5]
    top_commission_by_staff = [
        f"ꔷ {staff_name}：{float(commission) / 1e4:.2f} 万    "
        for staff_name, commission in curr_commission_by_staff
    ]
    top_commission_by_staff = (
        "\n".join(top_commission_by_staff) if top_commission_by_staff else "无    "
    )

    curr_trade_client_by_department = [
        (department_name, trade_client)
        for department_name, _, trade_client, _ in curr_trade_by_department
        if trade_client != 0
    ]
    curr_trade_client_by_department.sort(key=lambda x: x[1], reverse=True)
    curr_trade_client_by_department = curr_trade_client_by_department[:5]
    top_trade_client_by_department = [
        f"ꔷ {department_name}：{trade_client:d} 个    "
        for department_name, trade_client in curr_trade_client_by_department
    ]
    top_trade_client_by_department = (
        "\n".join(top_trade_client_by_department)
        if top_trade_client_by_department
        else "无    "
    )

    curr_trade_client_by_staff = [
        (staff_name, trade_client)
        for staff_name, _, trade_client, _ in curr_trade_by_staff
        if trade_client != 0
    ]
    curr_trade_client_by_staff.sort(key=lambda x: x[1], reverse=True)
    curr_trade_client_by_staff = curr_trade_client_by_staff[:5]
    top_trade_client_by_staff = [
        f"ꔷ {staff_name}：{trade_client:d} 个    "
        for staff_name, trade_client in curr_trade_client_by_staff
    ]
    top_trade_client_by_staff = (
        "\n".join(top_trade_client_by_staff) if top_trade_client_by_staff else "无    "
    )

    curr_trade_times_by_staff = [
        (staff_name, trade_times)
        for staff_name, _, _, trade_times in curr_trade_by_staff
        if trade_times != 0
    ]
    curr_trade_times_by_staff.sort(key=lambda x: x[1], reverse=True)
    curr_trade_times_by_staff = curr_trade_times_by_staff[:5]
    top_trade_times_by_staff = [
        f"ꔷ {staff_name}：{trade_times:d} 个    "
        for staff_name, trade_times in curr_trade_times_by_staff
    ]
    top_trade_times_by_staff = (
        "\n".join(top_trade_times_by_staff) if top_trade_times_by_staff else "无    "
    )

    individual_trade_staff_ytd_change_rate = (
        f"{(curr_individual_trade_staff_ytd - prev_individual_trade_staff_ytd) / prev_individual_trade_staff_ytd:.2%}"
        if prev_individual_trade_staff_ytd
        else "-"
    )
    corporate_trade_staff_ytd_change_rate = (
        f"{(curr_corporate_trade_staff_ytd - prev_corporate_trade_staff_ytd) / prev_corporate_trade_staff_ytd:.2%}"
        if prev_corporate_trade_staff_ytd
        else "-"
    )

    message_title = "内部票据日报"
    message_content = f"""### {message_title}
统计日期：截止至{curr_end_date.year}年{curr_end_date.month}月{curr_end_date.day}日    
金额单位：等值美元    
\*\*不包含资管客户    
\*\*括号内为同比去年增长值
<br>
#### 整体数据(个人 / 企业)
ꔷ 当前内部票据 AUM：{float(curr_all_holding_value) / 1e8:.2f} 亿    
ꔷ 今年内部票据交易佣金：{float(curr_all_commission_ytd) / 1e4:.2f} 万    
ꔷ 今年内部票据交易客户数：{curr_all_trade_client_ytd:d}    
ꔷ 总开单内部票据交易销售人数：{curr_individual_trade_staff_ytd:d}（{individual_trade_staff_ytd_change_rate}） / {curr_corporate_trade_staff_ytd}（{corporate_trade_staff_ytd_change_rate}）
<br>
#### 今年内部票据交易佣金团队TOP5
{top_commission_by_department}
<br>
#### 今年内部票据交易佣金个人TOP5
{top_commission_by_staff}
<br>
#### 今年内部票据交易客户数团队TOP5
{top_trade_client_by_department}
<br>
#### 今年内部票据交易客户数个人TOP5
{top_trade_client_by_staff}
<br>
#### 今年内部票据交易笔数个人TOP5
{top_trade_times_by_staff}"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_external_note_weekly_report(curr_end_date: date) -> dict[str, str]:
    prev_end_date = date(
        curr_end_date.year - 1,
        curr_end_date.month,
        28
        if curr_end_date.month == 2 and curr_end_date.day == 29
        else curr_end_date.day,
    )

    sql_query = get_file_content("sql/ads/wealth_report/note_daily_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    result_statement = "select * from tmp_summary"
    (
        curr_all_holding_value,
        curr_all_commission_ytd,
        curr_all_trade_client_ytd,
        curr_individual_trade_staff_ytd,
        curr_corporate_trade_staff_ytd,
    ) = ads_hook.get_first(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            p_note_type=2,
            result_statement=result_statement,
        )
    )
    (
        prev_all_holding_value,
        prev_all_commission_ytd,
        prev_all_trade_client_ytd,
        prev_individual_trade_staff_ytd,
        prev_corporate_trade_staff_ytd,
    ) = ads_hook.get_first(
        sql_query.format(
            p_end_date=prev_end_date.isoformat(),
            p_note_type=2,
            result_statement=result_statement,
        )
    )

    result_statement = "select * from tmp_trade_by_department"
    curr_trade_by_department = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            p_note_type=2,
            result_statement=result_statement,
        )
    )
    prev_trade_by_department = ads_hook.get_records(
        sql_query.format(
            p_end_date=prev_end_date.isoformat(),
            p_note_type=2,
            result_statement=result_statement,
        )
    )

    result_statement = "select * from tmp_trade_by_staff"
    curr_trade_by_staff = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(),
            p_note_type=2,
            result_statement=result_statement,
        )
    )

    curr_commission_by_department = [
        (department_name, commission)
        for department_name, commission, _, _ in curr_trade_by_department
        if commission != 0
    ]
    curr_commission_by_department.sort(key=lambda x: x[1], reverse=True)
    prev_commission_by_department = {
        department_name: float(commission)
        for department_name, commission, _, _ in prev_trade_by_department
    }

    top_commission_by_department = []
    for idx, (department_name, commission) in enumerate(
            curr_commission_by_department, start=1
    ):
        prev_commission = prev_commission_by_department.get(department_name, 0.0)
        top_commission_by_department.append(
            f"ꔷ {department_name}：{float(commission) / 1e4:.2f} 万（{(float(commission) - prev_commission) / 1e4:.2f}万）    "
        )
        if idx >= 5:
            break
    top_commission_by_department = (
        "\n".join(top_commission_by_department)
        if top_commission_by_department
        else "无    "
    )

    curr_commission_by_staff = [
        (staff_name, commission)
        for staff_name, commission, _, _ in curr_trade_by_staff
        if commission != 0
    ]
    curr_commission_by_staff.sort(key=lambda x: x[1], reverse=True)
    curr_commission_by_staff = curr_commission_by_staff[:5]
    top_commission_by_staff = [
        f"ꔷ {staff_name}：{float(commission) / 1e4:.2f} 万    "
        for staff_name, commission in curr_commission_by_staff
    ]
    top_commission_by_staff = (
        "\n".join(top_commission_by_staff) if top_commission_by_staff else "无    "
    )

    curr_trade_client_by_department = [
        (department_name, trade_client)
        for department_name, _, trade_client, _ in curr_trade_by_department
        if trade_client != 0
    ]
    curr_trade_client_by_department.sort(key=lambda x: x[1], reverse=True)
    curr_trade_client_by_department = curr_trade_client_by_department[:5]
    top_trade_client_by_department = [
        f"ꔷ {department_name}：{trade_client:d} 个    "
        for department_name, trade_client in curr_trade_client_by_department
    ]
    top_trade_client_by_department = (
        "\n".join(top_trade_client_by_department)
        if top_trade_client_by_department
        else "无    "
    )

    curr_trade_client_by_staff = [
        (staff_name, trade_client)
        for staff_name, _, trade_client, _ in curr_trade_by_staff
        if trade_client != 0
    ]
    curr_trade_client_by_staff.sort(key=lambda x: x[1], reverse=True)
    curr_trade_client_by_staff = curr_trade_client_by_staff[:5]
    top_trade_client_by_staff = [
        f"ꔷ {staff_name}：{trade_client:d} 个    "
        for staff_name, trade_client in curr_trade_client_by_staff
    ]
    top_trade_client_by_staff = (
        "\n".join(top_trade_client_by_staff) if top_trade_client_by_staff else "无    "
    )

    curr_trade_times_by_staff = [
        (staff_name, trade_times)
        for staff_name, _, _, trade_times in curr_trade_by_staff
        if trade_times != 0
    ]
    curr_trade_times_by_staff.sort(key=lambda x: x[1], reverse=True)
    curr_trade_times_by_staff = curr_trade_times_by_staff[:5]
    top_trade_times_by_staff = [
        f"ꔷ {staff_name}：{trade_times:d} 个    "
        for staff_name, trade_times in curr_trade_times_by_staff
    ]
    top_trade_times_by_staff = (
        "\n".join(top_trade_times_by_staff) if top_trade_times_by_staff else "无    "
    )

    individual_trade_staff_ytd_change_rate = (
        f"{(curr_individual_trade_staff_ytd - prev_individual_trade_staff_ytd) / prev_individual_trade_staff_ytd:.2%}"
        if prev_individual_trade_staff_ytd
        else "-"
    )
    corporate_trade_staff_ytd_change_rate = (
        f"{(curr_corporate_trade_staff_ytd - prev_corporate_trade_staff_ytd) / prev_corporate_trade_staff_ytd:.2%}"
        if prev_corporate_trade_staff_ytd
        else "-"
    )

    message_title = "结构化票据交易周报"
    message_content = f"""### {message_title}
统计日期：截止至{curr_end_date.year}年{curr_end_date.month}月{curr_end_date.day}日    
金额单位：等值港币    
\*\*括号内为同比去年增长值
<br>
#### 整体数据(个人 / 企业)
ꔷ 当前结构化票据 AUM：{float(curr_all_holding_value) / 1e8:.2f} 亿    
ꔷ 今年结构化票据交易佣金：{float(curr_all_commission_ytd) / 1e4:.2f} 万    
ꔷ 今年结构化票据交易客户数：{curr_all_trade_client_ytd:d}    
ꔷ 总开单结构化票据交易销售人数：{curr_individual_trade_staff_ytd:d}（{individual_trade_staff_ytd_change_rate}） / {curr_corporate_trade_staff_ytd}（{corporate_trade_staff_ytd_change_rate}）
<br>
#### 今年结构化票据交易佣金团队TOP5
{top_commission_by_department}
<br>
#### 今年结构化票据交易佣金个人TOP5
{top_commission_by_staff}
<br>
#### 今年结构化票据交易客户数团队TOP5
{top_trade_client_by_department}
<br>
#### 今年结构化票据交易客户数个人TOP5
{top_trade_client_by_staff}
<br>
#### 今年结构化票据交易笔数个人TOP5
{top_trade_times_by_staff}"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_online_operation_revenue_report(curr_end_date: date) -> dict[str, str]:
    sql_query = get_file_content(
        "sql/ads/operation_explore/online_operation_revenue_report.sql"
    )
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    curr_ytd_begin_date = date(curr_end_date.year, 1, 1)
    prev_ytd_end_date = date(
        curr_end_date.year - 1,
        curr_end_date.month,
        28
        if curr_end_date.month == 2 and curr_end_date.day == 29
        else curr_end_date.day,
    )
    prev_ytd_begin_date = date(prev_ytd_end_date.year, 1, 1)
    (
        curr_ytd_stock_commission,
        curr_ytd_stock_platform_fee,
        curr_ytd_interest,
        curr_ytd_ipo,
        curr_ytd_wealth,
        curr_ytd_option_commission,
        curr_ytd_option_platform_fee,
        curr_ytd_option_interest,
        curr_ytd_total_revenue,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=curr_ytd_begin_date.isoformat(),
            p_end_date=curr_end_date.isoformat(),
        )
    )
    (
        prev_ytd_stock_commission,
        prev_ytd_stock_platform_fee,
        prev_ytd_interest,
        prev_ytd_ipo,
        prev_ytd_wealth,
        prev_ytd_option_commission,
        prev_ytd_option_platform_fee,
        prev_ytd_option_interest,
        prev_ytd_total_revenue,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=prev_ytd_begin_date.isoformat(),
            p_end_date=prev_ytd_end_date.isoformat(),
        )
    )

    curr_mtd_begin_date = date(curr_end_date.year, curr_end_date.month, 1)
    prev_mtd_end_date = curr_mtd_begin_date - timedelta(days=1)
    if prev_mtd_end_date.day > curr_end_date.day:
        prev_mtd_end_date = date(
            prev_mtd_end_date.year, prev_mtd_end_date.month, curr_end_date.day
        )
    prev_mtd_begin_date = date(prev_mtd_end_date.year, prev_mtd_end_date.month, 1)
    (
        curr_mtd_stock_commission,
        curr_mtd_stock_platform_fee,
        curr_mtd_interest,
        curr_mtd_ipo,
        curr_mtd_wealth,
        curr_mtd_option_commission,
        curr_mtd_option_platform_fee,
        curr_mtd_option_interest,
        curr_mtd_total_revenue,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=curr_mtd_begin_date.isoformat(),
            p_end_date=curr_end_date.isoformat(),
        )
    )
    (
        prev_mtd_stock_commission,
        prev_mtd_stock_platform_fee,
        prev_mtd_interest,
        prev_mtd_ipo,
        prev_mtd_wealth,
        prev_mtd_option_commission,
        prev_mtd_option_platform_fee,
        prev_mtd_option_interest,
        prev_mtd_total_revenue,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=prev_mtd_begin_date.isoformat(),
            p_end_date=prev_mtd_end_date.isoformat(),
        )
    )

    curr_wtd_begin_date = curr_end_date - timedelta(days=curr_end_date.weekday())
    prev_wtd_end_date = curr_end_date - timedelta(days=7)
    prev_wtd_begin_date = curr_wtd_begin_date - timedelta(days=7)
    (
        curr_wtd_stock_commission,
        curr_wtd_stock_platform_fee,
        curr_wtd_interest,
        curr_wtd_ipo,
        curr_wtd_wealth,
        curr_wtd_option_commission,
        curr_wtd_option_platform_fee,
        curr_wtd_option_interest,
        curr_wtd_total_revenue,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=curr_wtd_begin_date.isoformat(),
            p_end_date=curr_end_date.isoformat(),
        )
    )
    (
        prev_wtd_stock_commission,
        prev_wtd_stock_platform_fee,
        prev_wtd_interest,
        prev_wtd_ipo,
        prev_wtd_wealth,
        prev_wtd_option_commission,
        prev_wtd_option_platform_fee,
        prev_wtd_option_interest,
        prev_wtd_total_revenue,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=prev_wtd_begin_date.isoformat(),
            p_end_date=prev_wtd_end_date.isoformat(),
        )
    )

    ytd_total_revenue_change_rate = (
        f"{(curr_ytd_total_revenue - prev_ytd_total_revenue) / prev_ytd_total_revenue:.2%}"
        if prev_ytd_total_revenue
        else "-"
    )
    ytd_stock_commission_change_rate = (
        f"{(curr_ytd_stock_commission - prev_ytd_stock_commission) / prev_ytd_stock_commission:.2%}"
        if prev_ytd_stock_commission
        else "-"
    )
    ytd_stock_platform_fee_change_rate = (
        f"{(curr_ytd_stock_platform_fee - prev_ytd_stock_platform_fee) / prev_ytd_stock_platform_fee:.2%}"
        if prev_ytd_stock_platform_fee
        else "-"
    )
    ytd_interest_change_rate = (
        f"{(curr_ytd_interest - prev_ytd_interest) / prev_ytd_interest:.2%}"
        if prev_ytd_interest
        else "-"
    )
    ytd_ipo_change_rate = (
        f"{(curr_ytd_ipo - prev_ytd_ipo) / prev_ytd_ipo:.2%}" if prev_ytd_ipo else "-"
    )
    ytd_wealth_change_rate = (
        f"{(curr_ytd_wealth - prev_ytd_wealth) / prev_ytd_wealth:.2%}"
        if prev_ytd_wealth
        else "-"
    )
    ytd_option_commission_change_rate = (
        f"{(curr_ytd_option_commission - prev_ytd_option_commission) / prev_ytd_option_commission:.2%}"
        if prev_ytd_option_commission
        else "-"
    )
    ytd_option_platform_fee_change_rate = (
        f"{(curr_ytd_option_platform_fee - prev_ytd_option_platform_fee) / prev_ytd_option_platform_fee:.2%}"
        if prev_ytd_option_platform_fee
        else "-"
    )
    ytd_option_interest_change_rate = (
        f"{(curr_ytd_option_interest - prev_ytd_option_interest) / prev_ytd_option_interest:.2%}"
        if prev_ytd_option_interest
        else "-"
    )

    mtd_total_revenue_change_rate = (
        f"{(curr_mtd_total_revenue - prev_mtd_total_revenue) / prev_mtd_total_revenue:.2%}"
        if prev_mtd_total_revenue
        else "-"
    )
    mtd_stock_commission_change_rate = (
        f"{(curr_mtd_stock_commission - prev_mtd_stock_commission) / prev_mtd_stock_commission:.2%}"
        if prev_mtd_stock_commission
        else "-"
    )
    mtd_stock_platform_fee_change_rate = (
        f"{(curr_mtd_stock_platform_fee - prev_mtd_stock_platform_fee) / prev_mtd_stock_platform_fee:.2%}"
        if prev_mtd_stock_platform_fee
        else "-"
    )
    mtd_interest_change_rate = (
        f"{(curr_mtd_interest - prev_mtd_interest) / prev_mtd_interest:.2%}"
        if prev_mtd_interest
        else "-"
    )
    mtd_ipo_change_rate = (
        f"{(curr_mtd_ipo - prev_mtd_ipo) / prev_mtd_ipo:.2%}" if prev_mtd_ipo else "-"
    )
    mtd_wealth_change_rate = (
        f"{(curr_mtd_wealth - prev_mtd_wealth) / prev_mtd_wealth:.2%}"
        if prev_mtd_wealth
        else "-"
    )
    mtd_option_commission_change_rate = (
        f"{(curr_mtd_option_commission - prev_mtd_option_commission) / prev_mtd_option_commission:.2%}"
        if prev_mtd_option_commission
        else "-"
    )
    mtd_option_platform_fee_change_rate = (
        f"{(curr_mtd_option_platform_fee - prev_mtd_option_platform_fee) / prev_mtd_option_platform_fee:.2%}"
        if prev_mtd_option_platform_fee
        else "-"
    )
    mtd_option_interest_change_rate = (
        f"{(curr_mtd_option_interest - prev_mtd_option_interest) / prev_mtd_option_interest:.2%}"
        if prev_mtd_option_interest
        else "-"
    )

    wtd_total_revenue_change_rate = (
        f"{(curr_wtd_total_revenue - prev_wtd_total_revenue) / prev_wtd_total_revenue:.2%}"
        if prev_wtd_total_revenue
        else "-"
    )
    wtd_stock_commission_change_rate = (
        f"{(curr_wtd_stock_commission - prev_wtd_stock_commission) / prev_wtd_stock_commission:.2%}"
        if prev_wtd_stock_commission
        else "-"
    )
    wtd_stock_platform_fee_change_rate = (
        f"{(curr_wtd_stock_platform_fee - prev_wtd_stock_platform_fee) / prev_wtd_stock_platform_fee:.2%}"
        if prev_wtd_stock_platform_fee
        else "-"
    )
    wtd_interest_change_rate = (
        f"{(curr_wtd_interest - prev_wtd_interest) / prev_wtd_interest:.2%}"
        if prev_wtd_interest
        else "-"
    )
    wtd_ipo_change_rate = (
        f"{(curr_wtd_ipo - prev_wtd_ipo) / prev_wtd_ipo:.2%}" if prev_wtd_ipo else "-"
    )
    wtd_wealth_change_rate = (
        f"{(curr_wtd_wealth - prev_wtd_wealth) / prev_wtd_wealth:.2%}"
        if prev_wtd_wealth
        else "-"
    )
    wtd_option_commission_change_rate = (
        f"{(curr_wtd_option_commission - prev_wtd_option_commission) / prev_wtd_option_commission:.2%}"
        if prev_wtd_option_commission
        else "-"
    )
    wtd_option_platform_fee_change_rate = (
        f"{(curr_wtd_option_platform_fee - prev_wtd_option_platform_fee) / prev_wtd_option_platform_fee:.2%}"
        if prev_wtd_option_platform_fee
        else "-"
    )
    wtd_option_interest_change_rate = (
        f"{(curr_wtd_option_interest - prev_wtd_option_interest) / prev_wtd_option_interest:.2%}"
        if prev_wtd_option_interest
        else "-"
    )

    message_title = "运营重点关注数据播报"
    message_content = f"""### {message_title}
{curr_end_date.year}年{curr_end_date.month}月{curr_end_date.day}日数据
<br>
#### {curr_end_date.year}年数据【F001~F007&B1&B2】
#### 年创收：{curr_ytd_total_revenue:.2f} HKD（{ytd_total_revenue_change_rate}）
ꔷ 证券佣金创收：{curr_ytd_stock_commission:.2f} HKD（{ytd_stock_commission_change_rate}）    
ꔷ 证券平台费创收：{curr_ytd_stock_platform_fee:.2f} HKD（{ytd_stock_platform_fee_change_rate}）    
ꔷ 孖展利息创收：{curr_ytd_interest:.2f} HKD（{ytd_interest_change_rate}）    
ꔷ 打新创收：{curr_ytd_ipo:.2f} HKD（{ytd_ipo_change_rate}）    
ꔷ 财富收入：{curr_ytd_wealth:.2f} HKD（{ytd_wealth_change_rate}）    
<br>
ꔷ 期权佣金创收：{curr_ytd_option_commission:.2f} HKD（{ytd_option_commission_change_rate}）    
ꔷ 期权平台费创收：{curr_ytd_option_platform_fee:.2f} HKD（{ytd_option_platform_fee_change_rate}）    
ꔷ 期权孖展利息创收：{curr_ytd_option_interest:.2f} HKD（{ytd_option_interest_change_rate}）
<br>
#### 当月数据（自然月为一完整月）
#### 月创收：{curr_mtd_total_revenue:.2f} HKD（{mtd_total_revenue_change_rate}）
ꔷ 证券佣金创收：{curr_mtd_stock_commission:.2f} HKD（{mtd_stock_commission_change_rate}）    
ꔷ 证券平台费创收：{curr_mtd_stock_platform_fee:.2f} HKD（{mtd_stock_platform_fee_change_rate}）    
ꔷ 孖展利息创收：{curr_mtd_interest:.2f} HKD（{mtd_interest_change_rate}）    
ꔷ 打新创收：{curr_mtd_ipo:.2f} HKD（{mtd_ipo_change_rate}）    
ꔷ 财富收入：{curr_mtd_wealth:.2f} HKD（{mtd_wealth_change_rate}）    
<br>
ꔷ 期权佣金创收：{curr_mtd_option_commission:.2f} HKD（{mtd_option_commission_change_rate}）    
ꔷ 期权平台费创收：{curr_mtd_option_platform_fee:.2f} HKD（{mtd_option_platform_fee_change_rate}）    
ꔷ 期权孖展利息创收：{curr_mtd_option_interest:.2f} HKD（{mtd_option_interest_change_rate}）
<br>
#### 当周数据（周一至周日为一完整周）
#### 周创收：{curr_wtd_total_revenue:.2f} HKD（{wtd_total_revenue_change_rate}）
ꔷ 证券佣金创收：{curr_wtd_stock_commission:.2f} HKD（{wtd_stock_commission_change_rate}）    
ꔷ 证券平台费创收：{curr_wtd_stock_platform_fee:.2f} HKD（{wtd_stock_platform_fee_change_rate}）    
ꔷ 孖展利息创收：{curr_wtd_interest:.2f} HKD（{wtd_interest_change_rate}）    
ꔷ 打新创收：{curr_wtd_ipo:.2f} HKD（{wtd_ipo_change_rate}）    
ꔷ 财富收入：{curr_wtd_wealth:.2f} HKD（{wtd_wealth_change_rate}）    
<br>
ꔷ 期权佣金创收：{curr_wtd_option_commission:.2f} HKD（{wtd_option_commission_change_rate}）    
ꔷ 期权平台费创收：{curr_wtd_option_platform_fee:.2f} HKD（{wtd_option_platform_fee_change_rate}）    
ꔷ 期权孖展利息创收：{curr_wtd_option_interest:.2f} HKD（{wtd_option_interest_change_rate}）"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_private_fund_daily_report(curr_end_date: date) -> dict[str, str]:
    prev_end_date = curr_end_date - timedelta(days=7)

    sql_query = get_file_content("sql/ads/wealth_report/private_fund_daily_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    result_statement = "select * from tmp_summary"
    (
        curr_total_holding_value,
        curr_total_commission,
        curr_total_trade_amount,
        curr_total_all_trade_client,
        curr_individual_trade_staff,
        curr_corporate_trade_staff,
        curr_total_commission_wtd,
        curr_total_trade_amount_wtd,
    ) = ads_hook.get_first(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(), result_statement=result_statement
        )
    )
    (
        prev_total_holding_value,
        prev_total_commission,
        prev_total_trade_amount,
        prev_total_all_trade_client,
        prev_individual_trade_staff,
        prev_corporate_trade_staff,
        prev_total_commission_wtd,
        prev_total_trade_amount_wtd,
    ) = ads_hook.get_first(
        sql_query.format(
            p_end_date=prev_end_date.isoformat(), result_statement=result_statement
        )
    )

    result_statement = (
        "select * from tmp_trade_amount_by_staff_wtd order by trade_amount desc"
    )
    curr_trade_amount_by_staff_wtd = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(), result_statement=result_statement
        )
    )

    result_statement = "select * from tmp_commission_by_staff order by commission desc"
    curr_commission_by_staff = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(), result_statement=result_statement
        )
    )

    result_statement = (
        "select * from tmp_holding_client_by_staff order by holding_client desc"
    )
    curr_holding_client_by_staff = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(), result_statement=result_statement
        )
    )

    result_statement = (
        "select * from tmp_holding_client_by_department order by holding_client desc"
    )
    curr_holding_client_by_department = ads_hook.get_records(
        sql_query.format(
            p_end_date=curr_end_date.isoformat(), result_statement=result_statement
        )
    )

    individual_trade_staff_change_rate = (
        f"{(curr_individual_trade_staff - prev_individual_trade_staff) / prev_individual_trade_staff:.2%}"
        if prev_individual_trade_staff
        else "-"
    )
    corporate_trade_staff_change_rate = (
        f"{(curr_corporate_trade_staff - prev_corporate_trade_staff) / prev_corporate_trade_staff:.2%}"
        if prev_corporate_trade_staff
        else "-"
    )

    trade_amount_by_staff_wtd = []
    for staff_name, trade_amount in curr_trade_amount_by_staff_wtd:
        trade_amount_by_staff_wtd.append(
            f"ꔷ {staff_name}：{float(trade_amount) / 1e4:.2f} 万    "
        )
    trade_amount_by_staff_wtd = (
        "\n".join(trade_amount_by_staff_wtd) if trade_amount_by_staff_wtd else "无    "
    )

    top_commission_by_staff = []
    for idx, (staff_name, commission) in enumerate(curr_commission_by_staff, start=1):
        top_commission_by_staff.append(
            f"ꔷ {staff_name}：{float(commission) / 1e4:.2f} 万    "
        )
        if idx >= 5:
            break
    top_commission_by_staff = (
        "\n".join(top_commission_by_staff) if top_commission_by_staff else "无    "
    )

    top_holding_client_by_department = []
    for idx, (department_name, holding_client) in enumerate(
            curr_holding_client_by_department, start=1
    ):
        top_holding_client_by_department.append(
            f"ꔷ {department_name}：{holding_client}    "
        )
        if idx >= 5:
            break
    top_holding_client_by_department = (
        "\n".join(top_holding_client_by_department)
        if top_holding_client_by_department
        else "无    "
    )

    top_holding_client_by_staff = []
    for idx, (staff_name, holding_client) in enumerate(
            curr_holding_client_by_staff, start=1
    ):
        top_holding_client_by_staff.append(f"ꔷ {staff_name}：{holding_client}    ")
        if idx >= 5:
            break
    top_holding_client_by_staff = (
        "\n".join(top_holding_client_by_staff)
        if top_holding_client_by_staff
        else "无    "
    )

    message_title = "私募基金交易日报"
    message_content = f"""### {message_title}
统计日期：截止至{curr_end_date.year}年{curr_end_date.month}月{curr_end_date.day}日    
金额单位：港币
<br>
#### 整体数据(个人 / 企业)
ꔷ 当前私募基金AUM：{float(curr_total_holding_value) / 1e8:.2f} 亿    
ꔷ 今年私募基金认购费：{float(curr_total_commission) / 1e4:.2f} 万    
ꔷ 今年私募基金交易额：{float(curr_total_trade_amount) / 1e4:.2f} 万    
ꔷ 今年私募交易客户数：{curr_total_all_trade_client}    
ꔷ 总开单交易销售人数：{curr_individual_trade_staff}（{individual_trade_staff_change_rate}） / {curr_corporate_trade_staff}（{corporate_trade_staff_change_rate}）    
ꔷ 本周新增私募基金认购费：{float(curr_total_commission_wtd) / 1e4:.2f} 万    
ꔷ 本周新增私募基金交易额：{float(curr_total_trade_amount_wtd) / 1e4:.2f} 万    
\*\*括号内为同比上周增幅
<br>
#### 本周私募基金下单销售及交易额
{trade_amount_by_staff_wtd}
<br>
#### 今年私募基金交易认购费个人TOP5
{top_commission_by_staff}
<br>
#### 当前私募基金持仓客户数团队TOP5
{top_holding_client_by_department}
<br>
#### 当前私募基金持仓客户数个人 TOP5
{top_holding_client_by_staff}"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_virtual_asset_daily_report(p_end_date: date) -> dict[str, str]:
    sql_query = get_file_content("sql/ads/virtual_asset/virtual_asset_daily_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    acc_begin_date = date(2000, 1, 1)
    acc_open_num, acc_bind_num, acc_trade_amount_hkd, acc_revenue_hkd = (
        ads_hook.get_first(
            sql_query.format(
                p_begin_date=acc_begin_date.isoformat(),
                p_end_date=p_end_date.isoformat(),
            )
        )
    )

    ytd_begin_date = date(p_end_date.year, 1, 1)
    ytd_open_num, ytd_bind_num, ytd_trade_amount_hkd, ytd_revenue_hkd = (
        ads_hook.get_first(
            sql_query.format(
                p_begin_date=ytd_begin_date.isoformat(),
                p_end_date=p_end_date.isoformat(),
            )
        )
    )

    mtd_begin_date = date(p_end_date.year, p_end_date.month, 1)
    mtd_open_num, mtd_bind_num, mtd_trade_amount_hkd, mtd_revenue_hkd = (
        ads_hook.get_first(
            sql_query.format(
                p_begin_date=mtd_begin_date.isoformat(),
                p_end_date=p_end_date.isoformat(),
            )
        )
    )

    wtd_begin_date = p_end_date - timedelta(days=p_end_date.weekday())
    wtd_open_num, wtd_bind_num, wtd_trade_amount_hkd, wtd_revenue_hkd = (
        ads_hook.get_first(
            sql_query.format(
                p_begin_date=wtd_begin_date.isoformat(),
                p_end_date=p_end_date.isoformat(),
            )
        )
    )

    message_title = "虚拟资产交易日报"
    message_content = f"""### {message_title}
统计日期：截止至{p_end_date.year}年{p_end_date.month}月{p_end_date.day}日    
金额单位：港币
<br>
#### 整体数据
ꔷ 累计客户：{acc_open_num}    
ꔷ 累计钱包：{acc_bind_num}
<br>
#### 今年数据
ꔷ 今年新增客户：{ytd_open_num}    
ꔷ 今年新增钱包：{ytd_bind_num}    
ꔷ 今年交易量：{float(ytd_trade_amount_hkd) / 1e4:.2f} 万    
ꔷ 今年创收：{float(ytd_revenue_hkd) / 1e4:.2f} 万
<br>
#### 本月数据
ꔷ 本月新增客户：{mtd_open_num}    
ꔷ 本月新增钱包：{mtd_bind_num}    
ꔷ 本月交易量：{float(mtd_trade_amount_hkd) / 1e4:.2f} 万    
ꔷ 本月创收：{float(mtd_revenue_hkd) / 1e4:.2f} 万
<br>
#### 本周数据
ꔷ 本周新增客户：{wtd_open_num}    
ꔷ 本周新增钱包：{wtd_bind_num}    
ꔷ 本周交易量：{wtd_trade_amount_hkd:.2f}    
ꔷ 本周创收：{wtd_revenue_hkd:.2f}"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_greater_wealth_report(period_type: str, p_cur_end_date: date) -> str:
    if period_type == "W":
        p_cur_begin_date = p_cur_end_date - timedelta(days=p_cur_end_date.weekday())
    else:
        p_cur_begin_date = date(p_cur_end_date.year, p_cur_end_date.month, 1)

    p_pre_end_date = p_cur_begin_date - timedelta(days=1)
    if period_type == "W":
        p_pre_begin_date = p_pre_end_date - timedelta(days=6)
    else:
        p_pre_begin_date = date(p_pre_end_date.year, p_pre_end_date.month, 1)

    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")
    sql_query = get_file_content("sql/ads/manage_analysis/greater_wealth_report.sql")
    (
        open_num_total,
        open_num_ytd,
        open_num_mtd,
        pi_num_total,
        pi_num_ytd,
        pi_num_mtd,
        high_asset_num_cur,
        high_asset_num_year_start,
        high_asset_num_month_start,
        high_asset_num_ytd,
        high_asset_num_mtd,
        asset_total_cur,
        asset_total_pre,
        asset_total_change,
        asset_broker_cur,
        asset_broker_pre,
        asset_broker_change,
        asset_note_cur,
        asset_note_pre,
        asset_note_change,
        asset_bond_cur,
        asset_bond_pre,
        asset_bond_change,
        asset_fund_cur,
        asset_fund_pre,
        asset_fund_change,
        asset_balance_cur,
        asset_balance_pre,
        asset_balance_change,
        trade_amount_total_cur,
        trade_amount_total_pre,
        trade_amount_total_change,
        trade_amount_broker_cur,
        trade_amount_broker_pre,
        trade_amount_broker_change,
        trade_amount_bond_cur,
        trade_amount_bond_pre,
        trade_amount_bond_change,
        trade_amount_note_cur,
        trade_amount_note_pre,
        trade_amount_note_change,
        trade_amount_insurance_cur,
        trade_amount_insurance_pre,
        trade_amount_insurance_change,
    ) = ads_hook.get_first(
        sql_query.format(
            p_cur_begin_date=p_cur_begin_date.isoformat(),
            p_cur_end_date=p_cur_end_date.isoformat(),
            p_pre_begin_date=p_pre_begin_date.isoformat(),
            p_pre_end_date=p_pre_end_date.isoformat(),
            p_department_type=1,
        )
    )

    message = f"""标题: 大财富经营{"周" if period_type == "W" else "月"}报
统计日期: {p_cur_end_date.isoformat()}

PI客户数: {pi_num_total}
今年PI客户数: {pi_num_ytd}
本月PI客户数: {pi_num_mtd}

总AUM: {asset_total_cur}
总AUM同比新增: {asset_total_change}
证券AUM: {asset_broker_cur}
证券AUM同比新增: {asset_broker_change}
票据AUM: {asset_note_cur}
票据AUM同比新增: {asset_note_change}
债券AUM: {asset_bond_cur}
债券AUM同比新增: {asset_bond_change}
基金AUM: {asset_fund_cur}
基金AUM同比新增: {asset_fund_change}
现金AUM: {asset_balance_cur}
现金AUM同比新增: {asset_balance_change}

今年创收:
今年创收完成度:
今年证券创收:
今年证券创收完成度:
今年投顾创收:
今年投顾创收完成度:
今年利息创收:
今年利息创收完成度:
今年票据(复盈宝)创收:
今年票据(复盈宝)创收完成度:
今年票据(其他)创收:
今年票据(其他)创收完成度:
今年债券创收:
今年债券创收完成度:
今年基金创收:
今年基金创收完成度:
今年旅居及基金创收:
今年旅居及基金创收完成度:
今年星享创收:
今年星享创收完成度:
今年星链创收:
今年星链创收完成度:
今年保险及基金创收:
今年保险及基金创收完成度:

本期创收:
本期创收完成度:
本期证券创收:
本期证券创收完成度:
本期投顾创收:
本期投顾创收完成度:
本期利息创收:
本期利息创收完成度:
本期票据(复盈宝)创收:
本期票据(复盈宝)创收完成度:
本期票据(其他)创收:
本期票据(其他)创收完成度:
本期债券创收:
本期债券创收完成度:
本期基金创收:
本期基金创收完成度:
本期旅居及基金创收:
本期旅居及基金创收完成度:
本期星享创收:
本期星享创收完成度:
本期星链创收:
本期星链创收完成度:
本期保险及基金创收:
本期保险及基金创收完成度:

交易金额: {trade_amount_total_cur}
交易金额同比变化: {trade_amount_total_change}
证券交易金额: {trade_amount_broker_cur}
证券交易金额同比变化: {trade_amount_broker_change}
债券交易金额: {trade_amount_bond_cur}
债券交易金额同比变化: {trade_amount_bond_change}
票据交易金额: {trade_amount_note_cur}
票据交易金额同比变化: {trade_amount_note_change}
保险交易金额: {trade_amount_insurance_cur}
保险交易金额同比变化: {trade_amount_insurance_change}
"""

    return message


def generate_wealth_department_report(
        period_type: str, p_cur_end_date: date, p_department_type: int, title_prefix: str
) -> str:
    if period_type == "W":
        p_cur_begin_date = p_cur_end_date - timedelta(days=p_cur_end_date.weekday())
    else:
        p_cur_begin_date = date(p_cur_end_date.year, p_cur_end_date.month, 1)

    p_pre_end_date = p_cur_begin_date - timedelta(days=1)
    if period_type == "W":
        p_pre_begin_date = p_pre_end_date - timedelta(days=6)
    else:
        p_pre_begin_date = date(p_pre_end_date.year, p_pre_end_date.month, 1)

    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")
    sql_query = get_file_content("sql/ads/manage_analysis/greater_wealth_report.sql")
    (
        open_num_total,
        open_num_ytd,
        open_num_mtd,
        pi_num_total,
        pi_num_ytd,
        pi_num_mtd,
        high_asset_num_cur,
        high_asset_num_year_start,
        high_asset_num_month_start,
        high_asset_num_ytd,
        high_asset_num_mtd,
        asset_total_cur,
        asset_total_pre,
        asset_total_change,
        asset_broker_cur,
        asset_broker_pre,
        asset_broker_change,
        asset_note_cur,
        asset_note_pre,
        asset_note_change,
        asset_bond_cur,
        asset_bond_pre,
        asset_bond_change,
        asset_fund_cur,
        asset_fund_pre,
        asset_fund_change,
        asset_balance_cur,
        asset_balance_pre,
        asset_balance_change,
        trade_amount_total_cur,
        trade_amount_total_pre,
        trade_amount_total_change,
        trade_amount_broker_cur,
        trade_amount_broker_pre,
        trade_amount_broker_change,
        trade_amount_bond_cur,
        trade_amount_bond_pre,
        trade_amount_bond_change,
        trade_amount_note_cur,
        trade_amount_note_pre,
        trade_amount_note_change,
        trade_amount_insurance_cur,
        trade_amount_insurance_pre,
        trade_amount_insurance_change,
    ) = ads_hook.get_first(
        sql_query.format(
            p_cur_begin_date=p_cur_begin_date.isoformat(),
            p_cur_end_date=p_cur_end_date.isoformat(),
            p_pre_begin_date=p_pre_begin_date.isoformat(),
            p_pre_end_date=p_pre_end_date.isoformat(),
            p_department_type=p_department_type,
        )
    )

    message = f"""标题: {title_prefix}{"周" if period_type == "W" else "月"}报
统计日期: {p_cur_end_date.isoformat()}

开户数: {open_num_total}
今年开户数: {open_num_ytd}
本月开户数: {open_num_mtd}

1万美金客户数: {high_asset_num_cur}
今年1万美金客户数: {high_asset_num_ytd}
本月1万美金客户数: {high_asset_num_mtd}

PI客户数: {pi_num_total}
今年PI客户数: {pi_num_ytd}
本月PI客户数: {pi_num_mtd}

总AUM: {asset_total_cur}
总AUM同比新增: {asset_total_change}
证券AUM: {asset_broker_cur}
证券AUM同比新增: {asset_broker_change}
票据AUM: {asset_note_cur}
票据AUM同比新增: {asset_note_change}
债券AUM: {asset_bond_cur}
债券AUM同比新增: {asset_bond_change}
基金AUM: {asset_fund_cur}
基金AUM同比新增: {asset_fund_change}
现金AUM: {asset_balance_cur}
现金AUM同比新增: {asset_balance_change}

今年创收:
今年创收完成度:
今年证券创收:
今年证券创收完成度:
今年利息创收:
今年利息创收完成度:
今年票据创收:
今年票据创收完成度:
今年债券创收:
今年债券创收完成度:
今年基金创收:
今年基金创收完成度:
今年旅居及基金创收:
今年旅居及基金创收完成度:
今年保险及基金创收:
今年保险及基金创收完成度:

本期创收:
本期创收完成度:
本期证券创收:
本期证券创收完成度:
本期利息创收:
本期利息创收完成度:
本期票据创收:
本期票据创收完成度:
本期债券创收:
本期债券创收完成度:
本期基金创收:
本期基金创收完成度:
本期旅居及基金创收:
本期旅居及基金创收完成度:
本期保险及基金创收:
本期保险及基金创收完成度:

交易金额: {trade_amount_total_cur}
交易金额同比变化: {trade_amount_total_change}
证券交易金额: {trade_amount_broker_cur}
证券交易金额同比变化: {trade_amount_broker_change}
债券交易金额: {trade_amount_bond_cur}
债券交易金额同比变化: {trade_amount_bond_change}
票据交易金额: {trade_amount_note_cur}
票据交易金额同比变化: {trade_amount_note_change}
"""

    return message


def generate_send_payoneer_open_aum(p_report_date: date) -> dict[str, str]:
    sql_query = get_file_content("sql/ads/wealth_report/payoneer_open_aum.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    total_open_num, total_asset_hkd, mmf_value_hkd = ads_hook.get_first(
        sql_query.format(p_report_date=p_report_date.isoformat())
    )

    message_title = "派安盈开户及AUM日报"
    message_content = f"""### {message_title}
统计日期：{p_report_date.year}年{p_report_date.month}月{p_report_date.day}日    
开户数：{total_open_num}    
W300 AUM规模：{float(total_asset_hkd) / 1e8:.2f}亿HKD    
W300 货基规模：{float(mmf_value_hkd) / 1e8:.2f}亿HKD"""

    return {
        "title": message_title,
        "text": message_content,
    }


def generate_us_option_daily_report(curr_end_date: date) -> dict[str, str]:
    sql_query = get_file_content("sql/ads/misc/us_option_daily_report.sql")
    ads_hook = clickhouse.ClickHouseHook(mysql_conn_id="ads")

    # 整体-全部客户
    acc_begin_date = date(2020, 1, 1)
    (
        acc_all_open_num,
        acc_all_asset_hkd,
        acc_all_revenue_hkd,
        acc_all_trade_client_num,
        acc_all_trade_times,
        acc_all_trade_times_online,
        acc_all_trade_times_offline,
        acc_all_trade_amount_hkd,
        acc_all_underlying_symbols,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=acc_begin_date.isoformat(),
            p_end_date=curr_end_date.isoformat(),
            p_only_external_client=0,
            p_is_prev_us_day=0,
        )
    )

    # 整体-外部客户
    (
        acc_external_open_num,
        acc_external_asset_hkd,
        acc_external_revenue_hkd,
        acc_external_trade_client_num,
        acc_external_trade_times,
        acc_external_trade_times_online,
        acc_external_trade_times_offline,
        acc_external_trade_amount_hkd,
        acc_external_underlying_symbols,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=acc_begin_date.isoformat(),
            p_end_date=curr_end_date.isoformat(),
            p_only_external_client=1,
            p_is_prev_us_day=0,
        )
    )

    # 本月
    curr_mtd_begin_date = date(curr_end_date.year, curr_end_date.month, 1)
    (
        curr_mtd_open_num,
        curr_mtd_asset_hkd,
        curr_mtd_revenue_hkd,
        curr_mtd_trade_client_num,
        curr_mtd_trade_times,
        curr_mtd_trade_times_online,
        curr_mtd_trade_times_offline,
        curr_mtd_trade_amount_hkd,
        curr_mtd_underlying_symbols,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=curr_mtd_begin_date.isoformat(),
            p_end_date=curr_end_date.isoformat(),
            p_only_external_client=0,
            p_is_prev_us_day=0,
        )
    )

    # 上个月
    # 上个月最后一天
    prev_month_max_date = curr_end_date - timedelta(days=curr_end_date.day)
    prev_month_day = curr_end_date.day if prev_month_max_date.day > curr_end_date.day else prev_month_max_date.day
    prev_mtd_end_date = date(
        prev_month_max_date.year, prev_month_max_date.month, prev_month_day
    )
    prev_mtd_begin_date = date(prev_mtd_end_date.year, prev_mtd_end_date.month, 1)
    (
        prev_mtd_open_num,
        prev_mtd_asset_hkd,
        prev_mtd_revenue_hkd,
        prev_mtd_trade_client_num,
        prev_mtd_trade_times,
        prev_mtd_trade_times_online,
        prev_mtd_trade_times_offline,
        prev_mtd_trade_amount_hkd,
        prev_mtd_underlying_symbols,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=prev_mtd_begin_date.isoformat(),
            p_end_date=prev_mtd_end_date.isoformat(),
            p_only_external_client=0,
            p_is_prev_us_day=0,
        )
    )

    # 本周
    curr_wtd_begin_date = curr_end_date - timedelta(days=curr_end_date.weekday())
    (
        curr_wtd_open_num,
        curr_wtd_asset_hkd,
        curr_wtd_revenue_hkd,
        curr_wtd_trade_client_num,
        curr_wtd_trade_times,
        curr_wtd_trade_times_online,
        curr_wtd_trade_times_offline,
        curr_wtd_trade_amount_hkd,
        curr_wtd_underlying_symbols,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=curr_wtd_begin_date.isoformat(),
            p_end_date=curr_end_date.isoformat(),
            p_only_external_client=0,
            p_is_prev_us_day=0,
        )
    )

    # 上周
    prev_wtd_begin_date = curr_wtd_begin_date - timedelta(days=7)
    prev_wtd_end_date = curr_end_date - timedelta(days=7)
    (
        prev_wtd_open_num,
        prev_wtd_asset_hkd,
        prev_wtd_revenue_hkd,
        prev_wtd_trade_client_num,
        prev_wtd_trade_times,
        prev_wtd_trade_times_online,
        prev_wtd_trade_times_offline,
        prev_wtd_trade_amount_hkd,
        prev_wtd_underlying_symbols,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=prev_wtd_begin_date.isoformat(),
            p_end_date=prev_wtd_end_date.isoformat(),
            p_only_external_client=0,
            p_is_prev_us_day=0,
        )
    )
    # 当天
    curr_day_begin_date = curr_end_date
    (
        curr_day_open_num,
        curr_day_asset_hkd,
        curr_day_revenue_hkd,
        curr_day_trade_client_num,
        curr_day_trade_times,
        curr_day_trade_times_online,
        curr_day_trade_times_offline,
        curr_day_trade_amount_hkd,
        curr_day_underlying_symbols,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=curr_day_begin_date.isoformat(),
            p_end_date=curr_end_date.isoformat(),
            p_only_external_client=0,
            p_is_prev_us_day=0,
        )
    )

    # 前一天
    prev_day_begin_date = curr_day_begin_date - timedelta(days=1)
    prev_day_end_date = curr_end_date - timedelta(days=1)
    (
        prev_day_open_num,
        prev_day_asset_hkd,
        prev_day_revenue_hkd,
        prev_day_trade_client_num,
        prev_day_trade_times,
        prev_day_trade_times_online,
        prev_day_trade_times_offline,
        prev_day_trade_amount_hkd,
        prev_day_underlying_symbols,
    ) = ads_hook.get_first(
        sql_query.format(
            p_begin_date=prev_day_begin_date.isoformat(),
            p_end_date=prev_day_end_date.isoformat(),
            p_only_external_client=0,
            p_is_prev_us_day=1,
        )
    )
    def format_ratio(part_value, all_value):
        return f"{part_value / all_value:.2%}" if all_value else "-"

    def format_change_rate(curr_value, prev_value):
        return f"{(curr_value - prev_value) / prev_value:.2%}" if prev_value else "-"

    message_title = "美股期权日报"
    message_content = f"""## {message_title}
统计日期：{curr_end_date.year}年{curr_end_date.month}月{curr_end_date.day}日    
金额币种：HKD
<br>
### 整体数据
累计开户数：{acc_all_open_num}（{format_ratio(acc_external_open_num, acc_all_open_num)}）    
期权AUM：{float(acc_all_asset_hkd) / 1e4:.2f}万（{format_ratio(acc_external_asset_hkd, acc_all_asset_hkd)}）    
创收：{float(acc_all_revenue_hkd) / 1e4:.2f}万（{format_ratio(acc_external_revenue_hkd, acc_all_revenue_hkd)}）    
交易总人数：{acc_all_trade_client_num}（{format_ratio(acc_external_trade_client_num, acc_all_trade_client_num)}）    
交易总单数：{acc_all_trade_times}（{format_ratio(acc_external_trade_times, acc_all_trade_times)}）（线上：{acc_all_trade_times_online}，线下：{acc_all_trade_times_offline}）    
交易总金额：{float(acc_all_trade_amount_hkd) / 1e4:.2f}万（{format_ratio(acc_external_trade_amount_hkd, acc_all_trade_amount_hkd)}）    
交易张数前3股票：{acc_all_underlying_symbols}
\*\*括号内为外部客户占比
<br>
### 本月数据
开户数：{curr_mtd_open_num}（{format_change_rate(curr_mtd_open_num, prev_mtd_open_num)}）    
期权AUM：{float(curr_mtd_asset_hkd) / 1e4:.2f}万（{format_change_rate(curr_mtd_asset_hkd, prev_mtd_asset_hkd)}）    
创收：{float(curr_mtd_revenue_hkd) / 1e4:.2f}万（{format_change_rate(curr_mtd_revenue_hkd, prev_mtd_revenue_hkd)}）    
交易总人数：{curr_mtd_trade_client_num}（{format_change_rate(curr_mtd_trade_client_num, prev_mtd_trade_client_num)}）    
交易总单数：{curr_mtd_trade_times}（{format_change_rate(curr_mtd_trade_times, prev_mtd_trade_times)}）（线上：{curr_mtd_trade_times_online}，线下：{curr_mtd_trade_times_offline}）   
交易总金额：{float(curr_mtd_trade_amount_hkd) / 1e4:.2f}万（{format_change_rate(curr_mtd_trade_amount_hkd, prev_mtd_trade_amount_hkd)}）    
交易张数前3股票：{curr_mtd_underlying_symbols}
\*\*括号内为环比上月
<br>
### 本周数据
开户数：{curr_wtd_open_num}（{format_change_rate(curr_wtd_open_num, prev_wtd_open_num)}）    
期权AUM：{float(curr_wtd_asset_hkd) / 1e4:.2f}万（{format_change_rate(curr_wtd_asset_hkd, prev_wtd_asset_hkd)}）    
创收：{float(curr_wtd_revenue_hkd) / 1e4:.2f}万（{format_change_rate(curr_wtd_revenue_hkd, prev_wtd_revenue_hkd)}）    
交易总人数：{curr_wtd_trade_client_num}（{format_change_rate(curr_wtd_trade_client_num, prev_wtd_trade_client_num)}）    
交易总单数：{curr_wtd_trade_times}（{format_change_rate(curr_wtd_trade_times, prev_wtd_trade_times)}）（线上：{curr_wtd_trade_times_online}，线下：{curr_wtd_trade_times_offline}）    
交易总金额：{float(curr_wtd_trade_amount_hkd) / 1e4:.2f}万（{format_change_rate(curr_wtd_trade_amount_hkd, prev_wtd_trade_amount_hkd)}）    
交易张数前3股票：{curr_wtd_underlying_symbols}
\*\*括号内为环比上周
<br>
### 当天数据
开户数：{curr_day_open_num}（{format_change_rate(curr_day_open_num, prev_day_open_num)}）    
期权AUM：{float(curr_day_asset_hkd) / 1e4:.2f}万（{format_change_rate(curr_day_asset_hkd, prev_day_asset_hkd)}）    
创收：{float(curr_day_revenue_hkd) / 1e4:.2f}万（{format_change_rate(curr_day_revenue_hkd, prev_day_revenue_hkd)}）    
交易总人数：{curr_day_trade_client_num}（{format_change_rate(curr_day_trade_client_num, prev_day_trade_client_num)}）    
交易总单数：{curr_day_trade_times}（{format_change_rate(curr_day_trade_times, prev_day_trade_times)}）（线上：{curr_day_trade_times_online}，线下：{curr_day_trade_times_offline}）    
交易总金额：{float(curr_day_trade_amount_hkd) / 1e4:.2f}万（{format_change_rate(curr_day_trade_amount_hkd, prev_day_trade_amount_hkd)}）    
交易张数前3股票：{curr_day_underlying_symbols}
\*\*括号内为环比前一天"""

    return {
        "title": message_title,
        "text": message_content,
    }
