/* SharableEvents-Convert.sql
   2011-12-17 ve3meo
   2011-12-19 rev to convert existing sharers of an event that will be deleted to share another event
              to share the same event
   Combines two overall procedures:
    1. Convert Indiv Events matching a Family Event to witnesses of the family event
    2. Convert each group of matching Indiv Events to witnesses of the senior member of the group.
    DOES NOT DEAL WITH MEDIA ATTACHED TO A CONVERTED EVENT NOR DOES IT ASSIGN ROLES
*/


-- 1. Convert Indiv Events that duplicate Family Events to sharers of same -----
-- first build a temporary table
DROP TABLE IF EXISTS TmpWitness ;
CREATE TEMP TABLE IF NOT EXISTS TmpWitness 
AS SELECT 
  NULL AS WitnessID , 
  FamEvent.EventID AS SharedEventID , 
  EventTable.OwnerID AS PersonID , 
  0 AS WitnessOrder , 
  /* CASE FamEvent.EventType
    WHEN 1003 THEN 70
    WHEN 1004 THEN 71
    WHEN 1005 THEN 72
   END AS Role,
   */
  NULL AS Role , 
  '' AS Sentence , 
  EventTable.Note AS Note , 
  '' AS Given , 
  '' AS Surname , 
  '' AS Prefix , 
  '' AS Suffix , 
  FamEvent.OwnerID AS FamilyID , 
  EventTable.EventID AS OldEventID ,
  WitnessTable.WitnessID AS OldWitnessID, 
  CitationID , 
  FamEvent.PlaceID , 
  FamEvent.SiteID , 
  EventTable.PlaceID , 
  EventTable.SiteID 
FROM 
  ( 
    SELECT -- Family Events
      EventTable.OwnerID , 
      EventID , 
      EventType , 
      FactTypeTable.Name AS FactName , 
      Date , 
      PlaceID , 
      SiteID , 
      Details 
    FROM 
      EventTable INNER JOIN 
      FactTypeTable ON EventType = FactTypeID 
    WHERE 
      EventTable.OwnerType = 1 AND EventTable.Details <> '' 
  ) AS FamEvent LEFT JOIN 
  EventTable USING 
    ( 
      Date , 
      PlaceID , 
      SiteID , 
      Details 
    ) LEFT JOIN 
  CitationTable ON EventTable.EventID = CitationTable.OwnerID INNER JOIN 
  FactTypeTable ON EventTable.EventType = FactTypeTable.FactTypeID LEFT JOIN
  WitnessTable  ON EventTable.EventID = WitnessTable.EventID 
WHERE 
  EventTable.OwnerType = 0 AND CitationTable.OwnerType = 2 AND FamEvent.FactName LIKE FactTypeTable.Name || '%' 
;

/* 
     Reassign sharers of an event that is to become a sharer to another shared event
     by copying to a new WitnessID with new SharedEventID and later deleting the orphaned
     sharers from WitnessTable 
*/
INSERT INTO WitnessTable 
SELECT  
  NULL AS WitnessID , 
  TmpWitness.SharedEventID AS SharedEventID , 
  WitnessTable.PersonID AS PersonID , 
  WitnessTable.WitnessOrder AS WitnessOrder , 
  WitnessTable.Role AS Role , 
  WitnessTable.Sentence AS Sentence , 
  WitnessTable.Note AS Note , 
  WitnessTable.Given AS Given , 
  WitnessTable.Surname AS Surname , 
  WitnessTable.Prefix AS Prefix , 
  WitnessTable.Suffix AS Suffix
FROM TmpWitness
INNER JOIN WitnessTable  -- to get the current sharers of OldEventID
ON TmpWitness.OldEventID = WitnessTable.EventID
;

DROP TABLE IF EXISTS TmpDelete ;
/*  delete self-referencing Witnesses by building a temp table of OldEventID's from
    TmpWitness whose OwnerID is that of the husband or wife in the couple/family
    who own the new shared event. Then delete the recs having those OldEventIDs
    from the TmpWitness table
*/
CREATE TEMP TABLE IF NOT EXISTS TmpDelete 
AS SELECT 
  OldEventID 
FROM 
  TmpWitness 
