-- Facts-SplitSharedToIndiv-RM9.sql
-- 2023-03-08 Tom Holden ve3meo
/*
adapted from 2012-12-19 
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,
  (SELECT julianday('now') - 2415018.5) AS UTCModDate
; -- tested RM9


-- 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 'CitationTable', MAX(CitationID) FROM CitationTable -- is this even needed for RM9?
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'MediaLinkTable', MAX(LinkID) FROM MediaLinkTable
;
INSERT OR REPLACE INTO zTmpShareSplit
SELECT 'URLTable', MAX(LinkID) FROM URLTable
; -- tested RM9. Does it need expansion for Tasks, Groups?


-- 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
    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, -- E.EditDate, not RM9 
  '' AS Sentence,
  LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N.Given || ' ' || N.Surname || '-' || N.OwnerID AS Details,
  W.Note AS Note,
  (SELECT julianday('now') - 2415018.5) AS UTCModDate -- RM9

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
; -- tested RM9

-- 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
    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, -- E.EditDate, not in RM9
  '' 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,
  W.Note AS Note,
  (SELECT julianday('now') - 2415018.5) AS UTCModDate -- RM9

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 links from shared event to Share events
-- major rev for RM9
INSERT INTO CitationLinkTable
SELECT DISTINCT
  NULL AS LinkID, 
  CL.CitationID, 
  CL.OwnerType, 
  E.EventID AS OwnerID, 
  NULL AS SortOrder,   
  CL.Quality, CL.IsPrivate, -- C.Comments, C.ActualText, C.RefNumber, 
--  CL.CitationID AS Flags, -- temp use of Flags to link media and webtags to new citations, set to 0 later
  CL.Flags, -- since CitationID already in result for RM9, temp use of Flags is unneeded?
  (SELECT julianday('now') - 2415018.5) AS UTCModDate -- RM9
 
FROM CitationLinkTable CL
JOIN EventTable E ON CL.OwnerID = E.IsPrimary 
AND CL.OwnerType = 2
AND E.EventID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'EventTable')
; --tested RM9

-- 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 -- not RM9
  ML.Comments, -- RM9
  (SELECT julianday('now') - 2415018.5) AS UTCModDate -- RM9
  
FROM MediaLinkTable ML
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')
; -- tested RM9

/* NOT for RM9
-- Copy Media tag from citation of shared event to citation of Share events

INSERT INTO MediaLinkTable

SELECT
  NULL AS LinkID, ML.MediaID, ML.OwnerType, 
  C.CitationID 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
FROM MediaLinkTable ML
INNER JOIN CitationTable C ON ML.OwnerID = C.Flags -- remember Flags temp holds the CitationID of the shared Event 
AND ML.OwnerType =4
AND C.CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable')

;

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

INSERT INTO URLTable

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

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

UPDATE EventTable
SET Note =
-- SELECT 
  Note || 
-- SELECT 
  CAST(X'0D' AS TEXT) || -- line feed 
  '{** ' || CAST(X'0D' AS TEXT) ||
  (SELECT GROUP_CONCAT(W.Given || ' ' || W.Surname || ' was ' || LOWER(R.RoleName) || '. '
    || W.Note, CAST(X'0D' AS TEXT)) 
           AS AppendNote 
   FROM WitnessTable W
   LEFT JOIN RoleTable R ON W.Role = R.RoleID
   WHERE EventTable.EventID = W.EventID
   AND W.PersonID = 0
   GROUP BY W.EventID 
  ) || CAST(X'0D' AS TEXT) || '**}'
  ,UTCModDate=  (SELECT julianday('now') - 2415018.5) -- RM9

--  FROM EventTable 
  WHERE EventTable.EventID 
  IN (SELECT DISTINCT EventID FROM WitnessTable WHERE PersonID = 0)
; -- tested RM9

-- 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')
; -- tested RM9

/* NOT for RM9
-- Reset CitationTable.Flags temp used for Shared Event CitationID to 0
UPDATE CitationTable SET Flags = 0
WHERE CitationID > (SELECT Maxrowid FROM zTmpShareSplit WHERE TableName LIKE 'CitationTable')
;
*/

-- 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.

*/