217 lines
9.3 KiB
SQL
217 lines
9.3 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "public"."MarketingInitiativeType" AS ENUM ('evento', 'campania', 'cambio', 'implementacion', 'otro');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "public"."MarketingInitiativeStatus" AS ENUM ('planning', 'in_progress', 'completion', 'results', 'evaluation');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "public"."MarketingTaskStatus" AS ENUM ('todo', 'in_progress', 'blocked', 'done');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "public"."MarketingPublicOpinion" AS ENUM ('positive', 'mixed', 'negative');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingInitiative" (
|
|
"id" TEXT NOT NULL,
|
|
"department" "public"."DepartmentKey" NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"type" "public"."MarketingInitiativeType" NOT NULL,
|
|
"status" "public"."MarketingInitiativeStatus" NOT NULL DEFAULT 'planning',
|
|
"ownerId" TEXT,
|
|
"dueDate" TIMESTAMP(3) NOT NULL,
|
|
"completedAt" TIMESTAMP(3),
|
|
"importanceWeight" DOUBLE PRECISION NOT NULL DEFAULT 1,
|
|
"leadRating1to5" INTEGER NOT NULL DEFAULT 3,
|
|
"target" DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
"actual" DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
"ticketsTarget" INTEGER NOT NULL DEFAULT 0,
|
|
"ticketsActual" INTEGER NOT NULL DEFAULT 0,
|
|
"revenueTarget" DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
"revenueActual" DOUBLE PRECISION NOT NULL DEFAULT 0,
|
|
"opinionPublica" "public"."MarketingPublicOpinion" NOT NULL DEFAULT 'mixed',
|
|
"queFunciono" TEXT NOT NULL DEFAULT '',
|
|
"queNo" TEXT NOT NULL DEFAULT '',
|
|
"proximoIntento" TEXT NOT NULL DEFAULT '',
|
|
"updatedById" TEXT,
|
|
"updatedByName" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "MarketingInitiative_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingInitiativeContributor" (
|
|
"initiativeId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingInitiativeContributor_pkey" PRIMARY KEY ("initiativeId","userId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingInitiativeLocation" (
|
|
"id" TEXT NOT NULL,
|
|
"initiativeId" TEXT NOT NULL,
|
|
"locationId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingInitiativeLocation_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingInitiativeEvidence" (
|
|
"id" TEXT NOT NULL,
|
|
"initiativeId" TEXT NOT NULL,
|
|
"url" TEXT NOT NULL,
|
|
"createdById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingInitiativeEvidence_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingTask" (
|
|
"id" TEXT NOT NULL,
|
|
"initiativeId" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL DEFAULT '',
|
|
"assigneeId" TEXT,
|
|
"status" "public"."MarketingTaskStatus" NOT NULL DEFAULT 'todo',
|
|
"dueDate" TIMESTAMP(3) NOT NULL,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingTask_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingTaskEvidence" (
|
|
"id" TEXT NOT NULL,
|
|
"taskId" TEXT NOT NULL,
|
|
"url" TEXT NOT NULL,
|
|
"createdById" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingTaskEvidence_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingInitiativeEdit" (
|
|
"id" TEXT NOT NULL,
|
|
"initiativeId" TEXT NOT NULL,
|
|
"editedById" TEXT,
|
|
"editedByName" TEXT NOT NULL,
|
|
"summary" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingInitiativeEdit_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingSocialSnapshot" (
|
|
"id" TEXT NOT NULL,
|
|
"department" "public"."DepartmentKey" NOT NULL,
|
|
"channel" TEXT NOT NULL,
|
|
"range" TEXT NOT NULL,
|
|
"followersStart" INTEGER NOT NULL,
|
|
"followersEnd" INTEGER NOT NULL,
|
|
"engagementRate" DOUBLE PRECISION NOT NULL,
|
|
"reach" INTEGER NOT NULL,
|
|
"impressions" INTEGER NOT NULL,
|
|
"capturedAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingSocialSnapshot_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "public"."MarketingBrandPulse" (
|
|
"id" TEXT NOT NULL,
|
|
"department" "public"."DepartmentKey" NOT NULL,
|
|
"month" TEXT NOT NULL,
|
|
"rating1to5" INTEGER NOT NULL,
|
|
"notes" TEXT NOT NULL DEFAULT '',
|
|
"updatedById" TEXT,
|
|
"updatedByName" TEXT,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "MarketingBrandPulse_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingInitiative_department_status_dueDate_idx" ON "public"."MarketingInitiative"("department", "status", "dueDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingInitiativeContributor_userId_idx" ON "public"."MarketingInitiativeContributor"("userId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "MarketingInitiativeLocation_initiativeId_locationId_key" ON "public"."MarketingInitiativeLocation"("initiativeId", "locationId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingInitiativeLocation_locationId_idx" ON "public"."MarketingInitiativeLocation"("locationId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingInitiativeEvidence_initiativeId_createdAt_idx" ON "public"."MarketingInitiativeEvidence"("initiativeId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingTask_initiativeId_status_dueDate_idx" ON "public"."MarketingTask"("initiativeId", "status", "dueDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingTask_assigneeId_idx" ON "public"."MarketingTask"("assigneeId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingTaskEvidence_taskId_createdAt_idx" ON "public"."MarketingTaskEvidence"("taskId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingInitiativeEdit_initiativeId_createdAt_idx" ON "public"."MarketingInitiativeEdit"("initiativeId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingSocialSnapshot_department_range_channel_capturedAt_idx" ON "public"."MarketingSocialSnapshot"("department", "range", "channel", "capturedAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "MarketingBrandPulse_department_month_idx" ON "public"."MarketingBrandPulse"("department", "month");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiative" ADD CONSTRAINT "MarketingInitiative_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiative" ADD CONSTRAINT "MarketingInitiative_updatedById_fkey" FOREIGN KEY ("updatedById") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiativeContributor" ADD CONSTRAINT "MarketingInitiativeContributor_initiativeId_fkey" FOREIGN KEY ("initiativeId") REFERENCES "public"."MarketingInitiative"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiativeContributor" ADD CONSTRAINT "MarketingInitiativeContributor_userId_fkey" FOREIGN KEY ("userId") REFERENCES "public"."User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiativeLocation" ADD CONSTRAINT "MarketingInitiativeLocation_initiativeId_fkey" FOREIGN KEY ("initiativeId") REFERENCES "public"."MarketingInitiative"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiativeEvidence" ADD CONSTRAINT "MarketingInitiativeEvidence_initiativeId_fkey" FOREIGN KEY ("initiativeId") REFERENCES "public"."MarketingInitiative"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiativeEvidence" ADD CONSTRAINT "MarketingInitiativeEvidence_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingTask" ADD CONSTRAINT "MarketingTask_initiativeId_fkey" FOREIGN KEY ("initiativeId") REFERENCES "public"."MarketingInitiative"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingTask" ADD CONSTRAINT "MarketingTask_assigneeId_fkey" FOREIGN KEY ("assigneeId") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingTaskEvidence" ADD CONSTRAINT "MarketingTaskEvidence_taskId_fkey" FOREIGN KEY ("taskId") REFERENCES "public"."MarketingTask"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingTaskEvidence" ADD CONSTRAINT "MarketingTaskEvidence_createdById_fkey" FOREIGN KEY ("createdById") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiativeEdit" ADD CONSTRAINT "MarketingInitiativeEdit_initiativeId_fkey" FOREIGN KEY ("initiativeId") REFERENCES "public"."MarketingInitiative"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingInitiativeEdit" ADD CONSTRAINT "MarketingInitiativeEdit_editedById_fkey" FOREIGN KEY ("editedById") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "public"."MarketingBrandPulse" ADD CONSTRAINT "MarketingBrandPulse_updatedById_fkey" FOREIGN KEY ("updatedById") REFERENCES "public"."User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|