WHERE 
  OldEventID IN 
    ( 
      SELECT DISTINCT -- list of husband's events 
        HusbEvent.EventID 
      FROM 
        TmpWitness INNER JOIN 
        EventTable AS FamEvent ON TmpWitness.SharedEventID = FamEvent.EventID INNER JOIN 
        FamilyTable ON FamEvent.OwnerID = FamilyTable.FamilyID INNER JOIN 
        EventTable AS HusbEvent ON FamilyTable.FatherID = HusbEvent.OwnerID 
      WHERE 
        HusbEvent.OwnerType = 0 AND FamEvent.Date = HusbEvent.Date AND FamEvent.PlaceID = HusbEvent.PlaceID AND FamEvent.SiteID = HusbEvent.SiteID AND FamEvent.Details = HusbEvent.Details 
      UNION 
      SELECT DISTINCT -- list of wife's events 
        WifeEvent.EventID 
      FROM 
        TmpWitness INNER JOIN 
        EventTable AS FamEvent ON TmpWitness.SharedEventID = FamEvent.EventID INNER JOIN 
        FamilyTable ON FamEvent.OwnerID = FamilyTable.FamilyID INNER JOIN 
        EventTable AS WifeEvent ON FamilyTable.MotherID = WifeEvent.OwnerID 
      WHERE 
        WifeEvent.OwnerType = 0 AND FamEvent.Date = WifeEvent.Date AND FamEvent.PlaceID = WifeEvent.PlaceID AND FamEvent.SiteID = WifeEvent.SiteID AND FamEvent.Details = WifeEvent.Details 
    ) ;
    
DELETE FROM TmpWitness 
WHERE 
  OldEventID IN 
    ( 
      SELECT 
        OldEventID 
      FROM 
        TmpDelete 
    ) ;

/* On the remote chance that some new SharedEventID is the same as the OldEventID 
   in TmpWitness, delete that record from TmpWitness as it would be self-referencing
   */
DELETE FROM TmpWitness 
WHERE 
  SharedEventID = OldEventID ;

/* Now delete the events from EventTable that are going to be converted to sharers in WitnessTable
*/
DELETE FROM EventTable 
WHERE 
  EventID IN 
    ( 
      SELECT 
        OldEventID 
      FROM 
        TmpDelete 
    ) ;

UPDATE TmpWitness -- Put the future WitnessTable.WitnessID into TmpWitness
SET 
  WitnessID = ( 
    SELECT 
      MAX( ROWID ) 
    FROM 
      WitnessTable 
  ) + ROWID 
;

INSERT INTO WitnessTable -- Copy values from TmpWitness and insert into WitnessTable 
SELECT 
  WitnessID , 
  SharedEventID , 
  PersonID , 
  WitnessOrder , 
  Role , 
  Sentence , 
  Note , 
  Given , 
  Surname , 
  Prefix , 
  Suffix 
FROM 
  TmpWitness 
;

INSERT INTO CitationTable -- Copy Citations from Indiv facts to shared Family Fact 
SELECT DISTINCT          
  NULL AS CitationID , 
  2 AS OwnerType , 
  CitationTable.SourceID , 
  TmpWitness.SharedEventID AS OwnerID , 
  CitationTable.Quality , 
  CitationTable.IsPrivate , 
  CitationTable.Comments , 
  CitationTable.ActualText , 
  CitationTable.RefNumber , 
  CitationTable.Flags , 
  CitationTable.Fields 
FROM 
  CitationTable INNER JOIN 
  TmpWitness ON OldEventID = CitationTable.OwnerID LEFT JOIN 
  CitationTable AS SharedEventCitation ON SharedEventID = SharedEventCitation.OwnerID 
WHERE 
  CitationTable.OwnerType = 2 AND SharedEventCitation.OwnerType = 2 AND CitationTable.SourceID || CitationTable.IsPrivate || CitationTable.Comments || CitationTable.ActualText || CitationTable.RefNumber || CitationTable.Fields NOT LIKE SharedEventCitation.SourceID || SharedEventCitation.IsPrivate || SharedEventCitation.Comments || SharedEventCitation.ActualText || SharedEventCitation.RefNumber || SharedEventCitation.Fields
 ;

/*-- Delete the old Indiv facts */
DELETE FROM EventTable  -- Delete old events
WHERE 
  EventID IN 
    ( 
      SELECT DISTINCT 
        OldEventID 
      FROM 
        TmpWitness 
      UNION 
      SELECT DISTINCT 
        OldEventID 
      FROM 
        TmpDelete 
    ) 
