""".. title:: VTEC Events by WFO and Year

Return to `API Services </api/>`_

Documentation for /json/vtec_events.py
--------------------------------------

This provides metadata on VTEC events for a given WFO and year.

Changelog
---------

- 2025-01-10: Support providing all events for a given year and wfo.
- 2024-08-08: Migration to pydantic validation

Example Usage
-------------

Provide all NWS Des Moines VTEC events for 2024.

https://mesonet.agron.iastate.edu/json/vtec_events.py?wfo=DMX&year=2024

Provide all NWS Des Moines Tornado Warnings for 2024:

https://mesonet.agron.iastate.edu/json/vtec_events.py?wfo=DMX&year=2024\
&phenomena=TO&significance=W

Provide all SV, TO, FF, MA events for NWS Des Moines in 2024 in csv format:

https://mesonet.agron.iastate.edu/json/vtec_events.py?wfo=DMX&year=2024\
&combo=1&fmt=csv

Provide all 2024 tornado warnings for NWS Des Moines in xlsx format:

https://mesonet.agron.iastate.edu/json/vtec_events.py?wfo=DMX&year=2024\
&phenomena=TO&significance=W&fmt=xlsx

"""

import json
from io import BytesIO, StringIO
from typing import Annotated

import pandas as pd
from pydantic import Field
from pyiem.database import get_sqlalchemy_conn, sql_helper
from pyiem.nws.vtec import VTEC_PHENOMENA, VTEC_SIGNIFICANCE
from pyiem.reference import ISO8601
from pyiem.webutil import CGIModel, iemapp
from sqlalchemy import Connection

from iemweb.fields import (
    CALLBACK_FIELD,
    VTEC_PH_FIELD_OPTIONAL,
    VTEC_SIG_FIELD_OPTIONAL,
    VTEC_YEAR_FIELD,
    WFO3_FIELD,
)
from iemweb.mlib import rectify_wfo
from iemweb.util import json_response_dict

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


class Schema(CGIModel):
    """See how we are called."""

    callback: CALLBACK_FIELD = None
    combo: Annotated[
        bool,
        Field(
            description="Special one-off to get all SV, TO, FF, MA events",
        ),
    ] = False
    fmt: Annotated[
        str,
        Field(
            description="Output format, json, csv, xlsx",
            pattern="^(json|csv|xlsx)$",
        ),
    ] = "json"
    phenomena: VTEC_PH_FIELD_OPTIONAL = None
    significance: VTEC_SIG_FIELD_OPTIONAL = None
    wfo: WFO3_FIELD = "MPX"
    year: VTEC_YEAR_FIELD = 2015


