from secure_db_client import get_remote_engine
import platform
from sqlalchemy import create_engine
import pandas as pd
import datetime
from params import DB_CONN_DICT, PG_CONN_DICT_21, REDIS_CONN, PG_CONN_DICT
import redis
import time
from collections import Counter
import json

import codecs


def update_state(site):
    if site == 'us':
        db = 'selection'
    else:
        db = f'selection_{site}'
    engine = create_engine(
        f'mysql+pymysql://{DB_CONN_DICT["mysql_user"]}:' + f'{DB_CONN_DICT["mysql_pwd"]}@{DB_CONN_DICT["mysql_host"]}:{DB_CONN_DICT["mysql_port"]}/{db}?charset=utf8mb4')  # , pool_recycle=3600
    engine_spider = create_engine(
        f"postgresql+psycopg2://postgres:F9kL2sXe81rZq@61.145.136.61:54328/{db}",
        encoding='utf-8')
    engine_pg6 = create_engine(
        f"postgresql+psycopg2://{PG_CONN_DICT['pg_user']}:{PG_CONN_DICT['pg_pwd']}@{PG_CONN_DICT['pg_host']}:{PG_CONN_DICT['pg_port']}/{db}",
        encoding='utf-8', connect_args={"connect_timeout": 10})
    now_time = datetime.datetime.now()
    up_time = (now_time + datetime.timedelta(hours=-0.21)).strftime("%Y-%m-%d %H:%M:%S")
    up_1_time = (now_time + datetime.timedelta(hours=-0.21)).strftime("%Y-%m-%d %H:%M:%S")
    print(up_time)
    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)
        # 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_2025_{min_month} SET state = 1 WHERE id IN (  SELECT id  FROM {site}_all_syn_st_month_2025_{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_2025_{month_} SET state = 1 WHERE id IN (  SELECT id  FROM {site}_all_syn_st_month_2025_{month_}  WHERE state = 2    AND updated_time <'{up_1_time}'  LIMIT 5000);"
                print('sql_update:', sql_update)
                conn_14.execute(sql_update)
            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)
            # seller_sql = f"update {site}_seller_account_syn_distinct set state = 1 where  state = 2 and updated_at <'{up_time}';"
            # print('修改店铺信息抓取表状态：',seller_sql)
            # conn_14.execute(seller_sql)
            # seller_sql_product = f"update {site}_seller_account_syn_distinct set product_state = 1 where  product_state = 2 and updated_at <'{up_time}';"
            # print('修改店铺asin抓取表状态',seller_sql_product)
            # conn_14.execute(seller_sql_product)
            select_sql = f"select count(id) from {site}_all_syn_st_month_2025_{min_month} where state=1"
            df = pd.read_sql(select_sql, con=engine_spider)
            id_count_min = df.iloc[0, 0]
            select_sql = f"select count(id) from {site}_all_syn_st_month_2025_{month_} where state=1"
            df = pd.read_sql(select_sql, con=engine_spider)
            id_count = df.iloc[0, 0]
    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 = '2025_{min_month}'
                        AND state <> 1
                        AND NOT EXISTS (
                            SELECT 1
                            FROM (
                                SELECT 1
                                FROM {site}_syn_asin_all_minid_maxid
                                WHERE yaer_month = '2025_{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 = '2025_{month_}'
                                        AND state <> 1
                                        AND NOT EXISTS (
                                            SELECT 1
                                            FROM (
                                                SELECT 1
                                                FROM {site}_syn_asin_all_minid_maxid
                                                WHERE yaer_month = '2025_{month_}'
                                                AND state = 1
                                                LIMIT 1
                                            ) AS subquery
                                        );
                                        """
                conn.execute(u_sql)

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


def get_redis_data(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 = pd.read_sql(sql_count, con=engine_spider)
        remain_asin_total = df_count_id.values.tolist()[0][0]
        print(remain_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 {site_name}_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
            ) VALUES (
                '{new_date}',
                {list_data[1]},
                {list_data[0]},
                {list_data[2]},
                {list_data[3]},
                '{hour_data_json}',
                '{asin_column_json}',
                {remain_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;
            """
            print(sql_upsert)
            conn.execute(sql_upsert)





if __name__ == '__main__':
    for i in range(10000):
        site_list = ['us', 'de', 'uk', 'fr', 'es', 'it']
        # site_list = ['us']
        for site in site_list:
            update_state(site)
        time.sleep(60)