/*
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 red person if the person is not already color coded (spouses)
blue   - spouse of a green 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)
navy   - not otherwise colored but have a city directory entry
gray   - "keepers" that are set manually and not recolored in this script
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
WHERE Color != 14;      -- leave any gray people alone (added 10/12/2022)

-----------------------------------------------------------------------------------
-- color code all people to whom I am related to red
UPDATE PersonTable
SET Color = 1    -- red
WHERE ((Relate1 <> 0) OR (Relate2 <> 0)) AND Color !=14 ;   -- gray exception added 10/12/2022

-----------------------------------------------------------------------------------
-- 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 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 '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 = 10 THEN '9 navy'
WHEN P.Color = 11 THEN '5 purple'
WHEN P.Color = 12 THEN '6 brown'
WHEN P.Color = 14 THEN 'a 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 =  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 'sp siblings'
WHEN P.Color = 14 THEN 'unrelated but keeping'
END AS type
FROM PersonTable AS P
GROUP BY P.Color
ORDER BY color_T;