-- LivingFlag-GlobalSet.sql
/*
2015-01-25 Tom Holden ve3meo

Sets Living flag false for:
- person with events before current year less 105, e.g., 1910 in 2015;
- person whose spouse has events before this cusp year;
- children of persons with birth before a certain year or death before a later
year should be deemed dead (if person born >105 yrs ago deemed dead, then 
a) child of parent born > (105 + 25) yrs ago could be deemed to have been born 
around 105 years ago and now dead 
b) child of parent who died > 105 years ago can be deemed to have been born 
around the same time and now dead;
- spouses of persons deemed dead are probably also dead;
- ancestors of above persons;
- person with death fact; 

Sets it true for everyone else.

To consider: 
- siblings of children who are deemed dead?
- descendants of deemed dead for a number of generations?   
*/

-- Calculate the year around which the Living flag is determined in the absence
-- of a death fact. RootsMagic uses 105 years after Birth
DROP VIEW IF EXISTS cuspyear
;

CREATE TEMP VIEW cuspyear
AS
SELECT strftime('%Y','now') - 105 AS Year
;


DROP VIEW IF EXISTS NotLiving_Factual
;
-- Persons deemed dead by age of their events
CREATE TEMP VIEW NotLiving_Factual
AS
-- EXPLAIN QUERY PLAN
     -- Persons with any Individual facts dated earlier than sosme year
     SELECT PersonID FROM PersonTable, EventTable
      WHERE PersonID=OwnerID
      AND OwnerType=0
      AND DATE LIKE 'D%'
      AND substr(DATE,4,4)< (SELECT Year FROM cuspyear) 
     UNION
     -- Husbands with any Family facts dated earlier than some year
     SELECT FatherID FROM FamilyTable F, EventTable E
      WHERE F.FamilyID = E.OwnerID
      AND FatherID > 0
      AND OwnerType=1
      AND DATE LIKE 'D%'
      AND substr(DATE,4,4)< (SELECT Year FROM cuspyear) 
     UNION
     -- Wives with any Family facts dated earlier than some year
     SELECT MotherID FROM FamilyTable F, EventTable E
      WHERE F.FamilyID = E.OwnerID
      AND MotherID > 0
      AND OwnerType=1
      AND DATE LIKE 'D%'
      AND substr(DATE,4,4)< (SELECT Year FROM cuspyear) 
     UNION
     -- Witness to any event dated earlier than some year
     SELECT PersonID FROM WitnessTable, EventTable
      USING(EventID)
      WHERE PersonID > 0
      AND DATE LIKE 'D%'
      AND substr(DATE,4,4)< (SELECT Year FROM cuspyear) 
;

DROP VIEW IF EXISTS NotLiving_Inferred
;

CREATE TEMP VIEW NotLiving_Inferred
AS
-- EXPLAIN QUERY PLAN
     -- Husband of any person deemed dead by age of events
     SELECT FatherID AS PersonID FROM FamilyTable F, NotLiving_Factual NLF
      WHERE F.MotherID = NLF.PersonID
      AND FatherID > 0
     UNION 
     -- Wife of any person deemed dead by age of events
     SELECT MotherID FROM FamilyTable F, NotLiving_Factual NLF
      WHERE F.FatherID = NLF.PersonID
      AND MotherID > 0
     UNION
     -- Child of father who died earlier than some year or born earlier than another
     SELECT Ch.ChildID FROM ChildTable Ch
     JOIN FamilyTable F USING(FamilyID)
     JOIN NameTable N1 ON F.FatherID = N1.OwnerID 
     WHERE N1.IsPrimary
     AND F.FatherID > 0 
     AND
     ( 
      (N1.DeathYear < (SELECT Year FROM cuspyear)  AND N1.DeathYear <> 0)
      OR
      (N1.BirthYear < ((SELECT Year FROM cuspyear)  -25) AND N1.BirthYear <> 0)
     )
     UNION
     -- Child of mother who died earlier than some year or born earlier than another
     SELECT Ch.ChildID FROM ChildTable Ch
     JOIN FamilyTable F USING(FamilyID)
     JOIN NameTable N1 ON F.MotherID = N1.OwnerID 
     WHERE N1.IsPrimary 
     AND F.MotherID > 0
     AND
     ( 
      (N1.DeathYear < (SELECT Year FROM cuspyear)  AND N1.DeathYear <> 0)
      OR
      (N1.BirthYear < ((SELECT Year FROM cuspyear)  -25) AND N1.BirthYear <> 0)
     )

