module (..., package.seeall)

-- Load libraries
require("modelfunctions")
require("posix")
require("fs")
require("format")
require("validator")
require("luasql.postgres")
require("session")

-- Set variables
local DatabaseName = "provisioning"
local DatabaseUser = "postgres"
local DatabasePassword

local path = "PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin "
local baseurl = "/etc/provisioning/templates/"
local updatedevicescriptfile = "/etc/provisioning/update_device.lua"
local updatedeviceparamsscriptfile = "/etc/provisioning/update_device_params.lua"
local deletedevicescriptfile = "/etc/provisioning/delete_device.lua"
local env
local con

local saved_devices = {}
local saved_device_params = {}

-- if a table_creation_script does not create the named table or throw an exception then you will get an infinite loop, so be careful
local table_creation_scripts = {
	-- List of all available templates
	provisioning_templates = {
		"CREATE TABLE provisioning_templates (template_id SERIAL PRIMARY KEY, filename VARCHAR(255) UNIQUE, label VARCHAR(255) UNIQUE)",
	},
	-- List of each device that we manage
	provisioning_devices = {
		"CREATE TABLE provisioning_devices (device_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255), template_id INTEGER REFERENCES provisioning_templates)",
		"CREATE INDEX devices_name_idx ON provisioning_devices (name)",
		"CREATE INDEX devices_template_idx ON provisioning_devices (template_id)",
	},
	-- Multi-to-multi mapping of devices to classes
	devices_to_classes = {
		"CREATE TABLE devices_to_classes (device_id INTEGER REFERENCES provisioning_devices, class_id INTEGER REFERENCES provisioning_classes)",
		"CREATE INDEX d2c_device_idx ON devices_to_classes (device_id)",
		"CREATE INDEX d2c_class_idx ON devices_to_classes (class_id)",
		-- Need to enforce that devices do not contain more than one class in the same class group (using triggers)
		-- Theoretically should also check on updates and updates of classes, but not going to bother
		"CREATE OR REPLACE FUNCTION check_class_groups() RETURNS TRIGGER AS $$ \
			BEGIN \
				PERFORM * from provisioning_classes join devices_to_classes using(class_id) where device_id=NEW.device_id and class_group_id=(select class_group_id from provisioning_classes where class_id=NEW.class_id); \
				IF FOUND THEN \
					RAISE EXCEPTION 'Device cannot have multiple classes within the same class group'; \
				END IF; \
				RETURN NEW; \
			END; \
		$$ LANGUAGE plpgsql",
		"CREATE TRIGGER check_class_trigger BEFORE INSERT ON devices_to_classes FOR EACH ROW EXECUTE PROCEDURE check_class_groups()",
	},
	-- List of different class groups
	provisioning_class_groups = {
		"CREATE TABLE provisioning_class_groups (class_group_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255) UNIQUE, seq INTEGER)",
	},
	-- List of different device classes
	provisioning_classes = {
		"CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, class_group_id INTEGER REFERENCES provisioning_class_groups, label VARCHAR(255), seq INTEGER)",
		"CREATE INDEX classes_group_idx ON provisioning_classes (class_group_id)",
		"CREATE INDEX classes_label_idx ON provisioning_classes (label)",
		-- Need to enforce that group/label combo is unique
		"CREATE UNIQUE INDEX classes_group_label_idx ON provisioning_classes (class_group_id, label)",
	},
	-- Multi-to-multi mapping of classes to parameter groups
	classes_to_param_groups = {
		"CREATE TABLE classes_to_param_groups (class_id INTEGER REFERENCES provisioning_classes, group_id INTEGER REFERENCES provisioning_groups)",
		"CREATE INDEX c2g_class_idx ON classes_to_param_groups (class_id)",
		"CREATE INDEX c2g_group_idx ON classes_to_param_groups (group_id)",
		-- Need to enforce that classes do not contain more than one parameter group with same name (using triggers)
		-- Theoretically should also check on updates and updates of groups, but not going to bother
		"CREATE OR REPLACE FUNCTION check_group_names() RETURNS TRIGGER AS $$ \
		        BEGIN \
				PERFORM * from provisioning_groups join classes_to_param_groups using(group_id) where class_id=NEW.class_id and name=(select name from provisioning_groups where group_id=NEW.group_id); \
				IF FOUND THEN \
					RAISE EXCEPTION 'Classes cannot have multiple parameter groups with the same name'; \
				END IF; \
				RETURN NEW; \
			END; \
		$$ LANGUAGE plpgsql",
		"CREATE TRIGGER check_group_trigger BEFORE INSERT ON classes_to_param_groups FOR EACH ROW EXECUTE PROCEDURE check_group_names()",
	},
	-- List of different parameter groups
	provisioning_groups = {
		"CREATE TABLE provisioning_groups (group_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE, seq INTEGER)",
		"CREATE INDEX groups_name_idx ON provisioning_groups (name)",
	},
	-- Multi-to-multi mapping of parameter groups to parameters
	param_groups_to_params = {
		"CREATE TABLE param_groups_to_params (group_id INTEGER REFERENCES provisioning_groups, param_id INTEGER REFERENCES provisioning_params, value VARCHAR(255), editable BOOLEAN)",
		"CREATE INDEX g2p_group_idx ON param_groups_to_params (group_id)",
		"CREATE INDEX g2p_param_idx ON param_groups_to_params (param_id)",
		"CREATE INDEX g2p_editable_idx ON param_groups_to_params (editable)",
		"CREATE UNIQUE INDEX g2p_group_param_idx ON param_groups_to_params (group_id, param_id)",
	},
	-- List of each parameter used in any way for any device - mostly for how to display
	provisioning_params = {
		"CREATE TABLE provisioning_params (param_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255), seq INTEGER, regexp VARCHAR(255))",
		"CREATE INDEX params_name_idx ON provisioning_params (name)",
	},
	-- All of the (non-default) parameter values for all devices are stored here
	provisioning_values = {
		-- device_id is a device id from provisioning_devices and param_id is a param id from provisioning_params
		"CREATE TABLE provisioning_values (device_id INTEGER REFERENCES provisioning_devices, group_name VARCHAR(255), param_id INTEGER REFERENCES provisioning_params, value VARCHAR(255))",
		"CREATE INDEX values_device_idx ON provisioning_values (device_id)",
		"CREATE INDEX values_group_idx ON provisioning_values (group_name)",
		"CREATE INDEX values_param_idx ON provisioning_values (param_id)",
		"CREATE UNIQUE INDEX values_device_group_param_idx ON provisioning_values (device_id, group_name, param_id)",
		-- Need to enforce that group_name is a valid name in provisioning_groups (cannot use foreign key because name is not unique in provisioning_groups)
		-- Better yet, we'll check for a valid combination of device, group, and param (using triggers)
		-- Theoretically should also check on updates and updates of devices, groups, or parameters, but not going to bother
		"CREATE OR REPLACE FUNCTION check_valid_param() RETURNS TRIGGER AS $$ \
		        BEGIN \
				PERFORM * from devices_to_classes d join provisioning_classes using(class_id) join classes_to_param_groups using(class_id) join provisioning_groups g using(group_id) join param_groups_to_params p using(group_id) where d.device_id=NEW.device_id and g.name=NEW.group_name and p.param_id=NEW.param_id; \
				IF NOT FOUND THEN \
					RAISE EXCEPTION 'Invalid combination of device, group, and parameter'; \
				END IF; \
				RETURN NEW; \
			END; \
		$$ LANGUAGE plpgsql",
		"CREATE TRIGGER valid_param_trigger BEFORE INSERT ON provisioning_values FOR EACH ROW EXECUTE PROCEDURE check_valid_param()",
	},
	-- List of options for parameters
	provisioning_options = {
		"CREATE TABLE provisioning_options (param_id INTEGER REFERENCES provisioning_params, label VARCHAR(255), value VARCHAR(255), seq INTEGER)",
		"CREATE INDEX options_param_idx ON provisioning_options (param_id)",
		"CREATE UNIQUE INDEX options_param_label_idx ON provisioning_options (param_id, label)",
	},
}

