<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE data SYSTEM "../../../lib/pkp/dtd/xmlData.dtd">

<!--
  * 3.0.0_reviewer_files.xml
  *
  * Copyright (c) 2014-2021 Simon Fraser University
  * Copyright (c) 2003-2021 John Willinsky
  * Distributed under the GNU GPL v3. For full terms see the file docs/COPYING.
  *
  * 3.0.0.0 reviewer files update fix.
  -->
<data>
	<sql>
		<!-- #2173 the files should have file_stage = 5 (SUBMISSION_FILE_REVIEW_ATTACHMENT), assoc_type should be 517 (ASSOC_TYPE_REVIEW_ASSIGNMENT), assoc_id should be the appropriate review_id. -->
		<query driver="mysql,mysqli">UPDATE submission_files sf, review_assignments ra SET sf.file_stage = 5, sf.assoc_type = 517, sf.assoc_id = ra.review_id WHERE ra.reviewer_file_id IS NOT NULL AND sf.file_id = ra.reviewer_file_id</query>
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">UPDATE submission_files SET file_stage = 5, assoc_type = 517, assoc_id = ra.review_id FROM review_assignments ra WHERE ra.reviewer_file_id IS NOT NULL AND submission_files.file_id = ra.reviewer_file_id</query>
		<!-- #2173 insert a raw in review_round_files, with stage_id = 3 (WORKFLOW_STAGE_ID_EXTERNAL_REVIEW) -->
		<query>INSERT INTO review_round_files (submission_id, review_round_id, stage_id, file_id, revision) SELECT ra.submission_id, ra.review_round_id, 3, ra.reviewer_file_id, sf.revision FROM review_assignments ra, submission_files sf WHERE ra.reviewer_file_id IS NOT NULL AND sf.file_id = ra.reviewer_file_id</query>
		<!-- #2173 set reviewer_file_id = NULL in review_assignments -->
		<query>UPDATE review_assignments SET reviewer_file_id = NULL WHERE reviewer_file_id IS NOT NULL</query>
	</sql>
</data>