import geopandas as gpd
import leafmap

def build_trip_map(con, service, month, metric, cmap, legend_prefix):
    if metric == 'pu':
        table, col = 'trip_counts_pu', 'trips_pu'
    else:
        table, col = 'trip_counts_do', 'trips_do'
    df = con.sql(f"""
        SELECT
            tz.zone,
            t.{col} AS trips,
            ST_AsText(ST_Transform(tz.geometry, 'EPSG:26918', 'OGC:CRS84')) AS geometry
        FROM {table} AS t
        JOIN taxi_zones_utm AS tz ON t.LocationID = tz.LocationID
        WHERE t.service = '{service}' AND t.month = '{month}'
    """).df()
    gdf = gpd.GeoDataFrame(df, geometry=gpd.GeoSeries.from_wkt(df['geometry']), crs='EPSG:4326')
    m = leafmap.Map(center=[40.7, -73.9], zoom=10, draw_control=False)
    m.layout.height = '400px'
    m.add_basemap('CartoDB.DarkMatter')
    m.add_data(gdf, column='trips', cmap=cmap,
               legend_title=f'{legend_prefix} ({month})')
    return m


def build_demo_map(con, column, baseline_val, legend_title):
    df = con.sql(f"""
        SELECT
            zd.TaxiZone AS zone,
            ROUND(zd.{column} - {baseline_val}, 1) AS deviation,
            ST_AsText(ST_Transform(tz.geometry, 'EPSG:26918', 'OGC:CRS84')) AS geometry
        FROM zone_demographics AS zd
        JOIN taxi_zones_utm AS tz ON zd.LocationID = tz.LocationID
        WHERE zd.TotalPop > 0
    """).df()
    gdf = gpd.GeoDataFrame(df, geometry=gpd.GeoSeries.from_wkt(df['geometry']), crs='EPSG:4326')
    m = leafmap.Map(center=[40.7, -73.9], zoom=10, draw_control=False)
    m.layout.height = '400px'
    m.add_basemap('CartoDB.DarkMatter')
    m.add_data(gdf, column='deviation', cmap='RdYlBu', legend_title=legend_title)
    return m


def build_stats(con, service, month, metric, cmap, label):
    if metric == 'pu':
        table, col, alias = 'trip_counts_pu', 'trips_pu', 'Pickups'
    else:
        table, col, alias = 'trip_counts_do', 'trips_do', 'Dropoffs'
    df = con.sql(f"""
        SELECT
            tz.zone AS Neighborhood,
            t.{col} AS {alias}
        FROM {table} t
        JOIN taxi_zones_utm tz ON t.LocationID = tz.LocationID
        WHERE t.service = '{service}' AND t.month = '{month}'
        ORDER BY t.{col} DESC LIMIT 10
    """).df()
    header = f'### {service} Top 10 {label} ({month})'
    styled = df.style.background_gradient(cmap=cmap, subset=[alias]).hide(axis='index')
    return header, styled