-- EventDescriptionToNote-Move.sql
/*
2016-05-08 Tom Holden ve3meo
-- adapted from EventNoteToDescription-Move.sql
Moves content from Event Description (Details) to Event Note 
for Fact Type whose abbreviation (the label as 
seen in the Edit Person screen) matches the value entered when run. 
*/
BEGIN TRANSACTION;

UPDATE
	OR
ROLLBACK EventTable
SET Note = 
  CASE 
    WHEN LENGTH(TRIM(Note)) 
    THEN CAST(TRIM(Note) || CHAR(0x0A)||CHAR(0x0A) || TRIM(Details) AS BLOB)
    ELSE CAST(TRIM(Details) AS BLOB)
  END 
    , Details = CAST('' AS BLOB) -- Description emptied
WHERE EventID IN (
		-- List of EventIDs for particular fact type having a non-empty 
		-- description.
		SELECT EventID --, Description, Note -- uncomment to view
		FROM (
			-- list of event ID, Description, Note for particular fact type
			SELECT EventID
				,CAST(Details AS TEXT) AS Description
				,CAST(Note AS TEXT) AS Note
			FROM EventTable E
			INNER JOIN FactTypeTable F ON E.EventType = F.FactTypeID
			WHERE E.EventType IN (
					-- FactTypeID of Fact Type whose abbreviation matches the runtime value entered
					SELECT FactTypeID
					FROM FactTypeTable F
					WHERE Abbrev LIKE $ABBREV -- enter the label from the Edit Person screen at runtime
					)
			)
		WHERE LENGTH(Description) > 0 -- non-empty Description
		);

COMMIT TRANSACTION;
	-- END