#!/usr/bin/env python3
"""Offline cell-type fixture. Python 3.9+, XlsxWriter 3.3.1.

Run: python agent-spreadsheet-text.py [--test]
Uses in-memory workbooks; never launches a spreadsheet or contacts a URL.
This is a bounded export example, not a general XLSX security validator.
"""
import csv
import io
import json
import sys
import unittest
import xml.etree.ElementTree as ET
import zipfile

import xlsxwriter

NS = {"s": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
VALUES = ["=1+1", "+1+1", "-1+1", "@SUM(A1:A2)",
          "https://example.invalid/review", "00123", 'note, "quoted"',
          "line one\nline two", "＝1+1", "ordinary text"]


def make_workbook(values, strict=True):
    """One text column, at most 1000 rows and 1000 characters per cell."""
    values = list(values)
    if len(values) > 1000:
        raise ValueError("too many rows")
    for value in values:
        if type(value) is not str:
            raise TypeError("text column requires strings")
        if not value or len(value) > 1000:
            raise ValueError("cell must contain 1..1000 characters")
        if any(ord(c) < 32 and c not in "\n\t" for c in value):
            raise ValueError("unsupported control character")
    output = io.BytesIO()
    options = {"in_memory": True}
    if strict:
        options.update(strings_to_formulas=False, strings_to_urls=False,
                       strings_to_numbers=False)
    with xlsxwriter.Workbook(output, options) as book:
        sheet = book.add_worksheet("Review")
        for row, value in enumerate(values):
            writer = sheet.write_string if strict else sheet.write
            result = writer(row, 0, value)
            if result != 0:
                raise ValueError("cell write failed: %s" % result)
    return output.getvalue()


def inspect_workbook(data):
    """Inspect only the known single-sheet fixture, not arbitrary uploads."""
    with zipfile.ZipFile(io.BytesIO(data)) as archive:
        sheet = ET.fromstring(archive.read("xl/worksheets/sheet1.xml"))
        strings = ET.fromstring(archive.read("xl/sharedStrings.xml"))
        text = ["".join(si.itertext()) for si in strings.findall("s:si", NS)]
        cells = sheet.findall(".//s:c", NS)
        restored = [text[int(c.find("s:v", NS).text)]
                    for c in cells if c.get("t") == "s"]
        return {
            "formulas": len(sheet.findall(".//s:f", NS)),
            "hyperlinks": len(sheet.findall(".//s:hyperlink", NS)),
            "cells": len(cells),
            "string_cells": sum(c.get("t") == "s" for c in cells),
            "restored": restored,
        }


def csv_roundtrip(value):
    output = io.StringIO(newline="")
    csv.writer(output, quoting=csv.QUOTE_ALL).writerow([value])
    output.seek(0)
    return next(csv.reader(output))[0]


class ExportTests(unittest.TestCase):
    def test_default_write_emits_formula(self):
        self.assertEqual(inspect_workbook(make_workbook(VALUES, False))["formulas"], 1)

    def test_default_write_emits_hyperlink(self):
        self.assertEqual(inspect_workbook(make_workbook(VALUES, False))["hyperlinks"], 1)

    def test_strict_has_no_formulas_or_hyperlinks(self):
        result = inspect_workbook(make_workbook(VALUES))
        self.assertEqual((result["formulas"], result["hyperlinks"]), (0, 0))

    def test_every_cell_is_a_string(self):
        result = inspect_workbook(make_workbook(VALUES))
        self.assertEqual(result["cells"], len(VALUES))
        self.assertEqual(result["string_cells"], len(VALUES))

    def test_exact_text_roundtrip(self):
        self.assertEqual(inspect_workbook(make_workbook(VALUES))["restored"], VALUES)

    def test_csv_quotes_preserve_formula_prefix(self):
        self.assertEqual(csv_roundtrip("=1+1"), "=1+1")

    def test_reject_wrong_types(self):
        for value in [None, True, 123, {}, []]:
            with self.subTest(value=value), self.assertRaises(TypeError):
                make_workbook([value])

    def test_cell_limits(self):
        for value in ["", "x" * 1001, "bad\x00text"]:
            with self.subTest(length=len(value)), self.assertRaises(ValueError):
                make_workbook([value])
        self.assertEqual(inspect_workbook(make_workbook(["x" * 1000]))["cells"], 1)

    def test_row_limit(self):
        with self.assertRaises(ValueError):
            make_workbook(["x"] * 1001)


if __name__ == "__main__":
    if "--test" in sys.argv:
        unittest.main(argv=[sys.argv[0]])
    else:
        result = {}
        for label, strict in [("generic_write", False), ("explicit_text", True)]:
            report = inspect_workbook(make_workbook(VALUES, strict))
            report.pop("restored")
            result[label] = report
        print(json.dumps(result, indent=2))
