"""
 Download interface for the data stored in coop database (alldata)

 This is called from /request/coop/fe.phtml
"""
import datetime
import zipfile
from io import BytesIO, StringIO

import pandas as pd
import psycopg2.extras
from paste.request import parse_formvars
from pyiem.network import Table as NetworkTable
from pyiem.util import get_dbconn, utc, get_dbconnstr
from metpy.units import units
from sqlalchemy import text

DEGC = units.degC
DEGF = units.degF
EXL = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"


def f2c(val):
    """Convert F to C."""
    return (val * DEGF).to(DEGC).m


def get_scenario_period(ctx):
    """Compute the inclusive start and end dates to fetch scenario data for
    Arguments:
        ctx dictionary context this app was called with
    """
    sts = datetime.date(ctx["scenario_year"], ctx["ets"].month, ctx["ets"].day)
    ets = datetime.date(ctx["scenario_year"], 12, 31)
    return sts, ets


def get_database():
    """Get database"""
    return get_dbconn("coop")


def sane_date(year, month, day):
    """Attempt to account for usage of days outside of the bounds for
    a given month"""
    # Calculate the last date of the given month
    nextmonth = datetime.date(year, month, 1) + datetime.timedelta(days=35)
    lastday = nextmonth.replace(day=1) - datetime.timedelta(days=1)
    return datetime.date(year, month, min(day, lastday.day))


def get_cgi_dates(form):
    """Figure out which dates are requested via the form, we shall attempt
    to account for invalid dates provided!"""
    y1 = int(form.get("year1"))
    m1 = int(form.get("month1"))
    d1 = int(form.get("day1"))
    y2 = int(form.get("year2"))
    m2 = int(form.get("month2"))
    d2 = int(form.get("day2"))

    ets = sane_date(y2, m2, d2)
    archive_end = datetime.date.today() - datetime.timedelta(days=1)
    if ets > archive_end:
        ets = archive_end

    return [sane_date(y1, m1, d1), ets]


def get_cgi_stations(form):
    """Figure out which stations the user wants, return a list of them"""
    reqlist = form.getall("station[]")
    if not reqlist:
        reqlist = form.getall("stations")
    if not reqlist:
        return []
    if "_ALL" in reqlist:
        network = form.get("network")
        nt = NetworkTable(network, only_online=False)
        return nt.sts.keys()

    return reqlist


