#!/usr/bin/env python

import sys
import csv
import os
import sqlite3
from optparse import OptionParser, OptionGroup, SUPPRESS_HELP

__all__ = ['addCard', 'addChannel', 'addDeviceArea', 'addDeviceDetailLinks', 'addDeviceDetails', 'addDevices', 'addFaults', 'addLinkNode', 'addPvArea', 'addPvDeviceType', 'cleanString']

def addPvDeviceType(cursor, pvdevtype, verbose):
    # Test to see if it exists
    query = 'select count(*) from ZPVDEVICETYPE where ZNAME = ?'
    cursor.execute(query, (pvdevtype,))
    count = int(cursor.fetchone()[0])
    
    # If it doesn't, add it
    if count == 0:
        if verbose:
            print "Adding new PV Device Type \"%s\" to database" % pvdevtype
        try:
            query = """insert into ZPVDEVICETYPE (Z_OPT, Z_ENT, ZNAME)
                                   values        (    1,    15,     ?);"""
            cursor.execute(query, (pvdevtype,))
        except sqlite3.Error, e:
            print "addPvDeviceType:", e.args[0]




def addPvArea(cursor, pvarea, verbose):
    # Test to see if it exists
    query = 'select count(*) from ZPVAREA where ZNAME = ?'
    cursor.execute(query, (pvarea,))
    count = int(cursor.fetchone()[0])
    
    # If it doesn't, add it
    if count == 0:
        if verbose:
            print "Adding new PV area \"%s\" to database" % pvarea
        try:
            query = """insert into ZPVAREA (Z_OPT, Z_ENT, ZNAME)
                                   values  (    2,    14,     ?);"""
            cursor.execute(query, (pvarea,))
        except sqlite3.Error, e:
            print "addPvArea:", e.args[0]



def addDeviceArea(cursor, devarea, verbose):
    # Test to see if it exists
    query = 'select count(*) from ZDEVICEAREA where ZNAME = ?'
    cursor.execute(query, (devarea,))
    count = int(cursor.fetchone()[0])
    
    # If it doesn't, add it
    if count == 0:
        if verbose:
            print "Adding new device area \"%s\" to database" % devarea
        try:
            query = """insert into ZDEVICEAREA (Z_OPT, Z_ENT, ZNAME)
                                   values      (    1,     3,     ?);"""
            cursor.execute(query, (devarea,))
        except sqlite3.Error, e:
            print "addDeviceArea:", e.args[0]



def addLinkNode(cursor, lnid, verbose):
    # Test to see if it exists
    query = 'select count(*) from ZLINKNODE where ZLINKNODEID = ?'
    cursor.execute(query, (lnid,))
    count = int(cursor.fetchone()[0])
    
    # If it doesn't, add it
    if count == 0:
        if verbose:
            print "Adding new Link Node with ID %d to database" % lnid
        try:
            query = """insert into ZLINKNODE (Z_OPT, Z_ENT, ZLINKNODEID, ZTYPE, ZRACK, ZHOSTNAME, ZRACKELEVATION)
                                   values    (    4,     8,           ?, (select Z_PK from ZLINKNODETYPE where ZENUM == 0), 1, "eioc-xxxx-mpxx", 1);"""
            cursor.execute(query, (lnid,))
        except sqlite3.Error, e:
            print "addLinkNode:", e.args[0]




def addCard(cursor, lnid, card, verbose):
    # Make sure Link Node exists
    addLinkNode(cursor, lnid,                verbose)

    # Test to see if it exists
    query = """select
                    count(*)
               from
                    ZLINKNODECARD card left outer join
                    ZLINKNODE ln on card.ZLINKNODE == ln.Z_PK
               where
                    ln.ZLINKNODEID == ? and
                    card.ZNUMBER   == ?"""
                    
    cursor.execute(query, (lnid, card, ))
    count = int(cursor.fetchone()[0])
    
    # If it doesn't, add it
    if count == 0:
        if verbose:
            print "Adding new Link Node Card %d.%d to database" % (lnid, card)
        try:
            query = """insert into ZLINKNODECARD (Z_OPT, Z_ENT, ZNUMBER, ZLINKNODE, ZTYPE)
                                   values        (    1,     9,       ?, (select Z_PK from ZLINKNODE where ZLINKNODEID = ?), (select Z_PK from ZLINKNODECARDTYPE where ZNAME == 'Input'));"""
            cursor.execute(query, (card, lnid,))
        except sqlite3.Error, e:
            print "addCard:", e.args[0]




