#!/usr/bin/env python

import sys
import os
import sqlite3

__all__ = ['FaultInput', 'insert_fault_inputs']


def table_zent_zopt(mpl_cursor, table):
    """
    Returns CoreData (Z_ENT, Z_OPT) to attempt to keep CoreData sane.
    Z_ENT, Z_OPT columns and this function should be removed once a
    non-CoreData editor is available.
    """
    mpl_cursor.execute('select Z_ENT, Z_OPT from {0}'.format(table))
    return mpl_cursor.fetchone()


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', autorecoverable=False, description=''):
        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.autorecoverable = autorecoverable
        self.description = description
        
        self.devarea = pvarea

    @property
    def devicename(self):
        return  '_'.join((self.pvdevtype, self.pvarea, self.pvpos, self.pvattr))
    
    def __str__(self):
        format = (
            '{0.lnid:>4}'
            '{0.card:>4}'
            '{0.channel:>4}   '
            '{0.pvdevtype:>6}'
            '{0.pvarea:>6}'
            '{0.pvpos:>9}'
            '{0.pvattr:>16}   '
            '{0.faultname:>16}   '
            '{0.faultedname:>16}   '
            '{0.okname:>16}   '
            '{0.autorecoverable:>4}   '
            '{0.description}'
        )
        return format.format(self)

class ExternalPvFaultInput(object):
    """
    Represents an external PV fault input.
    """
    def __init__(self, external_pv, pvdevtype, pvarea, pvpos, pvattr, devicename='', faultname='FAULT', faultedname='IS_FAULTED', okname='IS_OK', autorecoverable=False, description=''):
        self.external_pv = external_pv
        
        self.pvdevtype = pvdevtype
        self.pvarea = pvarea
        self.pvpos = pvpos
        self.pvattr = pvattr
        
        self.faultname = faultname
        self.faultedname = faultedname
        self.okname = okname
        self.autorecoverable = autorecoverable
        self.description = description
        
        self.devarea = pvarea

    @property
    def devicename(self):
        return  '_'.join((self.pvdevtype, self.pvarea, self.pvpos, self.pvattr))
    
    def __str__(self):
        format = (
            '{0.external_pv:20}'
            '{0.pvdevtype:>6}'
            '{0.pvarea:>6}'
            '{0.pvpos:>9}'
            '{0.pvattr:>16}   '
            '{0.faultname:>16}   '
            '{0.faultedname:>16}   '
            '{0.okname:>16}   '
            '{0.autorecoverable:>4}   '
            '{0.description}'
        )
        return format.format(self)
    


def insert_fault_inputs(cursor, input_list, verbose=False):
    """
    Inserts list of fault inputs into database cursor.
    """
    
    for device in input_list:
        try:
            lnid       = int(device.lnid)
            card       = int(device.card)
            channel    = int(device.channel)
            is_link_node_channel_device = True
        except AttributeError:
            is_link_node_channel_device = False
            pass
        
        try:
            external_pv = device.external_pv
            is_external_pv_device = True
        except AttributeError:
            is_external_pv_device = False
            pass
        
        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)
        #autorecoverable = 1 if device.autorecoverable else 0
        if device.autorecoverable == True:
            autorecoverable = 1 
        else:
            autorecoverable = 0
        #print  'device.autorecoverable', autorecoverable  
        description = device.description

        if is_link_node_channel_device:
            addChannel(
                cursor,
                lnid,
                card,
                channel,
                verbose
            )
            addDeviceDetails(
                cursor,
                lnid,
                card,
                channel,
                verbose
            )
            addDevices(
                cursor,
                lnid,
                card,
                channel,
                devname,
                devarea,
                pvdevtype,
                pvarea,
                pvpos,
                verbose
            )
        
        if is_external_pv_device:
            addExternalPv(
                cursor,
                external_pv,
                verbose
            )
            addDeviceDetails_ExternalPv(
                cursor,
                external_pv,
                verbose
            )
            addDevice_ExternalPv(
                cursor,
                external_pv,
                devname,
                devarea,
                pvdevtype,
                pvarea,
                pvpos,
                verbose
            )
            
        addFaults(
            cursor,
            devname,
            faultname,
            pvattr,
            okstate,
            faultstate,
            autorecoverable,
            description
        )
        addDeviceDetailLinks(
            cursor,
            verbose
        )
    
        if verbose:
            if is_link_node_channel_device:
                print 'Added Channel %2d %2d %2d -- %s %s' % (lnid, card, channel, devname, faultname)
            if is_external_pv_device:
                print 'Added External PV {0} -- {1} {2}'.format(external_pv, devname, faultname)

