-- Create Find-a-grave citations

INSERT or IGNORE INTO CitationTable

SELECT
null,
'73' as SourceID,

'[' || FT.Name || '] ' || 
'{' || Details || '} ' || 
pt.Reverse || " | "
|| Given  || ' ' || Surname  || ' ' || Suffix  || ' (' || BirthYear  || '-' || DeathYear  || ' ) '
AS Comments,

null as ActualText,

'[' || FT.Name || '] ' || 
'[' || ML.LinkID || '] ' || 
'[' || ML.MediaID || '] ' || 
'[' || nt.OwnerID || '] ' || 
'[' || pt.PlaceID || '] ' || 
'[' || E.EventID || '] ' || 
(substr(pt.Name, 1, instr(pt.Name,', ')-1)) 
as RefNumber,

null as Footnote,
null as ShortFootnote,
null as Bibliography,

'
<Root><Fields>
<Field><Name>AccessType</Name><Value>web</Value></Field>
<Field><Name>AccessDate</Name><Value>' || strftime('%m/%d/%Y', ML.UTCModDate+2415018.5) ||' </Value></Field> 
<Field><Name>memorial</Name><Value>' || Prefix || ' ' || Given  || ' ' || Surname  || ' ' || Suffix  || ' (' || BirthYear  || '-' || DeathYear  || ' ) ' || ' </Value></Field>
<Field><Name>FaG</Name><Value>'      || Details || ' </Value></Field>
<Field><Name>cmtrynm</Name><Value>'  || (substr(pt.Name, 1, instr(pt.Name,', ')-1))   || ' </Value></Field>
<Field><Name>cmtryloc</Name><Value>' || (substr(pt.Name, instr(pt.Name,',')+2))       || ' </Value></Field>
<Field><Name>createdby</Name><Value></Value></Field>
<Field><Name>photoby</Name><Value></Value></Field>
</Fields></Root>
'
as Fields,

(julianday('now') - 2415018.5)  AS UTCModDate,

'[' || FT.Name || '] ' || 
'{' || Details || '} ' || 
'|' || nt.OwnerID || '| ' || 
Prefix || ' ' || Given  || ' ' || Surname  || ' ' || Suffix  || ' (' || BirthYear  || '-' || DeathYear  || ' ) '||
(substr(pt.Name, 1, instr(pt.Name,', ')-1)) 
 as CitationName
 
FROM FactTypeTable AS FT
JOIN EventTable AS E ON FT.Name LIKE '%grave%' AND E.OwnerTYPE = 0 AND E.EventType = FT.FactTypeID
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 2 AND ML.OwnerID = E.EventID
LEFT JOIN NameTable as nt ON (nt.OwnerID = E.OwnerID)
LEFT JOIN PlaceTable as pt ON (e.placeID = pt.PlaceID)
WHERE ML.LinkID IS NOT NULL and Details is NOT NULL and nt.IsPrimary and FT.Name is NOT NULL and FT.Name = 'Find-a-Grave'
GROUP BY CitationName
ORDER BY Details