-- lx-bundle:true

--- Minimal XLSX workbook text extractor.
-- @module plugins.xlsxutils.reader

local spreadsheet = require("core.lib.spreadsheet")

local reader = {}

--- Decode one numeric XML character reference
-- @param digits string captured hexadecimal or decimal digits
-- @param base integer numeric base used by the reference
-- @param reference string original XML reference for error reporting
-- @return string UTF-8 character
local function decode_xml_character(digits, base, reference)
	local value = tonumber(digits, base)
	local valid = value == 0x09
		or value == 0x0A
		or value == 0x0D
		or (value and value >= 0x20 and value <= 0xD7FF)
		or (value and value >= 0xE000 and value <= 0xFFFD)
		or (value and value >= 0x10000 and value <= 0x10FFFF)
	if not valid then
		error("invalid XML character reference '" .. reference .. "'", 0)
	end

	return utf8.char(value)
end

--- Decode predefined and numeric XML entities in text
-- @param text string|nil encoded XML text
-- @return string decoded text
local function xml_unescape(text)
	text = tostring(text or "")
	text = text:gsub("&lt;", "<")
		:gsub("&gt;", ">")
		:gsub("&quot;", '"')
		:gsub("&apos;", "'")
		:gsub("&amp;", "&")
	-- Pattern breakdown: capture hexadecimal digits between the numeric XML prefix and semicolon
	text = text:gsub("&#x([%da-fA-F]+);", function(hex)
		return decode_xml_character(hex, 16, "&#x" .. hex .. ";")
	end)
	-- Pattern breakdown: capture decimal digits between the numeric XML prefix and semicolon
	text = text:gsub("&#(%d+);", function(dec)
		return decode_xml_character(dec, 10, "&#" .. dec .. ";")
	end)
	return text
end

local function strip_namespaces(xml)
	xml = tostring(xml or "")
	xml = xml:gsub("%s+xmlns[:%w_%-]*%s*=%s*\"[^\"]*\"", "")
		:gsub("%s+xmlns[:%w_%-]*%s*=%s*'[^']*'", "")
	xml = xml:gsub("<(/?)[%w_%-]+:", "<%1")
	xml = xml:gsub("([%s<])[%w_%-]+:([%w_%-]+)%s*=", "%1%2=")
	return xml
end

local function attrs(text)
	local out = {}
	for key, value in tostring(text or ""):gmatch("([%w_:%.-]+)%s*=%s*\"([^\"]*)\"") do
		local attr_key = key:match("([^:]+)$") or key
		out[attr_key] = xml_unescape(value)
	end
	for key, value in tostring(text or ""):gmatch("([%w_:%.-]+)%s*=%s*'([^']*)'") do
		local attr_key = key:match("([^:]+)$") or key
		out[attr_key] = xml_unescape(value)
	end
	return out
end

local function dirname(path)
	return (path:match("^(.*)/[^/]*$") or "")
end

