-- RM8_WaymarksViews.sql
/*
2014-10-27 Tom Holden ve3meo as RM7_5_WaymarksViews
2017-07-01 rev TH 
- added LinkAncestryWay for LinkAncestryTable added
by RM 7.5.
- corrected null results for family couple with unknown spouse (RIN=0)

-- 2021-01-25 revised for RM8 database changes by Pat Jones

Creates temporary views of all or most tables in the RootsMagic 8
database with the first column filled with Waymarks to aid in 
navigating through RootsMagic to the screen that controls the 
data contained in that table.

Requires a SQLite manager with a RMNOCASE collation sequence.

*/

-----Names of Persons concatenated into one column, "Person"
DROP VIEW IF EXISTS NameWay
;
CREATE TEMP VIEW NameWay
AS
SELECT
  CASE WHEN IsPrimary THEN '' ELSE '+' END  
  || CASE WHEN PREFIX NOT LIKE '' THEN PREFIX || ' ' ELSE '' END  
  || CASE WHEN GIVEN NOT LIKE '' THEN GIVEN || ' ' ELSE '' END 
  || CASE WHEN NICKNAME NOT LIKE '' THEN '"'|| NICKNAME || '" ' ELSE '' END 
  || SURNAME 
  || CASE WHEN SUFFIX NOT LIKE '' THEN ', '|| SUFFIX ELSE '' END 
  || '-' || OwnerID  
  AS Person  
  , OwnerID AS RIN
  , *
FROM NameTable
;

----- Concatenated Names of partners in families in columns Person1 and Person2
DROP VIEW IF EXISTS FamilyWay
;
CREATE TEMP VIEW FamilyWay
AS
SELECT
  IFNULL((SELECT Person FROM NameWay NW WHERE FatherID = NW.OwnerID AND NW.IsPrimary), '_____')
 || ' & ' || 
  IFNULL((SELECT Person FROM NameWay NW WHERE MotherID = NW.OwnerID AND NW.IsPrimary), '_____') AS Waymarks
 , FT.* 
FROM FamilyTable FT
;
---- Place, PlaceDetail
DROP VIEW IF EXISTS PlaceWay
;
CREATE TEMP VIEW PlaceWay
AS
SELECT
  CASE P1.PlaceType 
   WHEN 0 THEN P1.Name   
   WHEN 2 THEN P2.Name || CHAR(13) || ' ' || P1.Name
   ELSE ''
   END   
   AS Place   
  , P1.*
FROM PlaceTable P1
LEFT JOIN PlaceTable P2
ON P1.[MasterID]=P2.[PlaceID]
WHERE P1.PlaceType <> 1
; 

-- Event Pointer
DROP VIEW IF EXISTS EventWay
;
CREATE TEMP View EventWay
AS
SELECT
  CASE E.OwnerType  
    WHEN 0 THEN NW.Person
    WHEN 1 THEN FW.Waymarks
    ELSE 'TBD'    
  END || CHAR(13) 
  || ' ' ||  FT.Abbrev || ' ' || IFNULL(SUBSTR(E.Date, 4,4),'') 
  AS Waymarks
  , E.*    
FROM EventTable E
JOIN FactTypeTable FT
ON E.EventType = FT.FactTypeID
LEFT JOIN NameWay NW
ON E.OwnerID = NW.OwnerID
LEFT JOIN FamilyWay FW
ON E.OwnerID = FW.FamilyID
WHERE NW.IsPrimary
;

-- FactType Pointer
DROP VIEW IF EXISTS FactTypeWay
;
CREATE TEMP VIEW FactTypeWay
AS
SELECT
  'Fact Type' || CHAR(13) || ' ' || Name AS Waymarks
  , * 
FROM FactTypeTable
;

---- Citation Pointer
--RM8 Citations split to CitationTable and CitationLinkTable
DROP VIEW IF EXISTS CitationWay
;
CREATE TEMP View CitationWay
AS
SELECT
  *  
FROM
(
-- Person & Family citations
SELECT
  CASE Cl.OwnerType  
    WHEN 0 THEN NW.Person    
    WHEN 1 THEN FW.Waymarks 
    ELSE 'TBD'    
  END || CHAR(13) || ' citing ' || S.Name AS Waymarks
  , C.*,cl.*  
FROM CitationTable C
JOIN SourceTable S
USING(SourceID)
LEFT OUTER JOIN CitationLinkTable cl
ON C.CitationID = cl.CitationID
LEFT JOIN NameWay NW
ON cl.OwnerID = NW.OwnerID
LEFT JOIN FamilyWay FW
ON cl.OwnerID = FW.FamilyID
WHERE cl.OwnerType IN (0,1)
AND NW.IsPrimary

UNION ALL 

-- Cit pointer for INDI and FAM events
SELECT 
  EW.Waymarks || CHAR(13) || ' citing ' || S.Name AS Waymarks
  , C.*,cl.*  
FROM CitationTable C
JOIN SourceTable S
USING(SourceID)
LEFT OUTER JOIN CitationLinkTable cl
ON C.CitationID = cl.CitationID
JOIN EventWay EW
ON cl.OwnerID = EW.EventID
WHERE cl.OwnerType = 2 

UNION ALL 

-- Cit pointer for Alternate Names
SELECT
  NW.Person || CHAR(13) 
  || CASE NW.IsPrimary
     WHEN 1 THEN ' (Name)'
     ELSE ' (Alt Name)'
     END
  || CHAR(13) || ' citing ' || S.Name AS Waymarks 
  , C.*,cl.*
FROM CitationTable C
JOIN SourceTable S
USING(SourceID)
LEFT OUTER JOIN CitationLinkTable cl
ON C.CitationID = cl.CitationID
JOIN NameWay NW
ON cl.OwnerID = NW.NameID
WHERE cl.OwnerType = 7 --AND NOT NW.IsPrimary
)
;
--End of CitationWay

