"""INDL-42 — Add/Edit Plot & CSV Import backend coverage.

Covers the two new endpoints (PATCH /plots/{id}/revert, POST /plots/import) plus
the import preview / suggest-mapping helpers, and the PRD Security Acceptance
Criteria (SEC-01..SEC-07, SEC-10, SEC-11) and functional ACs
(AC-11/AC-13/AC-15/AC-16/AC-18/AC-19).
"""
import io
import json
from datetime import datetime, timezone
from uuid import uuid4

import pytest
from httpx import AsyncClient
from sqlalchemy.ext.asyncio import AsyncSession

from src.apps.auth.models.user import User
from src.apps.plots.models.plot import Plot
from src.apps.plots.services.plot_import import suggest_mapping, escape_formula
from src.apps.sections.models.section import Section
from src.apps.tenants.models.account import Account
from src.core.security import build_token_payload, create_access_token

pytestmark = pytest.mark.asyncio


# ── helpers ──────────────────────────────────────────────────────────────────

async def _make_account(db: AsyncSession) -> Account:
    uid = uuid4().hex[:8]
    acc = Account(
        organization_name=f"Import Cemetery {uid}",
        subdomain=f"imp-{uid}",
        contact_email=f"imp-{uid}@test.ca",
        plan="starter",
        status="active",
    )
    db.add(acc)
    await db.flush()
    return acc


async def _make_user(db, account, role="administrator") -> str:
    user = User(
        tenant_id=account.id,
        email=f"{role}-{uuid4().hex[:6]}@test.ca",
        password_hash="x",
        first_name="T",
        last_name="U",
        role=role,
        status="active",
    )
    db.add(user)
    await db.flush()
    return create_access_token(build_token_payload(user, account))


def _headers(token, account) -> dict:
    return {"Authorization": f"Bearer {token}", "X-Tenant-ID": str(account.id)}


async def _make_section(db, account, code="A", name="Section A") -> Section:
    sec = Section(
        tenant_id=account.id, code=code, name=name,
        plot_number_prefix=code, next_plot_seq=1,
    )
    db.add(sec)
    await db.flush()
    return sec


async def _make_plot(db, account, ref, section=None, status="vacant") -> Plot:
    plot = Plot(
        tenant_id=account.id, plot_ref=ref,
        section_id=section.id if section else None, status=status,
    )
    db.add(plot)
    await db.flush()
    return plot


def _csv_bytes(header: list[str], rows: list[list[str]]) -> bytes:
    lines = [",".join(header)] + [",".join(str(c) for c in r) for r in rows]
    return ("\n".join(lines)).encode("utf-8")


def _files(content: bytes, name="ledger.csv", ctype="text/csv"):
    return {"file": (name, io.BytesIO(content), ctype)}


# ── Revert (AC-18 / SEC-02 / SEC-11) ─────────────────────────────────────────

