-- Deduplicate conversations per job before adding unique constraint.
-- Prefer the thread with the most messages, then the oldest createdAt.

DELETE c_dup FROM conversations c_dup
INNER JOIN conversations c_keep ON c_dup.jobId = c_keep.jobId AND c_dup.id > c_keep.id
WHERE c_dup.jobId IS NOT NULL
  AND (
    (SELECT COUNT(*) FROM messages m WHERE m.conversationId = c_dup.id)
    < (SELECT COUNT(*) FROM messages m WHERE m.conversationId = c_keep.id)
    OR (
      (SELECT COUNT(*) FROM messages m WHERE m.conversationId = c_dup.id)
      = (SELECT COUNT(*) FROM messages m WHERE m.conversationId = c_keep.id)
    )
  );

-- Move any remaining messages from duplicate rows (if deletion order left orphans)
UPDATE messages m
INNER JOIN conversations c_dup ON m.conversationId = c_dup.id
INNER JOIN (
  SELECT jobId, MIN(id) AS keep_id
  FROM conversations
  WHERE jobId IS NOT NULL
  GROUP BY jobId
) keepers ON c_dup.jobId = keepers.jobId AND c_dup.id <> keepers.keep_id
SET m.conversationId = keepers.keep_id;

DELETE c_dup FROM conversations c_dup
INNER JOIN (
  SELECT jobId, MIN(id) AS keep_id
  FROM conversations
  WHERE jobId IS NOT NULL
  GROUP BY jobId
) keepers ON c_dup.jobId = keepers.jobId AND c_dup.id <> keepers.keep_id;

-- CreateIndex
CREATE UNIQUE INDEX `conversations_jobId_key` ON `conversations`(`jobId`);