#!/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')

user_version = 3

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
    if newdb == 1:
        datenow = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        conn = sqlite3.connect(database)
        c = conn.cursor()
        c.execute('create table if not exists system ( \
                    tag text, \
                    data text)')
        c.execute('create table if not exists displays ( \
                    display_id integer primary key autoincrement, \
                    model text, \
                    serial text, \
                    mac_address text, \
                    ip text, \
                    f1 text, \
                    f2 text, \
                    f3 text, \
                    f4 text, \
                    id int, \
                    lava_id text, \
                    power_state int, \
                    input text, \
                    lastinput text, \
                    volume int, \
                    osd int, \
                    t1 text, \
                    t2 text, \
                    t3 text, \
                    tile_state int, \
                    tile_h text, \
                    tile_v text, \
                    tile_p text, \
                    tile_id int \
                    )')
        c.execute('create table if not exists tiles ( \
                    tile_key integer primary key autoincrement, \
                    tileid int, \
                    member int, \
                    size text, \
                    pos int \
                    dpchain int \
                    )')
        c.execute('create table if not exists counters ( \
                    tags text, \
                    value int, \
                    strvalue text, \
                    td timestamp \
                    )')
        c.execute('pragma user_version = {}'.format(user_version))
    else:
        conn = sqlite3.connect(database)
        c = conn.cursor()
        c.execute('pragma user_version')
        row = c.fetchone()
        cur_user_version = row[0]
        while cur_user_version < user_version:
            print('        Upgrading database to schema version {}'.format(cur_user_version + 1))
            if cur_user_version == 0:
                c.execute('pragma user_version = 1')
            if cur_user_version == 1:
                c.execute('alter table monitors rename to displays')
                c.execute('pragma user_version = 2')
            if cur_user_version == 2:
                c.execute('alter table displays add column lava_id text')
                c.execute('alter table displays add column t1 text')
                c.execute('alter table displays add column t2 text')
                c.execute('alter table displays add column t3 text')
                c.execute('pragma user_version = 3')
            c.execute('pragma user_version')
            row = c.fetchone()
            cur_user_version = row[0]

    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:
            if val == 'value':
                c.execute('insert into counters (tags, value) values (?, ?) ', (record, data))
            elif val == 'strvalue':
                c.execute('insert into counters (tags, strvalue) values (?, ?) ', (record, data))
            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()