;

DELETE FROM CitationTable -- Delete old citations 
WHERE 
  OwnerID NOT IN 
    ( 
      SELECT 
        EventID 
      FROM 
        EventTable 
    ) AND CitationTable.OwnerType = 2 
;

DELETE FROM WitnessTable -- Delete orphaned sharers
WHERE 
  EventID NOT IN
    ( 
      SELECT 
        EventID 
      FROM 
        EventTable 
    ) 
;    
    /*
------ 2. CONVERT GROUPS OF MATCHING INDIV EVENTS -------
-- build a table of all events with non-empty Details, key event properties plus sex and BirthYear of owner (+10 for female)
*/
DROP TABLE IF EXISTS TmpEvent ;
CREATE TEMP TABLE IF NOT EXISTS TmpEvent 
AS SELECT 
  OwnerType , 
  EventID , 
  EventType , 
  EventTable.DATE , 
  Details , 
  PlaceID , 
  SiteID , 
  EventTable.Note , 
  EventTable.OwnerID , 
  Sex , 
  CASE Sex 
    WHEN 1 THEN BirthYear + 10 -- allows for Male owner if close in age
    ELSE BirthYear 
  END AS BirthYear 
FROM 
  EventTable INNER JOIN 
  NameTable USING 
    ( 
      OwnerID 
    ) INNER JOIN 
  PersonTable ON OwnerID = PersonID 
WHERE 
  Details NOT LIKE '' AND +NameTable.IsPrimary 
ORDER BY 
  EventType , 
  EventTable.DATE , 
  Details , 
  PlaceID , 
  SiteID , 
  BirthYear DESC -- when grouped on all but BirthYear, the record having the oldest BirthYear in the group will result 
;

DROP TABLE IF EXISTS TmpSharedEvent ;
CREATE TEMP TABLE TmpSharedEvent -- Table of master Shared Indiv Events
AS SELECT 
  EventID 
FROM -- List of key matching criteria for matched groups of purely Individual events and identify the event within a group having the oldest owner
  ( 
    SELECT 
      EventID , 
      EventType , 
      DATE , 
      Details , 
      PlaceID , 
      SiteID , 
      OwnerID 
    FROM -- List of key matching criteria of matched groups of events, test for groups having purely Indiv events, and oldest event owner
-- Sum(OwnerType)=0 for group having solely Indiv events
      ( 
        SELECT 
          SUM( OwnerType ) , 
          COUNT( ) - 1 AS Dupes , 
          EventID , 
          EventType , 
          DATE , 
          Details , 
          PlaceID , 
          SiteID , 
          OwnerID , 
          Sex , 
          BirthYear 
        FROM 
          TmpEvent 
        GROUP BY 
          EventType , 
          DATE , 
          Details , 
          PlaceID , 
          SiteID 
      ) 
    WHERE 
      "SUM(OwnerType)" = 0 AND Dupes > 0 
    ORDER BY 
      EventID 
  ) ;

DROP TABLE IF EXISTS TmpWitnessEvent ;
CREATE TEMP TABLE TmpWitnessEvent -- Table of EventIDs to become witnesses 
AS SELECT 
  Witness.EventID 
FROM 
  TmpSharedEvent INNER JOIN 
  EventTable USING 
    ( 
      EventID 
    ) INNER JOIN 
  EventTable AS Witness USING 
    ( 
      EventType , 
      DATE , 
      Details , 
      PlaceID , 
      SiteID 
    ) 
EXCEPT 
SELECT 
  EventID 
FROM 
  TmpSharedEvent 
;

/*
-- Convert Indiv Events that duplicate a senior Indiv Event to sharers of same
*/
DROP TABLE IF EXISTS TmpWitness ;
CREATE TEMP TABLE TmpWitness -- Add to TmpWitness table
AS SELECT 
  NULL AS WitnessID , 
  TmpSharedEvent.EventID AS SharedEventID , 
  EventTable.OwnerID AS PersonID , 
  0 AS WitnessOrder , 
  NULL AS Role , 
  '' AS Sentence , 
  EventTable.Note AS Note , 
  '' AS Given , 
  '' AS Surname , 
  '' AS Prefix , 
  '' AS Suffix , 
  NULL AS FamilyID , 
  EventTable.EventID AS OldEventID , 
  CitationID , 
  NULL , 
  NULL , 
  NULL , 
  NULL 
