Systems United Navy: Roster Linter

linter.coffee

###
Roster Linter
Created by Michael "NightExcessive" Johnson
Released under the MIT License
###

PublicCache = CacheService.getPublicCache()

class StringUtil
	@isAlphanumeric: (char) ->
		(char >= 'A' and char <= 'Z') or
		(char >= 'a' and char <= 'z') or
		StringUtil.isDigit(char)

	@isDigit: (char) ->
		char >= '0' and char <= '9'


class SpreadsheetUtil
	@normalizeHeaders: (headers) ->
		SpreadsheetUtil.normalizeHeader header for header in headers

	@normalizeHeader: (header) ->
		if !header?
			throw 'SpreadsheetUtil.normalizeHeader requires a header argument.'

		key = ""
		upperCase = false

		for letter in header
			if letter == " " and key.length > 0
				upperCase = true
				continue

			continue if not StringUtil.isAlphanumeric letter
			continue if key.length == 0 and StringUtil.isDigit letter

			if upperCase
				upperCase = false
				key += letter.toUpperCase()
			else
				key += letter.toLowerCase()

		return key


	@getHeaders: (sheet, headerRow=1, firstColumn=1, lastColumn=sheet.getLastColumn()) ->
		headersRange = sheet.getRange(headerRow, firstColumn, 1, lastColumn-firstColumn+1)
		headers = headersRange.getValues()[0]
		headersNormalized = SpreadsheetUtil.normalizeHeaders headers

		headerKeys = {}
		headerKeys[header] = i for header, i in headersNormalized

		headersNormalized.keys = headerKeys
		return headersNormalized

	@isCellEmpty: (data) ->
		return !data? or (typeof data == "string" and data.length <= 0)

	@getObjects: (data, keys) ->
		for row in data
			object = {}

			for cell, cellIndex in row
				continue if !keys[cellIndex]?
				continue if SpreadsheetUtil.isCellEmpty(cell)
				object[keys[cellIndex]] = cell

			object

	@getRowsData: (sheet, range, headerRow=1) ->
		range ?= sheet.getRange(headerRow+1, 1, sheet.getLastRow() - headerRow+1, sheet.getLastColumn())
		headers = SpreadsheetUtil.getHeaders(sheet, headerRow, range.getColumn(), range.getLastColumn())

		SpreadsheetUtil.getObjects(range.getValues(), headers)


