-- RMGC_Properties.sql
-- Lists properties of RM4 database except for dates plus 
--   extra detail identifying possible problems
-- 2010-01-28 by romermb
-- 2010-01-29 rev by ve3meo with EXISTS tests for one shot op
--         30 rev adding breakdown of Places, unused Sources,
--            orphaned and headless Citations.
--         31 rev Place Details with Notes*, Multimedia with Date and Note*, 
--            nameless addresses, nameless people, orphaned events,
--            orphaned Alt Names, images lacking thumbnails
-- 2010-02-01 rev: Summary/Detail choice; Duplicate Names
-- 2010-02-02 rev: Remarks, Dup names with Media Links
-- 2010-02-13 rev: Duplicate citations
-- 2010-02-16 rev: Duplicate citations speeded up and more accurate
-- 2010-02-17 rev: Used Place Details with Notes - made accurate
-- 2010-02-23 rev: Duplicate Names now pairs, better use of indexes for speed
-- 2010-07-05 rev: error corrected Dup Citations for families did not distinguish different events
-- 2010-07-06 rev: error corrected Dup Citations for indiv facts had false family facts
-- 2010-07-13 rev: added Witness properties
-- 2010-07-14 rev: added Role properties, requiring the attachment of an empty database for comparison
-- 2010-08-09 rev: added Places and Place Details: Used, having Geo-coordinates 
--                 added FactType and SourceTemplate properties
-- 2010-08-10 rev: added Version property 
-- 2010-12-13 rev: added Multimedia duplicate filenames and duplicate Multimedia links properties
-- 2012-02-20 rev: corrected Orphaned Alternate Names; adapted Media Date+Description test for RM4/5 differences
-- 2012-03-01 rev: discovered and added class of Headless Citations for Alternate Names mispointing to a Primary Name 
-- To Do Unused Repositories*?,
--       Problem Events, Families, People, etc.   

-- Create a new empty RootsMagic database using the current version as a Control group 
--   and edit the line below with its correct name and path between the single quotes.
-- Do not edit 'CTL'
-- Execute the next line: 
ATTACH 'C:\MyDocs\FamilyTree\RM5\Empty5021.rmgc' AS 'CTL';
 
DROP TABLE IF EXISTS VariableTable;

CREATE TEMP TABLE IF NOT EXISTS VariableTable
(
Variable TEXT,
Value INTEGER,
Remark TEXT
);
 
INSERT INTO VariableTable
SELECT 'Version',
 (SELECT CAST(substr(datarec,58,4) AS TEXT) FROM ConfigTable WHERE RecType=1),
 'vs Control version ' || (SELECT substr(datarec,58,4) FROM CTL.ConfigTable WHERE RecType=1)
;

INSERT INTO VariableTable
SELECT 'People', COUNT(1), 'all records in PersonTable'
FROM PersonTable;
 
INSERT INTO VariableTable
SELECT '- Nameless People', COUNT(1), 'no record in NameTable for that RIN'
FROM PersonTable P 
LEFT JOIN nametable N
ON P.PersonID = N.OwnerID  AND n.ISPRIMARY
WHERE N.nameid ISNULL
;

DROP TABLE IF EXISTS namesort;
CREATE temp TABLE IF NOT EXISTS namesort AS
SELECT ownerid, surname collate nocase || given collate nocase AS name FROM nametable ORDER BY surname COLLATE NOCASE, given COLLATE NOCASE;

CREATE INDEX IF NOT EXISTS idxNameSortName ON Namesort (NAME);
CREATE INDEX IF NOT EXISTS idxNameSortOwnerID ON Namesort (OwnerID);

INSERT INTO VariableTable
SELECT '- Unresolved Duplicate Name Pairs',COUNT(1), 'pairs of Given and Surnames, not flagged as "Not a Problem"' 
FROM
( SELECT n.ownerid AS ID1, n2.ownerid AS ID2 from namesort n INNER JOIN namesort n2 USING (name) where ID1<ID2
  EXCEPT SELECT ID1,ID2 FROM EXCLUSIONTABLE WHERE exclusiontype=1);

