"""Fetch churned recurring customers from Airtable for reactivation campaign."""
import urllib.request, urllib.parse, json, sys

key = open('/Users/harvey/.openclaw/secrets/airtable-api-key.txt').read().strip()
base = 'applisWTLgZJOnYtj'
table = 'Customers'

# Simple filter: no active bookings, had recurring frequency, has email
# Airtable formula — keep it simple to avoid 422
formula = "AND({# of Active Bkgs}=0, NOT({Frequency of Last Booking}=''), NOT({Frequency of Last Booking}='One Time'), NOT({Email}=''))"

params = [
    ('maxRecords', '200'),
    ('filterByFormula', formula),
    ('fields[]', 'Full name'),
    ('fields[]', 'First name'),
    ('fields[]', 'Email'),
    ('fields[]', 'Phone'),
    ('fields[]', 'Frequency of Last Booking'),
    ('fields[]', 'Date of Last Booking'),
    ('fields[]', '# of Bkgs'),
    ('fields[]', 'Total Spent'),
]

url = f'https://api.airtable.com/v0/{base}/{urllib.parse.quote(table)}?{urllib.parse.urlencode(params)}'
req = urllib.request.Request(url, headers={'Authorization': f'Bearer {key}'})

try:
    with urllib.request.urlopen(req) as r:
        d = json.load(r)
except urllib.error.HTTPError as e:
    print(f"Error {e.code}: {e.read().decode()}")
    sys.exit(1)

records = d.get('records', [])
print(f"Found {len(records)} churned recurring customers\n")

# Sort by total spent descending (highest value first)
records.sort(key=lambda r: r['fields'].get('Total Spent', 0), reverse=True)

for rec in records[:20]:
    f = rec['fields']
    print(f"  {f.get('Full name','?'):30s} | {f.get('Email','?'):35s} | Last: {f.get('Date of Last Booking','?')[:10] if f.get('Date of Last Booking') else '?':12s} | Freq: {f.get('Frequency of Last Booking','?'):20s} | Spent: ${f.get('Total Spent',0):.0f}")