--
-- contest system data model
--
-- created  6/29/97 by Philip Greenspun (philg@mit.edu)
-- modified 1/18/98 by Cotton Seed (cottons@arsdigita.com)
-- modified 11/26/98 by Philip Greenspun to integrate
-- with community data model
-- modified 3/10/00 by Mark Dalrymple (markd@arsdigita.com) to
-- use integer primary keys instead of characters
-- modified 04/14/00 by Malte Sussdorff (malte@arsdigita.com) to
-- add voting from arsdigita.org and naming constraints

-- Code actually stolen from the event module. Taken unchanged. And not working. Propably have to upgrade the installation.

create or replace procedure administration_subgroup_add (pretty_name IN
varchar, v_short_name IN varchar, v_module IN varchar, v_submodule IN
varchar, v_multi_role_p IN varchar, v_url IN varchar,
v_parent_module IN varchar)
IS
  v_group_id    integer;
  n_administration_groups integer;
  v_system_user_id integer;
  v_parent_id integer;
BEGIN
  if v_submodule is null then
      select count(group_id) into n_administration_groups
        from administration_info
        where module = v_module
        and submodule is null;
      else
        select count(group_id) into n_administration_groups
         from administration_info  
         where module = v_module
         and submodule = v_submodule;
  end if;
  if n_administration_groups = 0 then
     -- call procedure defined in community-core.sql to get system user
     v_system_user_id := system_user_id;
     select user_group_sequence.nextval into v_group_id from dual;
     insert into user_groups
      (group_id, group_type, short_name, group_name, creation_user, creation_ip_address, approved_p, existence_public_p, new_member_policy, multi_role_p)
      values
      (v_group_id, 'administration', v_short_name, pretty_name, v_system_user_id, '0.0.0.0', 't', 'f', 'closed', v_multi_role_p);  
     insert into administration_info (group_id, module, submodule, url) values (v_group_id, v_module, v_submodule, v_url);
   end if;

   Begin
      select ai.group_id into v_parent_id
      from administration_info ai, user_groups ug
      where ai.module = v_parent_module
      and ai.group_id != v_group_id
      and ug.group_id = ai.group_id
      and ug.parent_group_id is null;
   Exception when others then null;
   End;

   update user_groups
   set parent_group_id = v_parent_id
   where group_id = v_group_id;
end;
/
show errors  

--- create the administration group for the Contest module
begin
   administration_group_add ('Contest Administration', 'contest', 'contest', '', 'f', '/contest/admin/');
end;
/    


create sequence contest_domain_id_sequence;

-- Each contest has it's own domain, where all the information about the contest is stored

create table contest_domains (
	domain_id		integer 
				constraint cd_domain_id_pk 
				not null primary key,
	domain			varchar(21) 
				constraint c_domain_un not null unique,
	-- the unique constraint creates an index for us
	entrants_table_name	varchar(30),
	-- name of the table for the contest (contest_entrants_$domain_id)
	pretty_name		varchar(100) not null,
	-- where the contest starts
	home_url	varchar(200),
	-- arbitrary HTML text that goes at the top of 
	-- the auto-generated entry form
	blather		varchar(4000),
	-- where to send users after they enter
	-- (if blank, we use a generated form)
	post_entry_url	varchar(200),
	confirm_entry	varchar(4000),
	maintainer	constraint cd_maintainer_fk
			not null references users(user_id),
	notify_of_additions_p	char(1) default 'f'
			constraint cd_notify_of_addtions_p_ck 
			check (notify_of_additions_p in ('t', 'f')),  -- send email when a person enters
	us_only_p	char(1) default 'f' 
			constraint cd_us_only_p_ck check (us_only_p in ('t', 'f')),
	start_date	date,	-- these are optional
	end_date	date,
	voting_p	char(1) 
			constraint cd_voting_p_ck check(voting_p in ('t','f'))
);

-- Contest might need additional information (actually always do) besides the standard values, therefore extra columns information are stored here.

create table contest_extra_columns (
	domain_id		not null
				constraint cec_domain_id_fk references contest_domains,
	column_pretty_name	varchar(200),
	column_actual_name	varchar(200) not null,
	column_type		varchar(200) not null,	-- things like 'boolean' or 'text'
	column_extra_sql	varchar(200),	-- things like 'not null' or 'default 5'
	-- entry form will sort by this column
	sort_column		integer,
	constraint cec_domain_id_actual_name_pk primary key (domain_id, column_actual_name)
);


--
-- every contest will be created with a table named
-- contest_entrants_$domain_id ; this may have lots of extra columns 
-- 
-- here's what a default table and accompaning sequence
-- 
-- create sequence contest_entrants_1_seq;
-- 
-- create table contest_entrants_1 (
-- 	-- we don't care how many times they enter;
-- 	-- we query for "distinct" eventually
-- 	entry_date	date not null,
--	entry_id	integer not null
-- 			constraint contest_e1_entry_id_pk primary key,
-- 	user_id		not null
-- 			constraint contest_e1_user_id_fk references users,
--	status		varchar(30)
-- );
--
-- NOTE: If there is a voting_p is "t", the following column
-- will be added: 
-- alter table  contest_entrants_1 add (
-- 	title	varchar(200)
-- );

-- In here we store the votes. Per entry only one vote is allowed per user.

create table contest_votes (
	user_id		integer not null 
			constraint contest_votes_user_id_fk references users,
	entry_date	date,
	domain_id	integer 
			constraint contest_votes_domain_id_fk references contest_domains,
	entry_id	integer,
	ipaddress	varchar(100),
	integer_vote	integer,
	comments	varchar(4000),
	constraint cv_entry_user_domain_pk primary key (entry_id, user_id, domain_id)
);