-- Children-SetLineage2Birth.sql
/*
2015-10-18 Tom Holden ve3meo

Sets the displayed lineage to that of the Birth parents so that 
lineage views and reports follow the birth line.
In the case of multiple birth parents (permitted by RootsMagic),
sets the birth family to the one with the highest Proof setting,
else to the lowest FamilyID.

It does not (or cannot?) cover all possible combinations that
a user might wish to allow or consider to be the preferred lineage
because a parent family (couple) may have only one member (mother
or father) and each spouse of a couple may have any of the four
proof settings for the birth relationship (0-undefined, 1-proven, 
2-disproven, 3-disputed). 

*/

DROP VIEW IF EXISTS BirthCouple
;
-- this view of the parents includes only birth parents in descending order of proof
CREATE TEMP VIEW BirthCouple AS
SELECT  ChildID, FamilyID FROM ChildTable 
WHERE RelFather = 0 AND RelMother = 0  -- birth relationships
AND ProofFather < 2 AND ProofMother < 2  -- relationship proof is proven or undefined
ORDER BY ChildID, ProofFather DESC, ProofMother DESC -- priority to 'proven' relationships
;

-- set the display lineage to the birth parents with highest level of proof and
-- do not touch persons with no parents 
UPDATE PersonTable 
  SET ParentID = 
    (SELECT FamilyID FROM BirthCouple BC
       WHERE PersonID = BC.ChildID)
  WHERE PersonID IN (SELECT ChildID FROM BirthCouple)
;

-- the temporary SQLite View BirthCouple will be deleted when the SQLite 
-- manager closes the database