-- This data model is for the Software Development Module (SDM)
-- The tables were designed by Ben Adida (ben@mit.edu) and Philip Greenspun (philg@mit.edu)
-- The details of the data model were done by Ben Adida (ben@mit.edu)

-- packages
-- packages are totally independent products. One package might
-- be the ACS, while another is AOLServer.

-- ported to Postgres by Ben Adida (ben@mit.edu)

create sequence package_id_sequence;

create table packages (
	package_id		integer not null primary key,
	package_name		varchar(100) not null,
	private_p		char(1) check (private_p in ('t','f')),
	description		varchar(4000)
);

create view public_packages as select * from packages where private_p='f';

create sequence package_release_id_sequence;

create table package_releases (
	release_id			integer not null primary key,
	package_id			integer not null references packages,
	-- These version numbers are for 1.1.7, 2.0.1
	major_version			integer not null,
	minor_version			integer not null,
	patch_version			integer not null,
	beta_version			integer,
	-- Release Dates
	anticipated_release_date	datetime,
	release_date			datetime,
	manager				integer not null references users,
	general_description		varchar(4000),
	release_notes			lztext,
	supported_platforms		varchar(250),
	release_filename		varchar(250)
);
	
alter table packages add
	current_release		integer references package_releases;

create table package_admins (
	package_id		integer not null references packages,
	user_id			integer not null references users,
	primary key (package_id, user_id)
);

create sequence module_id_sequence;

create table modules (
	module_id		integer not null primary key,
	package_id		integer references packages,
	module_name		varchar(100) not null,
	owner			integer not null references users,
	private_p		char(1) check (private_p in ('t','f')),
	description		varchar(4000)
);

create view public_modules as select * from modules where private_p='f';

-- A table for assigning users to modules, so that they may see
-- the module if the module is private, and later upload code to the
-- module

create table module_users (
	module_id		integer not null references modules,
	user_id			integer not null references users,
	primary key(module_id, user_id)
);


-- procs to determine if a user can see/edit packages/modules.
-- because of Oracle deadlock issues, these should be selected
-- from dual and usually not at the same time as other columns
-- are selected from tables.

create function user_can_edit_package_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_package_id alias for $2;
	v_count integer;
BEGIN
	select count(*) into v_count from package_admins where package_id= v_package_id and user_id= v_user_id;
	IF (v_count > 0)
	THEN return(''t'');
	ELSE return(''f'');
	END IF;
END;
' language 'plpgsql';

create function user_can_edit_module_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_module_id alias for $2;
	v_count integer;
BEGIN
	select count(*) into v_count from modules where module_id= v_module_id and owner= v_user_id;
	if v_count > 0
	THEN return(''t'');
	END IF;	
	
	return(''f'');
END;
' language 'plpgsql';

create function user_can_see_package_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_package_id alias for $2;
	v_count integer;
BEGIN
	select count(*) into v_count from public_packages where package_id= v_package_id;
	IF (v_count > 0)
	THEN return(''t'');
	END IF;

	select count(*) into v_count from module_users where module_id IN (select module_id from modules where package_id= v_package_id) and user_id= v_user_id;
	IF (v_count > 0)
	THEN return(''t'');
	END IF;

	IF (user_can_edit_package_p(v_user_id, v_package_id)=''t'')
	THEN return(''t'');
	END IF;

	return(''f'');
END;
' language 'plpgsql';

create function user_can_see_module_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_module_id alias for $2;
	v_count integer;
	v_package_id integer;
BEGIN
	select count(*) into v_count from public_modules where module_id= v_module_id;
	IF (v_count > 0)
	THEN return(''t'');
	END IF;

	select count(*) into v_count from module_users where user_id= v_user_id and module_id= v_module_id;
	IF (v_count > 0)
	THEN return(''t'');
	END IF;

	select package_id into v_package_id from modules where module_id= v_module_id;
	IF (user_can_edit_package_p(v_user_id, v_package_id)=''t'')
	THEN return(''t'');
	END IF;
	
	IF (user_can_edit_module_p(v_user_id, v_module_id)=''t'')
	THEN return(''t'');
	END IF;

	return(''f'');
		
