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

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

# Read 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

# Reactive state
selected_month = solara.reactive('Feb2025')


@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 DemographicMap(column, baseline_val, title):
    m = build_demo_map(con, column, baseline_val, title)
    with solara.Card(title=title):
        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():
        with solara.Row():
            with solara.Column():
                solara.Markdown(pu_head, style={'text-align': 'center'})
                solara.display(pu_table)
            with solara.Column():
                solara.Markdown(do_head, style={'text-align': 'center'})
                solara.display(do_table)


@solara.component
def Page():
    solara.Title('NYC Taxi Mobility Equity Dashboard')

    with solara.Column():
        with solara.Column(align='center'):
            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>'
            )
            solara.Select(
                label='Select Month',
                value=selected_month,
                values=['Jan2025', 'Feb2025', 'Mar2025']
            )

        # Trip maps grid
        with solara.GridFixed(columns=2):
            TaxiMap('FHV', 'pu', 'Blues', 'FHV Pickups')
            TaxiMap('Yellow', 'pu', 'Blues', 'Yellow Pickups')
            TaxiMap('FHV', 'do', 'Greens', 'FHV Drop-offs')
            TaxiMap('Yellow', 'do', 'Greens', 'Yellow Drop-offs')

        # Demographic baseline maps
        with solara.GridFixed(columns=2):
            DemographicMap('white_pct', baseline_white * 100,
                           f'White Pop. Deviation from Baseline ({baseline_white*100:.1f}%)')
            DemographicMap('black_pct', baseline_black * 100,
                           f'Black Pop. Deviation from Baseline ({baseline_black*100:.1f}%)')

        # Stats tables
        with solara.Column(align='center'):
            with solara.GridFixed(columns=2):
                ServiceStatsCard('FHV', 'Blues', 'Greens')
                ServiceStatsCard('Yellow', 'YlOrBr', 'YlOrBr')