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

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

# Load new companies
new_companies = []
with open('/Users/max/.openclaw/workspace/postharvest/nz-new-companies.csv', 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    new_companies = list(reader)

print(f"Loaded {len(new_companies)} new companies")

# Map to existing sheet structure
# Expected columns: Company Name, Website, Country, State/Region, Primary Fruit, CA Storage Confirmed, 
# Employee Count, Est. Revenue ($M), Contact Name, Contact Title, Contact Email, Contact Phone, Notes, Source, Qualified

mapped_rows = []
for company in new_companies:
    # Extract website (clean it)
    website = (company.get('Website') or '').strip()
    if website:
        website = website.replace('https://', '').replace('http://', '').replace('www.', '').rstrip('/')
    
    # Determine Primary Fruit from Type and Notes
    company_type = company.get('Type', '')
    notes = company.get('Notes', '')
    primary_fruit = ''
    
    if 'kiwifruit' in company_type.lower() or 'kiwifruit' in notes.lower():
        primary_fruit = 'Kiwifruit'
    elif 'apple' in company_type.lower() or 'apple' in notes.lower():
        primary_fruit = 'Apples'
    elif 'cherry' in company_type.lower() or 'cherry' in notes.lower():
        primary_fruit = 'Cherries'
    elif 'citrus' in company_type.lower():
        primary_fruit = 'Citrus'
    elif 'stonefruit' in company_type.lower() or 'peach' in notes.lower() or 'apricot' in notes.lower():
        primary_fruit = 'Stonefruit'
    elif 'cold storage' in company_type.lower():
        primary_fruit = 'Cold storage facility'
    else:
        primary_fruit = 'Mixed produce'
    
    # Build row
    row = [
        company.get('Company Name', ''),  # Company Name
        website,  # Website
        'NZ',  # Country
        company.get('Region', ''),  # State/Region
        primary_fruit,  # Primary Fruit
        'Likely',  # CA Storage Confirmed
        '',  # Employee Count
        '',  # Est. Revenue ($M)
        '',  # Contact Name
        '',  # Contact Title
        '',  # Contact Email
        company.get('Phone', ''),  # Contact Phone
        f"{company.get('Type', '')}. {company.get('Notes', '')}".strip('. '),  # Notes
        'Web search / Industry research',  # Source
        'Yes' if website else 'No'  # Qualified (has website = qualified)
    ]
    
    mapped_rows.append(row)

print(f"Mapped {len(mapped_rows)} rows")

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

if result.returncode == 0:
    print(f"✅ Successfully added {len(mapped_rows)} companies to New Zealand sheet!")
    print(f"🔗 https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit#gid=<NZ_GID>")
else:
    print(f"❌ Error: {result.stderr}")
    exit(1)