DROP TABLE IF EXISTS ACCESS_TOKEN;

CREATE TABLE ACCESS_TOKEN (
   ACCESS_TOKEN_ID INT NOT NULL AUTO_INCREMENT,
   TOKEN_HASH VARCHAR(40) NOT NULL,
   USER_NAME VARCHAR(100) NOT NULL,
   NOTE TEXT NOT NULL
);

ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_PK PRIMARY KEY (ACCESS_TOKEN_ID);
ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_FK0 FOREIGN KEY (USER_NAME) REFERENCES ACCOUNT (USER_NAME)
  ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE ACCESS_TOKEN ADD CONSTRAINT IDX_ACCESS_TOKEN_TOKEN_HASH UNIQUE(TOKEN_HASH);


DROP TABLE IF EXISTS COMMIT_STATUS;
CREATE TABLE COMMIT_STATUS(
  COMMIT_STATUS_ID INT AUTO_INCREMENT,
  USER_NAME VARCHAR(100) NOT NULL,
  REPOSITORY_NAME VARCHAR(100) NOT NULL,
  COMMIT_ID VARCHAR(40) NOT NULL,
  CONTEXT VARCHAR(255) NOT NULL, -- context is too long (maximum is 255 characters)
  STATE VARCHAR(10) NOT NULL, -- pending, success, error, or failure
  TARGET_URL VARCHAR(200),
  DESCRIPTION TEXT,
  CREATOR VARCHAR(100) NOT NULL,
  REGISTERED_DATE TIMESTAMP NOT NULL, -- CREATED_AT
  UPDATED_DATE TIMESTAMP NOT NULL -- UPDATED_AT
);
ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_PK PRIMARY KEY (COMMIT_STATUS_ID);
ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_1
  UNIQUE (USER_NAME, REPOSITORY_NAME, COMMIT_ID, CONTEXT);
ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK1
  FOREIGN KEY (USER_NAME, REPOSITORY_NAME)
  REFERENCES REPOSITORY (USER_NAME, REPOSITORY_NAME)
  ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK2
  FOREIGN KEY (USER_NAME) REFERENCES ACCOUNT (USER_NAME)
  ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE COMMIT_STATUS ADD CONSTRAINT IDX_COMMIT_STATUS_FK3
  FOREIGN KEY (CREATOR) REFERENCES ACCOUNT (USER_NAME)
  ON DELETE CASCADE ON UPDATE CASCADE;