require "LuaOrm.Date"
require "uuid"

-- SQL
local SQL = require("reporter.sql.sql"):Init()

module(..., package.seeall)

-- in case it's available
local LogInfo = LogInfo or function(msg, ...)
	print("Info", msg:format(...))
end
local LogWarning = LogWarning or function(msg, ...)
	print("Warning", msg:format(...))
end

local function GetSchedulerModule ()
	local scheduler = require("reporter.scheduler")
	return scheduler
end

--[[
Funcion que valida si la Base de Datos esta lista para ser utilizada.
Conexion basica contra la DB, server, user y password.
]]--
local function DataBaseIsReady (schedule)
	local databaseConfig = schedule.database or {}

	LogInfo("[DATABASE:DataBaseIsReady] Validate connection to DataBase. DataBase Info: '%s'.", SQL.LogDataSource(databaseConfig))

	if not databaseConfig.Name then
		LogError("[DATABASE:DataBaseIsReady] DataBase Name is required.")
		return false, "Incomplete definition. DataBase Name is required."
	end
	if not databaseConfig.Server then
		LogError("[DATABASE:DataBaseIsReady] DataBase Server is required.")
		return false, "Incomplete definition. DataBase Server is required."
	end
	if not databaseConfig.Port then
		LogError("[DATABASE:DataBaseIsReady] DataBase Port is required.")
		return false, "Incomplete definition. DataBase Port is required."
	end
	if not databaseConfig.User then
		LogError("[DATABASE:DataBaseIsReady] DataBase User is required.")
		return false, "Incomplete definition. DataBase User is required."
	end
	if not databaseConfig.Pwd then
		LogError("[DATABASE:DataBaseIsReady] DataBase Pwd is required.")
		return false, "Incomplete definition. DataBase Pwd file is required."
	end

	-- Me conecto a la BD, valido si la conexion se realiza de forma exitosa, y me quedo con el objeto "connection".
	local res, connection = SQL.Connect(databaseConfig)
	--LogDebug("-------------> res: %s, connection: %s.", res, connection)

	if not res or not connection then
	-- Si da Error la conexion la BD
		local err = SQL.GetStringErr(databaseConfig, connection)
		LogError("[DATABASE:DataBaseIsReady] Connection error to DataBase. Err: '%s', DataBase Server: '%s:%s'.", err, databaseConfig.Server, databaseConfig.Port)
		return false, err
	end

	-- OK - retorno el objeto "connection"
	LogInfo("[DATABASE:DataBaseIsReady]--> Successful connection to DataBase Server: '%s'.", databaseConfig.Server)

	return true, connection
end

--[[
Funcion que ejecuta el SP de transacciones (being, error, end)
]]--
local function ExecuteTransaction (databaseConfig, dbConnection, scheduleToRun, reportId, runId, state, totalRows, updatedRows, err)
	assert(dbConnection)
	assert(type(scheduleToRun) == "table")
	assert(scheduleToRun.Id)
	assert(scheduleToRun.Vcc)
	assert(reportId)
	assert(state)
	assert(runId)

	totalRows = totalRows or ""
	updatedRows = updatedRows or ""
	err = err or ""
	LogInfo("[DATABASE:ExecuteTransaction] Transaction '%s' '%s'. Vcc: %s', Scheduler Id: '%s', Report: '%s', Run Id: '%s', totalRows: '%s', updatedRows: '%s'.",
		state, err, scheduleToRun.Vcc, scheduleToRun.Id, reportId, runId, totalRows, updatedRows)

	local parametersList = {
		{name = "vcc"        , value = scheduleToRun.Vcc },
		{name = "scheduleId" , value = scheduleToRun.Id  },
		{name = "reportId"   , value = reportId          },
		{name = "runId"      , value = runId             },
		{name = "state"      , value = state             },
		{name = "totalRows"  , value = totalRows         },
		{name = "updatedRows", value = updatedRows       },
		{name = "err"        , value = err               }
	}

	local spName = "sp_ReportTransaction"
	local success, resp = SQL.ExecuteSP(dbConnection, databaseConfig, spName, parametersList)

	--LogDebug(" 1 -- ExecuteTransaction - SQL Query success: %s resp %s", success, resp)

	if not success then
		local err = SQL.GetStringErr(databaseConfig, resp)
		LogError("[DATABASE:ExecuteTransaction] SQL Command Error. %s. Vcc: %s', Scheduler Id: '%s', Report: '%s', Run Id: '%s'.", err, scheduleToRun.Vcc, scheduleToRun.Id, reportId, runId)
		--LogDebug("----------------------------------------------------------------------------------------------\n\n")
		return false, err
	end

	-- ok
	return true, resp
