import sys
import os

sys.path.append(os.path.dirname(sys.path[0]))  # 上级目录
from utils.secure_db_client import get_remote_engine
from amazon_params.params import DORIS_ADV_DIRECT_CONN
import pymysql
import platform
import datetime
import redis
import time
from collections import Counter
import json
REDIS_CONN = {
     "redis_host": "113.100.143.162",
    "redis_port": 54372,
    "redis_pwd": "N8#rTp2Xz!Lk6@Vw9qHs4&Yb1Fm0Cj3",
    "redis_db": 14

}


def update_state(site):
    engine = get_remote_engine(
        site_name=site, # -> database "selection"
        db_type="mysql",  # -> 服务端 alias "mysql"
    )
    engine_spider = get_remote_engine(
        site_name=site,  # -> database "selection"
        db_type="postgresql_14_outer",  # -> 服务端 alias "mysql"
    )
    engine_pg6 = get_remote_engine(
        site_name='us',  # -> database "selection"
        db_type="postgresql_15_outer",  # -> 服务端 alias "mysql"
    )
    engine_abv = get_remote_engine(
        site_name='us',  # -> database "selection"
        db_type="doris_adv",  # -> 服务端 alias "mysql"
        database="advertising_manager",  # -> 服务端 alias "mysql"
    )
    now_time = datetime.datetime.now()
    up_time = (now_time + datetime.timedelta(hours=-0.30)).strftime("%Y-%m-%d %H:%M:%S")
    up_1_time = (now_time + datetime.timedelta(hours=-0.30)).strftime("%Y-%m-%d %H:%M:%S")
    up_sp_time = (now_time + datetime.timedelta(hours=-0.25)).strftime("%Y-%m-%d %H:%M:%S")
    print(up_time)
    print(up_1_time)
    print(up_sp_time)
    # time.sleep(333)
    if site == "us":
        conn_adv = pymysql.connect(**DORIS_ADV_DIRECT_CONN)
        cursor_adv = conn_adv.cursor()
        sp_search_term_syn_sql = f"update {site}_sp_search_term_syn SET state = 1 where state = 2 and updated_time <'{up_sp_time}'"
        print('sp_search_term_syn_sql:', sp_search_term_syn_sql)
        cursor_adv.execute(sp_search_term_syn_sql)
        conn_adv.commit()
        sp_search_term_selct_slq = f'select * from {site}_sp_search_term_syn where state in(1,2) limit 1'
        print('sp_search_term_selct_slq:', sp_search_term_selct_slq)
        cursor_adv.execute(sp_search_term_selct_slq)
        row = cursor_adv.fetchone()
        print(row, '243')
        if not row:
            s = datetime.datetime.now().strftime("%Y-%m-%d-%H")
            sp_search_term_syn_sql1 = f"update {site}_sp_search_term_syn SET state = 1, time_batch='{s}'"
            print('sp_search_term_syn_sql1:', sp_search_term_syn_sql1)
            cursor_adv.execute(sp_search_term_syn_sql1)
            conn_adv.commit()
        cursor_adv.close()
        conn_adv.close()
    with engine_spider.begin() as conn_14:
        week = time.strftime("%W")
        if int(week) > 1:
            min_week = int(week) - 1  # 上周
        else:
            min_week = int(week)  # 本周
        week_ = int(week)  # 本周
        if min_week < 10:
            min_week = str(f'0{min_week}')
        if week_ < 10:
            week_ = str(f'0{week_}')

        month = time.strftime("%m")
        if int(month) > 1:
            min_month = int(month) - 1  # 上个月
        else:
            min_month = int(month)
        month_ = int(month)  # 本月

        if min_month < 10:
            min_month = str(f'0{min_month}')
        if month_ < 10:
            month_ = str(f'0{month_}')

        if site in ['us', 'de', 'uk']:
            for i in range(5):
                sql_update_ = f"update {site}_all_syn_st_month_2026_{min_month} SET state = 1 WHERE id IN (  SELECT id  FROM {site}_all_syn_st_month_2026_{min_month}  WHERE state = 2    AND updated_time <'{up_1_time}'  LIMIT 5000);"
                print('sql_update_:', sql_update_)
                conn_14.execute(sql_update_)
                sql_update = f"update {site}_all_syn_st_month_2026_{month_} SET state = 1 WHERE id IN (  SELECT id  FROM {site}_all_syn_st_month_2026_{month_}  WHERE state = 2    AND updated_time <'{up_1_time}'  LIMIT 5000);"
                print('sql_update:', sql_update)
                conn_14.execute(sql_update)
                if site == 'us':
                    day_syn_asin_sql = f"update {site}_all_syn_st_day_2026 SET state = 1 WHERE id IN (  SELECT id  FROM {site}_all_syn_st_day_2026  WHERE state = 2    AND updated_time <'{up_1_time}'  LIMIT 5000)"
                    print('day_syn_asin_sql:', day_syn_asin_sql)
                    conn_14.execute(day_syn_asin_sql)
                    not_asin_list = ["delete from us_seller_asin_product_2026 where seller_id ='A1SBJ94FXX1DDF';",
                                     "delete from us_seller_account_feedback_2026 where seller_id ='A1SBJ94FXX1DDF';",
                     "delete from us_asin_detail_month_2026 where  asin in ('B0BZ92LLZ7','B0BZ8Z8YLX','B0BZ59J19C','B0BYVM5L4B');"
                                     ]
                    for i in not_asin_list:
                        conn_14.execute(i)

            sql_update_month = f"update {site}_search_term_month_syn set state = 1  where state = 2 and updated_time <'{up_time}'"
            print(sql_update_month)

            conn_14.execute(sql_update_month)
            sql_update_chatgpt_syn_st = f"update {site}_chatgpt_syn_st SET state = 1 WHERE id IN (  SELECT id  FROM {site}_chatgpt_syn_st  WHERE state = 2    AND updated_time <'{up_1_time}'  LIMIT 1000);"
            print('sql_update:', sql_update_chatgpt_syn_st)
            conn_14.execute(sql_update_chatgpt_syn_st)

            select_sql = f"select count(id) from {site}_all_syn_st_month_2026_{min_month} where state=1"
            print(select_sql)
            df = engine_spider.read_sql(select_sql)
            if not df.empty:
                id_count_min = df.iloc[0, 0]
            select_sql_1 = f"select count(id) from {site}_all_syn_st_month_2026_{month_} where state=1"
            print(select_sql_1)
            df = engine_spider.read_sql(select_sql_1)
            print(df.values)
            if not df.empty:
                id_count = df.values[0]
            # 更新 所有 实时任务。 长时间爬取中改为未开始
            if platform.system().lower() != 'windows' and site == 'us':
                ai_sql = f"update ai_asin_analyze_spider set status = '未开始' where sub_step = 'AsinInfoRepository:详情' and status = '爬取中' and site_name='{site}'"
                print(ai_sql)
                with engine_pg6.begin() as conn_6:
                    conn_6.execute(ai_sql)
                    if site == 'us':
                        print('修改ai测试状态：',)
                        ai_sql1 = f"update ai_asin_analyze_log set status = '未开始' where  status = '爬取中'  and module = 'Amazon:asin'"
                        ai_sql3 = f"update ai_asin_analyze_log set status = '未开始' where  status = '爬取中' and module = 'Amazon:asinList'"
                        print(ai_sql1)
                        conn_6.execute(ai_sql1)
                        conn_6.execute(ai_sql3)

    with engine.begin() as conn:
        seller_sql = f'update {site}_seller_account_syn_distinct set state = 1 where  state = 2 and updated_at <"{up_time}";'
        print(seller_sql)
        conn.execute(seller_sql)
        seller_sql = f'update {site}_seller_account_syn_distinct set product_state = 1 where  product_state = 2 and updated_at <"{up_time}";'
        print(seller_sql)
        conn.execute(seller_sql)
        if site in ['us', 'de', 'uk']:
            print('us id_count::', id_count_min, id_count)
            if id_count_min > 10000:
                u_sql = f"""UPDATE {site}_syn_asin_all_minid_maxid
                        SET state = 1
                        WHERE yaer_month = '2026_{min_month}'
                        AND state <> 1
                        AND NOT EXISTS (
                            SELECT 1
                            FROM (
                                SELECT 1
                                FROM {site}_syn_asin_all_minid_maxid
                                WHERE yaer_month = '2026_{min_month}'
                                AND state = 1
                                LIMIT 1
                            ) AS subquery
                        );
                        """
                conn.execute(u_sql)
            if id_count > 10000:
                u_sql = f"""UPDATE {site}_syn_asin_all_minid_maxid
                                        SET state = 1
                                        WHERE yaer_month = '2026_{month_}'
                                        AND state <> 1
                                        AND NOT EXISTS (
                                            SELECT 1
                                            FROM (
                                                SELECT 1
                                                FROM {site}_syn_asin_all_minid_maxid
                                                WHERE yaer_month = '2026_{month_}'
                                                AND state = 1
                                                LIMIT 1
                                            ) AS subquery
                                        );
                                        """

                conn.execute(u_sql)

    if platform.system().lower() == 'windows':
        get_redis_data(site, engine_pg6, engine_spider)


