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";
  `);
}