"""

 Description: Database lib for cronrec.

 Author: Eugen Sawin (sawine@me73.com)

 Dependencies: libsqlite3-dev

 """

 import sqlite3

 ACTIVITIES_TABLE = """activities(

 name text not null,

 description text)"""

 COMPANIES_TABLE = """companies(

 name text not null,

 street text,

 area text,

 town text,

 country text,

 postal text)"""

 PROJECTS_TABLE = """projects(

 name text not null,

 number int,

 customer int,

 constraint customer_fk foreign key(customer) references companies(rowid) on delete

 cascade)"""

 CURRENCIES_TABLE = """currencies(

 name text not null)"""

 RATE_TYPES_TABLE = """rate_types(

 name text not null)"""

 RATES_TABLE = """rates(

 rate smallmoney not null,

 currency int not null,

 type int not null,

 project int not null,

 activity int,

 constraint currency_fk foreign key(currency) references currencies(rowid) on

 delete cascade,

 constraint type_fk foreign key(type) references rate_types(rowid) on delete

 cascade,

 constraint project_fk foreign key(project) references projects(rowid) on delete

 cascade,

 constraint activity_fk foreign key(activity) references activities(rowid) on delete

 cascade)"""

 TASKS_TABLE = """tasks(

 begin datetime,

 end datetime,

 project int,

 activity int,

 log text,

 constraint project_fk foreign key(project) references projects(rowid) on delete

 cascade,

 constraint activity_fk foreign key(activity) references activities(rowid) on delete

 cascade)"""

 BREAKS_TABLE = """breaks(

 begin datetime,

 end datetime,

 task int,

 log text,

 constraint task_fk foreign key(task) references tasks(rowid) on delete

 cascade)"""

 TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, CURRENCIES_TABLE,

 RATES_TABLE, RATE_TYPES_TABLE, TASKS_TABLE, BREAKS_TABLE)

 DEF_PROJECT = "default"

 DEF_ACTIVITY = "default"

 def session(db_file):

 	con = sqlite3.connect(db_file)

 	return con, con.cursor()

 def test(db_file):

 	con, cur = session(db_file)

 	cur.execute("select * from projects")

 	print "\nPROJECTS"

 	for c in cur:

 		print c

 	cur.execute("select * from activities")

 	print "\nACTIVITIES"

 	for c in cur:

 		print c

 	cur.execute("select * from tasks")

 	print "\nTASKS"

 	for c in cur:

 		print c

 	cur.execute("select * from breaks")

 	print "\nBREAKS"

 	for c in cur:

 		print c

 	cur.execute("select * from currencies")

 	print "\nCURRENCIES"

 	for c in cur:

 		print c

 	cur.execute("select * from rate_types")

 	print "\nRATE_TYPES"

 	for c in cur:

 		print c

 	cur.execute("select * from rates")

 	print "\nRATES"

 	for c in cur:

 		print c

 	cur.close()

 def init(db_file):

 	con, cur = session(db_file)

 	for table in TABLES:

 		sql = "create table " + table

 		cur.execute(sql)

 	con.commit()

 	cur.close()

 def update_where(db_file, table, dep_column, dep_value, set_column, set_value):

 	con, cur = session(db_file)

 	values = (set_value, dep_value)	

 	sql = "update %s set %s=? where %s=?" % (table, set_column, dep_column)

 	cur.execute(sql, values)

 	con.commit()

 	cur.close()

 	test(db_file)

 def set_rate(db_file, project, activity, rate, currency, type):

 	if not activity:

 		activity = DEF_ACTIVITY

 	con, cur = session(db_file)

 	values = (rate, row_id(db_file, "currencies", currency), 

 				row_id(db_file, "rate_types", type),

 				row_id(db_file, "projects", project), 

 				row_id(db_file, "activities", activity))

 	sql = "insert into rates values(?, ?, ?, ?, ?)"

 	cur.execute(sql, values)

 	con.commit()

 	cur.close()

 	test(db_file)

 def find_last_project(db_file, time):

 	con, cur = session(db_file)

 	values = (time,)

 	sql = "select project from tasks where begin < ? and end is null order by \

 begin desc"

 	cur.execute(sql, values)

 	id = cur.fetchone()

 	if id:

 		values = (id[0],)

 		sql = "select name from projects where rowid=?"

 		cur.execute(sql, values)

 		project = cur.fetchone()[0]

 	else:

 		project = None#DEF_PROJECT

 	con.commit()

 	cur.close()

 	return project

 def find_last_activity(db_file, time):

 	con, cur = session(db_file)

 	values = (time,)

 	sql = "select activity from tasks where begin < ? and end is null order by \

 begin desc"

 	cur.execute(sql, values)

 	id = cur.fetchone()

 	if id:

 		values = (id[0],)

 		sql = "select name from activities where rowid=?"

 		cur.execute(sql, values)

 		activity = cur.fetchone()[0]

 	else:

 		activity = None#DEF_ACTIVITY

 	con.commit()

 	cur.close()

 	return activity

 def find_active_task(db_file, time=None):

 	con, cur = session(db_file)

 	if time:

 		values = (time, time)

 		sql = "select project, activity from tasks where begin < ? and \

 (end is null or end > ?)"

 	else:

 		values = ()

 		sql = "select project, activity from tasks where end is null"

 	cur.execute(sql, values)	

 	task = cur.fetchone()

 	if task:

 		task = task[0]	

 	cur.close()

 	return task			

 def id_name(db_file, table, id):

 	con, cur = session(db_file)

 	values = (id,)

 	sql = "select name from %s where rowid=?" % table

 	cur.execute(sql, values)

 	id = cur.fetchone()

 	cur.close()

 	return id[0]

 def row_id(db_file, table, name):

 	con, cur = session(db_file)

 	values = (name,)

 	sql = "select rowid from %s where name=?" % table

 	cur.execute(sql, values)

 	id = cur.fetchone()

 	if not id:

 		sql = "insert into %s(name) values(?)" % table

 		cur.execute(sql, values)

 		con.commit()

 		cur.close()

 		return row_id(db_file, table, name)

 	con.commit()

 	cur.close()

 	return id[0]

 def project_id(db_file, name):

 	return row_id(db_file, "projects", name)

 def activity_id(db_file, name):

 	return row_id(db_file, "activities", name)

 def end(db_file, project, activity, time, log):

 	print "ending: %s:%s" % (project, activity)

 	if activity or project:		

 		if not activity:

 			activity = find_last_activity(db_file, time)

 		elif not project:

 			project = find_last_project(db_file, time) 	

 		con, cur = session(db_file)

 		if activity == "all" and project == "all":

 			values = (time, log)

 			sql = "update tasks set end=?, log=? where end is null"

 		else:

 			values = (time, log, project_id(db_file, project), 

 						activity_id(db_file, activity))

 			sql = "update tasks set end=?, log=? where project=? and activity=?"

 		cur.execute(sql, values)

 		con.commit()

 		cur.close()

 	test(db_file)

 def begin(db_file, project, activity, time):

 	print "beginning: %s:%s" % (project, activity)

 	last_project = find_last_project(db_file, time) 

 	last_activity = find_last_activity(db_file, time)	

 	if not project:

 		project = last_project

 	elif not activity:

 			activity = DEF_ACTIVITY

 	if activity != last_activity or project != last_project:

 		end(db_file, last_project, last_activity, time, None)

 		con, cur = session(db_file)

 		values = (time, None, project_id(db_file, project), 

 					activity_id(db_file, activity), None)

 		sql = "insert into tasks values(?, ?, ?, ?, ?)"

 		cur.execute(sql, values)

 		con.commit()

 		cur.close()

 	test(db_file)

 def is_paused(db_file, time):

 	con, cur = session(db_file)

 	values = (time,)

 	sql = "select rowid from breaks where begin < ? and end is null"

 	cur.execute(sql, values)

 	paused = cur.fetchone()

 	cur.close()

 	return paused

 def pause(db_file, time):

 	print "pausing" 

 	if not is_paused(db_file, time):

 		task = find_active_task(db_file, time)

 		con, cur = session(db_file)

 		values = (time, None, task, None)

 		sql = "insert into breaks values(?, ?, ?, ?)"

 		cur.execute(sql, values)

 		con.commit()

 		cur.close()

 	test(db_file)

 def resume(db_file, task, time, log):

 	print "resuming"

 	if is_paused(db_file, time):	

 		if not task:

 			task = find_active_task(db_file, time)	

 		con, cur = session(db_file)

 		values = (time, log, task)

 		sql = "update breaks set end=?, log=? where task=? and end is null"

 		cur.execute(sql, values)

 		con.commit()

 		cur.close()

 	test(db_file)

 def status(db_file):

 	task = find_active_task(db_file)

 	con, cur = session(db_file)

 	result = None

 	if task:

 		values = (task,)

 		sql = "select project, activity from tasks where rowid=?"

 		cur.execute(sql, values)

 		result = cur.fetchone()

 		if result:

 			result = (id_name(db_file, "projects", result[0]),

 						id_name(db_file, "activities", result[1]))

 	return result