-- Review L-3/L-12: enforce the uniqueness constraints the handlers rely on.
-- Both dedupe existing rows first so the migration succeeds on an installed
-- database that already contains duplicates.

-- One calendar share per (creator, recipient, date). Keep the newest row and
-- collapse the rest; re-sharing now updates the surviving row's note.
DELETE FROM "SharedDate" older
USING "SharedDate" newer
WHERE older."creatorId" = newer."creatorId"
  AND older."recipientId" = newer."recipientId"
  AND older.date = newer.date
  AND (older."createdAt", older.id) < (newer."createdAt", newer.id);

DROP INDEX IF EXISTS "SharedDate_creatorId_recipientId_date_idx";
CREATE UNIQUE INDEX "SharedDate_creatorId_recipientId_date_idx"
  ON "SharedDate"("creatorId","recipientId",date);

-- Legacy kanban status keys are unique per user. Keep the key on the
-- earliest column and clear it from duplicates; those columns remain normal
-- (explicitly placed) columns, only the ambiguous legacy mapping goes away.
UPDATE "KanbanColumn" dup
SET "key" = NULL
WHERE dup."key" IS NOT NULL
  AND EXISTS (
    SELECT 1 FROM "KanbanColumn" keep
    WHERE keep."userId" = dup."userId"
      AND keep."key" = dup."key"
      AND (keep.position, keep.id) < (dup.position, dup.id)
  );

CREATE UNIQUE INDEX IF NOT EXISTS "KanbanColumn_userId_key_idx"
  ON "KanbanColumn"("userId","key") WHERE "key" IS NOT NULL;