path: root/did-model.lua

module(..., package.seeall)

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

-- NOTE -- This is the SQL statement that should be run by the VoIP server to figure out the current extension for a DID
-- "SELECT extension FROM pubdid WHERE did='$1' AND 'now'>=starttime AND 'now'<endtime ORDER BY stale LIMIT 1"
-- To update pubdid each night at midnight, use cron and the didpublish script

local DatabaseName = "did"
local DatabaseOwner = "didowner"
local DatabaseUser = "diduser"

local configfile = "/etc/did.conf"

local path = "PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin "
local env
local con

local database_creation_script = {
	"CREATE AGGREGATE array_accum(anyelement) (SFUNC = array_append, STYPE = anyarray, INITCOND = '{}')",
	"CREATE TABLE dbhistlog (logdatetime timestamp(3) without time zone NOT NULL, msgtext text, userid text)",
	"CREATE TABLE definition (did character varying(40) NOT NULL, identification character varying(7), department character varying(40), description character varying(255))",
	"CREATE TABLE pubdid (did character varying(40), extension character varying(40), starttime time without time zone, endtime time without time zone, stale boolean)",
	"CREATE TABLE rule (did character varying(40) NOT NULL, extension character varying(40) NOT NULL, starttime time without time zone, endtime time without time zone, startdate date, enddate date, dayofweek bit(7))",
	"ALTER TABLE ONLY definition ADD CONSTRAINT definition_pkey PRIMARY KEY (did)",
	"CREATE INDEX ruledididx ON rule USING btree (did)",
	"CREATE INDEX ruledidextensionidx ON rule USING btree (did, extension)",
	"CREATE INDEX ruledidtimesdowidx ON rule USING btree (did, starttime, endtime, dayofweek)",
	"CREATE INDEX pubdiddidtimesidx ON pubdid USING btree (did, starttime, endtime)",
	"GRANT SELECT, INSERT ON dbhistlog TO "..DatabaseUser,
	"GRANT SELECT, UPDATE ON definition TO "..DatabaseUser,
	"GRANT SELECT, UPDATE, INSERT, DELETE ON rule TO "..DatabaseUser,
}

-- ################################################################################
-- DATABASE FUNCTIONS

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 ""
	return string.gsub(sql, "'", "''")
end

