import sys
import os

sys.path.append(os.path.dirname(sys.path[0]))  # 上级目录
from utils.db_connect import BaseUtils
from amazon_params.params import DB_CONN_DICT
import math
import pandas as pd
import time
import pymysql
import requests

"""计算销量，均值差"""


class CalculateMean(BaseUtils):

    def __init__(self, site_name='us', nums_start=0, nums_step=10000000, year=2025, 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")
        sql = f"select * from {self.site_name}_one_category where state!=4 and name = 'Health & Household' and `year_month`='{_year_month}';"
        print('查询原始表：', sql)
        self.df_sum = self.engine.read_sql(sql)
        # # 排序
        self.df_sum.sort_values(by=['name', 'rank'], inplace=True)

        print("self.df_sum.shape1:", self.df_sum.shape)
        # # 删除重复行，保留最后一行
        self.df_sum.drop_duplicates(['name', 'rank'], keep='last', inplace=True)
        print("self.df_sum.shape2:", self.df_sum.shape)
        print(self.df_sum.values.tolist())
        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)

    def handle_data(self):
        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)
                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:
                        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)
        time.sleep(4444)
        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_pyb where `year_month`='{self.year_month}';")
            conn.execute(f"delete from {self.site_name}_one_category_report_pyb where `year_month`='{self.year_month}';")

        # sql = f'select en_name as name,category_id from  {self.site_name}_bs_category WHERE nodes_num =2 and delete_time is NULL'
        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"
        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 >= 1]  # 保留大于0的 排名月销
        self.engine.to_sql(self.df_repeat,f"{self.site_name}_one_category_report_pyb", if_exists="append")

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

    def sendMessage(self, week, site_name):
        db = pymysql.connect(host=DB_CONN_DICT['mysql_host'], port=DB_CONN_DICT['mysql_port'],
                             user=DB_CONN_DICT['mysql_user'],
                             password=DB_CONN_DICT['mysql_pwd'],
                             database='selection', charset="utf8mb4")
        cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
        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='2025-{week}' and site_name='{site_name}' and date_type='week'"
        print(update_workflow_progress)
        cursor.execute(update_workflow_progress)
        db.commit()
        cursor.close()
        db.close()
        url = 'http://47.112.96.71:8082/selection/sendMessage'
        data = {
            'account': 'pengyanbing,fangxingjun,wangrui4',
            '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=2025, week=week)
    handle_obj_us.run()
    # handle_obj_us.sendMessage(week, site_name='us')
    # handle_obj_uk = CalculateMean(site_name='uk', year=2025, week=week)
    # handle_obj_uk.run()
    # handle_obj_uk.sendMessage(week, site_name='uk')
    # handle_obj_de = CalculateMean(site_name='de', year=2025, week=week)
    # handle_obj_de.run()
    # handle_obj_de.sendMessage(week, site_name='de')
    # handle_obj_fr = CalculateMean(site_name='fr', year=2025, week=week)
    # handle_obj_fr.run()
    # handle_obj_fr.sendMessage(week, site_name='fr')
    # handle_obj_es = CalculateMean(site_name='es', year=2025, week=week)
    # handle_obj_es.run()
    # handle_obj_es.sendMessage(week, site_name='es')
    # handle_obj_it = CalculateMean(site_name='it', year=2025, week=week)
    # handle_obj_it.run()
    # handle_obj_it.sendMessage(week, site_name='it')
