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

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

# Load Non Cool Rooms data
with open('/Users/max/.openclaw/workspace/postharvest/non-cool-rooms.json', 'r') as f:
    data = json.load(f)
    rows = data['values']

header = rows[0]
print(f"Current rows: {len(rows)-1}")

# Filter out sporting goods stores
filtered = [header]
deleted_count = 0

sporting_keywords = [
    'sporting goods',
    'sports + outdoors',
    'bass pro',
    "dick's sporting",
    'rei',
    'academy sports',
    'big 5 sporting'
]

for row in rows[1:]:
    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()
    
    combined = f"{company_name} {primary_fruit} {notes}"
    
    # Check if it's a sporting goods store
    is_sporting = any(keyword in combined for keyword in sporting_keywords)
    
    if is_sporting:
        print(f"Deleting: {row[0] if row else 'Unknown'}")
        deleted_count += 1
    else:
        filtered.append(row)

print(f"\nDeleted {deleted_count} sporting goods stores")
print(f"Remaining rows: {len(filtered)-1}")

# Update sheet
print("\nUpdating 'Non Cool Rooms' sheet...")

subprocess.run([
    'gog', 'sheets', 'clear',
    spreadsheet_id,
    'Non Cool Rooms!A:Z',
    '--account', account
], capture_output=True)

result = subprocess.run([
    'gog', 'sheets', 'update',
    spreadsheet_id,
    'Non Cool Rooms!A1',
    '--values-json', json.dumps(filtered),
    '--input', 'USER_ENTERED',
    '--account', account
], capture_output=True, text=True)

if result.returncode == 0:
    print(f"✅ Deleted {deleted_count} sporting goods stores")
else:
    print(f"❌ Error: {result.stderr}")