-- DuplicateCouples-CreateView.sql
/* 2026-08-15 by Gemini Flash directed by Tom Holden ve3meo
Stored query (temporary) lists duplicate couples.
*/ 
DROP VIEW IF EXISTS DuplicateCouples;
CREATE TEMP VIEW DuplicateCouples AS
SELECT 
    f.FatherID,
    COALESCE(fsf.fsID, '') AS FatherFSID,
    COALESCE(nf.Surname || ', ' || nf.Given, '[Unknown Father]') AS FatherName,
    f.MotherID,
    COALESCE(fsm.fsID, '') AS MotherFSID,
    COALESCE(nm.Surname || ', ' || nm.Given, '[Unknown Mother]') AS MotherName,
    COUNT(f.FamilyID) AS DuplicateCount,
    GROUP_CONCAT(f.FamilyID, ', ') AS DuplicateFamilyIDs
FROM FamilyTable f
LEFT JOIN FamilySearchTable fsf 
    ON f.FatherID = fsf.rmID 
   AND fsf.LinkType = 0
LEFT JOIN NameTable nf 
    ON f.FatherID = nf.OwnerID 
   AND nf.IsPrimary = 1
LEFT JOIN FamilySearchTable fsm 
    ON f.MotherID = fsm.rmID 
   AND fsm.LinkType = 0
LEFT JOIN NameTable nm 
    ON f.MotherID = nm.OwnerID 
   AND nm.IsPrimary = 1
WHERE f.FatherID > 0 OR f.MotherID > 0
GROUP BY f.FatherID, f.MotherID
HAVING COUNT(f.FamilyID) > 1
ORDER BY FatherName, MotherName
;