136 lines
4.2 KiB
PL/PgSQL
136 lines
4.2 KiB
PL/PgSQL
-- 007_remove_duplicate_recipes_by_name.sql
|
||
-- Removes duplicate recipes that share the same "Name" (trimmed, exact match).
|
||
-- Keeps the row with the lowest "Id" for each name.
|
||
--
|
||
-- Analysis of Recipes-2.csv export (196 rows):
|
||
-- 168 unique names, 28 duplicate groups, 28 rows to delete (Ids 113–140).
|
||
-- Those duplicate Ids mirror Ids 29–56 one-to-one.
|
||
--
|
||
-- Run inside a transaction. Review the preview queries before COMMIT.
|
||
|
||
BEGIN;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 1) Preview duplicates
|
||
-- ---------------------------------------------------------------------------
|
||
-- SELECT
|
||
-- r."Name",
|
||
-- array_agg(r."Id" ORDER BY r."Id") AS ids,
|
||
-- min(r."Id") AS keep_id,
|
||
-- array_agg(r."Id" ORDER BY r."Id") FILTER (WHERE r."Id" <> min(r."Id")) AS remove_ids
|
||
-- FROM diet."Recipes" r
|
||
-- GROUP BY r."Name"
|
||
-- HAVING count(*) > 1
|
||
-- ORDER BY min(r."Id");
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2) Build duplicate map: remove_id -> keep_id
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TEMP TABLE recipe_dedup_map ON COMMIT DROP AS
|
||
SELECT
|
||
r."Id" AS remove_id,
|
||
d.keep_id
|
||
FROM diet."Recipes" r
|
||
INNER JOIN (
|
||
SELECT btrim("Name") AS name_key, MIN("Id") AS keep_id
|
||
FROM diet."Recipes"
|
||
GROUP BY btrim("Name")
|
||
HAVING COUNT(*) > 1
|
||
) d ON btrim(r."Name") = d.name_key
|
||
WHERE r."Id" <> d.keep_id;
|
||
|
||
-- Optional: restrict to known duplicate ids from export
|
||
-- DELETE FROM recipe_dedup_map WHERE remove_id NOT BETWEEN 113 AND 140;
|
||
|
||
DO $$
|
||
DECLARE
|
||
dup_count int;
|
||
BEGIN
|
||
SELECT count(*) INTO dup_count FROM recipe_dedup_map;
|
||
RAISE NOTICE 'Duplicate recipe rows to remove: %', dup_count;
|
||
END $$;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 3) Re-point foreign keys to the canonical (lowest-id) recipe
|
||
-- ---------------------------------------------------------------------------
|
||
|
||
-- Meal planner / diet generator drafts
|
||
UPDATE diet."MealPlanEntries" e
|
||
SET "RecipeId" = m.keep_id,
|
||
"UpdatedAt" = now()
|
||
FROM recipe_dedup_map m
|
||
WHERE e."RecipeId" = m.remove_id;
|
||
|
||
UPDATE diet."DietGeneratorDraftMeals" d
|
||
SET "RecipeId" = m.keep_id
|
||
FROM recipe_dedup_map m
|
||
WHERE d."RecipeId" = m.remove_id;
|
||
|
||
-- AI recipe generator saved draft pointer
|
||
UPDATE diet."RecipeGeneratorSessions" s
|
||
SET "SavedRecipeId" = m.keep_id,
|
||
"UpdatedAt" = now()
|
||
FROM recipe_dedup_map m
|
||
WHERE s."SavedRecipeId" = m.remove_id;
|
||
|
||
-- Diet tracking (nullable FK)
|
||
UPDATE diet."MealConsumptions" c
|
||
SET "RecipeId" = m.keep_id
|
||
FROM recipe_dedup_map m
|
||
WHERE c."RecipeId" = m.remove_id;
|
||
|
||
-- Favorites: drop rows that would collide after merge, then re-point the rest
|
||
DELETE FROM diet."UserFavoriteRecipes" f
|
||
USING recipe_dedup_map m
|
||
WHERE f."RecipeId" = m.remove_id
|
||
AND EXISTS (
|
||
SELECT 1
|
||
FROM diet."UserFavoriteRecipes" f2
|
||
WHERE f2."UserId" = f."UserId"
|
||
AND f2."RecipeId" = m.keep_id
|
||
);
|
||
|
||
UPDATE diet."UserFavoriteRecipes" f
|
||
SET "RecipeId" = m.keep_id
|
||
FROM recipe_dedup_map m
|
||
WHERE f."RecipeId" = m.remove_id;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 4) Delete duplicate recipes (Ingredients + RecipeSteps cascade)
|
||
-- ---------------------------------------------------------------------------
|
||
DELETE FROM diet."Recipes" r
|
||
USING recipe_dedup_map m
|
||
WHERE r."Id" = m.remove_id;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 5) Verify
|
||
-- ---------------------------------------------------------------------------
|
||
DO $$
|
||
DECLARE
|
||
remaining_dup_names int;
|
||
recipe_count int;
|
||
BEGIN
|
||
SELECT count(*) INTO remaining_dup_names
|
||
FROM (
|
||
SELECT btrim("Name")
|
||
FROM diet."Recipes"
|
||
GROUP BY btrim("Name")
|
||
HAVING count(*) > 1
|
||
) d;
|
||
|
||
SELECT count(*) INTO recipe_count FROM diet."Recipes";
|
||
|
||
IF remaining_dup_names > 0 THEN
|
||
RAISE EXCEPTION 'Duplicate recipe names still present: % groups', remaining_dup_names;
|
||
END IF;
|
||
|
||
RAISE NOTICE 'Done. Active recipe count: %', recipe_count;
|
||
END $$;
|
||
|
||
COMMIT;
|
||
|
||
-- Optional hardening (uncomment if every recipe name must be unique going forward):
|
||
-- CREATE UNIQUE INDEX IF NOT EXISTS "UX_Recipes_Name"
|
||
-- ON diet."Recipes" (btrim("Name"))
|
||
-- WHERE "IsActive" AND NOT "IsDraft";
|