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

-- properties
supportsNolock = true
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 = "sp_primary_keys_rowset '"..table.."'"
	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)
	LogDebug("Error code beign checked is:"..tostring(code))
	if (tostring(code) == "547") then
		return true
	end
	return false
end

function IsDuplicateViolation(code)
	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 = "sp_columns '"..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["COLUMN_NAME"],
			type = string.lower(string.match(row["TYPE_NAME"], "(%a+)")),
			isAutoNumber = ((string.match(row["TYPE_NAME"], "%a+%p+%s+(%a+)") == "identity") and true) or false,
			length = row.LENGTH,
			nullable = (row["NULLABLE"] == 1)
		}
		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 = {}
	local sql = "sp_fkeys '"..model.tableName.."'"
	local cursor, err = conn:execute(sql)
	if not cursor then error(err) end
	local row = cursor:fetch({}, "a")
	while row do
		if (row["FKTABLE_NAME"] == fromModel.tableName) then
			foreignKeys[row["PKCOLUMN_NAME"]] = row["FKCOLUMN_NAME"]
		end
		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 = {}
	local sql = "sp_fkeys NULL, NULL, NULL, '"..model.tableName.."'"
	local cursor, err = conn:execute(sql)
	if not cursor then error(err) end
	local row = cursor:fetch({}, "a")
	while row do
		if (row["PKTABLE_NAME"] == toModel.tableName) then
			foreignKeys[row["PKCOLUMN_NAME"]] = row["FKCOLUMN_NAME"]
		end
		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) + 1
	local topRow = baseRow + rowsPerPage - 1
	local sql = "WITH pagedRequest AS ( SELECT ROW_NUMBER() OVER (ORDER BY "..order..") AS m_RowNumber,"..fieldList.." FROM "..tableList.." "..condition..")"
	local untabledFields = {}
	local untabledOrderField = ""
	local untabledOrderDirection = ""
	string.gsub(order, "(%[[%w_%.]+%])%.(%[[%w_%.]+%])%s*(%w*)", 
		function (table, field, direction) 
			untabledOrderField = field
			if (direction) then
				untabledOrderDirection = direction
			end
		end)
	string.gsub(fieldList, "(%[[%w_%.]+%])%.(%[[%w_%.]+%])",
		function (table, field) 
			untabledFields[#untabledFields + 1] = field
		end)
	local orderBy = ""
	if (untabledOrderField ~= "") then
		orderBy = " ORDER BY "..untabledOrderField.. " " .. untabledOrderDirection
	end
	local selectQuery = table.concat(untabledFields, ",").." FROM pagedRequest"
	if (hasRelationship) then
		sql = string.format([[ %s SELECT * FROM ( 
										SELECT ROW_NUMBER() OVER (%s) AS m_RowNumber, * 
										FROM ( SELECT DISTINCT %s 
										) joinedTabled
									) numberedTable 
									WHERE m_RowNumber BETWEEN %s AND %s %s]], sql, orderBy, selectQuery, baseRow, topRow, orderBy)
	else
		sql = string.format([[ %s SELECT %s WHERE m_RowNumber BETWEEN %s AND %s %s]], sql, selectQuery, baseRow, topRow, orderBy)
	end
	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
	return "SELECT COUNT(*) as totalRows FROM (" .. selectQuery .. fieldList .. " FROM " .. tableList .. condition .. ") TableCounter"
end


function BeginTransaction (conn)
	return conn:begintrans()
end

function Commit (conn)
	return conn:commit()
end

function Rollback (conn)
	return conn:rollback()
end
