-- Table of departments which contain vehicles.
departments = { }

-- Table indexed on fleet_id which references specific vehicles in departments.
vehicles = { }

-- Table of vehicle names by model name vehicleNames[model] = { short = "2016 FPIU", long = "2016 Ford Police Interceptor Utility" }
vehicleNames =  { }

-- Stores abbrev to index municipalityIndices['DPD'] = 1, therefore departments['MP'][1] = DPD data.
municipalityIndices = { }

-- Stores list of vehicle models to be blocked by vMenu, derived from vehicles description table { modelName, ...}
blockedVehicles = { }

-- Stores list of vehicle model hashes to be pruned if spawned outside VMS's controls.
blockedVehicleHashes = { }

-- Stores out of service data.
outOfService = { }

-- Stores R&T and other rewards members to bypass limits; rewardsBypass[discordID] = { models ...}
rewardsBypass = { }

---------------------------------------------------------------------------------------------------
CreateThread(function()
	StartInitializing()
	BuildOOSTable()
	RemoveExpiredReward()
	BuildRewardsBypassTable()
	
	while true do
		Wait(1000)
		RemoveExpiredSqlOosRecords()
		UpdateSqlOosCounts()
		ProcessMaintenanceVehicles()
		Wait(900000)
	end
end)

-- Database event handler for updating SQL and replication locally.
RegisterServerEvent('vms:SubmitNewOOS')
AddEventHandler('vms:SubmitNewOOS', function(oosData)
	InsertNewOOS(oosData)
end)

---------------------------------------------------------------------------------------------------

function BuildOOSTable()
	local query = [[
		SELECT
			vo.oos_id AS oosId,
			vm.full_name AS municipalityFullName,
			vm.municipality_id AS municipality,
			vv.vehicle_id AS model,
			vn.long_name AS modelFullName,
			vv.unmarked AS unmarked,
			vo.category AS category,
			vo.operator AS operator,
			vo.reporter AS reporter,
			DATE_FORMAT(vo.record_date, "%c/%d/%y %H:%i") AS recordDateTime,
			DATE_FORMAT(vo.expire_date, "%c/%d/%y %H:%i") AS returnDateTime,
			vo.notes AS notes,
			vo.status AS status
		FROM
			vms_2_oos vo
		JOIN
			vms_2_vehicles vv ON vo.fleet_id = vv.fleet_id
		JOIN
			vms_2_municipalities vm ON vv.municipality_id = vm.municipality_id
		JOIN
			vms_2_vehiclenames vn ON vv.vehicle_id = vn.vehicle_id;
	]]
	
	log.info('Selecting OOS data. (Database)')
	MySQL.query(query, {}, function(result)
		if result then
			local newOOSData = { }
			for _, oosData in pairs(result) do
				table.insert(newOOSData, 
					{ 
						oosId = oosData.oosId, 
						municipalityFullName = oosData.municipalityFullName, 
						modelFullName = string.format("%s (%s)", oosData.modelFullName, oosData.model),
						unmarked = oosData.unmarked, 
						category = oosData.category, 
						operator = oosData.operator,
						reporter = oosData.reporter, 
						recordDateTime = oosData.recordDateTime, 
						returnDateTime = oosData.returnDateTime, 
						notes = oosData.notes,
						status = oosData.status,						
					}
				)
			end
			outOfService = newOOSData
		end
	end)
	log.info('Finished SQL OOS select. (Database)')
end

