"""Style metadata in the .xlsx viewer (#153 A15): bold, colors, number formats,
merged ranges and freeze panes — plus the endpoint wiring."""

from __future__ import annotations

from pathlib import Path

import pytest

openpyxl = pytest.importorskip("openpyxl")

VAULT = "TestVault"


@pytest.fixture
def styled_book(test_vault_dir: str) -> str:
    """A workbook with a bold red-font header, a filled cell, a merge and a freeze."""
    from openpyxl.styles import Font, PatternFill

    path = Path(test_vault_dir) / "styles.xlsx"
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Data"
    ws["A1"] = "En-tête"
    ws["A1"].font = Font(bold=True, italic=True, color="FFCC0000")
    ws["B1"] = "Total"
    ws["B1"].fill = PatternFill("solid", fgColor="FFFFEE99")
    ws["B2"] = 1234.5
    ws["B2"].number_format = "#,##0.00"
    ws["C3"] = "fusionnée"
    ws.merge_cells("C3:D3")
    ws["C3"].value = "fusionnée"
    ws.freeze_panes = "A2"
    wb.save(path)
    return str(path)


@pytest.fixture
def plain_book(test_vault_dir: str) -> str:
    """An unstyled workbook — the write side starts from a blank slate."""
    path = Path(test_vault_dir) / "plain.xlsx"
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Data"
    ws.append(["Nom", "Valeur"])
    ws.append(["a", 1])
    ws.append(["b", 2])
    wb.save(path)
    return str(path)


class TestStyleReading:
    def test_bold_italic_and_font_color(self, styled_book):
        from backend.xlsx_reader import read_sheet_styles

        styles = read_sheet_styles(Path(styled_book), "Data")
        a1 = styles["A1"]
        assert "font-weight:600" in a1["style"]
        assert "font-style:italic" in a1["style"]
        assert a1["style"].startswith("color:#cc0000")

    def test_background_fill_is_read(self, styled_book):
        from backend.xlsx_reader import read_sheet_styles

        styles = read_sheet_styles(Path(styled_book), "Data")
        assert "background:#ffee99" in styles["B1"]["style"]

    def test_number_format_is_signalled(self, styled_book):
        from backend.xlsx_reader import read_sheet_styles

        styles = read_sheet_styles(Path(styled_book), "Data")
        assert "font-family" in styles["B2"]["style"]

    def test_plain_cell_has_no_entry(self, styled_book):
        from backend.xlsx_reader import read_sheet_styles

        styles = read_sheet_styles(Path(styled_book), "Data")
        assert "A2" not in styles  # untouched cell

    def test_merges_and_freeze(self, styled_book):
        from backend.xlsx_reader import read_sheet_freeze, read_sheet_merges

        assert read_sheet_merges(Path(styled_book), "Data") == ["C3:D3"]
        assert read_sheet_freeze(Path(styled_book), "Data") == "A2"

    def test_unknown_sheet_yields_empty(self, styled_book):
        from backend.xlsx_reader import read_sheet_styles

        assert read_sheet_styles(Path(styled_book), "Nope") == {}

    def test_broken_file_yields_empty(self, test_vault_dir):
        from backend.xlsx_reader import read_sheet_styles

        bad = Path(test_vault_dir) / "broken-styles.xlsx"
        bad.write_bytes(b"not a zip")
        assert read_sheet_styles(bad, "Data") == {}


