216 lines
7.0 KiB
SQL
216 lines
7.0 KiB
SQL
-- CreateSchema
|
|
CREATE SCHEMA IF NOT EXISTS "public";
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "TripStatus" AS ENUM ('PLANNING', 'BOOKED', 'IN_PROGRESS', 'COMPLETED', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "CollaboratorRole" AS ENUM ('OWNER', 'EDITOR', 'VIEWER', 'MEMBER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ItineraryCategory" AS ENUM ('FLIGHT', 'TRANSPORT', 'ACCOMMODATION', 'ACTIVITY', 'MEAL', 'FREE_TIME', 'OTHER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "BookingType" AS ENUM ('FLIGHT', 'HOTEL', 'CAR_RENTAL', 'TRAIN', 'BUS', 'FERRY', 'ACTIVITY', 'RESTAURANT', 'OTHER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "BookingStatus" AS ENUM ('PLANNED', 'BOOKED', 'CONFIRMED', 'CANCELLED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ExpenseCategory" AS ENUM ('FLIGHT', 'ACCOMMODATION', 'FOOD', 'TRANSPORT', 'ACTIVITY', 'SHOPPING', 'OTHER');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SplitType" AS ENUM ('EQUAL', 'CUSTOM', 'INDIVIDUAL');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"did" TEXT NOT NULL,
|
|
"username" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Trip" (
|
|
"id" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"rawInput" TEXT,
|
|
"startDate" TIMESTAMP(3),
|
|
"endDate" TIMESTAMP(3),
|
|
"budgetTotal" DOUBLE PRECISION,
|
|
"budgetCurrency" TEXT NOT NULL DEFAULT 'USD',
|
|
"status" "TripStatus" NOT NULL DEFAULT 'PLANNING',
|
|
"canvasSlug" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Trip_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "TripCollaborator" (
|
|
"id" TEXT NOT NULL,
|
|
"userId" TEXT NOT NULL,
|
|
"tripId" TEXT NOT NULL,
|
|
"role" "CollaboratorRole" NOT NULL DEFAULT 'MEMBER',
|
|
"joinedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "TripCollaborator_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Destination" (
|
|
"id" TEXT NOT NULL,
|
|
"tripId" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"country" TEXT,
|
|
"lat" DOUBLE PRECISION,
|
|
"lng" DOUBLE PRECISION,
|
|
"arrivalDate" TIMESTAMP(3),
|
|
"departureDate" TIMESTAMP(3),
|
|
"notes" TEXT,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"canvasShapeId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Destination_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "ItineraryItem" (
|
|
"id" TEXT NOT NULL,
|
|
"tripId" TEXT NOT NULL,
|
|
"destinationId" TEXT,
|
|
"title" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"date" TIMESTAMP(3),
|
|
"startTime" TEXT,
|
|
"endTime" TEXT,
|
|
"category" "ItineraryCategory" NOT NULL DEFAULT 'ACTIVITY',
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"canvasShapeId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "ItineraryItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Booking" (
|
|
"id" TEXT NOT NULL,
|
|
"tripId" TEXT NOT NULL,
|
|
"destinationId" TEXT,
|
|
"type" "BookingType" NOT NULL,
|
|
"provider" TEXT,
|
|
"confirmationNumber" TEXT,
|
|
"details" TEXT,
|
|
"cost" DOUBLE PRECISION,
|
|
"currency" TEXT NOT NULL DEFAULT 'USD',
|
|
"startDate" TIMESTAMP(3),
|
|
"endDate" TIMESTAMP(3),
|
|
"status" "BookingStatus" NOT NULL DEFAULT 'PLANNED',
|
|
"canvasShapeId" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Booking_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Expense" (
|
|
"id" TEXT NOT NULL,
|
|
"tripId" TEXT NOT NULL,
|
|
"paidById" TEXT,
|
|
"description" TEXT NOT NULL,
|
|
"amount" DOUBLE PRECISION NOT NULL,
|
|
"currency" TEXT NOT NULL DEFAULT 'USD',
|
|
"category" "ExpenseCategory" NOT NULL DEFAULT 'OTHER',
|
|
"date" TIMESTAMP(3),
|
|
"splitType" "SplitType" NOT NULL DEFAULT 'EQUAL',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Expense_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "PackingItem" (
|
|
"id" TEXT NOT NULL,
|
|
"tripId" TEXT NOT NULL,
|
|
"addedById" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"category" TEXT,
|
|
"packed" BOOLEAN NOT NULL DEFAULT false,
|
|
"quantity" INTEGER NOT NULL DEFAULT 1,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "PackingItem_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_did_key" ON "User"("did");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Trip_slug_key" ON "Trip"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "TripCollaborator_tripId_idx" ON "TripCollaborator"("tripId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "TripCollaborator_userId_tripId_key" ON "TripCollaborator"("userId", "tripId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Destination_tripId_idx" ON "Destination"("tripId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ItineraryItem_tripId_idx" ON "ItineraryItem"("tripId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "ItineraryItem_destinationId_idx" ON "ItineraryItem"("destinationId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Booking_tripId_idx" ON "Booking"("tripId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Expense_tripId_idx" ON "Expense"("tripId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "PackingItem_tripId_idx" ON "PackingItem"("tripId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TripCollaborator" ADD CONSTRAINT "TripCollaborator_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "TripCollaborator" ADD CONSTRAINT "TripCollaborator_tripId_fkey" FOREIGN KEY ("tripId") REFERENCES "Trip"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Destination" ADD CONSTRAINT "Destination_tripId_fkey" FOREIGN KEY ("tripId") REFERENCES "Trip"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ItineraryItem" ADD CONSTRAINT "ItineraryItem_tripId_fkey" FOREIGN KEY ("tripId") REFERENCES "Trip"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "ItineraryItem" ADD CONSTRAINT "ItineraryItem_destinationId_fkey" FOREIGN KEY ("destinationId") REFERENCES "Destination"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_tripId_fkey" FOREIGN KEY ("tripId") REFERENCES "Trip"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_destinationId_fkey" FOREIGN KEY ("destinationId") REFERENCES "Destination"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Expense" ADD CONSTRAINT "Expense_tripId_fkey" FOREIGN KEY ("tripId") REFERENCES "Trip"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Expense" ADD CONSTRAINT "Expense_paidById_fkey" FOREIGN KEY ("paidById") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PackingItem" ADD CONSTRAINT "PackingItem_tripId_fkey" FOREIGN KEY ("tripId") REFERENCES "Trip"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "PackingItem" ADD CONSTRAINT "PackingItem_addedById_fkey" FOREIGN KEY ("addedById") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|