-- work_time_bank.lua
-- run with ___test_Lua
local dt = require "dt"
local util = require "util"
local json = require "json"
local db = require "database"
local dbtype = "4d" -- "4d" => inside 4D = "plg4d" , outside 4D = "sql4d"
local f = db.fieldTable()

local function isWeekend(num)
	return num == 1 or num == 7
end

local function preference(conn, paramTbl)
	--[[
	{
		"Options": {
			"EndDate": "2014.03.21",
			"NormalWorkTime": 8,
			"Employee": ["AK"]
		}
	}
	]]
	local currentDate = dt.currentDate()
	currentDate = dt.toDateString(currentDate)
	local pref = db.preferenceFromJson(conn, "wpa_tc_WorkTimeBank.json")
	-- util.printTable(pref, "pref - work_time_bank.lua")
	-- util.printTable(paramTbl, "1. paramTbl - work_time_bank.lua")
	if paramTbl == nil then
		paramTbl = {}
	end
	-- util.printTable(paramTbl, "2. paramTbl - work_time_bank.lua")
	if paramTbl.Options ~= nil then
		if paramTbl.Options.EndDate == "0000-00-00" then
			paramTbl.Options.EndDate = currentDate
		end
	else
		paramTbl.Options = {}
	end
	pref.EmployeeArr = paramTbl.Options.Employee or {"PM"} -- {"AK","PM"}
	pref.StartDate = paramTbl.Options.StartDate or "0000-00-00" -- "0000-00-00" --"0000-00-00", "2013-04-01"
	-- print("StartDate: "..pref.StartDate)
	-- if(pref.StartDate == "0000-00-00") then
	-- pref.StartDate = "1990-01-01"
	-- end
	pref.EndDate = paramTbl.Options.EndDate or currentDate
	pref.NormalWorkTime = paramTbl.Options.NormalWorkTime or pref.NormalWorkTime -- 0000.00.00
	pref.WorkNumberArr = pref.WorkNumberArr or {"0002", "0003", "0004"}
	pref.WorkTimeZeroWorkNumberArr = pref.WorkTimeZeroWorkNumberArr or {"05003"}
	pref.WorkTimeVacationWorkNumberArr = pref.WorkTimeVacationWorkNumberArr or {"0002", "0003"}
	pref.WorkTimeBankWorkNumberArr = pref.WorkTimeBankWorkNumberArr or {"0002", "0003"}
	pref.WorkTimeZeroWorkNumberArr = util.invertTable(pref.WorkTimeZeroWorkNumberArr)
	pref.WorkTimeVacationWorkNumberArr = util.invertTable(pref.WorkTimeVacationWorkNumberArr)
	pref.WorkTimeBankWorkNumberArr = util.invertTable(pref.WorkTimeBankWorkNumberArr)
	pref.WorkTimeVacationMinDayWorkTime = pref.WorkTimeVacationMinDayWorkTime or 7.5
	pref.WorkTimeVacationDayHours = pref.WorkTimeVacationDayHours or (100 / 365)

	pref.StartDate = dt.dateParse(pref.StartDate)
	pref.StartDate = dt.toDateString(pref.StartDate) -- pref.StartDate = dt.toString(pref.StartDate)
	pref.EndDate = dt.dateParse(pref.EndDate)
	pref.EndDate = dt.toDateString(pref.EndDate) -- pref.EndDate = dt.toString(pref.EndDate)

	return pref
end

local function createJson(sel)
	local returnJson = {}
	returnJson.ReturnArr = sel
	return json.toJson(returnJson) -- toJsonRaw()
	--[[ {
	"ReturnArr:	[
		{"Employee": "AK", "WorkTimeBank": -5.12},
		{"Employee": "PM", "WorkTimeBank": 12.4}
		]
	}]]
end

