#!/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_non_coolroom(row):
    """Check if a row is NOT a cool room/packhouse facility"""
    if len(row) == 0:
        return False
    
    company_name = (row[0] if len(row) > 0 else '').lower()
    primary_fruit = (row[4] if len(row) > 4 else '').lower()
    notes = (row[12] if len(row) > 12 else '').lower()
    
    # Keywords that indicate NOT a cool storage facility
    non_facility_keywords = [
        'ice cream cart',
        'ice cream truck',
        'sign',
        'landmark',
        'supermarket',
        'grocery store',
        'retail',
        'restaurant',
        'cafe',
        'market stall',
        'fruit stand',
        'vegetable store',
        'farmers market',
        'food truck',
        'cart',
        'media',
        'event',
        'festival',
        'tourism',
        'visitor center',
        'museum',
        'store front',
        'shop',
        'delicatessen',
        'deli'
    ]
    
    # Check company name and notes
    combined = f"{company_name} {primary_fruit} {notes}"
    
    for keyword in non_facility_keywords:
        if keyword in combined:
            return True
    
    # Specific patterns
    if 'capital of the world' in combined and 'sign' in combined:
        return True
    
    if 'get real fruit ice cream' in company_name:
        return True
    
    # Check Primary Fruit column for retail indicators
    if any(x in primary_fruit for x in ['supermarket', 'retail', 'store', 'shop', 'deli']):
        return True
    
    return False

all_non_coolrooms = []
header = None

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
    
    if header is None:
        header = rows[0]
    
    # Separate cool rooms vs non-cool rooms
    cool_rooms = [rows[0]]  # Keep header
    non_coolrooms_this_sheet = 0
    
    for row in rows[1:]:
        if is_non_coolroom(row):
            all_non_coolrooms.append(row)
            non_coolrooms_this_sheet += 1
            print(f"  Moving: {row[0] if row else 'Unknown'}")
        else:
            cool_rooms.append(row)
    
    if non_coolrooms_this_sheet == 0:
        print(f"  No non-cool rooms found")
        continue
    
    print(f"  Found {non_coolrooms_this_sheet} non-cool room facilities")
    print(f"  Keeping {len(cool_rooms)-1} cool room facilities")
    
    # Update sheet without non-cool rooms
    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(cool_rooms),
        '--input', 'USER_ENTERED',
        '--account', account
    ], capture_output=True, text=True)
    
    if result.returncode == 0:
        print(f"  ✅ Updated {sheet}")
    else:
        print(f"  ❌ Error: {result.stderr}")

print(f"\n\nTotal non-cool room facilities found: {len(all_non_coolrooms)}")

if len(all_non_coolrooms) > 0:
    # Add to "Non Cool Rooms" sheet
    output_data = [header] + all_non_coolrooms
    
    print("\nClearing 'Non Cool Rooms' sheet...")
    subprocess.run([
        'gog', 'sheets', 'clear',
        spreadsheet_id,
        'Non Cool Rooms!A:Z',
        '--account', account
    ], capture_output=True)
    
    print("Uploading non-cool room facilities...")
    result = subprocess.run([
        'gog', 'sheets', 'update',
        spreadsheet_id,
        'Non Cool Rooms!A1',
        '--values-json', json.dumps(output_data),
        '--input', 'USER_ENTERED',
        '--account', account
    ], capture_output=True, text=True)
    
    if result.returncode == 0:
        print(f"✅ Moved {len(all_non_coolrooms)} non-cool room facilities to 'Non Cool Rooms'")
    else:
        print(f"❌ Error: {result.stderr}")

print("\n✅ All non-cool room facilities moved!")