150 lines
6.1 KiB
SQL
150 lines
6.1 KiB
SQL
CREATE TYPE "Papel" AS ENUM ('CLIENTE', 'ADMIN');
|
|
CREATE TYPE "TipoPessoa" AS ENUM ('PF', 'PJ');
|
|
CREATE TYPE "TipoPergunta" AS ENUM ('TEXTAREA', 'RADIO', 'CHECKBOX', 'ANEXO');
|
|
CREATE TYPE "StatusBriefing" AS ENUM ('RASCUNHO', 'ENVIADO', 'EM_ANALISE', 'CONCLUIDO');
|
|
|
|
CREATE TABLE "users" (
|
|
"id" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"senhaHash" TEXT NOT NULL,
|
|
"papel" "Papel" NOT NULL DEFAULT 'CLIENTE',
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"atualizadoEm" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "clientes" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"nomeCompleto" TEXT NOT NULL,
|
|
"cpfCifrado" TEXT NOT NULL,
|
|
"cpfHash" TEXT NOT NULL,
|
|
"whatsapp" TEXT NOT NULL,
|
|
"tipo" "TipoPessoa" NOT NULL DEFAULT 'PF',
|
|
"razaoSocial" TEXT,
|
|
"cnpj" TEXT,
|
|
"cep" TEXT,
|
|
"logradouro" TEXT,
|
|
"numero" TEXT,
|
|
"complemento" TEXT,
|
|
"bairro" TEXT,
|
|
"cidade" TEXT,
|
|
"uf" TEXT,
|
|
"lgpdAceiteEm" TIMESTAMP(3) NOT NULL,
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"atualizadoEm" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "clientes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "secoes" (
|
|
"id" TEXT NOT NULL,
|
|
"ordem" INTEGER NOT NULL,
|
|
"titulo" TEXT NOT NULL,
|
|
"descricao" TEXT,
|
|
"ativo" BOOLEAN NOT NULL DEFAULT true,
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"atualizadoEm" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "secoes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "perguntas" (
|
|
"id" TEXT NOT NULL,
|
|
"secaoId" TEXT NOT NULL,
|
|
"ordem" INTEGER NOT NULL,
|
|
"rotulo" TEXT NOT NULL,
|
|
"dica" TEXT,
|
|
"tipo" "TipoPergunta" NOT NULL,
|
|
"opcoes" JSONB NOT NULL,
|
|
"obrigatoria" BOOLEAN NOT NULL DEFAULT false,
|
|
"permiteComplemento" BOOLEAN NOT NULL DEFAULT false,
|
|
"ativo" BOOLEAN NOT NULL DEFAULT true,
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"atualizadoEm" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "perguntas_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "ajudas" (
|
|
"id" TEXT NOT NULL,
|
|
"perguntaId" TEXT NOT NULL,
|
|
"oQueResponder" TEXT NOT NULL,
|
|
"porQuePerguntamos" TEXT NOT NULL,
|
|
"miniExemplo" TEXT NOT NULL,
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"atualizadoEm" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "ajudas_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "briefings" (
|
|
"id" TEXT NOT NULL,
|
|
"clienteId" TEXT NOT NULL,
|
|
"titulo" TEXT NOT NULL,
|
|
"status" "StatusBriefing" NOT NULL DEFAULT 'RASCUNHO',
|
|
"confirmadoEm" TIMESTAMP(3),
|
|
"enviadoEm" TIMESTAMP(3),
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"atualizadoEm" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "briefings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "respostas" (
|
|
"id" TEXT NOT NULL,
|
|
"briefingId" TEXT NOT NULL,
|
|
"perguntaId" TEXT NOT NULL,
|
|
"texto" TEXT,
|
|
"complemento" TEXT,
|
|
"selecionadas" JSONB,
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"atualizadoEm" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "respostas_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "anexos" (
|
|
"id" TEXT NOT NULL,
|
|
"briefingId" TEXT NOT NULL,
|
|
"perguntaId" TEXT NOT NULL,
|
|
"nomeOriginal" TEXT NOT NULL,
|
|
"caminho" TEXT NOT NULL,
|
|
"mime" TEXT NOT NULL,
|
|
"tamanho" INTEGER NOT NULL,
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "anexos_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "password_reset_tokens" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"tokenHash" TEXT NOT NULL,
|
|
"expiraEm" TIMESTAMP(3) NOT NULL,
|
|
"usadoEm" TIMESTAMP(3),
|
|
"criadoEm" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "password_reset_tokens_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
|
|
CREATE UNIQUE INDEX "clientes_userId_key" ON "clientes"("userId");
|
|
CREATE UNIQUE INDEX "clientes_cpfHash_key" ON "clientes"("cpfHash");
|
|
CREATE INDEX "clientes_tipo_idx" ON "clientes"("tipo");
|
|
CREATE UNIQUE INDEX "secoes_ordem_key" ON "secoes"("ordem");
|
|
CREATE INDEX "perguntas_tipo_idx" ON "perguntas"("tipo");
|
|
CREATE UNIQUE INDEX "perguntas_secaoId_ordem_key" ON "perguntas"("secaoId", "ordem");
|
|
CREATE UNIQUE INDEX "ajudas_perguntaId_key" ON "ajudas"("perguntaId");
|
|
CREATE INDEX "briefings_clienteId_idx" ON "briefings"("clienteId");
|
|
CREATE INDEX "briefings_status_idx" ON "briefings"("status");
|
|
CREATE INDEX "respostas_perguntaId_idx" ON "respostas"("perguntaId");
|
|
CREATE UNIQUE INDEX "respostas_briefingId_perguntaId_key" ON "respostas"("briefingId", "perguntaId");
|
|
CREATE INDEX "anexos_briefingId_idx" ON "anexos"("briefingId");
|
|
CREATE INDEX "anexos_perguntaId_idx" ON "anexos"("perguntaId");
|
|
CREATE UNIQUE INDEX "password_reset_tokens_tokenHash_key" ON "password_reset_tokens"("tokenHash");
|
|
CREATE INDEX "password_reset_tokens_userId_idx" ON "password_reset_tokens"("userId");
|
|
CREATE INDEX "password_reset_tokens_expiraEm_idx" ON "password_reset_tokens"("expiraEm");
|
|
|
|
ALTER TABLE "clientes" ADD CONSTRAINT "clientes_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "perguntas" ADD CONSTRAINT "perguntas_secaoId_fkey" FOREIGN KEY ("secaoId") REFERENCES "secoes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "ajudas" ADD CONSTRAINT "ajudas_perguntaId_fkey" FOREIGN KEY ("perguntaId") REFERENCES "perguntas"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "briefings" ADD CONSTRAINT "briefings_clienteId_fkey" FOREIGN KEY ("clienteId") REFERENCES "clientes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "respostas" ADD CONSTRAINT "respostas_briefingId_fkey" FOREIGN KEY ("briefingId") REFERENCES "briefings"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "respostas" ADD CONSTRAINT "respostas_perguntaId_fkey" FOREIGN KEY ("perguntaId") REFERENCES "perguntas"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "anexos" ADD CONSTRAINT "anexos_briefingId_fkey" FOREIGN KEY ("briefingId") REFERENCES "briefings"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "anexos" ADD CONSTRAINT "anexos_perguntaId_fkey" FOREIGN KEY ("perguntaId") REFERENCES "perguntas"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "password_reset_tokens" ADD CONSTRAINT "password_reset_tokens_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|