def addExternalPv(cursor, external_pv, verbose):
    # Test to see if it exists
    query = 'select count(*) from ZEXTERNALPV where ZNAME = ?'
    cursor.execute(query, (external_pv,))
    count = int(cursor.fetchone()[0])

    # If it doesn't, add it
    if count == 0:
        if verbose:
            print 'Adding new External PV "%s" to database' % external_pv
        
        (zent, zopt) = table_zent_zopt(cursor, 'ZEXTERNALPV')
        
        try:
            query = """
                insert into ZEXTERNALPV (
                    Z_ENT,
                    Z_OPT,
                    ZNAME
                ) values (
                     ?,
                     ?,
                     ?);
            """
            cursor.execute(query, (zent, zopt, external_pv))
        except sqlite3.Error, e:
            print "addExternalPv:", e.args[0]


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 addDeviceDetails_ExternalPv(cursor, external_pv, verbose):
    query = """
        insert into ZDEVICEDETAILS
        (
            Z_OPT,
            Z_ENT,
            ZEPICSFAULT
        )
        values
        (
            1,
            4,
            (
                select
                    Z_PK
                from
                    ZEXTERNALPV
                where
                    ZNAME = ?
            )
        );"""

    try:
        cursor.execute(query, [external_pv])
    except sqlite3.Error, e:
        print "addDeviceDetails_ExternalPv:", 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 addDevice_ExternalPv (cursor, external_pv, 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,
            ZNAME,
            ZPOSITIONY,
            ZPOSITIONX,
            ZPOSITIONZ
        ) values (
            1,
            2,
            (
                select
                    Z_PK
                from
                    ZDEVICEDETAILS
                where
                    ZEPICSFAULT = (
                        select
                            Z_PK
                        from
                            ZEXTERNALPV
                        where
                            ZNAME = ?
                    )
            ),
            (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 = 'External EPICS Fault'),
            ?,
            0,
            0,
            0
        );
    """
    try:
        cursor.execute(query, (external_pv, pvdevtype, pvarea, pvpos, devarea, devname))
    except sqlite3.Error, e:
        print "addDevices:", e.args[0]


def addFaults(cursor, devname, faultname, pvattr, okstate, faultstate, autorecoverable, description, verbose=True):

    """
    Test to see if it exists 2010/08/05 dayle
    The exists test in this case is that a record with both ZDEVICE and ZNAME
    matching our intended insert. If we find any records then throw an
    exception to bubble out
    """
    query = 'select count(*) from ZFAULT where ZDEVICE = (select Z_PK from ZDEVICE where ZNAME = ?) and ZNAME = ?'
    cursor.execute(query, (devname, faultname))
    count = int(cursor.fetchone()[0])
    if verbose:
        print 'Check if already in ZFAULT: ZDEVICE=%s' % devname
        print 'and ZNAME=%s' % faultname

    # If it does, throw exception
    if count > 0:
       if verbose:
           print 'Found, so not adding PV Fault ', devname, faultname
       #raise Exception("Fault already exists for "+ devname+"_"+faultname+". Not adding")
    
    # If it doesn't, add it
    if count == 0:
        if verbose:
            print 'Not found, so adding PV Fault"%s"' % devname, faultname
  
        query = """
            insert into ZFAULT (
                Z_ENT,
                Z_OPT,
                ZAUTORECOVERABLE,
                ZDEVICE,
                ZNAME,
                ZPVATTRIBUTE,
                ZOKSTATENAME,
                ZFAULTEDSTATENAME,
                ZAUTORECOVERABLE,
                ZDESCRIPT)
            values (
                7,
                1,
                1,
                (select Z_PK from ZDEVICE where ZNAME = ?),
                ?,
                ?,
                ?,
                ?,
                ?,
                ?);
        """
        try:
            cursor.execute(query, (devname, faultname, pvattr, okstate, faultstate, autorecoverable, description))
            #print "addFaults:", (devname, faultname, pvattr, okstate, faultstate, autorecoverable, description)
        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()

