CREATE TABLE GROUP_MEMBER(
  GROUP_NAME VARCHAR(100) NOT NULL,
  USER_NAME  VARCHAR(100) NOT NULL
);

ALTER TABLE GROUP_MEMBER ADD CONSTRAINT IDX_GROUP_MEMBER_PK PRIMARY KEY (GROUP_NAME, USER_NAME);
ALTER TABLE GROUP_MEMBER ADD CONSTRAINT IDX_GROUP_MEMBER_FK0 FOREIGN KEY (GROUP_NAME) REFERENCES ACCOUNT (USER_NAME);
ALTER TABLE GROUP_MEMBER ADD CONSTRAINT IDX_GROUP_MEMBER_FK1 FOREIGN KEY (USER_NAME) REFERENCES ACCOUNT (USER_NAME);

ALTER TABLE ACCOUNT ADD COLUMN GROUP_ACCOUNT BOOLEAN NOT NULL DEFAULT FALSE;

CREATE OR REPLACE VIEW ISSUE_OUTLINE_VIEW AS
 SELECT
   A.USER_NAME,
   A.REPOSITORY_NAME,
   A.ISSUE_ID,
   NVL(B.COMMENT_COUNT, 0) AS COMMENT_COUNT
 FROM ISSUE A
 LEFT OUTER JOIN (
   SELECT USER_NAME, REPOSITORY_NAME, ISSUE_ID, COUNT(COMMENT_ID) AS COMMENT_COUNT FROM ISSUE_COMMENT
   WHERE ACTION IN ('comment', 'close_comment', 'reopen_comment')
   GROUP BY USER_NAME, REPOSITORY_NAME, ISSUE_ID
 ) B
 ON (A.USER_NAME = B.USER_NAME AND A.REPOSITORY_NAME = B.REPOSITORY_NAME AND A.ISSUE_ID = B.ISSUE_ID);
