/**
 * TO correct missing identifier mappings in the CN network
 * see: https://redmine.dataone.org/issues/3697 
 */
-- on CN1
create table identifier_mapping (guid text, docid varchar(250), rev int);

insert into identifier_mapping (guid) 
select guid from systemMetadata s 
where s.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', 'application/vnd.ms-excel',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') 
and not exists (select * from identifier i where i.guid = s.guid)
order by guid;

COPY (select guid, docid, rev FROM identifier_mapping) TO '/tmp/identifier_mapping.csv' WITH CSV HEADER;

-- on CN2
-- scp from CN1 to CN2
-- scp /tmp/identifier_mapping.csv leinfelder@cn-unm-1.dataone.org:/tmp/identifier_mapping.csv
create table identifier_mapping (guid text, docid varchar(250), rev int);

COPY identifier_mapping FROM '/tmp/identifier_mapping.csv' WITH CSV HEADER;

insert into identifier_mapping (guid) 
select guid from systemMetadata s 
where s.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', 'application/vnd.ms-excel',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') 
and not exists (select * from identifier i where i.guid = s.guid)
order by guid;

UPDATE identifier_mapping im
SET docid = id.docid, rev = id.rev
FROM identifier id
WHERE im.guid = id.guid;

COPY (select guid, docid, rev FROM identifier_mapping) TO '/tmp/identifier_mapping.csv' WITH CSV HEADER;

-- on CN3
-- scp from CN2 to CN3
-- scp /tmp/identifier_mapping.csv leinfelder@cn-orc-1.dataone.org:/tmp/identifier_mapping.csv
create table identifier_mapping (guid text, docid varchar(250), rev int);

COPY identifier_mapping FROM '/tmp/identifier_mapping.csv' WITH CSV HEADER;

insert into identifier_mapping (guid) 
select guid from systemMetadata s 
where s.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', 'application/vnd.ms-excel',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') 
and not exists (select * from identifier i where i.guid = s.guid)
order by guid;

UPDATE identifier_mapping im
SET docid = id.docid, rev = id.rev
FROM identifier id
WHERE im.guid = id.guid;

COPY (select guid, docid, rev FROM identifier_mapping) TO '/tmp/identifier_mapping.csv' WITH CSV HEADER;

-- back to CN1
-- scp from CN3 to CN1
-- scp /tmp/identifier_mapping.csv leinfelder@cn-ucsb-1.dataone.org:/tmp/identifier_mapping.csv
TRUNCATE TABLE identifier_mapping;
COPY identifier_mapping FROM '/tmp/identifier_mapping.csv' WITH CSV HEADER;

UPDATE identifier_mapping im
SET docid = id.docid, rev = id.rev
FROM identifier id
WHERE im.guid = id.guid;

COPY (select guid, docid, rev FROM identifier_mapping) TO '/tmp/identifier_mapping.csv' WITH CSV HEADER;

-- back to CN2
-- scp from CN1 to CN2
-- scp /tmp/identifier_mapping.csv leinfelder@cn-unm-1.dataone.org:/tmp/identifier_mapping.csv
TRUNCATE TABLE identifier_mapping;
COPY identifier_mapping FROM '/tmp/identifier_mapping.csv' WITH CSV HEADER;

UPDATE identifier_mapping im
SET docid = id.docid, rev = id.rev
FROM identifier id
WHERE im.guid = id.guid;

-- check that there are none left
select im.guid, object_format 
from identifier_mapping im, systemmetadata sm 
where im.guid = sm.guid 
and im.docid is null;

COPY (select guid, docid, rev FROM identifier_mapping) TO '/tmp/identifier_mapping.csv' WITH CSV HEADER;

-- should have all mappings now on CN2

-- copy them to other servers and run the update
-- scp /tmp/identifier_mapping.csv leinfelder@cn-ucsb-1.dataone.org:/tmp/identifier_mapping.csv
-- scp /tmp/identifier_mapping.csv leinfelder@cn-orc-1.dataone.org:/tmp/identifier_mapping.csv
TRUNCATE TABLE identifier_mapping;
COPY identifier_mapping FROM '/tmp/identifier_mapping.csv' WITH CSV HEADER;

-- check the existing records
select id.guid as bad_guid, im.guid, im.docid, im.rev  
from identifier id, identifier_mapping im
where id.docid = im.docid
and id.rev = im.rev
and id.guid != im.guid;

BEGIN;

UPDATE identifier id
SET guid = im.guid
FROM identifier_mapping im
WHERE id.docid = im.docid
AND id.rev = im.rev
AND id.guid != im.guid;

-- check
select count(guid) from systemMetadata s 
where s.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', 'application/vnd.ms-excel',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') 
and not exists (select * from identifier i where i.guid = s.guid);

COMMIT;

-- DONE!