-- Defensive cleanup for stage/prod (and any DB where the stale unique index
-- survived under a truncated/renamed Postgres identifier).
--
-- History:
--   20260707130000 created unique (labCaseId, treatmentDetailId, prosthesisTypeCode, stepOrder)
--     → PG truncates the index name to 63 chars (...prosthesisTypeCode_s)
--   20260710150528 renamed that truncated name to ...prosthesisTypeCo_key
--   20260716120000 dropped the *untruncated* name (no-op) and added the correct
--     unique that includes selectionGroupId — leaving the stale unique in place
--   20260717123000 drops known stale names; this migration also drops by definition
--     so any leftover old unique is removed regardless of identifier length/rename.

DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT indexname
    FROM pg_indexes
    WHERE schemaname = 'public'
      AND tablename = 'lab_case_tasks'
      AND indexdef ILIKE '%UNIQUE%'
      AND indexdef ILIKE '%prosthesisTypeCode%'
      AND indexdef ILIKE '%stepOrder%'
      AND indexdef ILIKE '%treatmentDetailId%'
      AND indexdef NOT ILIKE '%selectionGroupId%'
  LOOP
    EXECUTE format('DROP INDEX IF EXISTS %I', r.indexname);
  END LOOP;
END $$;

-- Correct unique (includes selectionGroupId). Idempotent if already present.
CREATE UNIQUE INDEX IF NOT EXISTS "lab_case_tasks_labCaseId_treatmentDetailId_selectionGroupId_prosthesisTypeCode_stepOrder_key"
  ON "lab_case_tasks"("labCaseId", "treatmentDetailId", "selectionGroupId", "prosthesisTypeCode", "stepOrder");
