--
-- polls.sql - user opinion surveys
--
-- markd@arsdigita.com 9/7/99
-- based on stuff by Ben Adida
--
-- (added integrity constraints, 9/27/99)
--

create sequence poll_id_sequence;

create table polls (
	poll_id		integer not null primary key,
	name		varchar(100) not null,
	description	varchar(4000),
	-- make the dates NULL for an on-going poll
	start_date	datetime,
	end_date	datetime,
	require_registration_p	char(1) default 'f' check (require_registration_p in ('t','f'))
);


create sequence poll_choice_id_sequence;


create table poll_choices (
	choice_id	integer not null primary key,
	poll_id		integer not null references polls,
	label		varchar(500) not null,
	sort_order	integer
);

create index poll_choices_index on poll_choices(poll_id, choice_id);



create table poll_user_choices (
	poll_id		integer references polls not null,
	choice_id	integer references poll_choices not null,
	-- user_id can be NULL if we're not requiring registration
	user_id		integer references users,
	ip_address	varchar(50) not null,
	choice_date	datetime not null
);

create index poll_user_choice_index on poll_user_choices(poll_id);
create index poll_user_choices_choice_index on poll_user_choices(choice_id);


create function poll_is_active_p(datetime, datetime) returns char as '
declare
	start_date alias for $1;
	end_date alias for $2;
	result_p char;
begin
	result_p:= ''t'';

	if (trunc(start_date) > trunc(current_timestamp))
	then result_p:=''f'';
	end if;

	if (trunc(end_date) < trunc(current_timestamp))
	then result_p:=''f'';
	end if;

	return result_p;
end;
' language 'plpgsql';