-- Places-UnConvertPlaceDetailsConvertedToPlaces.sql
/*
2015-01-21 Tom Holden ve3meo
Revises the names of all Places having a MasterID by subtracting the names of their former Master Place.
Changes the PlaceType of all Places having a MasterID from 0 to 2 for a PlaceDetail.
Revises the EventTable by moving the value of PlaceID to SiteID and backfilling PlaceID with
the MasterID of the PlaceDetail
*/

-- Create a table to control the conversion
;

CREATE TEMP TABLE xPlaceConvertTable
 (PlaceID INTEGER PRIMARY KEY
 , MasterID INTEGER
 , OldName TEXT
 , MasterName TEXT
 , NewName TEXT)
;

-- pop the table with the PlaceID's to be converted back to PlaceDetails
INSERT INTO xPlaceConvertTable
SELECT PlaceID
       , REPLACE(MasterID, '*', '') AS MasterID
       , Name AS OldName
       , '' AS MasterName
       , '' AS NewName
FROM PlaceTable
WHERE MasterID LIKE '*%' -- flag prepended to MasterID by Places-ConvertPlaceDetailsToPlaces.sql
;
/* 
Above might be revised to work off the "{MasterID=nnn}" string prepended to Note
*/


-- pop the table with the names of the Master Places
UPDATE xPlaceConvertTable
SET MasterName =
  (SELECT Name FROM PlaceTable P0 WHERE xPlaceConvertTable.MasterID = P0.PlaceID)
;

-- pop the table with the new PlaceDetail name
UPDATE xPlaceConvertTable
SET NewName = REPLACE(OldName, ', ' || MasterName, '')
;
 
-- look at new PlaceDetail Name from Place 
SELECT * FROM xPlaceConvertTable
; 

-- convert Place to Place Detail
UPDATE PlaceTable
SET Name = 
(SELECT PC.NewName
 FROM xPlaceConvertTable PC 
 WHERE PlaceTable.PlaceID = PC.PlaceID
 )
 , PlaceType = 2
 , MasterID = 
(SELECT PC.MasterID
 FROM xPlaceConvertTable PC 
 WHERE PlaceTable.PlaceID = PC.PlaceID
 )
WHERE PlaceID IN
(SELECT PlaceID FROM xPlaceConvertTable)
;

-- revise EventTable so that Place Detail (SiteID) becomes the PlaceID
/*
SELECT E.PlaceID AS EventPlaceID, PC.MasterID AS NewPlaceID, PC.PlaceID AS NewSiteID
FROM EventTable E
JOIN xPlaceConvertTable PC USING(PlaceID)
;
*/

UPDATE EventTable
SET PlaceID = (SELECT MasterID FROM xPlaceConvertTable PC WHERE EventTable.PlaceID = PC.PlaceID)
   , SiteID = PlaceID
WHERE PlaceID IN (SELECT PlaceID FROM xPlaceConvertTable)
;

SELECT 'Script completed without execution error' AS Status
;