#This script will create new table named milestone_target to track milestone upgrade history
#
#
#/***************************************************************************
# * This software is licensed as described in the file COPYING, which
# * you should have received as part of this distribution. 
# *
# * You may opt to use, copy, modify, merge, publish, distribute and/or sell
# * copies of the Software, and permit persons to whom the Software is
# * furnished to do so, under the terms of the COPYING file.
# *
# ***************************************************************************/

import os,sys,sqlite3
from pkg_env import SERVER_PATH,LOG_PATH
import time

date_argv = sys.argv[1]
try:
	time.strptime(date_argv, "%Y-%m-%d")
except:
	print "error happen"
	print time.strptime(date_argv, "%Y-%m-%d")
	format_error_log.write("The date argument %s is not right when setting milestone start date. Please check that in getallpkginfo.bash\n" % date_argv )
	sys.exit(1)

format_error_log = open(LOG_PATH + "block_update_process_format_problem.log", 'a')
print sys.argv
db = sqlite3.connect(SERVER_PATH + 'pkginfo.sqlite')
milestone_db = sqlite3.connect(LOG_PATH + 'pkginfo.sqlite.%s' % date_argv )
format_error_log = open(LOG_PATH + "block_update_process_format_problem.log", 'a')

db_cu = db.cursor()
milestone_db_cu = milestone_db.cursor()

db_cu.execute('drop table if exists milestone_target')
cmd1 = 'create table if not exists milestone_target as select pn from pkginfotable'
db_cu.execute(cmd1)
a = milestone_db_cu.execute('select pn,pv,pupver,pstatus from pkginfotable')
base_pn_pupver = a.fetchall()

#Add column pv pupver pastatus from milestone database
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN pv_milestone')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN pupver_milestone')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN pstatus_milestone')
for tup in base_pn_pupver:
	cmd1 = 'update milestone_target set pv_milestone = "%s" where pn = "%s"' % (tup[1],tup[0])
	db_cu.execute(cmd1)
	cmd2 = 'update milestone_target set pupver_milestone = "%s" where pn = "%s"' % (tup[2],tup[0])
	db_cu.execute(cmd2)
	cmd3 = 'update milestone_target set pstatus_milestone = "%s" where pn = "%s"' % (tup[3],tup[0])
	db_cu.execute(cmd3)


#Add other information from the latest database
b = db_cu.execute('select pn,pv,pupver,pmver,pmt,upgradetime,lastchktime,noupgreason,psrcuri from pkginfotable')
base_other_info = b.fetchall()
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN pv')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN pupver')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN pmver')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN pmt')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN upgradetime')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN lastchktime')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN noupgreason')
db_cu.execute('ALTER TABLE milestone_target ADD COLUMN psrcuri')
for tup in base_other_info:
	cmd0 = 'update milestone_target set pv = "%s" where pn = "%s" ' % (tup[1],tup[0])
	db_cu.execute(cmd0)
	cmd1 = 'update milestone_target set pupver = "%s" where pn = "%s" ' % (tup[2],tup[0])
	db_cu.execute(cmd1)
	cmd2 = 'update milestone_target set pmver = "%s" where pn = "%s" ' % (tup[3],tup[0])
	try:
		db_cu.execute(cmd2)
	except:
		format_error_log.write(cmd3 + "\n")
	cmd3 = 'update milestone_target set pmt = "%s" where pn = "%s" ' % (tup[4],tup[0])
	db_cu.execute(cmd3)
	cmd4 = 'update milestone_target set upgradetime = "%s" where pn = "%s" ' % (tup[5],tup[0])
	db_cu.execute(cmd4)
	cmd5 = 'update milestone_target set lastchktime = "%s" where pn = "%s" ' % (tup[6],tup[0])
	db_cu.execute(cmd5)
	cmd6 = 'update milestone_target set noupgreason = "%s" where pn = "%s" ' % (tup[7],tup[0])
	db_cu.execute(cmd6)
	cmd7 = 'update milestone_target set psrcuri= "%s" where pn = "%s" ' % (tup[8],tup[0])
	db_cu.execute(cmd7)

format_error_log.close()
db.commit()