"""Fill Client Profile tab for 'red' via batch update. One-off for W0."""
import json, sys, urllib.request, urllib.parse, urllib.error
from pathlib import Path

SHEETS_BASE = "https://sheets.googleapis.com/v4/spreadsheets"
TOKEN_PATH = Path.home() / ".config/google-workspace-mcp/profiles/emerson-north/tokens.json"
CREDS_PATH = Path.home() / ".config/google-workspace-mcp/profiles/emerson-north/credentials.json"

# load token and refresh if needed
with open(TOKEN_PATH) as f: tok = json.load(f)
with open(CREDS_PATH) as f: creds = json.load(f)
# creds is Google-format {installed:{client_id,...}} usually
client = creds.get("installed") or creds.get("web") or creds

def refresh():
    data = urllib.parse.urlencode({
        "client_id": client["client_id"],
        "client_secret": client["client_secret"],
        "refresh_token": tok["refresh_token"],
        "grant_type": "refresh_token",
    }).encode()
    req = urllib.request.Request(
        "https://oauth2.googleapis.com/token",
        data=data, method="POST",
        headers={"Content-Type": "application/x-www-form-urlencoded"}
    )
    r = json.loads(urllib.request.urlopen(req, timeout=20).read().decode())
    return r["access_token"]

access_token = tok.get("access_token")
# Always refresh to be safe
access_token = refresh()

SHEET_ID = "1NnNUwUK9Holx4jQJkBxVsuoLOCprbTQQvmN-HkDyMeA"

# Field values — from Supabase client_profiles for 'red'
vals = {
    "client_name": "red",
    "contact_name": "Test User",
    "niche": "r",
    "tone": "Inspirational, Humorous",
    "instagram_handle": "ss",
    "facebook_handle": "",
    "tiktok_handle": "",
    "youtube_handle": "",
    "linkedin_handle": "",
    "x_handle": "",
    "threads_handle": "",
    "gbp_handle": "",
    "pintrest_handle": "",
    "client_type": "b2b",
    "tagline": "r",
    "brand_color_primary": "#2ba829",
    "brand_color_secondary": "#000000",
    "mood_anchor": "outdoor_editorial",
    "website_platform": "Other",
    "current_crm": "Other",
    "services_enabled": "resources,profile",
    "contact_email": "1brycefolsom@gmail.com",
    "product_1_name": "Gre",
    "product_1_description": "",
    "product_1_price": "50-200",
    "product_2_name": "",
    "product_2_description": "",
    "product_2_price": "",
    "product_3_name": "",
    "product_3_description": "",
    "product_3_price": "",
    "format_preference": "long-form",
    "analytics_setup": "none",
    "ga_measurement_id": "",
    "meta_pixel_id": "",
    "tech_notes": "",
    "revenue_tracking": "no",
    "unfair_advantage": "ee",
    "existing_proof": "",
    "website_url": "",
    "landing_page_hosting": "en_managed",
    "assistant_name": "Hero",
}

# key -> row
row_map = {
    "client_name":2, "contact_name":3, "niche":4, "target_audience":5, "tone":6,
    "content_pillar_1":7, "content_pillar_2":8, "content_pillar_3":9, "content_pillar_4":10, "content_pillar_5":11,
    "instagram_handle":12, "facebook_handle":13, "tiktok_handle":14, "youtube_handle":15,
    "linkedin_handle":16, "x_handle":17, "threads_handle":18, "gbp_handle":19, "pintrest_handle":20,
    "active_campaign":21, "active_product":22, "active_landing_page":23,
    "creator_1_name":24,"creator_1_platform":25,"creator_1_why":26,
    "creator_2_name":27,"creator_2_platform":28,"creator_2_why":29,
    "creator_3_name":30,"creator_3_platform":31,"creator_3_why":32,
    "creator_4_name":33,"creator_4_platform":34,"creator_4_why":35,
    "creator_5_name":36,"creator_5_platform":37,"creator_5_why":38,
    "brand_surface_guide_url":39, "landing_page_hosting":40,
    "secondary_audience":41,
    "subreddit_1":42,"subreddit_2":43,"subreddit_3":44,"subreddit_4":45,"subreddit_5":46,
    "subreddit_6":47,"subreddit_7":48,"subreddit_8":49,"subreddit_9":50,
    "current_demographics":51, "client_type":52, "target_market":53, "product_category":54,
    "competitor_1_name":55,"competitor_1_url":56,"competitor_2_name":57,"competitor_2_url":58,
    "competitor_3_name":59,"competitor_3_url":60,"competitor_4_name":61,"competitor_4_url":62,
    "competitor_5_name":63,"competitor_5_url":64,
    "knowledge_base_url":65, "assistant_name":66, "assistant_status":67,
    "b2b_subreddit_1":68,"b2b_subreddit_2":69,"b2b_subreddit_3":70,"b2b_subreddit_4":71,"b2b_subreddit_5":72,
    "booking_link":73, "first_response":74,
    "tagline":75, "brand_color_primary":76, "brand_color_secondary":77, "mood_anchor":78,
    "website_platform":79, "current_crm":80, "bio_link_url":81, "services_enabled":82,
    "google_tag_id":83, "google_analytics_id":84,
    "portal_password":86, "contact_email":87,
    "product_1_name":88, "product_1_description":89, "product_1_price":90,
    "product_2_name":91, "product_2_description":92, "product_2_price":93,
    "product_3_name":94, "product_3_description":95, "product_3_price":96,
    "format_preference":97, "analytics_setup":98, "ga_measurement_id":99, "meta_pixel_id":100,
    "website_vercel_project":101, "website_path":102, "website_domain":103,
    "tech_notes":104, "revenue_tracking":105, "unfair_advantage":106, "existing_proof":107,
    "ghl_location_id":108, "ghl_user_id":109, "dns_connected":110, "client_uploads_folder":111,
    "portal_url":112,
    "hero_image":121, "website_url":122,
}

# Build batchUpdate body
data = []
for key, val in vals.items():
    row = row_map.get(key)
    if row is None:
        print(f"SKIP (no row): {key}", file=sys.stderr)
        continue
    data.append({"range": f"Client Profile!B{row}", "values": [[val if val is not None else ""]]})

body = {
    "valueInputOption": "USER_ENTERED",
    "data": data,
}

req = urllib.request.Request(
    f"{SHEETS_BASE}/{SHEET_ID}/values:batchUpdate",
    data=json.dumps(body).encode(),
    method="POST",
    headers={
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json",
    }
)
try:
    res = json.loads(urllib.request.urlopen(req, timeout=30).read().decode())
    print("OK:", res.get("totalUpdatedCells"), "cells updated in", res.get("totalUpdatedRanges"), "ranges")
except urllib.error.HTTPError as e:
    print("ERROR:", e.code, e.read().decode())
    sys.exit(1)