100 lines
4.1 KiB
SQL
100 lines
4.1 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "WorkshopProgressStatus" AS ENUM ('NOT_STARTED', 'WATCHED', 'EVIDENCE_SUBMITTED', 'APPROVED', 'REJECTED', 'SKIPPED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "WorkshopEvidenceValidationStatus" AS ENUM ('PENDING', 'APPROVED', 'REJECTED', 'ERROR');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "DevelopmentWorkshop" (
|
|
"id" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"moduleId" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"summary" TEXT NOT NULL,
|
|
"videoUrl" TEXT NOT NULL,
|
|
"durationMinutes" INTEGER NOT NULL DEFAULT 0,
|
|
"evidenceRequired" TEXT NOT NULL,
|
|
"learningObjectives" JSONB,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "DevelopmentWorkshop_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "DevelopmentWorkshopProgress" (
|
|
"id" TEXT NOT NULL,
|
|
"workshopId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"status" "WorkshopProgressStatus" NOT NULL DEFAULT 'NOT_STARTED',
|
|
"watchedAt" TIMESTAMP(3),
|
|
"skippedAt" TIMESTAMP(3),
|
|
"completedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "DevelopmentWorkshopProgress_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "DevelopmentWorkshopEvidence" (
|
|
"id" TEXT NOT NULL,
|
|
"workshopId" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"validationStatus" "WorkshopEvidenceValidationStatus" NOT NULL DEFAULT 'PENDING',
|
|
"validationReason" TEXT,
|
|
"validationConfidence" DOUBLE PRECISION,
|
|
"validatedAt" TIMESTAMP(3),
|
|
"fileName" TEXT NOT NULL,
|
|
"storedFileName" TEXT NOT NULL,
|
|
"filePath" TEXT NOT NULL,
|
|
"mimeType" TEXT NOT NULL,
|
|
"sizeBytes" INTEGER NOT NULL,
|
|
"checksumSha256" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "DevelopmentWorkshopEvidence_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "DevelopmentWorkshop_key_key" ON "DevelopmentWorkshop"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "DevelopmentWorkshop_moduleId_sortOrder_idx" ON "DevelopmentWorkshop"("moduleId", "sortOrder");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "DevelopmentWorkshop_isActive_sortOrder_idx" ON "DevelopmentWorkshop"("isActive", "sortOrder");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "DevelopmentWorkshopProgress_workshopId_userId_key" ON "DevelopmentWorkshopProgress"("workshopId", "userId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "DevelopmentWorkshopProgress_userId_status_idx" ON "DevelopmentWorkshopProgress"("userId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "DevelopmentWorkshopProgress_workshopId_status_idx" ON "DevelopmentWorkshopProgress"("workshopId", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "DevelopmentWorkshopEvidence_userId_workshopId_createdAt_idx" ON "DevelopmentWorkshopEvidence"("userId", "workshopId", "createdAt");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "DevelopmentWorkshopEvidence_workshopId_validationStatus_idx" ON "DevelopmentWorkshopEvidence"("workshopId", "validationStatus");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DevelopmentWorkshop" ADD CONSTRAINT "DevelopmentWorkshop_moduleId_fkey" FOREIGN KEY ("moduleId") REFERENCES "DiagnosticModule"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DevelopmentWorkshopProgress" ADD CONSTRAINT "DevelopmentWorkshopProgress_workshopId_fkey" FOREIGN KEY ("workshopId") REFERENCES "DevelopmentWorkshop"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DevelopmentWorkshopProgress" ADD CONSTRAINT "DevelopmentWorkshopProgress_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DevelopmentWorkshopEvidence" ADD CONSTRAINT "DevelopmentWorkshopEvidence_workshopId_fkey" FOREIGN KEY ("workshopId") REFERENCES "DevelopmentWorkshop"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "DevelopmentWorkshopEvidence" ADD CONSTRAINT "DevelopmentWorkshopEvidence_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|