#!/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 and update the three companies
for i, row in enumerate(rows):
    if len(row) == 0:
        continue
    
    company_name = row[0]
    
    # Pad row to ensure we have all columns
    while len(row) < 15:
        row.append('')
    
    # Update Maleme Coldstorage
    if company_name == 'Maleme Coldstorage':
        print(f"Updating: {company_name}")
        row[1] = ''  # No website
        row[6] = 'No'  # Qualified = No
        row[9] = '10 Maleme St, Greerton, Tauranga. 4,088 sqm site. Property listed for sale 2025 by Bayleys - status uncertain. Near Port of Tauranga'  # Notes
        row[13] = '+64 7 541 0772'  # Contact Phone
        row[14] = 'Bayleys real estate, near-place.com'  # Source
    
    # Update Golden Oak Orchard
    elif company_name == 'Golden Oak Orchard':
        print(f"Updating: {company_name}")
        row[1] = ''  # No website
        row[6] = 'No'  # Qualified = No
        row[9] = 'Golden Oak Orchard Partnership. Small local orchard in Hawke\'s Bay. No website found. Listed in White Pages NZ'  # Notes
        row[14] = 'White Pages NZ'  # Source
    
    # Update Motueka Cold Storage Ltd
    elif company_name == 'Motueka Cold Storage Ltd':
        print(f"Updating: {company_name}")
        row[1] = 'onelineage.com'  # Lineage website (parent company)
        row[6] = 'Yes'  # Qualified = Yes (has parent website)
        row[9] = 'Old Wharf Rd, Port Motueka. Registered 1981. ACQUIRED BY LINEAGE LOGISTICS in August 2022. Now part of Cold Storage Nelson Ltd (Lineage\'s NZ network). Lineage has 400+ facilities globally across 20 countries'  # Notes
        row[14] = 'bizdb.co.nz, Lineage press release'  # Source

# 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"\n✅ Updated 3 companies:")
    print(f"  - Maleme Coldstorage: Property for sale (status uncertain)")
    print(f"  - Golden Oak Orchard: Small local orchard (no website)")
    print(f"  - Motueka Cold Storage: Acquired by Lineage Logistics 2022")
else:
    print(f"❌ Error: {result.stderr}")