#!/usr/bin/env python
#---------------------------------------------------------------------
# Imports
#---------------------------------------------------------------------

import sys
import os
import sqlite3

from optparse import OptionParser, OptionGroup, SUPPRESS_HELP


#---------------------------------------------------------------------
# Database Functions
#---------------------------------------------------------------------

def createTables(cursor):
    cursor.execute('''
CREATE TABLE ZMETADATA(
    ZVERSION                INTEGER,
    ZDATABASETYPE           VARCHAR
);''')

    cursor.execute('''
CREATE TABLE ZMESSAGETYPES(
    Z_PK                INTEGER PRIMARY KEY,
    ZNAME               VARCHAR
);''')

    cursor.execute('''
CREATE TABLE ZBYPASSHISTORY(
    Z_PK                INTEGER PRIMARY KEY,
    ZTIMESTAMP          VARCHAR,
    ZMESSAGETYPE        INTEGER,
    ZUSER               VARCHAR,
    ZFAULTNAME          VARCHAR,
    ZBYPASSTIME         INTEGER
);''')
        




def fillMetadataTable(cursor):
    cursor.execute("""insert into ZMETADATA (
            ZVERSION,
            ZDATABASETYPE
        ) values (?, ?)""", (
            1,
            "MPS Bypass History Database"
        ))



def fillMessageTypesTable(cursor):
    cursor.execute("insert into ZMESSAGETYPES (ZNAME) values (\"Set\")")
    cursor.execute("insert into ZMESSAGETYPES (ZNAME) values (\"Clear\")")



def fillExampleDataForBypassHistoryTable(cursor, numberOfRows):
    from datetime import datetime, timedelta
    from random   import randint
    
    users  = ["snorum", "pkr", "rdh"]
    faults = ["Device 1",
              "Device 2",
              "Device 3",
              "Device 4",
              "Device 5",
              "Device 6"]
              
    bypassTime = datetime.utcnow()
    timeDelta  = timedelta(minutes=1)

    for i in range(numberOfRows):
        cursor.execute("""insert into ZBYPASSHISTORY (
            ZTIMESTAMP,
            ZMESSAGETYPE,
            ZUSER,
            ZFAULTNAME,
            ZBYPASSTIME 
        ) values (?, ?, ?, ?, ?)""", (
            bypassTime.isoformat(),
            1,
            users[randint(0, 2)],
            faults[randint(0, 5)],
            randint(1,60*48) * 60
        ))
        bypassTime += timeDelta




#---------------------------------------------------------------------
# Helper Functions
#---------------------------------------------------------------------


def handleOptions():
    # Create parse options
    usage   = "Usage: %prog [options]"                
    version = "%prog 1.0"
    
    parser = OptionParser(usage=usage, version=version)

    # parser.add_option("-v", "--verbose",   action="store_true",           dest="verbose",                    help="print verbose output")
    parser.add_option("-o", "--output",        action="store",      type="string", dest="outpath",            metavar="FILE",  help="SQLite3 output file path.  Default: bypassHistory.db")
    parser.add_option("-e", "--example_data",  action="store",      type="int",    dest="exampleDataCount",   metavar="ROWS",  help="Number of rows of example data to create.  Default: 0")

    # Defaults
    parser.set_defaults(#verbose           = False,
                        outpath            = "bypassHistory.db",
                        exampleDataCount   = 0)

    # Parse arguments
    (options, args) = parser.parse_args()    
    
    return (options, args)




#---------------------------------------------------------------------
# Main
#---------------------------------------------------------------------

def main():
    
    # Options
    (options, args) = handleOptions()
    
    # Delete outpath if it already exists
    output = os.path.normcase( options.outpath )
    
    if os.path.isfile(output):
        try:
            os.remove(output)
        except OSError, e:
            sys.stderr.write('Could not remove "' + output + '": ' + e.strerror + "\n")
            sys.exit(1)
    
    
    # Connect to database
    connection = sqlite3.connect(output)
    cursor     = connection.cursor()
    
    createTables(cursor)
    fillMetadataTable(cursor)
    fillMessageTypesTable(cursor)
    fillExampleDataForBypassHistoryTable(cursor, options.exampleDataCount)
        
    connection.commit()
    connection.close()



if __name__ == "__main__":
    main()

