-- DummyFamily-Add2.sql
/* 2016-08-16 Tom Holden ve3meo
rev 2016-08-20 added criteria for General Note and for Alternate Name
 
Extension of DummyFamily-Add.sql described below with added constraints so that
the trick is applied only to those childless, spouseless persons having events
other than Birth or Death or more than two of those or BD events with notes
or having a General Note or an Alternate Name.

-- Based on trick developed by Jerry Bryan to cause Narrative Descendant reports
-- to output childless, spouseless children in their own generation, along with their 
-- married or parental siblings.
-- 
-- Adds dummy family(ies) for this(all such) child(ren), based on the runtime parameter,
-- to the FamilyTable. Requires SQLite Expert Personal or equiv to support selection of a single
-- child.
-- 
-- After completing the report, run DummyFamily-Delete.sql; it is probably less risky, however,
-- to run the first query and report on a copy of the database and then delete the copy.
-- 
-- 
*/

-- childless, spouseless children
DROP VIEW IF EXISTS vEndChildren
;
CREATE TEMP VIEW vEndChildren 
AS
SELECT CH.ChildID FROM ChildTable CH
WHERE
-- the child is not a father
 CH.ChildID NOT IN (SELECT FatherID FROM FamilyTable) 
AND  -- the child is not a mother
 CH.ChildID NOT IN (SELECT MotherID FROM FamilyTable)
AND
 (  -- the child has events other than Birth and Death and Reference #
  (SELECT COUNT() FROM EventTable E 
   WHERE CH.ChildID = E.OwnerID 
   AND E.OwnerType=0 AND E.EventType NOT IN (1,2,35)
   )
  OR  -- the child has more than 2 Birth or Death events
  (SELECT COUNT() FROM EventTable E 
   WHERE CH.ChildID = E.OwnerID 
   AND E.OwnerType=0 AND E.EventType BETWEEN 1 AND 2
   ) > 2
  OR  -- the child has Notes in the Birth and Death events
  (SELECT SUM(LENGTH(E.Note)) FROM EventTable E 
   WHERE CH.ChildID = E.OwnerID 
   AND E.OwnerType=0 AND E.EventType BETWEEN 1 AND 2
   )
  OR  -- the child has a General Note
  (SELECT PersonID FROM PersonTable P
   WHERE CH.ChildID = P.PersonID
   AND LENGTH(Note)
   ) 
  OR  -- the child has Alternate Names
  (SELECT DISTINCT OwnerID FROM NameTable N
   WHERE CH.ChildID = N.OwnerID
   AND NOT IsPrimary
   )
  )
; 

INSERT INTO FamilyTable 
 (FatherID)
SELECT ChildID FROM vEndChildren
WHERE
 CASE 
  WHEN @RIN NOT LIKE '' THEN ChildID = @RIN
  ELSE 1
 END 
;

SELECT 'Completed adding a dummy family to each childless, spouseless child.' || X'0D0A' ||
       'Run Dummy Family - Delete after completing the RootsMagic reports.'  AS Status
;