--  RM8Facts-SplitSharedToIndiv.sql 
--
-- Original RM7 sql = 
--   2012-12-19 Tom Holden ve3meo
-- Revisions to Tom's Script (km)
---- 2022-06 Removed section related to updating the AncestryTable. Additions should not be needed to the AncestryTable because the new individual facts reuse the original shared citation.
---- 2022-01 Updated for RM8 Table Changes. Citation of shared event is now reused for the new split events.
---- 2020 - The following edits were done for an rm7 version of this script
------ Insert new events into LATable if shared event was there. Script does not address media linked to LATable. 
------ Prepend Witness Note to Event Note (if it exists) in the family and non-family segments of the script so as not to lose the shared event note.
------ Retain some extra fact types
------ Removed RIN of original person from Description of new split event.
------ Note that this version of Tom's script does not include the 2016 BEGIN/COMMIT update

/* 
2012-12-21 rev. divided script into separate files. 
  
Run this query to convert shared events to Individual 'Share' events,
except Census, Residence fact types whose Fact Name is preserved. 

Creates a new Individual Fact Type named and abbreviated 'Share', without the quotes,
if none exists.

Witness not in File and their note is appended to the shared event note
and enclosed in privacy brackets, thus {** ... **}.

See end of script for steps to UNDO the changes

*/

-- DELETE FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share';
-- Create Share facttype if it does not exist
INSERT OR IGNORE INTO FactTypeTable

SELECT 
  (
   SELECT FactTypeID FROM FactTypeTable
   WHERE LOWER(Abbrev) LIKE 'share'
   ) AS FactTypeID,
  0 AS OwnerType,
  'Share' AS Name,
  'Share' AS Abbrev,
  'EVEN' AS GedcomTag,
  1 AS UseValue,
  1 AS UseDate,
  1 AS UsePlace,
  '[person] was involved as a< [Desc]>< [Date]>< [PlaceDetails]>< [Place]>.' AS Sentence,
  -1 AS Flags,
  NULL AS UTCModDate
;


-- keep track of the current last key rowid's before the adding of the Share events
DROP TABLE IF Exists zTmpShareSplit
;

