import os
import sys

sys.path.append(os.path.dirname(sys.path[0]))  # 上级目录
import curl_cffi
from DrissionPage import ChromiumPage, ChromiumOptions
from DrissionPage.common import Keys
import json
import time
from utils.secure_db_client import get_remote_engine
import random

RANK_LIST = [
    1, 10, 30, 50, 100, 200, 300, 400, 500, 600, 700, 800, 900, 1000,
    *range(1100, 10001, 100),
    *range(11000, 21000, 1000),
    25000, 30000, 35000, 40000, 45000, 50000
]

ACCOUNTS = [
    ['18823832416', '18823832416qaz'],
    ['15368051270', '123456'],
    ['18307967347', 'Aa123456.'],
    ['qq16531218653@163.com', 'qq16531218653'],
]


class AccountManager:
    def __init__(self):
        self.index = 0
        self.fail_count = 0

    def current(self):
        return ACCOUNTS[self.index]

    def switch_next(self):
        self.fail_count += 1
        if self.fail_count >= len(ACCOUNTS):
            print('所有账号已轮换一轮，睡眠2小时')
            time.sleep(7200)
            self.fail_count = 0
            self.index = 0
        else:
            self.index = self.fail_count
        print(f'切换到账号[{self.index}]：{self.current()[0]}')


def mysql_connect(site='us'):
    return get_remote_engine(site_name=site, db_type='mysql')


def db_cursor_connect_update(sql, site):
    for i in range(3):
        try:
            engine = mysql_connect(site=site)
            print('更新sql：', sql)
            with engine.begin() as conn:
                conn.execute(sql)
            break
        except:
            print(site, 'db_cursor_connect 报错：', sql)


def db_cursor_connect_msyql_read(site, sql):
    for i in range(3):
        try:
            engine = mysql_connect(site=site)
            return engine.read_sql(sql)
        except Exception as e:
            import traceback
            traceback.print_exc()
            print(e, 'db_cursor_connect_msyql_read 报错：', sql)


def sellersprite_login(account):
    """传入 [username, password]，返回 cookies_dict"""
    print('登录账号：', account[0])
    chrome_options = ChromiumOptions()
    chrome_options.set_browser_path(r'C:\Program Files\Google\Chrome\Application\chrome.exe')
    chrome_options.set_local_port(9333)

    page_chrome = ChromiumPage(addr_or_opts=chrome_options)
    page_chrome.get("https://www.sellersprite.com/cn/w/user/login")
    page_chrome.set.window.max()
    page_chrome.set.cookies.clear()
    time.sleep(random.randint(1, 3))
    page_chrome.refresh()
    time.sleep(random.randint(1, 3))
    page_chrome.get("https://www.sellersprite.com/cn/w/user/login")
    time.sleep(random.randint(6, 10))

    page_chrome.ele('xpath://a[text()="账号登录"]', timeout=10).click()
    print('点击账号登录')
    time.sleep(random.randint(5, 10))

    email_input = page_chrome.ele('xpath://div[@id="form_signin_password"]//input[@name="email"]')
    email_input.clear()
    email_input.input(account[0])
    print("已输入账号")
    time.sleep(random.randint(5, 10))

    password_input = page_chrome.ele('xpath://div[@id="form_signin_password"]//input[@type="password"]')
    password_input.clear()
    password_input.input(account[1])
    time.sleep(random.randint(5, 10))
    page_chrome.actions.type(Keys.ENTER)
    time.sleep(random.randint(5, 10))

    page_chrome.get('https://www.sellersprite.com/v2/tools/sales-estimator')
    time.sleep(random.randint(5, 10))

    cookies = {c['name']: c['value'] for c in page_chrome.cookies()}
    print('获取到 cookies，key数量：', len(cookies))
    page_chrome.close()
    return cookies


