/*
 * JsTree 추적 로그 테이블
 * 트리거 Log를 저장합니다.
 */
CREATE TABLE T_JSTREE_COMPARE_INFO_LOG
(
 C_ID        NUMBER                            NOT NULL,
 C_PARENTID  NUMBER                            NOT NULL,
 C_POSITION  NUMBER                            NOT NULL,
 C_LEFT      NUMBER                            NOT NULL,
 C_RIGHT     NUMBER                            NOT NULL,
 C_LEVEL     NUMBER                            NOT NULL,
 C_TITLE     VARCHAR2(4000 BYTE),
 C_TYPE      VARCHAR2(4000 BYTE),
 C_METHOD    VARCHAR2(4000 BYTE),
 C_STATE     VARCHAR2(4000 BYTE),
 C_DATE      DATE                              NOT NULL
);

COMMENT ON TABLE T_JSTREE_COMPARE_INFO_LOG IS '기본 트리 스키마 트리거 로그';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_ID IS '노드 아이디';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_PARENTID IS '부모 노드 아이디';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_POSITION IS '노드 포지션';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_LEFT IS '노드 좌측 끝 포인트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_RIGHT IS '노드 우측 끝 포인트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_LEVEL IS '노드 DEPTH ';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_TITLE IS '노드 명';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_TYPE IS '노드 타입';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_METHOD IS '노드 변경 행위';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_STATE IS '노드 상태값 ( 이전인지. 이후인지)';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO_LOG.C_DATE IS '노드 변경 시';

/*
 * JsTree
 */
CREATE TABLE T_JSTREE_COMPARE_INFO
(
 C_ID        		NUMBER                            NOT NULL,
 C_PARENTID  	    NUMBER                            NOT NULL,
 C_POSITION  	    NUMBER                            NOT NULL,
 C_LEFT      		NUMBER                            NOT NULL,
 C_RIGHT     		NUMBER                            NOT NULL,
 C_LEVEL     		NUMBER                            NOT NULL,
 C_TITLE     		VARCHAR2(4000 BYTE),
 C_TYPE      		VARCHAR2(4000 BYTE),

    CAL_TIME                    VARCHAR2(4000 BYTE),

    TOP_IMAGE_URL_1                     VARCHAR2(4000 BYTE),
    TOP_IMAGE_URL_2                     VARCHAR2(4000 BYTE),
    TOP_IMAGE_URL_3                     VARCHAR2(4000 BYTE),
    TOP_VENDER_URL                      VARCHAR2(4000 BYTE),
    TOP_VENDER_TEXT                     VARCHAR2(4000 BYTE),
    TOP_POST1_ICON                      VARCHAR2(4000 BYTE),
    TOP_POST1_URL                       VARCHAR2(4000 BYTE),
    TOP_POST1_TEXT                      VARCHAR2(4000 BYTE),
    TOP_POST2_ICON                      VARCHAR2(4000 BYTE),
    TOP_POST2_URL                       VARCHAR2(4000 BYTE),
    TOP_POST2_TEXT                      VARCHAR2(4000 BYTE),
    TOP_POST3_ICON                      VARCHAR2(4000 BYTE),
    TOP_POST3_URL                       VARCHAR2(4000 BYTE),
    TOP_POST3_TEXT                      VARCHAR2(4000 BYTE),
    TOP_POST4_ICON                      VARCHAR2(4000 BYTE),
    TOP_POST4_URL                       VARCHAR2(4000 BYTE),
    TOP_POST4_TEXT                      VARCHAR2(4000 BYTE),
    TOP_POST5_ICON                      VARCHAR2(4000 BYTE),
    TOP_POST5_URL                       VARCHAR2(4000 BYTE),
    TOP_POST5_TEXT                      VARCHAR2(4000 BYTE),

    MID_IMAGE_URL_1                     VARCHAR2(4000 BYTE),
    MID_IMAGE_URL_2                     VARCHAR2(4000 BYTE),
    MID_IMAGE_URL_3                     VARCHAR2(4000 BYTE),
    MID_VENDER_URL                      VARCHAR2(4000 BYTE),
    MID_VENDER_TEXT                     VARCHAR2(4000 BYTE),
    MID_POST1_ICON                      VARCHAR2(4000 BYTE),
    MID_POST1_URL                       VARCHAR2(4000 BYTE),
    MID_POST1_TEXT                      VARCHAR2(4000 BYTE),
    MID_POST2_ICON                      VARCHAR2(4000 BYTE),
    MID_POST2_URL                       VARCHAR2(4000 BYTE),
    MID_POST2_TEXT                      VARCHAR2(4000 BYTE),
    MID_POST3_ICON                      VARCHAR2(4000 BYTE),
    MID_POST3_URL                       VARCHAR2(4000 BYTE),
    MID_POST3_TEXT                      VARCHAR2(4000 BYTE),
    MID_POST4_ICON                      VARCHAR2(4000 BYTE),
    MID_POST4_URL                       VARCHAR2(4000 BYTE),
    MID_POST4_TEXT                      VARCHAR2(4000 BYTE),
    MID_POST5_ICON                      VARCHAR2(4000 BYTE),
    MID_POST5_URL                       VARCHAR2(4000 BYTE),
    MID_POST5_TEXT                      VARCHAR2(4000 BYTE),

    BOT_IMAGE_URL_1                     VARCHAR2(4000 BYTE),
    BOT_IMAGE_URL_2                     VARCHAR2(4000 BYTE),
    BOT_IMAGE_URL_3                     VARCHAR2(4000 BYTE),
    BOT_VENDER_URL                      VARCHAR2(4000 BYTE),
    BOT_VENDER_TEXT                     VARCHAR2(4000 BYTE),
    BOT_POST1_ICON                      VARCHAR2(4000 BYTE),
    BOT_POST1_URL                       VARCHAR2(4000 BYTE),
    BOT_POST1_TEXT                      VARCHAR2(4000 BYTE),
    BOT_POST2_ICON                      VARCHAR2(4000 BYTE),
    BOT_POST2_URL                       VARCHAR2(4000 BYTE),
    BOT_POST2_TEXT                      VARCHAR2(4000 BYTE),
    BOT_POST3_ICON                      VARCHAR2(4000 BYTE),
    BOT_POST3_URL                       VARCHAR2(4000 BYTE),
    BOT_POST3_TEXT                      VARCHAR2(4000 BYTE),
    BOT_POST4_ICON                      VARCHAR2(4000 BYTE),
    BOT_POST4_URL                       VARCHAR2(4000 BYTE),
    BOT_POST4_TEXT                      VARCHAR2(4000 BYTE),
    BOT_POST5_ICON                      VARCHAR2(4000 BYTE),
    BOT_POST5_URL                       VARCHAR2(4000 BYTE),
    BOT_POST5_TEXT                      VARCHAR2(4000 BYTE),

 CONSTRAINT  T_JSTREE_COMPARE_INFO_PK PRIMARY KEY (C_ID)
 /*
   * CONSTRAINT  T_JSTREE_COMPARE_INFO_FK1 FOREIGN KEY (OTHER_ID) REFERENCES OTHER T_JSTREE_COMPARE_INFO(C_ID) ON DELETE CASCADE
   */
);

