# Run in publishing_api_development
# These queries need reviewing to filter out links to non-live content

\copy (select l.target_content_id,coalesce(d.content_id, d2.content_id) as source_content_id, l.link_type, l.position, case when l.edition_id is null then 'document' else 'edition' end as link_scope from links l left outer join link_sets ls on l.link_set_id=ls.id left outer join documents d on ls.content_id=d.content_id left outer join editions e on e.id=l.edition_id left outer join documents d2 on d2.id=e.document_id) to 'content-links.csv' with csv header;

\copy (select d.content_id,d.locale,e.title,e.description,e.document_type,e.analytics_identifier,e.state from documents d join editions e on e.document_id=d.id and e.content_store='live') to 'content.csv' with csv header;

\copy (select d.content_id, d2.content_id as owning_content_id from documents d join documents d2 on d.owning_document_id=d2.id) to owning_documents.csv with csv header