"""Calculated columns ({name, expression}) on a query or a saved dataset:
worked out on each row — linked records' fields included — and usable for
filtering, sorting and grouping like stored columns."""

import uuid

from app.crud import dataset_query
from app.models.master import Master, MasterRecord
from tests.conftest import AsyncSessionLocal


async def _orders(tenant_id):
    async with AsyncSessionLocal() as db:
        async with db.begin():
            customers = Master(tenant_id=tenant_id, name=f"Customers {uuid.uuid4().hex[:6]}", field_schema=[{"name": "name", "type": "text"}, {"name": "discount", "type": "number"}])
            db.add(customers)
            await db.flush()
            asha = MasterRecord(tenant_id=tenant_id, master_id=customers.id, field_values={"name": "Asha", "discount": 10})
            db.add(asha)
            await db.flush()
            orders = Master(
                tenant_id=tenant_id,
                name=f"Orders {uuid.uuid4().hex[:6]}",
                field_schema=[
                    {"name": "no", "type": "text"},
                    {"name": "qty", "type": "number"},
                    {"name": "price", "type": "number"},
                    {"name": "customer", "type": "reference", "reference_master_id": str(customers.id), "reference_display_field": "name"},
                ],
            )
            db.add(orders)
            await db.flush()
            for no, qty, price, who in [("O1", 2, 50, asha.id), ("O2", 1, 30, None), ("O3", 5, 10, asha.id)]:
                values = {"no": no, "qty": qty, "price": price}
                if who:
                    values["customer"] = str(who)
                db.add(MasterRecord(tenant_id=tenant_id, master_id=orders.id, field_values=values))
            return orders.id


COMPUTED = [
    {"name": "total", "expression": "{qty} * {price}"},
    # A linked record's field, and an earlier calculated column.
    {"name": "net", "expression": "{total} - {total} * COALESCE({customer.discount}, 0) / 100"},
    {"name": "size", "expression": "total >= 50 ? 'big' : 'small'"},
]


async def test_calculated_columns(test_tenant):
    tenant_id = test_tenant["tenant_id"]
    orders_id = await _orders(tenant_id)

    async def rows(**kw):
        async with AsyncSessionLocal() as db:
            return await dataset_query.resolve_rows(
                db, tenant_id, "master", orders_id, kw.pop("filters", []), kw.pop("sort_field", None), kw.pop("sort_dir", "asc"), kw.pop("columns", []), computed=COMPUTED, **kw
            )

    got, total, cols = await rows(columns=["no", "total", "net", "size"], sort_field="no")
    assert cols == ["no", "total", "net", "size"] and total == 3
    assert [(r["no"], r["total"], r["net"], r["size"]) for r in got] == [("O1", 100, 90, "big"), ("O2", 30, 30, "small"), ("O3", 50, 45, "big")]

    big, total, _ = await rows(columns=["no"], filters=[{"field": "total", "operator": "gte", "value": 50}], sort_field="net", sort_dir="desc")
    assert [r["no"] for r in big] == ["O1", "O3"] and total == 2

    grouped, _, cols = await rows(group_by="size", aggregate_fn="sum", aggregate_field="net")
    assert {r["size"]: r[cols[1]] for r in grouped} == {"big": 135, "small": 30}

    async with AsyncSessionLocal() as db:
        series, _ = await dataset_query.resolve_grouped_series(db, tenant_id, "master", orders_id, [], "size", "none", [{"aggregate": "sum", "field": "total", "label": "t"}], computed=COMPUTED)
    assert {r["x"]: r["t"] for r in series} == {"big": 150, "small": 30}


async def test_a_saved_dataset_keeps_its_calculated_columns(client, auth_headers, test_tenant):
    orders_id = await _orders(test_tenant["tenant_id"])
    resp = await client.post(
        "/datasets",
        headers=auth_headers,
        json={"name": "Order totals", "source_type": "master", "source_id": str(orders_id), "columns": ["no", "total"], "computed": COMPUTED[:1], "sort_field": "total", "sort_dir": "desc"},
    )
    assert resp.status_code == 201, resp.text
    dataset = resp.json()
    assert dataset["computed"] == COMPUTED[:1]
    data = (await client.get(f"/datasets/{dataset['id']}/data", headers=auth_headers)).json()
    assert [r["total"] for r in data["rows"]] == [100, 50, 30]
    # A bad expression or name is refused when saving.
    bad = await client.post("/datasets", headers=auth_headers, json={"name": "Bad", "source_type": "master", "source_id": str(orders_id), "computed": [{"name": "x", "expression": "{qty} *"}]})
    assert bad.status_code == 422
    bad = await client.post("/datasets", headers=auth_headers, json={"name": "Bad", "source_type": "master", "source_id": str(orders_id), "computed": [{"name": "two words", "expression": "1"}]})
    assert bad.status_code == 422
    # Ad hoc, from a page or the Data Explorer.
    resp = await client.post(
        "/datasets/query", headers=auth_headers, json={"source_type": "master", "source_id": str(orders_id), "columns": ["no", "total"], "computed": COMPUTED[:1], "filters": [{"field": "total", "operator": "lt", "value": 60}]}
    )
    assert sorted(r["total"] for r in resp.json()["rows"]) == [30, 50]