88 lines
2.7 KiB
SQL
88 lines
2.7 KiB
SQL
-- T-16: replace Work/App with a unified Project model and align categories.
|
|
|
|
-- The old content tables are intentionally removed because the database has no production content yet.
|
|
DROP TABLE "Work";
|
|
DROP TABLE "App";
|
|
DROP TABLE "Melody";
|
|
|
|
CREATE TYPE "ProjectType" AS ENUM ('PORTFOLIO', 'APP', 'WEBSITE', 'DESIGN');
|
|
|
|
CREATE TYPE "CategoryKind_new" AS ENUM ('PROJECT', 'MELODY');
|
|
|
|
ALTER TABLE "Category"
|
|
ALTER COLUMN "kind" TYPE "CategoryKind_new"
|
|
USING (
|
|
CASE
|
|
WHEN "kind"::text = 'MELODY' THEN 'MELODY'::"CategoryKind_new"
|
|
ELSE 'PROJECT'::"CategoryKind_new"
|
|
END
|
|
);
|
|
|
|
DROP TYPE "CategoryKind";
|
|
ALTER TYPE "CategoryKind_new" RENAME TO "CategoryKind";
|
|
|
|
CREATE TABLE "Project" (
|
|
"id" TEXT NOT NULL,
|
|
"type" "ProjectType" NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"titleAr" TEXT NOT NULL,
|
|
"titleEn" TEXT NOT NULL,
|
|
"summaryAr" TEXT,
|
|
"summaryEn" TEXT,
|
|
"descAr" TEXT,
|
|
"descEn" TEXT,
|
|
"coverImage" TEXT,
|
|
"images" TEXT[] NOT NULL,
|
|
"technologies" TEXT[] NOT NULL,
|
|
"externalUrl" TEXT,
|
|
"repoUrl" TEXT,
|
|
"platform" TEXT,
|
|
"appStoreUrl" TEXT,
|
|
"testflightUrl" TEXT,
|
|
"appVersion" TEXT,
|
|
"supportUrl" TEXT,
|
|
"appPrivacyUrl" TEXT,
|
|
"status" "PublishStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"isFeatured" BOOLEAN NOT NULL DEFAULT false,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"publishedAt" TIMESTAMP(3),
|
|
"categoryId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Project_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "Melody" (
|
|
"id" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"titleAr" TEXT NOT NULL,
|
|
"titleEn" TEXT NOT NULL,
|
|
"descAr" TEXT,
|
|
"descEn" TEXT,
|
|
"audioFile" TEXT NOT NULL,
|
|
"coverImage" TEXT,
|
|
"durationSec" INTEGER,
|
|
"isDownloadable" BOOLEAN NOT NULL DEFAULT false,
|
|
"status" "PublishStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"isFeatured" BOOLEAN NOT NULL DEFAULT false,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"categoryId" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Melody_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "Project_slug_key" ON "Project"("slug");
|
|
CREATE INDEX "Project_type_status_idx" ON "Project"("type", "status");
|
|
CREATE UNIQUE INDEX "Melody_slug_key" ON "Melody"("slug");
|
|
|
|
ALTER TABLE "Project"
|
|
ADD CONSTRAINT "Project_categoryId_fkey"
|
|
FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
ALTER TABLE "Melody"
|
|
ADD CONSTRAINT "Melody_categoryId_fkey"
|
|
FOREIGN KEY ("categoryId") REFERENCES "Category"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|