--
--      for future reference, a few old style Master Sources
--      do have media, but they all use the Free Form source
--      template so they should be identifed as problems
--      on that basis.
--
--SELECT ML.LinkID, ML.MediaID, ML.OwnerType, ML.OwnerID, S.*
--FROM SourceTable AS S
--        JOIN
--     (SELECT ML.* FROM MediaLinkTable AS ML WHERE ML.OwnerType = 3) AS ML ON ML.OwnerID = S.SourceID   
--WHERE S.Name NOT LIKE('%*%');




-- missing stuff.  This is my master check list creator of all things missing.

-- Create a view of the PersonTable filtered on individuals who are color coded red.
DROP VIEW IF EXISTS PersonTableView;
CREATE TEMP VIEW PersonTableView AS
SELECT P.*
FROM PersonTable AS P
       INNER JOIN
     NameTable AS N ON N.OwnerID = P.PersonID
WHERE P.Color = 1 AND N.Given NOT LIKE('%dummy%');
--WHERE P.PersonID IN (1474);

-- Create a list of persons who have a Burial fact
DROP VIEW IF EXISTS PersonsWithBurial;
CREATE TEMP VIEW PersonsWithBurial AS
SELECT Distinct P.PersonID 
FROM EventTable AS E
         INNER JOIN
     PersonTableView AS P ON E.OwnerID = P.PersonID     
WHERE E.EventType = 4; -- Burial is FactType = 4;

-- Create a list of persons who have a BurialInscription fact
DROP VIEW IF EXISTS PersonsWithBurialInscription;
CREATE TEMP VIEW PersonsWithBurialInscription AS
SELECT Distinct P.PersonID -- People with (  Burial  = 4 and or BurialInscription = 1150)
FROM EventTable AS E
         INNER JOIN
     PersonTableView AS P ON E.OwnerID = P.PersonID     
WHERE E.EventType = 1150; -- BurialInscription is FactType = 1150;

-- Create a list of persons who have a BurialInsription fact
DROP VIEW IF EXISTS PersonsWithBurialGPS;
CREATE TEMP VIEW PersonsWithBurialGPS AS
SELECT Distinct P.PersonID -- People with (  Burial  = 4 and or BurialInscription = 1150)
FROM EventTable AS E
         INNER JOIN
     PersonTableView AS P ON E.OwnerID = P.PersonID     
WHERE E.EventType = 1151; -- BurialInscription is FactType = 1150;

-- Create a list of persons who have a Burial fact without having a BurialInscription fact
DROP VIEW IF EXISTS PersonsWithoutBurialInscription;
CREATE TEMP VIEW PersonsWithoutBurialInscription AS
SELECT P.PersonID AS Rin, 'BurialInscription' AS Abbrev
FROM PersonsWithBurial AS P
        LEFT JOIN
     PersonsWithBurialInscription AS G ON P.PersonID = G.PersonID
WHERE G.PersonID IS NULL;

-- Create a list of persons who have a Burial fact without having a BurialInscription fact
DROP VIEW IF EXISTS PersonsWithoutBurialGPS;
CREATE TEMP VIEW PersonsWithoutBurialGPS AS
SELECT P.PersonID AS Rin, 'BurialGPS' AS Abbrev
FROM PersonsWithBurial AS P
        LEFT JOIN
     PersonsWithBurialGPS AS G ON P.PersonID = G.PersonID
WHERE G.PersonID IS NULL;

-- Create a view of the NameTable filtered on individuals who are in PersonTableView
DROP VIEW IF EXISTS NameTableView;
CREATE TEMP VIEW NameTableView AS
SELECT N.*
FROM NameTable AS N
        INNER JOIN
     PersonTableView AS P ON N.OwnerID = P.PersonID;
     
-- Create a view of the FamilyTable filtered on individuals who are in PersonTableView
DROP VIEW IF EXISTS FamilyTableView;
CREATE TEMP VIEW FamilyTableView AS
SELECT DISTINCT F.*
FROM FamilyTable AS F
        INNER JOIN
     PersonTableView AS P ON (F.FatherID = P.PersonID OR F.MotherID = P.PersonID);
     
-- Create a view of the ChildTable filtered on individuals who are in PersonTableView
DROP VIEW IF EXISTS ChildTableView;
CREATE TEMP VIEW ChildTableView AS
SELECT DISTINCT C.*
FROM ChildTable AS C
        INNER JOIN
     PersonTableView AS P ON C.ChildID = P.PersonID;         

