#!/usr/bin/env python

import sys
import os
import sqlite3

__all__ = ['FaultInput', 'insert_fault_inputs']

class FaultInput(object):
    """Represents a hard-wired Link Node fault input."""
    def __init__(self, lnid, card, channel, pvdevtype, pvarea, pvpos, pvattr, devicename='', faultname='FAULT', faultedname='IS_FAULTED', okname='IS_OK'):
        self.lnid = lnid
        self.card = card
        self.channel = channel
        
        self.pvdevtype = pvdevtype
        self.pvarea = pvarea
        self.pvpos = pvpos
        self.pvattr = pvattr
        
        self.faultname = faultname
        self.faultedname = faultedname
        self.okname = okname
        
        self.devarea = pvarea

        if not devicename:
            self.devicename = '%s_%s_%s_%s' % (pvdevtype, pvarea, pvpos, pvattr)


def insert_fault_inputs(cursor, input_list, verbose=False):
    """Inserts list of fault inputs into database cursor."""
    
    for device in input_list:        
        lnid       = int(device.lnid)
        card       = int(device.card)
        channel    = int(device.channel)
        
        pvdevtype  = clean_string(device.pvdevtype)
        pvarea     = clean_string(device.pvarea)
        pvpos      = clean_string(device.pvpos)
        pvattr     = clean_string(device.pvattr)
        
        devname    = clean_string(device.devicename)
        
        devarea    = device.devarea
        faultname  = clean_string(device.faultname)
        okstate    = clean_string(device.okname)
        faultstate = clean_string(device.faultedname)

        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)



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 clean_string(str):
    import re
    str = str.upper()
    str = re.sub("[^a-zA-Z0-9]", "_", str)
    return str



def main():
    pass


if __name__ == "__main__":
    main()

