ALTER TABLE ZH_PREFERENCE_VIEW DROP CONSTRAINT ZH_PREFERENCE_VIEW_FILTER_FK
/
ALTER TABLE ZH_USER_WIDGET DROP CONSTRAINT ZH_USER_WIDGET_VIEW_FK
/
ALTER TABLE ZH_USER_WIDGET DROP CONSTRAINT ZH_USER_WIDGET_FILTER_FK
/
ALTER TABLE ZH_PREFERENCE_VIEW DROP CONSTRAINT ZH_PREFERENCE_VIEW_LAYOUT_FK
/
ALTER TABLE ZH_USER_WIDGET DROP CONSTRAINT ZH_USER_WIDGET_LAYOUT_FK
/
DECLARE
   V_CONT INTEGER;
BEGIN
   SELECT COUNT(1)
     INTO V_CONT
     FROM ZH_PREFERENCE_FILTER; 
   
   IF (V_CONT = 0) THEN
      EXECUTE IMMEDIATE 'DROP TABLE ZH_PREFERENCE_FILTER';
      EXECUTE IMMEDIATE 'CREATE TABLE ZH_PREFERENCE_FILTER(
	ID 			VARCHAR2(255) 		NOT NULL, 
	USER_ID 			VARCHAR2(12)		NOT NULL,
	ORGANIZATION_ID 	NUMBER(10,0)  		NOT NULL, 
	LABEL 			VARCHAR2(255)		NOT NULL, 
	WIDGET_ID 		VARCHAR2(255) 		NOT NULL, 
	FILTER_ROW 		CLOB 	      		NOT NULL, 
	LAST_MODIFIED_DATE 	DATE 	      		DEFAULT SYSDATE NOT NULL, 
		CONSTRAINT ZH_PREFERENCE_FILTER_PK PRIMARY KEY (ID, USER_ID),
		CONSTRAINT ZH_PREFERENCE_FILTER_NAME_UK UNIQUE (LABEL, WIDGET_ID, USER_ID))';
   END IF;
END;
/
DECLARE
   V_CONT INTEGER;
BEGIN
   SELECT COUNT(1)
     INTO V_CONT
     FROM ZH_PREFERENCE_LAYOUT;
   
   IF (V_CONT = 0) THEN
      EXECUTE IMMEDIATE 'DROP TABLE ZH_PREFERENCE_LAYOUT';
      EXECUTE IMMEDIATE 'CREATE TABLE ZH_PREFERENCE_LAYOUT(
 	ID 			VARCHAR2(255) 		NOT NULL ,
 	USER_ID 			VARCHAR2(12)		NOT NULL ,
	ORGANIZATION_ID 	NUMBER(10,0) 		NOT NULL ,
 	LABEL 			VARCHAR2(255) 		NOT NULL ,
 	PDF_REPORT_ORIENTATION 	VARCHAR2(20) 		NOT NULL ,
 	WIDGET_ID 		VARCHAR2(255) 		NOT NULL ,
 	LAYOUT_CHANGES 		CLOB 			NOT NULL ,
 	LAST_MODIFIED_DATE 	DATE 			DEFAULT SYSDATE NOT NULL ,
 		CONSTRAINT ZH_PREFERENCE_LAYOUT_PK PRIMARY KEY (ID, USER_ID) ,
 		CONSTRAINT ZH_PREFERENCE_LAYOUT_CHK CHECK (PDF_REPORT_ORIENTATION IN('||CHR(39)||'PORTRAIT'||CHR(39)||', '||CHR(39)||'LANDSCAPE'||CHR(39)||')) ,
 		CONSTRAINT ZH_PREFERENCE_LAYOUT_NAME_UK UNIQUE (LABEL, WIDGET_ID, USER_ID))';
   END IF;
END;
/
DECLARE
   V_CONT INTEGER;
