import os
import sys
import traceback

sys.path.append(os.path.dirname(sys.path[0]))  # 上级目录
from curl_cffi import requests
from utils.db_connect import BaseUtils
import time
import random
import pandas as pd
import json
from lxml import etree
from urllib.parse import urlencode
import html
import re
from html import unescape
import urllib.parse
from datetime import datetime as date_time


class recall_cases():

    def __init__(self):
        self.ec_europa_uk_url = 'https://www.gov.uk/product-safety-alerts-reports-recalls'
        self.ec_europa_uk_data_list = []
        self.uk_drug_device = []
        self.uk_drug_device_url = 'https://www.gov.uk/drug-device-alerts'
        self.mysql_connect1()
        week = time.strftime("%W")

    def mysql_connect1(self):
        self.mysql_db = BaseUtils().mysql_connect()

    def _parse_date_str(self, date_str):
        """
                支持三种日期格式：
                  - 中文或英文格式："十月 04, 2011" 或 "September 28, 2011" （月份在前）
                  - 英文格式："18 February 2026" （日在前）
                返回标准的 "YYYY-MM-DD" 格式日期。
                """
        # 定义中英文月份映射
        month_mapping = {
            "一月": "01", "二月": "02", "三月": "03", "四月": "04", "五月": "05", "六月": "06",
            "七月": "07", "八月": "08", "九月": "09", "十月": "10", "十一月": "11", "十二月": "12",
            "January": "01", "February": "02", "March": "03", "April": "04", "May": "05", "June": "06",
            "July": "07", "August": "08", "September": "09", "October": "10", "November": "11", "December": "12"
        }

        # 去除逗号，并按空格分割
        date_parts = date_str.replace(",", "").split()
        print("解析后:", date_parts)

        if len(date_parts) != 3:
            raise ValueError(f"无法解析日期格式: {date_str}")

        # 如果第一个部分为数字，则格式为 "日 月份 年份"
        if date_parts[0].isdigit():
            day, month, year = date_parts
        else:
            # 否则格式为 "月份 日 年份"
            month, day, year = date_parts

        if month not in month_mapping:
            raise ValueError(f"未知月份: {month}")

        month_num = month_mapping[month]
        standard_date = f"{year}-{month_num}-{day.zfill(2)}"
        return standard_date

    def _request(self, headers=None, url=None, data_type=None):
        for i in range(5):
            try:
                response = requests.get(url, headers=headers, impersonate="chrome120", timeout=120)
                if data_type is None:
                    rep = etree.HTML(response.text)
                    rep.xpath('//div')
                elif 'country' in response.text:
                    pass
                else:
                    rep = etree.HTML(response.text)
                    rep.xpath('//div')
                return response
            except:
                time.sleep(random.uniform(1, 5))

    def _parse_details(self, headers, a_href):
        if 'Referer' in headers:
            del headers['Referer']
        response = self._request(headers=headers, url=a_href)
        response_detail = etree.HTML(response.text)
        if response_detail:
            recall_date_list = response_detail.xpath("//div[contains(text(),'Recall Date:')]/parent::div/text()")
            product_title_list = response_detail.xpath("//div[contains(text(),'Name of Product:')]/parent::div/text()")
            if product_title_list:
                matches = re.findall(r'[A-Za-z\-®]+(?: [A-Za-z\-®]+)*', product_title_list[-1].strip())
                if matches:
                    brand = ','.join(matches)
                else:
                    brand = None

            else:
                brand = None
            hazard_list = response_detail.xpath("//div[contains(text(),'危险:')]/parent::div//p//text()")
            image_url_list = response_detail.xpath("//div[@id='recall-gallery-img']//li/img/@src")
            recall_date = recall_date_list[-1].strip() if recall_date_list else None  # 召回日期
            product_title = product_title_list[-1].strip() if product_title_list else None  # 产品标题
            hazard = ''.join(hazard_list) if hazard_list else None  # 风险描述
            image_url = 'https://www.cpsc.gov' + image_url_list[0].strip() if image_url_list else None  # 图片
            if recall_date:
                recall_date = self._parse_date_str(recall_date)
            data_list = ['us_recalls_product', recall_date, product_title, hazard, image_url, a_href, brand]
            return data_list
        else:
            return None

    def us_recalls(self):
        headers = {
            'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
            'Accept-Encoding': 'gzip, deflate, br, zstd',
            'Accept-Language': 'zh-CN,zh;q=0.9',
            'Cache-Control': 'no-cache',
            'Pragma': 'no-cache',
            'Sec-Ch-Ua': '"Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120"',
            'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36'
        }
        url = 'https://www.cpsc.gov/Recalls'
        response = self._request(headers=headers, url=url)
        # print(response.text)
        resp = etree.HTML(response.text)

        dup_count = 0           # 连续重复计数
        DUP_THRESHOLD = 5       # 连续 5 条已存在则停止抓取

        while True:
            next_url_list = resp.xpath("//a[@aria-label='Next page']/@href")
            div_a_list = resp.xpath("//div[@class='recall-list']//div[@class='recall-list__title']/a/@href")
            div_a_title_list = resp.xpath("//div[@class='recall-list']//div[@class='recall-list__title']/a/@title")
            early_stop = False
            if div_a_list:
                for index, div_a in enumerate(div_a_list):
                    time.sleep(random.uniform(1, 5))
                    a_title = div_a_title_list[index]
                    a_href = 'https://www.cpsc.gov' + div_a
                    print('详情url：', a_href)

                    # 先按 ext_url 查重，避免无效 INSERT 触发 RemoteEngine 20 次重试
                    safe_url = a_href.replace("'", "''")
                    df_check = self.mysql_db.read_sql(
                        f"SELECT COUNT(*) AS cnt FROM recall_cases_data WHERE ext_url = '{safe_url}'"
                    )
                    if df_check['cnt'].iloc[0] > 0:
                        dup_count += 1
                        print(f'⏭️ 已存在跳过: {a_title}（连续重复 {dup_count}/{DUP_THRESHOLD}）')
                        if dup_count >= DUP_THRESHOLD:
                            print('⛔ 连续重复达阈值，判定无新数据，停止抓取')
                            early_stop = True
                            break
                        continue

                    # 新数据 → 解析详情 + 插入
                    dup_count = 0
                    data_list = self._parse_details(headers, a_href)
                    if not data_list:
                        continue
                    data_list.append(a_title)
                    data_list.append('us')
                    print(data_list)

                    # INSERT IGNORE 避免唯一键冲突触发 500 + 20 次重试
                    try:
                        with self.mysql_db.begin() as conn:
                            conn.execute(
                                'INSERT IGNORE INTO recall_cases_data '
                                '(data_type, recall_date, product_title, hazard, image_url, '
                                'ext_url, brand, recall_title, country) '
                                'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)',
                                [tuple(data_list)]
                            )
                        print(f'✅ 已存储新数据: {a_title}')
                    except Exception as e:
                        print(f'❌ 存储异常（跳过该条继续）: {e}')

                if early_stop:
                    break
            print('请求下一页url:', next_url_list)
            if next_url_list:
                next_url = 'https://www.cpsc.gov/Recalls' + next_url_list[0]
                print('22请求下一页url:', next_url)

                if '?page=1' == next_url_list[0]:
                    headers['Referer'] = 'https://www.cpsc.gov/Recalls'
                else:
                    page = re.findall(r'page=(\d+)', next_url_list[0])[0]
                    headers['Referer'] = f'https://www.cpsc.gov/Recalls?page={int(page) - 1}'
                    print("headers['Referer']::", headers['Referer'])
                response = self._request(headers=headers, url=next_url)
                resp = etree.HTML(response.text)
            else:
                break
            time.sleep(random.uniform(1, 5))

    def us_fda_gov(self):
        base_url = "https://www.fda.gov/datatables/views/ajax?"
        headers = {
            'authority': 'www.fda.gov',
            'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
            'accept-language': 'zh-CN,zh;q=0.9',
            'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36',
        }

        # 固定参数
        params = {
            "search_api_fulltext": "",
            "field_regulated_product_field": "All",
            "field_terminated_recall": "All",
            "draw": 1,  # 初始请求
            "_drupal_ajax": "1",
            "_wrapper_format": "drupal_ajax",
            "pager_element": "0",
            "view_base_path": "safety/recalls-market-withdrawals-safety-alerts/datatables-data",
            "view_display_id": "recall_datatable_block_1",
            "view_name": "recall_solr_index",
            "view_path": "/safety/recalls-market-withdrawals-safety-alerts",
            "total_items": "912"
        }

        # 生成分页 URL
        def get_page_url(start, draw):
            params["start"] = start
            params["length"] = 100
            params["draw"] = draw  # draw 递增
            return base_url + urlencode(params)

        # 生成前 5 页的 URL

        dup_count = 0
        DUP_THRESHOLD = 5
        for i in range(0, 1000, 100):
            early_stop = False
            detail_url = get_page_url(i, i // 100 + 1)
            print('当前请求url:', detail_url)
            response = self._request(headers=headers, url=detail_url)
            if response.text:
                dict_item = response.json()
                data_lists = dict_item['data']
                for data in data_lists:
                    print(data, '344444444')
                    try:
                        decoded_data = [html.unescape(item.encode().decode("unicode_escape")) for item in data]
                        time_match = re.search(r'<time datetime="([\d-]+)T', decoded_data[0])
                        date = time_match.group(1) if time_match else None
                        link_match = re.search(r'<a href="(.*?)">', decoded_data[1])
                        url = unescape(link_match.group(1)).replace("\\/", "/") if link_match else None
                        url = 'https://www.fda.gov' + url
                        print('链接', url)

                        # 先按 ext_url 查重，避免无效请求详情页 + 20 次重试
                        safe_url = url.replace("'", "''")
                        df_check = self.mysql_db.read_sql(
                            f"SELECT COUNT(*) AS cnt FROM recall_cases_data WHERE ext_url = '{safe_url}'"
                        )
                        if df_check['cnt'].iloc[0] > 0:
                            dup_count += 1
                            print(f'⏭️ 已存在跳过（连续重复 {dup_count}/{DUP_THRESHOLD}）')
                            if dup_count >= DUP_THRESHOLD:
                                print('⛔ 连续重复达阈值，停止抓取')
                                early_stop = True
                                break
                            continue
                        dup_count = 0

                        text_match = re.search(r'>(.*?)<', data[1])
                        link_text = unescape(text_match.group(1)) if text_match else None
                        print('产品名称：', link_text)
                        recall_title = decoded_data[2]
                        product_category = decoded_data[3]
                        hazard = decoded_data[4]
                        response2 = self._request(headers=headers, url=url)
                        response_detail = etree.HTML(response2.text)
                        src_list = response_detail.xpath("//div[@id='recall-photos']//img/@src")
                        Brand_list = response_detail.xpath(
                            "//div[contains(text(),'Brand Name')]/following-sibling::div//text()")
                        brand = ''.join(Brand_list).strip() if Brand_list else None
                        image_url = ('https://www.fda.gov' + src_list[0]) if src_list else None
                        row = ('us_fba_recalls', date, link_text, hazard, image_url, url, recall_title, 'us',
                               product_category, brand)
                        try:
                            with self.mysql_db.begin() as conn:
                                conn.execute(
                                    'INSERT IGNORE INTO recall_cases_data '
                                    '(data_type, recall_date, product_title, hazard, image_url, '
                                    'ext_url, recall_title, country, product_category, brand) '
                                    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
                                    [row]
                                )
                            print(f'✅ 已存储新数据: {link_text}')
                        except Exception as e:
                            print(f'❌ 存储异常（跳过该条继续）: {e}')
                    except:
                        print('解析报错')
                if early_stop:
                    break
            else:
                break

    def ec_europa_eu(self):
        '欧盟召回'
        for i in range(1, 33):
            url = 'https://ec.europa.eu/safety-gate-alerts/public/api/notification/carousel/?'
            data = {"language": "en", "page": f"{i}"}
            headers = {
                'Accept': 'application/json, text/plain, */*',
                'Accept-Encoding': 'gzip, deflate, br, zstd',
                'Accept-Language': 'zh-CN,zh;q=0.9',
                'Cache-Control': 'No-Cache',
                'Connection': 'keep-alive',
                'Content-Type': 'application/json',
                'Host': 'ec.europa.eu',
                'Origin': 'https://ec.europa.eu',
                'Pragma': 'no-cache',
                'Referer': 'https://ec.europa.eu/safety-gate-alerts/screen/search?resetSearch=true',
                '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'

            }
            if i > 9:
                headers[
                    'cookie'] = 'dtCookie=v_4_srv_-2D75_sn_09DKOP0KLAKFPP43DLPEUMVNBCEPU72F; rxVisitor=1736736746251Q036364T31CSNJ20PFTFC3EJCVTG8JO6; SGR_lng=en; dtSa=-; rxvt=1739950709830|1739946650982; dtPC=-75$548510459_53h31vEAUWSKPPJUEIKTFEGODSHLDWUPRMCUMR-0e0'
                headers['X-Dtpc'] = '-75$548510459_53h31vEAUWSKPPJUEIKTFEGODSHLDWUPRMCUMR-0e0'
            print(data, '请求列表页url:', url)
            is_None = True
            response = requests.post(url, headers=headers, impersonate="chrome120", timeout=120, json=data)
            print(response.url)
            if response:
                content = response.json()['content']
                for ids in content:
                    data_list = []
                    time.sleep(random.uniform(1, 3))
                    url = f"https://ec.europa.eu/safety-gate-alerts/public/api/notification/{ids['id']}?language=en"
                    print('请求详情url:', url)
                    headers = {
                        'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
                        'Accept-Encoding': 'ggzip, deflate, br, zstd',
                        'Accept-Language': 'zh-CN,zh;q=0.9',
                        'Cache-Control': 'No-Cache',
                        'Host': 'ec.europa.eu',
                        'Connection': 'keep-alive',
                        'Content-Type': 'application/json',
                        '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'

                    }
                    resp_detail = self._request(headers=headers, url=url, data_type=1)
                    items = resp_detail.json()
                    data_json = json.dumps(items)
                    # print(items)
                    date = items['publicationDate']
                    product_category = items['product']['productCategory']['name']
                    print(date)
                    print(product_category)
                    product_title = items['product']['versions'][0]['name']
                    recall_title = items['product']['versions'][0]['description']
                    print(product_title)
                    print(recall_title)
                    brands = items['product']['brands']
                    if brands:
                        brand = brands[0].get('brand')
                    else:
                        brand = None
                    print('brand::1', brand)
                    hazard = items['risk']['versions'][0]['riskDescription']
                    print(hazard)
                    ext_url = 'https://ec.europa.eu/safety-gate-alerts/screen/webReport/alertDetail/' + str(
                        items['id']) + '?lang=en'
                    print('ext_url::', ext_url)
                    if items['product']['photos']:
                        image_id = items['product']['photos'][0]['id']
                        image_url = f'https://ec.europa.eu/safety-gate-alerts/public/api/notification/image/{image_id}'
                    else:
                        image_url = None
                    print(image_url)
                    data_list.append(
                        [date, product_category, product_title, recall_title, hazard, 'eu_recall', image_url, 'eu',
                         ext_url, data_json, brand])

                    keys = [
                        "recall_date", "product_category", "product_title", "recall_title",
                        "hazard", "data_type", "image_url", "country", "ext_url", "data_json", "brand"
                    ]
                    dict_list = [dict(zip(keys, row)) for row in data_list]
                    result = [
                        [d['recall_date'], d['product_category'], d['product_title'], d['recall_title'], d['hazard'],
                         d['data_type'],
                         d['image_url'], d['country'], d['ext_url'], d['data_json'], d['brand']] for d in dict_list]

                    print(result)
                    for i in range(4):
                        try:
                            with self.mysql_db.begin() as conn:
                                conn.execute(
                                    f"insert into recall_cases_data (recall_date, product_category, product_title, recall_title, hazard, data_type, image_url, country, ext_url, data_json, brand) values (%s, %s, %s, %s,%s, %s, %s, %s,%s, %s, %s) ON DUPLICATE KEY UPDATE recall_date = values(recall_date),product_title = values(product_title),ext_url = values(ext_url)",
                                    result)
                            break
                        except:
                            print('报错32222222',traceback.format_exc())
                            time.sleep(20)
                if is_None == False:
                    break
            else:
                break
            time.sleep(random.uniform(2, 8))

    def ec_europa_uk(self):
        'https://www.gov.uk/product-safety-alerts-reports-recalls?page=2'
        url = 'https://www.gov.uk/product-safety-alerts-reports-recalls'
        dup_count = 0
        DUP_THRESHOLD = 5
        for i in range(50):
            headers = {
                'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signe',
                'Accept-Encoding': 'gzip, deflate, br, zstd',
                'Accept-Language': 'zh-CN,zh;q=0.9',
                'Cache-Control': 'No-Cache',
                'Connection': 'keep-alive',
                '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'
            }
            early_stop = False
            print('请求列表页：', url)
            response = requests.get(url, headers=headers, impersonate="chrome120", timeout=120)
            resp = etree.HTML(response.text)
            next_url_list = resp.xpath("//div[@class='govuk-pagination__next']/a/@href")
            li_list = resp.xpath("//div[@id='js-results']//ul//li")
            for li in li_list:
                time.sleep(random.uniform(1, 3))
                href_list = li.xpath('./div/a/@href')
                product_title_list = li.xpath('./div/a/text()')
                if href_list and product_title_list:
                    recall_title = product_title_list[0].replace('Product Safety Report:', '').replace(
                        'Product Recall:', '')
                    detail_url = 'https://www.gov.uk' + href_list[0]
                    print('详情页url,', detail_url)

                    # 先按 ext_url 查重
                    safe_url = detail_url.replace("'", "''")
                    df_check = self.mysql_db.read_sql(
                        f"SELECT COUNT(*) AS cnt FROM recall_cases_data WHERE ext_url = '{safe_url}'"
                    )
                    if df_check['cnt'].iloc[0] > 0:
                        dup_count += 1
                        print(f'⏭️ 已存在跳过（连续重复 {dup_count}/{DUP_THRESHOLD}）')
                        if dup_count >= DUP_THRESHOLD:
                            print('⛔ 连续重复达阈值，停止抓取')
                            early_stop = True
                            break
                        continue
                    dup_count = 0

                    resp = self._request(url=detail_url, headers=headers)
                    resp_html = etree.HTML(resp.text)
                    recall_date = resp_html.xpath("//dt[contains(text(),'Published')]/following-sibling::dd/text()")
                    recall_date = self._parse_date_str(recall_date[0])
                    product_category = resp_html.xpath(
                        "//dt[contains(text(),'Product category:')]/following-sibling::dd/a/text()")
                    product_title = resp_html.xpath("//p[contains(text(),'Product: ')]/text()")
                    hazard_list = resp_html.xpath("//p[contains(text(),'Hazard:')]/text()|//h2[contains(text(),'Hazard')]/following-sibling::p[1]/text()")
                    Brand_list = resp_html.xpath("//td[contains(text(),'Brand')]/following-sibling::td/text()")
                    brand = Brand_list[0].strip() if Brand_list else None
                    image_url_list = resp_html.xpath("//span[@class='attachment-inline']/a/@href")
                    product_category = product_category[0].strip() if product_category else None
                    product_title = product_title[0].strip().replace('Product:', '') if product_title else None
                    hazard_list = hazard_list[0].strip().replace('Hazard:', '') if hazard_list else None
                    image_url_list = image_url_list[0].strip() if image_url_list else None
                    row = (recall_title, detail_url, recall_date, product_category, product_title,
                           hazard_list, image_url_list, 'uk_recall', 'uk', brand)
                    try:
                        with self.mysql_db.begin() as conn:
                            conn.execute(
                                'INSERT IGNORE INTO recall_cases_data '
                                '(recall_title, ext_url, recall_date, product_category, product_title, '
                                'hazard, image_url, data_type, country, brand) '
                                'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
                                [row]
                            )
                        print(f'✅ 已存储新数据: {recall_title}')
                    except Exception as e:
                        print(f'❌ 存储异常（跳过该条继续）: {e}')
            if early_stop:
                break
            if next_url_list:
                url = 'https://www.gov.uk' + next_url_list[0]
                print('下一页url:', url)
                time.sleep(random.uniform(2, 8))
            else:
                break

    def gov_uk(self):
        url = 'https://www.gov.uk/drug-device-alerts'
        dup_count = 0
        DUP_THRESHOLD = 5
        for i in range(50):
            headers = {
                'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signe',
                'Accept-Encoding': 'gzip, deflate, br, zstd',
                'Accept-Language': 'zh-CN,zh;q=0.9',
                'Cache-Control': 'No-Cache',
                'Connection': 'keep-alive',
                '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'
            }
            print('请求列表页：', url)
            early_stop = False
            response = requests.get(url, headers=headers, impersonate="chrome120", timeout=120)
            resp = etree.HTML(response.text)
            next_url_list = resp.xpath("//div[@class='govuk-pagination__next']/a/@href")
            li_list = resp.xpath("//div[@id='js-results']//ul//li")
            for li in li_list:
                time.sleep(random.uniform(1, 3))
                href_list = li.xpath('./div/a/@href')
                product_title_list = li.xpath('./div/a/text()')
                _datetime = li.xpath('./time/@datetime')
                if href_list and product_title_list and 'Field Safety Notices' not in product_title_list[0]:
                    recall_title = product_title_list[0]
                    if ':' in recall_title:
                        product_title = re.findall(r':(.*)', recall_title)[0].strip()
                    else:
                        product_title = recall_title
                    detail_url = 'https://www.gov.uk' + href_list[0]
                    print('详情页url,', detail_url)

                    # 先按 ext_url 查重
                    safe_url = detail_url.replace("'", "''")
                    df_check = self.mysql_db.read_sql(
                        f"SELECT COUNT(*) AS cnt FROM recall_cases_data WHERE ext_url = '{safe_url}'"
                    )
                    if df_check['cnt'].iloc[0] > 0:
                        dup_count += 1
                        print(f'⏭️ 已存在跳过（连续重复 {dup_count}/{DUP_THRESHOLD}）')
                        if dup_count >= DUP_THRESHOLD:
                            print('⛔ 连续重复达阈值，停止抓取')
                            early_stop = True
                            break
                        continue
                    dup_count = 0

                    resp = self._request(url=detail_url, headers=headers)
                    resp_html = etree.HTML(resp.text)
                    recall_date = resp_html.xpath("//dt[contains(text(),'Published')]/following-sibling::dd/text()")
                    if _datetime:
                        recall_date = _datetime[0]
                    else:
                        if recall_date:
                            recall_date = self._parse_date_str(recall_date[0])
                        else:
                            recall_date = None
                    product_category = resp_html.xpath(
                        "//dt[contains(text(),'Message type:')]/following-sibling::dd/a/text()")
                    hazard_list = resp_html.xpath(
                        "//h2[contains(text(),'Brief')]/following-sibling::p[1]/text()|//h2[contains(text(),'Background')]/following-sibling::p[1]/text()")
                    image_url_list = resp_html.xpath(
                        "//div[@id='contents']//img/@src|//span[@class='attachment-inline']/a/@href")
                    product_category = product_category[0].strip() if product_category else None
                    hazard_list = hazard_list[0].strip().replace('Hazard:', '') if hazard_list else None
                    image_url_list = image_url_list[0].strip() if image_url_list else None
                    row = (recall_title, detail_url, recall_date, product_category,
                           product_title.replace('Class 4 Medicines Notification,', ''),
                           hazard_list, image_url_list, 'uk_drug_device', 'uk')
                    try:
                        with self.mysql_db.begin() as conn:
                            conn.execute(
                                'INSERT IGNORE INTO recall_cases_data '
                                '(recall_title, ext_url, recall_date, product_category, product_title, '
                                'hazard, image_url, data_type, country) '
                                'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)',
                                [row]
                            )
                        print(f'✅ 已存储新数据: {recall_title}')
                    except Exception as e:
                        print(f'❌ 存储异常（跳过该条继续）: {e}')
            if early_stop:
                break
            if next_url_list:
                url = 'https://www.gov.uk' + next_url_list[0]
                print('下一页url:', url)
                time.sleep(random.uniform(2, 8))
            else:
                break

    def global_recalls(self):
        '该函数是一次性获取所有历史数据。后面更新数据每日抓取不调用该函数'
        num_pages = 2418  # 总页数
        page_size = 20  # 每页大小

        urls = [
            f"https://globalrecalls.oecd.org/ws/search.xqy?end={i * page_size}&lang=en&order=desc&q=&sort=date&start={(i - 1) * page_size}&uiLang=en"
            for i in range(1, num_pages + 1)]
        print('请求总数：', len(urls))
        # 示例：打印前 5 页的 URL
        page = 0
        for url in urls:
            page += 1
            for i in range(5):
                try:
                    headers = {

                        'Accept': '*/*',
                        'Accept-Encoding': 'gzip, deflate, br, zstd',
                        'Accept-Language': 'zh-CN,zh-TW;q=0.9,zh;q=0.8',
                        'Cache-Control': 'no-cache',
                        'Pragma': 'no-cache',
                        'Referer': 'https://globalrecalls.oecd.org/',
                        'Sec-Ch-Ua': '"Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120"',
                        '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'

                    }
                    print('url,', url)
                    resp = requests.get(url, headers=headers, timeout=60)
                    print(resp.text)
                    print(url)
                    data_list = [[json.dumps(resp.json()), page]]
                    print('page:', page)
                    df = pd.DataFrame(data=data_list,
                                      columns=['data_json', 'page'])
                    self.mysql_db.to_sql(df, 'global_recalls_data', if_exists="append")
                    break
                except Exception as e:
                    wait_time = (i + 1) * 2
                    print(f"错误: {e}, {wait_time}秒后重试...")
                    time.sleep(wait_time)
                    print(f'请求报错。第{i}页', e)

                    self.mysql_connect1()
            break

    def get_globalrecalls(self):
        # sql = 'SELECT data_json FROM global_recalls_data'
        # df_data = pd.read_sql(sql, con=self.mysql_db)
        list_url = 'https://globalrecalls.oecd.org/ws/search.xqy?end=0&lang=en&order=desc&q=&sort=date&start=-20&uiLang=en'  # 第一页url

        # list_urls = [40, 60, 80, 100, 120, 140, 160, 180, 200, 220, 240, 260, 280, 300, 320, 340, 360, 380, 400, 420, 440, 460, 480, 500]
        # for url_num in list_urls:
        #     list_url = f'https://globalrecalls.oecd.org/ws/search.xqy?end={url_num}&lang=en&order=desc&q=&sort=date&start={url_num-20}&uiLang=en'
        print('请求url111', list_url)
        headers = {
            'Accept': '*/*',
            'Accept-Encoding': 'gzip, deflate, br, zstd',
            'Accept-Language': 'zh-CN,zh-TW;q=0.9,zh;q=0.8',
            'Cache-Control': 'no-cache',
            'Pragma': 'no-cache',
            'Referer': 'https://globalrecalls.oecd.org/',
            'Sec-Ch-Ua': '"Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120"',
            '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'

        }
        print('请求列表第一页list_url：', list_url)
        resp = requests.get(list_url, headers=headers, timeout=60)
        response = resp.json()
        result_list = response['results']
        for result in result_list:
            countryId = result['countryId']
            imageUri = result['imageUri']
            if countryId.lower() in ['us', 'ca', 'mx', 'nl', 'sa', 'se', 'pl', 'tr', 'be', 'uk', 'de', 'es', 'fr',
                                     'it',
                                     'jp']:
                date_time = result['date']
                extUrl = result['extUrl']
                title_name = result['product.name']
                _id = re.findall(r'/(\d+)$', extUrl)
                data_list = []
                headers = {
                    'Accept': '*/*',
                    'Accept-Encoding': 'gzip, deflate, br, zstd',
                    'Accept-Language': 'zh-CN,zh-TW;q=0.9,zh;q=0.8',
                    'Cache-Control': 'no-cache',
                    'Pragma': 'no-cache',
                    'Sec-Ch-Ua': '"Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120"',
                    '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'
                }
                if _id:
                    items = {}
                    encoded_url = urllib.parse.quote(imageUri)
                    imaurl = "https://globalrecalls.oecd.org/ws/getdocument.xqy?uri=" + encoded_url
                    url = f'https://ec.europa.eu/safety-gate-alerts/public/api/notification/{_id[0]}?language=en'
                    print('请求url:', url)
                    resp = requests.get(url, headers=headers, timeout=60,verify=False, impersonate="chrome")
                    if 'ENTITY_NOT_FOUN' in resp.text:
                        continue
                    items_data = resp.json()
                    brands = items_data['product']['brands']
                    if brands:
                        brand = brands[0].get('brand')
                    else:
                        brand = None
                    time.sleep(random.uniform(1, 3))
                    items['country'] = countryId
                    items['reacll_time'] = date_time
                    productCategory = items_data['product']['productCategory']['name']
                    if items_data['risk']['versions']:
                        riskDescription = items_data['risk']['versions'][0]['riskDescription']
                    else:
                        riskDescription = None
                    items['recall_title'] = items_data['product']['versions'][0]['description']
                    items['productCategory'] = productCategory
                    items['riskDescription'] = riskDescription
                    items['image_url'] = imaurl
                    items['data_type'] = 'global_recalls'
                    items['product_title'] = re.findall(r'^(.*?)\s*;', title_name + ';')[0]
                    items['ext_url'] = extUrl[:255] if extUrl else None
                    items['brand'] = brand

                    data_json = json.dumps(items_data)
                    data_list.append([items['data_type'], items['product_title'], items['productCategory'],
                                      items['reacll_time'], items['riskDescription'], items['country'],
                                      items['image_url'],
                                      items['recall_title'], items['ext_url'], data_json, items['brand']])
                    print('itemsitems::', items)

                    with self.mysql_db.begin() as conn:
                        conn.execute(
                            'INSERT IGNORE INTO recall_cases_data '
                            '(data_type, product_title, product_category, recall_date, hazard, '
                            'country, image_url, recall_title, ext_url, data_json, brand) '
                            'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
                            data_list
                        )
                    print('存储成功', len(data_list))
                else:
                    print('没有解析到id')
                    items = {}
                    url = result['uri']
                    items['country'] = countryId  # 站点
                    encoded_url = urllib.parse.quote(url)
                    _url = 'https://globalrecalls.oecd.org/ws/getrecall.xqy?uiLang=en&uri=' + encoded_url
                    print('_url::', _url)
                    resp = requests.get(_url, headers=headers, timeout=60)
                    recall_detail = resp.json()  # 避免覆盖外层循环变量 result
                    print("result::", recall_detail)
                    time.sleep(random.uniform(1, 3))
                    extUrl = recall_detail['recall']['extUrl']  # 详情url
                    images = recall_detail['recall'].get('images', [])
                    if images:
                        imageUri = images[0]['imageUri']
                        encode_imageUri = urllib.parse.quote(imageUri)
                        imaurl = f"https://globalrecalls.oecd.org/ws/getdocument.xqy?uri={encode_imageUri}"
                    else:
                        imaurl = None
                    date_time = recall_detail['recall']['date']
                    items['reacll_time'] = date_time
                    title_name = recall_detail['recall']['product.name']
                    recall_title = recall_detail['recall']['product.desc']
                    if recall_title is None:
                        recall_title = images[0].get('alt.text') if images else None
                    if recall_title:
                        recall_title = recall_title.replace('Image of ', '')  # 修复：结果需赋值
                    hazard = recall_detail['recall']['hazard']
                    items['recall_title'] = recall_title
                    items['productCategory'] = recall_detail['recall']['product.type']
                    items['riskDescription'] = hazard
                    items['image_url'] = imaurl
                    items['data_type'] = 'global_recalls'
                    items['product_title'] = re.findall(r'^(.*?)\s*;', (title_name or '') + ';')[0] if title_name else None
                    items['ext_url'] = extUrl[:255] if extUrl else None
                    # product_title 为 None 时唯一索引(product_title,recall_date,ext_url)失效
                    # MySQL NULL≠NULL，需手动按 ext_url 查重
                    if items['product_title'] is None and items['ext_url']:
                        safe_url = items['ext_url'].replace("'", "''")
                        df_check = self.mysql_db.read_sql(
                            f"SELECT COUNT(*) as cnt FROM recall_cases_data WHERE ext_url = '{safe_url}'"
                        )
                        if df_check['cnt'].iloc[0] > 0:
                            print('已存在跳过(product_title为空):', items['ext_url'][:80])
                            continue
                    data_json = json.dumps(recall_detail)
                    data_list.append([items['data_type'], items['product_title'], items['productCategory'],
                                      items['reacll_time'], items['riskDescription'], items['country'],
                                      items['image_url'],
                                      items['recall_title'], items['ext_url'], data_json])
                    print('没有解析到id的数据：', items)

                    with self.mysql_db.begin() as conn:
                        conn.execute(
                            'INSERT IGNORE INTO recall_cases_data '
                            '(data_type, product_title, product_category, recall_date, hazard, '
                            'country, image_url, recall_title, ext_url, data_json) '
                            'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
                            data_list
                        )
                    print('没有解析到id 存储成功', len(data_list))

    def webgate_ec(self):
        '欧盟食品和饲料快速预警系统'
        headers = {
            "Accept": "application/json, text/plain, */*",
            "Accept-Encoding": "gzip, deflate, br, zstd",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Cache-Control": "No-Cache",
            "Connection": "keep-alive",
            "Content-Length": "378",
            "Content-Type": "application/json",
            "Host": "webgate.ec.europa.eu",
            "Origin": "https://webgate.ec.europa.eu",
            "Pragma": "no-cache",
            "Referer": "https://webgate.ec.europa.eu/rasff-window/screen/search?searchQueries=eyJkYXRlIjp7InN0YXJ0UmFuZ2UiOiIiLCJlbmRSYW5nZSI6IiJ9LCJjb3VudHJpZXMiOnt9LCJ0eXBlIjp7fSwibm90aWZpY2F0aW9uU3RhdHVzIjp7fSwicHJvZHVjdCI6e30sInJpc2siOnt9LCJyZWZlcmVuY2UiOiIiLCJzdWJqZWN0IjoiRm9vZCBjb250YWN0IG1hdGVyaWFscyJ9",
            "Sec-Ch-Ua": '"Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120"',
            "Sec-Ch-Ua-Mobile": "?0",
            "Sec-Ch-Ua-Platform": '"Windows"',
            "Sec-Fetch-Dest": "empty",
            "Sec-Fetch-Mode": "cors",
            "Sec-Fetch-Site": "same-origin",
            "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",
            "X-Requested-With": "XMLHttpRequest",
        }

        data = {"parameters": {"pageNumber": 1, "itemsPerPage": 25}, "notificationReference": None,
                "subject": "Food contact materials", "notifyingCountry": None, "originCountry": None,
                "distributionCountry": None, "notificationType": None, "notificationStatus": None,
                "notificationClassification": None, "notificationBasis": None, "productCategory": None,
                "actionTaken": None,
                "hazardCategory": None, "riskDecision": None}
        url = 'https://webgate.ec.europa.eu/rasff-window/backend/public/notification/search/consolidated/'
        response = requests.post(url, headers=headers, json=data, timeout=120)
        print(response.text)
        response_json = json.loads(response.text)
        notifications_list = response_json.get("notifications", [])
        if notifications_list:
            dup_count = 0
            DUP_THRESHOLD = 5
            for notif in notifications_list:
                items_data = {}
                productType = notif['productType']['description']
                items_data['product_category'] = productType if productType else None
                subject_title = notif['subject']
                items_data['recall_title'] = subject_title if subject_title else None
                items_data['hazard'] = items_data['recall_title']
                recall_date = notif['ecValidationDate']
                if recall_date:
                    dt = date_time.strptime(recall_date, "%d-%m-%Y %H:%M:%S")
                    items_data['recall_date'] = dt.strftime("%Y-%m-%d")
                else:
                    items_data['recall_date'] = None
                country = notif['notifyingCountry']['isoCode']
                items_data['country'] = country if country else None
                ext_url = 'https://webgate.ec.europa.eu/rasff-window/screen/notification/' + str(notif['notifId'])
                items_data['ext_url'] = ext_url

                # 先按 ext_url 查重，避免无效详情请求 + 20 次重试
                safe_url = ext_url.replace("'", "''")
                df_check = self.mysql_db.read_sql(
                    f"SELECT COUNT(*) AS cnt FROM recall_cases_data WHERE ext_url = '{safe_url}'"
                )
                if df_check['cnt'].iloc[0] > 0:
                    dup_count += 1
                    print(f'⏭️ 已存在跳过（连续重复 {dup_count}/{DUP_THRESHOLD}）')
                    if dup_count >= DUP_THRESHOLD:
                        print('⛔ 连续重复达阈值，停止抓取')
                        break
                    continue
                dup_count = 0

                self.webgate_ec_product(notif['notifId'], items_data)
                time.sleep(random.uniform(5, 10))

    def webgate_ec_product(self, notif_id, items_data):
        headers1 = {
            "Accept": "application/json, text/plain, */*",
            "Accept-Encoding": "gzip, deflate, br, zstd",
            "Accept-Language": "zh-CN,zh;q=0.9",
            "Cache-Control": "No-Cache",
            "Connection": "keep-alive",
            "Host": "webgate.ec.europa.eu",
            "Pragma": "no-cache",
            "Referer": f"https://webgate.ec.europa.eu/rasff-window/screen/notification/{notif_id}",
            "Sec-Ch-Ua": '"Not_A Brand";v="8", "Chromium";v="120", "Google Chrome";v="120"',
            "Sec-Ch-Ua-Mobile": "?0",
            "Sec-Ch-Ua-Platform": '"Windows"',
            "Sec-Fetch-Dest": "empty",
            "Sec-Fetch-Mode": "cors",
            "Sec-Fetch-Site": "same-origin",
            "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",
            "X-Requested-With": "XMLHttpRequest",
        }

        url1 = f'https://webgate.ec.europa.eu/rasff-window/backend/public/notification/view/id/{notif_id}/'
        print('详情url:', url1)
        response = requests.get(url1, headers=headers1, timeout=120)
        response_json = json.loads(response.text)
        product = response_json.get("product", {})
        title = product.get("description")
        items_data['product_title'] = title
        items_data['data_type'] = 'europa_ec'
        print("欧盟食品和饲料快速预警系统: ", items_data)
        items_data['data_json'] = response.text
        columns = ['data_type', 'product_title', 'product_category', 'recall_date',
                   'hazard',
                   'country', 'recall_title', 'ext_url', 'data_json',
                   ]
        data_list = []
        i_list = []
        for i in columns:
            i_list.append(items_data[i])
        data_list.append(i_list)
        try:
            with self.mysql_db.begin() as conn:
                conn.execute(
                    'INSERT IGNORE INTO recall_cases_data '
                    '(data_type, product_title, product_category, recall_date, hazard, '
                    'country, recall_title, ext_url, data_json) '
                    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)',
                    [tuple(i_list)]
                )
            print(f'✅ 已存储新数据: {items_data.get("product_title")}')
        except Exception as e:
            print(f'❌ 存储异常（跳过该条继续）: {e}')

    def run(self):
        # self.global_recalls()
        self.get_globalrecalls()
        self.us_recalls()
        self.us_fda_gov()
        self.ec_europa_eu()
        self.ec_europa_uk()
        self.gov_uk()
        self.webgate_ec()
        # """


