local mymodule = {}
-- Helper library for model functions for viewing and editing database entries
-- Relies heavily on the acf-lib/db.lua library (db object passed into each function)

function mymodule.list_databases(dbase, self, clientdata)
	local retval = {}
        local errtxt
	local res, err = pcall(function()
		local db = dbase
		if type(dbase) == "function" then
			db = dbase()
		end
		retval = db.listdatabases()
	end)
	if not res and err then
		errtxt = err
	end
	
	return cfe({ type="list", value=retval, label="List of Databases", errtxt=errtxt })
end

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

	return retval
end

function mymodule.create_table_entry(dbase, self, entry, action)
        return mymodule.update_table_entry(dbase, self, entry, action, true)
end

function mymodule.update_table_entry(dbase, self, entry, action, 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 db = dbase
			if type(dbase) == "function" then
				db = dbase()
			end
			local connected = db.databaseconnect()
			local tables = db.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 "..db.escape(entry.value.table.value).." WHERE id='"..db.escape(entry.value.id.value).."'"
				local tmp = db.getselectresponse(sql)
				if not tmp or #tmp == 0 then
					success = false
					entry.value.id.errtxt = "Entry does not exist"
					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] = db.escape(n)
						values[#values+1] = db.escape(v.value)
					end
				end
				if create then
					sql = "INSERT INTO "..db.escape(entry.value.table.value).." ("..table.concat(names, ", ")..") VALUES('"..table.concat(values, "', '").."')"
				else
					sql = "UPDATE "..db.escape(entry.value.table.value).." SET ("..table.concat(names, ", ")..") = ('"..table.concat(values, "', '").."') WHERE id='"..db.escape(entry.value.id.value).."'"
				end
				db.runsqlcommand(sql)
			end
			if connected then db.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 mymodule.get_delete_table_entry(dbase, self, clientdata)
	local retval = {}
	retval.table = cfe({ value=clientdata.table or "", label="Table", seq=0 })
	retval.id = cfe({ value=clientdata.id or "", label="ID", seq=1 })
	return cfe({ type="group", value=retval, label="Delete Database Table Entry" })
end

function mymodule.delete_table_entry(dbase, self, entry)
	entry.errtxt = "Failed to delete table entry"
	if entry.value.table.value == "" then
		entry.value.table.errtxt = "Invalid table"
	elseif entry.value.id.value == "" then
		entry.value.id.errtxt = "Invalid entry"
	else
	       	local res, err = pcall(function()
			local db = dbase
			if type(dbase) == "function" then
				db = dbase()
			end
			local connected = db.databaseconnect()
			entry.value.table.errtxt = "Invalid table"
			local tables = db.listtables()
			for i,t in ipairs(tables) do
				if t == entry.value.table.value then
					entry.value.table.errtxt = nil
					break
				end
			end
			if not entry.value.table.errtxt then
				local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE id='"..db.escape(entry.value.id.value).."'"
				db.runsqlcommand(sql)
				entry.errtxt = nil
			end
			if connected then db.databasedisconnect() end
		end)
		if not res and err then
			entry.errtxt = err
		end
	end

	return entry
end

return mymodule