-- SpouseSort.sql
/*
2016-01-10 Tom Holden ve3meo

Sets the spouse order for persons with multiple marriages
in order of SortDate.
Ignores spouses with no marriage event; for a person with both
a spouse with a marriage fact and a spouse without, the order
is unaffected. However, if he/she has two spouses with marriage facts,
then they will sort after the factless spouse. 
Overrides manually sorted spouses.
*/

BEGIN
;
DROP VIEW IF EXISTS xvHusbMarriages
;
-- view of husband's marriage count
CREATE TEMP VIEW xvHusbMarriages
AS
SELECT FatherID
     , COUNT() AS Marriages
FROM EventTable Evt 
INNER JOIN FamilyTable Fam ON Evt.OwnerID = Fam.FamilyID
WHERE EventType = 300
GROUP BY FatherID
;

DROP VIEW IF EXISTS xvWifeMarriages
;
-- view of wife's marriage count
CREATE TEMP VIEW xvWifeMarriages
AS
SELECT MotherID
     , COUNT() AS Marriages
FROM EventTable Evt 
INNER JOIN FamilyTable Fam ON Evt.OwnerID = Fam.FamilyID
WHERE EventType = 300
GROUP BY MotherID
;

;
-- this table puts husband's wives in chronological order
CREATE TEMP TABLE xWifeOrder
AS
SELECT Fam.FamilyID
       , FatherID
       , MotherID
       , SortDate
       , 0 AS WifeOrder
FROM EventTable Evt 
INNER JOIN FamilyTable Fam ON Evt.OwnerID = Fam.FamilyID
WHERE EventType = 300
AND FatherID IN
( 
SELECT FatherID FROM xvHusbMarriages WHERE Marriages > 1
)
ORDER BY FatherID, SortDate
;
-- assign the wife order number (1,2,...) 
UPDATE xWifeOrder
SET WifeOrder = (SELECT MAX(WifeOrder)+1 FROM xWifeOrder X WHERE xWifeOrder.FatherID = X.FatherID)
; 

;
-- this table puts wife's husbands in chronological order
CREATE TEMP TABLE xHusbOrder
AS
SELECT Fam.FamilyID
       , FatherID
       , MotherID
       , SortDate
       , 0 AS HusbOrder
FROM EventTable Evt 
INNER JOIN FamilyTable Fam ON Evt.OwnerID = Fam.FamilyID
WHERE EventType = 300
AND MotherID IN
( 
SELECT MotherID FROM xvWifeMarriages WHERE Marriages > 1
)
ORDER BY MotherID, SortDate
;
-- assign the husband order number (1,2,...) 
UPDATE xHusbOrder
SET HusbOrder = (SELECT MAX(HusbOrder)+1 FROM xHusbOrder X WHERE xHusbOrder.MotherID = X.MotherID)
; 
-- this completes the preparatory stuff in the temp views and tables which can be inspected
COMMIT
;

BEGIN
;
-- transfer WifeOrder from temp table to FamilyTable
UPDATE OR ROLLBACK FamilyTable
SET WifeOrder = (SELECT WifeOrder FROM xWifeOrder X WHERE FamilyTable.FamilyID = X.FamilyID)
WHERE FamilyID IN (SELECT DISTINCT FamilyID FROM xWifeOrder ORDER BY FamilyID)
;
-- UPDATE FamilyTable SET WifeOrder = 0;  -- reset order to as entered

-- transfer HusbOrder from temp table to FamilyTable
UPDATE OR ROLLBACK FamilyTable
SET HusbOrder = (SELECT HusbOrder FROM xHusbOrder X WHERE FamilyTable.FamilyID = X.FamilyID)
WHERE FamilyID IN (SELECT DISTINCT FamilyID FROM xHusbOrder ORDER BY FamilyID)
;
-- UPDATE FamilyTable SET HusbOrder = 0;  -- reset order to as entered
COMMIT
;