--DuplicateCouples-Colorcode-FAST.sql
/*  2026-08-19 by Gemini Flash AI directed by Tom Holden ve3meo
Rev 2026-08-26 added Purple because these children are significant markers in the 
     duplication of couples and child-parent links in RM8-11 downloads from
     FamilySearch Family Tree 
Issue: The YELLOW process could do with speedup (30s on medium db. 
       PURPLE was intially worse at >40s but brought down to <1s using temp table
Colour Coding:     
RED/GREEN - Father/Mother in a duplicate couple
YELLOW - persons of duplicate couple with a child not in their other duplicates
PURPLE - children of duplicate parents who are not common with the children of
         the other duplicate parents 
BLUE - Initially all Children of duplicate couples but then overwritten by the above
 leaving blue those that have as parents just one of the duplicate couples
*/

BEGIN TRANSACTION;

-- 1. Clear existing colors in Colorset1
UPDATE PersonTable SET Color = 0;

-- 2. Build lightweight temporary table of duplicate couple primary IDs
DROP TABLE IF EXISTS TempDupCouples;
CREATE TEMP TABLE TempDupCouples AS
SELECT FatherID, MotherID, MIN(FamilyID) AS PrimaryFamilyID,
       COUNT(FamilyID) AS TotalFamilyCount
FROM FamilyTable f
WHERE FatherID > 0 OR MotherID > 0
GROUP BY FatherID, MotherID
HAVING COUNT(FamilyID) > 1;

CREATE INDEX idx_temp_dup_parents ON TempDupCouples(FatherID, MotherID);

-- 3. Set Color = 3 (Blue) for children of duplicate couples
UPDATE PersonTable 
SET Color = 3 
WHERE PersonID IN (
    SELECT DISTINCT c.ChildID
    FROM ChildTable c
    JOIN FamilyTable f ON c.FamilyID = f.FamilyID
    JOIN TempDupCouples t ON f.FatherID = t.FatherID AND f.MotherID = t.MotherID
);

-- 4. Set Color = 1 (Red) for Fathers of duplicate couples
UPDATE PersonTable 
SET Color = 1 
WHERE PersonID IN (
    SELECT FatherID FROM TempDupCouples WHERE FatherID > 0
);

-- 5. Set Color = 2 (Lime) for Mothers of duplicate couples
UPDATE PersonTable 
SET Color = 2 
WHERE PersonID IN (
    SELECT MotherID FROM TempDupCouples WHERE MotherID > 0
);

-- 6. Overwrite with Color = 5 (Yellow) for parents with INCONGRUENT child sets
UPDATE PersonTable 
SET Color = 5 
WHERE PersonID IN (
    WITH IncongruentCouples AS (
        SELECT DISTINCT dc.FatherID, dc.MotherID
        FROM TempDupCouples dc
        JOIN FamilyTable f_sec 
          ON f_sec.FatherID = dc.FatherID 
         AND f_sec.MotherID = dc.MotherID 
         AND f_sec.FamilyID > dc.PrimaryFamilyID
        JOIN ChildTable c
          ON c.FamilyID = dc.PrimaryFamilyID OR c.FamilyID = f_sec.FamilyID
        LEFT JOIN ChildTable c_prim 
          ON c_prim.FamilyID = dc.PrimaryFamilyID AND c_prim.ChildID = c.ChildID
        LEFT JOIN ChildTable c_sec 
          ON c_sec.FamilyID = f_sec.FamilyID AND c_sec.ChildID = c.ChildID
        WHERE c_prim.ChildID IS NULL OR c_sec.ChildID IS NULL
    )
    SELECT FatherID AS PersonID FROM IncongruentCouples WHERE FatherID > 0
    UNION
    SELECT MotherID AS PersonID FROM IncongruentCouples WHERE MotherID > 0
);

-- 7. Overwrite with Color = 11 (Purple) for uncommon children of duplicate parents
-- -----------------------------------------------------------------------------
-- 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);

-- -----------------------------------------------------------------------------
-- Step 3: Set Color = 11 (Purple) for uncommon children of duplicate parents
-- -----------------------------------------------------------------------------
UPDATE PersonTable
SET Color = 11
WHERE PersonID IN
(SELECT DISTINCT ChildID
 FROM UncommonKidsOfDupParents
 );

COMMIT;