#
# Python 3 program to:
#   1. Download a days worth of data from the WeatherFlow API in CSV format (or use a provided CSV file)
#   2. Upadte the weewx.sdb file for the data in the CSV file:
#       2a. lightning_strike_count = Total number of lightning strikes during the 5 minute time period
#       2b. lightning_distance = AverageLightning Distance during the 5 minute time period
#       2c. rain = Amount of rain in millimeters during the 5 minute time period
#       2d. rainrate = Amount of rain if received over an hour period (rain * 12)
#       2e. rainBatteryStatus = Precipitation Type (0=None; 1=Rain; 2=Hail)
#       2f. signal7 = Closest (Min) Lightning Distance during the 5 minute time period
#       2g. signal8 = Furthest (Max) Lightning Distance during the 5 minute time period
#
# By James Bellanca
#

#
# Can take one of two command line arguments.
# Both cannot be used at the same time. The last one entered wins.
# If no argument is specified, it assumes -d 1 to retrieve and process yesterday's data.
#
# -d, -DaysAgo x Retrieves data from the WeatherFlow API for x days ago and processes it.
#                The file is saved in the folder that this program is in.
# -f, -File x    Processes the file x. It must be a properly formatted WeatherFlow API csv file.

from datetime import datetime, timedelta
import sys, getopt
import time
import subprocess
import csv
import sqlite3
import urllib.request

# Set to the path and file of your WeeWx SDB database file
DEFAULT_PATH = "/Users/FamilyMini/weewx/current/archive/weewx.sdb"

# Set the Tempest Device ID to your value
stationID = "68706" # IMPORTANT -- Put you Device ID here!!!

# This is the demo API key which works for now for individual use
APIkey = "20c70eae-e62f-4d3b-b3a4-8586e90f3ac8"

# Function to update the values in the WeeWx database
def updateRow(nextTimeInterval, loopIndex, writeRainData, timestamp, averagingStrikeIndex, strike_distance, precip_final, local_daily_precip_final, strike_count, precipTypeMax, strikeDistanceMin, strikeDistanceMax):
    nextTimeInterval = str(int(timestamp)+300)
    if strikeDistanceMin == 999:
        strikeDistanceMin = 0
    if averagingStrikeIndex > 0:
        strike_distance = round(strike_distance / averagingStrikeIndex, 2)
    strikeDistanceMin = round(strikeDistanceMin / 1.609, 2) # Convert from KM to Miles
    strikeDistanceMax = round(strikeDistanceMax / 1.609, 2)
    strike_distance = round(strike_distance / 1.609, 2)
    precip_final = round(precip_final/25.4, 6) #convert from mm to inches and round
    rainRate = round(precip_final*12, 6) #convert from mm to inches and round
    if strike_distance == 0:
        strike_distance_write = "null"
    else:
        strike_distance_write = str(strike_distance)
    if strikeDistanceMin == 0:
        strikeDistanceMinWrite = "null"
    else:
        strikeDistanceMinWrite = str(strikeDistanceMin)
    if strikeDistanceMax == 0:
        strikeDistanceMaxWrite = "null"
    else:
        strikeDistanceMaxWrite = str(strikeDistanceMax)
    local_daily_precip_final = round(local_daily_precip_final/25.4, 6) #convert mm to inches and round
    sqltoexecute = "update archive set lightning_strike_count = " + str(strike_count) + ", lightning_distance = " + str(strike_distance_write) + ", signal7 = " + str(strikeDistanceMinWrite)+ ", signal8 = " + str(strikeDistanceMaxWrite) + " where dateTime = " + timestamp
    cursorObj.execute(sqltoexecute)
    con.commit()
    print (sqltoexecute)
    if writeRainData == 1:
        sqltoexecute = "update archive set rain = " + str(precip_final) + ", rainrate = " + str(rainRate) + ", rainBatteryStatus = " + str(precipTypeMax)+ " where dateTime = " + timestamp
        print (sqltoexecute)
    cursorObj.execute(sqltoexecute)
    con.commit()
    return 

argumentList = sys.argv[1:] 
  
# Options 
options = "hd:f:"
  
# Long options 
long_options = ["Help", "Days Ago", "File ="] 
  
try: 
    # Parsing argument 
    arguments, values = getopt.getopt(argumentList, options, long_options) 
    
    daysAgo = 1
    processingMode = 0 # 0 = not set, assume 1 day ago; 1 = retrieve file from x days ago; 2 = Process filen from command line arguments
    
    # checking each argument 
    for currentArgument, currentValue in arguments: 
    
        if currentArgument in ("-h", "--Help"): 
            print ("usage: -d | -f | -h")
            print ("") 
            print ("Options:")
            print ("  -d x, --Days Ago x   Retrieves data from the WeatherFlow API for x days ago")
            print ("  -f x, --File x       Processes the file x which must be a properly formatted WeatherFlow API csv file") 
            print ("  -h, --Help           Displays help")
            print ("  No arguments         Same as running -d 1")
            sys.exit()
              
        elif currentArgument in ("-d", "--DaysAgo"): 
            daysAgo = int(currentValue)
            if daysAgo < 1 or daysAgo > 7:
                print("Days Ago argument must be between 1 and 7.")
                sys.exit()
            else:
                processingMode = 1
        
        elif currentArgument in ("-f", "--File"): 
            processingMode = 2
            print (("File to process (% s)") % (currentValue)) 
            filename = str(currentValue)
            print(filename)
            