END;
' language 'plpgsql';



-- This table describes relationships between modules, and
-- who owns the "glue" between them
create table module_relationships (
	first_module		integer not null references modules,
	second_module		integer not null references modules,
	owner			integer not null references users,
	primary key (first_module, second_module)
);

create table package_release_downloads (
	package_id			integer not null references packages,
	release_id			integer not null references package_releases,
	download_date			datetime,
	ip_address			varchar(15)
);

create function release_lessthan_p(integer, integer, integer, integer, integer, integer, integer, integer)
returns char as '
DECLARE
	first_major alias for $1;
	first_minor alias for $2;
	first_patch alias for $3;
	first_beta alias for $4;
	second_major alias for $5;
	second_minor alias for $6;
	second_patch alias for $7;
	second_beta alias for $8;
BEGIN	
	IF first_major < second_major 
	THEN RETURN ''t''; 
	END IF;

	IF first_major > second_major
	THEN RETURN ''f'';
	END IF;

	IF first_minor < second_minor 
	THEN RETURN ''t''; 
	END IF;

	IF first_minor > second_minor
	THEN RETURN ''f'';
	END IF;

	IF first_patch < second_patch 
	THEN RETURN ''t''; 
	END IF;

	IF first_patch > second_patch
	THEN RETURN ''f'';
	END IF;

	IF first_beta is NULL
	THEN RETURN ''f'';
	END IF;

	IF second_beta is NULL
	THEN RETURN ''t'';
	END IF;

	IF first_beta > second_beta
	THEN RETURN ''t'';
	END IF;
		
	RETURN ''f'';
END;
' language 'plpgsql';

create function release_name(integer,integer,integer,integer)
returns varchar as '
DECLARE
	major_version alias for $1;
	minor_version alias for $2;
	patch_version alias for $3;
	beta_version alias for $4;
BEGIN
	IF major_version IS NULL
	THEN RETURN NULL;
	END IF;

	IF beta_version is NULL or beta_version=0
	THEN
	return (major_version || ''.'' || minor_version || ''.'' || patch_version);
	ELSE
	return (major_version || ''.'' || minor_version || ''.'' || patch_version || ''b'' || beta_version);
	END IF;
END;
' language 'plpgsql';

-- Note that a baf is a bug-and-feature.
-- Since we don't want to write bug-and-feature everywhere
-- we go, we'll call a bug-and-feature a "baf" from now on.
create table baf_status (
       baf_status_id	integer not null primary key,
       baf_status	varchar(100)
);

insert into baf_status values (1, 'open');
insert into baf_status values (2, 'fixed');
insert into baf_status values (3, 'closed');
insert into baf_status values (4, 'reopened');

create sequence baf_id_sequence start 5;

create table bugs_and_features (
	baf_id				integer not null primary key,
	old_baf_id			integer references bugs_and_features,
	baf_type			varchar(20) not null check (baf_type in ('bug', 'feature')),
	package_id			integer not null references packages,
	module_id			integer references modules,
	baf_status			integer not null references baf_status,
	last_updated_by			integer references users,
	-- who pointed this out
	entered_by			integer not null references users,
	-- who's working on this
	-- we're moving to a mapping table
	-- assigned_user			references users,
	-- a priority between 1 and 9, 1 being the highest priority
	severity		        varchar(20) not null,
	insertion_date			datetime,
	expected_completion		integer references package_releases,
	completion			integer references package_releases,
	description			lztext	
);

create index baf_type_index on bugs_and_features(baf_type);

create view open_bafs as select * from bugs_and_features where completion is NULL;

-- In case we want to easily view these separately
create view bugs as select * from bugs_and_features where baf_type='bug';
create view open_bugs as select * from bugs where completion is NULL;
create view closed_bugs as select * from bugs where completion is not NULL;

create view features as select * from bugs_and_features where baf_type='feature';
create view open_features as select * from features where completion is NULL;
create view closed_features as select * from features where completion is not NULL;


