#!/usr/bin/env python

import datetime
import cx_Oracle
import psycopg2
from sys import argv, exit, stdout
from string import find, upper, lower, strip, split
from os import getenv, environ, popen, umask, getcwd, system

program = 'mark5c2db'
author = 'Walter Brisken'
version = '0.11'
verdate = '20151207'

# Name of the Oracle VLBA legacy database
databaseName = getenv("VLBA_DB")

# Name of the Postgres VLBA database based on EVLA
newDatabaseName = getenv("VLBAMPTS_DB")

# Name of the Postgres EVLA database
evlaDatabaseName = getenv("EVLAMPTS_DB")

mjd0 = datetime.datetime(1858, 11, 17, 0, 0)

def usage(pgm):
	print '%s ver %s  %s  %s\n' % (program, version, author, verdate)
	print 'A program to populate the legacy VLBA database with Mark5C information.\n'
	print 'Usage: %s [options] <vex file> [list of station codes]\n'
	print 'Options can be:'
	print '  -h  or  --help         print help information and quit.'
	print '  -p  or  --pretend      run in pretend mode (don\'t update the database).'
	print '  --force                force insertion even if previous values exist already.'
	print '  --override             like force, but allows one project to override another.'
	print '\nIf a space separated list of one or more station codes is provided, only those'
	print 'stations will be processed.\n'
	print 'Note! Avoid running this program multiple times for the same experiment.\n'
	print 'Example: %s bl175s.vex\n' % pgm

def zero2oh(str):
	return str.replace('O', '0')

def vexPeek(vexFile, verbose):
	
	cmd = 'vexpeek %s' % vexFile
	if verbose > 0:
		print 'Executing command: %s' % cmd

	p = popen(cmd)
	data = p.readlines()

	if len(data) == 0:
		return 'Error', 'Error', 'Error'
	
	obsCode = upper(strip(data[0]))
	obsSeg = ''

	if obsCode[0:5] == 'ERROR':
		return 'Error', 'Error', 'Error'

	if len(obsCode) > 3:
		if obsCode[0].isalpha() and obsCode[1].isalpha() and obsCode[2].isdigit():
			for i in range(3, len(obsCode)):
				if obsCode[i].isalpha():
					obsSeg = obsCode[i:]
					obsCode = obsCode[0:i]
					break;
		if obsCode[0].isalpha() and obsCode[1].isdigit():
			for i in range(2, len(obsCode)):
				if obsCode[i].isalpha():
					obsSeg = obsCode[i:]
					obsCode = obsCode[0:i]
					break;
		

	stationTimes = {}
	for d in data[1:]:
		s = split(strip(d))
		stationTimes[upper(s[0])] = [float(s[1]), float(s[2])]

	print 'This is experiment %s %s' % (obsCode, obsSeg)

	return obsCode, obsSeg, stationTimes

def process(monData):
	vsn = ['', '']
	recvsn = ''
	recStart = -1
	recStop = -1
	recBank = -1
	active = -1	# or 0 or 1
	rate = 0

	N = len(monData)

	scans = []

	nNullVSN = 0
	for m in monData:
		N -= 1
		action = ''
		dev = m[0]
		mpt = m[1]
		t   = m[2]
		v   = m[3]
		s   = m[4]
		if s == None:
			s = ''

		if mpt == 'VSN':
			if dev in ['BANK0', 'BANKA']:
				b = 0
			elif dev in ['BANK1', 'BANKB']:
				b = 1
			else:
				b = -1
			if b != -1:
				if active == b and s != vsn[b] and s != '':
					action = 'stop'
			vsn[b] = s
		elif mpt == 'RecordingRate':
			if v == 0 and rate > 0:
				action = 'stop'
			elif v > 0 and rate == 0 and active >= 0:
				action = 'start'
			rate = v
		elif mpt == 'RecordState':
			if v > 0 and recStart < 0 and active >= 0:
				action = 'start'
			elif v == 0 and recStart > 0:
				action = 'stop'
		elif mpt == 'ActiveBank':
			if (v == -1 or v != active) and recStart > 0:
				action = 'stop'
			active = int(v)

		if N == 0:
			action = 'stop'

		if action == 'stop' and recStart > 0:
			recStop = t
			if recvsn == '':
				if nNullVSN == 0:
					print 'Scan with null VSN encountered: %12.6f to %12.6f' % (recStart, recStop)
				nNullVSN += 1
			else:
				scans.append([recStart, recStop, recvsn, recBank])
			recStart = -1
		elif action == 'start':
			recvsn = vsn[active]
			recStart = t
			recStop = -1
			recBank = active

	if nNullVSN > 0:
		print 'A total of %d scans with null VSNs were seen.  This is probably nothing to worry about.' % nNullVSN
	
	blocks = []
	lastblock = None
	for scan in scans:
		if lastblock == None:
			lastblock = [scan[0], scan[1], scan[2], scan[3]]
			blocks.append(lastblock)
		elif lastblock[2] != scan[2]:
			lastblock = [scan[0], scan[1], scan[2], scan[3]]
			blocks.append(lastblock)
		elif scan[0] - lastblock[1] > 20.0/1440.0:
			lastblock = [scan[0], scan[1], scan[2], scan[3]]
			blocks.append(lastblock)
		else:
			lastblock[1] = scan[1]

	return blocks

