begin;
--drop schema debug cascade;
create schema debug;


create sequence debug.form_id;

create sequence debug.form_instance_id;

create sequence debug.form_q_id;



create table debug.form_template (
   fid integer primary key default nextval('debug.form_id'),    
   name text
);


create table debug.form_instance (
   finstance integer 
      primary key 
      default nextval('debug.form_instance_id'),
   fid integer not null
     references debug.form_template (fid)
     on delete cascade
     on update cascade,
   type text check (type = 'COURSE')
);

create table debug.course_form (
   finstance integer not null
     primary key
     references debug.form_instance (finstance)
     on delete cascade
     on update cascade,
   cid integer not null
--     references debug.courses (cid)
--     on delete cascade
--     on update cascade
);



create table debug.form_qtypes (
  qtype integer primary key,
  atype text not null
     check (atype = 'INT' or atype = 'TEXT')
);


create table debug.form_q (
  qid integer not null primary key 
      default nextval('debug.form_q_id'),
  fid integer not null
      references debug.form_template (fid)
      on delete cascade
      on update cascade,
  qno integer not null
      check (qno >= 0),
  qtype integer not null
      references debug.form_qtypes (qtype)
      on update cascade,
  qtext text
);


create table debug.form_q_alt (
  qid integer not null
      references debug.form_q (qid)
      on delete cascade
      on update cascade,
  altno integer not null
      check (altno >= 0),
  altvalue text not null,
  alttext text,
  primary key (qid, altno)
);


create or replace
   function debug.add_course_form(integer, integer) 
   returns integer as '
DECLARE
   my_fid ALIAS FOR $1;
   my_cid ALIAS FOR $2;
   finst integer;   
BEGIN
   select nextval(''debug.form_instance_id'') into finst;
   insert into debug.form_instance (finstance, fid, type)
	values(finst, my_fid, ''COURSE'');
   insert into debug.course_form (finstance, cid)
	values (finst, my_cid);
   return finst;
END
' 
language 'plpgsql';



create or replace
   function debug.get_atype(integer) 
   returns text stable as '
DECLARE
   my_qid ALIAS FOR $1;
   my_qtype integer;
BEGIN
   select qtype 
       from debug.form_q 
       where qid=my_qid
       into my_qtype;  
   if my_qtype is null then
      return '''';
   end if;
   return 
      (select atype
       from debug.form_qtypes
       where qtype=my_qtype);
END
' 
language 'plpgsql';


create or replace
   function debug.match_q_instance(integer, integer) 
   returns boolean stable as '
DECLARE
   my_finstance ALIAS FOR $1;
   my_qid ALIAS FOR $2;
   q_fid integer;
   finst_fid integer;
BEGIN
   select fid
       from debug.form_instance
       where finstance=my_finstance
       into finst_fid;  
   select fid
       from debug.form_q
       where qid=my_qid
       into q_fid;  

   return finst_fid = q_fid;
END
' 
language 'plpgsql';



create table debug.form_a_int (
  finstance integer not null
      references debug.form_instance (finstance)
      on delete cascade
      on update cascade,
  qid integer not null
      references debug.form_q (qid)
      on delete cascade
      on update cascade,
  uid integer not null,
--      references debug.users (uid) 
--      on update cascade,
  a_int integer,
  check (debug.get_atype(qid) = 'INT'),
  check (debug.match_q_instance(finstance, qid)),
  primary key (finstance, qid, uid)
);



create table debug.form_a_text (
  finstance integer not null
      references debug.form_instance (finstance)
      on delete cascade
      on update cascade,
  qid integer not null
      references debug.form_q (qid)
      on delete cascade
      on update cascade,
  uid integer not null,
--      references debug.users (uid) 
--      on update cascade,
  a_text text,
  check (debug.get_atype(qid) = 'TEXT'),
  check (debug.match_q_instance(finstance, qid)),
  primary key (finstance, qid, uid)
);

create or replace view debug.form_answers as
 select fid, finstance, qno, qid, qtype, atype, uid, a_text, a_int
   from debug.form_a_int  as ai 
   natural full outer join debug.form_a_text as at
   natural join debug.form_q 
   natural join debug.form_qtypes 
   order by fid, finstance, qno, uid;


-- Text string
insert into debug.form_qtypes (qtype, atype) values (1, 'TEXT');
-- A numeric answer
insert into debug.form_qtypes (qtype, atype) values (2, 'INT');
-- Question with alternatives, numeric answer
insert into debug.form_qtypes (qtype, atype) values (3, 'INT');


insert into debug.form_template (name) values ('Test template');

insert into debug.form_q (fid,qno,qtype,qtext)
  values (1,1,1, 'Fie');
insert into debug.form_q (fid,qno,qtype,qtext)
  values (1,2,2, 'Fum');

insert into debug.form_instance (fid, type) values (1,'COURSE');
insert into debug.course_form (finstance,cid) values (1,2);

insert into debug.form_a_text (finstance,qid,uid,a_text)
  values(1,1,1109,'foo');
insert into debug.form_a_int (finstance,qid,uid,a_int)
  values(1,2,1109,5);



commit;