-- 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.

--- Minimal legacy XLS BIFF8/BIFF7 text extractor
-- @module plugins.msoutils.xls

local cfb = require("plugins.msoutils.cfb")
local spreadsheet = require("core.lib.spreadsheet")
local excel_number_format = require("plugins.msoutils.excelnf")

local reader = {}

local TYPE_EOF = 0x0a
local TYPE_BOUNDSHEET = 0x85
local TYPE_DIMENSION = 0x200
local TYPE_RK = 0x7e
local TYPE_RK2 = 0x27e
local TYPE_MULRK = 0xbd
local TYPE_SST = 0xfc
local TYPE_LABEL = 0x204
local TYPE_LABELSST = 0xfd
local TYPE_NUMBER = 0x203
local TYPE_FORMULA = 0x406
local TYPE_FORMULA2 = 0x6
local TYPE_BOOLERR = 0x205
local TYPE_STRING = 0x207
local TYPE_NINETEENFOUR = 0x22
local TYPE_WINDOW1 = 0x3d
local TYPE_CONTINUE = 0x3c
local TYPE_XF = 0xe0
local TYPE_FORMAT = 0x41e
local TYPE_FORMAT_OLD = 0x1e

local BIFF8 = 0x600
local BIFF7 = 0x500
local WORKBOOKGLOBALS = 0x5
local WORKSHEET = 0x10

-- BIFF stores spreadsheet errors as stable one-byte display codes
local ERROR_TEXT = {
	[0x00] = "#NULL!",
	[0x07] = "#DIV/0!",
	[0x0f] = "#VALUE!",
	[0x17] = "#REF!",
	[0x1d] = "#NAME?",
	[0x24] = "#NUM!",
	[0x2a] = "#N/A",
	[0x2b] = "#GETTING_DATA",
}

local function bytez(data, offset)
	return data:byte(offset + 1) or 0
end

local function u16z(data, offset)
	return bytez(data, offset) | (bytez(data, offset + 1) << 8)
end

local function u32z(data, offset)
	return bytez(data, offset)
		| (bytez(data, offset + 1) << 8)
		| (bytez(data, offset + 2) << 16)
		| (bytez(data, offset + 3) << 24)
end

local function substrz(data, offset, len)
	if len <= 0 then
		return ""
	end
	return data:sub(offset + 1, offset + len)
end

local function codepoint_to_utf8(code)
	if not code or code == 0 then
		return ""
	end
	return utf8.char(code)
end