local function createWorkTimeBankTable(paramTbl)
	local sel = {}
	local conn = db.connect(dbtype)
	if conn then
		local pref = preference(conn, paramTbl)

		-- get employee
		local selectAll = true
		if pref.EmployeeArr then
			if #pref.EmployeeArr > 0 then
				if pref.EmployeeArr[1] ~= "@" and pref.EmployeeArr[1] ~= "" then
					selectAll = false
				end
			end
		end
		if selectAll then
			db.query(conn, "", f.em_state_, ">=", 0)
		else
			-- db.query(conn, "", f.em_employee_id, "=", pref.EmployeeArr[1])
			db.query(conn, "", f.em_employee_id, "in", pref.EmployeeArr)
		end

		local function wpaSortFunc(a, b)
			if a.wpa_date_ < b.wpa_date_ then
				return true
			else
				return false
			end
		end

		-- print("db.selectionToRecordTable - start")
		local selEm = db.selectionToRecordTable(conn, {f.em_employee_id, f.em_work_time_bank, f.em_work_time_bank_date})
		-- json.printJson("selEm :" ,selEm)

		local checkEm = {}
		for _, recEm in ipairs(selEm) do

			db.query(conn, "", f.wpa_work__position_num_id, "in", pref.WorkNumberArr)
			db.query(conn, "and", f.wpa_employee_id, "=", recEm.em_employee_id)

			local queryStartDateTbl = dt.dateParse(recEm.em_work_time_bank_date)
			if queryStartDateTbl > 0 then
				queryStartDateTbl = dt.daysAdd(queryStartDateTbl, 1)
			end

			local startDate
			local dateTbl
			if pref.StartDate == "0000-00-00" then
				startDate = dt.toDateString(queryStartDateTbl) -- recEm.em_work_time_bank_date
				-- dateTbl = dt.dateParse(startDate)
				-- if dateTbl > 0 then
				--	dateTbl = dt.dayAdd(dateTbl,1)
				-- end
			else
				startDate = pref.StartDate
				-- dateTbl = dt.dateParse(startDate)
			end

			dateTbl = dt.numToTable(queryStartDateTbl) -- dateTbl)
			db.query(conn, "and", f.wpa_date_, ">=", dateTbl)

			local endDate = pref.EndDate
			dateTbl = dt.dateParse(endDate)
			dateTbl = dt.numToTable(dateTbl)
			db.query(conn, "and", f.wpa_date_, "<=", dateTbl)

			local selWpa = db.selectionToRecordTable(conn, {f.wpa_date_, f.wpa_work_time__amount, f.wpa_work__position_num_id})
			table.sort(selWpa, wpaSortFunc)
			-- print("selWpa: "..createJson(selWpa))

			-- collect same date values
			local workTimeTbl = {}
			local vacationWorkTimeTbl = {}
			local periodWorkTimeTbl = {}
			local periodVacationWorkTimeTbl = {}
			local workTimeBankTimeTbl = {}
			local period_workTimeBankTimeTbl = {}
			-- util.printTable(selWpa, "selWpa")
			-- util.printTable(pref, "pref")
			for _, recWpa in ipairs(selWpa) do
				local workTime = recWpa.wpa_work_time__amount
				if pref.WorkTimeZeroWorkNumberArr[recWpa.wpa_work__position_num_id] then
					workTime = 0
				end
				if not workTimeTbl[recWpa.wpa_date_] then
					workTimeTbl[recWpa.wpa_date_] = workTime
					--[[vacationWorkTimeTbl[recWpa.wpa_date_] = 0
					periodVacationWorkTimeTbl[recWpa.wpa_date_] = false
					workTimeBankTimeTbl[recWpa.wpa_date_] = false
					period_workTimeBankTimeTbl[recWpa.wpa_date_] = false]]
				else
					workTimeTbl[recWpa.wpa_date_] = workTimeTbl[recWpa.wpa_date_] + workTime
				end

				if vacationWorkTimeTbl[recWpa.wpa_date_] == nil then
					vacationWorkTimeTbl[recWpa.wpa_date_] = 0
				end
				if periodVacationWorkTimeTbl[recWpa.wpa_date_] == nil then
					periodVacationWorkTimeTbl[recWpa.wpa_date_] = false
				end
				if workTimeBankTimeTbl[recWpa.wpa_date_] == nil then
					workTimeBankTimeTbl[recWpa.wpa_date_] = false
				end
				if period_workTimeBankTimeTbl[recWpa.wpa_date_] == nil then
					period_workTimeBankTimeTbl[recWpa.wpa_date_] = false
				end
				if pref.WorkTimeVacationWorkNumberArr[recWpa.wpa_work__position_num_id] then
					-- util.printTable(recWpa, "recWpa")
					-- util.printTable(vacationWorkTimeTbl, "vacationWorkTimeTbl")
					-- print("vacationWorkTimeTbl[recWpa.wpa_date_]", vacationWorkTimeTbl[recWpa.wpa_date_])
					vacationWorkTimeTbl[recWpa.wpa_date_] = vacationWorkTimeTbl[recWpa.wpa_date_] + recWpa.wpa_work_time__amount
					if recWpa.wpa_date_ >= startDate and recWpa.wpa_date_ <= endDate then -- collect time period values
						if not periodVacationWorkTimeTbl[recWpa.wpa_date_] then
							periodVacationWorkTimeTbl[recWpa.wpa_date_] = recWpa.wpa_work_time__amount
						else
							periodVacationWorkTimeTbl[recWpa.wpa_date_] = periodVacationWorkTimeTbl[recWpa.wpa_date_] + recWpa.wpa_work_time__amount
						end
					end
				else
					vacationWorkTimeTbl[recWpa.wpa_date_] = nil
				end
				-- sets workTimeBank values to true if a specific workNumber is in WorkTimeBankWorkNumberArr as index
				if pref.WorkTimeBankWorkNumberArr[recWpa.wpa_work__position_num_id] then
					workTimeBankTimeTbl[recWpa.wpa_date_] = true -- workTimeBankTimeTbl[recWpa.wpa_date_] + recWpa.wpa_work_time__amount
					if recWpa.wpa_date_ >= startDate and recWpa.wpa_date_ <= endDate then -- time period values
						period_workTimeBankTimeTbl[recWpa.wpa_date_] = true
					else
						period_workTimeBankTimeTbl[recWpa.wpa_date_] = false
					end
				end

				if recWpa.wpa_date_ >= startDate and recWpa.wpa_date_ <= endDate then -- collect time period values
					if not periodWorkTimeTbl[recWpa.wpa_date_] then
						periodWorkTimeTbl[recWpa.wpa_date_] = workTime
					else
						periodWorkTimeTbl[recWpa.wpa_date_] = periodWorkTimeTbl[recWpa.wpa_date_] + workTime
					end
				end

			end
			-- calculate WorkTimeBank values & make return table
			-- print("#workTimeTbl: "..#workTimeTbl)
			local days = 0
			for date_idx, workTime in pairs(workTimeTbl) do
				local i = #sel
				if not checkEm[recEm.em_employee_id] then
					checkEm[recEm.em_employee_id] = 1
					i = i + 1 -- create new element
					sel[i] = {}
					sel[i].Employee = recEm.em_employee_id
					sel[i].WorkTimeBankStartValue = recEm.em_work_time_bank
					sel[i].WorkTimeBank = recEm.em_work_time_bank -- set employee work time bank value to start value
					sel[i].WorkTimeBankTimePeriod = 0
					sel[i].WorkTimeVacation = 0
					sel[i].WorkTimeVacationPeriod = 0
				end

				-- All hours to WorkTimeBank if work is done at week and set in workTimeBankTimeTbl
				local num = dt.dayOfWeek(date_idx)
				if workTimeBankTimeTbl[date_idx] and isWeekend(num) then
					sel[i].WorkTimeBank = sel[i].WorkTimeBank + workTime
				elseif workTimeBankTimeTbl[date_idx] then
					sel[i].WorkTimeBank = sel[i].WorkTimeBank + (workTime - pref.NormalWorkTime)
				end

				-- All hours to periodWorkTimeBank if work is done at week and set in workTimeBankTimeTbl
				if period_workTimeBankTimeTbl[date_idx] and isWeekend(num) then
					if periodWorkTimeTbl[date_idx] then
						sel[i].WorkTimeBankTimePeriod = sel[i].WorkTimeBankTimePeriod + workTime
					end
				elseif period_workTimeBankTimeTbl[date_idx] then
					if periodWorkTimeTbl[date_idx] then
						sel[i].WorkTimeBankTimePeriod = sel[i].WorkTimeBankTimePeriod + (workTime - pref.NormalWorkTime)
					end
				end

				-- WorkTimeVacation is not count, if work is done at weekend
				if num ~= 1 and num ~= 7 then
					if vacationWorkTimeTbl[date_idx] ~= nil and vacationWorkTimeTbl[date_idx] >= pref.WorkTimeVacationMinDayWorkTime then
						-- sel[i].WorkTimeVacation = sel[i].WorkTimeVacation + pref.WorkTimeVacationDayHours -- PM 2025-07-16, if commented then do not show Pekkaset on Kellokortti -screen
						if periodVacationWorkTimeTbl[date_idx] then
							sel[i].WorkTimeVacationPeriod = sel[i].WorkTimeVacationPeriod + pref.WorkTimeVacationDayHours
						end
					end
				end

				days = days + 1 -- for trace
			end
		end
	end
	db.disconnect(conn)

	return sel
end

-- main program

local function calculateWorkTimeBank(paramTbl)
	--[[
	local num, name = dt.dayOfWeek("1","1","2014")
	num, name = dt.dayOfWeek("2013-02-06 00:00:00.000")
	print(num, name)

	local current = dt.currentDate()
	num, name = dt.dayOfWeek(current)
	print(num, name)

	num, name = dt.dayOfWeek(2014, 5, 5)
	print(num, name)

	num, name = dt.dayOfWeek("2014", "5", "5")
	print(num, name)
	]]

	local sel = createWorkTimeBankTable(paramTbl)
	return createJson(sel)
end

return {calculateWorkTimeBank = calculateWorkTimeBank}
