"""

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

TABLES = (ACTIVITIES_TABLE, COMPANIES_TABLE, PROJECTS_TABLE, RATES_TABLE,

TASKS_TABLE)

DEF_PROJECT = "default"

DEF_ACTIVITY = "default"

def session(db_file):

	con = sqlite3.connect(db_file)

	return con, con.cursor()

def test_tables(db_file):

	con, cur = session(db_file)

	sql = "select * from sqlite_master where type='table'"

	cur.execute(sql)

	for c in cur:

		print c

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

	con.commit()

	cur.close()

	return activity

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

	if not activity:

		activity = find_last_activity(db_file, time)

	if not project:

		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 tasks set end=? where project=? and activity=?"

	cur.execute(sql, values)

	con.commit()

	cur.execute("select * from tasks")

	for c in cur:

		print c

	cur.close()

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

	last_project = find_last_project(db_file, time) 

	last_activity = find_last_activity(db_file, time)

	if not activity:

		activity = DEF_ACTIVITY

	if not project:

		project = last_project

	if activity != last_activity or project != last_project:

		end(db_file, 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.execute("select * from tasks")

	for c in cur:

		print c

	con.commit()

	cur.close()