#!/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,
    ZTIMECREATED        REAL
);''')

    cursor.execute('''
CREATE TABLE ZDEBOUNCETIME(
    Z_PK                INTEGER PRIMARY KEY,
    ZENUM               INTEGER,
    ZTIMEINMICROSECONDS INTEGER,
    ZNAME               VARCHAR
);''')
        
    cursor.execute('''
CREATE TABLE ZLINKNODETYPE(
    Z_PK                INTEGER PRIMARY KEY,
    ZENUM               INTEGER,
    ZNAME               VARCHAR
);''')
        
    cursor.execute('''
CREATE TABLE ZLINKNODE(
    Z_PK                INTEGER PRIMARY KEY,
    ZLINKNODEID         INTEGER,
    ZHOSTNAME           VARCHAR,
    ZRACKLOCATION       VARCHAR,
    ZRACKNUMBER         VARCHAR,
    ZRACKELEVATION      VARCHAR,
    ZPVDEVICETYPE       VARCHAR,
    ZPVAREA             VARCHAR,
    ZPVPOSITION         VARCHAR,
    ZTYPE               INTEGER
);''')

    cursor.execute('''
CREATE TABLE ZLINKNODEINPUTCHANNEL(
    Z_PK                INTEGER PRIMARY KEY,
    ZNAME               VARCHAR,
    ZLINKNODE           INTEGER,
    ZINPUTCARD          INTEGER,
    ZINPUTCARDCHANNEL   INTEGER,
    ZPVDEVICETYPE       VARCHAR,
    ZPVAREA             VARCHAR,
    ZPVPOSITION         VARCHAR,
    ZPVATTRIBUTE        VARCHAR,
    ZSTATE0             VARCHAR,
    ZSTATE1             VARCHAR,
    ZISAUTORECOVERABLE  INTEGER,
    ZDEBOUNCETIME       INTEGER,
    ZDEADMAN            INTEGER,
    ZCABLE              VARCHAR,
    ZPOSITIONX          REAL,
    ZPOSITIONY          REAL,
    ZPOSITIONZ          REAL,
    ZDESCRIPTION        VARCHAR,
    ZLOCATION           VARCHAR
);''')

    cursor.execute('''