--RoleWay
DROP VIEW IF EXISTS RoleWay
;
CREATE TEMP VIEW RoleWay
AS
SELECT
  'Fact Type' || CHAR(13) || FT.Name || CHAR(13) || '  ' || RoleName AS [Waymarks]
  , R.* 
FROM RoleTable R
JOIN FactTypeTable FT
ON EventType = FactTypeID
;

--WitnessWay
DROP VIEW IF EXISTS WitnessWay
;
CREATE TEMP VIEW WitnessWay
AS
SELECT
  NW.Person ||
  CHAR(13) || ' shared ' || EW.Waymarks AS [Waymarks]  
  , W.*
FROM WitnessTable W
JOIN temp.NameWay NW  
ON W.PersonID = NW.OwnerID
JOIN temp.EventWay EW
USING(EventID)
WHERE NW.IsPrimary
;

--AddressPointer
DROP VIEW IF EXISTS AddressWay
;
CREATE TEMP VIEW AddressWay
AS
SELECT
  CASE AddressType  
      WHEN 0 THEN 'Address'      
      WHEN 1 THEN 'Repository'      
      ELSE 'TBD'      
  END || ' List' || CHAR(13) ||
  Name || CHAR(13) || City || ', ' || State AS Waymarks
  , *  
FROM AddressTable
;

--ResearchPointer
-- RM8 research and Research Items changed to TaskTable, TaskLinkTable and TagTable for the labels
DROP VIEW IF EXISTS ResearchWay
;
CREATE TEMP VIEW ResearchWay
AS
SELECT
  CASE  
	-- RM8 task types changed 
		WHEN RT.TaskType = 2 THEN 'ToDo'
		WHEN RT.TaskType = 3 THEN 'Correspondence'
		WHEN RT.TaskType = 1 THEN 'Research Log'
      ELSE 'TBD'      
    END || ' (' ||
    CASE   
	-- RM8 owner type 8 removed and replaced with null owner
	-- RM8 owner type 18 added
	WHEN tl.OwnerType = 0 THEN 'Individual'
	WHEN tl.OwnerType = 1 THEN 'Family'
	WHEN tl.OwnerType = 2 THEN 'Event'
	WHEN tl.OwnerType = 5 THEN 'Place'
	WHEN tl.OwnerType IS NULL THEN 'General'
	WHEN tl.OwnerType = 18 THEN 'Research Log'
      ELSE 'TBD'      
    END  || ')' || CHAR(13) ||   
    Name || CHAR(13) ||
    CASE tl.OwnerType  
      WHEN 0 THEN (SELECT Person FROM temp.NameWay NW WHERE tl.OwnerID = NW.OwnerID AND NW.IsPrimary)      
      WHEN 1 THEN (SELECT Waymarks FROM temp.[FamilyWay] FW WHERE tl.OwnerID = FW.FamilyID)      
      WHEN NULL THEN 'General'
	  WHEN 18 THEN 'Research Log'
      ELSE 'TBD'      
    END AS Waymarks
  , RT.* , tl.*   
FROM TaskTable RT LEFT OUTER JOIN TaskLinkTable tl
ON RT.TaskID = tl.TaskID
;
-- research log name = tagTable with tagType=1 and TagID = logID
--ResearchItemPointer (Research Logs)
DROP VIEW IF EXISTS ResearchItemWay
;
CREATE TEMP VIEW ResearchItemWay
AS
SELECT
  'Research Manager' || CHAR(13) || Waymarks AS Waymarks  
  , RIT.*,tg.*
FROM TaskTable RIT
JOIN temp.ResearchWay RW
ON RIT.TaskID = RW.TaskID
LEFT JOIN TagTable tg
ON RW.OwnerID=tg.TagID
AND RW.OwnerType=18
;  

--MultimediaPointer (Media Gallery)
DROP VIEW IF EXISTS MultimediaWay
;
CREATE TEMP VIEW MultimediaWay
AS
SELECT
  'Media Gallery'  || CHAR(13) || 'Filename: ' || MediaFile || CHAR(13) || 'Caption: ' || Caption 
   AS Waymarks   
  , *
