/**
 * Restore archived documents
 */

/* 
 * Gather most recent docids that:
 * have access_log event='delete' by the CN
 * are obsoleted by a newer version
 * Then we know the current version should be restored
 */

DROP TABLE IF EXISTS current_documents;
CREATE TABLE current_documents (
	guid text, 
	obsoleted_by text
);

/* Find the most recent version by traversing system metadata 
 * see: http://www.postgresql.org/docs/8.4/static/queries-with.html
 */
INSERT INTO current_documents (guid, obsoleted_by)
WITH RECURSIVE q AS
(
	SELECT  id.guid, sm.obsoleted_by
	FROM access_log al, identifier id, systemmetadata sm
	WHERE al.event = 'delete'
	AND al.date_logged >= '20140101'
	AND al.principal LIKE '%urn:node:CN%'
	AND al.docid = id.docid || '.' || id.rev 
	AND id.guid = sm.guid
	AND sm.obsoleted_by IS NOT null
UNION ALL
	SELECT newer.guid, newer.obsoleted_by
	FROM systemMetadata newer
	JOIN q
	ON q.obsoleted_by = newer.guid
)
SELECT guid, obsoleted_by
FROM q
WHERE obsoleted_by is null
ORDER BY guid;

/**
 * Gather the details of the documents to restore
 */
DROP TABLE IF EXISTS restore_documents;
CREATE TABLE restore_documents (
	docid VARCHAR(250),
	rev INT8,
	rootnodeid INT8,
	guid text
);
INSERT INTO restore_documents (
	docid, 
	rev, 
	rootnodeid, 
	guid
) 
SELECT 
	x.docid,
	x.rev,
	x.rootnodeid,
	id.guid
FROM current_documents cd,
	xml_revisions x,
	identifier id
WHERE x.docid = id.docid
AND x.rev = id.rev
AND id.guid = cd.guid;

/* look at them */
/*
SELECT * 
FROM restore_documents;
*/

/* STOP HERE WHEN TESTING */

/* Move xml_nodes_revisions back into xml_nodes for the affected docids 
 */
INSERT INTO xml_nodes
	(nodeid, nodeindex, nodetype, nodename, nodeprefix,
	nodedata, parentnodeid, rootnodeid, docid, date_created,
	date_updated, nodedatanumerical, nodedatadate)
SELECT 
	nodeid, nodeindex, nodetype, nodename, nodeprefix,  
	nodedata, parentnodeid, x.rootnodeid, x.docid, date_created,
	date_updated, nodedatanumerical, nodedatadate
FROM xml_nodes_revisions x, restore_documents rd
WHERE x.rootnodeid = rd.rootnodeid;

/* Move xml_revisions back into xml_documents for the affected docids 
 */
INSERT INTO xml_documents
	(docid, rootnodeid, docname, doctype,
	user_owner, user_updated, date_created, date_updated,
	server_location, rev, public_access, catalog_id) 
SELECT 
	x.docid, x.rootnodeid, docname, doctype,
	user_owner, user_updated , date_created, date_updated,
	server_location, x.rev, public_access, catalog_id
FROM xml_revisions x, restore_documents rd
WHERE x.rootnodeid = rd.rootnodeid;

/* Remove the records from revisions 
 * Order matters here because of foreign key constraints
 */
DELETE FROM xml_revisions x
USING restore_documents rd
WHERE x.rootnodeid = rd.rootnodeid;

DELETE FROM xml_nodes_revisions x
USING restore_documents rd
WHERE x.rootnodeid = rd.rootnodeid;

/* Ensure ALL previous revisions of docids 
 * that have been obsoleted_by something else
 * do not also have archived=true flag set
 * (Avoids encountering this issue again)
 */

/* Check the numbers in xml_revisions
 */
/*
SELECT count(id.guid)
FROM xml_revisions x,
	identifier id,
	systemMetadata sm
WHERE x.docid = id.docid
AND x.rev = id.rev
AND id.guid = sm.guid
AND sm.obsoleted_by IS NOT null
AND sm.archived = 'true';
*/

/*Do the update on xml_revisions
 */
UPDATE systemMetadata sm
SET archived = false
FROM xml_revisions x,
	identifier id
WHERE x.docid = id.docid
AND x.rev = id.rev
AND id.guid = sm.guid
AND sm.obsoleted_by IS NOT null
AND sm.archived = 'true';

/** 
 * Check numbers in xml_documents
 */
/*
SELECT count(id.guid)
FROM xml_documents x,
	identifier id,
	systemMetadata sm
WHERE x.docid = id.docid
AND x.rev = id.rev
AND id.guid = sm.guid
AND sm.obsoleted_by IS NOT null
AND sm.archived = 'true';
*/

/*Do the update on xml_documents
 */
UPDATE systemMetadata sm
SET archived = false
FROM xml_documents x,
	identifier id
WHERE x.docid = id.docid
AND x.rev = id.rev
AND id.guid = sm.guid
AND sm.obsoleted_by IS NOT null
AND sm.archived = 'true';

/* Clean up
 */
DROP TABLE IF EXISTS current_documents;
DROP TABLE IF EXISTS restore_documents;

/* Register schemas
*/
DELETE FROM xml_catalog 
      WHERE entry_type LIKE 'Schema'
        AND system_id LIKE '%/dataone/%';
DELETE FROM xml_catalog 
      WHERE entry_type LIKE 'Schema'
        AND system_id LIKE '%/dc/%';
DELETE FROM xml_catalog 
      WHERE entry_type LIKE 'Schema'
        AND system_id LIKE '%/dwc/%';
DELETE FROM xml_catalog 
      WHERE entry_type LIKE 'Schema'
        AND system_id LIKE '%/dryad/%';                
INSERT INTO xml_catalog (entry_type, public_id, system_id)
  VALUES ('Schema', 'http://ns.dataone.org/service/types/v1', '/schema/dataone/dataoneTypes.xsd');
INSERT INTO xml_catalog (entry_type, public_id, system_id)
  VALUES ('Schema', 'http://ns.dataone.org/service/types/v1.1', '/schema/dataone/dataoneTypes_v1.1.xsd');
INSERT INTO xml_catalog (entry_type, public_id, system_id)
  VALUES ('Schema', 'http://purl.org/dryad/schema/terms/v3.1', '/schema/dryad/dryad.xsd');
INSERT INTO xml_catalog (entry_type, public_id, system_id)
  VALUES ('Schema', 'http://purl.org/dryad/schema/dryad-bibo/v3.1', '/schema/dryad/dryad-bibo.xsd');
INSERT INTO xml_catalog (entry_type, public_id, system_id)
  VALUES ('Schema', 'http://purl.org/dc/terms/', '/schema/dc/dcterms.xsd');
INSERT INTO xml_catalog (entry_type, public_id, system_id)
  VALUES ('Schema', 'http://purl.org/dc/elements/1.1/', '/schema/dc/dc.xsd');
INSERT INTO xml_catalog (entry_type, public_id, system_id)
  VALUES ('Schema', 'http://purl.org/dc/dcmitype/', '/schema/dc/dcmitype.xsd');
INSERT INTO xml_catalog (entry_type, public_id, system_id)
  VALUES ('Schema', 'http://rs.tdwg.org/dwc/terms/', '/schema/dwc/tdwg_dwcterms.xsd');

/*
 * update the database version
 */
UPDATE db_version SET status=0;

INSERT INTO db_version (version, status, date_created) 
  VALUES ('2.4.0', 1, CURRENT_DATE);