-- MediaTags-CopySharedFactsMediaToShareePersonal.sql
/*
2012-12-27 Tom Holden ve3meo
2012-12-28 V1 complete
2013-01-05 1.1 outputs media tag for sharees having no defined role in the event. 
           Sentence in tag description reads "Shared in the ...".

Creates a media tag, for media already tagged to a shared event, 
 to the Persons sharing the event. The Description contains
 the original Description appended with the Person's role name, 
 the event the Person shared in and the name(s) of the Principal(s)
 in the event.

Version 1 deals with tags for shared non-family and family events.

Use MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by this script.
*/


INSERT OR REPLACE INTO MediaLinkTable

-- tags for shared individual (non-family) events
SELECT
 NULL AS LinkID, 
 MediaID,
 0 AS OwnerType,
 OwnerID,
 0 AS IsPrimary,
 0 AS Include1,
 0 AS Include2,
 0 AS Include3,
 0 AS Include4,
 0 AS SortOrder,
 0 AS RectLeft,
 0 AS RectTop,
 0 AS RectRight,
 0 AS RectBottom,
 '' AS Note,
 Caption,
 RefNumber,
 Date,
 SortDate,
 Description
FROM
( 
SELECT
 ML.MediaID AS MediaID, 
 W.PersonID AS OwnerID,
 ML.Caption AS Caption,
 ML.RefNumber AS RefNumber,
 ML.Date AS Date,
 ML.SortDate AS SortDate,
 ML.Description
  || ' '
  || ifnull(R.RoleName, 'Shared')
  || ' in the ' 
  || LOWER(F.Name) 
  ||  ' of ' 
  || N.Given 
  || ' ' 
  || N.Surname 
  || '-' 
  || N.OwnerID
  AS Description
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType = 2 -- Event
 AND E.OwnerType = 0 -- Person
INNER JOIN WitnessTable W
 USING(EventID)
INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
LEFT JOIN RoleTable R ON W.Role = R.RoleID
INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE W.PersonID > 0
AND ML.MediaID || '.' || W.PersonID
NOT IN 
(
 SELECT DISTINCT MediaID || '.' || OwnerID  
 FROM MediaLinkTable
 WHERE OwnerType = 0
 ORDER BY MediaID, OwnerID
 )

UNION
-- tags for shared family events
SELECT
 ML.MediaID AS MediaID, 
 W.PersonID AS OwnerID,
 ML.Caption AS Caption,
 ML.RefNumber AS RefNumber,
 ML.Date AS Date,
 ML.SortDate AS SortDate,
 ML.Description
  || ' '
  || ifnull(R.RoleName, 'Shared')
  || ' in the ' 
  || LOWER(F.Name) 
  ||  ' of ' 
  || Husb.Given 
  || ' ' 
  || Husb.Surname 
  || '-' 
  || Husb.OwnerID
  || ' & '
  || Wife.Given
  || ' ' 
  || Wife.Surname 
  || '-' 
  || Wife.OwnerID
  
  AS Description
FROM MediaLinkTable ML
INNER JOIN EventTable E
 ON ML.OwnerID = E.EventID
 AND ML.OwnerType = 2 -- Event
 AND E.OwnerType = 1 -- FamilyPerson
INNER JOIN FamilyTable Fam
 ON E.OwnerID = Fam.FamilyID
LEFT JOIN NameTable Husb
 ON Fam.FatherID = Husb.OwnerID AND +Husb.IsPrimary
LEFT JOIN NameTable Wife
 ON Fam.MotherID = Wife.OwnerID AND +Wife.IsPrimary
INNER JOIN WitnessTable W
 USING(EventID)
INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
LEFT JOIN RoleTable R ON W.Role = R.RoleID
--INNER JOIN NameTable N ON E.OwnerID = N.OwnerID AND +N.IsPrimary
WHERE W.PersonID > 0
AND W.PersonID || '.' || ML.MediaID
NOT IN 
(
 SELECT DISTINCT MediaID || '.' || OwnerID  
 FROM MediaLinkTable
 WHERE OwnerType = 0
 ORDER BY MediaID, OwnerID
 )
)

;  

-- USE MediaTags-DeletePersonalHavingFactDupes.sql to delete tags created by above
;