local function latin1_to_utf8(data)
	local out = {}
	for i = 1, #data do
		out[#out + 1] = codepoint_to_utf8(data:byte(i))
	end
	return table.concat(out)
end

local function utf16le_to_utf8(data)
	local out = {}
	local i = 1

	while i < #data do
		local lo, hi = data:byte(i, i + 1)
		local code = lo | (hi << 8)
		i = i + 2

		if code >= 0xd800 and code <= 0xdbff and i < #data then
			local lo2, hi2 = data:byte(i, i + 1)
			local trail = lo2 | (hi2 << 8)
			if trail >= 0xdc00 and trail <= 0xdfff then
				code = 0x10000 + ((code - 0xd800) << 10) + (trail - 0xdc00)
				i = i + 2
			end
		end

		out[#out + 1] = codepoint_to_utf8(code)
	end

	return table.concat(out)
end

local function decode_biff_string(raw, is_16bit)
	if is_16bit then
		return utf16le_to_utf8(raw)
	end
	return latin1_to_utf8(raw)
end

local function parse_short_string(data, offset, version)
	if version == BIFF8 then
		local char_count = u16z(data, offset)
		local flags = bytez(data, offset + 2)
		local is_16bit = (flags & 0x01) ~= 0
		local byte_count = is_16bit and char_count * 2 or char_count
		return decode_biff_string(substrz(data, offset + 3, byte_count), is_16bit)
	end

	local char_count = bytez(data, offset)
	return latin1_to_utf8(substrz(data, offset + 1, char_count))
end

local function parse_sst(data, pos, length)
	local strings = {}
	local spos = pos + 4
	local limitpos = spos + length
	local unique_strings = u32z(data, spos + 4)
	spos = spos + 8

	for _ = 1, unique_strings do
		if spos >= limitpos then
			local opcode = u16z(data, spos)
			local conlength = u16z(data, spos + 2)
			if opcode ~= TYPE_CONTINUE then
				return strings
			end
			spos = spos + 4
			limitpos = spos + conlength
		end

		local char_count = u16z(data, spos)
		spos = spos + 2
		local option_flags = bytez(data, spos)
		spos = spos + 1
		local is_8bit = (option_flags & 0x01) == 0
		local has_ext = (option_flags & 0x04) ~= 0
		local has_rich = (option_flags & 0x08) ~= 0
		local formatting_runs = 0
		local extended_run_length = 0

		if has_rich then
			formatting_runs = u16z(data, spos)
			spos = spos + 2
		end
		if has_ext then
			extended_run_length = u32z(data, spos)
			spos = spos + 4
		end

		local remaining_chars = char_count
		local segment_is_16bit = not is_8bit
		local utf16 = {}

		while remaining_chars > 0 do
			if spos >= limitpos then
				local opcode = u16z(data, spos)
				local conlength = u16z(data, spos + 2)
				if opcode ~= TYPE_CONTINUE then
					break
				end
				spos = spos + 4
				limitpos = spos + conlength
				local option = bytez(data, spos)
				spos = spos + 1
				segment_is_16bit = (option & 0x01) ~= 0
			end

			local bytes_per_char = segment_is_16bit and 2 or 1
			local available_chars = math.floor((limitpos - spos) / bytes_per_char)
			local take_chars = math.min(remaining_chars, available_chars)
			if take_chars <= 0 then
				break
			end

			local part = substrz(data, spos, take_chars * bytes_per_char)
			if segment_is_16bit then
				utf16[#utf16 + 1] = part
			else
				for i = 1, #part do
					utf16[#utf16 + 1] = part:sub(i, i) .. "\0"
				end
			end
			spos = spos + #part
			remaining_chars = remaining_chars - take_chars
		end

		strings[#strings + 1] = utf16le_to_utf8(table.concat(utf16))

		if has_rich then
			spos = spos + (4 * formatting_runs)
		end
		if has_ext then
			spos = spos + extended_run_length
		end
	end

	return strings
end

local function number_to_text(value)
	if value ~= value or value == math.huge or value == -math.huge then
		return ""
	end
	if math.type and math.type(value) == "integer" then
		return tostring(value)
	end
	if math.floor(value) == value then
		return string.format("%.0f", value)
	end
	return tostring(value)
end

--- Render one numeric cell through its workbook number format when supported
-- @param workbook table parsed workbook
-- @param xf_index integer cell XF index
-- @param value number stored numeric value
-- @return string text visible cell value
local function formatted_number(workbook, xf_index, value)
	local format_id = workbook.xfs[xf_index + 1]

	return excel_number_format.format_number(value, format_id, workbook.formats, workbook.date1904)
		or number_to_text(value)
end

local function read_double(data, offset)
	local ok, value = pcall(string.unpack, "<d", substrz(data, offset, 8))
	if ok then
		return value
	end
	return 0
end

--- Decode one compact BIFF RK number
-- @param raw integer encoded RK value
-- @return number value decoded number
local function rk_value(raw)
	local value
	if (raw & 0x02) ~= 0 then
		value = raw >> 2
		-- RK integers use a signed 30-bit value inside the unsigned record word
		if value >= 0x20000000 then
			value = value - 0x40000000
		end
	else
		local sign = (raw & 0x80000000) ~= 0
		local exp = (raw & 0x7ff00000) >> 20
		local mantissa = 0x100000 | (raw & 0x000ffffc)
		if exp == 0 then
			value = 0
		else
			value = mantissa / (2 ^ (20 - (exp - 1023)))
		end
		if sign then
			value = -value
		end
	end
	if (raw & 0x01) ~= 0 then
		value = value / 100
	end
	return value
end

local function add_cell(sheet, row, col, value)
	row = row + 1
	col = col + 1
	sheet.rows[row] = sheet.rows[row] or {}
	sheet.rows[row][col] = tostring(value or "")
	sheet.max_row = math.max(sheet.max_row, row)
	sheet.max_col = math.max(sheet.max_col, col)
end

--- Return the visible spreadsheet text for one BIFF error code
-- @param code integer encoded error value
-- @return string text displayed error
local function error_to_text(code)
	return ERROR_TEXT[code] or "#ERROR!"
end

local function parse_boundsheet_name(data, pos, version)
	local name_len = bytez(data, pos + 10)
	if version == BIFF8 then
		local flags = bytez(data, pos + 11)
		local is_16bit = (flags & 0x01) ~= 0
		local byte_len = is_16bit and name_len * 2 or name_len
		return decode_biff_string(substrz(data, pos + 12, byte_len), is_16bit)
	end
	return latin1_to_utf8(substrz(data, pos + 11, name_len))
end

local function parse_sheet(workbook, sheet)
	local data = workbook.data
	local pos = sheet.offset
	local length = u16z(data, pos + 2)
	local version = u16z(data, pos + 4)
	local substream_type = u16z(data, pos + 6)

	if (version ~= BIFF8 and version ~= BIFF7) or substream_type ~= WORKSHEET then
		return
	end

	pos = pos + length + 4
	local pending_string_formula

	while pos < #data do
		local code = u16z(data, pos)
		length = u16z(data, pos + 2)
		local payload = pos + 4

		if pending_string_formula and code ~= TYPE_STRING then
			pending_string_formula = nil
		end

		if code == TYPE_EOF then
			break
		elseif code == TYPE_STRING and pending_string_formula then
			add_cell(
				sheet,
				pending_string_formula.row,
				pending_string_formula.col,
				parse_short_string(data, payload, version)
			)
			pending_string_formula = nil
		elseif code == TYPE_DIMENSION then
			if length == 10 or version == BIFF7 then
				sheet.max_row = math.max(sheet.max_row, u16z(data, payload + 2))
				sheet.max_col = math.max(sheet.max_col, u16z(data, payload + 6))
			else
				sheet.max_row = math.max(sheet.max_row, u32z(data, payload + 4))
				sheet.max_col = math.max(sheet.max_col, u16z(data, payload + 10))
			end
		elseif code == TYPE_LABELSST then
			local row = u16z(data, payload)
			local col = u16z(data, payload + 2)
			local sst_index = u32z(data, payload + 6)
			add_cell(sheet, row, col, workbook.sst[sst_index + 1] or "")
		elseif code == TYPE_LABEL then
			local row = u16z(data, payload)
			local col = u16z(data, payload + 2)
			add_cell(sheet, row, col, parse_short_string(data, payload + 6, version))
		elseif code == TYPE_NUMBER then
			local row = u16z(data, payload)
			local col = u16z(data, payload + 2)
			local xf_index = u16z(data, payload + 4)
			add_cell(sheet, row, col, formatted_number(workbook, xf_index, read_double(data, payload + 6)))
		elseif code == TYPE_RK or code == TYPE_RK2 then
			local row = u16z(data, payload)
			local col = u16z(data, payload + 2)
			local xf_index = u16z(data, payload + 4)
			add_cell(sheet, row, col, formatted_number(workbook, xf_index, rk_value(u32z(data, payload + 6))))
		elseif code == TYPE_MULRK then
			local row = u16z(data, payload)
			local first_col = u16z(data, payload + 2)
			local last_col = u16z(data, payload + length - 2)
			local rk_pos = payload + 4
			for col = first_col, last_col do
				local xf_index = u16z(data, rk_pos)
				add_cell(sheet, row, col, formatted_number(workbook, xf_index, rk_value(u32z(data, rk_pos + 2))))
				rk_pos = rk_pos + 6
			end
		elseif code == TYPE_BOOLERR then
			local row = u16z(data, payload)
			local col = u16z(data, payload + 2)
			local raw_value = bytez(data, payload + 6)
			local value = bytez(data, payload + 7) == 1
				and error_to_text(raw_value)
				or (raw_value == 1 and "TRUE" or "FALSE")
			add_cell(sheet, row, col, value)
		elseif code == TYPE_FORMULA or code == TYPE_FORMULA2 then
			local row = u16z(data, payload)
			local col = u16z(data, payload + 2)
			local marker = bytez(data, payload + 6)
			local special_result = marker < 4
				and bytez(data, payload + 12) == 255
				and bytez(data, payload + 13) == 255
			if not special_result then
				local xf_index = u16z(data, payload + 4)
				add_cell(sheet, row, col, formatted_number(workbook, xf_index, read_double(data, payload + 6)))
			elseif marker == 0 then
				pending_string_formula = { row = row, col = col }
			elseif marker == 1 then
				add_cell(sheet, row, col, bytez(data, payload + 8) == 1 and "TRUE" or "FALSE")
			elseif marker == 2 then
				add_cell(sheet, row, col, error_to_text(bytez(data, payload + 8)))
			else
				add_cell(sheet, row, col, "")
			end
		end

		pos = pos + length + 4
	end
end

local function parse_workbook_stream(data)
	local workbook = {
		data = data,
		sst = {},
		sheets = {},
		formats = {},
		xfs = {},
		active_sheet = 1,
		date1904 = false,
	}
	local pos = 0
	local length = u16z(data, pos + 2)
	local version = u16z(data, pos + 4)
	local substream_type = u16z(data, pos + 6)

	if (version ~= BIFF8 and version ~= BIFF7) or substream_type ~= WORKBOOKGLOBALS then
		return nil, "unsupported BIFF workbook stream"
	end

	pos = pos + length + 4
	while pos < #data do
		local code = u16z(data, pos)
		length = u16z(data, pos + 2)
		local payload = pos + 4

		if code == TYPE_EOF then
			break
		elseif code == TYPE_SST then
			workbook.sst = parse_sst(data, pos, length)
		elseif code == TYPE_BOUNDSHEET then
			workbook.sheets[#workbook.sheets + 1] = {
				name = parse_boundsheet_name(data, pos, version),
				offset = u32z(data, pos + 4),
				rows = {},
				max_row = 0,
				max_col = 0,
			}
		elseif code == TYPE_NINETEENFOUR then
			workbook.date1904 = bytez(data, pos + 4) == 1
		elseif code == TYPE_WINDOW1 then
			workbook.active_sheet = u16z(data, pos + 14) + 1
		elseif code == TYPE_XF then
			workbook.xfs[#workbook.xfs + 1] = u16z(data, payload + 2)
		elseif code == TYPE_FORMAT or code == TYPE_FORMAT_OLD then
			local format_id = u16z(data, payload)
			workbook.formats[format_id] = parse_short_string(data, payload + 2, version)
		end

		pos = pos + length + 4
	end

	for _, sheet in ipairs(workbook.sheets) do
		parse_sheet(workbook, sheet)
	end

	if #workbook.sheets == 0 then
		return nil, "no worksheets found"
	end

	return workbook
end

local function load_workbook(filename)
	local archive, err = cfb.open(filename)
	if not archive then
		return nil, err
	end

	local stream = archive:read_stream("Workbook") or archive:read_stream("Book")
	if not stream then
		return nil, "Workbook stream not found"
	end

	return parse_workbook_stream(stream)
end

local function render_padded_text_sheet(sheet)
	return spreadsheet.render_text(sheet)
end

local function render_padded_markdown_sheet(sheet)
	return spreadsheet.render_markdown(sheet)
end

local function render_text(workbook)
	local out = {}

	for sheet_index, sheet in ipairs(workbook.sheets) do
		if sheet_index > 1 then
			out[#out + 1] = ""
		end
		out[#out + 1] = "Sheet: " .. sheet.name
		for _, line in ipairs(render_padded_text_sheet(sheet)) do
			out[#out + 1] = line
		end
	end

	return table.concat(out, "\n") .. "\n"
end

local function render_markdown(workbook)
	local out = {}

	for sheet_index, sheet in ipairs(workbook.sheets) do
		if sheet_index > 1 then
			out[#out + 1] = ""
		end
		out[#out + 1] = "## " .. sheet.name
		out[#out + 1] = ""
		for _, line in ipairs(render_padded_markdown_sheet(sheet)) do
			out[#out + 1] = line
		end
	end

	return table.concat(out, "\n") .. "\n"
end

local function render_tsv(workbook)
	local out = {}
	local include_sheet_headers = #workbook.sheets > 1

	for sheet_index, sheet in ipairs(workbook.sheets) do
		if sheet_index > 1 then
			out[#out + 1] = ""
		end
		if include_sheet_headers then
			out[#out + 1] = "Sheet:\t" .. spreadsheet.cell_text(sheet.name)
		end

		for _, line in ipairs(spreadsheet.render_tsv(sheet)) do
			out[#out + 1] = line
		end
	end

	return table.concat(out, "\n") .. "\n"
end

function reader.read(filename)
	return load_workbook(filename)
end

function reader.extract_text(filename)
	local workbook, err = load_workbook(filename)
	if not workbook then
		return nil, err
	end
	return render_text(workbook)
end

function reader.extract_markdown(filename)
	local workbook, err = load_workbook(filename)
	if not workbook then
		return nil, err
	end
	return render_markdown(workbook)
end

function reader.extract_tsv(filename)
	local workbook, err = load_workbook(filename)
	if not workbook then
		return nil, err
	end
	return render_tsv(workbook)
end

return reader