end

--[[
Funcion que ejecuta el SP para marcar como "RUNNING" la transaccion.
]]--
local function BeginTransaction (databaseConfig, dbConnection, scheduleToRun, reportId, runId, totalRows)
	local state = "RUNNING"
	return ExecuteTransaction(databaseConfig, dbConnection, scheduleToRun, reportId, runId, state, totalRows)
end

--[[
Funcion que ejecuta el SP para marcar como "COMPLETED" la transaccion.
]]--
local function EndTransaction (databaseConfig, dbConnection, scheduleToRun, reportId, runId, totalRows, updatedRows)
	local state = "COMPLETED"
	return ExecuteTransaction(databaseConfig, dbConnection, scheduleToRun, reportId, runId, state, totalRows, updatedRows)
end

--[[
Funcion que ejecuta el SP para marcar como "ERROR" la transaccion.
]]--
local function ErrorTransaction (databaseConfig, dbConnection, scheduleToRun, reportId, runId, totalRows, updatedRows, err)
	local state = "ERROR"
	return ExecuteTransaction(databaseConfig, dbConnection, scheduleToRun, reportId, runId, state, totalRows, updatedRows, err)
end

--[[
Esta funcion comienza a realizar el proceso de Ejecucion de SPs a la DataBase con las conversaciones.
]]--
local function DataBaseStartRun (dbConnection, scheduleToRun, jsonResult, params, utils)
	local databaseConfig = scheduleToRun.database or {}
	local reportResult = json.decode(jsonResult)
	--LogDebug("--------> jsonResult: %s", jsonResult)
	local runId = uuid.new()
	local reportId = scheduleToRun.Report

	--LogDebug("\n----------------------------------------------------------------------------------------------")
	LogInfo("[DATABASE:DataBaseStartRun] START Running Scheduler Id: '%s', VCC: '%s', Report: '%s', Run Id: '%s'.\nInfo: '%s'\nDataBase Config: '%s'.",
		scheduleToRun.Id,
		scheduleToRun.Vcc,
		reportId,
		runId,
		json.encode(params),
		SQL.LogDataSource(databaseConfig)
	)

	-- Si el resultado de la ejecucion del reporte, no contiene la estructura esperada (NO deberia suceder nunca!!)
	if type(reportResult.result) ~= "table" then
		LogError("[DATABASE:DataBaseStartRun] Error - Scheduler result malformed. Scheduler Id: '%s', Report: '%s', Run Id: '%s'. Err: '%s'.", scheduleToRun.Id, reportId, runId, reportResult.result)
		--LogDebug("----------------------------------------------------------------------------------------------\n\n")
		return false, "Scheduler result malformed"
	end

	-- Si no ha retornado registros.
	if reportResult.result.length < 1 then
		LogInfo("[DATABASE:DataBaseStartRun] END Ran to DataBase. WITHOUT RESULTS. Scheduler Id: '%s', Report: '%s', Run Id: '%s'.", scheduleToRun.Id, reportId, runId)
		--LogDebug("----------------------------------------------------------------------------------------------\n\n")
		return true
	end

	-- Ejecuto el SP de Comienzo de la TRANSACCION
	--
	local success, resp = BeginTransaction(databaseConfig, dbConnection, scheduleToRun, reportId, runId, reportResult.result.length)
	if not success then
		local err = resp
		LogError("1 [DATABASE:DataBaseStartRun] SQL Command Error. %s. Scheduler Id: '%s', Report: '%s', Run Id: '%s'.", err, scheduleToRun.Id, reportId, runId)
		--LogDebug("----------------------------------------------------------------------------------------------\n\n")
		return false, err
	end

	-- El mainRowId es el ID de Interaction utilizado para poder referenciar los rows del SP de Threaded con el Row del SP principal
	local mainRowId = nil
	local hasIncludeVCCInThreaded = false

	-- Funcion encargada de transformar valores a ser enviados a la BD.
	--  Ej: El caso de los "StateRaw" que determindas veces llega la palabra "False" y debemos convertirla en un ID del sistema valido.
	--
	local function TransformValue (rowValue, index, columnsMetadata)
		local columnMetadata = columnsMetadata[index]
		if not columnMetadata then
			return rowValue
		end
		if columnMetadata.name == "StateRaw" then
			if tostring(rowValue or ""):lower() == "false" then
				rowValue = "LOGGED"
			end
		end
		-- En "ID" viene el Id de la interaction; si el mismo viene me tengo quedar con el, para poder asignarlo a los SPs de threaded (hijos)
		if columnMetadata.name == "ID" then
			mainRowId = rowValue
		end
		-- Si viene el "VCC" me tengo quedar con el, para poder asignarlo a los SPs de threaded (hijos)
		if columnMetadata.name == "VCC" and not hasIncludeVCCInThreaded then
			hasIncludeVCCInThreaded = true
		end
		return rowValue, columnMetadata.name
	end

	--[[
		Función que crea el nombre del SP según si es del System o Custom.
	]]--
	local function MakeSpName (isThreaded)
		local spName
		local reportInfo = scheduleToRun.__ReportInfo

		if reportInfo and reportInfo.isCustomReport then
			local name = reportInfo.name or reportId
			spName = string.format("sp_Custom_%s", name)
		else
			spName = string.format("sp_%s", reportId)
		end

		if isThreaded then
			spName = string.format("%s_Threaded", spName)
		end
	
		--LogDebug("1---------> spName '%s'", spName)
		-- Elimino caracteres invalidos.
		spName = string.gsub(spName, " ", "_")
		spName = string.gsub(spName, "-", "_")
		--LogDebug("2---------> spName '%s'", spName)

		return spName
	end

	---
	local StartRowsTransaction, StartSubRowsTransaction

	local function RowsTransaction (spName, columnsMetadata, rows, threadingInfo, parentRowNumber, parentID)
		local totalRows = 0
		local totalSubRows = 0

		for rowNumber, row in ipairs(rows or {}) do
			totalRows = totalRows + 1

			--LogDebug("  - row: %s'", rowNumber)

			-- Aca agrego los 4 parametros que van siempre en la ejecucion de cada SP.
			-- @scheduleId, @reportId, @runId (que es un guid que se genera por cada ejecucion del reporte) y el numero de row @rowNumber
			local parametersList = {
				{name = "scheduleId", value = scheduleToRun.Id},
				{name = "reportId"  , value = reportId        },
				{name = "runId"     , value = runId           },
				{name = "rowNumber" , value = rowNumber       }
			}
			-- Estos 2 parametros vienen cuando la funcion es invocada desde (**) para los Threading
			--
			if parentRowNumber or parentID then
				-- El numero de row parent
				if parentRowNumber then
					table.insert(parametersList, {name = "parentRowNumber", value = parentRowNumber})
				end
				-- El Id de interaction de row parent.
				if parentID then
					table.insert(parametersList, {name = "parentID", value = parentID})
				end
				-- Si en el row padre viene definido el VCC, también lo debo incluir en el hijo (threaded)
				if hasIncludeVCCInThreaded then
					table.insert(parametersList, {name = "VCC", value = scheduleToRun.Vcc})
				end
			end
			-- Ahora recorro el Row, obteniendo el valor de cada celda (columna)
			mainRowId = nil
			for j, rowValue in ipairs(row) do
				local transformedValue, columnName = TransformValue(rowValue, j, columnsMetadata)
				--LogDebug(" RowsTransaction ---> columnName: '%s' = rowValue: '%s' -> transformedValue: '%s'-> mainRowId: '%s'", columnName, rowValue, transformedValue, mainRowId)
				table.insert(parametersList, {name = "field" .. tostring(j), value = transformedValue})
			end

			-- Ejecuto el SP
			local success, resp = SQL.ExecuteSP(dbConnection, databaseConfig, spName, parametersList)
			--LogDebug(" 2 -- SQL Query success: %s result %s", success, resp)
	
			-- Si la ejecucion dio Error, finalizo la transaccion con Error
			if not success then
				local err = SQL.GetStringErr(databaseConfig, resp)
				LogError("[DATABASE:DataBaseStartRun] SQL Command Error. %s. Scheduler Id: '%s', Report: '%s', Run Id: '%s'.", err, scheduleToRun.Id, reportId, runId)
				--LogDebug("----------------------------------------------------------------------------------------------\n\n")
	
				-- Ejecuto el SP de Error en la TRANSACCION
				--
				err = "Updating Error" .. tostring(err)
				ErrorTransaction(databaseConfig, dbConnection, scheduleToRun, reportId, runId, reportResult.result.length, totalRows, err)
				return false, err
			end

			if threadingInfo then
				--LogDebug("----------------------------------------------------------------------------------------------\n\n")
				--LogDebug("+--> START threadingInfo parent Row %d", rowNumber)
				local columnsMetadataThreading = threadingInfo.columnsMetadata
				local rowsThreading = (threadingInfo.records or {})[rowNumber]
				-- Creo el nombre del SP con hilos
				local isThreaded = true
				local spNameSubRows = MakeSpName(isThreaded)
				-- Ejecuto el SP
				local total = StartSubRowsTransaction(spNameSubRows, columnsMetadataThreading, rowsThreading, nil, rowNumber, mainRowId) -- (**)
				if total ~= false and type(total) == "number" then
					totalSubRows = totalSubRows + total
				end
				--LogDebug("---> END threadingInfo parent Row %d", rowNumber)
			end
		end

		return totalRows, totalSubRows
	end

	-- Arora comienza el proceso de actualizacion de cada registro, ejecutando el SP por cada ROW.
	StartRowsTransaction = RowsTransaction
	StartSubRowsTransaction = RowsTransaction

	local columnsMetadata = reportResult.result.columnsMetadata
	local rows = reportResult.result.rows
	local threadingInfo = reportResult.result.threadingInfo
	-- Creo el nombre del SP
	local spName = MakeSpName()
	-- Ejecuto el SP
	local totalRows, totalSubRows = StartRowsTransaction(spName, columnsMetadata, rows, threadingInfo)
	-- Si viene en false es que dio algun error
	if totalRows == false then
		local err = totalSubRows
		return false, err
	end

	-- OK --

	-- Ejecuto el SP de Finalizacion de la TRANSACCION
	--
	local success, resp = EndTransaction(databaseConfig, dbConnection, scheduleToRun, reportId, runId, reportResult.result.length, totalRows)
	if not success then
		local err = resp
		LogError("1 [DATABASE:DataBaseStartRun] SQL Command Error. %s. Scheduler Id: '%s', Report: '%s', Run Id: '%s'.", err, scheduleToRun.Id, reportId, runId)
		--LogDebug("----------------------------------------------------------------------------------------------\n\n")
		return false, err
	end

	LogInfo("[DATABASE:DataBaseStartRun] END Ran to DataBase. Scheduler Id: '%s', Report: '%s', Run Id: '%s'. Total Rows: %s, Total Sub Rows: %s", scheduleToRun.Id, reportId, runId, totalRows, totalSubRows)
	--LogDebug("----------------------------------------------------------------------------------------------\n\n")

	return true
