-- Migration: add overtimeMinutes to Performance and create PerformanceParticipation table
ALTER TABLE "Performance" ADD COLUMN IF NOT EXISTS "overtimeMinutes" INT NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS "PerformanceParticipation" (
  "id" TEXT PRIMARY KEY DEFAULT gen_random_uuid(),
  "performanceId" TEXT NOT NULL,
  "engagementWeekId" TEXT NOT NULL,
  "bandTrackId" TEXT NOT NULL,
  "attended" BOOLEAN NOT NULL DEFAULT true,
  "applyOnstage" BOOLEAN NOT NULL DEFAULT false,
  "applyCostume" BOOLEAN NOT NULL DEFAULT false,
  "applyMemorizeA" BOOLEAN NOT NULL DEFAULT false,
  "applyMemorizeB" BOOLEAN NOT NULL DEFAULT false,
  "applyChoreography" BOOLEAN NOT NULL DEFAULT false,
  "auditorMusicianId" TEXT NULL,
  CONSTRAINT "PerformanceParticipation_performanceId_fkey" FOREIGN KEY ("performanceId") REFERENCES "Performance" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "PerformanceParticipation_engagementWeekId_fkey" FOREIGN KEY ("engagementWeekId") REFERENCES "EngagementWeek" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "PerformanceParticipation_bandTrackId_fkey" FOREIGN KEY ("bandTrackId") REFERENCES "BandTrack" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "PerformanceParticipation_auditorMusicianId_fkey" FOREIGN KEY ("auditorMusicianId") REFERENCES "Musician" ("id") ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS "PerformanceParticipation_performanceId_bandTrackId_key" ON "PerformanceParticipation" ("performanceId", "bandTrackId");
CREATE INDEX IF NOT EXISTS "PerformanceParticipation_engagementWeekId_idx" ON "PerformanceParticipation" ("engagementWeekId");