"""Additional spreadsheet formats in the viewer (#153 A16): .xlsm editable
with macros preserved, .xls/.ods read-only renders and .csv A1-addressed
saves."""

from __future__ import annotations

from pathlib import Path

import pytest

openpyxl = pytest.importorskip("openpyxl")

VAULT = "TestVault"


@pytest.fixture
def formats_vault(test_vault_dir: str) -> Path:
    """Directory of the test vault, for fixture files written in-place."""
    return Path(test_vault_dir)


@pytest.fixture
def xlsm_book(formats_vault: Path) -> str:
    """A macro-enabled workbook: one sheet, two cells, a fake VBA blob."""
    path = formats_vault / "macro.xlsm"
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Data"
    ws["A1"] = "Nom"
    ws["A2"] = "Ada"
    wb.save(path)
    # Inject a minimal vbaProject.bin part so the archive really IS a .xlsm
    # (keep_vba only matters when macros exist).
    import shutil
    import zipfile

    real = path.with_suffix(".tmp.xlsm")
    with zipfile.ZipFile(path) as zin, zipfile.ZipFile(real, "w") as zout:
        for item in zin.namelist():
            zout.writestr(item, zin.read(item))
        zout.writestr("xl/vbaProject.bin", b"VBA-FAKE-CONTENT")
    shutil.move(real, path)
    return "macro.xlsm"


@pytest.fixture
def ods_book(formats_vault: Path) -> str:
    """A small ODS spreadsheet."""
    # The odfpy distribution installs a top-level module named `odf`.
    pytest.importorskip("odf")
    from odf.opendocument import OpenDocumentSpreadsheet
    from odf.table import Table, TableCell, TableRow
    from odf.text import P

    path = formats_vault / "classeur.ods"
    doc = OpenDocumentSpreadsheet()
    table = Table(name="Feuille1")
    for values in (["Ville", "Pop"], ["Paris", "2100000"]):
        tr = TableRow()
        for v in values:
            tc = TableCell(valuetype="string")
            tc.addElement(P(text=str(v)))
            tr.addElement(tc)
        table.addElement(tr)
    doc.spreadsheet.addElement(table)
    doc.save(str(path))
    return "classeur.ods"


class TestXlsmEditable:
    def test_read_serves_the_xlsx_viewer_payload(self, client, xlsm_book):
        resp = client.get(f"/api/file/{VAULT}", params={"path": xlsm_book})
        assert resp.status_code == 200
        data = resp.json()
        assert data["is_xlsx"] is True
        assert data["extension"] == ".xlsm"
        # Macros are NOT lossy for .xlsm: keep_vba preserves them, so no
        # confirmation round-trip is ever triggered.
        assert data["xlsx_lossy_features"] == []
        assert 'data-cell="A1"' in data["xlsx_sheets"][0]["html"]

    def test_save_round_trips_and_keeps_vba(self, client, xlsm_book):
        resp = client.put(
            f"/api/file/{VAULT}/xlsx/save",
            params={"path": xlsm_book},
            json={"sheet": "Data", "cells": {"A2": "Grace"}},
        )
        assert resp.status_code == 200
        import zipfile

        # Re-read through the API instead of guessing the root on disk.
        reread = client.get(f"/api/file/{VAULT}", params={"path": xlsm_book})
        assert "Grace" in reread.json()["xlsx_sheets"][0]["html"]
        # The fake VBA part survived the round-trip.
        with zipfile.ZipFile(_vault_file(client, xlsm_book)) as zf:
            assert "xl/vbaProject.bin" in zf.namelist()

    def test_sheet_window_is_served_for_xlsm(self, client, xlsm_book):
        """BUG-097 — a .xlsm is editable, so its lazy row windows must work."""
        resp = client.get(
            f"/api/file/{VAULT}/xlsx/sheet",
            params={"path": xlsm_book, "sheet": "Data", "offset": 0, "limit": 100},
        )
        assert resp.status_code == 200, resp.text
        body = resp.json()
        assert body["sheet"] == "Data"
        assert 'data-cell="A2"' in body["html"]
        assert body["has_more"] is False

    def test_sheet_window_still_refuses_other_formats(self, client, formats_vault):
        (formats_vault / "plain.csv").write_text("a,b\n", encoding="utf-8")
        resp = client.get(
            f"/api/file/{VAULT}/xlsx/sheet",
            params={"path": "plain.csv", "sheet": "Feuille1"},
        )
        assert resp.status_code == 415

    def test_lossy_gate_is_skipped_for_xlsm(self, client, formats_vault, xlsm_book):
        from backend.services.mutations import edit_xlsx_cells

        # `client` is required: the service resolves the vault through the app
        # registry (the test used to pass only thanks to state leaked by an
        # earlier test in this class).
        # No force flag: the save must succeed despite the vbaProject part
        # (it would raise xlsx_lossy_content on a plain .xlsx).
        result = edit_xlsx_cells(VAULT, xlsm_book, "Data", {"A2": "Alan"})
        assert result["success"] is True


