DROP TABLE IF EXISTS avaliacao_questao_tipo CASCADE;
CREATE TABLE IF NOT EXISTS avaliacao_questao_tipo
(
  id   SERIAL NOT NULL,
  nome CHARACTER VARYING(30),
  CONSTRAINT pk_avaliacao_questao_tipo PRIMARY KEY (id)
)
WITH (
OIDS =FALSE
);
ALTER TABLE avaliacao_questao_tipo
OWNER TO postgres;


-- Table: tbl_questao

DROP TABLE IF EXISTS avaliacao_questao CASCADE;
CREATE TABLE IF NOT EXISTS avaliacao_questao
(
  id              SERIAL   NOT NULL,
  ordem           INTEGER  NOT NULL,
  questao         TEXT,
  questao_tipo_id INTEGER  NOT NULL,
  estado          SMALLINT NOT NULL DEFAULT 1,
  CONSTRAINT pk_avaliacao_questao PRIMARY KEY (id),
  CONSTRAINT fk_avaliacao_questao_tipo FOREIGN KEY (questao_tipo_id)
  REFERENCES avaliacao_questao_tipo (id) MATCH FULL
  ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (
OIDS =FALSE
);
ALTER TABLE avaliacao_questao
OWNER TO postgres;


DROP TABLE IF EXISTS avaliacao CASCADE;
CREATE TABLE IF NOT EXISTS avaliacao
(
  id                     SERIAL                NOT NULL,
  usuario_tipo_id        CHARACTER(1)          NOT NULL,
  avaliador_id           CHARACTER VARYING(20) NOT NULL,
  funcionario_id         CHARACTER(5)          NOT NULL,
  turma_disciplina_id    INTEGER               DEFAULT NULL,
  departamento_delegacao CHARACTER VARYING(10) DEFAULT NULL,
  ano                    CHARACTER(4)          NOT NULL,
  semestre               CHARACTER(1)          NOT NULL,
  obs                    TEXT,
  CONSTRAINT pk_avaliacao PRIMARY KEY (id),
  CONSTRAINT fk_funcionario FOREIGN KEY (funcionario_id)
  REFERENCES funcionario (id) MATCH FULL
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_turma_disciplina FOREIGN KEY (turma_disciplina_id)
  REFERENCES turma_disciplina (id) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_usuario FOREIGN KEY (usuario_tipo_id)
  REFERENCES usuario_tipo (id) MATCH FULL
  ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (
OIDS =FALSE
);
ALTER TABLE avaliacao
OWNER TO postgres;

DROP TABLE IF EXISTS avaliacao_pontuacao CASCADE;
CREATE TABLE IF NOT EXISTS avaliacao_pontuacao
(
  id                   SERIAL                NOT NULL,
  avaliacao_id         INTEGER               NOT NULL,
  avaliacao_questao_id INTEGER               NOT NULL,
  pontuacao            SMALLINT              NOT NULL,
  CONSTRAINT pk_avaliacao_pontuacao PRIMARY KEY (id),
  CONSTRAINT fk_avaliacao FOREIGN KEY (avaliacao_id)
  REFERENCES avaliacao (id) MATCH FULL
  ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_avaliacao_questao FOREIGN KEY (avaliacao_questao_id)
  REFERENCES avaliacao_questao (id)
  ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (
OIDS =FALSE
);
ALTER TABLE avaliacao_pontuacao
OWNER TO postgres;

-- Table: questao_tipo






CREATE TABLE audit.log
(
  id         SERIAL                      NOT NULL,
  ficheiro   CHARACTER VARYING(50)       NOT NULL,
  accao      TEXT                        NOT NULL,
  sucesso    SMALLINT                    NOT NULL DEFAULT 0,
  ip         CHARACTER VARYING(30)       NOT NULL,
  data_reg   TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT now(),
  usuario_id CHARACTER VARYING(50),
  state      SMALLINT                    NOT NULL DEFAULT 1,
  obs        TEXT,
  CONSTRAINT log_pkey PRIMARY KEY (id)
)
WITH (
OIDS =FALSE
);
ALTER TABLE audit.log
OWNER TO postgres;

CREATE INDEX ON avaliacao (avaliador_id);
CREATE INDEX ON avaliacao_pontuacao (avaliador_id);
CREATE INDEX ON avaliacao_questao (ordem);

