def customer_text(purchases):
    """고객 한 명을 구매 이력으로 표현한다."""
    return " ".join(f"{name} (별점 {rating}) {review}" for name, rating, review in purchases)

import sqlite3
import statistics

from transformers import AutoTokenizer

tok = AutoTokenizer.from_pretrained("intfloat/multilingual-e5-small")
con = sqlite3.connect("cosmetic.db")

# 고객 한 명 = 후기를 전부 이어 붙인 글
history = {}
for cid, name, rating, review in con.execute("""
    SELECT purchases.customer_id, products.name, purchases.rating, purchases.review
    FROM purchases JOIN products ON products.product_id = purchases.product_id
    WHERE purchases.is_holdout = 0
    ORDER BY purchases.customer_id
"""):
    history.setdefault(cid, []).append(f"{name} (별점 {rating}) {review}")

joined = [" ".join(v) for v in history.values()]
counts = sorted(len(tok.encode(t)) for t in joined)
buys = sorted(len(v) for v in history.values())

print(f"  고객 {len(joined)}명")
print(f"    이어 붙인 글  최소 {counts[0]} · 중앙 {int(statistics.median(counts))} · 최대 {counts[-1]} 토큰")
print(f"    구매 건수      최소 {buys[0]} · 중앙 {int(statistics.median(buys))} · 최대 {buys[-1]}건")
print(f"    상한(512) 초과 {sum(1 for c in counts if c > 512)}명")

per = counts[-1] / buys[-1]
print(f"\n  구매 1건당 약 {per:.0f}토큰 -> 512 를 넘으려면 {512 / per:.0f}건쯤 사야 한다")

one = sorted(len(tok.encode(r)) for (r,) in con.execute(
    "SELECT review FROM purchases WHERE review != ''"))
print(f"  후기 한 건       최소 {one[0]} · 중앙 {int(statistics.median(one))} · 최대 {one[-1]} 토큰")

import sqlite3
from collections import Counter

import numpy as np
from langchain_huggingface import HuggingFaceEmbeddings

con = sqlite3.connect("cosmetic.db")
model = HuggingFaceEmbeddings(
    model_name="intfloat/multilingual-e5-small",
    model_kwargs={"device": "cpu"},
    encode_kwargs={"normalize_embeddings": True, "batch_size": 64})

# 상품 벡터 ― 비교 대상
rows = con.execute("""
    SELECT product_id, name, brand, category, price, skin_type,
           ingredient, concern, tags, description FROM products ORDER BY product_id
""").fetchall()
pids = [r[0] for r in rows]
P = np.array(model.embed_documents(
    [" · ".join(str(x) for x in r[1:]) for r in rows]), dtype="float32")

# 고객 재료
info = {c: r for c, *r in con.execute(
    "SELECT customer_id, age, gender, skin_type, city FROM customers")}
hist = {}
for cid, name, cat, ing, concern, rating, review in con.execute("""
    SELECT purchases.customer_id, products.name, products.category, products.ingredient,
           products.concern, purchases.rating, purchases.review
    FROM purchases JOIN products ON products.product_id = purchases.product_id
    WHERE purchases.is_holdout = 0
    ORDER BY purchases.customer_id, purchases.purchase_id
"""):
    #  purchase_id 까지 정렬한다. 고객까지만 정렬하면 같은 고객 안의 순서가 실행할때마다 달라짐
    #  결국 아래 most_common 의 동점 순서가 바뀌어 테스트할때마다 달라지는 순서로 점수가 흔들림
    #  테스트할때마다 동일한 점수가 나와야 함
    hist.setdefault(cid, []).append((name, cat, ing, concern, rating, review))

answer = dict(con.execute(
    "SELECT customer_id, product_id FROM purchases WHERE is_holdout = 1"))
bought = {}
for cid, pid in con.execute(
        "SELECT customer_id, product_id FROM purchases WHERE is_holdout = 0"):
    bought.setdefault(cid, set()).add(pid)
cids = [c for c in sorted(hist) if c in answer]


def top(items, n=3):
    return " · ".join(x for x, _ in Counter(items).most_common(n))


def taste(cid):
    h = hist[cid]
    avg = sum(x[4] for x in h) / len(h)
    return (f"{info[cid][2]} 피부 · 선호 카테고리 {top([x[1] for x in h])} · "
            f"자주 쓴 성분 {top([x[2] for x in h])} · "
            f"관심 고민 {top([x[3] for x in h])} · 평균 별점 {avg:.1f}")


VARIANTS = [
    ("① 후기 이어붙이기", lambda c: " ".join(f"{x[0]} (별점 {x[4]}) {x[5]}" for x in hist[c])),
    ("② 취향만", taste),
    ("③ 취향 + 나이·성별·지역", lambda c:
        f"{info[c][0] // 10 * 10}대 {'여성' if info[c][1] == 'F' else '남성'} · "
        f"{info[c][3]} 거주 · " + taste(c)),
    ("④ 취향 + 후기", lambda c: taste(c) + " · " + " ".join(x[5] for x in hist[c])[:200]),
]

print(f"  {'무엇':26s} {'평균 글자':>8s} {'hit@1':>7s} {'hit@3':>7s} {'hit@5':>7s}")
for label, make in VARIANTS:
    texts = [make(c) for c in cids]
    C = np.array(model.embed_documents(texts), dtype="float32")
    hits = [0, 0, 0]
    for i, cid in enumerate(cids):
        order = np.argsort(-(P @ C[i]))
        ranked = [pids[j] for j in order if pids[j] not in bought.get(cid, ())]
        for slot, k in enumerate((1, 3, 5)):
            hits[slot] += answer[cid] in ranked[:k]
    n = len(cids)
    print(f"  {label:26s} {sum(len(t) for t in texts) / n:>8.0f} "
          f"{hits[0] / n * 100:>6.1f}% {hits[1] / n * 100:>6.1f}% {hits[2] / n * 100:>6.1f}%")