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

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

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

def is_kiwifruit(row):
    """Check if a row is a kiwifruit company"""
    if len(row) <= 4:
        return False
    
    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()
    
    return ('kiwifruit' in primary_fruit or 
            'kiwi fruit' in primary_fruit or
            'kiwifruit' in notes or
            'kiwi' in notes or
            'kiwifruit' in company_name)

for sheet in country_sheets:
    print(f"\nProcessing {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 or len(rows) <= 1:
        print(f"  No data in {sheet}")
        continue
    
    header = rows[0]
    
    # Filter OUT kiwifruit companies (keep everything else)
    non_kiwifruit = [header]
    kiwifruit_removed = 0
    
    for row in rows[1:]:
        if is_kiwifruit(row):
            kiwifruit_removed += 1
            print(f"  Removing: {row[0] if row else 'Unknown'}")
        else:
            non_kiwifruit.append(row)
    
    if kiwifruit_removed == 0:
        print(f"  No kiwifruit companies to remove")
        continue
    
    print(f"  Removed {kiwifruit_removed} kiwifruit companies")
    print(f"  Keeping {len(non_kiwifruit)-1} non-kiwifruit companies")
    
    # Clear and rewrite the sheet WITHOUT kiwifruit companies
    print(f"  Updating {sheet}...")
    
    subprocess.run([
        'gog', 'sheets', 'clear',
        spreadsheet_id,
        f'{sheet}!A:Z',
        '--account', account
    ], capture_output=True)
    
    result = subprocess.run([
        'gog', 'sheets', 'update',
        spreadsheet_id,
        f'{sheet}!A1',
        '--values-json', json.dumps(non_kiwifruit),
        '--input', 'USER_ENTERED',
        '--account', account
    ], capture_output=True, text=True)
    
    if result.returncode == 0:
        print(f"  ✅ Updated {sheet}")
    else:
        print(f"  ❌ Error updating {sheet}: {result.stderr}")

print("\n✅ All kiwifruit companies moved to 'Kiwi Fruit Storage Global'")
print("✅ Removed from all country sheets")