-- Import_RM8_to_RM7-Part2A.sql
/* 2022-04-16 Tom Holden ve3meo
Imports a RM8 database into a RM7 database
except for ConfigTable, Folders or ResearchItems
Rev 2022-04-18 imports basic Tasks into ResearchTable
Rev 2022-04-20 imports TreeShare and FamilySearch linkages
Rev 2022-04-22 imports Tasks & Folders to To-Do's and Logs
more or less fully, given changes in database structuer
Rev 2022-06-12 import ConfigTable - so far appears compatible
               
EDIT 3 STATEMENTS FLAGGED BY **** FOR YOUR RM8 FILE AND MEDIA PATHS
*/

SELECT 'Import_RM8_to_RM7 Script started. 
Very large databases may take a couple of minutes to complete...' 
;
REINDEX
;
-- ****REVISE THE NEXT LINE FOR THE FULL PATH TO SOURCE RM8 DATABASE****
ATTACH DATABASE "C:\Users\Tom\Documents\FamilyTree\RM8\Bartels-Cremer-2.7 (3).rmtree" AS RM8db
;

/* TBD
ConfigTable --Can anything useful be copied from RM8?
*/

BEGIN Transaction
;

DELETE FROM ConfigTable
;
INSERT OR REPLACE INTO ConfigTable
 SELECT RecID
       ,RecType
       ,Title
       ,DataRec
 FROM RM8db.ConfigTable
;

DELETE FROM PersonTable
;
INSERT OR REPLACE INTO PersonTable
 SELECT PersonID
       ,UniqueID
       ,Sex
       ,UTCModDate AS EditDate
       ,ParentID  
       ,SpouseID
       ,Color -- have to map 28 color values to 15 yet
       ,Relate1
       ,Relate2
       ,Flags
       ,Living
       ,IsPrivate
       ,Proof
       ,0 AS Bookmark
       ,Note -- should this be cast as blob?
 FROM RM8db.PersonTable
; 

DELETE FROM NameTable
;
INSERT OR REPLACE INTO NameTable
 SELECT NameID
       ,OwnerID
       ,Surname
       ,Given
       ,Prefix
       ,Suffix
       ,Nickname
       ,NameType
       ,Date
       ,SortDate
       ,IsPrimary
       ,IsPrivate
       ,Proof
       ,UTCModDate AS EditDate
       ,Sentence
       ,Note
       ,BirthYear
       ,DeathYear
 FROM RM8db.NameTable
;

DELETE FROM FamilyTable
;
INSERT OR REPLACE INTO FamilyTable
 SELECT
   FamilyID
   ,FatherID
   ,MotherID
   ,ChildID
   ,HusbOrder
   ,WifeOrder
   ,IsPrivate
   ,Proof
   ,SpouseLabel
   ,FatherLabel
   ,MotherLabel
   ,Note
 FROM RM8db.FamilyTable
;

DELETE FROM ChildTable
;
INSERT OR REPLACE INTO ChildTable
 SELECT
   RecID
   ,ChildID
   ,FamilyID
   ,RelFather
   ,RelMother
   ,ChildOrder
   ,IsPrivate
   ,ProofFather
   ,ProofMother
   ,Note
 FROM RM8db.ChildTable
;

DELETE FROM FactTypeTable
;
INSERT OR REPLACE INTO FactTypeTable
 SELECT 
   FactTypeID
   ,OwnerType
   ,Name
   ,Abbrev
   ,GedcomTag
   ,UseValue
   ,UseDate
   ,UsePlace
   ,Sentence
   ,Flags
 FROM RM8db.FactTypeTable
;

DELETE FROM EventTable
;
INSERT OR REPLACE INTO EventTable
 SELECT
   EventID
   ,EventType
   ,OwnerType
   ,OwnerID
   ,FamilyID
   ,PlaceID
   ,SiteID
   ,Date
   ,SortDate
   ,IsPrimary
   ,IsPrivate
   ,Proof
   ,Status
   ,UTCModDate AS EditDate
   ,Sentence
   ,Details
   ,Note
 FROM RM8db.EventTable
;

DELETE FROM GroupTable
;
INSERT OR REPLACE INTO GroupTable
 SELECT
   RecID
   ,GroupID-1000
   ,StartID
   ,EndID
 FROM RM8db.GroupTable
;

DELETE FROM LabelTable
;
INSERT OR REPLACE INTO LabelTable
 SELECT
  TagID AS LabelID
  ,TagType AS LabelType
  ,TagValue-1000 AS LabelValue
  ,TagName AS LabelName
  ,Description
 FROM RM8db.TagTable
 WHERE TagType=0 --Group Names only
;

DELETE FROM PlaceTable
;
INSERT OR REPLACE INTO PlaceTable
 SELECT
   PlaceID
   ,PlaceType
   ,Name
   ,Abbrev
   ,Normalized
   ,Latitude
   ,Longitude
   ,LatLongExact
   ,MasterID
   ,Note
 FROM RM8db.PlaceTable
