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

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

# Load NZCSA data
nzcsa_companies = []
with open('/Users/max/.openclaw/workspace/nzcsa-produce-facilities.csv', 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        nzcsa_companies.append(row)

print(f"Loaded {len(nzcsa_companies)} NZCSA companies")

# Map to sheet format
new_rows = []
for company in nzcsa_companies:
    # Determine Primary Fruit from Products
    products = company.get('Products', '').lower()
    primary_fruit = ''
    
    if 'kiwifruit' in products:
        primary_fruit = 'Kiwifruit'
    elif 'citrus' in products or 'avocado' in products or 'blueberr' in products:
        primary_fruit = 'Mixed produce (citrus, avocados, berries)'
    elif 'fruit' in products and 'vegetable' in products:
        primary_fruit = 'Fruit and vegetables'
    elif 'vegetable' in products:
        primary_fruit = 'Vegetables'
    elif 'fruit' in products:
        primary_fruit = 'Fruit'
    elif 'field crops' in products:
        primary_fruit = 'Field crops'
    else:
        primary_fruit = 'Mixed cold storage'
    
    # Determine CA Storage Confirmed
    notes_lower = company.get('Notes', '').lower()
    ca_storage = 'Yes' if 'ca room' in notes_lower else 'Likely'
    
    # Build row
    row = [
        company.get('Company Name', ''),  # Company Name
        company.get('Website', '').replace('https://', '').replace('http://', '').replace('www.', '').rstrip('/'),  # Website
        'NZ',  # Country
        company.get('Location', ''),  # State/Region
        primary_fruit,  # Primary Fruit
        ca_storage,  # CA Storage Confirmed
        'Yes' if company.get('Website', '') else 'No',  # Qualified
        '',  # Employee Count
        '',  # Revenue
        f"{company.get('Products', '')}. {company.get('Capacity', '')}. {company.get('Notes', '')}".strip('. '),  # Notes
        company.get('Contact Name', ''),  # Contact Name
        '',  # Contact Title
        company.get('Contact Email', ''),  # Contact Email
        company.get('Contact Phone', ''),  # Contact Phone
        'NZCSA member directory'  # Source
    ]
    
    new_rows.append(row)

print(f"Formatted {len(new_rows)} rows for sheet")

# Append to New Zealand sheet
print("\nAppending to New Zealand sheet...")
result = subprocess.run([
    'gog', 'sheets', 'append',
    spreadsheet_id,
    'New Zealand!A:O',
    '--values-json', json.dumps(new_rows),
    '--input', 'USER_ENTERED',
    '--account', account
], capture_output=True, text=True)

if result.returncode == 0:
    print(f"✅ Added {len(new_rows)} NZCSA facilities to New Zealand sheet")
    for row in new_rows:
        print(f"  + {row[0]}")
else:
    print(f"❌ Error: {result.stderr}")