const { pool, setSurvey, transaction, fetchRow } = require('../db/connection')

async function projectLastModified (projectId) {
	const client = await setSurvey(projectId);

	const text = `
		SELECT to_timestamp(max(greatest(hash[2]::numeric, hash[3]::numeric))) AS tstamp
		FROM (
			SELECT string_to_array(hash, ':') hash
			FROM files
		) AS h;
	`;

	const res = ((await client.query(text))?.rows ?? [])[0]?.tstamp;
	await client.release();
	return res;
}

async function sequenceLastModified (projectId, sequence) {
	const client = await setSurvey(projectId);

	const text = `
		SELECT to_timestamp(max(greatest(hash[2]::numeric, hash[3]::numeric))) AS tstamp
		FROM (
			SELECT sequence, string_to_array(hash, ':') AS hash
			FROM raw_shots
			UNION SELECT sequence, string_to_array(hash, ':') AS hash
			FROM final_shots
		) AS h
		GROUP BY sequence
		HAVING sequence = $1;
	`;

	const values = [ sequence ];

	const res = ((await client.query(text, values))?.rows ?? [])[0]?.tstamp;
	await client.release();
	return res;
}

/** Return or save a hash representing the state of the project.
 *
 * The hash is an MD5 digest from the concatenation of all
 * file paths + file hashes known to Dougal for a given
 * project.
 *
 * The idea is that this should change every time a new
 * file is imported or when an existing file is changed
 * or deleted.
 *
 * Going only by file timestamp does not work as we may
 * be importing files with timestamps older than the last
 * QC run.
 *
 * @a projectId The ID of the project to operate on.
 * @a hash If present, sets info.qc->'hash' to this value,
 * if absent, return a hash.
 */
async function projectHash (projectId, hash) {
	const client = await setSurvey(projectId);

	if (hash) {
		const text = `
			INSERT INTO info (key, value)
			VALUES ('qc', json_build_object('hash', to_jsonb($1::text)))
			ON CONFLICT (key)
			DO UPDATE
			SET value = jsonb_set(info.value, ARRAY['hash'], to_jsonb($1::text));
		`;

		const values = [hash];
		const res = await client.query(text, values);
		await client.release();
		return res;
	} else {
		const text = `
			SELECT md5(text) hash FROM (
				SELECT string_agg(path || E'\t' || hash, E'\n') AS text
				FROM files
			) AS t;
		`;

		const res = ((await client.query(text))?.rows ?? [])[0]?.hash;
		await client.release();
		return res;
	}
}

/** Return or save a hash representing the state of a sequence.
 *
 * Analogous to projectHash() but for a specific sequence.
 */
async function sequenceHash (projectId, sequence, hash) {
	const client = await setSurvey(projectId);

	if (hash) {
		const text = `
			UPDATE raw_lines
			SET
				meta = jsonb_set(meta, array['lastQCHash'], to_jsonb($2::text))
			WHERE sequence = $1;
		`;

		const values = [ sequence, hash ];

		const res = await client.query(text, values);
		await client.release();
		return res;
	} else {
		const text = `
			SELECT sequence, md5(string_agg(hash, E'\t')) AS hash
			FROM (
				SELECT *
				FROM raw_lines_files
				UNION SELECT *
				FROM final_lines_files
			) AS t
			GROUP BY sequence
			HAVING sequence = $1;
		`;

		const values = [ sequence ];

		const res = ((await client.query(text, values))?.rows ?? [])[0]?.hash;
		await client.release();
		return res;
	}
}

module.exports = {
	projectLastModified,
	sequenceLastModified,
	projectHash,
	sequenceHash
};