end

--[[
	Funcion encargada de ejecutar el reporte hacia una Base de Datos
]]--
function ExecuteReport (utils, authFilter, prefix, scheduleToRun, executionType)
	LogInfo("[DATABASE:ExecuteReport] DataBase execution schedule Id '%s', Execution Type: '%s'.", scheduleToRun.Id, executionType)
	--LogDebug("[DATABASE:ExecuteReport] scheduleToRun '%s'.", json.encode(scheduleToRun))

	local scheduler = GetSchedulerModule()
	local success, dbConnection = DataBaseIsReady(scheduleToRun)
	local errMsg = {subject = nil, body = nil, reason = nil}
	local now = LuaOrm.Date()
	local userId = utils.GetCurrentLoggedUser()

	local function AddSuccessHistory()
		local data = {type = "text/db", sendType = "DATABASE", executionType = executionType, userId = userId}
		scheduler.add_schedule_historical(utils, scheduleToRun.Id, now, true, data)
	end

	local function AddErrorHistory(data)
		data.sendType = "DATABASE"
		data.executionType = executionType
		data.userId = userId
		scheduler.add_schedule_historical(utils, scheduleToRun.Id, now, false, data)
	end

	local OnError = function(err)
		--LogDebug("------***-----------> %s", type(err))
		-- for _,v in pairs(err) do
		-- 	LogDebug("   %s = %s", _, v)
		-- 		for _1,v1 in pairs(v) do
		-- 			LogDebug("     %s = %s", _1, v1)
		-- 		end
		-- end
		success = false
		errMsg.subject = "Failed to Export report to DataBase."
		errMsg.body = "Some problem occurred during Exporting to DataBase."
		errMsg.reason = "Failed to Export report to DataBase."

		if type(err) == "string" then
			errMsg.body = errMsg.body .. "\n\nError:\n" .. tostring(err)
			errMsg.reason = tostring(err)
			--LogDebug("---- *** > %s", errMsg.reason)
		end

		LogWarning("[DATABASE:ExecuteReport] Some problem occurred during Exporting to DataBase. Report: %s", scheduleToRun.Id)

		AddErrorHistory({reason = errMsg.reason})

		return success, errMsg
	end

	if not success then
		local err = dbConnection
		return OnError(err)
	end

	-- Ejecuto el Reporte, contra la app "reports" del repo "agent"
	local result, code, headers, httpStatus, params, name, jsonResult = scheduler.request_run_report(utils, authFilter, prefix, scheduleToRun)
	-- LogDebug("--1 result: %s", result)
	-- LogDebug("--2 code: %s", code)
	-- LogDebug("--3 headers: %s", headers)
	-- LogDebug("--4 httpStatus: %s", httpStatus)
	-- LogDebug("--5 params: %s", params)
	-- LogDebug("--6 name: %s", name)
	-- LogDebug("--7 jsonResult: %s", jsonResult)

	-- Si dio ok Comienza la exportacion a la BD
	if code == 200 then
		-- Ejecuto la exportacion a la BD
		local ok, err = DataBaseStartRun(dbConnection, scheduleToRun, jsonResult, params, utils)
		SQL.Disconnect(dbConnection)

		if not ok then
			success, errMsg = OnError(err)
		else
			AddSuccessHistory()
		end
	else
		SQL.Disconnect(dbConnection)
		success = false
		errMsg.subject = "Failed to execute report "
		errMsg.body = "Some problem occurred during execution of your report "
		errMsg.reason = "Error during execution report. " .. (httpStatus or code or "")

		LogWarning("[DATABASE:ExecuteReport] Some problem occurred during execution of request: %s", httpStatus)

		AddErrorHistory({reason = errMsg.reason, result = result, code = code, headers = headers, httpStatus = httpStatus})
	end

	return success, errMsg
