"""

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