import sys
import os
import time
import traceback

import pandas as pd

sys.path.append(os.path.dirname(sys.path[0]))  # 上级目录
# from utils.templates import Templates
# from ..utils.templates import Templates
from utils.db_util import DbTypes, DBUtil
from urllib.parse import quote


class ImportStToPg14(object):

    def __init__(self, site_name='us', date_type='month', date_info='2024-01'):
        self.site_name = site_name
        self.date_type = date_type
        self.date_info = date_info
        self.engine_mysql = DBUtil.get_db_engine(db_type=DbTypes.mysql.name, site_name=self.site_name)
        self.engine_pg14 = DBUtil.get_db_engine(db_type=DbTypes.postgresql_14.name, site_name=self.site_name)
        self.df_st_week = pd.DataFrame()
        self.df_st_month = pd.DataFrame()
        self.df_st_month_state = pd.DataFrame()
        self.df_save = pd.DataFrame()
        # self.fetch_year_month_by_week()  # 如果传的date_type='week', 将date_info转换成月的值
        self.site_name_url_dict = {
            "us": 'https://www.amazon.com/',
            "uk": 'https://www.amazon.co.uk/',
            "de": 'https://www.amazon.de/',
            "es": 'https://www.amazon.es/',
            "fr": 'https://www.amazon.fr/',
            "it": 'https://www.amazon.it/',
        }
        self.site_name_pri_dict = {
            "us": 1,
            "uk": 3,
            "de": 5,
        }
        self.fetch_year_month_by_week()  # 如果传的date_type='week', 将date_info转换成月的值
        self.year, self.month = self.date_info.split("-")[0], int(self.date_info.split("-")[1])

    def fetch_year_month_by_week(self):
        if self.date_type == 'week':
            sql = f"select `year_month` from selection.date_20_to_30 WHERE year_week='{self.date_info}' and week_day=1;"
            df = pd.read_sql(sql, con=self.engine_mysql)
            self.date_info = list(df.year_month)[0]

    def delete_dirty_data(self):
        print(f"删除脏数据, 防止失败执行时报错")
        from datetime import datetime

        today_str = datetime.now().strftime("%Y-%m-%d")
        with self.engine_pg14.begin() as conn:
            sql_delete = f"delete from {self.site_name}_search_term_month where (date_info='{self.date_info}' and created_time>='{today_str} 00:00:00') or (date_info<'{self.date_info}');"
            print(f"sql_delete--{self.site_name}_search_term_month:", sql_delete)
            conn.execute(sql_delete)

            sql_delete = f"delete from {self.site_name}_search_term_month_syn where (date_info='{self.date_info}' and state=1 and created_time>='{today_str} 00:00:00') or (date_info<'{self.date_info}');"
            print(f"sql_delete--{self.site_name}_search_term_month_syn:", sql_delete)
            conn.execute(sql_delete)

    def read_data(self):
        self.delete_dirty_data()
        # 1. 读取date_20_to_30表获取月份对应的周
        sql_get_week = f"select year_week, year, week from selection.date_20_to_30 WHERE `year_month`='{self.date_info}' and week_day=1"
        df_week = pd.read_sql(sql_get_week, con=self.engine_mysql)
        print(df_week.shape, "sql_get_week:", sql_get_week)
        # 2. 读取月对应周的搜索词, 并去重
        df_st_week_list = []
        for year, week in zip(df_week.year, df_week.week):
            sql = f"select DISTINCT(search_term) from {self.site_name}_brand_analytics_{year} WHERE `week`={week} limit 1500000;"
            df_st_week = pd.read_sql(sql, con=self.engine_mysql)
            print(year, week, df_st_week.shape, "sql:", sql)
            df_st_week_list.append(df_st_week)
        self.df_st_week = pd.concat(df_st_week_list)
        # 3. 读取mysql的月搜索词表
        sql_get_month = f"SELECT DISTINCT(search_term) from {self.site_name}_brand_analytics_month_{self.year} WHERE year={self.year} and `month`={self.month};"
        self.df_st_month = pd.read_sql(sql_get_month, con=self.engine_mysql)
        print(self.df_st_month.shape, "sql_get_month:", sql_get_month)
        # 4. 读取pg14的搜索词状态表
        sql_get_month_state = f"SELECT DISTINCT(search_term) from {self.site_name}_search_term_month WHERE date_info='{self.date_info}' and state !=1;"
        print("sql_get_month_state:", sql_get_month_state)
        self.df_st_month_state = pd.read_sql(sql_get_month_state, con=self.engine_pg14)
        print(self.df_st_month_state.shape, "sql_get_month_state:", sql_get_month_state)

    def handle_data(self):
        self.df_save = pd.concat([self.df_st_week, self.df_st_month])
        self.df_save = self.df_save.drop_duplicates(['search_term'])
        df_count_before = self.df_save.shape[0]
        self.df_save = self.df_save.loc[~self.df_save.search_term.isin(self.df_st_month_state.search_term)]
        self.df_save['month'] = self.month
        self.df_save['date_info'] = self.date_info
        df_count_after = self.df_save.shape[0]
        print(f"df_count_before:{df_count_before}, df_count_after:{df_count_after}")

    def handle_data_sync(self):
        result_list = []
        for search_term in self.df_save.search_term:
            urls = self.build_urls(search_term)
            result_list.extend(urls)

        # 创建初始 DataFrame
        df_search_term = pd.DataFrame(data=result_list, columns=['search_term', 'url'])
        print(df_search_term.shape)
        before_count = len(df_search_term)

        df_search_term = df_search_term.loc[
            df_search_term["url"].fillna("").astype(str).str.len() <= 450
            ].copy()

        after_count = len(df_search_term)

        print(f"search_term长度过滤：过滤前 {before_count}，过滤后 {after_count}，过滤掉 {before_count - after_count}")
        df_search_term['month'] = self.month
        df_search_term['date_info'] = self.date_info
        return df_search_term

    def save_data(self):


        print(f"存储{self.site_name}_search_term_month: {self.df_save.shape}")
        self.df_save.to_sql(f"{self.site_name}_search_term_month", con=self.engine_pg14, index=False,
                            if_exists="append")
        # pass

        df_search_term_sync = self.handle_data_sync()
        print(f"存储{self.site_name}_search_term_month_syn: {df_search_term_sync.shape}")
        df_search_term_sync.to_sql(f"{self.site_name}_search_term_month_syn", con=self.engine_pg14, index=False,
                                   if_exists="append")

        if df_search_term_sync.shape[0] == 0:
            print(f"当前没有新增的搜索词同步, 不需要更改进度表， 退出程序")
            quit()
        # 更改workflow_manager进度表
        self.update_workflow_manager()

        with self.engine_pg14.begin() as conn:
            # sql_delete = f"delete from {self.site_name}_search_term_month_syn where (date_info='{self.date_info}' and state=1) or (date_info<'{self.date_info}');"
            # print(f"sql_delete--{self.site_name}_search_term_month_syn:", sql_delete)
            # conn.execute(sql_delete)

            sql_update = f"update {self.site_name}_search_term_month set state=3 where date_info='{self.date_info}' and state=1"
            print(f"sql_update--{self.site_name}_search_term_month:", sql_update)
            conn.execute(sql_update)

    # 构建 URL 的函数
    def build_urls(self, search_term):
        site_name_url = self.site_name_url_dict[self.site_name]
        url_template = f"{site_name_url}s?k={{search_term}}&page={{page_number}}"
        search_term_chinese = quote(search_term, 'utf-8')
        search_term_chinese = search_term_chinese.replace("'", '%27').replace("/", '%2F')
        urls = [
            url_template.format(
                search_term=search_term_chinese.replace(' ', '+').replace('&', '%26').replace('#', '%23').replace(
                    '(',
                    '%28').replace(
                    ')', '%29'), page_number=1),
            url_template.format(
                search_term=search_term_chinese.replace(' ', '+').replace('&', '%26').replace('#', '%23').replace(
                    '(',
                    '%28').replace(
                    ')', '%29'), page_number=2),
            url_template.format(
                search_term=search_term_chinese.replace(' ', '+').replace('&', '%26').replace('#', '%23').replace(
                    '(',
                    '%28').replace(
                    ')', '%29'), page_number=3)
        ]
        return [[search_term, url] for url in urls]

    def update_workflow_manager(self):
        with self.engine_mysql.begin() as conn:
            priority = self.site_name_pri_dict[self.site_name]
            spider_script = f'ansible dabing_all -f 10 -m shell -a "nohup /usr/local/bin/python3 /mnt/py_spider/threading_spider/Poll_site_search_term_month.py {self.site_name} {self.date_info} >/dev/null 2>&1 &";'
            update_sql_workflow = f"""
                                    INSERT INTO selection.workflow_manager
                    (
                        workflow_name,
                        site_name,
                        date_type,
                        date_info,
                        priority,
                        spider_name,
                        spider_script,
                        spider_is_ready,
                        spider_state,
                        bg_name,
                        bg_dol_name,
                        bg_dol_state
                    )
                    VALUES
                    (
                        '月全流程',
                        '{self.site_name}',
                        'month',
                        '{self.date_info}',
                        {priority},
                        '{self.site_name}_spider_st',
                        '{spider_script}',
                        'yes',
                        1,
                        '{self.site_name}_asin_export', 
                        'ALL站点-asin同步-pg-api',
                        1
                    )
                    ON DUPLICATE KEY UPDATE
                        spider_is_ready = VALUES(spider_is_ready), 
                        spider_script = VALUES(spider_script), 
                        bg_dol_state = VALUES(bg_dol_state), 
                        spider_state = VALUES(spider_state);
            """
            print(f"workflow_manager进度表---重置爬虫的搜索词抓取进度: {update_sql_workflow}")
            conn.execute(update_sql_workflow)

    def run(self, num=0):
        while num <= 5:
            try:
                self.read_data()
                self.handle_data()
                self.save_data()
                break
            except Exception as e:
                print(f"搜索词导入到pg14失败：{self.site_name}-{self.date_type}--{self.date_info}, 报错信息: {traceback.format_exc()}")
                self.engine_mysql = DBUtil.get_db_engine(db_type=DbTypes.mysql.name, site_name=self.site_name)
                self.engine_pg14 = DBUtil.get_db_engine(db_type=DbTypes.postgresql_14.name, site_name=self.site_name)
                time.sleep(60)
                num += 1
                continue


if __name__ == '__main__':
    site_name = sys.argv[1]  # 参数1：站点
    date_type = sys.argv[2]  # 参数2：类型：month
    date_info = sys.argv[3]  # 参数3：月对应的值： 2024-01
    handle_obj = ImportStToPg14(site_name=site_name, date_type=date_type, date_info=date_info)
    if site_name in ['us', 'uk', 'de']:
        handle_obj.run()
    else:
        print(f"小站点不需要同步爬虫表, 当前站点: {site_name}")
