# -*- coding: utf-8 -*-
# 既存MVPシート(17cmBS...)を更新：storesタブ刷新＋invoicesタブ(請求書管理ミラー)追加。
import os, csv, json
HERE=os.path.dirname(os.path.abspath(__file__))
OPS=r"C:\Users\daito\company\ops"
SID="17cmBSwKrH_fTGGg4VblIC8ukqPCMssDISMGWZ_zR6l8"
def creds():
    from google.oauth2.credentials import Credentials
    from google.auth.transport.requests import Request
    c=Credentials.from_authorized_user_file(os.path.join(OPS,"token_drive.json"),["https://www.googleapis.com/auth/drive.file"])
    if c.expired and c.refresh_token: c.refresh(Request()); open(os.path.join(OPS,"token_drive.json"),"w",encoding="utf-8").write(c.to_json())
    return c
def read_csv(n):
    with open(os.path.join(HERE,n),encoding="utf-8-sig",newline="") as f: return [r for r in csv.reader(f)]

from googleapiclient.discovery import build
svc=build("sheets","v4",credentials=creds(),cache_discovery=False)
meta=svc.spreadsheets().get(spreadsheetId=SID,fields="sheets(properties(sheetId,title))").execute()
titles={sh["properties"]["title"]:sh["properties"]["sheetId"] for sh in meta["sheets"]}

# invoices タブ作成（無ければ）
if "invoices" not in titles:
    svc.spreadsheets().batchUpdate(spreadsheetId=SID,body={"requests":[{"addSheet":{"properties":{"title":"invoices","gridProperties":{"frozenRowCount":1}}}}]}).execute()

# stores 刷新
svc.spreadsheets().values().clear(spreadsheetId=SID,range="stores!A1:AZ5000").execute()
stores=read_csv("stores.csv")
svc.spreadsheets().values().update(spreadsheetId=SID,range="stores!A1",valueInputOption="USER_ENTERED",body={"values":stores}).execute()

# invoices 刷新（請求書管理.json をそのまま）
inv=json.load(open(os.path.join(HERE,"source","請求書管理.json"),encoding="utf-8"))
svc.spreadsheets().values().clear(spreadsheetId=SID,range="invoices!A1:AZ5000").execute()
svc.spreadsheets().values().update(spreadsheetId=SID,range="invoices!A1",valueInputOption="USER_ENTERED",body={"values":inv}).execute()

# ヘッダ太字
meta=svc.spreadsheets().get(spreadsheetId=SID,fields="sheets(properties(sheetId,title))").execute()
reqs=[]
for sh in meta["sheets"]:
    if sh["properties"]["title"] in ("stores","invoices"):
        reqs.append({"repeatCell":{"range":{"sheetId":sh["properties"]["sheetId"],"startRowIndex":0,"endRowIndex":1},
            "cell":{"userEnteredFormat":{"textFormat":{"bold":True}}},"fields":"userEnteredFormat.textFormat.bold"}})
if reqs: svc.spreadsheets().batchUpdate(spreadsheetId=SID,body={"requests":reqs}).execute()
print("stores rows:",len(stores),"invoices rows:",len(inv))