class RSIProfile
	constructor: (@name) ->
		@flags = {}
		@orgs = {}

	@cacheTime = 6*60*60 # 6 hours in seconds

	@ourOrg = 'SUN'
	@ourOrgLower = @ourOrg.toLowerCase()

	@flags =
		NOT_FOUND: 'NOT_FOUND'
		PRIMARY_ORG_REDACTED: 'PRIMARY_ORG_REDACTED'
		AFFILIATES_REDACTED: 'AFFILIATES_REDACTED'
		HIDDEN_AFFILIATES_ALL: 'HIDDEN_AFFILIATES_ALL'
		HIDDEN_AFFILIATES_SOME: 'HIDDEN_AFFILIATES_SOME'
		NOT_BACKER: 'NOT_BACKER'
		NO_FORUM_PROFILE: 'NO_FORUM_PROFILE'

	@defaultColor = "#a2c4c9"

	@noteInfo =
		NOT_FOUND:
			color: "#ffff00"
			note: "Profile not found"
		PRIMARY_ORG_REDACTED:
			color: "#e6b8af"
			note: "Primary org redacted"
		AFFILIATES_REDACTED:
			color: "#e6b8af"
			note: "Affiliate(s) redacted"
		HIDDEN_AFFILIATES_ALL:
			note: "Possible hidden affiliate(s) (all affiliates hidden)"
		HIDDEN_AFFILIATES_SOME:
			note: "Possible hidden affiliate(s) (at least one affiliate hidden)"
		NOT_BACKER:
			color: "#ff9900"
			note: "Not a backer"
		NO_FORUM_PROFILE:
			color: "#3b4cc9"
			note: "No forum profile"

	@magic =
		AFFILIATES_HIDDEN_ALL: "\n                        </div>\n"
		AFFILIATES_HIDDEN_SOME: "\n                         \n               \n"

		sidMatcher: /<span.+?>Spectrum Identification \(SID\)<\/span>\n\s+?<strong.+?>(.+)<\/strong>/gi
		forumBackerMatcher: /<dd class="Roles">.*?(Backer|Ready).*?<\/dd>/i
		profileNameMatcher: /<a href="\/citizens\/(.*?)" class="holotab">/i

	@get: (name, useCache=true) ->
		if !name?
			throw 'RSIProfile.get requires a name'
		Logger.log("Getting RSI profile for %s...", name)

		cacheKey = "rsi-profile/#{name.toLowerCase()}"

		if useCache
			cached = PublicCache.get(cacheKey)
			if cached?
				cachedObj = RSIProfile.fromCache(cached)
				if cachedObj?
					Logger.log("Retrieved profile from cache.")
					return cachedObj

		newProfile = new RSIProfile(name)

		Logger.log("Fetching profile from RSI website...")
		rsiProfileResult = UrlFetchApp.fetch(newProfile.getUrl()+"/organizations", {"muteHttpExceptions": true, "validateHttpsCertificates": false})
		rsiProfileResultCode = rsiProfileResult.getResponseCode()
		if rsiProfileResultCode != 200
			if rsiProfileResultCode == 404
				Logger.log("Profile not found.")
				newProfile.setFlag(RSIProfile.flags.NOT_FOUND)
				PublicCache.put(cacheKey, newProfile.cacheData(), RSIProfile.cacheTime)
				return newProfile

			Logger.log("Unexpected result code: %s", rsiProfileResultCode)
			newProfile.setFlag("unknown_profile_return_code_#{rsiProfileResultCode}")
			return newProfile

		Logger.log("Parsing RSI profile...")
		rsiProfileResultText = rsiProfileResult.getContentText()

		Logger.log("Finding profile name...")
		# Reset the regular expression
		RSIProfile.magic.profileNameMatcher.lastIndex = 0
		if (nameMatch = RSIProfile.magic.profileNameMatcher.exec(rsiProfileResultText)) and nameMatch.length >= 2
			newProfile.name = nameMatch[1]

		Logger.log("Finding profile org IDs...")
		# Reset the regular expression
		RSIProfile.magic.sidMatcher.lastIndex = 0
		inOrg = false
		while match = RSIProfile.magic.sidMatcher.exec(rsiProfileResultText)
			inOrg = true
			newProfile.addOrg(match[1].toLowerCase())

		Logger.log("Adding misc. flags from profile...")
		newProfile.addFlag(RSIProfile.flags.PRIMARY_ORG_REDACTED) if rsiProfileResultText.indexOf("box-content org main visibility-R") != -1
		newProfile.addFlag(RSIProfile.flags.AFFILIATES_REDACTED) if rsiProfileResultText.indexOf("org affiliation  visibility-R") != -1
		newProfile.addFlag(RSIProfile.flags.HIDDEN_AFFILIATES_ALL) if rsiProfileResultText.indexOf(RSIProfile.magic.AFFILIATES_HIDDEN_ALL) != -1
		newProfile.addFlag(RSIProfile.flags.HIDDEN_AFFILIATES_SOME) if rsiProfileResultText.indexOf(RSIProfile.magic.AFFILIATES_HIDDEN_SOME) != -1

		if !inOrg and !newProfile.hasFlag(RSIProfile.flags.PRIMARY_ORG_REDACTED) and !newProfile.hasFlag(RSIProfile.flags.HIDDEN_AFFILIATES_ALL) and !newProfile.hasFlag(RSIProfile.flags.HIDDEN_AFFILIATES_SOME)
			Logger.log("Checking backer status...")
			forumProfileResult = UrlFetchApp.fetch("https://forums.robertsspaceindustries.com/profile/"+encodeURIComponent(name), {"muteHttpExceptions": true, "validateHttpsCertificates": false})
			forumProfileResultCode = forumProfileResult.getResponseCode()
			if forumProfileResultCode == 404
				Logger.log("Not backer: Forum profile doesn't exist")
				newProfile.addFlag(RSIProfile.flags.NO_FORUM_PROFILE)
			else if forumProfileResultCode != 200
				Logger.log("Unexpected result code from forum profile: %s", forumProfileResultCode)
				newProfile.setFlag("unknown_forum_profile_return_code_#{forumProfileResultCode}")
				return newProfile
			else
				Logger.log("Parsing RSI forum profile...")
				forumProfileResultText = forumProfileResult.getContentText()
				if !RSIProfile.magic.forumBackerMatcher.test(forumProfileResultText)
					Logger.log("Not backer: No Backer group found")
					newProfile.addFlag(RSIProfile.flags.NOT_BACKER)

		Logger.log("Caching...")
		PublicCache.put(cacheKey, newProfile.cacheData(), RSIProfile.cacheTime)

		return newProfile

	@fromCache: (cacheData) ->
		try
			obj = JSON.parse(cacheData)

			if !obj.name? or !obj.flags? or !obj.orgs?
				return

			newProfile = new RSIProfile(obj.name)
			newProfile.flags = obj.flags
			newProfile.orgs = obj.orgs

			return newProfile
		catch e
			return

	cacheData: () ->
		return JSON.stringify(
			name: @name
			flags: @flags
			orgs: @orgs
		)

	getUrl: () ->
		"https://robertsspaceindustries.com/citizens/#{encodeURIComponent(@name)}"

	noteData: () ->
		Logger.log("Generating note data for %s...", @name)

		colors = []
		notes = []

		if !@hasOrg(RSIProfile.ourOrgLower) && !@hasFlag(RSIProfile.flags.NOT_FOUND) && !@hasFlag(RSIProfile.flags.NOT_BACKER)
			colors.push("#00ffff")
			notes.push("Not in #{RSIProfile.ourOrg} org")

		for flag of @flags
			noteInfo = RSIProfile.noteInfo[flag]
			if noteInfo?
				if noteInfo.color?
					colors.push(noteInfo.color)
				if noteInfo.note?
					notes.push(noteInfo.note)
			else
				colors.push("#ffffff")
				notes.push("Unknown flag: #{flag}")

		for org of @orgs
			if !LinterUtil.isOrgWhitelisted(org)
				colors.push("#b4a7d6")
				notes.push("Unallowed org: #{org.toUpperCase()}")

		color = colors[0]
		for newColor in colors
			if color != newColor
				color = "#dc143c"
				break
		if !color?
			color = "#a2c4c9"

		return notes: notes.join("\n"), color: color

	addFlag: (flag) ->
		@flags[flag] = true
		return

	setFlag: (flag) ->
		@flags = {}
		@addFlag(flag)
		return

	hasFlag: (flag) ->
		return @flags[flag] == true

	addOrg: (org) ->
		@orgs[org] = true
		return

	hasOrg: (org) ->
		return @orgs[org] == true

