/* DuplicateCouples-Child-Uncommon Children of Duplicate Parents.sql
   2026-08-26 Tom Holden ve3meo with Gemini
   
===============================================================================
Uncommon Children of Duplicate Parents (High-Performance TEMP TABLE Version)
Target: RootsMagic 8-11 SQLite Database
Performance: ~4,929 rows in < 1 second on medium database (165k+ Families)

Description:
  Identifies children linked to AT LEAST ONE family record of a duplicate couple,
  but NOT linked to ALL family records of that same couple (excluding childless
  family instances). 

  These "uncommon children" serve as primary diagnostic markers for identifying
  lineage-duplication bugs introduced during FamilySearch Family Tree downloads.
===============================================================================
*/

-- -----------------------------------------------------------------------------
-- Step 1: Materialize active duplicate parent pairs (Excludes childless families)
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS TempDupParents;
CREATE TEMP TABLE TempDupParents AS
SELECT
    f.FatherID,
    f.MotherID,
    MIN(f.FamilyID) AS PrimaryFamilyID,
    COUNT(DISTINCT c.FamilyID) AS TotalFamilyCount
FROM ChildTable c
JOIN FamilyTable f ON c.FamilyID = f.FamilyID
WHERE f.FatherID > 0 OR f.MotherID > 0
GROUP BY f.FatherID, f.MotherID
HAVING TotalFamilyCount > 1;

-- Index the temp table to ensure instant joins in Step 2
CREATE INDEX IF NOT EXISTS idx_TempDupParents ON TempDupParents(FatherID, MotherID);

-- -----------------------------------------------------------------------------
-- Step 2: Build table of children attached to fewer family records than total
-- -----------------------------------------------------------------------------
DROP TABLE IF EXISTS UncommonKidsOfDupParents;
CREATE TEMP TABLE UncommonKidsOfDupParents AS
WITH ChildParentCounts AS (
    SELECT 
        c.ChildID,
        dp.FatherID,
        dp.MotherID,
        dp.TotalFamilyCount,
        COUNT(DISTINCT c.FamilyID) AS ChildFamilyCount
    FROM ChildTable c
    JOIN FamilyTable f ON c.FamilyID = f.FamilyID
    JOIN TempDupParents dp ON f.FatherID = dp.FatherID AND f.MotherID = dp.MotherID
    GROUP BY c.ChildID, dp.FatherID, dp.MotherID
)
SELECT DISTINCT
    ChildID,
    FatherID,
    MotherID
FROM ChildParentCounts
WHERE ChildFamilyCount < TotalFamilyCount;

-- Index the resulting child IDs for rapid downstream reporting & color-coding
CREATE INDEX IF NOT EXISTS idx_UncommonKids ON UncommonKidsOfDupParents(ChildID);


-- =============================================================================
-- REPORTING & DIAGNOSTIC OUTPUTS
-- =============================================================================

-- 1. Base Summary: Unique IDs of Uncommon Children and Parents
SELECT * FROM UncommonKidsOfDupParents;

/* SELECT DESIRED QUERY AND USE SELECTIVE EXECUTION
-- 2. Detailed List: Child Name, FamilySearch ID, and Parent Details
WITH fsIDname AS (
    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 
    uk.ChildID AS RIN,
    COALESCE(c_info.fsID, '') AS ChildFSID,
    c_info.FullName AS ChildName,
    uk.FatherID,
    COALESCE(f_info.FullName, '[No Father Listed]') AS FatherName,
    uk.MotherID,
    COALESCE(m_info.FullName, '[No Mother Listed]') AS MotherName
FROM UncommonKidsOfDupParents uk
LEFT JOIN fsIDname c_info ON uk.ChildID = c_info.OwnerID
LEFT JOIN fsIDname f_info ON uk.FatherID = f_info.OwnerID
LEFT JOIN fsIDname m_info ON uk.MotherID = m_info.OwnerID
ORDER BY c_info.FullName;


-- 3. Diagnostic Check: View Current RootsMagic Color Labels for target children
SELECT 
    uk.ChildID, 
    p.Color, 
    COUNT(*) AS InstanceCount
FROM UncommonKidsOfDupParents uk
JOIN PersonTable p ON uk.ChildID = p.PersonID
GROUP BY uk.ChildID, p.Color;
*/