"""
This application generates time series charts using data from the
ISU Soil Moisture Network.
"""

import re
from datetime import datetime, timedelta, timezone
from zoneinfo import ZoneInfo

import matplotlib.colors as mpcolors
import matplotlib.dates as mdates
import numpy as np
import pandas as pd
from matplotlib.axes import Axes
from matplotlib.lines import Line2D
from matplotlib.patches import Patch
from metpy.calc import dewpoint_from_relative_humidity
from metpy.units import units
from pyiem import meteorology
from pyiem.database import get_sqlalchemy_conn, sql_helper
from pyiem.exceptions import NoDataFound
from pyiem.grid.nav import get_nav
from pyiem.iemre import get_hourly_ncname
from pyiem.plot import figure, figure_axes, get_cmap
from pyiem.util import (
    c2f,
    convert_value,
    ncopen,
    utc,
)

CENTRAL = ZoneInfo("America/Chicago")
PLOTTYPES = {
    "1": "3 Panel Plot",
    "m": "Meteogram (Temperature, Dew Point, Wind)",
    "8": "Battery Voltage",
    "3": "Daily Max/Min 4 Inch Soil Temps",
    "9": "Daily Rainfall, 4 inch Soil Temp, and RH",
    "7": "Daily Soil Water + Change",
    "4": "Daily Solar Radiation",
    "5": "Daily Potential Evapotranspiration",
    "encrh": "Enclosure Relative Humidity",
    "6": "Histogram of Volumetric Soil Moisture",
    "10": "Inversion Diagnostic Plot (BOOI4, CAMI4, CRFI4)",
    "11": "Inversion Daily Timing (BOOI4, CAMI4, CRFI4)",
    "2": "Just Soil Temps",
    "4iemre": "Four inch soil temperature QC diagnostic",
    "sm": "Just Soil Moisture",
    "at": "One Minute Timeseries",
}


def get_description():
    """Return a dict describing how to call this plotter"""
    desc = {"description": __doc__, "data": True}
    desc["frontend"] = "/agclimate/smts.php"
    ets = datetime.now().replace(minute=0)
    sts = ets - timedelta(days=7)
    desc["arguments"] = [
        dict(
            type="networkselect",
            name="station",
            default="BOOI4",
            label="Select Station:",
            network="ISUSM",
        ),
        dict(
            type="select",
            name="opt",
            default="1",
            options=PLOTTYPES,
            label="Select Plot Type:",
        ),
        dict(
            type="datetime",
            name="sts",
            default=sts.strftime("%Y/%m/%d %H%M"),
            label="Start Time (Central Time):",
            min="2012/01/01 0000",
        ),
        dict(
            type="datetime",
            name="ets",
            default=ets.strftime("%Y/%m/%d %H%M"),
            label="End Time (Central Time):",
            min="2012/01/01 0000",
        ),
    ]
    return desc


def make_valid_subtitle(df: pd.DataFrame):
    """Make something nice for the plot subtitles."""
    dt1 = df.index[0].to_pydatetime().astimezone(CENTRAL)
    dt2 = df.index[-1].to_pydatetime().astimezone(CENTRAL)
    return (
        f"Data between {dt1:%-d %b %Y %-I:%M %p} and "
        f"{dt2:%-d %b %Y %-I:%M %p} Central Time"
    )


def make_inversion_timing(ctx):
    """Generate an inversion plot"""
    # Rectify the start date to midnight
    ctx["sts"] = ctx["sts"].replace(hour=0, minute=0)
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            sql_helper("""
    SELECT valid at time zone 'UTC' as utc_valid, tair_10_c_avg_qc,
    tair_15_c_avg_qc from sm_inversion where station = :station and
    valid >= :sts and valid < :ets and tair_10_c_avg_qc is not null and
    tair_15_c_avg_qc is not null ORDER by valid ASC
            """),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"],
                "ets": ctx["ets"],
            },
        )
    if df.empty:
        raise NoDataFound("No inversion data found for station!")
    df = df.assign(
        utc_valid=lambda df_: df_.utc_valid.dt.tz_localize(timezone.utc),
        valid=lambda df_: df_.utc_valid.dt.tz_convert(CENTRAL),
        delta=lambda df_: (
            c2f(df_.tair_10_c_avg_qc) - c2f(df_.tair_15_c_avg_qc)
        ),
    )

    grid = np.ones(((ctx["ets"] - ctx["sts"]).days + 1, 1440)) * np.nan
    df["minute"] = df["valid"].dt.hour * 60 + df["valid"].dt.minute
    # F
    df["day"] = (df["valid"] - df["valid"].iloc[0]).dt.days
    for _, row in df.iterrows():
        grid[row["day"], row["minute"]] = float(row["delta"])
    title = f"ISUSM Station: {ctx['_sname']} :: Inversion Timeseries"
    subtitle = "10 Foot Air Temperature minus 1.5 Foot Air Temperature (F)"
    fig, ax = figure_axes(apctx=ctx, title=title, subtitle=subtitle)
    clevs = np.arange(-2, 2.1, 0.2)
    cmap = get_cmap("bwr")
    cmap.set_bad("tan")
    norm = mpcolors.BoundaryNorm(clevs, cmap.N)
    res = ax.imshow(
        grid, aspect="auto", interpolation="none", cmap=cmap, norm=norm
    )
    fig.colorbar(res).set_label(
        "<< Less Likely           Inversion            More Likely >>"
    )
    ax.set_xticks(np.arange(0, 1441, 120))
    # hours of the day
    ax.set_xticklabels(
        (
            "Mid|2 AM|4 AM|6 AM|8 AM|10 AM|Noon|2 PM|4 PM|6 PM|8 PM|10 PM|Mid"
        ).split("|")
    )
    ax.grid(True)
    ax.set_xlabel(f"{ctx['sts'].year} Local Time (US Central)")

    def custom(x, _pos=None):
        dt = ctx["sts"] + timedelta(days=x)
        return dt.strftime("%-d %b")

    ax.yaxis.set_major_formatter(custom)

    return fig, df