local function normalize_path(path)
	local parts = {}
	path = tostring(path or ""):gsub("\\", "/"):gsub("^/+", "")

	for part in path:gmatch("[^/]+") do
		if part == ".." then
			parts[#parts] = nil
		elseif part ~= "." and part ~= "" then
			parts[#parts + 1] = part
		end
	end

	return table.concat(parts, "/")
end

local function resolve_target(base, target)
	target = tostring(target or ""):gsub("\\", "/")
	if target:sub(1, 1) == "/" then
		return normalize_path(target)
	end
	if base == "" then
		return normalize_path(target)
	end
	return normalize_path(base .. "/" .. target)
end

local function rel_type_name(value)
	return tostring(value or ""):match("([^/]+)$") or ""
end

local function read_xml(archive, path)
	local data, err = archive:read(path)
	if not data then
		return nil, err
	end
	return strip_namespaces(data)
end

local function parse_relationships(xml)
	local rels = {}

	for attr_text in tostring(xml or ""):gmatch("<Relationship%s+([^>]-)/?>") do
		local a = attrs(attr_text)
		if a.Id then
			rels[a.Id] = a
		end
		rels[#rels + 1] = a
	end

	return rels
end

local function parse_rich_text(xml)
	local parts = {}
	xml = tostring(xml or ""):gsub("<rPh[^>]*>.-</rPh>", ""):gsub("<phoneticPr[^>]*/>", "")
	for text in xml:gmatch("<t[^>]*>(.-)</t>") do
		parts[#parts + 1] = xml_unescape(text)
	end
	return table.concat(parts)
end

local function parse_shared_strings(xml)
	local strings = {}

	for si in tostring(xml or ""):gmatch("<si[^>]*>(.-)</si>") do
		strings[#strings + 1] = parse_rich_text(si)
	end

	return strings
end

local function column_index(cell_ref)
	local letters = tostring(cell_ref or ""):match("^([A-Z]+)")
	if not letters then
		return nil
	end

	local value = 0
	for i = 1, #letters do
		value = (value * 26) + (letters:byte(i) - 64)
	end
	return value
end

local function row_index(cell_ref)
	local digits = tostring(cell_ref or ""):match("(%d+)")
	return digits and tonumber(digits) or nil
end

local function bool_value(value)
	return tostring(value or "") == "1" and "TRUE" or "FALSE"
end

local function cell_value(cell_xml, cell_attrs, shared_strings)
	local cell_type = cell_attrs.t or ""

	if cell_type == "inlineStr" then
		local inline = cell_xml:match("<is[^>]*>(.-)</is>")
		return inline and parse_rich_text(inline) or ""
	end

	local value = cell_xml:match("<v[^>]*>(.-)</v>") or ""
	value = xml_unescape(value)

	if cell_type == "s" then
		return shared_strings[(tonumber(value) or -1) + 1] or ""
	elseif cell_type == "b" then
		return bool_value(value)
	end

	return value
end

local function parse_sheet(xml, name, shared_strings)
	local sheet = {
		name = name,
		rows = {},
		max_row = 0,
		max_col = 0,
	}
	local inferred_row = 1

	for row_attrs_text, row_xml in tostring(xml or ""):gmatch("<row([^>]*)>(.-)</row>") do
		local row_attrs = attrs(row_attrs_text)
		local r = tonumber(row_attrs.r) or inferred_row
		local inferred_col = 1
		sheet.rows[r] = sheet.rows[r] or {}

		-- Pattern breakdown: match a 'c' cell tag with attributes through its self-closing '/>'
		local expanded_row_xml = row_xml:gsub("<c(%s+[^>]*)/>", "<c%1></c>")
		-- Pattern breakdown: match the valid attribute-free '<c/>' form
		expanded_row_xml = expanded_row_xml:gsub("<c%s*/>", "<c></c>")

		for cell_attrs_text, cell_xml in expanded_row_xml:gmatch("<c([^>]*)>(.-)</c>") do
			local cell_attrs = attrs(cell_attrs_text)
			local ref = cell_attrs.r
			local c = column_index(ref) or inferred_col
			local rr = row_index(ref) or r

			if rr ~= r then
				sheet.rows[rr] = sheet.rows[rr] or {}
			end
			sheet.rows[rr][c] = cell_value(cell_xml, cell_attrs, shared_strings)
			sheet.max_row = math.max(sheet.max_row, rr)
			sheet.max_col = math.max(sheet.max_col, c)
			inferred_col = c + 1
		end

		sheet.max_row = math.max(sheet.max_row, r)
		inferred_row = r + 1
	end

	return sheet
end

--- Load one XLSX workbook without its public error boundary
-- @param filename string archive path
-- @return table|nil workbook parsed workbook
-- @return string|nil error extraction error
local function load_workbook_unchecked(filename)
	local archive <close>, err = pack.zip.open_file_read(filename)
	if not archive then
		return nil, err
	end

	local package_rels, package_rels_err = read_xml(archive, "_rels/.rels")
	if not package_rels then
		return nil, package_rels_err or "missing package relationships"
	end

	local workbook_path
	for _, rel in ipairs(parse_relationships(package_rels)) do
		if rel_type_name(rel.Type) == "officeDocument" then
			workbook_path = resolve_target("", rel.Target)
			break
		end
	end
	workbook_path = workbook_path or "xl/workbook.xml"

	local workbook_xml, workbook_err = read_xml(archive, workbook_path)
	if not workbook_xml then
		return nil, workbook_err or "missing workbook.xml"
	end

	local workbook_base = dirname(workbook_path)
	local workbook_rels_path = resolve_target(workbook_base, "_rels/" .. workbook_path:match("[^/]+$") .. ".rels")
	local workbook_rels_xml, workbook_rels_err = read_xml(archive, workbook_rels_path)
	if not workbook_rels_xml then
		return nil, workbook_rels_err or "missing workbook relationships"
	end

	local workbook_rels = parse_relationships(workbook_rels_xml)

	local shared_strings = {}
	for _, rel in ipairs(workbook_rels) do
		if rel_type_name(rel.Type) == "sharedStrings" then
			local shared_path = resolve_target(workbook_base, rel.Target)
			local shared_xml, shared_err = read_xml(archive, shared_path)
			if not shared_xml then
				return nil, shared_err or "missing shared strings"
			end

			shared_strings = parse_shared_strings(shared_xml)
			break
		end
	end

	local sheets = {}
	for sheet_attr_text in workbook_xml:gmatch("<sheet%s+([^>]-)/?>") do
		local sheet_attrs = attrs(sheet_attr_text)
		local rel = workbook_rels[sheet_attrs.id]
		if rel and rel_type_name(rel.Type) == "worksheet" then
			local path = resolve_target(workbook_base, rel.Target)
			local sheet_xml, sheet_err = read_xml(archive, path)
			if not sheet_xml then
				return nil, sheet_err or "missing worksheet"
			end

			sheets[#sheets + 1] = parse_sheet(sheet_xml, sheet_attrs.name or path, shared_strings)
		end
	end

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

	return { sheets = sheets }
end

--- Load one XLSX workbook and normalize parser failures
-- @param filename string archive path
-- @return table|nil workbook parsed workbook
-- @return string|nil error extraction error
local function load_workbook(filename)
	local ok, workbook, err = pcall(load_workbook_unchecked, filename)
	if not ok then
		return nil, "invalid XLSX XML: " .. tostring(workbook)
	end

	return workbook, err
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
