import osmnx as ox
import mysql.connector
import json
from datetime import datetime

# ✅ List of supported cities (expanded to hard-to-park 60+ U.S. cities)
cities = [
    ("New York", "NY"),
    ("Los Angeles", "CA"),
    ("San Francisco", "CA"),
    ("Boston", "MA"),
    ("Chicago", "IL"),
    ("Seattle", "WA"),
    ("Philadelphia", "PA"),
    ("Washington", "DC"),
    ("Miami", "FL"),
    ("Honolulu", "HI"),
    ("Portland", "OR"),
    ("Denver", "CO"),
    ("San Diego", "CA"),
    ("Austin", "TX"),
    ("Dallas", "TX"),
    ("Houston", "TX"),
    ("Atlanta", "GA"),
    ("Nashville", "TN"),
    ("Charlotte", "NC"),
    ("Phoenix", "AZ"),
    ("Salt Lake City", "UT"),
    ("Baltimore", "MD"),
    ("Minneapolis", "MN"),
    ("Cambridge", "MA"),
    ("Palo Alto", "CA"),
    ("Santa Monica", "CA"),
    ("Santa Barbara", "CA"),
    ("New Orleans", "LA"),
    ("Las Vegas", "NV"),
    ("San Jose", "CA"),
    ("Oakland", "CA"),
    ("Sacramento", "CA"),
    ("St. Louis", "MO"),
    ("Cleveland", "OH"),
    ("Detroit", "MI"),
    ("Pittsburgh", "PA"),
    ("Orlando", "FL"),
    ("Tampa", "FL"),
    ("Fort Lauderdale", "FL"),
    ("Boulder", "CO"),
    ("Madison", "WI"),
    ("Providence", "RI"),
    ("Hartford", "CT"),
    ("Stamford", "CT"),
    ("Jersey City", "NJ"),
    ("Newark", "NJ"),
    ("Long Beach", "CA"),
    ("Santa Cruz", "CA"),
    ("Berkeley", "CA"),
    ("Pasadena", "CA"),
    ("Glendale", "CA"),
    ("Scottsdale", "AZ"),
    ("Tempe", "AZ"),
    ("Raleigh", "NC"),
    ("Durham", "NC"),
    ("Charleston", "SC"),
    ("Savannah", "GA"),
    ("Burlington", "VT")
]

# ✅ MySQL DB credentials
db_config = {
    "host": "localhost",
    "user": "root",
    "password": "",
    "database": "eco_parking"
}

try:
    # ✅ Connect to MySQL
    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()

    # ✅ Clear existing table data (optional)
    cursor.execute("DELETE FROM usa_city_polygons")
    conn.commit()

    for city_name, state_code in cities:
        full_name = f"{city_name}, {state_code}, USA"
        print(f"Fetching polygon for: {full_name}")

        try:
            # Get city boundary as a polygon
            gdf = ox.geocode_to_gdf(full_name, which_result=1)
            geometry = gdf.iloc[0].geometry

            # Convert to GeoJSON-style dict
            geojson = {"type": "Polygon", "coordinates": []}

            if geometry.geom_type == "Polygon":
                geojson["coordinates"] = [list(geometry.exterior.coords)]
            elif geometry.geom_type == "MultiPolygon":
                largest = max(geometry.geoms, key=lambda p: p.area)
                geojson["coordinates"] = [list(largest.exterior.coords)]
            else:
                print(f"❌ Unknown geometry type for {full_name}")
                continue

            polygon_json = json.dumps(geojson)

            # ✅ Insert into database
            sql = """
            INSERT INTO usa_city_polygons (city_name, state_code, polygon_json, created_at)
            VALUES (%s, %s, %s, %s)
            """
            values = (city_name, state_code, polygon_json, datetime.now())

            cursor.execute(sql, values)
            conn.commit()
            print(f"✅ Inserted: {city_name}, {state_code}")

        except Exception as e:
            print(f"❌ Error processing {full_name}: {e}")

except mysql.connector.Error as db_err:
    print(f"❌ Database connection error: {db_err}")

finally:
    try:
        cursor.close()
        conn.close()
    except:
        pass

print("🎉 Done inserting all city polygons.")