class ForumProfile
	@apiKey = encodeURIComponent(PropertiesService.getScriptProperties().getProperty("forum_api_key"))
	@cacheTime = 6*60*60 # 6 hours in seconds

	constructor: (@name) ->
		@id = -1
		@lastActive = new Date(0)

	@fromCache: (cacheData) ->
		try
			obj = JSON.parse(cacheData)

			if !obj.name? or !obj.id? or !obj.lastActive?
				return

			newProfile = new ForumProfile(obj.name)
			newProfile.id = obj.id
			newProfile.lastActive = new Date(Date.parse(obj.lastActive))

			return newProfile
		catch e
			return

	@get: (name, useCache=true) ->
		if !name?
			throw 'ForumProfile.get requires a name'
		Logger.log("Getting SUN forum profile for %s...", name)

		cacheKey = "forum-profile/#{name.toLowerCase()}"

		if useCache
			cached = PublicCache.get(cacheKey)
			if cached?
				cachedObj = ForumProfile.fromCache(cached)
				if cachedObj?
					Logger.log("Retrieved profile from cache.")
					return cachedObj

		newProfile = new ForumProfile(name)

		Logger.log("Fetching profile from SUN API...")
		forumProfileResult = UrlFetchApp.fetch("https://www.systemsunitednavy.com/api.php?action=getUser&value=#{encodeURIComponent(name)}&hash=#{ForumProfile.apiKey}", {"muteHttpExceptions": true, "validateHttpsCertificates": true});
		forumProfileResultCode = forumProfileResult.getResponseCode()
		if forumProfileResultCode != 200
			Logger.log("Unexpected result code: %s", forumProfileResultCode)
			throw "Unexpected response code: #{forumProfileResultCode}"

		forumProfileResultObject = JSON.parse(forumProfileResult.getContentText())
		if !forumProfileResultObject?
			Logger.log("Response unexpectedly empty")
			throw "Response from SUN forum was unexpectedly empty"

		if forumProfileResultObject.error?
			Logger.log("Unexpected error: (%s) %s", forumProfileResultObject.error, forumProfileResultObject.message)
			throw "Unexpected error: (#{forumProfileResultObject.error}) #{forumProfileResultObject.message}"

		newProfile.id = forumProfileResultObject.user_id
		newProfile.name = forumProfileResultObject.username
		newProfile.lastActive = new Date(forumProfileResultObject.last_activity*1000)

		if newProfile.isValid()
			Logger.log("Caching...")
			PublicCache.put(cacheKey, newProfile.cacheData(), ForumProfile.cacheTime)

		return newProfile

	cacheData: () ->
		return JSON.stringify(
			name: @name
			id: @id
			lastActive: @lastActive
		)

	isValid: () ->
		return (@id? and @id != -1) and (@lastActive? and +(@lastActive) != 0)

	getUrl: () ->
		return "https://www.systemsunitednavy.com/members/#{encodeURIComponent(@name.toLowerCase().replace(" ", "-").replace(".", "-"))}.#{encodeURIComponent(@id)}/?utm_source=roster"