async def test_revert_clears_reservation(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    sec = await _make_section(db_session, acc)
    plot = await _make_plot(db_session, acc, "A-1", sec, status="reserved")
    plot.reserved_by = "Walsh family"
    plot.reserved_at = datetime.now(timezone.utc)
    await db_session.flush()

    r = await client.patch(
        f"/api/v1/plots/{plot.id}/revert", headers=_headers(token, acc)
    )
    assert r.status_code == 200
    data = r.json()["data"]
    assert data["status"] == "vacant"
    assert data["reserved_by"] is None
    assert data["reserved_at"] is None


async def test_revert_cross_tenant_is_404(client: AsyncClient, db_session):
    acc_a = await _make_account(db_session)
    acc_b = await _make_account(db_session)
    token_a = await _make_user(db_session, acc_a)
    plot_b = await _make_plot(db_session, acc_b, "B-1", status="reserved")

    r = await client.patch(
        f"/api/v1/plots/{plot_b.id}/revert", headers=_headers(token_a, acc_a)
    )
    assert r.status_code == 404
    # untouched
    await db_session.refresh(plot_b)
    assert plot_b.status == "reserved"


async def test_revert_staff_forbidden(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc, role="staff")
    plot = await _make_plot(db_session, acc, "A-1", status="reserved")
    r = await client.patch(
        f"/api/v1/plots/{plot.id}/revert", headers=_headers(token, acc)
    )
    assert r.status_code == 403


# ── Import happy path + validation (AC-13/AC-15/AC-16) ────────────────────────

async def test_import_creates_valid_rows(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    await _make_section(db_session, acc, code="A", name="Garden")

    content = _csv_bytes(
        ["Plot Ref", "Sect", "State", "Price"],
        [["A-100", "A", "Reserved", "4200"], ["A-101", "A", "Available", "3900"]],
    )
    mapping = {"Plot Ref": "plot_ref", "Sect": "section",
               "State": "status", "Price": "price"}
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content), data={"mapping": json.dumps(mapping)},
        headers=_headers(token, acc),
    )
    assert r.status_code == 200
    data = r.json()["data"]
    assert data["created"] == 2
    assert data["skipped"] == 0
    assert data["failed"] == []


async def test_import_duplicate_and_unknown_section(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    await _make_section(db_session, acc, code="A", name="Garden")
    await _make_plot(db_session, acc, "A-1", status="vacant")  # existing DB ref

    content = _csv_bytes(
        ["Plot Ref", "Sect"],
        [["A-1", "A"],       # duplicate against DB → error
         ["A-2", "A"],       # ok
         ["A-2", "A"],       # duplicate within file → error
         ["A-3", "Z"]],      # unknown section → error
    )
    mapping = {"Plot Ref": "plot_ref", "Sect": "section"}
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content), data={"mapping": json.dumps(mapping)},
        headers=_headers(token, acc),
    )
    data = r.json()["data"]
    assert data["created"] == 1          # only A-2 (first)
    assert data["skipped"] == 3
    reasons = " ".join(f["reason"] for f in data["failed"]).lower()
    assert "already exists" in reasons
    assert "earlier in this file" in reasons
    assert "unknown section" in reasons


async def test_import_unknown_status_defaults_with_warning(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    await _make_section(db_session, acc, code="A")

    content = _csv_bytes(
        ["Plot Ref", "Sect", "State"], [["A-9", "A", "Purgatory"]]
    )
    mapping = {"Plot Ref": "plot_ref", "Sect": "section", "State": "status"}
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content), data={"mapping": json.dumps(mapping)},
        headers=_headers(token, acc),
    )
    data = r.json()["data"]
    assert data["created"] == 1
    assert any("Unrecognized status" in m
               for w in data["warnings"] for m in w["messages"])


async def test_import_gps_out_of_range_is_error(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    await _make_section(db_session, acc, code="A")
    content = _csv_bytes(
        ["Plot Ref", "Sect", "Lat"], [["A-1", "A", "999"]]
    )
    mapping = {"Plot Ref": "plot_ref", "Sect": "section", "Lat": "latitude"}
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content), data={"mapping": json.dumps(mapping)},
        headers=_headers(token, acc),
    )
    data = r.json()["data"]
    assert data["created"] == 0
    assert "latitude" in data["failed"][0]["reason"].lower()


# ── Security ACs ─────────────────────────────────────────────────────────────

async def test_import_staff_forbidden(client: AsyncClient, db_session):
    """SEC-01: staff role cannot import."""
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc, role="staff")
    content = _csv_bytes(["Plot Ref", "Sect"], [["A-1", "A"]])
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content), data={"mapping": json.dumps({"Plot Ref": "plot_ref"})},
        headers=_headers(token, acc),
    )
    assert r.status_code == 403


