const path = require('path');
const fs = require('fs').promises;
const cfg = require('DOUGAL_ROOT/lib/config');
const { setSurvey, pool } = require('../connection');
const get = require('./get');
const { INFO, DEBUG, WARNING, ERROR } = require('DOUGAL_ROOT/debug')(__filename);


function checkSyntax (value, type = "project") {
	var requiredFields = {};

	switch (type) {
		case "project":
			requiredFields = {
				id: "string",
				name: "string",
				epsg: "number",
				binning: function (value) { return checkSyntax (value, "binning"); }
			};
			break;
		case "binning":
			requiredFields = {
				theta: "number",
				I_inc: "number",
				J_inc: "number",
				I_width: "number",
				J_width: "number",
				origin: function (value) { return checkSyntax (value, "origin"); }
			}
			break
		case "origin":
			requiredFields = {
				easting: "number",
				northing: "number",
				I: "number",
				J: "number"
			}
			break;
		default:
			return typeof type == "function"
				? type(value)
				: typeof value == type;
	}

	for (const [field, test] of Object.entries(requiredFields)) {
		if (!value.hasOwnProperty(field)) {
			return `Missing required property: ${field}`;
		}
		const res = checkSyntax(value[field], test);
		if (res !== true) {
			return res === false ? `Syntax error on "${field}"` : res;
		}
	}
	return true;

}

async function applySchemaTemplate (projectDefinition) {
	const templatePath = path.resolve(cfg.DOUGAL_ROOT, "etc/db/schema-template.sql");
	const text = await fs.readFile(templatePath, "utf-8");
	return text.replace(/_SURVEY__TEMPLATE_/g, projectDefinition.schema).replace(/_EPSG__CODE_/g, projectDefinition.epsg);
}

async function surveyIds () {
	const res = await pool.query("SELECT schema FROM public.projects;");
	if (res.rows?.length) {
		return res.rows.map( s => Number(s.schema.replace(/^survey_/, "")) ).sort((a, b) => a-b);
	} else {
		return []
	}
}

async function nextSurveyId () {
	const ids = await surveyIds();
	if (ids.length) {
		return ids.pop() + 1;
	} else {
		return 1;
	}
}

async function idExists (id) {
	const surveys = await get();
	return surveys.includes(s => s.pid.toLowerCase() == id.toLowerCase());
}

async function createProjectSchema (projectDefinition) {
	const sql = await applySchemaTemplate(projectDefinition);
	const client = await pool.connect();
	try {
		await client.query(sql);
	} catch (err) {
		console.error(err);
	} finally {
		client.release(true);
	}
}

async function dropProjectSchema (projectDefinition) {
	const sql = `
		DROP SCHEMA ${projectDefinition.schema} CASCADE;
	`;
	try {
		return await pool.query(sql);
	} catch (err) {
		console.error("dropProjectSchema", err);
	}
}

async function addProjectToList (projectDefinition) {
	const sql = `
		INSERT INTO public.projects (pid, name, schema, meta) VALUES (LOWER($1), $2, $3, $4);
	`;
	const values = [ projectDefinition.id, projectDefinition.name, projectDefinition.schema, projectDefinition ];
	try {
		return await pool.query(sql, values);
	} catch (err) {
		if (err.code == "23505") {
			if (err.constraint == "projects_name_key") {
				throw { message: "A project with this name already exists" }
			}
		} else {
			throw err;
		}
	}
}

async function updateProject (projectId, projectDefinition) {
	const sql = `
		UPDATE public.projects
		SET
			name = $2,
			meta = $3
		WHERE pid = $1;
	`;
	const values = [ projectId, projectDefinition.name, projectDefinition ];
	try {
		return await pool.query(sql, values);
	} catch (err) {
		throw err;
	}
}

async function create (projectDefinition) {

	const syntaxOk = checkSyntax(projectDefinition);
	if (syntaxOk !== true) {
		throw { status: 400, message: syntaxOk };
	} else if (await idExists(projectDefinition.id)) {
		throw { status: 409 }
	} else {
		try {
			const survey_id = await nextSurveyId();
			projectDefinition.schema = `survey_${survey_id}`;
			projectDefinition.archived = projectDefinition.archived ?? false;
			await createProjectSchema(projectDefinition)
			await addProjectToList(projectDefinition);
		} catch (err) {
			DEBUG(err);
			await dropProjectSchema(projectDefinition);
			throw { status: 500, message: err.message ?? "Failed to create database for new project", detail: err.detail }
		}
	}

}


async function modify (projectId, projectDefinition) {

	const syntaxOk = checkSyntax(projectDefinition);
	if (syntaxOk !== true) {
		throw { status: 400, message: syntaxOk };
	} else {
		try {
			const res = await updateProject(projectId, projectDefinition);
		} catch (err) {
			DEBUG(err);
			throw { status: 500, message: err.message ?? "Failed to update project definition", detail: err.detail }
		}
	}

}


module.exports = {
	checkSyntax,
	create,
	modify
};

