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

<!--
 * dbscripts/xml/upgrade/3.2.0_versioning.xml
 *
 * Copyright (c) 2013-2021 Simon Fraser University
 * Copyright (c) 2003-2021 John Willinsky
 * Distributed under the GNU GPL v3. For full terms see the file docs/COPYING.
 *
 * Copy submission data to temporary tables so it can be updated
 * after the publication table modifications are made.
 *
 -->

<data>
	<sql>
		<!-- change date_published from datetime to date before migrating -->
		<query driver="mysql,mysqli">ALTER TABLE temp_published_submissions MODIFY COLUMN date_published DATE NULL</query>
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">ALTER TABLE temp_published_submissions ALTER COLUMN date_published TYPE DATE</query>
		<!-- migrate data to new publications table -->
		<query>
			INSERT INTO publications (access_status, date_published, last_modified, locale, section_id, seq, submission_id, status, version)
				SELECT COALESCE(ps.access_status, 0), COALESCE(ps.date_published, NULL), s.last_modified, s.locale, s.section_id, COALESCE(ps.seq, 0), s.submission_id, s.status, 1
				FROM temp_submissions as s
				LEFT JOIN temp_published_submissions as ps
					ON (s.submission_id = ps.submission_id)
				GROUP BY s.submission_id, ps.access_status, ps.date_published, s.last_modified, s.section_id, ps.seq, s.locale, s.status;
		</query>
		<query>
			UPDATE publications as p
			SET primary_contact_id = (
				SELECT a.author_id
				FROM temp_authors as a
				WHERE a.submission_id = p.submission_id
					AND a.primary_contact = 1
				LIMIT 1
			)
		</query>

		<!-- set the current_publication_id column in submissions -->
		<query>
			UPDATE submissions as s
			SET current_publication_id = (
				SELECT p.publication_id
				FROM publications as p
				WHERE s.submission_id = p.submission_id
			)
		</query>

		<!-- migrate submission_settings to publication_settings -->
		<query>
			INSERT INTO publication_settings(publication_id, locale, setting_name, setting_value)
				SELECT s.current_publication_id, ss.locale, ss.setting_name, ss.setting_value
				FROM submissions as s
				LEFT JOIN submission_settings as ss
					ON (ss.submission_id = s.submission_id)
				WHERE (
					setting_name IN ('abstract', 'cleanTitle', 'copyrightHolder', 'copyrightYear', 'coverage', 'coverImage', 'licenseUrl', 'locale', 'prefix', 'rights', 'source', 'subtitle', 'title', 'type')
					OR setting_name LIKE '%pub-id%'
				)
		</query>
		<query>
			DELETE FROM submission_settings
			WHERE (
				setting_name IN ('abstract', 'cleanTitle', 'copyrightHolder', 'copyrightYear', 'coverage', 'coverImage', 'coverImageAltText', 'licenseUrl', 'locale', 'prefix', 'rights', 'source', 'subtitle', 'title', 'type')
				OR setting_name LIKE '%pub-id%'
			)
		</query>

		<!-- migrate some data from submissions to publication_settings -->
		<query>
			INSERT INTO publication_settings(publication_id, setting_name, setting_value)
				SELECT s.current_publication_id, 'language', ts.language
				FROM submissions as s
				LEFT JOIN temp_submissions as ts
					ON s.submission_id = ts.submission_id
				WHERE ts.language != ''
		</query>
		<query>
			INSERT INTO publication_settings(publication_id, setting_name, setting_value)
				SELECT s.current_publication_id, 'citationsRaw', ts.citations
				FROM submissions as s
				LEFT JOIN temp_submissions as ts
					ON s.submission_id = ts.submission_id
				WHERE ts.citations IS NOT NULL
		</query>
		<query>
			INSERT INTO publication_settings(publication_id, setting_name, setting_value)
				SELECT s.current_publication_id, 'pages', ts.pages
				FROM submissions as s
				LEFT JOIN temp_submissions as ts
					ON s.submission_id = ts.submission_id
				WHERE ts.pages IS NOT NULL
		</query>
		<query>
			INSERT INTO publication_settings(publication_id, setting_name, setting_value)
				SELECT s.current_publication_id, 'hide_author', ts.hide_author
				FROM submissions as s
				LEFT JOIN temp_submissions as ts
					ON s.submission_id = ts.submission_id
				WHERE ts.hide_author IS NOT NULL
		</query>

		<!-- migrate issue_id from published_submissions to publication settings -->
		<query>
			INSERT INTO publication_settings(publication_id, setting_name, setting_value)
				SELECT s.current_publication_id, 'issueId', ps.issue_id
				FROM temp_published_submissions as ps
				LEFT JOIN submissions as s
					ON ps.submission_id = s.submission_id
				WHERE s.current_publication_id IS NOT NULL
		</query>

		<!-- move some author data to settings table -->
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">ALTER TABLE author_settings ALTER COLUMN setting_type DROP NOT NULL</query>
		<query>
			INSERT INTO author_settings(author_id, setting_name, setting_value)
			SELECT ta.author_id, 'country', ta.country
			FROM temp_authors as ta
		</query>
		<query>
			INSERT INTO author_settings(author_id, setting_name, setting_value)
			SELECT ta.author_id, 'url', ta.url
			FROM temp_authors as ta
			WHERE ta.url IS NOT NULL
		</query>
		<!-- connect authors to publications -->
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
			UPDATE authors
			SET publication_id = s.current_publication_id
			FROM submissions s, temp_authors ta
			WHERE authors.author_id = ta.author_id AND s.submission_id = ta.submission_id
		</query>
		<query driver="mysql,mysqli">
			UPDATE authors as a
			SET a.publication_id = (
				SELECT s.current_publication_id
				FROM submissions as s
				LEFT JOIN temp_authors as ta
					ON s.submission_id = ta.submission_id
				WHERE a.author_id = ta.author_id
			)
		</query>

		<!-- move submission_galleys to publication_galleys -->
		<query>
			INSERT INTO publication_galleys(galley_id, locale, publication_id, label, file_id, seq, remote_url, is_approved)
				SELECT tsg.galley_id, tsg.locale, s.current_publication_id, tsg.label, tsg.file_id, tsg.seq, tsg.remote_url, 1
				FROM submissions as s, temp_submission_galleys as tsg
				WHERE s.submission_id = tsg.submission_id
		</query>
		<query>
			INSERT INTO publication_galley_settings(galley_id, locale, setting_name, setting_value)
				SELECT tsgs.galley_id, tsgs.locale, tsgs.setting_name, tsgs.setting_value
				FROM submission_galley_settings as tsgs
				WHERE tsgs.galley_id IS NOT NULL
		</query>

		<!-- connect controlled vocab metadata to publications, 1048585 = ASSOC_TYPE_SUBMISSION, 1048588 = ASSOC_TYPE_PUBLICATION -->
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
			UPDATE controlled_vocabs
			SET assoc_type = 1048588, assoc_id = s.current_publication_id
			FROM submissions s
			WHERE assoc_type = 1048585 AND assoc_id = s.submission_id
		</query>
		<query driver="mysql,mysqli">
			UPDATE controlled_vocabs AS cv,
				submissions AS s
			SET cv.assoc_type = 1048588, cv.assoc_id = s.current_publication_id
			WHERE cv.assoc_type = 1048585 AND s.submission_id = cv.assoc_id
		</query>

		<!-- connect citations to publications -->
		<!-- To avoid collisions per pkp/pkp-lib#5795, first flip to negative, then clean up. -->
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
			UPDATE citations
			SET publication_id = -s.current_publication_id
			FROM submissions s
			JOIN temp_citations as tc ON (s.submission_id = tc.submission_id)
			WHERE citations.citation_id = tc.citation_id
		</query>
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9"><![CDATA[
			UPDATE citations SET publication_id = -publication_id WHERE publication_id < 0
		]]></query>
		<query driver="mysql,mysqli">
			UPDATE citations as c
			SET c.publication_id = (
				SELECT s.current_publication_id
				FROM submissions as s
				JOIN temp_citations as tc ON s.submission_id = tc.submission_id
				WHERE c.citation_id = tc.citation_id
			) ORDER BY c.publication_id ASC
		</query>

		<!-- update the DOI setting names -->
		<query>
			UPDATE plugin_settings
			SET setting_name='doiPublicationSuffixPattern'
			WHERE setting_name='doiSubmissionSuffixPattern'
				AND plugin_name='doipubidplugin'
		</query>
		<query>
			UPDATE plugin_settings
			SET setting_name='enablePublicationDoi'
			WHERE setting_name='enableSubmissionDoi'
				AND plugin_name='doipubidplugin'
		</query>

		<!-- update the URN setting names -->
		<query>
			UPDATE plugin_settings
			SET setting_name='urnPublicationSuffixPattern'
			WHERE setting_name='urnSubmissionSuffixPattern'
				AND plugin_name='urnpubidplugin'
		</query>
		<query>
			UPDATE plugin_settings
			SET setting_name='enablePublicationURN'
			WHERE setting_name='enableSubmissionURN'
				AND plugin_name='urnpubidplugin'
		</query>

		<!--
		pkp/pkp-lib#7266 Assigned articles missing from future issues after upgrade to OJS 3.3.0-8
		Constants:
		  - STATUS_SCHEDULED = 5
		  - WORKFLOW_STAGE_ID_EDITING = 4
		  - WORKFLOW_STAGE_ID_PRODUCTION = 5
		-->
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
			UPDATE publications as p
			SET status = 5
			WHERE ctid IN (
				SELECT p.ctid
				FROM publications as p
				LEFT JOIN publication_settings as pss ON p.publication_id = pss.publication_id
				LEFT JOIN submissions as s ON p.submission_id = s.submission_id
				WHERE pss.setting_name = 'issueId'
				AND pss.setting_value IS NOT NULL
				AND p.status = 1
				AND s.stage_id IN (4, 5)
			)
		</query>
		<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
			UPDATE "submissions" as "s"
			SET "status" = 5
			WHERE "ctid" IN (
				SELECT "s"."ctid"
				FROM "submissions" as "s"
				LEFT JOIN "publications" as "p" ON "s"."submission_id" = "p"."submission_id"
				LEFT JOIN "publication_settings" as "pss" ON "p"."publication_id" = "pss"."publication_id"
				WHERE "pss"."setting_name" = 'issueId'
				AND "pss"."setting_value" IS NOT NULL
				AND "s"."status" = 1
				AND "s"."stage_id" IN (4,5)
			)
		</query>
		<query driver="mysql,mysqli">
			UPDATE publications as p
			LEFT JOIN publication_settings as pss ON p.publication_id = pss.publication_id
			LEFT JOIN submissions as s ON p.submission_id = s.submission_id
			SET p.status = 5, s.status = 5
			WHERE pss.setting_name = 'issueId'
			AND pss.setting_value IS NOT NULL
			AND p.status = 1
			AND s.stage_id in (4, 5)
		</query>

		<!-- remove deprecated and temporary tables. should be last commands -->
		<drop table="submission_galleys" />
		<drop table="submission_galley_settings" />
		<drop table="published_submissions" />
		<drop table="temp_authors" />
		<drop table="temp_submissions" />
		<drop table="temp_submission_galleys" />
		<drop table="temp_published_submissions" />
		<drop table="temp_citations" />
	</sql>
</data>