/*
sources_by_template.sql   Jerry Bryan  3/15/2024
*/

SELECT S.TemplateID, 
CASE WHEN S.TemplateID = 0 THEN 'Free-Form'
     WHEN S.TemplateID > 0 AND S.TemplateID < 10000 THEN 'Built-in'
     WHEN S.TemplateID >= 10000 THEN 'User-Defined'
END AS TemplateType,
S.Name AS 'SourceName', ST.Name AS 'TemplateName',
C.CitationName,
CASE WHEN CL.OwnerType = 0 THEN 'Person'
     WHEN CL.OwnerType = 1 THEN 'Family'
     WHEN CL.OwnerType = 2 THEN 'Fact'
     WHEN CL.OwnerType = 7 THEN 'Name'
END AS C_OwnerType, 

R.RIN1, R.RIN2, R.Fact
FROM SourceTable AS S
JOIN CitationTable AS C ON C.SourceID = S.SourceID
JOIN CitationLinkTable AS CL ON CL.CitationID = C.CitationID
JOIN
(
  -- person citations
  SELECT CL2.LinkID, N.OwnerID AS RIN1, NULL AS RIN2, NULL AS Fact
  FROM CitationLinkTable AS CL2
  JOIN NameTable AS N ON CL2.OwnerType = 0 AND N.OwnerID = CL2.OwnerID
  
  UNION
  
  -- family ciations
  SELECT CL2.LinkID, F.FatherID AS RIN1, F.MotherID AS RIN2, NULL AS Fact
  FROM CitationLinkTable AS CL2
  JOIN FamilyTable AS F ON CL2.OwnerType = 1 AND F.FamilyID = CL2.OwnerID
  
  UNION
  
  -- individual fact citations
  SELECT CL2.LinkID, E.OwnerID AS RIN1, NULL AS RIN2, FT.Name AS Fact
  FROM CitationLinkTable AS CL2
  JOIN EventTable AS E ON E.OwnerType = 0 AND E.EventID = CL2.OwnerID
  JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
  
  UNION
  
  -- family fact citations
  SELECT CL2.LinkID, F.FatherID AS RIN1, F.MotherID AS RIN2, FT.Name AS Fact
  FROM CitationLinkTable AS CL2
  JOIN EventTable AS E ON E.OwnerType = 1 AND E.EventID = CL2.OwnerID
  JOIN FactTypeTable AS FT ON FT.FactTypeID = E.EventType
  JOIN FamilyTable AS F ON F.FamilyID = E.OwnerID
  
  UNION
  
  -- name citations
  SELECT CL2.LinkID, N.OwnerID AS RIN1, NULL AS RIN2, NULL AS Fact
  FROM CitationLinkTable AS CL2
  JOIN NameTable AS N ON N.NameID = CL2.OwnerID
  
)
AS R ON R.LinkID = CL.LinkID
LEFT JOIN SourceTemplateTable AS ST ON ST.TemplateID = S.TemplateID
ORDER BY TemplateType, TemplateName, SourceName