; 

DELETE FROM MultiMediaTable
;
INSERT OR REPLACE INTO MultiMediaTable
 SELECT
   MediaID
   ,MediaType
   ,MediaPath
   ,MediaFile
   ,URL
   ,Thumbnail
   ,Caption
   ,RefNumber
   ,Date
   ,SortDate
   ,Description
 FROM RM8db.MultiMediaTable
;   

DELETE FROM MediaLinkTable
;
INSERT OR REPLACE INTO MediaLinkTable
 SELECT
   LinkID
   ,MediaID
   ,OwnerType
   ,OwnerID
   ,IsPrimary
   ,Include1
   ,Include2
   ,Include3
   ,Include4
   ,SortOrder
   ,RectLeft
   ,RectTop
   ,RectRight
   ,RectBottom
   ,Comments AS Note
   ,NULL AS Caption
   ,NULL AS RefNumber
   ,NULL AS Date
   ,NULL AS SortDate
   ,NULL AS Description
 FROM RM8db.MediaLinkTable
;

DELETE FROM SourceTemplateTable 
WHERE TemplateID>9999
;
INSERT OR REPLACE INTO SourceTemplateTable
  SELECT
    TemplateID
    ,Name
    ,Description
    ,Favorite
    ,Category
    ,Footnote
    ,ShortFootnote
    ,Bibliography
    ,FieldDefs
  FROM RM8db.SourceTemplateTable
  WHERE RM8db.SourceTemplateTable.TemplateID>9999
;

DELETE FROM SourceTable
;
INSERT OR REPLACE INTO SourceTable
  SELECT
    SourceID
    ,Name
    ,RefNumber
    ,ActualText
    ,Comments
    ,IsPrivate
    ,TemplateID
    ,Fields
   FROM RM8db.SourceTable
;    

DELETE FROM CitationTable
;
INSERT OR REPLACE INTO CitationTable
  SELECT
    CitationID
    ,OwnerType
    ,SourceID
    ,OwnerID
    ,Quality
    ,IsPrivate
    ,Comments
    ,ActualText
    ,RefNumber
    ,Flags
    ,Fields
  FROM RM8db.CitationTable 
  JOIN RM8db.CitationLinkTable 
  USING(CitationID)
;

DELETE FROM RoleTable
;
INSERT OR REPLACE INTO RoleTable
  SELECT
    RoleID
    ,RoleName
    ,EventType
    ,RoleType
    ,Sentence
  FROM RM8db.RoleTable
;

DELETE FROM WitnessTable
;
INSERT OR REPLACE INTO WitnessTable
  SELECT
    WitnessID
    ,EventID
    ,PersonID
    ,WitnessOrder
    ,Role
    ,Sentence
    ,Note
    ,Given
    ,Surname
    ,Prefix
    ,Suffix
  FROM RM8db.WitnessTable
;

DELETE FROM URLTable
;
INSERT OR REPLACE INTO URLTable
  SELECT
    LinkID
    ,OwnerType
    ,OwnerID
    ,LinkType
    ,Name
    ,URL
    ,Note
  FROM RM8db.URLTable
;

DELETE FROM AddressTable
;
INSERT OR REPLACE INTO AddressTable
  SELECT
    AddressID
    ,AddressType
    ,Name
    ,Street1
    ,Street2
    ,City
    ,State
    ,Zip
    ,Country
    ,Phone1
    ,Phone2
    ,Fax
    ,Email
    ,URL
    ,Latitude
    ,Longitude
    ,Note
  FROM RM8db.AddressTable
;

DELETE FROM AddressLinkTable
;
INSERT OR REPLACE INTO AddressLinkTable
  SELECT
    LinkID
    ,OwnerType
    ,AddressID
    ,OwnerID
    ,AddressNum
    ,Details
  FROM RM8db.AddressLinkTable
;    

DELETE FROM ExclusionTable
;
INSERT OR REPLACE INTO ExclusionTable
  SELECT
    RecID
    ,ExclusionType
    ,ID1
    ,ID2
  FROM RM8db.ExclusionTable
; 

DELETE FROM LinkAncestryTable 
;
INSERT OR REPLACE INTO LinkAncestryTable
  SELECT
    LinkID
    ,2 AS extSystem
    ,LinkType
    ,rmID
    ,anID AS extID
    ,Modified
    ,anVersion as extVersion
    ,anDate as extDate
    ,Status
    ,NULL AS Note
  FROM RM8db.AncestryTable
;

DELETE FROM LinkTable 
;
INSERT OR REPLACE INTO LinkTable
  SELECT
    LinkID
    ,1 AS extSystem
    ,LinkType
    ,rmID
    ,fsID AS extID
    ,Modified
    ,fsVersion as extVersion
    ,fsDate as extDate
    ,Status
    ,NULL AS Note
  FROM RM8db.FamilySearchTable
