/*

_color_code_relatives_v3.sql   14 Jan 2025     Jerry Bryan

This is step #1 of a four step procedure to color code relatives and to
do so without relying on the new version of the Set Relationips tool
in RM 10.0.1 which sets relationships to spouses of relatives. On its
face, that's a good idea. But many times a person is identifed as
spouse of a relative rather than being identifying an actual relative.
The purpose of this new color coding script is to color code a person
as an actual relative rather than as the spouse of a relative whenever
possible.

This step first color codes everyone in the database as yellow. Yellow
is the color for someone who is not color coded. Yellow is used rather
than black to be able to distinguish existing people who are not color coded
from newly entered people in the database who default to black (or to
no color).

This step then color codes everyone as gray who has the Color_Gray fact.
Gray is the color for people for whom I have no known family relationship
but where I want to keep them in my database rather than deleting them. The
reasons for marking certain people as gray and for keeping them in my database
are various and often without specific rhyme or reason. So they are simply gray
and they follow no other rules. I try to keep them at a minimum. When I color
code them as gray, I also add a Color_Gray fact. Color codes can be ephemeral
and sometimes easily lost. A fact itself is a little more reliable in the
long run.

Finally, this step color codes as red one person in the database to be
the base person of the color coding. This person is usually yourself, but
it need not be. Also, the overall script is set up so that you could have
more than one base person, such as both yourself and your spouse. That
way, both your relatives and your spouse's relatives would be color coded
as relatives.

Everything in this script can actually be accomplished directly from the
RM user interface. But when running the overall four steps, it's more
convenient just to run this step from SQLite than from RM.

*/

--------------------------------------------------------------------------------

UPDATE PersonTable         -- clear all colors
SET Color  = 5;            -- yellow as the cleared color

--------------------------------------------------------------------------------

UPDATE PersonTable         -- set special people who follow no rules
SET Color = 14             -- gray as the special color
WHERE PersonID IN          -- people with the Color_Gray fact
(
SELECT DISTINCT E.OwnerID
FROM EventTable AS E
JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
 AND FT.Name LIKE 'Color_Gray'
);

--------------------------------------------------------------------------------

-- other options for the base person than yourself could include multiple
-- base people, such as yourself and your spouse to pick up your spouse's family
-- as well as your own. Or there could be other individuals of interest that you
-- might wish color code as "related", such as your father's stepmother who
-- raised him or things like that.

UPDATE PersonTable         -- set base person for relationships
SET Color = 1              -- red as the relative color 
WHERE PersonID IN (2,3);   -- base person for relationships, usually yourself
                           -- could be multiple people, e.g. IN (2,3,12)

--------------------------------------------------------------------------------

-- Step #2. Color code ancestors of red people as red. We color code parents
-- of red people as red recursively. The net effect is to color code all
-- ancestors as red.

WITH Parents AS
(
SELECT F.FatheriD AS Parent, F.FamilyID
FROM FamilyTable AS F
WHERE F.FatherID != 0

UNION ALL

SELECT F.MotherID AS Parent, F.FamilyID
FROM FamilyTable AS F
WHERE F.MotherID != 0
),

Ancestors (A_ID, Fam_ID) AS
(
  SELECT CH.ChildID, CH.FamilyID
  FROM ChildTable AS CH
  JOIN PersonTable AS P ON P.PersonID = CH.ChildID AND Color = 1
 
 UNION ALL

 SELECT F.Parent, F.FamilyID
 FROM Parents AS F
 JOIN ChildTable AS CH ON CH.FamilyID = F.FamilyID
 JOIN Ancestors AS A ON A.A_ID = CH.ChildID
)

UPDATE PersonTable
SET Color = CASE WHEN PersonID IN (SELECT A_ID FROM Ancestors) THEN 1 ELSE 5 END
WHERE Color != 14;     -- don't change gray people


--------------------------------------------------------------------------------

-- Step #3. Color code children of red people as red recursively,
-- yielding all relatives to be marked red.