FROM MultimediaTable
;

--MediaLinkPointer (MediaLinkTable or MediaTags)
DROP VIEW IF EXISTS MediaLinkWay
;
CREATE TEMP VIEW MediaLinkWay
AS
SELECT
  MC.Waymarks AS List
  , 'Tag ' ||
    CASE OwnerType
    WHEN 0 THEN     
      'person: ' || (SELECT Person FROM temp.NameWay NW WHERE MLT.OwnerID = NW.OwnerID AND NW.IsPrimary)      
    WHEN 1 THEN    
      'couple: ' || (SELECT Waymarks FROM temp.FamilyWay FW WHERE MLT.OwnerID = FW.FamilyID)      
    WHEN 2 THEN    
      'event: ' || (SELECT Waymarks FROM temp.EventWay EW WHERE MLT.OwnerID = EW.EventID) 
    WHEN 3 THEN
      'source: ' || (SELECT Name FROM SourceTable S WHERE MLT.OwnerID = S.SourceID)
    WHEN 4 THEN
      'citation: ' || (SELECT Waymarks FROM temp.CitationWay CW WHERE MLT.OwnerID = CW.CitationID)
    WHEN 5 THEN
      'place: ' || (SELECT Place FROM temp.PlaceWay P WHERE MLT.OwnerID = P.PlaceID)     
    ELSE 'TBD'    
    END AS Tag    
  , MLT.*
FROM MediaLinkTable MLT
JOIN temp.[MultimediaWay] MC
USING(MediaID)
;

--URLpointer  (Web Tags)
DROP VIEW IF EXISTS URLWay
;
CREATE TEMP VIEW URLWay
AS
SELECT
  'Web Tag for:' || CHAR(13) ||
    CASE UT.OwnerType
    WHEN 0 THEN     
      'person: ' || (SELECT Person FROM temp.NameWay NW WHERE UT.OwnerID = NW.OwnerID AND NW.IsPrimary)      
    WHEN 1 THEN    
      'couple: ' || (SELECT Waymarks FROM temp.FamilyWay FW WHERE UT.OwnerID = FW.FamilyID)      
    WHEN 2 THEN    
      'event: ' || (SELECT Waymarks FROM temp.EventWay EW WHERE UT.OwnerID = EW.EventID) 
    WHEN 3 THEN
      'source: ' || (SELECT Name FROM SourceTable S WHERE UT.OwnerID = S.SourceID)
    WHEN 4 THEN
      'citation: ' || (SELECT Waymarks FROM temp.CitationWay CW WHERE UT.OwnerID = CW.CitationID)
    WHEN 5 THEN
      'place: ' || (SELECT Place FROM temp.PlaceWay PW WHERE UT.OwnerID = PW.PlaceID)     
    WHEN 15 THEN
      (SELECT Waymarks FROM temp.ResearchItemWay RIW WHERE UT.OwnerID = RIW.TaskID)     
    ELSE 'TBD'    
    END AS Waymarks    
  , *
FROM URLTable UT
;

--LabelPointer  (Group names)
-- RM8 label table changed to TagTable
DROP VIEW IF EXISTS LabelWay
;
CREATE TEMP VIEW LabelWay
AS
SELECT
  CASE TagType  
    WHEN 0 THEN    
      'Group: ' || CHAR(13) || TagName      
    ELSE 'Label: TBD'    
    END AS Waymarks    
  , *
FROM
TagTable
;

-- Link Pointer (FSFT)
--RM8 Link table changed to FamilySearchTable
DROP VIEW IF EXISTS LinkWay
;
CREATE TEMP VIEW LinkWay
AS
SELECT
  'FamilySearch' AS extSysNam 
  , CASE LinkType  
    WHEN 0 THEN    
      (SELECT Person || ' (' || fsID || ')' FROM temp.NameWay NW WHERE rmID = NW.OwnerID)
    ELSE 'TBD'
    END AS Waymarks    
  , *
FROM FamilySearchTable 
;

-- LinkAncestry Pointer (Ancestry)
--RM8 Link Ancestry table changed to AncestryTable
DROP VIEW IF EXISTS LinkAncestryWay
;
CREATE TEMP VIEW LinkAncestryWay
AS
SELECT
  'Ancestry' AS extSysNam 
  , CASE LinkType  
    WHEN 0 THEN    
      (SELECT Person || ' (' || anID || ')' FROM temp.NameWay NW WHERE rmID = NW.OwnerID)
  	WHEN 4 THEN 
	    (SELECT Waymarks FROM temp.CitationWay CW WHERE rmID = CW.CitationID)
  	WHEN 11 THEN 
	    (SELECT Waymarks FROM temp.MultimediaWay MMW WHERE rmID = MMW.MediaID)
    ELSE 'TBD'
    END AS Waymarks    
  , *
FROM AncestryTable 
;