-- lx-bundle:true

--- Minimal legacy XLS BIFF8/BIFF7 text extractor.
-- @module plugins.xlsutils.reader

local cfb = require("plugins.xlsutils.cfb")

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_NINETEENFOUR = 0x22
local TYPE_WINDOW1 = 0x3d
local TYPE_CONTINUE = 0x3c

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

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 bytes_per_char = is_8bit and 1 or 2
		local bytes_needed = char_count * bytes_per_char
		local raw = {}

		while bytes_needed > 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

				if is_8bit and option ~= 0 then
					local converted = {}
					for i = 1, #raw do
						local part = raw[i]
						for j = 1, #part do
							converted[#converted + 1] = part:sub(j, j) .. "\0"
						end
					end
					raw = { table.concat(converted) }
					is_8bit = false
					bytes_per_char = 2
					bytes_needed = bytes_needed * 2
				elseif (not is_8bit) and option == 0 then
					bytes_per_char = 1
				else
					bytes_per_char = is_8bit and 1 or 2
				end
			end

			local take = math.min(bytes_needed, limitpos - spos)
			raw[#raw + 1] = substrz(data, spos, take)
			spos = spos + take
			bytes_needed = bytes_needed - take
		end

		strings[#strings + 1] = decode_biff_string(table.concat(raw), not is_8bit)

		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

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

local function rk_value(raw)
	local value
	if (raw & 0x02) ~= 0 then
		value = raw >> 2
	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

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

	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_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)
			add_cell(sheet, row, col, number_to_text(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)
			add_cell(sheet, row, col, number_to_text(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
				add_cell(sheet, row, col, number_to_text(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 value = bytez(data, payload + 6) == 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)
			if not (marker < 4 and bytez(data, payload + 12) == 255 and bytez(data, payload + 13) == 255) then
				add_cell(sheet, row, col, number_to_text(read_double(data, payload + 6)))
			end
		end

		pos = pos + length + 4
	end
end

local function parse_workbook_stream(data)
	local workbook = {
		data = data,
		sst = {},
		sheets = {},
		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)

		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
		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 cell_to_text(value)
	value = tostring(value or "")
	value = value:gsub("\r\n", "\n"):gsub("\r", "\n"):gsub("\t", " "):gsub("\n", " ")
	return value
end

local function column_name(index)
	local out = {}
	while index > 0 do
		local rem = (index - 1) % 26
		table.insert(out, 1, string.char(65 + rem))
		index = (index - rem - 1) // 26
	end
	return table.concat(out)
end

local function markdown_cell(value)
	value = cell_to_text(value):gsub("\\", "\\\\"):gsub("|", "\\|")
	return value
end

local function display_len(value)
	value = tostring(value or "")
	local ok, count = pcall(function()
		local n = 0
		for _ in utf8.codes(value) do
			n = n + 1
		end
		return n
	end)
	return ok and count or #value
end

local function pad_right(value, width)
	value = tostring(value or "")
	local missing = width - display_len(value)
	if missing > 0 then
		return value .. string.rep(" ", missing)
	end
	return value
end

local function is_empty_sheet_row(sheet, row_index)
	local row = sheet.rows[row_index] or {}

	for col = 1, sheet.max_col do
		if cell_to_text(row[col]) ~= "" then
			return false
		end
	end

	return true
end

local function last_non_empty_row(sheet)
	for r = sheet.max_row, 1, -1 do
		if not is_empty_sheet_row(sheet, r) then
			return r
		end
	end

	return 0
end

local function sheet_text_grid(sheet)
	local rows = {}
	local widths = {}
	local last_row = last_non_empty_row(sheet)

	for r = 1, last_row do
		local source_row = sheet.rows[r] or {}
		local row = {}
		for col = 1, sheet.max_col do
			local value = cell_to_text(source_row[col])
			row[col] = value
			widths[col] = math.max(widths[col] or 0, display_len(value))
		end
		rows[r] = row
	end

	return rows, widths, last_row
end

local function render_padded_text_sheet(sheet)
	local rows, widths, last_row = sheet_text_grid(sheet)
	local out = {}

	for r = 1, last_row do
		local cells = {}
		for col = 1, sheet.max_col do
			cells[col] = pad_right(rows[r][col], widths[col] or 0)
		end
		out[#out + 1] = table.concat(cells, "  "):gsub("%s+$", "")
	end

	return out
end

local function render_padded_markdown_sheet(sheet)
	local width = math.max(1, sheet.max_col)
	local rows = {}
	local widths = {}
	local last_row = last_non_empty_row(sheet)

	rows[1] = {}
	for col = 1, width do
		rows[1][col] = column_name(col)
		widths[col] = display_len(rows[1][col])
	end

	for r = 1, last_row do
		local source_row = sheet.rows[r] or {}
		local row = {}
		for col = 1, width do
			local value = markdown_cell(source_row[col])
			row[col] = value
			widths[col] = math.max(widths[col] or 0, display_len(value))
		end
		rows[#rows + 1] = row
	end

	local out = {}
	local header = {}
	local separator = {}
	for col = 1, width do
		header[col] = " " .. pad_right(rows[1][col], widths[col]) .. " "
		separator[col] = string.rep("-", widths[col] + 2)
	end
	out[#out + 1] = "|" .. table.concat(header, "|") .. "|"
	out[#out + 1] = "|" .. table.concat(separator, "|") .. "|"

	for r = 2, #rows do
		local cells = {}
		for col = 1, width do
			cells[col] = " " .. pad_right(rows[r][col], widths[col]) .. " "
		end
		out[#out + 1] = "|" .. table.concat(cells, "|") .. "|"
	end

	return out
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" .. cell_to_text(sheet.name)
		end

		local last_row = last_non_empty_row(sheet)
		for r = 1, last_row do
			local source_row = sheet.rows[r] or {}
			local cells = {}
			for col = 1, sheet.max_col do
				cells[col] = cell_to_text(source_row[col])
			end
			out[#out + 1] = table.concat(cells, "\t")
		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