-- BAF Auditing
create table baf_audit (
       baf_id	       integer not null,
       who	       varchar(250),
       what	       varchar(250),
       old_value       varchar(250),
       new_value       varchar(250),
       audit_date       datetime
);

create index baf_audit_baf_id on baf_audit(baf_id);
create index baf_audit_audit_date on baf_audit(audit_date);

create function trig_baf_audit() returns opaque
as '
DECLARE
	the_name	varchar(200);
BEGIN
	select first_names || '' '' || last_name into the_name
	from users where user_id= NEW.last_updated_by;

	IF OLD.baf_type != NEW.baf_type
	THEN 
	insert into baf_audit (baf_id, who, what, old_value, new_value, audit_date)
	values (NEW.baf_id, the_name, ''baf_type'', OLD.baf_type, NEW.baf_type, sysdate());
	END IF;

	IF OLD.baf_status != NEW.baf_status
	THEN 
	insert into baf_audit (baf_id, who, what, old_value, new_value, audit_date)
	values (NEW.baf_id, the_name, ''baf_status'', sdm_get_baf_status(OLD.baf_status), sdm_get_baf_status(NEW.baf_status), sysdate());
	END IF;

	IF OLD.severity != NEW.severity
	THEN 
	insert into baf_audit (baf_id, who, what, old_value, new_value, audit_date)
	values (NEW.baf_id, the_name, ''severity'', OLD.severity, NEW.severity, sysdate());
	END IF;

	IF OLD.expected_completion != NEW.expected_completion
	THEN 
	insert into baf_audit (baf_id, who, what, old_value, new_value, audit_date)
	values (NEW.baf_id, the_name, ''expected_completion'', fetch_release_name(OLD.expected_completion), fetch_release_name(NEW.expected_completion), sysdate());
	END IF;

	return NEW;
END;
' language 'plpgsql';

create trigger baf_audit
after update on bugs_and_features
for each row
execute procedure trig_baf_audit();

create function trig_lowlevel_baf_audit() returns opaque
as '
DECLARE
	v_baf_type_text varchar(100);
	v_bogus integer;
BEGIN
	IF NEW.what=''baf_type''
	THEN
	v_bogus:= baf_edit_notification(NEW.baf_id::integer, ''This '' || NEW.old_value || '' is now classified as a '' || NEW.new_value);
	ELSE
	v_bogus:= baf_edit_notification(NEW.baf_id::integer, NEW.what || '' changed from '' || NEW.old_value || '' to '' || NEW.new_value);
	END IF;
	
	RETURN NEW;
END;
' language 'plpgsql';

create trigger lowlevel_baf_audit
before insert on baf_audit
for each row
execute procedure trig_lowlevel_baf_audit();

create function user_can_edit_baf_p(integer, integer)
returns char
as '
DECLARE
	v_user_id alias for $1;
	v_baf_id alias for $2;
	v_check integer;
	v_check_char char(1);
BEGIN
	select user_can_edit_module_p(v_user_id, module_id) into v_check_char
	from bugs_and_features where baf_id= v_baf_id;

	if v_check_char = ''t''
	then return ''t'';
	end if;

	select user_can_edit_package_p(v_user_id, package_id) into v_check_char
	from bugs_and_features where baf_id= v_baf_id;

	if v_check_char = ''t''
	then return ''t'';
	end if;

	select count(*) into v_check from baf_assignments where baf_id= v_baf_id and user_id= v_user_id;
	if v_check > 0
	then return ''t'';
	end if;

	select count(*) into v_check from bugs_and_features where baf_id= v_baf_id and entered_by= v_user_id;
	if v_check > 0
	then return ''t'';
	end if;

	return ''f'';
END;
' language 'plpgsql';

-- A way for users to rank the importance of a bug
create table baf_ratings (
       user_id		 integer not null references users,
       baf_id		 integer not null references bugs_and_features,
       primary key(user_id, baf_id),
       rating		 integer not null check (rating between 1 and 10),
       rating_date	 datetime
);


-- Who's assigned to what bugs
create table baf_assignments (
       baf_id		     integer not null references bugs_and_features,
       user_id		     integer not null references users,
       role		     varchar(100),
       assignment_date	     datetime
);       