class TestLegacyReadOnly:
    def test_xls_renders_cells(self, client, formats_vault):
        pytest.importorskip("xlrd")
        import shutil

        shutil.copy("tests/fixtures/sample.xls", formats_vault / "sample.xls")
        resp = client.get(f"/api/file/{VAULT}", params={"path": "sample.xls"})
        assert resp.status_code == 200
        data = resp.json()
        assert data["is_xlsx"] is True
        assert data["xlsx_readonly"] is True
        sheet = data["xlsx_sheets"][0]
        assert sheet["name"] == "Data"
        assert "Produit" in sheet["html"]
        assert "Café" in sheet["html"]

    def test_ods_renders_cells(self, client, ods_book):
        resp = client.get(f"/api/file/{VAULT}", params={"path": ods_book})
        assert resp.status_code == 200
        data = resp.json()
        assert data["xlsx_readonly"] is True
        html = data["xlsx_sheets"][0]["html"]
        assert "Paris" in html
        assert "2100000" in html

    def test_readonly_meta_is_empty(self, client, formats_vault):
        pytest.importorskip("xlrd")
        import shutil

        shutil.copy("tests/fixtures/sample.xls", formats_vault / "sample.xls")
        sheet = client.get(
            f"/api/file/{VAULT}", params={"path": "sample.xls"}
        ).json()["xlsx_sheets"][0]
        assert sheet["styles"] == {}
        assert sheet["merges"] == []
        assert sheet["freeze"] == ""

    def test_broken_legacy_file_yields_one_empty_sheet(self, client, formats_vault):
        (formats_vault / "broken.xls").write_bytes(b"not an ole file")
        resp = client.get(f"/api/file/{VAULT}", params={"path": "broken.xls"})
        assert resp.status_code == 200
        sheet = resp.json()["xlsx_sheets"][0]
        assert sheet["rows"] == 0


class TestCsvDelimiter:
    """BUG-098 — the delimiter is detected, not assumed to be a comma."""

    def test_sniffing_handles_the_common_separators(self):
        from backend.xlsx_reader import sniff_csv_delimiter

        assert sniff_csv_delimiter("a;b\n1;2\n") == ";"
        assert sniff_csv_delimiter("a,b\n1,2\n") == ","
        assert sniff_csv_delimiter("a\tb\n1\t2\n") == "\t"

    def test_single_column_falls_back_to_a_comma(self):
        from backend.xlsx_reader import sniff_csv_delimiter

        assert sniff_csv_delimiter("juste\nune\ncolonne\n") == ","