-- ################################################################################
-- LOCAL FUNCTIONS
local function escape_quotes(str)
	return string.gsub(str or "", "'", "'\\''")
end

local function assert (v, m)
	if not v then
		m = m or "Assertion failed!"
		error(m, 0)
	end
	return v, m
end

-- Escape special characters in sql statements
local escape = function(sql)
	sql = sql or ""
	sql = string.gsub(sql, "'", "''")
	return string.gsub(sql, "\\", "\\\\")
end

local createdatabase = function()
        local result = {}

	-- First, create the user
	if DatabaseUser ~= "postgres" then
		local cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..''
		if DatabasePassword then
			cmd = cmd .. ' WITH PASSWORD \''..DatabasePassword..'\''
		end
		cmd = cmd .. '" 2>&1'
		local f = io.popen(cmd)
		table.insert(result, f:read("*a"))
		f:close()
	end
	
	-- Create the database
	local cmd = path..'psql -U postgres -c "CREATE DATABASE '..DatabaseName..' WITH OWNER '..DatabaseUser..'" 2>&1'
	local f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()

	cmd = path..'createlang -U postgres plpgsql '..DatabaseName
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()

	logevent(table.concat(result, "\n"))

	return table.concat(result, "\n")
end
	
-- Delete the database and roles
local deletedatabase = function()
	local result = {}
	
	local cmd = path..'psql -U postgres -c "DROP DATABASE '..DatabaseName..'" 2>&1'
	local f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()

	if DatabaseUser ~= "postgres" then
		cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseUser..'" 2>&1'
		f = io.popen(cmd)
		table.insert(result, f:read("*a"))
		f:close()
	end

	logevent(table.concat(result, "\n"))

	return table.concat(result, "\n")
end

local databaseconnect = function()
	if not con then
		-- create environment object
		env = assert (luasql.postgres())
		-- connect to data source
		local err
		con, err = env:connect(DatabaseName, DatabaseUser, DatabasePassword)
		if err and string.match(err, "Error connecting to database.") then
			createdatabase()
			con, err = env:connect(DatabaseName, DatabaseUser, DatabasePassword)
		end
		assert(con, err)
		return true
	end
	return false
end

local databasedisconnect = function()
	if env then
		env:close()
		env = nil
	end
	if con then
		con:close()
		con = nil
	end
end

-- Declare runsqlcommand first because it's recursive
-- we also have recursion when runsqlcommand calls runscript, so we have to be careful in creating table_creation_scripts
local runsqlcommand

local runscript = function(script, in_transaction)
	for i,scr in ipairs(script) do
		runsqlcommand(scr, in_transaction)
	end
end

runsqlcommand = function(sql, in_transaction)
logevent(sql)
	if in_transaction then assert(con:execute("SAVEPOINT before_command")) end
        local res, err = con:execute(sql)
	if not res and err then
		-- Catch the error to see if it's caused by lack of table
logevent(err)
		local table = string.match(err, "relation \"(%S+)\" does not exist")
		if table and table_creation_scripts[table] then
			if in_transaction then assert(con:execute("ROLLBACK TO before_command")) end
			runscript(table_creation_scripts[table])
			runsqlcommand(sql)
		else
			assert(res, err)
		end
	else
		if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_command")) end
	end
end

