#!/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 home improvement, ice cream, and K-Market stores
filtered = [header]
deleted_count = 0

delete_keywords = [
    # Home improvement
    'home depot',
    'the home depot',
    "lowe's",
    'menards',
    'ace hardware',
    "home hardware",
    'hardware',
    'home improvement',
    
    # Ice cream
    'ice cream',
    'gelato',
    
    # K-Market (Finnish grocery chain)
    'k-market',
    'k-ruokafi',
]

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 matches delete criteria
    should_delete = any(keyword in combined for keyword in delete_keywords)
    
    if should_delete:
        print(f"Deleting: {row[0] if row else 'Unknown'}")
        deleted_count += 1
    else:
        filtered.append(row)

print(f"\nDeleted {deleted_count} entries (home improvement + ice cream + K-Market)")
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} entries")
else:
    print(f"❌ Error: {result.stderr}")