INSERT INTO VariableTable
SELECT '- Resolved* Duplicate Name Pairs',COUNT(1), 'flagged as "Not a Problem" - flags lost on transfer' 
FROM EXCLUSIONTABLE WHERE exclusiontype=1;
 
INSERT INTO VariableTable
SELECT '- Unresolved Duplicates with Media Links',COUNT(1), "secondary persons' links lost on merge" 
FROM
( SELECT n.ownerid AS ID1, n2.ownerid as ID2 FROM namesort n INNER JOIN namesort n2 USING (name) 
  INNER JOIN medialinktable ml ON n2.ownerid=ml.ownerid  
  where ID1<ID2 AND ml.ownertype=0
  EXCEPT SELECT ID1,ID2 FROM EXCLUSIONTABLE WHERE exclusiontype=1
  )
   ;
 
INSERT INTO VariableTable
SELECT 'Alternate names', COUNT(1), 'all records in NameTable where IsPrimary=0'
FROM NameTable
WHERE IsPrimary = 0;
 
INSERT INTO VariableTable
SELECT '- Orphaned Alternate names*', COUNT(1), 'no Primary name record found'
FROM NameTable na
LEFT JOIN nametable n
ON na.ownerid=n.Ownerid
WHERE NOT +na.IsPrimary AND +N.IsPrimary AND n.nameid ISNULL;
 
INSERT INTO VariableTable
SELECT 'Families', COUNT(1), 'all records in FamilyTable'
FROM FamilyTable;
 
INSERT INTO VariableTable
SELECT 'Fact Types', COUNT(1), 'no. of records from FactTypeTable' FROM FactTypeTable
;

INSERT INTO VariableTable
SELECT '- Custom Fact Types', COUNT(1), 'no. of custom Fact Types' FROM main.FactTypeTable 
 WHERE FactTypeID > (SELECT max (FactTypeID) FROM CTL.FactTypeTable)
;

INSERT INTO VariableTable
SELECT '- Customised Built-in Fact Types', COUNT(1), 'no. of built-in Fact Types modified' FROM 
 (
  -- List of customised built-in Fact Types
  SELECT FactTypeID, Name COLLATE NOCASE, Abbrev, GedcomTag, usevalue, usedate, UsePlace, Sentence FROM main.FactTypeTable 
    WHERE FactTypeID <= (SELECT max (FactTypeID) FROM CTL.FactTypeTable)    
    EXCEPT SELECT FactTypeID, Name COLLATE NOCASE, Abbrev, GedcomTag, usevalue, usedate, UsePlace, Sentence FROM CTL.FactTypeTable
  )
; 

INSERT INTO VariableTable
SELECT '- Unused Fact Types', COUNT(1), 'no. of Fact Types not used' FROM 
 (
  -- List of unused Fact Types
  SELECT FactTypeID, Name COLLATE NOCASE, Sentence FROM main.FactTypeTable WHERE FactTypeID IN 
   (
    SELECT FactTypeID FROM main.FactTypeTable EXCEPT SELECT EventType FROM main.EventTable
    )    
  )
;

INSERT INTO VariableTable
SELECT '- Blank Fact Type Names', COUNT(1), 'FactTypes must be named' FROM 
 (
  -- List
  SELECT FactTypeID, Name COLLATE NOCASE, Sentence FROM FactTypeTable WHERE Name COLLATE NOCASE LIKE ''
  )
;

INSERT INTO VariableTable
SELECT '- Blank FactType Sentences', COUNT(1), 'FactType needing definition' FROM 
 (
  -- List
  SELECT FactTypeID, Name COLLATE NOCASE, Sentence FROM FactTypeTable WHERE Sentence LIKE ''
  )
;

INSERT INTO VariableTable
SELECT 'Events', COUNT(1), 'all records of EventTable'
FROM EventTable;
 
