-- KinshipList.sql
/*
2016-01-20 Tom Holden ve3meo
rev 2016-07-29 sample query calculates consanguinity degree of relationship

Displays relationships as set by the RootsMagic Set Relationships tool.
The script creates two SQLite Views, xRelativesBlood on which xRelativesAll 
is built, adding the spouses of the blood relatives. These Views persist
until the SQLite manager closes the database so they can be used
both directly and in other scripts.

*/

DROP VIEW IF EXISTS xRelativesBlood
;

CREATE TEMP VIEW xRelativesBlood
AS
SELECT PersonID, Relate1, Relate2,
  CASE Relate1
    WHEN 999
      THEN CASE Relate2
             WHEN 1 THEN 'self'
             WHEN 2 THEN 'spouse'
             WHEN 3 THEN SUBSTR('father,mother',Sex*7+1,6)||'-in-law'
             WHEN 4 THEN SUBSTR('brother,sister',Sex*8+1,7)||'-in-law'
             WHEN 5 THEN SUBSTR('son,daughter',Sex*4+1,3+Sex*5)||'-in-law'
           END
    WHEN 0
      THEN CASE Relate2
             WHEN 0 THEN 'TBD'
             WHEN 1 THEN SUBSTR('father,mother',Sex*7+1,6)
             WHEN 2 THEN 'grand'||SUBSTR('father,mother',Sex*7+1,6)
             ELSE (Relate2-2)||' great-grand'||SUBSTR('father,mother',Sex*7+1,6)
           END
    WHEN 1
      THEN CASE Relate2
             WHEN 0 THEN SUBSTR('son,daughter',Sex*4+1,3+Sex*5)
             WHEN 1 THEN SUBSTR('brother,sister',Sex*8+1,7)
             WHEN 2 THEN SUBSTR('uncle,aunt',Sex*6+1,5)
             WHEN 3 THEN 'grand'||SUBSTR('uncle,aunt',Sex*6+1,5)
             ELSE (Relate2-3)||' great-grand'||SUBSTR('uncle,aunt',Sex*6+1,5)
           END
    WHEN 2
      THEN CASE Relate2
             WHEN 0 THEN 'grand'||SUBSTR('son,daughter',Sex*4+1,3+Sex*5)
             WHEN 1 THEN SUBSTR('nephew,niece',SEX*7+1,6)
             WHEN 2 THEN '1 cousin'
             ELSE '1 cousin ' || (Relate2-2) || 'x removed'
           END    
    ELSE CASE 
           WHEN Relate2=0 THEN CASE WHEN(Relate1-2) THEN (Relate1-2)||' ' ELSE '' END ||'great-grand'||SUBSTR('son,daughter',Sex*4+1,3+Sex*5)
           WHEN Relate2=1 THEN CASE Relate1
                            WHEN 3 THEN 'grand'||SUBSTR('nephew,niece',SEX*7+1,6)
                            ELSE CASE WHEN (Relate1-4) THEN (Relate1-4)||' ' ELSE '' END ||'great-grand'||SUBSTR('nephew,niece',SEX*7+1,6)
                          END
           WHEN Relate1=Relate2 THEN ABS(Relate1-1)||' cousin' 
           ELSE (MIN(Relate1,Relate2)-1)||' cousin '|| ABS(Relate1-Relate2) || 'x removed'
         END
  END
  AS Relationship
FROM PersonTable
WHERE Relate1 + Relate2 > 0
;
 
DROP VIEW IF EXISTS xRelativesAll
;

CREATE TEMP VIEW xRelativesAll AS 
SELECT * FROM xRelativesBlood
UNION ALL
-- wives of relatives
SELECT P.PersonID, P.Relate1, P.Relate2,
  'spouse of ' || R.Relationship
FROM PersonTable P
INNER JOIN FamilyTable F ON P.PersonID=F.FatherID
INNER JOIN xRelativesBlood R ON F.MotherID = R.PersonID
WHERE P.Relate1 = 0 AND P.Relate2=0
UNION ALL
-- husbands of relatives
SELECT P.PersonID, P.Relate1, P.Relate2,
  'spouse of ' || R.Relationship
FROM PersonTable P
INNER JOIN FamilyTable F ON P.PersonID=F.MotherID
INNER JOIN xRelativesBlood R ON F.FatherID = R.PersonID
WHERE P.Relate1 = 0 AND P.Relate2=0
;       

-- sample query using the xRelativesAll View
-- Consanguinity degree = Relate1 + Relate2 for Relate1 <999
SELECT PersonID AS RIN, Surname||', '||Given AS Relative, Relationship, Relate1, Relate2, Relate1+Relate2 AS Consanguinity 
FROM xRelativesAll
INNER JOIN NameTable N
ON PersonID = OwnerID AND IsPrimary
;