COMMENT ON TABLE T_JSTREE_COMPARE_INFO IS '기본 트리 스키마';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_ID IS '노드 아이디';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_PARENTID IS '부모 노드 아이디';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_POSITION IS '노드 포지션';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_LEFT IS '노드 좌측 끝 포인트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_RIGHT IS '노드 우측 끝 포인트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_LEVEL IS '노드 DEPTH ';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_TITLE IS '노드 명';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.C_TYPE IS '노드 타입';

COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.CAL_TIME IS '누적 집계 시간';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_IMAGE_URL_1 IS '상위 대표 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_IMAGE_URL_2 IS '상위 서브 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_IMAGE_URL_3 IS '상위 테일 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_VENDER_URL  IS '상위 벤더 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_VENDER_TEXT IS '상위 벤더 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST1_ICON  IS '상위 포스트 1번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST1_URL   IS '상위 포스트 1번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST1_TEXT  IS '상위 포스트 1번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST2_ICON  IS '상위 포스트 2번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST2_URL   IS '상위 포스트 2번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST2_TEXT  IS '상위 포스트 2번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST3_ICON  IS '상위 포스트 3번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST3_URL   IS '상위 포스트 3번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST3_TEXT  IS '상위 포스트 3번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST4_ICON  IS '상위 포스트 4번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST4_URL   IS '상위 포스트 4번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST4_TEXT  IS '상위 포스트 4번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST5_ICON  IS '상위 포스트 5번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST5_URL   IS '상위 포스트 5번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.TOP_POST5_TEXT  IS '상위 포스트 5번 링크 텍스트';

COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_IMAGE_URL_1 IS '중위 대표 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_IMAGE_URL_2 IS '중위 서브 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_IMAGE_URL_3 IS '중위 테일 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_VENDER_URL  IS '중위 벤더 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_VENDER_TEXT IS '중위 벤더 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST1_ICON  IS '중위 포스트 1번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST1_URL   IS '중위 포스트 1번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST1_TEXT  IS '중위 포스트 1번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST2_ICON  IS '중위 포스트 2번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST2_URL   IS '중위 포스트 2번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST2_TEXT  IS '중위 포스트 2번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST3_ICON  IS '중위 포스트 3번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST3_URL   IS '중위 포스트 3번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST3_TEXT  IS '중위 포스트 3번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST4_ICON  IS '중위 포스트 4번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST4_URL   IS '중위 포스트 4번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST4_TEXT  IS '중위 포스트 4번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST5_ICON  IS '중위 포스트 5번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST5_URL   IS '중위 포스트 5번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.MID_POST5_TEXT  IS '중위 포스트 5번 링크 텍스트';

COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_IMAGE_URL_1 IS '하위 대표 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_IMAGE_URL_2 IS '하위 서브 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_IMAGE_URL_3 IS '하위 테일 이미지';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_VENDER_URL  IS '하위 벤더 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_VENDER_TEXT IS '하위 벤더 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST1_ICON  IS '하위 포스트 1번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST1_URL   IS '하위 포스트 1번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST1_TEXT  IS '하위 포스트 1번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST2_ICON  IS '하위 포스트 2번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST2_URL   IS '하위 포스트 2번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST2_TEXT  IS '하위 포스트 2번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST3_ICON  IS '하위 포스트 3번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST3_URL   IS '하위 포스트 3번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST3_TEXT  IS '하위 포스트 3번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST4_ICON  IS '하위 포스트 4번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST4_URL   IS '하위 포스트 4번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST4_TEXT  IS '하위 포스트 4번 링크 텍스트';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST5_ICON  IS '하위 포스트 5번 아이콘';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST5_URL   IS '하위 포스트 5번 링크';
COMMENT ON COLUMN T_JSTREE_COMPARE_INFO.BOT_POST5_TEXT  IS '하위 포스트 5번 링크 텍스트';
/*
 * 인덱스는 되도록 걸지 말것.
 * CREATE UNIQUE INDEX I_COMPREHENSIVETREE ON T_JSTREE_COMPARE_INFO
 *      ("C_ID" ASC)
 * DROP SEQUENCE S_JSTREE_COMPARE_INFO;
 */


CREATE SEQUENCE S_JSTREE_COMPARE_INFO
 START WITH 10
 MAXVALUE 999999999999999999999999999
 MINVALUE 0
 NOCYCLE
 CACHE 20
 NOORDER;

/*
 * JsTree 트리거
 */
CREATE OR REPLACE TRIGGER "TG_JSTREE_COMPARE_INFO"
BEFORE DELETE OR INSERT OR UPDATE
ON T_JSTREE_COMPARE_INFO
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
/******************************************************************************
   NAME:       TRIGGER_COMPREHENSIVETREE
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2012-08-29             1. Created this trigger.

   NOTES:

   Automatically available Auto Replace Keywords:
      Object Name:     TRIGGER_COMPREHENSIVETREE
      Sysdate:         2012-08-29
      Date and Time:   2012-08-29, 오후 5:26:44, and 2012-08-29 오후 5:26:44
      Username:         (set in TOAD Options, Proc Templates)
      Table Name:      T_JSTREE_COMPARE_INFO (set in the "New PL/SQL Object" dialog)
      Trigger Options:  (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
  tmpVar := 0;
   IF UPDATING  THEN
       insert into T_JSTREE_COMPARE_INFO_LOG
       values (:old.C_ID,:old.C_PARENTID,:old.C_POSITION,:old.C_LEFT,:old.C_RIGHT,:old.C_LEVEL,:old.C_TITLE,:old.C_TYPE,'update','변경이전데이터',sysdate);
       insert into T_JSTREE_COMPARE_INFO_LOG
       values (:new.C_ID,:new.C_PARENTID,:new.C_POSITION,:new.C_LEFT,:new.C_RIGHT,:new.C_LEVEL,:new.C_TITLE,:new.C_TYPE,'update','변경이후데이터',sysdate);
    END IF;
   IF DELETING THEN
       insert into T_JSTREE_COMPARE_INFO_LOG
       values (:old.C_ID,:old.C_PARENTID,:old.C_POSITION,:old.C_LEFT,:old.C_RIGHT,:old.C_LEVEL,:old.C_TITLE,:old.C_TYPE,'delete','삭제된데이터',sysdate);
   END IF;
   IF INSERTING  THEN
       insert into T_JSTREE_COMPARE_INFO_LOG
       values (:new.C_ID,:new.C_PARENTID,:new.C_POSITION,:new.C_LEFT,:new.C_RIGHT,:new.C_LEVEL,:new.C_TITLE,:new.C_TYPE,'insert','삽입된데이터',sysdate);
   END IF;

  EXCEPTION
    WHEN OTHERS THEN
      -- Consider logging the error and then re-raise
      RAISE;
END TG_JSTREE_COMPARE_INFO;

/**
PK - FK
**/
ALTER TABLE T_JSTREE_MENU ADD(
  CONSTRAINT T_JSTREE_MENU_FK_CI
 FOREIGN KEY (COMPARE_INFO_ID)
 REFERENCES T_JSTREE_COMPARE_INFO (C_ID));
/