class LinterUtil
	@allowedOrgs = null

	@getWhitelistedOrgs: () ->
		Logger.log("Getting whitelisted orgs...")
		whitelistSpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Org whitelist")
		rows = SpreadsheetUtil.getRowsData(whitelistSpreadsheet)

		whitelistedOrgs = {}
		for row in rows when row? and row.sid?
			whitelistedOrgs[row.sid.toLowerCase()] = true

		return whitelistedOrgs

	@isOrgWhitelisted: (sid) ->
		LinterUtil.allowedOrgs ?= LinterUtil.getWhitelistedOrgs()

		return LinterUtil.allowedOrgs[sid] == true

class BaseLinter
	constructor: (@sheet, @useCache=true) ->

	lintMulti: (startRow, endRow=@sheet.getLastRow()) ->
		if !startRow?
			startRow = @sheet.getFrozenRows()+1
		else
			startRow = Math.max(startRow, @sheet.getFrozenRows()+1)

		Logger.log("Retrieving sheet headers...")
		headers = SpreadsheetUtil.getHeaders(@sheet)

		Logger.log("Creating operating range...")
		operatingRange = @sheet.getRange(startRow, 1, endRow-startRow+1, @sheet.getLastColumn())
		baseRow = operatingRange.getRow()

		Logger.log("Setting up values...")
		toSet =
			backgrounds: operatingRange.getBackgrounds()
			notes: operatingRange.getNotes()
			values: operatingRange.getValues()

		Logger.log("Generating objects...")
		rows = SpreadsheetUtil.getObjects(toSet.values, headers)
		for row, i in rows
			sendValues =
				backgrounds: toSet.backgrounds[i]
				notes: toSet.notes[i]
				values: toSet.values[i]

			Logger.log("Processing row %s", baseRow+i)
			@doLint(row, headers, sendValues)

		Logger.log("Setting backgrounds...")
		operatingRange.setBackgrounds(toSet.backgrounds)
		Logger.log("Setting notes...")
		operatingRange.setNotes(toSet.notes)
		Logger.log("Setting values...")
		operatingRange.setValues(toSet.values)

		Logger.log("Done.")
		return

	lint: (row) ->
		if !row?
			throw 'BaseLinter.lint requires a row number'

		@lintMulti(startRow=row, endRow=row)

	doLint: (rowData, headers, values) ->
		throw 'Not implemented'