FROM 
  TmpSharedEvent INNER JOIN 
  EventTable AS Shared USING 
    ( 
      EventID 
    ) INNER JOIN 
  EventTable USING 
    ( 
      EventType , 
      DATE , 
      Details , 
      PlaceID , 
      SiteID 
    ) LEFT JOIN 
  CitationTable ON EventTable.EventID = CitationTable.OwnerID 
WHERE 
  EventTable.OwnerType = 0 AND CitationTable.OwnerType = 2 
;

/* 
     Reassign sharers of an event that is to become a sharer to another shared event
     by copying to a new WitnessID with new SharedEventID and later deleting the orphaned
     sharers from WitnessTable 
*/
INSERT INTO WitnessTable 
SELECT  
  NULL AS WitnessID , 
  TmpWitness.SharedEventID AS SharedEventID , 
  WitnessTable.PersonID AS PersonID , 
  WitnessTable.WitnessOrder AS WitnessOrder , 
  WitnessTable.Role AS Role , 
  WitnessTable.Sentence AS Sentence , 
  WitnessTable.Note AS Note , 
  WitnessTable.Given AS Given , 
  WitnessTable.Surname AS Surname , 
  WitnessTable.Prefix AS Prefix , 
  WitnessTable.Suffix AS Suffix
FROM TmpWitness
INNER JOIN WitnessTable  -- to get the current sharers of OldEventID
ON TmpWitness.OldEventID = WitnessTable.EventID
;

DELETE FROM TmpWitness -- delete self-witnessing
WHERE 
  OldEventID IN 
    ( 
      SELECT 
        EventID 
      FROM 
        TmpSharedEvent 
     ) ;

/*
-- Put the future WitnessTable.WitnessID into TmpWitness
*/
UPDATE TmpWitness 
SET 
  WitnessID = ( 
    SELECT 
      MAX( ROWID ) 
    FROM 
      WitnessTable 
  ) + ROWID 
;

INSERT INTO WitnessTable -- Copy values from TmpWitness and insert into WitnessTable 
SELECT 
  WitnessID , 
  SharedEventID , 
  PersonID , 
  WitnessOrder , 
  Role , 
  Sentence , 
  Note , 
  Given , 
  Surname , 
  Prefix , 
  Suffix 
FROM 
  TmpWitness 
;

INSERT INTO CitationTable -- Copy Citations from Indiv facts to shared Indiv Fact 
SELECT DISTINCT 
  NULL AS CitationID , 
  2 AS OwnerType , 
  CitationTable.SourceID , 
  TmpWitness.SharedEventID AS OwnerID , 
  CitationTable.Quality , 
  CitationTable.IsPrivate , 
  CitationTable.Comments , 
  CitationTable.ActualText , 
  CitationTable.RefNumber , 
  CitationTable.Flags , 
  CitationTable.Fields 
FROM 
  CitationTable INNER JOIN 
  TmpWitness ON OldEventID = CitationTable.OwnerID LEFT JOIN 
  CitationTable AS SharedEventCitation ON SharedEventID = SharedEventCitation.OwnerID 
WHERE 
  CitationTable.OwnerType = 2 AND SharedEventCitation.OwnerType = 2 AND CitationTable.SourceID || CitationTable.IsPrivate || CitationTable.Comments || CitationTable.ActualText || CitationTable.RefNumber || CitationTable.Fields NOT LIKE SharedEventCitation.SourceID || SharedEventCitation.IsPrivate || SharedEventCitation.Comments || SharedEventCitation.ActualText || SharedEventCitation.RefNumber || SharedEventCitation.Fields
 ;
 
DELETE FROM EventTable -- Delete the old Indiv facts 
WHERE 
  EventID IN 
    ( 
      SELECT DISTINCT 
        OldEventID 
      FROM 
        TmpWitness 
    ) ;
    
DELETE FROM CitationTable -- Delete old citations 
WHERE 
  OwnerID NOT IN 
    ( 
      SELECT 
        EventID 
      FROM 
        EventTable 
    ) AND CitationTable.OwnerType = 2 ;
/* END OF SCRIPT */