---------------------------------------------------------------------------------------------------
function InsertNewOOS(oosData)
    local insertQuery = [[
        -- Insert into vms_oos
        INSERT INTO vms_2_oos 
            (fleet_id, category, operator, reporter, record_date, expire_date, notes, status)
        VALUES
            (?, ?, ?, ?, STR_TO_DATE(?, "%m/%d/%Y %H:%i"), STR_TO_DATE(?, "%m/%d/%Y %H:%i"), ?, ?);
    ]]

    log.info('Storing new OOS fleet id:', oosData.fleetId, 'category:', oosData.category, '(Database)')

    -- Insert the OOS record
    MySQL.query(insertQuery,
        {
            oosData.fleetId,
            oosData.category, 
            oosData.operator, 
            oosData.reporter, 
            oosData.recordDateTime, 
            oosData.returnDateTime, 
            oosData.notes, 
            true
        },
        function(returnData) 
            -- Check if the inserted category is 'Maintenance'
            if oosData.category == 'Maintenance' then
                -- Subtract service hours conditionally
                local updateQuery = [[
                    UPDATE vms_2_vehicles
                    SET service_hours = GREATEST(service_hours - 600, 0)
                    WHERE fleet_id = ?;
                ]]

                MySQL.query(updateQuery, { oosData.fleetId }, function(updateResult)
                    if updateResult.affectedRows > 0 then
                        log.info('Reducing service hours OOS is Maintenance category. (Database)')
                    end
                end)
            end

            -- Update counts in vehicles SQL table after insertion.
            UpdateSqlOosCounts()
            -- Update local copy of OOS data.
            BuildOOSTable()
        end)

    log.info('Finished SQL OOS insert. (Database)')
end



---------------------------------------------------------------------------------------------------
function StartInitializing()
	local query = [[
		SELECT * FROM vms_2_municipalities ORDER BY sort_order;
	]]
	
	log.info('Building municipality tables. (Initializing)')
	--[[ Build department municipality tables. ]]
	MySQL.query(query, {}, function(result)
		if result then
			for _, municipality in pairs(result) do
				if departments[municipality.department] == nil then
					departments[municipality.department] = { }
				end
				table.insert(departments[municipality.department], 
					{ 
						abbrevName = municipality.municipality_id, 
						fullName = municipality.full_name, 
						image = municipality.image,
						markedVehicles = { },
						unmarkedVehicles = { },
						count = 0
					}
				)
				municipalityIndices[municipality.municipality_id] = #departments[municipality.department]
			end
		end
		log.info('Finished municipality tables. (Initializing)')
		BuildVehicleTables()
		BuildBlockedTable()
	end)
end

---------------------------------------------------------------------------------------------------
-- Builds main vehicle table, also creates a reference table to look up vehicles by ID.
--	departments[] contains nested tables department>municipality>municipality vehicles & attributes.
--	municipalityIndices[] stores index within department to locate municipality by abbreviation without iteration.
--	vehicles[] contains pointers to departments[] vehicles, for direct access and update without iteration.
--	vehicleNames[] contains short and long names for vehicles by model.
function BuildVehicleTables()
	local query = [[
		SELECT
			vv.fleet_id,
			vv.municipality_id,
			vm.department,
			vv.vehicle_id,
			vn.short_name,
			vn.long_name,
			vv.max_count,
			vv.oos_count,
			vv.init_livery,
			vv.image,
			vv.restricted,
			vv.unmarked,
			vv.sort_order
		FROM 
			vms_2_vehicles vv
		JOIN 
			vms_2_vehiclenames vn ON vv.vehicle_id = vn.vehicle_id
		JOIN 
			vms_2_municipalities vm ON vv.municipality_id = vm.municipality_id
		ORDER BY 
			vv.municipality_id, vv.unmarked, vv.sort_order;
	]]
	
	log.info('Building vehicle tables. (Initializing)')
	--[[ Build vehicle tables. ]]
	MySQL.query(query, {}, function(result)
		if result then
			for _, vehicle in pairs(result) do
				local municipalityIndex = municipalityIndices[vehicle.municipality_id]
				
				if municipalityIndex then
					local newVehicle = {
							fleetId 		= vehicle.fleet_id,
							department 		= vehicle.department,
							municipality 	= vehicle.municipality_id,
							model 			= vehicle.vehicle_id, 
							current 		= 0,
							max 			= vehicle.max_count - vehicle.oos_count,
							oos 			= vehicle.oos_count,
							initLivery 		= vehicle.init_livery, 
							image 			= vehicle.image,
							abbrevName 		= vehicle.short_name,
							fullName 		= vehicle.long_name,
							restricted 		= vehicle.restricted,
							unmarked 		= vehicle.unmarked,
					}
					
					vehicles[vehicle.fleet_id] = newVehicle
					
					if vehicle.unmarked then
						table.insert(departments[vehicle.department][municipalityIndex].unmarkedVehicles, newVehicle)
					else
						table.insert(departments[vehicle.department][municipalityIndex].markedVehicles, newVehicle)
					end
					
					-- Store vehicle names
					if vehicleNames[vehicle.vehicle_id] == nil then
						vehicleNames[vehicle.vehicle_id] = { short = vehicle.short_name, long = vehicle.long_name }
					end
				else
					log.error('Failed to locate municipality index of', vehicle.municipality_id)
				end
			end
		end
		log.info('Finished vehicle tables. (Initializing)')
	end)