BEGIN
   SELECT COUNT(1)
     INTO V_CONT
     FROM ZH_PREFERENCE_VIEW;
   
   IF (V_CONT = 0) THEN
      EXECUTE IMMEDIATE 'DROP TABLE ZH_PREFERENCE_VIEW';
      EXECUTE IMMEDIATE 'CREATE TABLE ZH_PREFERENCE_VIEW(
	ID 			VARCHAR2(255)		NOT NULL,
 	USER_ID 			VARCHAR2(12) 		NOT NULL,
 	ORGANIZATION_ID 	NUMBER(10,0) 		NOT NULL,
 	LABEL 			VARCHAR2(255) 		NOT NULL,
 	WIDGET_ID 		VARCHAR2(255)		NOT NULL,
 	FILTER_ID 		VARCHAR2(255)		NOT NULL,
 	LAYOUT_ID 		VARCHAR2(255) 		NOT NULL,
	LAST_MODIFIED_DATE 	DATE 			DEFAULT SYSDATE NOT NULL,
 		CONSTRAINT ZH_PREFERENCE_VIEW_PK PRIMARY KEY (ID, USER_ID),
 		CONSTRAINT ZH_PREFERENCE_VIEW_OPTS_UK UNIQUE (USER_ID, WIDGET_ID, FILTER_ID, LAYOUT_ID),
		CONSTRAINT ZH_PREFERENCE_VIEW_NAME_UK UNIQUE (LABEL, WIDGET_ID, USER_ID),
 		CONSTRAINT ZH_PREFERENCE_VIEW_FILTER_FK FOREIGN KEY (FILTER_ID, USER_ID)
 			REFERENCES ZH_PREFERENCE_FILTER (ID, USER_ID),
 		CONSTRAINT ZH_PREFERENCE_VIEW_LAYOUT_FK FOREIGN KEY (LAYOUT_ID, USER_ID)
  			REFERENCES ZH_PREFERENCE_LAYOUT (ID, USER_ID))';
   END IF;
END;
/
DECLARE
   V_CONT INTEGER;
BEGIN
   SELECT COUNT(1)
     INTO V_CONT
     FROM ZH_USER_WIDGET;
   
   IF (V_CONT = 0) THEN
      EXECUTE IMMEDIATE 'DROP TABLE ZH_USER_WIDGET';
      EXECUTE IMMEDIATE 'CREATE TABLE ZH_USER_WIDGET(
	ID 			VARCHAR2(255)  		NOT NULL,
 	USER_ID 			VARCHAR2(12) 		NOT NULL,
 	ORGANIZATION_ID 	NUMBER(10,0) 		NOT NULL,
	WIDGET_ID 		VARCHAR2(255) 		NOT NULL,
 	DEFAULT_FILTER 		VARCHAR2(255),
 	DEFAULT_LAYOUT 		VARCHAR2(255),
 	DEFAULT_VIEW 		VARCHAR2(255),
 	LAST_MODIFIED_DATE 	DATE 			DEFAULT SYSDATE NOT NULL,
 		CONSTRAINT ZH_USER_WIDGET_PK PRIMARY KEY (ID),
 		CONSTRAINT ZH_USER_WIDGET_UK UNIQUE (WIDGET_ID, USER_ID),
 		CONSTRAINT ZH_USER_WIDGET_FILTER_FK FOREIGN KEY (DEFAULT_FILTER, USER_ID)
 		REFERENCES ZH_PREFERENCE_FILTER (ID, USER_ID),
 		CONSTRAINT ZH_USER_WIDGET_LAYOUT_FK FOREIGN KEY (DEFAULT_LAYOUT, USER_ID)
 		REFERENCES ZH_PREFERENCE_LAYOUT (ID, USER_ID),
 		CONSTRAINT ZH_USER_WIDGET_VIEW_FK FOREIGN KEY (DEFAULT_VIEW, USER_ID)
 		REFERENCES ZH_PREFERENCE_VIEW (ID, USER_ID))';
   END IF;
END;