-- Sometimes, bugs apply to releases before the date when they were found.
create table bug_release_map (
	bug_id		integer not null references bugs_and_features,
	release_id	integer not null references package_releases,
	discovery_date	datetime ,
	primary key (bug_id, release_id)
);

-- We want to map what a user might be interested in
-- this is mostly for bugs/features, but might be for
-- tasks
-- for now, let's see how it works with just bafs.

create table user_baf_interest_map (
	user_id			integer not null references users,
	baf_id			integer not null references bugs_and_features,
	primary key (user_id, baf_id)
);

-- User Interests in Packages

create table user_package_interest_map (
       user_id			       integer not null references users,
       package_id		       integer not null references packages,
       primary key (user_id, package_id)
);

-- comments on bugs and features

create view baf_comments as select * from general_comments where on_which_table='bugs_and_features';


--
-- Here we get into the source repository stuff
-- ben@mit.edu
--

create table package_repositories (
	package_id		integer not null primary key references packages,
	repository_name		varchar(100) not null,
	file_glob_patterns	varchar(200)
);

create function package_has_repository_p(integer)
returns char as '
DECLARE
	v_package_id alias for $1;
	v_count integer;
BEGIN
	select count(*) into v_count from package_repositories where package_id= v_package_id;

	if (v_count > 0)
	THEN return ''t'';
	ELSE return ''f'';
	END IF;
END;
' language 'plpgsql';


--
-- The CVS specific stuff

create table cvs_package_data (
	package_id		integer not null primary key references packages,
	cvs_server		varchar(200),
	cvs_username		varchar(100),
	cvs_password		varchar(100),
	cvs_path		varchar(200),
	cvs_package_name	varchar(200)
);

--
-- Spamming stuff
-- 
create table sdm_notification_prefs (
       user_id			    integer not null primary key references users,
       package_pref		    varchar(50) default 'none' check (package_pref in ('none', 'hourly', 'daily', 'weekly')),
       baf_pref			    varchar(50) default 'none' check (baf_pref in ('none', 'hourly', 'daily', 'weekly'))
);


create table sdm_notifications (
       user_id		       integer not null primary key references users,
       baf_notifications       lztext,
       package_notifications   lztext
);

-- PL/SQL to do notifications
create function baf_edit_notification(integer, varchar) returns integer
as '
DECLARE
	v_baf_id alias for $1;
	v_message alias for $2;
	v_one_baf	bugs_and_features%ROWTYPE;
