import mysql.connector
import os
from commonUtils import insertTM

DROP_TABLE_NMR = 'DROP TABLE IF EXISTS NMR'
CREATE_TABLE_NMR = '''
CREATE TABLE IF NOT EXISTS NMR(IDE_XRN INT,NMR_L VARCHAR(2048), NMR_CITATION VARCHAR(128), NMR_SOURCE VARCHAR(128),NMR_KW VARCHAR(512),NMR_NUC VARCHAR(128),
NMR_NUI VARCHAR(128), NMR_SOL VARCHAR(128), NMR_T VARCHAR(128),NMR_F VARCHAR(128), NMR_TXT VARCHAR(2048), NMR_SIG VARCHAR(1024), NMR_SIGTYP VARCHAR(2048),
NMR_INT VARCHAR(128), NMR_ED VARCHAR(128), NMR_LCN VARCHAR(128), NMR_TAG VARCHAR(128), NMR_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_IR = 'DROP TABLE IF EXISTS IR'
CREATE_TABLE_IR = '''
CREATE TABLE IF NOT EXISTS IR(IDE_XRN INT,IR_L VARCHAR(2048), IR_CITATION VARCHAR(128), IR_SOURCE VARCHAR(128),IR_KW VARCHAR(128),
IR_SOL VARCHAR(128), IR_T VARCHAR(128), IR_TXT VARCHAR(2048), IR_SIG VARCHAR(512), IR_INT VARCHAR(1024), IR_ED VARCHAR(128), 
IR_LCN VARCHAR(128), IR_TAG VARCHAR(128), IR_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_MS = 'DROP TABLE IF EXISTS MS'
CREATE_TABLE_MS = '''
CREATE TABLE IF NOT EXISTS MS(IDE_XRN INT,MS_L VARCHAR(512), MS_CITATION VARCHAR(128), MS_SOURCE VARCHAR(128),MS_KW VARCHAR(512),
MS_SIG VARCHAR(1024), MS_INT VARCHAR(256), MS_ED VARCHAR(128), MS_LCN VARCHAR(128), MS_TAG VARCHAR(128), MS_COM VARCHAR(128), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_UV = 'DROP TABLE IF EXISTS UV'
CREATE_TABLE_UV = '''
CREATE TABLE IF NOT EXISTS UV(IDE_XRN INT,UV_L VARCHAR(2048), UV_CITATION VARCHAR(128), UV_SOURCE VARCHAR(128),UV_KW VARCHAR(128),
UV_SOL VARCHAR(256), UV_AM VARCHAR(256), UV_EAC VARCHAR(256), UV_LOGE VARCHAR(128), UV_ED VARCHAR(128), UV_LCN VARCHAR(128), UV_TAG VARCHAR(128), 
UV_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_ESR = 'DROP TABLE IF EXISTS ESR'
CREATE_TABLE_ESR = '''
CREATE TABLE IF NOT EXISTS ESR(IDE_XRN INT,ESR_L VARCHAR(256), ESR_CITATION VARCHAR(128), ESR_SOURCE VARCHAR(128),ESR_KW VARCHAR(128),
ESR_NUI VARCHAR(128), ESR_SOL VARCHAR(128), ESR_T VARCHAR(128), ESR_ED VARCHAR(128), ESR_LCN VARCHAR(128), ESR_TAG VARCHAR(128), 
ESR_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_NQR = 'DROP TABLE IF EXISTS NQR'
CREATE_TABLE_NQR = '''
CREATE TABLE IF NOT EXISTS NQR(IDE_XRN INT,NQR_L VARCHAR(256), NQR_CITATION VARCHAR(128), NQR_SOURCE VARCHAR(128),NQR_KW VARCHAR(128),
NQR_NUC VARCHAR(128), NQR_ED VARCHAR(128), NQR_LCN VARCHAR(128), NQR_TAG VARCHAR(128), NQR_COM VARCHAR(128), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_ROT = 'DROP TABLE IF EXISTS ROT'
CREATE_TABLE_ROT = '''
CREATE TABLE IF NOT EXISTS ROT(IDE_XRN INT,ROT_L VARCHAR(512), ROT_CITATION VARCHAR(128), ROT_SOURCE VARCHAR(128), ROT_KW VARCHAR(128),
ROT_ED VARCHAR(128), ROT_LCN VARCHAR(128), ROT_TAG VARCHAR(128), ROT_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_RAMAN = 'DROP TABLE IF EXISTS RAMAN '
CREATE_TABLE_RAMAN = '''
CREATE TABLE IF NOT EXISTS RAMAN(IDE_XRN INT,RAMAN_L TEXT, RAMAN_CITATION VARCHAR(128), RAMAN_SOURCE VARCHAR(128), RAMAN_KW VARCHAR(128),
RAMAN_SOL VARCHAR(128), RAMAN_ED VARCHAR(128), RAMAN_LCN VARCHAR(128), RAMAN_TAG VARCHAR(128), RAMAN_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_LUM = 'DROP TABLE IF EXISTS LUM'
CREATE_TABLE_LUM = '''
CREATE TABLE IF NOT EXISTS LUM(IDE_XRN INT,LUM_L VARCHAR(2048), LUM_CITATION VARCHAR(128), LUM_SOURCE VARCHAR(128), LUM_KW VARCHAR(128),
LUM_ED VARCHAR(128), LUM_LCN VARCHAR(128), LUM_TAG VARCHAR(128), LUM_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_FLU = 'DROP TABLE IF EXISTS FLU'
CREATE_TABLE_FLU = '''
CREATE TABLE IF NOT EXISTS FLU(IDE_XRN INT,FLU_L VARCHAR(2048), FLU_CITATION VARCHAR(128), FLU_SOURCE VARCHAR(128),FLU_KW VARCHAR(128),
FLU_SOL VARCHAR(128), FLU_T VARCHAR(128), FLU_ED VARCHAR(128), FLU_LCN VARCHAR(128), FLU_TAG VARCHAR(128), FLU_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_PHO = 'DROP TABLE IF EXISTS PHO'
CREATE_TABLE_PHO = '''
CREATE TABLE IF NOT EXISTS PHO(IDE_XRN INT,PHO_L VARCHAR(2048), PHO_CITATION VARCHAR(128), PHO_SOURCE VARCHAR(128),PHO_KW VARCHAR(128),
PHO_SOL VARCHAR(128), PHO_T VARCHAR(128), PHO_ED VARCHAR(128), PHO_LCN VARCHAR(128), PHO_TAG VARCHAR(128), PHO_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
DROP_TABLE_OSM = 'DROP TABLE IF EXISTS OSM'
CREATE_TABLE_OSM = '''
CREATE TABLE IF NOT EXISTS OSM(IDE_XRN INT,OSM_L TEXT, OSM_CITATION VARCHAR(128), OSM_SOURCE VARCHAR(128),OSM_KW VARCHAR(128),
OSM_MOENUC VARCHAR(128), OSM_ED VARCHAR(128), OSM_LCN VARCHAR(128), OSM_TAG VARCHAR(128), OSM_COM VARCHAR(256), PRIMARY KEY(IDE_XRN))
'''
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="123456",
    database="ghddi_spe",
    auth_plugin='mysql_native_password'
)
BATCH_DROP = [DROP_TABLE_ESR,DROP_TABLE_FLU,DROP_TABLE_IR,DROP_TABLE_LUM,DROP_TABLE_MS,DROP_TABLE_NMR,DROP_TABLE_NQR,DROP_TABLE_OSM,DROP_TABLE_PHO,DROP_TABLE_RAMAN,DROP_TABLE_ROT,DROP_TABLE_UV]
BATCH_CREATE = [CREATE_TABLE_ESR,CREATE_TABLE_FLU,CREATE_TABLE_IR,CREATE_TABLE_LUM,CREATE_TABLE_MS,CREATE_TABLE_NMR,CREATE_TABLE_NQR,CREATE_TABLE_OSM,CREATE_TABLE_PHO,CREATE_TABLE_RAMAN,CREATE_TABLE_ROT,CREATE_TABLE_UV]
cursor = db.cursor()
#for drop in BATCH_DROP:
#    cursor.execute(drop)
#db.commit()
for create in BATCH_CREATE:
    cursor.execute(create)
db.commit()
cursor.closeSession()
db.closeSession()

INSERT_TABLE_NMR = '''
INSERT IGNORE INTO NMR(IDE_XRN,NMR_L,NMR_CITATION,NMR_SOURCE,NMR_KW,NMR_NUC,NMR_NUI,NMR_SOL,NMR_T,NMR_F,NMR_TXT,NMR_SIG,NMR_SIGTYP,NMR_INT,NMR_ED,NMR_LCN,NMR_TAG,NMR_COM) 
VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_NMR = ['NMR.L','NMR.citation','NMR.SOURCE','NMR.KW','NMR.NUC','NMR.NUI','NMR.SOL','NMR.T','NMR.F','NMR.TXT','NMR.SIG','NMR.SIGTYP','NMR.INT','NMR.ED','NMR.LCN','NMR.TAG','NMR.COM']

INSERT_TABLE_IR = '''
INSERT IGNORE INTO IR (IDE_XRN, IR_L, IR_CITATION, IR_SOURCE, IR_KW, IR_SOL, IR_T, IR_TXT, IR_SIG, IR_INT, IR_ED, IR_LCN, IR_TAG, IR_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_IR = ['IR.L','IR.citation','IR.SOURCE','IR.KW','IR.SOL','IR.T','IR.TXT','IR.SIG','IR.INT','IR.ED','IR.LCN','IR.TAG','IR.COM']

INSERT_TABLE_MS = '''
INSERT IGNORE INTO MS (IDE_XRN, MS_L, MS_CITATION, MS_SOURCE, MS_KW, MS_SIG, MS_INT, MS_ED, MS_LCN, MS_TAG, MS_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_MS = ['MS.L','MS.citation','MS.SOURCE','MS.KW','MS.SIG','MS.INT','MS.ED','MS.LCN','MS.TAG','MS.COM']

INSERT_TABLE_UV = '''
INSERT IGNORE INTO UV (IDE_XRN, UV_L, UV_CITATION, UV_SOURCE, UV_KW, UV_SOL, UV_AM, UV_EAC, UV_LOGE, UV_ED, UV_LCN, UV_TAG, UV_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_UV = ['UV.L','UV.citation','UV.SOURCE','UV.KW','UV.SOL','UV.AM','UV.EAC','UV.LOGE','UV.ED','UV.LCN','UV.TAG','UV.COM']

INSERT_TABLE_ESR = '''
INSERT IGNORE INTO ESR (IDE_XRN, ESR_L, ESR_CITATION, ESR_SOURCE, ESR_KW, ESR_NUI, ESR_SOL, ESR_T, ESR_ED, ESR_LCN, ESR_TAG, ESR_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_ESR = ['ESR.L','ESR.citation','ESR.SOURCE','ESR.KW','ESR.NUI','ESR.SOL','ESR.T','ESR.ED','ESR.LCN','ESR.TAG','ESR.COM']

INSERT_TABLE_NQR = '''
INSERT IGNORE INTO NQR (IDE_XRN, NQR_L, NQR_CITATION, NQR_SOURCE, NQR_KW, NQR_NUC, NQR_ED, NQR_LCN, NQR_TAG, NQR_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_NQR = ['NQR.L','NQR.citation','NQR.SOURCE','NQR.KW','NQR.NUC','NQR.ED','NQR.LCN','NQR.TAG','NQR.COM']

INSERT_TABLE_ROT = '''
INSERT IGNORE INTO ROT (IDE_XRN, ROT_L, ROT_CITATION, ROT_SOURCE, ROT_KW, ROT_ED, ROT_LCN, ROT_TAG, ROT_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_ROT = ['ROT.L','ROT.citation','ROT.SOURCE','ROT.KW','ROT.ED','ROT.LCN','ROT.TAG','ROT.COM']

INSERT_TABLE_RAMAN = '''
INSERT IGNORE INTO RAMAN (IDE_XRN, RAMAN_L, RAMAN_CITATION, RAMAN_SOURCE, RAMAN_KW, RAMAN_SOL, RAMAN_ED, RAMAN_LCN, RAMAN_TAG, RAMAN_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_RAMAN = ['RAMAN.L','RAMAN.citation','RAMAN.SOURCE','RAMAN.KW','RAMAN.SOL','RAMAN.ED','RAMAN.LCN','RAMAN.TAG','RAMAN.COM']

INSERT_TABLE_LUM = '''
INSERT IGNORE INTO LUM (IDE_XRN, LUM_L, LUM_CITATION, LUM_SOURCE, LUM_KW, LUM_ED, LUM_LCN, LUM_TAG, LUM_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_LUM = ['LUM.L','LUM.citation','LUM.SOURCE','LUM.KW','LUM.ED','LUM.LCN','LUM.TAG','LUM.COM']

INSERT_TABLE_FLU = '''
INSERT IGNORE INTO FLU (IDE_XRN, FLU_L, FLU_CITATION, FLU_SOURCE, FLU_KW, FLU_SOL, FLU_T, FLU_ED, FLU_LCN, FLU_TAG, FLU_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_FLU = ['FLU.L','FLU.citation','FLU.SOURCE','FLU.KW','FLU.SOL','FLU.T','FLU.ED','FLU.LCN','FLU.TAG','FLU.COM']

INSERT_TABLE_PHO = '''
INSERT IGNORE INTO PHO (IDE_XRN, PHO_L, PHO_CITATION, PHO_SOURCE, PHO_KW, PHO_SOL, PHO_T, PHO_ED, PHO_LCN, PHO_TAG, PHO_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_PHO = ['PHO.L','PHO.citation','PHO.SOURCE','PHO.KW','PHO.SOL','PHO.T','PHO.ED','PHO.LCN','PHO.TAG','PHO.COM']

INSERT_TABLE_OSM = '''
INSERT IGNORE INTO OSM (IDE_XRN, OSM_L, OSM_CITATION, OSM_SOURCE, OSM_KW, OSM_MOENUC, OSM_ED, OSM_LCN, OSM_TAG, OSM_COM) 
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
'''
KEY_OSM = ['OSM.L','OSM.citation','OSM.SOURCE','OSM.KW','OSM.MOENUC','OSM.ED','OSM.LCN','OSM.TAG','OSM.COM']
db = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="123456",
    database="ghddi_spe",
    auth_plugin='mysql_native_password'
)

INSERT_TABLE_LIST = [INSERT_TABLE_ESR,INSERT_TABLE_FLU,INSERT_TABLE_IR,INSERT_TABLE_LUM,INSERT_TABLE_MS,INSERT_TABLE_NMR,INSERT_TABLE_NQR,INSERT_TABLE_OSM,INSERT_TABLE_PHO,INSERT_TABLE_RAMAN,INSERT_TABLE_ROT,INSERT_TABLE_UV]
KEY_NAME_LIST = [KEY_ESR,KEY_FLU,KEY_IR,KEY_LUM,KEY_MS,KEY_NMR,KEY_NQR,KEY_OSM,KEY_PHO,KEY_RAMAN,KEY_ROT,KEY_UV]
BASE_PATH = "/data/file/Rx/others2/SPE-import"
TARGET_NAME = 'substance'
ID_KEY = 'IDE.XRN'

def dealXML(fileName):
    tree = insertTM.getTree(fileName)
    for i in range(len(INSERT_TABLE_LIST)):
        insertTM.setSqlValue(INSERT_TABLE_LIST[i], KEY_NAME_LIST[i])
        insertTM.dealXMLByTree(tree)
    os.remove(os.path.join('%s/%s' % (BASE_PATH, fileName)))
    print(fileName,'success')

if __name__ == '__main__':
    insertTM.setBasicValue(BASE_PATH, TARGET_NAME, ID_KEY)
    listDir = os.listdir(BASE_PATH)
    #with ThreadPoolExecutor(20) as exector:
    #    exector.map(dealXML,listDir)
    for fileName in listDir:
        dealXML(fileName)
