import pandas as pd
from sqlalchemy import text

from orm import engine

# ===============================
# 数据库连接
# ===============================

sql_file = "query_top35_products.sql"

# 指定对比列表
specified_list = [
    '61','68','65','66','59','71','77','7','38','95','58','90','56','57','97','98',
    '27','8','11','9','21','96','99','100','44','45','46','47','48','49','50','51',
    '52','53','54','55'
]
specified_set = set(specified_list)

# ===============================
# 执行 SQL 查询
# ===============================
with open(sql_file, "r", encoding="utf-8") as f:
    str_sql = f.read()

# 使用 connect() 解决 AttributeError
with engine.connect() as conn:
    df = pd.read_sql(text(str_sql), conn)

print(f"[信息] 查询完成，获取到 {len(df)} 条记录。")

# ===============================
# 按 ga_id 分组计算匹配情况
# ===============================
all_results = []

for ga_id, group in df.groupby("ga_id"):
    vulnerable35_product = group['id_product'].astype(str).tolist()
    vulnerable_set = set(vulnerable35_product)

    matching = vulnerable_set & specified_set
    extra = vulnerable_set - specified_set
    missing = specified_set - vulnerable_set

    all_results.append({
        "ga_id": ga_id,
        "vulnerable35": ",".join(vulnerable35_product),
        "matching_count": len(matching),
        "matching_products": ",".join(matching),
        "extra_count": len(extra),
        "extra_products": ",".join(extra),
        "missing_count": len(missing),
        "missing_products": ",".join(missing)
    })

# ===============================
# 输出 CSV
# ===============================
df_results = pd.DataFrame(all_results)
df_results.to_csv("vulnerable20_match_results_new1122.csv", index=False, encoding='utf-8-sig')
print("[完成] 已生成 vulnerable35_match_results.csv")
