#!/usr/bin/python3
# LAVA Controls Setup - Copyright 2018 Lucid Networks - Stephen Loeckle

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

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

def dbwork(args):
    if os.path.exists(database):
        print('{} exists.'.format(database))
        if args.dbforce == 1:
            print('    Forcing overwrite.')
            os.remove(database)
            newdb = 1
        else:
            print('    Checking for schema updates')
            newdb = 0
    else:
        print('{} Does not exist. Creating.'.format(database))
        newdb = 1
    datenow = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn = sqlite3.connect(database)
    c = conn.cursor()
    c.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='system'")
    row = c.fetchall()
    if len(row) == 0:
        c.execute('create table if not exists system ( \
                    tag text, \
                    data text)')
    c.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='monitors'")
    row = c.fetchall()
    if len(row) == 0:
        c.execute('create table if not exists monitors ( \
                    monitor_id integer primary key autoincrement, \
                    model text, \
                    serial text, \
                    mac_address text, \
                    ip text, \
                    f1 text, \
                    f2 text, \
                    f3 text, \
                    f4 test, \
                    id int, \
                    power_state int, \
                    input text, \
                    lastinput text, \
                    volume int, \
                    osd int, \
                    tile_state int, \
                    tile_h text, \
                    tile_v text, \
                    tile_p text, \
                    tile_id int \
                    )')
    c.execute("select name from sqlite_master where type='table' and name='tiles'")
    row = c.fetchall()
    if len(row) != 0:
        columnlist = ['tile_key', 'tileid', 'member', 'size', 'pos']
        columns = c.execute('select * from tiles')
        names = list(map(lambda x: x[0], columns.description))
        for n in names:
            if n not in columnlist:
                print('        Tiles schema out of date. Dropping table and updating.')
                c.execute('drop table tiles')
                break
    c.execute('create table if not exists tiles ( \
                tile_key integer primary key autoincrement, \
                tileid int, \
                member int, \
                size text, \
                pos int \
                )')
    c.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='counters'")
    row = c.fetchall()
    if len(row) == 0:
        c.execute('create table if not exists counters ( \
                    tags text, \
                    value int, \
                    strvalue text, \
                    td timestamp \
                    )')

    countersrecords = [['lasttileid', 0, 'value']] # Add another set of internal brackets for every table entry, separated by comma like ,['strplaceholder', '', 'strvalue']
    for (record, data, val) in countersrecords:
        c.execute('SELECT * FROM counters WHERE tags = ?', (record,))
        row = c.fetchall()
        if len(row)==0:
            print('    counters:{} not found'.format(record))
            if val == 'value':
                c.execute('insert into counters (tags, value) values (?, ?) ', (record, data))
                print('        Added counters:{}'.format(record))
            elif val == 'strvalue':
                c.execute('insert into counters (tags, strvalue) values (?, ?) ', (record, data))
                print('        Added counters:{}'.format(record))
            else:
                print('        counters:{} failed'.format(record))
                
    if newdb == 1:
        c.execute('insert into system (tag, data) values (?, ?) ', ('dbinit', datenow))
        
    conn.commit()
    conn.close()

def main():

    parser = argparse.ArgumentParser(description='LAVA Controls Setup')
    subparsers = parser.add_subparsers()
    
#    parser_config = subparsers.add_parser('config')
#    parser_config.add_argument('-l', '--license', metavar = 'LICENSEKEY', help = 'Setup license key', nargs=1)
#    parser_config.set_defaults(func=configwork)

    parser_db = subparsers.add_parser('db')
    parser_db.add_argument('-f', '--force', dest = 'dbforce', action="store_true", help = 'Force DB Overwrite')
    parser_db.set_defaults(func=dbwork)
    
#    parser.set_default_subparser('db')

    args = parser.parse_args()
#    try:
#        args.func(args)
#    except:
#        pass
#        parser.print_help()
    if len(sys.argv) == 1:
        parser.print_help()
    else:
        args.func(args)

if __name__ == '__main__':
   main()