200 lines
7.8 KiB
SQL
200 lines
7.8 KiB
SQL
CREATE TYPE "BookingStatus" AS ENUM ('PENDING_PAYMENT', 'PAYMENT_RECEIVED', 'CONFIRMED', 'CANCELLED', 'FAILED');
|
|
CREATE TYPE "PaymentStatus" AS ENUM ('REQUIRES_PAYMENT', 'COMPLETED', 'FAILED', 'REFUNDED');
|
|
CREATE TYPE "AvailabilityBlockReason" AS ENUM ('MAINTENANCE', 'OWNER_BLOCKED', 'BASE_RULE', 'OTHER');
|
|
CREATE TYPE "ContentPageStatus" AS ENUM ('DRAFT', 'PUBLISHED', 'ARCHIVED');
|
|
|
|
CREATE TABLE "SiteSettings" (
|
|
"id" TEXT NOT NULL,
|
|
"businessName" TEXT NOT NULL,
|
|
"tagline" TEXT,
|
|
"contactEmail" TEXT,
|
|
"contactPhone" TEXT,
|
|
"defaultSeoTitle" TEXT,
|
|
"defaultSeoDescription" TEXT,
|
|
"bookingHoldMinutes" INTEGER NOT NULL DEFAULT 30,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "SiteSettings_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE TABLE "Property" (
|
|
"id" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"summary" TEXT NOT NULL,
|
|
"longDescription" TEXT NOT NULL,
|
|
"locationText" TEXT NOT NULL,
|
|
"sleeps" INTEGER NOT NULL,
|
|
"bedrooms" INTEGER NOT NULL,
|
|
"bathrooms" INTEGER NOT NULL,
|
|
"petsAllowed" BOOLEAN NOT NULL DEFAULT false,
|
|
"published" BOOLEAN NOT NULL DEFAULT false,
|
|
"featured" BOOLEAN NOT NULL DEFAULT false,
|
|
"minStayNights" INTEGER,
|
|
"checkInTime" TEXT,
|
|
"checkOutTime" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Property_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "Property_slug_key" ON "Property"("slug");
|
|
|
|
CREATE TABLE "PropertyImage" (
|
|
"id" TEXT NOT NULL,
|
|
"propertyId" TEXT NOT NULL,
|
|
"url" TEXT NOT NULL,
|
|
"altText" TEXT NOT NULL,
|
|
"displayOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"primaryImage" BOOLEAN NOT NULL DEFAULT false,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "PropertyImage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "PropertyImage_propertyId_displayOrder_idx" ON "PropertyImage"("propertyId", "displayOrder");
|
|
|
|
CREATE TABLE "Amenity" (
|
|
"id" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "Amenity_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "Amenity_slug_key" ON "Amenity"("slug");
|
|
|
|
CREATE TABLE "PropertyAmenity" (
|
|
"propertyId" TEXT NOT NULL,
|
|
"amenityId" TEXT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "PropertyAmenity_propertyId_amenityId_key" ON "PropertyAmenity"("propertyId", "amenityId");
|
|
CREATE INDEX "PropertyAmenity_amenityId_idx" ON "PropertyAmenity"("amenityId");
|
|
|
|
CREATE TABLE "PricingRule" (
|
|
"id" TEXT NOT NULL,
|
|
"propertyId" TEXT NOT NULL,
|
|
"label" TEXT,
|
|
"basePriceCents" INTEGER NOT NULL,
|
|
"weekendPriceCents" INTEGER,
|
|
"guestDeltaCents" INTEGER,
|
|
"validFrom" TIMESTAMP(3),
|
|
"validTo" TIMESTAMP(3),
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "PricingRule_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "PricingRule_propertyId_validFrom_validTo_idx" ON "PricingRule"("propertyId", "validFrom", "validTo");
|
|
|
|
CREATE TABLE "AvailabilityBlock" (
|
|
"id" TEXT NOT NULL,
|
|
"propertyId" TEXT NOT NULL,
|
|
"startDate" TIMESTAMP(3) NOT NULL,
|
|
"endDate" TIMESTAMP(3) NOT NULL,
|
|
"reason" "AvailabilityBlockReason" NOT NULL DEFAULT 'OTHER',
|
|
"notes" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "AvailabilityBlock_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "AvailabilityBlock_propertyId_startDate_endDate_idx" ON "AvailabilityBlock"("propertyId", "startDate", "endDate");
|
|
|
|
CREATE TABLE "Booking" (
|
|
"id" TEXT NOT NULL,
|
|
"propertyId" TEXT NOT NULL,
|
|
"firstName" TEXT NOT NULL,
|
|
"lastName" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"phone" TEXT,
|
|
"arrivalDate" TIMESTAMP(3) NOT NULL,
|
|
"departureDate" TIMESTAMP(3) NOT NULL,
|
|
"adults" INTEGER NOT NULL,
|
|
"children" INTEGER NOT NULL DEFAULT 0,
|
|
"pets" INTEGER NOT NULL DEFAULT 0,
|
|
"specialRequests" TEXT,
|
|
"termsAccepted" BOOLEAN NOT NULL DEFAULT false,
|
|
"holdExpiresAt" TIMESTAMP(3),
|
|
"totalCents" INTEGER NOT NULL,
|
|
"currency" TEXT NOT NULL DEFAULT 'GBP',
|
|
"status" "BookingStatus" NOT NULL DEFAULT 'PENDING_PAYMENT',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Booking_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "Booking_propertyId_arrivalDate_departureDate_idx" ON "Booking"("propertyId", "arrivalDate", "departureDate");
|
|
CREATE INDEX "Booking_status_idx" ON "Booking"("status");
|
|
|
|
CREATE TABLE "Payment" (
|
|
"id" TEXT NOT NULL,
|
|
"bookingId" TEXT NOT NULL,
|
|
"stripeCheckoutSessionId" TEXT,
|
|
"stripePaymentIntentId" TEXT,
|
|
"stripeEventId" TEXT,
|
|
"amountCents" INTEGER NOT NULL,
|
|
"currency" TEXT NOT NULL DEFAULT 'GBP',
|
|
"status" "PaymentStatus" NOT NULL DEFAULT 'REQUIRES_PAYMENT',
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "Payment_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "Payment_bookingId_key" ON "Payment"("bookingId");
|
|
CREATE UNIQUE INDEX "Payment_stripeCheckoutSessionId_key" ON "Payment"("stripeCheckoutSessionId");
|
|
CREATE UNIQUE INDEX "Payment_stripePaymentIntentId_key" ON "Payment"("stripePaymentIntentId");
|
|
CREATE UNIQUE INDEX "Payment_stripeEventId_key" ON "Payment"("stripeEventId");
|
|
|
|
CREATE TABLE "Enquiry" (
|
|
"id" TEXT NOT NULL,
|
|
"propertyId" TEXT,
|
|
"name" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"phone" TEXT,
|
|
"message" TEXT NOT NULL,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "Enquiry_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "Enquiry_propertyId_createdAt_idx" ON "Enquiry"("propertyId", "createdAt");
|
|
|
|
CREATE TABLE "Testimonial" (
|
|
"id" TEXT NOT NULL,
|
|
"propertyId" TEXT,
|
|
"authorName" TEXT NOT NULL,
|
|
"content" TEXT NOT NULL,
|
|
"rating" INTEGER,
|
|
"published" BOOLEAN NOT NULL DEFAULT false,
|
|
"displayOrder" INTEGER NOT NULL DEFAULT 0,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT "Testimonial_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE INDEX "Testimonial_published_displayOrder_idx" ON "Testimonial"("published", "displayOrder");
|
|
|
|
CREATE TABLE "ContentPage" (
|
|
"id" TEXT NOT NULL,
|
|
"slug" TEXT NOT NULL,
|
|
"title" TEXT NOT NULL,
|
|
"body" TEXT NOT NULL,
|
|
"status" "ContentPageStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"seoTitle" TEXT,
|
|
"seoDescription" TEXT,
|
|
"createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" TIMESTAMP(3) NOT NULL,
|
|
CONSTRAINT "ContentPage_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
CREATE UNIQUE INDEX "ContentPage_slug_key" ON "ContentPage"("slug");
|
|
|
|
ALTER TABLE "PropertyImage" ADD CONSTRAINT "PropertyImage_propertyId_fkey" FOREIGN KEY ("propertyId") REFERENCES "Property"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "PropertyAmenity" ADD CONSTRAINT "PropertyAmenity_propertyId_fkey" FOREIGN KEY ("propertyId") REFERENCES "Property"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "PropertyAmenity" ADD CONSTRAINT "PropertyAmenity_amenityId_fkey" FOREIGN KEY ("amenityId") REFERENCES "Amenity"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "PricingRule" ADD CONSTRAINT "PricingRule_propertyId_fkey" FOREIGN KEY ("propertyId") REFERENCES "Property"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "AvailabilityBlock" ADD CONSTRAINT "AvailabilityBlock_propertyId_fkey" FOREIGN KEY ("propertyId") REFERENCES "Property"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "Booking" ADD CONSTRAINT "Booking_propertyId_fkey" FOREIGN KEY ("propertyId") REFERENCES "Property"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
ALTER TABLE "Payment" ADD CONSTRAINT "Payment_bookingId_fkey" FOREIGN KEY ("bookingId") REFERENCES "Booking"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
ALTER TABLE "Enquiry" ADD CONSTRAINT "Enquiry_propertyId_fkey" FOREIGN KEY ("propertyId") REFERENCES "Property"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
ALTER TABLE "Testimonial" ADD CONSTRAINT "Testimonial_propertyId_fkey" FOREIGN KEY ("propertyId") REFERENCES "Property"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|