/* DuplicateCouples-CreateChildView.sql
/* 2026-08-15 by Gemini Flash directed by Tom Holden ve3meo
rev2026-08-27 vastly improved performance on large database
===============================================================================
Duplicate Couples Children - High Performance Version
Author: Tom Holden / Gemini
Target: RootsMagic 11 SQLite Database
Performance: Sub-second (~ 1s) on large databases (340k+ ChildTable records)

Description:
  Generates a list of all distinct children attached to any family instance 
  of a duplicate parent pair, including primary names and FamilySearch IDs.
===============================================================================
*/

-- Step 1: Materialize active duplicate parent pairs into an indexed TEMP TABLE
DROP TABLE IF EXISTS TempDupCouples;
CREATE TEMP TABLE TempDupCouples AS
SELECT 
    FatherID, 
    MotherID,
    GROUP_CONCAT(FamilyID, ', ') AS AllFamilyIDs
FROM FamilyTable
WHERE FatherID > 0 OR MotherID > 0
GROUP BY FatherID, MotherID
HAVING COUNT(FamilyID) > 1;

CREATE INDEX IF NOT EXISTS idx_TempDupCouples ON TempDupCouples(FatherID, MotherID);

-- Step 2: Create the Temp View driven directly from the Temp Table
DROP VIEW IF EXISTS DuplicateCouplesChildren;
CREATE TEMP VIEW DuplicateCouplesChildren AS
WITH fsIDname AS (
    -- Pre-build flat lookup for primary names & FamilySearch IDs
    SELECT 
        n.OwnerID, 
        fs.fsID, 
        COALESCE(n.Surname || ', ' || n.Given, '[Unknown]') AS FullName
    FROM NameTable n
    LEFT JOIN FamilySearchTable fs 
        ON n.OwnerID = fs.rmID 
       AND fs.LinkType = 0
    WHERE n.IsPrimary = 1
)
SELECT DISTINCT
    c.ChildID,
    COALESCE(fn.fsID, '') AS ChildFSID,
    COALESCE(fn.FullName, '[Unknown Child]') AS ChildName,
    dc.FatherID,
    dc.MotherID,
    dc.AllFamilyIDs AS CoupleFamilyIDs
FROM TempDupCouples dc
JOIN FamilyTable f 
    ON f.FatherID = dc.FatherID 
   AND f.MotherID = dc.MotherID
JOIN ChildTable c 
    ON c.FamilyID = f.FamilyID
LEFT JOIN fsIDname fn 
    ON c.ChildID = fn.OwnerID
ORDER BY ChildName;

-- Display View
SELECT * FROM DuplicateCouplesChildren;