/**
 * This part of the script collects PIDS to be deleted
 * and performs the deletion on that set of pids.
 */
-- get the PIDs that have no mapping and that are not DATA format types
CREATE TABLE pids_to_delete AS
SELECT *, text(null) as docid, null::integer as rev
FROM systemMetadata
WHERE guid not in (SELECT guid FROM identifier)
AND object_format not in (
'CF-1.0',
'CF-1.1',
'CF-1.2',
'CF-1.3',
'CF-1.4',
'text/plain',
'text/csv',
'image/bmp',
'image/gif',
'image/jp2',
'image/jpeg',
'image/png',
'image/svg+xml',
'image/tiff',
'application/octet-stream',
'application/pdf');

SELECT count(*) FROM pids_to_delete;

-- update with docid/rev from other server[s]
-- copy to other server in order to update the values, then reload into table
COPY 
(SELECT guid, docid, rev
FROM pids_to_delete 
ORDER BY guid) 
TO '/tmp/shared_identifier_mapping.csv'
WITH CSV HEADER;

-- merge list from all three servers to get complete list of guids

--import the shared list
CREATE TABLE shared_identifier_mapping (guid text, docid text, rev integer);
COPY shared_identifier_mapping
FROM '/home/leinfelder/shared_identifier_mapping.csv'
WITH CSV HEADER;

-- update with docid/rev if we have them so we know which document mappings we have and don't have for the various servers
UPDATE shared_identifier_mapping sim
SET docid = id.docid, rev = id.rev
FROM identifier id
WHERE sim.guid = id.guid;

-- export again
COPY 
(SELECT guid, docid, rev
FROM shared_identifier_mapping 
ORDER BY guid) 
TO '/tmp/shared_identifier_mapping.csv'
WITH CSV HEADER;

-- merge the records from each server again, now they all will have docid, rev

-- import again
TRUNCATE TABLE shared_identifier_mapping;
COPY shared_identifier_mapping
FROM '/home/leinfelder/shared_identifier_mapping.csv'
WITH CSV HEADER;

--update the identifier table the missing mappings
BEGIN;
INSERT INTO identifier (guid, docid, rev)
SELECT guid, docid, rev
FROM shared_identifier_mapping
WHERE guid not in (select guid from identifier); 
COMMIT;

drop table shared_identifier_mapping;
drop table pids_to_delete;