# -*- coding: utf-8 -*-
# 名店マスター → Googleスプレッドシート取込用CSV（stores）＋ 3ログ表の雛形CSVを生成。
# 4シート構成: stores / visit_logs / field_intel / news_clips（storeId外部キー）。
import json, os, csv
HERE=os.path.dirname(os.path.abspath(__file__))
SRC=os.path.join(os.path.dirname(HERE),"data","meiten-master.json")
with open(SRC,encoding="utf-8") as f: j=json.load(f)

def reps_str(s):
    out=[]
    for r in s.get("salesReps",[]):
        iss=("Vol."+",".join(map(str,r.get("issues",[])))) if r.get("issues") else (r.get("note","") or "")
        out.append(f"{r.get('rep','')}({iss})")
    return " / ".join(out)
def rating_str(s):
    r=s.get("rating")
    return (f"{r.get('source','')} {r.get('score','')}".strip()) if (r and r.get("score")) else ""

# stores.csv
cols=["storeId","name","category","ward","town","address","label",
      "優先度","優先度スコア","推奨アクション",
      "Vol15状態","Vol15枠","Vol15額","請求ステータス","掲載号","最終出稿額","累計出稿額",
      "客層","インバウンド適性","ポテンシャル","手形適性","ホテル送客","公式サイト","評価","評価出典URL","レビュー",
      "営業ポイント(潜在)","担当部署","電話","ベスト100順位","タグ","営業担当","Googleマップ","次アクション"]
with open(os.path.join(HERE,"stores.csv"),"w",encoding="utf-8-sig",newline="") as f:
    w=csv.writer(f); w.writerow(cols)
    for s in j["stores"]:
        r=s.get("rating") or {}; v=s.get("vol15") or {}; nb=s.get("nba") or {}
        w.writerow([
            s.get("storeId"),s.get("name"),s.get("category"),
            (s.get("area")or{}).get("ward"),(s.get("area")or{}).get("town"),s.get("address"),
            s.get("label"),
            nb.get("priority") or "", nb.get("priorityScore") if nb.get("priorityScore") is not None else "", nb.get("actionName") or nb.get("recommendedAction") or "",
            v.get("status") or "", v.get("slot") or "", v.get("amount") or "", v.get("invoiceStatus") or "",
            "Vol."+",".join(map(str,s.get("issuesPlaced",[]))) if s.get("issuesPlaced") else "",
            s.get("lastSpend") or "", s.get("spendKnownTotal") or "",
            s.get("targetPrimary") or "", s.get("inboundReady") or "",
            s.get("potentialTier"),(s.get("tegataFit")or{}).get("level"),s.get("hotelEastFit"),
            s.get("officialSite"),rating_str(s),r.get("url",""),
            s.get("reviewComment"),
            s.get("bizDevNote") or "", s.get("deptContact") or "", s.get("phone") or "", s.get("best100Rank") or "",
            "; ".join(s.get("tags",[])),reps_str(s),
            s.get("googleMapsUrl"),s.get("nextAction"),
        ])

# 雛形（ヘッダのみ）
def tmpl(name,header):
    with open(os.path.join(HERE,name),"w",encoding="utf-8-sig",newline="") as f:
        csv.writer(f).writerow(header)
tmpl("visit_logs.csv",["記録ID","storeId","店名","担当者","日時","種別(挨拶/情報交換/商談/受注/飲食で聞いた)","内容メモ","写真URL","次アクション","登録者","登録日時"])
tmpl("field_intel.csv",["intelID","storeId","店名","共有者","日時","カテゴリ(噂/人事/新メニュー/競合/イベント)","内容","出典(口頭/来店/SNS等)","活用メモ"])
tmpl("news_clips.csv",["clipID","storeId","店名/企業名","掲載日","媒体/出典","見出し","要約","URL","タグ","登録者"])

print("exported:", os.listdir(HERE))
print("stores rows:", len(j["stores"]))