-- AllCitations.sql
-- 2010-01-28 ve3meo
-- Lists citations for each person
-- 2010-01-29 rev by ve3meo to use LEFT OUTER JOINS to include the most orphaned citations
--     Citations for Alternate Names, added column for NameTable.IsPrimary AS Uniq to all queries 
--     and negated it for Alt Name and Couple.Wife queries; filter on Uniq for principal name to 
--     reduce multiple listing of same citation OR Uniq ISNULL for citations unlinked to persons. 
--     Requires a temp table because of speed degradation when incorporated in main selects; 
--      filtering can be done on screen in SQLiteDeveloper.
-- 2010-01-30 rev by ve3meo. Dropped UNIQUE from INDEX because other SQLite managers objected.
--     Put QUOTE() around BLOB type fields from CitationTable to display text where some SQLite 
--      managers merely say BLOB.
-- To Do - maybe add eventtable.SortDate as a sorting criterion
-- 2011-11-04 ve3meo corrections for spouse, and family fact citations and multiples due Alt Name
-- 2022-04-26 ve3meo corrected the statement for Family events because current SQLite reported an error
--
-- BEGIN
-- all Personal citations for Individual
DROP TABLE IF EXISTS tmpCitations;
CREATE TEMP TABLE tmpCitations AS
SELECT  c.CITATIONID AS CitID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary AS Uniq, n.surname COLLATE NOCASE AS Surname, n.suffix COLLATE NOCASE AS Sfx, n.prefix COLLATE NOCASE AS Pfx, n.given COLLATE NOCASE AS Givens, n.birthyear AS Born, 
  n.deathyear AS Died, 'Personal' AS Citer, s.NAME COLLATE NOCASE AS Source, s.refnumber AS SrcREFN, s.actualtext AS SrcTxt, s.comments AS SrcComment, c.refnumber AS CitREFN, 
  QUOTE(c.actualtext) AS CitTxt, QUOTE(c.comments) AS CitComment
FROM  citationtable c 
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN persontable p ON c.ownerid=p.personid 
  LEFT OUTER JOIN  nametable n ON p.personid=n.ownerid
WHERE  c.ownertype=0 AND +n.IsPrimary=1
;
INSERT INTO tmpCitations
-- all Fact citations for Individual
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname COLLATE NOCASE , n.suffix COLLATE NOCASE , n.prefix COLLATE NOCASE , n.given COLLATE NOCASE , n.birthyear, 
  n.deathyear, f.NAME AS Citer, s.NAME COLLATE NOCASE , s.refnumber, s.actualtext, s.comments, c.refnumber, 
  c.actualtext, c.comments
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid
  LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventid
  LEFT OUTER JOIN persontable p ON e.ownerid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1 
;

INSERT INTO tmpCitations
-- all Spouse citations for Father|Husband|Partner 1
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, 'Spouse' as 'Citer', s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber, 
  c.actualtext, c.comments
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN familytable fm ON c.ownerid=fm.FamilyID
  LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
--  LEFT OUTER JOIN eventtable e ON e.ownerid=fm.familyid
--  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=1 -- AND e.ownertype=1 AND f.ownertype=1 
AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
-- all Couple Event citations for Father|Husband|Partner 1
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, f.NAME, s.NAME, s.refnumber, s.actualtext, s.comments, c.refnumber, 
  c.actualtext, c.comments
FROM  citationtable c
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN eventtable e ON c.ownerid=e.eventID
  LEFT OUTER JOIN familytable fm ON e.ownerid=fm.familyid
  LEFT OUTER JOIN persontable p ON fm.fatherid=p.personid
  LEFT OUTER JOIN nametable n ON p.personid=n.ownerid
  LEFT OUTER JOIN facttypetable f ON e.eventtype=f.facttypeid
WHERE c.ownertype=2 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1
;

INSERT INTO tmpCitations
-- Citations for Alternate Names 
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, NOT n.IsPrimary, n.surname, n.suffix, n.prefix, n.given, n.birthyear, 
  n.deathyear, 'Alternate Name' AS Citer, s.NAME AS Source, s.refnumber, s.actualtext, s.comments, c.refnumber, 
  c.actualtext, c.comments 
FROM  citationtable c 
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN  nametable n ON n.nameid=c.ownerid
WHERE  c.ownertype=7  AND +n.IsPrimary=0
;
CREATE INDEX tmpCitations_idx ON tmpCitations(CitID); 
-- Now filter the results to get rid of duplicate citation IDs due Alt Names
SELECT * FROM tmpcitations
WHERE uniq=1 OR uniq ISNULL 
ORDER BY RIN, Citer , SOURCE
; 
-- CitID
-- RIN, Citer;
-- END