INSERT INTO VariableTable
SELECT '- Orphaned Events', SUM(Ctr), 'events for which no person or family match in respective tables' 
FROM
(
SELECT '- Orphaned Personal Events', COUNT(1) AS Ctr
FROM EventTable E
LEFT JOIN persontable p
ON e.OWNERID=p.PersonID 
LEFT JOIN nametable n
ON p.PERSONID=N.OwnerID 
WHERE e.OWNERTYPE=0  AND n.ISPRIMARY AND n.nameid ISNULL
UNION ALL
SELECT '- Orphaned Family Events', COUNT(1)
FROM EventTable E
LEFT JOIN familytable fm
ON e.OWNERID=fm.FamilyID 
WHERE e.OWNERTYPE=1 AND fm.FamilyID ISNULL
)
;

INSERT INTO VariableTable
SELECT '- Event Witnesses', COUNT(1), 'All records in WitnessTable of persons sharing events' FROM Witnesstable
;

INSERT INTO VariableTable
SELECT '-- Nominal Witnesses', COUNT(1), 'not Persons from database, but named in WitnessTable as sharing an event' FROM Witnesstable WHERE PersonID = 0 
;

INSERT INTO VariableTable
SELECT '-- Headless Witnesses', COUNT(1), 'PersonID (RIN) in WitnessTable missing from PersonTable' FROM
 (
  -- List of RINs of Headless Witnesses
  SELECT PersonID FROM WitnessTable WHERE WitnessTable.PersonID > 0
   EXCEPT SELECT PersonID FROM PersonTable   
  )
;

INSERT INTO VariableTable
SELECT '-- Witnesses to Lost Events', COUNT(1), 'EventID in WitnessTable cannot be found in EventTable' FROM
 (
  -- List of Witnesses to Lost Events  
  SELECT [WitnessID],[EventID],[PersonID],[WitnessOrder],[Role],[Sentence],[Note],[Given] COLLATE NOCASE,[Surname] COLLATE NOCASE,[Prefix] COLLATE NOCASE,[Suffix] COLLATE NOCASE FROM WitnessTable WHERE EventID IN  
   (
    -- List of EventIDs of Lost Events with Witnesses
    SELECT EventID FROM WitnessTable 
     EXCEPT SELECT EventID FROM EventTable
    )    
  )
;

INSERT INTO VariableTable
SELECT '-- Witnesses with blank Role', count(1), 'no role has been assigned from RoleTable or the RoleTable role is empty' FROM 
 ( 
  -- List of Witnesses with Blank Role  
  SELECT [WitnessID],[EventID],[PersonID],[WitnessOrder],[Role],W.[Sentence],[Note],[Given] COLLATE NOCASE,[Surname] COLLATE NOCASE,[Prefix] COLLATE NOCASE,[Suffix] COLLATE NOCASE  FROM WitnessTable W, RoleTable WHERE Role LIKE RoleID AND RoleName COLLATE NOCASE LIKE ''
  ) 
;

INSERT INTO VariableTable
SELECT '-- Witnesses with Custom Sentence', count(1), 'a custom sentence has been assigned, unique to this witness' FROM 
 ( 
  -- List of Witnesses with Custom Sentence  
  SELECT [WitnessID],[EventID],[PersonID],[WitnessOrder],[Role],W.[Sentence],[Note],[Given] COLLATE NOCASE,[Surname] COLLATE NOCASE,[Prefix] COLLATE NOCASE,[Suffix] COLLATE NOCASE  FROM WitnessTable W, RoleTable WHERE Role LIKE RoleID AND W.Sentence NOT LIKE ''
  ) 
;

INSERT INTO VariableTable
SELECT '-- Witnesses with Note', count(1), 'a note has been entered for this witness to an event' FROM 
 ( 
  -- List of Witnesses with Note  
  SELECT [WitnessID],[EventID],[PersonID],[WitnessOrder],[Role],W.[Sentence],[Note],[Given] COLLATE NOCASE,[Surname] COLLATE NOCASE,[Prefix] COLLATE NOCASE,[Suffix] COLLATE NOCASE  FROM WitnessTable W, RoleTable WHERE Role LIKE RoleID AND Note NOT LIKE ''
  ) 
;
 
INSERT INTO VariableTable
SELECT '-- Roles', COUNT(1), 'no. of records from RoleTable' FROM roletable
;

