#!/usr/bin/env python3
"""Format 'Unknown - Call' cells with red background in NZ sheet column G."""

import subprocess
import json

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

# Get all column G values to find "Unknown - Call" cells
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 rows with "Unknown - Call" (sheet is 1-indexed, API is 0-indexed)
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)  # 0-indexed row number

print(f"Found 'Unknown - Call' in rows: {[r+1 for r in unknown_rows]}")

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

# Build formatting requests for Google Sheets API
requests = []
for row_idx in unknown_rows:
    requests.append({
        "repeatCell": {
            "range": {
                "sheetId": 0,  # Assuming "New Zealand" is first sheet
                "startRowIndex": row_idx,
                "endRowIndex": row_idx + 1,
                "startColumnIndex": 6,  # Column G (0-indexed)
                "endColumnIndex": 7
            },
            "cell": {
                "userEnteredFormat": {
                    "backgroundColor": {
                        "red": 1.0,
                        "green": 0.8,
                        "blue": 0.8
                    }
                }
            },
            "fields": "userEnteredFormat.backgroundColor"
        }
    })

# Apply formatting via API
batch_update = {
    "requests": requests
}

# Write to temp file and apply via gog (if it supports batch updates)
# Otherwise we'll need to use direct API calls
import tempfile
import os

with tempfile.NamedTemporaryFile(mode='w', suffix='.json', delete=False) as f:
    json.dump(batch_update, f)
    temp_file = f.name

try:
    # Try using curl directly with Google Sheets API
    token_result = subprocess.run(
        ["gog", "auth", "token", ACCOUNT],
        capture_output=True,
        text=True,
        check=True
    )
    token = token_result.stdout.strip()
    
    # Use curl to make the API call
    api_url = f"https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}:batchUpdate"
    
    subprocess.run(
        ["curl", "-X", "POST", api_url,
         "-H", f"Authorization: Bearer {token}",
         "-H", "Content-Type: application/json",
         "-d", json.dumps(batch_update)],
        check=True
    )
    
    print(f"✅ Applied red background to {len(unknown_rows)} cells")
    
finally:
    os.unlink(temp_file)