def make_inversion_plot(ctx):
    """Generate an inversion plot"""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            sql_helper("""
        SELECT * from sm_inversion where station = :station and
        valid >= :sts and valid < :ets ORDER by valid ASC"""),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"],
                "ets": ctx["ets"],
            },
        )
    if df.empty:
        raise NoDataFound("No inversion data found for station!")

    # stride the result to <=1000 rows
    stride = max(1, int(len(df.index) / 1000.0))
    df = df.iloc[::stride]

    axwidth = 0.88
    axheight = 0.25
    fig = figure(apctx=ctx)
    axes = fig.subplots(3, 1, sharex=True)
    ax = axes[0]
    ax.set_position([0.07, 0.7, axwidth, axheight])
    ax.plot(df["valid"], c2f(df["tair_15_c_avg_qc"].values), label="1.5 feet")
    ax.plot(df["valid"], c2f(df["tair_5_c_avg_qc"].values), label="5 feet")
    ax.plot(df["valid"], c2f(df["tair_10_c_avg_qc"].values), label="10 feet")
    ax.grid(True)
    ax.set_ylabel("Air Temperature °F")
    ax.set_title(f"ISUSM Station: {ctx['_sname']} :: Inversion Timeseries")
    apply_xaxis_formatting(ax, ctx)
    ax.legend(loc="best", ncol=3, fontsize=10)

    ax = axes[1]
    ax.set_position([0.07, 0.4, axwidth, axheight])
    ax.plot(
        df["valid"],
        (
            c2f(df["tair_10_c_avg_qc"].values)
            - c2f(df["tair_15_c_avg_qc"].values)
        ),
    )
    ax.grid(True)
    ax.set_title("10 Foot Temperature minus 1.5 Foot Temperature")
    ax.set_ylabel("Air Temperature Diff °F")
    apply_xaxis_formatting(ax, ctx)

    ax = axes[2]
    ax.set_position([0.07, 0.1, axwidth, axheight])
    ax.bar(
        df["valid"],
        convert_value(
            df["ws_ms_max_qc"].values, "meter / second", "mile / hour"
        ),
        zorder=2,
        color="red",
        width=stride / 1440.0,
        label="5 Second Gust",
    )
    ax.bar(
        df["valid"],
        convert_value(
            df["ws_ms_avg_qc"].values, "meter / second", "mile / hour"
        ),
        zorder=3,
        color="lightblue",
        label="1 Minute Speed",
        width=stride / 1440.0,
    )
    ax.set_title("10 Foot Wind Speed")
    ax.grid(True)
    ax.set_ylabel("Wind Speed/Gust [MPH]")
    ax.legend(ncol=2)

    return fig, df


def make_daily_pet_plot(ctx):
    """Generate a daily PET plot"""
    with get_sqlalchemy_conn("isuag") as conn:
        res = conn.execute(
            sql_helper("""WITH climo as (
            select to_char(valid, 'mmdd') as mmdd, avg(c70) as  et
            from daily where station = 'A130209' GROUP by mmdd
        ), obs as (
            SELECT valid, dailyet_qc / 25.4 as et,
            to_char(valid, 'mmdd') as mmdd
            from sm_daily WHERE station = :station and valid >= :sts
            and valid <= :ets
        )

        select o.valid, o.et, c.et from obs o
        JOIN climo c on (c.mmdd = o.mmdd) ORDER by o.valid ASC
        """),
            {
                "station": ctx["station"],
                "sts": ctx["sts"].date(),
                "ets": ctx["ets"].date(),
            },
        )
        dates = []
        o_dailyet = []
        c_et = []
        for row in res:
            dates.append(row[0])
            o_dailyet.append(row[1] if row[1] is not None else 0)
            c_et.append(row[2])

    df = pd.DataFrame(dict(dates=dates, dailyet=o_dailyet, climo_dailyet=c_et))
    if df.empty:
        raise NoDataFound("No Data Found!")

    title = (
        f"ISUSM Station: {ctx['_sname']} Timeseries\n"
        "Potential Evapotranspiration, Climatology from Ames 1986-2014"
    )
    (fig, ax) = figure_axes(apctx=ctx, title=title)
    ax.bar(
        dates,
        o_dailyet,
        fc="brown",
        ec="brown",
        zorder=1,
        align="center",
        label="Observed",
    )
    ax.plot(dates, c_et, label="Climatology", color="k", lw=1.5, zorder=2)
    ax.grid(True)
    ax.set_ylabel("Potential Evapotranspiration [inch]")
    ax.xaxis.set_major_formatter(mdates.DateFormatter("%-d %b\n%Y"))
    interval = int(len(dates) / 7.0 + 1)
    ax.xaxis.set_major_locator(mdates.DayLocator(interval=interval))
    ax.legend(loc="best", ncol=1, fontsize=10)
    return fig, df


