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

/**
 * Migration zur Behebung der Schema-Divergenz zwischen Code und Datenbank
 *
 * Hauptänderungen:
 * - media_tags → media_texts umbenennen (Payload 3.x Konvention)
 * - Spaltenstruktur anpassen (path-Feld hinzufügen)
 */
export async function up({ db, payload, req }: MigrateUpArgs): Promise<void> {
  await db.execute(sql`
    -- Umbenennen von media_tags zu media_texts
    ALTER TABLE IF EXISTS "media_tags" RENAME TO "media_texts";

    -- Spalte 'path' hinzufügen falls nicht vorhanden (Payload 3.x benötigt diese)
    ALTER TABLE "media_texts" ADD COLUMN IF NOT EXISTS "path" varchar NOT NULL DEFAULT 'tags';

    -- Indices umbenennen
    ALTER INDEX IF EXISTS "media_tags_order_idx" RENAME TO "media_texts_order_idx";
    ALTER INDEX IF EXISTS "media_tags_parent_idx" RENAME TO "media_texts_parent_idx";

    -- Constraint umbenennen
    ALTER TABLE "media_texts" DROP CONSTRAINT IF EXISTS "media_tags_parent_id_fk";
    ALTER TABLE "media_texts" ADD CONSTRAINT "media_texts_parent_id_fk"
      FOREIGN KEY ("parent_id") REFERENCES "public"."media"("id") ON DELETE cascade ON UPDATE no action;

    -- Path Index hinzufügen
    CREATE INDEX IF NOT EXISTS "media_texts_path_idx" ON "media_texts" USING btree ("path");
  `)
}

export async function down({ db, payload, req }: MigrateDownArgs): Promise<void> {
  await db.execute(sql`
    -- Zurück umbenennen
    ALTER TABLE IF EXISTS "media_texts" RENAME TO "media_tags";

    -- Spalte entfernen
    ALTER TABLE "media_tags" DROP COLUMN IF EXISTS "path";

    -- Indices zurück umbenennen
    ALTER INDEX IF EXISTS "media_texts_order_idx" RENAME TO "media_tags_order_idx";
    ALTER INDEX IF EXISTS "media_texts_parent_idx" RENAME TO "media_tags_parent_idx";
    DROP INDEX IF EXISTS "media_texts_path_idx";

    -- Constraint zurück
    ALTER TABLE "media_tags" DROP CONSTRAINT IF EXISTS "media_texts_parent_id_fk";
    ALTER TABLE "media_tags" ADD CONSTRAINT "media_tags_parent_id_fk"
      FOREIGN KEY ("parent_id") REFERENCES "public"."media"("id") ON DELETE cascade ON UPDATE no action;
  `)
}
