local mymodule = {}

db = require("acf.db")

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

local function get_connection(dbase, self, clientdata)
	local retval = cfe({ type="group", value={} })
	retval.value.connection = cfe({ type="group", value={}, label="Database Connection", seq=0 })
	retval.value.connection.value.engine = cfe({ type="select", label="Database Engine", option={}, key=true, seq=1 })
	for n,v in pairs(db.engine) do
		retval.value.connection.value.engine.option[#retval.value.connection.value.engine.option+1] = {label=n, value=tostring(v)}
	end
	retval.value.connection.value.database = cfe({ label="Database", key=true, seq=2 })
	retval.value.connection.value.user = cfe({ label="User", key=true, seq=3 })
	retval.value.connection.value.password = cfe({ type="password", label="Password", key=true, seq=4 })
	retval.value.connection.value.host = cfe({ label="Host", key=true, seq=5 })
	retval.value.connection.value.port = cfe({ label="Port", key=true, seq=6 })

	self.handle_clientdata(retval, clientdata)

	-- If dbase has a default, remove the cfe. Otherwise, fill in with the value from the cfe.
	for n,v in pairs(retval.value.connection.value) do
		if dbase[n] then
			retval.value.connection.value[n] = nil
		elseif n == "engine" or n == "port" then
			dbase[n] = tonumber(v.value)
		else
			dbase[n] = v.value
		end
	end

	return retval
end

local function fill_connection(dbase, self, retval)
	-- Fill in dbase with the values from the cfe retval
	for n,v in pairs(retval.value.connection.value) do
		if n == "engine" or n == "port" then
			dbase[n] = tonumber(v.value)
		else
			dbase[n] = v.value
		end
	end

	return retval
end

function mymodule.list_databases(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

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

	return retval
end

function mymodule.list_tables(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "List of Database Tables"
	retval.value.tables = cfe({ type="list", value={}, label="List of Database Tables" })
	local res, err = pcall(function()
		local connected = db.databaseconnect()
		retval.value.tables.value = db.listtables()
		if connected then db.databasedisconnect() end
	end)
	if not res and err then
		retval.value.connection.errtxt = err
	end
	return retval
end

function mymodule.list_table_entries(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "Database Table Entries"
	-- Need to specify the table
	retval.value.table = cfe({ label="Table", key=true })
	-- Here is the pagination/filtering info
        retval.value.page = cfe({ value=0, label="Page Number", descr="0 indicates ALL", key=true })
	retval.value.pagesize = cfe({ value=10, label="Page Size", key=true })
	retval.value.rowcount = cfe({ value=0, label="Row Count" })
	-- orderby must be an array of tables with column name and direction
	retval.value.orderby = cfe({ type="structure", value={}, label="Order By", key=true })
	-- filter is a table with a string filter for each column
	retval.value.filter = cfe({ type="structure", value={}, label="Filter", key=true })
	self.handle_clientdata(retval, clientdata)

	-- Here is the result
	retval.value.fields = cfe({ type="list", value={}, label="List of Table Fields" })
	retval.value.keyfields = cfe({ type="list", value={}, label="List of Key Table Fields" })
	retval.value.entries = cfe({ type="structure", value={}, label="List of Database Entries" })

        -- Process the incoming page data
	local page = tonumber(retval.value.page.value) or 0
	retval.value.page.value = page
	local pagesize = tonumber(retval.value.pagesize.value) or 10
	retval.value.pagesize.value = pagesize

	local res, err = pcall(function()
		local connected = db.databaseconnect()
		local tables = db.listtables()
		retval.value.table.errtxt = "Table does not exist"
		retval.errtxt = "Table does not exist"
		for i,t in ipairs(tables) do
			if t == retval.value.table.value then
				retval.value.table.errtxt = nil
				retval.errtxt = nil
				retval.value.fields.value = db.listcolumns(t) or {}
				retval.value.keyfields.value = db.listkeycolumns(t) or {}

				local orderby = {}
				local columns = {}
				for i,c in ipairs(retval.value.fields.value) do columns[c] = c end
				local directions = {asc="ASC", desc="DESC", ASC="ASC", DESC="DESC"}
				for i,o in ipairs(retval.value.orderby.value) do
					if columns[o.column] and directions[o.direction] then
						orderby[#orderby+1] = columns[o.column].." "..directions[o.direction]
					end
				end

				local filter = {}
				-- FIXME - not all columns types will allow regex filtering, such as date columns
				for c,f in pairs(retval.value.filter.value) do
					if columns[c] and f ~= "" then
						filter[#filter+1] = columns[c].."~'"..db.escape(f).."'"
					end
				end

				local sql = " FROM "..db.escape(t)
				if #filter>0 then
					sql = sql.." WHERE "..table.concat(filter, " AND ")
				end
				if page > 0 then
					local count = db.getselectresponse("SELECT count(*)"..sql)
					retval.value.rowcount.value = count[1].count
				end
				if #orderby>0 then
					sql = sql.." ORDER BY "..table.concat(orderby, ", ")
				end
				if page > 0 then
					sql = sql.." LIMIT "..pagesize.." OFFSET "..(page - 1)*pagesize
				end
				retval.value.entries.value = db.getselectresponse("SELECT *"..sql) or {}
				if page <= 0 then
					retval.value.rowcount.value = #retval.value.entries.value
				end
			end
		end
		if connected then db.databasedisconnect() end
	end)
	if not res and err then
		retval.value.connection.errtxt = err
	end

	return retval
end

function mymodule.get_new_table_entry(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "Database Table Entry"

	retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 })
	self.handle_clientdata(retval, clientdata)
	retval.errtxt = "Table does not exist"
	if retval.value.table.value ~= "" then
		local res, err = pcall(function()
			local connected = db.databaseconnect()
			local tables = db.listtables()
			for i,t in ipairs(tables) do
				if t == retval.value.table.value then
					retval.value.table.errtxt = nil
					retval.value.table.readonly = true
					retval.errtxt = nil
					for n,v in pairs(retval.value.connection.value) do
						v.readonly = true
					end
					break
				end
			end
			if not errtxt then
				retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 })
				retval.value.nulls = cfe({ type="group", value={}, label="Null Value Table Fields", seq=3 })
				retval.value.defaults = cfe({ type="group", value={}, label="Default Value Table Fields", seq=4 })
				local fields, defaults, nullable = db.listcolumns(retval.value.table.value)
				for i,f in ipairs(fields) do
					retval.value.fields.value[f] = cfe({ label=f, seq=i })
					if nullable[f] then
						retval.value.nulls.value[f] = cfe({ type="boolean", value=false, label=f, seq=i })
					end
					if defaults[f] then
						retval.value.defaults.value[f] = cfe({ type="boolean", value=true, label=f, default=defaults[f], seq=i })
					end
				end
			end
			if connected then db.databasedisconnect() end
		end)
		if not res and err then
			retval.value.connection.errtxt = err
		end
	end

	return retval
end

local function get_field_value(dbase, self, entry, field)
	local retval
	if entry.value.defaults and entry.value.defaults.value[field] and entry.value.defaults.value[field].value then
		retval = "DEFAULT"
	elseif entry.value.nulls and entry.value.nulls.value[field] and entry.value.nulls.value[field].value then
		retval = "NULL"
	elseif entry.value.fields and entry.value.fields.value[field] then
		retval = "'"..dbase.escape(entry.value.fields.value[field].value).."'"
	end
	return retval
end

function mymodule.get_table_entry(dbase, self, clientdata)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = mymodule.get_new_table_entry(db, self, clientdata)
	if retval.value.fields and retval.value.defaults then
		for n,v in pairs(retval.value.fields.value) do
			if retval.value.defaults.value[n] then
				retval.value.defaults.value[n].value = false
			end
		end
	end
	self.handle_clientdata(retval, clientdata) -- Fill in the fields
	if not retval.value.connection.errtxt and not retval.value.table.errtxt and retval.value.fields then
		local res, err = pcall(function()
			local connected = db.databaseconnect()
			local keyfields = db.listkeycolumns(retval.value.table.value)
			local reversekeyfields = {}
			local fieldchecks = {}
			for i,f in ipairs(keyfields) do
				reversekeyfields[f] = i
				retval.value.fields.value[f].errtxt = "Entry does not exist"
				fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, retval, f)
			end
			retval.errtxt = "Entry does not exist"
			local sql = "SELECT * FROM "..db.escape(retval.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
			local entry = db.getselectresponse(sql)
			if entry and #entry > 0 then
				retval.errtxt = nil
				--for n,v in pairs(entry[1]) do
				for n,v in pairs(retval.value.fields.value) do
					if not entry[1][n] then
						retval.value.nulls.value[n].value = true
					else
						retval.value.fields.value[n].value = entry[1][n]
					end
					if reversekeyfields[n] then
						retval.value.fields.value[n].readonly = true
						retval.value.fields.value[n].errtxt = nil
					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 db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end
	fill_connection(db, self, entry)

	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 or nil == entry.value.fields then
		success = false
		entry.value.table.errtxt = "Table does not exist"
	end
	if success then
        	local res, err = pcall(function()
			local connected = db.databaseconnect()
			local keyfields = db.listkeycolumns(entry.value.table.value)
			local reversekeyfields = {}
			local fieldchecks = {}
			for i,f in ipairs(keyfields) do
				reversekeyfields[f] = i
				fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, entry, f)
			end
			-- We want to catch syntax errors, because they just indicate the entry does not exist
			local res2, err2 = pcall(function()
				local sql = "SELECT * FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
				local tmp = db.getselectresponse(sql)
				if not create and (not tmp or #tmp == 0) then
					success = false
					for i,n in ipairs(keyfields) do
						entry.value.fields.value[n].errtxt = "Entry does not exist"
				end
				elseif create and tmp and #tmp>0 then
					success = false
					for i,n in ipairs(keyfields) do
						entry.value.fields.value[n].errtxt = "Entry already exists"
					end
				end
			end)
			if not res2 and err2 then
				if not create then
					success = false
					errtxt = err
					for i,n in ipairs(keyfields) do
						entry.value.fields.value[n].errtxt = "Entry does not exist"
					end
				end
			end
			if success then
				local sql
				local names = {}
				local values = {}
				if create then
					for n,v in pairs(entry.value.fields.value) do
						names[#names+1] = n
						values[#values+1] = get_field_value(db, self, entry, n)
					end
					sql = "INSERT INTO "..db.escape(entry.value.table.value).." ("..table.concat(names, ", ")..") VALUES("..table.concat(values, ", ")..")"
				else
					for n,v in pairs(entry.value.fields.value) do
						if not reversekeyfields[n] then
							values[#values+1] = n.."="..get_field_value(db, self, entry, n)
						end
					end
					sql = "UPDATE "..db.escape(entry.value.table.value).." SET "..table.concat(values, ", ").." WHERE "..table.concat(fieldchecks, " AND ")
				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 db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end

	local retval = get_connection(db, self, clientdata)
	retval.label = "Delete Database Table Entry"

	retval.value.table = cfe({ label="Table", errtxt="Table does not exist", key=true, seq=1 })
	self.handle_clientdata(retval, clientdata)
	retval.errtxt = "Table does not exist"
	if retval.value.table.value ~= "" then
		local res, err = pcall(function()
			local connected = db.databaseconnect()
			local tables = db.listtables()
			for i,t in ipairs(tables) do
				if t == retval.value.table.value then
					retval.value.table.errtxt = nil
					retval.value.table.readonly = true
					retval.errtxt = nil
					for n,v in pairs(retval.value.connection.value) do
						v.readonly = true
					end
					break
				end
			end
			if not errtxt then
				local keyfields = db.listkeycolumns(retval.value.table.value)
				retval.value.fields = cfe({ type="group", value={}, label="Table Fields", seq=2 })
				retval.value.nulls = cfe({ type="group", value={}, label="Null Value Table Fields", seq=3 })
				for i,f in ipairs(keyfields) do
					retval.value.fields.value[f] = cfe({ label=f, seq=i })
					retval.value.nulls.value[f] = cfe({ type="boolean", value=false, label=f, seq=i })
				end
			end
			if connected then db.databasedisconnect() end
		end)
		if not res and err then
			retval.value.connection.errtxt = err
		end
	end

	return retval
end

function mymodule.delete_table_entry(dbase, self, entry)
	local db = dbase
	if type(dbase) == "function" then
		db = dbase()
	end
	fill_connection(db, self, entry)

	entry.errtxt = "Failed to delete table entry"
	if entry.value.table.value == "" then
		entry.value.table.errtxt = "Invalid table"
	else
	       	local res, err = pcall(function()
			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 keyfields = db.listkeycolumns(entry.value.table.value)
				local fieldchecks = {}
				for i,f in ipairs(keyfields) do
					fieldchecks[#fieldchecks+1] = f.."="..get_field_value(db, self, entry, f)
				end
				local sql = "DELETE FROM "..db.escape(entry.value.table.value).." WHERE "..table.concat(fieldchecks, " AND ")
				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