scripts/19_eval_triple.py

"""
Step 19: 3-way 비교 평가 — bge-m3 vs +HyDE vs +Contextual+HyDE

Contextual Retrieval(18번 스크립트) 적용 후 개선 효과 측정.
기존 Menusearch_100_answer.xlsx의 bge-m3 / +HyDE 결과를 baseline으로 읽고,
새로 재빌드된 인덱스로 +Contextual+HyDE 검색을 실행해 3-way 비교.

실행 순서:
    1. python scripts/18_contextual_retrieval.py  (인덱스 재빌드 포함)
    2. python scripts/19_eval_triple.py

출력:
    data/generated/Menusearch_triple_YYYYMMDD.xlsx

Excel 컬럼:
    A: No  B: 쿼리  C: 정답 메뉴
    D~H : [bge-m3]          Top1~5   (baseline, 이전 결과)
    I   : [bge-m3]          결과
    J~N : [+HyDE]           Top1~5   (baseline, 이전 결과)
    O   : [+HyDE]           결과
    P~T : [+Contextual+HyDE] Top1~5  (신규 검색)
    U   : [+Contextual+HyDE] 결과
    V   : 변화 (+HyDE vs +Contextual+HyDE)
"""
import sys
import re
from datetime import datetime
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent))

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment

from core.search_engine import MenuSearchEngine

# ── 경로 ──────────────────────────────────────────────────────────────────────
BASE_DIR   = Path(__file__).parent.parent
BASELINE_PATH = BASE_DIR / "data" / "generated" / "Menusearch_100_answer.xlsx"
TODAY     = datetime.now().strftime("%Y%m%d")
OUT_PATH  = BASE_DIR / "data" / "generated" / f"Menusearch_triple_{TODAY}.xlsx"

RANK_MAP = {"Top1 정답": 3, "Top3 정답": 2, "Top5 정답": 1, "오답": 0, "평가제외": -1}


# ── 정답 비교 유틸 ─────────────────────────────────────────────────────────────

def normalize_path(path: str) -> str:
    if not path:
        return ""
    path = re.sub(r'\s*>\s*', '>', path)
    path = re.sub(r'\(.*?\)', '', path)
    return path.strip().lower()


def matches(pred: str, ans: str) -> bool:
    if not pred or not ans:
        return False
    if pred == ans:
        return True
    pred_end = pred.split(">")[-1].strip()
    ans_end  = ans.split(">")[-1].strip()
    if pred_end and ans_end and pred_end == ans_end:
        pred_parts = pred.split(">")
        ans_parts  = ans.split(">")
        overlap = sum(1 for p in pred_parts if p in ans_parts)
        return overlap >= min(2, len(ans_parts))
    return False


def evaluate(answer_cell, top_paths: list) -> tuple:
    if not answer_cell or pd.isna(answer_cell):
        return None, None, None
    ans_lower = str(answer_cell).lower()
    first_line = ans_lower.split("\n")[0]
    if ">" not in first_line and any(
        first_line.startswith(m) for m in ["hts", "계좌개설", "마이페이지"]
    ):
        return None, None, None
    valid_answers = [normalize_path(a) for a in str(answer_cell).split("\n") if a.strip()]
    norm_top = [normalize_path(p) for p in top_paths if p]
    def hit_at(k):
        return any(matches(p, a) for p in norm_top[:k] for a in valid_answers)
    return hit_at(1), hit_at(3), hit_at(5)


def label_from_eval(a1, a3, a5) -> str:
    if a1 is None: return "평가제외"
    if a1:         return "Top1 정답"
    if a3:         return "Top3 정답"
    if a5:         return "Top5 정답"
    return "오답"


# ── 통계 ──────────────────────────────────────────────────────────────────────