local getselectresponse
getselectresponse = function(sql, in_transaction)
	local retval = {}
	if in_transaction then assert(con:execute("SAVEPOINT before_select")) end
        local res, err = pcall(function()
logevent(sql)
		local cur = assert (con:execute(sql))
		local row = cur:fetch ({}, "a")
		while row do
			local tmp = {}
			for name,val in pairs(row) do
				tmp[name] = val
			end
			retval[#retval + 1] = tmp
			row = cur:fetch (row, "a")
		end
		cur:close()
	end)
	if not res and err then
logevent(err)
		-- Catch the error to see if it's caused by lack of table
		local table = string.match(err, "relation \"(%S+)\" does not exist")
		if table and table_creation_scripts[table] then
			if in_transaction then assert(con:execute("ROLLBACK TO before_select")) end
			runscript(table_creation_scripts[table])
			return getselectresponse(sql)
		else
			assert(res, err)
		end
	else
		if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_select")) end
	end
	return retval
end

local validateparam
validateparam = function(p)
	if p.type == "group" then
		local success = true
		for n,p2 in pairs(p.value) do
			success = validateparam(p2) and success
		end
		return success
	elseif p.type == "select" then
		return modelfunctions.validateselect(p)
	elseif p.type == "text" and p.regexp and p.regexp ~= "" then
		if not string.match(p.value, p.regexp) then
			p.errtxt = "Invalid value"
			return false
		end
	end
	return true
end

local function callscript(script, ...)
	local functions = {
		getselectresponse=getselectresponse,
		runsqlcommand=runsqlcommand,
		get_device=get_device,
		get_device_params=get_device_params,
	}
	local env = {}
	setmetatable (env, {__index = _G})
	-- loadfile loads into the global environment
	-- so we set env 0, not env 1
	setfenv (0, env)
	local f = loadfile(script)
	if (f) then f(functions, ...) end
	setfenv (0, _G)
end

local function validatefiledetails(filedetails)
	local success = true
	
	-- Validate that contents are valid lua code
	local env = {}
	setmetatable (env, {__index = _G})
	-- loadfile loads into the global environment
	-- so we set env 0, not env 1
	setfenv (0, env)
	local f,err = loadstring(filedetails.value.filecontent.value)
	if not (f) then
		success = false
		filedetails.value.filecontent.errtxt = "Invalid Lua code\n"..(err or "")
	end
	setfenv (0, _G)
	-- setmetatable (self.conf.app_hooks, {})

	return success, filedetails
end

local duplicatestructure
duplicatestructure = function(value, saved)
	saved = saved or {}
	if type(value) == "table" then
		if saved[value] then
			return saved[value]
		else
			local output = {}
			saved[value] = output
			for k,v in pairs(value) do
				output[k] = duplicatestructure(v, saved)
			end
			return output
		end
	else
		return value
	end
end

-- ################################################################################
-- PUBLIC FUNCTIONS

list_templates = function()
	local retval = {}
	local errtxt
	-- Get the templates from the DB
        local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_templates ORDER BY label ASC, filename ASC"
		retval = getselectresponse(sql)
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end
	local reversed = {}
	for i,t in ipairs(retval) do
		reversed[t.filename] = i
	end
	-- Get the file stats for each template and add in any template files that aren't in the DB
	fs.create_directory(baseurl)
	for f in posix.files(baseurl) do
		local file = baseurl..f
                local details = fs.stat(file)
		if details.type == "regular" then
			if not reversed[file] then
				retval[#retval+1] = {filename=file}
				reversed[file] = #retval
			end
			local t = retval[reversed[file]]
			t.filesize = details.size
			t.mtime = details.mtime
		end
	end

	return cfe({ type="structure", value=retval, label="List of Templates", errtxt=errtxt })
end

get_template = function(template_id, filename)
	local retval = {}
	if filename and not string.match(filename, "/") then
		filename = baseurl .. filename
	end
	retval.template_id = cfe({value=template_id or "", label="Template ID"})
	retval.filename = cfe({value=filename or "", label="File Name", descr="Must be in "..baseurl})
	retval.label = cfe({label="Template Label"})
	local errtxt
	if template_id and template_id ~= "" then
	        local res, err = pcall(function()
			local connected = databaseconnect()
			local sql = "SELECT * FROM provisioning_templates WHERE template_id='"..escape(template_id).."' ORDER BY label ASC, filename ASC"
			local tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				for n,v in pairs(tmp[1]) do
					if retval[n] then
						retval[n].value = v
					end
				end
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			errtxt = err
		end
	end
	local filedetails = modelfunctions.getfiledetails(retval.filename.value, function(filename) return validator.is_valid_filename(filename, baseurl) end)
	for i,n in ipairs({"filecontent", "filesize", "mtime"}) do
		retval[n] = filedetails.value[n]
	end

	return cfe({ type="group", value=retval, label="Provisioning Template", errtxt=errtxt })
end

create_template = function(template)
	return update_template(template, true)
end

update_template = function(template, create)
	local success = true
	local errtxt
	-- Validate the settings
	if template.value.filename and not string.match(template.value.filename.value, "/") then
		template.value.filename.value = baseurl .. template.value.filename.value
	end
	if not validator.is_valid_filename(template.value.filename.value, baseurl) then
		success = false
		template.value.filename.errtxt = "Invalid filename"
	end
	if template.value.label.value == "" then
		success = false
		template.value.label.errtxt = "Cannot be blank"
	end
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			if not create then
				local sql = "SELECT * FROM provisioning_templates WHERE template_id='"..escape(template.value.template_id.value).."'"
				local tmp = getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					errtxt = "Template does not exist"
				end
			end
			if success then
				if create then
					sql = "INSERT INTO provisioning_templates VALUES(DEFAULT, '"..escape(template.value.filename.value).."', '"..escape(template.value.label.value).."')"
				else
					sql = "UPDATE provisioning_templates SET (filename, label) = ('"..escape(template.value.filename.value).."', '"..escape(template.value.label.value).."') WHERE template_id='"..escape(template.value.template_id.value).."'"
				end
				runsqlcommand(sql)

				fs.write_file(template.value.filename.value, string.gsub(format.dostounix(template.value.filecontent.value), "\n+$", ""))
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			success = false
			errtxt = err
		end
	end
	if not success then
		if create then
			template.errtxt = errtxt or "Failed to create template"
		else
			template.errtxt = errtxt or "Failed to save template"
		end
	end
	return template
end

delete_template = function(template_id)
	local result = ""
	local errtxt
       	local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_templates WHERE template_id='"..escape(template_id).."'"
		local tmp = getselectresponse(sql)
		if #tmp == 0 then
			errtxt = "Template does not exist"
		else
			-- Remove the template
			sql = "DELETE FROM provisioning_templates WHERE template_id='"..escape(template_id).."'"
			runsqlcommand(sql)
			-- Delete the template file
			os.remove(tmp[1].filename)
			result = "Template Deleted"
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ value=result, errtxt=errtxt, label="Delete Template Result" })
end

list_class_groups = function()
	local retval = {}
	local errtxt
	-- Get the groups from the DB
        local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_class_groups ORDER BY seq ASC, label ASC"
		retval = getselectresponse(sql)
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="structure", value=retval, label="List of Class Groups", errtxt=errtxt })
end

get_class_group = function(class_group_id)
	local retval = {}
	retval.class_group_id = cfe({value=class_group_id or "", label="Class Group ID", seq=1})
	retval.name = cfe({label="Name", seq=2})
	retval.label = cfe({label="Label", seq=3})
	retval.seq = cfe({label="Sequence", seq=4})
	local errtxt
	local res, err = pcall(function()
		local connected = databaseconnect()
		if class_group_id and class_group_id ~= "" then
			sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..escape(class_group_id).."'"
			tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				for n,v in pairs(tmp[1]) do
					if retval[n] then
						retval[n].value = v
					end
				end
			end
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="group", value=retval, label="Provisioning Class Group", errtxt=errtxt })
end

create_class_group = function(group)
	return update_class_group(group, true)
end

update_class_group = function(group, create)
	local success = true
	local errtxt
	-- Validate the settings
	if group.value.name.value == "" then
		success = false
		group.value.name.errtxt = "Cannot be blank"
	elseif string.find(group.value.name.value, "%W") then
		success = false
		group.value.name.errtxt = "Must only contain alphanumeric characters"
	end
	if group.value.label.value == "" then
		success = false
		group.value.label.errtxt = "Cannot be blank"
	end
	if not validator.is_integer(group.value.seq.value) then
		success = false
		group.value.seq.errtxt = "Must be an integer"
	end
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			if not create then
				local sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..escape(group.value.class_group_id.value).."'"
				local tmp = getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					errtxt = "Group does not exist"
				end
			end
			if success then
				local sql = "BEGIN TRANSACTION"
				runsqlcommand(sql)
				if create then
					sql = "INSERT INTO provisioning_class_groups VALUES(DEFAULT, '"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."')"
					runsqlcommand(sql, true)
					sql = "SELECT class_group_id FROM provisioning_class_groups WHERE label='"..escape(group.value.label.value).."'"
					local tmp = getselectresponse(sql, true)
					if tmp and #tmp>0 then
						group.value.class_group_id.value = tmp[1].class_group_id
					end
				else
					sql = "UPDATE provisioning_class_groups SET (name, label, seq) = ('"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."') WHERE class_group_id='"..escape(group.value.class_group_id.value).."'"
					runsqlcommand(sql, true)
				end

				sql = "COMMIT"
				runsqlcommand(sql)
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			pcall(function() con:execute("ROLLBACK") end)
			success = false
			errtxt = err
		end
	end
	if not success then
		if create then
			group.errtxt = errtxt or "Failed to create class group"
		else
			group.errtxt = errtxt or "Failed to save class group"
		end
	end
	return group
end

delete_class_group = function(class_group_id)
	local result = ""
	local errtxt
       	local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_class_groups WHERE class_group_id='"..escape(class_group_id).."'"
		local tmp = getselectresponse(sql)
		if #tmp == 0 then
			errtxt = "Group does not exist"
		else
			sql = "DELETE FROM provisioning_class_groups WHERE class_group_id='"..escape(class_group_id).."'"
			runsqlcommand(sql)
			result = "Class Group Deleted"
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ value=result, errtxt=errtxt, label="Delete Class Group Result" })
end

list_classes = function()
	local retval = {}
	local errtxt
	-- Get the classes from the DB
        local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT class_id, g.label AS group, c.label, c.seq FROM provisioning_classes c JOIN provisioning_class_groups g USING(class_group_id) ORDER BY g.seq ASC, g.label ASC, c.seq ASC, c.label ASC"
		retval = getselectresponse(sql)
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="structure", value=retval, label="List of Classes", errtxt=errtxt })
end