;

DROP VIEW IF EXISTS NotLiving_Inferred_Spouse
;

CREATE TEMP VIEW NotLiving_Inferred_Spouse
AS
-- EXPLAIN QUERY PLAN
     -- Husband of any person deemed dead by inference
     SELECT FatherID AS PersonID FROM FamilyTable F, NotLiving_Inferred NLI
      WHERE F.MotherID = NLI.PersonID
     UNION 
     -- Wife of any person deemed dead by inference
     SELECT MotherID FROM FamilyTable F, NotLiving_Inferred NLI
      WHERE F.FatherID = NLI.PersonID
;

DROP VIEW IF EXISTS NotLiving_Roots
;

CREATE TEMP VIEW NotLiving_Roots
AS
-- EXPLAIN QUERY PLAN
SELECT PersonID FROM NotLiving_Factual 
UNION 
SELECT PersonID FROM NotLiving_Inferred
UNION 
SELECT PersonID FROM NotLiving_Inferred_Spouse
;

-- Ancestors of persons deemed dead by age of events or inference are also dead
DROP VIEW IF EXISTS NotLiving_Ancestral
;

CREATE TEMP VIEW NotLiving_Ancestral
AS
-- EXPLAIN QUERY PLAN
WITH RECURSIVE
  parent_of(ChildID, ParentID) AS
    (SELECT PersonID, FatherID AS ParentID FROM PersonTable
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID
       LEFT JOIN FamilyTable USING(FamilyID)
--       WHERE RelFather=0 --birth father (remove WHERE constraint to include all relationships)
     UNION
     SELECT PersonID, MotherID AS ParentID FROM PersonTable
       LEFT JOIN ChildTable ON PersonID=ChildTable.ChildID
       LEFT JOIN FamilyTable USING(FamilyID)
--       WHERE RelMother=0 --birth mother (remove WHERE constraint to include all relationships)
     ),
  ancestor_of_person(AncestorID) AS
    (SELECT ParentID FROM parent_of
       WHERE ChildID
        IN (SELECT PersonID FROM NotLiving_Roots) --=$Person(RIN) --enter RIN of starting person at runtime
     UNION --ALL
     SELECT ParentID FROM parent_of
       INNER JOIN ancestor_of_person ON ChildID = AncestorID)
SELECT AncestorID AS PersonID FROM ancestor_of_person, PersonTable
 WHERE ancestor_of_person.AncestorID=PersonTable.PersonID
;

DROP VIEW IF EXISTS Not_Living
;
CREATE TEMP VIEW Not_Living
AS
 -- Persons with death facts
 SELECT PersonID FROM PersonTable, EventTable
   WHERE PersonID=OwnerID
   AND EventType=2
 UNION
-- Persons deemed dead by age of their events
 SELECT PersonID FROM NotLiving_Factual 
 UNION
-- Persons inferred to be dead
 SELECT PersonID FROM NotLiving_Inferred
 UNION
-- spouses of Persons inferred to be dead
 SELECT PersonID FROM NotLiving_Inferred_Spouse
 UNION 
-- Ancestors of persons deemed dead by age of events are also dead
 SELECT PersonID FROM NotLiving_Ancestral
;

BEGIN TRANSACTION
;
-- Reset Living flag to true
UPDATE PersonTable
SET Living = 1
;

-- Set Living flag false
UPDATE PersonTable
SET Living = 0
WHERE PersonID IN
(
 SELECT PersonID FROM Not_Living
)
;
-- SET Living flag true for everyone else
UPDATE PersonTable
SET Living = 1
WHERE PersonID NOT IN
(
 SELECT PersonID FROM Not_Living
)
;
COMMIT TRANSACTION
;

SELECT '1. Script completed without execution error.' AS Status
UNION
SELECT '2. Check results in RootsMagic.'
UNION
SELECT '3. Use optional companion color-coding script.' 
;