class IDDataLinter extends BaseLinter
	doLint: (rowData, headers, values) ->
		if rowData.rsiHandle?
			rsiProfile = RSIProfile.get(rowData.rsiHandle, @useCache)
			noteData = rsiProfile.noteData()

			values.backgrounds[headers.keys.rsiHandle] = noteData.color
			values.notes[headers.keys.rsiHandle] = noteData.notes
			values.values[headers.keys.rsiHandle] = "=HYPERLINK(\"#{rsiProfile.getUrl()}\"; \"#{rsiProfile.name}\")"
		else
			values.backgrounds[headers.keys.rsiHandle] = '#a2c4c9'
			values.notes[headers.keys.rsiHandle] = ''
			values.values[headers.keys.rsiHandle] = ''

		if rowData.username?
			try
				forumProfile = ForumProfile.get(rowData.username, @useCache)

				if forumProfile.isValid()
					values.backgrounds[headers.keys.username] = '#d0e0e3'
					values.notes[headers.keys.username] = ''
					values.values[headers.keys.username] = "=HYPERLINK(\"#{forumProfile.getUrl()}\"; \"#{forumProfile.name}\")"

					values.backgrounds[headers.keys.lastActive] = '#d0e0e3'
					values.notes[headers.keys.lastActive] = ''
					values.values[headers.keys.lastActive] = forumProfile.lastActive
				else
					values.backgrounds[headers.keys.username] = '#ffff00'
					values.notes[headers.keys.username] = 'Forum profile not found'
					values.values[headers.keys.username] = forumProfile.name

					values.backgrounds[headers.keys.lastActive] = '#d0e0e3'
					values.notes[headers.keys.lastActive] = ''
					values.values[headers.keys.lastActive] = ''
			catch e
				Logger.log("Error processing profile for %s: %s", rowData.username, e)

				values.backgrounds[headers.keys.username] = '#ffffff'
				values.notes[headers.keys.username] = e

				values.backgrounds[headers.keys.lastActive] = '#ffffff'
				values.notes[headers.keys.lastActive] = ''
				values.values[headers.keys.lastActive] = ''
		else
			values.backgrounds[headers.keys.username] = '#d0e0e3'
			values.notes[headers.keys.username] = ''
			values.values[headers.keys.username] = ''

			values.backgrounds[headers.keys.lastActive] = '#d0e0e3'
			values.notes[headers.keys.lastActive] = ''
			values.values[headers.keys.lastActive] = ''


		return


fullLint = (useCache=true) ->
	idLinter = new IDDataLinter(SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ID data"), useCache)
	idLinter.lintMulti()

	return

manualCheckRSIHandle = () ->
	ui = SpreadsheetApp.getUi()
	response = ui.prompt('Enter handle', 'This will perform a roster check on a single RSI handle. This does bypass the caching to prevent providing stale results.\n\nEnter RSI handle to check:', ui.ButtonSet.OK_CANCEL)

	button = response.getSelectedButton()
	if button == ui.Button.OK or button == ui.Button.YES
		handle = response.getResponseText()
		if !handle? or handle.length <= 0
			ui.alert("Error", "You must enter a handle!", ui.ButtonSet.OK)
			return

		rsiProfile = RSIProfile.get(handle, false)
		notes = rsiProfile.noteData()

		if notes.notes? and notes.notes.length > 0
			ui.alert("Results for "+rsiProfile.name, notes.notes, ui.ButtonSet.OK);
		else
			ui.alert("Results for "+rsiProfile.name, "No results returned. This is a good thing! It means that we couldn't find anything wrong.", ui.ButtonSet.OK);

onEdit_ = (event) ->
	sheet = event.range.getSheet()
	sheetName = sheet.getName().toLowerCase();

	startRow = event.range.getRow()
	endRow = event.range.getLastRow()

	Logger.log("%s edited on %s", event.range.getA1Notation(), sheet.getName())

	switch sheetName
		when "id data"
			idLinter = new IDDataLinter(sheet)
			idLinter.lintMulti(startRow, endRow)
		else Logger.log("Ignoring edit on %s", sheetName)

	return

onOpen_ = (event) ->
	menu = SpreadsheetApp.getUi().createMenu('Roster Helper')

	menu.addItem("Manually check single RSI handle...", "triggerManualCheckRSIHandle")
	menu.addSeparator()
	menu.addItem("Force Manual Linter Run", "triggerManualLint")
	menu.addItem("Force Manual Linter Run (without cache)", "triggerManualLintNoCache")

	menu.addToUi()
	return

`function triggerFullLint() { fullLint(true); }`
`function triggerFullLintNoCache() { fullLint(false); }`
`function triggerManualLint() { fullLint(true); }`
`function triggerManualLintNoCache() { fullLint(false); }`
`function triggerManualCheckRSIHandle() { manualCheckRSIHandle(); }`
`function onEdit() { onEdit_.apply(this, arguments); }`
`function onOpen() { onOpen_.apply(this, arguments); }`