import googlemaps
import json
import time

# --- 配置 ---
# 💡 强烈建议：请更换已在控制台撤销并重新生成的 API Key
API_KEY = 'AIzaSyBFXMXAMXplWh9zPXYnCcVtXhk3POkAEvY'
gmaps = googlemaps.Client(key=API_KEY)

# 限制设置
MAX_RESTAURANTS = 1000
processed_place_ids = set() 
all_sql_statements = []

TAG_MAPPING = {
    "halal": "tag_halal",
    "chinese_restaurant": "tag_chinese",
    "malay_restaurant": "tag_malay",
    "indian_restaurant": "tag_indian",
    "cafe": "tag_coffee",
    "bakery": "tag_dessert",
    "fast_food": "tag_fast_food",
    "vegetarian_restaurant": "tag_vegetarian",
    "bar": "tag_drinks",
}

def get_my_tags(google_types, name):
    found_tags = []
    if not google_types: google_types = []
    name_lower = name.lower()
    for g_type, my_tag in TAG_MAPPING.items():
        if g_type in google_types:
            found_tags.append(my_tag)
    if any(k in name_lower for k in ["spicy", "pedas", "mala"]): found_tags.append("tag_spicy")
    if "chicken" in name_lower: found_tags.append("tag_chicken")
    if "breakfast" in name_lower: found_tags.append("tag_breakfast")
    return list(set(found_tags))

def process_single_place(place_id):
    try:
        details = gmaps.place(place_id=place_id, fields=[
            'name', 'formatted_address', 'geometry', 'opening_hours', 
            'photo', 'type', 'rating', 'user_ratings_total'
        ]).get('result', {})

        name = details.get('name', '').replace("'", "''")
        address = details.get('formatted_address', '').replace("'", "''")
        loc = details.get('geometry', {}).get('location', {})
        lat, lng = loc.get('lat'), loc.get('lng')
        rating = details.get('rating', 0.0)
        visited = details.get('user_ratings_total', 0)

        images = []
        photo_data = details.get('photos') or details.get('photo')
        if photo_data:
            for p in photo_data[:3]:
                url = f"https://maps.googleapis.com/maps/api/place/photo?maxwidth=800&photoreference={p['photo_reference']}&key={API_KEY}"
                images.append({"url": url, "type": "image"})

        type_data = details.get('types') or details.get('type')
        tags = get_my_tags(type_data, name)

        # 💡 注意：确保你的 logo_url 字段已通过 ALTER TABLE 改为 TEXT 类型
        sql = f"""
INSERT INTO `restaurants` (`created_by`, `name`, `latitude`, `longitude`, `address`, `logo_url`, `description`, `rating`, `visited_count`, `tags`, `images`) 
VALUES (1, '{name}', {lat}, {lng}, '{address}', '{images[0]['url'] if images else ''}', 'Auto-scraped from Batu Pahat', {rating}, {visited}, '{json.dumps(tags)}', '{json.dumps(images)}');
SET @last_rest_id = LAST_INSERT_ID();"""
        
        if 'opening_hours' in details and 'periods' in details['opening_hours']:
            for p in details['opening_hours']['periods']:
                day = p['open']['day']
                db_day = 7 if day == 0 else day 
                open_t = f"{p['open']['time'][:2]}:{p['open']['time'][2:]}:00"
                close_t = f"{p['close']['time'][:2]}:{p['close']['time'][2:]}:00" if 'close' in p else "23:59:59"
                sql += f"\nINSERT INTO `opening_hours` (`restaurant_id`, `day_of_week`, `open_time`, `close_time`, `is_closed`) VALUES (@last_rest_id, {db_day}, '{open_t}', '{close_t}', 0);"
        
        return sql + "\n-- ----------------------------"
    except Exception as e:
        print(f"Error processing {place_id}: {e}")
        return None

# --- Batu Pahat 网格搜索逻辑 ---
def start_grid_scrape_bp():
    # 📍 Batu Pahat 范围设定 (涵盖市中心及周边主要镇区如 BP Mall, Old Street 等)
    lat_start, lat_end = 1.8300, 1.8800  
    lng_start, lng_end = 102.9100, 102.9800 
    step = 0.015 # 步长缩小以提高精准度

    curr_lat = lat_start
    while curr_lat < lat_end:
        curr_lng = lng_start
        while curr_lng < lng_end:
            if len(processed_place_ids) >= MAX_RESTAURANTS:
                break

            print(f"Scraping Batu Pahat: {curr_lat:.4f}, {curr_lng:.4f}... (Count: {len(processed_place_ids)})")
            
            try:
                # 增大半径到 2000m 以覆盖郊区餐厅
                response = gmaps.places_nearby(
                    location=(curr_lat, curr_lng),
                    radius=2000,
                    type='restaurant'
                )

                for p in response.get('results', []):
                    pid = p['place_id']
                    if pid not in processed_place_ids:
                        sql = process_single_place(pid)
                        if sql:
                            all_sql_statements.append(sql)
                            processed_place_ids.add(pid)
                        
                        if len(processed_place_ids) >= MAX_RESTAURANTS:
                            print("Reached limit. Stopping...")
                            return

                time.sleep(1.2) # 稍微增加延迟，保护 API 额度
            except Exception as e:
                print(f"Grid error: {e}")

            curr_lng += step
        curr_lat += step

# --- 执行 ---
start_grid_scrape_bp()

filename = 'bp_restaurants_data.sql'
with open(filename, 'w', encoding='utf-8') as f:
    f.write("SET NAMES utf8mb4;\nSET FOREIGN_KEY_CHECKS = 0;\n")
    for s in all_sql_statements:
        f.write(s + "\n")

print(f"Batu Pahat data saved to {filename}")