indexing
	description: "Database access to workitem data."
	author: "Peter Wyss <wyssp@ethz.ch>"
	date: "$Date$"
	revision: "$Revision$"

class
	WORKITEM_ACCESS

inherit
	DATABASE_ACCESS

create
	make

feature -- Status

	is_found: BOOLEAN
			-- Did the last retrieve query find a workitem?

feature -- Access

	last_workitem: TUPLE [workitem_id: INTEGER; type: INTEGER; creation_time: INTEGER; project_id: INTEGER; project: STRING; user: STRING]
			-- Last retrieved workitem by a retrieve query.

feature -- Select queries

	retrieve_workitems (a_user: INTEGER; a_limit: INTEGER; a_unread_only: BOOLEAN): ARRAYED_LIST[TUPLE [workitem_id: INTEGER; type: INTEGER; creation_time: INTEGER; project_id: INTEGER; project: STRING; user: STRING; is_read: BOOLEAN]] is
			-- Retrieve max a_limit (unread) workitems for a_user
		require
			a_user_valid: a_user > 0
			a_limit_valid: a_limit > 0
		local
			l_selection: SELECTION_RESULT
			l_tuple: TUPLE [workitem_id: INTEGER; type: INTEGER; creation_time: INTEGER; project_id: INTEGER; project: STRING; user: STRING; is_read: BOOLEAN]
		do
			select_query.set_map_name (a_limit, "limit")
			select_query.set_map_name (a_user, "user_id")

			if a_unread_only then
				l_selection := select_query.execute_query ("[
					SELECT w.workitem_id, w.type_id, w.creation_time, w.project_id, p.name AS project_name, u.name AS user_name, IF(r.workitem_id>0, 1, 0) AS is_read FROM workitem w
					LEFT JOIN user u USING (user_id)
					LEFT JOIN project p USING (project_id)
					LEFT JOIN user_read_workitem r ON r.workitem_id = w.workitem_id AND r.user_id = :user_id
					LEFT JOIN user_workitem_subscription AS s USING(type_id, project_id)
					WHERE s.user_id = :user_id AND s.subscription_type=1 AND s.enabled=1 AND (r.workitem_id = 0 OR r.workitem_id IS NULL)
					ORDER BY creation_time DESC
					LIMIT :limit
					]")
			else
				l_selection := select_query.execute_query ("[
					SELECT w.workitem_id, w.type_id, w.creation_time, w.project_id, p.name AS project_name, u.name AS user_name, IF(r.workitem_id>0, 1, 0) AS is_read FROM workitem w
					LEFT JOIN user u USING (user_id)
					LEFT JOIN project p USING (project_id)
					LEFT JOIN user_read_workitem r ON r.workitem_id = w.workitem_id AND r.user_id = :user_id
					LEFT JOIN user_workitem_subscription AS s USING(type_id, project_id)
					WHERE s.user_id = :user_id AND s.subscription_type=1 AND s.enabled=1
					ORDER BY creation_time DESC
					LIMIT :limit
					]")
			end

				-- get result and put it into an arrayed list as named tuples
			from
				create Result.make (l_selection.count)
				l_selection.start
			until
				l_selection.after
			loop
				create l_tuple
				l_tuple.workitem_id := l_selection.read_integer ("workitem_id")
				l_tuple.type := l_selection.read_integer ("type_id")
				l_tuple.creation_time := l_selection.read_integer ("creation_time")
				l_tuple.project_id := l_selection.read_integer ("project_id")
				l_tuple.project := l_selection.read_string ("project_name")
				l_tuple.user := l_selection.read_string ("user_name")
				l_tuple.is_read := l_selection.read_integer ("is_read") = 1

				Result.force (l_tuple)
				l_selection.forth
			end
		ensure
			Result_set: Result /= Void
		end

	retrieve_workitems_projects (a_user: INTEGER; a_limit: INTEGER; a_unread_only: BOOLEAN): ARRAYED_LIST[TUPLE [workitem_id: INTEGER; type: INTEGER; creation_time: INTEGER; project_id: INTEGER; project: STRING; user: STRING; is_read: BOOLEAN]] is
			-- Retrieve `a_limit' workitems for each project for `a_user'
		require
			a_user_valid: a_user > 0
			a_limit_valid: a_limit > 0
		local
			l_project_selection: SELECTION_RESULT
			l_project_id_list: ARRAYED_LIST[INTEGER]
		do
			-- fetch project ids from db
			select_query.set_map_name (a_user, "user_id")
			l_project_selection := select_query.execute_query ("[
					SELECT DISTINCT s.project_id FROM user_workitem_subscription AS s
					WHERE s.user_id = :user_id AND s.subscription_type = 1 AND s.enabled = 1
					]")

			-- convert selection result into list
			from
				create l_project_id_list.make(l_project_selection.count)
				l_project_selection.start
			until
				l_project_selection.after
			loop
				l_project_id_list.force(l_project_selection.read_integer ("project_id"))
				l_project_selection.forth
			end

			Result := retrieve_workitems_projects_internal (a_user, a_limit, a_unread_only, l_project_id_list);
		end

	retrieve_workitems_project (a_project_id: INTEGER; a_user: INTEGER; a_limit: INTEGER; a_unread_only: BOOLEAN): ARRAYED_LIST [TUPLE [workitem_id: INTEGER; type: INTEGER; creation_time: INTEGER; project_id: INTEGER; project: STRING; user: STRING; is_read: BOOLEAN]] is
			-- Retrieve `a_limit' workitems for project with id `a_project_id'
		require
			a_user_valid: a_user > 0
			a_limit_valid: a_limit > 0
			a_project_id_valid: a_project_id > 0
		local
			l_project_id_list: ARRAYED_LIST [INTEGER]
		do
			create l_project_id_list.make (1)
			l_project_id_list.force (a_project_id)
			Result := retrieve_workitems_projects_internal (a_user, a_limit, a_unread_only, l_project_id_list);
		end

	retrieve_workitem_ids_project (a_project: INTEGER): ARRAYED_LIST [INTEGER] is
			-- Retrieve workitem ids for a_project
		require
			a_project_valid: a_project > 0
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_project, "project_id")
			l_selection := select_query.execute_query ("[
					SELECT workitem_id FROM workitem
					WHERE project_id = :project_id
					]")
			from
				create Result.make(l_selection.count)
				l_selection.start
			until
				l_selection.after
			loop
				Result.force(l_selection.read_integer ("workitem_id"))
				l_selection.forth
			end

		ensure
			Result_set: Result /= Void
		end

	retrieve_workitem_types (): DS_ARRAYED_LIST[INTEGER] is
			-- Retrieve workitem types
		local
			l_selection: SELECTION_RESULT
		do
			l_selection := select_query.execute_query ("[
					SELECT type_id FROM workitem_type
					]")
			from
				create Result.make(l_selection.count)
				l_selection.start
			until
				l_selection.after
			loop
				Result.force_last (l_selection.read_integer ("type_id"))
				l_selection.forth
			end

		ensure
			Result_set: Result /= Void
		end

	retrieve_workitem_by_id (a_workitem_id: INTEGER) is
			-- Retrieve workitem with id a_workitem_id
		require
			a_workitem_id_valid: a_workitem_id > 0
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_workitem_id, "workitem_id")
			l_selection := select_query.execute_query ("[
					SELECT w.workitem_id, w.type_id, w.creation_time, w.project_id, p.name AS project_name, u.name AS user_name FROM workitem w
					LEFT JOIN user u USING (user_id)
					LEFT JOIN project p USING (project_id)
					WHERE w.workitem_id=:workitem_id
					]")


			if l_selection.count = 1 then
				is_found := True
				l_selection.start
				create last_workitem
				last_workitem.workitem_id := l_selection.read_integer ("workitem_id")
				last_workitem.type := l_selection.read_integer ("type_id")
				last_workitem.creation_time := l_selection.read_integer ("creation_time")
				last_workitem.project_id := l_selection.read_integer ("project_id")
				last_workitem.project := l_selection.read_string ("project_name")
				last_workitem.user := l_selection.read_string ("user_name")
			else
				is_found := False
			end

		ensure
			is_found_implies_workitem: is_found implies last_workitem /= Void
		end

	retrieve_mail_addresses (a_workitem_id: INTEGER): ARRAYED_LIST [STRING]
			-- Retrieve mail addresses for a_workitem_id
		require
			a_workitem_id_valid: a_workitem_id > 0
		local
			l_selection: SELECTION_RESULT
			l_type: INTEGER
			l_project: INTEGER
		do
				-- get project and type of workitem
			select_query.set_map_name (a_workitem_id, "workitem_id")
			l_selection := select_query.execute_query ("[
					SELECT type_id, project_id FROM workitem
					WHERE workitem_id = :workitem_id
					]")

			l_selection.start
			l_type := l_selection.read_integer ("type_id")
			l_project := l_selection.read_integer ("project_id")

				-- get mail addresses
			if l_type = 1 then
					-- issue

					-- check if issue is private
				select_query.set_map_name (a_workitem_id, "workitem_id")
				l_selection := select_query.execute_query ("[
						SELECT i.private
						FROM workitem_issue w
						LEFT JOIN issue_revision r USING (issue_revision_id)
						LEFT JOIN issue i USING (issue_id)
						WHERE w.workitem_id = :workitem_id
						]")
				l_selection.start
				if l_selection.read_integer ("private") = 0 then
					select_query.set_map_name (l_project, "project_id")
					select_query.set_map_name (l_type, "type_id")
					l_selection := select_query.execute_query ("[
							SELECT DISTINCT u.email FROM user AS u
							LEFT JOIN user_workitem_subscription AS s USING(user_id)
							WHERE s.project_id = :project_id AND s.type_id = :type_id AND s.subscription_type = 2 AND s.enabled = 1 AND u.disabled = 'false'
							]")
				else
					select_query.set_map_name (l_project, "project_id")
					select_query.set_map_name (l_type, "type_id")
					l_selection := select_query.execute_query ("[
							SELECT DISTINCT u.email FROM user AS u
							LEFT JOIN user_workitem_subscription AS s USING(user_id)
							LEFT JOIN user_project_association AS a USING(user_id, project_id)
							WHERE s.project_id = :project_id AND s.type_id = :type_id AND s.subscription_type = 2 AND s.enabled = 1 
							AND (a.group_id = 3 OR a.group_id = 4) AND u.disabled = 'false'
							]")
				end
			elseif l_type = 3 then
					-- commit

					-- check if project is closed source
				select_query.set_map_name (l_project, "project_id")
				l_selection := select_query.execute_query ("[
						SELECT closed_source FROM project
						WHERE project_id = :project_id
						]")
				l_selection.start
				if l_selection.read_string ("closed_source").out.is_equal("false") then
					select_query.set_map_name (l_project, "project_id")
					select_query.set_map_name (l_type, "type_id")
					l_selection := select_query.execute_query ("[
							SELECT DISTINCT u.email FROM user AS u
							LEFT JOIN user_workitem_subscription AS s USING(user_id)
							WHERE s.project_id = :project_id AND s.type_id = :type_id AND s.subscription_type = 2 AND s.enabled = 1 AND u.disabled = 'false'
							]")
				else
					select_query.set_map_name (l_project, "project_id")
					select_query.set_map_name (l_type, "type_id")
					l_selection := select_query.execute_query ("[
							SELECT DISTINCT u.email FROM user AS u
							LEFT JOIN user_workitem_subscription AS s USING(user_id)
							LEFT JOIN user_project_association AS a USING(user_id, project_id)
							WHERE s.project_id = :project_id AND s.type_id = :type_id AND s.subscription_type = 2 AND s.enabled = 1
							AND (a.group_id = 3 OR a.group_id = 4) AND u.disabled = 'false'
							]")
				end

			elseif l_type = 4 then
					-- wiki

					-- check if wiki page is private
				select_query.set_map_name (a_workitem_id, "workitem_id")
				l_selection := select_query.execute_query ("[
						SELECT private FROM workitem_wiki
						WHERE workitem_id = :workitem_id
						]")
				l_selection.start
				if l_selection.read_integer ("private") = 0 then
					select_query.set_map_name (l_project, "project_id")
					select_query.set_map_name (l_type, "type_id")
					l_selection := select_query.execute_query ("[
							SELECT DISTINCT u.email FROM user AS u
							LEFT JOIN user_workitem_subscription AS s USING(user_id)
							WHERE s.project_id = :project_id AND s.type_id = :type_id AND s.subscription_type = 2 AND s.enabled = 1 AND u.disabled = 'false'
							]")
				else
					select_query.set_map_name (l_project, "project_id")
					select_query.set_map_name (l_type, "type_id")
					l_selection := select_query.execute_query ("[
							SELECT DISTINCT u.email FROM user AS u
							LEFT JOIN user_workitem_subscription AS s USING(user_id)
							LEFT JOIN user_project_association AS a USING(user_id, project_id)
							WHERE s.project_id = :project_id AND s.type_id = :type_id AND s.subscription_type = 2 AND s.enabled = 1 AND u.disabled = 'false'
							AND (a.group_id = 3 OR a.group_id = 4)
							]")
				end

			else
					-- other workitems
				select_query.set_map_name (l_project, "project_id")
				select_query.set_map_name (l_type, "type_id")
				l_selection := select_query.execute_query ("[
						SELECT DISTINCT u.email FROM user AS u
						LEFT JOIN user_workitem_subscription AS s USING(user_id)
						WHERE s.project_id = :project_id AND s.type_id = :type_id AND s.subscription_type = 2 AND s.enabled = 1 AND u.disabled = 'false'
						]")
			end

			from
				create Result.make (l_selection.count)
				l_selection.start
			until
				l_selection.after
			loop
				Result.force (l_selection.read_string ("email"))
				l_selection.forth
			end
		ensure
			Result_set: Result /= Void
		end

	retrieve_tag_id (a_tag: STRING): INTEGER
			-- Retrieve id of a_tag
		require
			a_tag_ok: a_tag /= Void and then not a_tag.is_empty
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_tag, "a_tag")
			l_selection := select_query.execute_query ("SELECT tag_id FROM tag_text WHERE text=:a_tag")
			if l_selection.is_empty then
				Result := 0
			else
				l_selection.start
				Result := l_selection.read_integer ("tag_id")
			end
		end

	retrieve_release_workitem_id_by_release_id (a_release_id: INTEGER): INTEGER is
			-- Retrieve a release workitem ID for a given release ID a_release_id
		require
			a_release_id_valid: a_release_id > 0
		local
			l_selection: SELECTION_RESULT
		do
			select_query.set_map_name (a_release_id, "release_id")
			l_selection := select_query.execute_query ("SELECT workitem_id FROM workitem_release WHERE release_id=:release_id")
			if l_selection.is_empty then
				Result := 0
			else
				l_selection.start
				Result := l_selection.read_integer ("workitem_id")
			end
		end