CREATE TABLE IF NOT EXISTS zTmpShareSplit
(TableName TEXT, Maxrowid INTEGER)
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'FactTypeTable', 
  (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share')
; 

INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'EventTable', MAX(EventID) FROM EventTable
;

INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'CitationLinkTable', MAX(LinkID) FROM CitationLinkTable
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'MediaLinkTable', MAX(LinkID) FROM MediaLinkTable
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'URLTable', MAX(LinkID) FROM URLTable
;

-- Split Shared non-family facts
INSERT INTO EventTable

SELECT
  NULL AS EventID, 
  CASE E.EventType
    WHEN 18 THEN 18  -- Census to Census
    WHEN 311 THEN 18 -- Census (family) to Census
    WHEN 29 THEN 29  -- Residence to Residence
    WHEN 310 THEN 29 -- Residence (family) to Residence
    WHEN 17  THEN 17 -- Immigration to Immigration  added 2020km
    WHEN 27  THEN 27 -- Property to Property  added 2020km
    ELSE (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') -- all others to Share
  END
    AS EventType, 
  E.OwnerType, W.PersonID, E.FamilyID, E.PlaceID, E.SiteID, 
  E.Date, E.SortDate, 
  E.EventID AS IsPrimary, -- temp store for Principal's EventID for citations, later set to 0 
  E.IsPrivate, E.Proof, E.Status, 
  '' AS Sentence,
  -- LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N.Given || ' ' || N.Surname || '-' || N.OwnerID AS Details,   -- 2020 change km
  LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N.Given || ' ' || N.Surname AS Details,
--  W.Note AS Note    -- 2020 change km
  TRIM(W.Note || ' ' || E.Note) AS Note,   -- 2020 change km
  E.UTCModDate
FROM
  WitnessTable W 
  INNER JOIN EventTable E USING(EventID)
  INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
  INNER JOIN RoleTable R ON W.Role = R.RoleID
  INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE E.OwnerType = 0 -- Person
AND W.PersonID > 0 -- Person is not just a name in the WitnessTable but a person in the database tree
; 

-- Split Shared family facts
INSERT INTO EventTable

SELECT
  NULL AS EventID, 
  CASE E.EventType
    WHEN 18 THEN 18  -- Census to Census
    WHEN 311 THEN 18 -- Census (family) to Census
    WHEN 29 THEN 29  -- Residence to Residence
    WHEN 310 THEN 29 -- Residence (family) to Residence
    WHEN 17  THEN 17 -- Immigration to Immigration  added 2020km
    WHEN 27  THEN 27 -- Property to Property  added 2020km
    ELSE (SELECT FactTypeID FROM FactTypeTable WHERE LOWER(Abbrev) LIKE 'share') -- all others to Share
  END
    AS EventType, 
  0 AS OwnerType, W.PersonID, E.FamilyID, E.PlaceID, E.SiteID, 
  E.Date, E.SortDate, 
  E.EventID AS IsPrimary, -- temp store for Principal's EventID for citations, later set to 0 
  E.IsPrivate, E.Proof, E.Status, 
  '' AS Sentence,
  -- LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N1.Given || ' ' || N1.Surname || '-' || N1.OwnerID || ' & ' || N2.Given || ' ' || N2.Surname || '-' || N2.OwnerID AS Details, -- 2020 change km
  LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N1.Given || ' ' || N1.Surname || ' & ' || N2.Given || ' ' || N2.Surname AS Details,
--  W.Note AS Note      -- 2020 change km
  TRIM(W.Note || ' ' || E.Note) AS Note,       -- 2020 change km
  E.UTCModDate 
FROM
  WitnessTable W 
  INNER JOIN EventTable E USING(EventID)
  INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
  INNER JOIN RoleTable R ON W.Role = R.RoleID
  INNER JOIN FamilyTable F ON E.OwnerID = F.FamilyID
  LEFT JOIN NameTable N1 ON F.FatherID = N1.OwnerID AND +N1.IsPrimary -- to get Husband Names
  LEFT JOIN NameTable N2 ON F.MotherID = N2.OwnerID AND +N2.IsPrimary -- to get Wife Names
WHERE E.OwnerType = 1 -- Family
AND W.PersonID > 0 -- Person is not just a name in the WitnessTable but a person in the database tree
; 

-- Copy citation from shared event to Share events
INSERT INTO CitationLinkTable
SELECT
  NULL AS LinkID, 
  CL.CitationID,
  CL.OwnerType, 
  E.EventID AS OwnerID, 
  '' AS SortOrder,
  CL.Quality, CL.IsPrivate,
  CL.LinkID AS Flags, -- temp use of Flags to link media and webtags to new citation Link, set to 0 later 
  Null AS UTCModDate
FROM CitationLinkTable CL
INNER JOIN CitationTable C ON C.CitationID = CL.CitationID
INNER JOIN EventTable E ON CL.OwnerID = E.IsPrimary 
AND CL.OwnerType = 2
AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')
;

-- Copy Media tag from shared event to Share events

INSERT INTO MediaLinkTable

SELECT
  NULL AS LinkID, ML.MediaID, ML.OwnerType, 
  E.EventID AS OwnerID, 
  ML.IsPrimary, ML.Include1, ML.Include2, ML.Include3, ML.Include4, ML.SortOrder, 
  ML.RectLeft, ML.RectTop, ML.RectRight, ML.RectBottom,  
-- ML.Note, ML.Caption, ML.RefNumber, ML.Date, ML.SortDate, ML.Description
  ML.Comments,
  Null AS UTCModDate
FROM MediaLinkTable ML
INNER JOIN EventTable E ON ML.OwnerID = E.IsPrimary -- remember IsPrimary temp holds the shared EventID 
AND ML.OwnerType =2
AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')
;

-- Copy Media tag from citation of shared event to citation of Share events

INSERT INTO MediaLinkTable

SELECT
  NULL AS LinkID, ML.MediaID, ML.OwnerType, 
  CL.LinkID AS OwnerID, 
  ML.IsPrimary, ML.Include1, ML.Include2, ML.Include3, ML.Include4, ML.SortOrder, 
  ML.RectLeft, ML.RectTop, ML.RectRight, ML.RectBottom, 
--  ML.Note, ML.Caption, ML.RefNumber, ML.Date, ML.SortDate, ML.Description
  ML.Comments,
  Null AS UTCModDate
FROM MediaLinkTable ML
INNER JOIN CitationLinkTable CL ON ML.OwnerID = CL.Flags -- remember Flags temp holds the CitationLinkID of the shared Event 
AND ML.OwnerType =4
AND CL.LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationLinkTable')
;

-- Copy WebTag from citation of shared event to citation of Share events

INSERT INTO URLTable

SELECT
  NULL AS LinkID, 4 AS OwnerType, CL.LinkID, U.LinkType, U.Name, U.URL, U.Note,
  Null AS UTCModDate
FROM URLTable U
INNER JOIN CitationLinkTable CL ON U.OwnerID = CL.Flags -- remember Flags temp holds the CitationLinkID of the shared Event  
AND U.OwnerType = 4
AND CL.LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationLinkTable')
;

-- Append Name and Note from witness not in database to the original shared event note

UPDATE EventTable
SET Note =
-- SELECT 
  Note || 
-- SELECT  
  '{** ' || CAST(X'0A0D' AS TEXT) ||
  (SELECT W.Given || ' ' || W.Surname || ' was ' || LOWER(R.RoleName) || '. '
    || W.Note 
   FROM WitnessTable W
   INNER JOIN RoleTable R ON W.Role = R.RoleID
   WHERE EventTable.EventID = W.EventID
   AND W.PersonID = 0
  ) || CAST(X'0A0D' AS TEXT) || '**}'
--  FROM EventTable 
  WHERE EventTable.EventID 
  IN (SELECT EventID FROM WitnessTable WHERE PersonID = 0)
;

-- Reset EventTable.IsPrimary temp used for Shared EventID to 0
UPDATE EventTable SET IsPrimary = 0
WHERE EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')
;

-- Reset CitationLinkTable.Flags temp used for Shared Event Citation LinkID to 0
UPDATE CitationLinkTable SET Flags = 0
WHERE LinkID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationLinkTable')
;

-- Make a backup of the WitnessTable before wiping it
DROP TABLE IF EXISTS WitnessTableSafe
;
CREATE TABLE IF NOT EXISTS WitnessTableSafe
AS
SELECT * FROM WitnessTable
;

/*
 DONE! Shared events still shared but now the sharers also have individual unshared events
 with their particular note.  
 When SQLiteSpy closes the database, any temp tables in memory will be gone
 but WitnessTableSafe will remain in the database and could be used to restore the Sharings
 before running the script again. So, too, remains zTmpShareSplit. Facts-Split-Undo.sql
 depends on their presence to reverse the splits.

 Facts-Split-HideTracks.sql drops these tables from the database, no going back.

*/