-- Licensed Materials - Property of IBM
-- BI and PM: Mobile
-- (C) Copyright IBM Corp. 2007, 2012
-- US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

--
-- Mobile database table creation scripts for IBM Informix.

-- MOB_PORTALITEMS
CREATE TABLE MOB_PORTALITEMS (
	PORTALITEM_ID SERIAL PRIMARY KEY,
	LABEL varchar (100),
	STORE_ID varchar (200),
	CM_PATH lvarchar (1000),
	SOURCE_PATH lvarchar (1000),
	DESCRIPTION lvarchar (500),
	PORTALITEM_TYPE int DEFAULT 0 NOT NULL
);
CREATE INDEX IDX_MOB_REP_SI ON MOB_PORTALITEMS(STORE_ID);

-- MOB_RENDERS
CREATE TABLE MOB_RENDERS (
	RENDER_ID SERIAL PRIMARY KEY,
	RENDER_TIME datetime YEAR TO SECOND NOT NULL,
	RENDER_SIZE int NOT NULL,
	STATUS_CODE int NOT NULL,
	SOURCE_CODE smallint,
	DRILL_PARAMS lvarchar (1000),
	PORTALITEM_ID int NOT NULL,
	BASE_DOC varchar (200),
	SMALL_THUMB varchar (200),
	MEDIUM_THUMB varchar (200),
	LARGE_THUMB varchar (200),
	SAVED_OUTPUT_TYPE smallint,
	CREATION_TIME datetime YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL,
	FOREIGN KEY(PORTALITEM_ID) REFERENCES MOB_PORTALITEMS(PORTALITEM_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_R_PIID
);

-- MOB_USERS
CREATE TABLE MOB_USERS (
	USER_ID SERIAL PRIMARY KEY CONSTRAINT USER_ID,
	DEVICE_ID varchar (100) NOT NULL,
	DEVICE_PROFILE varchar (200),
	CREDENTIAL_PATH varchar (200),
	CAM_ID varchar (100),
	KEY_TYPE varchar (20),	
	KEY_BYTES blob (32),
	SYNC_METHOD varchar (20), 
	LAST_LOGIN datetime YEAR TO SECOND,
	LAST_UPDATED datetime YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL
);
CREATE INDEX IDX_CAM_ID ON MOB_USERS(CAM_ID);

-- MOB_TEMPSTORAGE
CREATE TABLE MOB_TEMPSTORAGE (
	TS_ID SERIAL PRIMARY KEY CONSTRAINT TS_ID,
	PATH lvarchar (500),
	CREATED datetime YEAR TO SECOND NOT NULL,
	MODIFIED datetime YEAR TO SECOND NOT NULL 
);
--CREATE INDEX PK_MOB_TS ON MOB_TEMPSTORAGE(TS_ID); --serial creates one
CREATE INDEX IDX_MOB_MODIFIED ON MOB_TEMPSTORAGE(MODIFIED);
CREATE INDEX IDX_MOB_CREATED ON MOB_TEMPSTORAGE(CREATED);
CREATE INDEX IDX_MOB_TMPPATH ON MOB_TEMPSTORAGE(PATH);


-- MOB_HISTORY
CREATE TABLE MOB_HISTORY (
	HISTORY_ID SERIAL PRIMARY KEY CONSTRAINT HISTORY_ID,
	EVENT_TIME datetime YEAR TO SECOND,
	EVENT_CODE int NOT NULL,
	ARGUMENTS lvarchar (1000)
);
CREATE INDEX IDX_HISTORY_CODE ON MOB_HISTORY(EVENT_CODE);
CREATE INDEX IDX_HISTORY_TIME ON MOB_HISTORY(EVENT_TIME);

-- MOB_BLOBS
CREATE TABLE MOB_BLOBS (
	HASH char (32),
	USER_ID int,
	SEQUENCE int NOT NULL,
	BLOB_VALUE blob (1024) NOT NULL,
	FORMAT smallint,
	ADDED datetime YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND NOT NULL,
	FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_BLOBS_UID
);
CREATE INDEX IDX_MOB_BLOBS_HASH ON MOB_BLOBS(HASH);

-- MOB_TEMPSTOREBLOBS
CREATE TABLE MOB_TEMPSTOREBLOBS (
	TS_ID int NOT NULL,
	SEQUENCE int NOT NULL,
	BLOB_VALUE blob (1024) NOT NULL,
	FOREIGN KEY(TS_ID) REFERENCES MOB_TEMPSTORAGE(TS_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_TSB_TSID
);
CREATE INDEX IDX_MOB_TSBLOBS2 ON MOB_TEMPSTOREBLOBS(TS_ID, SEQUENCE);

-- MOB_USER_HISTORY
CREATE TABLE MOB_USER_HISTORY (
	USER_ID int NOT NULL,
	HISTORY_ID int NOT NULL,
	FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_UH_UID, 
	FOREIGN KEY(HISTORY_ID) REFERENCES MOB_HISTORY(HISTORY_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_UH_HID,
	PRIMARY KEY(USER_ID, HISTORY_ID) CONSTRAINT PK_MOB_UH_UID
);
--CREATE INDEX IDX_HIST_USER_ID ON MOB_USER_HISTORY(USER_ID); --already exists

-- MOB_USER_RENDER
CREATE TABLE MOB_USER_RENDER (
	USER_ID int NOT NULL,
	RENDER_ID int NOT NULL,
	NAME varchar (100),
	LAST_VIEWED datetime YEAR TO SECOND,
	FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_UR_UID,
	FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_UR_RID,
	PRIMARY KEY(USER_ID, RENDER_ID) CONSTRAINT PK_MOB_USERRNDR
);

-- MOB_USER_PORTALITEM
CREATE TABLE MOB_USER_PORTALITEM (
	USER_ID int NOT NULL,
	PORTALITEM_ID int NOT NULL,
	USER_PORTALITEM_TYPE int DEFAULT 0 NOT NULL,
	FOREIGN KEY(USER_ID) REFERENCES MOB_USERS(USER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_UPI_UID,
	FOREIGN KEY(PORTALITEM_ID) REFERENCES MOB_PORTALITEMS(PORTALITEM_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_UPI_RSID,
	PRIMARY KEY(USER_ID, PORTALITEM_ID, USER_PORTALITEM_TYPE) CONSTRAINT PK_MOB_USERPIID
);

CREATE TABLE MOB_RESOURCES (
	RESOURCE_ID SERIAL PRIMARY KEY,
	RENDER_ID int,
	PATH varchar (200),
	HASH char (32),
	FORMAT smallint,
	HEIGHT int,
	WIDTH int,
	BLOB_ID int,
	OBJECT_INDEX int,
	PAGE_INDEX int,
	FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_RES_RID
);
-- CREATE INDEX IDX_MOB_RESOURCES ON MOB_RESOURCES(RENDER_ID); -- already exists

-- MOB_RENDER_HISTORY
CREATE TABLE MOB_RENDER_HISTORY (
	RENDER_ID   int  NOT NULL,
	HISTORY_ID int NOT NULL,
	FOREIGN KEY(RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_RH_RID, 
	FOREIGN KEY(HISTORY_ID) REFERENCES MOB_HISTORY(HISTORY_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_RH_HID
);
-- CREATE INDEX IDX_MOB_RH_RID ON MOB_RENDER_HISTORY(RENDER_ID); -- already exists

--CREATE TRIGGER TGR_M_UPI_SYNC_DE -- doesn't work with ON DELETE CASCADE 
--DELETE ON MOB_USER_PORTALITEM
--REFERENCING OLD AS pre
--FOR EACH ROW
--	(UPDATE MOB_USERS SET LAST_UPDATED = CURRENT YEAR TO SECOND
--		WHERE USER_ID = pre.USER_ID);

CREATE TRIGGER TGR_M_UPI_SYNC_IN 
INSERT ON MOB_USER_PORTALITEM
REFERENCING NEW AS pre
FOR EACH ROW
	(UPDATE MOB_USERS SET LAST_UPDATED = CURRENT YEAR TO SECOND
		WHERE USER_ID = pre.USER_ID);
		
CREATE TRIGGER TGR_M_UPI_SYNC_UP 
UPDATE ON MOB_USER_PORTALITEM
REFERENCING NEW AS pre
FOR EACH ROW
	(UPDATE MOB_USERS SET LAST_UPDATED = CURRENT YEAR TO SECOND
		WHERE USER_ID = pre.USER_ID);
		
CREATE TRIGGER TGR_MOB_UR_SYNC_IN 
INSERT ON MOB_USER_RENDER
REFERENCING NEW AS pre
FOR EACH ROW
	(UPDATE MOB_USERS SET LAST_UPDATED = CURRENT YEAR TO SECOND
		WHERE USER_ID = pre.USER_ID);
		
CREATE TRIGGER TGR_MOB_UR_SYNC_UP 
UPDATE ON MOB_USER_RENDER
REFERENCING NEW AS pre
FOR EACH ROW
	(UPDATE MOB_USERS SET LAST_UPDATED = CURRENT YEAR TO SECOND
		WHERE USER_ID = pre.USER_ID);
		
--CREATE TRIGGER TGR_MOB_UR_SYNC_DE -- doesn't work with ON DELETE CASCADE
--DELETE ON MOB_USER_RENDER
--REFERENCING OLD AS pre
--FOR EACH ROW
--	(UPDATE MOB_USERS SET LAST_UPDATED = CURRENT YEAR TO SECOND
--		WHERE USER_ID = pre.USER_ID);

CREATE TABLE MOB_DRILLS (
	SOURCE_RENDER_ID int NOT NULL,
	TARGET_RENDER_ID int NOT NULL,
	DRILL_CONTEXT lvarchar (1000) NOT NULL,
	DRILL_TYPE smallint NOT NULL,
	FOREIGN KEY(SOURCE_RENDER_ID) REFERENCES MOB_RENDERS(RENDER_ID) ON DELETE CASCADE CONSTRAINT FK_MOB_D_SRID
);