-- lx-bundle:true

-- This file is part of Lx, created by Liqube <www.liqube.com>
-- Copyright Liqube Audio. All rights reserved.
-- Lx Interim License. See LICENSE for details.

--- Excel number-format and serial-date rendering used by XLS and XLSX
-- @module plugins.msoutils.excelnf

local excel_number_format = {}

-- Built-in Excel formats whose display meaning is stable enough to extract
local BUILTIN_NUMBER_FORMATS = {
	[5] = "$#,##0;-$#,##0",
	[6] = "$#,##0;[Red]-$#,##0",
	[7] = "$#,##0.00;-$#,##0.00",
	[8] = "$#,##0.00;[Red]-$#,##0.00",
	[9] = "0%",
	[10] = "0.00%",
	[14] = "m/d/yy",
	[15] = "d-mmm-yy",
	[16] = "d-mmm",
	[17] = "mmm-yy",
	[18] = "h:mm AM/PM",
	[19] = "h:mm:ss AM/PM",
	[20] = "h:mm",
	[21] = "h:mm:ss",
	[22] = "m/d/yy h:mm",
	[37] = "#,##0;(#,##0)",
	[38] = "#,##0;[Red](#,##0)",
	[39] = "#,##0.00;(#,##0.00)",
	[40] = "#,##0.00;[Red](#,##0.00)",
	[44] = "$#,##0.00;($#,##0.00)",
	[45] = "mm:ss",
	[46] = "[h]:mm:ss",
	[47] = "mmss.0",
}

local MONTH_NAMES = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" }

