-- Facts-RefNos_person_spouse_parents.sql
/*
2014-01-21 Tom Holden ve3meo

Lists persons with their spouse and parents with RefNo values for each

Creates a temporary table xNamesRefNoTable
*/

DROP TABLE IF EXISTS xNamesRefNoTable;
	
CREATE TEMP TABLE IF NOT EXISTS xNamesRefNoTable AS
	SELECT *
	FROM (
		SELECT Per.PersonID AS RIN
			,UPPER(Nam.Surname) || ' ' || Nam.Suffix || ', ' || Nam.[Prefix] || ' ' || Nam.
			Given || ' -' || Per.PersonID AS NAME
		FROM PersonTable Per
		INNER JOIN NameTable Nam ON Per.PersonID = Nam.OwnerID
			AND + Nam.IsPrimary -- excludes Alternate Names;
		) NATURAL
	INNER JOIN (
		SELECT Per.PersonID AS RIN
			,CAST(Evt.Details AS TEXT) AS RefNo
		FROM PersonTable Per
		LEFT JOIN EventTable Evt ON Per.PersonID = Evt.[OwnerID]
			AND Evt.EventType = 35 
			-- the FactTypeID for the Reference Number fact is 35
			AND Evt.OwnerType = 0 
			-- restricts to events for individuals; redundant in this case because the FactType is so restricted
		);



SELECT RINS.RIN, Person.[RefNo], Person.[NAME], Spouse.[RefNo], Spouse.[NAME], Father.[RefNo], Father.[Name], Mother.[RefNo], Mother.Name
FROM
(
SELECT Pert.PersonID AS RIN, Spouses.SpouseID, Parents.[FatherID], Parents.MotherID
FROM PersonTable Pert

LEFT JOIN

(
-- Get RIN of Spouse (MotherID)
SELECT Per.PersonID AS RIN, Fam.[MotherID] AS SpouseID
FROM PersonTable Per
INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[FatherID] 
UNION
-- Get RIN of Spouse (FatherID)
SELECT Per.PersonID AS RIN, Fam.[FatherID] AS SpouseID
FROM PersonTable Per
INNER JOIN FamilyTable Fam ON Per.PersonID = Fam.[MotherID] 
) AS Spouses
ON Pert.[PersonID] = Spouses.RIN

LEFT JOIN

(
-- Get RINs of Parents
SELECT Per.PersonID AS RIN, Fam.[FatherID], Fam.[MotherID]
FROM PersonTable Per
LEFT JOIN ChildTable Child ON Per.PersonID = Child.[ChildID]
INNER JOIN FamilyTable Fam USING(FamilyID)
) AS Parents
ON Pert.[PersonID] = Parents.RIN
) AS RINS
NATURAL JOIN xNamesRefNoTable AS Person
LEFT JOIN xNamesRefNoTable AS Spouse ON RINS.SpouseID = Spouse.[RIN]
LEFT JOIN xNamesRefNoTable AS Father ON FatherID = Father.[RIN]
LEFT JOIN xNamesRefNoTable AS Mother ON MotherID = Mother.[RIN]
;