-- SpouseSort2.sql
/*
2016-01-12 Tom Holden ve3meo

Sets the spouse order for persons with multiple spouse
in order of SortDate for the earlier of marriage or childbirth.
For a person with both
a spouse with a marriage fact or child and a spouse without both, the order
is unaffected. However, if he/she has two spouses with either fact,
then they will sort after the factless spouse. 
Overrides manually sorted spouses if the above condition is met.
*/

BEGIN
;
-- count how many spouses each husband has
DROP VIEW IF EXISTS xvWifeCount
;
CREATE TEMP VIEW xvWifeCount
AS
SELECT FatherID, COUNT() AS WifeQty FROM FamilyTable
GROUP BY FatherID
;

-- count how many spouses each wife has
DROP VIEW IF EXISTS xvHusbCount
;
CREATE TEMP VIEW xvHusbCount
AS
SELECT MotherID, COUNT() AS HusbQty FROM FamilyTable
GROUP BY MotherID
;

-- list married wives of husbands with multiple spouses
DROP VIEW IF EXISTS xvHusbSpouses
;
CREATE TEMP VIEW xvHusbSpouses
AS
SELECT Fam.FamilyID, Fam.FatherID, Fam.MotherID, Evt.SortDate FROM EventTable Evt
INNER JOIN FamilyTable Fam
ON Evt.OwnerID = Fam.FamilyID
WHERE EventType = 300
AND Fam.FatherID IN
(SELECT FatherID FROM xvWifeCount WHERE WifeQty > 1)
ORDER BY FatherID, SortDate
;

-- list married husbands of wives with multiple spouses
DROP VIEW IF EXISTS xvWifeSpouses
;
CREATE TEMP VIEW xvWifeSpouses
AS
SELECT Fam.FamilyID, Fam.FatherID, Fam.MotherID, Evt.SortDate FROM EventTable Evt
INNER JOIN FamilyTable Fam
ON Evt.OwnerID = Fam.FamilyID
WHERE EventType = 300
AND Fam.MotherID IN
(SELECT MotherID FROM xvHusbCount WHERE HusbQty > 1)
ORDER BY MotherID, SortDate
;

-- list unmarried child-bearing wives of husbands with multiple spouses
DROP VIEW IF EXISTS xvUnmarriedMothers
;
CREATE TEMP VIEW xvUnmarriedMothers
AS
SELECT Fam.FamilyID, Fam.FatherID, Fam.MotherID, MIN(Evt.SortDate) SortDate FROM EventTable Evt
INNER JOIN ChildTable Child
ON Evt.OwnerID = Child.ChildID
INNER JOIN FamilyTable Fam
ON Child.FamilyID = Fam.FamilyID
WHERE 
(EventType = 1  --Birth
 OR EventType = 3     --Christen
)
AND Fam.FatherID IN
(SELECT FatherID FROM xvWifeCount WHERE WifeQty > 1)
AND Fam.FamilyID NOT IN (SELECT DISTINCT OwnerID FROM EventTable WHERE EventType = 300) --exclude couples with marriage event
GROUP BY Fam.FamilyID
ORDER BY FatherID, SortDate
;

-- list unmarried husbands of child-bearing wives with multiple spouses
DROP VIEW IF EXISTS xvUnmarriedFathers
;
CREATE TEMP VIEW xvUnmarriedFathers
AS
SELECT Fam.FamilyID, Fam.FatherID, Fam.MotherID, MIN(Evt.SortDate) SortDate FROM EventTable Evt
INNER JOIN ChildTable Child
ON Evt.OwnerID = Child.ChildID
INNER JOIN FamilyTable Fam
ON Child.FamilyID = Fam.FamilyID
WHERE 
(EventType = 1  --Birth
 OR EventType = 3     --Christen
)
AND Fam.MotherID IN
(SELECT MotherID FROM xvHusbCount WHERE HusbQty > 1)
AND Fam.FamilyID NOT IN (SELECT DISTINCT OwnerID FROM EventTable WHERE EventType = 300) --exclude couples with marriage event
GROUP BY Fam.FamilyID
ORDER BY MotherID, SortDate
;
 
DROP TABLE IF EXISTS xWifeOrder
;
-- this table puts husband's wives in chronological order
CREATE TEMP TABLE xWifeOrder
AS

SELECT *, 0 AS WifeOrder
FROM
-- combine married and unmarried wives of husbands
(
SELECT * FROM xvHusbSpouses
UNION
SELECT * FROM xvUnMarriedMothers
)
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)
; 

DROP TABLE IF EXISTS xHusbOrder
;
-- this table puts wife's husbands in chronological order
CREATE TEMP TABLE xHusbOrder
AS

SELECT *, 0 AS HusbOrder
FROM
-- combine married and unmarried wives of husbands
(
SELECT * FROM xvWifeSpouses
UNION
SELECT * FROM xvUnMarriedFathers
)
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
;
--------------------------------------------------------

-- now update the RootsMagic database's spouse orders
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
;

SELECT 'Script finished without execution error.' AS Status
UNION ALL
SELECT 'Inspect database with RootsMagic.' AS Status
;
-- End of script