INSERT INTO VariableTable
SELECT '--- Custom Roles', COUNT(1), 'no. of custom roles' FROM main.RoleTable 
 WHERE RoleID > (SELECT max (RoleID) FROM CTL.roletable)
;

INSERT INTO VariableTable
SELECT '--- Customised Built-in Roles', COUNT(1), 'no. of built-in roles modified' FROM 
 (
  -- List of customised built-in roles
  SELECT RoleID, RoleName COLLATE NOCASE, EventType, RoleType, Sentence FROM main.RoleTable 
    WHERE RoleID <= (SELECT max (RoleID) FROM CTL.roletable)    
    EXCEPT SELECT RoleID, RoleName COLLATE NOCASE, EventType, RoleType, Sentence FROM CTL.RoleTable
  )
; 

INSERT INTO VariableTable
SELECT '--- Unused Roles', COUNT(1), 'no. of roles not used' FROM 
 (
  -- List of unused roles
  SELECT RoleID, RoleName COLLATE NOCASE, EventType, RoleType, Sentence FROM main.RoleTable WHERE RoleID IN 
   (
    SELECT RoleID FROM main.RoleTable EXCEPT SELECT Role FROM main.WitnessTable
    )    
  )
;

INSERT INTO VariableTable
SELECT '--- Blank Role Names', COUNT(1), 'Roles needing definition' FROM 
 (
  -- List
  SELECT RoleID, RoleName COLLATE NOCASE, EventType, RoleType, Sentence FROM RoleTable WHERE rolename COLLATE NOCASE LIKE ''
  )
;


INSERT INTO VariableTable
SELECT '--- Blank Role Sentences', COUNT(1), 'Roles needing definition' FROM 
 (
  -- List
  SELECT RoleID, RoleName COLLATE NOCASE, EventType, RoleType, Sentence FROM RoleTable WHERE Sentence LIKE ''
  )
;

INSERT INTO VariableTable
SELECT 'Total Places', COUNT(1), 'all records in PlaceTable incl Places and Place Details (Sites)'
FROM PlaceTable
;
 
INSERT INTO VariableTable
SELECT '- System Places', COUNT(1), 'system supplied Places: LDS Temples'
FROM PlaceTable
WHERE PlaceType = 1
;
 
INSERT INTO VariableTable
SELECT '- User Places', COUNT(1), 'user defined Places excl Sites'
FROM PlaceTable
WHERE PlaceType = 0
;
 
INSERT INTO VariableTable
SELECT '-- Used, having Geo-coordinates', COUNT(1), 'non-empty Lat or Long'
FROM (SELECT P.PlaceID 
     FROM PlaceTable P, eventtable e
     WHERE P.PlaceType = 0 AND P.Latitude +P.Longitude <> 0 
     AND P.PlaceID=E.PlaceID
     GROUP BY P.PlaceID)
;

INSERT INTO VariableTable
SELECT '-- Unused User Places*', COUNT(1), 'not used by EventTable, will be dropped in a transfer'
 FROM PlaceTable,       
 (SELECT PlaceTable.PlaceID AS unusedPlaceID          
  FROM PlaceTable 
  WHERE placetable.PlaceType = 0         
  EXCEPT SELECT eventtable.PlaceID           
         FROM eventtable   
)      
  WHERE PlaceTable.PlaceID = unusedPlaceID;

INSERT INTO VariableTable
SELECT '-- User Place Details', COUNT(1), 'user defined Sites'
FROM PlaceTable
WHERE PlaceType = 2;

INSERT INTO VariableTable
SELECT '--- Used, having Place Detail Notes*', COUNT(1), 'Site Notes will be lost in a transfer'
FROM (SELECT PD.PlaceID 
     FROM PlaceTable PD, PlaceTable P, eventtable e
     WHERE PD.PlaceType = 2 AND PD.Note <> '' AND PD.MasterID=P.PlaceID 
     AND PD.MasterID=E.PlaceID AND PD.PlaceID=E.SiteID
     GROUP BY PD.PlaceID)
;

