#!/usr/bin/env python3
"""Format 'Unknown - Call' cells with red background."""

import subprocess
import json
import sys

SHEET_ID = "1uVd-xZFF4TEQGqtvw9z6W8fffeaifPCoLsek83GmEoQ"
ACCOUNT = "jonny@jonnyshannon.com"

# Get column G values
result = subprocess.run(
    ["gog", "sheets", "get", SHEET_ID, "New Zealand!G:G", "--account", ACCOUNT, "--json"],
    capture_output=True,
    text=True,
    check=True
)

data = json.loads(result.stdout)
values = data.get("values", [])

# Find "Unknown - Call" rows
unknown_rows = []
for i, row in enumerate(values):
    if row and len(row) > 0 and "Unknown - Call" in str(row[0]):
        unknown_rows.append(i + 1)  # Convert to 1-indexed

print(f"Found 'Unknown - Call' in rows: {unknown_rows}")

if not unknown_rows:
    print("No 'Unknown - Call' entries found.")
    sys.exit(0)

# Get sheet metadata to find the sheet ID
metadata_result = subprocess.run(
    ["gog", "sheets", "metadata", SHEET_ID, "--account", ACCOUNT, "--json"],
    capture_output=True,
    text=True,
    check=True
)

metadata = json.loads(metadata_result.stdout)
nz_sheet_id = None
for sheet in metadata.get("sheets", []):
    if sheet.get("properties", {}).get("title") == "New Zealand":
        nz_sheet_id = sheet.get("properties", {}).get("sheetId")
        break

if nz_sheet_id is None:
    print("Could not find 'New Zealand' sheet ID")
    sys.exit(1)

print(f"New Zealand sheet ID: {nz_sheet_id}")

# Build API request
requests = []
for row_num in unknown_rows:
    requests.append({
        "repeatCell": {
            "range": {
                "sheetId": nz_sheet_id,
                "startRowIndex": row_num - 1,  # 0-indexed
                "endRowIndex": row_num,
                "startColumnIndex": 6,  # Column G
                "endColumnIndex": 7
            },
            "cell": {
                "userEnteredFormat": {
                    "backgroundColor": {
                        "red": 1.0,
                        "green": 0.8,
                        "blue": 0.8
                    }
                }
            },
            "fields": "userEnteredFormat.backgroundColor"
        }
    })

batch_update_body = json.dumps({"requests": requests})

# Get OAuth credentials from gog config
import os
gog_config_path = os.path.expanduser("~/.config/gog")
if not os.path.exists(gog_config_path):
    print("Could not find gog config directory")
    sys.exit(1)

# Try to find token file
token_files = subprocess.run(
    ["find", gog_config_path, "-name", "*.token"],
    capture_output=True,
    text=True
).stdout.strip().split('\n')

token_path = None
for tf in token_files:
    if tf and ACCOUNT in tf:
        token_path = tf
        break

if not token_path or not os.path.exists(token_path):
    print(f"Could not find token file for {ACCOUNT}")
    sys.exit(1)

# Read token
with open(token_path, 'r') as f:
    token_data = json.load(f)
    access_token = token_data.get("access_token")

if not access_token:
    print("Could not extract access token")
    sys.exit(1)

# Make API call
api_url = f"https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}:batchUpdate"

curl_result = subprocess.run(
    ["curl", "-X", "POST", api_url,
     "-H", f"Authorization: Bearer {access_token}",
     "-H", "Content-Type: application/json",
     "-d", batch_update_body,
     "-s"],
    capture_output=True,
    text=True
)

if curl_result.returncode == 0:
    print(f"✅ Applied red background to cells in rows: {unknown_rows}")
    print(curl_result.stdout)
else:
    print(f"❌ Error: {curl_result.stderr}")
    sys.exit(1)