class TestStyleWriting:
    """#156-A8 — the viewer can now WRITE formatting, not just read it.

    One locked, atomic rewrite through ``PUT …/xlsx/style``: styles (bold,
    italic, underline, colours, alignment, number format), merges, column
    widths, row heights and frozen panes.
    """

    def _style(self, client, path, ops, **extra):
        return client.put(
            f"/api/file/{VAULT}/xlsx/style",
            params={"path": path},
            json={"ops": ops, **extra},
        )

    def test_styles_survive_a_reload(self, client, plain_book):
        resp = self._style(client, "plain.xlsx", [
            {
                "op": "cell", "sheet": "Data", "range": "A1:B1",
                "style": {
                    "bold": True, "italic": True, "underline": True,
                    "font_color": "#cc0000", "fill_color": "#ffe08a",
                    "align": "center", "number_format": "0.00",
                },
            },
        ])
        assert resp.status_code == 200
        assert resp.json()["revision"]
        wb = openpyxl.load_workbook(plain_book)
        ws = wb["Data"]
        assert ws["A1"].font.bold is True and ws["A1"].font.italic is True
        assert ws["A1"].font.underline == "single"
        assert ws["A1"].font.color.rgb == "FFCC0000"
        assert ws["A1"].fill.start_color.rgb == "FFFFE08A"
        assert ws["A1"].alignment.horizontal == "center"
        assert ws["B1"].number_format == "0.00"
        assert ws["A1"].value == "Nom", "the value is untouched"
        assert not ws["A2"].font.bold, "a neighbour outside the range is untouched"
        wb.close()

    def test_the_written_style_is_read_back_by_the_viewer(self, client, plain_book):
        self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {"bold": True, "fill_color": "#ffe08a"}},
        ])
        resp = client.get(f"/api/file/{VAULT}", params={"path": "plain.xlsx"})
        assert resp.status_code == 200
        sheet = resp.json()["xlsx_sheets"][0]
        style = sheet["styles"]["A1"]
        assert "font-weight:600" in style and "background:#ffe08a" in style

    def test_merges_sizes_and_freeze(self, client, plain_book):
        resp = self._style(client, "plain.xlsx", [
            {"op": "merge", "sheet": "Data", "range": "A1:B1"},
            {"op": "col_width", "sheet": "Data", "col": "A", "width": 24},
            {"op": "row_height", "sheet": "Data", "row": 2, "height": 30},
            {"op": "freeze", "sheet": "Data", "cell": "A2"},
        ])
        assert resp.status_code == 200
        wb = openpyxl.load_workbook(plain_book)
        ws = wb["Data"]
        assert [str(r) for r in ws.merged_cells.ranges] == ["A1:B1"]
        assert ws.column_dimensions["A"].width == 24
        assert ws.row_dimensions[2].height == 30
        assert str(ws.freeze_panes) == "A2"
        wb.close()

        resp = self._style(client, "plain.xlsx", [
            {"op": "unmerge", "sheet": "Data", "range": "A1:B1"},
            {"op": "freeze", "sheet": "Data", "cell": ""},
        ])
        assert resp.status_code == 200
        wb = openpyxl.load_workbook(plain_book)
        ws = wb["Data"]
        assert not list(ws.merged_cells.ranges)
        assert ws.freeze_panes is None
        wb.close()

    def test_a_style_can_be_cleared(self, client, plain_book):
        self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {"bold": True, "font_color": "#cc0000", "fill_color": "#ffe08a"}},
        ])
        resp = self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {"bold": False, "font_color": "", "fill_color": "", "number_format": "General"}},
        ])
        assert resp.status_code == 200
        wb = openpyxl.load_workbook(plain_book)
        ws = wb["Data"]
        assert ws["A1"].font.bold in (None, False)
        assert ws["A1"].font.color is None
        assert ws["A1"].fill.fill_type is None
        assert ws["A1"].number_format == "General"
        wb.close()

    def test_invalid_inputs_are_refused(self, client, plain_book):
        bad_style = self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {"blink": True}},
        ])
        assert bad_style.status_code == 400
        assert self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {}},
        ]).status_code == 400
        assert self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {"fill_color": "rouge"}},
        ]).status_code == 400
        assert self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "pas-une-plage", "style": {"bold": True}},
        ]).status_code == 400
        assert self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Nope", "range": "A1", "style": {"bold": True}},
        ]).status_code == 400
        assert self._style(client, "plain.xlsx", [{"op": "spin", "sheet": "Data"}]).status_code == 400
        assert self._style(client, "plain.xlsx", []).status_code == 400
        assert self._style(client, "plain.xlsx", [
            {"op": "col_width", "sheet": "Data", "col": "A", "width": 900},
        ]).status_code == 400
        assert self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {"align": "diagonal"}},
        ]).status_code == 400

    def test_a_csv_is_refused(self, client, test_vault_dir):
        (Path(test_vault_dir) / "style.csv").write_text("a,b\n1,2\n", encoding="utf-8")
        resp = self._style(client, "style.csv", [
            {"op": "cell", "sheet": "x", "range": "A1", "style": {"bold": True}},
        ])
        assert resp.status_code == 400

    def test_a_stale_revision_is_refused_and_the_fresh_one_accepted(self, client, plain_book):
        first = self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {"bold": True}},
        ])
        assert first.status_code == 200
        stale = self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A2", "style": {"bold": True}},
        ], if_match="deadbeef-1")
        assert stale.status_code == 409
        assert stale.json()["details"]["reason"] == "stale_revision"
        fresh = self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A2", "style": {"bold": True}},
        ], if_match=first.json()["revision"])
        assert fresh.status_code == 200
        assert self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A3", "style": {"bold": True}},
        ], if_match=42).status_code == 400

    def test_a_backup_is_written_before_the_rewrite(self, client, plain_book):
        from backend.services.backups import get_backup_dir

        assert self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": {"bold": True}},
        ]).status_code == 200
        backups = list(get_backup_dir(VAULT, "plain.xlsx").glob("plain.xlsx.*.bak"))
        assert backups, "the rewrite is backed up first"


