import os import json import base64 import urllib.request import urllib.parse import random import time import openpyxl from http.server import SimpleHTTPRequestHandler, HTTPServer from refresh_images import INTERIOR_PHOTOS, MALE_PORTRAITS, FEMALE_PORTRAITS, get_portrait_url, get_interior_url, get_person_photo PORT = 8080 GEMINI_BATCH_SIZE = 10 BACKUP_DIR = os.path.join(os.path.dirname(__file__), 'excel_backups') PROJECT_DIR = os.path.dirname(__file__) DEFAULT_DATASET_FILE = os.path.join(BACKUP_DIR, 'fnb_merchant_seed_dataset_FNB_50_M011_M020_images_refreshed_relevant_v2.xlsx') ACTIVE_REFERENCE_META = os.path.join(PROJECT_DIR, 'latest_reference.json') def load_env_file(): env_path = os.path.join(os.path.dirname(__file__), '.env') if not os.path.exists(env_path): return try: with open(env_path, 'r', encoding='utf-8') as env_file: for raw_line in env_file: line = raw_line.strip() if not line or line.startswith('#') or '=' not in line: continue key, value = line.split('=', 1) key = key.strip() value = value.strip().strip('"').strip("'") if key and key not in os.environ: os.environ[key] = value except OSError as e: print(f"Warning: failed to load .env file: {e}") load_env_file() def sanitize_backup_filename(filename): safe_name = os.path.basename(filename or 'backup.xlsx').strip() if not safe_name: safe_name = 'backup.xlsx' return ''.join(c if c.isalnum() or c in ('-', '_', '.', ' ') else '_' for c in safe_name) def reserve_backup_path(filename): os.makedirs(BACKUP_DIR, exist_ok=True) safe_name = sanitize_backup_filename(filename) base_name, ext = os.path.splitext(safe_name) ext = ext or '.xlsx' candidate = os.path.join(BACKUP_DIR, f"{base_name}{ext}") counter = 1 while os.path.exists(candidate): candidate = os.path.join(BACKUP_DIR, f"{base_name}_{counter}{ext}") counter += 1 return candidate def to_project_relative_path(file_path): return os.path.relpath(file_path, PROJECT_DIR).replace(os.sep, '/') def get_active_reference_path(): if os.path.exists(ACTIVE_REFERENCE_META): try: with open(ACTIVE_REFERENCE_META, 'r', encoding='utf-8') as meta_file: data = json.load(meta_file) relative_path = data.get('relativePath', '').strip() if relative_path: absolute_path = os.path.join(PROJECT_DIR, relative_path) if os.path.exists(absolute_path): return absolute_path except (OSError, json.JSONDecodeError) as e: print(f"Warning: failed to read active reference metadata: {e}") return DEFAULT_DATASET_FILE def set_active_reference_path(file_path): relative_path = to_project_relative_path(file_path) with open(ACTIVE_REFERENCE_META, 'w', encoding='utf-8') as meta_file: json.dump({'relativePath': relative_path}, meta_file) return relative_path # ─── MASTER POOLS & TEMPLATES IN PYTHON (Matching JS) ─── REVIEWERS = [ { 'name': 'Mei Ling Tan', 'photo': 'photo-1494790108377-be9c29b29330', 'ethnicity': 'Chinese' }, { 'name': 'Wei Jie Lim', 'photo': 'photo-1507003211169-0a1dd7228f2d', 'ethnicity': 'Chinese' }, { 'name': 'Xiu Hui Chen', 'photo': 'photo-1517841905240-472988babdf9', 'ethnicity': 'Chinese' }, { 'name': 'Jun Hao Wong', 'photo': 'photo-1506794778202-cad84cf45f1d', 'ethnicity': 'Chinese' }, { 'name': 'Hui Min Ng', 'photo': 'photo-1534528741775-53994a69daeb', 'ethnicity': 'Chinese' }, { 'name': 'Zhi Wei Goh', 'photo': 'photo-1472099645785-5658abf4ff4e', 'ethnicity': 'Chinese' }, { 'name': 'Qi Xuan Lee', 'photo': 'photo-1488716820095-cbe80883c496', 'ethnicity': 'Chinese' }, { 'name': 'Amirah Binte Hassan', 'photo': 'photo-1524504388940-b1c1722653e1', 'ethnicity': 'Malay' }, { 'name': 'Faizal Bin Ismail', 'photo': 'photo-1570295999919-56ceb5ecca61', 'ethnicity': 'Malay' }, { 'name': 'Nurul Ain Binte Rahman', 'photo': 'photo-1531427186611-ecfd6d936c79', 'ethnicity': 'Malay' }, { 'name': 'Hafiz Bin Abdullah', 'photo': 'photo-1519085360753-af0119f7cbe7', 'ethnicity': 'Malay' }, { 'name': 'Priya Krishnamurthy', 'photo': 'photo-1580489944761-15a19d654956', 'ethnicity': 'Indian' }, { 'name': 'Arjun Selvam', 'photo': 'photo-1568602471122-7832951cc4c5', 'ethnicity': 'Indian' }, { 'name': 'Deepa Nair', 'photo': 'photo-1546961329-78bef0414d7c', 'ethnicity': 'Indian' }, { 'name': 'Rajan Pillai', 'photo': 'photo-1552058544-f2b08422138a', 'ethnicity': 'Indian' }, { 'name': 'Jamie Pereira', 'photo': 'photo-1502685104226-ee32379fefbe', 'ethnicity': 'Eurasian' }, { 'name': 'Alex D\'Souza', 'photo': 'photo-1542909168-82c3e7fdca5c', 'ethnicity': 'Eurasian' }, { 'name': 'Sophie Tan-Williams', 'photo': 'photo-1554151228-14d9def656e4', 'ethnicity': 'Eurasian' }, { 'name': 'Marcus Fernandez', 'photo': 'photo-1552374196-c4e7ffc6e126', 'ethnicity': 'Eurasian' }, { 'name': 'Clara Yap', 'photo': 'photo-1544723795-3fb6469f5b39', 'ethnicity': 'Eurasian' } ] PERSONNEL_POOL = [ { 'name': 'Cheryl Lim', 'photo': 'photo-1494790108377-be9c29b29330', 'ethnicity': 'Chinese' }, { 'name': 'Darren Goh', 'photo': 'photo-1507003211169-0a1dd7228f2d', 'ethnicity': 'Chinese' }, { 'name': 'Nicholas Teo', 'photo': 'photo-1500648767791-00dcc994a43e', 'ethnicity': 'Chinese' }, { 'name': 'Fiona Chen', 'photo': 'photo-1438761681033-6461ffad8d80', 'ethnicity': 'Chinese' }, { 'name': 'Brandon Tan', 'photo': 'photo-1506794778202-cad84cf45f1d', 'ethnicity': 'Chinese' }, { 'name': 'Keith Wong', 'photo': 'photo-1519085360753-af0119f7cbe7', 'ethnicity': 'Chinese' }, { 'name': 'Ryan Koh', 'photo': 'photo-1522075469751-3a6694fb2f61', 'ethnicity': 'Chinese' }, { 'name': 'Rachel Ng', 'photo': 'photo-1534528741775-53994a69daeb', 'ethnicity': 'Chinese' }, { 'name': 'Jeremy Low', 'photo': 'photo-1539571696357-5a69c17a67c6', 'ethnicity': 'Chinese' }, { 'name': 'Natalie Chua', 'photo': 'photo-1517841905240-472988babdf9', 'ethnicity': 'Chinese' }, { 'name': 'Isaac Lee', 'photo': 'photo-1501196354995-cbb51c65aaea', 'ethnicity': 'Chinese' }, { 'name': 'Michelle Wee', 'photo': 'photo-1524504388940-b1c1722653e1', 'ethnicity': 'Chinese' }, { 'name': 'Samuel Tay', 'photo': 'photo-1492562080023-ab3db95bfbce', 'ethnicity': 'Chinese' }, { 'name': 'Jessica Sim', 'photo': 'photo-1488716820095-cbe80883c496', 'ethnicity': 'Chinese' }, { 'name': 'Marcus Ho', 'photo': 'photo-1560250097-0b93528c311a', 'ethnicity': 'Chinese' }, { 'name': 'Kimberly Ong', 'photo': 'photo-1544005313-94ddf0286df2', 'ethnicity': 'Chinese' }, { 'name': 'Jonathan Seah', 'photo': 'photo-1472099645785-5658abf4ff4e', 'ethnicity': 'Chinese' }, { 'name': 'Valerie Leong', 'photo': 'photo-1531746020798-e6953c6e8e04', 'ethnicity': 'Chinese' }, { 'name': 'Adrian Loo', 'photo': 'photo-1513956589380-bad6acb9b9d4', 'ethnicity': 'Chinese' }, { 'name': 'Stephanie Yeo', 'photo': 'photo-1554151228-14d9def656e4', 'ethnicity': 'Chinese' }, { 'name': 'Gabriel Tan', 'photo': 'photo-1527980965255-d3b416303d12', 'ethnicity': 'Chinese' }, { 'name': 'Patricia Lim', 'photo': 'photo-1580489944761-15a19d654956', 'ethnicity': 'Chinese' }, { 'name': 'Benjamin Chan', 'photo': 'photo-1500048993953-d23a436266cf', 'ethnicity': 'Chinese' }, { 'name': 'Evelyn Ng', 'photo': 'photo-1542206395-9feb3edaa68d', 'ethnicity': 'Chinese' }, { 'name': 'Syazwan Bin Ramli', 'photo': 'photo-1566753323558-f4e0952af115', 'ethnicity': 'Malay' }, { 'name': 'Siti Nurhaliza', 'photo': 'photo-1529626455594-4ff0802cfb7e', 'ethnicity': 'Malay' }, { 'name': 'Farhan Bin Rosli', 'photo': 'photo-1531427186611-ecfd6d936c79', 'ethnicity': 'Malay' }, { 'name': 'Diana Binte Roslan', 'photo': 'photo-1544723795-3fb6469f5b39', 'ethnicity': 'Malay' }, { 'name': 'Iskandar Bin Zulkifli', 'photo': 'photo-1552058544-f2b08422138a', 'ethnicity': 'Malay' }, { 'name': 'Hidayah Binte Samad', 'photo': 'photo-1531123897727-8f129e1688ce', 'ethnicity': 'Malay' }, { 'name': 'Khairul Bin Anwar', 'photo': 'photo-1570295999919-56ceb5ecca61', 'ethnicity': 'Malay' }, { 'name': 'Fazira Binte Salleh', 'photo': 'photo-1508214751196-bcfd4ca60f91', 'ethnicity': 'Malay' }, { 'name': 'Ridzuan Bin Hashim', 'photo': 'photo-1568602471122-7832951cc4c5', 'ethnicity': 'Malay' }, { 'name': 'Nadhirah Binte Jamil', 'photo': 'photo-1573496359142-b8d87734a5a2', 'ethnicity': 'Malay' }, { 'name': 'Zulfikar Bin Hamid', 'photo': 'photo-1517423568366-8b83523034fd', 'ethnicity': 'Malay' }, { 'name': 'Aishah Binte Mansur', 'photo': 'photo-1537368910025-700350fe46c7', 'ethnicity': 'Malay' }, { 'name': 'Firdaus Bin Latif', 'photo': 'photo-1564564321837-a57b7070ac4f', 'ethnicity': 'Malay' }, { 'name': 'Kamariah Binte Daud', 'photo': 'photo-1504257486230-1699f45f011c', 'ethnicity': 'Malay' }, { 'name': 'Karthik Rajan', 'photo': 'photo-1520156473399-030ec603e5c4', 'ethnicity': 'Indian' }, { 'name': 'Shalini Devi', 'photo': 'photo-1530268729831-4b0b9e170218', 'ethnicity': 'Indian' }, { 'name': 'Vignesh Kumar', 'photo': 'photo-1509783236416-c9ad59bab472', 'ethnicity': 'Indian' }, { 'name': 'Preethi Nair', 'photo': 'photo-1589156280159-27698a70f29e', 'ethnicity': 'Indian' }, { 'name': 'Dinesh Pillay', 'photo': 'photo-1534751516642-a131ffd10b7f', 'ethnicity': 'Indian' }, { 'name': 'Meera Krishnan', 'photo': 'photo-1567532939604-b6b5b0db2604', 'ethnicity': 'Indian' }, { 'name': 'Suresh Naidu', 'photo': 'photo-1579038773867-044c48829161', 'ethnicity': 'Indian' }, { 'name': 'Divya Selvan', 'photo': 'photo-1619380061814-58f03707f082', 'ethnicity': 'Indian' }, { 'name': 'Christian de Souza', 'photo': 'photo-1607990283143-e81e7a2c93ab', 'ethnicity': 'Eurasian' }, { 'name': 'Sarah Rodrigues', 'photo': 'photo-1628157582853-a796fa650a6a', 'ethnicity': 'Eurasian' }, { 'name': 'Nathanial Pereira', 'photo': 'photo-1607746882042-944635dfe10e', 'ethnicity': 'Eurasian' }, { 'name': 'Clara Fernandez', 'photo': 'photo-1614644147798-f8c0fc9da7f6', 'ethnicity': 'Eurasian' } ] ROLES = ['Store Manager','Operations Lead','Kitchen Lead','Service Captain','Marketing Coordinator'] LEVELS = ['Lead','Senior','Senior','Standard','Junior'] FACILITY_IMAGES = [ { 'id': "photo-1554118811-1e0d58224f24", 'tags': ["cafe interior", "coffee shop", "tables", "indoor seating", "dining"] }, { 'id': "photo-1501339847302-ac426a4a7cbb", 'tags': ["cafe storefront", "outdoor seating", "patio", "exterior"] }, { 'id': "photo-1495474472287-4d71bcdd2085", 'tags': ["espresso bar", "barista counter", "coffee machine", "brewing"] }, { 'id': "photo-1555396273-367ea4eb4db5", 'tags': ["restaurant kitchen", "chef counter", "food prep", "cooking"] }, { 'id': "photo-1517248135467-4c7edcad34c4", 'tags': ["dining room", "group tables", "restaurant seating", "cozy dining"] }, { 'id': "photo-1546069901-ba9599a7e63c", 'tags': ["salad bar", "buffet", "healthy food display", "organic choices"] }, { 'id': "photo-1512621776951-a57141f2eefd", 'tags': ["vegan food corner", "green counter", "fresh juice"] }, { 'id': "photo-1511920170033-f8396924c348", 'tags': ["bakery display", "cake counter", "pastries", "dessert showcase"] }, { 'id': "photo-1577896851231-70ef18881754", 'tags': ["classroom", "kids desks", "learning area", "childcare"] }, { 'id': "photo-1564424224828-5d10d0b2c85b", 'tags': ["play area", "toys", "kindergarten", "indoor play"] }, { 'id': "photo-1587654780291-39c9404d746b", 'tags': ["kids learning corner", "educational games", "activities"] }, { 'id': "photo-1485546246426-74dc88dec4d9", 'tags': ["nursery", "baby cribs", "infant care", "nap room"] }, { 'id': "photo-1503676260728-1c00da094a0b", 'tags': ["kids library", "reading zone", "storybooks"] }, { 'id': "photo-1560066984-138dadb4c035", 'tags': ["hair salon chairs", "styling station", "haircut salon"] }, { 'id': "photo-1604654894610-df63bc536371", 'tags': ["nail salon desk", "manicure station", "pedicure"] }, { 'id': "photo-1522337360788-8b13dee7a37e", 'tags': ["facial bed", "skincare room", "treatment zone"] }, { 'id': "photo-1540555700478-4be289fbecef", 'tags': ["spa massage room", "therapy table", "relaxation room"] }, { 'id': "photo-1620331713507-b0a7d1a73387", 'tags': ["lash and brow chair", "cosmetic studio", "makeup"] }, { 'id': "photo-1516734212186-a967f81ad0d7", 'tags': ["pet grooming tub", "dog wash station", "grooming"] }, { 'id': "photo-1581888227599-779811939961", 'tags': ["dog play area", "pet cafe lobby", "cat playzone"] }, { 'id': "photo-1535268647977-a403b69fc7f5", 'tags': ["veterinary exam table", "pet clinic", "treatment room"] }, { 'id': "photo-1601758228041-f3b2795255f1", 'tags': ["dog boarding kennel", "pet supplies shelf", "food store"] }, { 'id': "photo-1441986300917-64674bd600d8", 'tags': ["clothing rack", "boutique interior", "shopping clothes"] }, { 'id': "photo-1534452203293-494d7ddbf7e0", 'tags': ["shoe display shelves", "shopping store", "accessories"] }, { 'id': "photo-1472851294608-062f824d29cc", 'tags': ["checkout counter", "cashier desk", "reception reception"] }, { 'id': "photo-1542838132-92c53300491e", 'tags': ["supermarket aisle", "grocery shelves", "retail display"] }, { 'id': "photo-1534438327276-14e5300c3a48", 'tags': ["weight lifting gym", "dumbbells rack", "workout space"] }, { 'id': "photo-1518611012118-696072aa579a", 'tags': ["yoga mats floor", "meditation studio interior", "pilates"] }, { 'id': "photo-1518310383802-640c2de311b2", 'tags': ["treadmill row", "cardio area", "fitness equipment"] }, { 'id': "photo-1526506118085-60ce8714f8c5", 'tags': ["pilates reformer", "exercise machines", "stretching"] }, { 'id': "photo-1511512578047-dfb367046420", 'tags': ["arcade machines", "gaming zone", "playstation"] }, { 'id': "photo-1552820728-8b83bb6b773f", 'tags': ["vr headsets play area", "gaming room", "simulator"] }, { 'id': "photo-1609873963870-dfae66681634", 'tags': ["trampoline park jumping area", "trampolines"] }, { 'id': "photo-1538481199705-c710c4e965fc", 'tags': ["bowling lanes pin deck", "bowling"] }, { 'id': "photo-1531538606174-0f90ff5dce83", 'tags': ["escape room game room", "puzzles", "lockers"] }, { 'id': "photo-1508847154043-be12a62861c1", 'tags': ["indoor playground slides", "ball pit"] } ] PRODUCT_TEMPLATES = { 'Cafes': [ {'name':'Signature Latte', 'desc':'A smooth espresso latte with silky milk and light foam, crafted for a warm and familiar cafe moment.', 'price':7, 'cat':'Beverage'}, {'name':'Brunch Pancake Stack', 'desc':'Fluffy pancakes stacked with a golden finish, served as a hearty cafe brunch plate with a sweet, comforting profile.', 'price':22, 'cat':'Brunch'}, {'name':'Cafe Avocado Toast', 'desc':'Crusty sourdough topped with fresh avocado, seasoning, and a poached egg for a light and satisfying brunch choice.', 'price':18, 'cat':'Brunch'}, {'name':'Croissant', 'desc':'A buttery, flaky croissant baked fresh and served warm as a quick pastry option for any time of day.', 'price':5, 'cat':'Pastry'}, {'name':'Iced Americano', 'desc':'Cold brewed espresso with filtered water over ice, delivering a clean and refreshing coffee hit.', 'price':6, 'cat':'Beverage'}, {'name':'Flat White', 'desc':'A smooth espresso-based coffee with velvety milk, balanced body, and a gentle finish for daily cafe sipping.', 'price':6, 'cat':'Beverage'}, {'name':'Cappuccino', 'desc':'A classic coffee with espresso, steamed milk, and airy foam, made for a warm and familiar cafe break.', 'price':6, 'cat':'Beverage'}, {'name':'Iced Coffee', 'desc':'Cold coffee served over ice with a clean, refreshing taste and a light finish for warm afternoons.', 'price':7, 'cat':'Beverage'}, {'name':'Banana Bread Slice', 'desc':'Moist banana bread slice with a soft crumb and gentle sweetness, ideal as a snack or light breakfast option.', 'price':6, 'cat':'Pastry'}, {'name':'Cheesecake Slice', 'desc':'Creamy New York-style cheesecake with a buttery crust and smooth filling, served as a dessert or afternoon treat.', 'price':9, 'cat':'Dessert'}, {'name':'Fresh Salad Bowl', 'desc':'Mixed greens, garden vegetables, and a light dressing tossed together for a refreshing cafe lunch choice.', 'price':16, 'cat':'Light Meal'}, {'name':'Grilled Sandwich', 'desc':'A toasted sandwich layered with fresh fillings, melted cheese, and a satisfying bite for a quick cafe meal.', 'price':14, 'cat':'Snack'}, {'name':'Latte Art Coffee', 'desc':'An espresso latte topped with a hand-poured milk art design, offering both a visual and flavour experience.', 'price':8, 'cat':'Beverage'}, {'name':'Creamy Pasta Plate', 'desc':'Creamy pasta tossed until glossy and rich, finished as a filling plate for guests who want a warm cafe main.', 'price':23, 'cat':'Pasta'}, {'name':'Matcha Latte', 'desc':'Premium ceremonial matcha whisked with steamed milk for a smooth, earthy green tea beverage.', 'price':7, 'cat':'Beverage'} ], 'default': [ {'name':'Signature Dish', 'desc':'Our signature dish, crafted with fresh local ingredients and served with care.', 'price':22, 'cat':'Main'}, {'name':'House Special', 'desc':'A crowd favourite made fresh daily, combining bold flavours with premium ingredients.', 'price':20, 'cat':'Main'}, {'name':'Set Meal A', 'desc':'A complete set meal featuring a main, side, and beverage for a satisfying experience.', 'price':25, 'cat':'Set'}, {'name':'Chef\'s Recommendation', 'desc':'Hand-picked by our chef for quality and flavour, representing the best of our menu.', 'price':28, 'cat':'Chef\'s Pick'}, {'name':'Classic Bowl', 'desc':'A comforting bowl of rice, protein, and fresh vegetables served in traditional style.', 'price':18, 'cat':'Bowl'}, {'name':'Combo Platter', 'desc':'A generous platter combining three of our most popular dishes, ideal for sharing.', 'price':35, 'cat':'Platter'}, {'name':'Light Bites', 'desc':'Small bites of crisp appetisers served warm and fresh, perfect to start.', 'price':12, 'cat':'Starter'}, {'name':'House Noodles', 'desc':'Hand-pulled noodles tossed in a rich sauce with toppings, prepared fresh.', 'price':16, 'cat':'Noodles'}, {'name':'Grilled Protein', 'desc':'Freshly grilled with seasoning and served with sides, suiting any appetite.', 'price':26, 'cat':'Grill'}, {'name':'House Soup', 'desc':'A rich broth simmered for hours with premium ingredients, served hot.', 'price':14, 'cat':'Soup'}, {'name':'Seasonal Dessert', 'desc':'A sweet finish made from seasonal fruits and premium dairy, crafted to complement.', 'price':10, 'cat':'Dessert'}, {'name':'Soft Drink', 'desc':'A chilled soft drink served with ice, perfect alongside any meal.', 'price':4, 'cat':'Beverage'}, {'name':'House Tea', 'desc':'Freshly brewed local tea served hot or iced, a refreshing complement.', 'price':4, 'cat':'Beverage'}, {'name':'Sharing Starter', 'desc':'A generous starter platter of bite-sized pieces to kick off a group meal.', 'price':18, 'cat':'Starter'}, {'name':'Weekend Special', 'desc':'A limited-weekend creation combining seasonal produce and chef creativity.', 'price':30, 'cat':'Special'} ] } def sample_reviewers(seed): arr = list(REVIEWERS) s = seed for i in range(len(arr) - 1, 0, -1): s = (s * 1664525 + 1013904223) & 0xffffffff # Ensure s is treated as 32-bit signed or unsigned for modular math unsigned_s = s if s >= 0 else (s + 0x100000000) j = unsigned_s % (i + 1) arr[i], arr[j] = arr[j], arr[i] return arr[:5] def generate_products(mid, category, merchant_name, seed): is_cafe = 'Cafe' in category or 'Coffee' in category pool = PRODUCT_TEMPLATES['Cafes'] if is_cafe else PRODUCT_TEMPLATES['default'] chosen = pool[:15] products = [] for i, p in enumerate(chosen): name = p['name'] if not is_cafe and name == 'Signature Dish': name = f"{merchant_name} Signature Dish" products.append({ 'id': f"I{mid[1:]}-{i+1}", 'name': name, 'desc': p['desc'].replace('${merchantName}', merchant_name), 'price': p['price'], 'cat': p['cat'], 'featured': i < 3 }) return products def generate_personnel(mid, mid_num, category, merchant_name): letters = ['A','B','C','D','E'] arr = list(PERSONNEL_POOL) s = mid_num * 73 + 19 for i in range(len(arr) - 1, 0, -1): s = (s * 1664525 + 1013904223) & 0xffffffff unsigned_s = s if s >= 0 else (s + 0x100000000) j = unsigned_s % (i + 1) arr[i], arr[j] = arr[j], arr[i] chosen = arr[:5] personnel = [] for i, role in enumerate(ROLES): p = chosen[i] personnel.append({ 'id': f"P{mid[1:]}{letters[i]}", 'name': p['name'], 'role': role, 'level': LEVELS[i] }) return personnel # ─── API FETCH / GEMINI INTEGRATION LOGIC ─── def fetch_nearby_places(api_key, place_type, page_token=None, keyword=None): url = f"https://maps.googleapis.com/maps/api/place/nearbysearch/json?location=1.3521,103.8198&radius=12000&type={place_type}&key={api_key}" if keyword and not page_token: url += f"&keyword={urllib.parse.quote(keyword)}" if page_token: url += f"&pagetoken={page_token}" req = urllib.request.Request(url, headers={'User-Agent': 'Mozilla/5.0'}) with urllib.request.urlopen(req) as res: return json.loads(res.read().decode('utf-8')) def get_place_search_config(industry_key, category): default_type_map = { 'childcare': 'school', 'beauty': 'beauty_salon', 'fnb': 'restaurant', 'pets': 'pet_store', 'retail': 'store', 'fitness': 'gym', 'play': 'amusement_park', 'others': 'establishment' } place_type = default_type_map.get(industry_key, 'restaurant') keyword = None if industry_key == 'fnb': fnb_search_map = { 'Cafes & Coffee': ('cafe', 'cafe coffee'), 'Western / Fusion': ('restaurant', 'western restaurant bistro'), 'Chinese Cuisine': ('restaurant', 'chinese restaurant'), 'Japanese Food': ('restaurant', 'japanese restaurant sushi ramen'), 'Korean': ('restaurant', 'korean restaurant'), 'Thai': ('restaurant', 'thai restaurant'), 'Indian': ('restaurant', 'indian restaurant'), 'Malay / Halal': ('restaurant', 'halal malay restaurant'), 'Desserts / Bakery': ('bakery', 'dessert bakery cake'), 'Hotpot': ('restaurant', 'hotpot steamboat restaurant'), 'Health (Vegan / Acai / Clean eating)': ('restaurant', 'vegan acai salad cafe'), 'Others (F&B)': ('restaurant', 'restaurant cafe food') } place_type, keyword = fnb_search_map.get(category, ('restaurant', 'restaurant cafe')) return place_type, keyword def fetch_place_details(api_key, place_id): fields = 'name,formatted_address,formatted_phone_number,website,rating,price_level,geometry,opening_hours,types,photos,vicinity,address_components,place_id' url = f"https://maps.googleapis.com/maps/api/place/details/json?place_id={place_id}&fields={urllib.parse.quote(fields)}&key={api_key}" req = urllib.request.Request(url, headers={'User-Agent': 'Mozilla/5.0'}) with urllib.request.urlopen(req) as res: return json.loads(res.read().decode('utf-8')) def is_relevant_fnb_place(place, category=None): name = (place.get('name') or '').lower() types = {t.lower() for t in place.get('types', [])} primary_types = {'restaurant', 'cafe', 'bakery'} supporting_types = {'meal_takeaway', 'meal_delivery', 'food'} positive_keywords = [ 'restaurant', 'restoran', 'cafe', 'coffee', 'bakery', 'bistro', 'eatery', 'kitchen', 'grill', 'bbq', 'hotpot', 'ramen', 'sushi', 'noodle', 'pizza', 'steak', 'brunch', 'kopi', 'toast', 'dessert', 'espresso', 'latte' ] hard_negative_keywords = [ 'hotel', 'hostel', 'resort', 'tower', 'residence', 'apartment', 'mall', 'shopping centre', 'shopping center', 'hospital', 'medical', 'clinic', 'school', 'tuition', 'bank', 'office', 'building', 'condominium', 'country club', 'clubhouse', 'club', 'community center', 'community centre', 'safra' ] soft_negative_keywords = ['bar', 'whiskey', 'whisky', 'pub', 'lounge'] food_centre_keywords = ['food centre', 'food center', 'hawker centre', 'hawker center', 'hawker'] has_primary_type = bool(types & primary_types) has_supporting_type = bool(types & supporting_types) has_positive_keyword = any(keyword in name for keyword in positive_keywords) has_hard_negative_keyword = any(keyword in name for keyword in hard_negative_keywords) has_soft_negative_keyword = any(keyword in name for keyword in soft_negative_keywords) has_food_centre_keyword = any(keyword in name for keyword in food_centre_keywords) if has_hard_negative_keyword: return False if has_soft_negative_keyword and not has_primary_type: return False if category == 'Cafes & Coffee': cafe_keywords = ['cafe', 'coffee', 'espresso', 'latte', 'kopi', 'toast'] return 'cafe' in types or any(keyword in name for keyword in cafe_keywords) or has_food_centre_keyword if category == 'Desserts / Bakery': bakery_keywords = ['bakery', 'cake', 'dessert', 'pastry', 'bread'] return 'bakery' in types or any(keyword in name for keyword in bakery_keywords) if has_food_centre_keyword: return True if has_primary_type: return True if has_supporting_type and has_positive_keyword: return True return has_positive_keyword def infer_area(address): if not address: return 'Central' upper = address.upper() areas = [ 'Orchard', 'Dhoby Ghaut', 'Somerset', 'Tiong Bahru', 'Buona Vista', 'Bishan', 'Novena', 'City Hall', 'Bugis', 'Marina Bay', 'Chinatown', 'Toa Payoh', 'Clementi', 'Jurong East', 'Jurong West', 'Bukit Timah', 'Bukit Panjang', 'Choa Chu Kang', 'Sengkang', 'Hougang', 'Punggol', 'Tampines', 'Bedok', 'Pasir Ris', 'Changi', 'Marine Parade', 'Katong', 'Paya Lebar', 'Simei', 'Serangoon', 'Ang Mo Kio', 'Yishun', 'Woodlands', 'Sembawang', 'Admiralty', 'HarbourFront', 'Sentosa', 'Little India', 'Farrer Park', 'Kallang', 'River Valley', 'Beach Road', 'Lavender', 'Geylang', 'Queenstown', 'Alexandra', 'Redhill', 'Boon Lay' ] for area in areas: if area.upper() in upper: return area districts = ['ORCHARD','CLEMENTI','JURONG','TAMPINES','BEDOK','CHANGI','BISHAN', 'PUNGGOL','SENGKANG','HOUGANG','YISHUN','WOODLANDS','ANG MO KIO', 'SERANGOON','BUKIT TIMAH','NOVENA','CHINATOWN','MARINA','TIONG BAHRU', 'KATONG','GEYLANG','PAYA LEBAR'] for d in districts: if d in upper: return d[0] + d[1:].lower() return 'Central' def get_region(area): area_region = { 'Orchard': 'Central', 'Dhoby Ghaut': 'Central', 'Somerset': 'Central', 'Tiong Bahru': 'Central', 'Buona Vista': 'West', 'Bishan': 'Central', 'Novena': 'Central', 'City Hall': 'Central', 'Bugis': 'Central', 'Marina Bay': 'Central', 'Chinatown': 'Central', 'Toa Payoh': 'Central', 'Clementi': 'West', 'Jurong East': 'West', 'Jurong West': 'West', 'Bukit Timah': 'Central', 'Bukit Panjang': 'West', 'Choa Chu Kang': 'West', 'Sengkang': 'North-East', 'Hougang': 'North-East', 'Punggol': 'North-East', 'Tampines': 'East', 'Bedok': 'East', 'Pasir Ris': 'East', 'Changi': 'East', 'Marine Parade': 'East', 'Katong': 'East', 'Paya Lebar': 'East', 'Simei': 'East', 'Serangoon': 'North-East', 'Ang Mo Kio': 'North-East', 'Yishun': 'North', 'Woodlands': 'North', 'Sembawang': 'North', 'Admiralty': 'North', 'HarbourFront': 'South', 'Sentosa': 'South', 'Little India': 'Central', 'Farrer Park': 'Central', 'Kallang': 'Central', 'River Valley': 'Central', 'Beach Road': 'Central', 'Lavender': 'Central', 'Geylang': 'East', 'Queenstown': 'West', 'Alexandra': 'West', 'Redhill': 'West', 'Boon Lay': 'West' } return area_region.get(area, 'Central') def call_gemini(api_key, system_instruction, prompt_text): url = f"https://generativelanguage.googleapis.com/v1beta/models/gemini-2.5-flash:generateContent?key={api_key}" payload = { "contents": [{"parts": [{"text": prompt_text}]}], "systemInstruction": {"parts": [{"text": system_instruction}]} if system_instruction else None, "generationConfig": { "responseMimeType": "application/json" } } headers = { 'Content-Type': 'application/json', 'User-Agent': 'Mozilla/5.0' } data = json.dumps(payload).encode('utf-8') req = urllib.request.Request(url, data=data, headers=headers, method='POST') try: with urllib.request.urlopen(req) as res: res_data = json.loads(res.read().decode('utf-8')) text = res_data['candidates'][0]['content']['parts'][0]['text'] return json.loads(text.strip()) except urllib.error.HTTPError as e: err_body = e.read().decode('utf-8') raise Exception(f"Gemini API HTTP Error {e.code}: {err_body}") def call_openai(api_key, system_instruction, prompt_text): url = "https://api.openai.com/v1/chat/completions" payload = { "model": "gpt-4.1-mini", "response_format": {"type": "json_object"}, "messages": [ {"role": "system", "content": system_instruction}, {"role": "user", "content": prompt_text} ], "temperature": 0.3 } headers = { 'Content-Type': 'application/json', 'Authorization': f'Bearer {api_key}', 'User-Agent': 'Mozilla/5.0' } data = json.dumps(payload).encode('utf-8') req = urllib.request.Request(url, data=data, headers=headers, method='POST') try: with urllib.request.urlopen(req) as res: res_data = json.loads(res.read().decode('utf-8')) text = res_data['choices'][0]['message']['content'] return json.loads(text.strip()) except urllib.error.HTTPError as e: err_body = e.read().decode('utf-8') raise Exception(f"OpenAI API HTTP Error {e.code}: {err_body}") def build_gemini_batch_prompt(merchant_contexts, industry_label): schema_example = { "merchants": [ { "merchantId": "M101", "reviews": [ { "reviewerName": "string", "overallRating": 5, "qualityRating": 5, "serviceRating": 5, "environmentRating": 5, "valueForMoneyRating": 4, "vibeTags": ["string", "string"], "reviewText": "string" } ], "facilities": [ { "categoryName": "string", "subFacilities": ["string", "string"], "image1PhotoId": "string", "image2PhotoId": "string" } ] } ] } input_payload = { "industry": industry_label, "merchants": merchant_contexts } return f"""Generate realistic Singapore business reviews and facility categories for every merchant in the input JSON. Return valid JSON only with the top-level key "merchants". Rules for each merchant: 1. Generate exactly 5 reviews for the listed reviewers and keep the reviewer order exactly as given. 2. The overall ratings for the 5 reviews must be exactly: 5, 5, 4, 3, 2. 3. qualityRating should match overallRating. 4. serviceRating, environmentRating, and valueForMoneyRating must be integers from 1 to 5. 5. Each review must have 2-3 vibeTags. 6. reviewText must use this exact structure with line breaks and emojis: star line for quality mentioning 1-2 product names bell line for service mentioning 1 personnel name sparkle line for environment receipt line for overall verdict 7. Generate exactly 10 facility categories per merchant. 8. Each facility category must have exactly 2 subFacilities. 9. image1PhotoId and image2PhotoId must be different and selected only from the allowed image pool. Allowed image pool: {json.dumps(FACILITY_IMAGES)} Response schema example: {json.dumps(schema_example)} Input JSON: {json.dumps(input_payload, ensure_ascii=False)}""" def is_valid_gemini_batch_item(item): return ( isinstance(item, dict) and isinstance(item.get('merchantId'), str) and isinstance(item.get('reviews'), list) and len(item.get('reviews', [])) == 5 and isinstance(item.get('facilities'), list) and len(item.get('facilities', [])) == 10 ) def apply_batch_results(merchant_contexts, merchant_lookup, batch_results, source_label): results_by_mid = { item.get('merchantId'): item for item in batch_results if is_valid_gemini_batch_item(item) } for context in merchant_contexts: mid = context['merchantId'] merchant = merchant_lookup[mid] item = results_by_mid.get(mid) if item: merchant['geminiReviews'] = item['reviews'] merchant['geminiFacilities'] = item['facilities'] print(f" {source_label} generated batch data for {merchant['name']} successfully.") else: print(f" ⚠️ {source_label} returned incomplete batch data for {merchant['name']}.") def enrich_merchants_with_ai_batches(gemini_key, openai_key, detailed_merchants, start_mid, industry_label, category): if not gemini_key and not openai_key: return print(f"Calling AI enrichment in batches of {GEMINI_BATCH_SIZE} merchants...") system_instruction = ( "You are an expert copywriter and dataset generator for Find, a Singapore business directory app. " "Generate realistic, high-quality reviews and facilities. " "Always return strict JSON matching the requested schema." ) for batch_start in range(0, len(detailed_merchants), GEMINI_BATCH_SIZE): batch_merchants = detailed_merchants[batch_start:batch_start + GEMINI_BATCH_SIZE] batch_number = batch_start // GEMINI_BATCH_SIZE + 1 print(f" Processing AI batch {batch_number} with {len(batch_merchants)} merchants...") merchant_contexts = [] merchant_lookup = {} for offset, merchant in enumerate(batch_merchants): absolute_index = batch_start + offset mid_num = start_mid + absolute_index mid = f"M{str(mid_num).zfill(3)}" products = generate_products(mid, category, merchant['name'], mid_num) personnel = generate_personnel(mid, mid_num, category, merchant['name']) chosen_reviewers = sample_reviewers(mid_num * 97 + 13) merchant_contexts.append({ "merchantId": mid, "merchantName": merchant['name'], "category": category, "description": f"{merchant['name']} is a {industry_label.lower()} business offering quality products and services.", "personnel": [ {"name": p['name'], "role": p['role']} for p in personnel ], "products": [ {"name": p['name'], "price": p['price']} for p in products ], "reviewers": [r['name'] for r in chosen_reviewers] }) merchant_lookup[mid] = merchant prompt = build_gemini_batch_prompt(merchant_contexts, industry_label) openai_failed = False if openai_key: try: result = call_openai(openai_key, system_instruction, prompt) batch_results = result.get('merchants', []) if isinstance(result, dict) else [] apply_batch_results(merchant_contexts, merchant_lookup, batch_results, "🧠 OpenAI") except Exception as e: openai_failed = True print(f" ⚠️ OpenAI batch {batch_number} failed: {str(e)}") else: openai_failed = True if openai_failed and gemini_key: try: result = call_gemini(gemini_key, system_instruction, prompt) batch_results = result.get('merchants', []) if isinstance(result, dict) else [] apply_batch_results(merchant_contexts, merchant_lookup, batch_results, "🤖 Gemini fallback") except Exception as e: print(f" ⚠️ Gemini fallback batch {batch_number} failed: {str(e)}") def generate_synthetic_places(count, category): sg_names = [ 'Kopi Corner','The Bean Spot','Milo Dinosaur House','Nanyang Heritage Coffee', 'Katong Kaya Toast','Tiong Bahru Bakery East','Common Man Coffee','Jewel Cafe', 'Seng Kee Black Bean Noodle','Lola\'s Cafe','Papa Palheta','Chye Seng Huat', 'Nylon Coffee Roasters','Duck & Hippo','Gather Here','Two Hats','Homeground Coffee', 'White Label Coffee','Void Deck Cafe','Eighteen Chefs','BARISTART Coffee', 'Highlander Coffee','Platform Coffee','The Refinery',' Percolate','Books & Coffee', 'Forty Hands','Sarnies','The Assembly Ground','Curious Palette','Symmetry','Little Part 1', 'Kith Cafe','Project Acai','Grain Traders','WAATR','iSETA Coffee','Mellower Coffee', 'Bettr Barista','Columbus Coffee','ORIGIN + BLOOM','CUT by Wolfgang Puck','SHAN','Tamarind Hill', 'Long Beach Seafood','Crystal Jade','Paradise Dynasty','Jumbo Seafood','Imperial Treasure' ] areas = [ 'Orchard', 'Clementi', 'Jurong East', 'Tampines', 'Bedok', 'Changi', 'Bishan', 'Punggol', 'Sengkang', 'Hougang', 'Yishun', 'Woodlands', 'Ang Mo Kio', 'Serangoon', 'Bukit Timah', 'Novena', 'Chinatown', 'Katong', 'Geylang' ] places = [] for i in range(min(count, len(sg_names))): name = sg_names[i] area = areas[i % len(areas)] places.append({ 'place_id': f"synthetic_{i}", 'name': name, 'vicinity': f"{10 + i} Singapore Street, {area}", 'geometry': { 'location': { 'lat': 1.3 + random.random() * 0.1, 'lng': 103.75 + random.random() * 0.15 } }, 'rating': round(3.5 + random.random() * 1.5, 1), 'price_level': random.choice([1, 2, 2, 3]) }) return places def parse_excel_merchants(file_path=None): file_path = file_path or get_active_reference_path() if not os.path.exists(file_path): print(f"Error: file not found at {file_path}") return [] import datetime def serialize_value(val): if isinstance(val, (datetime.datetime, datetime.date)): return val.isoformat() return val try: wb = openpyxl.load_workbook(file_path, data_only=True) # Find all sheets starting with M followed by 3 digits import re target_sheets = [s for s in wb.sheetnames if re.match(r'^M\d{3}\b', s)] merchants = [] for sheet_name in target_sheets: ws = wb[sheet_name] mid = sheet_name.split(' ', 1)[0] # Read grid into memory to avoid slow row lookup grid = [] for r in range(1, ws.max_row + 1): row_vals = [ws.cell(row=r, column=c).value for c in range(1, 15)] grid.append(row_vals) def find_row_by_keys(keys): keys_lower = [str(k).lower().strip() for k in keys] for idx, r in enumerate(grid): if r[0] and str(r[0]).lower().strip() in keys_lower: return idx + 1 return None # Parse Business Details details = {} details_mapping = { 'Logo URL': ['Logo URL', 'logoUrl', 'logo_url', 'logo'], 'Merchant Name': ['Merchant Name', 'merchantName', 'merchant_name', 'name'], 'Description': ['Description', 'description', 'desc'], 'Industry': ['Industry', 'industry'], 'Category': ['Category', 'category'], 'Subcategory': ['Subcategory', 'subcategory'], 'Keywords (max 10)': ['Keywords (max 10)', 'Keywords', 'keywords'], 'Price Tier': ['Price Tier', 'Price Range', 'priceRange', 'price_tier', 'price_range', 'priceTier'], 'Status': ['Status', 'businessStatus', 'business_status', 'status'], 'Merchant Thumbnail URL': ['Merchant Thumbnail URL', 'merchantThumbnailUrl', 'merchant_thumbnail_url', 'thumbnailUrl', 'thumbnail_url', 'thumbnail'] } for key, candidates in details_mapping.items(): r_idx = find_row_by_keys(candidates) if r_idx: details[key] = serialize_value(grid[r_idx-1][1]) else: details[key] = None # Handle defaults for missing Business Details keys (for M021-M050 format) mid_num = 0 try: mid_num = int(mid[1:]) except Exception: pass if not details['Logo URL']: logo_photo = INTERIOR_PHOTOS[mid_num % len(INTERIOR_PHOTOS)] details['Logo URL'] = get_interior_url(logo_photo, 1024, 1024) if not details['Merchant Thumbnail URL']: thumb_photo = INTERIOR_PHOTOS[(mid_num + 3) % len(INTERIOR_PHOTOS)] details['Merchant Thumbnail URL'] = get_interior_url(thumb_photo, 1920, 1440) if not details['Industry']: details['Industry'] = 'F&B' if not details['Keywords (max 10)']: cat = details['Category'] or 'cafe' details['Keywords (max 10)'] = f"{cat.lower()}, food, beverage, singapore" # Parse Address address = {} address_mapping = { 'Country': ['Country', 'country'], 'Postal Code': ['Postal Code', 'postalCode', 'postal_code'], 'Region': ['Region', 'region'], 'Area': ['Area', 'area'], 'Address Line 1': ['Address Line 1', 'addressLine1', 'address_line_1', 'address1'], 'Address Line 2': ['Address Line 2', 'addressLine2', 'address_line_2', 'address2'], 'Latitude': ['Latitude', 'latitude', 'lat'], 'Longitude': ['Longitude', 'longitude', 'lng'] } for key, candidates in address_mapping.items(): r_idx = find_row_by_keys(candidates) if r_idx: address[key] = serialize_value(grid[r_idx-1][1]) else: address[key] = None # Provide sensible defaults for missing Address values if not address['Country']: address['Country'] = 'Singapore' if not address['Area']: address['Area'] = infer_area(address['Address Line 1'] or '') if not address['Region']: address['Region'] = get_region(address['Area'] or 'Central') if not address['Postal Code']: address['Postal Code'] = str(238000 + mid_num * 37) if not address['Latitude']: address['Latitude'] = 1.3521 if not address['Longitude']: address['Longitude'] = 103.8198 # Parse Contacts contacts = {} contacts_mapping = { 'phone': ['phone', 'Phone', 'telephone', 'tel'], 'email': ['email', 'Email'], 'website': ['website', 'Website', 'web'], 'timezone': ['timezone', 'Timezone'], 'defaultLanguage': ['defaultLanguage', 'default_language', 'language'], 'acceptsReservations': ['acceptsReservations', 'accepts_reservations', 'reservations'], 'deliveryAvailable': ['deliveryAvailable', 'delivery_available', 'delivery'] } for key, candidates in contacts_mapping.items(): r_idx = find_row_by_keys(candidates) if r_idx: contacts[key] = serialize_value(grid[r_idx-1][1]) else: contacts[key] = None # Parse Operating Hours hours = [] hours_idx = find_row_by_keys(['OPERATING HOURS', 'operatingHours']) if hours_idx: for offset in range(1, 8): row_vals = grid[hours_idx + offset] if row_vals[0]: hours.append({ 'day': serialize_value(row_vals[0]), 'open': serialize_value(row_vals[1]), 'close': serialize_value(row_vals[2]), 'lastOrder': serialize_value(row_vals[3]), 'closed': serialize_value(row_vals[4]), 'notes': serialize_value(row_vals[5]) }) # Parse Banners banners = [] banners_idx = find_row_by_keys(['BANNER MEDIA', 'bannerMedia']) if banners_idx: offset = 1 while True: row_idx = banners_idx + offset if row_idx >= len(grid): break row_vals = grid[row_idx] if not row_vals[0] or row_vals[0] in ['ITEM INFORMATION / PRODUCTS', 'products', 'ITEM INFORMATION']: break banners.append({ 'id': serialize_value(row_vals[0]), 'mediaType': serialize_value(row_vals[1]), 'mediaUrl': serialize_value(row_vals[2]), 'altText': serialize_value(row_vals[3]), 'sortOrder': serialize_value(row_vals[4]), 'active': serialize_value(row_vals[5]) }) offset += 1 # Parse Products products = [] products_idx = find_row_by_keys(['ITEM INFORMATION / PRODUCTS', 'products', 'ITEM INFORMATION']) if products_idx: offset = 1 while True: row_idx = products_idx + offset if row_idx >= len(grid): break row_vals = grid[row_idx] if not row_vals[0] or row_vals[0] in ['COUPONS', 'coupons']: break products.append({ 'id': serialize_value(row_vals[0]), 'thumbnail': serialize_value(row_vals[1]), 'name': serialize_value(row_vals[2]), 'desc': serialize_value(row_vals[3]), 'price': serialize_value(row_vals[4]), 'cat': serialize_value(row_vals[5]), 'option': serialize_value(row_vals[6]), 'featured': serialize_value(row_vals[7]), 'stockStatus': serialize_value(row_vals[8]) }) offset += 1 # Parse Coupons coupons = [] coupons_idx = find_row_by_keys(['COUPONS', 'coupons']) if coupons_idx: offset = 1 while True: row_idx = coupons_idx + offset if row_idx >= len(grid): break row_vals = grid[row_idx] if not row_vals[0] or row_vals[0] in ['TERMS_AND_CONDITIONS', 'termsAndConditions', 'terms']: break coupons.append({ 'id': serialize_value(row_vals[0]), 'title': serialize_value(row_vals[1]), 'price': serialize_value(row_vals[2]), 'discountPercent': serialize_value(row_vals[3]), 'type': serialize_value(row_vals[4]), 'value': serialize_value(row_vals[5]), 'lastBoughtAt': serialize_value(row_vals[6]), 'soldCount': serialize_value(row_vals[7]), 'remainingCount': serialize_value(row_vals[8]) }) offset += 1 # Parse Terms & Conditions terms = [] terms_idx = find_row_by_keys(['TERMS_AND_CONDITIONS', 'termsAndConditions', 'terms']) if terms_idx: offset = 1 while True: row_idx = terms_idx + offset if row_idx >= len(grid): break row_vals = grid[row_idx] if not row_vals[0] or row_vals[0] in ['PERSONNEL', 'personnel']: break terms.append({ 'number': serialize_value(row_vals[0]), 'condition': serialize_value(row_vals[1]) }) offset += 1 # Parse Personnel personnel = [] personnel_idx = find_row_by_keys(['PERSONNEL', 'personnel']) if personnel_idx: offset = 1 while True: row_idx = personnel_idx + offset if row_idx >= len(grid): break row_vals = grid[row_idx] if not row_vals[0] or row_vals[0] in ['FACILITIES_JSON_FORMAT', 'facilities', 'FACILITIES']: break personnel.append({ 'id': serialize_value(row_vals[0]), 'thumbnail': serialize_value(row_vals[1]), 'name': serialize_value(row_vals[2]), 'role': serialize_value(row_vals[3]), 'desc': serialize_value(row_vals[4]), 'experience': serialize_value(row_vals[5]), 'level': serialize_value(row_vals[6]), 'specialties': serialize_value(row_vals[7]), 'workingDays': serialize_value(row_vals[8]) }) offset += 1 # Parse Facilities JSON Format facilities = [] facilities_idx = find_row_by_keys(['FACILITIES_JSON_FORMAT', 'facilities', 'FACILITIES']) if facilities_idx: offset = 1 while True: row_idx = facilities_idx + offset if row_idx >= len(grid): break row_vals = grid[row_idx] if not row_vals[0] or row_vals[0] in ['REVIEWS', 'reviews']: break cat_id = serialize_value(row_vals[0]) cat_name = serialize_value(row_vals[1]) order_val = serialize_value(row_vals[2]) # Parse images JSON images_raw = row_vals[3] parsed_images = [] if images_raw: try: img_list = json.loads(images_raw) for idx, img in enumerate(img_list): m_url = img.get('media_url', '') if 'source.unsplash.com' in m_url: p_id = INTERIOR_PHOTOS[(mid_num + idx) % len(INTERIOR_PHOTOS)] m_url = get_interior_url(p_id, 1264, 841) img['media_url'] = m_url parsed_images.append(img) except Exception as e: print(f"Warning: Failed to parse facilities images JSON: {str(e)}") # Parse facilities JSON fac_list_raw = row_vals[4] parsed_facs = [] if fac_list_raw: try: parsed_facs = json.loads(fac_list_raw) except Exception as e: print(f"Warning: Failed to parse sub-facilities JSON: {str(e)}") facilities.append({ 'categoryId': cat_id, 'categoryName': cat_name, 'order': order_val, 'images': parsed_images, 'facilities': parsed_facs }) offset += 1 # Parse Reviews reviews = [] reviews_idx = find_row_by_keys(['REVIEWS', 'reviews']) if reviews_idx: offset = 1 while True: row_idx = reviews_idx + offset if row_idx >= len(grid): break row_vals = grid[row_idx] if not row_vals[0] or row_vals[0] in ['PAYMENT METHODS', 'PAYMENT_METHODS', 'paymentMethods']: break r_id = serialize_value(row_vals[0]) r_name = serialize_value(row_vals[2]) r_img = serialize_value(row_vals[3]) if r_img and 'source.unsplash.com' in r_img: r_img = get_person_photo(r_name, offset) overall = serialize_value(row_vals[4]) quality = serialize_value(row_vals[5]) service = serialize_value(row_vals[6]) env = serialize_value(row_vals[7]) value = serialize_value(row_vals[8]) vibe_raw = row_vals[9] vibe_tags = [] if vibe_raw: try: vibe_tags = json.loads(vibe_raw) except Exception: vibe_tags = [v.strip() for v in str(vibe_raw).replace('[','').replace(']','').replace('"','').split(',') if v.strip()] rev_text = serialize_value(row_vals[11]) # Parse recommendedProductIds (col 12) and recommendedPersonnelIds (col 13) rec_products_raw = row_vals[12] if len(row_vals) > 12 else None rec_products = [] if rec_products_raw: try: rec_products = json.loads(rec_products_raw) except Exception: rec_products = [v.strip() for v in str(rec_products_raw).replace('[','').replace(']','').replace('"','').split(',') if v.strip()] rec_personnel_raw = row_vals[13] if len(row_vals) > 13 else None rec_personnel = [] if rec_personnel_raw: try: rec_personnel = json.loads(rec_personnel_raw) except Exception: rec_personnel = [v.strip() for v in str(rec_personnel_raw).replace('[','').replace(']','').replace('"','').split(',') if v.strip()] reviews.append({ 'id': r_id, 'reviewerName': r_name, 'reviewerImageUrl': r_img, 'overallRating': overall, 'qualityRating': quality, 'serviceRating': service, 'environmentRating': env, 'valueForMoneyRating': value, 'vibeTags': vibe_tags, 'reviewText': rev_text, 'recommendedProductIds': rec_products, 'recommendedPersonnelIds': rec_personnel }) offset += 1 merchants.append({ 'id': mid, 'details': details, 'address': address, 'contacts': contacts, 'hours': hours, 'banners': banners, 'products': products, 'coupons': coupons, 'personnel': personnel, 'terms': terms, 'facilities': facilities, 'reviews': reviews }) wb.close() return merchants except Exception as parse_exc: print(f"Exception during Excel parsing: {str(parse_exc)}") raise parse_exc # ───────────────────────────────────────────────── # PY SERVER HANDLER # ───────────────────────────────────────────────── class BackendHandler(SimpleHTTPRequestHandler): def end_headers(self): # Allow CORS self.send_header('Access-Control-Allow-Origin', '*') self.send_header('Access-Control-Allow-Headers', 'Content-Type') self.send_header('Access-Control-Allow-Methods', 'POST, GET, OPTIONS') super().end_headers() def do_OPTIONS(self): self.send_response(200) self.end_headers() def do_GET(self): if self.path == '/api/get-merchants': try: active_reference_path = get_active_reference_path() merchants = parse_excel_merchants(active_reference_path) self.send_response(200) self.send_header('Content-Type', 'application/json') self.end_headers() self.wfile.write(json.dumps({ 'status': 'ok', 'merchants': merchants, 'referenceFilePath': to_project_relative_path(active_reference_path) }).encode('utf-8')) except Exception as e: print(f"Error parsing Excel: {str(e)}") self.send_response(500) self.send_header('Content-Type', 'application/json') self.end_headers() self.wfile.write(json.dumps({ 'status': 'error', 'message': str(e) }).encode('utf-8')) else: super().do_GET() def do_POST(self): if self.path == '/api/save-excel': try: content_length = int(self.headers['Content-Length']) post_data = self.rfile.read(content_length) params = json.loads(post_data.decode('utf-8')) filename = params.get('filename', 'backup.xlsx') file_data = params.get('fileData', '') if not file_data: raise ValueError('Missing fileData for backup save') backup_path = reserve_backup_path(filename) workbook_bytes = base64.b64decode(file_data) with open(backup_path, 'wb') as backup_file: backup_file.write(workbook_bytes) self.send_response(200) self.send_header('Content-Type', 'application/json') self.end_headers() self.wfile.write(json.dumps({ 'status': 'ok', 'path': backup_path }).encode('utf-8')) except Exception as save_err: print(f"Error saving Excel backup: {str(save_err)}") self.send_response(500) self.send_header('Content-Type', 'application/json') self.end_headers() self.wfile.write(json.dumps({ 'status': 'error', 'message': str(save_err) }).encode('utf-8')) elif self.path == '/api/upload-excel': try: content_length = int(self.headers['Content-Length']) post_data = self.rfile.read(content_length) params = json.loads(post_data.decode('utf-8')) file_data = params.get('fileData', '') upload_name = params.get('filename', 'uploaded_reference.xlsx') if not file_data: raise ValueError('Missing fileData for upload') file_path = reserve_backup_path(upload_name) workbook_bytes = base64.b64decode(file_data) with open(file_path, 'wb') as f: f.write(workbook_bytes) print("Excel file uploaded. Refreshing images...") try: from refresh_images import update_excel_images update_excel_images(file_path) print("Images successfully updated in uploaded Excel.") except Exception as update_err: print(f"Warning: Failed to refresh images in uploaded Excel: {str(update_err)}") reference_relative_path = set_active_reference_path(file_path) merchants = parse_excel_merchants(file_path) self.send_response(200) self.send_header('Content-Type', 'application/json') self.end_headers() self.wfile.write(json.dumps({ 'status': 'ok', 'merchants': merchants, 'referenceFilePath': reference_relative_path }).encode('utf-8')) except Exception as err: print(f"Error in upload handler: {str(err)}") self.send_response(500) self.send_header('Content-Type', 'application/json') self.end_headers() self.wfile.write(json.dumps({ 'status': 'error', 'message': str(err) }).encode('utf-8')) elif self.path == '/api/fetch': # Parse parameters content_length = int(self.headers['Content-Length']) post_data = self.rfile.read(content_length) params = json.loads(post_data.decode('utf-8')) api_key = params.get('apiKey', '').strip() gemini_key = params.get('geminiApiKey', '').strip() or os.getenv('GEMINI_API_KEY', '').strip() openai_key = params.get('openaiApiKey', '').strip() or os.getenv('OPENAI_API_KEY', '').strip() industry_key = params.get('industry', 'fnb') category = params.get('category', 'Cafes & Coffee') start_mid = int(params.get('startMid', 101)) target_count = min(50, int(params.get('targetCount', 50))) industry_labels = { 'childcare': 'Childcare', 'beauty': 'Beauty', 'fnb': 'F&B', 'pets': 'Pets', 'retail': 'Retail', 'fitness': 'Fitness', 'play': 'Play', 'others': 'Others' } industry_label = industry_labels.get(industry_key, 'F&B') place_type, search_keyword = get_place_search_config(industry_key, category) print(f"Backend fetching {target_count} merchants for category: {category}...") if search_keyword: print(f"Using Google Maps search type '{place_type}' with keyword '{search_keyword}'.") try: # 1. Fetch from Google Maps all_places = [] page_token = None page = 0 if api_key: while len(all_places) < target_count + 10 and page < 3: print(f"Fetching page {page+1} from Maps API...") data = fetch_nearby_places(api_key, place_type, page_token, search_keyword) if data.get('status') in ['REQUEST_DENIED', 'INVALID_REQUEST']: raise Exception(f"Google Maps API Error: {data.get('error_message', data.get('status'))}") results = data.get('results', []) all_places.extend(results) page_token = data.get('next_page_token') page += 1 if not page_token: break if page < 3: time.sleep(2.2) # Maps requirement # De-duplicate seen = set() unique_places = [] for p in all_places: pid = p.get('place_id') if pid not in seen: seen.add(pid) unique_places.append(p) if industry_key == 'fnb': unique_places = [p for p in unique_places if is_relevant_fnb_place(p, category)] print(f"Filtered F&B candidates to {len(unique_places)} restaurant/cafe/food-centre-relevant places.") if not unique_places: print("No Google Maps places retrieved. Using synthetic dataset...") unique_places = generate_synthetic_places(target_count, category) selected = unique_places[:target_count] # Fetch details detailed_merchants = [] for i, place in enumerate(selected): print(f"Processing details {i+1}/{len(selected)}: {place.get('name')}...") detail = None if api_key and not place.get('place_id', '').startswith('synthetic_'): try: resp = fetch_place_details(api_key, place['place_id']) if resp.get('status') == 'OK': detail = resp.get('result') except Exception as e: print(f"Detail fetch failed: {str(e)}") r = detail if detail else place if industry_key == 'fnb' and not is_relevant_fnb_place(r, category): print(f"Skipping non-F&B place after detail check: {r.get('name')}") continue address = r.get('formatted_address', r.get('vicinity', '')) area = infer_area(address) # Postal code parsing import re postal_match = re.search(r'\b\d{6}\b', address) postal_code = postal_match.group(0) if postal_match else str(238000 + (start_mid + i) * 37) geo = r.get('geometry', {}).get('location', {}) photos = r.get('photos', place.get('photos', [])) or [] photo_references = [ p.get('photo_reference') for p in photos if p.get('photo_reference') ][:5] price_tiers = {0: '$', 1: '$', 2: '$$', 3: '$$$', 4: '$$$$'} price_level = r.get('price_level', 2) price_tier_val = price_tiers.get(price_level, '$$') detailed_merchants.append({ 'place_id': place.get('place_id'), 'name': r.get('name'), 'address1': address.split(',')[0] if address else f"{start_mid + i} Singapore Road", 'vicinity': r.get('vicinity'), 'area': area, 'region': get_region(area), 'postalCode': postal_code, 'lat': geo.get('lat', 1.3521), 'lng': geo.get('lng', 103.8198), 'phone': ''.join(filter(str.isdigit, r.get('formatted_phone_number', '')))[-8:] if r.get('formatted_phone_number') else None, 'website': r.get('website'), 'rating': r.get('rating', 4.0), 'priceTier': price_tier_val, 'googlePhotoReferences': photo_references }) if industry_key == 'fnb' and len(detailed_merchants) < target_count: print(f"Only {len(detailed_merchants)} F&B-relevant merchants found. Filling remainder with synthetic F&B data...") missing_count = target_count - len(detailed_merchants) synthetic_places = generate_synthetic_places(missing_count, category) for offset, place in enumerate(synthetic_places, start=len(detailed_merchants)): area = infer_area(place.get('vicinity', '')) detailed_merchants.append({ 'place_id': place.get('place_id'), 'name': place.get('name'), 'address1': place.get('vicinity', f"{start_mid + offset} Singapore Road").split(',')[0], 'vicinity': place.get('vicinity'), 'area': area, 'region': get_region(area), 'postalCode': str(238000 + (start_mid + offset) * 37), 'lat': place.get('geometry', {}).get('location', {}).get('lat', 1.3521), 'lng': place.get('geometry', {}).get('location', {}).get('lng', 103.8198), 'phone': None, 'website': None, 'rating': place.get('rating', 4.0), 'priceTier': {0: '$', 1: '$', 2: '$$', 3: '$$$', 4: '$$$$'}.get(place.get('price_level', 2), '$$'), 'googlePhotoReferences': [] }) # 2. Gemini Enrichment if gemini_key or openai_key: enrich_merchants_with_ai_batches( gemini_key, openai_key, detailed_merchants, start_mid, industry_label, category ) # Respond self.send_response(200) self.send_header('Content-Type', 'application/json') self.end_headers() response_payload = { 'status': 'ok', 'merchants': detailedMerchants if 'detailedMerchants' in locals() else detailed_merchants } self.wfile.write(json.dumps(response_payload).encode('utf-8')) except Exception as outer_err: print(f"Error in fetch execution: {str(outer_err)}") self.send_response(500) self.send_header('Content-Type', 'application/json') self.end_headers() self.wfile.write(json.dumps({ 'status': 'error', 'message': str(outer_err) }).encode('utf-8')) else: super().do_POST() if __name__ == '__main__': # Make sure we run in the right directory os.chdir(os.path.dirname(os.path.abspath(__file__))) print(f"Starting Python Backend server at http://localhost:{PORT}") print("Serving files and handling APIs...") server = HTTPServer(('', PORT), BackendHandler) try: server.serve_forever() except KeyboardInterrupt: print("\nExiting server.") server.server_close()