"""

 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)"""

 RATES_TABLE = """rates(

 rate smallmoney

 flatrate smallmoney,

 project int,

 activity int,

 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, RATES_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

 	con.commit()

 	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 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):

 	con, cur = session(db_file)

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

 	cur.execute(sql)	

 	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):

 	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,)

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

 		else:

 			values = (time, project_id(db_file, project), activity_id(db_file, activity))

 			sql = "update tasks set end=? 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)

 		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)

 	sql = "select rowid from breaks where end is null"

 	cur.execute(sql)

 	paused = cur.fetchone()

 	cur.close()

 	return paused

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

 	print "pausing" 

 	if not is_paused(db_file, time):

 		project = find_last_project(db_file, time) 

 		activity = find_last_activity(db_file, time)

 		con, cur = session(db_file)

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

 					activity_id(db_file, activity), None)

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

 		cur.execute(sql, values)

 		con.commit()

 		cur.close()

 	test(db_file)

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

 	print "resuming"

 	if is_paused(db_file, time):

 		task = find_active_task(db_file)

 		activity = find_last_activity(db_file, time)

 		project = find_last_project(db_file, time) 			

 		con, cur = session(db_file)

 		values = (time, project_id(db_file, project), activity_id(db_file, activity))

 		sql = "update breaks set end=? where project=? and activity=?"

 		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