import duckdb
import solara
from dashboard_helpers import (
    build_bivariate_map,
    build_bivariate_legend,
    build_summary_stats,
    build_need_chart,
    build_need_map,
)

# 1. Connect to database
con = duckdb.connect("processed_dashboard.db", read_only=True)

baseline_df       = con.sql("SELECT * FROM city_baselines").df()
baseline_ndvi     = float(baseline_df["baseline_ndvi"].iloc[0])
baseline_income   = float(baseline_df["baseline_income"].iloc[0])
baseline_minority = float(baseline_df["baseline_minority_pct"].iloc[0])
# 2. Reactive state

# X axis:
x_col = solara.reactive("ndvi_mean")

X_OPTIONS = {
    "ndvi_mean":          "NDVI (Vegetation)",
    "tree_equity_score":  "Tree Equity Score",
}

# Y axis:
y_col = solara.reactive("median_income")

Y_OPTIONS = {
    "median_income": "Median Income",
    "pct_minority":  "% Minority",
}

# 3. Components

@solara.component
def BivariateMapCard():
    x_label = X_OPTIONS[x_col.value]
    y_label = Y_OPTIONS[y_col.value]
    m       = build_bivariate_map(con, x_col.value, x_label, y_col.value, y_label)
    solara.Markdown(f"#### Bivariate Choropleth — {x_label} × {y_label}")
    solara.display(m)

@solara.component
def LegendCard():
    x_label = X_OPTIONS[x_col.value]
    y_label = Y_OPTIONS[y_col.value]
    fig     = build_bivariate_legend(x_label, y_label, y_col.value)
    with solara.Card(title="Legend"):
        solara.FigureMatplotlib(fig, dependencies=[x_col.value, y_col.value])

@solara.component
def SummaryStatsCard():
    x_label = X_OPTIONS[x_col.value]
    y_label = Y_OPTIONS[y_col.value]
    styled  = build_summary_stats(con, x_col.value, y_col.value)
    with solara.Card(title=f"Summary Stats — {x_label} × {y_label}"):
        solara.display(styled)

@solara.component
def NeedChartCard():
    fig = build_need_chart(con, y_col.value)
    with solara.Card(title="Top 15 Block Groups by Tree Program Need"):
        solara.FigureMatplotlib(fig, dependencies=[y_col.value])

@solara.component
def NeedMapCard():
    m = build_need_map(con, y_col.value)
    with solara.Card(title="Top 15 Block Groups — Location"):
        solara.display(m)

# 4. Page

@solara.component
def Page():
    solara.Title("Urban Vegetation Dashboard — Phoenix, AZ")

    with solara.Column():

        # Header
        with solara.Column(align="center"):
            solara.Markdown("# Understanding Urban Vegetation and Socioeconomic Demographics in Maricopa County",
                            style={"text-align": "center"})
            solara.Markdown(
                "<div style='text-align:center;color:#8b949e'>"
                "Exploring tree equity across Maricopa County block groups using NDVI, "
                "Tree Equity Scores, and Census demographics.<br>"
                "Data: Google Earth Engine · U.S. Census ACS 5-Year · American Forests"
                "</div>"
            )

        with solara.GridFixed(columns=2):
            with solara.Card(title="Maricopa County Baselines"):
                with solara.Row():
                    solara.Markdown(f"**Avg NDVI:** `{baseline_ndvi:.3f}`")
                    solara.Markdown(f"**Median income:** `${baseline_income:,.0f}`")
                    solara.Markdown(f"**% Minority:** `{baseline_minority:.1f}%`")

            with solara.Card(title="Map Controls"):
                with solara.Row():
                    with solara.Column():
                        solara.Select(
                            label="X axis",
                            value=x_col,
                            values=list(X_OPTIONS.keys()),
                        )
                    with solara.Column():
                        solara.Select(
                            label="Y axis",
                            value=y_col,
                            values=list(Y_OPTIONS.keys()),
                        )

        # Bivariate map (full width)
        BivariateMapCard()

        # Legend + stats side by side
        with solara.GridFixed(columns=2):
            LegendCard()
            SummaryStatsCard()

        # Need chart + map full width
        with solara.GridFixed(columns=2):
            NeedChartCard()
            NeedMapCard()