USE cmdsystem;

CREATE TABLE rrec (
  RREC_TIMESTAMP_DT varchar(25) NOT NULL,
  RREC_CALL_BEGIN_DT varchar(25) DEFAULT NULL,
  RREC_CALL_END_DT varchar(25) DEFAULT NULL,
  RREC_CALL_DIRECTION int(3) DEFAULT NULL,
  RREC_CALL_CALLEDNUMBER text DEFAULT NULL,
  RREC_CALL_CALLERNUMBER text DEFAULT NULL,
  RREC_CALL_STATE int(3) NOT NULL,
  RREC_CALL_USERINPUT text DEFAULT NULL,
  RREC_TARGETTYPE int(3) DEFAULT NULL,
  RREC_TARGETNAME text DEFAULT NULL,
  RREC_TARGETADRESS text DEFAULT NULL,
  RREC_STATE int(3) DEFAULT NULL,
  RREC_FAILURE_REASON text DEFAULT NULL,
  RREC_CALL_CONNECTED_DT varchar(25) DEFAULT NULL,
  RREC_SESSION_UID varchar(70) NOT NULL,
  RREC_HISTORY text DEFAULT NULL,
  RREC_AGENTGROUPNAME text DEFAULT NULL,
  RREC_AGENTGROUPID int(10) NOT NULL,
  RREC_MAILBOXFLAG int(1) NOT NULL,
  RREC_RES_NAME text DEFAULT NULL,
  RREC_RES_COMPANY text DEFAULT NULL,
  RREC_RES_OBJECTID text DEFAULT NULL,
  RREC_RES_SOURCE text DEFAULT NULL,
  RREC_AFTERCALLWORK_END_DT varchar(25) DEFAULT NULL,
  RREC_ELAPSEDTIME_WA int(10) DEFAULT NULL,
  RREC_CALL_ROUTED varchar(25) DEFAULT NULL,
  RREC_CALL_FIRSTTRY_DT varchar(25) DEFAULT NULL,
  RREC_DURINGWORKTIME int(2) DEFAULT NULL,
  RREC_SL_CALLCLASS int(1) DEFAULT NULL,
  RREC_PROCESSING_GROUPIDS text DEFAULT NULL,
  RREC_LASTSUPPORTER_FLAG int(3) DEFAULT NULL,
  RREC_DIRECTCALL int(3) DEFAULT NULL,
  RREC_AGENTID int(10) DEFAULT NULL,
  RREC_CALLSUCCESS int(3) DEFAULT NULL,
  RREC_SL_CALLSTAGE int(3) DEFAULT NULL,
  RREC_ATTENDANT_TARGET varchar(40) DEFAULT NULL,
  RREC_ATTENDANT_TARGET_NAME varchar(200) DEFAULT NULL,
  RREC_ATTENDANT_SUCCESSSTATE int(3) DEFAULT NULL,
  RREC_DISCONNECTREASON int(11) DEFAULT NULL,
  RREC_NEWDESTADDRESS varchar(200) DEFAULT NULL,
  RREC_FORM_ID int(10) DEFAULT NULL,
  RREC_CAD_AVAILABLE int(1) DEFAULT NULL,
  RREC_SL_INFO text DEFAULT NULL,
  RREC_ORIGINAL_AGENTGROUPID int(10) DEFAULT NULL,
  RREC_SKILL_REQUESTED text DEFAULT NULL,
  RREC_SKILL_FULLFILLED text DEFAULT NULL,
  RREC_EXCLUDEFROMSTATS int(1) DEFAULT NULL,
  RREC_REDIR_NUMBER varchar(200) DEFAULT NULL,
  RREC_ORIGIN int(2) DEFAULT NULL,
  RREC_BUCKET_CON varchar(20) DEFAULT NULL,
  RREC_BUCKET_LOST varchar(20) DEFAULT NULL,
  RREC_NORM_CONTACTNUMBER varchar(200) DEFAULT NULL,
  RREC_HIDERECENTCALLLIST int(1) DEFAULT NULL,
  RREC_OUTBOUND_CAMPAIGNID int(10) DEFAULT NULL,
  RREC_OUTBOUND_CAMPAIGNNAME text DEFAULT NULL,
  RREC_OUTBOUND_CAMPAIGNENTRYID int(10) DEFAULT NULL,
  RREC_RECSTATE int(3) DEFAULT NULL,
  RREC_COACH_ID int(10) DEFAULT NULL,
  RREC_COACH_NAME varchar(200) DEFAULT NULL,
  RREC_COACH_STARTTIME varchar(25) DEFAULT NULL,
  RREC_COACH_ENDTIME varchar(25) DEFAULT NULL,
  RREC_COACH_STATE int(3) DEFAULT NULL,
  RREC_OUTBOUND_CAMPAIGNSTATE smallint(6) DEFAULT NULL,
  RREC_RES_NAME_REDIR text DEFAULT NULL,
  RREC_RES_COMPANY_REDIR text DEFAULT NULL,
  RREC_RES_OBJECTID_REDIR text DEFAULT NULL,
  RREC_RES_SOURCE_REDIR text DEFAULT NULL,
  PRIMARY KEY (RREC_SESSION_UID),
  INDEX RREC_AGENT_CALLS (RREC_AGENTID, RREC_TIMESTAMP_DT),
  INDEX RREC_AGENT_CALLS2 (RREC_TARGETADRESS (100), RREC_TARGETTYPE, RREC_TIMESTAMP_DT),
  INDEX RREC_IDX_AGENTS (RREC_TARGETTYPE, RREC_STATE, RREC_AGENTGROUPID, RREC_TIMESTAMP_DT),
  INDEX RREC_IDX_CONTACTNUMBER (RREC_NORM_CONTACTNUMBER, RREC_TIMESTAMP_DT),
  INDEX RREC_IDX_GROUP (RREC_ORIGINAL_AGENTGROUPID, RREC_AGENTGROUPID),
  INDEX RREC_IDX_JOURNAL (RREC_AGENTGROUPID, RREC_ORIGINAL_AGENTGROUPID, RREC_TIMESTAMP_DT, RREC_OUTBOUND_CAMPAIGNID, RREC_AGENTID),
  INDEX RREC_IDX_RES (RREC_RES_SOURCE (50), RREC_RES_OBJECTID (100)),
  INDEX RREC_IDX_RLOG (RREC_SESSION_UID, RREC_TIMESTAMP_DT),
  INDEX RREC_IDX_SL (RREC_TIMESTAMP_DT, RREC_AGENTGROUPID),
  INDEX RREC_IDX_TIME (RREC_TIMESTAMP_DT (10), RREC_TARGETTYPE, RREC_STATE),
  INDEX RREC_IDX_WORKTIME (RREC_TARGETTYPE, RREC_STATE, RREC_CALL_BEGIN_DT, RREC_CALL_END_DT, RREC_AFTERCALLWORK_END_DT)
)
ENGINE = INNODB
AVG_ROW_LENGTH = 197
CHARACTER SET latin1
COLLATE latin1_general_cs
ROW_FORMAT = COMPRESSED;

CREATE TABLE rlog (
  RLOG_ID varchar(70) NOT NULL,
  RLOG_LOGENTRY longtext DEFAULT NULL,
  PRIMARY KEY (RLOG_ID),
  UNIQUE INDEX IDX_UK_rlog (RLOG_ID),
  CONSTRAINT RLOG_RREC_FK FOREIGN KEY (RLOG_ID)
  REFERENCES rrec (RREC_SESSION_UID) ON DELETE CASCADE ON UPDATE NO ACTION
)
ENGINE = INNODB
AVG_ROW_LENGTH = 2343
CHARACTER SET latin1
COLLATE latin1_general_cs
ROW_FORMAT = COMPRESSED;