class TestCsvEditable:
    def test_read_renders_the_xlsx_shaped_table(self, client, formats_vault):
        (formats_vault / "liste.csv").write_text("a,b\n1,2\n", encoding="utf-8")
        resp = client.get(f"/api/file/{VAULT}", params={"path": "liste.csv"})
        assert resp.status_code == 200
        data = resp.json()
        assert data["is_csv"] is True
        html = data["html"]
        assert 'data-cell="A1"' in html
        assert "xlsx-table" in html

    def test_save_cells_and_grow_the_grid(self, client, formats_vault):
        (formats_vault / "liste.csv").write_text("a,b\n1,2\n", encoding="utf-8")
        resp = client.put(
            f"/api/file/{VAULT}/csv/save",
            params={"path": "liste.csv"},
            json={"cells": {"B1": "modifié", "C3": "nouveau"}},
        )
        assert resp.status_code == 200
        text = _vault_file(client, "liste.csv").read_text(encoding="utf-8")
        assert "modifié" in text
        assert "nouveau" in text
        # Existing rows survive, and the reference beyond the extent grew the
        # grid to 3 rows x 3 cols.
        assert text.startswith("a,modifié")
        assert "1,2" in text
        assert text.count("\n") >= 2

    def test_csv_values_are_stored_verbatim(self, client, formats_vault):
        (formats_vault / "formules.csv").write_text("x\n", encoding="utf-8")
        client.put(
            f"/api/file/{VAULT}/csv/save",
            params={"path": "formules.csv"},
            json={"cells": {"A1": "=1+1"}},
        )
        text = _vault_file(client, "formules.csv").read_text(encoding="utf-8")
        assert "=1+1" in text  # no formula engine: stored as text

    def test_quoting_survives_a_round_trip(self, client, formats_vault):
        (formats_vault / "quotes.csv").write_text('nom\n"Dupont, Jean"\n', encoding="utf-8")
        client.put(
            f"/api/file/{VAULT}/csv/save",
            params={"path": "quotes.csv"},
            json={"cells": {"A2": "Martin, Pierre"}},
        )
        text = _vault_file(client, "quotes.csv").read_text(encoding="utf-8")
        assert '"Martin, Pierre"' in text

    def test_semicolon_csv_renders_multiple_columns(self, client, formats_vault):
        """BUG-098 — a French (semicolon) CSV is no longer a single column."""
        (formats_vault / "fr.csv").write_text("Nom;Ville\nAda;Paris\n", encoding="utf-8")
        html = client.get(
            f"/api/file/{VAULT}", params={"path": "fr.csv"}
        ).json()["html"]
        assert 'data-cell="B1"' in html and 'data-cell="B2"' in html
        assert "Paris" in html

    def test_semicolon_is_preserved_when_saving(self, client, formats_vault):
        """BUG-098 — the write-back keeps the file's own delimiter."""
        (formats_vault / "fr.csv").write_text("Nom;Ville\nAda;Paris\n", encoding="utf-8")
        resp = client.put(
            f"/api/file/{VAULT}/csv/save",
            params={"path": "fr.csv"},
            json={"cells": {"B2": "Lyon"}},
        )
        assert resp.status_code == 200
        text = _vault_file(client, "fr.csv").read_text(encoding="utf-8")
        assert "Nom;Ville" in text
        assert "Ada;Lyon" in text
        assert "," not in text  # the source delimiter is not switched to a comma

    def test_quoted_semicolon_survives_a_round_trip(self, client, formats_vault):
        (formats_vault / "quote-fr.csv").write_text(
            'Nom;Note\n"Dupont; Jean";ok\n', encoding="utf-8"
        )
        client.put(
            f"/api/file/{VAULT}/csv/save",
            params={"path": "quote-fr.csv"},
            json={"cells": {"B2": "modifié"}},
        )
        text = _vault_file(client, "quote-fr.csv").read_text(encoding="utf-8")
        assert '"Dupont; Jean"' in text
        assert ";modifié" in text

    def test_bad_reference_is_refused(self, client, formats_vault):
        (formats_vault / "liste.csv").write_text("a\n", encoding="utf-8")
        resp = client.put(
            f"/api/file/{VAULT}/csv/save",
            params={"path": "liste.csv"},
            json={"cells": {"XX": "v"}},  # missing row number
        )
        assert resp.status_code == 400


def _vault_file(client, rel: str) -> Path:
    """Resolve a vault file path for on-disk assertions."""
    root = None
    for route in client.app.routes:
        pass
    # The service layer exposes the vault root; use it directly.
    from backend.services.vaults import get_vault_root

    root = get_vault_root(VAULT)
    return root / rel