import { MigrateUpArgs, MigrateDownArgs, sql } from '@payloadcms/db-postgres'

export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
  await db.execute(sql`
   CREATE TABLE "courses_free_lessons" (
  	"_order" integer NOT NULL,
  	"_parent_id" integer NOT NULL,
  	"id" varchar PRIMARY KEY NOT NULL,
  	"code" varchar,
  	"video_url" varchar NOT NULL,
  	"poster_id" integer
  );
  
  CREATE TABLE "courses_free_lessons_locales" (
  	"title" varchar NOT NULL,
  	"id" serial PRIMARY KEY NOT NULL,
  	"_locale" "_locales" NOT NULL,
  	"_parent_id" varchar NOT NULL
  );
  
  ALTER TABLE "catalogue_locales" ADD COLUMN "labels_free_lessons" varchar;
  ALTER TABLE "catalogue_locales" ADD COLUMN "labels_free_lessons_note" varchar;
  ALTER TABLE "catalogue_locales" ADD COLUMN "labels_watch" varchar;
  ALTER TABLE "courses_free_lessons" ADD CONSTRAINT "courses_free_lessons_poster_id_media_id_fk" FOREIGN KEY ("poster_id") REFERENCES "public"."media"("id") ON DELETE set null ON UPDATE no action;
  ALTER TABLE "courses_free_lessons" ADD CONSTRAINT "courses_free_lessons_parent_id_fk" FOREIGN KEY ("_parent_id") REFERENCES "public"."courses"("id") ON DELETE cascade ON UPDATE no action;
  ALTER TABLE "courses_free_lessons_locales" ADD CONSTRAINT "courses_free_lessons_locales_parent_id_fk" FOREIGN KEY ("_parent_id") REFERENCES "public"."courses_free_lessons"("id") ON DELETE cascade ON UPDATE no action;
  CREATE INDEX "courses_free_lessons_order_idx" ON "courses_free_lessons" USING btree ("_order");
  CREATE INDEX "courses_free_lessons_parent_id_idx" ON "courses_free_lessons" USING btree ("_parent_id");
  CREATE INDEX "courses_free_lessons_poster_idx" ON "courses_free_lessons" USING btree ("poster_id");
  CREATE UNIQUE INDEX "courses_free_lessons_locales_locale_parent_id_unique" ON "courses_free_lessons_locales" USING btree ("_locale","_parent_id");`)
}

export async function down({ db, payload, req }: MigrateDownArgs): Promise<void> {
  await db.execute(sql`
   DROP TABLE "courses_free_lessons" CASCADE;
  DROP TABLE "courses_free_lessons_locales" CASCADE;
  ALTER TABLE "catalogue_locales" DROP COLUMN "labels_free_lessons";
  ALTER TABLE "catalogue_locales" DROP COLUMN "labels_free_lessons_note";
  ALTER TABLE "catalogue_locales" DROP COLUMN "labels_watch";`)
}