;  

--RM8toRM7Research.sql
DELETE FROM ResearchTable
;
--Create basic tasks in ResearchTable
INSERT OR REPLACE INTO ResearchTable
  SELECT
    TaskID
    ,MAX(0,TT.TaskType-2) AS TaskType -- 2=To-Do=>0, 3=Correspondence=>1, ignore 1=Log/Folder
    ,IFNULL(OwnerID,0) AS OwnerID
    ,IFNULL(OwnerType,8) AS OwnerType 
    ,RefNumber
    ,Name
    ,Status-1 AS Status -- RM8 New and Cancelled have no equivalent status in RM7
    ,Priority
    ,Date1
    ,Date2
    ,Date3
    ,SortDate1
    ,SortDate2
    ,SortDate3
    ,Filename
    ,Details
  FROM RM8db.TaskTable TT
  LEFT JOIN RM8db.TaskLinkTable TL USING(TaskID)
--  WHERE TaskType IN (0,2)
  WHERE TL.OwnerType<>18 -- ignore Log/Folder for now
  -- AND TT.TaskType>1
;

--Convert Tasks for Events to Individual or Family owning the event
UPDATE ResearchTable
SET (OwnerID, OwnerType) =
(SELECT E.OwnerID, E.OwnerType 
FROM EventTable E
WHERE ResearchTable.OwnerID=E.EventID
)
WHERE OwnerType=2
;

--Temp list of TagIDs for RM8 Folders 
--and the TaskID to be assigned for a RM7 Log in ResearchTable
;
CREATE  Temp Table IF NOT EXISTS FolderLogID
AS
SELECT
  (SELECT MAX(TaskID)FROM ResearchTable)+TG.TagID AS LogID
  ,TL.TaskID     -- to infer owner of folder(log) from a task tied to it
  ,TL2.OwnerType -- ditto 
  ,TL2.OwnerID   -- ditto
  ,TG.*          -- folder name and description
FROM TagTable TG -- contains group names and folder names (TagTypes 0,1 resp)
LEFT JOIN TaskLinkTable TL
ON TG.TagID=TL.OwnerID AND TL.OwnerType=18 -- filter for records that link to TagTable
LEFT JOIN TaskLinkTable TL2 
USING(TaskID) 
WHERE TL2.OwnerType <>18
GROUP BY TG.TagID -- to get one record only for each folder
--WHERE TL.OwnerType = 18
;

--Create Log records from TagNames
INSERT OR REPLACE INTO ResearchTable
SELECT 
  FL.LogID AS TaskID
  ,2 AS TaskType
  --could we infer owner from the TaskLinkTable for the same TaskID?
  ,IFNULL(FL.OwnerID,0) AS OwnerID
  ,IFNULL(FL.OwnerType,8) AS OwnerType 
  ,'' AS RefNumber
    ,FL.TagName AS Name
    ,NULL AS Status --Status-1 AS Status -- RM8 New and Cancelled have no equivalent status in RM7
    ,NULL AS Priority
    ,'.' AS Date1
    ,'.' AS Date2
    ,'.' AS Date3
    ,null AS SortDate1
    ,null AS SortDate2
    ,null AS SortDate3
    ,'' AS Filename
    ,FL.Description AS Details
  FROM FolderLogID FL
;

DELETE FROM ResearchItemTable
;
--Create ResearchItemsTable records (items in Research Log)
INSERT OR REPLACE INTO ResearchItemTable
SELECT 
  TL.LinkID AS ItemID --could be null, just using LinkID to help relate back to TLTable
  ,FL.LogID 
  ,TT.Date1 AS Date
  ,TT.SortDate1 AS SortDate
  ,TT.RefNumber AS RefNumber
  ,'' AS Repository
--  ,TT.Name ||CHAR(13)||CHAR(10)||TT.Details AS Goal
  ,TT.Details AS Goal
  ,'' AS Source
  ,TT.Results AS Result
FROM RM8db.TaskLinkTable TL
JOIN FolderLogID FL 
ON TL.OwnerID=FL.TagID AND TL.OwnerType=18
JOIN RM8db.TaskTable TT USING(TaskID)
;

COMMIT Transaction
;
DETACH DATABASE RM8db
;

--****EDIT FOLLOWING STATEMENT TO SET THE ~ SYMBOL TO YOUR USER PATH****
UPDATE MultiMediaTable
SET MediaPath=REPLACE(MediaPath,'~\','C:\Users\Tom\')
;
--****EDIT FOLLOWING STATEMENT TO SET THE * SYMBOL TO YOUR MEDIA PATH****
UPDATE MultiMediaTable
SET MediaPath=REPLACE(MediaPath,'*\','C:\Users\Tom\Documents\FamilyTree\RM8\')
;

SELECT 'Script completed. '
        ||'RUN DATABASE TOOLS IN RM7' AS Status
;