def make_daily_rad_plot(ctx):
    """Generate a daily radiation plot"""
    # Get clear sky theory
    theory = meteorology.clearsky_shortwave_irradiance_year(
        ctx["_nt"].sts[ctx["station"]]["lat"],
        ctx["_nt"].sts[ctx["station"]]["elevation"],
    )
    maxes = pd.DataFrame({"tmax": theory, "jday": range(1, 366)})

    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            """
            SELECT valid, slrkj_tot_qc / 1000. as rad from sm_daily
            where station = %s and valid >= %s and valid <= %s
            ORDER by valid ASC
            """,
            conn,
            params=(
                ctx["station"],
                ctx["sts"].strftime("%Y-%m-%d"),
                ctx["ets"].strftime("%Y-%m-%d"),
            ),
            parse_dates="valid",
            index_col="valid",
        )
    if df.empty:
        raise NoDataFound("No Data Found, sorry")
    df = (
        df.reindex(pd.date_range(df.index[0], df.index[-1], freq="1D"))
        .reset_index()
        .rename(columns={"index": "valid"})
    )
    df["jday"] = df["valid"].dt.strftime("%j").astype(int)
    df = df.join(maxes, on="jday", lsuffix="_")

    title = (
        f"ISUSM Station: {ctx['_sname']} Timeseries\n Daily Solar Radiation"
    )
    (fig, ax) = figure_axes(apctx=ctx, title=title)
    ax.bar(
        df["valid"].values,
        df["rad"].values,
        fc="tan",
        ec="brown",
        zorder=2,
        align="center",
        label="Observed",
    )
    ax.plot(
        df["valid"].values,
        df["tmax"].values,
        label=r"Modelled Max $\tau$ =0.75",
        color="k",
        lw=1.5,
    )
    ax.grid(True)
    ax.xaxis.set_major_formatter(mdates.DateFormatter("%-d %b\n%Y"))
    interval = int(len(df.index) / 7.0 + 1)
    ax.xaxis.set_major_locator(mdates.DayLocator(interval=interval))
    ax.set_ylabel("Solar Radiation $MJ m^{-2}$")
    ax.set_ylim(0, 38)
    ax.legend(loc=1, ncol=2, fontsize=10)
    return fig, df


def make_daily_rainfall_soil_rh(ctx):
    """Give them what they want."""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            sql_helper("""
            SELECT valid, rain_in_tot_qc, t4_c_avg_qc, rh_avg_qc
            from sm_daily where station = :station and valid >= :sts
            and valid <= :ets and t4_c_avg_qc is not null
            ORDER by valid ASC
            """),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"].strftime("%Y-%m-%d"),
                "ets": ctx["ets"].strftime("%Y-%m-%d"),
            },
            index_col="valid",
        )
    if df.empty:
        raise NoDataFound("No Data Found for Query")

    title = f"ISUSM Station: {ctx['_sname']} Timeseries"
    subtitle = "Daily Precipitation, 4 Inch Soil Temperature, and Avg RH"

    fig = figure(title=title, subtitle=subtitle, apctx=ctx)

    def common(ax):
        """do common things."""
        ax.grid(True)
        ax.xaxis.set_major_formatter(mdates.DateFormatter("%-d %b\n%Y"))
        interval = int(len(df.index) / 7.0 + 1)
        ax.xaxis.set_major_locator(mdates.DayLocator(interval=interval))

    ax = fig.add_axes((0.1, 0.7, 0.8, 0.2))
    ax.bar(
        df.index.values,
        df["rain_in_tot_qc"].values,
        color="blue",
        align="center",
    )
    ax.set_ylim(bottom=0)
    ax.set_ylabel("Precipitation [inch]")
    common(ax)

    ax = fig.add_axes((0.1, 0.4, 0.8, 0.2))
    vals = c2f(df["t4_c_avg_qc"].values)
    ax.bar(
        df.index.values,
        vals,
        color="brown",
        align="center",
    )
    ax.set_ylim(np.min(vals) - 5, np.max(vals) + 5)
    ax.set_ylabel("4 Inch Soil Temp [°F]")
    common(ax)

    ax = fig.add_axes((0.1, 0.1, 0.8, 0.2))
    if not pd.isna(df["rh_avg_qc"]).all():
        ax.bar(
            df.index.values,
            df["rh_avg_qc"].values,
            color="blue",
            align="center",
        )
        ax.set_ylim(0, 100)
        ax.set_ylabel("Average\nRelative Humidity [%]")
    common(ax)

    return fig, df


def make_daily_plot(ctx):
    """Generate a daily plot of max/min 4 inch soil temps"""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            """
            SELECT date(valid), min(t4_c_avg_qc),
            max(t4_c_avg_qc), avg(t4_c_avg_qc) from sm_hourly
            where station = %s and valid >= %s and valid < %s
            and t4_c_avg is not null GROUP by date ORDER by date ASC
        """,
            conn,
            params=(
                ctx["station"],
                ctx["sts"].strftime("%Y-%m-%d 00:00"),
                ctx["ets"].strftime("%Y-%m-%d 23:59"),
            ),
            index_col="date",
        )
    if df.empty:
        raise NoDataFound("No Data Found for Query")

    mins = c2f(df["min"].values)
    maxs = c2f(df["max"].values)
    avgs = c2f(df["avg"].values)
    title = (
        f"ISUSM Station: {ctx['_sname']} Timeseries\n"
        "Daily Max/Min/Avg 4 inch Soil Temperatures"
    )
    (fig, ax) = figure_axes(apctx=ctx, title=title)
    ax.bar(
        df.index.values,
        maxs - mins,
        bottom=mins,
        fc="tan",
        ec="brown",
        zorder=2,
        align="center",
        label="Max/Min",
    )
    ax.scatter(
        df.index.values,
        avgs,
        marker="*",
        s=30,
        zorder=3,
        color="brown",
        label="Hourly Avg",
    )
    ax.axhline(50, lw=1.5, c="k")
    ax.grid(True)
    ax.set_ylabel("4 inch Soil Temperature [°F]")
    apply_xaxis_formatting(ax, ctx)
    ax.legend(loc="best", ncol=2, fontsize=10)
    return fig, df


def make_hourly_plot(ctx, vname):
    """Generate a plot of battery"""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            sql_helper(
                """SELECT valid, {vname} from sm_hourly
    where station = :station and valid >= :sts and valid < :ets
    and {vname} is not null ORDER by valid ASC
            """,
                vname=vname,
            ),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"],
                "ets": ctx["ets"],
            },
        )
    title = "Battery Voltage"
    label = "Battery Voltage [V]"
    if vname == "encrh_avg":
        title = "Enclosure Relative Humidity"
        label = "Relative Humidity [%]"
    title = f"ISUSM Station: {ctx['_sname']} Timeseries\n{title}"
    (fig, ax) = figure_axes(apctx=ctx, title=title)
    ax.plot(df["valid"], df[vname])
    ax.grid(True)
    ax.set_ylabel(label)
    apply_xaxis_formatting(ax, ctx)
    return fig, df


