/**
 * @author tpasquet via sbarthon
 * @date 19/07/21
 * @version 1.0.0
 * @project SIEau Fonto de vivo
 * @table public.enquetes et autres
 */

# --- !Ups

drop table if exists ENQUETES_DEFINITIONS;
drop table if exists ENQUETES_PARAGRAPHES;
drop table if exists ENQUETES_QUESTIONS;
drop table if exists ENQUETES_QUESTIONS_VALEURS;
drop table if exists ENQUETES_DESTINATAIRE;
drop table if exists ENQUETES_REPONSES;
drop table if exists ENQUETES_REPONSES_MULTIPLES;

CREATE TABLE ENQUETES_DEFINITIONS (
idEnquete bigint,
dateEnquete timestamp with time zone,
dateDebut timestamp with time zone,
dateFin timestamp with time zone,
codePays bigint,
dateMaj timestamp with time zone,
loginMaj character varying(25)
);
CREATE TABLE ENQUETES_PARAGRAPHES (
idEnquete bigint,
idParagraphe bigint,
description text
);
CREATE TABLE ENQUETES_QUESTIONS (
idEnquete bigint,
idParagraphe bigint,
idQuestion bigint,
description text,
typeQuestion bigint,
obligatoire boolean
);
CREATE TABLE ENQUETES_QUESTIONS_VALEURS (
idEnquete bigint,
idQuestion bigint,
idReponse bigint,
descriptionReponse text
);
CREATE TABLE ENQUETES_DESTINATAIRE (
codeDestinataire bigint,
typeDestinataire bigint,
idEnquete bigint,
dateReponse timestamp with time zone
);
CREATE TABLE ENQUETES_REPONSES (
codeDestinataire bigint,
typeDestinataire bigint,
idQuestion bigint,
reponseLong bigint,
reponseBoolean bigint,
reponseDate timestamp with time zone,
reponseText text,
reponseChoixMultiple text
);
CREATE TABLE ENQUETES_REPONSES_MULTIPLES (
codeDestinataire bigint,
typeDestinataire bigint,
idEnquete bigint,
idQuestion bigint,
reponseLong bigint
);
ALTER TABLE ENQUETES_DEFINITIONS ADD description character varying (100);
ALTER TABLE ENQUETES_DEFINITIONS ADD statutEnquete bigint;
ALTER TABLE ENQUETES_PARAGRAPHES ADD ordreAffichage integer;
ALTER TABLE ENQUETES_QUESTIONS ADD ordreAffichage integer;
ALTER TABLE ENQUETES_REPONSES ADD idEnquete bigint;
CREATE UNIQUE INDEX if not exists pk_enquetes ON ENQUETES_DEFINITIONS (idEnquete);
CREATE UNIQUE INDEX if not exists pk_enquetes_paragraphes ON ENQUETES_PARAGRAPHES (idParagraphe);
CREATE UNIQUE INDEX if not exists pk_enquetes_questions ON ENQUETES_QUESTIONS (idQuestion);
CREATE UNIQUE INDEX if not exists pk_enquetes_questions_valeurs ON ENQUETES_QUESTIONS_VALEURS (idReponse);
CREATE INDEX if not exists idx_enquetes_questions_valeurs ON ENQUETES_QUESTIONS_VALEURS (idEnquete, idQuestion);
CREATE UNIQUE INDEX if not exists pk_enquetes_questions_valeurs ON ENQUETES_QUESTIONS_VALEURS (idReponse);
CREATE INDEX if not exists idx_enquetes_questions_valeurs ON ENQUETES_QUESTIONS_VALEURS (idEnquete, idQuestion);
CREATE INDEX if not exists idx_enquetes_reponses ON ENQUETES_REPONSES (codeDestinataire, typeDestinataire, idEnquete, idQuestion);
CREATE INDEX if not exists idx_enquetes_reponses_multiples ON ENQUETES_REPONSES_MULTIPLES (codeDestinataire, typeDestinataire, idEnquete, idQuestion);


# --- !Downs

drop table if exists ENQUETES_DEFINITIONS;
drop table if exists ENQUETES_PARAGRAPHES;
drop table if exists ENQUETES_QUESTIONS;
drop table if exists ENQUETES_QUESTIONS_VALEURS;
drop table if exists ENQUETES_DESTINATAIRE;
drop table if exists ENQUETES_REPONSES;
drop table if exists ENQUETES_REPONSES_MULTIPLES;