-- Birth-AddFromChristenBaptism.sql
/* 2017-02-11 Tom Holden ve3meo
For persons having no Birth event but with either a Christen
or Baptism event, adds a Birth event duplicating the CHR or BAPM
event. 

It does not copy citations or media and does not modify
the Date or SortDate. If a person has both CHR and BAPM, it 
copies CHR. If a person has multiple CHR or BAPM events,
it copies only one which with priority given to IsPrimary 
and secondly to lowest SortDate.
*/

DROP VIEW IF EXISTS vPersonsWithBirth
;

CREATE TEMP VIEW vPersonsWithBirth AS
SELECT DISTINCT OwnerID AS PersonID FROM EventTable WHERE EventType = 1
;

DROP VIEW IF EXISTS vPersonsWithChristenNotBirth
;

CREATE TEMP VIEW vPersonsWithChristenNotBirth AS
SELECT DISTINCT OwnerID AS PersonID FROM EventTable WHERE EventType = 3
EXCEPT SELECT PersonID FROM vPersonsWithBirth
;

DROP VIEW IF EXISTS vPersonsWithBaptismNotBirthNorChristen
;

CREATE TEMP VIEW vPersonsWithBaptismNotBirthNorChristen AS
SELECT DISTINCT OwnerID AS PersonID FROM EventTable WHERE EventType = 7
EXCEPT SELECT PersonID FROM vPersonsWithBirth UNION SELECT PersonID FROM vPersonsWithChristenNotBirth
;

DROP VIEW IF EXISTS vChristenBaptismNoBirth
;

CREATE TEMP VIEW vChristenBaptismNoBirth AS
SELECT * FROM EventTable 
WHERE EventType IN (3, 7)
AND OwnerID IN (SELECT PersonID FROM vPersonsWithBaptismNotBirthNorChristen)
ORDER BY EventType, OwnerID, IsPrimary, SortDate DESC
;

DROP VIEW IF EXISTS vNewBirth
;

CREATE TEMP VIEW vNewBirth AS
SELECT NULL AS EventID
      ,1 AS EventType
      ,OwnerType
      ,OwnerID
      ,FamilyID
      ,PlaceID
      ,SiteID
      ,Date
      ,SortDate
      ,IsPrimary
      ,IsPrivate
      ,Proof
      ,Status
      ,EditDate
      ,Sentence
      ,Details
      ,Note
       
FROM vChristenBaptismNoBirth 
GROUP BY EventType, OwnerID
;

INSERT INTO EventTable
SELECT * FROM vNewBirth
;
-- DONE
/*
The TEMP VIEWS will all be dropped when the database is closed but
you can select and execute the following if you wish to drop them
while keeping the database open for further operations.

DROP VIEW IF EXISTS vPersonsWithBirth
;
DROP VIEW IF EXISTS vPersonsWithChristenNotBirth
;
DROP VIEW IF EXISTS vPersonsWithBaptismNotBirthNorChristen
;
DROP VIEW IF EXISTS vChristenBaptismNoBirth
;
DROP VIEW IF EXISTS vNewBirth
;
*/