create user skull identified by vertigo default tablespace users
temporary tablespace temp;

grant resource,connect to  skull;


create table members(
mem_id number(3),
mem_firstname varchar2(30),
mem_lastname varchar2(30),
mem_email varchar2(30),
mem_timestamp date);

create table groups(
grp_id number(2),
grp_name varchar2(30),
grp_desc varchar2(30),
grp_timestamp date
);

create table mem_grps(
mem_grps_id number(2),
grp_id number(2),
mem_id number(2),
mem_grp_status varchar2(2), -- active,inactive -> bc4j constraint
mem_grp_timstamp date);

create sequence mem_seq;
create sequence grp_seq;
create sequence mem_grp_seq;

ALTER TABLE "MEMBERS" ADD(CONSTRAINT "MEM_PK" PRIMARY KEY("MEM_ID"));
ALTER TABLE "GROUPS" ADD(CONSTRAINT "GRP_PK" PRIMARY KEY("GRP_ID"));
ALTER TABLE "MEM_GRPS" ADD(CONSTRAINT "MEM_GRPS_PK" PRIMARY KEY("MEM_GRPS_ID"));

ALTER TABLE "MEM_GRPS" 
ADD(CONSTRAINT "MG_MEM_FK" FOREIGN KEY("MEM_ID") 
REFERENCES "MEMBERS"("MEM_ID"));

ALTER TABLE "MEM_GRPS" 
ADD(CONSTRAINT "MG_GRP_FK" FOREIGN KEY("GRP_ID") 
REFERENCES "GROUPS"("GRP_ID"));

--
declare
mem_number number;
grp_number number;
grp_mem_number number;
begin
 select mem_seq.nextval into mem_number from dual;
 insert into members values(mem_number,'Fuju','Graham','fuju@exzilla.net',sysdate);

 select mem_seq.nextval into mem_number from dual;
 insert into members values(mem_number,'Teera','Dokmaihom','teddy@exzilla.net',sysdate);

 select mem_seq.nextval into mem_number from dual;
 insert into members values(mem_number,'Cupid','N','cupid@narisa.com',sysdate);

 select mem_seq.nextval into mem_number from dual;
 insert into members values(mem_number,'Kevin','Tan','kouprex@exzilla.net',sysdate);

 select mem_seq.nextval into mem_number from dual;
 insert into members values(mem_number,'Thongchai','Boonsanong','tyranno@exzilla.net',sysdate);


-- Group data
 select grp_seq.nextval into grp_number from dual;
 insert into groups values(grp_number,'Developer','Development Group',sysdate);

 select grp_seq.nextval into grp_number from dual;
 insert into groups values(grp_number,'SE','System Engineer Group',sysdate);

 commit;
end;