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/"
local env
local con

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)",
	},
	-- Multi-to-multi mapping of devices to classes
-- Need to enforce that devices do not contain more than one class with same name (using triggers)
	devices_to_classes = {
		"CREATE TABLE devices_to_classes (device_id INTEGER, class_id INTEGER)",
	},
	-- List of different device classes
	provisioning_classes = {
		"CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE)",
	},
	-- Multi-to-multi mapping of classes to parameter groups
-- Need to enforce that classes do not contain more than one parameter group with same name (using triggers)
	classes_to_param_groups = {
		"CREATE TABLE classes_to_param_groups (class_id INTEGER, group_id INTEGER)",
	},
	-- 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)",
	},
	-- Multi-to-multi mapping of parameter groups to parameters
	param_groups_to_params = {
		"CREATE TABLE param_groups_to_params (group_id INTEGER, param_id INTEGER, value VARCHAR(255), editable BOOLEAN)",
	},
	-- 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)",
-- Add a way to include validation code and/or options list
	},
	-- 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, group_name VARCHAR(255), param_id INTEGER, value VARCHAR(255))",
	},
	-- List of options for parameters
	provisioning_options = {
		"CREATE TABLE provisioning_options (param_id INTEGER, label VARCHAR(255), value VARCHAR(255), seq INTEGER)",
	},
-- Where do we define the triggers to enforce the foreign keys? Triggers are only on updates, so don't have to worry about read speed
-- Need to define indices
}

--[[
-- Get the values for a particular device for use
1) select * from provisioning_devices where name = 'devicename'
2) SELECT g.name AS group, p.name, 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 USING(device_id, group_id, param_id)
	WHERE d2t.device_id='yyyyy'

-- Get the values for a particular device for editing in ACF
1) Same as above
2) Same as above, but replace step 2 first line with:
SELECT g.name AS group, p.name, p.type, p.label, p.descr, 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, CASE WHEN g2p.value IS NOT NULL THEN g2p.value ELSE p.value END AS default, g2p.seq, g.label AS grouplabel
3) Get the options




-- Get the params for a class of device
a = select * from provisioning_types where name = '%1'
b = select * from provisioning_params c, a.params d where c.pid = d.pid order by d.order
(loop through looking for type="group" or option ~= null)
to get the options, it is:
select * from e.option order by order

-- Creating new param_table or option_table is just a matter of copying the schema (and indexes) of param_table or option_table
--]]




-- ################################################################################
-- 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()
	
	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

local runscript = function(script)
	for i,scr in ipairs(script) do
		logevent(scr)
		assert( con:execute(scr) )
	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
			if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_command")) end
			assert(res, err)
		end
	else
		if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_command")) end
	end
end

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
			if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_select")) end
			assert(res, err)
		end
	else
		if in_transaction then assert(con:execute("RELEASE SAVEPOINT before_select")) end
	end
	return retval
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 template_id 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
	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"
			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, template.value.filecontent.value)
			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_classes = function()
	local retval = {}
	local errtxt
	-- Get the classes from the DB
        local res, err = pcall(function()
		local connected = databaseconnect()
		local sql = "SELECT * FROM provisioning_classes ORDER BY class_id 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"})
	retval.name = cfe({label="Name"})
	retval.label = cfe({label="Label"})
	retval.groups = cfe({type="multi", value={}, label="Parameter Groups", option={}})
	local errtxt
	local res, err = pcall(function()
		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
				retval.groups.value[#retval.groups.value + 1] = g.group_id
			end
		end
		-- Finally, get the paramgroup options
		sql = "SELECT group_id, name, label FROM provisioning_groups ORDER BY label ASC"
		tmp = getselectresponse(sql)
		for i,g in ipairs(tmp) do
			retval.groups.option[#retval.groups.option + 1] = {value=g.group_id, label=g.label, name=g.name}
		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.validatemulti(class.value.groups)
	if class.value.name.value == "" then
		success = false
		class.value.name.errtxt = "Cannot be blank"
	end
	if class.value.label.value == "" then
		success = false
		class.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_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.name.value).."', '"..escape(class.value.label.value).."')"
					runsqlcommand(sql, true)
					sql = "SELECT class_id FROM provisioning_classes WHERE name='"..escape(class.value.name.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 (name, label) = ('"..escape(class.value.name.value).."', '"..escape(class.value.label.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 i,g in ipairs(class.value.groups.value) do
					sql = "INSERT INTO classes_to_param_groups VALUES('"..escape(class.value.class_id.value).."', '"..escape(g).."')"
					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
			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
		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 == "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"
	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_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
		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"}, seq=3})
	retval.label = cfe({label="Label", seq=4})
	retval.descr = cfe({label="Description", seq=5})
	retval.value = cfe({label="Default Value", seq=6})
	retval.seq = cfe({label="Sequence", seq=7})
-- 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).."')"
					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) = ('"..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).."') 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)
			result = "Parameter Deleted"
			sql = "COMMIT"
			runsqlcommand(sql)
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		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 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="multi", value={}, label="Classes", option={}, seq=5})
	local errtxt
	local res, err = pcall(function()
		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
				retval.classes.value[#retval.classes.value + 1] = g.class_id
			end
		end
		-- Next, get the template options
		sql = "SELECT template_id, label FROM provisioning_templates ORDER BY template_id 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 * FROM provisioning_classes ORDER BY class_id ASC"
		tmp = getselectresponse(sql)
		for i,c in ipairs(tmp) do
			retval.classes.option[#retval.classes.option + 1] = {value=c.class_id, label=c.label}
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end

	return cfe({ type="group", value=retval, label="Provisioning Device", errtxt=errtxt })
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)
	success = modelfunctions.validatemulti(device.value.classes) and success
	if device.value.name.value == "" then
		success = false
		device.value.name.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_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
				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 i,c in ipairs(device.value.classes.value) do
					sql = "INSERT INTO devices_to_classes VALUES('"..escape(device.value.device_id.value).."', '"..escape(c).."')"
					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
			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
			sql = "BEGIN TRANSACTION"
			runsqlcommand(sql)
			sql = "DELETE FROM provisioning_devices WHERE device_id='"..escape(device_id).."'"
			runsqlcommand(sql, true)
			sql = "DELETE FROM devices_to_classes WHERE device_id='"..escape(device_id).."'"
			runsqlcommand(sql, true)
			result = "Device Deleted"
			sql = "COMMIT"
			runsqlcommand(sql)
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		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].type="group"
					retval[g.name].value={}
				end
				-- Then, get all of the parameters for this device
				sql = "SELECT g.name AS group, p.param_id, p.name, p.type, p.label, p.descr, p.seq, 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 and put them into the groups
				for i,p in ipairs(tmp) do
					local value = retval[p.group].value
					if p.type == "boolean" then
						p.value = (p.value == "true")
					end
					value[#value+1] = p
				end
				-- Finally, loop through the result and remove empty groups
				for name,val in pairs(retval) do
					if #val.value == 0 then
						retval[name] = nil
					end
				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 Device Parameters", errtxt=errtxt })
end

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

set_device_params = function(params, editable)
	local success = true
	local errtxt
	-- Validate the settings
-- FIXME
	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
				local sql = "BEGIN TRANSACTION"
				runsqlcommand(sql)
				if not editable then
					-- Delete all values fro 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)
			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 = function(name)
	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 = 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 parameters for this device
				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(tmp[1].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.values.value[p.group] then
						retval.values.value[p.group] = {}
					end
					retval.values.value[p.group][p.name] = p.value
				end
			else
				errtxt = "Invalid device name"
			end
			if connected then databasedisconnect() end
		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