INSERT INTO VariableTable
SELECT '--- Used, having Geo-coordinates', COUNT(1), 'non-empty Lat or Long'
FROM (SELECT PD.PlaceID 
     FROM PlaceTable PD, PlaceTable P, eventtable e
     WHERE PD.PlaceType = 2 AND PD.Latitude +PD.Longitude <> 0 AND PD.MasterID=P.PlaceID 
     AND PD.MasterID=E.PlaceID AND PD.PlaceID=E.SiteID
     GROUP BY PD.PlaceID)
;

INSERT INTO VariableTable
SELECT '--- Unused Place Details*', COUNT(1), 'Sites will be lost in a transfer'
FROM PlaceTable PD 
LEFT JOIN PlaceTable P ON PD.MasterID=P.PlaceID
LEFT JOIN eventtable e ON (PD.MasterID=E.PlaceID AND PD.PlaceID=E.SiteID)
WHERE PD.PlaceType = 2 AND E.EVENTID ISNULL
;

INSERT INTO VariableTable
SELECT 'Source Templates', COUNT(1), '# of records from SourceTemplateTable' FROM SourceTemplateTable
;

INSERT INTO VariableTable
SELECT '- Custom Source Templates', COUNT(1), '# of custom Source Templates' FROM main.SourceTemplateTable 
 WHERE TemplateID > 9999
;

INSERT INTO VariableTable
SELECT '- Unupdated Built-in SourceTemplates', COUNT(1), '# not matching reference database' FROM 
 (
  -- List of customised built-in SourceTemplates
  SELECT TemplateID, Name COLLATE NOCASE, [Description],[Footnote],[ShortFootnote],[Bibliography],[FieldDefs] FROM main.SourceTemplateTable 
    WHERE TemplateID <= 9999    
    EXCEPT SELECT TemplateID, Name COLLATE NOCASE, [Description],[Footnote],[ShortFootnote],[Bibliography],[FieldDefs] FROM CTL.SourceTemplateTable
  )
; 

INSERT INTO VariableTable
SELECT '- Unused Custom SourceTemplates*', COUNT(1), 'lost on transfer' FROM 
 (
  -- List of unused Custom SourceTemplates
  SELECT TemplateID, Name COLLATE NOCASE FROM main.SourceTemplateTable WHERE TemplateID>9999 AND TemplateID IN 
   (
    SELECT TemplateID FROM main.SourceTemplateTable EXCEPT SELECT TemplateID FROM main.SourceTable
    )    
  )
;

INSERT INTO VariableTable
SELECT '- Incomplete Source Templates', COUNT(1), 'missing part of definition' FROM 
 (
  -- List
  SELECT TemplateID, Name COLLATE NOCASE, [Description],[Footnote],[ShortFootnote],[Bibliography],[FieldDefs] FROM main.SourceTemplateTable
  WHERE Name COLLATE NOCASE LIKE '' OR   
        [Description] LIKE '' OR   
        [Footnote] LIKE '' OR   
        [ShortFootnote] LIKE '' OR   
        [Bibliography] LIKE '' OR   
        [FieldDefs] LIKE '%<FIELDS/>%'
  )
;

INSERT INTO VariableTable
SELECT 'Total Sources', COUNT(1), 'all records from SourceTable'
FROM SourceTable;
 
INSERT INTO VariableTable
SELECT '- Unused Sources*', COUNT(1), 'SourceTable records unused by CitationTable'
FROM sourcetable s LEFT OUTER JOIN citationtable c
ON s.SOURCEID = c.SOURCEID 
WHERE c.SourceID ISNULL;
 
INSERT INTO VariableTable
SELECT 'Total Citations', COUNT(1), 'all records from CitationTable'
FROM CitationTable;

-- BEGIN '- Duplicate Citations'--------------------------
DROP TABLE IF EXISTS tmpCitations
;
CREATE TEMP TABLE tmpCitations (CITID INTEGER, SrcID INTEGER, RIN INTEGER, Dupes INTEGER, RinCitation TEXT);

INSERT INTO tmpCitations 
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, COUNT(1)-1 AS Dupes, 
  QUOTE(n.OwnerID) || 'Personal' || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.Fields AS TEXT)) || QUOTE(mm1.mediafile) 
  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.Fields AS TEXT)) || QUOTE(mm2.mediafile) 
  AS RinCitation
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
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3 
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID    
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4 
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID    
WHERE  c.ownertype=0 AND +n.IsPrimary=1
GROUP BY RinCitation

