292 lines
9.3 KiB
SQL
292 lines
9.3 KiB
SQL
-- CreateSchema
|
|
CREATE SCHEMA IF NOT EXISTS "public";
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "UserRole" AS ENUM ('OWNER', 'CE_MANAGER', 'CE_MEMBER', 'TOURIST', 'ADMIN');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "CarbetStatus" AS ENUM ('DRAFT', 'PUBLISHED', 'ARCHIVED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "MediaType" AS ENUM ('PHOTO', 'VIDEO');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "AvailabilityScope" AS ENUM ('PUBLIC', 'CE_ONLY');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "AvailabilityBlockReason" AS ENUM ('NONE', 'CE_BLOCKED', 'WEEKEND_BLOCKED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "BookingStatus" AS ENUM ('PENDING', 'CONFIRMED', 'CANCELLED', 'COMPLETED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "PaymentStatus" AS ENUM ('PENDING', 'AUTHORIZED', 'SUCCEEDED', 'FAILED', 'REFUNDED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SubscriptionStatus" AS ENUM ('TRIAL', 'ACTIVE', 'PAST_DUE', 'CANCELED');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Organization" (
|
|
"id" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Organization_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "User" (
|
|
"id" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"passwordHash" TEXT NOT NULL,
|
|
"firstName" TEXT NOT NULL,
|
|
"lastName" TEXT NOT NULL,
|
|
"phone" TEXT,
|
|
"role" "UserRole" NOT NULL,
|
|
"organizationId" TEXT,
|
|
"avatarUrl" TEXT,
|
|
"isActive" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "User_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Carbet" (
|
|
"id" TEXT NOT NULL,
|
|
"ownerId" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"river" TEXT NOT NULL,
|
|
"latitude" DECIMAL(9,6) NOT NULL,
|
|
"longitude" DECIMAL(9,6) NOT NULL,
|
|
"embarkPoint" TEXT NOT NULL,
|
|
"pirogueDurationMin" INTEGER NOT NULL,
|
|
"capacity" INTEGER NOT NULL,
|
|
"status" "CarbetStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Carbet_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Amenity" (
|
|
"id" TEXT NOT NULL,
|
|
"key" TEXT NOT NULL,
|
|
"label" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Amenity_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "CarbetAmenity" (
|
|
"carbetId" TEXT NOT NULL,
|
|
"amenityId" TEXT NOT NULL,
|
|
|
|
CONSTRAINT "CarbetAmenity_pkey" PRIMARY KEY ("carbetId","amenityId")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Media" (
|
|
"id" TEXT NOT NULL,
|
|
"carbetId" TEXT NOT NULL,
|
|
"type" "MediaType" NOT NULL,
|
|
"s3Key" TEXT NOT NULL,
|
|
"s3Url" TEXT NOT NULL,
|
|
"sortOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "Media_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Availability" (
|
|
"id" TEXT NOT NULL,
|
|
"carbetId" TEXT NOT NULL,
|
|
"startDate" TIMESTAMP(3) NOT NULL,
|
|
"endDate" TIMESTAMP(3) NOT NULL,
|
|
"scope" "AvailabilityScope" NOT NULL DEFAULT 'PUBLIC',
|
|
"blockReason" "AvailabilityBlockReason" NOT NULL DEFAULT 'NONE',
|
|
"isAvailable" BOOLEAN NOT NULL DEFAULT true,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Availability_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Booking" (
|
|
"id" TEXT NOT NULL,
|
|
"carbetId" TEXT NOT NULL,
|
|
"tenantId" TEXT NOT NULL,
|
|
"startDate" TIMESTAMP(3) NOT NULL,
|
|
"endDate" TIMESTAMP(3) NOT NULL,
|
|
"guestCount" INTEGER NOT NULL,
|
|
"status" "BookingStatus" NOT NULL DEFAULT 'PENDING',
|
|
"amount" DECIMAL(10,2) NOT NULL,
|
|
"currency" TEXT NOT NULL DEFAULT 'EUR',
|
|
"paymentStatus" "PaymentStatus" NOT NULL DEFAULT 'PENDING',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Booking_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Subscription" (
|
|
"id" TEXT NOT NULL,
|
|
"ownerId" TEXT NOT NULL,
|
|
"carbetId" TEXT NOT NULL,
|
|
"provider" TEXT NOT NULL,
|
|
"providerSubId" TEXT,
|
|
"status" "SubscriptionStatus" NOT NULL DEFAULT 'TRIAL',
|
|
"startedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"renewedAt" TIMESTAMP(3),
|
|
"canceledAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Subscription_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Review" (
|
|
"id" TEXT NOT NULL,
|
|
"bookingId" TEXT NOT NULL,
|
|
"carbetId" TEXT NOT NULL,
|
|
"authorId" TEXT NOT NULL,
|
|
"rating" INTEGER NOT NULL,
|
|
"comment" TEXT,
|
|
"hostResponse" TEXT,
|
|
"hostRespondedAt" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
|
|
CONSTRAINT "Review_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Organization_slug_key" ON "Organization"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Organization_name_idx" ON "Organization"("name");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_organizationId_idx" ON "User"("organizationId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "User_role_idx" ON "User"("role");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Carbet_slug_key" ON "Carbet"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Carbet_ownerId_idx" ON "Carbet"("ownerId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Carbet_status_idx" ON "Carbet"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Carbet_river_idx" ON "Carbet"("river");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Amenity_key_key" ON "Amenity"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "CarbetAmenity_amenityId_idx" ON "CarbetAmenity"("amenityId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Media_carbetId_sortOrder_idx" ON "Media"("carbetId", "sortOrder");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Availability_carbetId_idx" ON "Availability"("carbetId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Availability_scope_blockReason_idx" ON "Availability"("scope", "blockReason");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Availability_startDate_endDate_idx" ON "Availability"("startDate", "endDate");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Booking_carbetId_idx" ON "Booking"("carbetId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Booking_tenantId_idx" ON "Booking"("tenantId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Booking_status_paymentStatus_idx" ON "Booking"("status", "paymentStatus");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Booking_startDate_endDate_idx" ON "Booking"("startDate", "endDate");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Subscription_providerSubId_key" ON "Subscription"("providerSubId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Subscription_ownerId_idx" ON "Subscription"("ownerId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Subscription_carbetId_idx" ON "Subscription"("carbetId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Subscription_status_idx" ON "Subscription"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "Review_bookingId_key" ON "Review"("bookingId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Review_carbetId_idx" ON "Review"("carbetId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Review_authorId_idx" ON "Review"("authorId");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "User" ADD CONSTRAINT "User_organizationId_fkey" FOREIGN KEY ("organizationId") REFERENCES "Organization"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Carbet" ADD CONSTRAINT "Carbet_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CarbetAmenity" ADD CONSTRAINT "CarbetAmenity_carbetId_fkey" FOREIGN KEY ("carbetId") REFERENCES "Carbet"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "CarbetAmenity" ADD CONSTRAINT "CarbetAmenity_amenityId_fkey" FOREIGN KEY ("amenityId") REFERENCES "Amenity"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Media" ADD CONSTRAINT "Media_carbetId_fkey" FOREIGN KEY ("carbetId") REFERENCES "Carbet"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Availability" ADD CONSTRAINT "Availability_carbetId_fkey" FOREIGN KEY ("carbetId") REFERENCES "Carbet"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_carbetId_fkey" FOREIGN KEY ("carbetId") REFERENCES "Carbet"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_tenantId_fkey" FOREIGN KEY ("tenantId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Subscription" ADD CONSTRAINT "Subscription_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Subscription" ADD CONSTRAINT "Subscription_carbetId_fkey" FOREIGN KEY ("carbetId") REFERENCES "Carbet"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Review" ADD CONSTRAINT "Review_bookingId_fkey" FOREIGN KEY ("bookingId") REFERENCES "Booking"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Review" ADD CONSTRAINT "Review_carbetId_fkey" FOREIGN KEY ("carbetId") REFERENCES "Carbet"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "Review" ADD CONSTRAINT "Review_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|