get_class = function(class_id)
	local retval = {}
	retval.class_id = cfe({value=class_id or "", label="Class ID", seq=1})
	retval.class_group_id = cfe({type="select", label="Class Group", option={}, seq=2})
	retval.label = cfe({label="Label", seq=3})
	retval.seq = cfe({label="Sequence", seq=4})
	retval.groups = cfe({type="group", value={}, label="Parameter Groups", seq=5})
	local errtxt
	local res, err = pcall(function()
		local groups = {}
		local connected = databaseconnect()
		if class_id and class_id ~= "" then
			local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class_id).."'"
			local tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				for n,v in pairs(tmp[1]) do
					if retval[n] then
						retval[n].value = v
					end
				end
			end
			-- Now, get the class-to-paramgroup mappings
			sql = "SELECT group_id FROM classes_to_param_groups WHERE class_id="..escape(class_id)
			tmp = getselectresponse(sql)
			for i,g in ipairs(tmp) do
				groups[g.group_id] = true
			end
		end
		-- Get the class_group_id options
		sql = "SELECT * from provisioning_class_groups ORDER BY seq ASC, label ASC"
		tmp = getselectresponse(sql)
		for i,g in ipairs(tmp) do
			retval.class_group_id.option[#retval.class_group_id.option + 1] = {value=g.class_group_id, label=g.label}
		end
		-- Finally, get the paramgroup options
		sql = "SELECT group_id, name, label FROM provisioning_groups ORDER BY seq ASC, name ASC"
		tmp = getselectresponse(sql)
		for i,g in ipairs(tmp) do
			if not retval.groups.value[g.name] then
				retval.groups.value[g.name] = cfe({type="select", label=g.name, option={{value="", label=""}}, seq=i})
			end
			local group = retval.groups.value[g.name]
			group.option[#group.option + 1] = {value=g.group_id, label=g.label}
			if groups[g.group_id] then
				group.value = g.group_id
			end
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="group", value=retval, label="Provisioning Class", errtxt=errtxt })
end

create_class = function(class)
	return update_class(class, true)
end

update_class = function(class, create)
	local success = true
	local errtxt
	-- Validate the settings
	success = modelfunctions.validateselect(class.value.class_group_id) and success
	if class.value.label.value == "" then
		success = false
		class.value.label.errtxt = "Cannot be blank"
	end
	if not validator.is_integer(class.value.seq.value) then
		success = false
		class.value.seq.errtxt = "Must be an integer"
	end
	for n,g in pairs(class.value.groups.value) do
		success = modelfunctions.validateselect(g) and success
	end
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			if not create then
				local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class.value.class_id.value).."'"
				local tmp = getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					errtxt = "Class does not exist"
				end
			end
			if success then
				local sql = "BEGIN TRANSACTION"
				runsqlcommand(sql)
				if create then
					sql = "INSERT INTO provisioning_classes VALUES(DEFAULT, '"..escape(class.value.class_group_id.value).."', '"..escape(class.value.label.value).."', '"..escape(class.value.seq.value).."')"
					runsqlcommand(sql, true)
					sql = "SELECT class_id FROM provisioning_classes WHERE class_group_id='"..escape(class.value.class_group_id.value).."' AND label='"..escape(class.value.label.value).."'"
					local tmp = getselectresponse(sql, true)
					if tmp and #tmp>0 then
						class.value.class_id.value = tmp[1].class_id
					end
				else
					sql = "UPDATE provisioning_classes SET (class_group_id, label, seq) = ('"..escape(class.value.class_group_id.value).."', '"..escape(class.value.label.value).."', '"..escape(class.value.seq.value).."') WHERE class_id='"..escape(class.value.class_id.value).."'"
					runsqlcommand(sql, true)
					sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..escape(class.value.class_id.value).."'"
					runsqlcommand(sql, true)
				end
				-- Insert the class to group entries
				for n,g in pairs(class.value.groups.value) do
					if g.value ~= "" then
						sql = "INSERT INTO classes_to_param_groups VALUES('"..escape(class.value.class_id.value).."', '"..escape(g.value).."')"
						runsqlcommand(sql, true)
					end
				end

				sql = "COMMIT"
				runsqlcommand(sql)
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			pcall(function() con:execute("ROLLBACK") end)
			success = false
			errtxt = err
		end
	end
	if not success then
		if create then
			class.errtxt = errtxt or "Failed to create class"
		else
			class.errtxt = errtxt or "Failed to save class"
		end
	end
	return class
end

delete_class = function(class_id)
	local result = ""
	local errtxt
       	local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_classes WHERE class_id='"..escape(class_id).."'"
		local tmp = getselectresponse(sql)
		if #tmp == 0 then
			errtxt = "Class does not exist"
		else
			sql = "BEGIN TRANSACTION"
			runsqlcommand(sql)
			sql = "DELETE FROM provisioning_classes WHERE class_id='"..escape(class_id).."'"
			runsqlcommand(sql, true)
			sql = "DELETE FROM classes_to_param_groups WHERE class_id='"..escape(class_id).."'"
			runsqlcommand(sql, true)
			result = "Class Deleted"
			sql = "COMMIT"
			runsqlcommand(sql)
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		pcall(function() con:execute("ROLLBACK") end)
		errtxt = err
	end

	return cfe({ value=result, errtxt=errtxt, label="Delete Class Result" })
end

list_groups = function()
	local retval = {}
	local errtxt
	-- Get the groups from the DB
        local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_groups ORDER BY seq ASC, name ASC, label ASC"
		retval = getselectresponse(sql)
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="structure", value=retval, label="List of Parameter Groups", errtxt=errtxt })
end

