/* DuplicateCouples-CreateView.sql
===============================================================================
Duplicate Couples Identifier - High Performance Version
Author: Tom Holden assisted by Gemini Flash
Target: RootsMagic 11 SQLite Database
Performance: ~0.5s on 165k FamilyTable / 120k PersonTable records
2026-08-21 supersedes 2026-08-15 version which took 3 hours on same database
===============================================================================
*/
DROP VIEW IF EXISTS DuplicateCouples;
CREATE TEMP VIEW DuplicateCouples AS
WITH fsIDname AS (
    -- Pre-build a flat lookup table mapping PersonIDs to primary names & FSIDs
    SELECT 
        n.OwnerID, 
        fs.fsID, 
        COALESCE(n.Surname || ', ' || n.Given, '[Unknown]') AS Name
    FROM NameTable n
    LEFT JOIN FamilySearchTable fs 
        ON n.OwnerID = fs.rmID 
       AND fs.LinkType = 0
    WHERE n.IsPrimary = 1
),
dupCouple AS (
    -- Isolate duplicate parents in FamilyTable first
    SELECT 
        f.FatherID,
        f.MotherID,
        COUNT(f.FamilyID) AS DuplicateCount,
        GROUP_CONCAT(f.FamilyID, ', ') AS DuplicateFamilyIDs
    FROM FamilyTable f
    WHERE f.FatherID > 0 OR f.MotherID > 0
    GROUP BY f.FatherID, f.MotherID
    HAVING COUNT(f.FamilyID) > 1
)
SELECT 
    dc.FatherID, 
    COALESCE(fsnf.fsID, '') AS FatherFSID,
    COALESCE(fsnf.Name, '[Unknown Father]') AS FatherName,
    dc.MotherID,
    COALESCE(fsnm.fsID, '') AS MotherFSID,
    COALESCE(fsnm.Name, '[Unknown Mother]') AS MotherName,
    dc.DuplicateCount,
    dc.DuplicateFamilyIDs
FROM dupCouple dc
LEFT JOIN fsIDname fsnf ON dc.FatherID = fsnf.OwnerID
LEFT JOIN fsIDname fsnm ON dc.MotherID = fsnm.OwnerID
ORDER BY FatherName, MotherName;

SELECT * FROM DuplicateCouples; -- Display VIEW