def make_vsm_histogram_plot(ctx):
    """Option 6"""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            """
            SELECT
            CASE WHEN t12_c_avg_qc > 1 then vwc12_qc else null end
                as v12,
            CASE WHEN t24_c_avg_qc > 1 then vwc24_qc else null end
                as v24,
            CASE WHEN t50_c_avg_qc > 1 then vwc50_qc else null end
                as v50
            from sm_hourly
            where station = %s and valid >= %s and valid < %s
        """,
            conn,
            params=(ctx["station"], ctx["sts"], ctx["ets"]),
            index_col=None,
        )
    title = (
        f"ISUSM Station: {ctx['_sname']} VWC Histogram\n"
        f"For un-frozen condition between {ctx['sts']:%-d %b %Y} "
        f"and {ctx['ets']:%-d %b %Y}"
    )
    fig = figure(apctx=ctx, title=title)
    ax = fig.subplots(3, 1, sharex=True)
    for i, col in enumerate(["v12", "v24", "v50"]):
        ax[i].hist(df[col] * 100.0, bins=50, range=(0, 50), density=True)
        ax[i].set_ylabel("Frequency")
        ax[i].grid(True)
        ax[i].text(
            0.99,
            0.99,
            f"{col[1:]} inches",
            transform=ax[i].transAxes,
            ha="right",
            va="top",
        )
        ax[i].set_yscale("log")

    ax[2].set_xlabel("Volumetric Water Content [%]")
    return fig, df


def make_daily_water_change_plot(ctx):
    """Option 7"""
    # Get daily precip
    with get_sqlalchemy_conn("isuag") as conn:
        pdf = pd.read_sql(
            sql_helper("""
                SELECT valid, rain_in_tot_qc from sm_daily
                where station = :station
                and valid >= :sts and valid <= :ets ORDER by valid ASC
                """),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"].date(),
                "ets": ctx["ets"].date(),
            },
            index_col="valid",
            parse_dates="valid",
        )

        df = pd.read_sql(
            sql_helper("""
        WITH obs as (
            SELECT valid,
            CASE WHEN coalesce(t12_c_avg_qc, sv_t12_qc, sv_t14_qc) > 1 then
                coalesce(vwc12_qc, sv_vwc12_qc, sv_vwc14_qc) else null end
            as v12,
            CASE WHEN coalesce(t24_c_avg_qc, sv_t24_qc) > 1 then
                coalesce(vwc24_qc, sv_vwc24_qc) else null end
            as v24,
            CASE WHEN coalesce(t50_c_avg_qc, sv_t52_qc) > 1 then
                coalesce(vwc50_qc, sv_vwc52_qc) else null end
            as v50
            from sm_daily
            where station = :station and valid >= :sts and valid < :ets)

        SELECT valid,
        v12, v12 - lag(v12) OVER (ORDER by valid ASC) as v12_delta,
        v24, v24 - lag(v24) OVER (ORDER by valid ASC) as v24_delta,
        v50, v50 - lag(v50) OVER (ORDER by valid ASC) as v50_delta
        from obs ORDER by valid ASC
        """),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"],
                "ets": ctx["ets"],
            },
            index_col=None,
            parse_dates="valid",
        )
    if pdf.empty or df.empty:
        raise NoDataFound("No Data Found for Query")
    # 12inch covers 6-18 inches, 24inch covers 18-30 inches, 50inch excluded
    l1 = 12.0
    l2 = 12.0
    df["change"] = df["v12_delta"] * l1 + df["v24_delta"] * l2
    # Compute an estimate of available water capacity
    # thresholds arbitrarily chosen at 10% and 45%
    for lvl in ["v12", "v12"]:
        df[lvl] = df[lvl].clip(lower=0.1, upper=0.45)
    df["depth"] = df["v12"] * l1 + df["v24"] * l2 - (l1 + l2) * 0.1

    title = (
        f'ISUSM Station: {ctx["_sname"]} :: Daily Soil (6-30") Water\n'
        f"For un-frozen condition between {ctx['sts']:%-d %b %Y} and "
        f"{ctx['ets']:%-d %b %Y}"
    )
    fig = figure(apctx=ctx, title=title)
    ax = fig.subplots(2, 1, sharex=True)
    if not df["depth"].isnull().all():
        df2 = df[df["depth"] > 0]
        ax[0].bar(
            df2["valid"].values,
            df2["depth"].to_numpy(),
            color="b",
            width=1.1,
        )
    oneday = timedelta(days=1)
    ax[0].grid(True)
    ax[0].set_ylabel("Plant Available Soil Water [inch]")
    ax[0].set_ylim(bottom=0)

    # Second plot
    bars = ax[1].bar(df["valid"].values, df["change"].values, color="b")
    for mybar in bars:
        mybar.set_color("r" if mybar.get_y() < 0 else "b")
    ax[1].set_ylabel("Soil Water Change [inch]")
    ax[1].xaxis.set_major_formatter(mdates.DateFormatter("%-d %b\n%Y"))
    interval = int(len(df.index) / 7.0 + 1)
    ax[1].xaxis.set_major_locator(mdates.DayLocator(interval=interval))
    ax[1].grid(True)

    if (ctx["ets"] - ctx["sts"]).total_seconds() < (60 * 86400):
        ylim = ax[1].get_ylim()[1]
        # Attempt to place precip text above this plot
        pdf["pday"] = pdf["rain_in_tot_qc"].values
        for valid, row in pdf.iterrows():
            if row["pday"] > 0:
                ax[1].text(
                    valid,
                    ylim,
                    f"{row['pday']:.2f}",
                    rotation=90,
                    va="bottom",
                    color="b",
                )
        ax[1].text(
            -0.01,
            1.05,
            "Rain ->",
            ha="right",
            transform=ax[1].transAxes,
            color="b",
        )
    ax[0].set_xlim(df["valid"].min() - oneday, df["valid"].max() + oneday)
    return fig, df


