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

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

# Companies to delete completely
delete_list = [
    'Earnscleugh Orchards',
    'Prime Produce',
    'Dennys Orchard',
    'Kathy Forrest Orchard'
]

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

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

# Filter out the companies to delete
filtered = [header]
deleted = 0

for row in rows[1:]:
    if len(row) == 0:
        continue
    
    company_name = row[0]
    
    if company_name in delete_list:
        print(f"Deleting: {company_name}")
        deleted += 1
    else:
        filtered.append(row)

print(f"\nDeleted {deleted} companies")
print(f"Remaining: {len(filtered)-1} entries")

# Update sheet
print("\nUpdating Non Cool Rooms...")
subprocess.run([
    'gog', 'sheets', 'clear',
    spreadsheet_id,
    'Non Cool Rooms!A:O',
    '--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} unverifiable companies")
    print(f"✅ NZ Cold Storage Association remains in Non Cool Rooms")
else:
    print(f"❌ Error: {result.stderr}")