-- Drop the legacy allowDoubling flag now that band tracks use doubling tiers
ALTER TABLE "BandTrack" DROP COLUMN IF EXISTS "allowDoubling";

-- Add the new doubling tier discriminator with a sensible default
ALTER TABLE "BandTrack"
  ADD COLUMN IF NOT EXISTS "doublingTier" TEXT NOT NULL DEFAULT 'note';

-- Add nullable default musician link used for roster shortcuts
ALTER TABLE "BandTrack"
  ADD COLUMN IF NOT EXISTS "defaultMusicianId" TEXT;

-- Ensure existing rows have a doubling tier set before enforcing NOT NULL
UPDATE "BandTrack" SET "doublingTier" = COALESCE("doublingTier", 'note');

-- Backfill defaultMusicianId to NULL if not set to satisfy future constraints
UPDATE "BandTrack" SET "defaultMusicianId" = NULL WHERE "defaultMusicianId" IS NULL;

-- Wire up the foreign key (idempotent: drop first if present)
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.table_constraints
    WHERE constraint_name = 'BandTrack_defaultMusicianId_fkey'
      AND table_name = 'BandTrack'
  ) THEN
    ALTER TABLE "BandTrack" DROP CONSTRAINT "BandTrack_defaultMusicianId_fkey";
  END IF;
END $$;

ALTER TABLE "BandTrack"
  ADD CONSTRAINT "BandTrack_defaultMusicianId_fkey"
  FOREIGN KEY ("defaultMusicianId") REFERENCES "Musician"("id")
  ON DELETE SET NULL
  ON UPDATE CASCADE;
