// FIXME This code is in painful need of refactoring

const { setSurvey, transaction, pool } = require('../connection');
const { listen } = require('../notify');
const { ALERT, ERROR, WARNING, NOTICE, INFO, DEBUG } = require('DOUGAL_ROOT/debug')(__filename);

let last_tstamp = 0;

let project_configs, listener;

async function getAllProjectConfigs () {

	async function getFromDatabase () {
		DEBUG("Getting project configurations");
		const client = await pool.connect();

		try {
			const text = `
				SELECT schema, meta AS data
				FROM projects
				WHERE (meta->>'archived')::boolean IS NOT true;
			`;
			const res = await client.query(text);
			project_configs = res.rows;
			DEBUG("Have configurations for projects", project_configs.map(i => i.data.id));
		} catch (err) {
			ERROR(err);
		} finally {
			client.release();
		}
		return project_configs;
	}

	if (project_configs) {
		return project_configs;
	} else {
		listener = await listen(["project"], getFromDatabase);
		DEBUG("Added project configuration change listener");
		return await getFromDatabase();
	}
}

async function getNearestPreplot (candidates) {

	const pointsText = candidates.map(c => {
		return `
			SELECT '${c.schema}' AS schema, *
			FROM ${c.schema}.preplot_points
			WHERE line = ${c.line} AND point = ${c.point}
		`;
	}).join("\nUNION ");

	let text, values;

	if ("latitude" in candidates[0] && "longitude" in candidates[0]) {
		text = `
			WITH points AS (
				${pointsText}
			)
			SELECT
				*,
				ST_Distance(ST_Transform(ST_SetSRID(ST_MakePoint($1, $2), 4326), ST_SRID(geometry)), geometry) AS distance
			FROM points
			ORDER BY distance ASC, schema DESC
			LIMIT 1;
		`;
		values = [ candidates[0].longitude, candidates[0].latitude ];
	} else if ("easting" in candidates[0] && "northing" in candidates[0]) {
		text = `
			WITH points AS (
				${pointsText}
			)
			SELECT
				*,
				ST_Distance(ST_SetSRID(ST_MakePoint($1, $2), ST_SRID(geometry)), geometry) AS distance
			FROM points
			ORDER BY distance ASC, schema DESC
			LIMIT 1;
		`;
		values = [ candidates[0].easting, candidates[0].northing ];
	} else {
		// Missing a position, shouldn't happen at this point
		return;
	}

	const client = await pool.connect();
	const res = await client.query(text, values);
	client.release();
	return res.rows[0] && res.rows[0].schema;
}

async function getNearestOfflinePreplot (candidates) {

	const queries = candidates.map( c=> {
		let text, values;
		if ("latitude" in candidates[0] && "longitude" in candidates[0]) {
			text = `
				SELECT
					'${c.schema}' AS schema,
					ST_Distance(ST_Transform(ST_SetSRID(ST_MakePoint($1, $2), 4326), ST_SRID(geometry)), geometry) AS distance
				FROM ${c.schema}.preplot_points
				ORDER BY distance ASC
				LIMIT 1;
			`;
			values = [ candidates[0].longitude, candidates[0].latitude ];
		} else if ("easting" in candidates[0] && "northing" in candidates[0]) {
			text = `
				SELECT
					'${c.schema}' AS schema,
					ST_Distance(ST_SetSRID(ST_MakePoint($1, $2), ST_SRID(geometry)), geometry) AS distance
				FROM ${c.schema}.preplot_points
				ORDER BY distance ASC
				LIMIT 1;
			`;
			values = [ candidates[0].easting, candidates[0].northing ];
		} else {
			// Missing a position, shouldn't happen at this point
			return {};
		}
		return {text, values};
	}).filter(i => i.text && i.values);

	const client = await pool.connect();
	const results = [];
	for (const qry of queries) {
		const res = await client.query(qry.text, qry.values);
		if (res.rows[0] && res.rows[0].schema) {
			results.push(res.rows[0]);
		}
	}
	client.release();
	const schema = results.sort( (a, b) => a.distance - b.distance).shift()?.schema;
	return candidates.find(c => c.schema == schema);
}

async function saveOnline (dataset, opts = {}) {

	const client = await pool.connect();

	try {
		await transaction.begin(client);
		for (const item of dataset) {

			// Set schema
			await client.query(`SET search_path TO ${item.schema},public;`);

			// Add *online* pseudo-file
			await client.query(`INSERT INTO files (path, hash) VALUES ('', '*online*') ON CONFLICT DO NOTHING;`);

			// Add sequence if need be
			await client.query(`
				INSERT INTO raw_lines (sequence, line, incr)
					VALUES ($1, $2, false)
					ON CONFLICT DO NOTHING;
			`, [item.sequence, item.line]);

			// Add *online* to raw_lines_files
			await client.query(`INSERT INTO raw_lines_files (sequence, hash) VALUES ($1, '*online*') ON CONFLICT DO NOTHING;`, [item.sequence]);

			// Finally, add the actual shotpoint
			// FIXME Use grid coordinates whenever possible
			if (item.easting && item.northing) {
				await client.query(`
					INSERT INTO raw_shots
						(sequence, line, point, objref, tstamp, geometry, hash)
						VALUES ($1, $2, $3, $4, $5, ST_SetSRID(ST_MakePoint($6, $7), (select (project_configuration()->>'epsg')::integer as epsg)), '*online*')
						ON CONFLICT DO NOTHING;
				`, [item.sequence, item.line, item.point, 0, item.tstamp, item.easting, item.northing]);
			} else if (item.latitude && item.longitude) {
				await client.query(`
					INSERT INTO raw_shots
						(sequence, line, point, objref, tstamp, geometry, hash)
						VALUES ($1, $2, $3, $4, $5, ST_Transform(ST_SetSRID(ST_MakePoint($6, $7), 4326), (select (project_configuration()->>'epsg')::integer as epsg)), '*online*')
						ON CONFLICT DO NOTHING;
				`, [item.sequence, item.line, item.point, 0, item.tstamp, item.longitude, item.latitude]);
			} else {
				throw new Error("Real time data has neither geographical nor grid positions");
			}

		}
		await transaction.commit(client);
	} catch (error) {
		ERROR("ONLINE DATA INSERT ERROR");
		ERROR(error);
		await transaction.rollback(client);
	} finally {
		client.release();
	}
}

