""".. title:: WMO BUFR Surface Data

Documentation for /cgi-bin/request/wmo_bufr_srf.py
--------------------------------------------------

This application provides access to the IEM processed archives of stations
reporting via the WMO BUFR Surface.

Example Requests
----------------

Return data for WIGOS station 0-756-1-456700 for 17 Sep 2025

https://mesonet.agron.iastate.edu/cgi-bin/request/wmo_bufr_srf.py?\
stations=0-756-1-456700&sts=2025-09-17T00:00Z&ets=2025-09-17T23:59Z&format=csv

Same request, but Excel

https://mesonet.agron.iastate.edu/cgi-bin/request/wmo_bufr_srf.py?\
stations=0-756-1-456700&sts=2025-09-17T00:00Z&ets=2025-09-17T23:59Z&\
format=excel

Same request, but JSON

https://mesonet.agron.iastate.edu/cgi-bin/request/wmo_bufr_srf.py?\
stations=0-756-1-456700&sts=2025-09-17T00:00Z&ets=2025-09-17T23:59Z&format=json

"""

from io import BytesIO, StringIO
from typing import Annotated

import pandas as pd
from pydantic import AwareDatetime, Field
from pyiem.database import get_sqlalchemy_conn, sql_helper
from pyiem.exceptions import IncompleteWebRequest
from pyiem.webutil import CGIModel, ListOrCSVType, iemapp

EXL = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"


class MyModel(CGIModel):
    """Our model"""

    format: str = Field(
        "csv",
        description="The format of the data response. csv, json, or excel",
        pattern=r"^(csv|json|excel)$",
    )
    ets: AwareDatetime = Field(
        None,
        description="The end time for the data request",
    )
    stations: Annotated[
        ListOrCSVType, Field(description="The station identifiers")
    ]
    sts: AwareDatetime = Field(
        None,
        description="The start time for the data request",
    )
    year1: int = Field(
        None,
        description="The start year for the data request, when sts is not set",
    )
    month1: int = Field(
        None,
        description=(
            "The start month for the data request, when sts is not set"
        ),
    )
    day1: int = Field(
        None,
        description="The start day for the data request, when sts is not set",
    )
    hour1: int = Field(
        None,
        description="The start hour for the data request, when sts is not set",
    )
    year2: int = Field(
        None,
        description="The end year for the data request, when ets is not set",
    )
    month2: int = Field(
        None,
        description="The end month for the data request, when ets is not set",
    )
    day2: int = Field(
        None,
        description="The end day for the data request, when ets is not set",
    )
    hour2: int = Field(
        None,
        description="The end hour for the data request, when ets is not set",
    )


def get_data(sts, ets, stations, fmt):
    """Go fetch data please"""
    with get_sqlalchemy_conn("other") as conn:
        df = pd.read_sql(
            sql_helper(
                """
            select
            valid at time zone 'UTC' as utc_valid, * from alldata
            WHERE station = ANY(:stations)
            and valid >= :sts and valid <= :ets
            ORDER by valid, station ASC"""
            ),
            conn,
            params={
                "sts": sts,
                "ets": ets,
                "stations": stations,
            },
        )
    df = df.drop(columns=["valid"])
    if fmt == "json":
        return df.to_json(orient="records", date_format="iso")
    if fmt == "excel":
        bio = BytesIO()
        with pd.ExcelWriter(bio, engine="xlsxwriter") as writer:
            df.to_excel(writer, sheet_name="Data", index=False)
        return bio.getvalue()

    sio = StringIO()
    df.to_csv(sio, index=False)
    return sio.getvalue()


@iemapp(help=__doc__, schema=MyModel, default_tz="UTC")
def application(environ, start_response):
    """See how we are called"""
    if environ["sts"] is None or environ["ets"] is None:
        raise IncompleteWebRequest("Missing sts and/or ets")
    stations = environ["stations"]
    fmt = environ["format"]
    if fmt != "excel":
        payload = get_data(environ["sts"], environ["ets"], stations, fmt)
        start_response("200 OK", [("Content-type", "text/plain")])
        return payload
    headers = [
        ("Content-type", EXL),
        ("Content-disposition", "attachment; Filename=wmo_bufr_srf.xlsx"),
    ]
    payload = get_data(environ["sts"], environ["ets"], stations, fmt)
    start_response("200 OK", headers)
    return payload