UNION ALL
-- all Fact citations for Individual
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, COUNT(1)-1 AS Dupes, 
  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || QUOTE(s.NAME) || QUOTE(s.refnumber) || QUOTE(s.actualtext) || QUOTE(s.comments) || QUOTE(CAST(s.Fields AS TEXT)) || QUOTE(mm1.mediafile) 
  || QUOTE(c.refnumber) || QUOTE(c.actualtext) || QUOTE(c.comments) || QUOTE(CAST(c.Fields AS TEXT)) || QUOTE(mm2.mediafile) 
  AS RinCitation
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  
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3 
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID    
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4 
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID    
WHERE c.ownertype=2 AND e.ownertype=0 AND f.ownertype=0 AND +n.IsPrimary=1 
GROUP BY RinCitation

UNION ALL
-- all Couple citations for Father|Husband|Partner 1
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, COUNT(1)-1 AS Dupes, 
  QUOTE(n.OwnerID) || QUOTE(f.NAME) || QUOTE(e.EventID) || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.Fields) || QUOTE(mm1.mediafile) || 
  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.Fields) || QUOTE(mm2.mediafile) AS RinCitation
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
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3 
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID    
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4 
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID    
WHERE c.ownertype=1 AND e.ownertype=1 AND f.ownertype=1 AND +n.IsPrimary=1
GROUP BY RinCitation

UNION ALL
-- Citations for Alternate Names 
SELECT  c.CITATIONID, c.sourceid AS SrcID, n.ownerid AS RIN, COUNT(1)-1 AS Dupes, 
  n.OwnerID || 'Alternate Name' || s.NAME || s.refnumber || QUOTE(s.actualtext) || QUOTE(s.comments) || (s.Fields) || QUOTE(mm1.mediafile) || 
  c.refnumber || QUOTE(c.actualtext) || QUOTE(c.comments) || (c.Fields) || QUOTE(mm2.mediafile) AS RinCitation
FROM  citationtable c 
  LEFT OUTER JOIN sourcetable s ON c.sourceid=s.sourceid 
  LEFT OUTER JOIN  nametable n ON n.nameid=c.ownerid
  LEFT OUTER JOIN medialinktable ml1 ON s.SourceID=ml1.OwnerID AND ml1.OwnerType=3 
  LEFT OUTER JOIN multimediatable mm1 ON ml1.MediaID=mm1.MediaID    
  LEFT OUTER JOIN medialinktable ml2 ON c.CitationID=ml2.OwnerID  AND ml2.OwnerType=4 
  LEFT OUTER JOIN multimediatable mm2 ON ml2.MediaID=mm2.MediaID    
WHERE  c.ownertype=7 AND +n.IsPrimary=0
GROUP BY RinCitation
--ORDER BY rincitation 
;

-- Count duplicates

INSERT INTO VariableTable
SELECT '- Duplicate Citations', SUM(Dupes), 'identical in most respects, cluttering reports' 
  FROM tmpcitations
;
-- END '- Duplicate Citations' ----------------------------- 


INSERT INTO VariableTable
SELECT '- Sourceless Citations*', COUNT(1), 'no SourceTable record for this CitationTable record'
FROM CitationTable c LEFT OUTER JOIN sourcetable s
ON s.SOURCEID = c.SOURCEID
WHERE s.SourceID ISNULL;

INSERT INTO VariableTable
SELECT '- Headless Citations*', SUM(ctr), 'CitationTable records for which no Person, Event, Family, AltName found; cleaned on transfer' 
FROM
(
SELECT '- Headless Personal Citations', COUNT(1) AS ctr
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.ownerid ISNULL
UNION ALL
SELECT '- Headless Fact Citations', COUNT(1)
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 n.ownerid ISNULL
UNION ALL
SELECT '- Headless Spouse Citations', COUNT(1)
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
WHERE c.ownertype=1  AND n.ownerid ISNULL
UNION ALL
SELECT '- Headless Family Citations', COUNT(1)
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.ownerid ISNULL
UNION ALL
SELECT '- Headless Alt Name Citations', COUNT(1)
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.nameid ISNULL OR +N.IsPrimary)
);