def do_apsim(ctx):
    """
    [weather.met.weather]
    latitude = 42.1 (DECIMAL DEGREES)
    tav = 9.325084 (oC) ! annual average ambient temperature
    amp = 29.57153 (oC) ! annual amplitude in mean monthly temperature
    year          day           radn          maxt          mint          rain
    ()            ()            (MJ/m^2)      (oC)          (oC)          (mm)
     1986          1             7.38585       0.8938889    -7.295556      0
    """
    if len(ctx["stations"]) > 1:
        return (
            "ERROR: APSIM output is only "
            "permitted for one station at a time."
        ).encode("ascii")

    dbconn = get_database()
    cursor = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    station = ctx["stations"][0]
    table = get_tablename(ctx["stations"])
    network = f"{station[:2]}CLIMATE"
    nt = NetworkTable(network, only_online=False)

    thisyear = datetime.datetime.now().year
    extra = {}
    if ctx["scenario"] == "yes":
        sts = datetime.datetime(int(ctx["scenario_year"]), 1, 1)
        ets = datetime.datetime(int(ctx["scenario_year"]), 12, 31)
        febtest = datetime.date(thisyear, 3, 1) - datetime.timedelta(days=1)
        sdaylimit = ""
        if febtest.day == 28:
            sdaylimit = " and sday != '0229'"
        cursor.execute(
            f"""
            SELECT day, high, low, precip, 1 as doy,
            coalesce(narr_srad, merra_srad, hrrr_srad) as srad
            from {table} WHERE station = %s
            and day >= %s and day <= %s {sdaylimit}""",
            (ctx["stations"][0], sts, ets),
        )
        for row in cursor:
            ts = row[0].replace(year=thisyear)
            extra[ts] = row
            extra[ts]["doy"] = int(ts.strftime("%j"))
        if febtest not in extra:
            feb28 = datetime.date(thisyear, 2, 28)
            extra[febtest] = extra[feb28]

    sio = StringIO()
    sio.write("! Iowa Environmental Mesonet -- NWS Cooperative Data\n")
    sio.write(f"! Created: {utc():%d %b %Y %H:%M:%S} UTC\n")
    sio.write("! Contact: daryl herzmann akrherz@iastate.edu 515-294-5978\n")
    sio.write("! Station: %s %s\n" % (station, nt.sts[station]["name"]))
    sio.write("! Data Period: %s - %s\n" % (ctx["sts"], ctx["ets"]))
    if ctx["scenario"] == "yes":
        sio.write(
            "! !SCENARIO DATA! inserted after: %s replicating year: %s\n"
            % (ctx["ets"], ctx["scenario_year"])
        )

    sio.write("[weather.met.weather]\n")
    sio.write(
        "latitude = %.1f (DECIMAL DEGREES)\n" % (nt.sts[station]["lat"],)
    )

    # Compute average temperature!
    cursor.execute(
        "SELECT avg((high+low)/2) as avgt from ncei_climate91 "
        "WHERE station = %s",
        (nt.sts[station]["ncei91"],),
    )
    row = cursor.fetchone()
    sio.write(
        "tav = %.3f (oC) ! annual average ambient temperature\n"
        % (f2c(row["avgt"]),)
    )

    # Compute the annual amplitude in temperature
    cursor.execute(
        """
        select max(avg) as h, min(avg) as l from
            (SELECT extract(month from valid) as month, avg((high+low)/2.)
             from ncei_climate91
             WHERE station = %s GROUP by month) as foo
             """,
        (nt.sts[station]["ncei91"],),
    )
    row = cursor.fetchone()
    sio.write(
        ("amp = %.3f (oC) ! annual amplitude in mean monthly temperature\n")
        % (f2c(row["h"]) - f2c(row["l"]),)
    )

    sio.write(
        """year        day       radn       maxt       mint      rain
  ()         ()   (MJ/m^2)       (oC)       (oC)       (mm)\n"""
    )

    if ctx.get("hayhoe_model") is not None:
        cursor.execute(
            """
            SELECT day, high, low, precip,
            extract(doy from day) as doy,
            0 as srad
            from hayhoe_daily WHERE station = %s
            and day >= %s and scenario = %s and model = %s
            ORDER by day ASC
        """,
            (
                ctx["stations"][0],
                ctx["sts"],
                ctx["hayhoe_scenario"],
                ctx["hayhoe_model"],
            ),
        )
    else:
        cursor.execute(
            f"""
            SELECT day, high, low, precip,
            extract(doy from day) as doy,
            coalesce(narr_srad, merra_srad, hrrr_srad) as srad
            from {table}
            WHERE station = %s and
            day >= %s and day <= %s and high is not null and
            low is not null and precip is not null ORDER by day ASC
            """,
            (station, ctx["sts"], ctx["ets"]),
        )
    for row in cursor:
        srad = -99 if row["srad"] is None else row["srad"]
        sio.write(
            ("%4s %10.0f %10.3f %10.1f %10.1f %10.2f\n")
            % (
                row["day"].year,
                int(row["doy"]),
                srad,
                f2c(row["high"]),
                f2c(row["low"]),
                row["precip"] * 25.4,
            )
        )

    if extra:
        dec31 = datetime.date(thisyear, 12, 31)
        now = row["day"]
        while now <= dec31:
            row = extra[now]
            srad = -99 if row["srad"] is None else row["srad"]
            sio.write(
                ("%4s %10.0f %10.3f %10.1f %10.1f %10.2f\n")
                % (
                    now.year,
                    int(row["doy"]),
                    srad,
                    f2c(row["high"]),
                    f2c(row["low"]),
                    row["precip"] * 25.4,
                )
            )
            now += datetime.timedelta(days=1)
    return sio.getvalue().encode("ascii")