CREATE TABLE ZEPICSFAULT(
    Z_PK                INTEGER PRIMARY KEY,
    ZFAULTNUMBER        INTEGER,
    ZNAME               VARCHAR,
    ZFAULTPV            VARCHAR,
    ZPVDEVICETYPE       VARCHAR,
    ZPVAREA             VARCHAR,
    ZPVPOSITION         VARCHAR,
    ZPVATTRIBUTE        VARCHAR,
    ZSTATE0             VARCHAR,
    ZSTATE1             VARCHAR,
    ZISAUTORECOVERABLE  INTEGER,
    ZPOSITIONX          REAL,
    ZPOSITIONY          REAL,
    ZPOSITIONZ          REAL,
    ZDESCRIPTION        VARCHAR,
    ZLOCATION           VARCHAR
);''')



def fillMetadataTable(cursor):
    from time import time
    
    cursor.execute(""" insert into ZMETADATA (
            ZVERSION,
            ZDATABASETYPE,
            ZTIMECREATED
        ) values (?, ?, ?)""", (
            1,
            "MPS Configuration Database",
            time()
        ))


def fillDebounceTable(cursor):
    debounceTimes = (
        (0, 10,      "10 us"  ),
        (1, 100,     "100 us" ),
        (2, 1000,    "1 ms"   ),
        (3, 10000,   "10 ms"  ),
        (4, 100000,  "100 ms" ),
        (5, 500000,  "500 ms" ),
        (6, 1000000, "1 s"    ),
        (7, 5000000, "5 s"    ),
    )
    cursor.executemany("""insert into ZDEBOUNCETIME (
            ZENUM,
            ZTIMEINMICROSECONDS,
            ZNAME
        ) values (?, ?, ?)""",
            debounceTimes
        )

def fillLinkNodeTypeTable(cursor):
    linkNodeTypes = (
        (0, "Mitigation Control" ),
        (1, "BLM"                ),
        (2, "BYKIK"              ),
        (3, "MPG Control"        ),
        (4, "PIC"                ),
    )
    cursor.executemany("""insert into ZLINKNODETYPE (
            ZENUM,
            ZNAME
        ) values (?, ?)""",
            linkNodeTypes
        )


def fillLinkNodeTable(cursor, linkNodeIds):

    queryList = {
                    'mitigation': { 'query': 'select Z_PK from ZLINKNODETYPE where ZNAME = "Mitigation Control";',  'value': 0 },
                    'pic':        { 'query': 'select Z_PK from ZLINKNODETYPE where ZNAME = "PIC";',                 'value': 0 }
                }

    for query in queryList.items():
        try:
            cursor.execute(query[1]['query'])
            query[1]['value'] = cursor.fetchone()[0]
        except sqlite3.Error, e:
            print "fillLinkNodeTable:", e.args[0]
            sys.exit(1)

    for i in linkNodeIds:
        cursor.execute("""insert into ZLINKNODE (
            ZLINKNODEID,
            ZTYPE,
            ZHOSTNAME,
            ZPVDEVICETYPE,
            ZPVAREA,
            ZPVPOSITION
        ) values (?, ?, ?, ?, ?, ?)""",(
            i,
            queryList['mitigation']['value'],
            "eioc-xxx-ln%02x" % i,
            "LINKNODE",
            "TEST",
            str(i)
        ))


def fillInputTable(cursor):
    
    linkNodeIdsQuery    = 'select Z_PK, ZLINKNODEID from ZLINKNODE;'

    queryList = {
                    'debounce':   { 'query': 'select Z_PK from ZDEBOUNCETIME where ZTIMEINMICROSECONDS = 10;', 'value': 0 },
                }

    for query in queryList.items():
        try:
            cursor.execute(query[1]['query'])
            query[1]['value'] = cursor.fetchone()[0]
        except sqlite3.Error, e:
            print "fillLinkNodeTable:", e.args[0]
            sys.exit(1)

    # Make entries for every channel of every card of every link node
    cursor.execute(linkNodeIdsQuery)
    lnPks = cursor.fetchall()
    
    pos = 1.0;
    
    for ln in lnPks:
        lnpk = ln[0]
        for card in range(1, 6 + 1):
            for chan in range (1, 16 + 1):
                cursor.execute("""insert into ZLINKNODEINPUTCHANNEL (
                        ZNAME,
                        ZLINKNODE,
                        ZINPUTCARD,
                        ZINPUTCARDCHANNEL,
                        ZPVDEVICETYPE,
                        ZPVAREA,
                        ZPVPOSITION,
                        ZPVATTRIBUTE,
                        ZSTATE0,
                        ZSTATE1,
                        ZISAUTORECOVERABLE,
                        ZDEBOUNCETIME,
                        ZDEADMAN,
                        ZPOSITIONX,
                        ZPOSITIONY,
                        ZPOSITIONZ
                    ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", (
                        "LN_%03d_%d_%02d" % (ln[1], card, chan),
                        lnpk,
                        card,
                        chan,
                        "LN%03d"   % ln[1],
                        "CARD%d"   % card,
                        "CHAN%02d" % chan,
                        "STATE",
                        "FAULT",
                        "OK",
                        1,
                        queryList['debounce']['value'],
                        1,
                        pos,
                        pos,
                        pos
                    ))
                pos += 1.0


def fillEpicsFaultTable(cursor):
    epicsFaults = (
        (  0, "MC00_RTDS_16"     ,   "MC00:RTDS:16:TEMP"    ,   "RTDS"  ,  "MC00"  ,  "16"    ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  0   ,  35  ,  70  ),
        (  1, "MC00_RTDS_33"     ,   "MC00:RTDS:33:TEMP"    ,   "RTDS"  ,  "MC00"  ,  "33"    ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  1   ,  36  ,  71  ),
        (  2, "MC00_RTDS_45"     ,   "MC00:RTDS:45:TEMP"    ,   "RTDS"  ,  "MC00"  ,  "45"    ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  2   ,  37  ,  72  ),
        (  3, "MC00_RTDS_46"     ,   "MC00:RTDS:46:TEMP"    ,   "RTDS"  ,  "MC00"  ,  "46"    ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  3   ,  38  ,  73  ),
        (  4, "MC00_RTDS_101"    ,   "MC00:RTDS:101:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "101"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  4   ,  39  ,  74  ),
        (  5, "MC00_RTDS_119"    ,   "MC00:RTDS:119:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "119"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  5   ,  40  ,  75  ),
        (  6, "MC00_RTDS_145"    ,   "MC00:RTDS:145:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "145"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  6   ,  41  ,  76  ),
        (  7, "MC00_RTDS_150"    ,   "MC00:RTDS:150:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "150"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  7   ,  42  ,  77  ),
        (  8, "MC00_RTDS_155"    ,   "MC00:RTDS:155:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "155"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  8   ,  43  ,  78  ),
        (  9, "MC00_RTDS_165"    ,   "MC00:RTDS:165:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "165"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  9   ,  44  ,  79  ),
        ( 10, "MC00_RTDS_175"    ,   "MC00:RTDS:175:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "175"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  10  ,  45  ,  80  ),
        ( 11, "MC00_RTDS_185"    ,   "MC00:RTDS:185:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "185"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  11  ,  46  ,  81  ),
        ( 12, "MC00_RTDS_186"    ,   "MC00:RTDS:186:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "186"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  12  ,  47  ,  82  ),
        ( 13, "MC00_RTDS_195"    ,   "MC00:RTDS:195:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "195"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  13  ,  48  ,  83  ),
        ( 14, "MC00_RTDS_196"    ,   "MC00:RTDS:196:TEMP"   ,   "RTDS"  ,  "MC00"  ,  "196"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  14  ,  49  ,  84  ),
        ( 15, "MC00_RTDS_1010"   ,   "MC00:RTDS:1010:TEMP"  ,   "RTDS"  ,  "MC00"  ,  "1010"  ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  15  ,  50  ,  85  ),
        ( 16, "MC00_RTDS_1011"   ,   "MC00:RTDS:1011:TEMP"  ,   "RTDS"  ,  "MC00"  ,  "1011"  ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  16  ,  51  ,  86  ),
        ( 17, "MC00_RTDS_1024"   ,   "MC00:RTDS:1024:TEMP"  ,   "RTDS"  ,  "MC00"  ,  "1024"  ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  17  ,  52  ,  87  ),
        ( 18, "MC00_RTDS_1025"   ,   "MC00:RTDS:1025:TEMP"  ,   "RTDS"  ,  "MC00"  ,  "1025"  ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  18  ,  53  ,  88  ),
        ( 19, "MC00_RTDS_1020"   ,   "MC00:RTDS:1020:TEMP"  ,   "RTDS"  ,  "MC00"  ,  "1020"  ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  19  ,  54  ,  89  ),
        ( 20, "MC00_RTDS_1021"   ,   "MC00:RTDS:1021:TEMP"  ,   "RTDS"  ,  "MC00"  ,  "1021"  ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  20  ,  55  ,  90  ),
        ( 21, "MC00_RTDS_1030"   ,   "MC00:RTDS:1030:TEMP"  ,   "RTDS"  ,  "MC00"  ,  "1030"  ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  21  ,  56  ,  91  ),
        ( 22, "MC00_RTDS_1031"   ,   "MC00:RTDS:1031:TEMP"  ,   "RTDS"  ,  "MC00"  ,  "1031"  ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  22  ,  57  ,  92  ),
        ( 23, "CA11_RTDS_641"    ,   "CA11:RTDS:641:TEMP"   ,   "RTDS"  ,  "CA11"  ,  "641"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  23  ,  58  ,  93  ),
        ( 24, "CA11_RTDS_642"    ,   "CA11:RTDS:642:TEMP"   ,   "RTDS"  ,  "CA11"  ,  "642"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  24  ,  59  ,  94  ),
        ( 25, "CA11_RTDS_653"    ,   "CA11:RTDS:653:TEMP"   ,   "RTDS"  ,  "CA11"  ,  "653"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  25  ,  60  ,  95  ),
        ( 26, "CA11_RTDS_654"    ,   "CA11:RTDS:654:TEMP"   ,   "RTDS"  ,  "CA11"  ,  "654"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  26  ,  61  ,  96  ),
        ( 27, "CA11_RTDS_671"    ,   "CA11:RTDS:671:TEMP"   ,   "RTDS"  ,  "CA11"  ,  "671"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  27  ,  62  ,  97  ),
        ( 28, "CA11_RTDS_672"    ,   "CA11:RTDS:672:TEMP"   ,   "RTDS"  ,  "CA11"  ,  "672"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  28  ,  63  ,  98  ),
        ( 29, "AB01_RTDS_993"    ,   "AB01:RTDS:993:TEMP"   ,   "RTDS"  ,  "AB01"  ,  "993"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  29  ,  64  ,  99  ),
        ( 30, "AB01_RTDS_994"    ,   "AB01:RTDS:994:TEMP"   ,   "RTDS"  ,  "AB01"  ,  "994"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  30  ,  65  ,  100 ),
        ( 31, "AB01_RTDS_995"    ,   "AB01:RTDS:995:TEMP"   ,   "RTDS"  ,  "AB01"  ,  "995"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  31  ,  66  ,  101 ),
        ( 32, "AB01_RTDS_996"    ,   "AB01:RTDS:996:TEMP"   ,   "RTDS"  ,  "AB01"  ,  "996"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  32  ,  67  ,  102 ),
        ( 33, "AB01_RTDS_997"    ,   "AB01:RTDS:997:TEMP"   ,   "RTDS"  ,  "AB01"  ,  "997"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  33  ,  68  ,  103 ),
        ( 34, "AB01_RTDS_998"    ,   "AB01:RTDS:998:TEMP"   ,   "RTDS"  ,  "AB01"  ,  "998"   ,  "TEMPOK"  ,  "FAULT"  ,  "OK"  ,  1  ,  34  ,  69  ,  104 ),
    )
    cursor.executemany("""insert into ZEPICSFAULT (
            ZFAULTNUMBER,
            ZNAME,
            ZFAULTPV,
            ZPVDEVICETYPE,
            ZPVAREA,
            ZPVPOSITION,
            ZPVATTRIBUTE,
            ZSTATE0,
            ZSTATE1,
            ZISAUTORECOVERABLE,
            ZPOSITIONX,
            ZPOSITIONY,
            ZPOSITIONZ
        ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""",
            epicsFaults
        )


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

def rangeToString(theRange):
    if len(theRange) == 1:
        return str(theRange[0]).strip()
    if len(theRange) == 2:
        return str(theRange[0]).strip() + "-" + str(theRange[1]).strip()

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

    # parser.add_option("-v", "--verbose",   action="store_true",           dest="verbose",                    help="print verbose output")
    parser.add_option("-o", "", action="store", type="string", dest="outpath",   metavar="FILE",  help="SQLite3 output file path.  Default: testdb.mps")
    parser.add_option("-l", "", action="store", type="string", dest="linknodes", metavar="RANGE", help="Range of Link Nodes.  Ex. \"1-4, 6, 8\".  Default: 10-19")

    # Defaults
    parser.set_defaults(#verbose   = False,
                        outpath   = "testdb.mps",
                        linknodes = "10-19")

    # Parse arguments
    (options, args) = parser.parse_args()
    
    # Get range of Link Nodes
    linkNodeSet = set()
    linkNodeRanges = options.linknodes.split(",")
    for linkNodeRange in linkNodeRanges:
        boundaries = linkNodeRange.split("-")        
        try:
            if len(boundaries) == 1:
                linkNodeSet.add(int(boundaries[0]))
            if len(boundaries) == 2:
                for i in range(int(boundaries[0]), int(boundaries[1]) + 1):
                    linkNodeSet.add(i)
        except ValueError, e:
            sys.stderr.write("Bad value, \"" + rangeToString(boundaries) + "\", in Link Node range\n")
            sys.exit(1)

    options.linkNodeIds = sorted(list(linkNodeSet))
    
    
    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)
    fillDebounceTable(cursor)
    fillLinkNodeTypeTable(cursor)
    fillLinkNodeTable(cursor, options.linkNodeIds)
    fillInputTable(cursor)
    fillEpicsFaultTable(cursor)
        
    connection.commit()
    connection.close()



if __name__ == "__main__":
    main()

