-- lx-bundle:true

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

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

local reader = {}

local function xml_unescape(text)
	text = tostring(text or "")
	text = text:gsub("&lt;", "<")
		:gsub("&gt;", ">")
		:gsub("&quot;", '"')
		:gsub("&apos;", "'")
		:gsub("&amp;", "&")
	text = text:gsub("&#x([%da-fA-F]+);", function(hex)
		local code = tonumber(hex, 16)
		return code and utf8.char(code) or ""
	end)
	text = text:gsub("&#(%d+);", function(dec)
		local code = tonumber(dec, 10)
		return code and utf8.char(code) or ""
	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 {}

		for cell_attrs_text, cell_xml in 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

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

	local package_rels = read_xml(archive, "_rels/.rels")
	if not package_rels then
		return nil, "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 = read_xml(archive, workbook_rels_path) or ""
	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_xml = read_xml(archive, resolve_target(workbook_base, rel.Target))
			if shared_xml then
				shared_strings = parse_shared_strings(shared_xml)
			end
			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 = read_xml(archive, path)
			if sheet_xml then
				sheets[#sheets + 1] = parse_sheet(sheet_xml, sheet_attrs.name or path, shared_strings)
			end
		end
	end

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

	return { sheets = sheets }
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 c = 1, sheet.max_col do
		if cell_to_text(row[c]) ~= "" 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 c = 1, sheet.max_col do
			local value = cell_to_text(source_row[c])
			row[c] = value
			widths[c] = math.max(widths[c] 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 c = 1, sheet.max_col do
			cells[c] = pad_right(rows[r][c], widths[c] 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 c = 1, width do
		rows[1][c] = column_name(c)
		widths[c] = display_len(rows[1][c])
	end

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

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

	for r = 2, #rows do
		local cells = {}
		for c = 1, width do
			cells[c] = " " .. pad_right(rows[r][c], widths[c]) .. " "
		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 c = 1, sheet.max_col do
				cells[c] = cell_to_text(source_row[c])
			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
