#!/bin/env python

import cx_Oracle
from os import getenv
from string import strip, split
import datetime

dbName = getenv('VLBA_DB')
db = cx_Oracle.connect(dbName)


def tweakdates(db, pretend):

	fixquery = "select tape.vsn, shelves.shelf, program.status, program.proposal, program.segment, tape.epoch, tapes.last_erase, tapes.enter_service, tapes.type from tape, tapes, shelves, program where tape.vsn=tapes.vsn and tape.vsn=shelves.vsn and tapes.enter_service = to_date('2009/01/01:01:00:00am', 'yyyy/mm/dd:hh:mi:ssam') and tape.proposal = program.proposal and nvl(tape.segment,'') = nvl(program.segment,'') and tape.epoch > tapes.last_erase order by tape.vsn, tape.epoch"
	
	cursor = db.cursor()

	f = {}

	events = cursor.execute(fixquery)

	for e in events:
		f[e[0]] = [e[2], e[5], e[6], e[8]]
	
	keys = f.keys()

	keys.sort()

	enterdate = "to_date('2009/01/01:01:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')"

	for k in keys:
		print k, f[k]
		cmd = "delete from tapes where VSN='%s'" % k
		print cmd

		cursor.execute(cmd)

		if f[k][0] == 'RELEASED':
			q = f[k][1] + datetime.timedelta(1)
		else:
			q = f[k][1] - datetime.timedelta(20)

		newdate = "to_date('%d/%02d/%02d:01:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')" % (q.year, q.month, q.day)

		cmd = "insert into TAPES values ('%s', '%s', 25, %s, '?', 1, %s)" % (k, f[k][3], enterdate, newdate)
		print cmd

		if not pretend:
			cursor.execute(cmd)

	if not pretend:
		db.commit()



def adddisks(db):

	cursor = db.cursor()


	query1 = 'select distinct vsn from TAPE order by vsn'
	query2 = 'select distinct vsn from TAPES order by vsn'

	cursor.execute(query1)
	disks1 = cursor.fetchall()

	actualVSNs = []

	for disk in disks1:
		d = disk[0]
		if len(d) == 8 and ('-' in d or '+' in d):
			actualVSNs.append(d)

	cursor.execute(query2)
	disks2 = cursor.fetchall()

	currentVSNs = []

	for disk in disks2:
		d = disk[0]
		if len(d) == 8 and ('-' in d or '+' in d):
			currentVSNs.append(d)

	print 'Currently there are %d Mark5 modules in the TAPES table' % len(currentVSNs)
	print 'There are %d unique Mark5 modules in the TAPE table' % len(actualVSNs)

	addList = []

	date = "to_date('2009/01/01:01:00:00AM', 'yyyy/mm/dd:hh:mi:ssam')"

	cursor = db.cursor()

	for d in actualVSNs:
		if d not in currentVSNs:
			addList.append(d)
			if '+' in d:
				type = 'MARK5SER'
			else:
				type = 'MARK5PAR'
			cmd = "insert into TAPES values ('%s', '%s', 25, %s, '?', 1, %s)" % (d, type, date, date)
			print cmd
			cursor.execute(cmd)
			#print 'Need to add %s to TAPES' % d

	db.commit()

	print 'Added to add %d modules to TAPES' % len(addList)

# adddisks(db)

tweakdates(db, False)