def plot_sm(ctx: dict):
    """Just soil moisture."""
    with get_sqlalchemy_conn("isuag") as conn:
        # Find all columns in the sm_hourly table that have vwc in them
        # and end with _qc
        res = conn.execute(
            sql_helper("""
            SELECT column_name from information_schema.columns
            WHERE table_name = 'sm_hourly' and column_name ~* 'vwc'
            and column_name ~* '_qc' ORDER by column_name ASC
            """)
        )
        cols = [row[0] for row in res]

        df = pd.read_sql(
            sql_helper(
                """
    SELECT valid, {cols}
    from sm_hourly WHERE station = :station and
    valid BETWEEN :sts and :ets ORDER by valid ASC""",
                cols=", ".join(cols),
            ),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"],
                "ets": ctx["ets"],
            },
            index_col="valid",
        )
    if df.empty:
        raise NoDataFound("No Data Found for Query")
    valid = df.index.values

    (fig, ax) = figure_axes(
        apctx=ctx,
        title=f"{ctx['_sname']} Soil Moisture Time Series",
        subtitle=make_valid_subtitle(df),
    )

    # Adjust main axis and make lhs and rhs sliver plots for a first/last plot
    ax.set_position((0.23, 0.1, 0.6, 0.75))
    lhs = fig.add_axes((0.05, 0.1, 0.1, 0.7))
    lhs.set_ylabel("Depth [inch]")
    lhs.set_xlabel("VWC [m³/m³]")
    lhs.grid(True)
    rhs = fig.add_axes((0.88, 0.1, 0.1, 0.7))
    rhs.set_ylabel("Depth [inch]")
    rhs.set_xlabel("VWC [m³/m³]")
    rhs.grid(True)

    ax.grid(True)
    depth_re = re.compile(r"(\d+)")
    plotted = False

    first_row = df.iloc[0]
    lhs.set_title("First\nObservation", fontsize=9)
    last_row = df.iloc[-1]
    rhs.set_title("Last\nObservation", fontsize=9)
    maxdepth = 0
    # Sort by depth so the legend plots in order
    cols = sorted(cols, key=lambda x: int(depth_re.findall(x)[0]))
    for col in cols:
        if df[col].isnull().all():
            continue
        plotted = True
        depth = depth_re.findall(col)[0]
        maxdepth = max(int(depth), maxdepth)
        lp = ax.plot(
            valid, df[col].to_numpy(), linewidth=2, label=f"{depth}in"
        )
        lhs.barh(
            int(depth),
            first_row[col],
            color=lp[0].get_color(),
            height=1,
        )
        lhs.annotate(
            f"{first_row[col]:.2f}",
            xy=(first_row[col], int(depth)),
            xytext=(5, 0),
            textcoords="offset points",
            ha="left",
            va="center",
            fontsize=8,
            bbox=dict(boxstyle="round,pad=0.2", fc="white"),
        )
        rhs.barh(
            int(depth),
            last_row[col],
            color=lp[0].get_color(),
            height=1,
        )
        rhs.annotate(
            f"{last_row[col]:.2f}",
            xy=(last_row[col], int(depth)),
            xytext=(5, 0),
            textcoords="offset points",
            ha="left",
            va="center",
            fontsize=8,
            bbox=dict(boxstyle="round,pad=0.2", fc="white"),
        )
    if not plotted:
        raise NoDataFound("No Soil Moisture Data for this station")

    xmax = max(lhs.get_xlim()[1], rhs.get_xlim()[1]) * 1.15

    lhs.set_ylim(maxdepth + 2, 0)
    lhs.set_xlim(0, xmax)
    rhs.set_ylim(maxdepth + 2, 0)
    rhs.set_xlim(0, xmax)

    box = ax.get_position()
    ax.set_position([box.x0, box.y0, box.width, box.height * 0.94])
    ax.legend(
        bbox_to_anchor=(0.5, 1.0),
        ncol=7,
        loc="lower center",
        fontsize=9,
    )
    days = (ctx["ets"] - ctx["sts"]).days
    if days >= 3:
        interval = max(int(days / 7), 1)
        ax.xaxis.set_major_locator(
            mdates.DayLocator(interval=interval, tz=CENTRAL)
        )
        ax.xaxis.set_major_formatter(
            mdates.DateFormatter("%-d %b\n%Y", tz=CENTRAL)
        )
    else:
        ax.xaxis.set_major_locator(
            mdates.AutoDateLocator(maxticks=10, tz=CENTRAL)
        )
        ax.xaxis.set_major_formatter(
            mdates.DateFormatter("%-I %p\n%d %b", tz=CENTRAL)
        )
    ax.set_ylabel(r"Volumetric Soil Moisture $m^3/m^3$")
    return fig, df