get_group = function(group_id)
	local retval = {}
	retval.group_id = cfe({value=group_id or "", label="Group ID", seq=1})
	retval.name = cfe({label="Name", seq=2})
	retval.label = cfe({label="Label", seq=3})
	retval.seq = cfe({label="Sequence", seq=4})
	retval.params = cfe({type="multi", value={}, label="Parameters", option={}, descr="Each selected parameter will be included in the group", seq=5})
	retval.editable = cfe({type="multi", value={}, label="Editable Parameters", option={}, descr="Each selected parameter will be user editable", seq=6})
	retval.defaults = cfe({type="group", value={}, label="Parameter Defaults", seq=7})
	local errtxt
	local res, err = pcall(function()
		local connected = databaseconnect()
		-- First, let's get all the parameters to set up the params.options and defaults
		local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC"
		local tmp = getselectresponse(sql)
		for i,p in ipairs(tmp) do
			retval.params.option[#retval.params.option + 1] = {value=p.param_id, label=p.label}
			retval.editable.option[#retval.editable.option + 1] = {value=p.param_id, label=p.label}
			p.seq = i
			if p.type == "select" then
				sql = "SELECT * FROM provisioning_options WHERE param_id='"..escape(p.param_id).."' ORDER BY seq ASC"
				p.option = getselectresponse(sql) or {}
			end
			if p.type == "boolean" then
				p.value = (p.value == "true")
			end
			retval.defaults.value[p.param_id] = p
		end
		if group_id and group_id ~= "" then
			sql = "SELECT * FROM provisioning_groups WHERE group_id='"..escape(group_id).."'"
			tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				for n,v in pairs(tmp[1]) do
					if retval[n] then
						retval[n].value = v
					end
				end
			end
			-- Now, get the paramgroup-to-param mappings
			sql = "SELECT * FROM param_groups_to_params WHERE group_id="..escape(group_id)
			tmp = getselectresponse(sql)
			for i,p in ipairs(tmp) do
				retval.params.value[#retval.params.value + 1] = p.param_id
				if (p.editable == "t") then
					retval.editable.value[#retval.editable.value + 1] = p.param_id
				end
				if retval.defaults.value[p.param_id].type == "boolean" then
					retval.defaults.value[p.param_id].value = (p.value == "true")
				else
					retval.defaults.value[p.param_id].value = p.value
				end
			end
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="group", value=retval, label="Provisioning Parameter Group", errtxt=errtxt })
end

create_group = function(group)
	return update_group(group, true)
end

update_group = function(group, create)
	local success = true
	local errtxt
	-- Validate the settings
	success = modelfunctions.validatemulti(group.value.params)
	if group.value.name.value == "" then
		success = false
		group.value.name.errtxt = "Cannot be blank"
	-- Group name cannot be device_id, name, or label or will mess up get_device_params
	elseif group.value.name.value == "device_id" or group.value.name.value == "name" or  group.value.name.value == "label" then 
		success = false
		group.value.name.errtxt = "Illegal name"
	elseif string.find(group.value.name.value, "%W") then
		success = false
		group.value.name.errtxt = "Must only contain alphanumeric characters"
	end
	if group.value.label.value == "" then
		success = false
		group.value.label.errtxt = "Cannot be blank"
	end
	if not validator.is_integer(group.value.seq.value) then
		success = false
		group.value.seq.errtxt = "Must be an integer"
	end
	-- Validate the param defaults
	success = validateparam(group.value.defaults) and success
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			if not create then
				local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..escape(group.value.group_id.value).."'"
				local tmp = getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					errtxt = "Group does not exist"
				end
			end
			if success then
				local sql = "BEGIN TRANSACTION"
				runsqlcommand(sql)
				if create then
					sql = "INSERT INTO provisioning_groups VALUES(DEFAULT, '"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."')"
					runsqlcommand(sql, true)
					sql = "SELECT group_id FROM provisioning_groups WHERE name='"..escape(group.value.name.value).."' AND label='"..escape(group.value.label.value).."'"
					local tmp = getselectresponse(sql, true)
					if tmp and #tmp>0 then
						group.value.group_id.value = tmp[1].group_id
					end
				else
					sql = "UPDATE provisioning_groups SET (name, label, seq) = ('"..escape(group.value.name.value).."', '"..escape(group.value.label.value).."', '"..escape(group.value.seq.value).."') WHERE group_id='"..escape(group.value.group_id.value).."'"
					runsqlcommand(sql, true)
					sql = "DELETE FROM param_groups_to_params WHERE group_id='"..escape(group.value.group_id.value).."'"
					runsqlcommand(sql, true)
				end
				-- Reverse the editable table for ease of use below
				local reverseeditable = {}
				for i,p in ipairs(group.value.editable.value) do
					reverseeditable[p] = i
				end
				-- Insert the group to param entries
				for i,p in ipairs(group.value.params.value) do
					sql = "INSERT INTO param_groups_to_params VALUES('"..escape(group.value.group_id.value).."', '"..escape(p).."', '"..escape(tostring(group.value.defaults.value[p].value)).."', '"..tostring(reverseeditable[p] ~= nil).."')"
					runsqlcommand(sql, true)
				end

				sql = "COMMIT"
				runsqlcommand(sql)
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			pcall(function() con:execute("ROLLBACK") end)
			success = false
			errtxt = err
		end
	end
	if not success then
		if create then
			group.errtxt = errtxt or "Failed to create parameter group"
		else
			group.errtxt = errtxt or "Failed to save parameter group"
		end
	end
	return group
end

delete_group = function(group_id)
	local result = ""
	local errtxt
       	local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_groups WHERE group_id='"..escape(group_id).."'"
		local tmp = getselectresponse(sql)
		if #tmp == 0 then
			errtxt = "Group does not exist"
		else
			sql = "BEGIN TRANSACTION"
			runsqlcommand(sql)
			sql = "DELETE FROM provisioning_groups WHERE group_id='"..escape(group_id).."'"
			runsqlcommand(sql, true)
			sql = "DELETE FROM param_groups_to_params WHERE group_id='"..escape(group_id).."'"
			runsqlcommand(sql, true)
			result = "Parameter Group Deleted"
			sql = "COMMIT"
			runsqlcommand(sql)
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		pcall(function() con:execute("ROLLBACK") end)
		errtxt = err
	end

	return cfe({ value=result, errtxt=errtxt, label="Delete Parameter Group Result" })
end

list_params = function()
	local retval = {}
	local errtxt
	-- Get the params from the DB
        local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_params ORDER BY seq ASC, name ASC, label ASC"
		retval = getselectresponse(sql)
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="structure", value=retval, label="List of Parameters", errtxt=errtxt })
end

get_param = function(param_id)
	local retval = {}
	retval.param_id = cfe({value=param_id or "", label="Param ID", seq=1})
	retval.name = cfe({label="Name", seq=2})
	retval.type = cfe({type="select", label="Type", option={"text", "boolean", "select"}, seq=3})
	retval.label = cfe({label="Label", seq=4})
	retval.descr = cfe({label="Description", seq=5})
	retval.value = cfe({label="Default Value", descr="Warning, this value is not validated", seq=6})
	retval.regexp = cfe({label="Regular Expression", descr="Lua regular expression for validating the text parameter value", seq=7})
	retval.seq = cfe({label="Sequence", seq=8})
-- FIXME - we should add validation and option stuff here
	local errtxt
	local res, err = pcall(function()
		local connected = databaseconnect()
		if param_id and param_id ~= "" then
			sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param_id).."'"
			tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				for n,v in pairs(tmp[1]) do
					if retval[n] then
						retval[n].value = v
					end
				end
			end
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="group", value=retval, label="Provisioning Parameter", errtxt=errtxt })
end

create_param = function(param)
	return update_param(param, true)
end

update_param = function(param, create)
	local success = true
	local errtxt
	-- Validate the settings
	success = modelfunctions.validateselect(param.value.type)
	if param.value.name.value == "" then
		success = false
		param.value.name.errtxt = "Cannot be blank"
	end
	if param.value.label.value == "" then
		success = false
		param.value.label.errtxt = "Cannot be blank"
	end
	if not validator.is_integer(param.value.seq.value) then
		success = false
		param.value.seq.errtxt = "Must be an integer"
	end
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			if not create then
				local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param.value.param_id.value).."'"
				local tmp = getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					errtxt = "Param does not exist"
				end
			end
			if success then
				local sql = "BEGIN TRANSACTION"
				runsqlcommand(sql)
				if create then
					sql = "INSERT INTO provisioning_params VALUES(DEFAULT, '"..escape(param.value.name.value).."', '"..escape(param.value.type.value).."', '"..escape(param.value.label.value).."', '"..escape(param.value.descr.value).."', '"..escape(param.value.value.value).."', '"..escape(param.value.seq.value).."', '"..escape(param.value.regexp.value).."')"
					runsqlcommand(sql, true)
					sql = "SELECT param_id FROM provisioning_params WHERE name='"..escape(param.value.name.value).."' AND label='"..escape(param.value.label.value).."'"
					local tmp = getselectresponse(sql, true)
					if tmp and #tmp>0 then
						param.value.param_id.value = tmp[1].param_id
					end
				else
					sql = "UPDATE provisioning_params SET (name, type, label, descr, value, seq, regexp) = ('"..escape(param.value.name.value).."', '"..escape(param.value.type.value).."', '"..escape(param.value.label.value).."', '"..escape(param.value.descr.value).."', '"..escape(param.value.value.value).."', '"..escape(param.value.seq.value).."', '"..escape(param.value.regexp.value).."') WHERE param_id='"..escape(param.value.param_id.value).."'"
					runsqlcommand(sql, true)
				end

				sql = "COMMIT"
				runsqlcommand(sql)
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			pcall(function() con:execute("ROLLBACK") end)
			success = false
			errtxt = err
		end
	end
	if not success then
		if create then
			param.errtxt = errtxt or "Failed to create parameter"
		else
			param.errtxt = errtxt or "Failed to save parameter"
		end
	end
	return param
