local oo = require "loop.base"
local Utils = require "LuaOrm.Utils"
local math = require "math"
local string = require "string"
local table = require "table"
local pluto = require "pluto"

local assert = assert
local error = error
local luasql = luasql
local pairs = pairs
local ipairs = ipairs
local type = type
local require = require

module("LuaOrm.DBMS.DataSource", oo.class)

-- these are shared between all DataSource instances
local metadata_cache = {}
local metadata_cache_enabled = true

--
-- Members of this class are:
-- m_rawConnection: an LuaSQL connection
-- m_dbmsPlatform: an instance LuaOrm.DBMS used (i.e LuaOrm.DBMS.SqlServer or LuaOrm.DBMS.Sqlite3)
-- m_models: an array of LuaOrm.Model instances
-- m_driver: the LuaSQL driver in use ("inconcertPool" or "sqlite3")
-- m_database: the database we will connect to (mmprodat, historicaldata, etc)


--
-- Connects with data source using a given LuaSQL driver
-- @param driver is the LuaSQL driver to use (inconcertPool or sqlite3)
-- @param connectionString is a string with the data needed to connnect to the database (address, credentials, etc)
-- @param dbms is an instance of the LuaOrm.DBMS to use (i.e LuaOrm.DBMS.SqlServer or LuaOrm.DBMS.Sqlite3)
-- @param dataBase is a table with fields such as user, password, server, port. Stuff that is needed by each driver to connect.
-- @returns true or false plus an error string
function InitRdbmsLayer(self, driver, connectionString, dbms, config)
	assert(type(driver) == "string", "'driver' must be a string")
	assert(type(connectionString) == "string", "'connectionString' must be a string")
	
	local database = connectionString:match("database[%s]*=[%s]*([^;]+)")
	database = database or connectionString	-- SQLite3 does not use connection strings in the same way as odbc
	database = database:lower()
	
	config = config or {}
	
	local luasql = require ("luasql." .. driver)
	local env = luasql[driver]()
	
	local conn, err
	if driver == "mysql" then
		--al driver de luasql.mysql se le tiene que invocar a la funcion connect con mas parametros
		--si se quiere especificar el server, el port, etc
		--no acepta una connection string con dicha configuracion
		conn, err = env:connect(config.database, config.uid, config.pwd, config.server, config.port)
		if conn then
			conn:execute("SET wait_timeout = 31536000;")
		end
	else
		conn, err = env:connect(connectionString, config.uid, config.pwd)
	end
	if not conn then
		return false, err
	end
	self.m_rawConnection = conn
	self.m_dbmsPlatform = dbms
	self.m_models = {}
	self.m_driver = driver
	self.m_database = database	-- i.e. mmprodat or historicaldata
	
	self.Format = dbms.Format	-- we push up this function
	
	if not metadata_cache[database] then
		metadata_cache[database] = {}
	end
	self.m_metadata_cache = metadata_cache[database]
	return true
end

--
-- Adds a new model to the list of the models supported by this data source
-- @param model is an instance of LuaOrm.Model
function AddModel(self, model)
	if not self.m_models then
		error("DataSource not initialized. Call InitRdbmsLayer first")
	end
	self.m_models[model.tableName] = model
end

--
-- Validates model relationships in this datasource
function Validate(self)
	for k, v in pairs(self.m_models) do
		v:ValidateAssociations(self.m_models)
	end
end

--
-- Returns an array of column names of a given table, formatted as [TableName].[ColumnName]
-- @param tableName is the name of the table
-- @param colInfo is a table with the metadata
function GetColumnNames(self, tableName, colInfo)
	local colNames = {}
	for i, col in ipairs(colInfo) do
		colNames[i] = self.Format("%Q.%Q", tableName, col.name)
	end
	return colNames
end

