import json
import os
import re
import time
import traceback
from datetime import datetime

import requests
from lxml import etree
from playwright.sync_api import sync_playwright
from secure_db_client import get_remote_engine

'备份'
def mysql_connect():
    engine_us_mysql = get_remote_engine(
        site_name='us',  # -> database "selection"
        db_type='postgresql_15_outer',  # -> 服务端 alias "mysql"
    )
    return engine_us_mysql


def run_spider(asin_list):
    print('asin_list:::', asin_list)
    print('asin_list:::', len(asin_list))
    if asin_list:
        try:
            pr_name = "chrome.exe"
            os.system('%s%s' % ("taskkill /F /IM ", pr_name))
        except Exception as e:
            print("强制关闭chrome.exe失败：", e)
        # 初始化
        with sync_playwright() as _playwright:
            context = _playwright.chromium.launch_persistent_context(
                # 指定本机用户缓存地址
                user_data_dir=r"C:\Users\admin\AppData\Local\Google\Chrome\User Data",
                # 指定本机google客户端exe的路径
                executable_path=r"C:\Users\admin\AppData\Local\Google\Chrome\Application\chrome.exe",
                # 要想通过这个下载文件这个必然要开  默认是False
                accept_downloads=True,
                # 设置不是无头模式
                headless=False,  # False 打开。 True 无头浏览器
                bypass_csp=True,
                locale='en-GB',
                ignore_https_errors=True,
                no_viewport=True,
                slow_mo=10
            )
            page = context.pages[0] if context.pages else context.new_page()

            print('打开浏览器请求asin:')

            # page.goto('https://sellercentral.amazon.com/gp/homepage.html/ref=xx_home_logo_xx?')
            # page.wait_for_timeout(1500)

            def intercept_request(request):
                try:
                    if "populate" in request.url:
                        print('request.url::', request.url)
                        resp = request.response().text()
                        html_data_list.append(resp)
                except Exception as e:
                    print("获取响应失败:", e, f"\n{traceback.format_exc()}")

            try:
                page.goto(
                    'https://sellercentral.amazon.com/listing/varwiz?ref_=xx_swlang_head_xx&mons_sel_locale=en_US&languageSwitched=1')
                page.wait_for_timeout(1500)
                print('page.url::', page.url)
                for id_asin in asin_list:
                    print("id_asin::", id_asin)
                    id_asin_list = id_asin.split('|-|')
                    asin = id_asin_list[0]
                    id = int(id_asin_list[1])
                    print('开始抓取：：', asin, 'id：：', id)
                    error = None
                    if 'signin' in page.url:
                        save_asin_var_data(asin, [], '失败', 'us', [], [], '账号电脑退出登录', id)
                        semd_ms('1111')
                        return
                    html_data_list = []
                    # page.reload() # 刷新页面
                    print(f'请求asin {asin} data_list::', len(html_data_list))
                    page.wait_for_timeout(1000)
                    page.locator('//*[@id="varwizard_accordion"]/div[1]/div/div[1]/h5/a/i').click()
                    page.wait_for_timeout(1000)
                    page.locator('//*[@id="varwiz-search-text"]').fill(f'{asin}')
                    page.wait_for_timeout(5000)
                    page.on("requestfinished", intercept_request)
                    page.locator('//*[@id="a-autoid-0"]/span/input').click()  # 点击
                    page.wait_for_timeout(10000)
                    # 请求 指定的 URL
                    items_list = []
                    parent_asin_list = []
                    var_asin_list = []
                    for html_content in html_data_list:
                        parser = etree.HTML(html_content)
                        if (
                                'variation family is not supported on this' in html_content and 'ERROR' in html_content) or (
                                'you searched for is not' in page.content()):
                            try:
                                error = re.findall(r'message =(.*?);', html_content)[0]
                            except:
                                error = "The ASIN you searched for is not part of any variation family"
                            print('errorerror::', error)
                            save_asin_var_data(asin, [], '失败', 'us', [], [], error, id)
                            break
                        asin_id_list = parser.xpath("//tr/@id")
                        for asin_id in asin_id_list:
                            print('asin_id asin_id asin_id::', asin_id)
                            if len(asin_id) == 10:
                                parent_child_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='parent_child']//span/text()")
                                parent_child = parent_child_list[0] if parent_child_list else None
                                item_name_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='item_name']//span/text()")
                                product_name = item_name_list[0] if item_name_list else None
                                variation_theme_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='variation_theme']//span/text()")
                                variation_theme = variation_theme_list[0] if variation_theme_list else None
                                condition_type_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='condition_type']//span/text()")
                                condition_type = condition_type_list[0] if condition_type_list else None
                                size_map_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='size_map']//span/text()")
                                size_map = size_map_list[0] if size_map_list else None
                                size_name_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='size_name']//span/text()")
                                size_name = size_name_list[0] if size_name_list else None
                                special_size_type_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='special_size_type']//span/text()")
                                special_size_type = special_size_type_list[0] if special_size_type_list else None
                                item_package_quantity_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='item_package_quantity']//span/text()")
                                item_package_quantity = item_package_quantity_list[
                                    0] if item_package_quantity_list else None
                                color_map_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='color_map']//span/text()")
                                color_map = color_map_list[0] if color_map_list else None
                                color_name_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='color_name']//span/text()")
                                color_name = color_name_list[0] if color_name_list else None
                                item_sku_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='item_sku']//span/text()")
                                item_sku = item_sku_list[0] if item_sku_list else None
                                quantity_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='quantity']//span/text()")
                                quantity = quantity_list[0] if quantity_list else None
                                list_price = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='list_price']//span/text()")
                                price = list_price[0] if list_price else None
                                external_product_id_type_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='external_product_id_type']//span/text()")
                                external_product_id_type = external_product_id_type_list[
                                    0] if external_product_id_type_list else None
                                external_product_id_list = parser.xpath(
                                    f"//tr[@id='{asin_id}']/td[@data-column='external_product_id']//span/text()")
                                external_product_id = external_product_id_list[0] if external_product_id_list else None
                                items_dict = {'Parentage': parent_child, 'Asin': asin_id, 'SKU': item_sku,
                                              'Product Name': product_name,
                                              'External Product Id Type': external_product_id_type,
                                              'External Product Id': external_product_id,
                                              'Variation Theme': variation_theme, 'Color Map': color_map,
                                              'Package Quantity': item_package_quantity,
                                              'Color': color_name, 'Condition': condition_type,
                                              'Quantity': quantity, 'Price': price, 'Size Map': size_map,
                                              'Size': size_name,
                                              'Special Size Type': special_size_type
                                              }
                                items_dict = {k: (v.strip() if isinstance(v, str) else v) for k, v in
                                              items_dict.items()}
                                print(items_dict)
                                Parentage = items_dict.get('Parentage')
                                if Parentage == 'parent':
                                    parent_asin = items_dict.get('Asin')
                                    parent_asin_list.append(parent_asin)
                                elif Parentage == 'child':
                                    var_asin = items_dict.get('Asin')
                                    var_asin_list.append(var_asin)
                                items_list.append(items_dict)
                    if items_list:
                        print('items_listitems_list::', len(items_list))
                        items_list_json = json.dumps(items_list)
                        save_asin_var_data(asin, items_list_json, '成功', 'us', parent_asin_list, var_asin_list, None, id)
                        html_data_list = []
            except Exception as e:
                print(asin, '报错：：', e, f"\n{traceback.format_exc()}")
                semd_ms(asin)
                save_asin_var_data(asin, [], '失败', 'us', [], [], None, id)


def semd_ms(asin):
    try:
        url = 'http://47.112.96.71:8082/selection/sendMessage'
        data = {
            'account': '下载 变体数据失败。远程账号电脑 HM 299 421 380',
            'title': f'账号电脑  {asin} 变体数据 失败',
            'content': 'pengyanbing'
        }
        print(data)
        requests.post(url=url, data=data, timeout=15)
    except:
        pass


def mysql_get_asin():
    while True:
        try:
            print('轮询 mysql 查询:', datetime.now().strftime("%m-%d %H:%M:%S"))
            engine_us_mysql = mysql_connect()
            spider_state_sql = """select asin,id from asin_variation_family_log where status = '未开始' and length(asin)=10 limit 20 """
            print('spider_state_sql:', spider_state_sql)
            df_asin = engine_us_mysql.read_sql(spider_state_sql)
            if not df_asin.empty:
                update_time = int(time.time())
                with engine_us_mysql.begin() as conn:
                    index_tuple = tuple(df_asin['id'])
                    if len(index_tuple) == 1:
                        sql_update = f"""UPDATE asin_variation_family_log a set status='爬取中',update_time='{update_time}' where a.id in ({index_tuple[0]})"""
                    else:
                        sql_update = f"""UPDATE asin_variation_family_log a set status='爬取中',update_time='{update_time}' where a.id in {index_tuple}"""
                    print('UPDATE_sql:', sql_update)
                    conn.execute(sql_update)
                _asin_lis = list(df_asin.asin + '|-|' + df_asin.id.astype("U"))
                print("_asin_lis:::", _asin_lis, )
                print("_asin_lis::: len ", len(_asin_lis))
                run_spider(_asin_lis)  # 传递asin 列表
            time.sleep(3)
        except Exception as e:
            print('查询 mysql_get_asin 报错：：', e, f"\n{traceback.format_exc()}")


def save_asin_var_data(asin, data_json, spider_value, site_name, parent_asin_list, var_asin_list, error, id):
    if parent_asin_list:
        parent_asin = ','.join(parent_asin_list)
    else:
        parent_asin = []
    if len(var_asin_list) == 0:
        var_asin_list = "'{}'"
    else:
        var_asin_list = "'" + '{' + ','.join(var_asin_list) + '}' + "'"

    if data_json:
        data_json = data_json.replace('%', '%%').replace("'", "").replace("\'", "")
    if error:
        error = error.replace('%', '%%').replace("'", "").replace('"', '').replace("\'", "")
    while True:
        try:
            engine_us_mysql = mysql_connect()
            update_time = int(time.time())
            print(f'更新 {asin} 数据：')
            with engine_us_mysql.begin() as conn:
                if error is None:
                    sql = f"""
                    UPDATE asin_variation_family_log 
                    SET variation_family='{data_json}', status='{spider_value}', update_time='{update_time}' ,parent_asin='{parent_asin}',variation_asin={var_asin_list}
                    WHERE id={id} AND site_name='{site_name}'
                    """
                    print('成功',sql)
                else:
                    sql = f"""
                            UPDATE asin_variation_family_log 
                            SET variation_family='{data_json}', status='{spider_value}', update_time='{update_time}' ,parent_asin='{parent_asin}',variation_asin={var_asin_list},err_msg='{error}'
                            WHERE id={id} AND site_name='{site_name}'
                           """
                    print('error is not None：： ', sql)
                conn.execute(sql)
            print(asin, '更新成功')
            break
        except Exception as e:
            print('存储数据报错：', e)
            time.sleep(5)


if __name__ == '__main__':
    mysql_get_asin()