-- Create a view of the FactTypeTable filtered to exclude dummy facts.
DROP VIEW IF EXISTS FactTypeTableView;
CREATE TEMP VIEW FactTypeTableView AS
SELECT F.*
FROM FactTypeTable AS F
WHERE (F.Abbrev NOT LIKE '%*%' AND F.Abbrev NOT LIKE 'Ref%');

-- Create a view of the EventTable filtered to exclude dummy facts.
DROP VIEW IF EXISTS EventTableView;
CREATE TEMP VIEW EventTableView AS
SELECT E.*,
       F.Abbrev
FROM EventTable AS E
       INNER JOIN
     FactTypeTableView AS F ON E.EventType = F.FactTypeID;
     
-- Create a view of the EventTable filtered to individual facts for People in PersonTableView.
DROP VIEW IF EXISTS IndividualEventView;
CREATE TEMP VIEW IndividualEventView AS
SELECT E.*
FROM EventTableView AS E
        INNER JOIN
     PersonTableView AS P ON E.OwnerID = P.PersonID 
WHERE E.OwnerType = 0;

-- Create a view of the EventTable filtered to family facts for People in PersonTableView.
DROP VIEW IF EXISTS FamilyEventView;
CREATE TEMP VIEW FamilyEventView AS
SELECT E.*
FROM EventTableView AS E
        INNER JOIN
     FamilyTableView AS F ON E.OwnerID = F.FamilyID   
WHERE E.OwnerType = 1;
       
     
-- Create a view of the CitationTable filtered on OwnerType = 0 (citations for individuals)
DROP VIEW IF EXISTS CitationViewIndividual;
CREATE TEMP VIEW CitationViewIndividual AS 
SELECT C.*
FROM CitationTable AS C
WHERE C.OwnerType = 0;

-- Create a view of the CitationTable filtered on OwnerType = 1 (citations for families)
DROP VIEW IF EXISTS CitationViewFamily;
CREATE TEMP VIEW CitationViewFamily AS 
SELECT C.*
FROM CitationTable AS C
WHERE C.OwnerType = 1; 

-- Create a view of the CitationTable filtered on OwnerType = 2 (citations for events)
DROP VIEW IF EXISTS CitationViewEvent;
CREATE TEMP VIEW CitationViewEvent AS 
SELECT C.*
FROM CitationTable AS C
WHERE C.OwnerType = 2;

-- Create a view of the citations for individuals joined with chosen individuals.
-- It is a LEFT JOIN from the individuals to the citations to identify individuals
-- without citations.
DROP VIEW IF EXISTS CitationViewChosenIndividuals;
CREATE TEMP VIEW CitationViewChosenIndividuals AS     
SELECT P.PersonID AS RIN,
       ' Individual' AS Abbrev,
       NULL AS SortDate,
       NULL AS Date,
       C.SourceID AS SourceID
FROM PersonTableView AS P
       LEFT JOIN
     CitationViewIndividual AS C ON C.OwnerID = RIN;
     
-- Create a view of the individual events for selected individuals.
-- It is a LEFT JOIN from the individuals to the citations to identify events
-- without citations.
DROP VIEW IF EXISTS CitationViewChosenIndividualEvents;
CREATE TEMP VIEW CitationViewChosenIndividualEvents AS
SELECT E.OwnerID AS RIN,
       E.Abbrev AS Abbrev,
       E.SortDate AS SortDate,
       E.Date AS Date,
       C.SourceID AS SourceID
FROM IndividualEventView AS E
       LEFT JOIN 
     CitationViewEvent AS C ON C.OwnerID = E.EventID
ORDER BY RIN;

-- Create a view of the father's events in the FamilyTable for selected individuals.
-- It is a LEFT JOIN from the individuals to the citations to identify fathers
-- without citations.
DROP VIEW IF EXISTS FatherFactCitations;
CREATE TEMP VIEW FatherFactCitations AS
SELECT F.FatherID AS RIN,
       E.Abbrev AS Abbrev,
       E.SortDate AS SortDate,
       E.Date AS Date,
       C.SourceID AS SourceID
FROM FamilyEventView AS E
       INNER JOIN 
     FamilyTableView AS F ON E.OwnerID = F.FamilyID
       LEFT JOIN
     CitationViewEvent AS C ON C.OwnerID = E.EventID       
ORDER BY RIN;

