-----------------------------------------------------------------------------
-- AVZ_T_ENQUETE
-----------------------------------------------------------------------------

CREATE TABLE AVZ_T_ENQUETE
(
   ENQUETE_ID serial NOT NULL,
   CREATER_ID INTEGER,
   NAME VARCHAR (99),
   COMMENT VARCHAR,
   ITEM_NUM INTEGER,
   ITEM_NUM_STATUS VARCHAR (1),
   CREATE_DATE TIMESTAMP,
   UPDATE_DATE TIMESTAMP,
   PUBLIC_METHOD VARCHAR (1),
   PUBLIC_STATUS VARCHAR (1),
   PUBLIC_DATE TIMESTAMP,
   DEADLINE_METHOD VARCHAR (1),
   DEADLINE_DATE TIMESTAMP,
   CONSTRAINT AVZ_T_ENQUETE_ENQUETE_ID_PKEY PRIMARY KEY (ENQUETE_ID)
);

-----------------------------------------------------------------------------
-- AVZ_T_ENQUETE_ITEM
-----------------------------------------------------------------------------

CREATE TABLE AVZ_T_ENQUETE_ITEM
(
   ITEM_ID serial NOT NULL,
   ENQUETE_ID INTEGER NOT NULL,
   ITEM_NUMBER INTEGER NOT NULL,
   ITEM_TITLE VARCHAR (99),
   CHOICE_ITEM_NUM INTEGER NOT NULL,
   STATUS VARCHAR (1),
   CHOICE_ITEM_NUM_STATUS VARCHAR (1),
   FOREIGN KEY (ENQUETE_ID) REFERENCES AVZ_T_ENQUETE (ENQUETE_ID) ON DELETE CASCADE,
   PRIMARY KEY(ITEM_ID)
);
-----------------------------------------------------------------------------
-- AVZ_T_ENQUETE_CHOICE_ITEM
-----------------------------------------------------------------------------

CREATE TABLE AVZ_T_ENQUETE_CHOICE_ITEM
(
   CHOICE_ITEM_ID serial,
   ENQUETE_ID INTEGER NOT NULL,
   ITEM_ID INTEGER NOT NULL,
   ANSWER_ID INTEGER NOT NULL,
   ITEM_NUMBER INTEGER NOT NULL,
   CHOICE_ITEM_NUMBER INTEGER NOT NULL,
   CHOICE_ITEM_TITLE VARCHAR (500),
   STATUS VARCHAR (1),
   RESPONSE_STATUS VARCHAR (1),
   FOREIGN KEY (ENQUETE_ID) REFERENCES AVZ_T_ENQUETE (ENQUETE_ID) ON DELETE CASCADE,
   FOREIGN KEY (ITEM_ID) REFERENCES AVZ_T_ENQUETE_ITEM (ITEM_ID) ON DELETE CASCADE,
   PRIMARY KEY(CHOICE_ITEM_ID)
);