#!/usr/bin/env python3
import json
import subprocess

spreadsheet_id = '1uVd-xZFF4TEQGqtvw9z6W8fffeaifPCoLsek83GmEoQ'
account = 'jonny@jonnyshannon.com'

# Download New Zealand sheet
result = subprocess.run([
    'gog', 'sheets', 'get',
    spreadsheet_id,
    'New Zealand!A:O',
    '--account', account,
    '--json'
], capture_output=True, text=True)

data = json.loads(result.stdout)
rows = data['values']

# Find H1 Packhouse row
h1_row_index = None
for i, row in enumerate(rows):
    if len(row) > 0 and row[0] == 'H1 Packhouse':
        h1_row_index = i
        break

if h1_row_index is None:
    print("H1 Packhouse not found!")
    exit(1)

print(f"Found H1 Packhouse at row {h1_row_index + 1}")

# Update the row
updated_row = rows[h1_row_index].copy()

# Pad to ensure we have enough columns
while len(updated_row) < 15:
    updated_row.append('')

# Update fields:
# Column 1: Website = kaiaponi.co.nz
updated_row[1] = 'kaiaponi.co.nz'

# Column 9: Notes = explanation of connection
updated_row[9] = 'Likely part of Kaiaponi Farms (parent company). Evidence: H1 located at 544 Matawai Rd, Kaiaponi at 555 Matawai Rd (11m apart). Both use same accountant (Graham & Dobson Ltd). No independent web presence. Kaiaponi = major Gisborne grower/packer with 10+ coolstores, recent 550kW solar installation. Contact: Barton Witters (GM), +64 6 863 2980.'

# Column 6: Qualified = Yes (now has website)
updated_row[6] = 'Yes'

rows[h1_row_index] = updated_row

# Update sheet
subprocess.run([
    'gog', 'sheets', 'clear',
    spreadsheet_id,
    'New Zealand!A:O',
    '--account', account
], capture_output=True)

result = subprocess.run([
    'gog', 'sheets', 'update',
    spreadsheet_id,
    'New Zealand!A1',
    '--values-json', json.dumps(rows),
    '--input', 'USER_ENTERED',
    '--account', account
], capture_output=True, text=True)

if result.returncode == 0:
    print(f"✅ Updated H1 Packhouse:")
    print(f"  Website: kaiaponi.co.nz")
    print(f"  Qualified: Yes")
    print(f"  Notes: Connection to Kaiaponi Farms explained")
else:
    print(f"❌ Error: {result.stderr}")