def addChannel(cursor, lnid, card, channel, verbose):

    # Make sure Link Node Card exists
    addCard    (cursor, lnid, card,          verbose)

    # Test to see if it exists
    query = """select
                    count(*)
               from
                    ZLINKNODE          ln                                inner join
                    ZLINKNODECARD    card on card.ZLINKNODE ==   ln.Z_PK inner join
                    ZLINKNODECHANNEL lnch on lnch.ZCARD     == card.Z_PK
               where
                    ln.ZLINKNODEID == ? and
                    card.ZNUMBER   == ? and
                    lnch.ZNUMBER   == ?"""
                    
    cursor.execute(query, (lnid, card, channel))
    count = int(cursor.fetchone()[0])
    
    # If it doesn't, add it
    if count == 0:
        if verbose:
            print "Adding new Link Node Channel %d.%d.%d to database" % (lnid, card, channel)
        try:
            query = """insert into ZLINKNODECHANNEL (Z_OPT,
                                                     Z_ENT,
                                                     ZDEBOUNCETIME,
                                                     ZTYPE,
                                                     ZCARD,
                                                     ZNUMBER)
                                   values           (1,
                                                     11,
                                                     (select Z_PK from ZDEBOUNCETIME        where ZENUM = 0),
                                                     (select Z_PK from ZLINKNODECHANNELTYPE where ZENUM = 0),
                                                     (select Z_PK from ZLINKNODECARD card where card.ZLINKNODE == (select Z_PK from ZLINKNODE where ZLINKNODEID = ?) and card.ZNUMBER == ?),
                                                     ?);"""
            cursor.execute(query, (lnid, card, channel))
        except sqlite3.Error, e:
            print "addChannel:", e.args[0]


def addDeviceDetails(cursor, lnid, card, channel, verbose):
    query = """insert into ZDEVICEDETAILS (Z_OPT, Z_ENT, ZLINKNODECHANNEL) values (1, 4, (select Z_PK from ZLINKNODECHANNEL where ZCARD = (select Z_PK from ZLINKNODECARD where ZLINKNODE = (select Z_PK from ZLINKNODE where ZLINKNODEID = ?) and ZNUMBER == ?) and ZNUMBER = ?));"""

    try:
        cursor.execute(query, (lnid, card, channel))
    except sqlite3.Error, e:
        print "addDeviceDetails:", e.args[0]
        

def addDevices (cursor, lnid, card, channel, devname, devarea, pvdevtype, pvarea, pvpos, verbose):
    addPvDeviceType(cursor, pvdevtype, verbose)
    addPvArea      (cursor, pvarea,    verbose)
    addDeviceArea  (cursor, devarea,   verbose)
    query = """
        insert into ZDEVICE (Z_OPT,
                             Z_ENT,
                             ZDETAILS,
                             ZPVDEVICETYPE,
                             ZPVAREA,
                             ZPVPOSITION,
                             ZDEVICEAREA,
                             ZTYPE,
                             ZPOSITIONY,
                             ZPOSITIONX,
                             ZPOSITIONZ,
                             ZNAME)
                     values (1,
                             2,
                             (select Z_PK from ZDEVICEDETAILS where ZLINKNODECHANNEL =
                                (select Z_PK from ZLINKNODECHANNEL where ZCARD =
                                (select Z_PK from ZLINKNODECARD where ZLINKNODE =
                                (select Z_PK from ZLINKNODE where ZLINKNODEID = ?)
                                 and ZNUMBER == ?)
                                 and ZNUMBER = ?)),
                             (select Z_PK from ZPVDEVICETYPE where ZNAME = ?),
                             (select Z_PK from ZPVAREA       where ZNAME = ?),
                             ?,
                             (select Z_PK from ZDEVICEAREA where ZNAME = ?),
                             (select Z_PK from ZDEVICETYPE where ZNAME = 'Link Node Channel'),
                             0,
                             0,
                             0,
                             ?);
        """
    try:
        cursor.execute(query, (lnid, card, channel, pvdevtype, pvarea, pvpos, devarea, devname))
    except sqlite3.Error, e:
        print "addDevices:", e.args[0]


 

def addFaults(cursor, devname, faultname, pvattr, okstate, faultstate):
    query = """
        insert into ZFAULT (Z_ENT,
                            Z_OPT,
                            ZAUTORECOVERABLE,
                            ZDEVICE,
                            ZNAME,
                            ZPVATTRIBUTE,
                            ZOKSTATENAME,
                            ZFAULTEDSTATENAME)
                    values (7,
                            1,
                            1,
                            (select Z_PK from ZDEVICE where ZNAME = ?),
                            ?,
                            ?,
                            ?,
                            ?);
        """
    try:
        cursor.execute(query, (devname, faultname, pvattr, okstate, faultstate))
    except sqlite3.Error, e:
        print "addFaults:", e.args[0]
 

