DROP TABLE agent_type;
DROP TABLE group_type;
DROP TABLE agent;
DROP TABLE groups;
DROP TABLE group_member;
DROP SEQUENCE agent_type_agent_type_key_seq;
DROP SEQUENCE group_type_group_type_key_seq;

CREATE TABLE agent_type
(
    agent_type_key serial PRIMARY KEY,
    domain      	varchar(40),
    authority   	varchar(30),
    keyword    		varchar(30),
    descr       	text,
    CONSTRAINT cnst_agent_type UNIQUE (domain,authority,keyword)
);

CREATE TABLE group_type
(
    group_type_key serial PRIMARY KEY,
    domain      	varchar(40),
    authority   	varchar(30),
    keyword    		varchar(30),
    descr       	text,
    CONSTRAINT cnst_group_type UNIQUE (domain,authority,keyword)
);

CREATE TABLE agent
(
    agent_id            varchar(35) PRIMARY KEY,
    agent_type_key      integer REFERENCES agent_type,
    display_name    	varchar(30)
);

CREATE TABLE groups
(
    group_id            varchar(35) PRIMARY KEY,
    group_type_key      integer REFERENCES group_type,
    display_name    	varchar(30),
    description       	text
);

CREATE TABLE group_member
(
    group_id            varchar(35),
    member_id        	varchar(35),
    isGroup             boolean,
    CONSTRAINT cnst_group_member UNIQUE (group_id, member_id)
);

INSERT INTO agent_type   (domain, authority, keyword, descr) VALUES ('person', 'mit.edu', 'Student', '');
INSERT INTO group_type   (domain, authority, keyword, descr) VALUES ('person', 'mit.edu', 'Roster', '');
INSERT INTO group_type   (domain, authority, keyword, descr) VALUES ('person', 'mit.edu', 'Chat', '');
INSERT INTO group_type   (domain, authority, keyword, descr) VALUES ('person', 'mit.edu', 'Study', '');