package main

import (
	"archive/zip"
	"fmt"
	"hash/crc32"
	"os"
	"regexp"
	"strconv"
	"strings"
)

// odsMimetype is the ODF package media type, written as the archive's
// first, uncompressed entry.
const odsMimetype = "application/vnd.oasis.opendocument.spreadsheet"

// writeODS writes a minimal OpenDocument spreadsheet at path with one
// formula per row in column D, plus the same small fixed lookup table
// calc's own corpusEnv provides in columns A:B, rows 1-3 (see
// lookupTableRows), with C1 left blank. See main.go's package doc
// comment for the overall mechanism.
func writeODS(path string, cases []corpusCase) error {
	f, err := os.Create(path)
	if err != nil {
		return err
	}
	defer f.Close()

	zw := zip.NewWriter(f)

	// mimetype must be the first entry, stored uncompressed, per the ODF
	// package format spec. It is written with CreateRaw, not
	// CreateHeader, on purpose: CreateHeader streams the entry and
	// therefore sets the "sizes follow in a data descriptor" flag bit
	// even for a Store entry, and LibreOffice's ODF package reader
	// rejects that outright -- the whole document fails to open with
	// "source file could not be loaded", not with a per-cell error.
	// Verified against LibreOffice 26.8.0.3: CreateHeader here produces
	// an unloadable file, CreateRaw with the sizes and CRC filled in
	// produces a loadable one.
	mh := &zip.FileHeader{Name: "mimetype", Method: zip.Store}
	mh.CRC32 = crc32.ChecksumIEEE([]byte(odsMimetype))
	mh.CompressedSize64 = uint64(len(odsMimetype))
	mh.UncompressedSize64 = uint64(len(odsMimetype))
	mw, err := zw.CreateRaw(mh)
	if err != nil {
		return err
	}
	if _, err := mw.Write([]byte(odsMimetype)); err != nil {
		return err
	}

	cw, err := zw.Create("content.xml")
	if err != nil {
		return err
	}
	if _, err := cw.Write([]byte(contentXML(cases))); err != nil {
		return err
	}

	mfw, err := zw.Create("META-INF/manifest.xml")
	if err != nil {
		return err
	}
	if _, err := mfw.Write([]byte(manifestXML)); err != nil {
		return err
	}

	return zw.Close()
}

const manifestXML = `<?xml version="1.0" encoding="UTF-8"?>
<manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0" manifest:version="1.2">
  <manifest:file-entry manifest:full-path="/" manifest:version="1.2" manifest:media-type="application/vnd.oasis.opendocument.spreadsheet"/>
  <manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/>
</manifest:manifest>
`

// lookupTableRows is the fixed A:B data VLOOKUP/INDEX/MATCH cases
// reference as "A1:B3" (both columns) or "A1:A3" (the number column
// alone) -- byte-identical to the table
// internal/sheets/calc/corpus_test.go's corpusEnv and
// internal/sheets/xlsx/oracle_excelize_test.go's newOracleWorkbook both
// set up, so a generated corpus entry's Expr can be evaluated unchanged
// by both of those harnesses. C1 is deliberately left blank so it reads
// as a genuinely empty cell for the COUNT(A1,C1)/COUNTA(A1,C1) cases;
// each row's own formula therefore goes in column D, clear of the
// table.
var lookupTableRows = []struct {
	num  float64
	text string
}{
	{10, "ten"},
	{20, "twenty"},
	{30, "thirty"},
}

// formulaColumnIndex is the zero-based CSV field the formula cell lands
// in: columns A, B and C are the support table (and its deliberately
// blank C1), so column D is index 3. readResults reads this field, not
// field 0.
const formulaColumnIndex = 3

func contentXML(cases []corpusCase) string {
	var rows strings.Builder
	for i, c := range cases {
		rows.WriteString("      <table:table-row>")
		if i < len(lookupTableRows) {
			rows.WriteString(numberCell(lookupTableRows[i].num))
			rows.WriteString(stringCell(lookupTableRows[i].text))
			rows.WriteString(`<table:table-cell/>`)
		} else {
			rows.WriteString(`<table:table-cell table:number-columns-repeated="3"/>`)
		}
		// office:value is deliberately poisoned with a value no case can
		// produce, so a run in which LibreOffice failed to recalculate
		// would come back as 999999 everywhere rather than as plausible
		// answers. See main's poisonedCacheValue check.
		rows.WriteString(fmt.Sprintf(
			`<table:table-cell table:formula="of:=%s" office:value-type="float" office:value="%s"><text:p>%s</text:p></table:table-cell>`,
			escapeXML(toODFFormula(c.Expr)), poisonedCacheValue, poisonedCacheValue))
		rows.WriteString("</table:table-row>\n")
	}
	// xmlns:of is load-bearing, not decoration: the "of:" in each cell's
	// table:formula="of:=..." is an XML namespace prefix, and without
	// this declaration LibreOffice does not recognise the formula
	// grammar and every single cell comes back as Err:510 rather than a
	// value. Verified against LibreOffice 26.8.0.3 by running the same
	// document with and without it.
	return `<?xml version="1.0" encoding="UTF-8"?>
<office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
  xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
  xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
  xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
  office:version="1.2">
  <office:body>
    <office:spreadsheet>
      <!-- Excel's criteria semantics, stated per DOCUMENT.
           Without this element LibreOffice falls back to ODF's legacy
           default — regular expressions ON, wildcards OFF — so a
           criterion of "t*" is the REGEX t*, which matches nothing in
           "ten". Measured 2026-09-03: every wildcard case in the list
           came back 0, and the first reading of that was that
           LibreOffice's profile disagreed with Excel and could not be
           the oracle for them. It was not a profile and it does not
           disagree; the generator was writing a document that asked
           the wrong question. Corrected 2026-09-04 after review, which
           rebuilt the same file with this one element and got Excel's
           answers from the same binary. -->
      <table:calculation-settings table:use-wildcards="true" table:use-regular-expressions="false"/>
      <table:table table:name="Corpus">
` + rows.String() + `      </table:table>
    </office:spreadsheet>
  </office:body>
</office:document-content>
`
}

