/**
 * This function removes all traces of an object identified by the given {pid}.
 * NOTE: for complete removal, the object should also be deleted from the filesystem.
 */
CREATE OR REPLACE FUNCTION delete_all(pid text, deleteSystemMetadata boolean default true) RETURNS VOID AS $$ 
DECLARE 
	v_docid text; 
	v_rev integer; 
	v_doctype text;
	deleted_count integer;
BEGIN 
	SELECT INTO v_docid docid FROM identifier WHERE guid = pid;
	SELECT INTO v_rev rev FROM identifier WHERE guid = pid;

	SELECT INTO v_doctype doctype FROM xml_documents WHERE docid = v_docid and rev = v_rev;
	IF v_doctype IS NULL THEN
		SELECT INTO v_doctype doctype FROM xml_revisions WHERE docid = v_docid and rev = v_rev;
	END IF;
	
	RAISE NOTICE 'Processing pid: %, which is docid: %.%, with doctype: %', pid, v_docid, v_rev, v_doctype;

	IF v_docid IS NOT NULL THEN
		-- current versions
		DELETE FROM xml_queryresult WHERE docid = v_docid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from xml_queryresult', deleted_count;
		
		DELETE FROM xml_path_index WHERE docid = v_docid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from xml_path_index', deleted_count;
		
		DELETE FROM xml_index WHERE docid = v_docid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from xml_index', deleted_count;

                DELETE FROM xml_accesssubtree WHERE docid = v_docid;
                GET DIAGNOSTICS deleted_count = ROW_COUNT;
                RAISE NOTICE 'Deleted % rows from xml_accesssubtree', deleted_count;
	
		DELETE FROM xml_documents WHERE docid = v_docid AND rev = v_rev;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from xml_documents', deleted_count;
		
		DELETE FROM xml_nodes WHERE docid = v_docid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from xml_nodes', deleted_count;
	
		-- revisions
		DELETE FROM xml_revisions WHERE docid = v_docid AND rev = v_rev;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from xml_revisions', deleted_count;

		DELETE FROM xml_nodes_revisions WHERE docid = v_docid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from xml_nodes_revisions', deleted_count;

		-- event logs
		DELETE FROM access_log WHERE docid = v_docid||'.'||v_rev;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from access_log', deleted_count;

		-- reminder to delete filesystem content
		IF v_doctype = 'BIN' THEN
			RAISE NOTICE 'Remember to delete DATA file from filesystem docid: %.%', v_docid, v_rev;
		ELSE
			RAISE NOTICE 'Remember to delete METADATA file from filesystem docid: %.%', v_docid, v_rev;
		END IF;
	END IF;

	-- do we want to remove the SM records too?
	IF deleteSystemMetadata THEN
		DELETE FROM smReplicationPolicy WHERE guid = pid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from smReplicationPolicy', deleted_count;
		
		DELETE FROM smReplicationStatus WHERE guid = pid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from smReplicationStatus', deleted_count;
		
		DELETE FROM systemMetadata WHERE guid = pid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from systemMetadata', deleted_count;
	
		DELETE FROM xml_access WHERE guid = pid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from xml_access', deleted_count;
	
		DELETE FROM identifier WHERE guid = pid;
		GET DIAGNOSTICS deleted_count = ROW_COUNT;
		RAISE NOTICE 'Deleted % rows from identifier', deleted_count;
	END IF;
	
END;
$$ 
LANGUAGE plpgsql;
--BEGIN; select delete_all('bowles.55.4'); ROLLBACK;

/**
 * This part of the script collects PIDS to be deleted
 * and performs the deletion on that set of pids.
 * Currently we are targeting all ORNLDAAC objects.
 * Update this section as needed.
 */
-- Save the set of PIDs we are deleting
CREATE TABLE pids_to_delete AS
SELECT *, text(null) as docid, null::integer as rev
FROM systemMetadata
WHERE origin_member_node like 'urn:node:ORNLDAAC';

-- update with docid/rev if we have them
UPDATE pids_to_delete ptd
SET docid = id.docid, rev = id.rev
FROM identifier id
WHERE ptd.guid = id.guid;

-- Do the delete
BEGIN; 
SELECT delete_all(guid) FROM pids_to_delete;
--ROLLBACK;
COMMIT;
-- Clean up
DROP TABLE pids_to_delete;