-- ColorCode-byConsanguinity3.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
(farthest) 0 (black), 1 (maroon) to 14 (aqua).

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 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,8,'maroon');
INSERT INTO xConsangColor VALUES (2,10,'navy');
INSERT INTO xConsangColor VALUES (3,9,'green');
INSERT INTO xConsangColor VALUES (4,11,'purple');
INSERT INTO xConsangColor VALUES (5,12,'brown');
INSERT INTO xConsangColor VALUES (6,13,'teal');
INSERT INTO xConsangColor VALUES (7,14,'gray');
INSERT INTO xConsangColor VALUES (8,1,'red');
INSERT INTO xConsangColor VALUES (9,3,'blue');
INSERT INTO xConsangColor VALUES (10,2,'lime');
INSERT INTO xConsangColor VALUES (11,7,'silver');
INSERT INTO xConsangColor VALUES (12,4,'fuchsia');
INSERT INTO xConsangColor VALUES (13,5,'yellow');
INSERT INTO xConsangColor VALUES (14,6,'aqua');

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