-- Reports-RestoreAfterPointForm2.sql
/*
2016-02-01 Tom Holden ve3meo
2016-04-05 rev to complement Reports-PointFormNarrativeSetup2.sql

Restores after having run Reports-PointFormNarrativeSetup2.sql
the prior FactTypeTable, RoleTable and custom sentences in
EventTable, NameTable and WitnessTable and the last couple Notes
in EventTable 

*/

-- Test that backup tables exist - fail if not to prevent unexpected consequences
SELECT * FROM xEventTableSentences WHERE RowID=1
;
SELECT * FROM xFactTypeTable WHERE RowID=1
;
SELECT * FROM xNameTableSentences WHERE RowID=1
;
SELECT * FROM xRoleTable WHERE RowID=1
;
SELECT * FROM xWitnessTableSentences WHERE RowID=1
;
SELECT * FROM xLastCoupleEvent WHERE RowID=1
;

BEGIN
;
-- Delete _Heading events
DELETE FROM EventTable
WHERE EventType = 
(SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE '_Heading')
;

-- Restore FactTypeTable
INSERT OR REPLACE INTO FactTypeTable
SELECT * FROM xFactTypeTable
;

-- Restore RoleTable
INSERT OR REPLACE INTO RoleTable
SELECT * FROM xRoleTable
;

-- Restore local custom sentences to EventTable
UPDATE EventTable
SET Sentence = ''
;
UPDATE EventTable
SET Sentence =
(SELECT Sentence FROM xEventTableSentences X WHERE EventTable.EventID = X.EventID)
WHERE EventID IN (SELECT EventID FROM xEventTableSentences)
;

-- Restore Notes for last Couple Events that had <CR>s appended
UPDATE EventTable
SET Note = (SELECT Note FROM xLastCoupleEvent X WHERE EventID=X.EventID)
WHERE EventID
  IN (SELECT EventID FROM xLastCoupleEvent)
; 

-- Restore local custom sentences to NameTable
UPDATE NameTable
SET Sentence = ''
;
UPDATE NameTable
SET Sentence =
(SELECT Sentence FROM xNameTableSentences X WHERE NameTable.NameID = X.NameID)
WHERE NameID IN (SELECT NameID FROM xNameTableSentences)
;

-- Restore local custom sentences to WitnessTable
UPDATE WitnessTable
SET Sentence = ''
;
UPDATE WitnessTable
SET Sentence =
(SELECT Sentence FROM xWitnessTableSentences X WHERE WitnessTable.WitnessID = X.WitnessID)
WHERE WitnessID IN (SELECT WitnessID FROM xWitnessTableSentences)
;

DROP TABLE xEventTableSentences
;
DROP TABLE xFactTypeTable
;
DROP TABLE xNameTableSentences
;
DROP TABLE xRoleTable
;
DROP TABLE xWitnessTableSentences
;
DROP TABLE xLastCoupleEvent
;
COMMIT
;
SELECT 'Reports-RestoreAfterPointForm2.sql script completed without error' AS Status
-- end of script