-- Facts-SplitSharedToIndiv.sql
-- 2012-12-19 Tom Holden ve3meo
/* 
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
;


-- 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
;
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
    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, 
  '' AS Sentence,
  LOWER(R.RoleName) || ' in the ' || LOWER(F.Name) ||  ' of ' || N.Given || ' ' || N.Surname || '-' || N.OwnerID AS Details,
  W.Note AS Note
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
    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, 
  '' 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
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 CitationTable
SELECT
  NULL AS CitationID, 
  C.OwnerType, 
  C.SourceID, 
  E.EventID AS OwnerID, 
  C.Quality, C.IsPrivate, C.Comments, C.ActualText, C.RefNumber, 
  C.CitationID AS Flags, -- temp use of Flags to link media and webtags to new citations, set to 0 later 
  C.Fields
FROM CitationTable C
INNER JOIN EventTable E ON C.OwnerID = E.IsPrimary 
AND C.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
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, 
  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'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 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.

*/