class TestExtendedStyles:
    """Nouvelles clés de style (barre façon Sheets) : taille, barré,
    alignements verticaux, retour ligne, rotation, bordures — et commentaires."""

    def _style(self, client, path, ops, **extra):
        return client.put(
            f"/api/file/{VAULT}/xlsx/style",
            params={"path": path},
            json={"ops": ops, **extra},
        )

    def test_font_size_strike_valign_wrap_rotation(self, client, plain_book):
        resp = self._style(client, "plain.xlsx", [
            {
                "op": "cell", "sheet": "Data", "range": "A1",
                "style": {
                    "font_size": 14, "strike": True, "valign": "middle",
                    "wrap": True, "rotation": -45,
                },
            },
        ])
        assert resp.status_code == 200
        wb = openpyxl.load_workbook(plain_book)
        cell = wb["Data"]["A1"]
        assert cell.font.size == 14
        assert cell.font.strike is True
        assert cell.alignment.vertical == "center"
        assert cell.alignment.wrap_text is True
        assert cell.alignment.textRotation == 135
        wb.close()

        resp = client.get(f"/api/file/{VAULT}", params={"path": "plain.xlsx"})
        sheet = resp.json()["xlsx_sheets"][0]
        assert "font-size:14pt" in sheet["styles"]["A1"]
        assert "line-through" in sheet["styles"]["A1"]
        assert "vertical-align:middle" in sheet["styles"]["A1"]
        assert "white-space:normal" in sheet["styles"]["A1"]
        # La diagonale (-45° → 135 OOXML) est relue en `rotate()` CSS.
        assert "transform:rotate(45deg)" in sheet["styles"]["A1"]

    def test_borders_all_outer_none(self, client, plain_book):
        resp = self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1:B2",
             "style": {"border": "all", "border_color": "#112233"}},
        ])
        assert resp.status_code == 200
        wb = openpyxl.load_workbook(plain_book)
        side = wb["Data"]["A1"].border.left
        assert side.style == "thin" and side.color.rgb == "FF112233"
        wb.close()

        resp = self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1:B2", "style": {"border": "none"}},
        ])
        assert resp.status_code == 200
        wb = openpyxl.load_workbook(plain_book)
        cleared = wb["Data"]["A1"].border.left
        assert cleared is None or cleared.style is None
        wb.close()

    def test_comment_set_clear_and_readback(self, client, plain_book):
        resp = self._style(client, "plain.xlsx", [
            {"op": "comment_set", "sheet": "Data", "ref": "B2", "text": "À vérifier"},
        ])
        assert resp.status_code == 200
        wb = openpyxl.load_workbook(plain_book)
        assert wb["Data"]["B2"].comment.text == "À vérifier"
        wb.close()

        resp = client.get(f"/api/file/{VAULT}", params={"path": "plain.xlsx"})
        assert resp.json()["xlsx_sheets"][0]["comments"]["B2"] == "À vérifier"

        resp = self._style(client, "plain.xlsx", [
            {"op": "comment_clear", "sheet": "Data", "ref": "B2"},
        ])
        assert resp.status_code == 200
        wb = openpyxl.load_workbook(plain_book)
        assert wb["Data"]["B2"].comment is None
        wb.close()

    def test_formats_map_lists_number_formats(self, client, plain_book):
        self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "B2", "style": {"number_format": "0.00"}},
        ])
        resp = client.get(f"/api/file/{VAULT}", params={"path": "plain.xlsx"})
        assert resp.json()["xlsx_sheets"][0]["formats"]["B2"] == "0.00"

    def test_extended_invalid_inputs_are_refused(self, client, plain_book):
        style = lambda s: self._style(client, "plain.xlsx", [
            {"op": "cell", "sheet": "Data", "range": "A1", "style": s},
        ]).status_code
        assert style({"font_size": 200}) == 400
        assert style({"font_size": "grand"}) == 400
        assert style({"valign": "diagonal"}) == 400
        # 180° (texte vertical retourné, relu tel quel) est accepté — seules
        # les valeurs vraiment hors bornes sont refusées.
        assert style({"rotation": 180}) == 200
        assert style({"rotation": 181}) == 400
        assert style({"rotation": -91}) == 400
        assert style({"border": "dotted-circle"}) == 400
        assert style({"border_style": "wavy"}) == 400
        assert self._style(client, "plain.xlsx", [
            {"op": "comment_set", "sheet": "Data", "ref": "B2", "text": ""},
        ]).status_code == 400
        assert self._style(client, "plain.xlsx", [
            {"op": "comment_set", "sheet": "Data", "ref": "B2:C3", "text": "x"},
        ]).status_code == 400


class TestEndpointWiring:
    def test_read_response_carries_styles_merges_freeze(self, client, styled_book):
        resp = client.get(f"/api/file/{VAULT}", params={"path": "styles.xlsx"})
        assert resp.status_code == 200
        sheet = resp.json()["xlsx_sheets"][0]
        assert sheet["styles"]["A1"].startswith("color:#cc0000")
        assert sheet["aligns"] == {} or isinstance(sheet["aligns"], dict)
        assert sheet["merges"] == ["C3:D3"]
        assert sheet["freeze"] == "A2"

    def test_rendered_html_carries_the_inline_style(self, client, styled_book):
        resp = client.get(f"/api/file/{VAULT}", params={"path": "styles.xlsx"})
        html = resp.json()["xlsx_sheets"][0]["html"]
        assert 'data-cell="A1" style="' in html
        assert "font-weight:600" in html