def queryMark5C(db, startMJD, stopMJD, station, verbose):
	
	cursor = db.cursor()

	stn = lower(station)
	if stn == 'y':
		stn = 'ea'
	
	if stn == 'ea':
		print '\n*** NOTE: Querying VLA data can take a long time (10+ minutes) ***'
		print '*** Please do not interrupt the process.                         ***\n'

	hostlist = "'%s-mark5c-1', '%s-mark5c-2'" % (stn, stn)
	query = "select * from mcdata where hostname IN (%s) and timestamp > %10.4f and timestamp < %10.4f order by timestamp" % (hostlist, startMJD, stopMJD)
	query = "SELECT devicename,monpointname,timestamp,monpointvalue,monpointstr FROM mcdata WHERE hostname IN (%s) AND ((devicename IN ('BANK0', 'BANK1', 'BANKA', 'BANKB') AND monpointname='VSN') OR (devicename = 'MARK5C' AND monpointname IN ('ScanLabel', 'RecordingRate', 'ActiveBank', 'RecordState'))) AND timestamp BETWEEN %10.4f AND %10.4f ORDER BY timestamp" % (hostlist, startMJD, stopMJD)

	print 'Executing: %s' % query

	cursor.execute(query)
	monData = cursor.fetchall()

	print '%d mon data points found' % len(monData)

	f = open('/tmp/%s.mon' % station, 'w')
	for m in monData:
		f.write('%s\n' % str(m))
	f.close()

	return monData
	
# --------------------

if len(argv) < 2:
	print 'Invalid comamnd line: at least one argument is required.\nSee help information below.\n'
	usage(argv[0])
	exit(0)

vexFile = None
doForce = False
doPretend = False
doOverride = False
stationFilter = []
collisions = {}

for a in argv[1:]:
	if a in ['--help', '-h']:
		usage(argv[0])
		exit(0)
	elif a in ['--force']:
		doForce = True
	elif a in ['--override']:
		doOverride = True
	elif a in ['--pretend', '-p']:
		doPretend = True
	elif vexFile == None:
		vexFile = a
	elif len(a) < 3 and a.isalpha():
		stationFilter.append(lower(a))
	else:
		print 'Invalid comamnd line: only one vex file can be supplied.\nSee help information below.\n'
		usage(argv[0])
		exit(0)

if vexFile == None:
	print 'Invalid comamnd line: no vex file supplied.\nSee help information below.\n'
	usage(argv[0])
	exit(0)

print 'Connecting to the new database:'
newDb = psycopg2.connect(newDatabaseName)
print 'Connecting to the legacy database:'
oldDb = cx_Oracle.connect(databaseName)



obsCode, obsSeg, stationTimes = vexPeek(vexFile, 1)

if len(obsSeg) == 0:
	obsSeg = '  '	# yes : two spaces!
if len(stationFilter) > 0:
	stns = []
	for s in stationTimes.keys():
		if lower(s) in stationFilter:
			stns.append(s)
else:
	stns = stationTimes.keys()
stns.sort()

if 'Y' in stns:
	print 'Connecting to the EVLA database:'
	if evlaDatabaseName == None:
		print 'Error: evlaDatabaseName not set.  Please check for env. var. EVLAMPTS_DB'
		exit(0);
	evlaDb = psycopg2.connect(evlaDatabaseName)

cursor = oldDb.cursor()

for s in stns:
	print '\nProcessing station %s' % s
	startMJD = stationTimes[s][0]
	stopMJD = stationTimes[s][1]

	if s == 'Y':
		db = evlaDb
	else:
		db = newDb

	monData = queryMark5C(db, startMJD-0.25, stopMJD+0.05, s, 1)

	blocks = process(monData)

	for b in blocks:
		if b[1] < startMJD or b[0] > stopMJD:
			continue;

		dt = mjd0 + datetime.timedelta(max(startMJD, b[0]))
		epoch = "to_date('%d/%02d/%02d:%02d:%02d:%02d', 'yyyy/mm/dd:hh24:mi:ss')" % (dt.year, dt.month, dt.day, dt.hour, dt.minute, dt.second)

		vsn = zero2oh(b[2])

		if doOverride:
			cmd = "DELETE from TAPE WHERE STAID='%s' and EPOCH=%s" % (upper(s), epoch)
			print cmd
			if not doPretend:
				cursor.execute(cmd)

		elif doForce:
			cmd = "DELETE from TAPE WHERE PROPOSAL='%s' and SEGMENT='%s' and STAID='%s' and EPOCH=%s" % (obsCode, obsSeg, upper(s), epoch)
			print cmd
			if not doPretend:
				cursor.execute(cmd)

		cmd = "INSERT into TAPE VALUES ('%s', '%s', '%s', %s, '%s', '%d')" % (obsCode, obsSeg, upper(s), epoch, vsn, b[3]+2)
		print cmd
		if not doPretend:
			try:
				cursor.execute(cmd)
			except cx_Oracle.IntegrityError:
				collide = False
				cmd2 = "SELECT * from TAPE where STAID='%s' and EPOCH=%s" % (upper(s), epoch)
				cursor.execute(cmd2)
				results = cursor.fetchall()
				for R in results:
					if R[0] == obsCode and R[1] == obsSeg and R[2] == upper(s) and R[4] == vsn and R[5] == b[3]+2:
						pass
					else:
						if collide == False:
							collide = True
							print 'Insert command failed: Database uniqueness constraint violation: another entry already exists in the database with the same station and start time:'
						print '  %s %s  %s  %s' % (R[0], R[1], R[2], R[4])
						collisions[R[0]+R[1]] = 1
			else:
				print 'Executed'

if not doPretend:
	oldDb.commit()
	print 'Committed'
else:
	print 'None of this actually happened because pretend mode was used.'

if len(collisions) > 0:
	print '\nThe following projects prevented proper insertion of new data into the database:'
	for C in collisions:
		print '  %s' % C
	print 'Run this program again with the --override option to force overwriting this data with scans from this project.\n'