WITH Descendants (L, NewChild) AS
(
SELECT 1, Ch.ChildID
FROM FamilyTable AS F
JOIN ChildTable AS Ch ON Ch.FamilyID = F.FamilyID
JOIN PersonTable AS P ON (P.PersonID = F.FatherID AND P.Color = 1) OR (P.PersonID = F.MotherID AND P.Color = 1)

UNION ALL

SELECT L+1, Ch.ChildID
FROM Descendants AS D
JOIN FamilyTable AS F ON F.FatherID = NewChild OR F.MotherID = NewChild
JOIN ChildTable AS Ch ON Ch.FamilyID = F.FamilyID
WHERE L < 50     -- limit to 50 generations to avoid an infinite loop
)

UPDATE PersonTable
SET Color = 1
WHERE Color != 14 AND Color != 1
AND PersonID IN
(
 SELECT NewChild FROM Descendants
);

--------------------------------------------------------------------------------

/*

This is step #4 of a four step procedure to color code relatives and to
do so without relying on the new version of the Set Relationips tool
in RM 10.0.1 which sets relationships to spouses of relatives. On its
face, that's a good idea. But many times a person is identifed as
spouse of a relative rather than being identifying an actual relative.
The purpose of this new color coding script is to color code a person
as an actual relative rather than as the spouse of a relative whenever
possible.

This code is actually some of the same code that was in an earlier script
which did rely RM's Set Relationship tool. It just picks up after all the
actual relatives have been correctly color coded as red. The various
additional color codes and their purposes are identifed at each step of
the script. This script is run only once and requires no repetition with
F9 was described for step #2 and step #3 of this script.

*/

-----------------------------------------------------------------------------------
-- for people still yellow, set their color code to green if they are the spouse
-- of someone who has a color code of red (spouse of a relative)

UPDATE PersonTable
SET Color = 9    -- green
WHERE PersonID IN
(   
   SELECT F.FatherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 1
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 5     
                                        UNION         
   SELECT F.MotherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 1
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 5
);

-----------------------------------------------------------------------------------
-- for people still yellow, set their color code to blue if they are the spouse
-- of someone who has a color code of green (spouse of a spouse).
UPDATE PersonTable
SET Color = 3    -- blue
WHERE PersonID IN
(   
   SELECT F.FatherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 9
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 5     
                                        UNION         
   SELECT F.MotherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 9
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 5
);

-----------------------------------------------------------------------------------
-- for people still yellow, set their color code to purple if they are the parent
-- of someone who has a color code of green (parent of a spouse)
UPDATE PersonTable
SET Color = 11    -- purple
WHERE PersonID IN
(   
   SELECT DISTINCT F.FatherID AS RIN
   FROM ChildTable AS C
           JOIN
        FamilyTable AS F ON F.FamilyID = C.FamilyID
           JOIN 
        PersonTable AS P_child ON P_child.PersonID = C.Childid AND (P_child.Color = 9 OR P_child.Color = 1)
           JOIN
        PersonTable AS P_parent ON P_parent.PersonID = F.FatherID AND P_parent.Color = 5     
                 UNION              
   SELECT DISTINCT F.MotherID AS RIN
   FROM ChildTable AS C
           JOIN
        FamilyTable AS F ON F.FamilyID = C.FamilyID
           JOIN 
        PersonTable AS P_child ON P_child.PersonID = C.Childid AND (P_child.Color = 9 OR P_child.Color = 1)
           JOIN
        PersonTable AS P_parent ON P_parent.PersonID = F.MotherID AND P_parent.Color = 5
);

