61 lines
2.7 KiB
SQL
61 lines
2.7 KiB
SQL
CREATE TYPE "public"."WeeklyKpiStatus" AS ENUM ('on_track', 'watch', 'risk', 'no_score');
|
|
|
|
CREATE TABLE "public"."WeeklyKpiSnapshot" (
|
|
"id" TEXT NOT NULL,
|
|
"weekStart" TIMESTAMP(3) NOT NULL,
|
|
"weekEnd" TIMESTAMP(3) NOT NULL,
|
|
"source" TEXT NOT NULL DEFAULT 'platform',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "WeeklyKpiSnapshot_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "public"."WeeklyKpiSection" (
|
|
"id" TEXT NOT NULL,
|
|
"snapshotId" TEXT NOT NULL,
|
|
"sectionKey" TEXT NOT NULL,
|
|
"rawSectionLabel" TEXT NOT NULL,
|
|
"mappedDepartment" "public"."DepartmentKey",
|
|
"ownerTeamLabel" TEXT,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "WeeklyKpiSection_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "public"."WeeklyKpiRow" (
|
|
"id" TEXT NOT NULL,
|
|
"sectionId" TEXT NOT NULL,
|
|
"rowKey" TEXT NOT NULL,
|
|
"responsibilityText" TEXT NOT NULL,
|
|
"objectiveIndicatorText" TEXT,
|
|
"quantityQualityText" TEXT,
|
|
"complianceText" TEXT,
|
|
"dueCommitmentText" TEXT,
|
|
"targetValue" DOUBLE PRECISION,
|
|
"quantityValue" DOUBLE PRECISION,
|
|
"compliancePct" DOUBLE PRECISION,
|
|
"dueDate" TIMESTAMP(3),
|
|
"status" "public"."WeeklyKpiStatus" NOT NULL DEFAULT 'no_score',
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "WeeklyKpiRow_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "WeeklyKpiSnapshot_weekStart_key" ON "public"."WeeklyKpiSnapshot"("weekStart");
|
|
CREATE INDEX "WeeklyKpiSnapshot_weekStart_weekEnd_idx" ON "public"."WeeklyKpiSnapshot"("weekStart", "weekEnd");
|
|
CREATE UNIQUE INDEX "WeeklyKpiSection_snapshotId_sectionKey_key" ON "public"."WeeklyKpiSection"("snapshotId", "sectionKey");
|
|
CREATE INDEX "WeeklyKpiSection_snapshotId_mappedDepartment_sortOrder_idx" ON "public"."WeeklyKpiSection"("snapshotId", "mappedDepartment", "sortOrder");
|
|
CREATE UNIQUE INDEX "WeeklyKpiRow_sectionId_rowKey_key" ON "public"."WeeklyKpiRow"("sectionId", "rowKey");
|
|
CREATE INDEX "WeeklyKpiRow_sectionId_sortOrder_idx" ON "public"."WeeklyKpiRow"("sectionId", "sortOrder");
|
|
CREATE INDEX "WeeklyKpiRow_status_dueDate_idx" ON "public"."WeeklyKpiRow"("status", "dueDate");
|
|
|
|
ALTER TABLE "public"."WeeklyKpiSection"
|
|
ADD CONSTRAINT "WeeklyKpiSection_snapshotId_fkey"
|
|
FOREIGN KEY ("snapshotId") REFERENCES "public"."WeeklyKpiSnapshot"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "public"."WeeklyKpiRow"
|
|
ADD CONSTRAINT "WeeklyKpiRow_sectionId_fkey"
|
|
FOREIGN KEY ("sectionId") REFERENCES "public"."WeeklyKpiSection"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|