-- List the postgres databases on this system
local listdatabases = function()
	local dbs = {}
	local cmd = path.."psql -U postgres -tl 2>&1"
	local f = io.popen(cmd)
	local result = f:read("*a") or ""
	f:close()
	for line in string.gmatch(result, "[^\n]+") do
		dbs[#dbs+1] = string.match(line, "^ (%S+)")
	end
	return dbs
end

-- Create the necessary database
local createdatabase = function(password)
	local result = {}

	-- First, create the users
	local cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseOwner..' WITH PASSWORD \''..password..'\'" 2>&1'
	local f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()
	cmd = path..'psql -U postgres -c "CREATE USER '..DatabaseUser..'" 2>&1'
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()
	
	-- Create the database
	cmd = path..'psql -U postgres -c "CREATE DATABASE '..DatabaseName..' WITH OWNER '..DatabaseOwner..'" 2>&1'
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()
	
	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()
	cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseUser..'" 2>&1'
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()
	cmd = path..'psql -U postgres -c "DROP ROLE '..DatabaseOwner..'" 2>&1'
	f = io.popen(cmd)
	table.insert(result, f:read("*a"))
	f:close()
	
	return table.concat(result, "\n")
end

-- Run an SQL script
local runSQLscript = function(filename)
	-- Create the database
	local cmd = path..'psql -U postgres -f "'..filename..'" '..DatabaseName..' 2>&1'
	local f = io.popen(cmd)
	local result = f:read("*a") or ""
	f:close()
	-- Create the tables
	print (result)
	return result
end

-- Create the database and tables
-- pg_dump -U postgres -c did > did.postgres
--runSQLscript("/path/did.postgres")

local databaseconnect = function(username, password)
	if not con then
		-- create environment object
		env = assert (luasql.postgres())
		-- connect to data source
		con = assert (env:connect(DatabaseName, username, password))
		return true
	end
end

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

local logme = function(message)
	local userid = "-"
	if APP and APP.sessiondata and APP.sessiondata.userinfo and APP.sessiondata.userinfo.userid then
		userid = APP.sessiondata.userinfo.userid
	end
	local sql = string.format("INSERT INTO dbhistlog VALUES ('%s', '%s', '%s')",
		os.date("%Y-%m-%d %H:%M:%S"), escape(message), userid)
	local res = assert (con:execute(sql))
end

local listhistorylogentries = function()
	local entries = {}
	cur = assert (con:execute"SELECT logdatetime, msgtext, userid from dbhistlog ORDER BY logdatetime DESC")
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {logdatetime = row.logdatetime, msgtext = row.msgtext, userid = row.userid}
		row = cur:fetch (row, "a")
	end
	cur:close()
	return entries
end

-- Delete history log information from more than a month ago
local groomdbhistlog = function()
	local res = assert (con:execute("delete from dbhistlog where " ..
		"logdatetime < (now() - INTERVAL '1 month')"))
	logme("removed " .. res .. " old dbhistlog lines")
end

local generatewhereclause = function(did, extension, identification, description, department, clause)
	local sql = ""
	local where = {}
	-- We're going to use regular expressions so can search for substrings
	if did and did ~= "" then
		where[#where+1] = "definition.did ~ '.*"..escape(did)..".*'"
	end
	if extension and extension ~= "" then
		where[#where+1] = "extension ~ '.*"..escape(extension)..".*'"
	end
	if identification and identification ~= "" then
		where[#where+1] = "identification ~ '.*"..escape(identification)..".*'"
	end
	-- For these two, specify case insensitive
	if description and description ~= "" then
		where[#where+1] = "description ~* '.*"..escape(description)..".*'"
	end
	if department and department ~= "" then
		where[#where+1] = "department ~* '.*"..escape(department)..".*'"
	end
	if #where > 0 then
		sql = " " .. (clause or "WHERE") .. " " .. table.concat(where, " AND ")
	end
	return sql
end

local getdefinitionentries = function(sql)
	local entries = {}
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {did=row.did, identification=row.identification, department=row.department, description=row.description, extension=row.extension}
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local listunuseddefinitions = function(did, identification, description, department)
	local where = generatewhereclause(did, nil, identification, description, department, "AND")
	local sql = "SELECT * FROM definition WHERE did NOT IN (SELECT did FROM rule)"..where.." ORDER BY did"
	return getdefinitionentries(sql)
end

-- Lists only the definitions that have rules, this also allows us to select based upon extension
local listuseddefinitions = function(did, extension, identification, description, department)
	local where = string.gsub(generatewhereclause(did, extension, identification, description, department, "HAVING"), "extension", "array_to_string(array_accum(rule.extension), ', ')")
	-- Combine with rules to get extensions, this will drop all dids that don't have rules
	-- Relies on this custom aggregate function being defined
	-- local sql = "CREATE AGGREGATE array_accum(anyelement)(sfunc = array_append, stype = anyarray, initcond = '{}')"
	local sql = "SELECT definition.did, identification, department, description, array_to_string(array_accum(rule.extension), ', ') AS extension FROM definition,rule WHERE definition.did=rule.did"
	sql = sql.." GROUP BY definition.did, identification, department, description"..where.." ORDER BY definition.did"
	return getdefinitionentries(sql)
end

local listdefinitions = function(did, identification, description, department)
	local sql = "SELECT * FROM definition"..generatewhereclause(did, nil, identification, description, department).." ORDER BY did"
	return getdefinitionentries(sql)
end

local listdefs = function(did, identification, description, department)
	local sql = "SELECT did FROM definition"..generatewhereclause(did, nil, identification, description, department).." ORDER BY did"
	local entries = {}
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = row.did
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local listexchanges = function()
	local entries = {}
	local sql = "SELECT substring(did from 1 for 6) AS exchange FROM definition GROUP BY exchange ORDER BY exchange"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = row.exchange
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local findunuseddefinition = function(exchange)
	local entries = {}
	local sql = "SELECT did FROM definition WHERE did NOT IN (SELECT did FROM rule) AND substring(did from 1 for 6)='"..exchange.."' AND identification='' AND department=''"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = row.did
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local updatedefinitionentry = function(definition)
	local sql = string.format("UPDATE definition SET identification='%s', department='%s', description='%s' WHERE did='%s'",
		escape(definition.identification), escape(definition.department),
		escape(definition.description), escape(definition.did))
	local res = assert (con:execute(sql))
--	logme("Updated DID "..definition.did)
	return res
end

local insertdefinitionentry = function(definition)
	local sql = string.format("INSERT INTO definition (did, identification, department, description) VALUES ('%s', '%s', '%s', '%s')",
		escape(definition.did), escape(definition.identification),
		escape(definition.department), escape(definition.description))
	local res = assert (con:execute(sql))
	logme("Inserted DID "..definition.did)
	return res
end

local deletedefinitionentry = function(did)
	local sql = string.format("DELETE FROM definition WHERE did='%s'", escape(did))
	local res = assert (con:execute(sql))
	logme("Deleted DID "..did)
	return res
end

local listrules = function(did, date, dayofweek)
	local entries = {}
	-- retrieve a cursor
	local sql = "SELECT * from rule"
	local where = {}
	if did then
		where[#where+1] = "did='" .. did .. "'"
	end
	if date then
		where[#where+1] = "(startdate IS NULL OR startdate <= '"..date.."') AND (enddate IS NULL OR enddate >= '"..date.."')"
	end
	if dayofweek then
		where[#where+1] = "(dayofweek = '0000000' OR SUBSTRING(dayofweek FROM "..dayofweek.." FOR 1) = '1')"
	end
	if #where > 0 then
		sql = sql .. " WHERE " .. table.concat(where, " AND ")
	end
	-- This ordering controls which rule overrides another, highest priority will be first
	sql = sql .. " ORDER BY did, startdate ASC NULLS FIRST, enddate DESC NULLS FIRST, dayofweek ASC, starttime ASC NULLS FIRST, endtime DESC NULLS FIRST"
	cur = assert (con:execute(sql))
	row = cur:fetch ({}, "a")
	while row do
		entries[#entries+1] = {did=row.did, extension=row.extension, starttime=row.starttime, endtime=row.endtime, startdate=row.startdate, enddate=row.enddate, dayofweek=row.dayofweek}
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	return entries
end

local updaterules = function(did, rules)
	-- delete all rules for this did, and add in new ones
	local sql = string.format("DELETE FROM rule WHERE did='%s'", escape(did))
	local res = assert (con:execute(sql))
	for i,rule in ipairs(rules) do
		sql = {}
		sql[1] = "INSERT INTO rule ("
		sql[3] = ") VALUES ("
		sql[5] = ")"
		names = {}
		vals = {}
		for name,val in pairs(rule) do
			if val and val ~= "" then
				names[#names+1] = escape(name)
				vals[#vals+1] = "'"..escape(val).."'"
			end
		end
		sql[2] = table.concat(names, ", ")
		sql[4] = table.concat(vals, ", ")
		sql = table.concat(sql, "")
		res = assert (con:execute(sql))
	end
	return res
end

-- Put the given rules into pubdid
local publishrules = function(did, rules)
	-- mark all rules for this did as stale, add in new ones, and delete the stale ones
	local sql = string.format("UPDATE pubdid SET stale=TRUE WHERE did='%s'", escape(did))
	local res = assert (con:execute(sql))
	for i,rule in ipairs(rules) do
		sql = string.format("INSERT INTO pubdid VALUES ('%s', '%s', '%s', '%s', FALSE)", escape(rule.did),
			escape(rule.extension), escape(rule.starttime), escape(rule.endtime))
		res = assert (con:execute(sql))
	end
	sql = string.format("DELETE FROM pubdid WHERE did='%s' AND stale=TRUE", escape(did))
	res = assert (con:execute(sql))
	--logme("Published DID "..did)
	return res
end

local testdatabaseentry = function(datatype, value)
	local success = true
	local errtxt
	local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." DEFAULT '"..escape(value).."' ) ON COMMIT DROP"
	local res, err = pcall(function()
		assert (con:execute(sql))
	end)
	if not res then
		success = false
		errtxt = string.gsub(err or "", "\n.*", "")
	end
	return success, errtxt
end

local convertdatabaseentry = function(datatype, value)
	local success = true
	local errtxt
	local result = value
	local res, err = pcall(function()
		local sql = "CREATE TEMP TABLE testing ( test "..escape(datatype).." )"
		assert (con:execute(sql))
		sql = "INSERT INTO testing VALUES ('"..value.."')"
		assert (con:execute(sql))
		sql = "SELECT * FROM testing"
		local cur = assert (con:execute(sql))
		local row = cur:fetch ({}, "a")
		if row then
			result = row.test
		end
	end)
	if not res then
		success = false
		errtxt = string.gsub(err or "", "\n.*", "")
	end
	local res, err = pcall(function()
		local sql = "DROP TABLE testing"
		assert (con:execute(sql))
	end)
	return success, errtxt, result
end

local printtableentries = function(tablename)
	-- retrieve a cursor
	local count = 0
	cur = assert (con:execute("SELECT * from "..tablename))
	-- print all rows, the rows will be indexed by field names
	row = cur:fetch ({}, "a")
	while row do
		count = count + 1
		for name,val in pairs(row) do
			APP.logevent(name.." = "..val..", ")
		end
		row = cur:fetch (row, "a")
	end
	-- close everything
	cur:close()
	APP.logevent("Table "..tablename.." contains "..count.." rows")
end

local function insertdailyentry(rule, daily)
	rule.starttime = rule.starttime or "00:00:00"
	rule.endtime = rule.endtime or "24:00:00"
	-- find the spot for this entry
	local loc = #daily + 1 -- default to put at end
	for i,ent in ipairs(daily) do
		if ent.starttime >= rule.starttime then
			loc = i
			break
		end
	end
	-- Adjust previous entry
	if loc > 1 then
		if daily[loc-1].endtime > rule.endtime then
			-- split the previous entry
			local temp = {}
			for name,val in pairs(daily[loc-1]) do temp[name]=val end
			table.insert(daily, loc, temp)
		end
		daily[loc-1].endtime = rule.starttime
	end
	-- Adjust the trailing entries
	while #daily >= loc do
		daily[loc].starttime = rule.endtime
		if daily[loc].endtime <= daily[loc].starttime then
			table.remove(daily, loc)
		else
			break
		end
	end
	table.insert(daily, loc, rule)
	return daily
end

-- time is a Lua time value (ie. result of os.time()
local function getdailyentry(did, time)
	time = time or os.time()
	local date = os.date("%Y-%m-%d", time)
	local dayofweek = os.date("%w", time)
	if dayofweek == "0" then dayofweek = "7" end
	-- get the rules for this did and date
	local rules = listrules(did, date, dayofweek)
	local daily = {}
	for i,rule in ipairs(rules) do
		insertdailyentry(rule, daily)
	end
	return daily
end

-- We're going to handle rules as a string, one rule per line, comma separated
-- Convert rules table to string
local function formatrules(rules)
	value = {}
	for i,rule in ipairs(rules) do
		local rulearray = {rule.extension or "", rule.starttime or "", rule.endtime or "", rule.startdate or "", rule.enddate or "", rule.dayofweek or ""}
		table.insert(value, table.concat(rulearray, ", "))
	end
	return table.concat(value, "\n")
end

-- Convert rules string to table
local function parserules(did, rules)
	local value = {}
	for line in string.gmatch(rules, "([^\n]+)") do
		local tabs = format.string_to_table(line, "%s*,%s*")
		if #tabs > 0 then
			value[#value+1] = {did=did, extension=tabs[1], starttime=tabs[2] or "", endtime=tabs[3] or "", startdate=tabs[4] or "", enddate=tabs[5] or "", dayofweek=tabs[6] or "0000000"}
		end
	end
	return value
end

local function validaterules(rules)
	-- Basically, we assume that any combination of rules is acceptable as long as each rule is valid
	local success = true
	local errtxt = {}
	local res, err = pcall(function()
		local connected = databaseconnect(DatabaseUser)
		for i,rule in ipairs(rules) do
			if not validator.is_integer(rule.did) then
				errtxt[#errtxt+1] = "Rule #"..i..": DID is not a valid number"
			end
			if rule.extension == "" or string.find(rule.extension, "[^%d#%*]") then
				errtxt[#errtxt+1] = "Rule #"..i..": Extension is not a valid number"
			end
			local res,err
			if rule.starttime ~= "" then
				res,err,rule.starttime = convertdatabaseentry("TIME", rule.starttime)
				if not res then errtxt[#errtxt+1] = "Rule #"..i..": StartTime "..err end
			end
			if rule.endtime ~= "" then
				res,err,rule.endtime = convertdatabaseentry("TIME", rule.endtime)
				if not res then errtxt[#errtxt+1] = "Rule #"..i..": EndTime "..err end
			end
			if rule.startdate ~= "" then
				res,err,rule.startdate = convertdatabaseentry("DATE", rule.startdate)
				if not res then errtxt[#errtxt+1] = "Rule #"..i..": StartDate "..err end
			end
			if rule.enddate ~= "" then
				res,err,rule.enddate = convertdatabaseentry("DATE", rule.enddate)
				if not res then errtxt[#errtxt+1] = "Rule #"..i..": EndDate "..err end
			end
			if #rule.dayofweek ~= 7 or string.find(rule.dayofweek, "[^01]") then
				errtxt[#errtxt+1] = "Rule #"..i..": DayOfWeek invalid entry"
			end
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt[#errtxt+1] = err
	end
	if #errtxt > 0 then
		success = false
		errtxt = table.concat(errtxt, "\n")
	else
		errtxt = nil
	end
	return success, errtxt
end

local validatedefinition = function(defin)
	local success = true
	if not validator.is_integer(defin.value.did.value) then
		defin.value.did.errtxt = "Must be a number"
		success = false
	end
	if defin.value.identification.value ~= "" and not validator.is_integer(defin.value.identification.value) then
		defin.value.identification.errtxt = "Invalid identification number"
		success = false
	end
	-- defin.value.department
	-- defin.value.description
	
	return success
end

local function createdefinitionlist(did, extension, identification, description, department)
	local retval = {}
	retval.definitions = cfe({ type="list", value={}, label="DID Number List" })
	retval.did = cfe({ value=did or "", label="DID search string" })
	retval.extension = cfe({ value=extension or "", label="Extension search string" })
	retval.identification = cfe({ value=identification or "", label="Identification search string" })
	retval.description = cfe({ value=description or "", label="Description search string" })
	retval.department = cfe({ value=department or "", label="Department search string" })
	return cfe({ type="group", value=retval, label="DID Number List" })
end

local function stripdash(did)
	return (string.gsub(did or "", "%-", ""))
end

local function adddash(did)
	if #did > 0 then
		return (did:sub(1,3) or "") .. "-" .. (did:sub(4,6) or "") .. "-" .. (did:sub(7) or "")
	else
		return did
	end
end

local describechange = function(olddef, newdef)
	local changes = {}
	olddef = olddef or {}
	for name,val in pairs(newdef) do
		if name ~= "did" and name ~= "rules" and val ~= (olddef[name] or "") then
			changes[#changes+1] = name.." from '"..(olddef[name] or "").."' to '"..val.."'"
		end
	end

	if #changes > 0 then
		return table.concat(changes, " ")
	else
		return nil
	end
end

local describeruleschange = function(oldrules, newrules)
	local changes = {}
	for i,rule in ipairs(newrules) do
		rule2 = oldrules[i] or {did=rule.did, dayofweek="0000000"}
		for name,val in pairs(rule) do
			if val ~= (rule2[name] or "") then
				changes[#changes+1] = "Rule "..i.." "..name.." from '"..(rule2[name] or "").."' to '"..val.."'"
			end
		end
	end
	for i=#newrules+1,#oldrules do
		local values = {}
		for name,val in pairs(oldrules[i]) do
			if name ~= "did" and not (name == "dayofweek" and val == "0000000") then
				values[#values+1] = name.." = "..val
			end
		end
		changes[#changes+1] = "Rule "..i.." deleted ("..table.concat(values, ", ")..")"
	end

	if #changes > 0 then
		return table.concat(changes, " ")
	else
		return nil
	end
end

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

function getuseddefinitionlist(did, extension, identification, description, department)
	local def = createdefinitionlist(stripdash(did), extension, identification, description, department)
	def.label = "Used "..def.label
	local res, err = pcall(function()
		local connected = databaseconnect(DatabaseUser)
		def.value.definitions.value = listuseddefinitions(stripdash(did), extension, identification, description, department)
		if connected then databasedisconnect() end
	end)
	if not res then
		def.errtxt = err
	end
	for i,d in ipairs(def.value.definitions.value) do
		d.did = adddash(d.did)
	end
	return def
end

function getunuseddefinitionlist(did, identification, description, department)
	local def = createdefinitionlist(stripdash(did), nil, identification, description, department)
	def.value.extension = nil
	def.label = "Unused "..def.label
	local res, err = pcall(function()
		local connected = databaseconnect(DatabaseUser)
		def.value.definitions.value = listunuseddefinitions(stripdash(did), identification, description, department)
		if connected then databasedisconnect() end
	end)
	if not res then
		def.errtxt = err
	end
	for i,d in ipairs(def.value.definitions.value) do
		d.did = adddash(d.did)
	end
	return def
end

function getdefinitionlist(did, extension, identification, description, department)
	local def = createdefinitionlist(stripdash(did), extension, identification, description, department)
	--def.value.extension = nil
	local res, err = pcall(function()
		local connected = databaseconnect(DatabaseUser)
		if def.value.extension.value == "" then
			def.value.definitions.value = listdefinitions(did, identification, description, department)
		else
			def.value.definitions.value = listuseddefinitions(stripdash(did), extension, identification, description, department)
--[[			local tmp = listunuseddefinitions(stripdash(did), identification, description, department)
			for i,val in ipairs(tmp) do
				val.extension = ""
				table.insert(def.value.definitions.value, val)
			end
			table.sort(def.value.definitions.value, function (a,b) return (a.did < b.did) end )
--]]	
		end
		if connected then databasedisconnect() end
	end)
	if not res then
		def.errtxt = err
	end
	for i,d in ipairs(def.value.definitions.value) do
		d.did = adddash(d.did)
	end
	return def
end

function searchdefinitions(did)
	local result = {}
	local res, err = pcall(function()
		local connected = databaseconnect(DatabaseUser)
		result = listdefs(did)
		if connected then databasedisconnect() end
	end)
	if not res then
		def.errtxt = err
	end
	return cfe({ type="list", value=result, label="DID list" })
end

function getdefinition(did)
	local errtxt
	local group = {}
	group.did = cfe({ value=stripdash(did) or "", label="DID" })
	group.identification = cfe({ label="Identification Number" })
	group.department = cfe({ label="Department" })
	group.description = cfe({ label="Description" })
	group.rules = cfe({ type="longtext", label="Rules", descr="One entry (extension, starttime, endtime, startdate, enddate, dayofweek) per line"})
	if did then
		group.did.errtxt = "DID does not exist"
		local res, err = pcall(function()
			local connected = databaseconnect(DatabaseUser)
			local definition = listdefinitions(stripdash(did))
			local rules = listrules(stripdash(did))
			if connected then databasedisconnect() end
			if #definition == 1 then
				group.did.errtxt = nil
				for name,val in pairs(definition[1]) do
					if group[name] then
						group[name].value = val
					end
				end
			end
			if #rules > 0 then
				group.rules.value = formatrules(rules)
			end
		end)
		if not res then
			errtxt = err
		end
	end
	group.did.value = adddash(group.did.value)

	return cfe({ type="group", value=group, label="DID Description", errtxt=errtxt })
end

-- If exists true, then make sure exists first, if false or undefined, make sure doesn't exist
function savedefinition(defin, exists)
	-- remove blank entries, if present
	defin.value.rules.value = string.gsub("\n"..format.dostounix(defin.value.rules.value), "\n%s*,%s*,%s*,%s*,%s*,%s*0000000", "")
	defin.value.rules.value = string.gsub(defin.value.rules.value, "$\n", "")
	
	defin.value.did.value = stripdash(defin.value.did.value)
	local rules = parserules(defin.value.did.value, defin.value.rules.value)
	-- note that validaterules might change the rules to standard formatting
	local success, errtxt = validaterules(rules)
	defin.value.rules.value = formatrules(rules)
	defin.value.rules.errtxt = errtxt
	success = validatedefinition(defin) and success
	defin.errtxt = "Failed to save definition"
	if success then
		local definition = {}
		for name,val in pairs(defin.value) do
			definition[name] = val.value
		end

		local res, err = pcall(function()
			local connected
			if not exists then
				databasedisconnect()
				local pw = format.parse_ini_file(fs.read_file(configfile) or "", "", "password") or ""
				connected = databaseconnect(DatabaseOwner, pw)
			else
				connected = databaseconnect(DatabaseUser)
			end
			local def = listdefinitions(definition.did)
			if #def > 0 and not exists then
				defin.value.did.errtxt = "DID Number already exists"
			elseif #def == 0 and exists then
				defin.value.did.errtxt = "DID Number does not exist"
			else
				local descr = {}
				descr[#descr+1] = describechange(def[1], definition)
				if exists then
					defin.descr = "Updated DID "..definition.did.." "
					updatedefinitionentry(definition)
				else
					defin.descr = "Created DID "..definition.did.." "
					insertdefinitionentry(definition)
				end
				local oldrules = listrules(definition.did)
				updaterules(defin.value.did.value, rules)
				descr[#descr+1] = describeruleschange(oldrules, listrules(definition.did))
				if #descr > 0 then
					defin.descr = defin.descr..table.concat(descr, " ")
					logme(defin.descr)
				elseif exists then
					defin.descr = defin.descr.." no change"
				end
				defin.errtxt = nil
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			defin.descr = nil
			defin.errtxt = defin.errtxt .. "\n" .. err
		end
	end
	defin.value.did.value = adddash(defin.value.did.value)

	return defin
end

function updatedefinition(defin)
	return savedefinition(defin, true)
end

function deletedefinition(did)
	local result = cfe({ label="Delete DID Number Result", errtxt="DID Number does not exist" })
	did = stripdash(did)
	local res, err = pcall(function()
		databasedisconnect()
		local pw = format.parse_ini_file(fs.read_file(configfile) or "", "", "password") or ""
		databaseconnect(DatabaseOwner, pw)
		local def = listdefs(did)
		if #def == 1 then
			deletedefinitionentry(did)
			result.value = "DID Number Deleted"
			result.errtxt = nil
		end
		databasedisconnect()
	end)
	if not res then
		result.errtxt = err
	end
	return result
end

function getunuseddefinition()
	local errtxt
	local retval = {}
	retval.exchange = cfe({ type="select", label="Exchange", option={} })
	local res, err = pcall(function()
		local connected = databaseconnect(DatabaseUser)
		local exchanges = listexchanges()
		if #exchanges == 0 then
			retval.exchange.errtxt = "No exchanges available"
		else
			for i,val in ipairs(exchanges) do
				table.insert(retval.exchange.option, (string.gsub(val, "^(...)", "(%1) ")))
			end
			retval.exchange.value = retval.exchange.option[1]
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end
	return cfe({ type="group", value=retval, label="Create New DID Number" })
end

function setunuseddefinition(defin)
	local success = modelfunctions.validateselect(defin.value.exchange)
	if success then
		local res, err = pcall(function()
			local connected = databaseconnect(DatabaseUser)
			local did = findunuseddefinition(string.gsub(defin.value.exchange.value, "[^%d]", ""))
			if #did == 0 then
				success = false
				defin.value.exchange.errtxt = "There are no unused extensions for that exchange"
			else
				defin.value.did = cfe({ value=adddash(did[1]), label="DID" })
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			defin.errtxt = err
		end
	end
	if not success then
		defin.errtxt = "Failed to create new definition"
	end
	return defin
end

function publishdefinition(did)
	local result = ""
	local errtxt = "Invalid DID"
	did = stripdash(did)
	local pw = format.parse_ini_file(fs.read_file(configfile) or "", "", "password") or ""
	local res, err = pcall(function()
		databasedisconnect()
		databaseconnect(DatabaseOwner, pw)
		local dids = listdefs()
		for i,d in ipairs(dids) do
			if d == did then
				local rules = getdailyentry(did)
				publishrules(did, rules)
				errtxt = nil
				result = "Published DID rules"
				logme("Published DID "..did)
				break
			end
		end
		databasedisconnect()
	end)
	if not res and err then
		errtxt = err
	end
	return cfe({ value=result, errtxt=errtxt, label="Result of Publish" })
end

function publishalldefinitions()
	local result = ""
	local errtxt
	local pw = format.parse_ini_file(fs.read_file(configfile) or "", "", "password") or ""
	local res, err = pcall(function()
		databasedisconnect()
		databaseconnect(DatabaseOwner, pw)
		local dids = listdefs()
		local time = os.time()
		for i,did in ipairs(dids) do
			local rules = getdailyentry(did, time)
			publishrules(did, rules)
		end
		result = "Published "..#dids.." DID rules"
		logme("Publishing "..#dids.." DIDs took "..os.time()-time.." seconds")
		groomdbhistlog()
		databasedisconnect()
	end)
	if not res and err then
		errtxt = err
	end
	return cfe({ value=result or "", errtxt=errtxt, label="Result of Publish" })
end

function testdatabase()
	local retval = cfe({ type="boolean", value=false, label="Database present" })
	local dbs = listdatabases()
	for i,db in ipairs(dbs) do
		if db == DatabaseName then
			retval.value = true
			break
		end
	end
	return retval
end

function getnewdatabase()
	local database = {}
	local errtxt
	database.password = cfe({ label="Password" })
	database.password_confirm = cfe({ label="Password (confirm)" })
	local test = testdatabase()
	if test.value then
		errtxt = "Database already exists!"
		success = false
	end
	return cfe({ type="group", value=database, label="Create Database", errtxt=errtxt })
end

function create_database(database)
	local success = true
	local errtxt

	if database.value.password.value == "" or string.match(database.value.password.value, "'%s") then
		database.value.password.errtxt = "Invalid password"
		success = false
	end
	if database.value.password.value ~= database.value.password_confirm.value then
		database.value.password_confirm.errtxt = "Password does not match"
		success = false
	end
	local test = testdatabase()
	if test.value then
		errtxt = "Database already exists!"
		success = false
	end

	if success then
		errtxt = createdatabase(database.value.password.value)
		test = testdatabase()
		if not test.value then
			success = false
		else
			local res, err = pcall(function()
				databasedisconnect()
				databaseconnect(DatabaseOwner, database.value.password.value)
				for i,scr in ipairs(database_creation_script) do
					assert (con:execute(scr))
				end
				databasedisconnect()
				-- put the password in the config file for future use
				local configcontent = format.update_ini_file(fs.read_file(configfile) or "", "", "password", database.value.password.value)
				fs.write_file(configfile, configcontent)
			end)
			if not res then
				errtxt = err
				success = false
			end
		end
		if not success then
			deletedatabase()
		end
	end
	
	if not success then
		database.errtxt = "Failed to create database"
		if errtxt then
			database.errtxt = database.errtxt.."\n"..errtxt
		end
	end

	return database
end

function getactivitylog()
	local retval = cfe({ type="list", value={}, label="DID Activity Log" })
	local res, err = pcall(function()
		local connected = databaseconnect(DatabaseUser)
		retval.value = listhistorylogentries() or {}
		if connected then databasedisconnect() end
	end)
	if not res then
		retval.errtxt = err
	end

	return retval
end