-- Places-ConvertPlaceDetailsToPlaces.sql
/*
2015-01-21 Tom Holden ve3meo
Revises the names of all Place Details by appending the names of their Master Place.
Changes the PlaceType of all Place Details from 2 to 0 for a Place.
Revises the EventTable so by moving the value of SiteID to PlaceID and backfilling
with -1.

rev 2015-05-15 TAH: changed SiteId backfill from -1 to 0 and test from <>-1 to >0. 
*/

-- look at new Place Name from Place Detail
SELECT P2.PlaceID, P2.Name || ', ' || P0.Name AS Name
FROM PlaceTable P2
JOIN PlaceTable P0
ON P2.MasterID = P0.PlaceID 
; 

-- convert Place Detail to Place
UPDATE PlaceTable
SET Name = 
(SELECT P2.Name || ', ' || P0.Name AS Name
 FROM PlaceTable P2
 JOIN PlaceTable P0
 ON P2.MasterID = P0.PlaceID
 WHERE PlaceTable.PlaceID = P2.PlaceID
 )
 , PlaceType = 0
 , MasterID = '*' || MasterID  -- set flag for future unconvert
WHERE PlaceID IN
(SELECT P2.PlaceID
 FROM PlaceTable P2
 JOIN PlaceTable P0
 ON P2.MasterID = P0.PlaceID
 )
;
/*
-- now redundant, included in prev statement
UPDATE PlaceTable SET PlaceType = 0 WHERE PlaceType = 2
;
SELECT '*' || MasterID FROM PlaceTable WHERE MasterID > 0
;
-- test whether MasterID can be preserved for possible reversal of conversion
UPDATE PlaceTable
SET MasterID = '*' || MasterID
WHERE MasterID > 0
;
-- If Place is edited, MasterID is reset to 0; perhaps store in privacy braces in Note?
UPDATE PlaceTable
SET Note = '{MasterID=' || MasterID || '}'
WHERE MasterID <> 0
;
-- problem is knowing what the outcome might be if the Place has been edited, e.g., merged
-- or if the old MasterPlace has been deleted because it became unused...
*/

-- revise EventTable so that Place Detail (SiteID) becomes the PlaceID
UPDATE EventTable
SET PlaceID = SiteID
    , SiteID = 0 -- corrected = -1  2015-05-15 TAH
WHERE SiteID > 0 -- corrected <> -1 2015-05-15 TAH
;

SELECT 'Script completed without execution error' AS Status
;