feature {NONE} -- Internal select queries

	retrieve_workitems_projects_internal (a_user: INTEGER; a_limit: INTEGER; a_unread_only: BOOLEAN; project_ids: ARRAYED_LIST[INTEGER]): ARRAYED_LIST[TUPLE [workitem_id: INTEGER; type: INTEGER; creation_time: INTEGER; project_id: INTEGER; project: STRING; user: STRING; is_read: BOOLEAN]] is
			-- Helper method for `retrieve_workitems_project' and `retrieve_workitems_projects'
			-- Retrieve `a_limit' workitems for all projects whose ids are contained in `project_ids'.
		require
			a_user_valid: a_user > 0
			a_limit_valid: a_limit > 0
			project_ids_valid: project_ids /= Void
		local
			l_selection: SELECTION_RESULT
			l_tuple: TUPLE [workitem_id: INTEGER; type: INTEGER; creation_time: INTEGER; project_id: INTEGER; project: STRING; user: STRING; is_read: BOOLEAN]
			l_project_id: INTEGER
		do
			from
				create Result.make (a_limit * 5)
				project_ids.start
			until
				project_ids.after
			loop
				l_project_id := project_ids.item

				select_query.set_map_name (a_limit, "limit")
				select_query.set_map_name (l_project_id, "project_id")
				select_query.set_map_name (a_user, "user_id")
				if a_unread_only then
					l_selection := select_query.execute_query ("[
						SELECT w.workitem_id, w.type_id, w.creation_time, w.project_id, p.name AS project_name, u.name AS user_name, IF(r.workitem_id>0, 1, 0) AS is_read FROM workitem w
						LEFT JOIN user u USING (user_id)
						LEFT JOIN project p USING (project_id)
						LEFT JOIN user_read_workitem r ON r.workitem_id = w.workitem_id AND r.user_id = :user_id
						LEFT JOIN user_workitem_subscription AS s USING(type_id, project_id)
						WHERE p.project_id = :project_id AND (r.workitem_id = 0 OR r.workitem_id IS NULL) AND s.user_id = :user_id AND s.subscription_type=1 AND s.enabled=1
						ORDER BY w.creation_time DESC
						LIMIT :limit
						]")
				else
					l_selection := select_query.execute_query ("[
						SELECT w.workitem_id, w.type_id, w.creation_time, w.project_id, p.name AS project_name, u.name AS user_name, IF(r.workitem_id>0, 1, 0) AS is_read FROM workitem w
						LEFT JOIN user u USING (user_id)
						LEFT JOIN project p USING (project_id)
						LEFT JOIN user_read_workitem r ON r.workitem_id = w.workitem_id AND r.user_id = :user_id
						LEFT JOIN user_workitem_subscription AS s USING(type_id, project_id)
						WHERE p.project_id = :project_id AND s.user_id = :user_id AND s.subscription_type=1 AND s.enabled=1
						ORDER BY w.creation_time DESC
						LIMIT :limit
						]")
				end

					-- get result and put it into an arrayed list as named tuples
				from
					l_selection.start
				until
					l_selection.after
				loop
					create l_tuple
					l_tuple.workitem_id := l_selection.read_integer ("workitem_id")
					l_tuple.type := l_selection.read_integer ("type_id")
					l_tuple.creation_time := l_selection.read_integer ("creation_time")
					l_tuple.project_id := l_selection.read_integer ("project_id")
					l_tuple.project := l_selection.read_string ("project_name")
					l_tuple.user := l_selection.read_string ("user_name")
					l_tuple.is_read := l_selection.read_integer ("is_read") = 1

					Result.force (l_tuple)
					l_selection.forth
				end

				project_ids.forth
			end

		ensure
			Result_set: Result /= Void
		end

