--
-- == CLEAN UP ==
--
drop table askers cascade;
drop table answerers cascade;
drop table questions cascade;
drop table asker_questions cascade;
drop table answer_values cascade;
drop table answer_numeric cascade;
drop table answer_date cascade;
drop table answer_boolean cascade;
drop table answer_float cascade;
drop table answer_text cascade;
drop table answer_date_2 cascade;
drop table answer_numeric_2 cascade;
drop table answers cascade;
drop table atypes cascade;

--
-- == TABLES ==
--
create table askers (
	fid	SERIAL PRIMARY KEY,
	fname	text
);

create table answerers (
	orid	SERIAL PRIMARY KEY,
	orname	text
);

create table atypes (
	atype	text ,
	ncols	smallint,
	PRIMARY KEY (atype, ncols)
);

create table questions (
	qid		SERIAL PRIMARY KEY,
	question	text,
	atype		text,
	ncols		integer,
	FOREIGN KEY (atype, ncols) references atypes (atype, ncols)
);


create table asker_questions (
	fid	integer REFERENCES askers (fid),
	qid	integer REFERENCES questions (qid),
	fset	text,
	PRIMARY KEY (fid, qid, fset)
);

create table answer_values (
	orid	integer REFERENCES answerers (orid),
	qid	integer REFERENCES questions (qid),
	avid	SERIAL,
	atype	text,
	ncols	integer,
	PRIMARY KEY (orid, qid, avid),
	FOREIGN KEY (atype, ncols) references atypes (atype,ncols)
);

--
-- == CHILDREN ANSWER TABLES ==
--
create table answer_numeric (
	avalue	numeric 
) inherits (answer_values) ;
alter table answer_numeric alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_num_pk on answer_numeric (avid);

create table answer_date (
	avalue	date 
) inherits (answer_values) ;
alter table answer_date alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_date_pk on answer_date (avid);

create table answer_boolean (
	avalue	boolean 
) inherits (answer_values) ;
alter table answer_boolean alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_bool_pk on answer_boolean (avid);

create table answer_float (
	avalue	float 
) inherits (answer_values) ;
alter table answer_float alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_flt_pk on answer_float (avid);

create table answer_text (
	avalue	text 
) inherits (answer_values) ;
alter table answer_text alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_text_pk on answer_text (avid);

create table answer_date_2 (
	astart	date, 
	aend	date 
) inherits (answer_values) ;
alter table answer_date_2 alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_dater_pk on answer_date_2 (avid);

create table answer_numeric_2 (
	astart	numeric, 
	aend	numeric 
) inherits (answer_values) ;
create unique index answer_numr_pk on answer_numeric_2 (avid);
alter table answer_numeric_2 alter column avid  set default nextval('answer_values_avid_seq');

create table answer_addr (
	addr	text,
	city	text,
	state	char(2),
	zip	text
) inherits (answer_values) ;
alter table answer_addr alter column avid  set default nextval('answer_values_avid_seq');
create unique index answer_addr_pk on answer_addr (avid);