# -*- coding: utf-8 -*-
"""
create_mvp_sheet.py — 言問名店CRMのMVP Googleスプレッドシート(4タブ)をCEO個人Googleに作成。
タブ: stores / visit_logs / field_intel / news_clips。CSV(同フォルダ)から投入。
認証: ops/token_sheets.json（spreadsheetsスコープ・既存）。LINK_POLICY Tier1(httpsでiPhone可)。
使い方: py -3 create_mvp_sheet.py
"""
import os, csv
HERE = os.path.dirname(os.path.abspath(__file__))
OPS  = r"C:\Users\daito\company\ops"

def get_creds():
    # token_drive.json = yamada.daito@gmail.com（CEO個人・確認済）。drive.file スコープで
    # アプリ作成ファイルを Sheets API でも編集可。所有者=CEO個人＝iPhoneでそのまま開ける。
    from google.oauth2.credentials import Credentials
    from google.auth.transport.requests import Request
    tok = os.path.join(OPS, "token_drive.json")
    creds = Credentials.from_authorized_user_file(tok, ["https://www.googleapis.com/auth/drive.file"])
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
        open(tok, "w", encoding="utf-8").write(creds.to_json())
    return creds

def read_csv(name):
    with open(os.path.join(HERE, name), encoding="utf-8-sig", newline="") as f:
        return [row for row in csv.reader(f)]

def main():
    from googleapiclient.discovery import build
    svc = build("sheets", "v4", credentials=get_creds(), cache_discovery=False)
    tabs = ["stores","visit_logs","field_intel","news_clips"]
    body = {"properties":{"title":"言問名店CRM（MVP）— 名店DB＋訪問記録＋現場インテル＋ニュース"},
            "sheets":[{"properties":{"title":t,"gridProperties":{"frozenRowCount":1}}} for t in tabs]}
    ss = svc.spreadsheets().create(body=body, fields="spreadsheetId,spreadsheetUrl,sheets(properties(sheetId,title))").execute()
    sid = ss["spreadsheetId"]
    # 投入
    data=[]
    for t in tabs:
        rows = read_csv(t + ".csv")
        data.append({"range": f"{t}!A1", "values": rows})
    svc.spreadsheets().values().batchUpdate(spreadsheetId=sid,
        body={"valueInputOption":"USER_ENTERED","data":data}).execute()
    # ヘッダ太字
    reqs=[]
    for sh in ss["sheets"]:
        reqs.append({"repeatCell":{
            "range":{"sheetId":sh["properties"]["sheetId"],"startRowIndex":0,"endRowIndex":1},
            "cell":{"userEnteredFormat":{"textFormat":{"bold":True}}},
            "fields":"userEnteredFormat.textFormat.bold"}})
    svc.spreadsheets().batchUpdate(spreadsheetId=sid, body={"requests":reqs}).execute()
    print("SPREADSHEET_ID:", sid)
    print("URL:", ss.get("spreadsheetUrl"))

if __name__ == "__main__":
    main()