module(..., package.seeall)

-- Load libraries
require("posix")
require("modelfunctions")
require("fs")
require("format")
require("validator")

-- Set variables
local processname = "kamailio"
local packagename = "kamailio"
local baseurl = "/etc/kamailio"
local kamctlrc_file = "/etc/kamailio/kamctlrc"

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

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

local databaseconnect = function()
	if not con then
		-- parse the kamctlrc file
		local config = format.parse_ini_file(fs.read_file(kamctlrc_file), "") or {}
		if not config.DBENGINE then
			error("Database engine not specified, please setup one in the config script "..kamctlrc_file)
		end

		-- create environment object
		if config.DBENGINE == "MYSQL" or config.DBENGINE == "mysql" or config.DBENGINE == "MySQL" then
			error("MYSQL database not supported")
		elseif config.DBENGINE == "PGSQL" or config.DBENGINE == "pgsql" or config.DBENGINE == "postgres" or config.DBENGINE == "postgresql" or config.DBENGINE == "POSTGRESQL" then
			require("luasql.postgres")
			env = assert (luasql.postgres())
			DBENGINE = "PGSQL"
		elseif config.DBENGINE == "ORACLE" or config.DBENGINE == "oracle" or config.DBENGINE == "Oracle" then
			error("ORACLE database not supported")
		elseif config.DBENGINE == "DBTEXT" or config.DBENGINE == "dbtext" or config.DBENGINE == "textdb" then
			error("DBTEXT database not supported")
		elseif config.DBENGINE == "DB_BERKELEY" or config.DBENGINE == "db_berkeley" or config.DBENGINE == "BERKELEY" or config.DBENGINE == "berkeley" then
			error("BERKELEY database not supported")
		else
			error("Unknown database engine "..config.DBENGINE)
		end

		-- connect to data source
		con = assert(env:connect(config.DBNAME or "", config.DBRWUSER or "", config.DBRWPW or "", config.DBHOST, config.DBPORT))
		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 runsqlcommand = function(sql)
logevent(sql)
        assert(con:execute(sql))
end

local getselectresponse = function(sql)
	local retval = {}
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()
	return retval
end