def get_redis_data(site_name, engine_pg6, engine_spider):
    print(site_name, engine_pg6, engine_spider)
    redis14 = redis.Redis(host=REDIS_CONN['redis_host'], port=REDIS_CONN['redis_port'],
                          password=REDIS_CONN['redis_pwd'], db=REDIS_CONN['redis_db'])
    new_date = datetime.datetime.now().strftime("%Y-%m-%d")
    site_new_date = f'{site_name}_' + str(new_date)
    start_index = 0  # 起始索引
    end_index = -1  # 结束索引，-1 表示获取整个列表
    list_data = redis14.lrange(site_new_date, start_index, end_index)
    if list_data:
        new_date_month = datetime.datetime.now().strftime("%Y-%m")
        sql_count = f'select count(id) from {site_name}_all_syn_st_month_{new_date_month.replace("-", "_")} where state=1'
        print(sql_count)
        df_count_id = engine_spider.read_sql(sql_count)
        if not df_count_id.empty:
            remain_asin_total = df_count_id.values.tolist()[0][0]
        else:
            remain_asin_total = 0
        print('remain_asin_total::',remain_asin_total)
        sql_asin_total1 = f'select count(id) from {site_name}_all_syn_st_month_{new_date_month.replace("-", "_")}'
        print(sql_asin_total1)
        df_count_asin_total1 = engine_spider.read_sql(sql_asin_total1)
        if not df_count_asin_total1.empty:
            asin_total = df_count_asin_total1.values.tolist()[0][0]
        else:
            asin_total = 0

        print('remain_asin_total',asin_total)

        # 使用 Counter 统计元素出现次数
        element_counts = Counter(list_data)
        # 输出统计结果
        list_data = []
        elements_to_check = [1, 2, 3, 4]  # 需要检查的元素列表
        for element in elements_to_check:
            element_str = str(element).encode()  # 将整数转为字节字符串
            count = element_counts.get(element_str, 0)  # 获取元素的计数，若不存在则默认为 0
            print(f"{element}: {count}")
            list_data.append(count)
        print(list_data)
        new_date_hour = f'{site_name}_' + str(new_date) + ':0-23'
        print(new_date_hour,'13223')
        list_hour_data = redis14.lrange(new_date_hour, start_index, end_index)
        # 使用 Counter 统计元素出现次数
        element_counts_hour = Counter(list_hour_data)
        hour_data_dict = {}
        for element, counts in element_counts_hour.items():
            hour_data_dict[str(new_date) + ':' + element.decode('utf-8')] = counts
        hour_data_json = json.dumps(hour_data_dict)
        print(hour_data_json)
        # 统计字段空值次数。空值率
        log_time = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        asin_detail_column_is_none_count = f'{log_time}_asin_detail_is_none'
        redis_values = redis14.lrange(asin_detail_column_is_none_count, start_index, end_index)
        # 使用 map() 函数将字节串转换为 Python 字符串
        asin_detail_data_list = list(map(lambda x: x.decode('utf-8'), redis_values))
        # 使用 Counter 统计元素出现次数
        element_count = Counter(asin_detail_data_list)
        asin_detail_column_dict = {}
        column_chinese_describe = {
            'activity_type': '促销类型',
            'one_two_val': 'coupon额度 activity_type 1、2对应的值',
            'three_four_val': 'Prime Exclusive Discounts额度 activity_type 3 4对应的值',
            'eight_val': '9：降低幅度的值 ',
            'ac_name': 'Amazon Choice后面的词，不包含品牌名称',
            'package_quantity': '包装数量',
            'pattern_name': '模式名称',
            'buy_sales': 'asin详情 月销售量',
            'lob_asin_json': 'Make it a bundle商品的asin',
            'weight': '重量',
            'material': '材料或者材质',
            'launch_time': '上架时间',
            'together_asin': '一起购买的asin',
            'parentAsin': '父asin',
            'price': '价格',
            'rating': '星级',
            'total_comments': '评论数',
            'volume': '体积',
            'brand': '品牌名称',
            'weight_str': '完整重量未转换',
            'describe': '头部 五点描述',
            'rank': '排名',
            'node_id': 'node_id'
        }
        for element, count in element_count.items():
            asin_detail_column_dict[element] = count
        print(asin_detail_column_dict)
        asin_column_dict = {}
        for k, v in asin_detail_column_dict.items():
            asin_column_dict[k] = v
            asin_column_dict[k + '_describe'] = column_chinese_describe.get(k)
        asin_column_json = json.dumps(asin_column_dict, ensure_ascii=False)
        print(asin_column_json)
        # 验证码 1 异常 2 成功 3 总请求 4
        with engine_pg6.begin() as conn:
            sql_upsert = f"""
            INSERT INTO us_count_request_data (
                date_info,
                asin_request_err_total,
                code_err_total,
                success_asin_total,
                request_total_count,
                hour_asin_total,
                asin_column_json,
                remain_asin_total,
                site_name,
                asin_total
            ) VALUES (
                '{new_date}',
                {list_data[1]},
                {list_data[0]},
                {list_data[2]},
                {list_data[3]},
                '{hour_data_json}',
                '{asin_column_json}',
                {remain_asin_total},
                '{site}',
                '{asin_total}'
            )
            ON CONFLICT (date_info) DO UPDATE SET
                asin_request_err_total = EXCLUDED.asin_request_err_total,
                code_err_total        = EXCLUDED.code_err_total,
                success_asin_total    = EXCLUDED.success_asin_total,
                request_total_count   = EXCLUDED.request_total_count,
                hour_asin_total       = EXCLUDED.hour_asin_total,
                asin_column_json      = EXCLUDED.asin_column_json,
                remain_asin_total     = EXCLUDED.remain_asin_total,
                site_name     = EXCLUDED.site_name,
                asin_total     = EXCLUDED.asin_total;
            """
            print(sql_upsert)
            conn.execute(sql_upsert)

if __name__ == '__main__':
    if platform.system().lower() == 'windows':
        for i in range(10000):
            try:
                site_list = ['us', 'de', 'uk']
                # site_list = ['us']
                for site in site_list:
                    update_state(site)
                time.sleep(60)
            except Exception as e:
                pass
    else:
        site_list = ['us', 'de', 'uk']
        for site in site_list:
            update_state(site)