"""Reproduce the narrow SkillSignal XLSX conversion check with synthetic data."""
import argparse
import hashlib
import importlib.metadata as metadata
import json
import platform
import shutil
import subprocess
import sys
import tempfile
from pathlib import Path

from openpyxl import load_workbook

EXPECTED_SCRIPT_SHA256 = "ffbd8bf06d66e01e9f5ede866df5636eb0dc0ee7aab95b68b8474c8d89b99e14"


def digest(path):
    return hashlib.sha256(path.read_bytes()).hexdigest()


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--converter", type=Path, required=True)
    parser.add_argument("--fixture", type=Path, required=True)
    parser.add_argument("--output-dir", type=Path, required=True)
    args = parser.parse_args()
    assert digest(args.converter) == EXPECTED_SCRIPT_SHA256, "Converter does not match the pinned revision."
    versions = {name: metadata.version(name) for name in ["markitdown", "pandas", "openpyxl"]}
    assert versions == {"markitdown": "0.1.8", "pandas": "3.0.1", "openpyxl": "3.1.5"}, "Use the recorded dependency versions."
    formula_book = load_workbook(args.fixture, data_only=False)
    value_book = load_workbook(args.fixture, data_only=True)
    assert formula_book.sheetnames == ["Work", "Stock"]
    assert formula_book["Work"]["D2"].value == "=B2*C2"
    assert value_book["Work"]["D2"].value == 125
    assert value_book["Work"]["D6"].value == 165
    assert value_book["Stock"]["B2"].value == 0
    assert value_book["Stock"]["B3"].value is None
    formula_book.close()
    value_book.close()
    source_hash = digest(args.fixture)
    with tempfile.TemporaryDirectory(prefix="skillsignal-excel-check-") as folder:
        fixture = Path(folder) / "conversion-fixture.xlsx"
        shutil.copy2(args.fixture, fixture)
        before = digest(fixture)
        result = subprocess.run([sys.executable, str(args.converter.resolve()), str(fixture)], capture_output=True, text=True)
        assert result.returncode == 0, "Converter failed."
        assert digest(fixture) == before == source_hash
        markdown = (fixture.parent / fixture.stem / "conversion-fixture.md").read_text(encoding="utf-8")
        design = next(line for line in markdown.splitlines() if line.startswith("| Design |"))
        header = next(line for line in markdown.splitlines() if line.startswith("| Task |"))
        columns = lambda row: len(row.strip().strip("|").split("|"))
        observations = {
            "both_sheet_headings_present": "## Work" in markdown and "## Stock" in markdown,
            "cached_formula_values_present": "125.0" in markdown and "165.0" in markdown,
            "formula_expression_absent": "=B2*C2" not in markdown,
            "zero_retained": "| Zero stock | 0.0 |" in markdown,
            "blank_rendered_as_nan": "| Unknown stock | NaN |" in markdown,
            "unicode_text_retained": "Unicode item 设计" in markdown,
            "multiline_text_uses_literal_backslash_n": "First line\\nSecond line" in markdown,
            "header_column_count": columns(header),
            "pipe_containing_row_column_count": columns(design),
            "no_image_directory_for_this_image_free_fixture": not (fixture.parent / fixture.stem / "img").exists(),
        }
        assert all(value for key, value in observations.items() if not key.endswith("column_count"))
        assert observations["header_column_count"] == 5
        assert observations["pipe_containing_row_column_count"] == 6
    report = {
        "check_date_local": "2026-10-18",
        "timezone": "America/Los_Angeles",
        "platform": "Windows",
        "python": platform.python_version(),
        "dependencies": versions,
        "upstream_revision": "d7e4ad98ed8fd72e4744ee604e6277eb36748fe2",
        "converter_sha256": EXPECTED_SCRIPT_SHA256,
        "fixture_sha256": source_hash,
        "markdown_sha256": hashlib.sha256(markdown.encode("utf-8")).hexdigest(),
        "exit_code": result.returncode,
        "input_unchanged": True,
        "observations": observations,
        "scope": "One synthetic two-sheet XLSX; standalone script only. No Agent session, Excel recalculation, charts, images, protected files, batch conversion, or security test.",
    }
    args.output_dir.mkdir(parents=True, exist_ok=True)
    (args.output_dir / "converted.md").write_text(markdown, encoding="utf-8", newline="\n")
    (args.output_dir / "results.json").write_text(json.dumps(report, ensure_ascii=False, indent=2) + "\n", encoding="utf-8", newline="\n")
    print(json.dumps(report, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()