INSERT INTO VariableTable
SELECT 'Repositories', COUNT(1), 'all records from AddressTable of type Repository'
FROM AddressTable
WHERE AddressType = 1;
 
INSERT INTO VariableTable
SELECT 'To- do tasks', COUNT(1), 'all records from ResearchTable'
FROM ResearchTable
WHERE TaskType = 0;
 
INSERT INTO VariableTable
SELECT 'Multimedia items', COUNT(1), 'all records from MultimediaTable'
FROM MultimediaTable;
 
INSERT INTO VariableTable
SELECT '- lacking thumbnail', COUNT(1), 'probably an imported reference to an image file that has yet to be found'
FROM MultimediaTable 
WHERE mediatype=1 AND thumbnail ISNULL;
-- if thumbnails stored for other types revise

INSERT INTO VariableTable
SELECT '- duplicate multimedia filenames', SUM(DUPES), 'probably having different paths'
 FROM
 (
  SELECT MediaFile COLLATE NOCASE, COUNT(*)-1 AS DUPES FROM MultimediaTable 
    GROUP BY MediaFile COLLATE NOCASE
  )
;
 
INSERT INTO VariableTable
SELECT 'Multimedia links', COUNT(1), 'all records from MediaLinkTable'
FROM MediaLinkTable;
 
INSERT INTO VariableTable
SELECT '- with Date & Description*', COUNT(1), '(RM4) if a record has both, the Description is lost in a transfer'
FROM MultimediaTable m
INNER JOIN MediaLinkTable ml ON m.MediaID=ml.MediaID
WHERE ml.Date <>'.' AND ml.Description LIKE '%_' 
AND (SELECT Value FROM VariableTable WHERE Variable = 'Version') BETWEEN 4000 AND 4999 ;

INSERT INTO VariableTable
SELECT '- with Date & Description*', COUNT(1), '(RM5) if a record has both, the Description is lost in a transfer'
FROM MultimediaTable m
WHERE m.Date <>'.' AND m.Description LIKE '%_' 
AND (SELECT Value FROM VariableTable WHERE Variable = 'Version') BETWEEN 5000 AND 5999 ;

INSERT INTO VariableTable
SELECT '- duplicate multimedia links', SUM(DUPES), 'image appears multiple times for person, fact'
 FROM
 (
  SELECT COUNT(*)-1 AS DUPES FROM MediaLinkTable
  GROUP BY OwnerType, OwnerID, IsPrimary, Include1, Include2, Include3, Include4, SortOrder, RectLeft, RectTop, RectRight, RectBottom, Note, Caption COLLATE NOCASE, RefNumber COLLATE NOCASE, Date, SortDate, Description
  )
;

INSERT INTO VariableTable
SELECT 'Addresses', COUNT(1), 'all records from AddressTable of type Address'
FROM AddressTable
WHERE AddressType = 0;
 
INSERT INTO VariableTable
SELECT '- blank names', COUNT(1), 'Name field of AddressTable record is blank' 
FROM AddressTable  
WHERE AddressType = 0 AND Name Collate NOCASE LIKE '';
 
INSERT INTO VariableTable
SELECT 'Correspondence', COUNT(1), 'all records from ResearchTable of type Correspondence'
FROM ResearchTable
WHERE TaskType = 1;

INSERT INTO VARIABLETABLE
SELECT '* NOT TRANSFERABLE', NULL , 'via GEDCOM or Drag&Drop to another RM database';

-- reference database is unused beyond this point so detach it.
DETACH CTL
;

--      ****************************************************************************************
-- N.B. ***** To see results it may be necessary to re-run the following part of the query *****
--      ****************************************************************************************
SELECT Value, Variable, Remark FROM variabletable WHERE variable NOT LIKE 
CASE 
WHEN (:YforSummaryElseDetails) IN('y','Y') THEN '-%'
ELSE ''
END;