async def test_import_required_mapping_enforced(client: AsyncClient, db_session):
    """AC-14 server-side: Plot ID + Section must be mapped."""
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    content = _csv_bytes(["Plot Ref", "Sect"], [["A-1", "A"]])
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content),
        data={"mapping": json.dumps({"Plot Ref": "plot_ref"})},  # section missing
        headers=_headers(token, acc),
    )
    assert r.status_code == 422
    assert "section" in r.json()["message"].lower()


async def test_import_rejects_pdf_content_type(client: AsyncClient, db_session):
    """SEC-06: application/pdf → 415."""
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    r = await client.post(
        "/api/v1/plots/import",
        files={"file": ("x.pdf", io.BytesIO(b"%PDF-1.4"), "application/pdf")},
        data={"mapping": json.dumps({"a": "plot_ref"})},
        headers=_headers(token, acc),
    )
    assert r.status_code == 415


async def test_import_rejects_bad_extension(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    r = await client.post(
        "/api/v1/plots/import",
        files={"file": ("x.txt.pdf", io.BytesIO(b"junk"), "text/csv")},
        data={"mapping": json.dumps({"a": "plot_ref"})},
        headers=_headers(token, acc),
    )
    assert r.status_code == 415


async def test_import_rate_limited(client: AsyncClient, db_session):
    """SEC-07: 4th import within the window → 429 (fresh tenant = fresh counter)."""
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    await _make_section(db_session, acc, code="A")
    mapping = json.dumps({"Plot Ref": "plot_ref", "Sect": "section"})

    codes = []
    for i in range(4):
        # unique refs so only rate-limiting differs
        c = _csv_bytes(["Plot Ref", "Sect"], [[f"A-{i}", "A"]])
        r = await client.post(
            "/api/v1/plots/import",
            files=_files(c), data={"mapping": mapping},
            headers=_headers(token, acc),
        )
        codes.append(r.status_code)
    assert codes[:3] == [200, 200, 200]
    assert codes[3] == 429


async def test_import_row_tenant_id_is_ignored(client: AsyncClient, db_session):
    """SEC-03 / API3: a tenant_id column can never mass-assign — only whitelisted
    targets are writable, so a bogus tenant_id source column is simply dropped."""
    acc = await _make_account(db_session)
    other = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    await _make_section(db_session, acc, code="A")
    content = _csv_bytes(
        ["Plot Ref", "Sect", "tenant_id"], [["A-1", "A", str(other.id)]]
    )
    # even if a client tries to map a column onto tenant_id, it's not importable
    mapping = {"Plot Ref": "plot_ref", "Sect": "section", "tenant_id": "tenant_id"}
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content), data={"mapping": json.dumps(mapping)},
        headers=_headers(token, acc),
    )
    assert r.status_code == 200
    assert r.json()["data"]["created"] == 1
    plot = (await db_session.execute(
        __import__("sqlalchemy").select(Plot).where(Plot.plot_ref == "A-1")
    )).scalar_one()
    assert plot.tenant_id == acc.id  # stamped from context, never from file


async def test_error_report_escapes_formula(client: AsyncClient, db_session):
    """SEC-05: a formula payload in a failing row is quote-prefixed in the report."""
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    await _make_section(db_session, acc, code="A")
    # unknown section 'Z' forces the row to fail so it appears in the report
    content = _csv_bytes(
        ["Plot Ref", "Sect"], [['=HYPERLINK("http://evil.com","x")', "Z"]]
    )
    mapping = {"Plot Ref": "plot_ref", "Sect": "section"}
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content), data={"mapping": json.dumps(mapping)},
        headers=_headers(token, acc),
    )
    report = r.json()["data"]["error_report_csv"]
    assert "'=HYPERLINK" in report          # leading single-quote neutralises it
    assert "\n=HYPERLINK" not in report


# ── Preview + suggest-mapping (AC-13/AC-14) ──────────────────────────────────

