import sys, os
from datetime import datetime
sys.path.append(os.path.dirname(sys.path[0]))
import json
import re
import time
import random
import threading
import pandas as pd
from queue import Queue
from flask import Flask, request as flask_request, Response
from utils.secure_db_client import get_remote_engine

flask_app = Flask(__name__)

def get_business_val(seller_address, key):
    parts =[p.strip()for p in seller_address.split("|-|")]
    for i, p in enumerate(parts):
        if p.startswith(key):
            if key in ("Business Address","Geschaftsadresse","Geschäftsadresse"):
                return " ".join(parts[i + 1:]).strip()
            elif i + 1 < len(parts):
                return parts[i + 1].strip()
    return None

def json_response(data):
    return Response(json.dumps(data, ensure_ascii=False), mimetype='application/json')

# 站点对应的key映射
site_key_map = {
    "us": ("Business Name", "Business Address"),
    "uk": ("Business Name", "Business Address"),
    "de": ("Geschäftsname", "Geschäftsadresse")
}

def build_seller_info_item(site_name, seller_id, seller_address):
    if not seller_address or not isinstance(seller_address, str) or not seller_address.strip():
        return {
            "seller_id": seller_id,
            "business_name": None,
            "business_address": None
        }

    name_key, addr_key = site_key_map.get(site_name, ("Business Name", "Business Address"))
    return {
        "seller_id": seller_id,
        "business_name": get_business_val(seller_address, name_key),
        "business_address": get_business_val(seller_address, addr_key)
    }


def fetch_latest_seller_rows(engine, site_name, year, seller_ids):
    seller_ids_sql = ",".join([f"'{seller_id}'" for seller_id in seller_ids])
    sql = f"""
        SELECT seller_id, seller_address
        FROM {site_name}_seller_account_feedback_{year}
        WHERE seller_id IN ({seller_ids_sql})
        ORDER BY seller_id, id DESC
    """
    print('sql::', sql)
    return engine.read_sql(sql)


def scrape_seller_addresses(site_name, seller_ids):
    """
    对 DB 中未找到的 seller_id 列表，并发爬取 Amazon 页面获取 seller_address。
    返回 dict: {seller_id: seller_address or None}
    """
    from utils.requests_param import Requests_param_val
    from curl_cffi import requests as curl_requests
    from lxml import etree

    result_map = {}
    result_lock = threading.Lock()

    try:
        reuests_para_val = Requests_param_val(site_name=site_name, spider="seller_account", proxy_name=None)
        site_url, host = reuests_para_val.get_site_url(site_name)
        cookies_dict = reuests_para_val.get_cookie()
        cookies_queue = Queue()
        for ck in cookies_dict.values():
            cookies_queue.put(ck)
    except Exception as e:
        print(f"scrape_seller_addresses 初始化失败: {e}")
        return {sid: None for sid in seller_ids}

    task_queue = Queue()
    for sid in seller_ids:
        task_queue.put(sid)

    def worker():
        while True:
            try:
                seller_id = task_queue.get_nowait()
            except Exception:
                break
            try:
                cookie_str = reuests_para_val.get_cookie_str(cookies_queue)
                scraper_url = f"{site_url}sp?ie=UTF8&seller={seller_id}"
                headers = reuests_para_val.requests_amazon_headers(
                    host=host, site_url=site_url, asin=None, scraper_url=scraper_url)
                headers["cookie"] = cookie_str

                resp = curl_requests.get(scraper_url, headers=headers,
                                         timeout=10, verify=False, impersonate="chrome")

                if reuests_para_val.check_amazon_yzm(resp):
                    print(f"scrape seller {seller_id}: 出现验证码")
                    with result_lock:
                        result_map[seller_id] = None
                    time.sleep(random.uniform(2, 5))
                    continue

                if reuests_para_val.check_amazon_not_page(resp.text):
                    print(f"scrape seller {seller_id}: 页面变狗")
                    with result_lock:
                        result_map[seller_id] = None
                    time.sleep(random.uniform(4, 10))
                    continue

                response_s = etree.HTML(resp.text)
                if response_s is None:
                    with result_lock:
                        result_map[seller_id] = None
                    continue

                site_name_list = response_s.xpath(
                    '//ul[@class="a-unordered-list a-nostyle a-vertical"]//li/span/text()'
                    '|//div[contains(@id,"detail-seller")]//span/text()')

                seller_address = '|-|'.join(site_name_list) if site_name_list else None
                print(f"scrape seller {seller_id}: {seller_address}")
                with result_lock:
                    result_map[seller_id] = seller_address

                time.sleep(random.uniform(1, 3))
            except Exception as e:
                print(f"scrape seller {seller_id} 异常: {e}")
                with result_lock:
                    result_map[seller_id] = None

    thread_count = min(5, len(seller_ids))
    threads = [threading.Thread(target=worker) for _ in range(thread_count)]
    for t in threads:
        t.start()
    for t in threads:
        t.join()

    return result_map


@flask_app.route('/seller/info', methods=['POST'])
def get_seller_info():
    payload = flask_request.get_json(silent=True) or {}
    site_name = str(payload.get('site_name', '')).strip()
    seller_ids = payload.get('seller_ids') or []

    if not site_name or not isinstance(seller_ids, list) or not seller_ids:
        return json_response({"code": 400, "message": "缺少参数 site_name 或 seller_ids"})

    seller_ids = [str(seller_id).strip() for seller_id in seller_ids if str(seller_id).strip()]
    if not seller_ids:
        return json_response({"code": 400, "message": "seller_ids 不能为空"})

    if site_name not in site_key_map:
        return json_response({"code": 400, "message": f"不支持的站点: {site_name}"})

    try:
        engine = get_remote_engine(site_name=site_name, db_type="postgresql_14")
        year = datetime.now().year
        current_df = fetch_latest_seller_rows(engine, site_name, year, seller_ids)
        if not current_df.empty:
            current_df = current_df.drop_duplicates(subset=['seller_id'], keep='first')
        found_ids = set(current_df['seller_id'].astype(str).tolist()) if not current_df.empty else set()

        missing_ids = [seller_id for seller_id in seller_ids if seller_id not in found_ids]
        previous_df = pd.DataFrame(columns=['seller_id', 'seller_address'])
        if missing_ids:
            previous_df = fetch_latest_seller_rows(engine, site_name, year - 1, missing_ids)
            if not previous_df.empty:
                previous_df = previous_df.drop_duplicates(subset=['seller_id'], keep='first')

        seller_address_map = {}
        if not current_df.empty:
            for _, row in current_df.iterrows():
                seller_address_map[str(row['seller_id'])] = row.get('seller_address')
        if not previous_df.empty:
            for _, row in previous_df.iterrows():
                seller_address_map.setdefault(str(row['seller_id']), row.get('seller_address'))

        # DB 两年都找不到 → 去页面爬取
        still_missing = [sid for sid in seller_ids if sid not in seller_address_map]
        if still_missing:
            print(f"DB未找到，去页面爬取: {still_missing}")
            scraped_map = scrape_seller_addresses(site_name, still_missing)
            seller_address_map.update(scraped_map)

        result = [
            build_seller_info_item(site_name, seller_id, seller_address_map.get(seller_id))
            for seller_id in seller_ids
        ]

        return json_response({
            "code": 200,
            "message": "success",
            "data": result
        })
    except Exception as e:
        return json_response({"code": 500, "message": f"查询失败: {str(e)}"})


if __name__ == '__main__':
    flask_app.run('0.0.0.0', 10240)