--- Convert days since 1970-01-01 into a Gregorian date
-- @param days integer signed day offset
-- @return integer year
-- @return integer month
-- @return integer day
local function civil_from_days(days)
	local shifted = days + 719468
	local era = shifted // 146097
	local day_of_era = shifted - era * 146097
	local year_of_era = (day_of_era - day_of_era // 1460 + day_of_era // 36524 - day_of_era // 146096) // 365
	local year = year_of_era + era * 400
	local day_of_year = day_of_era - (365 * year_of_era + year_of_era // 4 - year_of_era // 100)
	local month_part = (5 * day_of_year + 2) // 153
	local day = day_of_year - (153 * month_part + 2) // 5 + 1
	local month = month_part + (month_part < 10 and 3 or -9)
	year = year + (month <= 2 and 1 or 0)

	return year, month, day
end

--- Convert one Excel serial day into calendar fields
-- @param serial_day integer whole serial day
-- @param date1904 boolean whether the workbook uses the 1904 epoch
-- @return integer year
-- @return integer month
-- @return integer day
local function excel_day_to_date(serial_day, date1904)
	if not date1904 and serial_day == 60 then
		return 1900, 2, 29
	end

	local days_since_1970
	if date1904 then
		days_since_1970 = serial_day - 24107
	elseif serial_day < 60 then
		days_since_1970 = serial_day - 25568
	else
		days_since_1970 = serial_day - 25569
	end

	return civil_from_days(days_since_1970)
end

--- Split one Excel serial value into date and time fields
-- @param value number serial cell value
-- @param date1904 boolean whether the workbook uses the 1904 epoch
-- @return table|nil fields converted date and time fields
local function excel_date_fields(value, date1904)
	if value < 0 then
		return nil
	end

	local serial_day = math.floor(value)
	local seconds = math.floor((value - serial_day) * 86400 + 0.5)
	local total_seconds = math.floor(value * 86400 + 0.5)
	if seconds >= 86400 then
		serial_day = serial_day + 1
		seconds = seconds - 86400
	end

	local year, month, day = excel_day_to_date(serial_day, date1904)

	return {
		year = year,
		month = month,
		day = day,
		hour = seconds // 3600,
		minute = (seconds // 60) % 60,
		second = seconds % 60,
		total_hours = total_seconds // 3600,
		total_minutes = total_seconds // 60,
		total_seconds = total_seconds,
	}
end

--- Count displayed decimal places from one number format
-- @param format_code string Excel number format
-- @return integer decimal_places
local function decimal_places(format_code)
	local places = format_code:match("%.([0#]+)")

	return places and #places or 0
end

--- Render one fixed decimal number with optional thousands separators
-- @param value number numeric value
-- @param places integer decimal places
-- @param grouped boolean whether to group thousands
-- @return string text rendered number
local function fixed_number(value, places, grouped)
	local negative = value < 0
	local text = string.format("%." .. places .. "f", math.abs(value))
	local integer, fraction = text:match("^(%d+)(.*)$")

	if grouped then
		local parts = {}
		while #integer > 3 do
			table.insert(parts, 1, integer:sub(-3))
			integer = integer:sub(1, -4)
		end
		table.insert(parts, 1, integer)
		integer = table.concat(parts, ",")
	end

	return (negative and "-" or "") .. integer .. fraction
end

--- Return the first format section without splitting quoted or escaped semicolons
-- @param format_code string Excel number format
-- @return string section First positive-value section
local function first_format_section(format_code)
	local quoted = false
	local bracketed = false
	local skip_next = false

	for index = 1, #format_code do
		local character = format_code:sub(index, index)
		if skip_next then
			skip_next = false
		elseif quoted then
			if character == '"' then
				quoted = false
			end
		elseif character == '"' then
			quoted = true
		elseif character == "[" then
			bracketed = true
		elseif character == "]" then
			bracketed = false
		elseif character == "\\" or character == "_" or character == "*" then
			skip_next = true
		elseif character == ";" and not bracketed then
			return format_code:sub(1, index - 1)
		end
	end

	return format_code
end

--- Remove format metadata and literal text before classifying display fields
-- @param section string One Excel number-format section
-- @return string|nil fields Display field syntax or nil for malformed metadata
local function format_display_fields(section)
	local fields = {}
	local index = 1

	while index <= #section do
		local character = section:sub(index, index)
		if character == '"' then
			local closing = section:find('"', index + 1, true)
			if not closing then
				return nil
			end

			index = closing + 1
		elseif character == "\\" or character == "_" or character == "*" then
			index = math.min(#section + 1, index + 2) -- Skip the following literal, spacer, or fill character
		elseif character == "[" then
			local closing = section:find("]", index + 1, true)
			if not closing then
				return nil
			end

			local directive = section:sub(index + 1, closing - 1):lower()
			-- Pattern breakdown: one or more identical h, m, or s letters is an elapsed-time field
			if directive:match("^h+$") or directive:match("^m+$") or directive:match("^s+$") then
				fields[#fields + 1] = "[" .. directive .. "]"
			end

			index = closing + 1
		else
			fields[#fields + 1] = character
			index = index + 1
		end
	end

	return table.concat(fields):lower()
end

--- Format one numeric spreadsheet value when its format is supported
-- Unsupported formats return nil so readers can keep the raw stored number
-- @param value number stored numeric value
-- @param format_id integer|nil workbook number-format id
-- @param custom_formats table|nil custom format strings by id
-- @param date1904 boolean|nil whether the workbook uses the 1904 epoch
-- @return string|nil text formatted display value
function excel_number_format.format_number(value, format_id, custom_formats, date1904)
	if type(value) ~= "number" or value ~= value or value == math.huge or value == -math.huge then
		return nil
	end

	local format_code = custom_formats and custom_formats[format_id] or nil
	format_code = format_code or BUILTIN_NUMBER_FORMATS[format_id]
	if type(format_code) ~= "string" then
		return nil
	end

	local first_section = first_format_section(format_code)
	local lower = format_display_fields(first_section)
	if not lower then
		return nil
	end
	if lower:find("%", 1, true) then
		return fixed_number(value * 100, decimal_places(lower), lower:find(",", 1, true) ~= nil) .. "%"
	end

	local currency = lower:find("€", 1, true) and "€"
		or lower:find("£", 1, true) and "£"
		or lower:find("¥", 1, true) and "¥"
		or lower:find("$", 1, true) and "$"
	if currency then
		local number = fixed_number(value, decimal_places(lower), lower:find(",", 1, true) ~= nil)
		if number:sub(1, 1) == "-" then
			return "-" .. currency .. number:sub(2)
		end

		return currency .. number
	end

	local is_date = lower:find("[yd]") ~= nil
	local is_time = lower:find("[hs]") ~= nil
		or lower:find("[m]", 1, true) ~= nil
		or lower:find("[mm]", 1, true) ~= nil
	if not is_date and not is_time then
		return nil
	end

	local fields = excel_date_fields(value, date1904 == true)
	if not fields then
		return nil
	end

	if lower == "m/d/yy" then
		return string.format("%d/%d/%02d", fields.month, fields.day, fields.year % 100)
	elseif lower == "d-mmm-yy" then
		return string.format("%d-%s-%02d", fields.day, MONTH_NAMES[fields.month], fields.year % 100)
	elseif lower == "d-mmm" then
		return string.format("%d-%s", fields.day, MONTH_NAMES[fields.month])
	elseif lower == "mmm-yy" then
		return string.format("%s-%02d", MONTH_NAMES[fields.month], fields.year % 100)
	end
	if is_date and not is_time then
		return string.format("%04d-%02d-%02d", fields.year, fields.month, fields.day)
	end

	local use_meridiem = lower:find("am/pm", 1, true) ~= nil
	local hour = fields.hour
	local suffix = ""
	if use_meridiem then
		suffix = hour >= 12 and " PM" or " AM"
		hour = hour % 12
		if hour == 0 then
			hour = 12
		end
	end

	local time_text
	if lower:find("[h]", 1, true) or lower:find("[hh]", 1, true) then
		time_text = string.format("%d:%02d:%02d", fields.total_hours, fields.minute, fields.second)
	elseif lower:find("[m]", 1, true) or lower:find("[mm]", 1, true) then
		time_text = string.format("%d:%02d", fields.total_minutes, fields.second)
	elseif lower:find("[s]", 1, true) or lower:find("[ss]", 1, true) then
		time_text = tostring(fields.total_seconds)
	elseif lower:find("s", 1, true) then
		time_text = string.format("%d:%02d:%02d%s", hour, fields.minute, fields.second, suffix)
	else
		time_text = string.format("%d:%02d%s", hour, fields.minute, suffix)
	end

	if is_date then
		return string.format("%d/%d/%02d %s", fields.month, fields.day, fields.year % 100, time_text)
	end

	return time_text
end

return excel_number_format