end

delete_param = function(param_id)
	local result = ""
	local errtxt
       	local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param_id).."'"
		local tmp = getselectresponse(sql)
		if #tmp == 0 then
			errtxt = "Parameter does not exist"
		else
			sql = "BEGIN TRANSACTION"
			runsqlcommand(sql)
			sql = "DELETE FROM provisioning_params WHERE param_id='"..escape(param_id).."'"
			runsqlcommand(sql, true)
			sql = "DELETE FROM provisioning_options WHERE param_id='"..escape(param_id).."'"
			runsqlcommand(sql, true)
			result = "Parameter Deleted"
			sql = "COMMIT"
			runsqlcommand(sql)
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		pcall(function() con:execute("ROLLBACK") end)
		errtxt = err
	end

	return cfe({ value=result, errtxt=errtxt, label="Delete Parameter Result" })
end

list_devices = function()
	local retval = {}
	local errtxt
	-- Get the devices from the DB
        local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_devices WHERE name IS NOT NULL ORDER BY name ASC, label ASC"
		retval = getselectresponse(sql)
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="structure", value=retval, label="List of Devices", errtxt=errtxt })
end

get_device = function(device_id)
	local retval = {}
	retval.device_id = cfe({value=device_id or "", label="Device ID", seq=1})
	retval.name = cfe({label="Name", seq=2})
	retval.label = cfe({label="Label", seq=3})
	retval.template_id = cfe({type="select", label="Template", option={}, seq=4})
	retval.classes = cfe({type="group", value={}, label="Classes", seq=5})
	local errtxt
	local res, err = pcall(function()
		local classes={}
		local connected = databaseconnect()
		if device_id and device_id ~= "" then
			local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'"
			local tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				for n,v in pairs(tmp[1]) do
					if retval[n] then
						retval[n].value = v
					end
				end
			end
			-- Now, get the device-to-class mappings
			sql = "SELECT class_id FROM devices_to_classes WHERE device_id="..escape(device_id)
			tmp = getselectresponse(sql)
			for i,g in ipairs(tmp) do
				classes[g.class_id] = true
			end
		end
		-- Next, get the template options
		sql = "SELECT template_id, label FROM provisioning_templates ORDER BY label ASC, filename ASC"
		tmp = getselectresponse(sql)
		for i,t in ipairs(tmp) do
			retval.template_id.option[#retval.template_id.option + 1] = {value=t.template_id, label=t.label}
		end
		-- Finally, get the class options
		sql = "SELECT class_id, g.name, g.label AS group, c.label, c.seq FROM provisioning_classes c JOIN provisioning_class_groups g USING(class_group_id) ORDER BY g.seq ASC, g.label ASC, c.seq ASC, c.label ASC"
		tmp = getselectresponse(sql)
		for i,c in ipairs(tmp) do
			if not retval.classes.value[c.name] then
				retval.classes.value[c.name] = cfe({type="select", label=c.group, option={{value="", label=""}}, seq=i})
			end
			local class = retval.classes.value[c.name]
			class.option[#class.option + 1] = {value=c.class_id, label=c.label}
			if classes[c.class_id] then
				class.value = c.class_id
			end
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	-- Save the device for later use
	local output = cfe({ type="group", value=retval, label="Provisioning Device", errtxt=errtxt })
	if device_id and device_id ~= "" then saved_devices[device_id] = duplicatestructure(output) end
	return output
end

create_device = function(device)
	return update_device(device, true)
end

update_device = function(device, create)
	local success = true
	local errtxt
	-- Validate the settings
	success = modelfunctions.validateselect(device.value.template_id)
	if device.value.name.value == "" then
		success = false
		device.value.name.errtxt = "Cannot be blank"
	end
	for n,c in pairs(device.value.classes.value) do
		success = modelfunctions.validateselect(c) and success
	end
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			if not create then
				local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device.value.device_id.value).."'"
				local tmp = getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					errtxt = "Device does not exist"
				end
			end
			if success then
				if not saved_devices[device.value.device_id.value] then get_device(device.value.device_id.value) end
				if not saved_device_params[device.value.device_id.value] then get_device_params(device.value.device_id.value) end

				local sql = "BEGIN TRANSACTION"
				runsqlcommand(sql)
				if create then
					sql = "INSERT INTO provisioning_devices VALUES(DEFAULT, '"..escape(device.value.name.value).."', '"..escape(device.value.label.value).."', '"..escape(device.value.template_id.value).."')"
					runsqlcommand(sql, true)
					sql = "SELECT device_id FROM provisioning_devices WHERE name='"..escape(device.value.name.value).."' AND label='"..escape(device.value.label.value).."'"
					local tmp = getselectresponse(sql, true)
					if tmp and #tmp>0 then
						device.value.device_id.value = tmp[1].device_id
					end
				else
					sql = "UPDATE provisioning_devices SET (name, label, template_id) = ('"..escape(device.value.name.value).."', '"..escape(device.value.label.value).."', '"..escape(device.value.template_id.value).."') WHERE device_id='"..escape(device.value.device_id.value).."'"
					runsqlcommand(sql, true)
					sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device.value.device_id.value).."'"
					runsqlcommand(sql, true)
				end
				-- Insert the device to class entries
				for n,c in pairs(device.value.classes.value) do
					if c.value ~= "" then
						sql = "INSERT INTO devices_to_classes VALUES('"..escape(device.value.device_id.value).."', '"..escape(c.value).."')"
						runsqlcommand(sql, true)
					end
				end

				sql = "COMMIT"
				runsqlcommand(sql)

				local s = saved_device_params[device.value.device_id.value]
				callscript(updatedevicescriptfile, device, saved_devices[device.value.device_id.value], get_device_params(device.value.device_id.value), s)
				saved_devices[device.value.device_id.value] = device
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			pcall(function() con:execute("ROLLBACK") end)
			success = false
			errtxt = err
		end
	end
	if not success then
		if create then
			device.errtxt = errtxt or "Failed to create device"
		else
			device.errtxt = errtxt or "Failed to save device"
		end
	end
	return device
end

delete_device = function(device_id)
	local result = ""
	local errtxt
       	local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'"
		local tmp = getselectresponse(sql)
		if #tmp == 0 then
			errtxt = "Device does not exist"
		else
			if not saved_device_params[device_id] then get_device_params(device_id) end
			if not saved_devices[device_id] then get_device(device_id) end
			
			sql = "BEGIN TRANSACTION"
			runsqlcommand(sql)
			sql = "DELETE FROM provisioning_values WHERE device_id='"..escape(device_id).."'"
			runsqlcommand(sql, true)
			sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device_id).."'"
			runsqlcommand(sql, true)
			sql = "DELETE FROM provisioning_devices WHERE device_id='"..escape(device_id).."'"
			runsqlcommand(sql, true)
			result = "Device Deleted"
			sql = "COMMIT"
			runsqlcommand(sql)

			callscript(deletedevicescriptfile, saved_devices[device_id], saved_device_params[device_id])
			saved_devices[device_id] = nil
			saved_device_params[device_id] = nil
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		pcall(function() con:execute("ROLLBACK") end)
		errtxt = err
	end

	return cfe({ value=result, errtxt=errtxt, label="Delete Device Result" })
