#!/usr/bin/python3
# Display Engine Client - Copyright 2018 Lucid Networks - Stephen Loeckle

import sqlite3
import os
import argparse
from datetime import datetime, timedelta
from time import sleep

database = ('/var/lib/declient/declient.db')

def main():
    createdb = 0
    updatedb = 0
    resetstats = 0
    systemrecords = [['dbinit', 'datenow', 'strvalue'], ['remediation', '', 'strvalue'], ['browserpid', 0, 'value'],
                     ['checkfundamentalsruns', 0, 'value'], ['screenanalysisruns', 0, 'value'], ['activeproblemcount', 0, 'value'],
                     ['activeproblem', '', 'strvalue'], ['startuptime', '', 'strvalue'], ['startup', 1, 'value'], ['shutdown', 0, 'value'],
                     ['cloudcmdshutdowncomplete', 0, 'value'], ['systemanalysisshutdowncomplete', 0, 'value'], ['apishutdowncomplete', 0, 'value'], 
                     ['maintenance', 0, 'value']]
    cloudrecords = [['lastheaddate', '', 'strvalue'], ['lastcommand', 0, 'value'], ['samecommandcount', 0, 'value'], ['cmdtime', 0, 'value'],
                    ['lastupdated', 0, 'value'], ['urlerrors', 0, 'value'], ['gatewayerrors', 0, 'value'], ['lastsystemerror', 'none', 'strvalue']]
    errorsrecords = [['chromenotrunning', 0, 'value'], ['chromewrongsize', 0, 'value'], ['openterminal', 0, 'value'],
                    ['desktopshowing', 0, 'value'], ['chromehungonstartup', 0, 'value'], ['greyscreen', 0, 'value'],
                    ['blackscreen', 0, 'value'], ['whitescreen', 0, 'value'], ['restorepagesdialog', 0, 'value'],
                    ['pagenotavailable', 0, 'value'], ['awsnap', 0, 'value'], ['webglcrash', 0, 'value'],
                    ['badrequest', 0, 'value'], ['500', 0, 'value'], ['503', 0, 'value'],
                    ['nginxerror', 0, 'value'], ['youtubeerror', 0, 'value'], ['sprcmdcenterplaylist', 0, 'value'],
                    ['sprcmdcenterlocked', 0, 'value'], ['videodrivererror', 0, 'value']]
    datenow = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    parser = argparse.ArgumentParser(description='Display Engine Client Init DB')
    parser.add_argument('-f', '--force', help = 'Force overwrite on database', action="store_true")
    parser.add_argument('-cl', '--clearlogs', help = 'Empty errorlogs table', action="store_true")
    parser.add_argument('-rs', '--resetstats', help = 'Reset stats in errors table', action="store_true")
    args = parser.parse_args()
    if args.clearlogs:
        if os.path.exists(database):
            conn = sqlite3.connect(database)
            c = conn.cursor()
            print('Clearing error logs table')
            c.execute('delete from errorlog')
            print('Error logs table cleared')
        else:
            print('Database does not exist. Creating now.')
            createdb = 1
    if args.resetstats:
        if os.path.exists(database):
            conn = sqlite3.connect(database)
            c = conn.cursor()
            print('Clearing error logs table')
            c.execute('delete from errorlog')
            print('Error logs table cleared')
            resetstats = 1
    if (not args.resetstats and not args.clearlogs):
        if os.path.exists(database):
            if args.force:
                print('Database found. Forcing database overwrite.')
                os.remove(database)
                createdb = 1
            else:
                print('Database found. Looking for Schema Updates.')
                updatedb = 1
        else:
            print('Database not found. Creating.')
            createdb = 1
    if updatedb == 1:
        conn = sqlite3.connect(database)
        c = conn.cursor()
        print('Updating schema.')
        c.execute('create table if not exists system (tags text, value int, strvalue text, td timestamp, notes text)')
        c.execute('create table if not exists cloud (tags text, value int, strvalue text, td timestamp)')
        c.execute('create table if not exists errors (tags text, value int, strvalue text, td timestamp, seq int)')
        c.execute('create table if not exists errorlog (logid integer PRIMARY KEY, tags text, td timestamp, ack int, notes text)')
        conn.commit()
        for (record, data, val) in systemrecords:
            c.execute('SELECT * FROM system WHERE tags = ?', (record,))
            row = c.fetchall()
            if len(row)==0:
                print('    system:{} not found'.format(record))
                if data == 'datenow':
                    c.execute('insert into system (tags, strvalue) values (?, ?) ', (record, datenow))
                    print('        Added system:{}'.format(record))
                elif val == 'value':
                    c.execute('insert into system (tags, value) values (?, ?) ', (record, data))
                    print('        Added system:{}'.format(record))
                elif val == 'strvalue':
                    c.execute('insert into system (tags, strvalue) values (?, ?) ', (record, data))
                    print('        Added system:{}'.format(record))
                else:
                    print('        system:{} failed'.format(record))