BEGIN
	select * into v_one_baf from bugs_and_features where baf_id= v_baf_id;

	update sdm_notifications set
	baf_notifications= coalesce(baf_notifications,'''') || to_char(sysdate(),''YYYY-MM-DD'') || '' '' || v_one_baf.baf_type || '' #'' || v_baf_id ||
			   '': '' || v_message || ''\n'' || substr(v_one_baf.description,0,60) || ''... \n\n''
	where user_id in (select user_id from user_baf_interest_map where baf_id= v_baf_id);

	return 1;
END;
' language 'plpgsql';

create function package_edit_notification(integer, varchar) returns integer
as '
DECLARE
	v_package_id alias for $1;
	v_message alias for $2;
	v_one_package	packages%ROWTYPE;
BEGIN
	select * into v_one_package from packages where package_id= v_package_id;
	
	update sdm_notifications set
	package_notifications= coalesce(package_notifications,'''') || to_char(sysdate(),''YYYY-MM-DD'') || '' '' || v_one_package.package_name || '': '' ||
			       v_message || ''\n''
	where user_id in (select user_id from user_package_interest_map where package_id= v_package_id);

	return 1;
END;
' language 'plpgsql';


--
-- VIEWS!
--

create view packages_with_cvs_data as select packages.*,cvs_server, cvs_username, cvs_password, cvs_path, cvs_package_name from packages,cvs_package_data where packages.package_id=cvs_package_data.package_id;


--
-- Procs for Postgres no Outer Joins
--

create function baf_module(integer)
returns varchar as '
DECLARE
	v_baf_id alias for $1;
	the_name varchar(100);
BEGIN
	select module_name into the_name from modules where module_id=
	       (select module_id from bugs_and_features where baf_id= v_baf_id);

	return the_name;
END;
' language 'plpgsql';


create function fetch_release_name(integer)
returns varchar as '
DECLARE
	v_release_id alias for $1;
	v_release_name varchar(100);
BEGIN
	select release_name(major_version, minor_version, patch_version, beta_version)
	into v_release_name
	from package_releases where release_id = v_release_id;

	return v_release_name;
END;
' language 'plpgsql';


create function get_baf_status_id(varchar)
returns integer as '
DECLARE
	status_name alias for $1;
BEGIN
	return baf_status_id from baf_status where baf_status= status_name;
END;
' language 'plpgsql';


create function sdm_get_baf_status(integer)
returns varchar as '
DECLARE
	v_baf_status_id alias for $1;
BEGIN
	return baf_status from baf_status where baf_status_id= v_baf_status_id;
END;
' language 'plpgsql';


create function baf_rating(integer)
returns numeric
as '
DECLARE
	v_baf_id alias for $1;
BEGIN
	return avg(rating) from baf_ratings where baf_id= v_baf_id;
END;
' language 'plpgsql';

create function baf_n_interested(integer)
returns numeric
as '
DECLARE
	v_baf_id alias for $1;
BEGIN
	return count(*) from user_baf_interest_map where baf_id= v_baf_id;
END;
' language 'plpgsql';

-- Stuff about assignments
create function sdm_baf_assigned(integer)
returns varchar as '
DECLARE
	v_baf_id alias for $1;
	v_assigned     varchar(200);
	v_one_row      users%ROWTYPE;
BEGIN
	v_assigned:= '''';

	FOR v_one_row IN select * from users where user_id in 
	(select user_id from baf_assignments where baf_id= v_baf_id) LOOP
	    IF v_assigned!=''''
	    then v_assigned:= v_assigned || '', '';
	    end if;

	    v_assigned:= v_assigned || v_one_row.first_names || '' '' || v_one_row.last_name;
	END LOOP;

	IF v_assigned= ''''
	then v_assigned:= ''(no one)'';
	end if;

	return v_assigned;
END;
' language 'plpgsql';

-- another function for outer join problems
create function sdm_completion_date(integer)
returns datetime as '
DECLARE
	v_baf_id alias for $1;
	v_completion integer;
BEGIN
	select completion into v_completion from bugs_and_features where baf_id= v_baf_id;
	if v_completion is NULL
	then return null;
	else return sysdate();
	end if;
END;
' language 'plpgsql';

create function sdm_baf_due_date(integer)
returns datetime as '
DECLARE
	v_baf_id alias for $1;
	v_release_id integer;
BEGIN
	select expected_completion into v_release_id from bugs_and_features;

	if v_release_id is null
	then return NULL;
	end if;

	return anticipated_release_date from package_releases where release_id= v_release_id;
END;
' language 'plpgsql';


-- Accepting patches

create sequence sdm_patch_id_sequence;

create table sdm_package_patches (
       patch_id			 integer not null primary key,
       user_id			 integer not null references users,
       package_id		 integer not null references packages,
       package_release_id	 integer not null references package_releases,
       submission_date		 datetime,
       patch_file		 varchar(200),
       patch_description	 varchar(4000),
       accepted_p		 char(1) check (accepted_p in ('t','f')),
       action_user		 integer references users,
       action_date		 datetime,
       action_description	 varchar(4000)
);

create table sdm_package_patch_ratings (
       patch_id			       integer not null references sdm_package_patches,
       user_id			       integer not null references users,
       primary key (patch_id, user_id),
       numeric_rating		       integer check (numeric_rating between 1 and 10),
       description		       varchar(4000),
       rating_date		       datetime
);

create function sdm_package_patch_rating(integer)
returns float
as '
DECLARE
	v_patch_id alias for $1;
BEGIN
	return avg(numeric_rating) from sdm_package_patch_ratings where patch_id= v_patch_id;
END;
' language 'plpgsql';