def fetch_rank_sales(db_base, c_name, c_id, rank, cookies_dict):
    """请求单个 rank 的销量，返回 response dict 或 None（3次全部失败）"""
    headers = {
        "Referer": "https://www.sellersprite.com/v2/tools/sales-estimator",
        "Origin": "https://www.sellersprite.com",
        "Accept": "application/json, text/javascript, */*; q=0.01",
        "Accept-Encoding": "gzip, deflate, br, zstd",
        "Accept-Language": "zh-CN,zh-TW;q=0.9,zh;q=0.8",
        "Cache-Control": "no-cache",
        "User-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
    }
    url = "https://www.sellersprite.com/v2/tools/sales-estimator/bsr.json"
    data = {"station": db_base.upper(), "cid": c_id, "bsr": f"{rank}"}

    print(c_name, '请求参数 data::', data)
    for _ in range(3):
        try:
            resp = curl_cffi.post(url, headers=headers, data=data,
                                  impersonate="chrome", timeout=300, cookies=cookies_dict)
            return json.loads(resp.text)
        except:
            time.sleep(random.uniform(5, 20.75))
    return None


def sellersprite_spider(db_base):
    month = time.strftime("%m")
    year = time.strftime("%Y")
    year_month = f'{year}_{int(month)}'

    # 同时捞 state IN (1,2)，state=2 是上次崩溃遗留的，允许重跑
    sql = f"select `name`,c_id from all_site_category where site='{db_base}' and state in (1,2)"
    category_name_list_df = db_cursor_connect_msyql_read('us', sql)
    print(category_name_list_df)
    category_name_list = list(category_name_list_df['name'] + '|-|==|' + category_name_list_df['c_id'])

    account_mgr = AccountManager()
    cookies_dict = sellersprite_login(account_mgr.current())

    for category_name_id in category_name_list:
        c_name = category_name_id.split('|-|==|')[0]
        c_id   = category_name_id.split('|-|==|')[1]
        print(c_name, c_id)

        name_rnak_list = []

        # state → 2 标记处理中
        db_cursor_connect_update(
            f"UPDATE all_site_category set state=2 WHERE site='{db_base}' and state in (1,2) and c_id='{c_id}'",
            'us'
        )

        for rank in RANK_LIST:
            response = fetch_rank_sales(db_base, c_name, c_id, rank, cookies_dict)

            if response is None:
                print(f"{c_name} cid={c_id} rank={rank} 请求3次全部失败，跳过")
                continue

            # 先判断 response_data 是否为 None，再取字段
            response_data = response.get('data')
            print('code::', response.get('code'), 'message::', response.get('message'))

            if not response_data:
                print(f'{c_name} rank={rank} data为空，可能cookie失效，切换账号重试')
                account_mgr.switch_next()
                cookies_dict = sellersprite_login(account_mgr.current())
                # 换号后重试当前 rank
                response = fetch_rank_sales(db_base, c_name, c_id, rank, cookies_dict)
                if response is None:
                    continue
                response_data = response.get('data')
                if not response_data:
                    continue

            est = response_data.get('estMonSales')
            print('estMonSales::', est)

            if est is None:
                print(f"{c_name} 排名{rank}：estMonSales=None，跳出循环")
                break
            if est == 0.0:
                print(f"{c_name} 排名{rank}：销量 0，跳出循环")
                break

            name_rnak_list.append((c_name, rank, int(est), year_month))
            time.sleep(random.uniform(10, 25.75))

        # 空列表不入库，state 重置回 1 等待下次重跑
        if not name_rnak_list:
            print(f'{c_name} 无数据，跳过入库，state 重置回 1')
            db_cursor_connect_update(
                f"UPDATE all_site_category set state=1 WHERE site='{db_base}' and c_id='{c_id}'",
                'us'
            )
            continue

        insert_sql = f"INSERT INTO {db_base}_one_category (name, rank, orders, `year_month`) values (%s, %s, %s, %s)"
        for attempt in range(10):
            try:
                engine_db = mysql_connect(site=db_base)
                with engine_db.begin() as conn:
                    conn.executemany(insert_sql, name_rnak_list)
                db_cursor_connect_update(
                    f"UPDATE all_site_category set state=3 WHERE site='{db_base}' and state=2 and c_id='{c_id}'",
                    'us'
                )
                print(f'{c_name} 入库完成，共 {len(name_rnak_list)} 条')
                break
            except Exception as e:
                print(f'存储失败(第{attempt + 1}次)：', e)
                time.sleep(10)

        print('当前完成。获取下一个分类销量')
        time.sleep(random.uniform(30, 60.5))


def run():
    for site in ['us', 'de', 'uk']:
        sellersprite_spider(site)


if __name__ == '__main__':
    run()