local listtables = function()
	local result = {}
	if DBENGINE == "PGSQL" then
		local tab = getselectresponse("SELECT tablename FROM pg_tables WHERE tablename !~* 'pg_*' ORDER BY tablename ASC")
		for i,t in ipairs(tab) do
			result[#result+1] = t.tablename
		end
	else
		-- untested
		result = con:tables()
	end
	return result
end

local listcolumns = function(table)
	local result = {}
	if DBENGINE == "PGSQL" then
		local col = getselectresponse("SELECT a.attname AS field FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = '"..table.."' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum")
		for i,c in ipairs(col) do
			result[#result+1] = c.field
		end
	end
	return result
end

-- ################################################################################
-- LOCAL FUNCTIONS

local is_valid_filename = function(filename)
	local dirname = posix.dirname(filename)
	return validator.is_valid_filename(filename) and string.match(dirname, baseurl) and not string.match(dirname, "%.%.")
end

local function validate_user(user)
	local success = true
	if user.value.username.value == "" then
		user.value.username.errtxt = "Cannot be empty"
		success = false
	end
	if user.value.password.value == "" then
		user.value.password.errtxt = "Cannot be empty"
		success = false
	end
	if user.value.password.value ~= user.value.password_confirm.value then
		user.value.password_confirm.errtxt = "Must match password"
		success = false
	end
	return success, user
end

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

function startstop_service(action)
	return modelfunctions.startstop_service(processname, action)
end

function getstatus()
	return modelfunctions.getstatus(processname, packagename, "Kamailio Status")
end

function get_filedetails(filename)
	return modelfunctions.getfiledetails(filename, is_valid_filename)
end

function update_filedetails(filedetails)
	return modelfunctions.setfiledetails(filedetails, is_valid_filename)
end

function list_files()
	local retval = {}
	for file in fs.find(null, baseurl) do
		local details = fs.stat(file)
		if details.type == "regular" then
			details.filename = file
			table.insert(retval, details)
		end
	end
	table.sort(retval, function(a,b) return a.filename < b.filename end)
	return cfe({ type="structure", value=retval, label="List of Kamailio files" })
end

local function parse_db_show(table)
	local cmd = path .. "kamctl db show "..(table or "")
	local f = io.popen(cmd)
	-- These settings work for Postgres and DBTEXT database
	local delimiter = "\'?%s*[,|]%s*\'?"
	local results = {}
	local errtxt
	for line in f:lines() do
		if #results == 0 and string.match(line, "^ERROR:") then
			errtxt = line
			results = nil
			break
		end
		if string.match(line, "^[+-]+$") then
			results = {}
		else
			local words = format.string_to_table(line, delimiter)
			if words and #words > 0 then
				results[#results+1] = words
			end
		end
	end
	f:close()
	return results, errtxt
end

function list_users()
	-- Database format: id | username | domain | password | email_address | ha1 | ha1b | rpid
	local results = {}
	local r, errtxt
	r, errtxt = parse_db_show("subscriber")
	for i,words in ipairs(r or {}) do
		if #words > 1 then
			local temp = {username = words[2],
				--domain = words[3],
				password = words[4],
				--email_address = words[5]
			}
			results[#results+1] = temp
		end
	end
	table.sort(results, function(a,b) return a.username < b.username end)
	return cfe({type="list", value=results, label="Kamailio Users"})
end

function get_new_user()
	local user = {}
	user.username = cfe({label="User Name"})
	user.password = cfe({label="Password"})
	user.password_confirm = cfe({label="Password (confirm)"})
	--user.email_address = cfe({label="E-mail Address"})
	return cfe({type="group", value=user, label="Kamailio User"})
end

function create_new_user(user)
	local success = validate_user(user)
	if success then
		local cmd = path .. "kamctl add "..format.escapespecialcharacters(user.value.username.value).." "..format.escapespecialcharacters(user.value.password.value)
		--if user.value.email_address.value ~= "" then
		--	cmd = cmd.." "..format.escapespecialcharacters(user.value.email_address.value)
		--end
		local f = io.popen(cmd)
		user.descr = f:read("*a")
		f:close()
	else
		user.errtxt = "Failed to create new user"
	end

	return user
end

function delete_user(username)
	local cmd = path .. "kamctl rm "..format.escapespecialcharacters(username)
	local f = io.popen(cmd)
	local result = f:read("*a")
	f:close()
	return cfe({value=result, label="Delete User Result"})
end

function get_user(username)
	local user = get_new_user()
	user.value.username.value = username
	user.value.username.errtxt = "Invalid user"
	local users = list_users()
	for i,u in ipairs(users.value) do
		if u.username == username then
			user.value.username.errtxt = nil
			break
		end
	end
	return user
end

function update_user(user)
	local success = validate_user(user)
	if success then
		local cmd = path .. "kamctl passwd "..format.escapespecialcharacters(user.value.username.value).." "..format.escapespecialcharacters(user.value.password.value)
		local f = io.popen(cmd)
		user.descr = f:read("*a")
		f:close()
	else
		user.errtxt = "Failed to update user"
	end

	return user
end

function list_tables()
	local retval = {}
        local errtxt
	-- Get the devices from the DB
	local res, err = pcall(function()
		local connected = databaseconnect()
		retval = listtables()
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end
	
	return cfe({ type="list", value=retval, label="List of Database Tables", errtxt=errtxt })
end

function list_table_entries(table)
	local retval = {}
	retval.table = cfe({ value=table or "", label="Table" })
	retval.fields = cfe({ type="list", value={}, label="List of Table Fields" })
	retval.entries = cfe({ type="structure", value={}, label="List of Database Entries" })
        local errtxt
	-- Get the devices from the DB
	local res, err = pcall(function()
		local connected = databaseconnect()
		local tables = listtables()
		retval.table.errtxt = "Table does not exist"
		errtxt = "Table does not exist"
		for i,t in ipairs(tables) do
			if t == table then
				retval.table.errtxt = nil
				errtxt = nil
				retval.entries.value = getselectresponse("SELECT * FROM "..table.." ORDER BY id ASC") or {}
				retval.fields.value = listcolumns(table) or {}
			end
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end
	
	return cfe({ type="group", value=retval, label="Database Table Entries", errtxt=errtxt })
end

function get_table_entry(table, id)
	local retval = {}
	retval.table = cfe({ value=table or "", label="Table", errtxt="Table does not exist", seq=0 })
	local errtxt = "Table does not exist"
	if table and table ~= "" then
		local res, err = pcall(function()
			local connected = databaseconnect()
			local tables = listtables()
			for i,t in ipairs(tables) do
				if t == table then
					retval.table.errtxt = nil
					errtxt = nil
					break
				end
			end
			if not errtxt then
				local fields = listcolumns(table)
				for i,f in ipairs(fields) do
					retval[f] = cfe({ label=f, seq=i })
				end
				if id and id ~= "" then
					local entry = getselectresponse("SELECT * FROM "..table.." WHERE id='"..escape(id).."'")
					if entry and #entry > 0 then
						for n,v in pairs(entry[1]) do
							if retval[n] then retval[n].value = v end
						end
					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="Database Table Entry", errtxt=errtxt })
end

function create_table_entry(entry)
        return update_table_entry(entry, true)
end
	
function update_table_entry(entry, create)
	local success = true
	local errtxt
	-- Validate the settings
	-- relying on get_table_entry to do the validation of table
	if entry.value.table.value == "" or entry.value.table.errtxt then
		success = false
		entry.value.table.errtxt = "Table does not exist"
	end
	if not create then
		if not entry.value.id then
			success = false
		elseif not entry.value.id.value or entry.value.id.value == "" then
			success = false
			entry.value.id.errtxt = "Invalid id"
		end
	end
	if success then
        	local res, err = pcall(function()
			local connected = databaseconnect()
			local tables = listtables()
			success = false
			entry.value.table.errtxt = "Table does not exist"
			for i,t in ipairs(tables) do
				if t == entry.value.table.value then
					success = true
					entry.value.table.errtxt = nil
					break
				end
			end
			if success and not create then
				local sql = "SELECT * FROM "..entry.value.table.value.." WHERE id='"..escape(entry.value.id.value).."'"
				local tmp = getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					errtxt = "Entry does not exist"
				end
			end
			if success then
				local names = {}
				local values = {}
				for n,v in pairs(entry.value) do
					if n ~= "table" and n ~= "id" then
						names[#names+1] = n
						values[#values+1] = escape(v.value)
					end
				end
				if create then
					sql = "INSERT INTO "..entry.value.table.value.." ("..table.concat(names, ", ")..") VALUES('"..table.concat(values, "', '").."')"
				else
					sql = "UPDATE "..entry.value.table.value.." SET ("..table.concat(names, ", ")..") = ('"..table.concat(values, "', '").."') WHERE id='"..escape(entry.value.id.value).."'"
				end
				runsqlcommand(sql)
			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
			entry.errtxt = errtxt or "Failed to create entry"
		else
			entry.errtxt = errtxt or "Failed to save entry"
		end
	end
	return entry
end

function delete_table_entry(table, id)
	local result = ""
	local errtxt
	if not table or table == "" then
		errtxt = "Invalid table"
	elseif not id or id == "" then
		errtxt = "Invalid entry"
	else
	       	local res, err = pcall(function()
			local connected = databaseconnect()
			errtxt = "Invalid table"
			local tables = listtables()
			for i,t in ipairs(tables) do
				if t == table then
					errtxt = nil
					break
				end
			end
			if not errtxt then
				local sql = "DELETE FROM "..table.." WHERE id='"..escape(id).."'"
				runsqlcommand(sql)
				result = "Entry Deleted"
			end
			if connected then databasedisconnect() end
		end)
		if not res and err then
			errtxt = err
		end
	end

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

function create_database()
	local cmd = path.."echo -e 'y\ny\n' | "..path.."kamdbctl create 2>&1"
	local f = io.popen(cmd)
	local result = f:read("*a")
	return cfe({ value=result, label="Create database result" })
end

function search_database(id, value, comparison)
	local errtxt
	retval = {}
	retval.id = cfe({type="select", value=id or "", label="Table.Column", option={}, seq=1})
	retval.comparison = cfe({type="select", value=comparison or "=", label="Comparison", option={"=", "!=", "~", "!~", "~*", "!*~"}, seq=2})
	retval.value = cfe({label="Value", value=value or "", descr="Value or SQL regular expression", seq=3})
        local res, err = pcall(function()
		local connected = databaseconnect()
		local tables = listtables() or {}
		for i,t in ipairs(tables) do
			local columns = listcolumns(t) or {}
			for i,c in ipairs(columns) do
				retval.id.option[#retval.id.option + 1] = t.."."..c
			end
		end
		-- Get the rows from the DB
		if id and modelfunctions.validateselect(retval.id) and modelfunctions.validateselect(retval.comparison) then
			retval.result = cfe({type="structure", value={}, label="List of Rows", seq=4 })
			local table, column = string.match(id, "^([^.]*)%.(.*)")
			if table then
				local sql = "SELECT * FROM "..table.." WHERE "..column..comparison.."'"..value.."'"
				retval.result.value = getselectresponse(sql)
			end
		end
		if connected then databasedisconnect() end
	end)
	if not res and err then
		errtxt = err
	end
	return cfe({type="group", value=retval, label="Database Search", errtxt=errtxt})
end