-- ColorCode-byConsanguinity2.sql
/* 2016-06-30 Tom Holden ve3meo

Sets color code by relationship distance = PersonTable.(Relate1 + Relate2) using
the reordered RootsMagic color values ranging from brightest (closest) to darkest
(farthest) 0 (black), 1 (fuchsia) to 14 (gray).

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. 


*/

BEGIN
;

-- a lookup table to relate consanguinity with color code from brightest to darkest
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,4,'fuchsia');
INSERT INTO xConsangColor VALUES (2,5,'yellow');
INSERT INTO xConsangColor VALUES (3,6,'aqua');
INSERT INTO xConsangColor VALUES (4,7,'silver');
INSERT INTO xConsangColor VALUES (5,1,'red');
INSERT INTO xConsangColor VALUES (6,2,'lime');
INSERT INTO xConsangColor VALUES (7,3,'blue');
INSERT INTO xConsangColor VALUES (8,14,'gray');
INSERT INTO xConsangColor VALUES (9,11,'purple');
INSERT INTO xConsangColor VALUES (10,12,'brown (olive?)');
INSERT INTO xConsangColor VALUES (11,13,'teal');
INSERT INTO xConsangColor VALUES (12,8,'maroon');
INSERT INTO xConsangColor VALUES (13,9,'green');
INSERT INTO xConsangColor VALUES (14,10,'navy');

-- clear colors
UPDATE PersonTable SET Color = 0
;

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