-- Import_RM9_to_RM7.sql
/* 2023-06-11 Tom Holden ve3meo
Imports a RM9 database into a RM7 database
except for Folders or ResearchItems.
Now does not cause access errors on extremely split sources
from templates having no Citation field.

Derived from Import_RM8_to_RM7_V3.sql Rev 2022-06-21
2022-04-16 Tom Holden ve3meo
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
V3 Rev 2022-06-19 corrected failure to convert reused Citations to individual Citations
 - plus related Media tags, Web tags, TreeShare links
 Rev 2022-06-21 corrected typos throwing media and web tags off
                downgrade Version in ConfigTable else RM7 refuses to open the file
               
EDIT 4 STATEMENTS FLAGGED BY **** FOR YOUR RM9 FILE PATH AND FOR RM9 WILDCARD MEDIA PATHS
*/

SELECT 'Import_RM9_to_RM7 Script started. 
Very large databases may take a couple of minutes to complete...' 
;

REINDEX -- N.B. rebuild indexes in RM on reopening the databases
;

-- ****REVISE THE NEXT LINE FOR THE FULL PATH TO SOURCE RM9 DATABASE****
ATTACH DATABASE "C:\Users\Tom\Documents\FamilyTree\RM9\TestSourcesFromTestOriginalAtree.rmtree" AS RM9db
;

BEGIN Transaction
;

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

--Downgrade Version declaration from 9000 to 6000 else RM7 won't open the file
UPDATE ConfigTable
  SET DataRec=CAST(REPLACE(DataRec, '<Root><Version>9000', '<Root><Version>6000') AS BLOB)
  WHERE RecID=1
;


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 RM9db.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 RM9db.NameTable
;

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

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

DELETE FROM FactTypeTable
;
INSERT OR REPLACE INTO FactTypeTable
 SELECT 
   FactTypeID
   ,OwnerType
   ,Name
   ,Abbrev
   ,GedcomTag
   ,UseValue
   ,UseDate
   ,UsePlace
   ,Sentence
   ,Flags
 FROM RM9db.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 RM9db.EventTable
;

DELETE FROM GroupTable
;
INSERT OR REPLACE INTO GroupTable
 SELECT
   RecID
   ,GroupID-1000
   ,StartID
   ,EndID
 FROM RM9db.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 RM9db.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 RM9db.PlaceTable
; 

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


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

DELETE FROM SourceTable
;
INSERT OR REPLACE INTO SourceTable
  SELECT
    SourceID
    ,Name
    ,RefNumber
    ,ActualText
    ,Comments
    ,IsPrivate
    ,TemplateID
    ,CAST('<?xml version="1.0" encoding="UTF-8"?>'||CHAR(13)||CHAR(10)||Fields AS Blob)
      -- 2023-06-10 added XML prolog as doe by RM7 and cast as blob
   FROM RM9db.SourceTable
;    


-- Generate a temp table to create individual citations from all RM9uses (Citation Links) rev 2022-06-19
;
CREATE TEMP TABLE NewCitationTable AS
  SELECT
    Null AS RM7CitationID
    ,CitationID AS RM9CitationID
    ,OwnerType
    ,SourceID
    ,OwnerID
    ,Quality
    ,IsPrivate
    ,Comments
    ,ActualText
    ,RefNumber
    ,Flags
    ,REPLACE(Fields,'<Root />','<Root><Fields/></Root>') AS Fields
    -- 2023-06-09 <Root /> causes access error in RM7
  FROM RM9db.CitationTable 
  JOIN RM9db.CitationLinkTable 
  USING(CitationID)
  ORDER BY CitationID
;

-- Assign new CitationIDs for RM7 rev 2022-06-19
UPDATE NewCitationTable SET RM7CitationID=ROWID
;

-- Transfer new Citations to RM7 rev 2022-06-19
DELETE FROM CitationTable
;
INSERT OR REPLACE INTO CitationTable
  SELECT
    RM7CitationID
    ,OwnerType
    ,SourceID
    ,OwnerID
    ,Quality
    ,IsPrivate
    ,Comments
    ,ActualText
    ,RefNumber
    ,Flags
    ,CAST('<?xml version="1.0" encoding="UTF-8"?>'||CHAR(13)||CHAR(10)||Fields AS Blob)
      -- 2023-06-10 added XML prolog as is normal in RM7
  FROM NewCitationTable
;

DELETE FROM MediaLinkTable
;
--Tags for all items other than Citations rev 2022-06-19
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 RM9db.MediaLinkTable
 WHERE OwnerType<>4 -- except links to Citations rev 2022-06-19
;

