import requests
from helpers.db import get_connection

SERPAPI_KEY = "fce322c02fe4410902365920b689b1c3d71af28551339529e86a215559ddd45f"
MAX_PAGES = 3  # SerpAPI allows 20 results/page, 3 pages = ~60 per tile

# Rough grid across San Antonio
TILES = [
    (29.4241, -98.4936),  # Downtown
    (29.4892, -98.3936),  # NE
    (29.4892, -98.5936),  # NW
    (29.3592, -98.3936),  # SE
    (29.3592, -98.5936),  # SW
    (29.4241, -98.3936),  # East
    (29.4241, -98.5936),  # West
    (29.4892, -98.4936),  # North Central
    (29.3592, -98.4936),  # South Central
]

def fetch_places(query, category, lat, lng):
    all_businesses = []

    for page in range(MAX_PAGES):
        start = page * 20
        print(f"🔍 {query} @ ({lat}, {lng}) page {page + 1}")

        params = {
            "engine": "google_maps",
            "q": query,
            "type": "search",
            "ll": f"@{lat},{lng},14z",
            "api_key": SERPAPI_KEY,
            "start": start
        }

        response = requests.get("https://serpapi.com/search.json", params=params)
        results = response.json()
        local_results = results.get("local_results", [])

        if not local_results:
            print("🚫 No more results.")
            break

        for place in local_results:
            name = place.get("title")
            website = place.get("website")
            phone = place.get("phone")
            address = place.get("address")

            if name and website:
                all_businesses.append({
                    "name": name,
                    "website": website,
                    "phone": phone,
                    "address": address,
                    "category": category
                })

    return all_businesses

def insert_businesses(businesses):
    conn = get_connection()
    cur = conn.cursor(dictionary=True)

    inserted = 0
    for biz in businesses:
        norm_address = biz['address'].strip().lower() if biz['address'] else ""

        # Fully consume the result (fixes "unread result" error)
        cur.execute("SELECT id FROM gym_contacts WHERE LOWER(TRIM(address)) = %s", (norm_address,))
        cur.fetchall()  # ✅ Consume the result set

        if cur.rowcount > 0:
            print(f"⏭️ Skipping duplicate address: {biz['address']}")
            continue

        cur.execute("""
            INSERT INTO gym_contacts (name, website, category, address, phone)
            VALUES (%s, %s, %s, %s, %s)
        """, (biz['name'], biz['website'], biz['category'], biz['address'], biz['phone']))
        inserted += 1

    conn.commit()
    cur.close()
    conn.close()
    print(f"📦 Inserted {inserted} new businesses.")

if __name__ == "__main__":
    seen = set()
    all_businesses = []

    for lat, lng in TILES:
        for category, query in [("gym", "gyms near me"), ("supplement", "supplement stores near me")]:
            results = fetch_places(query, category, lat, lng)
            for biz in results:
                key = (biz['name'].lower(), biz['website'].lower())
                if key not in seen:
                    seen.add(key)
                    all_businesses.append(biz)

    insert_businesses(all_businesses)