
local odbc = require "odbc"

local m_charsets = require("reporter.sql.charsets"):Init()
local SQLServer = require("reporter.sql.sqlServer"):Init()
local MySQL = require("reporter.sql.mySql"):Init()

module(..., package.seeall)

local sql = sql or {}

function logDataSource (datasource)
	local jsonDatasource = json.encode(datasource)
	--return json.encode(datasource):gsub(datasource.Password, "***")
	jsonDatasource = jsonDatasource:gsub((datasource.Pwd or ""), "***")
	return jsonDatasource
end

local function logConnection(connection)
	return logDataSource(connection.datasource)
end

local function getDriver(datasource)
	--LogDebug("---------datasource.Type-------> %s", datasource.Type)
	if datasource.Type == "sql_server" then
		return SQLServer
	elseif datasource.Type == "my_sql" then
		return MySQL
	else
		error("Unsuported DBMS " .. tostring(datasource.Type))
	end
end

sql.connect = function(datasource)
	--LogInfo("[SQL:connect] %s", logDataSource(datasource))

	local driver = getDriver(datasource)
	local connectionData = driver.MakeConnectionData(datasource)

	local success, cnn, env = pcall(odbc.driverconnect, connectionData)
	if not success then
		local err = cnn
		LogError("[SQL:connect] %s - %s", logDataSource(datasource), tostring(err))
		return false, err
	end

	if not cnn then
		LogError("[SQL:connect] %s - %s", logDataSource(datasource), tostring(env))
		return false, env
	end

	-- Ok
	return true, {datasource = datasource, cnn = cnn, env = env}
end

sql.disconnect = function(connection)
	--LogDebug("[SQL:disconnect] %s", logConnection(connection))
	if not connection then
		--LogDebug("[SQL:disconnect] 1")
		return
	end
	if connection.cnn then
		--LogDebug("[SQL:disconnect] 2")
		connection.cnn:destroy()
		connection.cnn = nil
	end
	if connection.env then
		--LogDebug("[SQL:disconnect] 3")
		connection.env:destroy()
		connection.env = nil
	end
end

local function Run (connection, query)
	--LogDebug("------> [SQL:Run] connection: %s ", logConnection(connection))
	--LogDebug("------> [SQL:Run] query %s", query)

	query = m_charsets.FromUTF8(query)
	--LogDebug("[SQL:Run] %s query %s", logConnection(connection), query)
	local stmt, err = connection.cnn:statement()
	if not stmt then
		LogError("[SQL:Run] %s error opening statement for query %s - %s", logConnection(connection), query, tostring(err))
		return false, err
	end

	local cur, err = stmt:execute(query)
	if not cur then
		stmt:destroy()
		LogError("[SQL:Run] %s error running query %s - %s", logConnection(connection), query, tostring(err))
		return false, err
	end

	-- OK
	return cur, stmt
end

sql.query = function(connection, query)
	--Run
	local cur, stmt = Run(connection, query)
	if not cur then
		return false, stmt
	end
	--process operation result
	if type(cur) == "number" then
		stmt:destroy()
		return true, { count = cur }
	end
	--process query result
	local result = {
		columns = {},
		rows = {}
	}
	--columns
	local colNames = cur:colnames()
	local colTypes = cur:coltypes()
	for index, colName in ipairs(colNames) do
		local colType = colTypes[index]
		table.insert(result.columns, { name = colName, type = colType })
	end
	--rows
	stmt:foreach("", nil, function(row_db)
		local row_a = {}
		for _, value in pairs(row_db) do
			if type(value) == "userdata" then
				value = json.null
			end
			table.insert(row_a, value)
		end
		
		local row = {}
		for index, colName in ipairs(colNames) do
			row[colName] = row_a[index]
		end
		table.insert(result.rows, row)
	end)
	cur:close()
	stmt:destroy()
	return true, result
end

sql.connectAndQuery = function(datasource, query)
	local connection, err = sql.connect(datasource)
	if not connection then
		return false, err
	end
	local result, err = sql.query(connection, query)
	sql.disconnect(connection)
	return result, err
end

sql.escapeValue = function(datasource, value)
	--LogInfo("[SQL:escapeValue] %s %s", datasource, value)
	local driver = getDriver(datasource)
	return tostring(driver.EscapeValue(value))
end

sql.getStringErr = function(datasource, err)
	if not err then
		return ""
	end
	--LogInfo("[SQL:getStringErr] %s %s", datasource, value)
	local driver = getDriver(datasource)
	return driver.GetStringErr(err)
end

--[[
	Recibe:
		dbConnection  : conexión a la BD
		datasource    : la config (MySql, SqlServer)
		spName        : el Nombre del SP a ejectuar
		parametersList: una tabla con la lista de parametros a ejecutar en el SP.
			Ej: de los parametros:
			{
				{name = "param1", value = "valor 1"},
				{name = "param2", value = "valor 2"},
				{name = "param3", value = "valor 3"},
				{name = "param4", value = "valor 4"}
			}
]]--
sql.executeSP = function(dbConnection, datasource, spName, parametersList)
	assert(dbConnection)
	assert(datasource)
	assert(spName)

	local parameters = ""
	for _, parameter in ipairs(parametersList or {}) do
		--LogDebug("[SQL:executeSP] Parameter %s = %s", _, json.encode(parameter))
		if parameters:len() > 0 then
			parameters = parameters .. ", "
		end
		local value = sql.escapeValue(datasource, parameter.value or "")
		parameters = parameters .. string.format([['%s']], value)
	end

	local driver = getDriver(datasource)
	local spQuery = driver.MakeExecuteSPQuery(spName, parameters)
	--LogDebug("[SQL:executeSP] %s", spQuery)
	local success, resp = sql.query(dbConnection, spQuery)
	return success, resp
end

local SQL = {
	Connect         = sql.connect,
	ConnectAndQuery = sql.connectAndQuery,
	Disconnect      = sql.disconnect,
	EscapeValue     = sql.escapeValue,
	ExecuteSP       = sql.executeSP,
	Query           = sql.query,
	LogDataSource   = logDataSource,
	GetStringErr    = sql.getStringErr
}

function Init ()
	return SQL
end