module("LuaOrm.DBMS.MySql", package.seeall)

-- properties
supportsNolock = false
cursorSupportsProperties = true


--
-- printf-like function that also takes care of quoting identifiers (table names, etc)
--
local left_quote = "`"
local right_quote = "`"

function Format (s, ...)
	local index = 1
	local args = {...}
	
	s = s:gsub("(%%.)", function(capture)
		local value = args[index]
		index = index + 1
		if value then
			if capture == "%Q" then
				return left_quote .. tostring(value) .. right_quote
			elseif capture == "%s" then
				return tostring(value)
			elseif capture == "%d" then
				return tonumber(value)
			end
		end
		return nil
	end)
	
	return s
end


function GetTablePrimaryKey(conn, table)
	local rows = {}
	local index = 0
	local sql = "SHOW INDEX FROM `"..table.."` Where Key_name = 'PRIMARY';"
	local cursor, err = conn:execute(sql)
	if not cursor then error(err) end
	local row = cursor:fetch({}, "a")
	while row do
		rows[index] = row["Column_name"]
		index = index + 1
		row = cursor:fetch(row, "a")
	end
	cursor:close()
	return rows
end

function IsForeignKeyViolation(code)
	--
	-- TODO: Esto en MySql no funca igual!!!
	--

	LogDebug("Error code beign checked is:"..tostring(code))
	if (tostring(code) == "547") then
		return true
	end
	return false
end

function IsDuplicateViolation(code)
	--
	-- TODO: Esto en MySql no funca igual!!!
	--
	
	LogDebug("Error code beign checked is:"..tostring(code))
	if (code == 2627) then
		return true
	end
	return false
end


function GetTableMetadata(conn, table)
	local rows = {}
	local metaData = {}
	local sql = "SHOW COLUMNS FROM `"..table.."`;"
	local cursor, err = conn:execute(sql)
	if not cursor then error(err) end
	local row = cursor:fetch({}, "a")
	while row do
		local colinfo = {
			name = row["Field"],
			type = (row["Type"] == "tinyint(1)") and "bit" or string.lower(string.match(row["Type"], "(%a+)")),
			isAutoNumber = (row["Extra"] == "auto_increment"),
			length = tonumber(row["Type"]:match("%((%d+)%)")) or 0,
			nullable = (row["NULL"] == "YES")
		}
		metaData[#metaData + 1] = colinfo
		metaData[colinfo.name] = colinfo
		row = cursor:fetch(row, "a")
	end
	cursor:close()
	return metaData
end


function GetForeignKeysFrom(connection, model, fromModel)
	local rows = {}
	local conn = connection
	local foreignKeys = {}
	--TODO: Verificar que esto este bien
	local sql = "Select table_name, column_name, referenced_table_name, referenced_column_name FROM information_schema.KEY_COLUMN_USAGE Where referenced_table_name = '"..model.tableName.."' and table_name = '"..fromModel.tableName.."';"
	local cursor, err = conn:execute(sql)
	if not cursor then error(err) end
	local row = cursor:fetch({}, "a")
	while row do
		foreignKeys[row["referenced_column_name"]] = row["column_name"]
		row = cursor:fetch(row, "a")
	end
	if next(foreignKeys) == nil then
		foreignKeys = nil
	end
	cursor:close()
	return foreignKeys
end

function GetForeignKeysTo(connection, model, toModel)
	local rows = {}
	local conn = connection
	local foreignKeys = {}
	--TODO: Verificar que esto este bien
	local sql = "Select table_name, column_name, referenced_table_name, referenced_column_name FROM information_schema.KEY_COLUMN_USAGE Where referenced_table_name = '"..toModel.tableName.."' and table_name = '"..model.tableName.."';"
	local cursor, err = conn:execute(sql)
	if not cursor then error(err) end
	local row = cursor:fetch({}, "a")
	while row do
		foreignKeys[row["referenced_column_name"]] = row["column_name"]
		row = cursor:fetch(row, "a")
	end
	if next(foreignKeys) == nil then
		foreignKeys = nil
	end
	cursor:close()
	return foreignKeys
end


function BuildPagedQuery(condition, tableList, fieldList, order, rowsPerPage, Page, hasRelationship)
	local baseRow = rowsPerPage * (Page - 1)
	--local topRow = baseRow + rowsPerPage - 1
	local sql = string.format("SELECT %s FROM %s %s ORDER BY %s LIMIT %s, %s", fieldList, tableList, condition, order, baseRow, rowsPerPage)
	return sql
end

function BuildTotalPagesQuery(condition, tableList, fieldList, order, hasRelationship)
        --do not need order to calculate total rows in this case
        local selectQuery = "SELECT "
        if (hasRelationship) then
                selectQuery = selectQuery .. " DISTINCT "
        end
        local sql = "SELECT COUNT(*) as totalRows FROM (" .. selectQuery .. fieldList .. " FROM " .. tableList .. condition .. ") TableCounter"
        return sql
end


function BeginTransaction (conn)
	--deshabilitamos el autocommit porque queremos meter varias operaciones en 1 unica transaccion
	conn:setautocommit(false)
	return conn:execute("START TRANSACTION;")
end

function Commit (conn)
	local result = {conn:commit()}
	--volvemos a habilitar el autocommit para que tenga el comportamiento esperado de antes
	--o sea: que las operaciones impacten todas al instante
	conn:setautocommit(true)
	return unpack (result)
end

function Rollback (conn)
	local result = {conn:rollback()}
	--idem que el caso anterior
	conn:setautocommit(true)
	return unpack (result)
end