--
-- Returns the primary key of a given table (platform specific)
-- @param tableName the name of the table
function GetTablePrimaryKey(self, tableName)
	if (self.m_dbmsPlatform and self.m_rawConnection) then
		local cache_key = "GetTablePrimaryKey_" .. tableName
		if metadata_cache_enabled and self.m_metadata_cache[cache_key] then
			return self.m_metadata_cache[cache_key]
		end
		local data = self.m_dbmsPlatform.GetTablePrimaryKey(self.m_rawConnection, tableName)
		self.m_metadata_cache[cache_key] = data
		return data
	end
	return nil
end

--
-- Returns the column metadata for a given table (platform specific)
-- @param tableName the name of the table
function GetTableMetadata(self, tableName)
	if (self.m_dbmsPlatform and self.m_rawConnection) then
		local cache_key = "GetTableMetadata_" .. tableName
		if metadata_cache_enabled and self.m_metadata_cache[cache_key] then
			return self.m_metadata_cache[cache_key]
		end
		local data = self.m_dbmsPlatform.GetTableMetadata(self.m_rawConnection, tableName)
		self.m_metadata_cache[cache_key] = data
		return data
	end
	return nil
end

--
-- Obtains table incoming foreign keys from 'fromModel' to 'toModel' (platform specific). 
-- @param toModel an instance of LuaOrm.Model
-- @param fromModel an instance of LuaOrm.Model
function GetForeignKeysFrom(self, toModel, fromModel)
	if (self.m_dbmsPlatform and self.m_rawConnection) then
		local cache_key = "GetForeignKeysFrom_" .. toModel.tableName .. "_" .. fromModel.tableName
		if metadata_cache_enabled and self.m_metadata_cache[cache_key] then
			return self.m_metadata_cache[cache_key]
		end
		local data = self.m_dbmsPlatform.GetForeignKeysFrom(self.m_rawConnection, toModel, fromModel)
		self.m_metadata_cache[cache_key] = data
		return data
	end
	return nil
end

--
-- Obtains table outgoing foreign keys from 'fromModel' to 'toModel' (platform specific). 
-- @param toModel an instance of LuaOrm.Model
-- @param fromModel an instance of LuaOrm.Model
function GetForeignKeysTo(self, fromModel, toModel)
	if (self.m_dbmsPlatform and self.m_rawConnection) then
		local cache_key = "GetForeignKeysTo_" .. fromModel.tableName .. "_" .. toModel.tableName
		if metadata_cache_enabled and self.m_metadata_cache[cache_key] then
			return self.m_metadata_cache[cache_key]
		end
		local data = self.m_dbmsPlatform.GetForeignKeysTo(self.m_rawConnection, fromModel, toModel)
		self.m_metadata_cache[cache_key] = data
		return data
	end
	return nil
end

--
-- Returns a string with the fields to project for this model (ie. field1, field2, field3)
-- @param model the model to get the fields of (an instance of LuaOrm.Model)
function GetProjectedFields(self, model)
	return table.concat(self:GetColumnNames(model.tableName, model.columnInfo), ", ")
end

-- 
-- Obtains a row from this model after executing the given query.
-- @param model is bla bla (an instance of LuaOrm.Model)
-- @param sql the query to execute
-- @return a table with the resulting row
function FetchRow(self, model, sql)
	local cursor, err = self.m_rawConnection:execute(sql)
	if not cursor then return nil end
	
	if model.wantMilliseconds and self.m_dbmsPlatform.cursorSupportsProperties then 
		cursor.wantMilliseconds = model.wantMilliseconds
	end
	
	local row = cursor:fetch({}, "a")
	cursor:close()
	if row then
		Utils.ConvertTypes(row, model.columnInfo, self.m_driver)
	end
	return row
end