def calc_stats(labels: list[str]) -> tuple:
    a1_list = [lbl == "Top1 정답" for lbl in labels if lbl != "평가제외"]
    a3_list = [lbl in ("Top1 정답", "Top3 정답") for lbl in labels if lbl != "평가제외"]
    a5_list = [lbl in ("Top1 정답", "Top3 정답", "Top5 정답") for lbl in labels if lbl != "평가제외"]
    valid = sum(1 for lbl in labels if lbl != "평가제외")
    skip  = len(labels) - valid
    n1 = sum(a1_list); n3 = sum(a3_list); n5 = sum(a5_list)
    r1 = n1 / valid if valid else 0
    r3 = n3 / valid if valid else 0
    r5 = n5 / valid if valid else 0
    return valid, skip, n1, n3, n5, r1, r3, r5


# ── 신규 검색 ─────────────────────────────────────────────────────────────────

def run_contextual_search(engine, queries: list[str], answers: list, use_hyde: bool) -> list[dict]:
    label = "+Contextual+HyDE" if use_hyde else "+Contextual"
    print(f"\n[검색] {label} 모드...")
    results = []
    for i, (query, answer_raw) in enumerate(zip(queries, answers)):
        hits  = engine.search(query, top_n=5, threshold=0.0, use_hyde=use_hyde)
        paths = [h.get("menu_path", "") for h in hits]
        while len(paths) < 5:
            paths.append("")
        a1, a3, a5 = evaluate(answer_raw, paths)
        lbl = label_from_eval(a1, a3, a5)
        status = "[O]" if a1 else ("[  ]" if a1 is None else "[X]")
        print(f"  {status} [{i+1:3d}] {query[:28]:<28} → {paths[0][:40]}")
        results.append({"paths": paths, "label": lbl, "a1": a1, "a3": a3, "a5": a5})
    return results


# ── 메인 ─────────────────────────────────────────────────────────────────────

