/*
color_code_relations.sql    Jerry Bryan   2022/05/03

Color codes everyone in my database based on their relationship to me or to
another designated person. The query drives off of RM's Set Relationship process.
For example, I can Set Relationships using one of my children as the base rather
than myself prior to running this script to include my wife's relatives in the
color coding.

red    - everyone I'm related to
green  - spouse of a green person if the person is not already color coded (spouses)
blue   - spouse of a blue person if the person is not already color coded (spouses of spouses)
purple - parent of a green person if the person is not already color coded (parents of spouses)
brown  - child of a purple person if the person is not already color coded (sibling ofspouses)
aqua   - spouse of a purple person if the person is not already color coded (another spouse of parent of spouse)
yellow - not color coded with any other color
*/


-----------------------------------------------------------------------------------
-- default everybody to a yellow color code rather than black so a color block will
-- show up for everybody in RM8
UPDATE PersonTable
SET Color = 5;    -- yellow

-----------------------------------------------------------------------------------
-- color codeall people to whom I am related to red
UPDATE PersonTable
SET Color = 1    -- red
WHERE (Relate1 <> 0) OR (Relate2 <> 0);

-----------------------------------------------------------------------------------
-- 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
           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
           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
);



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

SELECT 
CASE
WHEN P.Color =  1 THEN '1 red'
WHEN P.Color =  3 THEN '4 blue'
WHEN P.Color =  5 THEN '8 yellow'
WHEN P.Color =  6 THEN '7 aqua'
WHEN P.Color =  9 THEN '2 green'
WHEN P.Color = 11 THEN '5 purple'
WHEN P.Color = 12 THEN '6 brown'
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 =  5 THEN 'unrelated'
WHEN P.Color =  6 THEN 'other spouse of parent of spouse'
WHEN P.Color =  9 THEN 'spouses'
WHEN P.Color = 11 THEN 'parent of spouse'
WHEN P.Color = 12 THEN 'sp siblings'
END AS type
FROM PersonTable AS P
GROUP BY P.Color
ORDER BY color_T;