-- 
-- Obtains 'count' rows from this model after executing the given query.
-- @param model is bla bla (an instance of LuaOrm.Model)
-- @param sql the query to execute
-- @param count how many rows to return
-- @return an array of tables with the resulting rows
function FetchRows(self, model, sql, count)
	local rows = {}
	local cursor, err = self.m_rawConnection:execute(sql)
	if not cursor then return nil end
	
	if model.wantMilliseconds and self.m_dbmsPlatform.cursorSupportsProperties then 
		cursor.wantMilliseconds = model.wantMilliseconds
	end
	
	local row, fetched = cursor:fetch({}, "a"), 1
	while row and (not count or fetched <= count) do
		Utils.ConvertTypes(row, model.columnInfo, self.m_driver)
		rows[#rows + 1] = row
		row, fetched = cursor:fetch({}, "a"), fetched + 1
	end
	cursor:close()
	cursor = nil
	collectgarbage("step", 1)	-- drive forward the garbage collector
	return rows
end

--
-- Escapes a given value according to its type and the current DBMS rules. For instance, quotes in strings, etc.
-- @param model an instance of the LuaOrm.Model the field belogs to
-- @param field the field whose value we want to escape
-- @param value the value to escape
-- @return the escaped value
function Escape(self, model, field, value) 
	return Utils.EscapeValue(model.dataSource, model.columnInfo[field], value)
end

--
-- Builds a condition (with a lot of magic)
-- @param model an instance of the LuaOrm.Model
-- @param condition XXX
-- @param args XXX
-- @return return condition, table_list, field_list, hasRelationship
function BuildCondition(self, model, condition, args)
	local i = 0
	local relationshipsInUse = {}
	local hasRelationship
	args = args or {}
	condition = condition or ""
	if type(condition) == "table" then
		args = condition
		condition = ""
	end
	if condition ~= "" then
		condition = " where " ..
		string.gsub(condition, "([%w_%.]+)%s*([%a<>=]+)%s*%?",
			function (field, op)
				i = i + 1
				local op = string.lower(op)
				local relationship, fieldname = string.match(field, "([%w_]+)%.([%w_]+)")
				if (relationship) then
					hasRelationship = true
					local association = model:GetRelationship(relationship)
					if (association) then
						relationshipsInUse[relationship] = association
						return association:GetRelatedCondition(fieldname, op, args[i])
					else
						--condition refers to an unexistent relationship
						error("Condition refers to an unexistent relationship: ".. relationship)
					end
				end
				local column = model.columnInfo[field]
				if not column then
					error(string.format("Condition refers to unexistent field '%s' on table '%s'", field, model.tableName))
				end
				if not args[i] then
					return ""
				elseif type(args[i]) == "table" then
					local values = {}
					for j, value in ipairs(args[i]) do
						values[#values + 1] = field .. " " .. op .. " " .. Utils.EscapeValue(model.dataSource, column, value)
					end
					return "(" .. table.concat(values, " or ") .. ")"
				elseif op == "is" then
					return self.Format("%Q.%Q %s %s", model.tableName, field, op , args[i])
				elseif op == "like" then
					local escapedValue = Utils.EscapeTypedValue(model.dataSource, "string", "%"..args[i].."%")
					return self.Format("%Q.%Q %s %s", model.tableName, field, op, escapedValue)
				else
					return self.Format("%Q.%Q %s %s", model.tableName, field, op, Utils.EscapeValue(model.dataSource, column, args[i]))
				end
			end --ends gsub function
		) --ends gsub
	end
	local field_list
	local table_list
	if args.inject then
		local inject_condition
		field_list, table_list, inject_condition = self:BuildInject(args.inject, model)
		if condition == "" then
			condition = " where " .. inject_condition
		else
			condition = condition .. " and " .. inject_condition
		end
	else
		field_list = self:GetProjectedFields(model)
		table_list = self.Format("%Q", model.tableName) .. (self.m_dbmsPlatform.supportsNolock and " (nolock) " or "")
		for k, v in pairs(relationshipsInUse) do
			table_list = table_list .. self.Format(", %Q %s ", v:GetRelatedTableName(), self.m_dbmsPlatform.supportsNolock and " (nolock) " or "")
			condition = condition .. " AND " .. v:GetJoinExpression()
		end
	end
	return condition, table_list, field_list, hasRelationship
end

--
-- Builds a query for a given model, condition, args
-- @param model a LuaOrm.Model instance
-- @param condition XXX
-- @param args an optional table
-- @return a string with a valid SQL query (select foo from blah where etc etc)
function BuildQuery(self, model, condition, args)
	local order = ""
	if args and args.order then order = " order by " .. args.order end
	local condition, table_list, field_list, hasRelationship = self:BuildCondition(model, condition, args)
	local selectQuery = "select "
	if (hasRelationship) then
		selectQuery = selectQuery .. " DISTINCT "
	end
	return selectQuery .. field_list .. " from " .. table_list .. condition .. order
end

-- 
-- Executes an 'Insert' statement.
-- @param tableName the name of the table to insert into
-- @param columns an array of column names
-- @param values the array of values to be inserted
-- @return true or false plus an error message
function Insert(self, tableName, columns, values)
	local sql = self.Format("insert into %Q (%s) values (%s)", tableName, table.concat(columns, ", "), table.concat(values, ", "))
	local ok, err = self.m_rawConnection:execute(sql)
	if not ok then
		return false, err
	end
	return true
end

-- 
-- Executes an 'Update' statement.
-- @param tableName the name of the table to update into
-- @param updates an array with the new values
-- @param whereCondition the filtering condition of the query
-- @return true or false plus an error message
function Update(self, tableName, updates, whereCondition)
	local sql = self.Format("update %Q set %s %s", tableName, table.concat(updates, ", "), whereCondition)
	local ok, err = self.m_rawConnection:execute(sql)
	if not ok then 
		return false, err
	end
	return true
end

-- 
-- Executes an 'Delete' statement.
-- @param tableName the name of the table to update into
-- @param whereCondition the filtering condition of the query
-- @return true or false plus an error message
function Delete(self, tableName, whereCondition)
	local sql = self.Format("delete from %Q %s", tableName, whereCondition)
	local ok, err = self.m_rawConnection:execute(sql)
	if not ok then
		return false, err
	end
	return true
end

---
-- Transactions are delegated to the DBMS instance
function BeginTransaction (self)
	return self.m_dbmsPlatform.BeginTransaction(self.m_rawConnection)
end

function CommitTransaction (self)
	return self.m_dbmsPlatform.Commit(self.m_rawConnection)
end

function RollbackTransaction (self)
	return self.m_dbmsPlatform.Rollback(self.m_rawConnection)
end

--
-- Builds paged query
-- @return a string with the query
function BuildPagedQuery(self, condition, tableList, fieldList, order, rowsPerPage, page, hasRelationship)
	return self.m_dbmsPlatform.BuildPagedQuery(condition, tableList, fieldList, order, rowsPerPage, page, hasRelationship)
end

--
-- Computes how many pages are there in a given paged query
-- @return the number of pages
function GetPagesInQuery(self, condition, tableList, fieldList, order, rowsPerPage, hasRelationship)
	local sql = self.m_dbmsPlatform.BuildTotalPagesQuery(condition, tableList, fieldList, order, hasRelationship)
	local cursor, err = self.m_rawConnection:execute(sql)
	if not cursor then return 0 end
	local row = cursor:fetch({}, "a")
	cursor:close()
	if (row) then
		local totalRows = row["totalRows"]
		return math.ceil(totalRows / rowsPerPage), totalRows
	end
	return 0
end


--
-- Cache related functions

-- 
-- Call this if you want to never retrieve stuff from the metadata cache
-- (used mainly when we're building the cache file)
function DisallowRetrieveFromMetadataCache()
	metadata_cache_enabled = false
end

--
-- Loads the cached metadata from 'data', using pluto
function LoadMetadataCache(data)
	assert(data)
	metadata_cache = pluto.unpersist({}, data)
end

--
-- Serializes the metadata cache to a suitable form, using pluto
function PersistMetadataCache()
	return pluto.persist({}, metadata_cache)
end