async def test_import_preview_returns_counts_and_mapping(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    content = _csv_bytes(
        ["Plot Ref", "Sect", "Grave Type", "Holder"],
        [["A-1", "A", "Single", "Walsh"]],
    )
    r = await client.post(
        "/api/v1/plots/import/preview",
        files=_files(content), headers=_headers(token, acc),
    )
    assert r.status_code == 200
    data = r.json()["data"]
    assert data["row_count"] == 1
    assert data["column_count"] == 4
    m = data["suggested_mapping"]
    assert m["Plot Ref"] == "plot_ref"
    assert m["Sect"] == "section"
    assert m["Grave Type"] == "plot_type"
    assert m["Holder"] == "reserved_by"


def test_suggest_mapping_unit():
    m = suggest_mapping(["Plot ID", "Section", "State", "Owner", "Lat", "Lng", "Random"])
    assert m["Plot ID"] == "plot_ref"
    assert m["Section"] == "section"
    assert m["State"] == "status"
    assert m["Owner"] == "reserved_by"
    assert m["Lat"] == "latitude"
    assert m["Lng"] == "longitude"
    assert m["Random"] is None


def test_escape_formula_unit():
    assert escape_formula("=1+1") == "'=1+1"
    assert escape_formula("+A1") == "'+A1"
    assert escape_formula("@cmd") == "'@cmd"
    assert escape_formula("normal") == "normal"
    assert escape_formula(None) == ""


# ── Create-plot mass-assignment guard (SEC-03 / API3) ────────────────────────

async def test_create_plot_forbids_extra_fields(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    other = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    r = await client.post(
        "/api/v1/plots",
        json={"plot_ref": "X-1", "tenant_id": str(other.id)},
        headers=_headers(token, acc),
    )
    assert r.status_code == 422  # extra="forbid" rejects tenant_id


async def test_update_plot_with_status_and_public_description(client: AsyncClient, db_session):
    """AC-12/AC-08: the Edit form PATCHes plot_ref/status/public_description together."""
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    sec = await _make_section(db_session, acc)
    plot = await _make_plot(db_session, acc, "A-1", sec, status="vacant")

    r = await client.patch(
        f"/api/v1/plots/{plot.id}",
        json={"status": "reserved", "public_description": "Quiet corner near the oaks."},
        headers=_headers(token, acc),
    )
    assert r.status_code == 200
    data = r.json()["data"]
    assert data["status"] == "reserved"
    assert data["public_description"] == "Quiet corner near the oaks."


async def test_update_plot_invalid_status_rejected(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    plot = await _make_plot(db_session, acc, "A-1", status="vacant")
    r = await client.patch(
        f"/api/v1/plots/{plot.id}",
        json={"status": "purgatory"},
        headers=_headers(token, acc),
    )
    assert r.status_code == 422


async def test_create_plot_gps_out_of_range(client: AsyncClient, db_session):
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    r = await client.post(
        "/api/v1/plots",
        json={"plot_ref": "X-1", "latitude": 200},
        headers=_headers(token, acc),
    )
    assert r.status_code == 422


# ── XLSX round-trip (AC-13 file type) ────────────────────────────────────────

async def test_import_xlsx(client: AsyncClient, db_session):
    import openpyxl
    acc = await _make_account(db_session)
    token = await _make_user(db_session, acc)
    await _make_section(db_session, acc, code="A")

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.append(["Plot Ref", "Sect"])
    ws.append(["A-1", "A"])
    ws.append(["A-2", "A"])
    buf = io.BytesIO()
    wb.save(buf)
    content = buf.getvalue()

    mapping = {"Plot Ref": "plot_ref", "Sect": "section"}
    r = await client.post(
        "/api/v1/plots/import",
        files=_files(content, name="ledger.xlsx",
                     ctype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"),
        data={"mapping": json.dumps(mapping)},
        headers=_headers(token, acc),
    )
    assert r.status_code == 200
    assert r.json()["data"]["created"] == 2