/**
 * To be run in sequence, but take note of which server -- there are also scp commands in the comments. 
 * This is to be run statement by statement.
 * https://redmine.dataone.org/issues/3352
 */

/**
 * on UCSB
 */
CREATE TABLE identifier_mapping_ucsb AS
SELECT guid, docid, rev FROM identifier
WHERE guid IN ('doi:10.6085/AA/ANSXXX_015MTBD003R00_20031002.50.1', 'KWG.11.1', 'KWG.11.4', 'KWG.13.1', 'MarcoSchmidt.4.1', 'ark:/99999/fk4wm1w4h/14/cadwsap-s3400167-001-vuln.__csv', 'ark:/99999/fk4wm1w4h/14/cadwsap-s3400167-001.pdf', 'ark:/99999/fk4wm1w4h/14/cadwsap-s3400167-001.shp.xml', 'ark:/99999/fk4wm1w4h/14/mrt-dataone-map.rdf', 'ark:/99999/fk4wm1w4h/15/cadwsap-s3400167-001-main.csv', 'ark:/99999/fk4wm1w4h/15/cadwsap-s3400167-001.shp.xml', 'cbfs.121.2', 'cbfs.123.4', 'cbfs.125.16', 'cbfs.126.2', 'cbfs.127.12', 'cbfs.128.2', 'cbfs.24.7', 'cbfs.29.3', 'cbfs.30.3', 'cbfs.36.8', 'cbfs.37.13', 'cbfs.46.4', 'datastar.11.19', 'datastar.14.48', 'datastar.22.5', 'doi:10.6085/AA/testnewapi.40.1', 'doi:10.6085/AA/testnewapi.50.1', 'farshid25.23.1', 'farshid25.24.1', 'farshid25.25.1', 'farshid25.26.1', 'farshid25.28.1', 'farshid25.31.1', 'itsjustme.100.1', 'itsjustme.101.1', 'itsjustme.102.1', 'itsjustme.103.1', 'itsjustme.107.1', 'itsjustme.109.1', 'itsjustme.110.1', 'itsjustme.111.1', 'itsjustme.70.2', 'itsjustme.73.3', 'itsjustme.75.3', 'itsjustme.79.1', 'itsjustme.79.2', 'itsjustme.80.2', 'itsjustme.82.1', 'itsjustme.83.1', 'itsjustme.84.1', 'itsjustme.87.1', 'itsjustme.89.1', 'itsjustme.90.1', 'itsjustme.92.1', 'itsjustme.94.1', 'itsjustme.95.1', 'itsjustme.96.1', 'itsjustme.99.1', 'johnson.6.1', 'judith_kruger.24.2', 'judith_kruger.24.3', 'judith_kruger.25.2', 'judith_kruger.51.1', 'judith_kruger.53.1', 'judith_kruger.53.2', 'judith_kruger.55.1', 'judith_kruger.57.1', 'judith_kruger.58.1', 'judith_kruger.59.1', 'judith_kruger.60.1', 'judith_kruger.61.1', 'judith_kruger.65.1', 'judith_kruger.67.1', 'judith_kruger.69.1', 'judith_kruger.71.1', 'judith_kruger.72.1', 'judithk.1056.12', 'judithk.1157.1', 'judithk.1167.1', 'judithk.1169.1', 'judithk.1171.1', 'judithk.1172.1', 'judithk.1173.1', 'judithk.1175.1', 'judithk.1177.1', 'judithk.1242.5', 'judithk.1243.2', 'judithk.1312.4', 'judithk.1325.3', 'judithk.1333.5', 'judithk.1349.6', 'judithk.1369.2', 'judithk.1381.2', 'judithk.1384.11', 'judithk.1384.12', 'judithk.1384.13', 'judithk.1384.14', 'judithk.1384.6', 'judithk.1384.7', 'judithk.1384.8', 'judithk.1384.9', 'judithk.1407.1', 'judithk.1407.2', 'judithk.1407.3', 'judithk.1409.1', 'judithk.1410.1', 'judithk.1410.2', 'judithk.1412.1', 'judithk.1413.1', 'judithk.1414.1', 'judithk.1415.1', 'judithk.1416.1', 'judithk.1417.1', 'judithk.1417.2', 'judithk.1417.3', 'judithk.1420.1', 'judithk.1422.1', 'judithk.1424.3', 'judithk.1424.5', 'judithk.1424.7', 'judithk.1424.9', 'judithk.1429.1', 'judithk.1432.1', 'judithk.1434.1', 'judithk.1436.1', 'judithk.1437.1', 'judithk.1438.1', 'judithk.1439.1', 'judithk.1442.1', 'judithk.1444.1', 'judithk.1446.1', 'judithk.1448.1', 'judithk.1449.1', 'judithk.1450.1', 'judithk.1451.1', 'judithk.1452.1', 'judithk.1454.1', 'judithk.1456.1', 'judithk.1458.1', 'judithk.1459.1', 'judithk.1460.1', 'judithk.1461.1', 'judithk.1462.1', 'judithk.1464.1', 'judithk.1466.5', 'judithk.1466.6', 'judithk.1466.7', 'judithk.1468.3', 'judithk.1468.5', 'judithk.1468.6', 'judithk.1471.1', 'judithk.1472.1', 'judithk.1473.1', 'judithk.1474.1', 'judithk.1476.1', 'judithk.1478.1', 'judithk.1481.1', 'judithk.1483.1', 'judithk.1484.1', 'judithk.1486.1', 'judithk.1488.1', 'judithk.1494.1', 'judithk.1496.1', 'judithk.1500.1', 'judithk.1505.1', 'judithk.1507.1', 'judithk.1509.1', 'judithk.1510.1', 'judithk.1512.1', 'judithk.1516.1', 'judithk.1517.1', 'judithk.1522.1', 'judithk.1526.1', 'judithk.1529.1', 'judithk.1530.1', 'judithk.1531.1', 'judithk.1532.1', 'judithk.1534.1', 'judithk.1540.1', 'judithk.1542.1', 'judithk.1543.1', 'judithk.40.54', 'kgordon.14.85', 'kgordon.17.33', 'kgordon.19.61', 'kgordon.19.62', 'kgordon.19.63', 'kgordon.23.27', 'kgordon.31.27', 'kgordon.35.34', 'kgordon.35.35', 'kgordon.35.36', 'kgordon.35.38', 'kgordon.35.39', 'kgordon.35.40', 'kgordon.4.38', 'nceas.988.7', 'nceas.988.8', 'nceas.988.9', 'nceas.995.1', 'peggym.1055.20', 'peggym.1097.61', 'peggym.1097.62', 'peggym.1097.63', 'peggym.109757.7', 'peggym.109757.8', 'peggym.109757.9', 'peggym.1098.70', 'peggym.1098.71', 'peggym.1098.72', 'peggym.1098.73', 'peggym.1099.85', 'peggym.1099.86', 'peggym.1099.88', 'peggym.1099.89', 'peggym.1099.90', 'peggym.1102.51', 'peggym.1102.52', 'peggym.1102.53', 'peggym.1102.54', 'peggym.1102.55', 'peggym.1102.56', 'peggym.1102.57', 'peggym.1102.58', 'peggym.1108.60', 'peggym.1108.61', 'peggym.1108.63', 'peggym.1125.33', 'peggym.1161.22', 'peggym.1161.23', 'peggym.1161.24', 'peggym.1162.29', 'peggym.1162.30', 'peggym.1162.31', 'peggym.1224.17', 'peggym.1224.18', 'peggym.1224.19', 'peggym.1224.20', 'peggym.1224.21', 'peggym.1224.22', 'peggym.1348.2', 'wolkovich.38.2', 'wolkovich.39.1') 
ORDER BY guid;

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