async function saveOffline (navData, opts = {}) {
	const client = await pool.connect();

	const epsg = navData.epsg || opts.epsg;
	const schema = navData.payload && navData.payload._schema;
	const hasEastNorth = "easting" in navData && "northing" in navData;
	const hasLatLon = "latitude" in navData && "longitude" in navData;

	if (epsg && hasEastNorth) {
		const text = `
			INSERT INTO real_time_inputs (tstamp, geometry, meta)
			VALUES ($1, ST_Transform(ST_SetSRID(ST_MakePoint($2, $3), $5), 4326), $4);
		`;

		const values = [navData.tstamp, navData.easting, navData.northing, navData.payload, epsg];

		await client.query(text, values)
	} else if (schema && hasEastNorth) {
		const text = `
			INSERT INTO real_time_inputs (tstamp, geometry, meta)
			VALUES ($1, ST_Transform(ST_SetSRID(ST_MakePoint($2, $3), (select (project_configuration()->>'epsg')::integer as epsg), 4326), $4);
		`;

		const values = [navData.tstamp, navData.longitude, navData.latitude, navData.payload];

		await client.query(text, values)
	} else if (hasLatLon) {
		const text = `
			INSERT INTO real_time_inputs (tstamp, geometry, meta)
			VALUES ($1, ST_SetSRID(ST_MakePoint($2, $3), 4326), $4);
		`;

		const values = [navData.tstamp, navData.longitude, navData.latitude, navData.payload];

		await client.query(text, values)
	} else {
		const text = `
			INSERT INTO real_time_inputs (tstamp, meta)
			VALUES ($1, $2);
		`;

		const values = [navData.tstamp, navData.payload];

		await client.query(text, values)
	}

	client.release();
}

async function getCandidates (navData) {

	const configs = await getAllProjectConfigs();

	// We just get the bits of interest: pattern and schema
	const candidates = configs.map(c => {
		if (!c?.data?.online?.line || c?.archived === true) {
			return null;
		}

		const p = c.data.online.line.pattern; // For short

		const rx = new RegExp(p.regex, p.flags);
		const matches = navData.lineName.match(rx);

		if (!matches || ((matches.length+1) < p.captures.length)) {
			return null;
		}

		matches.shift(); // Get rid of the full matched text
		const obj = Object.assign({}, navData, {schema: c.schema});
		p.captures.forEach( (k, i) => {
			obj[k] = matches[i];
		});
		return obj;
	}).filter(c => !!c);
	// DEBUG("Candidates: %j", candidates.map(c => c.schema));

	return candidates;
}

async function save (navData, opts = {}) {

	const hasLatLon = ("latitude" in navData && "longitude" in navData);
	const hasEastNorth = ("easting" in navData && "northing" in navData);
	const hasLinePoint = ("lineName" in navData && "point" in navData);
	if (!(hasLinePoint || hasLatLon || hasEastNorth)) {
		// This is of no interest to us
		NOTICE("Ignoring data without useful values", navData);
		return;
	}

	// DEBUG("navData", navData);

	if (navData.online === true) {

		// So we have a lineName, see which projects match the line pattern.
		// For this we need to get all the project configs
		const candidates = await getCandidates(navData);

		if (candidates.length == 0) {
			// This is probably a test line, so we treat it as offline
			WARNING("No match");
		} else {
			if (candidates.length == 1) {
				// Only one candidate, associate with it
// 				console.log("Save into schema", candidates[0].match.schema);
				await saveOnline(candidates);
				navData.payload._schema = candidates[0].schema;
			} else {
				// More than one candidate, go for the closest. If more than one active
				// project with the same preplots, highest numbered schema.
// 				console.log("Choose nearest");
				const destinationSchema = await getNearestPreplot(candidates);
				if (destinationSchema) {
					await saveOnline(candidates.filter(c => c.schema == destinationSchema), opts);
					navData.payload._schema = destinationSchema;
				} else {
					WARNING("Nowhere to save to");
				}
			}

			navData.payload._sequence = candidates[0].sequence;
			navData.payload._line = candidates[0].line;
			navData.payload._point = candidates[0].point;
			navData.payload._online = true;
		}
	} else {
		// We are offline. We only assign _schema once every save_interval seconds at most
		// unless there is gun data present.
		if (opts.offline_survey_heuristics == "nearest_preplot") {
			const now = Date.now();
			const do_save = !opts.offline_survey_detect_interval ||
				(now - last_tstamp) >= opts.offline_survey_detect_interval;

			if (do_save || "guns" in navData?.payload) {
				const configs = await getAllProjectConfigs();
				const candidates = await getCandidates(navData);
				const bestCandidate = await getNearestOfflinePreplot(candidates);
				if (bestCandidate) {
					navData.payload._schema = bestCandidate.schema;
					last_tstamp = now;
				}
			}
		}
	}

	await saveOffline(navData, opts);
	DEBUG("Saved");
}

module.exports = save;
