-- ColorCode-byConsanguinity5 (Kbl).sql
/* 2016-06-30 Tom Holden ve3meo 
Sets color code by relationship distance = PersonTable.(Relate1 + Relate2) using
the reordered RootsMagic color values ranging from darkest (closest) to brightest
(by gray scale)

There are 14 color codes other than black so 14 distances from the 
root person can be coded. Any beyond that distance are limited to the last color. 

   2022-08-19 (slightly modified by Kevin Labore)
updated colors to protect Red, Orange, Yellow, Green, Silver form being overwritten
could be updated to user preference 

*/

BEGIN
;

-- a lookup table to relate consanguinity with color code from darkest to brightest
DROP TABLE IF EXISTS xConsangColor
;
CREATE TEMP TABLE xConsangColor(Consanguinity INTEGER Primary Key, ColorID INTEGER, ColorName TEXT)
;
INSERT INTO xConsangColor VALUES (0,0,'black');
INSERT INTO xConsangColor VALUES (1,27,'slate');
INSERT INTO xConsangColor VALUES (2,8,'maroon');
INSERT INTO xConsangColor VALUES (3,10,'navy');
INSERT INTO xConsangColor VALUES (4,25,'forest');
INSERT INTO xConsangColor VALUES (5,13,'teal');
INSERT INTO xConsangColor VALUES (6,12,'brown');
INSERT INTO xConsangColor VALUES (7,24,'olive');
INSERT INTO xConsangColor VALUES (8,23,'khaki');
INSERT INTO xConsangColor VALUES (9,3,'blue');
INSERT INTO xConsangColor VALUES (10,2,'lime');
INSERT INTO xConsangColor VALUES (11,20,'mauve');
INSERT INTO xConsangColor VALUES (12,6,'aqua');
INSERT INTO xConsangColor VALUES (13,4,'pink');
INSERT INTO xConsangColor VALUES (14,19,'azure');

-- clear colors (Where NOT Red, Orange, Yellow, Green, Silver protected colors)
UPDATE PersonTable SET Color = 0
WHERE COLOR <> 9 or COLOR <> 1 or COLOR <> 21 or COLOR <> 5 or COLOR <> 7 
;

UPDATE PersonTable
SET Color =
 (SELECT ColorID FROM xConsangColor WHERE MIN(Relate1 + Relate2,14) = Consanguinity)
WHERE Relate1 < 999 and Color=0  --except self and in-laws (Where NOT protected color)
;

COMMIT
;