-- Children-SetOrderByBirthSortDate.sql
/*
2015-01-22 Tom Holden ve3meo
2015-01-22 rev to preserve the order of children in families where
           1 or more have no birth date or no birth fact
2015-01-24 rev to correct the override of valid Christen SortDates
           by empty Birth SortDates 

Builds a view or table that has all the Primary Birth events,
non-primary Birth event in the absence of a primary, and the Christening event 
in the absence of a Birth event.
From that table, derive the ChildOrder and update the database ChildTable with it.

Potential issue: ideally, there should be only one birth-type event 
per child per family but the script does not guarantee that so the effect
is uncertain; probably the last event will govern the outcome. 
*/
-- collect primary Births' SortDates in a view 
DROP VIEW IF EXISTS ChildPrimBirthSortDate
;
CREATE TEMP VIEW ChildPrimBirthSortDate AS
SELECT OwnerID AS ChildID, SortDate
FROM EventTable
WHERE OwnerType = 0
AND EventType = 1
AND IsPrimary
AND SortDate < 9223372036854775807 -- not empty
;

-- collect non-primary Births' SortDates in a View
DROP VIEW IF EXISTS ChildNotPrimBirthSortDate
;
CREATE TEMP VIEW ChildNotPrimBirthSortDate AS
SELECT OwnerID AS ChildID, SortDate
FROM EventTable
WHERE OwnerType = 0
AND EventType = 1
AND NOT IsPrimary
AND SortDate < 9223372036854775807 -- not empty
;

-- Combine the Prim and Non-Prim Births, giving exclusive priority to Prim
DROP VIEW IF EXISTS ChildBirthSortDate
;
CREATE TEMP VIEW ChildBirthSortDate
AS
SELECT * FROM ChildPrimBirthSortDate
UNION
SELECT * FROM ChildNotPrimBirthSortDate
WHERE ChildID IN (SELECT ChildID FROM ChildNotPrimBirthSortDate EXCEPT SELECT ChildID FROM ChildPrimBirthSortDate)
;


-- collect primary Christenings' SortDates in a view 
DROP VIEW IF EXISTS ChildPrimChrisSortDate
;
CREATE TEMP VIEW ChildPrimChrisSortDate AS
SELECT OwnerID AS ChildID, SortDate
FROM EventTable
WHERE OwnerType = 0
AND EventType = 3
AND IsPrimary
AND SortDate < 9223372036854775807 -- not empty
;

-- collect non-primary Christenings' SortDates in a View
DROP VIEW IF EXISTS ChildNotPrimChrisSortDate
;
CREATE TEMP VIEW ChildNotPrimChrisSortDate AS
SELECT OwnerID AS ChildID, SortDate
FROM EventTable
WHERE OwnerType = 0
AND EventType = 3
AND NOT IsPrimary
AND SortDate < 9223372036854775807 -- not empty
;

-- Combine the Prim and Non-Prim Christenings, giving exclusive priority to Prim
DROP VIEW IF EXISTS ChildChrisSortDate
;
CREATE TEMP VIEW ChildChrisSortDate
AS
SELECT * FROM ChildPrimChrisSortDate
UNION
SELECT * FROM ChildNotPrimChrisSortDate
WHERE ChildID IN (SELECT ChildID FROM ChildNotPrimChrisSortDate EXCEPT SELECT ChildID FROM ChildPrimChrisSortDate)
; 

-- Combine the Birth and Chris, giving exclusive priority to Birth
DROP VIEW IF EXISTS ChildSortDate
;
CREATE TEMP VIEW ChildSortDate
AS
SELECT * FROM ChildBirthSortDate
UNION
SELECT * FROM ChildChrisSortDate
WHERE ChildID IN (SELECT ChildID FROM ChildChrisSortDate EXCEPT SELECT ChildID FROM ChildBirthSortDate)
; 

/* Check for duplication
-- SELECT ChildID, COUNT() FROM ChildSortDate GROUP BY ChildID
;
*/

-- tie each child's SortDate to each of his Families and put in sorted order in this view
DROP VIEW IF EXISTS ChildSort
;
CREATE TEMP VIEW ChildSort
AS
SELECT FamilyID, ChildTable.ChildID, SortDate FROM ChildTable
JOIN ChildSortDate USING(ChildID)
ORDER BY FamilyID, SortDate
;

/*
 create a temp table from the sorted view (need an auto-increment INTEGER PRIMARY KEY n/a
  in a View) and seed the ChildOrder with 1.
*/
DROP TABLE IF EXISTS xChildOrder
;
CREATE TEMP TABLE xChildOrder(RecID INTEGER PRIMARY KEY, FamilyID INTEGER, ChildID INTEGER, SortDate INTEGER, ChildOrder INTEGER)
;
INSERT INTO xChildOrder
SELECT null, *, 1 FROM ChildSort
;

/*
 create a view which derives the ChildOrder for non-first children.
 Uses the RecID PK from the sorted temp table to calculate the ChildOrder value
*/
DROP VIEW IF EXISTS ChildOrder
;
CREATE TEMP VIEW ChildOrder
AS
SELECT C1.RecID
     , C1.RecID +1 - (SELECT RecID FROM xChildOrder C2 WHERE C1.FamilyID = C2.FamilyID) AS ChildOrder 
     FROM xChildOrder C1 
     JOIN xChildOrder C0 
     USING(FamilyID)
     WHERE C1.RecID -1 = C0.RecID
;

-- replace the ChildOrder value for non-first children in the temp table
-- with that from the view
UPDATE xChildOrder
SET ChildOrder =
    (
     SELECT C1.RecID +1 - (SELECT RecID FROM xChildOrder C2 WHERE C1.FamilyID = C2.FamilyID) AS ChildOrder 
     FROM xChildOrder C1 
     JOIN xChildOrder C0 
     USING(FamilyID)
     WHERE C1.RecID -1 = C0.RecID
     AND xChildOrder.RecID = C1.RecID
    )
WHERE RecID IN (SELECT RecID FROM ChildOrder)
;

/* To prevent the order of families having undated children 
or lacking a birth-type fact from being reordered, 
we need to identify those familes
and delete them from the temp xChildOrder table 
*/

DROP VIEW IF EXISTS ExcludeFamily
;
CREATE TEMP VIEW ExcludeFamily
AS
SELECT FamilyID, ChildID FROM ChildTable C
LEFT JOIN xChildOrder CO USING(FamilyID, ChildID)
WHERE 
( CO.ChildID ISNULL
  OR
  CO.SortDate > 8443686368545079308
 )
;

-- Remove these families from xChildOrder
DELETE FROM xChildOrder
WHERE FamilyID IN
 (SELECT DISTINCT FamilyID FROM ExcludeFamily)
;


-- Now we can update the ChildOrder in the database ChildTable
/* 
Reset is unnecessary - used for testing - leave those 
lacking a Birth/Chris SortDate and whose order has 
been manually set alone. 
-- reset child order to order added
UPDATE ChildTable SET ChildOrder = 0
;
*/
UPDATE ChildTable
SET ChildOrder =
    (SELECT ChildOrder 
     FROM xChildOrder xC 
     WHERE ChildTable.ChildID = xC.ChildID 
     AND ChildTable.FamilyID = xC.FamilyID
     )
WHERE ChildID IN (SELECT DISTINCT ChildID FROM xChildOrder)
;
  
SELECT 'Script completed without execution error' AS Status
;