-- WebTags-Consolidate-RM8.sql
/*
2012-12-10 Tom Holden ve3meo
rev 2023-02-23 version for RM8

Generates a WebTag for the Individual from all WebTags attached to citations
of that Individual, the Family in which he/she is a spouse, all his/her events
and alternate names. This results in ready access to all the person's WebTags
from the button on the Edit Person screen.
URL Owner Type (0 = Individual, 3 = Source, 4 = Citation, 5 = Place, 15 = Research Item)
CitationLink Owner Type (0 = Personal, 1 = Family, 2 = Event, 7 = Alternate Name)
*/

INSERT OR REPLACE INTO URLTable

------- Citation WebTags --------
WITH LastEditDate AS
 (SELECT julianday('now') - 2415018.5 AS LastEditDate) -- to get one value for all new webtags
-- Person for citations of Persons
SELECT
  NULL AS LinkID,
  0 AS OwnerType, 
  CL.OwnerID AS OwnerID, 
  0 AS LinkType,
  URL.Name AS Name,
  URL.URL AS URL, 
  URL.Note AS Note
  ,LastEditDate AS UTCModDate
  FROM URLTable URL , LastEditDate
  JOIN CitationLinkTable AS CL
    ON URL.OwnerID=CL.LinkID
  JOIN
    CitationTable AS Cit
    USING(CitationID)
WHERE URL.OwnerType = 4 -- Citation webtag
   AND CL.OwnerType = 0

UNION
-- Fathers for citations of families
SELECT
  NULL AS LinkID,
  0 AS OwnerType, 
  Fam.FatherID AS OwnerID, 
  0 AS LinkType,
  URL.Name AS Name,
  URL.URL AS URL, 
  URL.Note AS Note 
  ,LastEditDate AS UTCModDate
  FROM URLTable URL , LastEditDate
  JOIN CitationLinkTable AS CL
    ON URL.OwnerID=CL.LinkID
  JOIN
    CitationTable AS Cit
    USING(CitationID)
  JOIN FamilyTable AS Fam
    ON CL.OwnerID = Fam.FamilyID  
  WHERE URL.OwnerType = 4 -- Citation webtag
    AND  CL.OwnerType = 1 -- Family

UNION
-- Mothers for citations of families
SELECT
  NULL AS LinkID,
  0 AS OwnerType, 
  Fam.MotherID AS OwnerID, 
  0 AS LinkType,
  URL.Name AS Name,
  URL.URL AS URL, 
  URL.Note AS Note 
  ,LastEditDate AS UTCModDate
  FROM URLTable URL , LastEditDate
  JOIN CitationLinkTable AS CL
    ON URL.OwnerID=CL.LinkID
  JOIN
    CitationTable AS Cit
    USING(CitationID)
  JOIN FamilyTable AS Fam
    ON CL.OwnerID = Fam.FamilyID  
  WHERE URL.OwnerType = 4 -- Citation webtag
    AND  CL.OwnerType = 1 -- Family

UNION
-- Person for citations of individual events
SELECT
  NULL AS LinkID,
  0 AS OwnerType, 
  Evt.OwnerID AS OwnerID, 
  0 AS LinkType,
  URL.Name AS Name,
  URL.URL AS URL, 
  URL.Note AS Note 
  ,LastEditDate AS UTCModDate
  FROM URLTable URL , LastEditDate
  JOIN CitationLinkTable AS CL
    ON URL.OwnerID=CL.LinkID
  JOIN
    CitationTable AS Cit
    USING(CitationID)
  INNER JOIN EventTable Evt
    ON CL.OwnerID = Evt.EventID
  WHERE URL.OwnerType = 4 -- Citation webtag
    AND CL.OwnerType = 2 -- Event citation
    AND Evt.OwnerType = 0 -- Person

UNION
-- Husband for citations of Family events
SELECT
  NULL AS LinkID,
  0 AS OwnerType, 
  Fam.FatherID AS OwnerID, 
  0 AS LinkType,
  URL.Name AS Name,
  URL.URL AS URL, 
  URL.Note AS Note 
  ,LastEditDate AS UTCModDate
  FROM URLTable URL , LastEditDate
  JOIN CitationLinkTable AS CL
    ON URL.OwnerID=CL.LinkID
  JOIN
    CitationTable AS Cit
    USING(CitationID)
  INNER JOIN EventTable Evt
    ON CL.OwnerID = Evt.EventID
  INNER JOIN FamilyTable Fam
    ON Evt.OwnerID = Fam.FamilyID
  WHERE URL.OwnerType = 4 -- Citation webtag
    AND CL.OwnerType = 2 -- Event citation
    AND Evt.OwnerType = 1 -- Family

UNION
-- Wife for citations of Family events
SELECT
  NULL AS LinkID,
  0 AS OwnerType, 
  Fam.MotherID AS OwnerID, 
  0 AS LinkType,
  URL.Name AS Name,
  URL.URL AS URL, 
  URL.Note AS Note 
  ,LastEditDate AS UTCModDate
  FROM URLTable URL , LastEditDate
  JOIN CitationLinkTable AS CL
    ON URL.OwnerID=CL.LinkID
  JOIN
    CitationTable AS Cit
    USING(CitationID)
  INNER JOIN EventTable Evt
    ON CL.OwnerID = Evt.EventID
  INNER JOIN FamilyTable Fam
    ON Evt.OwnerID = Fam.FamilyID
  WHERE URL.OwnerType = 4 -- Citation webtag
    AND CL.OwnerType = 2 -- Event citation
    AND Evt.OwnerType = 1 -- Family

UNION
-- Person for citations of Alternate Names
SELECT
  NULL AS LinkID,
  0 AS OwnerType, 
  Nam.OwnerID AS OwnerID, 
  0 AS LinkType,
  URL.Name AS Name,
  URL.URL AS URL, 
  URL.Note AS Note 
  ,LastEditDate AS UTCModDate
  FROM URLTable URL , LastEditDate
  JOIN CitationLinkTable AS CL
    ON URL.OwnerID=CL.LinkID
  JOIN
    CitationTable AS Cit
    USING(CitationID)
  JOIN NameTable Nam
    ON CL.OwnerID = Nam.NameID  
  WHERE URL.OwnerType = 4 -- Citation webtag
    AND CL.OwnerType = 7 -- Alt Name citation
------- End of Citation WebTags --------
;