import os
import numpy as np
import pandas as pd
from environs import Env
from render import Render
from utils import send_lark_message
from datetime import datetime, timedelta


env = Env()
env.read_env()

r = Render(os.path.join(os.path.dirname(__file__), 'weekly.yaml'), env.str("TRINO_HOST"))
text = '## 整体日账单\n'

begin_date = (datetime.now() - timedelta(days=9)).strftime('%Y-%m-%d')
end_date = (datetime.now() - timedelta(days=3)).strftime('%Y-%m-%d')

with r.context(begin_date=begin_date, end_date=end_date):
    text += f'最近 7 天（{begin_date} - {end_date}）整体账单趋势：\n'
    text += f'- 7 天账单均值：${r.sum_avg:,.2f}，环比（${r.prev_sum_avg:,.2f}）: {r.sum_avc_inc_percent:+.2f}%\n'
    text += f'- 最高值：在 {r.max_daily_cost_date}, ${r.max_daily_cost_value:,.2f}，'
    text += f'相比上周期（${r.prev_max_daily_cost_value:,.2f}）'
    text += f'{(r.max_daily_cost_value-r.prev_max_daily_cost_value)/r.prev_max_daily_cost_value*100:+.2f}%。'
    text += f'最低值：在 {r.min_daily_cost_date}，${r.min_daily_cost_value:,.2f}，'
    text += f'相比上周期（${r.prev_min_daily_cost_value:,.2f}）'
    text += f'{(r.min_daily_cost_value-r.prev_min_daily_cost_value)/r.prev_min_daily_cost_value*100:+.2f}% \n'
    text += f'- 七天内的日账单详情:\n{r.sum_cost_every_day}\n'

    text += '## 分摊到每个租户的日账单\n'
    text += f'最近 7 天（{begin_date} - {end_date}）分租户的账单趋势：\n'
    text += f'- 总共 {r.paid_tenant_count} 名租户有账单，环比 {r.paid_tenant_inc_count:+} 名\n'
    if len(r.dataframe('leave_paid_customers')) > 0:
        text += '  - 减少的租户是：' + ','.join(r.dataframe('leave_paid_customers').tenant_name) + '\n'
    if len(r.dataframe('new_paid_customers')) > 0:
        text += '  - 增加的租户是：' + ','.join(r.dataframe('new_paid_customers').tenant_name) + '\n'

    with r.context(tenant_id=r.biggest_customer_id):
        text += '\n'
        text += f'- 占比最高的租户是 {r.customer_name}\n'
        text += f'  - 日均值：{r.customer_daily_avg_percent:.2f}%，期末-期初变化 {r.customer_daily_percent_diff:+.2f}%\n'
        text += f'  - 最大值：在 {r.customer_max_daily_percent_date}, {r.customer_max_daily_percent_value:.2f}%，'
        text += f'最小值：在 {r.customer_min_daily_percent_date}, {r.customer_min_daily_percent_value:.2f}%\n'

    if r.biggest_customer_id != r.potential_customer_id:
        with r.context(tenant_id=r.potential_customer_id):
            text += f'- 增长最快的租户是 {r.customer_name}\n'
            text += f'  - 日均值：{r.customer_daily_avg_percent:.2f}%，期末-期初变化 {r.customer_daily_percent_diff:+.2f}%\n'
            text += f'  - 最大值：在 {r.customer_max_daily_percent_date}, {r.customer_max_daily_percent_value:.2f}%，'
            text += f'最小值：在 {r.customer_min_daily_percent_date}, {r.customer_min_daily_percent_value:.2f}%\n'

    text += '- 新租户（创建时间 180 天内），最近 7 天账单情况：\n'
    for _, row in r.dataframe('new_custermers').iterrows():
        with r.context(tenant_id=row.tenant_id):
            text += f'  - {r.customer_name}, 创建时间: {row.tenant_created_at}，平均占比：{r.customer_sum_percent:.2f}%，'
            text += f'环比（{r.customer_prev_sum_percent:.2f}%）{r.customer_sum_percent-r.customer_prev_sum_percent:+.2f}%\n'

    text += '## 关键租户的账单\n'
    text += '最近 7 天 top 5 账单的租户\n'

    for _, row in r.dataframe('top_5_customers').iterrows():
        with r.context(tenant_id=row.billing_tenant_id):
            text += '\n'
            text += f'### {r.customer_name}\n'
            
            text += f'#### 日账单\n'
            text += f'最近 7 天（{begin_date} - {end_date}）整体账单趋势：\n'
            text += f'- 7 天账单均值：${r.customer_sum_avg:,.2f}，环比（${r.prev_customer_sum_avg:,.2f}）'
            text += f'{(r.customer_sum_avg-r.prev_customer_sum_avg)/r.prev_customer_sum_avg*100:+.2f}%\n'
            text += f'- 最高值：在 {r.customer_max_daily_cost_date}，${r.customer_max_daily_cost_value:,.2f}，相比上周期（${r.prev_customer_max_daily_cost_value:,.2f}）'
            text += f'{(r.customer_max_daily_cost_value-r.prev_customer_max_daily_cost_value)/r.prev_customer_max_daily_cost_value*100:+.2f}%。'
            text += f'最低值：在 {r.customer_min_daily_cost_date}，${r.customer_min_daily_cost_value:,.2f}，相比上周期（${r.prev_customer_min_daily_cost_value:,.2f}）'
            text += f'{(r.customer_min_daily_cost_value-r.prev_customer_min_daily_cost_value)/r.prev_customer_min_daily_cost_value*100:+.2f}%。\n'
            text += f'- 七天内的日账单详情:\n{r.customer_sum_cost_every_day}\n'
   
            text += f'\n最近 7 天（{begin_date} - {end_date}）分 project 的趋势：\n'
            text += f'- 总共有 {r.customer_billing_project_count} 个 project 产生费用，环比（{r.customer_prev_billing_project_count}）'
            text += f'变化 {r.customer_billing_project_count-r.customer_prev_billing_project_count:+} 个\n'
            
            if len(r.dataframe('customer_missing_projects')) > 0:
                text += '  - 减少的 project 是：' + ','.join(r.dataframe('customer_missing_projects').billing_project_name) + '\n'

            text += '- Top 3 Projects\n'
            for idx, prow in r.dataframe('customer_top_3_projects').iterrows():
                with r.context(project_id=prow.billing_project_id):
                    text += f'  - No.{idx+1}: Project Name: {prow.billing_project_name}\n'
                    text += f'    - 7 天账单均值：${r.project_sum_avg:,.2f}，环比（${r.project_prev_sum_avg:,.2f}) '
                    text += f' {(r.project_sum_avg-r.project_prev_sum_avg)/r.project_prev_sum_avg*100:+.2f}%\n'
                    text += f'    - 最高值：在 {r.project_max_daily_cost_date}，${r.project_max_daily_cost_value:,.2f}，'
                    text += f'相比上周期（${r.prev_project_max_daily_cost_value:,.2f}）'
                    text += f'{(r.project_max_daily_cost_value-r.prev_project_max_daily_cost_value)/r.prev_project_max_daily_cost_value*100:+.2f}%。'
                    text += f'最低值：在 {r.project_min_daily_cost_date}，${r.project_min_daily_cost_value:,.2f}，相比上周期'
                    text += f'（{r.prev_project_min_daily_cost_value:,.2f}）'
                    text += f'{(r.project_min_daily_cost_value-r.prev_project_min_daily_cost_value)/r.prev_project_min_daily_cost_value*100:+.2f}%。\n'
                    text += f'    - 七天内的日账单详情:\n{r.project_sum_cost_every_day}\n'

            if len(r.dataframe('customer_new_projects')) > 0:
                text += '- New Projects\n'
                for _, prow in r.dataframe('customer_new_projects').iterrows():
                    text += '\n'
                    with r.context(project_id=prow.billing_project_id):
                        if not r.project_sum_avg:
                            continue
                        text += f'  - Project Name: {prow.billing_project_name}\n'
                        text += f'    - 7 天账单均值：${r.project_sum_avg:,.2f}\n'
                        text += f'    - 最高值：在 {r.project_max_daily_cost_date}，${r.project_max_daily_cost_value:,.2f}，'
                        text += f'最低值：在 {r.project_min_daily_cost_date}，${r.project_min_daily_cost_value:,.2f}\n'

            if len(r.dataframe('customer_disabled_projects')) > 0:
                text += '- Disable Projects\n'
                for _, prow in r.dataframe('customer_disabled_projects').iterrows():
                    text += '\n'
                    with r.context(project_id=prow.billing_project_id):
                        text += f'  - Project Name: {prow.billing_project_name}，费用消失时间：在 {prow.disabled_date}\n'
                        
            text += f'\n最近 7 天（{begin_date} - {end_date}）分账单单项的趋势：\n'
            text += '- Top 3 Services\n'
            for idx, srow in r.dataframe('customer_top_3_service').iterrows():
                with r.context(unit=srow.billing_unit):
                    text += f'  - No.{idx+1}: {srow.billing_unit}\n'
                    text += f'    - 7 天账单均值：${r.customer_unit_sum_avg:,.2f}，环比（${r.prev_customer_unit_sum_avg:,.2f}）'
                    text += f'{(r.customer_unit_sum_avg-r.prev_customer_unit_sum_avg)/r.prev_customer_unit_sum_avg*100:+.2f}%\n'
                    text += f'    - 最高值：在 {r.customer_unit_max_daily_cost_date}，${r.customer_unit_max_daily_cost_value:,.2f}，'
                    text += f'相比上周期（${r.prev_customer_unit_max_daily_cost_value:,.2f}）'
                    text += f'{(r.customer_unit_max_daily_cost_value-r.prev_customer_unit_max_daily_cost_value)/r.prev_customer_unit_max_daily_cost_value*100:+.2f}%。'
                    text += f'最低值：在 {r.customer_unit_min_daily_cost_date}，${r.customer_unit_min_daily_cost_value:,.2f}，'
                    text += f'相比上周期（${r.prev_customer_unit_min_daily_cost_value:,.2f}）'
                    text += f'{(r.customer_unit_min_daily_cost_value-r.prev_customer_unit_min_daily_cost_value)/r.prev_customer_unit_min_daily_cost_value*100:+.2f}%。\n'
                    text += f'    - 七天内的日账单详情:\n{r.customer_unit_sum_cost_every_day}\n'

            if len(r.dataframe('customer_new_units')) > 0:
                text += '- New Services\n'
                for _, srow in r.dataframe('customer_new_units').iterrows():
                    text += '\n'
                    with r.context(unit=srow.billing_unit):
                        text += f'  - Service Name: {srow.billing_unit}，出现时间：在 {srow.billing_cost_date}\n'
                        text += f'    - 7 天账单均值：${r.customer_unit_sum_avg:,.2f}。\n'
                        text += f'    - 最高值：在 {r.customer_unit_max_daily_cost_date}，${r.customer_unit_max_daily_cost_value:,.2f}。'
                        text += f'最低值：在 {r.customer_unit_min_daily_cost_date}，${r.customer_unit_min_daily_cost_value:,.2f}。\n'
            
            if len(r.dataframe('customer_disabled_units')) > 0:
                text += '- Disabled Services\n'
                for _, srow in r.dataframe('customer_disabled_units').iterrows():
                    text += '\n'
                    with r.context(unit=srow.billing_unit):
                        text += f'  - Service Name: {srow.billing_unit}，费用消失时间：在 {srow.disabled_date}\n'

            text += f'#### 每个 Project 账单\n'
            for _, prow in r.dataframe('customer_top_3_projects').iterrows():
                text += '\n'
                with r.context(project_id=prow.billing_project_id):
                    text += f'最近 7 天（{begin_date} - {end_date}）project {prow.billing_project_name} 的趋势：\n'
                    text += '- Top 3 Service\n'
                    for idx, srow in r.dataframe('project_top_3_service').iterrows():
                        with r.context(unit=srow.billing_unit):
                            text += f'  - No.{idx+1}: Service Name: {srow.billing_unit}\n'
                            text += f'    - 7 天账单均值：${r.project_unit_sum_avg:,.2f}，环比（${r.prev_project_unit_sum_avg:,.2f}） '
                            text += f'{(r.project_unit_sum_avg-r.prev_project_unit_sum_avg)/r.prev_project_unit_sum_avg*100:+.2f}%\n'
                            text += f'    - 最高值：在 {r.project_unit_max_daily_cost_date}，${r.project_unit_max_daily_cost_value:,.2f}，'
                            text += f'相比上周期（${r.prev_project_unit_max_daily_cost_value:,.2f}） '
                            text += f'{(r.project_unit_max_daily_cost_value-r.prev_project_unit_max_daily_cost_value)/r.prev_project_unit_max_daily_cost_value*100:+.2f}%。'
                            text += f'最低值：在 {r.project_unit_min_daily_cost_date}，${r.project_unit_min_daily_cost_value:,.2f}，相比上周期'
                            text += f'（${r.prev_project_unit_min_daily_cost_value:,.2f}） '
                            text += f'{(r.project_unit_min_daily_cost_value-r.prev_project_unit_min_daily_cost_value)/r.prev_project_unit_min_daily_cost_value*100:+.2f}%。\n'
                    
                    if len(r.dataframe('project_new_units')) > 0:
                        text += '- New Services\n'
                        for _, srow in r.dataframe('project_new_units').iterrows():
                            with r.context(unit=srow.billing_unit):
                                text += f'  - Service Name: {srow.billing_unit}，出现时间：在 {srow.billing_cost_date}\n'
                                text += f'    - 7 天账单均值：${r.project_unit_sum_avg:,.2f}。\n'
                                text += f'    - 最高值：在 {r.project_unit_max_daily_cost_date}，${r.project_unit_max_daily_cost_value:,.2f}。'
                                text += f'最低值：在 {r.project_unit_min_daily_cost_date}，${r.project_unit_min_daily_cost_value:,.2f}。\n'

                    if len(r.dataframe('project_disabled_units')) > 0:
                        text += '- Disabled Services\n'
                        for _, srow in r.dataframe('project_disabled_units').iterrows():
                            with r.context(unit=srow.billing_unit):
                                text += f'  - Service Name: {srow.billing_unit}，费用消失时间：在 {srow.disabled_date}\n'

send_lark_message(env, text, env.str('WEEKLY_CHAT_ID'))