import sys
import os

sys.path.append(os.path.dirname(sys.path[0]))  # 上级目录
from utils.db_connect import BaseUtils
from secure_db_client import get_remote_engine
import pandas as pd
import time
import requests
import math
"""计算销量，均值差"""


class CalculateMean(BaseUtils):

    def __init__(self, site_name='us', nums_start=0, nums_step=10000000, year=2026, week=1, flag_uniformity=False):
        super().__init__()
        self.site_name = site_name
        self.engine = self.mysql_connect()
        self.nums_start = nums_start  # limit 开始位置
        self.nums_step = nums_step  # limit 步长
        self.year = year
        self.week = week
        self.year_month = ''
        self.df_sum = pd.DataFrame()
        self.df_repeat = pd.DataFrame()
        self.df_repeat_list = []
        self.cate_list = []

    def send_mes(self, site_name):
        month = time.strftime("%m")
        year = time.strftime("%Y")
        _year_month = f'{year}_{int(month)}'
        with self.engine.begin() as conn:
            update_sql_state = f"""
               UPDATE {site_name}_one_category 
                   SET STATE=4 
                   WHERE `name` IN (
                       SELECT `name` FROM (
                           SELECT `name` FROM {site_name}_one_category WHERE rank=1 AND orders=0 AND `year_month`="{_year_month}"
                       ) AS temp_table
                   );
               """
            print('update_sql_state:', update_sql_state)
            conn.execute(update_sql_state)

    def db_read_data(self):
        month = time.strftime("%m")
        year = time.strftime("%Y")
        _year_month = f'{year}_{int(month)}'
        print(_year_month)
        print(f"读取 {self.site_name}_one_category")

        # # ---- 6 月份 ----
        # sql_6 = f"""
        #     SELECT * from {self.site_name}_one_category WHERE id in (  select max(id) from {self.site_name}_one_category where  `year_month`='2026_5' and orders=0 and rank>50000  GROUP BY `name`)
        #             UNION
        #             select * from {self.site_name}_one_category where `year_month`='2026_8'  and rank<=50000
        #     """
        # print('查询原始表6：', sql_6)
        # self.df_sum_6 = self.engine.read_sql(sql_6)
        #
        # # ---- 7 月份 ----
        # sql_7 = f"""
        #      SELECT * from {self.site_name}_one_category WHERE id in (  select max(id) from {self.site_name}_one_category where  `year_month`='2026_5' and orders=0 and rank>50000  GROUP BY `name`)
        #             UNION
        #             select * from {self.site_name}_one_category where `year_month`='2026_9'  and rank<=50000
        #     """
        # print('查询原始表7：', sql_7)
        # self.df_sum_7 = self.engine.read_sql(sql_7)
        #
        # # 合并后直接靠 keep='last' 留 7 月
        # self.df_sum = pd.concat([self.df_sum_6, self.df_sum_7], ignore_index=True)
        # print("合并前总行数:", len(self.df_sum))
        # self.df_sum.drop_duplicates(subset=['rank', 'name'], keep='last', inplace=True)
        # print("合并去重后总行数:", len(self.df_sum))

        print(f"读取 {self.site_name}_one_category")
        sql = f"select * from {self.site_name}_one_category where `year_month`='{_year_month}';"
        print('查询原始表：', sql)
        self.df_sum = self.engine.read_sql(sql)
        print("self.df_sum.shape1:", self.df_sum.shape)
        time.sleep(2)
        # 排序、后续处理
        self.df_sum.sort_values(by=['name', 'rank'], inplace=True)
        # # 删除重复行，保留最后一行
        self.df_sum.drop_duplicates(['name', 'rank'], keep='last', inplace=True)
        print("self.df_sum.shape2:", self.df_sum.shape)
        print(self.df_sum[['year_month', 'rank', 'orders']].head(10).to_string())
        self.cate_list = list(set(self.df_sum.name))
        sql_select = f"SELECT `year_month` from selection.week_20_to_30 WHERE `week`={int(self.week)} and `year`={self.year}"
        print(sql_select, 'sql_select:')
        df = self.engine.read_sql(sql_select)
        self.year_month = list(df['year_month'])[0] if list(df['year_month']) else ''
        print("self.year_month:", self.year_month)
        time.sleep(2)
        self.handle_data()


    def handle_data(self):#旧代码
        print(len(self.cate_list))
        for cate_name in self.cate_list:
            index_ = self.cate_list.index(cate_name)
            df = self.df_sum.loc[self.df_sum.name == cate_name]
            df['rate'] = 1
            data_list = []
            rank_list = list(df['rank'])
            orders_list = list(df['orders'])
            rate_list = list(df['rate'])
            for rank, orders, rate in zip(rank_list, orders_list, rate_list):
                print("index:", index_, cate_name, rank, orders, rate)
                if orders == 0:
                    break
                index = rank_list.index(rank)
                print(index,'2323232323232323ddd')
                if index + 1 < len(rank_list):
                    rank_next = rank_list[index + 1]
                    orders_next = orders_list[index + 1]
                    rate_next = rate_list[index + 1]
                    rank_diff = rank_next - rank
                    orders_diff = orders - orders_next
                    rate_diff = rate - rate_next
                    orders_avg = orders_diff / rank_diff
                    rate_avg = rate_diff / rank_diff
                    rank_range_list = list(range(rank, rank_next, 1))
                    for rank_range in rank_range_list:
                        print(rank_range,'233333wewewewe')
                        index_range = rank_range_list.index(rank_range)
                        s = orders - orders_avg * index_range
                        r = rate - rate_avg * index_range
                        data_list.append([rank_range, s, r])
                else:
                    data_list.append([rank, orders, rate])
            print("data_list:", len(data_list))
            df_repeat = pd.DataFrame(data_list, columns=['rank', 'orders', 'rate'])
            df_repeat['name'] = cate_name
            print(df_repeat.head(10))
            self.df_repeat_list.append(df_repeat)
        self.df_repeat = pd.concat(self.df_repeat_list)
        self.df_repeat.drop(columns=['rate'], inplace=True)
        self.df_repeat['orders_day'] = self.df_repeat.orders.apply(lambda x: math.ceil(x / 30))
        self.df_repeat['year_month'] = self.year_month
        self.df_repeat['week'] = self.week

    def db_save_data(self):
        with self.engine.begin() as conn:
            print(f"delete from {self.site_name}_one_category_report where `year_month`='{self.year_month}';")
            conn.execute(f"delete from {self.site_name}_one_category_report where `year_month`='{self.year_month}';")

        sql = f"select en_name as name, category_id from {self.site_name}_bs_category where 1 = 1  and nodes_num = 2 group by en_name, category_id"
        print('sql',sql)
        df_en_name = self.engine.read_sql(sql)
        # 使用 merge 判断两个列的 name 是否一样
        self.df_repeat = pd.merge(self.df_repeat, df_en_name, on='name', how='left')
        self.df_repeat = self.df_repeat.loc[self.df_repeat.orders >= 30]  # 保留大于0的 排名月销
        self.df_repeat.drop_duplicates(['name', 'rank','orders'], inplace=True)  # 去重
        self.engine.to_sql(self.df_repeat,f"{self.site_name}_one_category_report",if_exists="append")

    def run(self):
        self.db_read_data()
        self.handle_data()
        self.db_save_data()

    def sendMessage(self, week, site_name):
        engine_us_mysql =  get_remote_engine(
        site_name='us',  # -> database "selection"
        db_type="mysql",  # -> 服务端 alias "mysql"
    )
        with engine_us_mysql.begin() as conn:

            time_strftime = time.strftime("%Y-%m-%d %X", time.localtime())
            update_workflow_progress = f"update workflow_progress set status_val=3,status='抓取结束' where page='ASIN销量' and date_info='2026-{week}' and site_name='{site_name}' and date_type='week'"
            print(update_workflow_progress)
            conn.execute(update_workflow_progress)

        url = 'http://47.112.96.71:8082/selection/sendMessage'
        data = {
            'account': 'pengyanbing,fangxingjun',
            'title': f"{site_name} 站点类目销量统计",
            'content': str(self.week) + f' 周  {site_name}站点类目销量计算 已结束,请确认下一步流程!时间:' + time_strftime
        }
        try:
            requests.post(url=url, data=data, timeout=15)
        except:
            pass


if __name__ == '__main__':
    week = time.strftime("%W")
    # week = '04'
    print("week  周：", week)
    time.sleep(2)
    handle_obj_us = CalculateMean(site_name='us', year=2026, week=week)
    handle_obj_us.run()
    handle_obj_us.sendMessage(week, site_name='us')
    # handle_obj_uk = CalculateMean(site_name='uk', year=2026, week=week)
    # handle_obj_uk.run()
    # handle_obj_uk.sendMessage(week, site_name='uk')
    # handle_obj_de = CalculateMean(site_name='de', year=2026, week=week)
    # handle_obj_de.run()
    # handle_obj_de.sendMessage(week, site_name='de')
    # handle_obj_fr = CalculateMean(site_name='fr', year=2026, week=week)
    # handle_obj_fr.run()
    # handle_obj_fr.sendMessage(week, site_name='fr')
    # handle_obj_es = CalculateMean(site_name='es', year=2026, week=week)
    # handle_obj_es.run()
    # handle_obj_es.sendMessage(week, site_name='es')
    # handle_obj_it = CalculateMean(site_name='it', year=2026, week=week)
    # handle_obj_it.run()
    # handle_obj_it.sendMessage(week, site_name='it')