--Tags for Citations including reused now converted to individual rev 2022-06-19
INSERT INTO MediaLinkTable
 SELECT
   Null AS LinkID
   ,MediaID
   ,4 AS OwnerType
   ,RM7CitationID AS OwnerID
   ,IsPrimary
   ,Include1
   ,Include2
   ,Include3
   ,Include4
   ,SortOrder
   ,RectLeft
   ,RectTop
   ,RectRight
   ,RectBottom
   ,RM9ML.Comments AS Note
   ,NULL AS Caption
   ,NULL AS RefNumber
   ,NULL AS Date
   ,NULL AS SortDate
   ,NULL AS Description
 FROM RM9db.MediaLinkTable RM9ML
 JOIN NewCitationTable RM7NC
 ON RM9ML.OwnerID = RM7NC.RM9CitationID
 WHERE RM9ML.OwnerType=4 -- only links to Citations
;


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

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

DELETE FROM URLTable
;
INSERT OR REPLACE INTO URLTable
  SELECT
    LinkID
    ,OwnerType
    ,OwnerID
    ,LinkType
    ,Name
    ,URL
    ,Note
  FROM RM9db.URLTable
  WHERE OwnerType<>4 -- except links to Citations rev 2022-06-19
;

-- WebTags for individual Citations generated from RM9 Citation Links rev 2022-06-19
INSERT INTO URLTable
  SELECT
    Null AS LinkID
    ,4 AS OwnerType
    ,RM7CitationID AS OwnerID
    ,LinkType
    ,Name
    ,URL
    ,Note
  FROM RM9db.URLTable RM9U
 JOIN NewCitationTable RM7NC
 ON RM9U.OwnerID = RM7NC.RM9CitationID
 WHERE RM9U.OwnerType=4 -- only links to Citations
;

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 RM9db.AddressTable
;

DELETE FROM AddressLinkTable
;
INSERT OR REPLACE INTO AddressLinkTable
  SELECT
    LinkID
    ,OwnerType
    ,AddressID
    ,OwnerID
    ,AddressNum --RM7 seems to want this to be 1, not 0, for a Repository which is AddressTable.AddressType=1
    ,Details
  FROM RM9db.AddressLinkTable
--  WHERE OwnerType<>3 -- 2023-06-10 except Source Repository. Maybe there is no other type from TreeShare.
;

--Set AddressNum=1 for AddressType=1 2023-06-11
UPDATE AddressLinkTable
SET AddressNum=1
WHERE
OwnerType=3 
AND AddressID 
IN (SELECT AddressID FROM AddressTable WHERE AddressType=1)
;    

DELETE FROM ExclusionTable
;
INSERT OR REPLACE INTO ExclusionTable
  SELECT
    RecID
    ,ExclusionType
    ,ID1
    ,ID2
  FROM RM9db.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 RM9db.AncestryTable
  WHERE LinkType<>4 -- except links to Citations rev 2022-06-19
;

-- TreeShare links for indiv RM7 Citations generated from RM9 uses rev 2022-06-19
INSERT INTO LinkAncestryTable
  SELECT
    Null AS LinkID
    ,2 AS extSystem
    ,4 AS LinkType
    ,rm7CitationID AS rmID --2023-06-11 corrected error that fetched the RM9 rmID instead of the new RM7 value
    ,anID AS extID
    ,Modified
    ,anVersion as extVersion
    ,anDate as extDate
    ,Status
    ,'' AS Note
  FROM RM9db.AncestryTable RM9
  JOIN NewCitationTable RM7
  ON RM9.rmID = RM7.RM9CitationID
  WHERE RM9.LinkType=4 -- only links to Citations
;


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 RM9db.FamilySearchTable
;  

--RM9toRM7Research.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 -- RM9 New and Cancelled have no equivalent status in RM7
    ,Priority
    ,Date1
    ,Date2
    ,Date3
    ,SortDate1
    ,SortDate2
    ,SortDate3
    ,Filename
    ,Details
  FROM RM9db.TaskTable TT
  LEFT JOIN RM9db.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 RM9 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 -- RM9 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 RM9db.TaskLinkTable TL
JOIN FolderLogID FL 
ON TL.OwnerID=FL.TagID AND TL.OwnerType=18
JOIN RM9db.TaskTable TT USING(TaskID)
;

COMMIT Transaction
;
DETACH DATABASE RM9db
;

--****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 RM9 DATABASE PATH****
UPDATE MultiMediaTable
SET MediaPath=REPLACE(MediaPath,'*\','C:\Users\Tom\Documents\FamilyTree\RM9\')
;
--****EDIT FOLLOWING STATEMENT TO SET THE ? SYMBOL TO YOUR DEFAULT MEDIA PATH****
UPDATE MultiMediaTable
SET MediaPath=REPLACE(MediaPath,'?','C:\Users\Tom\Documents\FamilyTree\RM9\Media')
;

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