146 lines
10 KiB
TypeScript
146 lines
10 KiB
TypeScript
import { MigrateDownArgs, MigrateUpArgs, sql } from "@payloadcms/db-postgres";
|
|
|
|
/** Schema for CMS conversations, inbound replies and configurable group offers. */
|
|
export async function up({ db }: MigrateUpArgs): Promise<void> {
|
|
await db.execute(sql`
|
|
CREATE TYPE "public"."enum_offers_booking_mode" AS ENUM('individual', 'group');
|
|
CREATE TYPE "public"."enum_offers_group_registration_mode" AS ENUM('fixed', 'capacity', 'request');
|
|
CREATE TYPE "public"."enum_offers_group_schedule_recurrence" AS ENUM('weekly', 'monthly');
|
|
CREATE TYPE "public"."enum_offers_group_schedule_weekday" AS ENUM('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday');
|
|
CREATE TYPE "public"."enum_conversations_type" AS ENUM('contact', 'appointment', 'group');
|
|
CREATE TYPE "public"."enum_conversations_status" AS ENUM('open', 'waiting', 'closed');
|
|
CREATE TYPE "public"."enum_conversation_messages_sender_type" AS ENUM('visitor', 'admin');
|
|
CREATE TYPE "public"."enum_group_registrations_status" AS ENUM('pending', 'accepted', 'waitlist', 'rejected', 'cancelled');
|
|
|
|
ALTER TABLE "offers" ADD COLUMN "booking_mode" "enum_offers_booking_mode" DEFAULT 'individual' NOT NULL;
|
|
ALTER TABLE "offers" ADD COLUMN "group_registration_mode" "enum_offers_group_registration_mode" DEFAULT 'fixed';
|
|
ALTER TABLE "offers" ADD COLUMN "group_schedule_recurrence" "enum_offers_group_schedule_recurrence";
|
|
ALTER TABLE "offers" ADD COLUMN "group_schedule_weekday" "enum_offers_group_schedule_weekday";
|
|
ALTER TABLE "offers" ADD COLUMN "group_schedule_month_day" numeric;
|
|
ALTER TABLE "offers" ADD COLUMN "group_schedule_start_time" varchar;
|
|
ALTER TABLE "offers" ADD COLUMN "group_schedule_end_time" varchar;
|
|
ALTER TABLE "offers" ADD COLUMN "group_capacity" numeric;
|
|
ALTER TABLE "offers" ADD COLUMN "registration_deadline" timestamp(3) with time zone;
|
|
ALTER TABLE "offers" ADD COLUMN "group_info" varchar;
|
|
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_booking_mode" "enum_offers_booking_mode" DEFAULT 'individual';
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_group_registration_mode" "enum_offers_group_registration_mode" DEFAULT 'fixed';
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_group_schedule_recurrence" "enum_offers_group_schedule_recurrence";
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_group_schedule_weekday" "enum_offers_group_schedule_weekday";
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_group_schedule_month_day" numeric;
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_group_schedule_start_time" varchar;
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_group_schedule_end_time" varchar;
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_group_capacity" numeric;
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_registration_deadline" timestamp(3) with time zone;
|
|
ALTER TABLE "_offers_v" ADD COLUMN "version_group_info" varchar;
|
|
|
|
CREATE TABLE "conversations" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"subject" varchar NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"email" varchar NOT NULL,
|
|
"type" "enum_conversations_type" DEFAULT 'contact' NOT NULL,
|
|
"status" "enum_conversations_status" DEFAULT 'open' NOT NULL,
|
|
"customer_id" integer,
|
|
"thread_key" varchar NOT NULL,
|
|
"last_message_at" timestamp(3) with time zone,
|
|
"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
|
|
"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL
|
|
);
|
|
CREATE TABLE "conversation_messages" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"conversation_id" integer NOT NULL,
|
|
"sender_type" "enum_conversation_messages_sender_type" DEFAULT 'admin' NOT NULL,
|
|
"sender_name" varchar,
|
|
"sender_email" varchar,
|
|
"body" varchar NOT NULL,
|
|
"external_message_id" varchar,
|
|
"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
|
|
"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL
|
|
);
|
|
CREATE TABLE "group_registrations" (
|
|
"id" serial PRIMARY KEY NOT NULL,
|
|
"offer_id" integer NOT NULL,
|
|
"name" varchar NOT NULL,
|
|
"email" varchar NOT NULL,
|
|
"phone" varchar,
|
|
"message" varchar,
|
|
"status" "enum_group_registrations_status" DEFAULT 'pending' NOT NULL,
|
|
"registered_at" timestamp(3) with time zone DEFAULT now(),
|
|
"updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
|
|
"created_at" timestamp(3) with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "payload_locked_documents_rels" ADD COLUMN "conversations_id" integer;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD COLUMN "conversation_messages_id" integer;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD COLUMN "group_registrations_id" integer;
|
|
ALTER TABLE "conversations" ADD CONSTRAINT "conversations_customer_id_customers_id_fk" FOREIGN KEY ("customer_id") REFERENCES "public"."customers"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "conversation_messages" ADD CONSTRAINT "conversation_messages_conversation_id_conversations_id_fk" FOREIGN KEY ("conversation_id") REFERENCES "public"."conversations"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "group_registrations" ADD CONSTRAINT "group_registrations_offer_id_offers_id_fk" FOREIGN KEY ("offer_id") REFERENCES "public"."offers"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_conversations_fk" FOREIGN KEY ("conversations_id") REFERENCES "public"."conversations"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_conversation_messages_fk" FOREIGN KEY ("conversation_messages_id") REFERENCES "public"."conversation_messages"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "payload_locked_documents_rels" ADD CONSTRAINT "payload_locked_documents_rels_group_registrations_fk" FOREIGN KEY ("group_registrations_id") REFERENCES "public"."group_registrations"("id") ON DELETE cascade ON UPDATE no action;
|
|
|
|
CREATE UNIQUE INDEX "conversations_thread_key_idx" ON "conversations" USING btree ("thread_key");
|
|
CREATE INDEX "conversations_customer_idx" ON "conversations" USING btree ("customer_id");
|
|
CREATE INDEX "conversations_last_message_at_idx" ON "conversations" USING btree ("last_message_at");
|
|
CREATE INDEX "conversations_updated_at_idx" ON "conversations" USING btree ("updated_at");
|
|
CREATE INDEX "conversations_created_at_idx" ON "conversations" USING btree ("created_at");
|
|
CREATE INDEX "conversation_messages_conversation_idx" ON "conversation_messages" USING btree ("conversation_id");
|
|
CREATE UNIQUE INDEX "conversation_messages_external_message_id_idx" ON "conversation_messages" USING btree ("external_message_id");
|
|
CREATE INDEX "conversation_messages_updated_at_idx" ON "conversation_messages" USING btree ("updated_at");
|
|
CREATE INDEX "conversation_messages_created_at_idx" ON "conversation_messages" USING btree ("created_at");
|
|
CREATE INDEX "group_registrations_offer_idx" ON "group_registrations" USING btree ("offer_id");
|
|
CREATE INDEX "group_registrations_updated_at_idx" ON "group_registrations" USING btree ("updated_at");
|
|
CREATE INDEX "group_registrations_created_at_idx" ON "group_registrations" USING btree ("created_at");
|
|
CREATE INDEX "payload_locked_documents_rels_conversations_id_idx" ON "payload_locked_documents_rels" USING btree ("conversations_id");
|
|
CREATE INDEX "payload_locked_documents_rels_conversation_messages_id_idx" ON "payload_locked_documents_rels" USING btree ("conversation_messages_id");
|
|
CREATE INDEX "payload_locked_documents_rels_group_registrations_id_idx" ON "payload_locked_documents_rels" USING btree ("group_registrations_id");
|
|
`);
|
|
}
|
|
|
|
export async function down({ db }: MigrateDownArgs): Promise<void> {
|
|
await db.execute(sql`
|
|
ALTER TABLE "payload_locked_documents_rels" DROP CONSTRAINT "payload_locked_documents_rels_conversations_fk";
|
|
ALTER TABLE "payload_locked_documents_rels" DROP CONSTRAINT "payload_locked_documents_rels_conversation_messages_fk";
|
|
ALTER TABLE "payload_locked_documents_rels" DROP CONSTRAINT "payload_locked_documents_rels_group_registrations_fk";
|
|
ALTER TABLE "conversations" DROP CONSTRAINT "conversations_customer_id_customers_id_fk";
|
|
ALTER TABLE "conversation_messages" DROP CONSTRAINT "conversation_messages_conversation_id_conversations_id_fk";
|
|
ALTER TABLE "group_registrations" DROP CONSTRAINT "group_registrations_offer_id_offers_id_fk";
|
|
DROP TABLE "conversation_messages" CASCADE;
|
|
DROP TABLE "conversations" CASCADE;
|
|
DROP TABLE "group_registrations" CASCADE;
|
|
ALTER TABLE "payload_locked_documents_rels" DROP COLUMN "conversations_id";
|
|
ALTER TABLE "payload_locked_documents_rels" DROP COLUMN "conversation_messages_id";
|
|
ALTER TABLE "payload_locked_documents_rels" DROP COLUMN "group_registrations_id";
|
|
ALTER TABLE "offers" DROP COLUMN "booking_mode";
|
|
ALTER TABLE "offers" DROP COLUMN "group_registration_mode";
|
|
ALTER TABLE "offers" DROP COLUMN "group_schedule_recurrence";
|
|
ALTER TABLE "offers" DROP COLUMN "group_schedule_weekday";
|
|
ALTER TABLE "offers" DROP COLUMN "group_schedule_month_day";
|
|
ALTER TABLE "offers" DROP COLUMN "group_schedule_start_time";
|
|
ALTER TABLE "offers" DROP COLUMN "group_schedule_end_time";
|
|
ALTER TABLE "offers" DROP COLUMN "group_capacity";
|
|
ALTER TABLE "offers" DROP COLUMN "registration_deadline";
|
|
ALTER TABLE "offers" DROP COLUMN "group_info";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_booking_mode";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_group_registration_mode";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_group_schedule_recurrence";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_group_schedule_weekday";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_group_schedule_month_day";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_group_schedule_start_time";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_group_schedule_end_time";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_group_capacity";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_registration_deadline";
|
|
ALTER TABLE "_offers_v" DROP COLUMN "version_group_info";
|
|
DROP TYPE "public"."enum_offers_booking_mode";
|
|
DROP TYPE "public"."enum_offers_group_registration_mode";
|
|
DROP TYPE "public"."enum_offers_group_schedule_recurrence";
|
|
DROP TYPE "public"."enum_offers_group_schedule_weekday";
|
|
DROP TYPE "public"."enum_conversations_type";
|
|
DROP TYPE "public"."enum_conversations_status";
|
|
DROP TYPE "public"."enum_conversation_messages_sender_type";
|
|
DROP TYPE "public"."enum_group_registrations_status";
|
|
`);
|
|
}
|