except getopt.error as err: 
    # output error, and return with an error code 
    print (str(err)) 

if processingMode == 0 or processingMode == 1:
    if daysAgo == 1:
        print("Retrieving data from yesterday.")
    else:
        print ("Retrieving data from %s day(s) ago." % (daysAgo))
    # URL uses the demo API key from WeatherFlow
    url = 'https://swd.weatherflow.com/swd/rest/observations/device/' + stationID + '?api_key=' + APIkey + '&day_offset=' + str(daysAgo) + '&format=csv'
    fileDate = datetime.now() - timedelta(hours=(daysAgo*24))
    filename = "WF_"+str(fileDate.strftime("%Y-%m-%d"))+".csv"
    print(url)
    print(filename)
    urllib.request.urlretrieve(url, filename)

# Open WeeWx SQLite Database
            
con = sqlite3.connect(DEFAULT_PATH)
cursorObj = con.cursor()

# Main processing loop for the CSV file

with open(filename) as csvfile:
    
    # Set default values
    rowIndex = -1
    loopIndex = 0
    averagingStrikeIndex = 0
    precip_type = 0
    nextTimeInterval = 0
    timestamp = ""
    strike_distance = float(0)
    strike_count = int(0)
    precip_final = float(0)
    strikeDistanceMin = float(999)
    strikeDistanceMax = float(0)
    precipTypeMax = float(0)
    writeRainData = 1 # Sometimes the daily downloaded data doesn't populate the final rain check data. If that happens, ignore he rain data in the file.
    
    # Read and process each row in the CSV file
    readCSV = csv.reader(csvfile, delimiter=',')
    for row in readCSV:
        #print(row)
        rowIndex+=1
        if rowIndex > 0:
                timestamp = row[3]
                # Check to see if the current row has a time later than the next row to update in WeeWx.
                # This could happen if the csv file doesn't have an entry for the exact 5 minute entry that will be in WeeWx.
                if (int(timestamp) > int(nextTimeInterval)) and (int(nextTimeInterval) > 0):
                    print("Timestamp missing! ", nextTimeInterval)
                    # Call function to do the database update
                    updateRow(nextTimeInterval, loopIndex, writeRainData, nextTimeInterval, averagingStrikeIndex, strike_distance, precip_final, local_daily_precip_final, strike_count, precipTypeMax, strikeDistanceMin, strikeDistanceMax)
                    # Determine what the next WeeWx row should be to make sure we compute values at that row even if it's not present in the file
                    nextTimeInterval = str(int(timestamp)+300)
                    # Reset all values
                    strike_distance = 0
                    strike_count = 0
                    precip_final = 0
                    loopIndex = 0
                    averagingStrikeIndex = 0
                    strikeDistanceMin = 999
                    strikeDistanceMax = 0
                    precip_type = 0
                    precipTypeMax = 0
                # Sum or set values as appropriate
                strike_distance += float(row[17])
                strike_count += int(row[18])
                if row[22] == "" or row[23] == "":
                    writeRainData = 0 # Fina;l rain check data is blank, so stop writing rain data from this point on, only write lightning data
                else:
                    precip_final += float(row[22])
                    local_daily_precip_final = float(row[23])
                    precip_type = int(row[16])
                    if int(row[16]) > precipTypeMax:
                        precipTypeMax = int(row[16])
                loopIndex+=1
                # Compute number of rows with lightning to compute the average, and record min/max distance over the period, and precip type (0=none; 1=rain; 2=hail)
                if int(row[18]) != 0:
                    averagingStrikeIndex +=1
                if (int(row[17]) < strikeDistanceMin) and (int(row[18]) > 0):
                    strikeDistanceMin = float(row[17])
                if int(row[17]) > strikeDistanceMax:
                    strikeDistanceMax = float(row[17])
                if int(row[3])%300 == 0:
                     # Call function to do the database update
                     updateRow(nextTimeInterval, loopIndex, writeRainData, timestamp, averagingStrikeIndex, strike_distance, precip_final, local_daily_precip_final, strike_count, precipTypeMax, strikeDistanceMin, strikeDistanceMax)
                     # Determine what the next WeeWx row should be to make sure we compute values at that row even if it's not present in the file
                     nextTimeInterval = str(int(timestamp)+300)
                     # Reset all values
                     strike_distance = 0
                     strike_count = 0
                     precip_final = 0
                     loopIndex = 0
                     averagingStrikeIndex = 0
                     strikeDistanceMin = 999
                     strikeDistanceMax = 0
                     precip_type = 0
                     precipTypeMax = 0

if writeRainData == 0:
    print ("Rain Check Data missing from file!")
    
# To auto-run the daily rebuild, make sure to edit wee_database to comment out the Proceed lines starting at line 253
# Corrected so that the Proceed lines in wee_database don't need to be commented out by using redirection
#wee_database --rebuild-daily --date=YYYY-mm-dd
time.sleep(5)
#lineargs = "--rebuild-daily --date=" + str(fileDate.strftime("%Y-%m-%d"))
#subprocess.call(["wee_database", lineargs])
lineargs = 'echo "y" | wee_database --rebuild-daily --date=' + str(fileDate.strftime("%Y-%m-%d"))
p=subprocess.Popen(lineargs,stdout=subprocess.PIPE,shell=True)
(output,err)=p.communicate()
p_status=p.wait()
print (output)