end

---------------------------------------------------------------------------------------------------
-- Calculates and sets vehicles oos_count in SQL based on active OOS's in vms_2_oos sql.
function UpdateSqlOosCounts()
	local query = [[
		UPDATE vms_2_vehicles AS v
		JOIN (
			SELECT vms_2_vehicles.fleet_id, vms_2_vehicles.oos_count AS vms_vehicles_oos_count, COALESCE(oos_counts.oos_count, 0) AS calculated_oos_count
			FROM vms_2_vehicles
			LEFT JOIN (
				SELECT fleet_id, COUNT(*) AS oos_count
				FROM vms_2_oos
				WHERE status = TRUE
				GROUP BY fleet_id
			) AS oos_counts ON vms_2_vehicles.fleet_id = oos_counts.fleet_id
		) AS counts ON v.fleet_id = counts.fleet_id
		SET v.oos_count = counts.calculated_oos_count
		WHERE v.oos_count <> counts.calculated_oos_count;
	]]
	
	log.info('Updating OOS counts. (Database)')
	--[[ Build table of vMenu restricted model names. ]]
	MySQL.query(query, {}, function(result)
		if result.affectedRows > 0 then
			-- Change detected, update 
			ValidateAndUpdateLocalOosCounts()
		end
		log.info('Finished updating OOS counts. (Database)')
	end)
end

---------------------------------------------------------------------------------------------------
-- Mark vehicles over service hour limit OOS, reduce service hours while being serviced.
function ProcessMaintenanceVehicles()
    -- Insert OOS record
    local insertQuery = [[
        INSERT INTO vms_2_oos (fleet_id, category, operator, reporter, record_date, expire_date, notes, status)
        SELECT
            v.fleet_id,
            'Maintenance' AS category,
            '-' AS operator,
            'Fleet Services' AS reporter,
            NOW() AS record_date,
            DATE_ADD(NOW(), INTERVAL 2 HOUR) AS expire_date,
            'Scheduled Maintenance' AS notes,
            TRUE AS status
        FROM vms_2_vehicles AS v
        WHERE
            v.service_hours > (v.max_count * 600)
            AND NOT EXISTS (
                SELECT 1
                FROM vms_2_oos AS o
                WHERE o.fleet_id = v.fleet_id
                AND o.status = TRUE
            )
        LIMIT 1;
    ]]

    -- Execute the INSERT query
    MySQL.query(insertQuery, {}, function(result)
        if result.affectedRows > 0 then
            local updateQuery = [[
                UPDATE vms_2_vehicles AS v
                SET service_hours = GREATEST(v.service_hours - 600, 0)
                WHERE EXISTS (
                    SELECT 1
                    FROM vms_2_vehicles AS v2
                    WHERE v2.service_hours > (v2.max_count * 600)
                    AND v2.fleet_id = v.fleet_id
                );
            ]]

            MySQL.query(updateQuery, {}, function(updateResult)
                if updateResult.affectedRows > 0 then
                    log.info('Marked vehicle OOS, maintenance interval surpassed. (Database)')
					ValidateAndUpdateLocalOosCounts()
                end
            end)
        end
    end)
end

---------------------------------------------------------------------------------------------------
-- Update service hours since maintenance performed.
function UpdateServiceHours(data)
	local query = [[
		UPDATE vms_2_vehicles AS v
		SET service_hours = service_hours + ?
		WHERE fleet_id = ?;
	]]
	
	MySQL.query(query, {data.elapsedHours, data.fleetId}, function(result)
		if result.affectedRows > 0 then
			log.info('Updated service hours.', 	data.fleetId, data.elapsedHours, '(Database)')
		end
	end)
