import os, sys

BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
sys.path.append(BASE_DIR)
from aidso_geo.utils.bh_utils import query_data, insert_data


def _ch_escape_string(s: str) -> str:
    # s = s.replace("\x00", "")     # 防御性：去掉NUL
    # s = s.replace("\\", "\\\\")   # 可保留，可不保留（URL一般没有\）
    # s = s.replace("'", "''")      # 关键：单引号用两个单引号
    # return f"'{s}'"
    return s.replace("\\", "\\\\").replace("'", "''")


def generate_numeric_url_id(urls):
    url_str = ",".join([f"'{_ch_escape_string(url)}'" for url in urls if isinstance(url, str)])

    rows = query_data(f"select id,url from geo_dim_url where url in ({url_str})") or []

    url_id_map = {}
    for r in rows:
        if r and r.get("url"):
            url_id_map[r["url"]] = r.get("id")

    missing_urls = list(dict.fromkeys(u for u in urls if u not in url_id_map))

    if missing_urls:
        insert_list = [{"url": u} for u in missing_urls]
        insert_data("geo_dim_url", insert_list)

        missing_str = ",".join([f"'{url}'" for url in missing_urls])
        rows2 = query_data(f"select id,url from geo_dim_url where url in ({missing_str})") or []
        for r in rows2:
            if r and r.get("url"):
                url_id_map[r["url"]] = r.get("id")

    result_map = {u: url_id_map.get(u) for u in urls}
    return result_map



def generate_numeric_url_id_v2(urls):
    """
    根据 URL 列表生成 url -> id 映射：
    1. 先查 geo_dim_url 已存在的 URL
    2. 不存在的 URL 批量插入
    3. 再查一次缺失 URL
    4. 返回原始 URL 对应的 id
    """

    # 兼容单个字符串
    if isinstance(urls, str):
        urls = [urls]

    if not urls:
        return {}

    # 清洗 URL，只保留有效字符串，并去重，保持原顺序
    url_list = list(dict.fromkeys(
        url.strip()
        for url in urls
        if isinstance(url, str) and url.strip()
    ))

    if not url_list:
        return {}

    url_id_map = {}

    # 1. 先查已存在的 URL
    sql = """
        SELECT id, url
        FROM geo_dim_url
        WHERE url IN %(urls)s
    """

    rows = query_data(sql, {"urls": tuple(url_list)}) or []

    for r in rows:
        if r and r.get("url"):
            url_id_map[r["url"]] = r.get("id")

    # 2. 找出没有查到的 URL
    missing_urls = [
        url for url in url_list
        if url not in url_id_map
    ]

    # 3. 插入缺失 URL
    if missing_urls:
        insert_list = [
            {"url": url}
            for url in missing_urls
        ]

        insert_data("geo_dim_url", insert_list)

        # 4. 插入后再查一次
        rows2 = query_data(sql, {"urls": tuple(missing_urls)}) or []

        for r in rows2:
            if r and r.get("url"):
                url_id_map[r["url"]] = r.get("id")

    # 5. 返回结果，保持原始 urls 的映射关系
    result_map = {}

    for url in urls:
        if isinstance(url, str) and url.strip():
            clean_url = url.strip()
            result_map[url] = url_id_map.get(clean_url)
        else:
            result_map[url] = None

    return result_map

if __name__ == '__main__':


    urls = ["https://www.iesdouy''''in.com/share/video/760323182770899787'3",'https://beautygol.com/foundation-brands/','https://www.iesdouyin.com/share/video/7593368417316141242','https://www.iesdouyin.com/share/video/7588851834841129403','https://www.iesdouyin.com/share/video/7592518076731383796','https://www.voguescandinavia.com/articles/best-foundation','https://www.iesdouyin.com/share/video/7592931462669650609','https://www.iesdouyin.com/share/video/7611501293928833189','https://www.makeupbrands.org/best-makeup-foundation/','https://m.chinabgao.com/top/brand/103376.html','https://www.womenshealthmag.com/tw/beauty/make-up/g63062750/foundation/','https://www.beauty321.com/post/70204?utm_campaign=post&utm_medium=postinside&utm_source=ETtodayfashion','https://www.elle.com/tw/beauty/news/a70086124/2026-elle-beauty-awards-taiwan/']
    print(generate_numeric_url_id_v2(urls))

    # {'http: // m.toutiao.com / group / 7598066097035805219 / ': 7421792398252101632,
    #  'http: // m.toutiao.com / group / 7598067408829514280 / ': 7421792398252101633,
    #  'https: // www.iesdouyin.com / share / video / 7527608280689904956': 7421792398252101634}


    # print(quto)