#            else:
#                print('    system:{} found'.format(record))
        for (record, data, val) in cloudrecords:
            c.execute('SELECT * FROM cloud WHERE tags = ?', (record,))
            row = c.fetchall()
            if len(row)==0:
                print('    cloud:{} not found'.format(record))
                if val == 'value':
                    c.execute('insert into cloud (tags, value) values (?, ?) ', (record, data))
                    print('        Added cloud:{}'.format(record))
                elif val == 'strvalue':
                    c.execute('insert into cloud (tags, strvalue) values (?, ?) ', (record, data))
                    print('        Added cloud:{}'.format(record))
                else:
                    print('        cloud:{} failed'.format(record))
#            else:
#                print('    cloud:{} found'.format(record))
        for (record, data, val) in errorsrecords:
            c.execute('SELECT * FROM errors WHERE tags = ?', (record,))
            row = c.fetchall()
            if len(row)==0:
                print('    errors:{} not found'.format(record))
                if val == 'value':
                    c.execute('insert into errors (tags, value) values (?, ?) ', (record, data))
                    print('        Added errors:{}'.format(record))
                elif val == 'strvalue':
                    c.execute('insert into errors (tags, strvalue) values (?, ?) ', (record, data))
                    print('        Added errors:{}'.format(record))
                else:
                    print('        errors:{} failed'.format(record))
#            else:
#                print('    errors:{} found'.format(record))
        print('Done.')
    if createdb == 1:
        conn = sqlite3.connect(database)
        c = conn.cursor()
        print('Building schema.')
        c.execute('create table if not exists system (tags text, value int, strvalue text, td timestamp, notes text)')
        c.execute('create table if not exists cloud (tags text, value int, strvalue text, td timestamp)')
        c.execute('create table if not exists errors (tags text, value int, strvalue text, td timestamp, seq int)')
        c.execute('create table if not exists errorlog (logid integer PRIMARY KEY, tags text, td timestamp, ack int, notes text)')
        conn.commit()
        for (record, data, val) in systemrecords:
            if data == 'datenow':
                c.execute('insert into system (tags, strvalue) values (?, ?) ', (record, datenow))
                print('    Added system:{}'.format(record))
            elif val == 'value':
                c.execute('insert into system (tags, value) values (?, ?) ', (record, data))
                print('    Added system:{}'.format(record))
            elif val == 'strvalue':
                c.execute('insert into system (tags, strvalue) values (?, ?) ', (record, data))
                print('    Added system:{}'.format(record))
            else:
                print('    system:{} failed'.format(record))
        for (record, data, val) in cloudrecords:
            if val == 'value':
                c.execute('insert into cloud (tags, value) values (?, ?) ', (record, data))
                print('    Added cloud:{}'.format(record))
            elif val == 'strvalue':
                c.execute('insert into cloud (tags, strvalue) values (?, ?) ', (record, data))
                print('    Added cloud:{}'.format(record))
            else:
                print('    cloud:{} failed'.format(record))
        for (record, data, val) in errorsrecords:
            if val == 'value':
                c.execute('insert into errors (tags, value) values (?, ?) ', (record, data))
                print('    Added errors:{}'.format(record))
            elif val == 'strvalue':
                c.execute('insert into errors (tags, strvalue) values (?, ?) ', (record, data))
                print('    Added errors:{}'.format(record))
            else:
                print('    errors:{} failed'.format(record))
        print('Done.')
    if resetstats == 1:
        conn = sqlite3.connect(database)
        c = conn.cursor()
        print('Resetting stats.')
        for (record, data, val) in cloudrecords:
            c.execute('SELECT * FROM cloud WHERE tags = ?', (record,))
            row = c.fetchall()
            if len(row)==0:
                print('    cloud:{} not found'.format(record))
                if val == 'value':
                    c.execute('insert into cloud (tags, value) values (?, ?) ', (record, data))
                    print('        Added cloud:{}'.format(record))
                elif val == 'strvalue':
                    c.execute('insert into cloud (tags, strvalue) values (?, ?) ', (record, data))
                    print('        Added cloud:{}'.format(record))
                else:
                    print('        cloud:{} failed'.format(record))
            else:
                c.execute('UPDATE cloud SET value = ?, strvalue = ?, td = ? WHERE tags = ?', (0,'','',record))
#                print('    cloud:{} reset'.format(record))           
        for (record, data, val) in errorsrecords:
            c.execute('SELECT * FROM errors WHERE tags = ?', (record,))
            row = c.fetchall()
            if len(row)==0:
                print('    errors:{} not found'.format(record))
                if val == 'value':
                    c.execute('insert into errors (tags, value) values (?, ?) ', (record, data))
                    print('        Added errors:{}'.format(record))
                elif val == 'strvalue':
                    c.execute('insert into errors (tags, strvalue) values (?, ?) ', (record, data))
                    print('        Added errors:{}'.format(record))
                else:
                    print('        errors:{} failed'.format(record))
            else:
                c.execute('UPDATE errors SET value = ?, strvalue = ?, td = ?, seq = ? WHERE tags = ?', (0,'','',0,record))
#                print('    errors:{} reset'.format(record))
        print('Done.')
    conn.commit()
    conn.close()
        
if __name__ == '__main__':
   main()