-- Create a view of the mother's events in the FamilyTable for selected individuals.
-- It is a LEFT JOIN from the individuals to the citations to identify mother
-- without citations.
DROP VIEW IF EXISTS MotherFactCitations;
CREATE TEMP VIEW MotherFactCitations AS
SELECT F.MotherID AS RIN,
       E.Abbrev AS Abbrev,
       E.SortDate AS SortDate,
       E.Date AS Date,
       C.SourceID AS SourceID
FROM FamilyEventView AS E
       INNER JOIN 
     FamilyTableView AS F ON E.OwnerID = F.FamilyID
       LEFT JOIN
     CitationViewEvent AS C ON C.OwnerID = E.EventID       
ORDER BY RIN;

-- Create a view of all the different types of citations,
-- combined together with a UNION clause.
DROP VIEW IF EXISTS UnionCitations;
CREATE TEMP VIEW UnionCitations AS
SELECT * FROM CitationViewChosenIndividuals
    UNION
SELECT * FROM CitationViewChosenIndividualEvents
    UNION
SELECT * FROM FatherFactCitations
    UNION
SELECT * FROM MotherFactCitations;

DROP VIEW IF EXISTS MediaLinkTableFilteredMasterSources;
CREATE TEMP VIEW MediaLinkTableFilteredMasterSources AS
SELECT ML.MediaID AS MediaID,
       ML.OwnerType AS OwnerType,
       ML.OwnerID AS SourceID
FROM MediaLinkTable AS ML
WHERE ML.OwnerType = 3;

DROP VIEW IF EXISTS CitationTableLeftJoinMedia;
CREATE TEMP VIEW CitationTableLeftJoinMedia AS
SELECT U.*,
       S.Name AS SourceName,
       S.TemplateID AS TemplateID,
       ML.SourceID AS ML_SourceID,
       ML.MediaID AS MediaID
FROM UnionCitations AS U
       LEFT JOIN
     SourceTable AS S ON U.SourceID = S.SourceID
       LEFT JOIN
     MediaLinkTableFilteredMasterSources AS ML ON S.SourceID = ML.SourceID
        INNER JOIN
     PersonTableView AS P ON U.RIN = P.PersonID; 
     
SELECT U.SortDate AS SortDate,
       U.Date AS Date,
       U.RIN AS RIN,
       N.Surname AS Surname,
       N.Given AS Given,
       N.BirthYear AS BirthYear,
       U.Abbrev AS Abbrev,
       U.SourceName AS SourceName,
       U.SourceID AS SourceID,
       U.MediaID AS MediaID,       
       CASE WHEN U.TemplateID <> 0 THEN 'Y' END AS Template,       
       MM.MediaPath AS Path,
       MM.MediaFile AS File
FROM CitationTableLeftJoinMedia AS U
       LEFT JOIN
     MultiMediaTable AS MM ON U.MediaID = MM.MediaID
       INNER JOIN
     PersonTableView AS P ON U.RIN = P.PersonID
       INNER JOIN
     NameTableView AS N ON N.OwnerID = P.PersonID 
WHERE SourceName IS NULL
          OR
      SourceID IS NULL
          OR
      U.MediaID IS NULL
          OR
      TemplateID IS NULL
          OR
      TemplateID = 0
          OR
      Path IS NULL
          OR
      File IS NULL

UNION

SELECT NULL AS SortDate,
       NULL AS Date,
       Rin,
       N.Surname AS Surname,
       N.Given AS Given,
       N.BirthYear,
       Abbrev,
       NULL AS SourceName,
       NULL AS SourceID,
       NULL AS MediaID,       
       NULL AS Template,       
       NULL AS Path,
       NULL AS File
FROM PersonsWithoutBurialInscription
         INNER JOIN
     NameTableView AS N ON N.OwnerID = Rin

UNION

SELECT NULL AS SortDate,
       NULL AS Date,
       Rin,
       N.Surname AS Surname,
       N.Given AS Given,
       N.BirthYear,
       Abbrev,
       NULL AS SourceName,
       NULL AS SourceID,
       NULL AS MediaID,       
       NULL AS Template,       
       NULL AS Path,
       NULL AS File
FROM PersonsWithoutBurialGPS
         INNER JOIN
     NameTableView AS N ON N.OwnerID = Rin
         
ORDER BY SortDate DESC, Abbrev, SourceName, N.BirthYear DESC, N.Surname, N.Given, U.Rin, Template, U.Abbrev, U.SourceName, MM.MediaPath;