Скрипт для удаления дубликатов

-- 1. Найти все дублирующиеся email, игнорируя регистр
CREATE TEMP TABLE Migrations AS
WITH DuplicateEmails AS (
    SELECT LOWER(email) AS email, MIN(id::text)::UUID AS main_id, ARRAY_AGG(id::UUID) AS duplicate_ids
    FROM "user"
    GROUP BY LOWER(email)
    HAVING COUNT(*) > 1
)
SELECT email, main_id, UNNEST(array_remove(duplicate_ids, main_id)) AS duplicate_id
FROM DuplicateEmails;

-- 2. Перенос всех связанных записей к основному аккаунту с удалением дубликатов
-- Перенос в таблице media
UPDATE media
SET user_id = m.main_id
FROM Migrations m
WHERE media.user_id = m.duplicate_id;

-- Перенос в таблице subscription
UPDATE subscription
SET user_id = m.main_id
FROM Migrations m
WHERE subscription.user_id = m.duplicate_id;

-- Перенос в таблице message
UPDATE message
SET user_id = m.main_id
FROM Migrations m
WHERE message.user_id = m.duplicate_id;

-- Перенос в таблице message_reaction
DELETE FROM message_reaction
USING message_reaction mr2, Migrations m
WHERE message_reaction.user_id = m.duplicate_id
AND mr2.user_id = m.main_id
AND message_reaction.reaction_id = mr2.reaction_id
AND message_reaction.message_id = mr2.message_id;

UPDATE message_reaction
SET user_id = m.main_id
FROM Migrations m
WHERE message_reaction.user_id = m.duplicate_id;

-- Перенос в таблице chat_member
DELETE FROM chat_member
USING chat_member cm2, Migrations m
WHERE chat_member.user_id = m.duplicate_id
AND cm2.user_id = m.main_id
AND chat_member.chat_id = cm2.chat_id;

UPDATE chat_member
SET user_id = m.main_id
FROM Migrations m
WHERE chat_member.user_id = m.duplicate_id;

-- Перенос в таблице user_chat_group
UPDATE user_chat_group
SET user_id = m.main_id
FROM Migrations m
WHERE user_chat_group.user_id = m.duplicate_id;

-- Перенос в таблице chat_message_read
DELETE FROM chat_message_read
USING chat_message_read cmr2, Migrations m
WHERE chat_message_read.user_id = m.duplicate_id
AND cmr2.user_id = m.main_id
AND chat_message_read.message_id = cmr2.message_id;

UPDATE chat_message_read
SET user_id = m.main_id
FROM Migrations m
WHERE chat_message_read.user_id = m.duplicate_id;

-- Перенос в таблице chat_message_report
UPDATE chat_message_report
SET user_id = m.main_id
FROM Migrations m
WHERE chat_message_report.user_id = m.duplicate_id;

-- Перенос в таблице community_member
DELETE FROM community_member
USING community_member cm2, Migrations m
WHERE community_member.user_id = m.duplicate_id
AND cm2.user_id = m.main_id
AND community_member.community_id = cm2.community_id;

UPDATE community_member
SET user_id = m.main_id
FROM Migrations m
WHERE community_member.user_id = m.duplicate_id;

-- Перенос в таблице community_request
DELETE FROM community_request
USING community_request cr2, Migrations m
WHERE community_request.user_id = m.duplicate_id
AND cr2.user_id = m.main_id
AND community_request.community_id = cr2.community_id;

UPDATE community_request
SET user_id = m.main_id
FROM Migrations m
WHERE community_request.user_id = m.duplicate_id;

-- Перенос в таблице community_invite
DELETE FROM community_invite
USING community_invite ci2, Migrations m
WHERE community_invite.user_id = m.duplicate_id
AND ci2.user_id = m.main_id
AND community_invite.community_id = ci2.community_id;

UPDATE community_invite
SET user_id = m.main_id
FROM Migrations m
WHERE community_invite.user_id = m.duplicate_id;

-- Перенос в таблице publication
UPDATE publication
SET author_id = m.main_id
FROM Migrations m
WHERE publication.author_id = m.duplicate_id;

-- Перенос в таблице publication_reaction
DELETE FROM publication_reaction
USING publication_reaction pr2, Migrations m
WHERE publication_reaction.user_id = m.duplicate_id
AND pr2.user_id = m.main_id
AND publication_reaction.reaction_id = pr2.reaction_id
AND publication_reaction.publication_id = pr2.publication_id;

UPDATE publication_reaction
SET user_id = m.main_id
FROM Migrations m
WHERE publication_reaction.user_id = m.duplicate_id;

-- Перенос в таблице user_order
UPDATE user_order
SET user_id = m.main_id
FROM Migrations m
WHERE user_order.user_id = m.duplicate_id;

-- Перенос в таблице purchase.
DELETE FROM purchase
USING purchase p2, Migrations m
WHERE purchase.user_id = m.duplicate_id
AND p2.user_id = m.main_id
AND purchase.product_id = p2.product_id;

UPDATE purchase
SET user_id = m.main_id
FROM Migrations m
WHERE purchase.user_id = m.duplicate_id;

-- Перенос в таблице user_favorite_poster
DELETE FROM user_favorite_poster
USING user_favorite_poster ufp2, Migrations m
WHERE user_favorite_poster.user_id = m.duplicate_id
AND ufp2.user_id = m.main_id
AND user_favorite_poster.poster_id = ufp2.poster_id;

UPDATE user_favorite_poster
SET user_id = m.main_id
FROM Migrations m
WHERE user_favorite_poster.user_id = m.duplicate_id;

-- Перенос в таблице topic
UPDATE topic
SET user_id = m.main_id
FROM Migrations m
WHERE topic.user_id = m.duplicate_id;

-- Перенос в таблице user_activity.
UPDATE user_activity
SET user_id = m.main_id
FROM Migrations m
WHERE user_activity.user_id = m.duplicate_id;

-- Перенос в таблице user_offer
UPDATE user_offer
SET user_id = m.main_id
FROM Migrations m
WHERE user_offer.user_id = m.duplicate_id;

-- Перенос в таблице trajectory. Как перенести траекторию?
UPDATE trajectory
SET user_id = m.main_id
FROM Migrations m
WHERE trajectory.user_id = m.duplicate_id;

-- Перенос в таблице trajectory_offer
UPDATE trajectory_offer
SET user_id = m.main_id
FROM Migrations m
WHERE trajectory_offer.user_id = m.duplicate_id;