feature -- Insert queries

	insert_read_workitem (a_user_id: INTEGER; a_workitem_id: INTEGER) is
			-- Add a_workitem_id as a new read workitem.
		require
			a_user_id_valid: a_user_id > 0
			a_workitem_id_valid: a_workitem_id > 0
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_user_id, "user_id")
			l_store.set_map_name (a_workitem_id, "workitem_id")
			l_store.modify ("INSERT IGNORE INTO user_read_workitem SET user_id=:user_id, workitem_id=:workitem_id")

			handle_errors_and_warnings
		end

	insert_workitem (a_workitem: WORKITEM) is
			-- Add a_workitem as a new workitem.
		require
			a_workitem_not_void: a_workitem /= Void
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_workitem.project_id, "project_id")
			l_store.set_map_name (a_workitem.user_id, "user_id")
			l_store.set_map_name (a_workitem.type_id, "type_id")
			l_store.modify ("INSERT INTO workitem SET project_id=:project_id, user_id=:user_id, type_id=:type_id, creation_time=UNIX_TIMESTAMP()")

			handle_errors_and_warnings
		end

	insert_tag_text (a_tag: STRING) is
			-- Add a_tag as a new tag text
		require
			a_tag_not_void: a_tag /= Void
		local
			l_store: DB_CHANGE
		do
			create l_store.make
			l_store.set_map_name (a_tag, "a_tag")
			l_store.modify ("INSERT IGNORE INTO tag_text SET text=:a_tag")

			handle_errors_and_warnings
		end

feature -- Delete queries

	delete_read_workitem (a_user_id: INTEGER; a_workitem_id: INTEGER) is
			-- Delete a workitem with a_workitem_id as a read workitem -> is then unread/new.
		require
			a_user_id_valid: a_user_id > 0
			a_workitem_id_valid: a_workitem_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_user_id, "user_id")
			l_change.set_map_name (a_workitem_id, "workitem_id")
			l_change.modify ("DELETE FROM `user_read_workitem` WHERE `user_id`=:user_id AND `workitem_id`=:workitem_id")

			handle_errors_and_warnings
		end

	delete_release_workitem (a_workitem_id: INTEGER) is
			-- Delete a release workitem with a_workitem_id.
		require
			a_workitem_id_valid: a_workitem_id > 0
		local
			l_change: DB_CHANGE
		do
			create l_change.make
			l_change.set_map_name (a_workitem_id, "workitem_id")
			l_change.modify ("DELETE FROM `workitem` WHERE `workitem_id`=:workitem_id AND `type_id`=2")

			handle_errors_and_warnings
		end

end