--DuplicateCouples-Colorcode-FAST.sql
/* 2026-08-19 by Gemini Flash AI directed by Tom Holden ve3meo
RED/GREEN - Father/Mother in a duplicate couple
YELLOW - persons of duplicate couple with a child not in their other duplicates
BLUE - Child of a duplicate couple overwritten by the above
 leaving 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
FROM FamilyTable
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 FROM IncongruentCouples WHERE FatherID > 0
    UNION
    SELECT MotherID FROM IncongruentCouples WHERE MotherID > 0
);

-- Clean up
DROP TABLE IF EXISTS TempDupCouples;

COMMIT;