"""Report templates: one saved report reading several datasources and forms,
with parameters, joins by a shared key, totals, KPIs, per-section access
errors, exports and role limits."""

import io

from openpyxl import load_workbook

from tests.conftest import AsyncSessionLocal
from app.models.master import Master, MasterRecord
from tests.test_enterprise_access import _person


async def _sources(tenant_id):
    async with AsyncSessionLocal() as db:
        async with db.begin():
            staff = Master(tenant_id=tenant_id, name="Staff", field_schema=[{"name": "code", "type": "text"}, {"name": "name", "type": "text", "alias": "Employee"}, {"name": "office", "type": "text"}])
            db.add(staff)
            await db.flush()
            for code, name, office in [("E1", "Asha", "Kochi"), ("E2", "Ben", "Pune"), ("E3", "Chen", "Kochi")]:
                db.add(MasterRecord(tenant_id=tenant_id, master_id=staff.id, field_values={"code": code, "name": name, "office": office}))
            return staff


async def _claims_form(client, headers):
    form = (await client.post("/forms", headers=headers, json={"name": "Claims", "field_definitions": [
        {"name": "emp", "type": "text", "label": "Employee code"},
        {"name": "amount", "type": "number", "label": "Amount"},
        {"name": "day", "type": "date", "label": "Date"},
    ]})).json()
    await client.post(f"/forms/{form['form_id']}/publish", headers=headers)
    for emp, amount, day in [("E1", 100, "2026-09-02"), ("E2", 50, "2026-09-10"), ("E1", 25, "2026-10-01"), ("E9", 5, "2026-09-15")]:
        r = await client.post("/form-submissions", headers=headers, json={"form_id": form["form_id"], "form_version": 1, "field_values": {"emp": emp, "amount": amount, "day": day}})
        assert r.status_code == 201, r.text
    return form


def _template(staff_id, form_id):
    claims = {"source_type": "form", "source_id": form_id}
    in_range = {"field": "day", "operator": "between", "value": ["{{from}}", "{{to}}"]}
    return {
        "name": "Monthly claims",
        "parameters": [{"name": "from", "type": "date"}, {"name": "to", "type": "date"}, {"name": "office"}],
        "settings": {"subtitle": "From {{from}} to {{to}}"},
        "sections": [
            {"id": "intro", "kind": "text", "text": "Claims for {{office}}"},
            {"id": "count", "kind": "kpi", "title": "Claims", **claims, "aggregate_fn": "count", "filters": [in_range]},
            {"id": "sum", "kind": "kpi", "title": "Total", **claims, "aggregate_fn": "sum", "aggregate_field": "amount", "filters": [in_range]},
            {
                "id": "detail", "kind": "table", "title": "Claims by employee", **claims, "columns": ["emp", "amount"], "filters": [in_range],
                "sort_field": "amount", "sort_dir": "desc", "show_totals": True,
                "joins": [{"alias": "staff", "source_type": "master", "source_id": staff_id, "local_field": "emp", "foreign_field": "code", "columns": ["name", "office"]}],
            },
            {"id": "people", "kind": "table", "title": "Staff", "source_type": "master", "source_id": staff_id, "columns": ["code", "name"],
             "filters": [{"field": "office", "operator": "equals", "value": "{{office}}"}]},
            {"id": "byday", "kind": "chart", "chart_type": "bar", **claims, "group_by": "emp", "aggregate_fn": "sum", "aggregate_field": "amount"},
        ],
    }


async def test_multi_source_report(client, auth_headers, test_tenant):
    staff = await _sources(test_tenant["tenant_id"])
    form = await _claims_form(client, auth_headers)
    created = await client.post("/report-templates", headers=auth_headers, json=_template(str(staff.id), form["form_id"]))
    assert created.status_code == 201, created.text
    rid = created.json()["id"]

    run = await client.post(f"/report-templates/{rid}/run", headers=auth_headers, json={"params": {"from": "2026-09-01", "to": "2026-09-30", "office": "Kochi"}})
    assert run.status_code == 200, run.text
    body = run.json()
    s = {x["id"]: x for x in body["sections"]}
    assert body["subtitle"] == "From 2026-09-01 to 2026-09-30"
    assert s["intro"]["text"] == "Claims for Kochi"
    assert s["count"]["value"] == 3 and s["sum"]["value"] == 155
    detail = s["detail"]
    assert detail["columns"] == ["emp", "amount", "staff.name", "staff.office"]
    assert detail["rows"][0] == {"emp": "E1", "amount": 100, "staff.name": "Asha", "staff.office": "Kochi"}
    assert {"emp": "E9", "amount": 5, "staff.name": None, "staff.office": None} in detail["rows"]  # left join
    assert detail["totals"] == {"amount": 155}
    assert s["people"]["column_labels"]["name"] == "Employee"  # the datasource's alias
    assert sorted(r["code"] for r in s["people"]["rows"]) == ["E1", "E3"]
    assert {r["emp"]: r["sum_amount"] for r in s["byday"]["rows"]} == {"E1": 125, "E2": 50, "E9": 5}

    # Blank optional parameters widen the report instead of matching nothing.
    wide = (await client.post(f"/report-templates/{rid}/run", headers=auth_headers, json={"params": {"from": "2026-09-05"}})).json()
    w = {x["id"]: x for x in wide["sections"]}
    assert w["count"]["value"] == 3 and len(w["people"]["rows"]) == 3

    xlsx = await client.post(f"/report-templates/{rid}/export.xlsx", headers=auth_headers, json={"params": {"office": "Kochi"}})
    assert xlsx.status_code == 200
    wb = load_workbook(io.BytesIO(xlsx.content))
    assert wb.sheetnames == ["Summary", "Claims by employee", "Staff", "Section 6"]
    assert [c.value for c in wb["Claims by employee"][1]] == ["emp", "amount", "staff.name", "staff.office"]
    pdf = await client.post(f"/report-templates/{rid}/export.pdf", headers=auth_headers, json={"params": {}})
    assert pdf.status_code == 200 and pdf.content.startswith(b"%PDF")
    csv = await client.post(f"/report-templates/{rid}/export.csv", headers=auth_headers, json={"params": {"office": "Kochi"}})
    assert csv.status_code == 200 and csv.headers["content-type"].startswith("text/csv")
    lines = csv.content.decode("utf-8-sig").splitlines()
    assert "Claims by employee" in lines and "emp,amount,staff.name,staff.office" in lines

    dup = await client.post(f"/report-templates/{rid}/duplicate", headers=auth_headers)
    assert dup.status_code == 201 and dup.json()["name"] == "Monthly claims (copy)"


