`scripts/enrich-website-google-places.py`

122 lines
#!/usr/bin/env python3
"""Google Places API website enricher for lacountyeats facilities.

For each facility without a website, calls Find Place from Text
(name + city + state) and stores the resulting `website` (and place_id +
formatted_address as a sanity audit trail) into facility_enrichment.

Cost: $0.017 per Find Place call. Free $200/mo credit covers ~11.7k calls.
38k facilities = ~$646 total at sustained rate.

Usage:
  GOOGLE_PLACES_API_KEY=... python3 enrich-website-google-places.py --limit 500
  python3 enrich-website-google-places.py --limit 500   # reads from ~/Projects/secrets-manager/.env
"""
import argparse, json, os, sys, time, urllib.parse, urllib.request
import psycopg

DB_URL = 'postgresql:///restaurant_professional_directory?host=/tmp'

def load_key():
    if os.environ.get('GOOGLE_PLACES_API_KEY'):
        return os.environ['GOOGLE_PLACES_API_KEY']
    try:
        for line in open(os.path.expanduser('~/Projects/secrets-manager/.env')):
            if line.startswith('GOOGLE_PLACES_API_KEY='):
                return line.split('=', 1)[1].strip().strip('"').strip("'")
    except Exception:
        pass
    sys.exit('GOOGLE_PLACES_API_KEY not found in env or secrets registry')

KEY = load_key()
ENDPOINT = 'https://maps.googleapis.com/maps/api/place/findplacefromtext/json'
USER_AGENT = 'lacountyeats/1.0'

def find_place(name, city, state):
    q = f'{name} {city} {state}'
    params = {
        'input': q,
        'inputtype': 'textquery',
        'fields': 'place_id,name,website,formatted_address,rating,user_ratings_total',
        'key': KEY,
    }
    url = ENDPOINT + '?' + urllib.parse.urlencode(params)
    req = urllib.request.Request(url, headers={'User-Agent': USER_AGENT})
    try:
        with urllib.request.urlopen(req, timeout=10) as resp:
            data = json.loads(resp.read())
        if data.get('status') == 'OK' and data.get('candidates'):
            return data['candidates'][0]
        if data.get('status') in ('OVER_QUERY_LIMIT', 'REQUEST_DENIED'):
            print(f'  ! Google Places error: {data.get("status")} {data.get("error_message", "")}', file=sys.stderr)
            sys.exit(2)
        return None
    except urllib.error.HTTPError as e:
        if e.code == 429:
            time.sleep(5)
        return None
    except Exception:
        return None

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('--limit', type=int, default=500)
    ap.add_argument('--dry-run', action='store_true')
    args = ap.parse_args()

    with psycopg.connect(DB_URL) as conn:
        with conn.cursor() as cur:
            cur.execute("""
                SELECT f.facility_id, f.name, COALESCE(f.city,''), COALESCE(f.state,'CA')
                FROM facility f
                LEFT JOIN facility_enrichment fe ON fe.facility_id = f.facility_id
                WHERE (fe.website IS NULL OR fe.website = '')
                  AND f.name IS NOT NULL
                  AND f.city IS NOT NULL
                ORDER BY f.facility_id
                LIMIT %s
            """, (args.limit,))
            facilities = cur.fetchall()

        print(f'[gplaces] {len(facilities)} facilities to enrich')
        hits = misses = no_website = 0
        for i, (fid, name, city, state) in enumerate(facilities, 1):
            cand = find_place(name, city, state)
            if not cand:
                misses += 1
                if i % 50 == 0:
                    print(f'  [{i}/{len(facilities)}] hits={hits} no_website={no_website} no_match={misses}')
                time.sleep(0.05)
                continue
            website = cand.get('website', '')
            place_id = cand.get('place_id', '')
            addr = cand.get('formatted_address', '')
            rating = cand.get('rating')
            n_ratings = cand.get('user_ratings_total', 0)
            if not website:
                no_website += 1
                print(f'  [{i}/{len(facilities)}] · {name[:35]} → matched but no website ({addr[:40]})')
            else:
                hits += 1
                if not args.dry_run:
                    with conn.cursor() as cur:
                        cur.execute("""
                            INSERT INTO facility_enrichment (facility_id, website, google_place_id, rating_google, review_count_total)
                            VALUES (%s, %s, %s, %s, %s)
                            ON CONFLICT (facility_id) DO UPDATE SET
                                website = EXCLUDED.website,
                                google_place_id = COALESCE(facility_enrichment.google_place_id, EXCLUDED.google_place_id),
                                rating_google = COALESCE(EXCLUDED.rating_google, facility_enrichment.rating_google),
                                review_count_total = COALESCE(EXCLUDED.review_count_total, facility_enrichment.review_count_total)
                        """, (fid, website, place_id, rating, n_ratings))
                    conn.commit()
                print(f'  [{i}/{len(facilities)}] ✓ {name[:35]} → {website[:50]}')
            sys.stdout.flush()
            time.sleep(0.05)
        print(f'[gplaces] done · hits={hits} · matched-no-website={no_website} · no-match={misses} · hit-rate={100*hits/max(len(facilities),1):.1f}%')
        approx_cost = len(facilities) * 0.017
        print(f'[gplaces] approx cost: ${approx_cost:.2f} ({len(facilities)} Find Place calls @ $0.017)')

if __name__ == '__main__':
    main()