DROP TABLE IF EXISTS WEB_HOOK_EVENT;

CREATE TABLE WEB_HOOK_EVENT(
    USER_NAME VARCHAR(100) NOT NULL,
    REPOSITORY_NAME VARCHAR(100) NOT NULL,
    URL VARCHAR(200) NOT NULL,
    EVENT VARCHAR(30) NOT NULL
);

ALTER TABLE WEB_HOOK_EVENT ADD CONSTRAINT IDX_WEB_HOOK_EVENT_PK PRIMARY KEY (USER_NAME, REPOSITORY_NAME, URL, EVENT);
ALTER TABLE WEB_HOOK_EVENT ADD CONSTRAINT IDX_WEB_HOOK_EVENT_FK0 FOREIGN KEY (USER_NAME, REPOSITORY_NAME, URL) REFERENCES WEB_HOOK (USER_NAME, REPOSITORY_NAME, URL)
  ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TEMPORARY TABLE TMP_EVENTS (EVENT VARCHAR(30));

INSERT INTO TMP_EVENTS VALUES ('push'),('issue_comment'),('issues'),('pull_request');

INSERT INTO WEB_HOOK_EVENT (USER_NAME, REPOSITORY_NAME, URL, EVENT)
  SELECT USER_NAME, REPOSITORY_NAME, URL, EVENT
    FROM WEB_HOOK, TMP_EVENTS;

DROP TABLE TMP_EVENTS;

ALTER TABLE COMMIT_COMMENT ADD COLUMN ISSUE_ID INT;

CREATE OR REPLACE VIEW ISSUE_OUTLINE_VIEW AS
 SELECT
   A.USER_NAME,
   A.REPOSITORY_NAME,
   A.ISSUE_ID,
   NVL(B.COMMENT_COUNT, 0) + NVL(C.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)
 LEFT OUTER JOIN (
   SELECT USER_NAME, REPOSITORY_NAME, ISSUE_ID, COUNT(COMMENT_ID) AS COMMENT_COUNT FROM COMMIT_COMMENT
   GROUP BY USER_NAME, REPOSITORY_NAME, ISSUE_ID
 ) C
 ON (A.USER_NAME = C.USER_NAME AND A.REPOSITORY_NAME = C.REPOSITORY_NAME AND A.ISSUE_ID = C.ISSUE_ID);


UPDATE COMMIT_COMMENT C SET (ISSUE_ID) = (
  SELECT MAX(P.ISSUE_ID)
  FROM PULL_REQUEST P
  WHERE
    C.USER_NAME       = P.USER_NAME AND
    C.REPOSITORY_NAME = P.REPOSITORY_NAME AND
    C.COMMIT_ID       = P.COMMIT_ID_TO
);

ALTER TABLE COMMIT_COMMENT DROP COLUMN PULL_REQUEST;