def addDeviceDetailLinks(cursor, verbose):
    query = """update ZDEVICEDETAILS set ZDEVICE = (select Z_PK from ZDEVICE dev where dev.ZDETAILS = ZDEVICEDETAILS.Z_PK);"""
    try:
        cursor.execute(query)
    except sqlite3.Error, e:
        print "addDeviceDetailLinks:", e.args[0]



def cleanString(str):
    import re
    str = str.upper()
    str = re.sub("[^a-zA-Z0-9]", "_", str)
    return str
    

def addRow(cursor, rowdata, verbose):
    
    if len(rowdata) < 12:
        return
    
    # Fix up data
    lnid       = int(rowdata[0])
    card       = int(rowdata[1])
    channel    = int(rowdata[2])
    devname    = cleanString(rowdata[3])
    faultname  = cleanString(rowdata[4])
    pvdevtype  = cleanString(rowdata[5])
    pvarea     = cleanString(rowdata[6])
    pvpos      = cleanString(rowdata[7])
    pvattr     = cleanString(rowdata[8])
    okstate    = cleanString(rowdata[9])
    faultstate = cleanString(rowdata[10])
    devarea    = rowdata[11]


    if devname[0].isdigit():
        devname = "FLT_" + devname

    if len(faultname) == 0:
        faultname = "STATE"

    if len(okstate) == 0:
        okstate = "OK"

    if len(faultstate) == 0 or (faultstate == okstate):
        faultstate = "IS_NOT_" + okstate
        
    if len(pvdevtype) == 0:
        pvdevtype = "DEV"
    
    if len(pvarea) == 0:
        pvarea = "XXX"
        
    if len(pvpos) == 0:
        pvpos = "0"
    
    if len(devarea) == 0:
        devarea = "Unknown"


    addChannel      (cursor, lnid, card, channel, verbose)
    addDeviceDetails(cursor, lnid, card, channel, verbose)
    addDevices      (cursor, lnid, card, channel,
                     devname, devarea,
                     pvdevtype, pvarea, pvpos, verbose)
    addFaults       (cursor, devname, faultname, pvattr, okstate, faultstate)
    addDeviceDetailLinks(cursor, verbose)

    if verbose:
        print "Added Channel %2d %2d %2d -- %s %s" % (lnid, card, channel, devname, faultname)
        None
    





#---------------------------------------------------------------------
# Options
#---------------------------------------------------------------------

def handleOptions():
    # Create parse options
    usage   = "Usage: %prog -m database -c csvfile -t type"

    version = "%prog 0.1"
    parser = OptionParser(usage=usage, version=version)

    parser.add_option("-v", "--verbose",           action="store_true",  dest="verbose",            help="print verbose output")
    parser.add_option("-l", "--listtypes",         action="store_true",  dest="verbose",            help="list possible CSV types")
    
    group = OptionGroup(parser, "Arguments", "")
    group.add_option("-m", "--mps",  action="store", type="string", dest="mpsfilename",  metavar="FILE", help="path to .mps file")
    group.add_option("-c", "--csv",  action="store", type="string", dest="csvfilename",  metavar="FILE", help="path to CSV file")
    group.add_option("-t", "--type", action="store", type="string", dest="csvtype",      metavar="TYPE", help="CSV file type")
    parser.add_option_group(group)

    # Defaults
    parser.set_defaults(verbose     = False,
                        listtypes   = False,
                        mpsfilename = None,
                        csvfilename = None,
                        csvtype     = "blah")

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

    if (options.mpsfilename == None or 
        options.csvfilename == None or 
        options.csvtype     == None) and (options.listtypes == False):
        parser.error("incorrect number of arguments")
        
    return (options, args)

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

def main():

    (options, args) = handleOptions()

    mpsfilename = options.mpsfilename
    csvfilename = options.csvfilename
    verbose     = options.verbose
    
    # Open csv file
    csvfile     = open(csvfilename, "rU")
    dialect     = csv.excel
    reader      = csv.reader(csvfile, dialect)
    
    
    # Connect to database
    connection             = sqlite3.connect(mpsfilename)
    connection.row_factory = sqlite3.Row
    cursor                 = connection.cursor()

    
    try:
        for row in reader:
            addRow(cursor, row, verbose)
    except csv.Error, e:
        sys.exit('file %s, line %d: %s' % (filename, reader.line_num, e))


    csvfile.close()
    connection.commit()
    connection.close()


if __name__ == "__main__":
    main()