#         数据类型，属于那个国的
# eu_recall
# global_recalls
# uk_drug_device 1
# uk_recall 2
# us_fba_recalls 3
# us_recalls_product
#         """
#         with self.mysql_db.begin() as conn:
#             sql = "SELECT id,product_title FROM recall_cases_data WHERE data_type='us_recalls_product'"
#             df_data = pd.read_sql(sql, con=self.mysql_db)
#             df_data['id'] = df_data['id'].fillna('').astype(str)
#             df_data['product_title'] = df_data['product_title'].fillna('').astype(str)
#             data_json_list = list(df_data.id+ "|=||+||" + df_data.product_title)
#             for data_json_id in data_json_list:
#                 if data_json_id:
#                     data_json_id_list = data_json_id.split('|=||+||')
#                     id = data_json_id_list[0]
#                     product_title = data_json_id_list[1]
#                     print(product_title)
#                     if bool(re.search(r'[\u4e00-\u9fff]', product_title)):
#                         # print(product_title,'23333333')
#                         matches = re.findall(r'[A-Za-z\-®]+(?: [A-Za-z\-®]+)*', product_title.strip())
#                         if matches:
#                             brand = ','.join(matches)
#                         else:
#                             brand = None
#                         print(id, brand,'23444444444')
#                         if brand:
#                             brand = brand.replace('"','').replace("'","")
#                             up_sql = f"""update recall_cases_data set brand ="{brand}" WHERE id={id}"""
#                             print(up_sql)
#                             conn.execute(up_sql)


if __name__ == '__main__':
    recall_cases = recall_cases()
    recall_cases.run()
#
