#!/bin/env python3
#-----------------------------------------------------------------------------------------------------------------------------------
# Routine Name: weeUpdate.py
# Author:       Mike Revitt
# Date:         19/03/2020
#------------------------------------------------------------------------------------------------------------------------------------
# Revision History    Push Down List
# -----------------------------------------------------------------------------------------------------------------------------------
# Date        | Name        | Description
# ------------+-------------+--------------------------------------------------------------------------------------------------------
#             |             |
# 17/01/2021  | M Revitt    | Initial version
#-------------+-------------+--------------------------------------------------------------------------------------------------------
# Description:  Updates the archive table in the SQLite3 database to fill in missing values
#
# Issues:       None
#
# ***********************************************************************************************************************************
# Copyright 2020 Mike Revitt <mike@cougar.eu.com>. All Rights Reserved.
#
# Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files
# (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify,
# merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is
# furnished to do so.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
# LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
# CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
# ***********************************************************************************************************************************
import csv
import os
import sqlite3
import time

CSV_FILE_PATH     = '/tmp'
#CSV_FILE_PATH      = '/Users/revittmk/OneDrive/Mike/WebSites/Weewx/DataLoads/Ecowitt'
SQLITE_DB         = '/home/weewx/weewx.sdb'
#SQLITE_DB         = '/Users/revittmk/OneDrive/Mike/WebSites/Weewx/DataLoads/Ecowitt/weewx.sdb'
SOURCE_FILE       = 'Cougar-Caravan.csv'
DEST_FILE         = 'Epoc-Caravan.csv'

if __name__ == "__main__":

    firstRow      = True
    sourceFile    = os.path.join(CSV_FILE_PATH, SOURCE_FILE)
    destFile      = os.path.join(CSV_FILE_PATH, DEST_FILE)
    destData      = open(destFile, 'w')
    destWriter    = csv.writer(destData, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    sqliteDB      = sqlite3.connect(SQLITE_DB)
    sqliteCursor  = sqliteDB.cursor()

    with open(sourceFile, 'r') as sourceData:
        csvReader = csv.reader(sourceData, delimiter=',')

        for sLineIn in csvReader:
            if firstRow == True:
                destWriter.writerow(sLineIn)
                firstRow = False
            else:
                epochTime =  int(time.mktime(time.strptime(sLineIn[0],'%Y-%m-%d %H:%M')))
                destWriter.writerow([epochTime, sLineIn[1], sLineIn[2], sLineIn[3], sLineIn[4]])

                sqlString = 'update archive set extraTemp2  = ' + sLineIn[1] + ',';
                sqlString =  sqlString +       'extraHumid2 = ' + sLineIn[2] + ',';
                sqlString =  sqlString +       'extraTemp3  = ' + sLineIn[3] + ',';
                sqlString =  sqlString +       'extraHumid1 = ' + sLineIn[4] + ' ';
                sqlString =  sqlString + 'where datetime    = ' + str(epochTime)  + ';\n';
                sqliteCursor.execute(sqlString)

    sourceData.close()
    destData.close()
    sqliteDB.commit()
    sqliteCursor.close()