import solara
import duckdb
from dashboard_helpers import build_trip_map, build_demo_map, build_stats

# 1. Connect to our pre-baked database in read-only mode
con = duckdb.connect('processed_dashboard.db', read_only=True)
con.install_extension('spatial')
con.load_extension('spatial')

# 2. Grab baselines
baseline_df = con.sql("SELECT * FROM city_baselines").df()
baseline_white = float(baseline_df["baseline_white_pct"].iloc[0]) / 100.0
baseline_black = float(baseline_df["baseline_black_pct"].iloc[0]) / 100.0

# 3. Define our Reactive State
selected_month = solara.reactive("Feb2025")

# 4. Build Reusable Components
@solara.component
def TaxiMap(service, metric, cmap, title):
    month = selected_month.value
    m = build_trip_map(con, service, month, metric, cmap, title)
    
    with solara.Card(title=f"{title} ({month})"):
        solara.display(m)

@solara.component
def ServiceStatsCard(service, cmap_pu, cmap_do):
    month = selected_month.value
    pu_head, pu_table = build_stats(con, service, month, 'pu', cmap_pu, 'Pickups')
    do_head, do_table = build_stats(con, service, month, 'do', cmap_do, 'Drop-offs')
    with solara.Card():
        solara.Markdown(f"### {service} Top 10 Destinations ({month})", style={"text-align": "center"})
        with solara.Row():
            with solara.Column():
                solara.display(pu_head)
                solara.display(pu_table)
            with solara.Column():
                solara.display(do_head)
                solara.display(do_table)

@solara.component
def DemographicMap(column, baseline_val, title):
    m = build_demo_map(con, column, baseline_val, title)
    with solara.Card(title=title):
        solara.display(m)

# 5. Build the Main Page Layout
@solara.component
def Page():
    solara.Title("NYC Taxi Mobility Equity Dashboard")
    
    with solara.Column():
        with solara.Column(align="center"):
            # Header
            solara.Markdown("# NYC Taxi Mobility Equity Dashboard", style={"text-align": "center"})
            solara.Markdown(
                "<div style='text-align: center;'>This interactive dashboard analyzes whether <b>FHV</b> and <b>Yellow Taxi</b> services are over- or under-represented  in areas with different demographic compositions relative to the NYC baseline.</div>"
            )
            # UI Control
            solara.Select(label="Select Month", value=selected_month, values=['Jan2025', 'Feb2025', 'Mar2025'])
        
        # Grid Layout
        with solara.GridFixed(columns=2):
            # Row 1: Pickups
            TaxiMap('FHV', 'pu', 'Blues', 'FHV Pickups')
            TaxiMap('Yellow', 'pu', 'Blues', 'Yellow Pickups')
            
            # Row 2: Drop-offs
            TaxiMap('FHV', 'do', 'Greens', 'FHV Drop-offs')
            TaxiMap('Yellow', 'do', 'Greens', 'Yellow Drop-offs')
            
            # Row 3: Demographics
            DemographicMap('white_pct', baseline_white * 100, f'White Pop. Deviation ({baseline_white*100:.1f}%)')
            DemographicMap('black_pct', baseline_black * 100, f'Black Pop. Deviation ({baseline_black*100:.1f}%)')
    
    with solara.Column(align="center"):
        with solara.GridFixed(columns=2):
            ServiceStatsCard('FHV', 'Blues', 'YlOrBr')
            ServiceStatsCard('Yellow', 'Greens', 'YlOrBr')