def do_century(ctx):
    """Materialize the data in Century Format
    * Century format  (precip cm, avg high C, avg low C)
    prec  1980   2.60   6.40   0.90   1.00   0.70   0.00
    tmin  1980  14.66  12.10   7.33  -0.89  -5.45  -7.29
    tmax  1980  33.24  30.50  27.00  18.37  11.35   9.90
    prec  1981  12.00   7.20   0.60   4.90   1.10   0.30
    tmin  1981  14.32  12.48   8.17   0.92  -3.25  -8.90
    tmax  1981  30.84  28.71  27.02  16.84  12.88   6.82
    """
    if len(ctx["stations"]) > 1:
        return (
            "ERROR: Century output is only "
            "permitted for one station at a time."
        ).encode("ascii")

    station = ctx["stations"][0]
    network = "%sCLIMATE" % (station[:2],)
    nt = NetworkTable(network, only_online=False)

    dbconn = get_database()
    cursor = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    # Automatically set dates to start and end of year to make output clean
    sts = datetime.date(ctx["sts"].year, 1, 1)
    ets = datetime.date(ctx["ets"].year, 12, 31)
    if ets >= datetime.date.today():
        ets = datetime.date.today() - datetime.timedelta(days=1)

    table = get_tablename(ctx["stations"])
    thisyear = datetime.datetime.now().year
    cursor.execute(
        """
    WITH scenario as (
        SELECT """
        + str(thisyear)
        + """::int as year, month, high, low, precip
        from """
        + table
        + """
        WHERE station = %s and day > %s and day <= %s and sday != '0229'
    ), obs as (
      select year, month, high, low, precip from """
        + table
        + """
      WHERE station = %s and day >= %s and day <= %s
    ), data as (
      SELECT * from obs UNION select * from scenario
    )

    SELECT year, month, avg(high) as tmax, avg(low) as tmin,
    sum(precip) as prec from data GROUP by year, month
    """,
        (station, ctx["scenario_sts"], ctx["scenario_ets"], station, sts, ets),
    )
    data = {}
    for row in cursor:
        if row["year"] not in data:
            data[row["year"]] = {}
            for mo in range(1, 13):
                data[row["year"]][mo] = {"prec": -99, "tmin": -99, "tmax": -99}

        data[row["year"]][row["month"]] = {
            "prec": (row["prec"] * units("inch")).to(units("mm")).m,
            "tmin": f2c(row["tmin"]),
            "tmax": f2c(row["tmax"]),
        }
    sio = StringIO()
    sio.write("# Iowa Environmental Mesonet -- NWS Cooperative Data\n")
    sio.write(f"# Created: {utc():%d %b %Y %H:%M:%S} UTC\n")
    sio.write("# Contact: daryl herzmann akrherz@iastate.edu 515-294-5978\n")
    sio.write("# Station: %s %s\n" % (station, nt.sts[station]["name"]))
    sio.write("# Data Period: %s - %s\n" % (sts, ets))
    if ctx["scenario"] == "yes":
        sio.write(
            "# !SCENARIO DATA! inserted after: %s replicating year: %s\n"
            % (ctx["ets"], ctx["scenario_year"])
        )
    idxs = ["prec", "tmin", "tmax"]
    for year in range(sts.year, ets.year + 1):
        for idx in idxs:
            sio.write(
                (
                    "%s  %s%7.2f%7.2f%7.2f%7.2f%7.2f%7.2f%7.2f"
                    "%7.2f%7.2f%7.2f%7.2f%7.2f\n"
                )
                % (
                    idx,
                    year,
                    data[year][1][idx],
                    data[year][2][idx],
                    data[year][3][idx],
                    data[year][4][idx],
                    data[year][5][idx],
                    data[year][6][idx],
                    data[year][7][idx],
                    data[year][8][idx],
                    data[year][9][idx],
                    data[year][10][idx],
                    data[year][11][idx],
                    data[year][12][idx],
                )
            )
    return sio.getvalue().encode("ascii")


def do_daycent(ctx):
    """Materialize data for daycent

    Daily Weather Data File (use extra weather drivers = 0):
    > 1 1 1990 1 7.040 -10.300 0.000

    NOTES:
    Column 1 - Day of month, 1-31
    Column 2 - Month of year, 1-12
    Column 3 - Year
    Column 4 - Day of the year, 1-366
    Column 5 - Maximum temperature for day, degrees C
    Column 6 - Minimum temperature for day, degrees C
    Column 7 - Precipitation for day, centimeters
    """
    if len(ctx["stations"]) > 1:
        return (
            "ERROR: Daycent output is only "
            "permitted for one station at a time."
        ).encode("ascii")

    dbconn = get_database()
    cursor = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor)

    table = get_tablename(ctx["stations"])

    extra = {}
    thisyear = datetime.datetime.now().year
    if ctx["scenario"] == "yes":
        sts = datetime.datetime(int(ctx["scenario_year"]), 1, 1)
        ets = datetime.datetime(int(ctx["scenario_year"]), 12, 31)
        febtest = datetime.date(thisyear, 3, 1) - datetime.timedelta(days=1)
        sdaylimit = ""
        if febtest.day == 28:
            sdaylimit = " and sday != '0229'"
        cursor.execute(
            """
            SELECT day, high, low, precip
            from """
            + table
            + """ WHERE station = %s
            and day >= %s and day <= %s """
            + sdaylimit
            + """
            """,
            (ctx["stations"][0], sts, ets),
        )
        for row in cursor: