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

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

# All country sheets to search
country_sheets = ['USA', 'South Africa', 'Australia', 'New Zealand', 'Canada', 'UK', 'Other Countries']

all_kiwifruit = []
header = None

for sheet in country_sheets:
    print(f"Searching {sheet}...")
    
    # Download sheet data
    result = subprocess.run([
        'gog', 'sheets', 'get',
        spreadsheet_id,
        f'{sheet}!A:Z',
        '--account', account,
        '--json'
    ], capture_output=True, text=True)
    
    if result.returncode != 0:
        print(f"  Error downloading {sheet}: {result.stderr}")
        continue
    
    data = json.loads(result.stdout)
    rows = data.get('values', [])
    
    if not rows:
        print(f"  No data in {sheet}")
        continue
    
    # Save header from first sheet
    if header is None:
        header = rows[0]
    
    # Filter for kiwifruit companies
    # Column index 4 is "Primary Fruit", column 12 is "Notes"
    kiwifruit_count = 0
    for row in rows[1:]:  # Skip header
        if len(row) > 4:
            primary_fruit = (row[4] if len(row) > 4 else '').lower()
            notes = (row[12] if len(row) > 12 else '').lower()
            company_name = (row[0] if len(row) > 0 else '').lower()
            
            # Check if kiwifruit-related
            if ('kiwifruit' in primary_fruit or 
                'kiwi fruit' in primary_fruit or
                'kiwifruit' in notes or
                'kiwi' in notes or
                'kiwifruit' in company_name):
                all_kiwifruit.append(row)
                kiwifruit_count += 1
    
    print(f"  Found {kiwifruit_count} kiwifruit companies")

print(f"\nTotal kiwifruit companies: {len(all_kiwifruit)}")

if len(all_kiwifruit) == 0:
    print("⚠️  No kiwifruit companies found!")
    exit(0)

# Prepare data with header
output_data = [header] + all_kiwifruit

# Clear and populate "Kiwi Fruit Storage Global" sheet
print("\nClearing Kiwi Fruit Storage Global sheet...")
subprocess.run([
    'gog', 'sheets', 'clear',
    spreadsheet_id,
    'Kiwi Fruit Storage Global!A:Z',
    '--account', account
], capture_output=True)

print("Uploading kiwifruit companies...")
result = subprocess.run([
    'gog', 'sheets', 'update',
    spreadsheet_id,
    'Kiwi Fruit Storage Global!A1',
    '--values-json', json.dumps(output_data),
    '--input', 'USER_ENTERED',
    '--account', account
], capture_output=True, text=True)

if result.returncode == 0:
    print(f"✅ Successfully added {len(all_kiwifruit)} kiwifruit companies!")
    print(f"🔗 https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit")
else:
    print(f"❌ Error: {result.stderr}")