select 
text('testing') as ezid_account,
doc.doctype,
id.guid, 
'https://cn-stage.dataone.org/cn/v1/resolve/' || id.guid as url, 
text(null) as title, 
text(null) as creator
into table doi_registration 
from identifier id, xml_documents doc
where guid like 'doi%'
and id.docid = doc.docid;

-- update title using nodes
update doi_registration doi
set title = child.nodedata
from identifier id, xml_documents docs, xml_nodes nodes, xml_nodes parent, xml_nodes child
where doi.guid = id.guid
and id.docid = docs.docid
and id.rev = docs.rev
and docs.rootnodeid = nodes.rootnodeid
and nodes.nodeid = child.parentnodeid
and nodes.parentnodeid = parent.nodeid
and nodes.nodename = 'title'
and parent.nodename = 'dataset';

-- update creator using nodes
-- creator/individualName/surName
-- creator/individualName/givenName
-- TODO: use only surname since it is required?
update doi_registration doi
set creator = siblingchild.nodedata ||' '|| child.nodedata
from identifier id, 
xml_documents docs, 
xml_nodes nodes, 
xml_nodes parent, 
xml_nodes child, 
xml_nodes grandparent, 
xml_nodes sibling, 
xml_nodes siblingchild
where doi.guid = id.guid
and id.docid = docs.docid
and id.rev = docs.rev
and docs.rootnodeid = nodes.rootnodeid
and nodes.nodeid = child.parentnodeid
and nodes.parentnodeid = parent.nodeid
and parent.parentnodeid = grandparent.nodeid
and nodes.nodename = 'surName'
and parent.nodename = 'individualName'
and grandparent.nodename = 'creator'
and parent.nodeid = sibling.parentnodeid
and sibling.nodeid = siblingchild.parentnodeid
and sibling.nodename = 'givenName';


-- update title using revisions
update doi_registration doi
set title = child.nodedata
from identifier id, xml_revisions docs, xml_nodes_revisions nodes, xml_nodes_revisions parent, xml_nodes_revisions child
where doi.guid = id.guid
and id.docid = docs.docid
and id.rev = docs.rev
and docs.rootnodeid = nodes.rootnodeid
and nodes.nodeid = child.parentnodeid
and nodes.parentnodeid = parent.nodeid
and nodes.nodename = 'title'
and parent.nodename = 'dataset';


-- update creator using revisions
update doi_registration doi
set creator = siblingchild.nodedata ||' '|| child.nodedata
from identifier id, 
xml_revisions docs, 
xml_nodes_revisions nodes, 
xml_nodes_revisions parent, 
xml_nodes_revisions child, 
xml_nodes_revisions grandparent, 
xml_nodes_revisions sibling, 
xml_nodes_revisions siblingchild
where doi.guid = id.guid
and id.docid = docs.docid
and id.rev = docs.rev
and docs.rootnodeid = nodes.rootnodeid
and nodes.nodeid = child.parentnodeid
and nodes.parentnodeid = parent.nodeid
and parent.parentnodeid = grandparent.nodeid
and nodes.nodename = 'surName'
and parent.nodename = 'individualName'
and grandparent.nodename = 'creator'
and parent.nodeid = sibling.parentnodeid
and sibling.nodeid = siblingchild.parentnodeid
and sibling.nodename = 'givenName';

-- clean up
update doi_registration
set title = replace(title, E'\n', ' ');

COPY (select ezid_account, guid as doi, url, title, creator from doi_registration order by doi) 
TO '/tmp/doi_registration.csv'
WITH CSV HEADER;

--drop table doi_registration;