import json import os import sys from shutil import copy2 from urllib.parse import quote import openpyxl FACILITY_KEYWORDS = { 'menu highlights': 'restaurant food,dish,brunch', 'popular dishes': 'restaurant food,dish,chef special', 'fresh choices': 'fresh salad bowl,healthy food', 'order counter': 'cafe counter,order counter', 'wifi': 'wifi,laptop,cafe', 'free wifi': 'wifi,laptop,cafe', 'air conditioning': 'air conditioning,restaurant interior', 'reservations': 'reserved table,restaurant', 'reservation': 'reserved table,restaurant', 'group tables': 'group dining table,restaurant', 'group seating': 'group dining,restaurant table', 'indoor seating': 'restaurant seating,indoor', 'outdoor seating': 'outdoor seating,cafe terrace', 'takeaway pickup': 'takeaway coffee,pickup counter', 'takeaway': 'takeaway food,coffee cup', 'pickup': 'pickup counter,restaurant', 'delivery': 'food delivery,delivery bag', 'delivery available': 'food delivery,delivery bag', 'parking': 'parking lot,city', 'free parking': 'parking lot,city', 'wheelchair accessible': 'wheelchair ramp,accessibility', 'accessible': 'wheelchair ramp,accessibility', 'kids friendly': 'family restaurant,kids meal', 'kids menu': 'kids meal,family restaurant', 'play area': 'kids play area,family', 'halal': 'halal food,restaurant', 'vegetarian options': 'vegetarian food,salad', 'vegan options': 'vegan bowl,salad', 'balanced meals': 'healthy meal,balanced plate', 'relaxed seating': 'cozy cafe seating,armchair', 'pet friendly': 'dog friendly cafe,pet friendly', 'cashless payments': 'credit card payment,checkout', 'credit card': 'credit card payment,checkout', 'contactless': 'contactless payment,checkout', 'restroom': 'restroom sign,interior', 'toilet': 'restroom sign,interior', 'live music': 'live music,restaurant', 'private room': 'private dining room,restaurant', 'smoking area': 'smoking area sign,restaurant', 'prayer room': 'prayer room,quiet room', } def to_sheet_mid(sheet_name): if not sheet_name or not sheet_name.startswith('M') or len(sheet_name) < 4: return None token = sheet_name.split(' ', 1)[0] if len(token) != 4 or not token[1:].isdigit(): return None return int(token[1:]) def build_unsplash_url(width, height, query, sig): q = quote(query, safe=',') return f'https://source.unsplash.com/{width}x{height}/?{q}&sig={sig}' def keywords_for_facility(facility_name, category_name): name = (facility_name or '').strip().lower() if name in FACILITY_KEYWORDS: return FACILITY_KEYWORDS[name] for k, v in FACILITY_KEYWORDS.items(): if k in name: return v fallback = (facility_name or category_name or 'restaurant').strip().lower() fallback = fallback.replace('&', ' ').replace('/', ' ') fallback = ' '.join(fallback.split()) if not fallback: fallback = 'restaurant' if 'restaurant' not in fallback and 'cafe' not in fallback: fallback = f'{fallback},restaurant' return fallback.replace(' ', '-').replace('--', '-').replace('-', ' ') def find_facilities_section_row(ws): for r in range(1, ws.max_row + 1): v = ws.cell(r, 1).value if v in ('FACILITIES_JSON_FORMAT', 'FACILITIES', 'facilities'): return r return None def iter_facility_rows(ws, section_row): start_row = section_row + 2 row = start_row while True: first_cell = ws.cell(row, 1).value if not first_cell: break if first_cell in ('REVIEWS', 'reviews', 'TERMS_AND_CONDITIONS', 'terms'): break yield row row += 1 def update_facilities_images(path, mid_start=11, mid_end=20): backup_path = path.replace('.xlsx', '_before_facilities_images_fix.xlsx') if not os.path.exists(backup_path): copy2(path, backup_path) wb = openpyxl.load_workbook(path) updated_rows = 0 for sheet_name in wb.sheetnames: mid_num = to_sheet_mid(sheet_name) if mid_num is None or mid_num < mid_start or mid_num > mid_end: continue ws = wb[sheet_name] section_row = find_facilities_section_row(ws) if not section_row: continue for row_idx in iter_facility_rows(ws, section_row): category_name = ws.cell(row_idx, 2).value images_raw = ws.cell(row_idx, 4).value facilities_raw = ws.cell(row_idx, 5).value if not images_raw: continue try: images = json.loads(images_raw) except Exception: continue facilities = [] if facilities_raw: try: facilities = json.loads(facilities_raw) except Exception: facilities = [] if not isinstance(images, list) or not images: continue for i, img in enumerate(images): facility_name = None if isinstance(facilities, list) and i < len(facilities) and isinstance(facilities[i], dict): facility_name = facilities[i].get('facilityName') if not facility_name and isinstance(facilities, list) and facilities and isinstance(facilities[0], dict): facility_name = facilities[0].get('facilityName') width = int(img.get('width') or 1264) height = int(img.get('height') or 841) kw = keywords_for_facility(facility_name, category_name) img['media_url'] = build_unsplash_url(width, height, kw, f'M{mid_num:03d}-fac-{row_idx}-{i+1}') caption_text = (facility_name or category_name or '').strip() if caption_text: img.setdefault('caption', {}) img['caption']['en'] = f'{caption_text} image relevant to {sheet_name}.' if i == 0: img['is_primary'] = True ws.cell(row_idx, 4).value = json.dumps(images, ensure_ascii=False) updated_rows += 1 wb.save(path) return updated_rows, backup_path if __name__ == '__main__': if len(sys.argv) < 2: print('Usage: python3 update_facilities_images.py /absolute/path/to/file.xlsx [midStart] [midEnd]') raise SystemExit(1) file_path = sys.argv[1] start = int(sys.argv[2]) if len(sys.argv) >= 3 else 11 end = int(sys.argv[3]) if len(sys.argv) >= 4 else 20 rows, backup = update_facilities_images(file_path, start, end) print(f'Updated facilities image URLs in {rows} rows.') print(f'Backup copy: {backup}') print(f'Workbook updated: {file_path}')