DROP TABLE IF EXISTS URL_FILTER;
DROP TABLE IF EXISTS ACCESS_RESULT_DATA;
DROP TABLE IF EXISTS ACCESS_RESULT;
DROP TABLE IF EXISTS URL_QUEUE;

/**********************************/
/* Table Name: URL Queue */
/**********************************/
CREATE TABLE URL_QUEUE(
  ID BIGINT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  SESSION_ID VARCHAR(20) NOT NULL,
  METHOD VARCHAR(10) NOT NULL,
  URL TEXT NOT NULL,
  PARENT_URL TEXT,
  DEPTH INTEGER NOT NULL,
  LAST_MODIFIED DATETIME,
  CREATE_TIME DATETIME NOT NULL
) ENGINE = InnoDB;

/**********************************/
/* Table Name: Access Result */
/**********************************/
CREATE TABLE ACCESS_RESULT(
  ID BIGINT(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  SESSION_ID VARCHAR(20) NOT NULL,
  RULE_ID VARCHAR(20),
  URL TEXT NOT NULL,
  PARENT_URL TEXT,
  STATUS INTEGER NOT NULL,
  HTTP_STATUS_CODE INTEGER NOT NULL,
  METHOD VARCHAR(10) NOT NULL,
  MIME_TYPE VARCHAR(100) NOT NULL,
  CONTENT_LENGTH BIGINT NOT NULL,
  EXECUTION_TIME INTEGER NOT NULL,
  LAST_MODIFIED DATETIME NOT NULL,
  CREATE_TIME DATETIME NOT NULL
) ENGINE = InnoDB;

/**********************************/
/* Table Name: Access Result Data */
/**********************************/
CREATE TABLE ACCESS_RESULT_DATA(
  ID BIGINT(20) NOT NULL PRIMARY KEY,
  TRANSFORMER_NAME VARCHAR(255) NOT NULL,
  DATA LONGBLOB,
  ENCODING VARCHAR(20),
  FOREIGN KEY (ID) REFERENCES ACCESS_RESULT (ID)
) ENGINE = InnoDB;

/**********************************/
/* Table Name: URL Filter */
/**********************************/
CREATE TABLE URL_FILTER(
  ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  SESSION_ID VARCHAR(20) NOT NULL,
  URL TEXT NOT NULL,
  FILTER_TYPE VARCHAR(1) NOT NULL,
  CREATE_TIME DATETIME NOT NULL
) ENGINE = InnoDB;


CREATE INDEX IDX_URL_QUEUE_SESSION_ID_AND_TIME ON URL_QUEUE (SESSION_ID, CREATE_TIME);
CREATE INDEX IDX_URL_QUEUE_SESSION_ID_AND_URL ON URL_QUEUE (SESSION_ID, URL(255));
CREATE INDEX IDX_URL_QUEUE_SESSION_ID ON URL_QUEUE (SESSION_ID);

CREATE INDEX IDX_ACCESS_RESULT_SESSION_ID_AND_TIME ON ACCESS_RESULT (SESSION_ID, CREATE_TIME);
CREATE INDEX IDX_ACCESS_RESULT_SESSION_ID_AND_URL ON ACCESS_RESULT (SESSION_ID, URL(255));
CREATE INDEX IDX_ACCESS_RESULT_SESSION_ID ON ACCESS_RESULT (SESSION_ID);
CREATE INDEX IDX_ACCESS_RESULT_URL_AND_TIME ON ACCESS_RESULT (URL(255), CREATE_TIME);

CREATE INDEX IDX_URL_FILTER_SESSION_ID_AND_FILTER_TYPE ON URL_FILTER (SESSION_ID, FILTER_TYPE);