--
-- Copyright 2005-2016 The Kuali Foundation
--
-- Licensed under the Educational Community License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.opensource.org/licenses/ecl2.php
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

CREATE TABLE KRIM_HIST_GRP_T  (
  GRP_HIST_ID   decimal(19,0) NOT NULL,
	GRP_ID      	varchar(40) NOT NULL,
	OBJ_ID      	varchar(36) NOT NULL,
	VER_NBR     	decimal(8,0) NOT NULL DEFAULT '1',
	GRP_NM      	varchar(80) NOT NULL,
	NMSPC_CD    	varchar(40) NOT NULL,
	GRP_DESC    	varchar(4000) NULL,
	KIM_TYP_ID  	varchar(40) NOT NULL,
	ACTV_IND    	varchar(1) NULL DEFAULT 'Y',
	ACTV_FRM_DT 	datetime NULL,
	ACTV_TO_DT  	datetime NULL,
	LAST_UPDT_DT	datetime NULL,
	CONSTRAINT KRIM_GRP_TP1 PRIMARY KEY(GRP_HIST_ID),
	CONSTRAINT KRIM_GRP_TC0 UNIQUE (OBJ_ID)
) ENGINE InnoDB CHARACTER SET utf8 COLLATE utf8_bin
;
ALTER TABLE KRIM_HIST_GRP_T
    ADD CONSTRAINT KRIM_HIST_GRP_TR1
    FOREIGN KEY (KIM_TYP_ID)
    REFERENCES KRIM_TYP_T (KIM_TYP_ID)
;
CREATE INDEX KRIM_HIST_GRP_TI1 ON KRIM_HIST_GRP_T (GRP_ID, ACTV_FRM_DT, ACTV_TO_DT)
;
CREATE INDEX KRIM_HIST_GRP_TI2 ON KRIM_HIST_GRP_T (NMSPC_CD, GRP_NM, ACTV_FRM_DT, ACTV_TO_DT)
;
CREATE TABLE KRIM_HIST_GRP_ID_S (
  id bigint(19) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
;
CREATE TABLE KRIM_HIST_GRP_ATTR_DATA_T  (
	ATTR_DATA_ID    	varchar(40) NOT NULL,
	OBJ_ID          	varchar(36) NOT NULL,
	VER_NBR         	decimal(8,0) NOT NULL DEFAULT '1',
	GRP_HIST_ID          	varchar(40) NULL,
	KIM_TYP_ID      	varchar(40) NOT NULL,
	KIM_ATTR_DEFN_ID	varchar(40) NULL,
	ATTR_VAL        	varchar(400) NULL,
	CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_TP1 PRIMARY KEY(ATTR_DATA_ID),
  CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_TC0 UNIQUE (OBJ_ID)
) ENGINE InnoDB CHARACTER SET utf8 COLLATE utf8_bin
;
ALTER TABLE KRIM_HIST_GRP_ATTR_DATA_T
    ADD CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_TR1
    FOREIGN KEY (KIM_TYP_ID)
    REFERENCES KRIM_TYP_T (KIM_TYP_ID)
;

ALTER TABLE KRIM_HIST_GRP_ATTR_DATA_T
    ADD CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_TR3
    FOREIGN KEY (GRP_HIST_ID)
    REFERENCES KRIM_HIST_GRP_T (GRP_HIST_ID)
  ON DELETE CASCADE
;

ALTER TABLE KRIM_HIST_GRP_ATTR_DATA_T
    ADD CONSTRAINT KRIM_HIST_GRP_ATTR_DATA_TR2
    FOREIGN KEY (KIM_ATTR_DEFN_ID)
    REFERENCES KRIM_ATTR_DEFN_T (KIM_ATTR_DEFN_ID)
;
CREATE TABLE KRIM_HIST_GRP_ATTR_DATA_ID_S (
  id bigint(19) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
;


-- entity work
ALTER TABLE KRIM_ENTITY_EMP_INFO_T ADD TNR_IND VARCHAR(1)
;