--drop table identifier_mapping_ucsb;

/**
 * scp to ORC and UNM
 * scp /tmp/identifier_mapping_ucsb.csv leinfelder@cn-unm-1.dataone.org:/tmp/identifier_mapping_ucsb.csv
 * scp /tmp/identifier_mapping_ucsb.csv leinfelder@cn-orc-1.dataone.org:/tmp/identifier_mapping_ucsb.csv
 */

/**
 * on ORC and UNM
 */

CREATE TABLE identifier_mapping_ucsb AS
SELECT guid, docid, rev 
FROM identifier WHERE false;

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

-- check the records that wil be updated
SELECT id.guid as current_guid, ucsb.guid as new_guid
FROM identifier id, identifier_mapping_ucsb ucsb
WHERE id.docid = ucsb.docid
AND id.rev = ucsb.rev
AND id.guid like 'autogen%'
ORDER BY id.guid;

-- update the identifier mapping
BEGIN;
UPDATE identifier id
SET guid = ucsb.guid
FROM identifier_mapping_ucsb ucsb
WHERE id.docid = ucsb.docid
AND id.rev = ucsb.rev
AND id.guid like 'autogen%';
COMMIT;
--ROLLBACK;

-- check the guids ("autogen") that we don't want
select * from identifier 
where guid like 'autogen%'
order by guid;