end

---------------------------------------------------------------------------------------------------
-- Check if OOS expire date has passed. Sproc updates vms_2_vehicles automatically.
function RemoveExpiredSqlOosRecords()
	local query = [[
		UPDATE vms_2_oos
		SET status = FALSE
		WHERE expire_date < NOW() AND status = TRUE;
	]]
	
	log.info('Update statuses of OOS records. (Database)')
	MySQL.query(query, {}, function(result)
		if result.affectedRows > 0 then
			-- OOS expired, update vehicles table immediately.
			ValidateAndUpdateLocalOosCounts()
		end
		log.info('Finished updating statuses OOS counts. (Database)')
	end)
end

---------------------------------------------------------------------------------------------------
-- If sql oos_counts do not match local vehicles tables, update them.
function ValidateAndUpdateLocalOosCounts()
	local query = [[
		SELECT fleet_id, max_count, oos_count
		FROM vms_2_vehicles;
	]]
	
	log.info('Checking for oos_count discrepancies in local vs sql. (Database)')
	MySQL.query(query, {}, function(result)
		for _, sqlRow in ipairs(result) do
			local vehicle = vehicles[sqlRow.fleet_id]
			if vehicle then
				if vehicle.oos ~= sqlRow.oos_count then
					vehicle.oos = sqlRow.oos_count
					vehicle.max = math.max(sqlRow.max_count - sqlRow.oos_count, 0)
					log.info('Updating local oos count on', vehicle.model, 'with fleet_id', vehicle.fleetId, 'new max', vehicle.max)
				end
			else
				log.warn('Unable to locate vehicle', sqlRow.fleet_id, '. oos count update failed.')
			end
		end
		log.info('Finished oos_count validation. (Database)')
	end)
end

---------------------------------------------------------------------------------------------------
-- Creates list of vehicle models from vehiclenames to prevent spawning in vMenu.
function BuildBlockedTable()
	local query = [[
		SELECT vehicle_id FROM vms_2_vehiclenames;
	]]
	
	log.info('Building blockedVehicles tables. (Initializing)')
	--[[ Build table of vMenu restricted model names. ]]
	MySQL.query(query, {}, function(result)
		if result then
			for _, model in pairs(result) do
				table.insert(blockedVehicles, model.vehicle_id)
				blockedVehicleHashes[GetHashKey(model.vehicle_id)] = true
			end
		end
		log.info('Finished blockedVehicles tables. (Initializing)')
	end)
end

---------------------------------------------------------------------------------------------------
-- Creates table of users who can bypass limits based on discordId-fleetId.
function BuildRewardsBypassTable()
	local query = [[
		SELECT * FROM vms_2_rewards;
	]]
	
	log.info('Building rewards tables. (Initializing)')
	--[[ Build table of vMenu restricted model names. ]]
	MySQL.query(query, {}, function(result)
		if result then
			for _, reward in pairs(result) do
				-- Create table to insert all rewards fleetIds
				if rewardsBypass[reward.discord] == nil then
					rewardsBypass[reward.discord] = { }
				end
				
				table.insert(rewardsBypass[reward.discord], reward.model)
				log.warn('Storing bypass on', reward.model, 'for', reward.member_name, '(Initializing)')
			end
		end
		log.info('Finished rewards tables. (Initializing)')
	end)
end

---------------------------------------------------------------------------------------------------
-- If rewards date has passed remove rewards record.
function RemoveExpiredReward()
	local query = [[
		DELETE FROM vms_2_rewards
		WHERE expire_date < CURDATE();
	]]
	
	log.info('Removing expired rewards. (Initializing)')
	MySQL.query(query, {}, function(result)
		if result.affectedRows > 0 then
			log.info('Removed', result.affectedRows, 'expired rewards records. (Initializing)')
		end
		log.info('Finished removing expired rewards. (Initializing)')
	end)
end