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

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

# Companies to move to "Non Cool Rooms"
companies_to_move = [
    'NZ Cold Storage Association',
    'Earnscleugh Orchards',
    'Prime Produce',
    'Dennys Orchard',
    'Kathy Forrest Orchard'
]

# Load update data from CSV
updates = {}
with open('/Users/max/.openclaw/workspace/postharvest/nz_companies_update.csv', 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        updates[row['Company Name']] = row

print(f"Loaded {len(updates)} company updates")

# 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)
nz_rows = data['values']
header = nz_rows[0]

print(f"New Zealand sheet: {len(nz_rows)-1} companies")

# Separate rows into: keep (updated), move to non-cool rooms
keep_rows = [header]
move_rows = []

for row in nz_rows[1:]:
    if len(row) == 0:
        continue
    
    company_name = row[0]
    
    # Should we move this company?
    if company_name in companies_to_move:
        print(f"Moving to Non Cool Rooms: {company_name}")
        move_rows.append(row)
        continue
    
    # Should we update this company?
    if company_name in updates:
        update = updates[company_name]
        print(f"Updating: {company_name}")
        
        # Pad row to ensure we have all columns
        while len(row) < 15:
            row.append('')
        
        # Update columns based on CSV data
        row[1] = update.get('Website', row[1])  # Website
        row[4] = update.get('Primary Fruit', row[4])  # Primary Fruit
        row[6] = update.get('Qualified', row[6])  # Qualified
        row[9] = update.get('Notes', row[9])  # Notes
        row[10] = update.get('Contact Name', row[10])  # Contact Name
        row[11] = update.get('Contact Title', row[11])  # Contact Title
        row[12] = update.get('Contact Email', row[12])  # Contact Email
        row[13] = update.get('Contact Phone', row[13])  # Contact Phone
        row[14] = update.get('Source', row[14])  # Source
    
    keep_rows.append(row)

print(f"\nKeeping {len(keep_rows)-1} companies in New Zealand sheet")
print(f"Moving {len(move_rows)} companies to Non Cool Rooms")

# Update New Zealand sheet
print("\nUpdating New Zealand 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(keep_rows),
    '--input', 'USER_ENTERED',
    '--account', account
], capture_output=True, text=True)

if result.returncode == 0:
    print(f"✅ Updated New Zealand sheet")
else:
    print(f"❌ Error: {result.stderr}")

# Add to Non Cool Rooms sheet
if len(move_rows) > 0:
    print("\nAdding to Non Cool Rooms...")
    result = subprocess.run([
        'gog', 'sheets', 'append',
        spreadsheet_id,
        'Non Cool Rooms!A:O',
        '--values-json', json.dumps(move_rows),
        '--input', 'USER_ENTERED',
        '--account', account
    ], capture_output=True, text=True)
    
    if result.returncode == 0:
        print(f"✅ Added {len(move_rows)} companies to Non Cool Rooms")
    else:
        print(f"❌ Error: {result.stderr}")

print("\n✅ All updates complete!")