#!python

# ---- Import standard modules to the python path.

import argparse,os,string,random,pdb
from gwpy.segments import DataQualityFlag
import pandas as pd
from sqlalchemy.engine import create_engine

def parse_commandline():
    """Parse the arguments given on the command-line.
    """
    parser = argparse.ArgumentParser(description=__doc__)
    args = parser.parse_args()

    return args

args = parse_commandline()

# Load gspySearchDB
SQL_USER = os.environ['SQL_USER']
SQL_PASS = os.environ['SQL_PASS']
engine = create_engine('mysql://{0}:{1}@ldvw-dev.ligo.caltech.edu:3306/gravityspy'.format(SQL_USER,SQL_PASS))
print 'loading GSpySearchDB ...'
gSpySearch = pd.read_sql('GSMetadata', engine)
gSpySearch.drop('indexID', 1, inplace=True)
print 'done'

# Load GravitySpy
engine1 = create_engine('postgresql://{0}:{1}@gravityspy.ciera.northwestern.edu:5432/gravityspy'.format(os.environ['GRAVITYSPY_DATABASE_USER'],os.environ['GRAVITYSPY_DATABASE_PASSWD']))
print 'loading GSpyDB ...'
gSpyDB = pd.read_sql('glitches', engine1)
print 'done'

# Convert Graviyt Spy DB in to Search DB format. This is necessary to perform a diff between the DBs and determine how to update gspySearch accordingly
def makelink(x):
    # This horrendous thing obtains the public html path for image
    interMediatePath = '/'.join(filter(None,str(x.Filename1).split('/'))[3:-1])
    if x.ifo == 'L1':
        return 'https://ldas-jobs.ligo-la.caltech.edu/~scoughlin/{0}/{1}.png'.format(interMediatePath,x.uniqueID)
    elif x.ifo == 'V1':
        return 'https://ldas-jobs.ligo.caltech.edu/~scoughlin/{0}/{1}.png'.format(interMediatePath, x.uniqueID)
    else:
        return 'https://ldas-jobs.ligo-wa.caltech.edu/~scoughlin/{0}/{1}.png'.format(interMediatePath, x.uniqueID)

gSpyDB['imgUrl'] = gSpyDB[['ifo', 'uniqueID', 'Filename1']].apply(makelink,axis=1)

gSpyDB['confidence'] = gSpyDB[['1400Ripples','1080Lines','Air_Compressor','Blip', 'Chirp', 'Extremely_Loud', 'Helix', 'Koi_Fish', 'Light_Modulation', 'Low_Frequency_Burst', 'Low_Frequency_Lines','None_of_the_Above', 'No_Glitch', 'Paired_Doves', 'Power_Line','Repeating_Blips', 'Scattered_Light', 'Scratchy', 'Tomte','Violin_Mode', 'Wandering_Line', 'Whistle']].max(axis=1)

gSpyDB['pipeline'] = 'GravitySpy'
gSpyDB.loc[gSpyDB.citizenScore.isnull(), 'citizenScore'] = 0.0
gSpyDB['flag'] = 0

gSpyDB = gSpyDB[['uniqueID','ifo','Label','imgUrl', 'snr', 'amplitude','peak_frequency','central_freq','duration','bandwidth','chisq','chisq_dof','peakGPS','confidence', 'ImageStatus', 'pipeline', 'citizenScore', 'flag', 'DQFlag', 'qvalue']]

gSpyDB.columns = ['id', 'ifo', 'label', 'imgUrl', 'snr', 'amplitude', 'peakFreq', 'centralFreq', 'duration', 'bandwidth', 'chisq', 'chisqDof', 'GPStime','confidence', 'imageStatus', 'pipeline', 'citizenScore', 'flag', 'dqflag', 'qvalue']

gSpySearch = gSpySearch[['id', 'label', 'confidence', 'imageStatus', 'citizenScore', 'dqflag']]
gSpySearch.loc[gSpySearch.dqflag.isnull(), 'dqflag'] = 'NoFlag'
gSpyDB_trimmed = gSpyDB[['id', 'label', 'confidence', 'imageStatus', 'citizenScore', 'dqflag']]

# Now gSpyDB and gSpySearch are the same, time to see what entries are different or new

# First get identical entries and do a diff

sameEntriesDB = gSpyDB_trimmed.loc[gSpyDB_trimmed.id.isin(gSpySearch.id)]
sameEntriesSearch = gSpySearch.loc[gSpySearch.id.isin(gSpyDB_trimmed.id)]
sameEntriesDB = sameEntriesDB.set_index('id')
sameEntriesSearch = sameEntriesSearch.set_index('id')
sameEntriesDB.sort_index(inplace=True)
sameEntriesSearch.sort_index(inplace=True)
mask = (sameEntriesDB != sameEntriesSearch).any(1)
sameEntriesDB = sameEntriesDB[mask]
sameEntriesDB.reset_index(inplace=True)
print(len(sameEntriesDB))

# We will now updates these rows on gSpySearch
for iID in sameEntriesDB.id:
    SQLCommand = 'UPDATE GSMetadata SET '
    columnDict = sameEntriesDB.loc[sameEntriesDB.id == iID].drop('id', 1).to_dict(orient='records')[0]
    for Column in columnDict:
        if isinstance(columnDict[Column],basestring):
            SQLCommand = SQLCommand + '''{0} = \'{1}\', '''.format(Column,columnDict[Column])
        else:
            SQLCommand = SQLCommand + '''{0} = {1}, '''.format(Column,columnDict[Column])
    SQLCommand = SQLCommand[:-2] + ' WHERE id = \'' + iID + "'"
    engine.execute(SQLCommand)

# Get new entries
newEntriesDB = gSpyDB.loc[~gSpyDB.id.isin(gSpySearch.id)]
print 'Number of new entries...{0}'.format(len(newEntriesDB.loc[~newEntriesDB.confidence.isnull()]))
newEntriesDB.loc[~newEntriesDB.confidence.isnull()].to_sql(con=engine, name='GSMetadata', if_exists='append',index=False,chunksize=100)