def plot4iemre(ctx: dict, lon: float, lat: float):
    """Four inch soil temperature diagnostic"""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            sql_helper(
                "SELECT valid, t4_c_avg, t4_c_avg_qc from sm_hourly "
                "WHERE station = :station and "
                "valid BETWEEN :sts and :ets ORDER by valid ASC"
            ),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"],
                "ets": ctx["ets"],
            },
            index_col="valid",
        )
    if df.empty:
        raise NoDataFound("No Data Found for This Plot.")

    i, j = get_nav("IEMRE", "conus").find_ij(lon, lat)
    with ncopen(get_hourly_ncname(ctx["sts"].year), timeout=300) as nc:
        times = nc.variables["time"][:]
        time0 = utc(ctx["sts"].year, 1, 1)
        vtimes = [time0 + timedelta(hours=int(t)) for t in times]
        t4 = convert_value(nc.variables["soil4t"][:, j, i], "degK", "degF")

    title = f"ISUSM Station: {ctx['_sname']} :: Four inch soil temp diagnostic"
    (fig, ax) = figure_axes(apctx=ctx, title=title)

    ax.plot(vtimes, t4, label="IEMRE Hourly")
    ax.plot(
        df.index.to_pydatetime(),
        c2f(df["t4_c_avg_qc"].values),
        label="ISUSM QC",
    )
    ax.plot(
        df.index.to_pydatetime(),
        c2f(df["t4_c_avg"].values),
        label="ISUSM Obs",
    )

    apply_xaxis_formatting(ax, ctx)
    if ax.get_ylim()[0] < 40:
        ax.axhline(32, linestyle="--", lw=2, color="tan")
    ax.set_ylabel("Temperature [°F]")
    ax.set_xlim(ctx["sts"], ctx["ets"])
    ax.legend(ncol=3)
    ax.grid(True)

    return fig, df


def plot2(ctx):
    """Just soil temps"""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            "SELECT * from sm_hourly WHERE station = %s and "
            "valid BETWEEN %s and %s ORDER by valid ASC",
            conn,
            params=(ctx["station"], ctx["sts"], ctx["ets"]),
            index_col="valid",
        )
    d12t = df["t12_c_avg_qc"]
    d24t = df["t24_c_avg_qc"]
    d50t = df["t50_c_avg_qc"]
    d04t = df["t4_c_avg_qc"]
    valid = df.index.values

    title = f"ISUSM Station: {ctx['_sname']} :: Soil Temperature Timeseries"
    (fig, ax) = figure_axes(apctx=ctx, title=title)
    ax.grid(True)
    svplotted = False
    for depth in [2, 4, 8, 12, 14, 16, 20, 24, 28, 30, 32, 36, 40, 42, 52]:
        series = df[f"sv_t{depth}_qc"]
        if series.isnull().all():
            continue
        ax.plot(valid, c2f(series), linewidth=2, label=f"{depth}in")
        svplotted = True

    if not svplotted and not d04t.isnull().all():
        ax.plot(valid, c2f(d04t), linewidth=2, color="brown", label="4 inch")
    if not svplotted and not d12t.isnull().all():
        ax.plot(valid, c2f(d12t), linewidth=2, color="r", label="12 inch")
    if not svplotted and not d24t.isnull().all():
        ax.plot(valid, c2f(d24t), linewidth=2, color="purple", label="24 inch")
    if not svplotted and not d50t.isnull().all():
        ax.plot(valid, c2f(d50t), linewidth=2, color="black", label="50 inch")
    box = ax.get_position()
    ax.set_position([box.x0, box.y0, box.width, box.height * 0.94])
    ax.legend(
        bbox_to_anchor=(0.5, 1.0),
        ncol=10,
        loc="lower center",
        fontsize=9 if svplotted else 12,
    )
    apply_xaxis_formatting(ax, ctx)
    if ax.get_ylim()[0] < 40:
        ax.axhline(32, linestyle="--", lw=2, color="tan")
    ax.set_ylabel("Temperature [°F]")
    return fig, df


def plot_at(ctx: dict):
    """One minute temperatures."""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            sql_helper("""
            SELECT valid, tair_c_avg_qc, rh_avg_qc, slrkj_tot_qc,
            ws_mph_qc, ws_mph_max_qc, rain_in_tot_qc from sm_minute WHERE
            station = :station and valid BETWEEN :sts and :ets
            ORDER by valid ASC
            """),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"],
                "ets": ctx["ets"],
            },
            index_col="valid",
        )
    if df.empty:
        raise NoDataFound("No Data Found for This Plot.")
    # Fill out the missing minutes with NaN so the plot looks better
    df = (
        df.reindex(pd.date_range(df.index[0], df.index[-1], freq="1min"))
        .reset_index()
        .rename(columns={"index": "valid"})
        .set_index("valid")
    )

    fig = figure(
        apctx=ctx,
        title=f"{ctx['_sname']}:: One Minute Interval Plot",
        subtitle=make_valid_subtitle(df),
    )
    axes: list[Axes] = fig.subplots(3, 1, sharex=True)
    [ax1, ax2, ax3] = axes
    ax1.plot(df.index, c2f(df["tair_c_avg_qc"]), color="r", label="Air Temp")
    ax1.set_ylabel("Temperature [°F]")
    dwpf = dewpoint_from_relative_humidity(
        units("degC") * df["tair_c_avg_qc"].values,
        units("percent") * df["rh_avg_qc"].values,
    )
    ax1.plot(df.index, dwpf.to(units("degF")), color="g", label="Dew Point")

    ax1.grid(True)
    if ax1.get_ylim()[0] < 40:
        ax1.axhline(32, linestyle="--", lw=2, color="tan")
    ax1.legend(loc="best")

    ax1_2 = ax1.twinx()
    if not pd.isna(df["slrkj_tot_qc"]).all():
        ax1_2.plot(
            df.index, df["slrkj_tot_qc"].values / 60.0 * 1000, color="k"
        )
        ax1_2.set_ylabel("Solar Radiation [$W m^{-2}$]")
    apply_xaxis_formatting(ax1, ctx)

    if df["ws_mph_max_qc"].max() > 0.1:
        ax2.bar(
            df.index,
            df["ws_mph_max_qc"],
            width=1 / 1440.0,
            zorder=3,
            color="r",
            label="Gust",
        )
        ax2.bar(
            df.index,
            df["ws_mph_qc"],
            width=1 / 1440.0,
            zorder=4,
            color="b",
            label="Avg",
        )
        ax2.grid(True)
        ax2.set_ylabel("Wind Speed [MPH]")
        ax2.legend(loc="best", ncol=2)

    # Precipitation
    ax3.bar(
        df.index,
        df["rain_in_tot_qc"],
        width=1 / 1440.0,
        zorder=3,
        color="b",
    )
    ax3.grid(True)
    ax3.set_ylim(0, max(0.1, df["rain_in_tot_qc"].max() + 0.02))
    ax3.set_ylabel("Rainfall [inch]")

    def fmt_r(value):
        """Prevent ugly NaN."""
        return f"{value:.2f}" if pd.notna(value) else "N/A"

    ax3.annotate(
        (
            f'Max Rainfall Rate: 1min {fmt_r(df["rain_in_tot_qc"].max())}", '
            f'15min {fmt_r(df["rain_in_tot_qc"].rolling(15).sum().max())}", '
            f'60min {fmt_r(df["rain_in_tot_qc"].rolling(60).sum().max())}", '
            f"Total Plotted: {fmt_r(df['rain_in_tot_qc'].sum())} inch"
        ),
        xy=(0.01, 1.01),
        xycoords="axes fraction",
        ha="left",
    )

    return fig, df


