-- 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 XLSX workbook text extractor
-- @module plugins.msoutils.xlsx

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

local reader = {}

--- Reject invalid character references decoded by the XML API
-- @param text string decoded XML text
-- @return string text unchanged valid text
local function validate_xml_text(text)
	return ooxml.validate_text(text, "invalid XML character reference")
end

--- Parse one SpreadsheetML UTF-16 escape at an exact byte position
-- @param text string Encoded XML text
-- @param index integer Escape start byte
-- @return integer|nil code_unit UTF-16 code unit
-- @return string|nil token Original seven-byte token
local function spreadsheetml_escape_at(text, index)
	local token = text:sub(index, index + 6)
	-- Pattern breakdown: '_x', exactly four hexadecimal digits, and '_' fill one complete token
	local hex = token:match("^_x([%da-fA-F][%da-fA-F][%da-fA-F][%da-fA-F])_$")

	return hex and tonumber(hex, 16) or nil, hex and token or nil
end

--- Decode SpreadsheetML UTF-16 escapes without re-decoding protected literals
-- @param text string Encoded XML text
-- @return string decoded UTF-8 text
local function decode_spreadsheetml_escapes(text)
	local out = {}
	local index = 1

	while index <= #text do
		local escape_start = text:find("_x", index, true)
		if not escape_start then
			out[#out + 1] = text:sub(index)
			break
		end

		out[#out + 1] = text:sub(index, escape_start - 1)
		local code_unit, token = spreadsheetml_escape_at(text, escape_start)
		if not code_unit then
			out[#out + 1] = "_x"
			index = escape_start + 2
		elseif code_unit == 0x005F then
			local _, protected_token = spreadsheetml_escape_at(text, escape_start + 7)
			if protected_token then
				out[#out + 1] = protected_token
				index = escape_start + 14
			else
				out[#out + 1] = "_"
				index = escape_start + 7
			end
		elseif code_unit >= 0xD800 and code_unit <= 0xDBFF then
			local low_surrogate = spreadsheetml_escape_at(text, escape_start + 7)
			if low_surrogate and low_surrogate >= 0xDC00 and low_surrogate <= 0xDFFF then
				local codepoint = 0x10000 + (code_unit - 0xD800) * 0x400 + low_surrogate - 0xDC00
				out[#out + 1] = utf8.char(codepoint)
				index = escape_start + 14
			else
				out[#out + 1] = token -- Preserve an isolated high surrogate as authored text
				index = escape_start + 7
			end
		elseif code_unit >= 0xDC00 and code_unit <= 0xDFFF then
			out[#out + 1] = token -- Preserve an isolated low surrogate as authored text
			index = escape_start + 7
		else
			out[#out + 1] = utf8.char(code_unit)
			index = escape_start + 7
		end
	end

	return table.concat(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 child_nodes = ooxml.child_nodes

--- Read one XML attribute
-- @param node userdata|nil XML node
-- @param name string qualified attribute name
-- @return string|nil value attribute value
local function node_attribute(node, name)
	return node and node:attribute(name) or nil
end

--- Decode one user-facing SpreadsheetML attribute
-- @param node userdata|nil XML node
-- @param name string qualified attribute name
-- @return string|nil value decoded attribute value
local function text_attribute(node, name)
	local value = node_attribute(node, name)

	return value and decode_spreadsheetml_escapes(validate_xml_text(value)) or nil
end

--- Collect one element's direct text and CDATA children
-- @param node userdata|nil XML element
-- @return string text decoded SpreadsheetML text
local function node_text(node)
	local parts = {}

	for child in child_nodes(node) do
		local node_type = child:type()
		if node_type == "text" or node_type == "cdata" then
			parts[#parts + 1] = child:value()
		end
	end

	return decode_spreadsheetml_escapes(validate_xml_text(table.concat(parts)))
end

--- Read and parse one XML archive entry
-- @param archive userdata open ZIP archive
-- @param path string archive entry path
-- @return userdata|nil document parsed XML document
-- @return string|nil error read or parse error
local function read_xml(archive, path)
	local data, err = archive:read(path)
	if not data then
		return nil, err
	end

	local document, parse_error = xml.parse(data)
	if not document then
		return nil, 'invalid XLSX XML in "' .. path .. '": ' .. tostring(parse_error)
	end

	return document
end

--- Read package relationships by ID and source order
-- @param document userdata|nil relationships XML document
-- @return table relationships indexed and ordered relationships
local function parse_relationships(document)
	local rels = {}
	local root = document and document:root() or nil

	for node in child_nodes(root, "Relationship") do
		local relationship = {
			id = node_attribute(node, "Id"),
			type = node_attribute(node, "Type"),
			target = node_attribute(node, "Target"),
			target_mode = node_attribute(node, "TargetMode"),
		}
		if relationship.id then
			rels[relationship.id] = relationship
		end
		rels[#rels + 1] = relationship
	end

	return rels
end

--- Join visible rich-text runs and ignore phonetic guides
-- @param node userdata|nil shared or inline string node
-- @return string text visible string text
local function parse_rich_text(node)
	local parts = {}

	for child in child_nodes(node) do
		local text_node
		if child:name() == "t" then
			text_node = child
		elseif child:name() == "r" then
			text_node = child:first_child("t")
		end
		if text_node then
			parts[#parts + 1] = node_text(text_node)
		end
	end

	return table.concat(parts)
end

--- Read workbook shared strings in source order
-- @param document userdata|nil shared-strings XML document
-- @return table strings decoded strings
local function parse_shared_strings(document)
	local strings = {}
	local root = document and document:root() or nil

	for node in child_nodes(root, "si") do
		strings[#strings + 1] = parse_rich_text(node)
	end

	return strings
end

--- Read custom number formats and cell XF references from styles XML
-- @param document userdata|nil styles XML document
-- @return table styles parsed number-format tables
local function parse_styles(document)
	local styles = { formats = {}, xfs = {} }
	local root = document and document:root() or nil
	local number_formats = root and root:first_child("numFmts") or nil

	for node in child_nodes(number_formats, "numFmt") do
		local format_id = tonumber(node_attribute(node, "numFmtId"))
		local format_code = text_attribute(node, "formatCode")
		if format_id and format_code then
			styles.formats[format_id] = format_code
		end
	end

	local cell_xfs = root and root:first_child("cellXfs") or nil
	for node in child_nodes(cell_xfs, "xf") do
		styles.xfs[#styles.xfs + 1] = tonumber(node_attribute(node, "numFmtId")) or 0
	end

	return styles
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

--- Read one cell value and apply its supported display format
-- @param cell userdata cell XML node
-- @param shared_strings table workbook shared strings
-- @param styles table parsed workbook styles
-- @param date1904 boolean whether the workbook uses the 1904 epoch
-- @return string value visible cell value
local function cell_value(cell, shared_strings, styles, date1904)
	local cell_type = node_attribute(cell, "t") or ""

	if cell_type == "inlineStr" then
		local inline = cell:first_child("is")
		return inline and parse_rich_text(inline) or ""
	end

	local value_node = cell:first_child("v")
	local value = value_node and node_text(value_node) or ""

	if cell_type == "s" then
		return shared_strings[(tonumber(value) or -1) + 1] or ""
	elseif cell_type == "b" then
		return bool_value(value)
	elseif cell_type == "" or cell_type == "n" then
		local number = tonumber(value)
		local xf_index = tonumber(node_attribute(cell, "s")) or 0
		local format_id = styles.xfs[xf_index + 1]
		if number and format_id then
			return excel_number_format.format_number(number, format_id, styles.formats, date1904) or value
		end
	end

	return value
end

--- Parse one worksheet and its formatted cell values
-- @param document userdata worksheet XML document
-- @param name string sheet name
-- @param shared_strings table workbook shared strings
-- @param styles table parsed workbook styles
-- @param date1904 boolean whether the workbook uses the 1904 epoch
-- @return table sheet parsed worksheet
local function parse_sheet(document, name, shared_strings, styles, date1904)
	local sheet = {
		name = name,
		rows = {},
		max_row = 0,
		max_col = 0,
	}
	local root = document:root()
	local sheet_data = root and root:first_child("sheetData") or nil
	local inferred_row = 1

	for row in child_nodes(sheet_data, "row") do
		local r = tonumber(node_attribute(row, "r")) or inferred_row
		local inferred_col = 1
		sheet.rows[r] = sheet.rows[r] or {}

		for cell in child_nodes(row, "c") do
			local ref = node_attribute(cell, "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, shared_strings, styles, date1904)
			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_document, workbook_err = read_xml(archive, workbook_path)
	if not workbook_document 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_document, workbook_rels_err = read_xml(archive, workbook_rels_path)
	if not workbook_rels_document then
		return nil, workbook_rels_err or "missing workbook relationships"
	end

	local workbook_rels = parse_relationships(workbook_rels_document)
	local workbook_root = workbook_document:root()
	local workbook_properties = workbook_root and workbook_root:first_child("workbookPr") or nil
	local date1904_value = node_attribute(workbook_properties, "date1904")
	local date1904 = date1904_value == "1" or date1904_value == "true"

	local shared_strings = {}
	local styles = { formats = {}, xfs = {} }
	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_document, shared_err = read_xml(archive, shared_path)
			if not shared_document then
				return nil, shared_err or "missing shared strings"
			end

			shared_strings = parse_shared_strings(shared_document)
		elseif rel_type_name(rel.type) == "styles" then
			local styles_path = resolve_target(workbook_base, rel.target)
			local styles_document, styles_err = read_xml(archive, styles_path)
			if not styles_document then
				return nil, styles_err or "missing styles"
			end

			styles = parse_styles(styles_document)
		end
	end

	local sheets = {}
	local sheet_nodes = workbook_root and workbook_root:first_child("sheets") or nil
	for sheet in child_nodes(sheet_nodes, "sheet") do
		local relationship_id = node_attribute(sheet, "r:id") or node_attribute(sheet, "id")
		local rel = workbook_rels[relationship_id]
		if rel and rel_type_name(rel.type) == "worksheet" then
			local path = resolve_target(workbook_base, rel.target)
			local sheet_document, sheet_err = read_xml(archive, path)
			if not sheet_document then
				return nil, sheet_err or "missing worksheet"
			end

			sheets[#sheets + 1] = parse_sheet(
				sheet_document,
				text_attribute(sheet, "name") or path,
				shared_strings,
				styles,
				date1904
			)
		end
	end

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

	return { sheets = sheets, date1904 = date1904 }
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
