--DuplicateCouples-CLEAN_DELETE.sql
/* 2026-08-16 by Gemini Flash AI directed by Tom Holden ve3meo
Complete Execution Order
With child re-linking, event re-linking, and cleanup combined, the complete
 transaction sequence is:
*/
BEGIN TRANSACTION;

-- 1. Re-link children attached to duplicate families to the primary FamilyID
UPDATE ChildTable
SET FamilyID = (
    SELECT MIN(f2.FamilyID)
    FROM FamilyTable f2
    JOIN FamilyTable f1 
      ON f1.FatherID = f2.FatherID 
     AND f1.MotherID = f2.MotherID
    WHERE f1.FamilyID = ChildTable.FamilyID
      AND (f1.FatherID > 0 OR f1.MotherID > 0)
)
WHERE FamilyID IN (
    SELECT f1.FamilyID
    FROM FamilyTable f1
    JOIN FamilyTable f2 
      ON f1.FatherID = f2.FatherID 
     AND f1.MotherID = f2.MotherID
     AND f1.FamilyID > f2.FamilyID
    WHERE f1.FatherID > 0 OR f1.MotherID > 0
);

-- Deduplicate identical child links pointing to the same primary FamilyID
DELETE FROM ChildTable
WHERE rowid NOT IN (
    SELECT MIN(rowid)
    FROM ChildTable
    GROUP BY FamilyID, ChildID
);

-- 2. Re-link unique family events from duplicate families to the primary FamilyID
UPDATE EventTable
SET OwnerID = (
    SELECT MIN(f2.FamilyID)
    FROM FamilyTable f2
    JOIN FamilyTable f1 
      ON f1.FatherID = f2.FatherID 
     AND f1.MotherID = f2.MotherID
    WHERE f1.FamilyID = EventTable.OwnerID
      AND (f1.FatherID > 0 OR f1.MotherID > 0)
)
WHERE OwnerType = 1
  AND OwnerID IN (
      SELECT f1.FamilyID
      FROM FamilyTable f1
      JOIN FamilyTable f2 
        ON f1.FatherID = f2.FatherID 
       AND f1.MotherID = f2.MotherID
       AND f1.FamilyID > f2.FamilyID
      WHERE f1.FatherID > 0 OR f1.MotherID > 0
  )
  AND NOT EXISTS (
      SELECT 1
      FROM EventTable e_prim
      JOIN FamilyTable f1 ON f1.FamilyID = EventTable.OwnerID
      JOIN FamilyTable f_prim 
        ON f_prim.FatherID = f1.FatherID 
       AND f_prim.MotherID = f1.MotherID
      WHERE f_prim.FamilyID < f1.FamilyID
        AND e_prim.OwnerType = 1
        AND e_prim.OwnerID = f_prim.FamilyID
        AND e_prim.EventType = EventTable.EventType
        AND e_prim.PlaceID   IS EventTable.PlaceID
        AND e_prim.SiteID    IS EventTable.SiteID
        AND e_prim.Date      IS EventTable.Date
        AND e_prim.Sentence  IS EventTable.Sentence
        AND e_prim.Details   IS EventTable.Details
        AND e_prim.Note      IS EventTable.Note
  );
-- 3. Delete duplicate couple records from FamilyTable
DELETE FROM FamilyTable
WHERE FamilyID IN (
    SELECT f1.FamilyID
    FROM FamilyTable f1
    JOIN FamilyTable f2 
      ON f1.FatherID = f2.FatherID 
     AND f1.MotherID = f2.MotherID
     AND f1.FamilyID > f2.FamilyID
    WHERE f1.FatherID > 0 OR f1.MotherID > 0
);

-- 4. Clean up orphaned family events (OwnerType = 1)
DELETE FROM EventTable
WHERE OwnerType = 1 
  AND OwnerID NOT IN (
    SELECT FamilyID 
    FROM FamilyTable
);

COMMIT;
-----------END of FILE-------------