#!/usr/bin/env python
"""
Return a simple CSV of stuart smith data

Levelogger Reading (ft)
Barologger Reading
Temp (C)
Barologger Air Temp (C)
Conductivity (micro-S)
7.20473    ch1_data_p
2.68857     ch2_data_p
21.1       ch1_data_t
18.19    ch2_data_t
48       ch1_data_c
"""
import cgi
import datetime
import os

import pandas as pd
from pyiem.util import get_dbconn, ssw

LOOKUP = {9100104: "SSP #6",
          9100135: "SSP #8",
          9100131: "SSP #1",
          9100156: "SSP #7"}


def gage_run(sts, ets, stations, excel):
    """ run() """
    if not stations:
        stations = LOOKUP.keys()
    if len(stations) == 1:
        stations.append(0)

    dbconn = get_dbconn('other', user='nobody')
    sql = """select date(valid) as date, to_char(valid, 'HH24:MI:SS') as time,
    site_serial, ch1_data_p, ch2_data_p,
    ch1_data_t, ch2_data_t, ch1_data_c
    from ss_logger_data WHERE valid between '%s' and '%s' and
    site_serial in %s ORDER by valid ASC""" % (sts, ets, str(tuple(stations)))
    df = pd.read_sql(sql, dbconn)
    headers = ['date', 'time', 'site_serial', 'Levelogger Reading (ft)',
               'Barologger Reading', 'Water Temp (C)',
               'Barologger Air Temp (C)', 'Conductivity (micro-S)']

    if excel == 'yes':
        ssw("Content-type: application/vnd.ms-excel\n")
        ssw(("Content-Disposition: attachment; Filename=stuartsmith.xls\n\n"))
        df.to_excel('/tmp/ss.xls', header=headers, index=False)
        ssw(open('/tmp/ss.xls', 'rb').read())
        os.unlink('/tmp/ss.xls')
    else:
        ssw("Content-type: text/plain\n\n")
        ssw(df.to_csv(None, header=headers, index=False))


def bubbler_run(sts, ets, excel):
    """ run() """
    dbconn = get_dbconn('other', user='nobody')
    sql = """
    WITH one as (SELECT valid, value from ss_bubbler WHERE
    valid between '%s' and '%s' and field = 'Batt Voltage'),
    two as (SELECT valid, value from ss_bubbler WHERE
    valid between '%s' and '%s' and field = 'STAGE'),
    three as (SELECT valid, value from ss_bubbler WHERE
    valid between '%s' and '%s' and field = 'Water Temp')

    SELECT date(coalesce(one.valid, two.valid, three.valid)) as date,
    to_char(coalesce(one.valid, two.valid, three.valid), 'HH24:MI:SS') as time,
    one.value, two.value, three.value
    from one FULL OUTER JOIN two on (one.valid = two.valid)
        FULL OUTER JOIN three on (coalesce(two.valid,one.valid) = three.valid)
    ORDER by date ASC, time ASC
    """ % (sts, ets, sts, ets, sts, ets)
    df = pd.read_sql(sql, dbconn)
    headers = ['date', 'time', 'batt voltage', 'stage', 'water temp']

    if excel == 'yes':
        ssw("Content-type: application/vnd.ms-excel\n")
        ssw(("Content-Disposition: attachment; Filename=bubbler.xls\n\n"))
        df.to_excel('/tmp/ss.xls', header=headers, index=False)
        ssw(open('/tmp/ss.xls', 'rb').read())
        os.unlink('/tmp/ss.xls')
    else:
        ssw("Content-type: text/plain\n\n")
        ssw(df.to_csv(None, header=headers, index=False))


def main():
    """ Go Main Go """
    form = cgi.FieldStorage()
    opt = form.getfirst('opt', 'bubbler')

    year1 = int(form.getfirst("year1"))
    year2 = int(form.getfirst("year2"))
    month1 = int(form.getfirst("month1"))
    month2 = int(form.getfirst("month2"))
    day1 = int(form.getfirst("day1"))
    day2 = int(form.getfirst("day2"))
    stations = form.getlist('station')

    sts = datetime.datetime(year1, month1, day1)
    ets = datetime.datetime(year2, month2, day2)

    if opt == 'bubbler':
        bubbler_run(sts, ets, form.getfirst('excel', 'n'))
    elif opt == 'gage':
        gage_run(sts, ets, stations, form.getfirst('excel', 'n'))


if __name__ == '__main__':
    main()
