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

import requests
from lxml import etree
from DrissionPage import ChromiumPage, ChromiumOptions
from secure_db_client import get_remote_engine


def mysql_connect():
    engine_us_mysql = get_remote_engine(
        site_name='us',
        db_type='postgresql_15_outer',
    )
    return engine_us_mysql


def parse_list(s: str):
    return [t.strip() for t in s.strip().strip('[]').split(',') if t.strip()]


def clean_text(node):
    return " ".join("".join(node.itertext()).split())


def extract_visible_headers(doc):
    print('extract_visible_headers')
    hidden_val = doc.xpath('//input[@id="hiddenColumnTitles"]/@value')
    hidden_ids = set(parse_list(hidden_val[0])) if hidden_val else set()

    headers = []
    seen = set()
    for th in doc.xpath('//tr[@id="head-row"]/th[@id]'):
        col_id = th.get('id')
        if col_id in seen:
            continue
        seen.add(col_id)
        if col_id in hidden_ids:
            continue
        style = (th.get('style') or '').replace(' ', '').lower()
        classes = (th.get('class') or '')
        if 'display:none' in style or 'a-hidden' in classes.split():
            continue
        label = clean_text(th)
        if label:
            headers.append((col_id, label))
    return headers


def extract_rows(doc, headers):
    rows = []
    for tr in doc.xpath('//table[contains(@class,"mt-table")]//tr[contains(@class,"mt-row")]'):
        row = {}
        for col_id, label in headers:
            td = tr.xpath('.//td[@data-column=$c]', c=col_id)
            value = clean_text(td[0]) if td else ""
            row[label] = value
        if any(row.values()):
            rows.append(row)
    return rows


def build_page():
    """初始化 DrissionPage 的 ChromiumPage。
    复用之前已经登录的 user_data_dir，避免每次重登。
    """
    try:
        os.system("taskkill /F /IM chrome.exe /T")
    except Exception as e:
        print("强制关闭chrome.exe失败：", e)
    time.sleep(2)

    opts = ChromiumOptions()
    opts.set_browser_path(r'C:\Program Files\Google\Chrome\Application\chrome.exe')
    # 非默认路径，规避 Chrome 137+ 的 remote-debug 限制
    opts.set_user_data_path(r"C:\chrome_profile_TC_US\User Data")
    opts.set_local_port(9444)  # 自定义调试端口，避免和其它脚本冲突
    opts.set_argument('--disable-popup-blocking')
    opts.set_argument('--disable-notifications')
    opts.set_argument('--disable-infobars')
    opts.set_pref('profile.default_content_setting_values.notifications', 2)

    page = ChromiumPage(addr_or_opts=opts)
    page.set.window.max()
    return page


def run_spider(asin_list):
    print('asin_list:::', asin_list)
    print('asin_list:::', len(asin_list))
    if not asin_list:
        return

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

    try:
        page.get(
            'https://sellercentral.amazon.com/listing/varwiz?ref_=xx_swlang_head_xx&mons_sel_locale=en_US&languageSwitched=1'
        )
        time.sleep(2.5)
        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(asin)
                time.sleep(120)
                return

            html_data_list = []
            time.sleep(1)

            page.ele('xpath=//*[@id="varwizard_accordion"]/div[1]/div/div[1]/h5/a/i', timeout=15).click()
            time.sleep(1)

            page.ele('xpath=//*[@id="varwiz-search-text"]', timeout=15).input(asin)
            time.sleep(5)

            # 启动网络监听：只关心 URL 含 'populate' 的 fetch/xhr
            page.listen.start('populate')

            page.ele('xpath=//*[@id="a-autoid-0"]/span/input', timeout=15).click()
            time.sleep(15)

            # 收集本轮所有命中的请求响应
            try:
                for packet in page.listen.steps(timeout=2):
                    try:
                        body = packet.response.body
                        if isinstance(body, (dict, list)):
                            body = json.dumps(body)
                        if body:
                            print('listen url::', packet.url)
                            html_data_list.append(body)
                    except Exception as e:
                        print("读取 listen 包失败:", e, f"\n{traceback.format_exc()}")
            finally:
                page.listen.stop()

            items_list = []
            parent_asin_list = []
            var_asin_list = []
            print(f'请求asin {asin} data_list::', len(html_data_list))

            for html_content in html_data_list:
                if (
                    'variation family is not supported on this' in html_content and 'ERROR' in html_content
                ) or ('you searched for is not' in page.html):
                    try:
                        error = re.findall(r'message =(.*?);', html_content)[0]
                    except Exception:
                        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

                tree = etree.HTML(html_content)
                visible_headers = extract_visible_headers(tree)
                rows = extract_rows(tree, visible_headers)
                if rows:
                    for i in rows:
                        print(i, '233333333333')
                        Parentage = i.get('Parentage')
                        if Parentage == 'parent':
                            parent_asin_list.append(i.get('ASIN'))
                        if Parentage == 'child':
                            var_asin_list.append(i.get('ASIN'))
                    items_list.extend(rows)

            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)

    finally:
        try:
            page.quit()
        except Exception:
            pass


def semd_ms(asin):
    try:
        url = 'http://47.112.96.71:8082/selection/sendMessage'
        data = {
            'content': '下载 变体数据失败。远程账号电脑 HM 299 421 380',
            'title': f'账号电脑  {asin} 变体数据 失败',
            'account': 'pengyanbing'
        }
        print(data)
        y = requests.post(url=url, data=data, timeout=15)
        print(y.content.decode('gbk'))
    except Exception:
        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)
            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)
                elif error == '账号电脑退出登录':
                    sql = f"""
                        UPDATE asin_variation_family_log SET status='未开始' WHERE id={id} AND site_name='{site_name}'
                        """
                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()