def get_res(conn: Connection, wfo, year, phenomena, significance, combo):
    """Generate a report of VTEC ETNs used for a WFO and year

    Args:
      wfo (str): 3 character WFO identifier
      year (int): year to run for
      phenomena (str, optional): 2 character phenomena
      significance (str, optional): 1 character VTEC significance
      combo (int, optional): special one-offs
    """
    params = {
        "year": year,
        "wfo": wfo,
        "phenomena": phenomena,
        "significance": significance,
    }
    limits = ["phenomena is not null", "significance is not null"]
    orderby = "u.phenomena ASC, u.significance ASC, u.utc_issue ASC"
    if phenomena is not None:
        limits[0] = "phenomena = :phenomena"
    if significance is not None:
        limits[1] = "significance = :significance"
    plimit = " and ".join(limits)
    if combo:
        plimit = (
            "phenomena in ('SV', 'TO', 'FF', 'MA') and "
            "significance in ('W', 'A')"
        )
        orderby = "u.utc_issue ASC"
    res = conn.execute(
        sql_helper(
            """
    WITH polyareas as (
        SELECT phenomena, significance, eventid, round((ST_area(
        ST_transform(geom,9311)) / 1000000.0)::numeric,0) as area
        from sbw WHERE vtec_year = :year and wfo = :wfo
        and eventid is not null and {plimit} and status = 'NEW'
    ), ugcareas as (
        SELECT
        round(sum(ST_area(
            ST_transform(u.geom,9311)) / 1000000.0)::numeric,0) as area,
        string_agg(u.name || ' ['||u.state||']', ', ') as locations,
        eventid, phenomena, significance,
        min(issue) at time zone 'UTC' as utc_issue,
        max(expire) at time zone 'UTC' as utc_expire,
        min(product_issue) at time zone 'UTC' as utc_product_issue,
        max(init_expire) at time zone 'UTC' as utc_init_expire,
        max(hvtec_nwsli) as nwsli,
        max(fcster) as fcster from warnings w JOIN ugcs u on (w.gid = u.gid)
        WHERE vtec_year = :year and w.wfo = :wfo and eventid is not null
        and {plimit}
        GROUP by phenomena, significance, eventid)

    SELECT u.*, coalesce(p.area, u.area) as myarea
    from ugcareas u LEFT JOIN polyareas p on
    (u.phenomena = p.phenomena and u.significance = p.significance
     and u.eventid = p.eventid) ORDER by {orderby}
    """,
            plimit=plimit,
            orderby=orderby,
        ),
        params,
    )
    data = json_response_dict(
        {
            "wfo": wfo,
            "year": year,
            "events": [],
        }
    )
    for row in res.mappings():
        uri = (
            f"/vtec/?year={year}&wfo={rectify_wfo(wfo)}&"
            f"phenomena={row['phenomena']}&significance={row['significance']}&"
            f"eventid={row['eventid']:04.0f}"
        )
        data["events"].append(
            dict(
                phenomena=row["phenomena"],
                significance=row["significance"],
                ph_name=VTEC_PHENOMENA.get(row["phenomena"], "Unknown"),
                sig_name=VTEC_SIGNIFICANCE.get(row["significance"], "Unknown"),
                eventid=row["eventid"],
                hvtec_nwsli=row["nwsli"],
                area=float(row["myarea"]),
                locations=row["locations"],
                issue=row["utc_issue"].strftime(ISO8601),
                product_issue=row["utc_product_issue"].strftime(ISO8601),
                expire=row["utc_expire"].strftime(ISO8601),
                init_expire=row["utc_init_expire"].strftime(ISO8601),
                uri=uri,
                url=f"https://mesonet.agron.iastate.edu{uri}",
                wfo=wfo,
                fcster=row["fcster"],
            )
        )
    return data


@iemapp(help=__doc__, schema=Schema)
def application(environ, start_response):
    """Answer request."""
    query: Schema = environ["_cgimodel_schema"]
    with get_sqlalchemy_conn("postgis") as pgconn:
        res = get_res(
            pgconn,
            query.wfo,
            query.year,
            query.phenomena,
            query.significance,
            query.combo,
        )

    fnbase = (
        f"vtec_{query.wfo}_{query.year}_{query.phenomena}_{query.significance}"
    )
    if query.fmt == "xlsx":
        fn = f"{fnbase}.xlsx"
        headers = [
            ("Content-type", EXL),
            ("Content-disposition", f"attachment; Filename={fn}"),
        ]
        bio = BytesIO()
        pd.DataFrame(res["events"]).to_excel(bio, index=False)
        start_response("200 OK", headers)
        return [bio.getvalue()]
    if query.fmt == "csv":
        fn = f"{fnbase}.csv"
        headers = [
            ("Content-type", "application/octet-stream"),
            ("Content-disposition", f"attachment; Filename={fn}"),
        ]
        bio = StringIO()
        pd.DataFrame(res["events"]).to_csv(bio, index=False)
        start_response("200 OK", headers)
        return [bio.getvalue().encode("utf-8")]

    res = json.dumps(res)
    start_response("200 OK", [("Content-type", "application/json")])
    return res
