--
-- download.sql
-- 
-- created by philg@mit.edu on 12/28/99
-- augmented by ahmeds@mit.edu

-- supports a system for keeping track of what .tar files or whatever
-- are available to which users and who has downloaded what
--
-- e.g., we use this at ArsDigita to keep track of who has downloaded
-- our open-source toolkit (so that we can later spam them with 
-- upgrade notifications)
-- 
-- ported to Postgres by Ben Adida (ben@mit.edu).

create sequence download_id_sequence;

create table downloads (
	download_id		integer primary key,
	-- if scope=public, this is a download for the whole system
        -- if scope=group, this is a download for/from a subcommunity
        scope           varchar(20) not null,
	-- will be NULL if scope=public 
 	group_id	integer references user_groups on delete cascade,
	-- e.g., "Bloatware 2000"
	download_name	varchar(100) not null,
	directory_name	varchar(100) not null,
	description		varchar(4000),
	-- is the description in HTML or plain text (the default)
	html_p			char(1) default 'f' check(html_p in ('t','f')),
	creation_date		datetime default current_timestamp not null,
	creation_user		integer not null references users(user_id),
	creation_ip_address	varchar(50) not null,
        -- state should be consistent
	constraint download_scope_check check ((scope='group' and group_id is not null) 
                                               or (scope='public'))
);

create index download_group_idx on downloads ( group_id );

create sequence download_version_id_sequence;

create table download_versions (
	version_id	        integer primary key,
	download_id	        integer not null references downloads on delete cascade,
	-- when this can go live before the public
	release_date	        datetime not null,
	pseudo_filename	        varchar(100) not null,
	-- might be the same for a series of .tar files, we'll serve
	-- the one with the largest version_id
	version                 varchar(4000),
	version_description     varchar(4000),
	-- is the description in HTML or plain text (the default)
	version_html_p          char(1) default 'f' check(version_html_p in ('t','f')),
	status		        varchar(30) check (status in ('promote', 'offer_if_asked', 'removed')),
	creation_date		datetime default current_timestamp not null ,
	creation_user		integer references users on delete set null,
  	creation_ip_address	varchar(50) not null
  );
  
  create sequence download_rule_id_sequence start 1;
  
  create table download_rules (
  	rule_id		integer primary key,
  	-- one of the following will be not null
	version_id	integer references download_versions on delete cascade,
	download_id	integer references downloads on delete cascade,
	visibility	varchar(30) check (visibility in ('all', 'registered_users', 'purchasers', 'group_members', 'previous_purchasers')),
  	-- price to purchase or upgrade, typically NULL
  	price		numeric,
  	-- currency code to feed to CyberCash or other credit card system
	currency	char(3) default 'USD' references currency_codes ,
	constraint download_version_null_check check ( download_id is not null or version_id is not null)
  );
  
  -- PL/pgSQL proc
  -- returns 't' if a user can download, 'f' if not 
  -- if supplied user_id is NULL, this is an unregistered user and we 
  -- look for rules accordingly

  -- DRB: this probably needs work as I'm hackin', not testin'
  
create function download_authorized_p (integer, integer) returns varchar as '
declare
	v_version_id alias for $1;
	v_user_id alias for $2;
	v_visibility download_rules.visibility%TYPE;
	v_group_id downloads.group_id%TYPE;
	v_return_value varchar(30);
begin
	select visibility into v_visibility
	from download_rules
	where version_id = v_version_id;
	
	if v_visibility = ''all'' 
	then	
		return ''authorized'';
	else if v_visibility = ''group_members'' then	

		select group_id into v_group_id
		from downloads d, download_versions dv
		where dv.version_id = v_version_id
		and dv.download_id = d.download_id;

		select case when count(*) = 0 then ''not_authorized'' else ''authorized'' end into v_return_value
		from user_group_map where user_id = v_user_id 
		and group_id = v_group_id;

		return v_return_value;		
	else
		select case when count(*) = 0 then ''reg_required'' else ''authorized'' end into v_return_value
		from users where user_id = v_user_id;
		
		return v_return_value;
	end if; 
	end if;
  
END;' language 'plpgsql';
  
  -- history 
  
  create sequence download_log_id_sequence start 1;
  
create table download_log (
	log_id		integer primary key,
	version_id	integer not null references download_versions on delete cascade,
	-- user_id should reference users, but that interferes with
	-- downloadlog_user_delete_tr below.
	user_id		integer references users on delete set null,
	entry_date	datetime not null,
	ip_address	varchar(50) not null,
	-- keeps track of why people downloaded this
	download_reasons varchar(4000)
);
  
create index download_log_version_idx on download_log ( version_id );

-- DRB: only needed for PG 6.5.  If you put these back and get rid
-- of the "on delete cascades" above, please leave the cascade defs
-- commented out but left in the source so we can do this right for
-- PG V7.0.

-- create function trig_download_versions_delete_info() returns opaque as '
-- DECLARE
-- BEGIN
-- delete from download_versions
-- where download_id=OLD.download_id;
-- END;
-- ' language 'plpgsql';
-- 
-- create trigger download_versions_delete_info
-- before delete on downloads
-- for each row
-- execute procedure trig_download_versions_delete_info();
-- 
-- create function trig_downloads_rules_dload_del_tr() returns opaque as '
-- DECLARE
-- BEGIN
--    delete from download_rules
--    where download_id=OLD.download_id;
-- END;
-- ' language 'plpgsql';
-- 
-- create trigger downloads_rules_dload_del_tr
-- before delete on downloads
-- for each row
-- execute procedure trig_downloads_rules_dload_del_tr();
-- 
-- create function trig_downloads_rules_versions_del_tr() returns opaque as '
-- DECLARE
-- BEGIN
--    delete from download_rules
--    where version_id=OLD.version_id;
-- END;
-- ' language 'plpgsql';
-- 
-- create trigger downloads_rules_version_del_tr
-- before delete on download_versions
-- for each row
-- execute procedure trig_downloads_rules_versions_del_tr();
-- 
-- create function trig_download_log_user_delete_tr() returns opaque as '
-- DECLARE
-- BEGIN
--    delete from download_log
--    where user_id=OLD.user_id;
-- END;
-- ' language 'plpgsql';
-- 
-- create trigger download_log_user_delete_tr
-- before delete on users
-- for each row
-- execute procedure trig_download_log_user_delete_tr();
-- 
-- create function trig_download_log_version_delete_tr() returns opaque as '
-- DECLARE
-- BEGIN
--    delete from download_log
--    where version_id=OLD.version_id;
-- END;
-- ' language 'plpgsql';
-- 
-- create trigger download_log_version_delete_tr
-- before delete on download_versions
-- for each row
-- execute procedure trig_download_log_version_delete_tr();