-----------------------------------------------------------------------------------
-- for people still yellow, set their color code to brown if they are the child
-- of someone who has a color code of purple (sibling of a spouse)
UPDATE PersonTable
SET Color = 12    -- brown
WHERE PersonID IN
(
   SELECT DISTINCT C.ChildID AS RIN
   FROM ChildTable AS C
           JOIN
        PersonTable AS P_child ON P_child.PersonID = C.ChildID AND P_child.Color = 5
           JOIN
        FamilyTable AS F ON F.FamilyID = C.FamilyID
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 11
                 UNION
   SELECT DISTINCT C.ChildID AS RIN
   FROM ChildTable AS C
           JOIN
        PersonTable AS P_child ON P_child.PersonID = C.ChildID AND P_child.Color = 5
           JOIN
        FamilyTable AS F ON F.FamilyID = C.FamilyID
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 11
);

-----------------------------------------------------------------------------------
-- for people still yellow, set their color code to aqua if they are another spouse
-- of someone who has a color code of purple (another spouse of a parent of a spouse)
UPDATE PersonTable
SET Color = 6    -- aqua
WHERE PersonID IN
(   
   SELECT DISTINCT F.FatherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 11
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 5     
                                        UNION         
   SELECT DISTINCT F.MotherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 11
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 5
);

-----------------------------------------------------------------------------------
-- for people still yellow, set their color code to fuscia if they are the spouse
-- of someone who has a color code of brown (spouse of a sibling of a spouse of a relative)

UPDATE PersonTable
SET Color = 4    -- fuscia
WHERE PersonID IN
(   
   SELECT F.FatherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 12
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 5     
                                        UNION         
   SELECT F.MotherID AS RIN
   FROM FamilyTable AS F
           JOIN
        PersonTable AS P_father ON P_father.PersonID = F.FatherID AND P_father.Color = 12
           JOIN
        PersonTable AS P_mother ON P_mother.PersonID = F.MotherID AND P_mother.Color = 5
);


-----------------------------------------------------------------------------------
-- for people still yellow, set their color code to navy if they have a City Directory
-- fact for any year. this is basically in support of getting all Knoxville City
-- Directory entries for my main surnames, whether I am related to the person or not.
UPDATE PersonTable
SET Color = 10    -- navy
WHERE PersonID IN
(   
     SELECT DISTINCT E.OwnerID
     FROM EventTable AS E  -- E.OwnerID is the PersonID for City Directory facts
             JOIN
          FactTypeTable AS FT ON E.OwnerType = 0 AND FT.Name LIKE('%City Directory%') AND E.EventType = FT.FactTypeID
             JOIN
          PersonTable AS P ON P.PersonID = E.OwnerID AND P.Color = 5  
);

-----------------------------------------------------------------------------------
---------------------- run a little color summary report --------------------------
-----------------------------------------------------------------------------------

SELECT 
CASE
WHEN P.Color =  1 THEN '0 red'
WHEN P.Color =  3 THEN '2 blue'
WHEN P.Color =  4 THEN '5 fuscia'
WHEN P.Color =  5 THEN '7 yellow'
WHEN P.Color =  6 THEN '6 aqua'
WHEN P.Color =  9 THEN '1 green'
WHEN P.Color = 10 THEN '8 navy'
WHEN P.Color = 11 THEN '3 purple'
WHEN P.Color = 12 THEN '4 brown'
WHEN P.Color = 14 THEN '9 gray'
END AS color_T,
P.Color AS Color_N,
COUNT(P.Color) AS Num,
CASE
WHEN P.Color =  1 THEN 'relatives'
WHEN P.Color =  3 THEN 'spouses spouse'
WHEN P.Color =  4 THEN 'spouses siblings spouse'
WHEN P.Color =  5 THEN 'unrelated'
WHEN P.Color =  6 THEN 'other spouse of parent of spouse'
WHEN P.Color =  9 THEN 'spouses'
WHEN P.Color = 10 THEN 'city directory'
WHEN P.Color = 11 THEN 'parent of spouse'
WHEN P.Color = 12 THEN 'spouse siblings'
WHEN P.Color = 14 THEN 'unrelated but keeping'
END AS type
FROM PersonTable AS P
GROUP BY P.Color
ORDER BY color_T;