def plot_meteogram(ctx):
    """Do main plotting logic"""
    table = "sm_minute"
    barwidth = 1 / 1440.0
    if (ctx["ets"] - ctx["sts"]) > timedelta(days=5):
        table = "sm_hourly"
        barwidth = 1 / 24.0
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            sql_helper(
                """
            SELECT * from {table} WHERE
            station = :station and valid BETWEEN :sts and :ets
            ORDER by valid ASC""",
                table=table,
            ),
            conn,
            params={
                "station": ctx["station"],
                "sts": ctx["sts"],
                "ets": ctx["ets"],
            },
            index_col="valid",
        )
    if df.empty:
        raise NoDataFound("No Data Found for This Plot.")
    fig = figure(
        title=f"ISUSM Station: {ctx['_sname']} Timeseries",
        apctx=ctx,
    )
    axes = fig.subplots(2, 1, sharex=True)
    ax = axes[0]
    df2 = df[df["tair_c_avg_qc"] > -30]
    if not df2.empty:
        ax.plot(
            df2.index.values, c2f(df2["tair_c_avg_qc"]), color="r", label="Air"
        )
        dwpf = (
            dewpoint_from_relative_humidity(
                units("degC") * df2["tair_c_avg_qc"].values,
                units("percent") * df2["rh_avg_qc"].values,
            )
            .to(units("degF"))
            .m
        )
        ax.plot(df2.index.values, dwpf, color="g", label="Dew Point")
    ax.grid(True)
    ax.set_ylabel("Temperature [°F]")
    ax.legend(loc="best", ncol=2)

    # ----------------
    ax = axes[1]
    ax.bar(df.index.values, df["ws_mph_max_qc"], width=barwidth, color="r")
    ax.bar(df.index.values, df["ws_mph_qc"], width=barwidth, color="b")
    ax.grid(True)
    ax.set_ylabel("Wind Speed [MPH]")

    ax2 = ax.twinx()
    ax2.scatter(df.index.values, df["winddir_d1_wvt_qc"], color="g")
    ax2.set_ylabel("Wind Direction [deg]")
    ax2.set_ylim(-1, 361)
    ax2.set_yticks(range(0, 361, 45))
    ax2.set_yticklabels(["N", "NE", "E", "SE", "S", "SW", "W", "NW", "N"])

    handles = [
        Line2D([0], [0], color="r", lw=2, label="Max Gust"),
        Line2D([0], [0], color="b", lw=2, label="Avg Wind"),
        Line2D([0], [0], color="g", lw=2, label="Wind Dir"),
    ]
    ax.legend(
        handles=handles,
        loc=(0.5, 1.04),
        ncol=3,
        fontsize=10,
    )

    apply_xaxis_formatting(ax, ctx)
    return fig, df