end

get_editable_device_params = function(device_id)
	return get_device_params(device_id, true)
end

get_device_params = function(device_id, editable)
	local retval = {}
	retval.device_id = cfe({value=device_id or "", label="Device ID", seq=0})
	retval.name = cfe({label="Name", seq=0})
	retval.label = cfe({label="Label", seq=0})
	local errtxt = "Cannot find device"
	if device_id and device_id ~= "" then
		local res, err = pcall(function()
			local connected = databaseconnect()
			-- First, just check to see if device_id exists
			local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(device_id).."'"
			local tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				errtxt = nil
				retval.name.value = tmp[1].name
				retval.label.value = tmp[1].label
				-- Next, get all of the param groups
				sql = "SELECT * FROM provisioning_groups"
				local tmp = getselectresponse(sql)
				-- Loop through the groups and put them into the result
				for i,g in ipairs(tmp) do
					retval[g.name] = g
					retval[g.name].label = g.name
					retval[g.name].type="group"
				end
				-- Then, get all of the parameters for this device
				sql = "SELECT g.name AS group, g.label AS grouplabel, p.param_id, p.name, p.type, p.label, p.descr, p.seq, p.regexp, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS value, g2p.value AS default "..
					"FROM (devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING (class_id) JOIN provisioning_groups g USING(group_id) "..
						"JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id)) LEFT JOIN provisioning_values v ON(d2t.device_id=v.device_id AND p.param_id=v.param_id AND g.name=v.group_name ) "..
					"WHERE d2t.device_id='"..escape(device_id).."'"
				if editable then
					sql = sql.." AND g2p.editable='t'"
				end
				local tmp = getselectresponse(sql)
				-- Loop through the params to figure out options and put them into the groups
				for i,p in ipairs(tmp) do
					-- Options
					if (p.type == "select") then
						sql = "SELECT * FROM provisioning_options WHERE param_id='"..escape(p.param_id).."' ORDER BY seq ASC"
						p.option = getselectresponse(sql) or {}
					end
					-- Groups
					if not retval[p.group].value then
						retval[p.group].value = {}
					end
					retval[p.group].label = p.grouplabel
					local value = retval[p.group].value
					if p.type == "boolean" then
						p.value = (p.value == "true")
					end
					value[p.name] = p
				end
				-- Finally, loop through the result and remove empty groups
				for name,val in pairs(retval) do
					if not val.value then
						retval[name] = nil
					end
				end
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			errtxt = err
		end
	end

	-- Save the device for later use
	local output = cfe({ type="group", value=retval, label="Provisioning Device Parameters", errtxt=errtxt })
	if device_id and device_id ~= "" and not editable then saved_device_params[device_id] = duplicatestructure(output) end
	return output
end

set_editable_device_params = function(params)
	return set_device_params(params, true)
end

set_device_params = function(params, editable)
	-- Validate the settings
	local success = validateparam(params)
	local errtxt
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			if not create then
				local sql = "SELECT * FROM provisioning_devices WHERE device_id='"..escape(params.value.device_id.value).."'"
				local tmp = getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					errtxt = "Device does not exist"
				end
			end
			if success then
				if not saved_device_params[params.value.device_id.value] then get_device_params(params.value.device_id.value) end

				local sql = "BEGIN TRANSACTION"
				runsqlcommand(sql)
				if not editable then
					-- Delete all values for this device (can't do this if only updating editable)
					sql = "DELETE FROM provisioning_values WHERE device_id='"..escape(params.value.device_id.value).."'"
					runsqlcommand(sql, true)
				end
				-- Loop through the groups and params
				for group,v in pairs(params.value) do
					if v.type == "group" then
						for name,param in pairs(v.value) do
							if editable then
								sql = "DELETE FROM provisioning_values WHERE device_id='"..escape(params.value.device_id.value).."' AND group_name='"..escape(group).."' AND param_id='"..escape(param.param_id).."'"
								runsqlcommand(sql, true)
							end
							if tostring(param.value) ~= param.default then
								sql = "INSERT INTO provisioning_values VALUES('"..escape(params.value.device_id.value).."', '"..escape(group).."', '"..escape(param.param_id).."', '"..escape(tostring(param.value)).."')"
								runsqlcommand(sql, true)
							end
						end
					end
				end

				sql = "COMMIT"
				runsqlcommand(sql)
				
				local tmp = saved_device_params[params.value.device_id.value]
				local p = params
				if not editable then
					saved_device_params[params.value.device_id.value] = params
				else
					p = get_device_params(params.value.device_id.value)
				end
				callscript(updatedeviceparamsscriptfile, p, tmp)
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			pcall(function() con:execute("ROLLBACK") end)
			success = false
			errtxt = err
		end
	end
	if not success then
		params.errtxt = errtxt or "Failed to save device parameters"
	end
	return params
end

get_device_values_by_name = function(name)
	local retval = {}
	retval.device_id = cfe({label="Device ID", seq=1})
	retval.name = cfe({value=name or "", label="Name", seq=2})
	retval.label = cfe({label="Label", seq=3})
	retval.template = cfe({type="select", label="Template", option={}, seq=4})
	retval.values = cfe({type="structure", value={}, label="Parameter Values", option={}, seq=5})
	local errtxt
	local res, err = pcall(function()
		local connected = databaseconnect()
		if name and name ~= "" then
			local sql = "SELECT d.device_id, d.name, d.label, t.filename AS template FROM provisioning_devices d JOIN provisioning_templates t USING(template_id) WHERE name='"..escape(name).."'"
			local tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				for n,v in pairs(tmp[1]) do
					if retval[n] then
						retval[n].value = v
					end
				end
				
				-- Next, get all of the parameter values for this device
				retval.values = get_device_values(tmp[1].device_id)
				retval.values.seq = 5
			else
				errtxt = "Invalid device name"
			end
			if connected then databasedisconnect() end
		else
			errtxt = "Invalid device name"
		end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="group", value=retval, label="Provisioning Device Parameter Values", errtxt=errtxt })
end

get_device_values = function(device_id)
	local retval = {}
	local errtxt
	if device_id and device_id ~= "" then
		local res, err = pcall(function()
			local connected = databaseconnect()
			local sql = "SELECT g.name AS group, p.name, p.type, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS value "..
				"FROM (devices_to_classes d2t JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) JOIN provisioning_groups g USING(group_id) "..
					"JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id)) LEFT JOIN provisioning_values v ON(d2t.device_id=v.device_id AND p.param_id=v.param_id AND g.name=v.group_name ) "..
				"WHERE d2t.device_id='"..escape(device_id).."'"
			local tmp = getselectresponse(sql)
			-- Loop through the params and put them into the groups
			for i,p in ipairs(tmp) do
				if p.type == "boolean" then
					p.value = (p.value == "true")
				end
				if not retval[p.group] then
					retval[p.group] = {}
				end
				retval[p.group][p.name] = p.value
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			errtxt = err
		end
	else
		errtxt = "Invalid device id"
	end

	return cfe({type="structure", value=retval, label="Parameter Values", errtxt=errtxt})
end

search_device_values = function(parameter_id, parameter_value, comparison)
	local errtxt
	retval = {}
	retval.id = cfe({type="select", value=parameter_id or "name", label="Parameter", option={"name", "label", "template_id", "template"}, seq=1})
	retval.comparison = cfe({type="select", value=comparison or "=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!*~"}, seq=2})
	retval.value = cfe({label="Parameter Value", value=parameter_value, descr="Parameter value or SQL regular expression", seq=3})
	retval.result = cfe({type="structure", value={}, label="List of Devices", seq=4 })
        local res, err = pcall(function()
		local connected = databaseconnect()
		-- Get the group/parameter options
		local sql = "SELECT g.name AS group_name, p.name AS param_name FROM provisioning_groups g JOIN param_groups_to_params USING(group_id) JOIN provisioning_params p USING(param_id) GROUP BY g.name, p.name ORDER BY g.name ASC, p.name ASC"
		local tmp = getselectresponse(sql)
		local blankopt = {}
		local blankexists = {}
		for i,v in ipairs(tmp) do
			retval.id.option[#retval.id.option + 1] = v.group_name.."."..v.param_name
			if not blankexists[v.param_name] then
				blankopt[#blankopt+1] = "."..v.param_name
				blankexists[v.param_name] = true
			end
		end
		table.sort(blankopt)
		for i,o in ipairs(blankopt) do
			retval.id.option[#retval.id.option + 1] = o
		end
		-- Get the devices from the DB
		if parameter_id and modelfunctions.validateselect(retval.id) and modelfunctions.validateselect(retval.comparison) then
			parameter_value = parameter_value or ""
			sql = "SELECT d.device_id, d.name, d.label, d.template_id, "
			local group, param = string.match(parameter_id or "", "([^%.]*)%.(.*)")
			if not group then
				sql = sql.."'"..escape(parameter_id).."' AS param, "
				if parameter_id == "name" or parameter_id=="label" then
					sql = sql.."d."..escape(parameter_id).." AS value FROM provisioning_devices d WHERE d."..escape(parameter_id)
				elseif parameter_id=="template_id" then
					sql = sql.."d.template_id AS value FROM provisioning_devices d WHERE CAST(d.template_id AS text)"
				elseif parameter_id=="template" then
					-- Search by template label
					sql = sql.."t.label AS value FROM provisioning_devices d JOIN provisioning_templates t USING(template_id) WHERE t.label"
				end
				sql = sql..retval.comparison.value.."'"..escape(parameter_value).."'"
			else
				sql = sql.."g.name as group, p.name as param, CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END as value FROM "..
					"provisioning_devices d JOIN devices_to_classes d2t USING(device_id) JOIN provisioning_classes t USING(class_id) JOIN classes_to_param_groups t2g USING(class_id) "..
					"JOIN provisioning_groups g USING(group_id) JOIN param_groups_to_params g2p USING(group_id) JOIN provisioning_params p USING(param_id) "..
					"LEFT JOIN provisioning_values v ON(d2t.device_id=v.device_id AND p.param_id=v.param_id AND g.name=v.group_name ) "
				if group and group ~= "" then
					sql = sql.."WHERE g.name='"..escape(group).."' AND"
				else
					sql = sql.."WHERE"
				end
				sql = sql.." p.name='"..escape(param).."' AND CASE WHEN v.value IS NOT NULL THEN v.value WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END"..retval.comparison.value.."'"..escape(parameter_value).."'"
			end
			sql = sql.." ORDER BY d.name ASC, d.label ASC"
			retval.result.value = getselectresponse(sql)
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end
	return cfe({type="group", value=retval, label="Device Search", errtxt=errtxt})
end

get_param_options = function(param_id)
	local retval = {}
	retval.param_id = cfe({value=param_id or "", label="Parameter ID", seq=0})
	retval.name = cfe({label="Name", seq=1})
	retval.label = cfe({label="Label", seq=2})
	retval.options = cfe({type="list", value={}, label="Value Options", descr="Ordered list of options where each option is made up of 'value' or 'value,label'", seq=3})
	local errtxt = "Cannot find parameter"
	if param_id and param_id ~= "" then
		local res, err = pcall(function()
			local connected = databaseconnect()
			-- First, just check to see if param_id exists
			local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(param_id).."'"
			local tmp = getselectresponse(sql)
			if tmp and #tmp > 0 then
				errtxt = nil
				retval.name.value = tmp[1].name
				retval.label.value = tmp[1].label
				-- Next, get all of the param options
				sql = "SELECT * FROM provisioning_options WHERE param_id='"..escape(param_id).."' ORDER BY seq ASC"
				local tmp = getselectresponse(sql) or {}
				for i,t in ipairs(tmp) do
					retval.options.value[#retval.options.value + 1] = t.value..","..t.label
				end
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			errtxt = err
		end
	end
	return cfe({ type="group", value=retval, label="Provisioning Parameter Options", errtxt=errtxt })
end

set_param_options = function(options)
	local success = true
	local errtxt
	-- Validate the settings
--FIXME
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			local sql = "SELECT * FROM provisioning_params WHERE param_id='"..escape(options.value.param_id.value).."'"
			local tmp = getselectresponse(sql)
			if not tmp or #tmp == 0 then
				success = false
				errtxt = "Parameter does not exist"
			end
			if success then
				local sql = "BEGIN TRANSACTION"
				runsqlcommand(sql)
				-- Delete all options for this device
				sql = "DELETE FROM provisioning_options WHERE param_id='"..escape(options.value.param_id.value).."'"
				runsqlcommand(sql, true)
				-- Loop through the options
				for i,o in ipairs(options.value.options.value) do
					local v,l = string.match(o, "^%s*([^,]+),%s*(.*%S)%s*$")
					if v then
						v = string.match(v, "^(.*%S)%s*$")
					else
						v = string.match(o, "^%s*(.*%S)%s*$")
						l = v
					end
					sql = "INSERT INTO provisioning_options VALUES('"..escape(options.value.param_id.value).."', '"..escape(l).."', '"..escape(v).."', '"..i.."')"
					runsqlcommand(sql, true)
				end
				sql = "COMMIT"
				runsqlcommand(sql)
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			pcall(function() con:execute("ROLLBACK") end)
			success = false
			errtxt = err
		end
	end
	if not success then
		options.errtxt = errtxt or "Failed to save options"
	end
	return options
end

function get_filedetails(filename)
	return modelfunctions.getfiledetails(filename, {updatedevicescriptfile, updatedeviceparamsscriptfile, deletedevicescriptfile})
end

function update_filedetails(filedetails)
	return modelfunctions.setfiledetails(filedetails, {updatedevicescriptfile, updatedeviceparamsscriptfile, deletedevicescriptfile}, validatefiledetails)
end

function list_files()
	local retval = {}
	for i,file in ipairs({updatedevicescriptfile, updatedeviceparamsscriptfile, deletedevicescriptfile}) do
		local details = fs.stat(file) or {}
		details.filename = file
		table.insert(retval, details)
	end
	table.sort(retval, function(a,b) return a.filename < b.filename end)
	return cfe({ type="structure", value=retval, label="List of Provisioning Script files" })
end