def main():
    print("[19_triple] 3-way 비교 평가 시작")

    # ── 1. baseline 로드 ──────────────────────────────────────────────────────
    if not BASELINE_PATH.exists():
        print(f"[오류] 기준 파일 없음: {BASELINE_PATH}")
        print("  먼저 python scripts/17_eval_comparison.py 를 실행하세요.")
        sys.exit(1)

    df_base = pd.read_excel(BASELINE_PATH, sheet_name="시트1")
    # 숫자 No 행만 필터
    df_base = df_base[pd.to_numeric(df_base["No"], errors="coerce").notna()].reset_index(drop=True)
    print(f"[19_triple] baseline {len(df_base)}개 행 로드 (from {BASELINE_PATH.name})")

    # baseline 컬럼 자동 탐지 (17_eval 출력 컬럼명 기준)
    bge_top_cols  = [c for c in df_base.columns if "[bge-m3] Top" in str(c)]
    bge_lbl_col   = next((c for c in df_base.columns if "[bge-m3] 결과" in str(c)), None)
    hyde_top_cols = [c for c in df_base.columns if "[+HyDE] Top"  in str(c)]
    hyde_lbl_col  = next((c for c in df_base.columns if "[+HyDE] 결과" in str(c)), None)

    if not bge_top_cols or not hyde_top_cols:
        print("[오류] baseline Excel에서 [bge-m3] / [+HyDE] 컬럼을 찾을 수 없습니다.")
        print("  17_eval_comparison.py 로 먼저 baseline을 생성해 주세요.")
        sys.exit(1)

    queries = df_base["쿼리"].astype(str).str.strip().tolist()
    answers = [row if pd.notna(row) else None for row in df_base["정답 메뉴"]]
    bge_labels  = df_base[bge_lbl_col].astype(str).tolist()  if bge_lbl_col  else ["평가제외"] * len(df_base)
    hyde_labels = df_base[hyde_lbl_col].astype(str).tolist() if hyde_lbl_col else ["평가제외"] * len(df_base)

    # ── 2. 검색엔진 초기화 ────────────────────────────────────────────────────
    print("[19_triple] 검색엔진 초기화 (Contextual Retrieval 인덱스)…")
    engine = MenuSearchEngine.get_instance()
    print("[19_triple] 준비 완료")

    # ── 3. 신규 검색 ──────────────────────────────────────────────────────────
    ctx_results  = run_contextual_search(engine, queries, answers, use_hyde=False)
    ctxh_results = run_contextual_search(engine, queries, answers, use_hyde=True)

    ctx_labels  = [r["label"] for r in ctx_results]
    ctxh_labels = [r["label"] for r in ctxh_results]

    # ── 4. 통계 ───────────────────────────────────────────────────────────────
    b_valid,  b_skip,  b_n1,  b_n3,  b_n5,  b_r1,  b_r3,  b_r5  = calc_stats(bge_labels)
    h_valid,  h_skip,  h_n1,  h_n3,  h_n5,  h_r1,  h_r3,  h_r5  = calc_stats(hyde_labels)
    c_valid,  c_skip,  c_n1,  c_n3,  c_n5,  c_r1,  c_r3,  c_r5  = calc_stats(ctx_labels)
    ch_valid, ch_skip, ch_n1, ch_n3, ch_n5, ch_r1, ch_r3, ch_r5 = calc_stats(ctxh_labels)

    print("\n" + "=" * 80)
    print("== 3-way 비교 결과 ==")
    print("=" * 80)
    print(f"{'':20s} {'bge-m3':>12s}  {'+ HyDE':>14s}  {'+ Ctx':>13s}  {'+ Ctx+HyDE':>14s}")
    print(f"  {'Acc@1':<18s} {b_n1}/{b_valid}={b_r1:.1%}  {h_n1}/{h_valid}={h_r1:.1%}  {c_n1}/{c_valid}={c_r1:.1%}  {ch_n1}/{ch_valid}={ch_r1:.1%}")
    print(f"  {'Acc@3':<18s} {b_n3}/{b_valid}={b_r3:.1%}  {h_n3}/{h_valid}={h_r3:.1%}  {c_n3}/{c_valid}={c_r3:.1%}  {ch_n3}/{ch_valid}={ch_r3:.1%}")
    print(f"  {'Acc@5':<18s} {b_n5}/{b_valid}={b_r5:.1%}  {h_n5}/{h_valid}={h_r5:.1%}  {c_n5}/{c_valid}={c_r5:.1%}  {ch_n5}/{ch_valid}={ch_r5:.1%}")
    print("=" * 80)

    # ── 5. DataFrame 구성 ─────────────────────────────────────────────────────
    rows = []
    for i, row in df_base.iterrows():
        cr  = ctx_results[i]
        chr = ctxh_results[i]
        hyde_rank = RANK_MAP.get(hyde_labels[i],  -1)
        ctxh_rank = RANK_MAP.get(ctxh_labels[i], -1)
        if hyde_labels[i] == "평가제외":
            change = "제외"
        elif ctxh_rank > hyde_rank:
            change = "개선"
        elif ctxh_rank < hyde_rank:
            change = "하락"
        else:
            change = "동일"

        bge_tops  = [row.get(c, "") for c in bge_top_cols[:5]]
        hyde_tops = [row.get(c, "") for c in hyde_top_cols[:5]]
        while len(bge_tops)  < 5: bge_tops.append("")
        while len(hyde_tops) < 5: hyde_tops.append("")

        rows.append({
            "No":       row["No"],
            "쿼리":     str(row["쿼리"]).strip(),
            "정답 메뉴": row["정답 메뉴"] if pd.notna(row.get("정답 메뉴")) else "",
            # baseline bge-m3
            "bge Top1": bge_tops[0], "bge Top2": bge_tops[1], "bge Top3": bge_tops[2],
            "bge Top4": bge_tops[3], "bge Top5": bge_tops[4],
            "bge 결과": bge_labels[i],
            # baseline +HyDE
            "hyde Top1": hyde_tops[0], "hyde Top2": hyde_tops[1], "hyde Top3": hyde_tops[2],
            "hyde Top4": hyde_tops[3], "hyde Top5": hyde_tops[4],
            "hyde 결과": hyde_labels[i],
            # +Contextual+HyDE (신규)
            "ctxh Top1": chr["paths"][0], "ctxh Top2": chr["paths"][1],
            "ctxh Top3": chr["paths"][2], "ctxh Top4": chr["paths"][3],
            "ctxh Top5": chr["paths"][4],
            "ctxh 결과": ctxh_labels[i],
            # 변화
            "변화": change,
        })

    df_out = pd.DataFrame(rows)
    df_out.columns = [
        "No", "쿼리", "정답 메뉴",
        "[bge-m3] Top1", "[bge-m3] Top2", "[bge-m3] Top3", "[bge-m3] Top4", "[bge-m3] Top5",
        "[bge-m3] 결과",
        "[+HyDE] Top1",  "[+HyDE] Top2",  "[+HyDE] Top3",  "[+HyDE] Top4",  "[+HyDE] Top5",
        "[+HyDE] 결과",
        "[+Ctx+HyDE] Top1", "[+Ctx+HyDE] Top2", "[+Ctx+HyDE] Top3",
        "[+Ctx+HyDE] Top4", "[+Ctx+HyDE] Top5",
        "[+Ctx+HyDE] 결과",
        "변화",
    ]

    df_out.to_excel(OUT_PATH, sheet_name="시트1", index=False)

    # ── 6. Excel 스타일링 ──────────────────────────────────────────────────────
    wb = load_workbook(OUT_PATH)
    ws = wb["시트1"]

    col_widths = {
        "A": 5,  "B": 34, "C": 34,
        "D": 28, "E": 28, "F": 28, "G": 28, "H": 28, "I": 12,
        "J": 28, "K": 28, "L": 28, "M": 28, "N": 28, "O": 12,
        "P": 28, "Q": 28, "R": 28, "S": 28, "T": 28, "U": 12,
        "V": 9,
    }
    for col, w in col_widths.items():
        ws.column_dimensions[col].width = w
    ws.row_dimensions[1].height = 28

    HDR_BGE  = "1F4E79"
    HDR_HYDE = "1A5276"
    HDR_CTX  = "145A32"   # 초록 계열 (Contextual)
    HDR_ETC  = "2C3E50"

    FILL_MAP = {
        "Top1 정답": PatternFill("solid", start_color="C6EFCE"),
        "Top3 정답": PatternFill("solid", start_color="FFEB9C"),
        "Top5 정답": PatternFill("solid", start_color="FCE4D6"),
        "오답":      PatternFill("solid", start_color="FFC7CE"),
        "평가제외":  PatternFill("solid", start_color="F2F2F2"),
    }
    CHANGE_FILL = {
        "개선": PatternFill("solid", start_color="ABEBC6"),
        "하락": PatternFill("solid", start_color="F1948A"),
        "동일": PatternFill("solid", start_color="EBF5FB"),
        "제외": PatternFill("solid", start_color="F2F2F2"),
    }
    CHANGE_FC = {"개선": "1E8449", "하락": "922B21", "동일": "1A5276", "제외": "7F7F7F"}

    # 헤더
    for cell in ws[1]:
        cl = cell.column_letter
        if cl in ("A", "B", "C", "V"):
            bg = HDR_ETC
        elif cl in ("D", "E", "F", "G", "H", "I"):
            bg = HDR_BGE
        elif cl in ("J", "K", "L", "M", "N", "O"):
            bg = HDR_HYDE
        else:
            bg = HDR_CTX
        cell.fill = PatternFill("solid", start_color=bg)
        cell.font = Font(bold=True, color="FFFFFF", name="맑은 고딕", size=9)
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

    # 데이터 행
    for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
        rc = {cell.column_letter: cell for cell in row}
        bge_lbl  = rc.get("I").value  if rc.get("I")  else ""
        hyde_lbl = rc.get("O").value  if rc.get("O")  else ""
        ctxh_lbl = rc.get("U").value  if rc.get("U")  else ""
        chg_val  = rc.get("V").value  if rc.get("V")  else ""

        for cell in row:
            cell.font = Font(name="맑은 고딕", size=8)
            cell.alignment = Alignment(wrap_text=True, vertical="center")

        for col_letter, lbl in [("I", bge_lbl), ("O", hyde_lbl), ("U", ctxh_lbl)]:
            if rc.get(col_letter) and lbl:
                rc[col_letter].fill = FILL_MAP.get(lbl, FILL_MAP["평가제외"])
                rc[col_letter].font = Font(name="맑은 고딕", size=8, bold=True)
                rc[col_letter].alignment = Alignment(horizontal="center", vertical="center")

        if rc.get("V") and chg_val:
            rc["V"].fill = CHANGE_FILL.get(chg_val, CHANGE_FILL["동일"])
            rc["V"].font = Font(name="맑은 고딕", size=8, bold=True,
                                color=CHANGE_FC.get(chg_val, "000000"))
            rc["V"].alignment = Alignment(horizontal="center", vertical="center")

        if rc.get("A"):
            rc["A"].alignment = Alignment(horizontal="center", vertical="center")

    # 요약 행
    ws.append([])
    ws.append([
        "", "【요약】",
        f"유효 {b_valid}개 / 제외 {b_skip}개",
        f"Acc@1: {b_r1:.1%}", f"Acc@3: {b_r3:.1%}", f"Acc@5: {b_r5:.1%}",
        "", "", "",
        f"Acc@1: {h_r1:.1%}", f"Acc@3: {h_r3:.1%}", f"Acc@5: {h_r5:.1%}",
        "", "", "",
        f"Acc@1: {ch_r1:.1%}", f"Acc@3: {ch_r3:.1%}", f"Acc@5: {ch_r5:.1%}",
        "", "", "",
        "",
    ])
    sum_fill = PatternFill("solid", start_color="D9E1F2")
    for cell in ws[ws.max_row]:
        cell.fill = sum_fill
        cell.font = Font(bold=True, name="맑은 고딕", size=10)
        cell.alignment = Alignment(horizontal="center", vertical="center")

    # 개선 행
    ws.append([
        "", "【개선폭 vs bge-m3 baseline】", "",
        "+0.0%", "+0.0%", "+0.0%", "", "", "",
        f"+{h_r1 - b_r1:.1%}", f"+{h_r3 - b_r3:.1%}", f"+{h_r5 - b_r5:.1%}",
        "", "", "",
        f"+{ch_r1 - b_r1:.1%}", f"+{ch_r3 - b_r3:.1%}", f"+{ch_r5 - b_r5:.1%}",
        "", "", "",
        "",
    ])
    delta_fill = PatternFill("solid", start_color="E2EFDA")
    for cell in ws[ws.max_row]:
        cell.fill = delta_fill
        cell.font = Font(bold=True, name="맑은 고딕", size=10, color="375623")
        cell.alignment = Alignment(horizontal="center", vertical="center")

    wb.save(OUT_PATH)
    print(f"\n[19_triple] 저장 완료: {OUT_PATH}")
    print(f"  bge-m3 baseline:    Acc@1={b_r1:.1%}  Acc@3={b_r3:.1%}  Acc@5={b_r5:.1%}")
    print(f"  +HyDE baseline:     Acc@1={h_r1:.1%}  Acc@3={h_r3:.1%}  Acc@5={h_r5:.1%}")
    print(f"  +Contextual+HyDE:   Acc@1={ch_r1:.1%}  Acc@3={ch_r3:.1%}  Acc@5={ch_r5:.1%}")
    print(f"  총 개선 (bge→Ctx+HyDE): Acc@1 +{ch_r1-b_r1:.1%}  Acc@3 +{ch_r3-b_r3:.1%}  Acc@5 +{ch_r5-b_r5:.1%}")


if __name__ == "__main__":
    main()