supabase_store.py

# 분석 결과를 Supabase 플랫 테이블(analysis_run/cluster/demographic)에 적재하는 모듈
import os
from datetime import date
from functools import lru_cache

from supabase import create_client, Client


@lru_cache(maxsize=1)
def _client() -> Client:
    return create_client(os.environ["SUPABASE_URL"], os.environ["SUPABASE_SERVICE_KEY"])


def enabled() -> bool:
    # Supabase 자격증명이 있을 때만 적재
    return bool(os.environ.get("SUPABASE_URL") and os.environ.get("SUPABASE_SERVICE_KEY"))


def persist(result: dict) -> None:
    # analyze 결과 dict를 (keyword, run_date) 기준으로 upsert
    sb = _client()
    kw = result["keyword"]
    day = date.today().isoformat()
    ib = result.get("intent_breakdown") or {}

    sb.table("analysis_run").upsert({
        "keyword": kw, "run_date": day,
        "total_search_volume": result.get("total_search_volume"),
        "keyword_count": result.get("keyword_count"),
        "cluster_count": result.get("cluster_count", 0),
        "intent_info": ib.get("info", 0),
        "intent_transactional": ib.get("transactional", 0),
        "intent_navigational": ib.get("navigational", 0),
        "intent_mixed": ib.get("mixed", 0),
        "marketing_suggestion": result.get("marketing_suggestion"),
    }, on_conflict="keyword,run_date").execute()

    # 재적재 시 중복 방지: 같은 키워드·날짜 행을 지우고 다시 넣음
    clusters = result.get("clusters") or []
    sb.table("analysis_cluster").delete().eq("keyword", kw).eq("run_date", day).execute()
    if clusters:
        sb.table("analysis_cluster").insert([{
            "keyword": kw, "run_date": day,
            "theme": c.get("theme"), "rep_keyword": c.get("cluster_label"),
            "intent": c.get("intent"), "total_search_volume": c.get("total_search_volume"),
            "keyword_count": c.get("keyword_count"),
            "top_keywords": ", ".join(c.get("top_keywords", [])),
        } for c in clusters]).execute()

    demo = result.get("demographics")
    sb.table("analysis_demographic").delete().eq("keyword", kw).eq("run_date", day).execute()
    if demo and demo.get("cells"):
        sb.table("analysis_demographic").insert([{
            "keyword": kw, "run_date": day,
            "gender": x.get("gender"), "age": x.get("age"), "pct": x.get("pct"),
        } for x in demo["cells"]]).execute()