end


--[[
	Funcion encargada de ejecutar el reporte hacia una Base de Datos.
	Los datos se extraen de ES por lo que tengo que loopear
]]--
function ExecuteReportES (utils, authFilter, prefix, scheduleToRun, executionType)
	LogInfo("[DATABASE:ExecuteReportES] DataBase execution schedule Id '%s', Execution Type: '%s'.(BEGIN)", scheduleToRun.Id, executionType)
	--LogDebug("[DATABASE:ExecuteReportES] scheduleToRun '%s'.", json.encode(scheduleToRun))

	local scheduler = GetSchedulerModule()
	local success, dbConnection = DataBaseIsReady(scheduleToRun)
	local errMsg = {subject = nil, body = nil, reason = nil}
	local now = LuaOrm.Date()
	local userId = utils.GetCurrentLoggedUser()

	local function AddSuccessHistory()
		local data = {type = "text/db", sendType = "DATABASE", executionType = executionType, userId = userId}
		scheduler.add_schedule_historical(utils, scheduleToRun.Id, now, true, data)
	end

	local function AddErrorHistory(data)
		data.sendType = "DATABASE"
		data.executionType = executionType
		data.userId = userId
		scheduler.add_schedule_historical(utils, scheduleToRun.Id, now, false, data)
	end

	local OnError = function(err)
		success = false
		errMsg.subject = "Failed to Export report to DataBase."
		errMsg.body = "Some problem occurred during Exporting to DataBase."
		errMsg.reason = "Failed to Export report to DataBase."

		if type(err) == "string" then
			errMsg.body = errMsg.body .. "\n\nError:\n" .. tostring(err)
			errMsg.reason = tostring(err)
			--LogDebug("---- *** > %s", errMsg.reason)
		end

		LogWarning("[DATABASE:ExecuteReportES] Some problem occurred during Exporting to DataBase. Report: %s", scheduleToRun.Id)

		AddErrorHistory({reason = errMsg.reason})

		return success, errMsg
	end

	if not success then
		local err = dbConnection
		return OnError(err)
	end
	scheduleToRun.resultSize = 5000
	local loops = 0
	scheduleToRun.last_handle = {}
	while true do
		-- Ejecuto el Reporte, contra la app "reports" del repo "agent"
		local result, code, headers, httpStatus, params, name, jsonResult = scheduler.request_run_report(utils, authFilter, prefix, scheduleToRun)
		-- LogDebug("[DATABASE:ExecuteReportES]--1 result: %s", result)
		-- LogDebug("[DATABASE:ExecuteReportES]--2 code: %s", code)
		-- LogDebug("[DATABASE:ExecuteReportES]--3 headers: %s", headers)
		-- LogDebug("[DATABASE:ExecuteReportES]--4 httpStatus: %s", httpStatus)
		-- LogDebug("[DATABASE:ExecuteReportES]--5 params: %s", json.encode(params))
		-- LogDebug("[DATABASE:ExecuteReportES]--6 name: %s", name)
		-- LogDebug("[DATABASE:ExecuteReportES]--7 jsonResult: %s", jsonResult)

		-- Si dio ok Comienza la exportacion a la BD
		if code == 200 then
			local reportResult = json.decode(jsonResult)
			if type(reportResult.result) ~= "table" then
				return OnError("Scheduler result malformed")
			elseif reportResult.result.length < 1 then
				SQL.Disconnect(dbConnection)
				LogInfo("[DATABASE:ExecuteReportES] DataBase execution schedule Id '%s', Execution Type: '%s'.(END)", scheduleToRun.Id, executionType)
				AddSuccessHistory()
				return true
			end
			--LogDebug("[DATABASE:ExecuteReportES]--8 result.length: %s", json.encode(reportResult.result.length))		
			--LogDebug("[DATABASE:ExecuteReportES]--9 handle: %s", json.encode(reportResult.result.handle))		
			-- Ejecuto la exportacion a la BD
			local ok, err = DataBaseStartRun(dbConnection, scheduleToRun, jsonResult, params, utils)
			if not ok then
				SQL.Disconnect(dbConnection)
				return OnError(err)
			end
			scheduleToRun.last_handle = reportResult.result.handle
		else
			SQL.Disconnect(dbConnection)
			success = false
			errMsg.subject = "Failed to execute report "
			errMsg.body = "Some problem occurred during execution of your report "
			errMsg.reason = "Error during execution report. " .. (httpStatus or code or "")

			LogWarning("[DATABASE:ExecuteReport] Some problem occurred during execution of request: %s", httpStatus)

			AddErrorHistory({reason = errMsg.reason, result = result, code = code, headers = headers, httpStatus = httpStatus})
		end
		
		loops = loops + 1
		if loops > 150 then
			SQL.Disconnect(dbConnection)
			return OnError("Too many loops. Cancelling.")
		end
	end
	return success, errMsg
end