async def test_preview_validation_access_and_roles(client, auth_headers, test_tenant):
    staff = await _sources(test_tenant["tenant_id"])
    form = await _claims_form(client, auth_headers)
    tpl = _template(str(staff.id), form["form_id"])
    preview = await client.post("/report-templates/preview", headers=auth_headers, json={**tpl, "params": {}})
    assert preview.status_code == 200 and len(preview.json()["sections"]) == 6

    bad = await client.post("/report-templates", headers=auth_headers, json={"name": "Bad", "sections": [{"id": "x", "kind": "summary", "source_type": "form", "source_id": form["form_id"]}]})
    assert bad.status_code == 422
    required = await client.post("/report-templates/preview", headers=auth_headers, json={**tpl, "parameters": [{"name": "from", "required": True}], "params": {}})
    assert required.status_code == 422

    # A section whose source the viewer can't read shows an error; the rest still run.
    viewer = await _person(test_tenant["tenant_id"], "Viewer", matrix={"reports": ["view"]})
    rid = (await client.post("/report-templates", headers=auth_headers, json=tpl)).json()["id"]
    run = (await client.post(f"/report-templates/{rid}/run", headers=viewer["headers"], json={})).json()
    s = {x["id"]: x for x in run["sections"]}
    assert s["intro"]["error"] is None and "analytics" in s["detail"]["error"]

    insider = await _person(test_tenant["tenant_id"], "Insider", matrix={"reports": ["view"], "analytics": ["view"], "forms": ["view"], "masters": ["view"]})
    await client.patch(f"/report-templates/{rid}", headers=auth_headers, json={"role_ids": [insider["role_id"]]})
    assert (await client.post(f"/report-templates/{rid}/run", headers=viewer["headers"], json={})).status_code == 403
    assert (await client.post(f"/report-templates/{rid}/run", headers=insider["headers"], json={})).status_code == 200
    assert (await client.post("/report-templates", headers=viewer["headers"], json=tpl)).status_code == 403


async def test_join_on_record_id_with_several_matches(client, auth_headers):
    """The RTI case: forwards store the application's record id, and one
    application can be forwarded several times."""
    apps = (await client.post("/forms", headers=auth_headers, json={"name": "Applications", "field_definitions": [{"name": "number", "type": "text", "label": "No."}]})).json()
    fwd = (await client.post("/forms", headers=auth_headers, json={"name": "Forwards", "field_definitions": [
        {"name": "application_id", "type": "hidden", "label": "App"}, {"name": "seat", "type": "text", "label": "Seat"}, {"name": "comments", "type": "rich_text", "label": "Comments"},
    ]})).json()
    for f in (apps, fwd):
        await client.post(f"/forms/{f['form_id']}/publish", headers=auth_headers)

    async def submit(form, values):
        r = await client.post("/form-submissions", headers=auth_headers, json={"form_id": form["form_id"], "form_version": 1, "field_values": values})
        assert r.status_code == 201, r.text
        return r.json()["id"]

    a1 = await submit(apps, {"number": "RTI/1"})
    await submit(apps, {"number": "RTI/2"})
    for seat, note in [("Clerk", "<p>Received</p>"), ("Officer", "<p>Check &amp; reply</p>")]:
        await submit(fwd, {"application_id": a1, "seat": seat, "comments": note})

    def tpl(match):
        return {"name": "Forwards", "sections": [{
            "id": "t", "kind": "table", "source_type": "form", "source_id": apps["form_id"], "columns": ["number"], "sort_field": "number",
            "joins": [{"alias": "fwd", "source_type": "form", "source_id": fwd["form_id"], "local_field": "id", "foreign_field": "application_id", "columns": ["seat", "comments"], "match": match}],
        }]}

    async def rows(match):
        r = await client.post("/report-templates/preview", headers=auth_headers, json={**tpl(match), "params": {}})
        assert r.status_code == 200, r.text
        s = r.json()["sections"][0]
        assert s["error"] is None, s["error"]
        return [(x["number"], x["fwd.seat"], x["fwd.comments"]) for x in s["rows"]]

    assert await rows("first") == [("RTI/1", "Clerk", "Received"), ("RTI/2", None, None)]
    assert await rows("latest") == [("RTI/1", "Officer", "Check & reply"), ("RTI/2", None, None)]
    assert await rows("all") == [("RTI/1", "Clerk", "Received"), ("RTI/1", "Officer", "Check & reply"), ("RTI/2", None, None)]