def plot1(ctx):
    """Do main plotting logic"""
    with get_sqlalchemy_conn("isuag") as conn:
        df = pd.read_sql(
            "SELECT * from sm_hourly WHERE "
            "station = %s and valid BETWEEN %s and %s ORDER by valid ASC",
            conn,
            params=(ctx["station"], ctx["sts"], ctx["ets"]),
            index_col="valid",
        )
    if df.empty:
        raise NoDataFound("No Data Found for This Plot.")
    solar_wm2 = df["slrkj_tot_qc"] / 3600.0 * 1000.0
    d12sm = df["vwc12_qc"]
    d12t = df["t12_c_avg_qc"]
    d24t = df["t24_c_avg_qc"]
    d50t = df["t50_c_avg_qc"]
    d24sm = df["vwc24_qc"]
    d50sm = df["vwc50_qc"]
    rain = df["rain_in_tot_qc"]
    tair = df["tair_c_avg_qc"]
    d04t = df["t4_c_avg_qc"]
    valid = df.index

    fig = figure(
        title=f"ISUSM Station: {ctx['_sname']} Timeseries",
        apctx=ctx,
    )
    ax = fig.subplots(3, 1, sharex=True)
    ax[0].grid(True)
    ax2 = ax[0].twinx()
    ax[0].set_zorder(ax2.get_zorder() + 1)
    ax[0].patch.set_visible(False)
    # arange leads to funky values
    ax2.set_yticks([-0.6, -0.5, -0.4, -0.3, -0.2, -0.1, 0])
    ax2.set_yticklabels([0.6, 0.5, 0.4, 0.3, 0.2, 0.1, 0])
    ax2.set_ylim(-0.6, 0)
    ax2.set_ylabel("Hourly Precipitation [inch]")
    ax2.bar(valid, 0 - rain, width=0.04, fc="b", ec="b", zorder=4)

    if not d12sm.isnull().all():
        ax[0].plot(valid, d12sm * 100.0, linewidth=2, color="r", zorder=5)
    if not d24sm.isnull().all():
        ax[0].plot(valid, d24sm * 100.0, linewidth=2, color="purple", zorder=5)
    if not d50sm.isnull().all():
        ax[0].plot(valid, d50sm * 100.0, linewidth=2, color="black", zorder=5)
    ax[0].set_ylabel("Volumetric\nSoil Water Content [%]")

    box = ax[0].get_position()
    ax[0].set_position(
        [box.x0, box.y0 + box.height * 0.05, box.width, box.height * 0.95]
    )
    box = ax2.get_position()
    ax2.set_position(
        [box.x0, box.y0 + box.height * 0.05, box.width, box.height * 0.95]
    )
    handles = [
        Line2D([0], [0], color="r", lw=3, label="12 inch"),
        Line2D([0], [0], color="purple", lw=3, label="24 inch"),
        Line2D([0], [0], color="black", lw=3, label="50 inch"),
        Patch(facecolor="b", edgecolor="b", label="Hourly Precip"),
    ]
    ax[0].legend(
        handles=handles,
        bbox_to_anchor=(0.5, -0.15),
        ncol=4,
        loc="center",
        fontsize=12,
    )

    # ----------------------------------------
    if not d12t.isnull().all():
        ax[1].plot(valid, c2f(d12t), linewidth=2, color="r", label="12in")
    if not d24t.isnull().all():
        ax[1].plot(valid, c2f(d24t), linewidth=2, color="purple", label="24in")
    if not d50t.isnull().all():
        ax[1].plot(valid, c2f(d50t), linewidth=2, color="black", label="50in")
    ax[1].grid(True)
    ax[1].set_ylabel("Soil Temperature [°F]")
    box = ax[1].get_position()
    ax[1].set_position(
        [box.x0, box.y0 + box.height * 0.05, box.width, box.height * 0.95]
    )

    # ------------------------------------------------------

    ax2 = ax[2].twinx()
    (l3,) = ax2.plot(valid, solar_wm2, color="g", zorder=1, lw=2)
    ax2.set_ylabel("Solar Radiation [W/m^2]", color="g")

    handles = []
    labels = []
    if not tair.isnull().all():
        (l1,) = ax[2].plot(
            valid, c2f(tair), linewidth=2, color="blue", zorder=2
        )
        handles.append(l1)
        labels.append("Air")
    if not d04t.isnull().all():
        (l2,) = ax[2].plot(
            valid, c2f(d04t), linewidth=2, color="brown", zorder=2
        )
        handles.append(l2)
        labels.append('4" Soil')
    handles.append(l3)
    labels.append("Solar Radiation")
    ax[2].grid(True)
    ax[2].legend(
        handles,
        labels,
        bbox_to_anchor=(0.5, 1.1),
        loc="center",
        ncol=3,
    )
    ax[2].set_ylabel("Temperature [°F]")

    ax[2].set_zorder(ax2.get_zorder() + 1)
    ax[2].patch.set_visible(False)
    ax[2].set_xlim(df.index.min(), df.index.max())
    apply_xaxis_formatting(ax[2], ctx)

    return fig, df


def apply_xaxis_formatting(ax: Axes, ctx: dict) -> None:
    """Apply consistent x-axis formatting based on the time range."""
    days = (ctx["ets"] - ctx["sts"]).days
    if days >= 3:
        interval = max(int(days / 7), 1)
        ax.xaxis.set_major_locator(
            mdates.DayLocator(interval=interval, tz=CENTRAL)
        )
        ax.xaxis.set_major_formatter(
            mdates.DateFormatter("%-d %b\n%Y", tz=CENTRAL)
        )
    else:
        ax.xaxis.set_major_locator(
            mdates.AutoDateLocator(maxticks=10, tz=CENTRAL)
        )
        ax.xaxis.set_major_formatter(
            mdates.DateFormatter("%-I:%M %p\n%-d %b", tz=CENTRAL)
        )


def plotter(ctx: dict):
    """Go"""
    if ctx["opt"] == "1":
        fig, df = plot1(ctx)
    elif ctx["opt"] == "m":
        fig, df = plot_meteogram(ctx)
    elif ctx["opt"] == "2":
        fig, df = plot2(ctx)
    elif ctx["opt"] == "4iemre":
        lon = ctx["_nt"].sts[ctx["station"]]["lon"]
        lat = ctx["_nt"].sts[ctx["station"]]["lat"]
        fig, df = plot4iemre(ctx, lon, lat)
    elif ctx["opt"] == "at":
        fig, df = plot_at(ctx)
    elif ctx["opt"] == "sm":
        fig, df = plot_sm(ctx)
    elif ctx["opt"] == "3":
        fig, df = make_daily_plot(ctx)
    elif ctx["opt"] == "4":
        fig, df = make_daily_rad_plot(ctx)
    elif ctx["opt"] == "5":
        fig, df = make_daily_pet_plot(ctx)
    elif ctx["opt"] == "6":
        fig, df = make_vsm_histogram_plot(ctx)
    elif ctx["opt"] == "7":
        fig, df = make_daily_water_change_plot(ctx)
    elif ctx["opt"] == "8":
        fig, df = make_hourly_plot(ctx, "battv_min_qc")
    elif ctx["opt"] == "encrh":
        fig, df = make_hourly_plot(ctx, "encrh_avg")
    elif ctx["opt"] == "9":
        fig, df = make_daily_rainfall_soil_rh(ctx)
    elif ctx["opt"] == "10":
        fig, df = make_inversion_plot(ctx)
    else:  # 11
        fig, df = make_inversion_timing(ctx)

    # removal of timestamps, sigh
    df = df.reset_index()
    for col in [
        "valid",
        "ws_mph_tmx",
        "valid",
        "ws_mph_tmx_qc",
        "tair_c_tmn",
        "tair_c_tmx",
        "tair_c_tmx_qc",
        "tair_c_tmn_qc",
    ]:
        if col in df.columns:
            df[col] = df[col].apply(
                lambda x: (
                    x
                    if isinstance(x, str) or pd.isnull(x)
                    else x.strftime("%Y-%m-%d %H:%M")
                )
            )
    return fig, df