// numberCell/stringCell write a literal (not formula) value cell -- the
// lookup table's own data, which must be reflected in the file's actual
// value/type attributes, not just visible text, or LibreOffice would not
// see it as a number/string for VLOOKUP/INDEX/MATCH to compare against.
func numberCell(n float64) string {
	s := strconv.FormatFloat(n, 'g', -1, 64)
	return fmt.Sprintf(`<table:table-cell office:value-type="float" office:value="%s"><text:p>%s</text:p></table:table-cell>`, s, s)
}

func stringCell(s string) string {
	return fmt.Sprintf(`<table:table-cell office:value-type="string"><text:p>%s</text:p></table:table-cell>`, s)
}

// escapeXML escapes a formula for use inside an XML attribute value.
// Every case with a string argument contains double quotes, which would
// otherwise close the table:formula attribute early.
func escapeXML(s string) string {
	s = strings.ReplaceAll(s, "&", "&amp;")
	s = strings.ReplaceAll(s, "<", "&lt;")
	s = strings.ReplaceAll(s, ">", "&gt;")
	return strings.ReplaceAll(s, `"`, "&quot;")
}

// odfFunctionNames maps a function's Excel/English spelling to the name
// LibreOffice actually answers to inside an ODF of: formula. ODF's own
// function set is a standard; functions Microsoft added after it are
// vendor-namespaced, and LibreOffice writes and reads them under the
// COM.MICROSOFT. prefix. Calling one by its bare Excel name in an of:
// formula is not an error LibreOffice reports as such -- it is simply an
// unknown name, and the cell comes back #NAME?.
//
// This was found the hard way on this tool's first real run: IFS and
// CONCAT both came back #NAME?, which classify would have happily
// recorded as the corpus's expected answer, turning "LibreOffice has
// never heard of this ODF spelling" into "a real spreadsheet says these
// functions are errors". checkResults (soffice.go) now refuses a #NAME?
// result outright for exactly that reason.
//
// Add an entry here whenever a case uses a post-ODF Microsoft function.
// The symptom is always the same: a #NAME? that fails the run.
var odfFunctionNames = map[string]string{
	"IFS":    "COM.MICROSOFT.IFS",
	"CONCAT": "COM.MICROSOFT.CONCAT",
}

// funcCallPattern matches an identifier immediately followed by "(",
// i.e. a function call's name. Like the comma substitution below it does
// not skip quoted spans; no case has a bare word followed by "(" inside
// a string literal.
var funcCallPattern = regexp.MustCompile(`[A-Za-z_][A-Za-z0-9_.]*\(`)

// toODFFormula translates a comma-argument-separated Excel-style formula
// (this tool's own case list, and this project's own formula syntax --
// see internal/sheets/calc/parse.go) into ODF's own formula syntax, which
// uses semicolons between arguments. The translation is required, not
// cosmetic: verified against LibreOffice 26.8.0.3, an of:= formula with
// comma separators evaluates to #NAME?, not to a value.
//
// Two things this deliberately does NOT do, both checked by measurement
// rather than assumed:
//   - It does not rewrite A1-style references into ODF's bracketed
//     [.A1:.B3] form. LibreOffice accepts the plain form and resolves it
//     identically (VLOOKUP(20;A1:B3;2;FALSE()) and
//     VLOOKUP(20;[.A1:.B3];2;FALSE()) both return "twenty").
//   - It does not rewrite bare TRUE/FALSE into TRUE()/FALSE().
//     LibreOffice accepts the bare form (IF(TRUE;"yes";"no") returns
//     "yes").
//
// It DOES rewrite function names that ODF vendor-namespaces -- see
// odfFunctionNames.
//
// It is a blunt, comma-to-semicolon substitution -- wrong for any
// formula whose comma appears inside a quoted string argument (a text
// literal that itself contains a comma). None of this tool's own cases
// do; a human adding one that does needs to rewrite this function to
// skip quoted spans, or avoid the comma.
func toODFFormula(expr string) string {
	s := strings.ReplaceAll(expr, ",", ";")
	return funcCallPattern.ReplaceAllStringFunc(s, func(call string) string {
		name := strings.ToUpper(call[:len(call)-1])
		if odf, ok := odfFunctionNames[name]; ok {
			return odf + "("
		}
		return call
	})
}