migration.sql 2.0 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556
  1. -- Story 2.2: Formulaire de soumission contributeur (FR8, FR14)
  2. -- Ajoute les enums IdeaStatus, ImpactLevel, Frequency et le modèle Idea
  3. -- CreateEnum
  4. CREATE TYPE "IdeaStatus" AS ENUM ('SUBMITTED', 'IN_REVIEW', 'ON_AGENDA', 'IN_PROGRESS', 'NOT_PRIORITY');
  5. -- CreateEnum
  6. CREATE TYPE "ImpactLevel" AS ENUM ('FAIBLE', 'MODERE', 'ELEVE', 'CRITIQUE');
  7. -- CreateEnum
  8. CREATE TYPE "Frequency" AS ENUM ('QUOTIDIENNE', 'HEBDOMADAIRE', 'MENSUELLE', 'TRIMESTRIELLE', 'PONCTUELLE');
  9. -- CreateTable
  10. CREATE TABLE "Idea" (
  11. "id" TEXT NOT NULL,
  12. "workspaceId" TEXT NOT NULL,
  13. "contributorEmail" TEXT NOT NULL,
  14. "contributorId" TEXT,
  15. "department" TEXT NOT NULL,
  16. "problemDescription" TEXT NOT NULL,
  17. "estimatedImpact" "ImpactLevel" NOT NULL,
  18. "frequency" "Frequency" NOT NULL,
  19. "status" "IdeaStatus" NOT NULL DEFAULT 'SUBMITTED',
  20. "llmTitle" TEXT,
  21. "llmDomain" TEXT,
  22. "llmAiType" TEXT,
  23. "llmExplanation" TEXT,
  24. "llmScoreImpact" INTEGER,
  25. "llmScoreFeasibility" INTEGER,
  26. "llmScoreUrgency" INTEGER,
  27. "llmEnrichedAt" TIMESTAMP(3),
  28. "llmEnrichFailed" BOOLEAN NOT NULL DEFAULT false,
  29. "manualScoreImpact" INTEGER,
  30. "manualScoreFeasibility" INTEGER,
  31. "manualScoreUrgency" INTEGER,
  32. "priorityOrder" INTEGER,
  33. "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  34. "updatedAt" TIMESTAMP(3) NOT NULL,
  35. CONSTRAINT "Idea_pkey" PRIMARY KEY ("id")
  36. );
  37. -- CreateIndex
  38. CREATE INDEX "Idea_workspaceId_createdAt_idx" ON "Idea"("workspaceId", "createdAt");
  39. -- CreateIndex
  40. CREATE INDEX "Idea_workspaceId_status_idx" ON "Idea"("workspaceId", "status");
  41. -- CreateIndex
  42. CREATE INDEX "Idea_workspaceId_contributorEmail_idx" ON "Idea"("workspaceId", "contributorEmail");
  43. -- AddForeignKey
  44. ALTER TABLE "Idea" ADD CONSTRAINT "Idea_workspaceId_fkey" FOREIGN KEY ("workspaceId") REFERENCES "Workspace"("id") ON DELETE CASCADE ON UPDATE CASCADE;
  45. -- AddForeignKey
  46. ALTER TABLE "Idea" ADD CONSTRAINT "Idea_contributorId_fkey" FOREIGN KEY ("contributorId") REFERENCES "Contributor"("id") ON DELETE SET NULL ON UPDATE CASCADE;