/* Names-Swap_AltBirth_to_Primary.sql
   2020-05-09 Tom Holden ve3meo
rev2020-05-10 restricted to swapping only with undefined-type primary names

Turns the IsPrimary flag on for alternate names of NameType=1 (Birth)
 for persons with an undefined-type Primary name.
Turns off the flag for the former primary name.
Does so only for persons with only 1 Birth Type Alt Name
- those with multiple names of type Birth need to have that resolved
  to one only for the script to swap Primary. Script lists those people.
*/

-- list persons having a birth-type Alt Name AND an undefined-type Prim Name
DROP Table IF EXISTS AltNameBirth
;
CREATE TEMP TABLE AltNameBirth AS
SELECT NameID, OwnerID FROM NameTable 
WHERE NameType=1 AND NOT IsPrimary
AND OwnerID IN
  (SELECT OwnerID FROM NameTable 
   WHERE NameType=0 AND IsPrimary
   ) 
;
-- list persons and their count of birth-type names
DROP VIEW IF EXISTS CountBirthNames

;
CREATE TEMP VIEW CountBirthNames AS
SELECT OwnerID, COUNT(OwnerID) AS Qty  FROM NameTable
WHERE NameType = 1
GROUP BY OwnerID
;

-- list persons with more than 1 birth-type need
DROP VIEW IF EXISTS MultBirthNames
;
CREATE TEMP VIEW MultBirthNames AS
SELECT OwnerID FROM CountBirthNames 
WHERE Qty > 1
;

-- list persons and their undefined-type Prim NameID and birth-type Alt NameID
-- except those with multiple birth-type names
DROP TABLE IF EXISTS PersonsAltBirthNames
;
CREATE TEMP TABLE PersonsAltBirthNames AS
SELECT OwnerID, N.NameID AS PrimNameID, A.NameID AS AltNameID FROM NameTable N
JOIN AltNameBirth A USING(OwnerID) 
WHERE N.IsPrimary
  AND OwnerID NOT IN (SELECT OwnerID FROM MultBirthNames)
   -- exclude those with multiple alt birth names
; 

/* test that the same person does not have more than one possible swap
SELECT COUNT(), * FROM PersonsAltBirthNames
GROUP BY OwnerID
;
*/

-- set the birth-type Alt name as PRIMARY
UPDATE NameTable SET IsPrimary = 1 
WHERE NameID IN (SELECT AltNameID FROM PersonsAltBirthNames ORDER BY AltNameID)
;

-- set the undefined-type Primary name as Alt 
UPDATE NameTable SET IsPrimary = 0 
WHERE NameID IN (SELECT PrimNameID FROM PersonsAltBirthNames ORDER BY PrimNameID)
;

-- report of persons with multiple birth-type names left untouched
SELECT '***' AS RIN,'Persons with multiple birth names' Surname,'Resolve ***' Given
UNION ALL 
SELECT OwnerID, Surname, Given FROM MultBirthNames
NATURAL JOIN NameTable 
WHERE IsPrimary
;

-- end of script --